dbo.gab_rpt_mutasi_bahanbaku_all

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO


CREATE FUNCTION dbo.gab_rpt_mutasi_bahanbaku_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_material_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_bb(@date_start,@date_stop) group by kodebarang),
 --ct_saldo_awal_ceisa (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pemasukan_ceisa where receive_date < @date_start AND TIPE_ITEM='MATERIAL' group by KODE_BARANG),
 --ct_brg_in (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_pemasukan_barang where tgl_receive between @date_start and @date_stop group by kode_brg),
 --ct_brg_in_ceisa (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pemasukan_ceisa where receive_date between @date_start and @date_stop AND TIPE_ITEM='MATERIAL' group by KODE_BARANG),
 --ct_brg_out (kodebarang,qty) as (select kode_barang,sum(pengeluaran) from AW.DB_PASI_20201106.dbo.tbl_stock_out_bb where tanggal_pengeluaran between @date_start and @date_stop group by kode_barang),
 --ct_penyesuaian (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_adjustment_bb where tgl_sto between @date_start and @date_stop group by kode_barang),
 --ct_stock_opname (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_stock_opname_bb where tgl_sto between @date_start and @date_stop group by kode_barang),
 --ct_brg_in_awal_periode (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_pemasukan_barang where tgl_receive between case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_material) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_material order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start) group by kode_brg),
 --ct_brg_out_awal_periode (kodebarang, qty) as (select kode_barang,sum(pengeluaran) from AW.DB_PASI_20201106.dbo.tbl_stock_out_bb where tanggal_pengeluaran between case when exists(select * from AW.DB_PASI_20201106.dbo.tbl_stock_material) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_material order by tgl_sto desc) else '2010-01-01' end and dateadd(day,-1, @date_start) group by kode_barang)
 -- select ct_kodebrg.kodebarang as barang_code
 --, ct_kodebrg.namabarang as barang_name
 --, cast(isnull(ct_saldo_awal_ceisa.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(isnull(ct_brg_in.qty,0) as decimal(24,4)) + cast(isnull(ct_brg_in_ceisa.qty,0) as decimal(24,4)) as pemasukan
 --, cast(isnull(ct_brg_out.qty,0) as decimal(24,4)) as pengeluaran
 --, cast(isnull(ct_penyesuaian.qty,0) as decimal(24,4)) as penyesuaian
 --, cast((isnull(ct_saldo_awal_ceisa.qty,0)+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_brg_in_ceisa.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_bb where tgl_sto between @date_start and @date_stop ) is null then 0
 --else (isnull(ct_saldo_awal_ceisa.qty,0) +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_in_ceisa.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,
 --'' 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_penyesuaian on ct_kodebrg.kodebarang = ct_penyesuaian.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_in_ceisa on ct_kodebrg.kodebarang = ct_brg_in_ceisa.kodebarang
 -- left outer join ct_saldo_awal_ceisa on ct_kodebrg.kodebarang = ct_saldo_awal_ceisa.kodebarang
 ct_kodebrg (kodebarang,namabarang,satuan,mapped) as (select kdbrg, nama_barang, satuan , mapped from AW.DB_PASI_20201106.dbo.tbl_master_material_code WHERE (mapped is NULL) AND (cons = '0') group by kdbrg,nama_barang,satuan,mapped),
 ct_saldo_awal (kodebarang,qty) as (select kodebarang,sum(qty) from aw_fn_saldo_awal_bb(@date_start,@date_stop)  group by kodebarang),  
 ct_saldo_awal_ceisa (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pemasukan_ceisa where receive_date < @date_start AND TIPE_ITEM='MATERIAL' group by KODE_BARANG),
 ct_brg_in (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_pemasukan_barang where tgl_receive between @date_start and @date_stop group by kode_brg),
 ct_brg_in_ceisa (kodebarang,qty) as (select KODE_BARANG,sum(JUMLAH_SATUAN) from AW.DB_PASI_20201106.dbo.dv_data_pemasukan_ceisa where receive_date between @date_start and @date_stop AND TIPE_ITEM='MATERIAL' group by KODE_BARANG),
 ct_brg_out (kodebarang,qty) as (select kode_barang,sum(pengeluaran) from AW.DB_PASI_20201106.dbo.tbl_stock_out_bb where tanggal_pengeluaran between @date_start and @date_stop group by kode_barang),
 ct_penyesuaian (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_adjustment_bb where tgl_sto between @date_start and @date_stop group by kode_barang),
 ct_stock_opname (kodebarang,qty) as (select kode_barang,sum(qty) from AW.DB_PASI_20201106.dbo.tbl_stock_opname_bb where tgl_sto between @date_start and @date_stop group by kode_barang),
 ct_brg_in_awal_periode (kodebarang,qty) as (select kode_brg,sum(jumlah) from AW.DB_PASI_20201106.dbo.tbl_pemasukan_barang where tgl_receive  between case when exists(select *  from AW.DB_PASI_20201106.dbo.tbl_stock_material) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_material order by tgl_sto desc) else '2010-01-01' end  and dateadd(day,-1, @date_start) group by kode_brg),
 ct_brg_out_awal_periode (kodebarang, qty) as (select kode_barang,sum(pengeluaran) from AW.DB_PASI_20201106.dbo.tbl_stock_out_bb where tanggal_pengeluaran between  case when exists(select *  from AW.DB_PASI_20201106.dbo.tbl_stock_material) then (select top 1 tgl_sto from AW.DB_PASI_20201106.dbo.tbl_stock_material order by tgl_sto desc) else '2010-01-01' end  and dateadd(day,-1, @date_start) group by kode_barang)    
   select ct_kodebrg.kodebarang as barang_code
 , ct_kodebrg.namabarang as barang_name
 , cast(isnull(ct_saldo_awal_ceisa.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(isnull(ct_brg_in.qty,0) as decimal(24,4)) + cast(isnull(ct_brg_in_ceisa.qty,0) as decimal(24,4))  as pemasukan 
 , cast(isnull(ct_brg_out.qty,0) as decimal(24,4)) as pengeluaran 
 , cast(isnull(ct_penyesuaian.qty,0) as decimal(24,4)) as penyesuaian 
 , cast((isnull(ct_saldo_awal_ceisa.qty,0)+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_brg_in_ceisa.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_bb where tgl_sto between @date_start and @date_stop ) is null  then 0 
 else (isnull(ct_saldo_awal_ceisa.qty,0) +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_in_ceisa.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,
 '' 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_penyesuaian on ct_kodebrg.kodebarang = ct_penyesuaian.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_in_ceisa on ct_kodebrg.kodebarang = ct_brg_in_ceisa.kodebarang
   left outer join ct_saldo_awal_ceisa  on ct_kodebrg.kodebarang = ct_saldo_awal_ceisa.kodebarang

 union all
 select  kodebarang as barang_code,
 namabarang as barang_name,
 cast(qty_saldoawal as decimal(24,4)) as saldo_awal,
 cast(qty_in as decimal(24,4)) as pemasukan,
 cast(qty_out as decimal(24,4)) as pengeluaran,
 cast(adj  as decimal(24,4)) as penyesuaian ,
 cast(saldo_akhir as decimal(24,4)) as saldo_akhir,
 cast(stock_opname as decimal(24,4)) as stock_opname,
 cast(selisih as decimal(24,4)) as selisih,
 unit as satuan_code,
 '' as keterangan
 from dbo.f_mutasi_bb(@date_start,@date_stop,'RM')
  
)  
--GO
-- SELECT *
--FROM dbo.gab_rpt_mutasi_bahanbaku_all('12/12/2015', '12/12/2016')
--order by barang_code

GO