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_scr
(
@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
,adj float null
)
AS
begin
declare @datesta date
declare @stsstockopname int, @dtsto date
set @datesta=(select top 1 date_scrap_sta from tbl_scrap_sta group by date_scrap_sta order by date_scrap_sta 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 = 'SS'
) and a.typebahan = 'SS'
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 = 'SS'
) and a.typebahan = 'SS'
group by part_name_customs, [date_stock_opname]
) as x)
set @datesta=dateadd(day, 1, @dtsto)
end
--if (select count(*) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend) > 0
--begin
-- set @tgstart = (
-- select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend
-- )
--end
declare @tgstartx date
if (select count(*) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend) > 0
begin
set @tgstartx = (
select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend
)
if (@tgend <> @tgstartx)
begin
set @tgstart = dateadd(day, 1, @tgstartx)
end
end
-- declare @tgmax date
--if (select count(*) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend) > 0
--begin
-- set @tgmax = (select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend)
-- if @tgend <> @tgmax
-- begin
-- set @tgstart = (
-- select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend
-- )
-- 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 a.typebahan = 'SS'
-- group by part_name_customs, [date_stock_opname]
-- ) as x)
-- set @datesta=dateadd(day, 1, @dtsto)
-- end
--end
;with
ct_kodebrg (kodebarang, namabarang) as
(
select
part_no
,part_name
from dbo.tbl_scrap_data
) ,
ct_saldo_awal
(kodebarang, qty, unit) as (
select part_no, sum(qty), unit from dbo.tbl_scrap_sta where date_scrap_sta=@datesta
group by part_no,unit
),
ct_brg_in(
kodebarang, qty, unit
) as (
select part_no, sum(qty), unit from tbl_scrap
where date_scrap between @tgstart and @tgend
group by part_no, unit
),
ct_brg_out(
kodebarang, qty, unit
) as (
select part_no, sum(qty), unit from dbo.tbl_scrap_out
where date_scrap between @tgstart and @tgend
group by part_no, unit
) ,
ct_brg_in_periode(
kodebarang, qty, unit
) as (
select part_no, sum(qty), unit from tbl_scrap
where date_scrap between @datesta and dateadd(day,-1,@tgstart)
group by part_no, unit
),
ct_brg_out_periode(
kodebarang, qty, unit
) as (
select part_no, sum(qty), unit from dbo.tbl_scrap_out
where date_scrap between @datesta and dateadd(day,-1,@tgstart)
group by part_no, unit
) ,
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] < @tgstart and b.typebahan = 'SS'
) and a.typebahan = 'SS'
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 b.typebahan = 'SS'
) and a.typebahan = 'SS'
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 = 'SS'
) and a.typebahan = 'SS'
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 b.typebahan = 'SS'
) and a.typebahan = 'SS'
group by part_no
)
insert into @mutasibb (
kodebarang
,namabarang
,unit
,qty_saldoawal
,qty_in
,qty_out
,saldo_akhir
,stock_opname
,adj
)
select ct_kodebrg.kodebarang, ct_kodebrg.namabarang,isnull((case when ct_brg_in.unit is not null then ct_brg_in.unit when ct_brg_out.unit is not null then ct_brg_out.unit when ct_saldo_awal.unit IS not null then ct_saldo_awal.unit end ),'KGM') as unit
--,(sum(isnull(ct_saldo_awal.qty, 0)) + ( sum(isnull(ct_brg_in_periode.qty,0)) - sum(isnull(ct_brg_out_periode.qty,0)) )) as qty_sta --,sum(isnull(ct_saldo_awal.qty, 0)) as qty_sta
,case when isnull(cte_stock_opname_count.cnt,0) = 0 then
(isnull(sum(ct_saldo_awal.qty),0) + (sum(isnull(ct_brg_in_periode.qty,0))) - (sum(isnull(ct_brg_out_periode.qty,0)) ))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(sum(cte_stock_opname_awal.qty),0)
else
(isnull(sum(cte_stock_opname_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - (isnull(sum(ct_brg_out_periode.qty),0) )))
end
end as qty_sta
,sum(isnull(ct_brg_in.qty, 0)) as qty_in
,sum(isnull(ct_brg_out.qty, 0)) as qty_out
,
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(sum(ct_saldo_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - isnull(sum(ct_brg_out_periode.qty),0) ))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(sum(cte_stock_opname_awal.qty),0)
else
(isnull(sum(cte_stock_opname_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - (isnull(sum(ct_brg_out_periode.qty),0) )))
end
end
+
((isnull(sum(ct_brg_in.qty),0))) - (isnull(sum(ct_brg_out.qty),0)))
) as saldo_akhir
,
--isnull(sum(cte_stock_opname_akhir.qty),0) as sto
case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'SS') = 0 then 0 else isnull(sum(cte_stock_opname_tgl_akhir.qty),0) end as sto
,
case when sum(cte_stock_opname_tgl_akhir.qty) is null then 0 else
sum(cte_stock_opname_tgl_akhir.qty) -
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(sum(ct_saldo_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - isnull(sum(ct_brg_out_periode.qty),0) ))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(sum(cte_stock_opname_awal.qty),0)
else
(isnull(sum(cte_stock_opname_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - (isnull(sum(ct_brg_out_periode.qty),0) )))
end
end
+
((isnull(sum(ct_brg_in.qty),0))) - (isnull(sum(ct_brg_out.qty),0)))
)
end
--,(sum(isnull(ct_saldo_awal.qty, 0)) + (sum(isnull(ct_brg_in_periode.qty, 0)) - sum(isnull(ct_brg_out_periode.qty, 0))) + (sum(isnull(ct_brg_in.qty, 0)) - sum(isnull(ct_brg_out.qty, 0))) )
--,((sum(isnull(ct_saldo_awal.qty, 0)) + sum(isnull(ct_brg_in.qty, 0))) - sum(isnull(ct_brg_out.qty, 0))) AS saldo_akhir
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_periode on ct_kodebrg.kodebarang=ct_brg_in_periode.kodebarang
left outer join ct_brg_out_periode on ct_kodebrg.kodebarang=ct_brg_out_periode.kodebarang
left outer join cte_stock_opname_awal on ct_kodebrg.kodebarang= cte_stock_opname_awal.part_no
left outer join cte_stock_opname_akhir on ct_kodebrg.kodebarang = cte_stock_opname_akhir.part_no
left outer join cte_stock_opname_tgl_akhir on ct_kodebrg.kodebarang = cte_stock_opname_tgl_akhir.part_no
left outer join cte_stock_opname_count on ct_kodebrg.kodebarang = cte_stock_opname_count.part_no
--where ct_saldo_awal.qty <> 0 or ct_brg_in.qty <> 0 or ct_brg_out.qty <> 0
group by ct_kodebrg.kodebarang, ct_kodebrg.namabarang,ct_brg_in.unit,ct_brg_out.unit,ct_saldo_awal.unit,cte_stock_opname_count.cnt ,cte_stock_opname_count.date_stock_opname
insert into @mutasibb (
kodebarang
,namabarang
,unit
,qty_saldoawal
,qty_in
,qty_out
,saldo_akhir
,stock_opname
,adj
)
SELECT
kodebarang
, namabarang
, unit
, qty_saldoawal
, qty_in
, qty_out
, saldo_akhir
, stock_opname
, qty_penyesuaian
FROM dbo.vw_fn_ms_mutasi_mesin(@tgstart,@tgend,1) AS vw_fn_ms_mutasi_mesin_1
return
end
GO
GO
CREATE FUNCTION dbo.f_mutasi_scr
(
@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
,adj float null
)
AS
begin
declare @datesta date
declare @stsstockopname int, @dtsto date
set @datesta=(select top 1 date_scrap_sta from tbl_scrap_sta group by date_scrap_sta order by date_scrap_sta 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 = 'SS'
) and a.typebahan = 'SS'
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 = 'SS'
) and a.typebahan = 'SS'
group by part_name_customs, [date_stock_opname]
) as x)
set @datesta=dateadd(day, 1, @dtsto)
end
--if (select count(*) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend) > 0
--begin
-- set @tgstart = (
-- select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend
-- )
--end
declare @tgstartx date
if (select count(*) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend) > 0
begin
set @tgstartx = (
select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend
)
if (@tgend <> @tgstartx)
begin
set @tgstart = dateadd(day, 1, @tgstartx)
end
end
-- declare @tgmax date
--if (select count(*) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend) > 0
--begin
-- set @tgmax = (select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend)
-- if @tgend <> @tgmax
-- begin
-- set @tgstart = (
-- select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'SS' and [date_stock_opname] between @tgstart and @tgend
-- )
-- 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 a.typebahan = 'SS'
-- group by part_name_customs, [date_stock_opname]
-- ) as x)
-- set @datesta=dateadd(day, 1, @dtsto)
-- end
--end
;with
ct_kodebrg (kodebarang, namabarang) as
(
select
part_no
,part_name
from dbo.tbl_scrap_data
) ,
ct_saldo_awal
(kodebarang, qty, unit) as (
select part_no, sum(qty), unit from dbo.tbl_scrap_sta where date_scrap_sta=@datesta
group by part_no,unit
),
ct_brg_in(
kodebarang, qty, unit
) as (
select part_no, sum(qty), unit from tbl_scrap
where date_scrap between @tgstart and @tgend
group by part_no, unit
),
ct_brg_out(
kodebarang, qty, unit
) as (
select part_no, sum(qty), unit from dbo.tbl_scrap_out
where date_scrap between @tgstart and @tgend
group by part_no, unit
) ,
ct_brg_in_periode(
kodebarang, qty, unit
) as (
select part_no, sum(qty), unit from tbl_scrap
where date_scrap between @datesta and dateadd(day,-1,@tgstart)
group by part_no, unit
),
ct_brg_out_periode(
kodebarang, qty, unit
) as (
select part_no, sum(qty), unit from dbo.tbl_scrap_out
where date_scrap between @datesta and dateadd(day,-1,@tgstart)
group by part_no, unit
) ,
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] < @tgstart and b.typebahan = 'SS'
) and a.typebahan = 'SS'
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 b.typebahan = 'SS'
) and a.typebahan = 'SS'
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 = 'SS'
) and a.typebahan = 'SS'
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 b.typebahan = 'SS'
) and a.typebahan = 'SS'
group by part_no
)
insert into @mutasibb (
kodebarang
,namabarang
,unit
,qty_saldoawal
,qty_in
,qty_out
,saldo_akhir
,stock_opname
,adj
)
select ct_kodebrg.kodebarang, ct_kodebrg.namabarang,isnull((case when ct_brg_in.unit is not null then ct_brg_in.unit when ct_brg_out.unit is not null then ct_brg_out.unit when ct_saldo_awal.unit IS not null then ct_saldo_awal.unit end ),'KGM') as unit
--,(sum(isnull(ct_saldo_awal.qty, 0)) + ( sum(isnull(ct_brg_in_periode.qty,0)) - sum(isnull(ct_brg_out_periode.qty,0)) )) as qty_sta --,sum(isnull(ct_saldo_awal.qty, 0)) as qty_sta
,case when isnull(cte_stock_opname_count.cnt,0) = 0 then
(isnull(sum(ct_saldo_awal.qty),0) + (sum(isnull(ct_brg_in_periode.qty,0))) - (sum(isnull(ct_brg_out_periode.qty,0)) ))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(sum(cte_stock_opname_awal.qty),0)
else
(isnull(sum(cte_stock_opname_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - (isnull(sum(ct_brg_out_periode.qty),0) )))
end
end as qty_sta
,sum(isnull(ct_brg_in.qty, 0)) as qty_in
,sum(isnull(ct_brg_out.qty, 0)) as qty_out
,
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(sum(ct_saldo_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - isnull(sum(ct_brg_out_periode.qty),0) ))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(sum(cte_stock_opname_awal.qty),0)
else
(isnull(sum(cte_stock_opname_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - (isnull(sum(ct_brg_out_periode.qty),0) )))
end
end
+
((isnull(sum(ct_brg_in.qty),0))) - (isnull(sum(ct_brg_out.qty),0)))
) as saldo_akhir
,
--isnull(sum(cte_stock_opname_akhir.qty),0) as sto
case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'SS') = 0 then 0 else isnull(sum(cte_stock_opname_tgl_akhir.qty),0) end as sto
,
case when sum(cte_stock_opname_tgl_akhir.qty) is null then 0 else
sum(cte_stock_opname_tgl_akhir.qty) -
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(sum(ct_saldo_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - isnull(sum(ct_brg_out_periode.qty),0) ))
else
case when cte_stock_opname_count.date_stock_opname=dateadd(day, -1, @tgstart) then
isnull(sum(cte_stock_opname_awal.qty),0)
else
(isnull(sum(cte_stock_opname_awal.qty),0) + (isnull(sum(ct_brg_in_periode.qty),0) - (isnull(sum(ct_brg_out_periode.qty),0) )))
end
end
+
((isnull(sum(ct_brg_in.qty),0))) - (isnull(sum(ct_brg_out.qty),0)))
)
end
--,(sum(isnull(ct_saldo_awal.qty, 0)) + (sum(isnull(ct_brg_in_periode.qty, 0)) - sum(isnull(ct_brg_out_periode.qty, 0))) + (sum(isnull(ct_brg_in.qty, 0)) - sum(isnull(ct_brg_out.qty, 0))) )
--,((sum(isnull(ct_saldo_awal.qty, 0)) + sum(isnull(ct_brg_in.qty, 0))) - sum(isnull(ct_brg_out.qty, 0))) AS saldo_akhir
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_periode on ct_kodebrg.kodebarang=ct_brg_in_periode.kodebarang
left outer join ct_brg_out_periode on ct_kodebrg.kodebarang=ct_brg_out_periode.kodebarang
left outer join cte_stock_opname_awal on ct_kodebrg.kodebarang= cte_stock_opname_awal.part_no
left outer join cte_stock_opname_akhir on ct_kodebrg.kodebarang = cte_stock_opname_akhir.part_no
left outer join cte_stock_opname_tgl_akhir on ct_kodebrg.kodebarang = cte_stock_opname_tgl_akhir.part_no
left outer join cte_stock_opname_count on ct_kodebrg.kodebarang = cte_stock_opname_count.part_no
--where ct_saldo_awal.qty <> 0 or ct_brg_in.qty <> 0 or ct_brg_out.qty <> 0
group by ct_kodebrg.kodebarang, ct_kodebrg.namabarang,ct_brg_in.unit,ct_brg_out.unit,ct_saldo_awal.unit,cte_stock_opname_count.cnt ,cte_stock_opname_count.date_stock_opname
insert into @mutasibb (
kodebarang
,namabarang
,unit
,qty_saldoawal
,qty_in
,qty_out
,saldo_akhir
,stock_opname
,adj
)
SELECT
kodebarang
, namabarang
, unit
, qty_saldoawal
, qty_in
, qty_out
, saldo_akhir
, stock_opname
, qty_penyesuaian
FROM dbo.vw_fn_ms_mutasi_mesin(@tgstart,@tgend,1) AS vw_fn_ms_mutasi_mesin_1
return
end
GO