SQL View to Return Purchasing Status for the GP Elementz PO Management Module for Microsoft Dynamics GP

Microsoft Dynamics GPWhile it isn’t listed on the GP Elementz website, there is a module available called PO Management which replaces the standard POP/SOP link in Microsoft Dynamics GP. The standard functionality requires that all of a sales order line where there is a commitment to a PO must be fulfilled before that line can be invoiced; the PO Management module allows partial invoicing of a line committed to a PO.

After implementing the module for a client, they required a report showing the status of a SO line in terms of whether there was stock, if the SO line had been committed to a PO and if the line had been partially or fully received. As the PO Management module stores it data in a custom table, I had to create a SQL view pulling the relevant tables together to determine the status.

-- drop view if it exists
-- create view
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 Amounts Work'].SOPNUMBE ,['Sales Transaction Amounts Work'].SOPTYPE ,['Sales Transaction Amounts Work'].LNITMSEQ ,['Sales Transaction Amounts Work'].ITEMNMBR ,CASE WHEN ['Sales Transaction Amounts Work'].SOPTYPE NOT IN (2,6) THEN '' WHEN ['ISC Back to Back'].PONUMBER IS NULL AND (['Sales Transaction Amounts Work'].QTYFULFI - ['Sales Transaction Amounts Work'].QTYCANCE) <[/sqlgrey] (['Item Quantity Master'].QTYONHND - ['Item Quantity Master'].ATYALLOC) -- AVAILABLE THEN 'Needs Purchase' WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD IS NULL OR ['Purchasing Receipt Line Quantities'].QTYSHPPD = 0 THEN 'Purchased' WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD < (['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) THEN 'Partially Received' WHEN ['Purchasing Receipt Line Quantities'].QTYSHPPD >= (['Purchase Order Line'].QTYORDER - ['Purchase Order Line'].QTYCANCE) THEN 'Fully Received' ELSE 'None' END AS 'Purchasing Status' FROM SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) LEFT JOIN IV00102 AS ['Item Quantity Master'] -- Item Quantity Master (IV00102) ON ['Item Quantity Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR AND ['Item Quantity Master'].LOCNCODE = ['Sales Transaction Amounts Work'].LOCNCODE LEFT JOIN ISC_BACK AS ['ISC Back to Back'] -- ISC_Back_to_Back (ISC_Back) ON ['ISC Back to Back'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE AND ['ISC Back to Back'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE AND ['ISC Back to Back'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ AND ['ISC Back to Back'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ LEFT JOIN POP10110 AS ['Purchase Order Line'] -- Purchase Order Line (POP10110) ON ['Purchase Order Line'].PONUMBER = ['ISC Back to Back'].PONUMBER AND ['Purchase Order Line'].ORD = ['ISC Back to Back'].ORD LEFT JOIN ( SELECT PONUMBER ,POLNENUM ,SUM(QTYINVCD) AS QTYSHPPD FROM POP10500 -- Purchasing Receipt Line Quantities (POP10500) GROUP BY PONUMBER ,POLNENUM ) AS ['Purchasing Receipt Line Quantities'] ON ['Purchasing Receipt Line Quantities'].PONUMBER = ['ISC Back to Back'].PONUMBER AND ['Purchasing Receipt Line Quantities'].POLNENUM = ['ISC Back to Back'].ORD GO GRANT SELECT ON uv_AZRCRV_POMStatus TO DYNGRP GO