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 |
|---|---|---|---|
| @tglawal | date | 3 | |
| @tglakhir | date | 3 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
create FUNCTION dbo.f_mutasi_bb_added_mutasi
(
@tglawal date, @tglakhir date
)
RETURNS @mutasibb table (
part_no varchar(50) NULL
,part_no2 varchar(50) NULL
,part_no_sap varchar(50) NULL
,part_name varchar(50) NULL
,part_name2 varchar(50) NULL
,new_part varchar(50) NULL
,shipment_term varchar(50) NULL
,supplier varchar(50) NULL
,supplier_kode varchar(50) NULL
,inland_koefisien decimal(28,4) NULL
,purchase_price_usd_amount decimal(28,2) NULL
,purchase_price_usd decimal(28,2) NULL
,purchase_price_eur decimal(28,2) NULL
,purchase_price_idr decimal(28,2) NULL
,costing_price_usd decimal(28,4) NULL
,currency_part_number varchar(50) NULL
,uom varchar(50) NULL
,begin_balance_qty decimal(28,2) NULL
,begin_balance_usd decimal(28,2) NULL
,price_adjustment decimal(28,2) NULL
,in_qty decimal(28,2) NULL
,in_usd decimal(28,2) NULL
,out_qty decimal(28,2) NULL
,out_usd decimal(28,2) NULL
,end_system_qty decimal(28,2) NULL
,end_system_usd decimal(28,2) NULL
,end_sto_qty decimal(28,2) NULL
,end_sto_usd decimal(28,2) NULL
,[qty_in_daily_adjustment] decimal(28,4) NULL
,[amount_in_daily_adjustment] decimal(28,4) NULL
,[qty_out_daily_adjustment] decimal(28,4) NULL
,[amount_out_daily_adjustment] decimal(28,4) NULL
,qty_adjustment_sto decimal(28,4) NULL
,amount_adjustment_sto decimal(28,4) NULL
,compare_more_qty decimal(28,2) NULL
,compare_more_usd decimal(28,2) NULL
,compare_less_qty decimal(28,2) NULL
,compare_less_usd decimal(28,2) NULL
,mutasi_after_sto_in_qty decimal(28,2) NULL
,mutasi_after_sto_in_usd decimal(28,2) NULL
,mutasi_after_sto_out_qty decimal(28,2) NULL
,mutasi_after_sto_out_usd decimal(28,2) NULL
,requirement_15_month decimal(28,2) NULL
,over_stock_qty decimal(28,2) NULL
,dead_stock_qty decimal(28,2) NULL
,over_stock_amount decimal(28,2) NULL
,dead_stock_amount decimal(28,2) NULL
,supply_to_jai_qty decimal(28,2) NULL
,supply_to_jai_amount decimal(28,2) NULL
,supply_to_sai_qty decimal(28,2) NULL
,supply_to_sai_amount decimal(28,2) NULL
,supply_to_sami_qty decimal(28,2) NULL
,supply_to_sami_amount decimal(28,2) NULL
,supply_to_suai_qty decimal(28,2) NULL
,supply_to_suai_amount decimal(28,2) NULL
,supply_to_pasi_qty decimal(28,2) NULL
,supply_to_pasi_amount decimal(28,2) NULL
,receive_to_jai_qty decimal(28,2) NULL
,receive_to_jai_amount decimal(28,2) NULL
,receive_to_samitugu_qty decimal(28,2) NULL
,receive_to_samitugu_amount decimal(28,2) NULL
,receive_to_sai_qty decimal(28,2) NULL
,receive_to_sai_amount decimal(28,2) NULL
,receive_to_suai_qty decimal(28,2) NULL
,receive_to_suai_amount decimal(28,2) NULL
,receive_to_samijepara_qty decimal(28,2) NULL
,receive_to_samijepara_amount decimal(28,2) NULL
,return_from_production_qty decimal(28,2) NULL
,return_from_production_amount decimal(28,2) NULL
,sales_material_qty decimal(28,2) NULL
,sales_material_amount decimal(28,2) NULL
,return_wire_tube_qty decimal(28,2) NULL
,return_wire_tube_amount decimal(28,2) NULL
,out_to_production_qty decimal(28,2) NULL
,out_to_production_amount decimal(28,2) NULL
,in_purchase_qty decimal(28,2) NULL
,in_purchase_amount decimal(28,2) NULL
,in_purchase_quotes decimal(28,2) NULL
,price_idr decimal(28,2) NULL
,status_data varchar(50) NULL
,qty decimal(28,2) NULL
,maker varchar(50) NULL
,supplier_oes varchar(50) NULL
)
AS
begin
-----------------------------------
declare @tgl_sto_awal date
set @tgl_sto_awal = (
select top 1 tgl_adjustment from [dbo].[tbl_data_part_hs_adjustment]
where [tgl_adjustment] <= @tglawal and jenis = 'STO'
order by tgl_adjustment desc
)
;with
ct_master(
[part_no]
,[part_name]
,[part_name_customs]
,[hs_code]
,[bm]
,[cek_price]
,[part_no2]
,[kode_barang]
,[part_no_sap]
,[order_number]
,[new_part]
,[shipment_term]
,[supplier]
,[currency]
,[part_name_added]
,supplier_kode
,uom
) as
(
SELECT
[part_no]
,[part_name]
,[part_name_customs]
,[hs_code]
,[bm]
,[cek_price]
,[part_no2]
,[kode_barang]
,[part_no_sap]
,[order_number]
,[new_part]
,[shipment_term]
,[supplier]
,[currency]
,[part_name_added]
,supplier_kode
,uom
FROM [dbo].[tbl_data_part_hs]
where new_part is not null
)
,
ct_price (
partno2
,inland_koefisien
,purchase_price_usd
,purchase_price_eur
,purchase_price_idr
) as (
select
part_no2
,inland_koefisien
,purchase_price_usd
,purchase_price_eur
,purchase_price_idr
from [dbo].[f_mutasi_bb_added_price](@tglawal)
)
,
ct_price_valuta(
partno2, amount
) as (
select
[tbl_data_part_hs].[part_no2]
, (
case when [tbl_data_part_hs].currency = 'USD' then
a.purchase_price_usd /
(
select top 1 x.amount from tbl_data_part_hs_valuta x where x.valuta = 'USD'
and x.tgl_berlaku <= @tglawal
order by x.tgl_berlaku desc
)
when [tbl_data_part_hs].currency = 'EURO' then
a.purchase_price_eur /
(
select top 1 x.amount from tbl_data_part_hs_valuta x where x.valuta = 'EUR'
and x.tgl_berlaku <= @tglawal
order by x.tgl_berlaku desc
)
when [tbl_data_part_hs].currency = 'IDR' then
a.purchase_price_idr /
(
select top 1 x.amount from tbl_data_part_hs_valuta x where x.valuta = 'IDR'
and x.tgl_berlaku <= @tglawal
order by x.tgl_berlaku desc
)
else
0
end
)
FROM [dbo].[tbl_data_part_hs]
left outer join [dbo].[f_mutasi_bb_added_price](@tglawal) a on [tbl_data_part_hs].part_no2 = a.part_no2
where new_part is not null
)
,
ct_brg_in(
[partno2], qty
) as (
select
[part_no_article]
,sum(isnull(v_data_part_hs_artikel.qty_in, 0) * isnull(v_data_part_hs_artikel.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_part_hs_artikel
where v_data_part_hs_artikel.doc_in_date between @tglawal and @tglakhir
group by [part_no_article]
),
ct_brg_in_awal_periode(
[partno2], qty
) as (
select
[part_no_article]
,sum(isnull(v_data_part_hs_artikel.qty_in, 0) * isnull(v_data_part_hs_artikel.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_part_hs_artikel
where v_data_part_hs_artikel.doc_in_date between @tgl_sto_awal and dateadd(day, -1, @tglawal)
group by [part_no_article]
),
ct_brg_out(
[partno2], qty
) as (
select part_number, sum(qty) from
( select replace(part_number,' ', '' ) as 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 @tglawal and @tglakhir
group by part_number
) x group by part_number
),
ct_brg_out_awal_periode(
[partno2], qty
) as (
select part_number, sum(qty) from
( select replace(part_number,' ', '' ) as 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 @tgl_sto_awal and dateadd(day, -1, @tglawal)
group by part_number
) x group by part_number
),
ct_adjustment_daily
(
[partno2]
,[qty_in]
,[qty_out]
,[amount_in]
,[amount_out]
,[tgl_adjustment]
) as (
select
[partno2]
,sum(isnull([qty_in], 0))
,sum(isnull([qty_out], 0))
,sum(isnull([amount_in], 0))
,sum(isnull([amount_out], 0))
,[tgl_adjustment]
FROM [dbo].[tbl_data_part_hs_adjustment]
where jenis = 'ADJUSTMENT_DAILY' and ([tgl_adjustment] between @tglawal and @tglakhir)
group by
[partno2] ,[tgl_adjustment] ,[jenis]
)
,
ct_adjustment_sto
(
[partno2]
,[qty_in]
,[qty_out]
,[amount_in]
,[amount_out]
,[tgl_adjustment]
) as (
select
[partno2]
,sum(isnull([qty_in], 0))
,sum(isnull([qty_out], 0))
,sum(isnull([amount_in], 0))
,sum(isnull([amount_out], 0))
,[tgl_adjustment]
FROM [dbo].[tbl_data_part_hs_adjustment]
where jenis = 'ADJUSTMENT_STO' and ([tgl_adjustment] between @tglawal and @tglakhir)
group by
[partno2] ,[tgl_adjustment] ,[jenis]
)
,
ct_sto_awal
(
[partno2]
,[qty_in]
,[qty_out]
,[amount_in]
,[amount_out]
,[tgl_adjustment]
) as (
select
[partno2]
,sum(isnull([qty_in], 0))
,sum(isnull([qty_out], 0))
,sum(isnull([amount_in], 0))
,sum(isnull([amount_out], 0))
,[tgl_adjustment]
FROM [dbo].[tbl_data_part_hs_adjustment]
where jenis = 'STO' and ([tgl_adjustment] = @tgl_sto_awal)
group by
[partno2] ,[tgl_adjustment] ,[jenis]
)
insert into @mutasibb (
part_no
,part_no2
,part_no_sap
,part_name
,part_name2
,new_part
,shipment_term
,supplier
,supplier_kode
,inland_koefisien
,purchase_price_usd_amount
,purchase_price_usd
,purchase_price_eur
,purchase_price_idr
,costing_price_usd
,currency_part_number
,uom
,begin_balance_qty
,begin_balance_usd
,price_adjustment
,in_qty
,in_usd
,out_qty
,out_usd
,end_system_qty
,end_system_usd
,end_sto_qty
,end_sto_usd
,[qty_in_daily_adjustment]
,[amount_in_daily_adjustment]
,[qty_out_daily_adjustment]
,[amount_out_daily_adjustment]
,qty_adjustment_sto
,amount_adjustment_sto
)
select
part_no
,part_no2
,part_no_sap
,part_name
,part_name as part_name2
,new_part
,shipment_term
,supplier
,supplier_kode
,isnull(ct_price.inland_koefisien,0)
,isnull(ct_price_valuta.amount, 0)
as purchase_price_usd_amount
,isnull(ct_price.purchase_price_usd,0)
,isnull(ct_price.purchase_price_eur,0)
,isnull(ct_price.purchase_price_idr,0)
,( (1+isnull(ct_price.inland_koefisien,0))
* isnull(ct_price_valuta.amount,0)
) /1000 as costing_price_usd
,ct_master.currency
,ct_master.uom
, (
isnull(ct_sto_awal.[qty_in], 0) + ( isnull(ct_brg_in_awal_periode.qty, 0) - isnull(ct_brg_out_awal_periode.qty, 0) )
) as begin_balance_qty
,
( isnull(ct_sto_awal.amount_in, 0) +
( isnull(ct_brg_in_awal_periode.qty, 0)
* ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
-
(
isnull(ct_brg_out_awal_periode.qty, 0) * ((1+ct_price.inland_koefisien)* ct_price_valuta.amount)
)
)
)
as begin_balance_usd
,isnull(
(
(
(
isnull(ct_sto_awal.[qty_in], 0) + ( isnull(ct_brg_in_awal_periode.qty, 0) - isnull(ct_brg_out_awal_periode.qty, 0) )
) * ( ( (1+isnull(ct_price.inland_koefisien,0))
* isnull(ct_price_valuta.amount,0)
) /1000 )
) - ( isnull(ct_sto_awal.amount_in, 0) +
( isnull(ct_brg_in_awal_periode.qty, 0)
* ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
-
(
isnull(ct_brg_out_awal_periode.qty, 0) * ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
)
)
)
), 0)
as price_adjustment
,isnull(ct_brg_in.qty, 0) as in_qty
,
( (1+ct_price.inland_koefisien)
* (
ct_price_valuta.amount
)
) /1000 * isnull(ct_brg_in.qty, 0)
as in_usd
,isnull(ct_brg_out.qty, 0) as out_qty
,
( (1+isnull(ct_price.inland_koefisien,0))
* ( isnull( ct_price_valuta.amount,0) )
) /1000 * isnull(ct_brg_out.qty, 0)
,
(
(
isnull(ct_sto_awal.[qty_in], 0) + ( isnull(ct_brg_in_awal_periode.qty, 0) - isnull(ct_brg_out_awal_periode.qty, 0) )
) + isnull(ct_brg_in.qty, 0)
) - isnull(ct_brg_out.qty, 0)
as end_system_qty
,
(
( isnull(ct_sto_awal.amount_in, 0) +
( isnull(ct_brg_in_awal_periode.qty, 0)
* ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
-
(
isnull(ct_brg_out_awal_periode.qty, 0) * ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
)
)
) + ( (1+isnull(ct_price.inland_koefisien,0))
* (
isnull(ct_price_valuta.amount,0)
)
) /1000 * isnull(ct_brg_in.qty, 0)
) - ( (1+isnull(ct_price.inland_koefisien,0))
* ( isnull(ct_price_valuta.amount,0) )
) /1000 * isnull(ct_brg_out.qty, 0)
as end_system_usd
,0 as end_sto_qty
,0 as end_sto_usd
,isnull(ct_adjustment_daily.[qty_in], 0)
,isnull(ct_adjustment_daily.[amount_in], 0)
,isnull(ct_adjustment_daily.[qty_out], 0)
,isnull(ct_adjustment_daily.[amount_out], 0)
,isnull(ct_adjustment_sto.[qty_in], 0) as qty_adjustment_sto
,isnull(ct_adjustment_sto.[amount_in] , 0) as amount_adjustment_sto
from ct_master
left outer join ct_adjustment_daily on ct_master.part_no2 = ct_adjustment_daily.partno2
left outer join ct_adjustment_sto on ct_master.part_no2 = ct_adjustment_sto.partno2
left outer join ct_price on ct_master.part_no2 = ct_price.partno2
left outer join ct_sto_awal on ct_master.part_no2 = ct_sto_awal.partno2
left outer join ct_brg_in on ct_master.part_no2 = ct_brg_in.partno2
left outer join ct_brg_out on ct_master.part_no2 = ct_brg_out.partno2
left outer join ct_brg_in_awal_periode on ct_master.part_no2 = ct_brg_in_awal_periode.partno2
left outer join ct_brg_out_awal_periode on ct_master.part_no2 = ct_brg_out_awal_periode.partno2
left outer join ct_price_valuta on ct_master.part_no2 = ct_price_valuta.partno2
return
end
--go
--select * from [dbo].[f_mutasi_bb_added_mutasi]('2018-11-01','2018-11-30')
--where part_no2 in (
--'7134-4944-30'
--,'7138-3136'
--,'7138-3671'
--,'7138-3672'
--,'7138-4015'
--,'7138-4344'
--,'7138-4979'
--,'7138-8918-30'
--,'7139-5248-3B'
--,'7139-5331-30'
--,'7139-6127-30'
--,'7147-8948-30'
--,'7151-1104-0W'
--,'7154-5262'
--,'7157-3036-60'
--,'7157-3037-70'
--,'7157-3416-50'
--,'7157-3777'
--,'7157-3778'
--,'7157-3790-90'
--,'7157-3821'
--,'7157-3841'
--,'7157-3877-80'
--,'7157-3962-90'
--,'7157-7813-80'
--,'7158-3030-50'
--,'7158-3031-90'
--,'7158-3033-40'
--,'7158-3075-10'
--,'7158-3113-40'
--,'7158-3120-90'
--,'7158-3167-80'
--,'7158-3169-40'
--,'7158-3329'
--,'7184-1780'
--,'7194-3877-30'
--,'7282-4672-30'
--,'7327-6115'
--,'7327-6124'
--,'EPS13-11010'
--,'EPS13-50110'
--,'EPS13-50130'
--,'X1SS-6390'
--)
GO
GO
create FUNCTION dbo.f_mutasi_bb_added_mutasi
(
@tglawal date, @tglakhir date
)
RETURNS @mutasibb table (
part_no varchar(50) NULL
,part_no2 varchar(50) NULL
,part_no_sap varchar(50) NULL
,part_name varchar(50) NULL
,part_name2 varchar(50) NULL
,new_part varchar(50) NULL
,shipment_term varchar(50) NULL
,supplier varchar(50) NULL
,supplier_kode varchar(50) NULL
,inland_koefisien decimal(28,4) NULL
,purchase_price_usd_amount decimal(28,2) NULL
,purchase_price_usd decimal(28,2) NULL
,purchase_price_eur decimal(28,2) NULL
,purchase_price_idr decimal(28,2) NULL
,costing_price_usd decimal(28,4) NULL
,currency_part_number varchar(50) NULL
,uom varchar(50) NULL
,begin_balance_qty decimal(28,2) NULL
,begin_balance_usd decimal(28,2) NULL
,price_adjustment decimal(28,2) NULL
,in_qty decimal(28,2) NULL
,in_usd decimal(28,2) NULL
,out_qty decimal(28,2) NULL
,out_usd decimal(28,2) NULL
,end_system_qty decimal(28,2) NULL
,end_system_usd decimal(28,2) NULL
,end_sto_qty decimal(28,2) NULL
,end_sto_usd decimal(28,2) NULL
,[qty_in_daily_adjustment] decimal(28,4) NULL
,[amount_in_daily_adjustment] decimal(28,4) NULL
,[qty_out_daily_adjustment] decimal(28,4) NULL
,[amount_out_daily_adjustment] decimal(28,4) NULL
,qty_adjustment_sto decimal(28,4) NULL
,amount_adjustment_sto decimal(28,4) NULL
,compare_more_qty decimal(28,2) NULL
,compare_more_usd decimal(28,2) NULL
,compare_less_qty decimal(28,2) NULL
,compare_less_usd decimal(28,2) NULL
,mutasi_after_sto_in_qty decimal(28,2) NULL
,mutasi_after_sto_in_usd decimal(28,2) NULL
,mutasi_after_sto_out_qty decimal(28,2) NULL
,mutasi_after_sto_out_usd decimal(28,2) NULL
,requirement_15_month decimal(28,2) NULL
,over_stock_qty decimal(28,2) NULL
,dead_stock_qty decimal(28,2) NULL
,over_stock_amount decimal(28,2) NULL
,dead_stock_amount decimal(28,2) NULL
,supply_to_jai_qty decimal(28,2) NULL
,supply_to_jai_amount decimal(28,2) NULL
,supply_to_sai_qty decimal(28,2) NULL
,supply_to_sai_amount decimal(28,2) NULL
,supply_to_sami_qty decimal(28,2) NULL
,supply_to_sami_amount decimal(28,2) NULL
,supply_to_suai_qty decimal(28,2) NULL
,supply_to_suai_amount decimal(28,2) NULL
,supply_to_pasi_qty decimal(28,2) NULL
,supply_to_pasi_amount decimal(28,2) NULL
,receive_to_jai_qty decimal(28,2) NULL
,receive_to_jai_amount decimal(28,2) NULL
,receive_to_samitugu_qty decimal(28,2) NULL
,receive_to_samitugu_amount decimal(28,2) NULL
,receive_to_sai_qty decimal(28,2) NULL
,receive_to_sai_amount decimal(28,2) NULL
,receive_to_suai_qty decimal(28,2) NULL
,receive_to_suai_amount decimal(28,2) NULL
,receive_to_samijepara_qty decimal(28,2) NULL
,receive_to_samijepara_amount decimal(28,2) NULL
,return_from_production_qty decimal(28,2) NULL
,return_from_production_amount decimal(28,2) NULL
,sales_material_qty decimal(28,2) NULL
,sales_material_amount decimal(28,2) NULL
,return_wire_tube_qty decimal(28,2) NULL
,return_wire_tube_amount decimal(28,2) NULL
,out_to_production_qty decimal(28,2) NULL
,out_to_production_amount decimal(28,2) NULL
,in_purchase_qty decimal(28,2) NULL
,in_purchase_amount decimal(28,2) NULL
,in_purchase_quotes decimal(28,2) NULL
,price_idr decimal(28,2) NULL
,status_data varchar(50) NULL
,qty decimal(28,2) NULL
,maker varchar(50) NULL
,supplier_oes varchar(50) NULL
)
AS
begin
-----------------------------------
declare @tgl_sto_awal date
set @tgl_sto_awal = (
select top 1 tgl_adjustment from [dbo].[tbl_data_part_hs_adjustment]
where [tgl_adjustment] <= @tglawal and jenis = 'STO'
order by tgl_adjustment desc
)
;with
ct_master(
[part_no]
,[part_name]
,[part_name_customs]
,[hs_code]
,[bm]
,[cek_price]
,[part_no2]
,[kode_barang]
,[part_no_sap]
,[order_number]
,[new_part]
,[shipment_term]
,[supplier]
,[currency]
,[part_name_added]
,supplier_kode
,uom
) as
(
SELECT
[part_no]
,[part_name]
,[part_name_customs]
,[hs_code]
,[bm]
,[cek_price]
,[part_no2]
,[kode_barang]
,[part_no_sap]
,[order_number]
,[new_part]
,[shipment_term]
,[supplier]
,[currency]
,[part_name_added]
,supplier_kode
,uom
FROM [dbo].[tbl_data_part_hs]
where new_part is not null
)
,
ct_price (
partno2
,inland_koefisien
,purchase_price_usd
,purchase_price_eur
,purchase_price_idr
) as (
select
part_no2
,inland_koefisien
,purchase_price_usd
,purchase_price_eur
,purchase_price_idr
from [dbo].[f_mutasi_bb_added_price](@tglawal)
)
,
ct_price_valuta(
partno2, amount
) as (
select
[tbl_data_part_hs].[part_no2]
, (
case when [tbl_data_part_hs].currency = 'USD' then
a.purchase_price_usd /
(
select top 1 x.amount from tbl_data_part_hs_valuta x where x.valuta = 'USD'
and x.tgl_berlaku <= @tglawal
order by x.tgl_berlaku desc
)
when [tbl_data_part_hs].currency = 'EURO' then
a.purchase_price_eur /
(
select top 1 x.amount from tbl_data_part_hs_valuta x where x.valuta = 'EUR'
and x.tgl_berlaku <= @tglawal
order by x.tgl_berlaku desc
)
when [tbl_data_part_hs].currency = 'IDR' then
a.purchase_price_idr /
(
select top 1 x.amount from tbl_data_part_hs_valuta x where x.valuta = 'IDR'
and x.tgl_berlaku <= @tglawal
order by x.tgl_berlaku desc
)
else
0
end
)
FROM [dbo].[tbl_data_part_hs]
left outer join [dbo].[f_mutasi_bb_added_price](@tglawal) a on [tbl_data_part_hs].part_no2 = a.part_no2
where new_part is not null
)
,
ct_brg_in(
[partno2], qty
) as (
select
[part_no_article]
,sum(isnull(v_data_part_hs_artikel.qty_in, 0) * isnull(v_data_part_hs_artikel.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_part_hs_artikel
where v_data_part_hs_artikel.doc_in_date between @tglawal and @tglakhir
group by [part_no_article]
),
ct_brg_in_awal_periode(
[partno2], qty
) as (
select
[part_no_article]
,sum(isnull(v_data_part_hs_artikel.qty_in, 0) * isnull(v_data_part_hs_artikel.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_part_hs_artikel
where v_data_part_hs_artikel.doc_in_date between @tgl_sto_awal and dateadd(day, -1, @tglawal)
group by [part_no_article]
),
ct_brg_out(
[partno2], qty
) as (
select part_number, sum(qty) from
( select replace(part_number,' ', '' ) as 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 @tglawal and @tglakhir
group by part_number
) x group by part_number
),
ct_brg_out_awal_periode(
[partno2], qty
) as (
select part_number, sum(qty) from
( select replace(part_number,' ', '' ) as 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 @tgl_sto_awal and dateadd(day, -1, @tglawal)
group by part_number
) x group by part_number
),
ct_adjustment_daily
(
[partno2]
,[qty_in]
,[qty_out]
,[amount_in]
,[amount_out]
,[tgl_adjustment]
) as (
select
[partno2]
,sum(isnull([qty_in], 0))
,sum(isnull([qty_out], 0))
,sum(isnull([amount_in], 0))
,sum(isnull([amount_out], 0))
,[tgl_adjustment]
FROM [dbo].[tbl_data_part_hs_adjustment]
where jenis = 'ADJUSTMENT_DAILY' and ([tgl_adjustment] between @tglawal and @tglakhir)
group by
[partno2] ,[tgl_adjustment] ,[jenis]
)
,
ct_adjustment_sto
(
[partno2]
,[qty_in]
,[qty_out]
,[amount_in]
,[amount_out]
,[tgl_adjustment]
) as (
select
[partno2]
,sum(isnull([qty_in], 0))
,sum(isnull([qty_out], 0))
,sum(isnull([amount_in], 0))
,sum(isnull([amount_out], 0))
,[tgl_adjustment]
FROM [dbo].[tbl_data_part_hs_adjustment]
where jenis = 'ADJUSTMENT_STO' and ([tgl_adjustment] between @tglawal and @tglakhir)
group by
[partno2] ,[tgl_adjustment] ,[jenis]
)
,
ct_sto_awal
(
[partno2]
,[qty_in]
,[qty_out]
,[amount_in]
,[amount_out]
,[tgl_adjustment]
) as (
select
[partno2]
,sum(isnull([qty_in], 0))
,sum(isnull([qty_out], 0))
,sum(isnull([amount_in], 0))
,sum(isnull([amount_out], 0))
,[tgl_adjustment]
FROM [dbo].[tbl_data_part_hs_adjustment]
where jenis = 'STO' and ([tgl_adjustment] = @tgl_sto_awal)
group by
[partno2] ,[tgl_adjustment] ,[jenis]
)
insert into @mutasibb (
part_no
,part_no2
,part_no_sap
,part_name
,part_name2
,new_part
,shipment_term
,supplier
,supplier_kode
,inland_koefisien
,purchase_price_usd_amount
,purchase_price_usd
,purchase_price_eur
,purchase_price_idr
,costing_price_usd
,currency_part_number
,uom
,begin_balance_qty
,begin_balance_usd
,price_adjustment
,in_qty
,in_usd
,out_qty
,out_usd
,end_system_qty
,end_system_usd
,end_sto_qty
,end_sto_usd
,[qty_in_daily_adjustment]
,[amount_in_daily_adjustment]
,[qty_out_daily_adjustment]
,[amount_out_daily_adjustment]
,qty_adjustment_sto
,amount_adjustment_sto
)
select
part_no
,part_no2
,part_no_sap
,part_name
,part_name as part_name2
,new_part
,shipment_term
,supplier
,supplier_kode
,isnull(ct_price.inland_koefisien,0)
,isnull(ct_price_valuta.amount, 0)
as purchase_price_usd_amount
,isnull(ct_price.purchase_price_usd,0)
,isnull(ct_price.purchase_price_eur,0)
,isnull(ct_price.purchase_price_idr,0)
,( (1+isnull(ct_price.inland_koefisien,0))
* isnull(ct_price_valuta.amount,0)
) /1000 as costing_price_usd
,ct_master.currency
,ct_master.uom
, (
isnull(ct_sto_awal.[qty_in], 0) + ( isnull(ct_brg_in_awal_periode.qty, 0) - isnull(ct_brg_out_awal_periode.qty, 0) )
) as begin_balance_qty
,
( isnull(ct_sto_awal.amount_in, 0) +
( isnull(ct_brg_in_awal_periode.qty, 0)
* ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
-
(
isnull(ct_brg_out_awal_periode.qty, 0) * ((1+ct_price.inland_koefisien)* ct_price_valuta.amount)
)
)
)
as begin_balance_usd
,isnull(
(
(
(
isnull(ct_sto_awal.[qty_in], 0) + ( isnull(ct_brg_in_awal_periode.qty, 0) - isnull(ct_brg_out_awal_periode.qty, 0) )
) * ( ( (1+isnull(ct_price.inland_koefisien,0))
* isnull(ct_price_valuta.amount,0)
) /1000 )
) - ( isnull(ct_sto_awal.amount_in, 0) +
( isnull(ct_brg_in_awal_periode.qty, 0)
* ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
-
(
isnull(ct_brg_out_awal_periode.qty, 0) * ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
)
)
)
), 0)
as price_adjustment
,isnull(ct_brg_in.qty, 0) as in_qty
,
( (1+ct_price.inland_koefisien)
* (
ct_price_valuta.amount
)
) /1000 * isnull(ct_brg_in.qty, 0)
as in_usd
,isnull(ct_brg_out.qty, 0) as out_qty
,
( (1+isnull(ct_price.inland_koefisien,0))
* ( isnull( ct_price_valuta.amount,0) )
) /1000 * isnull(ct_brg_out.qty, 0)
,
(
(
isnull(ct_sto_awal.[qty_in], 0) + ( isnull(ct_brg_in_awal_periode.qty, 0) - isnull(ct_brg_out_awal_periode.qty, 0) )
) + isnull(ct_brg_in.qty, 0)
) - isnull(ct_brg_out.qty, 0)
as end_system_qty
,
(
( isnull(ct_sto_awal.amount_in, 0) +
( isnull(ct_brg_in_awal_periode.qty, 0)
* ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
-
(
isnull(ct_brg_out_awal_periode.qty, 0) * ((1+isnull(ct_price.inland_koefisien,0))* isnull(ct_price_valuta.amount,0))
)
)
) + ( (1+isnull(ct_price.inland_koefisien,0))
* (
isnull(ct_price_valuta.amount,0)
)
) /1000 * isnull(ct_brg_in.qty, 0)
) - ( (1+isnull(ct_price.inland_koefisien,0))
* ( isnull(ct_price_valuta.amount,0) )
) /1000 * isnull(ct_brg_out.qty, 0)
as end_system_usd
,0 as end_sto_qty
,0 as end_sto_usd
,isnull(ct_adjustment_daily.[qty_in], 0)
,isnull(ct_adjustment_daily.[amount_in], 0)
,isnull(ct_adjustment_daily.[qty_out], 0)
,isnull(ct_adjustment_daily.[amount_out], 0)
,isnull(ct_adjustment_sto.[qty_in], 0) as qty_adjustment_sto
,isnull(ct_adjustment_sto.[amount_in] , 0) as amount_adjustment_sto
from ct_master
left outer join ct_adjustment_daily on ct_master.part_no2 = ct_adjustment_daily.partno2
left outer join ct_adjustment_sto on ct_master.part_no2 = ct_adjustment_sto.partno2
left outer join ct_price on ct_master.part_no2 = ct_price.partno2
left outer join ct_sto_awal on ct_master.part_no2 = ct_sto_awal.partno2
left outer join ct_brg_in on ct_master.part_no2 = ct_brg_in.partno2
left outer join ct_brg_out on ct_master.part_no2 = ct_brg_out.partno2
left outer join ct_brg_in_awal_periode on ct_master.part_no2 = ct_brg_in_awal_periode.partno2
left outer join ct_brg_out_awal_periode on ct_master.part_no2 = ct_brg_out_awal_periode.partno2
left outer join ct_price_valuta on ct_master.part_no2 = ct_price_valuta.partno2
return
end
--go
--select * from [dbo].[f_mutasi_bb_added_mutasi]('2018-11-01','2018-11-30')
--where part_no2 in (
--'7134-4944-30'
--,'7138-3136'
--,'7138-3671'
--,'7138-3672'
--,'7138-4015'
--,'7138-4344'
--,'7138-4979'
--,'7138-8918-30'
--,'7139-5248-3B'
--,'7139-5331-30'
--,'7139-6127-30'
--,'7147-8948-30'
--,'7151-1104-0W'
--,'7154-5262'
--,'7157-3036-60'
--,'7157-3037-70'
--,'7157-3416-50'
--,'7157-3777'
--,'7157-3778'
--,'7157-3790-90'
--,'7157-3821'
--,'7157-3841'
--,'7157-3877-80'
--,'7157-3962-90'
--,'7157-7813-80'
--,'7158-3030-50'
--,'7158-3031-90'
--,'7158-3033-40'
--,'7158-3075-10'
--,'7158-3113-40'
--,'7158-3120-90'
--,'7158-3167-80'
--,'7158-3169-40'
--,'7158-3329'
--,'7184-1780'
--,'7194-3877-30'
--,'7282-4672-30'
--,'7327-6115'
--,'7327-6124'
--,'EPS13-11010'
--,'EPS13-50110'
--,'EPS13-50130'
--,'X1SS-6390'
--)
GO
Depends On
7Used By
No items found