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

Can ISC Software Solutions help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

What should we write about next?





Your Name (required) -
Your Email (required) -

Leave a Reply

Your email address will not be published. Required fields are marked *