Skip to Content
0
Aug 03, 2020 at 12:45 PM

Production Order: Currency Issues

63 Views

Hi Everyone,

Just an query on my Sql function for Production Order, my client uses 2 types of currencies (NTD - System Currency and GBP - Local Currency) for its Production Orders.

Below is my SQL attempt:

--Purchase Order select t1.LineNum+1 as '#' ,T0.Cardcode as 'Supplier Code' ,T0.Cardname as 'Supplier Name' ,t0.DocNum as 'Order Number' ,t0.Comments as 'Remarks' ,t0.TaxDate as 'Date' --,t0.SlpCode as 'Buyer(Employee)' ,t2.SlpName as 'Buyer(Employee)' ,t0.NumAtCard as 'Supplier Ref.' ,t0.SupplCode as 'Supplementary Code' --,T0.CntctCode as 'Cont.P(Supplier)' ,T3.Name as 'Cont.P(Supplier)' ,t0.DocDueDate as 'Delivery Date' ,t0.GroupNum as 'Payment Terms' ,t0.PeyMethod as 'Payment Method' ,t0.Address as 'Address' ,T1.ItemCode as 'Product Code' ,T1.Dscription as 'Product Description' ,t1.Price as 'Unit Price' ,t1.Quantity as 'Quantity' ,t1.DiscPrcnt as 'Disc%' ,t1.LineTotal as 'Row Total' , case when DocCur = 'NTD' then t1.LineTotal else t1.LineTotal end as 'Row Total (Sys Currency)' ,t1.Currency as 'Document Currency' ,t0.Address as 'Billing Address' ,t0.PaymentRef as 'Payment Reference Number' ,T4.PymntGroup as 'Payment Terms' ,T0.DiscPrcnt as 'Discount Percentage' ,T0.DiscSum as 'Discount Total' ,(t0.DocTotal - t0.VatSum) as 'Total Before Discount' , case when DocCur = 'GBP' then t0.DocTotal-t0.VatSum else t0.DocTotalFC - t0.VatSumFC end as 'Total Before Discount' --t0.TotalExpns as 'Frieght' ,case when DocCur ='GBP' then T0.TotalExpns else t0.TotalExpFC end as 'Frieght' --t0.VatSum as 'Tax Total' ,case when DocCur ='GBP' then T0.VatSum else t0.VATSUMFC end as 'Tax Total' --,t0.DocTotal as 'Total Sum' ,case when DocCur ='GBP' then T0.DocTotal else t0.DocTotalFC end as 'Total Sum' from OPOR T0 left join POR1 T1 on T0.DocEntry=T1.DocEntry left join OSLP T2 on T0.SlpCode = T2.SlpCode left join OCPR T3 on T0.CntctCode = T3.CntctCode left join OCTG T4 on T0.GroupNum = T4.GroupNum

Is there a way which the currencies can pick itself according to the Purchase Order document accordingly?

Many Thanks

Clement