Skip to Content
0
Jun 14, 2018 at 09:40 AM

List of PO linked to GRN and APInvoice

89 Views

Hello Guys,

can someone assist me on this issue i am facing? I would like to create a report to list date range of ALL Purchase orders with Good Receipt PO and AP invoices. the Query below only display Purchase order WITH AP invoice or GRN.

I need to show even without any GRN or AP invoice so i can see whether the GRN or AP invoice has been created or NOT. I require a list of PO with and without GRN or Invoice

Thank you in advanced.

Select DISTINCT

A.DocNum as "PO DocNum",A.CardCode,A.CardName,A.DocDate as 'PO Posting Date',A.DocDueDate as 'Po Del.Date', B.ItemCode,B.Dscription,B.Quantity as 'PO Qty',B.Price as 'PO Price',C.Docnum AS 'GRPO DocNum',C.DocDate as 'GRPO Posting Date',C.DocDueDate as 'GRPO DueDate',D.Quantity as 'GRPO Qty',B.OpenQty as 'Remaing Qty',F.DocNum as 'AP-Inv DocNum',F.DocDate as 'AP-Inv Posting Date',F.DocDueDate as 'AP-Inv DueDate'

FROM OPOR A Inner Join POR1 B on A.DocEntry=B.DocEntry

Left outer join PDN1 D on D.BaseEntry=A.DocEntry and D.BaseLine=B.LineNum

inner Join OPDN C on C.Docentry=D.Docentry

Left Outer Join PCH1 E on E.BaseEntry=C.DocEntry and E.BaseLine=D.LineNum

inner join OPCH F on E.DocEntry=F.DocEntry

WHERE A.DocDate BETWEEN '[%from]' AND '[%TO]'