SQL View on Sales Order Processing Backorder Transactions

Microsoft Dynamics GPI’ve written this view for a number of different clients over the years, so am posting it here for future reference. It returns only open backorders which have yet top be transferred to order or invoice; it includes PO information so users can easily see if a PO has been raised and if any of it has been receipted.

The SQL below includes the

CREATE VIEW uv_AZRCRV_SOPOpenBackorders AS
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT ['Sales Transaction Work'].SOPNUMBE AS 'SOP Number' ,['Sales Transaction Work'].SOPTYPE AS 'SOP Type' ,['Sales Transaction Work'].DOCDATE AS 'Document Date' ,['Sales Transaction Work'].CSTPONBR AS 'Customer PO Number' ,['Sales Transaction Work'].CURNCYID AS 'Currency' ,['Sales Transaction Work'].CUSTNMBR AS 'Customer Number' ,['Sales Transaction Work'].CUSTNAME AS 'Customer Name' ,['Sales Transaction Amounts Work'].ITEMNMBR AS 'Item Number' ,['Sales Transaction Amounts Work'].ITEMDESC AS 'Item Description' ,['Sales Transaction Amounts Work'].QUANTITY AS 'Original Backorder Quantity' ,['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV AS 'Current Backorder Quantity' ,['Sales Transaction Amounts Work'].QUANTITY - (['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) AS 'Quantity Shipped' ,['Sales Transaction Amounts Work'].UNITPRCE AS 'Unit Price' ,['Sales Transaction Amounts Work'].XTNDPRCE AS 'Extended Price' ,(['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) * ['Sales Transaction Amounts Work'].UNITPRCE AS 'Current Extended Price' ,['Sales Transaction Amounts Work'].ORUNTPRC AS 'Originating Unit Price' ,['Sales Transaction Amounts Work'].OXTNDPRC AS 'Originating Extended Price' ,(['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) * ['Sales Transaction Amounts Work'].ORUNTPRC AS 'Current Originating Extended Price' ,['Sales Transaction Amounts Work'].LOCNCODE AS 'Site ID' ,['Sales Transaction Amounts Work'].ReqShipDate AS 'Requested Ship Date' ,['SOP_POP Link'].PONUMBER AS 'PO Number' ,['Purchase Order Lines'].VENDORID AS 'Vendor ID' ,['SOP_POP Link'].QTYRECVD AS 'Quantity Received' ,['Purchase Order Lines'].PRMSHPDTE AS 'Promised Ship Date' ,['Sales Line Comment Work and History'].CMMTTEXT AS 'Comment Text' FROM SOP10100 AS ['Sales Transaction Work'] --Sales Transaction Work (SOP10100) INNER JOIN SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200) ON ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE INNER JOIN RM00101 AS ['RM Customer Master'] --RM Customer MSTR (RM00101) ON ['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR LEFT JOIN SOP60100 AS ['SOP_POP Link'] --SOP_POPLink (SOP60100) ON ['SOP_POP Link'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE AND ['SOP_POP Link'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE AND ['SOP_POP Link'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ AND ['SOP_POP Link'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ LEFT JOIN SOP10202 AS ['Sales Line Comment Work and History'] --Sales Distribution Work and History (SOP10102) ON ['Sales Line Comment Work and History'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE AND ['Sales Line Comment Work and History'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE AND ['Sales Line Comment Work and History'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ AND ['Sales Line Comment Work and History'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ LEFT JOIN POP10110 AS ['Purchase Order Lines'] --Purchase Order Line (POP10110) ON ['Purchase Order Lines'].PONUMBER = ['SOP_POP Link'].PONUMBER AND ['Purchase Order Lines'].ORD = ['SOP_POP Link'].ORD WHERE ['Sales Transaction Work'].SOPTYPE = 5 GO GRANT SELECT ON uv_AZRCRV_SOPOpenBackorders TO DYNGRP GO