Skip to Content
0
Former Member
May 28, 2012 at 10:31 AM

G/L Query

17 Views

Good morning,

I am writing a query based on the standard G/L report (querying JDT1) and would like to add the description(PCH1.dscription) if the posting has originated from an AP Invoice.

BUT - If the invoice has 2 lines it returns both records - even though there is only 1 entry in jdt1 for that transaction. This means the amounts returned are incorrect. I wouild like to return just the first line description if there are more than one AP invoice rows.

So far

SELECT DISTINCT T0.[TransId], T0.[Line_ID], T0.[Account], T0.[Debit], T0.[Credit],

T0.[FCCurrency], T0.[DueDate], T0.[ShortName], T0.[ContraAct], T0.[LineMemo],

CASE

when T0.TransType='18' then t6.Dscription

Else '-'

End,

T0.[TransType], T0.[RefDate], T4.[CardName]

FROM [dbo].[JDT1] T0 INNER JOIN [dbo].[OJDT] T1 ON T1.[TransId] = T0.[TransId]

LEFT OUTER JOIN [dbo].[OACT] T3 ON T3.[AcctCode] = T0.[ContraAct]

LEFT OUTER JOIN [dbo].[OCRD] T4 ON T4.[CardCode] = T0.[ContraAct]

Left Outer join OPCH t5 on t5.transid =t1.transid

left outer JOIN PCH1 t6 on t5.docentry = t6.docentry

WHERE T0.[Account] = '130000' AND T0.[RefDate] >='20120523'

Thanks a lot

Naomi.