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_bb_old
(
@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
declare @tgstartx date
if (select count(*) from [tbl_stock_opname] where typebahan = 'RM' and [date_stock_opname] between @tgstart and @tgend) > 0
begin
set @tgstartx = (
select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'RM' and [date_stock_opname] between @tgstart and @tgend
)
if (@tgend <> @tgstartx)
begin
set @tgstart = dateadd(day, 1, @tgstartx)
end
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) from (
-- SELECT tbl_data_part_hs.part_name_customs,SUM(ISNULL(tbl_data_in_dtl.qty_in, 0) * ISNULL(tbl_data_in_dtl.pengali, 1)) AS qty
--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 RIGHT OUTER JOIN
-- tbl_data_part_hs ON tbl_data_in_dtl.part_number = tbl_data_part_hs.part_no LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where 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),
--tbl_data_part_hs_article.part_no,
--tbl_data_in_dtl.part_number,
-- tbl_data_part_hs.part_name_customs
-- ) x group by part_name_customs
SELECT v_data_part_hs.part_name_customs AS Expr1, SUM(ISNULL([v_data_in_dtl_part_articel].qty_in, 0) * ISNULL([v_data_in_dtl_part_articel].pengali, 1)) AS Expr2
FROM [v_data_in_dtl_part_articel] LEFT OUTER JOIN
v_data_part_hs ON [v_data_in_dtl_part_articel].[part_no_article] = v_data_part_hs.part_no
WHERE ([v_data_in_dtl_part_articel].doc_in_date BETWEEN @tgstart and @tgend )
GROUP BY v_data_part_hs.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]
--) x group by [part_name_customs]
select part_name_customs, sum(qty) from
( SELECT tbl_data_part_hs.part_name_customs, SUM((tbl_data_out_dtl.qty * ISNULL(tbl_data_out_dtl.pengali, 1)) - ISNULL(qty_return, 0)) 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 RIGHT OUTER JOIN
tbl_data_part_hs ON tbl_data_out_dtl.part_number = tbl_data_part_hs.part_no
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 tbl_data_part_hs.part_name_customs
) x group by part_name_customs
),
ct_brg_in_awal_periode(
partcustoms, qty
) as (
--select part_name_customs, sum(qty) from (
-- SELECT tbl_data_part_hs.part_name_customs,SUM(ISNULL(tbl_data_in_dtl.qty_in, 0) * ISNULL(tbl_data_in_dtl.pengali, 1)) AS qty
--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 RIGHT OUTER JOIN
-- tbl_data_part_hs ON tbl_data_in_dtl.part_number = tbl_data_part_hs.part_no LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where 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),
--tbl_data_part_hs_article.part_no,
--tbl_data_in_dtl.part_number,
-- tbl_data_part_hs.part_name_customs
-- ) x group by part_name_customs
SELECT v_data_part_hs.part_name_customs AS Expr1, SUM(ISNULL([v_data_in_dtl_part_articel].qty_in, 0) * ISNULL([v_data_in_dtl_part_articel].pengali, 1)) AS Expr2
FROM [v_data_in_dtl_part_articel] LEFT OUTER JOIN
v_data_part_hs ON [v_data_in_dtl_part_articel].[part_no_article] = v_data_part_hs.part_no
WHERE ([v_data_in_dtl_part_articel].doc_in_date BETWEEN @datesaldoawal and dateadd(day, -1, @tgstart) )
GROUP BY v_data_part_hs.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]
--) x group by [part_name_customs]
--) x group by [part_name_customs]
select part_name_customs, sum(qty) from
( SELECT tbl_data_part_hs.part_name_customs, SUM((tbl_data_out_dtl.qty * ISNULL(tbl_data_out_dtl.pengali, 1)) - ISNULL(qty_return, 0)) 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 RIGHT OUTER JOIN
tbl_data_part_hs ON tbl_data_out_dtl.part_number = tbl_data_part_hs.part_no
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 tbl_data_part_hs.part_name_customs
) 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 tbl_data_part_hs.part_name_customs, SUM(a.qty) AS Expr1
FROM tbl_stock_opname AS a RIGHT OUTER JOIN
tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no
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 tbl_data_part_hs.part_name_customs
),
cte_stock_opname_akhir(
partcustoms, qty
) as (
SELECT tbl_data_part_hs.part_name_customs, SUM(a.qty) AS Expr1
FROM tbl_stock_opname AS a RIGHT OUTER JOIN
tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] <= @tgend and b.typebahan = 'RM'
) and a.typebahan = 'RM'
group by tbl_data_part_hs.part_name_customs
),
cte_stock_opname_count(
partcustoms, cnt, [date_stock_opname]
) as (
SELECT tbl_data_part_hs.part_name_customs, COUNT(*) AS cnt, a.date_stock_opname
FROM tbl_stock_opname AS a RIGHT OUTER JOIN
tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no
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 tbl_data_part_hs.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 b.typebahan = 'RM'
) 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(ct_brg_out_awal_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 (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(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_bb_old
(
@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
declare @tgstartx date
if (select count(*) from [tbl_stock_opname] where typebahan = 'RM' and [date_stock_opname] between @tgstart and @tgend) > 0
begin
set @tgstartx = (
select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'RM' and [date_stock_opname] between @tgstart and @tgend
)
if (@tgend <> @tgstartx)
begin
set @tgstart = dateadd(day, 1, @tgstartx)
end
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) from (
-- SELECT tbl_data_part_hs.part_name_customs,SUM(ISNULL(tbl_data_in_dtl.qty_in, 0) * ISNULL(tbl_data_in_dtl.pengali, 1)) AS qty
--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 RIGHT OUTER JOIN
-- tbl_data_part_hs ON tbl_data_in_dtl.part_number = tbl_data_part_hs.part_no LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where 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),
--tbl_data_part_hs_article.part_no,
--tbl_data_in_dtl.part_number,
-- tbl_data_part_hs.part_name_customs
-- ) x group by part_name_customs
SELECT v_data_part_hs.part_name_customs AS Expr1, SUM(ISNULL([v_data_in_dtl_part_articel].qty_in, 0) * ISNULL([v_data_in_dtl_part_articel].pengali, 1)) AS Expr2
FROM [v_data_in_dtl_part_articel] LEFT OUTER JOIN
v_data_part_hs ON [v_data_in_dtl_part_articel].[part_no_article] = v_data_part_hs.part_no
WHERE ([v_data_in_dtl_part_articel].doc_in_date BETWEEN @tgstart and @tgend )
GROUP BY v_data_part_hs.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]
--) x group by [part_name_customs]
select part_name_customs, sum(qty) from
( SELECT tbl_data_part_hs.part_name_customs, SUM((tbl_data_out_dtl.qty * ISNULL(tbl_data_out_dtl.pengali, 1)) - ISNULL(qty_return, 0)) 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 RIGHT OUTER JOIN
tbl_data_part_hs ON tbl_data_out_dtl.part_number = tbl_data_part_hs.part_no
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 tbl_data_part_hs.part_name_customs
) x group by part_name_customs
),
ct_brg_in_awal_periode(
partcustoms, qty
) as (
--select part_name_customs, sum(qty) from (
-- SELECT tbl_data_part_hs.part_name_customs,SUM(ISNULL(tbl_data_in_dtl.qty_in, 0) * ISNULL(tbl_data_in_dtl.pengali, 1)) AS qty
--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 RIGHT OUTER JOIN
-- tbl_data_part_hs ON tbl_data_in_dtl.part_number = tbl_data_part_hs.part_no LEFT OUTER JOIN
-- tbl_data_part_hs_article ON tbl_data_in_dtl.part_number = tbl_data_part_hs_article.article_code
-- where 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),
--tbl_data_part_hs_article.part_no,
--tbl_data_in_dtl.part_number,
-- tbl_data_part_hs.part_name_customs
-- ) x group by part_name_customs
SELECT v_data_part_hs.part_name_customs AS Expr1, SUM(ISNULL([v_data_in_dtl_part_articel].qty_in, 0) * ISNULL([v_data_in_dtl_part_articel].pengali, 1)) AS Expr2
FROM [v_data_in_dtl_part_articel] LEFT OUTER JOIN
v_data_part_hs ON [v_data_in_dtl_part_articel].[part_no_article] = v_data_part_hs.part_no
WHERE ([v_data_in_dtl_part_articel].doc_in_date BETWEEN @datesaldoawal and dateadd(day, -1, @tgstart) )
GROUP BY v_data_part_hs.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]
--) x group by [part_name_customs]
--) x group by [part_name_customs]
select part_name_customs, sum(qty) from
( SELECT tbl_data_part_hs.part_name_customs, SUM((tbl_data_out_dtl.qty * ISNULL(tbl_data_out_dtl.pengali, 1)) - ISNULL(qty_return, 0)) 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 RIGHT OUTER JOIN
tbl_data_part_hs ON tbl_data_out_dtl.part_number = tbl_data_part_hs.part_no
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 tbl_data_part_hs.part_name_customs
) 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 tbl_data_part_hs.part_name_customs, SUM(a.qty) AS Expr1
FROM tbl_stock_opname AS a RIGHT OUTER JOIN
tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no
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 tbl_data_part_hs.part_name_customs
),
cte_stock_opname_akhir(
partcustoms, qty
) as (
SELECT tbl_data_part_hs.part_name_customs, SUM(a.qty) AS Expr1
FROM tbl_stock_opname AS a RIGHT OUTER JOIN
tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no
where a.[date_stock_opname] = (select max([date_stock_opname]) from [dbo].[tbl_stock_opname] b
where b.[date_stock_opname] <= @tgend and b.typebahan = 'RM'
) and a.typebahan = 'RM'
group by tbl_data_part_hs.part_name_customs
),
cte_stock_opname_count(
partcustoms, cnt, [date_stock_opname]
) as (
SELECT tbl_data_part_hs.part_name_customs, COUNT(*) AS cnt, a.date_stock_opname
FROM tbl_stock_opname AS a RIGHT OUTER JOIN
tbl_data_part_hs ON a.part_no = tbl_data_part_hs.part_no
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 tbl_data_part_hs.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 b.typebahan = 'RM'
) 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(ct_brg_out_awal_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 (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(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