Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Saturday, September 27, 2014

Purchase Order Receiving and Invoices Details – SQL Script

In this post, an SQL Script is provided to list down all the purchase orders along with the associated details related to the POP transactions (Shipment, Shipment invoices …etc). The graph below shows the overall fields retrieved.

Purchase Order Details
/*----------------------------------------------------------------------------
Creation Date: 27th of September, 2014
Created by: Mahmoud M. AlSaadi

The main purpose of the script is to provide a detailed report for the
Purchase order along with the associated receiving and invoices details

The script has been tested on a very limited sample data.
Please feel free to contact me for any further enhancement

Revision History:
Revision No.         RevisionDate    Description
1                    27/09/2014           Original Version
----------------------------------------------------------------------------*/

Tables Included:

  • POP10100 | Purchase Order Work
  • POP10110 | Purchase Order Line Work
  • POP30310 | Receipt Line History
  • POP30300 | Receipt History Header

SELECT  A.[PONUMBER] ,
        CASE A.[POSTATUS]
           WHEN 1 THEN 'NEW'
           WHEN 2 THEN 'RELEASED'
           WHEN 3 THEN 'CHANGE ORDER'
           WHEN 4 THEN 'RECEIVED'
           WHEN 5 THEN 'CLOSED'
           WHEN 6 THEN 'CANCELED'
         END AS POSTATUS ,
        CASE A.[POTYPE]
           WHEN 1 THEN 'STANDARD'
           WHEN 2 THEN 'DROP-SHIP'
           WHEN 3 THEN 'BLANKET'
           WHEN 4 THEN 'BLANKET DROP-SHIP '
        END AS POTYPE ,
        A.[DOCDATE] AS Date ,
        A.[PRMDATE] AS PromiseDate ,
        A.[REQDATE] AS RequestedDate ,
        A.[REMSUBTO] AS RemainingSubTotal ,
        A.[SUBTOTAL] AS SubTotal ,
        A.OREMSUBT AS OriginatingRemainingSubTotal ,
        A.ORSUBTOT AS OriginatingSubTotal ,
        A.[VENDORID] AS VendorID ,
        A.[VENDNAME] AS VendorName ,
        A.[DUEDATE] AS DueDate ,
        A.[CURNCYID] ,
        A.[BUYERID] ,
        CASE B.[POLNESTA]
           WHEN 1 THEN 'NEW'
           WHEN 2 THEN 'RELEASED'
           WHEN 3 THEN 'CHANGE ORDER'
           WHEN 4 THEN 'RECEIVED'
           WHEN 5 THEN 'CLOSED'
           WHEN 6 THEN 'CANCELED'
        END AS POLineStatus ,
        B.[ITEMNMBR] AS ItemNumber ,
        B.[ITEMDESC] AS ItemDescrption ,
        B.[VENDORID] AS LineVendorID ,
        B.[VNDITNUM] AS VendorItemNumber ,
        B.[LOCNCODE] ,
        B.[UOFM] ,
        B.[QTYORDER] AS QuantityOrdered ,
        B.[QTYCANCE] AS QuantityCancelled ,
        B.[UNITCOST] ,
        B.ORUNTCST AS OriginatingUnitCost ,
        B.[EXTDCOST] AS ExtendedCost ,
        B.OREXTCST AS OriginatingExtendedCost ,
        B.XCHGRATE AS ExchangeRate ,
        ISNULL(C.POPRCTNM, ' ') AS POPRCTNM ,
        ISNULL(C.QTYSHPPD, 0) AS QuantityShipped ,
        ISNULL(C.QTYINVCD, 0) AS QuantityInvoiced ,
        ISNULL(C.QTYREJ, 0) AS QuantityRejected ,
        ISNULL(C.QTYMATCH, 0) AS QuantityMatch ,
        ISNULL(C.QTYRESERVED, 0) AS QuantityReserved ,
        CASE C.POPTYPE
           WHEN 1 THEN 'SHIPMENT'
           WHEN 2 THEN 'INVOICE'
           WHEN 3 THEN 'SHIPMENT/INVOICE'
           ELSE ' '
        END AS DocumentType ,
        ISNULL(C.UOFM, ' ') AS UOFM,
        ISNULL(C.DATERECD ,'') AS DateReceived,
        ISNULL(C.PCHRPTCT, 0) AS PURCHASERECEIPTCOST ,
        ISNULL(D.ORUNTCST, 0) AS ORGRECUNITCOST ,
        ISNULL(D.OREXTCST, 0) AS ORGRECCOST
        FROM    dbo.POP10100 AS A
        INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
        LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER]
                                         AND B.[ORD] = C.[POLNENUM]
        LEFT OUTER JOIN dbo.POP30310 AS D ON C.[PONUMBER] = D.[PONUMBER]
                                         AND C.[RCPTLNNM] = D.[RCPTLNNM]
                                         AND C.POPRCTNM = D.POPRCTNM
        LEFT OUTER JOIN dbo.POP30300 AS E ON D.[POPRCTNM] = E.[POPRCTNM]

 

Download LinkPurchase Order Details Script

Best Regards,
Mahmoud M. AlSaadi

1 comment:

  1. Review:
    FROM dbo.POP10100 AS A
    INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
    LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER]
    AND B.[ORD] = C.[POLNENUM]

    B.[ORD] not exist!

    ReplyDelete