Dynamics GP - SQL Views - Purchase Order Remaining Quantities

Original View here https://mahmoudsaadi.blogspot.com/2014/09/purchase-order-receiving-and-invoices.html



CREATE VIEW [dbo].[BI_PO_Rcts]
AS
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 Expr1, ISNULL(C.DATERECD, '') AS DateReceived, ISNULL(C.PCHRPTCT, 0) AS PURCHASERECEIPTCOST, ISNULL(D.ORUNTCST, 0) AS ORGRECUNITCOST, ISNULL(D.OREXTCST, 0) AS ORGRECCOST,
                  B.QTYORDER - B.QTYCANCE - ISNULL(C.QTYSHPPD, 0) - ISNULL(C.QTYREJ, 0) AS POQtyRemaining
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

GO