Skip to Content
0
Jun 17, 2021 at 03:12 PM

FC and SC Query - How to Show Correct Figure

50 Views Last edit Jun 18, 2021 at 07:11 AM 3 rev

Hi Experts,

I need to a query to display a list of invoices for a particular group of customers (QryGroup6) who uses different currencies EURO and GBP. On the GTotal is it showing both FC and NonFC from my query below.

How can I get the result to show the actual currency on the document e.g. AR Invoice 1258300 as in FC and other invoice in GBP are in SC. See below I need the query to display the actual figure on SAP AR invoice whether it is in EURO or GBP.

SELECT T0.[DocNum], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[currency], T1.[Price], T1.[VatSum], T1.[VatPrcnt], T1.[GTotal], T0.[DocDate], T0.[CardCode], T0.[CardName] , T1.Shipdate

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OCRD ON T0.CardCode = OCRD.CardCode

WHERE OCRD.QryGroup6 = 'Y' and T0.[DocDate] BETWEEN '[%FROMDATE]' AND '[%TODATE]'

My issue are the T1.[VatSum] and T1.[GTotal] columns will show in GBP amount and this is not what is actually on the invoice where the customer currency is in EURO.

Can you assist on this please?