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 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create FUNCTION dbo.f_mutasi_bb_per_partx_retur
(
@tgstart date, @tgend date
)
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
,sto int
)
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_saldo_awal
(part_no, part_name_customs, qty) as (
SELECT
tbl_data_part_hs.part_no,
tbl_data_part_hs.part_name_customs,
isnull(tbl_stock_awal.qty, 0)
FROM tbl_data_part_hs LEFT OUTER JOIN
tbl_stock_awal ON tbl_data_part_hs.part_no = tbl_stock_awal.part_no
),
--ct_brg_in(
-- part_no, qty
--) as (
--select
-- isnull(nullif(tbl_data_part_hs_article.part_no,''),tbl_data_in_dtl.part_number)
-- ,sum(isnull(tbl_data_in_dtl.qty_in, 0) * isnull(tbl_data_in_dtl.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 LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where tbl_data_in_dtl.type_brg = 1 and tbl_data_in_hdr.doc_in_date between @tgstart and @tgend
--group by isnull(nullif(tbl_data_part_hs_article.part_no,''),tbl_data_in_dtl.part_number)
--),
ct_brg_in(
part_no, qty
) as (
select
[part_no_article]
,sum(isnull([v_data_in_dtl_part_articel].qty_in, 0) * isnull([v_data_in_dtl_part_articel].pengali, 1)) --sum(qty_in)
-- + (select sum(isnull(qty_return, 0)) from tbl_data_out_dtl where status_return = 'RETURNPRODUKSI' and tgl_return between @tgstart and @tgend and part_number = [v_data_in_dtl_part_articel].[part_no_article])
FROM [v_data_in_dtl_part_articel]
where [v_data_in_dtl_part_articel].doc_in_date between @tgstart and @tgend
group by [part_no_article]
),
ct_in_retur( part_no, qty) as (
select part_number, sum(isnull(qty_return, 0)) from tbl_data_out_dtl where status_return = 'RETURNPRODUKSI' and tgl_return between @tgstart and @tgend
group by part_number
),
ct_brg_out(
part_no, qty
) as (
select part_number, sum(qty) from
( select part_number, 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_number
) x group by part_number
),
--ct_brg_in_awal_periode(
-- part_no, qty
--) as (
--select
-- isnull(nullif(tbl_data_part_hs_article.part_no,''),tbl_data_in_dtl.part_number)
-- ,sum(isnull(tbl_data_in_dtl.qty_in, 0) * isnull(tbl_data_in_dtl.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 LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where tbl_data_in_dtl.type_brg = 1 and tbl_data_in_hdr.doc_in_date between @datesaldoawal and dateadd(day, -1, @tgstart)
--group by isnull(nullif(tbl_data_part_hs_article.part_no,''),tbl_data_in_dtl.part_number)
--),
ct_brg_in_awal_periode(
part_no, qty
) as (
select
[part_no_article]
,sum(isnull([v_data_in_dtl_part_articel].qty_in, 0) * isnull([v_data_in_dtl_part_articel].pengali, 1))
--+ (select sum(isnull(qty_return, 0)) from tbl_data_out_dtl where status_return = 'RETURNPRODUKSI' and tgl_return between @datesaldoawal and dateadd(day, -1, @tgstart ) and part_number = [v_data_in_dtl_part_articel].[part_no_article])
FROM [v_data_in_dtl_part_articel]
where [v_data_in_dtl_part_articel].doc_in_date between @datesaldoawal and dateadd(day, -1, @tgstart)
group by [part_no_article]
),
ct_in_retur_awal_periode( part_no, qty) as (
select part_number, sum(isnull(qty_return, 0)) from tbl_data_out_dtl where status_return = 'RETURNPRODUKSI' and tgl_return between @datesaldoawal and dateadd(day, -1, @tgstart)
group by part_number
),
ct_brg_out_awal_periode(
part_no, qty
) as (
select part_number, sum(qty) from
( select part_number, 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_number
) x group by part_number
),
cte_stock_opname_awal(
part_no, qty
) as (
select part_no, 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] = @dtsto
) and a.typebahan = 'RM'
group by part_no
),
cte_stock_opname_akhir(
part_no, qty
) as (
select part_no, 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_no
),
cte_stock_opname_count(
part_no, cnt, [date_stock_opname]
) as (
select part_no, 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 b.typebahan = 'RM'
) and a.typebahan = 'RM'
group by part_no, [date_stock_opname]
),
cte_stock_opname_tgl_akhir(
part_no, qty
) as (
select part_no, 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_no
)
insert into @mutasibb (
kodebarang
,namabarang
,qty_saldoawal
,qty_in
,qty_out
,saldo_akhir
,unit
,stock_opname
,selisih
,adj
,sto
)
select ct_saldo_awal.part_no, ct_saldo_awal.part_name_customs
,case when @stsstockopname = 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(ct_in_retur_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_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(ct_in_retur_awal_periode.qty,0) ) - (isnull(ct_brg_out_awal_periode.qty,0) )))
end
end
,isnull(ct_brg_in.qty,0) + isnull(ct_in_retur.qty,0) as qty_in
,isnull(ct_brg_out.qty,0) as qty_out
,(case when @stsstockopname = 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) )))
else
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + (isnull(ct_in_retur_awal_periode.qty,0)) ) - (isnull(ct_brg_out_awal_periode.qty,0) )))
end) + isnull(ct_brg_in.qty,0) + isnull(ct_in_retur.qty,0) - isnull(ct_brg_out.qty,0)
,'-' as unit
,case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'RM') = 0 then 0 else isnull(cte_stock_opname_tgl_akhir.qty,0) end 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(ct_in_retur_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_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(ct_in_retur_awal_periode.qty,0) ) - (isnull(ct_brg_out_awal_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(ct_in_retur.qty,0) )) - (isnull(ct_brg_out.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(ct_in_retur_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_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(ct_in_retur_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(ct_in_retur.qty,0) )) - (isnull(ct_brg_out.qty,0) ))
)
end
,case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'RM') = 0 then 0 else 1 end as sto
from ct_saldo_awal
left outer join ct_brg_in on ct_saldo_awal.part_no = ct_brg_in.part_no
left outer join ct_brg_out on ct_saldo_awal.part_no = ct_brg_out.part_no
left outer join ct_brg_in_awal_periode on ct_saldo_awal.part_no = ct_brg_in_awal_periode.part_no
left outer join ct_brg_out_awal_periode on ct_saldo_awal.part_no = ct_brg_out_awal_periode.part_no
left outer join cte_stock_opname_awal on ct_saldo_awal.part_no = cte_stock_opname_awal.part_no
left outer join cte_stock_opname_akhir on ct_saldo_awal.part_no = cte_stock_opname_akhir.part_no
left outer join cte_stock_opname_count on ct_saldo_awal.part_no = cte_stock_opname_count.part_no
left outer join cte_stock_opname_tgl_akhir on ct_saldo_awal.part_no = cte_stock_opname_tgl_akhir.part_no
left outer join ct_in_retur on ct_saldo_awal.part_no = ct_in_retur.part_no
left outer join ct_in_retur_awal_periode on ct_saldo_awal.part_no = ct_in_retur_awal_periode.part_no
return
end
GO
GO
create FUNCTION dbo.f_mutasi_bb_per_partx_retur
(
@tgstart date, @tgend date
)
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
,sto int
)
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_saldo_awal
(part_no, part_name_customs, qty) as (
SELECT
tbl_data_part_hs.part_no,
tbl_data_part_hs.part_name_customs,
isnull(tbl_stock_awal.qty, 0)
FROM tbl_data_part_hs LEFT OUTER JOIN
tbl_stock_awal ON tbl_data_part_hs.part_no = tbl_stock_awal.part_no
),
--ct_brg_in(
-- part_no, qty
--) as (
--select
-- isnull(nullif(tbl_data_part_hs_article.part_no,''),tbl_data_in_dtl.part_number)
-- ,sum(isnull(tbl_data_in_dtl.qty_in, 0) * isnull(tbl_data_in_dtl.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 LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where tbl_data_in_dtl.type_brg = 1 and tbl_data_in_hdr.doc_in_date between @tgstart and @tgend
--group by isnull(nullif(tbl_data_part_hs_article.part_no,''),tbl_data_in_dtl.part_number)
--),
ct_brg_in(
part_no, qty
) as (
select
[part_no_article]
,sum(isnull([v_data_in_dtl_part_articel].qty_in, 0) * isnull([v_data_in_dtl_part_articel].pengali, 1)) --sum(qty_in)
-- + (select sum(isnull(qty_return, 0)) from tbl_data_out_dtl where status_return = 'RETURNPRODUKSI' and tgl_return between @tgstart and @tgend and part_number = [v_data_in_dtl_part_articel].[part_no_article])
FROM [v_data_in_dtl_part_articel]
where [v_data_in_dtl_part_articel].doc_in_date between @tgstart and @tgend
group by [part_no_article]
),
ct_in_retur( part_no, qty) as (
select part_number, sum(isnull(qty_return, 0)) from tbl_data_out_dtl where status_return = 'RETURNPRODUKSI' and tgl_return between @tgstart and @tgend
group by part_number
),
ct_brg_out(
part_no, qty
) as (
select part_number, sum(qty) from
( select part_number, 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_number
) x group by part_number
),
--ct_brg_in_awal_periode(
-- part_no, qty
--) as (
--select
-- isnull(nullif(tbl_data_part_hs_article.part_no,''),tbl_data_in_dtl.part_number)
-- ,sum(isnull(tbl_data_in_dtl.qty_in, 0) * isnull(tbl_data_in_dtl.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 LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where tbl_data_in_dtl.type_brg = 1 and tbl_data_in_hdr.doc_in_date between @datesaldoawal and dateadd(day, -1, @tgstart)
--group by isnull(nullif(tbl_data_part_hs_article.part_no,''),tbl_data_in_dtl.part_number)
--),
ct_brg_in_awal_periode(
part_no, qty
) as (
select
[part_no_article]
,sum(isnull([v_data_in_dtl_part_articel].qty_in, 0) * isnull([v_data_in_dtl_part_articel].pengali, 1))
--+ (select sum(isnull(qty_return, 0)) from tbl_data_out_dtl where status_return = 'RETURNPRODUKSI' and tgl_return between @datesaldoawal and dateadd(day, -1, @tgstart ) and part_number = [v_data_in_dtl_part_articel].[part_no_article])
FROM [v_data_in_dtl_part_articel]
where [v_data_in_dtl_part_articel].doc_in_date between @datesaldoawal and dateadd(day, -1, @tgstart)
group by [part_no_article]
),
ct_in_retur_awal_periode( part_no, qty) as (
select part_number, sum(isnull(qty_return, 0)) from tbl_data_out_dtl where status_return = 'RETURNPRODUKSI' and tgl_return between @datesaldoawal and dateadd(day, -1, @tgstart)
group by part_number
),
ct_brg_out_awal_periode(
part_no, qty
) as (
select part_number, sum(qty) from
( select part_number, 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_number
) x group by part_number
),
cte_stock_opname_awal(
part_no, qty
) as (
select part_no, 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] = @dtsto
) and a.typebahan = 'RM'
group by part_no
),
cte_stock_opname_akhir(
part_no, qty
) as (
select part_no, 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_no
),
cte_stock_opname_count(
part_no, cnt, [date_stock_opname]
) as (
select part_no, 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 b.typebahan = 'RM'
) and a.typebahan = 'RM'
group by part_no, [date_stock_opname]
),
cte_stock_opname_tgl_akhir(
part_no, qty
) as (
select part_no, 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_no
)
insert into @mutasibb (
kodebarang
,namabarang
,qty_saldoawal
,qty_in
,qty_out
,saldo_akhir
,unit
,stock_opname
,selisih
,adj
,sto
)
select ct_saldo_awal.part_no, ct_saldo_awal.part_name_customs
,case when @stsstockopname = 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + isnull(ct_in_retur_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_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(ct_in_retur_awal_periode.qty,0) ) - (isnull(ct_brg_out_awal_periode.qty,0) )))
end
end
,isnull(ct_brg_in.qty,0) + isnull(ct_in_retur.qty,0) as qty_in
,isnull(ct_brg_out.qty,0) as qty_out
,(case when @stsstockopname = 0 then
(isnull(ct_saldo_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) )))
else
(isnull(cte_stock_opname_awal.qty,0) + ((isnull(ct_brg_in_awal_periode.qty,0) + (isnull(ct_in_retur_awal_periode.qty,0)) ) - (isnull(ct_brg_out_awal_periode.qty,0) )))
end) + isnull(ct_brg_in.qty,0) + isnull(ct_in_retur.qty,0) - isnull(ct_brg_out.qty,0)
,'-' as unit
,case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'RM') = 0 then 0 else isnull(cte_stock_opname_tgl_akhir.qty,0) end 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(ct_in_retur_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_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(ct_in_retur_awal_periode.qty,0) ) - (isnull(ct_brg_out_awal_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(ct_in_retur.qty,0) )) - (isnull(ct_brg_out.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(ct_in_retur_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_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(ct_in_retur_awal_periode.qty,0)) - (isnull(ct_brg_out_awal_periode.qty,0) )))
end
end
+
((isnull(ct_brg_in.qty,0) + isnull(ct_in_retur.qty,0) )) - (isnull(ct_brg_out.qty,0) ))
)
end
,case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'RM') = 0 then 0 else 1 end as sto
from ct_saldo_awal
left outer join ct_brg_in on ct_saldo_awal.part_no = ct_brg_in.part_no
left outer join ct_brg_out on ct_saldo_awal.part_no = ct_brg_out.part_no
left outer join ct_brg_in_awal_periode on ct_saldo_awal.part_no = ct_brg_in_awal_periode.part_no
left outer join ct_brg_out_awal_periode on ct_saldo_awal.part_no = ct_brg_out_awal_periode.part_no
left outer join cte_stock_opname_awal on ct_saldo_awal.part_no = cte_stock_opname_awal.part_no
left outer join cte_stock_opname_akhir on ct_saldo_awal.part_no = cte_stock_opname_akhir.part_no
left outer join cte_stock_opname_count on ct_saldo_awal.part_no = cte_stock_opname_count.part_no
left outer join cte_stock_opname_tgl_akhir on ct_saldo_awal.part_no = cte_stock_opname_tgl_akhir.part_no
left outer join ct_in_retur on ct_saldo_awal.part_no = ct_in_retur.part_no
left outer join ct_in_retur_awal_periode on ct_saldo_awal.part_no = ct_in_retur_awal_periode.part_no
return
end
GO
Depends On
6Used By
No items found