Ratakan

Informations :

Manufacturing : Jobs Auto Create Item Engineering, Routing Header & Routing Entry

Simulasi dan Query disini untuk membuat Jobs Auto Create Item Engineering, Routing Header & Routing Entry   pada module manufacturing Dynamics GP 


1. Buat Table Tampungan untuk menampung Class ID 

CREATE TABLE ITMCLSCD_LTI (
    ITMCLSCD varchar(255) not null primary key,
    LTI_Label varchar(255),
[DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL)


3. Insert Table Tampungan tadi 

insert into ITMCLSCD_LTI (ITMCLSCD) select distinct ITMCLSCD from IV00101 where ITMCLSCD <> '' 

4. Update Table untuk mengisi Site ID Default 
update ITMCLSCD_LTI set LTI_Label = 'RM-AQ' where DEX_ROW_ID = 1

5. Query untuk Update Item Engineering 

update a
set a.ITEMSTATUS_I = 1,
a.MAKEBUYCODE_I = 2,
a.CALCMRP_I = 1,
a.ISCHANGD = 1,
a.LOCNCODE = y.LTI_Label
from IVR10015 a
inner join (select
a.ITEMNMBR,
a.ITMCLSCD,
b.LTI_Label
from IV00101 a
left join ITMCLSCD_LTI b
on a.ITMCLSCD = b.ITMCLSCD
where LTI_Label <> '') y on a.ITEMNMBR = y.ITEMNMBR




6. Setelah lakukan point 1 sampai 5 maka hasilnya Item Engineering sebagai berikut : 




7. Untuk membuat pengisian data routing header rapi di delete selain Default 
delete from RT010001 where ROUTINGNAME_I <> 'DEFAULT'


8. Setelah itu buat query untuk insert Routing Header 

insert into RT010001
(ROUTINGNAME_I,ITEMNMBR,RTPRIMARY_I,RTSTATUSDDL_I)
select 'DEFAULT',a.ITEMNMBR,1,4 from IV00101 a
where 'DEFAULT'+Rtrim(a.ITEMNMBR) not in (select 'DEFAULT'+Rtrim(ITEMNMBR) from RT010001)
and a.ITMCLSCD in (select distinct ITMCLSCD from ITMCLSCD_LTI)


9. Sampai point 8 hasilnya tampak sebagai berikut : 



10. Sekarang masuk ke bagian routing line, dirapikan dulu dengan delete selain DEFAULT
Query Delete Line Routing 

delete from RT010130 where RTSEQNUM_I <> '1'
delete from RT010130 where ROUTINGNAME_I <> 'DEFAULT'

11. Selanjutnya integrate dengan query insert 

insert into RT010130 (ROUTINGNAME_I,RTSEQNUM_I,ITEMNMBR,WCID_I,USERID,WIPOPPERMOSTARTQTY)
select 'DEFAULT',1,a.ITEMNMBR,b.LTI_Label,'sa',1 from IV00101 a
inner join ITMCLSCD_LTI b on a.ITMCLSCD = b.ITMCLSCD
where 'DEFAULT'+Rtrim(a.ITEMNMBR) not in (select 'DEFAULT'+Rtrim(ITEMNMBR) from RT010130)
and a.ITMCLSCD in (select distinct ITMCLSCD from ITMCLSCD_LTI)
and b.LTI_Label <> ''


12. Sampai sini hasilnya tercipta Routing Line sebagai berikut : 



13. Dari all point maka dapat dibuatkan jobs pada SQL Server dengan isi all query sebagai berikut : 

update a
set a.ITEMSTATUS_I = 1,
a.MAKEBUYCODE_I = 2,
a.CALCMRP_I = 1,
a.ISCHANGD = 1,
a.LOCNCODE = y.LTI_Label
from IVR10015 a
inner join (select 
a.ITEMNMBR,
a.ITMCLSCD,
b.LTI_Label
from IV00101 a
left join ITMCLSCD_LTI b 
on a.ITMCLSCD = b.ITMCLSCD
where LTI_Label <> '') y on a.ITEMNMBR = y.ITEMNMBR
where a.MAKEBUYCODE_I <> 2

go

delete from RT010001 where ROUTINGNAME_I <> 'DEFAULT'

go

insert into RT010001 
(ROUTINGNAME_I,ITEMNMBR,RTPRIMARY_I,RTSTATUSDDL_I)
select 'DEFAULT',a.ITEMNMBR,1,4 from IV00101 a
where 'DEFAULT'+Rtrim(a.ITEMNMBR) not in (select 'DEFAULT'+Rtrim(ITEMNMBR) from RT010001)
and a.ITMCLSCD in (select distinct ITMCLSCD from ITMCLSCD_LTI)


go

insert into RT010130 (ROUTINGNAME_I,RTSEQNUM_I,ITEMNMBR,WCID_I,USERID,WIPOPPERMOSTARTQTY)
select 'DEFAULT',1,a.ITEMNMBR,b.LTI_Label,'sa',1 from IV00101 a
inner join ITMCLSCD_LTI b on a.ITMCLSCD = b.ITMCLSCD
where 'DEFAULT'+Rtrim(a.ITEMNMBR) not in (select 'DEFAULT'+Rtrim(ITEMNMBR) from RT010130)
and a.ITMCLSCD in (select distinct ITMCLSCD from ITMCLSCD_LTI)
and b.LTI_Label <> ''



Demikian simulasi Guide ini Jika Anda menggunakan module Manufacturing Dynamics GP dan mengalami kesulitan silahkan tanyakan pada kami dengan klik Image dibawah ini : 


No comments