Production Order: Currency Issues

[former_member685937]

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

[kothandaraman_nagarajan]

Change your case statement as per below sample,

Case

when T0.[DocCur] = 'SGD' THEN T0.[DocTotal]

when T0.[DocCur] = 'USD' THEN T0.[DocTotalSy]

when T0.[DocCur] <> 'SGD' THEN T0.[DocTotalFC] End

Note:

Replace with your LC and System currency

[kothandaraman_nagarajan]

Hi,

Just add T0.Doccur in above query to get purchase order document currency.

Regards,

Nagarajan

[former_member685937]

Hi

Your function works well for when currency is GBP, but when currency is NTD, it doesn't show the correct amount.

The query will still show GBP185.00 instead of the NTD7400.00 I want. Please Advice

[former_member685937]

Hi Nagarajan

I would like the Document Currency and its respective values to show up in my sql query. Say like Purchase Order #1 uses NTD and Purchase Order #2 uses GBP, I would like them to show in my Crystal Report template

[kothandaraman_nagarajan]

Hi,

Do you want document currency in above query or add to filter by using document currency?

Regards,

Nagarajan