Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Null on Null Input | False |
| Schema Bound | False |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @tgstart | date | 3 | |
| @tgend | date | 3 | |
| @tipebahan | varchar | 50 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE FUNCTION dbo.f_mutasi_bbx
(
@tgstart date, @tgend date, @tipebahan varchar(50)
)
RETURNS @mutasibb table (
kodebarang varchar(100) null
,namabarang varchar(100) null
,qty_saldoawal float null
,qty_in float null
,qty_out float null
,saldo_akhir float null
,unit varchar(100) null
,stock_opname float null
,selisih float null
,adj float null
)
AS
begin
declare @datesaldoawal date
declare @stsstockopname int, @dtsto date
set @datesaldoawal=(select top 1 date_stock from dbo.tbl_stock_awal where typebahan='RM' order by date_stock desc)
set @stsstockopname = 0
if (
select count(*) from (
select count(*) as cnt from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] <= @tgstart and b.typebahan = 'RM'
) and a.typebahan = 'RM'
group by part_name_customs
) as x
) > 0
begin
set @stsstockopname = 1
set @dtsto = (select max(dt) from (
select [date_stock_opname] as dt from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] <= @tgstart and b.typebahan = 'RM'
) and a.typebahan = 'RM'
group by part_name_customs, [date_stock_opname]
) as x)
set @datesaldoawal=dateadd(day, 1, @dtsto)
end
;with
ct_kodebrg (kodebarang, partcustoms, satuan) as
(
select kode_barang, nama_barang, satuan from tbl_kode_barang
) ,
ct_saldo_awal
(kodebarang, partcustoms, qty) as (
select b.kode_barang,b.part_name_customs, sum(a.qty) from dbo.saldoawal(@tgstart,@tgend,'RM') a
inner join tbl_data_part_hs b on replace(a.part_no, char(32),'')=b.part_no2
group by b.kode_barang,b.part_name_customs
),
ct_brg_in(
partcustoms, qty
) as (
select part_name_customs,
sum(qty_in * isnull(pengali, 1)) --sum(qty_in)
from [tbl_data_in_dtl]
inner join tbl_data_in_hdr on [tbl_data_in_dtl].id_data_in_hdr=tbl_data_in_hdr.id_data_in_hdr
where type_brg = 1 and tbl_data_in_hdr.doc_in_date between @tgstart and @tgend
group by part_name_customs
),
ct_brg_out(
partcustoms, qty
) as (
select [part_name_customs], sum(qty) from
( select [part_name_customs], sum([qty] * isnull(pengali, 1)) as qty
FROM tbl_data_out_dtl INNER JOIN
tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr
where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
and [tbl_data_out_dtl].out_date between @tgstart and @tgend
group by [part_name_customs], [unit_out]
) x group by [part_name_customs]
),
ct_brg_in_awal_periode(
partcustoms, qty
) as (
select part_name_customs,
sum(qty_in * isnull(pengali, 1)) --sum(qty_in)
from [tbl_data_in_dtl]
inner join tbl_data_in_hdr on [tbl_data_in_dtl].id_data_in_hdr=tbl_data_in_hdr.id_data_in_hdr
where type_brg = 1 and tbl_data_in_hdr.doc_in_date between @datesaldoawal and dateadd(day, -1, @tgstart)
group by part_name_customs
),
ct_brg_out_awal_periode(
partcustoms, qty
) as (
select [part_name_customs], sum(qty) from
( select [part_name_customs], sum([qty] * isnull(pengali, 1)) as qty
FROM tbl_data_out_dtl INNER JOIN
tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr
where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
and [tbl_data_out_dtl].out_date between @datesaldoawal and dateadd(day, -1, @tgstart)
group by [part_name_customs], [unit_out]
) x group by [part_name_customs]
),
cte_inject_in_rm(
kodebarang, qty
) as
(
select [kode_barang], sum([qty]) from [dbo].[tbl_inject_rm_in]
where tgl between @tgstart and @tgend
group by [kode_barang]
),
cte_inject_in_rm_periode(
kodebarang, qty
) as
(
select [kode_barang], sum([qty]) from [dbo].[tbl_inject_rm_in]
where tgl between @datesaldoawal and dateadd(day, -1, @tgstart)
group by [kode_barang]
),
cte_inject_out_rm(
kodebarang, qty
) as
(
select [kode_barang], sum([qty]) from [dbo].[tbl_inject_rm_out]
where tgl between @tgstart and @tgend
group by [kode_barang]
),
cte_inject_out_rm_periode(
kodebarang, qty
) as
(
select [kode_barang], sum([qty]) from [dbo].[tbl_inject_rm_out]
where tgl between @datesaldoawal and dateadd(day, -1, @tgstart)
group by [kode_barang]
),
cte_stock_opname_awal(
partcustoms, qty
) as (
select part_name_customs, sum(qty) from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] < @tgstart
) and a.typebahan = 'RM'
group by part_name_customs
),
cte_stock_opname_akhir(
partcustoms, qty
) as (
select part_name_customs, sum(qty) from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] <= @tgend
) and a.typebahan = 'RM'
group by part_name_customs
),
cte_stock_opname_count(
partcustoms, cnt, [date_stock_opname]
) as (
select part_name_customs, count(*) AS cnt, [date_stock_opname] from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] < @tgstart
) and a.typebahan = 'RM'
group by part_name_customs, [date_stock_opname]
),
cte_stock_opname_tgl_akhir(
partcustoms, qty
) as (
select part_name_customs, sum(qty) from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] = @tgend
) and a.typebahan = 'RM'
group by part_name_customs
)
insert into @mutasibb (
kodebarang
,namabarang
,qty_saldoawal
,qty_in
,qty_out
,saldo_akhir
,unit
,stock_opname
,selisih
,adj
)
select ct_kodebrg.kodebarang, ct_kodebrg.partcustoms,
case when isnull(cte_stock_opname_count.cnt,0) = 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(cte_stock_opname_awal.qty,0)
else
--((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) ))
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
end
end as qty_saldoawal,
isnull(ct_brg_in.qty,0) + isnull(cte_inject_in_rm.qty, 0) as qty_in,
isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0) as qty_out,
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(cte_stock_opname_awal.qty,0)
else
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
) as saldo_akhir
,ct_kodebrg.satuan as unit
,isnull(cte_stock_opname_akhir.qty,0) as sto
,case when cte_stock_opname_tgl_akhir.qty is null then 0 else
cte_stock_opname_tgl_akhir.qty -
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(cte_stock_opname_awal.qty,0)
else
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
)
end as selisih
,case when cte_stock_opname_tgl_akhir.qty is null then 0 else
cte_stock_opname_tgl_akhir.qty -
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(cte_stock_opname_awal.qty,0)
else
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
)
end
from ct_kodebrg
left outer join ct_saldo_awal on ct_kodebrg.partcustoms = ct_saldo_awal.partcustoms
left outer join ct_brg_in on ct_kodebrg.partcustoms = ct_brg_in.partcustoms
left outer join ct_brg_out on ct_kodebrg.partcustoms = ct_brg_out.partcustoms
left outer join ct_brg_in_awal_periode on ct_kodebrg.partcustoms = ct_brg_in_awal_periode.partcustoms
left outer join ct_brg_out_awal_periode on ct_kodebrg.partcustoms = ct_brg_out_awal_periode.partcustoms
left outer join cte_inject_in_rm on ct_kodebrg.kodebarang = cte_inject_in_rm.kodebarang
left outer join cte_inject_out_rm on ct_kodebrg.kodebarang = cte_inject_out_rm.kodebarang
left outer join cte_inject_in_rm_periode on ct_kodebrg.kodebarang = cte_inject_in_rm_periode.kodebarang
left outer join cte_inject_out_rm_periode on ct_kodebrg.kodebarang = cte_inject_out_rm_periode.kodebarang
left outer join cte_stock_opname_awal on ct_kodebrg.partcustoms = cte_stock_opname_awal.partcustoms
left outer join cte_stock_opname_akhir on ct_kodebrg.partcustoms = cte_stock_opname_akhir.partcustoms
left outer join cte_stock_opname_count on ct_kodebrg.partcustoms = cte_stock_opname_count.partcustoms
left outer join cte_stock_opname_tgl_akhir on ct_kodebrg.partcustoms = cte_stock_opname_tgl_akhir.partcustoms
return
end
GO
GO
CREATE FUNCTION dbo.f_mutasi_bbx
(
@tgstart date, @tgend date, @tipebahan varchar(50)
)
RETURNS @mutasibb table (
kodebarang varchar(100) null
,namabarang varchar(100) null
,qty_saldoawal float null
,qty_in float null
,qty_out float null
,saldo_akhir float null
,unit varchar(100) null
,stock_opname float null
,selisih float null
,adj float null
)
AS
begin
declare @datesaldoawal date
declare @stsstockopname int, @dtsto date
set @datesaldoawal=(select top 1 date_stock from dbo.tbl_stock_awal where typebahan='RM' order by date_stock desc)
set @stsstockopname = 0
if (
select count(*) from (
select count(*) as cnt from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] <= @tgstart and b.typebahan = 'RM'
) and a.typebahan = 'RM'
group by part_name_customs
) as x
) > 0
begin
set @stsstockopname = 1
set @dtsto = (select max(dt) from (
select [date_stock_opname] as dt from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] <= @tgstart and b.typebahan = 'RM'
) and a.typebahan = 'RM'
group by part_name_customs, [date_stock_opname]
) as x)
set @datesaldoawal=dateadd(day, 1, @dtsto)
end
;with
ct_kodebrg (kodebarang, partcustoms, satuan) as
(
select kode_barang, nama_barang, satuan from tbl_kode_barang
) ,
ct_saldo_awal
(kodebarang, partcustoms, qty) as (
select b.kode_barang,b.part_name_customs, sum(a.qty) from dbo.saldoawal(@tgstart,@tgend,'RM') a
inner join tbl_data_part_hs b on replace(a.part_no, char(32),'')=b.part_no2
group by b.kode_barang,b.part_name_customs
),
ct_brg_in(
partcustoms, qty
) as (
select part_name_customs,
sum(qty_in * isnull(pengali, 1)) --sum(qty_in)
from [tbl_data_in_dtl]
inner join tbl_data_in_hdr on [tbl_data_in_dtl].id_data_in_hdr=tbl_data_in_hdr.id_data_in_hdr
where type_brg = 1 and tbl_data_in_hdr.doc_in_date between @tgstart and @tgend
group by part_name_customs
),
ct_brg_out(
partcustoms, qty
) as (
select [part_name_customs], sum(qty) from
( select [part_name_customs], sum([qty] * isnull(pengali, 1)) as qty
FROM tbl_data_out_dtl INNER JOIN
tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr
where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
and [tbl_data_out_dtl].out_date between @tgstart and @tgend
group by [part_name_customs], [unit_out]
) x group by [part_name_customs]
),
ct_brg_in_awal_periode(
partcustoms, qty
) as (
select part_name_customs,
sum(qty_in * isnull(pengali, 1)) --sum(qty_in)
from [tbl_data_in_dtl]
inner join tbl_data_in_hdr on [tbl_data_in_dtl].id_data_in_hdr=tbl_data_in_hdr.id_data_in_hdr
where type_brg = 1 and tbl_data_in_hdr.doc_in_date between @datesaldoawal and dateadd(day, -1, @tgstart)
group by part_name_customs
),
ct_brg_out_awal_periode(
partcustoms, qty
) as (
select [part_name_customs], sum(qty) from
( select [part_name_customs], sum([qty] * isnull(pengali, 1)) as qty
FROM tbl_data_out_dtl INNER JOIN
tbl_data_out_hdr ON tbl_data_out_dtl.id_data_out_hdr = tbl_data_out_hdr.id_data_out_hdr
where [type_items] = 'RM' and tbl_data_out_hdr.approve_date is not null and (doc_out_to_id <> 6)
and [tbl_data_out_dtl].out_date between @datesaldoawal and dateadd(day, -1, @tgstart)
group by [part_name_customs], [unit_out]
) x group by [part_name_customs]
),
cte_inject_in_rm(
kodebarang, qty
) as
(
select [kode_barang], sum([qty]) from [dbo].[tbl_inject_rm_in]
where tgl between @tgstart and @tgend
group by [kode_barang]
),
cte_inject_in_rm_periode(
kodebarang, qty
) as
(
select [kode_barang], sum([qty]) from [dbo].[tbl_inject_rm_in]
where tgl between @datesaldoawal and dateadd(day, -1, @tgstart)
group by [kode_barang]
),
cte_inject_out_rm(
kodebarang, qty
) as
(
select [kode_barang], sum([qty]) from [dbo].[tbl_inject_rm_out]
where tgl between @tgstart and @tgend
group by [kode_barang]
),
cte_inject_out_rm_periode(
kodebarang, qty
) as
(
select [kode_barang], sum([qty]) from [dbo].[tbl_inject_rm_out]
where tgl between @datesaldoawal and dateadd(day, -1, @tgstart)
group by [kode_barang]
),
cte_stock_opname_awal(
partcustoms, qty
) as (
select part_name_customs, sum(qty) from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] < @tgstart
) and a.typebahan = 'RM'
group by part_name_customs
),
cte_stock_opname_akhir(
partcustoms, qty
) as (
select part_name_customs, sum(qty) from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] <= @tgend
) and a.typebahan = 'RM'
group by part_name_customs
),
cte_stock_opname_count(
partcustoms, cnt, [date_stock_opname]
) as (
select part_name_customs, count(*) AS cnt, [date_stock_opname] from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] < @tgstart
) and a.typebahan = 'RM'
group by part_name_customs, [date_stock_opname]
),
cte_stock_opname_tgl_akhir(
partcustoms, qty
) as (
select part_name_customs, sum(qty) from [dbo].[tbl_stock_opname] a
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] = @tgend
) and a.typebahan = 'RM'
group by part_name_customs
)
insert into @mutasibb (
kodebarang
,namabarang
,qty_saldoawal
,qty_in
,qty_out
,saldo_akhir
,unit
,stock_opname
,selisih
,adj
)
select ct_kodebrg.kodebarang, ct_kodebrg.partcustoms,
case when isnull(cte_stock_opname_count.cnt,0) = 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(cte_stock_opname_awal.qty,0)
else
--((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) ))
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
end
end as qty_saldoawal,
isnull(ct_brg_in.qty,0) + isnull(cte_inject_in_rm.qty, 0) as qty_in,
isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0) as qty_out,
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(cte_stock_opname_awal.qty,0)
else
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
) as saldo_akhir
,ct_kodebrg.satuan as unit
,isnull(cte_stock_opname_akhir.qty,0) as sto
,case when cte_stock_opname_tgl_akhir.qty is null then 0 else
cte_stock_opname_tgl_akhir.qty -
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(cte_stock_opname_awal.qty,0)
else
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
)
end as selisih
,case when cte_stock_opname_tgl_akhir.qty is null then 0 else
cte_stock_opname_tgl_akhir.qty -
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(cte_stock_opname_awal.qty,0)
else
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(cte_inject_in_rm_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) + isnull(cte_inject_out_rm_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(cte_inject_in_rm.qty,0) )) - (isnull(ct_brg_out.qty,0) + isnull(cte_inject_out_rm.qty, 0)))
)
end
from ct_kodebrg
left outer join ct_saldo_awal on ct_kodebrg.partcustoms = ct_saldo_awal.partcustoms
left outer join ct_brg_in on ct_kodebrg.partcustoms = ct_brg_in.partcustoms
left outer join ct_brg_out on ct_kodebrg.partcustoms = ct_brg_out.partcustoms
left outer join ct_brg_in_awal_periode on ct_kodebrg.partcustoms = ct_brg_in_awal_periode.partcustoms
left outer join ct_brg_out_awal_periode on ct_kodebrg.partcustoms = ct_brg_out_awal_periode.partcustoms
left outer join cte_inject_in_rm on ct_kodebrg.kodebarang = cte_inject_in_rm.kodebarang
left outer join cte_inject_out_rm on ct_kodebrg.kodebarang = cte_inject_out_rm.kodebarang
left outer join cte_inject_in_rm_periode on ct_kodebrg.kodebarang = cte_inject_in_rm_periode.kodebarang
left outer join cte_inject_out_rm_periode on ct_kodebrg.kodebarang = cte_inject_out_rm_periode.kodebarang
left outer join cte_stock_opname_awal on ct_kodebrg.partcustoms = cte_stock_opname_awal.partcustoms
left outer join cte_stock_opname_akhir on ct_kodebrg.partcustoms = cte_stock_opname_akhir.partcustoms
left outer join cte_stock_opname_count on ct_kodebrg.partcustoms = cte_stock_opname_count.partcustoms
left outer join cte_stock_opname_tgl_akhir on ct_kodebrg.partcustoms = cte_stock_opname_tgl_akhir.partcustoms
return
end
GO
Depends On
11Used By
No items found