I have been asked to generate a query that will allow for all PO's received but not invoiced. to be displayed.
I have most of the logic I think but am struggling to cut the code, could one of the experts give me a hand on this possibly? Or perhaps there is a better way to do this.
I believe the logic would be something like find POs in the POP30100 Purchase Order History table that donothave a matching record in the POP30300 Purchasing Receipt History table where POP30300.POPTYPE = 2 (Invoice) or 3 (Shipment/Invoice). You’d also need to look at the POP10300 Purchasing Receipt Work table too: find all POs in the POP30100 table that do not have a matching record in the POP10300 table where POP10300.POPTYPE = 2 or 3.
Likewise with the related work tables: find all POs in the POP10100 Purchase Order Work table that do not have a matching record in the POP10300 Purchasing Receipt Work table where POP10300.POPTYPE = 2 or 3.
Playing in SQL I get
SELECT A.[PONUMBER] ,
CASE A.[POSTATUS]
WHEN 1 THEN 'NEW'
WHEN 2 THEN 'RELEASED'
WHEN 3 THEN 'CHANGE ORDER'
WHEN 4 THEN 'RECEIVED'
WHEN 5 THEN 'CLOSED'
WHEN 6 THEN 'CANCELED'
END AS POSTATUS ,
CASE A.[POTYPE]
WHEN 1 THEN 'STANDARD'
WHEN 2 THEN 'DROP-SHIP'
WHEN 3 THEN 'BLANKET'
WHEN 4 THEN 'BLANKET DROP-SHIP '
END AS POTYPE ,
A.[DOCDATE] AS Date ,
A.[PRMDATE] AS PromiseDate ,
A.[REQDATE] AS RequestedDate ,
A.[REMSUBTO] AS RemainingSubTotal ,
A.[SUBTOTAL] AS SubTotal ,
A.OREMSUBT AS OriginatingRemainingSubTotal ,
A.ORSUBTOT AS OriginatingSubTotal ,
A.[VENDORID] AS VendorID ,
A.[VENDNAME] AS VendorName ,
A.[DUEDATE] AS DueDate ,
A.[CURNCYID] ,
A.[BUYERID] ,
CASE B.[POLNESTA]
WHEN 1 THEN 'NEW'
WHEN 2 THEN 'RELEASED'
WHEN 3 THEN 'CHANGE ORDER'
WHEN 4 THEN 'RECEIVED'
WHEN 5 THEN 'CLOSED'
WHEN 6 THEN 'CANCELED'
END AS POLineStatus ,
B.[ITEMNMBR] AS ItemNumber ,
B.[ITEMDESC] AS ItemDescrption ,
B.[VENDORID] AS LineVendorID ,
B.[VNDITNUM] AS VendorItemNumber ,
B.[LOCNCODE] ,
B.[UOFM] ,
B.[QTYORDER] AS QuantityOrdered ,
B.[QTYCANCE] AS QuantityCancelled ,
B.[UNITCOST] ,
B.ORUNTCST AS OriginatingUnitCost ,
B.[EXTDCOST] AS ExtendedCost ,
B.OREXTCST AS OriginatingExtendedCost ,
B.XCHGRATE AS ExchangeRate ,
ISNULL(C.POPRCTNM, ' ') AS POPRCTNM ,
ISNULL(C.QTYSHPPD, 0) AS QuantityShipped ,
ISNULL(C.QTYINVCD, 0) AS QuantityInvoiced ,
ISNULL(C.QTYREJ, 0) AS QuantityRejected ,
ISNULL(C.QTYMATCH, 0) AS QuantityMatch ,
ISNULL(C.QTYRESERVED, 0) AS QuantityReserved ,
CASE C.POPTYPE
WHEN 1 THEN 'SHIPMENT'
WHEN 2 THEN 'INVOICE'
WHEN 3 THEN 'SHIPMENT/INVOICE'
ELSE ' '
END AS DocumentType ,
ISNULL(C.UOFM, ' ') AS UOFM,
ISNULL(C.DATERECD ,'') AS DateReceived,
ISNULL(C.PCHRPTCT, 0) AS PURCHASERECEIPTCOST ,
ISNULL(D.ORUNTCST, 0) AS ORGRECUNITCOST ,
ISNULL(D.OREXTCST, 0) AS ORGRECCOST
FROM dbo.POP10100 AS A
INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER]
AND B.[ORD] = C.[POLNENUM]
LEFT OUTER JOIN dbo.POP30310 AS D ON C.[PONUMBER] = D.[PONUMBER]
AND C.[RCPTLNNM] = D.[RCPTLNNM]
AND C.POPRCTNM = D.POPRCTNM
LEFT OUTER JOIN dbo.POP30300 AS E ON D.[POPRCTNM] = E.[POPRCTNM]
order by DATE DESC
I need to sort by status 2 and 3 I think to filter.