dbo.gab_rpt_mutasi_barangjadi_all

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO


CREATE FUNCTION dbo.gab_rpt_mutasi_barangjadi_all(
@date_start date
,@date_stop date  
)
 
RETURNS TABLE  
---XencX---with encryption
AS 
RETURN ( 

with 
 --ct_kodebrg (kodebarang,namabarang,satuan) as (select kdbrg, nama_barang, satuan from AW.DB_PASI_20201106.dbo.tbl_master_finishgood_code group by kdbrg,nama_barang,satuan),
 --ct_saldo_awal (kodebarang,qty) as (select kodebarang,sum(qty) from AW.DB_PASI_20201106.dbo.fn_saldo_awal_bj(@date_start,@date_stop) group by kodebarang),
 --ct_brg_in (kodebarang,qty) as (select kdbrg,sum(ttlQtyMeter) from AW.DB_PASI_20201106.dbo.tbl_finish_goods where tglprod between @date_start and @date_stop group by kdbrg),
 --ct_brg_out (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between @date_start and @date_stop group by kode_brg),
 --ct_stock_opname (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_stock_opname_finish_goods where tgl_sto between @date_start and @date_stop group by kode_barang),
 --ct_brg_in_awal_periode (kodebarang,qty) as (select kdbrg,sum(ttlQtyMeter) from AW.DB_PASI_20201106.dbo.tbl_finish_goods where tglprod between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start) group by kdbrg),
 --ct_brg_out_awal_periode (kodebarang, qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start) group by kode_brg)
 
 
 -- select
 --ct_kodebrg.kodebarang as barang_code
 --, ct_kodebrg.namabarang as barang_name
 --,cast(isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0) as decimal(24,4)) as saldo_awal
 -- ,cast(0 as decimal(24,4)) as penyesuaian
 --,cast((isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) - isnull(ct_brg_out.qty,0) as decimal(24,4)) as saldo_akhir
 --,cast(isnull(ct_stock_opname.qty,0) as decimal(24,4)) as stock_opname
 --,cast((case when (select top 1 qty from AW.DB_PASI_20201106.dbo.tbl_stock_opname_finish_goods where tgl_sto between @date_start and @date_stop ) is null then 0
 --else (isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) - isnull(ct_brg_out.qty,0) - isnull(ct_stock_opname.qty,0) end) as decimal(24,4)) as selisih
 --, ct_kodebrg.satuan as satuan_code
 --,cast(isnull(ct_brg_in.qty,0) as decimal(24,4)) as pemasukan
 --,cast(isnull(ct_brg_out.qty,0) as decimal(24,4)) as pengeluaran
 --,'' as keterangan
 -- from ct_kodebrg
 -- left outer join ct_saldo_awal on ct_kodebrg.kodebarang = ct_saldo_awal.kodebarang
 -- left outer join ct_brg_in on ct_kodebrg.kodebarang = ct_brg_in.kodebarang
 -- left outer join ct_brg_out on ct_kodebrg.kodebarang = ct_brg_out.kodebarang
 -- left outer join ct_brg_in_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_awal_periode.kodebarang
 -- left outer join ct_brg_out_awal_periode on ct_kodebrg.kodebarang = ct_brg_out_awal_periode.kodebarang
 -- left outer join ct_stock_opname on ct_kodebrg.kodebarang = ct_stock_opname.kodebarang
 --ct_kodebrg (kodebarang,namabarang,satuan) as (select kdbrg, nama_barang, satuan from AW.DB_PASI_20201106.dbo.tbl_master_finishgood_code group by kdbrg,nama_barang,satuan),
 --ct_saldo_awal (kodebarang,qty) as (select kodebarang,sum(qty) from AW.DB_PASI_20201106.dbo.fn_saldo_awal_bj(@date_start,@date_stop) group by kodebarang),
 --ct_brg_in (kodebarang,qty) as (select kdbrg,sum(ttlQtyMeter) from AW.DB_PASI_20201106.dbo.tbl_finish_goods where tglprod between @date_start and @date_stop group by kdbrg),
 --ct_brg_out (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between @date_start and @date_stop group by kode_brg),
 --ct_brg_out_ceisa (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between @date_start and @date_stop AND TIPE_ITEM='1' group by item_code),
 --ct_stock_opname (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_stock_opname_finish_goods where tgl_sto between @date_start and @date_stop group by kode_barang),
 --ct_brg_in_awal_periode (kodebarang,qty) as (select kdbrg,sum(ttlQtyMeter) from AW.DB_PASI_20201106.dbo.tbl_finish_goods where tglprod between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start) group by kdbrg),
 --ct_brg_out_awal_periode (kodebarang, qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start) group by kode_brg)
 -- select
 --ct_kodebrg.kodebarang as barang_code
 --, ct_kodebrg.namabarang as barang_name
 --,cast(isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0) as decimal(24,4)) as saldo_awal
 -- ,cast(0 as decimal(24,4)) as penyesuaian
 --,cast((isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) - isnull(ct_brg_out.qty,0) as decimal(24,4)) as saldo_akhir
 --,cast(isnull(ct_stock_opname.qty,0) as decimal(24,4)) as stock_opname
 --,cast((case when (select top 1 qty from AW.DB_PASI_20201106.dbo.tbl_stock_opname_finish_goods where tgl_sto between @date_start and @date_stop ) is null then 0
 --else (isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) - isnull(ct_brg_out.qty,0) - isnull(ct_stock_opname.qty,0) - isnull(ct_brg_out_ceisa.qty,0) end) as decimal(24,4)) as selisih
 --, ct_kodebrg.satuan as satuan_code
 --,cast(isnull(ct_brg_in.qty,0) as decimal(24,4)) as pemasukan
 --,cast(isnull(ct_brg_out.qty,0) as decimal(24,4)) + cast(isnull(ct_brg_out_ceisa.qty,0) as decimal(24,4)) as pengeluaran
 --,'' as keterangan
 -- from ct_kodebrg
 -- left outer join ct_saldo_awal on ct_kodebrg.kodebarang = ct_saldo_awal.kodebarang
 -- left outer join ct_brg_in on ct_kodebrg.kodebarang = ct_brg_in.kodebarang
 -- left outer join ct_brg_out on ct_kodebrg.kodebarang = ct_brg_out.kodebarang
 -- left outer join ct_brg_in_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_awal_periode.kodebarang
 -- left outer join ct_brg_out_awal_periode on ct_kodebrg.kodebarang = ct_brg_out_awal_periode.kodebarang
 -- left outer join ct_stock_opname on ct_kodebrg.kodebarang = ct_stock_opname.kodebarang
 -- left outer join ct_brg_out_ceisa on ct_kodebrg.kodebarang = ct_brg_out_ceisa.kodebarang
 ct_kodebrg (kodebarang,namabarang,satuan) as (select kdbrg, nama_barang, satuan from AW.DB_PASI_20201106.dbo.tbl_master_finishgood_code group by kdbrg,nama_barang,satuan),
 ct_saldo_awal (kodebarang,qty) as (select kodebarang,sum(qty) from aw_fn_saldo_awal_bj(@date_start,@date_stop)  group by kodebarang),  
 ct_brg_in (kodebarang,qty) as (select kdbrg,sum(ttlQtyMeter) from AW.DB_PASI_20201106.dbo.tbl_finish_goods where tglprod between @date_start and @date_stop group by kdbrg),
 ct_brg_out (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between @date_start and @date_stop group by kode_brg),
 ct_brg_out_ceisa_25 (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between @date_start and @date_stop AND  NOT supplier='PT SUBANG AUTOCOMP INDONESIA' AND KODE_DOKUMEN='BC25' AND TIPE_ITEM='1' group by item_code),
 ct_brg_out_ceisa_27 (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between @date_start and @date_stop AND KODE_DOKUMEN='BC27' AND TIPE_ITEM='1' group by item_code),
 ct_brg_out_ceisa_41 (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between @date_start and @date_stop AND KODE_DOKUMEN='BC41' AND TIPE_ITEM='1' group by item_code),
 ct_brg_out_ceisa_30 (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between @date_start and @date_stop AND KODE_DOKUMEN='BC30' AND TIPE_ITEM='1' group by item_code),  
 ct_brg_out_aktual (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_aktual where sa_date between @date_start and @date_stop group by item_code),
 ct_stock_opname (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_stock_opname_finish_goods where tgl_sto between @date_start and @date_stop group by kode_barang),
 ct_brg_in_awal_periode (kodebarang,qty) as (select kdbrg,sum(ttlQtyMeter) from AW.DB_PASI_20201106.dbo.tbl_finish_goods where tglprod  between  case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start) group by kdbrg),
 ct_brg_out_awal_periode (kodebarang, qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_outgoing_item where tgl_out between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start) group by kode_brg) ,   
 ct_brg_out_ceisa_25_awal (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start)  AND  NOT supplier='PT SUBANG AUTOCOMP INDONESIA' AND KODE_DOKUMEN='BC25' AND TIPE_ITEM='1' group by item_code),
 ct_brg_out_ceisa_27_awal (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start)  AND KODE_DOKUMEN='BC27' AND TIPE_ITEM='1' group by item_code),
 ct_brg_out_ceisa_41_awal (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start)  AND KODE_DOKUMEN='BC41' AND TIPE_ITEM='1' group by item_code),
 ct_brg_out_ceisa_30_awal (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_ceisa where sa_invoice_date between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start)  AND KODE_DOKUMEN='BC30' AND TIPE_ITEM='1' group by item_code),  
 ct_brg_out_aktual_awal (kodebarang,qty) as (select item_code,sum(qty) from AW.DB_PASI_20201106.dbo.dv_data_pengeluaran_aktual where sa_date between case when exists(select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_finish_goods order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start)  group by item_code)
   select 
 ct_kodebrg.kodebarang as barang_code
 , ct_kodebrg.namabarang as barang_name
 ,cast(isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0) - isnull(ct_brg_out_ceisa_25_awal.qty,0) - isnull(ct_brg_out_ceisa_27_awal.qty,0) - isnull(ct_brg_out_ceisa_41_awal.qty,0) - isnull(ct_brg_out_ceisa_30_awal.qty,0) - isnull(ct_brg_out_aktual_awal.qty,0) as decimal(24,4)) as saldo_awal
  ,cast(0 as decimal(24,4)) as penyesuaian
 ,cast((isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)- isnull(ct_brg_out_ceisa_25_awal.qty,0) - isnull(ct_brg_out_ceisa_27_awal.qty,0) - isnull(ct_brg_out_ceisa_41_awal.qty,0) - isnull(ct_brg_out_ceisa_30_awal.qty,0) - isnull(ct_brg_out_aktual_awal.qty,0)) + isnull(ct_brg_in.qty,0) - isnull(ct_brg_out.qty,0) - isnull(ct_brg_out_aktual.qty,0) - isnull(ct_brg_out_ceisa_30.qty,0) - isnull(ct_brg_out_ceisa_41.qty,0) - isnull(ct_brg_out_ceisa_27.qty,0)- isnull(ct_brg_out_ceisa_25.qty,0) as decimal(24,4) ) as saldo_akhir
 ,cast(isnull(ct_stock_opname.qty,0) as decimal(24,4)) as stock_opname
 --,cast((case when (select top 1 qty from AW.DB_PASI_20201106.dbo.tbl_stock_opname_finish_goods where tgl_sto between @date_start and @date_stop ) is null then 0
 --else (isnull(ct_saldo_awal.qty,0) + isnull(ct_brg_in_awal_periode.qty,0) - isnull(ct_brg_out_awal_periode.qty,0)) + isnull(ct_brg_in.qty,0) - isnull(ct_brg_out.qty,0) - isnull(ct_stock_opname.qty,0) - isnull(ct_brg_out_ceisa_25.qty,0) - isnull(ct_brg_out_ceisa_27.qty,0) - isnull(ct_brg_out_ceisa_41.qty,0) - isnull(ct_brg_out_ceisa_30.qty,0) - isnull(ct_brg_out_aktual.qty,0) end) as decimal(24,4)) as selisih
 ,0 as selisih
 , ct_kodebrg.satuan as satuan_code 
 ,cast(isnull(ct_brg_in.qty,0) as decimal(24,4)) as pemasukan
 ,cast(isnull(ct_brg_out.qty,0) as decimal(24,4)) + cast(isnull(ct_brg_out_ceisa_25.qty,0) as decimal(24,4)) + cast(isnull(ct_brg_out_ceisa_27.qty,0) as decimal(24,4)) + cast(isnull(ct_brg_out_ceisa_41.qty,0) as decimal(24,4))+ cast(isnull(ct_brg_out_ceisa_30.qty,0) as decimal(24,4))  + cast(isnull(ct_brg_out_aktual.qty,0) as decimal(24,4)) as pengeluaran
 ,'' as keterangan
   from ct_kodebrg  
  left outer join ct_saldo_awal on ct_kodebrg.kodebarang = ct_saldo_awal.kodebarang
  left outer join ct_brg_in on ct_kodebrg.kodebarang = ct_brg_in.kodebarang
  left outer join ct_brg_out on ct_kodebrg.kodebarang = ct_brg_out.kodebarang
  left outer join ct_brg_in_awal_periode on ct_kodebrg.kodebarang = ct_brg_in_awal_periode.kodebarang
  left outer join ct_brg_out_awal_periode on ct_kodebrg.kodebarang = ct_brg_out_awal_periode.kodebarang 
  left outer join ct_stock_opname on ct_kodebrg.kodebarang = ct_stock_opname.kodebarang   
  left outer join ct_brg_out_ceisa_27 on ct_kodebrg.kodebarang = ct_brg_out_ceisa_27.kodebarang 
  left outer join ct_brg_out_ceisa_25 on ct_kodebrg.kodebarang = ct_brg_out_ceisa_25.kodebarang 
  left outer join ct_brg_out_ceisa_41 on ct_kodebrg.kodebarang = ct_brg_out_ceisa_41.kodebarang 
  left outer join ct_brg_out_ceisa_30 on ct_kodebrg.kodebarang = ct_brg_out_ceisa_30.kodebarang 
  left outer join ct_brg_out_aktual on ct_kodebrg.kodebarang = ct_brg_out_aktual.kodebarang 
  left outer join ct_brg_out_ceisa_25_awal on ct_kodebrg.kodebarang = ct_brg_out_ceisa_25_awal.kodebarang 
  left outer join ct_brg_out_ceisa_27_awal on ct_kodebrg.kodebarang = ct_brg_out_ceisa_27_awal.kodebarang 
  left outer join ct_brg_out_ceisa_41_awal on ct_kodebrg.kodebarang = ct_brg_out_ceisa_41_awal.kodebarang 
  left outer join ct_brg_out_ceisa_30_awal on ct_kodebrg.kodebarang = ct_brg_out_ceisa_30_awal.kodebarang 
  left outer join ct_brg_out_aktual_awal on ct_kodebrg.kodebarang = ct_brg_out_aktual_awal.kodebarang 
 union all
 select 
 part_no   as barang_code
 ,part_name   as barang_name
 ,cast(stock_awal as decimal(24,4)) as saldo_awal 
 ,cast(adj as decimal(24,4))as penyesuaian 
 ,cast(sisa_by_date as decimal(24,4)) as saldo_akhir
 ,cast(stock_opname as decimal(24,4)) as stock_opname
 ,cast(0 as decimal(24,4)) as selisih
 ,unit as satuan_code
 ,cast(qty_in as decimal(24,4)) as pemasukan
 ,cast(qty_out as decimal(24,4)) as pengeluaran  
 ,'' as keterangan
  from dbo.f_mutasi_fg(@date_start,@date_stop,'FG') 
)  
--GO
--SELECT *
--FROM dbo.gab_rpt_mutasi_barangjadi_all('12/12/2015', '12/12/2016')

GO