Draft AP Service invoice report with G/L codes

[Former Member]

I need to generate a query report for AP DRAFT Invoices.

Doc. No. / Doc. Date / BP Name / Bp Code / GL Account Code / Description / Row total Total / GST value

This information is required for a AP Purchase - Invoice type Service DRAFT

But I can't able to pick the proper GL Account code and other required infromation from the Draft table.

I have tried incorporate the following tables without success.

invoice table OPCH

invoice lines table PCH1

G/L table OACT

Draft document table ODRF

Can anyone assist me please.

regards, Lisa

[former_member583013 · Accepted solution]

Lisa,

Please note that the DocNum you would get through the query is not the actual DocNum as the DocNum do not get confirmed till you actually add the AP Invoice.

Please make the correction to Gordon's Query...there was a small typo instead of ODRF the table name is typed as PDRF

SELECT T0.DocNum, T0.DocDate, T0.CardName, T0.CardCode, T2.FormatCode, T1.Dscription, T1.LineTotal, T1.VatSum

FROM [dbo\].[ODRF\] T0 Inner Join [dbo\].[DRF1\] T1 ON T1.DocEntry = T0.DocEntry

Inner Join [dbo\].[OACT\] T2 ON T2.AcctCode = T1.AcctCode

Where T0.ObjType = '18' and T0.DocType = 'S'

Suda

[Former Member]

wonderful thank you Gordon and Suda!

[Former Member]

Hi Lisa,

Please try this to start:

SELECT T0.DocNum, T0.DocDate, T0.CardName, T0.CardCode, 
T2.FormatCode, T1.Dscription, T1.LineTotal, T1.VatSum
Fom ODRF T0 Inner Join DRF1 T1 ON T1.DocEntry = T0.DocEntry
Inner Join OACT T2 ON T2.AcctCode = T1.AcctCode
Where T0.ObjType = '18' and T0.DocType = 'S'

Thanks,

Gordon