Skip to Content

Conversion Currency in query

Hi Expert,

I have a query to show list of GRPO. The problem is when GRPO currency is IDR, the query will show GRPO total in IDR, but when GRPO currency is USD, query cannot show GRPO total in IDR, because OPDN.DocTotalFC is 0.
How can i conversion this?

System setting:
Local Currency : USD
System Currency: Indonesia Rupiah (IDR)
Default Account Currency: Local Currency.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

7 Answers

  • Best Answer
    Posted on Oct 24, 2019 at 09:04 AM

    Dear ,

    please try this change

    Query

    case when T1."Currency"='USD' then t1."TotalSumSy" else T1."TotalFrgn" end AS "GRPO Total IDR"

    TotalSumSy means......is below

    Linetotal is the linetotal in the currency of the document. TotalSumSy is the total in the system currecny.

    Danish

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Danish,

      I've made a query like before. That query means if GRPO.Doccur is USD, then query will show GRPO.TotalSumSy, which GRPO.TotalSumSy is the result of multiplication between GRPO.DocTotal and exchange rate IDR.

      But, not all GRPO like that. There is GRPO which the result of multiplication between GRPO.DocTotal and exchange rate IDR not equal GRPO.TotalSumSy.

  • Posted on Oct 24, 2019 at 07:06 AM

    please share our query

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Danish,

      Here is my query.

      SELECT T0.CardCode [Vendor Code],T0.CardName[Vendor Name],T1.WhsCode[Warehouse],T1.ItemCode[Item Code],T1.Dscription[Item Description],T1.Baseref [PO Number],T0.DocNum [GRPO Number],T0.DocDate[GRPO Date], T1.Quantity[GRPO Quantity], T1.Price[GRPO Unit Price], T0.DiscPrcnt[Discount (%)],T0.DiscSum[Discount Total],T0.DiscSumFC[Discount Total IDR],T1.LineTotal[GRPO Total],T1.TotalFrgn[GRPO Total IDR],T0.TotalExpns,T7.DocNum [Return #], T7.DocDate [Return Date], T6.Quantity [Qty Return], T6.LineTotal [Return Line Total], T6.TotalSumSy[Return Line Total IDR],T0.comments[GRPO Remarks],T9.DocNum [AP Number],T8.Quantity [AP Quantity],T8.Price[Ap Unit Price],T8.LineTotal[AP Total],T8.TotalFrgn[AP Total IDR]
      FROM OPDN T0
      INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
      INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
      INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
      LEFT JOIN RPD1 T6 ON T0.DocNum = T6.BaseRef AND T1.LineNum = T6.BaseLine
      LEFT JOIN ORPD T7 ON T6.DocEntry = T7.DocEntry
      LEFT JOIN  PCH1 T8 ON T8.BaseEntry = T1.DocEntry AND T8.BaseRef = T1.LineNum
      LEFT JOIN OPCH T9 ON T9.DocEntry = T8.DocEntry
      
      WHERE T2.ItemCode LIKE '%%[%0]%%'
      AND T0.CardCode LIKE '%[%1]%%' 
      AND T1.WhsCode LIKE '%[%2]%%' 
      AND T3.ItmsGrpNam Like '%[%3]%'
      AND T0.DocDate >= '[%4]' AND T0.DocDate <= '[%5]'
  • Posted on Oct 24, 2019 at 07:41 AM

    try this

    SELECT
    T0."CardCode" AS "Vendor Code",
    T0."CardName" AS "Vendor Name",
    T1."WhsCode" AS "Warehouse",
    T1."ItemCode" AS "Item Code",
    T1."Dscription" AS "Item Description",
    T1."BaseRef" AS "PO Number",
    T0."DocNum" AS "GRPO Number",
    T0."DocDate" AS "GRPO Date",
    T1."Quantity" AS "GRPO Quantity",
    T1."Price" AS "GRPO Unit Price",
    T1."Currency",
    T1."Rate",
    T0."DiscPrcnt" AS "Discount (%)",
    T0."DiscSum" AS "Discount Total",
    T0."DiscSumFC" AS "Discount Total IDR",
    T1."LineTotal" AS "GRPO Total",
    T1."TotalFrgn" AS "GRPO Total IDR",
    T0."TotalExpns",
    T7."DocNum" AS "Return #",
    T7."DocDate" AS "Return Date",
    T6."Quantity" AS "Qty Return",
    T6."LineTotal" AS "Return Line Total",
    T6."TotalSumSy" AS "Return Line Total IDR",
    T0."Comments" AS "GRPO Remarks",
    T9."DocNum" AS "AP Number",
    T8."Quantity" AS "AP Quantity",
    T8."Price" AS "Ap Unit Price",
    T8."LineTotal" AS "AP Total",
    T8."TotalFrgn" AS "AP Total IDR"
    FROM OPDN T0
    INNER JOIN PDN1 T1 ON T0."DocEntry" = T1."DocEntry"
    INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
    INNER JOIN OITB T3 ON T2."ItmsGrpCod" = T3."ItmsGrpCod"
    LEFT OUTER JOIN RPD1 T6 ON T6."BaseEntry" = T1."DocEntry" AND T1."LineNum" = T6."BaseLine" and t6."BaseType"='20'
    LEFT OUTER JOIN ORPD T7 ON T6."DocEntry" = T7."DocEntry"
    LEFT OUTER JOIN PCH1 T8 ON T8."BaseEntry" = T1."DocEntry" AND T8."BaseLine" = T1."LineNum" and t8."BaseType"='20'
    LEFT OUTER JOIN OPCH T9 ON T9."DocEntry" = T8."DocEntry"
    WHERE T2."ItemCode" LIKE '%%[%0]%%' AND T0."CardCode" LIKE '%[%1]%%' AND
    T1."WhsCode" LIKE '%[%2]%%' AND T3."ItmsGrpNam" LIKE '%[%3]%' AND T0."DocDate" >= '[%4]' AND
    T0."DocDate" <= '[%5]'

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 24, 2019 at 07:44 AM

    Dear ,

    If an error occurs in this query, then show me the via Screenshot.

    1.Both GRPO(IDR& USD currency )

    Danish

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 24, 2019 at 08:21 AM

    Dear Charles Tongam,

    Please share screen shot, i will help you


    4.png (11.2 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 24, 2019 at 10:35 AM

    Dear

    Please check below joining...........

    your joining

    LEFT JOIN RPD1 T6 ON T0.DocNum = T6.BaseRef AND T1.LineNum = T6.BaseLine
    LEFT JOIN ORPD T7 ON T6.DocEntry = T7.DocEntry
    LEFT JOIN PCH1 T8 ON T8.BaseEntry = T1.DocEntry AND T8.BaseRef = T1.LineNum
    LEFT JOIN OPCH T9 ON T9.DocEntry = T8.DocEntry

    new joining

    LEFT OUTER JOIN RPD1 T6 ON T6."BaseEntry" = T1."DocEntry" AND T1."LineNum" = T6."BaseLine" and t6."BaseType"='20'
    LEFT OUTER JOIN ORPD T7 ON T6."DocEntry" = T7."DocEntry"
    LEFT OUTER JOIN PCH1 T8 ON T8."BaseEntry" = T1."DocEntry" AND T8."BaseLine" = T1."LineNum" and t8."BaseType"='20'
    LEFT OUTER JOIN OPCH T9 ON T9."DocEntry" = T8."DocEntry"

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 25, 2019 at 03:43 AM

    Dear Charles,

    So that I can fix your problem where would you like to join me.


    6.png (63.8 kB)
    Add a comment
    10|10000 characters needed characters exceeded