SQL Script for Purchase Order Line Items Not Yet Received

| 06/23/2011 | 6 Comments

Hi all

Today, I am posting a script which provides the details of the Purchase Order Line Items which are yet to be received.

/******************************************************************
Created June 23, 2011 by Sivakumar Venkataraman - Interdyn AKA
This view returns a returns the details of the PO Line which has
not been received fully. It provides the details of the quantity
to be received and the value of the line item to be received in
originating currency.
 
Tables used: 
 - POP10100 - Purchase Order Work
 - POP10110 - Purchase Order Line
 - POP10500 - Purchasing Receipt Line Quantities
 ******************************************************************/
CREATE VIEW vw_PORemainingDtl
AS  
SELECT  PO.[PO NUMBER] ,
        PO.[PO STATUS] ,
        PO.[PO TYPE] ,
        PO.[PO DATE] ,
        PO.[Currency ID] ,
        PO.[Vendor ID] ,
        PO.[Vendor Name] ,
        PO.[PO Line NUMBER] ,
        PO.[Item NUMBER] ,
        PO.[Item DESC] ,
        PO.[Vendor Item NUMBER] ,
        PO.[Vendor Item DESC] ,
        PO.[Location Code] ,
        PO.[UoM] ,
        ( ( PO.[Qty Ordered] - PO.[Qty Canceled] ) - PO.[Qty Shipped] ) AS [Qty TO Recv] ,
        ( ( ( PO.[Qty Ordered] - PO.[Qty Canceled] ) - PO.[Qty Shipped] )
          * PO.[Orig Unit Cost] ) AS [PO Line Remain VALUE]
FROM    ( SELECT    A.PONUMBER AS [PO NUMBER] ,
                    CASE WHEN A.POSTATUS = 1 THEN 'New'
                         WHEN A.POSTATUS = 2 THEN 'Released'
                         WHEN A.POSTATUS = 3 THEN 'Change Order'
                         WHEN A.POSTATUS = 4 THEN 'Received'
                         WHEN A.POSTATUS = 5 THEN 'Closed'
                         ELSE 'Canceled'
                    END AS [PO STATUS] ,
                    CASE WHEN A.POTYPE = 1 THEN 'Standard'
                         WHEN A.POTYPE = 2 THEN 'Drop-ship'
                         WHEN A.POTYPE = 3 THEN 'Blanket'
                         WHEN A.POTYPE = 4 THEN 'Drop-ship Blanket'
                         ELSE ''
                    END AS [PO TYPE] ,
                    A.DOCDATE AS [PO DATE] ,
                    A.CURNCYID AS [Currency ID] ,
                    A.ORSUBTOT AS [Orig Subtotal] ,
                    A.ORTDISAM AS [Orig Trade Disc] ,
                    A.ORFRTAMT AS [Orig Freight Amt] ,
                    A.OMISCAMT AS [Orig Misc Amt] ,
                    A.ORTAXAMT AS [Orig Tax Amt] ,
                    A.VENDORID AS [Vendor ID] ,
                    A.VENDNAME AS [Vendor Name] ,
                    B.ORD AS [PO Line NUMBER] ,
                    CASE WHEN B.POLNESTA = 1 THEN 'New'
                         WHEN B.POLNESTA = 2 THEN 'Released'
                         WHEN B.POLNESTA = 3 THEN 'Change Order'
                         WHEN B.POLNESTA = 4 THEN 'Received'
                         WHEN B.POLNESTA = 5 THEN 'Closed'
                         ELSE 'Canceled'
                    END AS [PO Line STATUS] ,
                    B.ITEMNMBR AS [Item NUMBER] ,
                    B.ITEMDESC AS [Item DESC] ,
                    B.VNDITNUM AS [Vendor Item NUMBER] ,
                    B.VNDITDSC AS [Vendor Item DESC] ,
                    B.LOCNCODE AS [Location Code] ,
                    B.UOFM AS [UoM] ,
                    B.QTYORDER AS [Qty Ordered] ,
                    B.QTYCANCE AS [Qty Canceled] ,
                    B.ORUNTCST AS [Orig Unit Cost] ,
                    B.OREXTCST AS [Orig Extd Cost] ,
                    ISNULL(( SELECT SUM(C.QTYSHPPD)
                             FROM   TWO.dbo.POP10500 C
                             WHERE  C.PONUMBER = B.PONUMBER
                                    AND C.POLNENUM = B.ORD
                                    AND C.POPTYPE IN ( 1, 2, 3 )
                           ), 0) AS [Qty Shipped] ,
                    ISNULL(( SELECT SUM(C.QTYINVCD)
                             FROM   TWO.dbo.POP10500 C
                             WHERE  C.PONUMBER = B.PONUMBER
                                    AND C.POLNENUM = B.ORD
                                    AND C.POPTYPE IN ( 1, 2, 3 )
                           ), 0) AS [Qty Invoiced] ,
                    ISNULL(( SELECT SUM(C.QTYRESERVED)
                             FROM   TWO.dbo.POP10500 C
                             WHERE  C.PONUMBER = B.PONUMBER
                                    AND C.POLNENUM = B.ORD
                                    AND C.POPTYPE IN ( 1, 2, 3 )
                           ), 0) AS [Qty Returned]
          FROM      TWO.dbo.POP10100 A
                    INNER JOIN TWO.dbo.POP10110 B ON A.PONUMBER = B.PONUMBER
        ) PO
WHERE   ( ( PO.[Qty Ordered] - PO.[Qty Canceled] ) - PO.[Qty Shipped] ) > 0

Hope this script is useful to the community…

Until next post!

VN:F [1.9.22_1171]
Rating: 6.8/10 (5 votes cast)
VN:F [1.9.22_1171]
Rating: +4 (from 4 votes)
SQL Script for Purchase Order Line Items Not Yet Received, 6.8 out of 10 based on 5 ratings

Tags: , , , , ,

Category: Dynamics, Great Plains, Purchase Order Processing, SQL Server, SQL Server 2005, SQL Server 2008

About the Author ()

  • Jeganeedhi Krishnan

    Hi Siva,
    This is great!!
    If I am not wrong, In the Blanket Order we should not consider the first line item quantity. But in your query i dint see any special logic/filter for Blanket Order.
    Please correct me if i misunderstood about blanket order.

    Thanks

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • Sivakumar Venkataraman

      Hi Jeganeedhi, This script is applicable for normal purchase orders and does not consider blanket PO’s. However, it can be tweaked to support blanket PO’s as well.

      VA:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      VA:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  • Lulu

    Hello Siva! Thanks for all the help with this view. Even the user was excited about being able to get this information within seconds :-)

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • veeyeskay

      Lulu, Glad the script was of help to you!

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  • Javier Sandoval

    Great post!!! You know that with this you same s lot of time to a lot of people… Thanks!

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  • Pingback: SQL Script for Purchase Order Line Items Not Yet Received | Interesting Findings & Knowledge Sharing - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community