IF OBJECT_ID ('dbo.tdd_POP10300','TR') IS NOT NULL
DROP TRIGGER dbo.tdd_POP10300
GO
CREATE TRIGGER dbo.tdd_POP10300
ON POP10300
FOR INSERT,UPDATE
AS
print 'tdd_pop10300'
insert into POP10700 ( POPRCTNM , RCPTLNNM ,LCHDRNUMBER,Landed_Cost_ID ,NOTEINDX,Long_Description ,Landed_Cost_Type,VENDORID ,Vendor_Note_Index,CURNCYID,Currency_Note_Index,CURRNIDX ,RATETPID,EXGTBLID,XCHGRATE,EXCHDATE,TIME1,RATECALC,DENXRATE,MCTRXSTT,DECPLCUR ,ODECPLCU ,ACPURIDX ,DistRef,PURPVIDX ,Invoice_Match ,CALCMTHD ,Orig_Landed_Cost_Amount ,Calculation_Percentage ,Orig_TotalLandedCostAmt ,Landed_Cost_Warnings ,Apportion_By,Orig_UnapportionedAmount,INVINDX ,Applied,Total_Landed_Cost_Amount,LCLINENUMBER)
select pp.poprctnm, pp.RCPTLNNM,'0' ,iv2.Landed_Cost_ID,0 ,iv0.Long_Description,'1' ,iv0.VENDORID,0 ,'Z-US$' ,0 ,1007 ,'' ,'' ,0 ,'' ,'' ,0 ,0 ,0 ,iv0.DECPLCUR + 6,iv0.ODECPLCU,iv0.ACPURIDX,'' ,iv0.PURPVIDX,iv0.Invoice_Match,iv0.CALCMTHD,iv0.Orig_Landed_Cost_Amount,iv0.Calculation_Percentage,iv0.Orig_Landed_Cost_Amount,pp.Landed_Cost_Warnings ,4 ,0 ,0 ,0 ,
case
--percent of extended cost
when iv0.CALCMTHD = 1 then round((convert(numeric(19,5),iv0.Calculation_Percentage)/100000) * pp.EXTDCOST,2)
--flat amount
when iv0.CALCMTHD = 2 then iv0.Orig_Landed_Cost_Amount
--flat amount per unit
when iv0.CALCMTHD = 3 then iv0.Orig_Landed_Cost_Amount * (case rlq.APPYTYPE when 1 then rlq.QTYSHPPD else rlq.QTYINVCD end)
--??
when iv0.CALCMTHD = 4 then 0
else 0 end as 'Total_Landed_Cost_Amount',
ROW_NUMBER() over (partition by pp.poprctnm, pp.RCPTLNNM order by pp.poprctnm, pp.RCPTLNNM) * 16384 as LCLINENUMBER
from POP10310 pp
join INSERTED r on r.POPRCTNM = pp.POPRCTNM
join IV00102 iq on iq.ITEMNMBR = pp.ITEMNMBR and iq.LOCNCODE = pp.LOCNCODE
join IV41101 iv1 on iv1.Landed_Cost_Group_ID = iq.Landed_Cost_Group_ID
join IV41102 iv2 on iv1.Landed_Cost_Group_ID = iv2.Landed_Cost_Group_ID
join IV41100 iv0 on iv0.Landed_Cost_ID = iv2.Landed_Cost_ID
left join POP10700 lc on lc.POPRCTNM = pp.POPRCTNM and lc.RCPTLNNM = pp.RCPTLNNM
join POP10500 rlq on rlq.POPRCTNM = pp.POPRCTNM and rlq.RCPTLNNM = pp.RCPTLNNM
where r.BACHNUMB like '%BATCH%'
and lc.POPRCTNM is null
order by iv2.Landed_Cost_ID
update POP10310 set Landed_Cost_Group_ID = iq.Landed_Cost_Group_ID
from POP10310 pp
join INSERTED r on r.POPRCTNM = pp.POPRCTNM
join IV00102 iq on iq.ITEMNMBR = pp.ITEMNMBR and iq.LOCNCODE = pp.LOCNCODE
where r.BACHNUMB like '%BATCH%'
update POP10300 set Total_Landed_Cost_Amount = t.Total
from POP10300 r
join
(select p1.POPRCTNM, SUM(p1.Total_Landed_Cost_Amount) as Total
from POP10700 p1
join INSERTED r on r.POPRCTNM = p1.POPRCTNM
where r.BACHNUMB like '%BATCH%'
group by p1.POPRCTNM
) t on r.POPRCTNM = t.POPRCTNM
update POP10500 set Total_Landed_Cost_Amount = round(t.Total,2)
from pop10500 r
join
(select p1.POPRCTNM, SUM(p1.Total_Landed_Cost_Amount) as Total
from POP10700 p1
join INSERTED r on r.POPRCTNM = p1.POPRCTNM
where r.BACHNUMB like '%BATCH%'
group by p1.POPRCTNM
) t on r.POPRCTNM = t.POPRCTNM