Day: January 13, 2021

I’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 an IT Professional (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