Skip to Content
0
Former Member
Apr 27, 2010 at 09:00 AM

Open Orders Query showing all open AP Orders

36 Views

Hi All,

I wish to generate a report that shows all open AP Purchase Orders as well as all orders that are partially inviced.

I have been using the following query but it is not giving me complete results:

SELECT

T1.[DocNum] AS 'PO NUMBER',

T1.[DocDate] AS 'POSTING DATE',

T1.[DocDueDate] AS 'DUE DATE',

T1.[TaxDate] AS 'DOC DATE',

T1.[Comments] AS 'REMARKS',

T1.[CardCode] AS 'CUSTOMER/VENDOR CODE',

T1.[CardName] AS 'CUSTOMER/VENDOR NAME',

T0.[LineTotal] AS 'LINE TOTAL',

T0.[OcrCode] AS 'PROFIT CENTER',

T1.[PaidSum] AS 'PAID',

T0.[Project] AS 'PROJECT CODE',

T0.[LineNum] AS 'ROW NUMBER',

T0.[LineStatus] AS 'LINE STATUS',

T1.[DocStatus] AS 'PR STATUS'

FROM

POR1 T0 INNER JOIN OPOR T1 ON T0.DocEntry = T1.DocEntry

WHERE

T1.DOCSTATUS <>'C'

AND

T0.LineStatus <> 'C'

ORDER BY T1.docentry

I need to add to this query a column to show PO's that have been partially invoiced, a column to show if the invoices have been paid and another column to show the balances for those particular orders.

HELP PLEASE!

Robinson