WITH beginStock AS
(SELECT t.barcode,
t.quantity,
nvl(t.wh_to_id, '-1') as wh_id,
t.material_id
FROM tb_usc_material_flow t
WHERE t.del_flag = 'N'
AND t.barcode is not null
UNION ALL
SELECT js.barcode,
-js.jit_stock_sum_qty as quantity,
nvl(js.wh_id, '-1'),
js.material_id
FROM tb_usc_jit_stock js
WHERE js.del_flag = 'N'
AND js.barcode is not null
)
SELECT distinct t.whId "whId"
FROM (select barcode,
sum(quantity) quantity,
wh_id as whId,
material_id
from beginStock
group by barcode,wh_id,material_id) t
WHERE t.quantity != 0
WITH beginStock AS(
SELECT t.material_id,
t.quantity, t.monthly_bal_id
FROM tb_usc_monthly_bal_flw t
WHERE t.del_flag = 'N'
UNION ALL
SELECT js.material_id,
-js.jit_stock_sum_qty as quantity, js.monthly_bal_id
FROM tb_usc_monthly_bal_end_jit js
WHERE js.del_flag = 'N'
UNION ALL
SELECT fs.material_id,
fs.jit_stock_sum_qty as quantity,fs.monthly_bal_id
FROM tb_usc_monthly_bal_begin_jit fs
WHERE fs.del_flag = 'N'
)
SELECT t.material_id "materialId"
FROM (select material_id,
sum(quantity) quantity
from beginStock
group by material_id,monthly_bal_id) t
WHERE t.quantity != 0
drop table tb_usc_monthly_balance_dtl_flw
drop table tb_usc_monthly_balance_dtl_jit
drop table tb_usc_monthly_balance_mst