Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
| Name | Data Type | Length | Description |
|---|---|---|---|
| @id_sa_hdr | bigint | 8 | |
| @SA_NO | varchar | 255 |
SQL Script
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE procedure dbo.sp_bahan_baku_asal_2
@id_sa_hdr bigint
,@SA_NO varchar(255)
as
declare @Tgl_keluar date = getdate()
declare @ndpbm_now decimal(18,2)
set @ndpbm_now = dbo.get_rate_valuta(GETDATE(),'USD')
select Top 1 @Tgl_keluar = TgDok from tbl_sa_dok where id_sa_hdr = @id_sa_hdr order by TgDok ASC
declare @tmp_sa_list_item
table (
id_sa bigint
, ASSY_CODE varchar(255) primary key
, total decimal(18,4)
, HS_CODE varchar(255)
, BM decimal(18,2)
, have_formula bit
)
insert into @tmp_sa_list_item (id_sa,ASSY_CODE,total,HS_CODE,BM ,have_formula)
select a.id_sa, a.ASSY_CODE, sum(a.TOTAL_SET ) as total, '85443019',5, case when c.total >0 then 1 else 0 end from tbl_sa a
left outer join vw_tbl_bom_hdr_check as c on a.ASSY_CODE=c.ASSY_CODE
Left Join tbl_sa_hdr b ON a.id_sa_hdr = b.id_sa_hdr
where b.id_sa_hdr = @id_sa_hdr
group by a.id_sa,a.ASSY_CODE,case when c.total >0 then 1 else 0 end
declare @rumus_assy varchar(max)= (
STUFF((
SELECT ', ''' + ASSY_CODE + ''''
FROM @tmp_sa_list_item
where have_formula=0
for xml PATH(''), type
).value('.', 'varchar(max)'), 1, 1, '')
)
Declare @tmp_bom_dtl TABLE(
ASSY_CODE varchar(255),
PART_NO varchar(255) NULL,
QTY decimal(38,4) NULL,
PARTNO2 varchar(255) ,
ASSY_NO varchar(255) NULL,
FLAG varchar(255) NULL,
STATUS varchar(255) NULL,
primary key(ASSY_CODE,PARTNO2)
)
if @rumus_assy is not null
begin
declare @TSQL as Nvarchar(max)
SELECT @TSQL = 'SELECT ASSY_CODE, NULL, ''FROMORACC'', 0, GETDATE() FROM OPENQUERY(ORACC,''SELECT * FROM PARTLIST_VIEW WHERE ASSY_CODE in (' + @rumus_assy + ')) GROUP BY ASSY_CODE'
insert into dbo.tbl_bom_hdr ( ASSY_CODE, ASSY_NO, filename, itemcount, dateexec)
EXEC (@TSQL)
SELECT @TSQL = 'SELECT *, replace(PART_NO,'' '','''') AS PART_NO2 FROM OPENQUERY(ORACC,''SELECT * FROM PARTLIST_VIEW WHERE PART_NO is not null and (ASSY_CODE in (' + @rumus_assy + ')))'
insert into @tmp_bom_dtl ( ASSY_CODE, PART_NO, QTY, PARTNO2)
EXEC (@TSQL)
MERGE dbo.tbl_bom_dtl AS TARGET
USING @tmp_bom_dtl AS SOURCE
ON ( SOURCE.ASSY_CODE = TARGET.ASSY_CODE and SOURCE.PARTNO2 = TARGET.PARTNO2 )
WHEN MATCHED
THEN
UPDATE set
TARGET.QTY = SOURCE.QTY
,TARGET.status_bom = (case when (select count(*) from dbo.tbl_data_part_hs where part_no2=SOURCE.PARTNO2 ) = 0 then 'not found part no' else 'ok' end)
WHEN NOT MATCHED BY TARGET THEN
INSERT (
ASSY_CODE, ASSY_NO,PART_NO, QTY, PARTNO2, FLAG, STATUS
,status_bom
) VALUES (
SOURCE.ASSY_CODE, SOURCE.ASSY_NO,SOURCE.PART_NO, SOURCE.QTY, SOURCE.PARTNO2, SOURCE.FLAG, SOURCE.STATUS
, (case when (select count(*) from dbo.tbl_data_part_hs where part_no2=SOURCE.PARTNO2 ) = 0 then 'not found part no' else 'ok' end)
);
update tbl_bom_hdr
set itemcount =isnull((select count(*) from tbl_bom_dtl where ASSY_CODE = tbl_bom_hdr.ASSY_CODE),0)
where exists(
select * from @tmp_bom_dtl as filter where tbl_bom_hdr.ASSY_CODE=filter.ASSY_CODE
)
end
declare @tmp_sa_list_item_bom table (
ASSY_CODE varchar(255)
, PART_NO varchar(255)
, PART_NO2 varchar(255)
, BM_BARANG decimal(18,2)
, id_sa bigint
, cunsumtion decimal(18,4)
primary key(ASSY_CODE,PART_NO2)
)
insert into @tmp_sa_list_item_bom
(ASSY_CODE,PART_NO,PART_NO2,BM_BARANG,id_sa,cunsumtion)
select tbl_bom_dtl.ASSY_CODE
, tbl_bom_dtl.PART_NO
, replace(tbl_bom_dtl.PARTNO2,' ','')
,data_item.BM
,data_item.id_sa
, tbl_bom_dtl.QTY * data_item.total
FROM tbl_bom_dtl INNER JOIN
-- (SELECT tbl_bom_dtl.ASSY_CODE)
@tmp_sa_list_item AS data_item ON tbl_bom_dtl.ASSY_CODE = data_item.ASSY_CODE
declare @tmp_sa_list_mat
table (
mat_code_fix varchar(255) primary key
, mat_code varchar(255)
, qty decimal(24,4)
)
insert into @tmp_sa_list_mat
SELECT
PART_NO2
, PART_NO
, sum(cunsumtion )
FROM @tmp_sa_list_item_bom
group by PART_NO2,PART_NO
declare @tmp_bb table(
urut bigint identity(1,1)
,urut_mat bigint
,CAR varchar(255)
,BC23No varchar(255)
,BC23Tg date
,part_name_customs varchar(255)
,kode_barang varchar(255)
,uom varchar(255)
,part_no varchar(255)
,part_no2 varchar(255)
,seri_doc int default(-1)
,sisa_sum decimal(38,4)
,doc_in_date date
,doc_in_from_id int
,harga decimal(38,6)
,qty_in decimal(38,4)
,harga_satuan decimal(38,6)
,sisa_qty decimal(38,4)
,pakai_qty decimal(38,4)
,KdKpbc varchar(255)
,typedok varchar(255)
,is_local bit
,KdVal varchar(255)
,ndpbm decimal(38,4)
,id_data_in_dtl bigint
,id_data_in_hdr bigint
,id_bc_in_hdr bigint
, HS_CODE varchar(25)
, BM decimal(18,2)
, PPH decimal(18,2)
, PPN decimal(18,2)
, primary key (urut_mat,part_no2)
)
insert into @tmp_bb(
urut_mat
,id_data_in_dtl
,id_data_in_hdr
,id_bc_in_hdr
,part_name_customs
,kode_barang
,uom
,sisa_sum
,doc_in_date
,doc_in_from_id
,harga
,qty_in
,harga_satuan
,sisa_qty
,pakai_qty
,part_no
,part_no2
,KdKpbc
,typedok
,is_local
,CAR
,BC23No
,BC23Tg
,KdVal
,ndpbm
, HS_CODE
, BM
, PPH
, PPN
)
select
row_number() over(partition by vw_sa_data_detail_in_for_suppy.part_no2 order by doc_in_date asc) as rn
,vw_sa_data_detail_in_for_suppy.id_data_in_dtl
, vw_sa_data_detail_in_for_suppy.id_data_in_hdr
, vw_sa_data_detail_in_for_suppy.id_bc_in_hdr
,tbl_data_part_hs.part_name_customs
,tbl_data_part_hs.kode_barang
,vw_sa_data_detail_in_for_suppy.unit_in
,SUM(sisa) OVER (PARTITION BY vw_sa_data_detail_in_for_suppy.part_no2 ORDER BY vw_sa_data_detail_in_for_suppy.doc_in_date ROWS UNBOUNDED PRECEDING ) as sisa_sum
, vw_sa_data_detail_in_for_suppy.doc_in_date
, vw_sa_data_detail_in_for_suppy.doc_in_from_id
--, vw_sa_data_detail_in_for_suppy.harga_satuan * vw_sa_data_detail_in_for_suppy.qty_in
, (case when is_local = 0 and vw_sa_data_detail_in_for_suppy.[bc_in_from_in] = 'INV HIB' then vw_sa_data_detail_in_for_suppy.harga_satuan/1000 * vw_sa_data_detail_in_for_suppy.qty_in else vw_sa_data_detail_in_for_suppy.harga_satuan * vw_sa_data_detail_in_for_suppy.qty_in end)
, vw_sa_data_detail_in_for_suppy.qty_in
--, vw_sa_data_detail_in_for_suppy.harga_satuan
, (case when is_local = 0 and vw_sa_data_detail_in_for_suppy.[bc_in_from_in] = 'INV HIB' then vw_sa_data_detail_in_for_suppy.harga_satuan/1000 else vw_sa_data_detail_in_for_suppy.harga_satuan end)
, vw_sa_data_detail_in_for_suppy.sisa
, vw_sa_data_detail_in_for_suppy.sisa
, vw_sa_data_detail_in_for_suppy.part_no
, vw_sa_data_detail_in_for_suppy.part_no2
, vw_sa_data_detail_in_for_suppy.KdKpbc
, vw_sa_data_detail_in_for_suppy.typedok
, vw_sa_data_detail_in_for_suppy.is_local
, vw_sa_data_detail_in_for_suppy.CAR
, vw_sa_data_detail_in_for_suppy.BC23No
, vw_sa_data_detail_in_for_suppy.BC23Tg
, vw_sa_data_detail_in_for_suppy.KdVal
, 1
, tbl_data_part_hs.hs_code
, tbl_data_part_hs.bm
, 2.5
, 10
FROM vw_sa_data_detail_in_for_suppy INNER JOIN
tbl_data_part_hs ON tbl_data_part_hs.part_no2 = vw_sa_data_detail_in_for_suppy.part_no2
where
(vw_sa_data_detail_in_for_suppy.doc_in_date BETWEEN '2017-04-01' AND @Tgl_keluar)
AND EXISTS(
select * from @tmp_sa_list_mat as filter
where vw_sa_data_detail_in_for_suppy.part_no=filter.mat_code_fix
)
and sisa>0
order by part_no2,doc_in_date
declare @mat_code_fix varchar(255)
declare @mat_total_req decimal(38,4)
DECLARE CursorName CURSOR FAST_FORWARD
FOR
select mat_code_fix,qty from @tmp_sa_list_mat
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @mat_code_fix,@mat_total_req
WHILE @@FETCH_STATUS = 0
BEGIN
declare @sisa_sum decimal(38,4)
declare @sisa_qty decimal(38,4)
declare @olah_pakai bit
declare @urut_mat bigint
select top (1)
@urut_mat=urut_mat
, @sisa_sum=sisa_sum
, @sisa_qty=sisa_qty
, @olah_pakai =case when sisa_sum=@mat_total_req then 0 else 1 end
from @tmp_bb where part_no2= @mat_code_fix and sisa_sum>=@mat_total_req order by urut_mat
if @urut_mat is not null
begin
delete from @tmp_bb where urut_mat >@urut_mat and part_no2= @mat_code_fix
if @olah_pakai =1
begin
update @tmp_bb
set pakai_qty=(@mat_total_req + @sisa_qty )-@sisa_sum
where urut_mat =@urut_mat and part_no2= @mat_code_fix
end
end
FETCH NEXT FROM CursorName
INTO @mat_code_fix,@mat_total_req
END
CLOSE CursorName
DEALLOCATE CursorName
insert into tbl_data_in_pakai_tmp_sa(id_sa, id_data_in_dtl, qty_pakai)
select
@id_sa_hdr
,id_data_in_dtl
,pakai_qty
from @tmp_bb
update @tmp_bb
set ndpbm = case when kdval ='IDR' then ndpbm else dbo.get_rate_valuta(BC23Tg,kdval) end
,seri_doc =isnull(
( SELECT top 1 Serial
FROM tbl_bc_in_dtl
WHERE (id_bc_in_hdr = [@tmp_bb].id_bc_in_hdr) AND (BrgUrai = [@tmp_bb].part_name_customs ) AND (KdBrg = [@tmp_bb].kode_barang )),-1)
update @tmp_bb
set seri_doc =isnull(( SELECT top 1 Serial
FROM tbl_bc_in_dtl
WHERE (id_bc_in_hdr = [@tmp_bb].id_bc_in_hdr) AND (KdBrg = [@tmp_bb].kode_barang )),1)
where seri_doc=-1
declare @cur_ASSY_CODE varchar(255)
, @cur_PART_NO varchar(255)
, @cur_PART_NO2 varchar(255)
, @cur_BM decimal(18,2)
, @cur_id_sa bigint
, @cur_cunsumtion decimal(18,4)
IF CURSOR_STATUS('global','loop_bom_assy')>=-1
BEGIN
DEALLOCATE loop_bom_assy
END
declare @tmp_bb_assy2 table(
urut bigint identity(1,1)
,bb_urut bigint
,sisa decimal(38,4)
,pakai_qty decimal(38,4)
,sum_sisa decimal(38,4)
)
declare @tmp_bb_assy table(
urut bigint identity(1,1)
,bb_urut bigint
,ASSC_CODE varchar(255)
,PART_NO2 varchar(255)
,BARANG_BM decimal(18,2)
,pakai_qty decimal(38,4)
,id_sa bigint
, primary key (bb_urut,ASSC_CODE)
)
Declare loop_bom_assy CURSOR FOR
select * from @tmp_sa_list_item_bom
OPEN loop_bom_assy
FETCH NEXT FROM loop_bom_assy INTO @cur_ASSY_CODE
, @cur_PART_NO
, @cur_PART_NO2
, @cur_BM
, @cur_id_sa
, @cur_cunsumtion
WHILE @@FETCH_STATUS = 0
BEGIN
delete from @tmp_bb_assy2;
insert into @tmp_bb_assy2 (
bb_urut
,sisa
,pakai_qty
,sum_sisa )
SELECT
tmp_bb.urut
,tmp_bb.pakai_qty-isnull(tmp_bom_assy.total_pakai,0)
,tmp_bb.pakai_qty-isnull(tmp_bom_assy.total_pakai,0)
,SUM(tmp_bb.pakai_qty-isnull(tmp_bom_assy.total_pakai,0)) OVER ( ORDER BY urut_mat ROWS UNBOUNDED PRECEDING ) as sisa_sum
FROM
@tmp_bb AS tmp_bb LEFT OUTER JOIN
(SELECT bb_urut, sum(pakai_qty)as total_pakai from @tmp_bb_assy group by bb_urut
)
AS tmp_bom_assy ON tmp_bb.urut = tmp_bom_assy.bb_urut
where tmp_bb.pakai_qty-isnull(tmp_bom_assy.total_pakai,0)>0 and tmp_bb.part_no2 =@cur_PART_NO2
order by urut_mat
declare @bom_sisa_sum decimal(38,4)
declare @bom_sisa_qty decimal(38,4)
declare @bom_olah_pakai bit
declare @bom_urut bigint
select top (1)
@bom_urut=urut
, @bom_sisa_sum=sum_sisa
, @bom_sisa_qty=sisa
, @bom_olah_pakai =case when sum_sisa=@cur_cunsumtion then 0 else 1 end
from @tmp_bb_assy2 where sum_sisa>=@cur_cunsumtion order by urut
if @urut_mat is not null
begin
delete from @tmp_bb_assy2 where urut >@bom_urut
if @bom_olah_pakai =1
begin
update @tmp_bb_assy2
set pakai_qty=(@cur_cunsumtion + @bom_sisa_qty )-@bom_sisa_sum
where urut =@bom_urut
end
end
insert into @tmp_bb_assy(
bb_urut
,ASSC_CODE
,PART_NO2
,BARANG_BM
,pakai_qty
,id_sa
)
select bb_urut, @cur_ASSY_CODE, @cur_PART_NO2,@cur_BM,pakai_qty,@cur_id_sa from @tmp_bb_assy2
FETCH NEXT FROM loop_bom_assy INTO @cur_ASSY_CODE
, @cur_PART_NO
, @cur_PART_NO2
,@cur_BM
, @cur_id_sa
, @cur_cunsumtion
END
CLOSE loop_bom_assy
DEALLOCATE loop_bom_assy
insert into tbl_sa_bom (
id_bc_in_hdr
, id_bc_in_dtl
, id_data_in_hdr
,id_data_in_dtl
,id_sa
,id_sa_hdr
,SA_NO,KODE_BARANG
,URAIAN,JUMLAH_SATUAN
,JENIS_SATUAN
,CURR,PRICE
,NDPMB
,NDPDM_NOW
,NOMOR_DAFTAR_DOK_ASAL
,TANGGAL_DAFTAR_DOK_ASAL
,ASSY_NO
,KODE_JENIS_DOK_ASAL
,KODE_KANTOR
,NOMOR_AJU_DOK_ASAL
,KODE_ASAL_BAHAN_BAKU, HS, BM, PPN, PPH, CIF, HARGA_PABEAN, BM_RP, PPN_RP, PPH_RP, SERI_BAHAN_BAKU_ASAL,PART_NO,SERI_BAHAN_BAKU )
select
b.id_bc_in_hdr
,-1
, b.id_data_in_hdr
,b.id_data_in_dtl
,c.id_sa
,@id_sa_hdr
,@SA_NO
,b.KODE_BARANG
,b.part_name_customs
,c.pakai_qty
,b.uom
,b.kdval
,(b.harga_satuan ) * c.pakai_qty
,b.ndpbm
,@ndpbm_now
,b.BC23No
,b.BC23Tg
,c.ASSC_CODE
,b.typedok
,b.Kdkpbc
,b.CAR
,b.is_local -- KODE_ASAL_BAHAN_BAKU
,b.hs_code
,b.bm
,b.PPN
,b.PPH
,cast((b.harga_satuan ) * c.pakai_qty as decimal(38,4))-- CIF
,cast((b.harga_satuan * b.ndpbm) * c.pakai_qty as decimal(38,4)) --HARGA_PABEAN
,cast(((((b.harga_satuan * b.ndpbm) * c.pakai_qty) * (case when c.BARANG_BM > b.BM then c.BARANG_BM else b.BM end )) /100 ) as decimal(38,4)) -- BM_RP
,cast(((((((b.harga_satuan * b.ndpbm) * c.pakai_qty) * (case when c.BARANG_BM > b.BM then c.BARANG_BM else b.BM end )) /100 ) + ((b.harga_satuan * b.ndpbm) * c.pakai_qty)) * b.PPN )/100 as decimal(38,4))
,cast(((((((b.harga_satuan * b.ndpbm) * c.pakai_qty) * (case when c.BARANG_BM > b.BM then c.BARANG_BM else b.BM end )) /100 ) + ((b.harga_satuan * b.ndpbm) * c.pakai_qty)) * b.PPH )/100 as decimal(38,4))
, b.seri_doc
, c.PART_NO2
,row_number() over(partition by c.ASSC_CODE order by b.is_local,b.typedok,c.PART_NO2 asc) as rn
from @tmp_bb_assy as c inner join @tmp_bb as b
on b.urut =c.bb_urut
order by c.urut
GO
GO
CREATE procedure dbo.sp_bahan_baku_asal_2
@id_sa_hdr bigint
,@SA_NO varchar(255)
as
declare @Tgl_keluar date = getdate()
declare @ndpbm_now decimal(18,2)
set @ndpbm_now = dbo.get_rate_valuta(GETDATE(),'USD')
select Top 1 @Tgl_keluar = TgDok from tbl_sa_dok where id_sa_hdr = @id_sa_hdr order by TgDok ASC
declare @tmp_sa_list_item
table (
id_sa bigint
, ASSY_CODE varchar(255) primary key
, total decimal(18,4)
, HS_CODE varchar(255)
, BM decimal(18,2)
, have_formula bit
)
insert into @tmp_sa_list_item (id_sa,ASSY_CODE,total,HS_CODE,BM ,have_formula)
select a.id_sa, a.ASSY_CODE, sum(a.TOTAL_SET ) as total, '85443019',5, case when c.total >0 then 1 else 0 end from tbl_sa a
left outer join vw_tbl_bom_hdr_check as c on a.ASSY_CODE=c.ASSY_CODE
Left Join tbl_sa_hdr b ON a.id_sa_hdr = b.id_sa_hdr
where b.id_sa_hdr = @id_sa_hdr
group by a.id_sa,a.ASSY_CODE,case when c.total >0 then 1 else 0 end
declare @rumus_assy varchar(max)= (
STUFF((
SELECT ', ''' + ASSY_CODE + ''''
FROM @tmp_sa_list_item
where have_formula=0
for xml PATH(''), type
).value('.', 'varchar(max)'), 1, 1, '')
)
Declare @tmp_bom_dtl TABLE(
ASSY_CODE varchar(255),
PART_NO varchar(255) NULL,
QTY decimal(38,4) NULL,
PARTNO2 varchar(255) ,
ASSY_NO varchar(255) NULL,
FLAG varchar(255) NULL,
STATUS varchar(255) NULL,
primary key(ASSY_CODE,PARTNO2)
)
if @rumus_assy is not null
begin
declare @TSQL as Nvarchar(max)
SELECT @TSQL = 'SELECT ASSY_CODE, NULL, ''FROMORACC'', 0, GETDATE() FROM OPENQUERY(ORACC,''SELECT * FROM PARTLIST_VIEW WHERE ASSY_CODE in (' + @rumus_assy + ')) GROUP BY ASSY_CODE'
insert into dbo.tbl_bom_hdr ( ASSY_CODE, ASSY_NO, filename, itemcount, dateexec)
EXEC (@TSQL)
SELECT @TSQL = 'SELECT *, replace(PART_NO,'' '','''') AS PART_NO2 FROM OPENQUERY(ORACC,''SELECT * FROM PARTLIST_VIEW WHERE PART_NO is not null and (ASSY_CODE in (' + @rumus_assy + ')))'
insert into @tmp_bom_dtl ( ASSY_CODE, PART_NO, QTY, PARTNO2)
EXEC (@TSQL)
MERGE dbo.tbl_bom_dtl AS TARGET
USING @tmp_bom_dtl AS SOURCE
ON ( SOURCE.ASSY_CODE = TARGET.ASSY_CODE and SOURCE.PARTNO2 = TARGET.PARTNO2 )
WHEN MATCHED
THEN
UPDATE set
TARGET.QTY = SOURCE.QTY
,TARGET.status_bom = (case when (select count(*) from dbo.tbl_data_part_hs where part_no2=SOURCE.PARTNO2 ) = 0 then 'not found part no' else 'ok' end)
WHEN NOT MATCHED BY TARGET THEN
INSERT (
ASSY_CODE, ASSY_NO,PART_NO, QTY, PARTNO2, FLAG, STATUS
,status_bom
) VALUES (
SOURCE.ASSY_CODE, SOURCE.ASSY_NO,SOURCE.PART_NO, SOURCE.QTY, SOURCE.PARTNO2, SOURCE.FLAG, SOURCE.STATUS
, (case when (select count(*) from dbo.tbl_data_part_hs where part_no2=SOURCE.PARTNO2 ) = 0 then 'not found part no' else 'ok' end)
);
update tbl_bom_hdr
set itemcount =isnull((select count(*) from tbl_bom_dtl where ASSY_CODE = tbl_bom_hdr.ASSY_CODE),0)
where exists(
select * from @tmp_bom_dtl as filter where tbl_bom_hdr.ASSY_CODE=filter.ASSY_CODE
)
end
declare @tmp_sa_list_item_bom table (
ASSY_CODE varchar(255)
, PART_NO varchar(255)
, PART_NO2 varchar(255)
, BM_BARANG decimal(18,2)
, id_sa bigint
, cunsumtion decimal(18,4)
primary key(ASSY_CODE,PART_NO2)
)
insert into @tmp_sa_list_item_bom
(ASSY_CODE,PART_NO,PART_NO2,BM_BARANG,id_sa,cunsumtion)
select tbl_bom_dtl.ASSY_CODE
, tbl_bom_dtl.PART_NO
, replace(tbl_bom_dtl.PARTNO2,' ','')
,data_item.BM
,data_item.id_sa
, tbl_bom_dtl.QTY * data_item.total
FROM tbl_bom_dtl INNER JOIN
-- (SELECT tbl_bom_dtl.ASSY_CODE)
@tmp_sa_list_item AS data_item ON tbl_bom_dtl.ASSY_CODE = data_item.ASSY_CODE
declare @tmp_sa_list_mat
table (
mat_code_fix varchar(255) primary key
, mat_code varchar(255)
, qty decimal(24,4)
)
insert into @tmp_sa_list_mat
SELECT
PART_NO2
, PART_NO
, sum(cunsumtion )
FROM @tmp_sa_list_item_bom
group by PART_NO2,PART_NO
declare @tmp_bb table(
urut bigint identity(1,1)
,urut_mat bigint
,CAR varchar(255)
,BC23No varchar(255)
,BC23Tg date
,part_name_customs varchar(255)
,kode_barang varchar(255)
,uom varchar(255)
,part_no varchar(255)
,part_no2 varchar(255)
,seri_doc int default(-1)
,sisa_sum decimal(38,4)
,doc_in_date date
,doc_in_from_id int
,harga decimal(38,6)
,qty_in decimal(38,4)
,harga_satuan decimal(38,6)
,sisa_qty decimal(38,4)
,pakai_qty decimal(38,4)
,KdKpbc varchar(255)
,typedok varchar(255)
,is_local bit
,KdVal varchar(255)
,ndpbm decimal(38,4)
,id_data_in_dtl bigint
,id_data_in_hdr bigint
,id_bc_in_hdr bigint
, HS_CODE varchar(25)
, BM decimal(18,2)
, PPH decimal(18,2)
, PPN decimal(18,2)
, primary key (urut_mat,part_no2)
)
insert into @tmp_bb(
urut_mat
,id_data_in_dtl
,id_data_in_hdr
,id_bc_in_hdr
,part_name_customs
,kode_barang
,uom
,sisa_sum
,doc_in_date
,doc_in_from_id
,harga
,qty_in
,harga_satuan
,sisa_qty
,pakai_qty
,part_no
,part_no2
,KdKpbc
,typedok
,is_local
,CAR
,BC23No
,BC23Tg
,KdVal
,ndpbm
, HS_CODE
, BM
, PPH
, PPN
)
select
row_number() over(partition by vw_sa_data_detail_in_for_suppy.part_no2 order by doc_in_date asc) as rn
,vw_sa_data_detail_in_for_suppy.id_data_in_dtl
, vw_sa_data_detail_in_for_suppy.id_data_in_hdr
, vw_sa_data_detail_in_for_suppy.id_bc_in_hdr
,tbl_data_part_hs.part_name_customs
,tbl_data_part_hs.kode_barang
,vw_sa_data_detail_in_for_suppy.unit_in
,SUM(sisa) OVER (PARTITION BY vw_sa_data_detail_in_for_suppy.part_no2 ORDER BY vw_sa_data_detail_in_for_suppy.doc_in_date ROWS UNBOUNDED PRECEDING ) as sisa_sum
, vw_sa_data_detail_in_for_suppy.doc_in_date
, vw_sa_data_detail_in_for_suppy.doc_in_from_id
--, vw_sa_data_detail_in_for_suppy.harga_satuan * vw_sa_data_detail_in_for_suppy.qty_in
, (case when is_local = 0 and vw_sa_data_detail_in_for_suppy.[bc_in_from_in] = 'INV HIB' then vw_sa_data_detail_in_for_suppy.harga_satuan/1000 * vw_sa_data_detail_in_for_suppy.qty_in else vw_sa_data_detail_in_for_suppy.harga_satuan * vw_sa_data_detail_in_for_suppy.qty_in end)
, vw_sa_data_detail_in_for_suppy.qty_in
--, vw_sa_data_detail_in_for_suppy.harga_satuan
, (case when is_local = 0 and vw_sa_data_detail_in_for_suppy.[bc_in_from_in] = 'INV HIB' then vw_sa_data_detail_in_for_suppy.harga_satuan/1000 else vw_sa_data_detail_in_for_suppy.harga_satuan end)
, vw_sa_data_detail_in_for_suppy.sisa
, vw_sa_data_detail_in_for_suppy.sisa
, vw_sa_data_detail_in_for_suppy.part_no
, vw_sa_data_detail_in_for_suppy.part_no2
, vw_sa_data_detail_in_for_suppy.KdKpbc
, vw_sa_data_detail_in_for_suppy.typedok
, vw_sa_data_detail_in_for_suppy.is_local
, vw_sa_data_detail_in_for_suppy.CAR
, vw_sa_data_detail_in_for_suppy.BC23No
, vw_sa_data_detail_in_for_suppy.BC23Tg
, vw_sa_data_detail_in_for_suppy.KdVal
, 1
, tbl_data_part_hs.hs_code
, tbl_data_part_hs.bm
, 2.5
, 10
FROM vw_sa_data_detail_in_for_suppy INNER JOIN
tbl_data_part_hs ON tbl_data_part_hs.part_no2 = vw_sa_data_detail_in_for_suppy.part_no2
where
(vw_sa_data_detail_in_for_suppy.doc_in_date BETWEEN '2017-04-01' AND @Tgl_keluar)
AND EXISTS(
select * from @tmp_sa_list_mat as filter
where vw_sa_data_detail_in_for_suppy.part_no=filter.mat_code_fix
)
and sisa>0
order by part_no2,doc_in_date
declare @mat_code_fix varchar(255)
declare @mat_total_req decimal(38,4)
DECLARE CursorName CURSOR FAST_FORWARD
FOR
select mat_code_fix,qty from @tmp_sa_list_mat
OPEN CursorName
FETCH NEXT FROM CursorName
INTO @mat_code_fix,@mat_total_req
WHILE @@FETCH_STATUS = 0
BEGIN
declare @sisa_sum decimal(38,4)
declare @sisa_qty decimal(38,4)
declare @olah_pakai bit
declare @urut_mat bigint
select top (1)
@urut_mat=urut_mat
, @sisa_sum=sisa_sum
, @sisa_qty=sisa_qty
, @olah_pakai =case when sisa_sum=@mat_total_req then 0 else 1 end
from @tmp_bb where part_no2= @mat_code_fix and sisa_sum>=@mat_total_req order by urut_mat
if @urut_mat is not null
begin
delete from @tmp_bb where urut_mat >@urut_mat and part_no2= @mat_code_fix
if @olah_pakai =1
begin
update @tmp_bb
set pakai_qty=(@mat_total_req + @sisa_qty )-@sisa_sum
where urut_mat =@urut_mat and part_no2= @mat_code_fix
end
end
FETCH NEXT FROM CursorName
INTO @mat_code_fix,@mat_total_req
END
CLOSE CursorName
DEALLOCATE CursorName
insert into tbl_data_in_pakai_tmp_sa(id_sa, id_data_in_dtl, qty_pakai)
select
@id_sa_hdr
,id_data_in_dtl
,pakai_qty
from @tmp_bb
update @tmp_bb
set ndpbm = case when kdval ='IDR' then ndpbm else dbo.get_rate_valuta(BC23Tg,kdval) end
,seri_doc =isnull(
( SELECT top 1 Serial
FROM tbl_bc_in_dtl
WHERE (id_bc_in_hdr = [@tmp_bb].id_bc_in_hdr) AND (BrgUrai = [@tmp_bb].part_name_customs ) AND (KdBrg = [@tmp_bb].kode_barang )),-1)
update @tmp_bb
set seri_doc =isnull(( SELECT top 1 Serial
FROM tbl_bc_in_dtl
WHERE (id_bc_in_hdr = [@tmp_bb].id_bc_in_hdr) AND (KdBrg = [@tmp_bb].kode_barang )),1)
where seri_doc=-1
declare @cur_ASSY_CODE varchar(255)
, @cur_PART_NO varchar(255)
, @cur_PART_NO2 varchar(255)
, @cur_BM decimal(18,2)
, @cur_id_sa bigint
, @cur_cunsumtion decimal(18,4)
IF CURSOR_STATUS('global','loop_bom_assy')>=-1
BEGIN
DEALLOCATE loop_bom_assy
END
declare @tmp_bb_assy2 table(
urut bigint identity(1,1)
,bb_urut bigint
,sisa decimal(38,4)
,pakai_qty decimal(38,4)
,sum_sisa decimal(38,4)
)
declare @tmp_bb_assy table(
urut bigint identity(1,1)
,bb_urut bigint
,ASSC_CODE varchar(255)
,PART_NO2 varchar(255)
,BARANG_BM decimal(18,2)
,pakai_qty decimal(38,4)
,id_sa bigint
, primary key (bb_urut,ASSC_CODE)
)
Declare loop_bom_assy CURSOR FOR
select * from @tmp_sa_list_item_bom
OPEN loop_bom_assy
FETCH NEXT FROM loop_bom_assy INTO @cur_ASSY_CODE
, @cur_PART_NO
, @cur_PART_NO2
, @cur_BM
, @cur_id_sa
, @cur_cunsumtion
WHILE @@FETCH_STATUS = 0
BEGIN
delete from @tmp_bb_assy2;
insert into @tmp_bb_assy2 (
bb_urut
,sisa
,pakai_qty
,sum_sisa )
SELECT
tmp_bb.urut
,tmp_bb.pakai_qty-isnull(tmp_bom_assy.total_pakai,0)
,tmp_bb.pakai_qty-isnull(tmp_bom_assy.total_pakai,0)
,SUM(tmp_bb.pakai_qty-isnull(tmp_bom_assy.total_pakai,0)) OVER ( ORDER BY urut_mat ROWS UNBOUNDED PRECEDING ) as sisa_sum
FROM
@tmp_bb AS tmp_bb LEFT OUTER JOIN
(SELECT bb_urut, sum(pakai_qty)as total_pakai from @tmp_bb_assy group by bb_urut
)
AS tmp_bom_assy ON tmp_bb.urut = tmp_bom_assy.bb_urut
where tmp_bb.pakai_qty-isnull(tmp_bom_assy.total_pakai,0)>0 and tmp_bb.part_no2 =@cur_PART_NO2
order by urut_mat
declare @bom_sisa_sum decimal(38,4)
declare @bom_sisa_qty decimal(38,4)
declare @bom_olah_pakai bit
declare @bom_urut bigint
select top (1)
@bom_urut=urut
, @bom_sisa_sum=sum_sisa
, @bom_sisa_qty=sisa
, @bom_olah_pakai =case when sum_sisa=@cur_cunsumtion then 0 else 1 end
from @tmp_bb_assy2 where sum_sisa>=@cur_cunsumtion order by urut
if @urut_mat is not null
begin
delete from @tmp_bb_assy2 where urut >@bom_urut
if @bom_olah_pakai =1
begin
update @tmp_bb_assy2
set pakai_qty=(@cur_cunsumtion + @bom_sisa_qty )-@bom_sisa_sum
where urut =@bom_urut
end
end
insert into @tmp_bb_assy(
bb_urut
,ASSC_CODE
,PART_NO2
,BARANG_BM
,pakai_qty
,id_sa
)
select bb_urut, @cur_ASSY_CODE, @cur_PART_NO2,@cur_BM,pakai_qty,@cur_id_sa from @tmp_bb_assy2
FETCH NEXT FROM loop_bom_assy INTO @cur_ASSY_CODE
, @cur_PART_NO
, @cur_PART_NO2
,@cur_BM
, @cur_id_sa
, @cur_cunsumtion
END
CLOSE loop_bom_assy
DEALLOCATE loop_bom_assy
insert into tbl_sa_bom (
id_bc_in_hdr
, id_bc_in_dtl
, id_data_in_hdr
,id_data_in_dtl
,id_sa
,id_sa_hdr
,SA_NO,KODE_BARANG
,URAIAN,JUMLAH_SATUAN
,JENIS_SATUAN
,CURR,PRICE
,NDPMB
,NDPDM_NOW
,NOMOR_DAFTAR_DOK_ASAL
,TANGGAL_DAFTAR_DOK_ASAL
,ASSY_NO
,KODE_JENIS_DOK_ASAL
,KODE_KANTOR
,NOMOR_AJU_DOK_ASAL
,KODE_ASAL_BAHAN_BAKU, HS, BM, PPN, PPH, CIF, HARGA_PABEAN, BM_RP, PPN_RP, PPH_RP, SERI_BAHAN_BAKU_ASAL,PART_NO,SERI_BAHAN_BAKU )
select
b.id_bc_in_hdr
,-1
, b.id_data_in_hdr
,b.id_data_in_dtl
,c.id_sa
,@id_sa_hdr
,@SA_NO
,b.KODE_BARANG
,b.part_name_customs
,c.pakai_qty
,b.uom
,b.kdval
,(b.harga_satuan ) * c.pakai_qty
,b.ndpbm
,@ndpbm_now
,b.BC23No
,b.BC23Tg
,c.ASSC_CODE
,b.typedok
,b.Kdkpbc
,b.CAR
,b.is_local -- KODE_ASAL_BAHAN_BAKU
,b.hs_code
,b.bm
,b.PPN
,b.PPH
,cast((b.harga_satuan ) * c.pakai_qty as decimal(38,4))-- CIF
,cast((b.harga_satuan * b.ndpbm) * c.pakai_qty as decimal(38,4)) --HARGA_PABEAN
,cast(((((b.harga_satuan * b.ndpbm) * c.pakai_qty) * (case when c.BARANG_BM > b.BM then c.BARANG_BM else b.BM end )) /100 ) as decimal(38,4)) -- BM_RP
,cast(((((((b.harga_satuan * b.ndpbm) * c.pakai_qty) * (case when c.BARANG_BM > b.BM then c.BARANG_BM else b.BM end )) /100 ) + ((b.harga_satuan * b.ndpbm) * c.pakai_qty)) * b.PPN )/100 as decimal(38,4))
,cast(((((((b.harga_satuan * b.ndpbm) * c.pakai_qty) * (case when c.BARANG_BM > b.BM then c.BARANG_BM else b.BM end )) /100 ) + ((b.harga_satuan * b.ndpbm) * c.pakai_qty)) * b.PPH )/100 as decimal(38,4))
, b.seri_doc
, c.PART_NO2
,row_number() over(partition by c.ASSC_CODE order by b.is_local,b.typedok,c.PART_NO2 asc) as rn
from @tmp_bb_assy as c inner join @tmp_bb as b
on b.urut =c.bb_urut
order by c.urut
GO