Ratakan

Informations :

Copy Item Class Setup from TWO to DEMO Database

Window yang akan di copy dari database TWO ke DEMO adalah Item Class Setup


Terlebih dahulu lakukan copy UofM Schedule

Link disini :

Copy UofM Schedule Setup from TWO to DEMO Database


Sintax Insert SQL :

insert into DEMO..IV40400 ( 
ITMCLSCD ,
ITMCLSDC ,
DEFLTCLS ,
ITEMTYPE ,
ITMTRKOP ,
LOTTYPE ,
KPERHIST ,
KPTRXHST ,
KPCALHST ,
KPDSTHST ,
ALWBKORD ,
ITMGEDSC ,
TAXOPTNS ,
ITMTSHID ,
Purchase_Tax_Options ,
Purchase_Item_Tax_Schedu ,
UOMSCHDL ,
VCTNMTHD ,
USCATVLS_1 ,
USCATVLS_2 ,
USCATVLS_3 ,
USCATVLS_4 ,
USCATVLS_5 ,
USCATVLS_6 ,
DECPLQTY ,
IVIVINDX ,
IVIVOFIX ,
IVCOGSIX ,
IVSLSIDX ,
IVSLDSIX ,
IVSLRNIX ,
IVINUSIX ,
IVINSVIX ,
IVDMGIDX ,
IVVARIDX ,
DPSHPIDX ,
PURPVIDX ,
UPPVIDX ,
IVRETIDX ,
ASMVRIDX ,
PRCLEVEL ,
PriceGroup ,
PRICMTHD ,
TCC ,
Revalue_Inventory ,
Tolerance_Percentage ,
CNTRYORGN ,
STTSTCLVLPRCNTG ,
INCLUDEINDP ,
LOTEXPWARN ,
LOTEXPWARNDAYS ,
UseQtyOverageTolerance ,
UseQtyShortageTolerance ,
QtyOverTolerancePercent ,
QtyShortTolerancePercent ,
IVSCRVIX )
select  
ITMCLSCD ,
ITMCLSDC ,
DEFLTCLS ,
ITEMTYPE ,
ITMTRKOP ,
LOTTYPE ,
KPERHIST ,
KPTRXHST ,
KPCALHST ,
KPDSTHST ,
ALWBKORD ,
ITMGEDSC ,
TAXOPTNS ,
ITMTSHID ,
Purchase_Tax_Options ,
Purchase_Item_Tax_Schedu ,
UOMSCHDL ,
VCTNMTHD ,
USCATVLS_1 ,
USCATVLS_2 ,
USCATVLS_3 ,
USCATVLS_4 ,
USCATVLS_5 ,
USCATVLS_6 ,
DECPLQTY ,
IVIVINDX ,
IVIVOFIX ,
IVCOGSIX ,
IVSLSIDX ,
IVSLDSIX ,
IVSLRNIX ,
IVINUSIX ,
IVINSVIX ,
IVDMGIDX ,
IVVARIDX ,
DPSHPIDX ,
PURPVIDX ,
UPPVIDX ,
IVRETIDX ,
ASMVRIDX ,
PRCLEVEL ,
PriceGroup ,
PRICMTHD ,
TCC ,
Revalue_Inventory ,
Tolerance_Percentage ,
CNTRYORGN ,
STTSTCLVLPRCNTG ,
INCLUDEINDP ,
LOTEXPWARN ,
LOTEXPWARNDAYS ,
UseQtyOverageTolerance ,
UseQtyShortageTolerance ,
QtyOverTolerancePercent ,
QtyShortTolerancePercent ,
IVSCRVIX 
from TWO..IV40400



Sintax Update Account Mapping SQL antara database TWO dengan DEMO :

update a
set a.IVIVINDX = b.ACTINDX_DEMO,
a.IVIVOFIX = c.ACTINDX_DEMO,
a.IVCOGSIX = d.ACTINDX_DEMO, 
a.IVSLSIDX = e.ACTINDX_DEMO, 
a.IVSLDSIX = f.ACTINDX_DEMO,
a.IVSLRNIX = g.ACTINDX_DEMO,
a.IVINUSIX = h.ACTINDX_DEMO,
a.IVINSVIX = i.ACTINDX_DEMO,
a.IVDMGIDX = j.ACTINDX_DEMO,
a.IVVARIDX = coalesce(k.ACTINDX_DEMO,0),
a.DPSHPIDX = l.ACTINDX_DEMO,
a.PURPVIDX = m.ACTINDX_DEMO,
a.UPPVIDX = n.ACTINDX_DEMO,
a.IVRETIDX = o.ACTINDX_DEMO,
a.ASMVRIDX = p.ACTINDX_DEMO,
a.IVSCRVIX = q.ACTINDX_DEMO
from DEMO..IV40400 a
inner join ABC_Account_Index_TWO_DEMO b on a.IVIVINDX = b.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO c on a.IVIVOFIX = c.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO d on a.IVCOGSIX = d.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO e on a.IVSLSIDX = e.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO f on a.IVSLDSIX = f.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO g on a.IVSLRNIX = g.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO h on a.IVINUSIX = h.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO i on a.IVINSVIX = i.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO j on a.IVDMGIDX = j.ACTINDX_TWO
left join ABC_Account_Index_TWO_DEMO k on coalesce(a.IVVARIDX,0) = coalesce(k.ACTINDX_TWO,0)
inner join ABC_Account_Index_TWO_DEMO l on a.DPSHPIDX = l.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO m on a.PURPVIDX = m.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO n on a.UPPVIDX = n.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO o on a.IVRETIDX = o.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO p on a.ASMVRIDX = p.ACTINDX_TWO
inner join ABC_Account_Index_TWO_DEMO q on a.IVSCRVIX = q.ACTINDX_TWO

Hasilnya :


Perhatikan sintax Query update dibagian sini dilakukan coalesce dan left join karena ada IVVARIDX TWO yang tidak ada  ACTINDX_TWO hal ini karena adanya index 774 yang mana dalam GP TWO pun Account Not Found

a.IVVARIDX = coalesce(k.ACTINDX_DEMO,0),
left join ABC_Account_Index_TWO_DEMO k on coalesce(a.IVVARIDX,0) = coalesce(k.ACTINDX_TWO,0)












No comments