Skip to Content

Production Order: Currency Issues

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Posted on Aug 03, 2020 at 01:31 PM

    Hi,

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

    Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 03, 2020 at 01:37 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 03, 2020 at 01:42 PM

    Hi,

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

    Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 03, 2020 at 02:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.