make a query , that will show credit and debit in system and local currency?

[Former Member]

hi, I wan to make a query , regarding trial balance that will show my credits and debits in system currency and local currency , how i do it?

[azizelmir]

Dears,

I would add to Mr. Mohd query's the attached modification showing the total in system currency as requested:

SELECT distinct T1."Account", 
				T2."AcctName",
				Sum(T1."Debit") AS "Debit",
				Sum(T1."Credit") As "Credit",
				Sum (T1."Debit" - T1."Credit") As "Balance LC",
				Sum(T1."SysDeb") AS "Debit SC",
				Sum(T1."SysCred") As "Credit SC",
				Sum (T1."SysDEb" - T1."SYSCred") As "Balance SC",


				T1."Project"
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0."TransId" = T1."TransId"
INNER JOIN OACT T2 ON T1."Account" = T2."AcctCode"
WHERE T0."RefDate" >='[%0]' AND T0."RefDate" <='[%1]'
GROUP BY T1."Account",T2."AcctName",T1."Project"

Thank you,

Aziz

[MD1]

DEAR ,

TRY THIS

SELECT distinct T1."Account", T2."AcctName", Sum(T1."Debit") AS "Debit",
Sum(T1."Credit") As "Credit", Sum (T1."Debit" - T1."Credit") As "Balance" ,T1."Project"
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0."TransId" = T1."TransId"
INNER JOIN OACT T2 ON T1."Account" = T2."AcctCode"
WHERE T0."RefDate" >='[%0]' AND T0."RefDate" <='[%1]'
GROUP BY T1."Account",T2."AcctName",T1."Project"

[former_member264639]

Hi,

Use the following query

select Debit, Credit, SYSCred, SYSDeb, FCDebit, FCCredit from JDT1

Thanks,

Karthik Kannan S

[iftikharabbasi]

Hi

Try accessing OJDT and JDT1 table. Also for the SYSCODE field you need to access/link with the OACT table to find the value.

I hope this helps.

Regards

Iftikhar

[iftikharabbasi]

Hi

Try accessing OJDT and JDT1 table. Also for the SYSCODE field you need to access/link with the OACT table to find the value.

I hope this helps.

Regards

Iftikhar