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_fg
(
@tgstart date, @tgend date, @tipebahan varchar(50)
)
RETURNS @mutasibj table (
part_no varchar(100) null
,part_name varchar(100) null
,stock_awal float null
,qty_in float null
,qty_out float null
,sisa float null
,unit varchar(100) null
,qty_in_by_date float null
,qty_out_by_date float null
,sisa_by_date float null
,stock_opname float null
,adj float null
)
AS
begin
declare @datesaldo date
declare @stsstockopname int, @dtsto date
set @datesaldo = (select max(date_stock) from [tbl_stock_awal] where typebahan='FG')
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 = 'FG'
) and a.typebahan = 'FG'
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 = 'FG'
) and a.typebahan = 'FG'
group by part_name_customs, [date_stock_opname]
) as x)
set @datesaldo=dateadd(day, 1, @dtsto)
end
declare @tgstartx date
if (select count(*) from [tbl_stock_opname] where typebahan = 'FG' and [date_stock_opname] between @tgstart and @tgend) > 0
begin
set @tgstartx = (
select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'FG' 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 = 'FG' and [date_stock_opname] between @tgstart and @tgend) > 0
--begin
-- set @tgmax = (select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'FG' 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 = 'FG' 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 = 'FG'
-- group by part_name_customs, [date_stock_opname]
-- ) as x)
-- set @datesaldo=dateadd(day, 1, @dtsto)
-- end
--end
;with
ct_assy_list(
part_no,part_name
) as
(
SELECT
[ASSY_CODE]
,[ASSY_NO]
FROM [DB_PEMI].[dbo].[tbl_assy_list] where status_aktif='aktif'
--FROM [DB_PEMI].[dbo].[tbl_assy_list]
group by
[ASSY_CODE]
,[ASSY_NO]
),
ctestokawal(part_no, qty) as (
SELECT
[part_no]
,sum([qty])
FROM [DB_PEMI].[dbo].[tbl_stock_awal]
where typebahan = 'FG' and date_stock=@datesaldo
group by [part_no]
),
ctestok_trxawal(
part_no, qty_in, unit
) as
(
--select part_no, sum(abs(qty_in)), unit_in
select part_no, sum(qty_in), unit_in
from tbl_data_in_dtl inner join tbl_data_in_hdr on tbl_data_in_hdr.id_data_in_hdr = tbl_data_in_dtl.id_data_in_hdr
where type_brg=2 and status='APPROVED' and tbl_data_in_hdr.doc_in_date between @tgstart and @tgend
group by part_no, unit_in
),
--ctest_trxoutawal(part_no, qty_out, unit) as (
--SELECT assy_code, sum(qty), unit_out
--FROM tbl_data_out_dtl
--where type_items='FG' and [out_date] between @tgstart and @tgend
--group by
--assy_code, unit_out
--),
ctest_trxoutawal(part_no, qty_out, unit) as (
SELECT assy_code, sum(qty), unit_out
FROM tbl_data_out_dtl
left outer join tbl_data_out_hdr on tbl_data_out_hdr.id_data_out_hdr = tbl_data_out_dtl.id_data_out_hdr
where type_items='FG' and tbl_data_out_hdr.doc_out_date between @tgstart and @tgend
group by
assy_code, unit_out
),
ctestok_in_date(
part_no, qty_in
) as
(
--select part_no, sum(abs(qty_in)) as qty_in
select part_no, sum(qty_in) as qty_in
from tbl_data_in_dtl inner join tbl_data_in_hdr on tbl_data_in_hdr.id_data_in_hdr = tbl_data_in_dtl.id_data_in_hdr
where type_brg=2 and status='APPROVED' and tbl_data_in_hdr.doc_in_date between @datesaldo and dateadd(day, -1, @tgstart)
group by part_no,unit_in
),
--ctest_trxout_bydate(part_no, qty_out, unit) as (
--SELECT assy_code, sum(qty), unit_out
--FROM tbl_data_out_dtl
--where type_items='FG' and out_date between @datesaldo and dateadd(day, -1, @tgstart)
--group by
--assy_code,unit_out
--),
ctest_trxout_bydate(part_no, qty_out, unit) as (
SELECT assy_code, sum(qty), unit_out
FROM tbl_data_out_dtl
left outer join tbl_data_out_hdr on tbl_data_out_hdr.id_data_out_hdr = tbl_data_out_dtl.id_data_out_hdr
where type_items='FG' and tbl_data_out_hdr.doc_out_date between @datesaldo and dateadd(day, -1, @tgstart)
group by
assy_code,unit_out
),
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 = 'FG'
) and a.typebahan = 'FG'
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 = 'FG'
) and a.typebahan = 'FG'
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 = 'FG'
) and a.typebahan = 'FG'
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 = 'FG'
) and a.typebahan = 'FG'
group by part_no
)
insert into @mutasibj (part_no,part_name,stock_awal,qty_in,qty_out,sisa,unit,qty_in_by_date,qty_out_by_date,sisa_by_date,stock_opname,adj
)
SELECT
ct_assy_list.part_no
, ct_assy_list.part_name
--,isnull(ctestokawal.qty,0) + (isnull(ctestok_in_date.qty_in, 0) - isnull(ctest_trxout_bydate.qty_out,0)) as stok_awal
,
case when isnull(cte_stock_opname_count.cnt,0) = 0 then
(isnull(ctestokawal.qty,0) + (isnull(ctestok_in_date.qty_in,0)) - (isnull(ctest_trxout_bydate.qty_out,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(ctestok_in_date.qty_in,0) - (isnull(ctest_trxout_bydate.qty_out,0) )))
end
end as stok_awal
,isnull(ctestok_trxawal.qty_in, 0) as qty_in
,isnull(ctest_trxoutawal.qty_out, 0) as qty_out
,(isnull(ctestokawal.qty,0) + (isnull(ctestok_trxawal.qty_in,0)) - isnull(ctest_trxoutawal.qty_out, 0) ) as sisa
,'PCS' as unit --(isnull(ctestok_trxawal.unit, 'PCS')) as unit
,isnull(ctestok_in_date.qty_in,0) as qty_in_by_date
,isnull(ctest_trxout_bydate.qty_out,0) as qty_out_by_date
--,(isnull(ctestokawal.qty,0) + ( ( isnull(ctestok_in_date.qty_in,0) - isnull(ctest_trxout_bydate.qty_out,0)) ) + (isnull(ctestok_trxawal.qty_in, 0) - isnull(ctest_trxoutawal.qty_out, 0))) as sisa_by_date
,
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(ctestokawal.qty,0) + (isnull(ctestok_in_date.qty_in,0) - isnull(ctest_trxout_bydate.qty_out,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(ctestok_in_date.qty_in,0) - (isnull(ctest_trxout_bydate.qty_out,0) )))
end
end
+
((isnull(ctestok_trxawal.qty_in,0))) - (isnull(ctest_trxoutawal.qty_out,0)))
) as saldo_akhir
,
--isnull(cte_stock_opname_akhir.qty,0) as sto
case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'FG') = 0 then 0 else isnull(cte_stock_opname_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(ctestokawal.qty,0) + (isnull(ctestok_in_date.qty_in,0) - isnull(ctest_trxout_bydate.qty_out,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(ctestok_in_date.qty_in,0) - (isnull(ctest_trxout_bydate.qty_out,0) )))
end
end
+
((isnull(ctestok_trxawal.qty_in,0))) - (isnull(ctest_trxoutawal.qty_out,0)))
)
end
from ct_assy_list
left outer join ctestok_trxawal on ct_assy_list.part_no = ctestok_trxawal.part_no
left outer join ctest_trxoutawal on ct_assy_list.part_no=ctest_trxoutawal.part_no
left outer join ctestokawal on ct_assy_list.part_no=ctestokawal.part_no
left outer join ctestok_in_date on ct_assy_list.part_no=ctestok_in_date.part_no
left outer join ctest_trxout_bydate on ct_assy_list.part_no=ctest_trxout_bydate.part_no
left outer join cte_stock_opname_awal on ct_assy_list.part_no = cte_stock_opname_awal.part_no
left outer join cte_stock_opname_akhir on ct_assy_list.part_no = cte_stock_opname_akhir.part_no
left outer join cte_stock_opname_count on ct_assy_list.part_no = cte_stock_opname_count.part_no
left outer join cte_stock_opname_tgl_akhir on ct_assy_list.part_no = cte_stock_opname_tgl_akhir.part_no
return
end
GO
GO
CREATE FUNCTION dbo.f_mutasi_fg
(
@tgstart date, @tgend date, @tipebahan varchar(50)
)
RETURNS @mutasibj table (
part_no varchar(100) null
,part_name varchar(100) null
,stock_awal float null
,qty_in float null
,qty_out float null
,sisa float null
,unit varchar(100) null
,qty_in_by_date float null
,qty_out_by_date float null
,sisa_by_date float null
,stock_opname float null
,adj float null
)
AS
begin
declare @datesaldo date
declare @stsstockopname int, @dtsto date
set @datesaldo = (select max(date_stock) from [tbl_stock_awal] where typebahan='FG')
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 = 'FG'
) and a.typebahan = 'FG'
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 = 'FG'
) and a.typebahan = 'FG'
group by part_name_customs, [date_stock_opname]
) as x)
set @datesaldo=dateadd(day, 1, @dtsto)
end
declare @tgstartx date
if (select count(*) from [tbl_stock_opname] where typebahan = 'FG' and [date_stock_opname] between @tgstart and @tgend) > 0
begin
set @tgstartx = (
select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'FG' 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 = 'FG' and [date_stock_opname] between @tgstart and @tgend) > 0
--begin
-- set @tgmax = (select max(date_stock_opname) from [tbl_stock_opname] where typebahan = 'FG' 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 = 'FG' 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 = 'FG'
-- group by part_name_customs, [date_stock_opname]
-- ) as x)
-- set @datesaldo=dateadd(day, 1, @dtsto)
-- end
--end
;with
ct_assy_list(
part_no,part_name
) as
(
SELECT
[ASSY_CODE]
,[ASSY_NO]
FROM [DB_PEMI].[dbo].[tbl_assy_list] where status_aktif='aktif'
--FROM [DB_PEMI].[dbo].[tbl_assy_list]
group by
[ASSY_CODE]
,[ASSY_NO]
),
ctestokawal(part_no, qty) as (
SELECT
[part_no]
,sum([qty])
FROM [DB_PEMI].[dbo].[tbl_stock_awal]
where typebahan = 'FG' and date_stock=@datesaldo
group by [part_no]
),
ctestok_trxawal(
part_no, qty_in, unit
) as
(
--select part_no, sum(abs(qty_in)), unit_in
select part_no, sum(qty_in), unit_in
from tbl_data_in_dtl inner join tbl_data_in_hdr on tbl_data_in_hdr.id_data_in_hdr = tbl_data_in_dtl.id_data_in_hdr
where type_brg=2 and status='APPROVED' and tbl_data_in_hdr.doc_in_date between @tgstart and @tgend
group by part_no, unit_in
),
--ctest_trxoutawal(part_no, qty_out, unit) as (
--SELECT assy_code, sum(qty), unit_out
--FROM tbl_data_out_dtl
--where type_items='FG' and [out_date] between @tgstart and @tgend
--group by
--assy_code, unit_out
--),
ctest_trxoutawal(part_no, qty_out, unit) as (
SELECT assy_code, sum(qty), unit_out
FROM tbl_data_out_dtl
left outer join tbl_data_out_hdr on tbl_data_out_hdr.id_data_out_hdr = tbl_data_out_dtl.id_data_out_hdr
where type_items='FG' and tbl_data_out_hdr.doc_out_date between @tgstart and @tgend
group by
assy_code, unit_out
),
ctestok_in_date(
part_no, qty_in
) as
(
--select part_no, sum(abs(qty_in)) as qty_in
select part_no, sum(qty_in) as qty_in
from tbl_data_in_dtl inner join tbl_data_in_hdr on tbl_data_in_hdr.id_data_in_hdr = tbl_data_in_dtl.id_data_in_hdr
where type_brg=2 and status='APPROVED' and tbl_data_in_hdr.doc_in_date between @datesaldo and dateadd(day, -1, @tgstart)
group by part_no,unit_in
),
--ctest_trxout_bydate(part_no, qty_out, unit) as (
--SELECT assy_code, sum(qty), unit_out
--FROM tbl_data_out_dtl
--where type_items='FG' and out_date between @datesaldo and dateadd(day, -1, @tgstart)
--group by
--assy_code,unit_out
--),
ctest_trxout_bydate(part_no, qty_out, unit) as (
SELECT assy_code, sum(qty), unit_out
FROM tbl_data_out_dtl
left outer join tbl_data_out_hdr on tbl_data_out_hdr.id_data_out_hdr = tbl_data_out_dtl.id_data_out_hdr
where type_items='FG' and tbl_data_out_hdr.doc_out_date between @datesaldo and dateadd(day, -1, @tgstart)
group by
assy_code,unit_out
),
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 = 'FG'
) and a.typebahan = 'FG'
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 = 'FG'
) and a.typebahan = 'FG'
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 = 'FG'
) and a.typebahan = 'FG'
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 = 'FG'
) and a.typebahan = 'FG'
group by part_no
)
insert into @mutasibj (part_no,part_name,stock_awal,qty_in,qty_out,sisa,unit,qty_in_by_date,qty_out_by_date,sisa_by_date,stock_opname,adj
)
SELECT
ct_assy_list.part_no
, ct_assy_list.part_name
--,isnull(ctestokawal.qty,0) + (isnull(ctestok_in_date.qty_in, 0) - isnull(ctest_trxout_bydate.qty_out,0)) as stok_awal
,
case when isnull(cte_stock_opname_count.cnt,0) = 0 then
(isnull(ctestokawal.qty,0) + (isnull(ctestok_in_date.qty_in,0)) - (isnull(ctest_trxout_bydate.qty_out,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(ctestok_in_date.qty_in,0) - (isnull(ctest_trxout_bydate.qty_out,0) )))
end
end as stok_awal
,isnull(ctestok_trxawal.qty_in, 0) as qty_in
,isnull(ctest_trxoutawal.qty_out, 0) as qty_out
,(isnull(ctestokawal.qty,0) + (isnull(ctestok_trxawal.qty_in,0)) - isnull(ctest_trxoutawal.qty_out, 0) ) as sisa
,'PCS' as unit --(isnull(ctestok_trxawal.unit, 'PCS')) as unit
,isnull(ctestok_in_date.qty_in,0) as qty_in_by_date
,isnull(ctest_trxout_bydate.qty_out,0) as qty_out_by_date
--,(isnull(ctestokawal.qty,0) + ( ( isnull(ctestok_in_date.qty_in,0) - isnull(ctest_trxout_bydate.qty_out,0)) ) + (isnull(ctestok_trxawal.qty_in, 0) - isnull(ctest_trxoutawal.qty_out, 0))) as sisa_by_date
,
(
(case when isnull(cte_stock_opname_count.cnt,0)= 0 then
(isnull(ctestokawal.qty,0) + (isnull(ctestok_in_date.qty_in,0) - isnull(ctest_trxout_bydate.qty_out,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(ctestok_in_date.qty_in,0) - (isnull(ctest_trxout_bydate.qty_out,0) )))
end
end
+
((isnull(ctestok_trxawal.qty_in,0))) - (isnull(ctest_trxoutawal.qty_out,0)))
) as saldo_akhir
,
--isnull(cte_stock_opname_akhir.qty,0) as sto
case when (select count(*) from [tbl_stock_opname] where [date_stock_opname] = @tgend and typebahan = 'FG') = 0 then 0 else isnull(cte_stock_opname_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(ctestokawal.qty,0) + (isnull(ctestok_in_date.qty_in,0) - isnull(ctest_trxout_bydate.qty_out,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(ctestok_in_date.qty_in,0) - (isnull(ctest_trxout_bydate.qty_out,0) )))
end
end
+
((isnull(ctestok_trxawal.qty_in,0))) - (isnull(ctest_trxoutawal.qty_out,0)))
)
end
from ct_assy_list
left outer join ctestok_trxawal on ct_assy_list.part_no = ctestok_trxawal.part_no
left outer join ctest_trxoutawal on ct_assy_list.part_no=ctest_trxoutawal.part_no
left outer join ctestokawal on ct_assy_list.part_no=ctestokawal.part_no
left outer join ctestok_in_date on ct_assy_list.part_no=ctestok_in_date.part_no
left outer join ctest_trxout_bydate on ct_assy_list.part_no=ctest_trxout_bydate.part_no
left outer join cte_stock_opname_awal on ct_assy_list.part_no = cte_stock_opname_awal.part_no
left outer join cte_stock_opname_akhir on ct_assy_list.part_no = cte_stock_opname_akhir.part_no
left outer join cte_stock_opname_count on ct_assy_list.part_no = cte_stock_opname_count.part_no
left outer join cte_stock_opname_tgl_akhir on ct_assy_list.part_no = cte_stock_opname_tgl_akhir.part_no
return
end
GO