Skip to Content
0
Jul 16, 2019 at 12:28 AM

Help getting Delivery and Invoice info in query

119 Views Last edit Jul 16, 2019 at 12:30 AM 2 rev

Query is working properly but would like to add two additional fields in my query, Delivered and Invoiced. Desired Input is "Yes" if it's been delivered, Or No if not, Same with Invoice.

Need help including the additional tables (PDN1, PCH1) in my query.

Select
A.DocDate 'SO Date', A.DocNum 'SO No', G.U_Name, A.CardCode, A.CardName 'Customer Name', E.SlpName 'Sales Rep', (B.LineNum +1) AS 'LineNum', F.FrgnName 'Product', B.ItemCode, B.Dscription, B.Quantity 'SO Qty', B.OpenCreQty 'SO Open Qty', B.Price 'SO Price', C.Quantity 'PO Quantity', C.Price 'PO Price', D.DocNum 'PO No', D.CardName 'Vendor', D.DocStatus 'PO Status', A.DocStatus 'SO Status'
from
ORDR A
inner join RDR1 B on A.DocEntry = B.DocEntry
inner join POR1 C on B.DocEntry = C.BaseEntry and B.ObjType=C.BaseType and B.LineNum = C.BaseLine
inner join OPOR D on C.DocEntry = D.DocEntry
inner join OSLP E on A.SlpCode = E.SlpCode
inner join OITM F on B.ItemCode = F.ItemCode
inner join OUSR G on A.Usersign = G.USERID


Where B.DocEntry=C.BaseEntry and B.ObjType=C.BaseType and A.DocDate between [%0] and [%1] and A.Canceled = 'N' and D.Canceled = 'N'
For Browse