MUTASI GJ

SELECT x.i_product_group as i_product, x.satuan, sum(x.saldoawal) as saldoawal,
sum(x.git_sjint_kawal) as git_sjint_kawal, sum(x.git_sjint_rollkawal) as git_sjint_rollkawal,
sum(x.git_retur_kawal) as git_retur_kawal,sum(x.git_manu_kawal )as git_manu_kawal,
sum(x.git_sjint_awal) as git_sjint_awal, sum(x.git_sjint_rollawal) as git_sjint_rollawal,
sum(x.git_retur_awal) as git_retur_awal,sum(x.git_manu_awal) as git_manu_awal,
sum(x.sjint_in) as sjint_in, sum(x.sjint_inroll) as sjint_inroll,
sum(x.retur_in) as retur_in, sum(x.n_manu_in) as n_manu_in,sum(x.convertion_in) as convertion_in,
sum(x.convertion_out)as convertion_out, sum(x.sjint_out_roll) as sjint_out_roll,
sum(x.sjint_out) as sjint_out,sum(x.manu_out) as manu_out, sum(x.retur) as retur,
sum(x.git_sjint_out) as git_sjint_out, sum(x.git_sjint_outroll) as git_sjint_outroll,sum(x.git_manu) as git_manu,
sum(x.git_retur) as git_retur, sum(x.stockopname) as stockopname,
 'GJ' as n_store

 from(

 -- DO IN
 select a.i_product_group, b.e_satuan_name as satuan, sum(b.n_deliver) as do_in, 0 as convertion_in, 0 as convertion_out, 0 as sjint_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_do_item b on(a.i_product=b.i_product)
 left join tm_do c on(b.i_do=c.i_do)
 where to_char(b.d_do, 'yyyymm')='202203'
 and b.i_store='GJ'
 and c.f_do_cancel='f'
 group by a.i_product_group, b.e_satuan_name

 union all -- RETUR IN

 select a.i_product, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, sum(b.n_quantity_int_receive) as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel='f'
 and c.f_sjint_retur='t'
 and to_char(c.d_sjint_receive, 'yyyymm')='202203'
 and not c.d_sjint_receive isnull
 and a.f_product_cancel='f'
 and c.i_store_to='GJ'

 group by a.i_product, d.e_satuan_name

 union all -- SJINT IN

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as convertion_out, 0 as sjint_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, sum(b.n_quantity_int_receive) as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel='f'
 and c.f_sjint_retur='f'
 and to_char(c.d_sjint_receive, 'yyyymm')='202203'
 and not c.d_sjint_receive isnull
 and a.f_product_cancel='f'
 and c.f_convertion_product='f'
 and c.i_store_to='GJ'
 group by a.i_product_group, d.e_satuan_name

 union all -- SJINT ROLL IN

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as convertion_out, 0 as sjint_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, sum(b.n_quantity_int_receive) as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel='f'
 and c.f_sjint_retur='f'
 and to_char(c.d_sjint_receive, 'yyyymm')='202203'
 and not c.d_sjint_receive isnull
 and a.f_product_cancel='f'
 and c.f_convertion_product='t'
 and c.i_store_to='GJ'
 group by a.i_product_group, d.e_satuan_name

 union all --CONV IN

 select a.i_product_group, b.e_satuan_name as satuan, 0 as do_in, sum(b.n_ic_convertion) as convertion_in, 0 as convertion_out, 0 as sjint_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_ic_convertionitem b on(a.i_product=b.i_product)
 left join tm_ic_convertion c on(b.i_ic_convertion=c.i_ic_convertion)
 where c.f_ic_convertioncancel='f'
 and a.f_product_cancel='f'
 and to_char(d_ic_convertion, 'yyyymm')='202203'
 and b.i_store='GJ'
 group by a.i_product_group, b.e_satuan_name


 union all -- CONV OUT

 select a.i_product_group, b.e_satuan_name_from as satuan, 0 as do_in, 0 as convertion_in, sum(b.n_ic_convertion_from) as convertion_out, 0 as sjint_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_ic_convertionitem b on(a.i_product=b.i_product)
 left join tm_ic_convertion c on(b.i_ic_convertion=c.i_ic_convertion)
 where c.f_ic_convertioncancel='f'
 and a.f_product_cancel='f'
 and b.i_store='GJ'
 and to_char(d_ic_convertion, 'yyyymm')='202203'
 group by a.i_product_group, b.e_satuan_name_from

 union all -- SJINT OUT

 select a.i_product, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as convertion_out, sum(b.n_quantity_int_receive) as sjint_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel='f'
 and c.f_sjint_retur='f'
 and to_char(c.d_sjint_receive, 'yyyymm')='202203'
 and not c.d_sjint_receive isnull
 and a.f_product_cancel='f'
 and c.f_convertion_product='f'
 and c.i_store_from='GJ'
 group by a.i_product, d.e_satuan_name

 union all -- SJINT ROLL OUT

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as convertion_out, 0 as sjint_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, sum(b.n_quantity_int_receive) as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel='f'
 and c.f_sjint_retur='f'
 and to_char(c.d_sjint_receive, 'yyyymm')='202203'
 and not c.d_sjint_receive isnull
 and a.f_product_cancel='f'
 and c.i_store_from='GJ'
 and c.f_convertion_product='t'
 group by a.i_product_group, d.e_satuan_name


 union all -- RETUR

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, sum(b.n_quantity_int_receive) as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel='f'
 and c.f_sjint_retur='t'
 and to_char(b.d_sjint, 'yyyymm')='202203'
 and not c.d_sjint_receive isnull
 and a.f_product_cancel='f'
 and c.i_store_from='GJ'

 group by a.i_product_group, d.e_satuan_name


 union all -- GIT SJINT OUT

 select a.i_product, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, sum(b.n_quantity_int) as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='f'
 and c.i_store_from='GJ'
 and c.f_convertion_product='f'
 --- tanggal receive null dan sj pada periode berjalan
 --- atau periode receive melebihi bulan berjalan dan tanggal sj periode sekarang
 and (
 (c.d_sjint_receive isnull and (to_char(c.d_sjint, 'yyyymm'::text)='202203'))
 or ((to_char(c.d_sjint_receive, 'yyyymm'::text)>'202203' and to_char(c.d_sjint, 'yyyymm'::text) = '202203'))
 )

 group by a.i_product, d.e_satuan_name

 union all -- GIT SJINT OUT ROLL

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, sum(b.n_quantity_int) as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='f'
 and c.i_store_from='GJ'
 and c.f_convertion_product='t'
 --- tanggal receive null dan sj pada periode berjalan
 --- atau periode receive melebihi bulan berjalan dan tanggal sj periode sekarang
 and (
 (c.d_sjint_receive isnull and (to_char(c.d_sjint, 'yyyymm'::text)='202203'))
 or ((to_char(c.d_sjint_receive, 'yyyymm'::text)>'202203' and to_char(c.d_sjint, 'yyyymm'::text) = '202203'))
 )

 group by a.i_product_group, d.e_satuan_name


 union all -- GIT RETUR

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, sum(b.n_quantity_int) as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='t'
 and c.i_store_from='GJ'
 --- tanggal receive null dan sj pada periode berjalan
 --- atau periode receive melebihi bulan berjalan dan tanggal sj periode sekarang
 and (
 (c.d_sjint_receive isnull and (to_char(c.d_sjint, 'yyyymm'::text)='202203'))
 or ((to_char(c.d_sjint_receive, 'yyyymm'::text)>'202203' and to_char(c.d_sjint, 'yyyymm'::text) = '202203'))
 )

 group by a.i_product_group, d.e_satuan_name


 union all -- GIT SJINT ROLL AWAL

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, sum(b.n_quantity_int) as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='f'
 and c.i_store_from='GJ'
 and c.f_convertion_product='t'
 --- tanggal receive null dan sj periode sebelumnya
 --- atau periode receive melebihi bulan berjalan dan tanggal sj kurang dari periode sekarang
 and ((c.d_sjint_receive is null and to_char(c.d_sjint, 'yyyymm'::text) <'202203' )
 or
 ( to_char(c.d_sjint_receive, 'yyyymm'::text) > to_char(c.d_sjint, 'yyyymm'::text)
 --and to_char(c.d_sjint_receive, 'yyyymm'::text) < '202203'
 and to_char(c.d_sjint_receive, 'yyyymm'::text) = '202203')
 )
 group by a.i_product_group, d.e_satuan_name

 union all -- GIT SJINT AWAL

 select a.i_product, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, sum(b.n_quantity_int) as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='f'
 and c.i_store_from='GJ'
 and c.f_convertion_product='f'
 --- tanggal receive null dan sj periode sebelumnya
 --- atau periode receive melebihi bulan berjalan dan tanggal sj kurang dari periode sekarang
 and ((c.d_sjint_receive is null and to_char(c.d_sjint, 'yyyymm'::text) <'202203' )
 or
 ( to_char(c.d_sjint_receive, 'yyyymm'::text) > to_char(c.d_sjint, 'yyyymm'::text)
 --and to_char(c.d_sjint_receive, 'yyyymm'::text) < '202203'
 and to_char(c.d_sjint_receive, 'yyyymm'::text) = '202203')
 )
 group by a.i_product, d.e_satuan_name

 union all -- GIT SJINT KELUAR AWAL

 select a.i_product, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, sum(b.n_quantity_int) as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='f'
 and c.i_store_from='GJ'
 and c.f_convertion_product='f'
 --- tanggal receive null dan sj periode sebelumnya
 --- atau periode receive melebihi bulan berjalan dan tanggal sj kurang dari periode sekarang
 and ((c.d_sjint_receive is null and to_char(c.d_sjint, 'yyyymm'::text) <'202203' )
 or
 ( to_char(c.d_sjint_receive, 'yyyymm'::text) > '202203'
 and to_char(c.d_sjint, 'yyyymm'::text) < '202203')
 )
 group by a.i_product, d.e_satuan_name

 union all -- GIT SJINT ROLL KELUAR AWAL

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, sum(b.n_quantity_int) as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='f'
 and c.i_store_from='GJ'
 and c.f_convertion_product='t'
 --- tanggal receive null dan sj periode sebelumnya
 --- atau periode receive melebihi bulan berjalan dan tanggal sj kurang dari periode sekarang
 and ((c.d_sjint_receive is null and to_char(c.d_sjint, 'yyyymm'::text) <'202203' )
 or
 ( to_char(c.d_sjint_receive, 'yyyymm'::text) > '202203'
 and to_char(c.d_sjint, 'yyyymm'::text) < '202203')
 )
 group by a.i_product_group, d.e_satuan_name

 union all -- GIT SJINT RETUR KELUAR AWAL

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, sum(b.n_quantity_int) as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='t'
 and c.i_store_from='GJ'
 --- tanggal receive null dan sj periode sebelumnya
 --- atau periode receive melebihi bulan berjalan dan tanggal sj kurang dari periode sekarang
 and ((c.d_sjint_receive isnull and to_char(c.d_sjint, 'yyyymm'::text) <'202203' )
 or
 ( to_char(c.d_sjint_receive, 'yyyymm'::text) > '202203'
 and to_char(c.d_sjint, 'yyyymm'::text) < '202203')
 )
 group by a.i_product_group, d.e_satuan_name

 union all -- GIT RETUR AWAL

 select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, sum(b.n_quantity_int) as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_sj_int_item b on(a.i_product=b.i_product)
 left join tm_sj_int c on(b.i_sjint=c.i_sjint)
 left join tr_product_satuan d on(a.i_product=d.i_product)
 where c.f_sjint_cancel ='f'
 and c.f_sjint_retur='t'
 and c.i_store_from='GJ'
 --- tanggal receive null dan sj periode sebelumnya
 --- atau periode receive melebihi bulan berjalan dan tanggal sj kurang dari periode sekarang
 and ((c.d_sjint_receive isnull and to_char(c.d_sjint, 'yyyymm'::text) <'202203' )
 or
 ( to_char(c.d_sjint_receive, 'yyyymm'::text) > to_char(c.d_sjint, 'yyyymm'::text)
 --and to_char(c.d_sjint_receive, 'yyyymm'::text) < '202203'
 and to_char(c.d_sjint_receive, 'yyyymm'::text) = '202203')
 )
 group by a.i_product_group, d.e_satuan_name


 union all -- SALDO AWAL

 select a.i_product, b.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, sum(b.n_saldo_awal) as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_mutasi_saldoawal b on(a.i_product=b.i_product)
 -- left join tr_product_satuan d on(a.i_product=d.i_product)
 where b.i_store='GJ'
 and b.i_store_location='00'
 and b.e_mutasi_periode='202203'
 group by a.i_product, b.e_satuan_name

 union all -- STOCKOPNAME

 select a.i_product, b.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, sum(b.n_stockopname) as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_stockopname_item b on(a.i_product=b.i_product)
 left join tm_stockopname c on(b.i_stockopname=c.i_stockopname)
 where c.i_store='GJ'
 and b.i_store_location='00'
 and b.e_mutasi_periode='202203'
 and c.f_stockopname_cancel='f'
 group by a.i_product, b.e_satuan_name

union all -- MANUFAKTUR IN
select a.i_product, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, sum(n_manufacture_detailhasil_roll) as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
 from tr_product_stock_produksi a
 left join tm_manufacturer_item b on(a.i_product=b.i_product)
 left join tm_manufacturer c on(b.i_manufacturer=c.i_manufacturer)
 left join tr_product_satuan d on(b.i_product=d.i_product)
 where b.i_store='GJ'
 and b.i_store_location='00'
 and to_char(c.d_manufacturer_finish, 'yyyymm')='202203'
 and c.f_manufacturer_cancel='f'
 group by a.i_product, d.e_satuan_name

union all -- MANUFAKTUR OUT
select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, sum(b.n_manufacture_bom) as manu_out, 0 as git_manu,0 as git_manu_kawal, 0 as git_manu_awal
from tr_product_stock_produksi a
left join tm_manufacturer_item b on(a.i_product=b.i_product_bom)
left join tm_manufacturer c on(b.i_manufacturer=c.i_manufacturer)
left join tr_product_satuan d on(b.i_product_bom=d.i_product)
where b.i_store='GJ'
and b.i_store_location='00'
and to_char(c.d_manufacturer_finish, 'yyyymm')='202203'
and c.f_manufacturer_cancel='f'
group by a.i_product_group, d.e_satuan_name

union all -- GIT MANUFAKTUR
select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, sum(b.n_manufacture_bom) as git_manu, 0 as git_manu_kawal, 0 as git_manu_awal
from tr_product_stock_produksi a
left join tm_manufacturer_item b on(a.i_product=b.i_product_bom)
left join tm_manufacturer c on(b.i_manufacturer=c.i_manufacturer)
left join tr_product_satuan d on(b.i_product_bom=d.i_product)
where b.i_store='GJ'
and b.i_store_location='00'
--- tanggal finis null dan sj pada periode berjalan
--- atau periode receive melebihi bulan berjalan dan tanggal sj periode sekarang
and (
 (c.d_manufacturer_finish isnull and (to_char(c.d_manufacturer, 'yyyymm'::text)='202203'))
 or ((to_char(c.d_manufacturer_finish, 'yyyymm'::text)>'202203' and to_char(c.d_manufacturer, 'yyyymm'::text) = '202203'))
)
and c.f_manufacturer_cancel='f'
group by a.i_product_group, d.e_satuan_name


union all -- GIT MANUFAKTUR KELUAR AWAL
select a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,sum(b.n_manufacture_bom) as git_manu_kawal, 0 as git_manu_awal
from tr_product_stock_produksi a
left join tm_manufacturer_item b on(a.i_product=b.i_product_bom)
left join tm_manufacturer c on(b.i_manufacturer=c.i_manufacturer)
left join tr_product_satuan d on(b.i_product_bom=d.i_product)
where b.i_store='GJ'
and b.i_store_location='00'
--- tanggal finish null dan manufaktur periode sebelumnya
 --- atau periode finis melebihi bulan berjalan dan tanggal manufaktur kurang dari periode sekarang
 and ((c.d_manufacturer_finish isnull and to_char(c.d_manufacturer, 'yyyymm'::text) <'202203' )
 or
 ( to_char(c.d_manufacturer_finish, 'yyyymm'::text) > '202203'
 and to_char(c.d_manufacturer, 'yyyymm'::text) < '202203')
 )
and c.f_manufacturer_cancel='f'
group by a.i_product_group, d.e_satuan_name

union all -- GIT MANUFAKTUR AWAL
select  a.i_product_group, d.e_satuan_name as satuan, 0 as do_in, 0 as convertion_in, 0 as sjint_out, 0 as convertion_out, 0 as retur, 0 as git_sjint_out, 0 as git_retur, 0 as git_sjint_kawal, 0 as git_retur_kawal, 0 as git_sjint_awal, 0 as git_retur_awal, 0 as saldoawal, 0 as stockopname, 0 as sjint_out_roll, 0 as git_sjint_rollkawal, 0 as git_sjint_rollawal, 0 as git_sjint_outroll, 0 as retur_in, 0 as sjint_in, 0 as sjint_inroll, 0 as n_manu_in, 0 as manu_out, 0 as git_manu,0 as git_manu_kawal,sum(b.n_manufacture_bom) as git_manu_awal
from tr_product_stock_produksi a
left join tm_manufacturer_item b on(a.i_product=b.i_product_bom)
left join tm_manufacturer c on(b.i_manufacturer=c.i_manufacturer)
left join tr_product_satuan d on(b.i_product_bom=d.i_product)
where b.i_store='GJ'
and b.i_store_location='00'
--- tanggal receive null dan sj periode sebelumnya
 --- atau periode receive melebihi bulan berjalan dan tanggal sj kurang dari periode sekarang
 and ((c.d_manufacturer_finish is null and to_char(c.d_manufacturer, 'yyyymm'::text) <'202203' )
 or
 ( to_char(c.d_manufacturer_finish, 'yyyymm'::text) > to_char(c.d_manufacturer, 'yyyymm'::text)
 --and to_char(c.d_sjint_receive, 'yyyymm'::text) < '202203'
 and to_char(c.d_manufacturer_finish, 'yyyymm'::text) = '202203')
 )
and c.f_manufacturer_cancel='f'
group by a.i_product_group, d.e_satuan_name




 ) as x
 group by i_product_group, satuan
 ORDER BY i_product_group