Draft AP Service invoice report with G/L codes
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.
Suda Sampath replied
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'