Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

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

10 comments:

  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
  2. Thank you so much - i turned this into a view and then a SmartList - beautiful!

    ReplyDelete
    Replies
    1. You are most welcome Linda :)

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  3. Hello, could you explain what the quantities mean? Like what does it mean when some receipt has QuantityMatch = 200 for example.
    thank you

    ReplyDelete
  4. Hello,
    Is it possible to generate inquiries about the purchase order date, receipt date and billing date?
    See amounts pending invoicing by purchase order, when invoice and billing cost.

    ReplyDelete
  5. One of the purchase dept. Rep. Entered a po in gp then system got hangged and pc restarted then she couldnt retrieve her po though the amount got deducted from the budget.. The error msg she got when she tried to access the po to print was you dont have access to this document type.. What can we do?

    ReplyDelete
  6. Hey I’m Martin Reed,if you are ready to get a loan contact.Mr Benjamin via email: lfdsloans@lemeridianfds.com,WhatsApp:+1 989-394-3740 I’m giving credit to Le_Meridian Funding Service .They grant me the sum 2,000,000.00 Euro. within 5 working days. Le_Meridian Funding Service  is a  group investors into pure loan and debt financing at the  returns of 1.9% to pay off your bills or buy a home Or Increase your Business. please I advise everyone out there who are in need of loan and can be reliable, trusted and capable of repaying back at the due time of funds.

    ReplyDelete
  7. We have some RCT and invoices without PO numbers, those are being excluded from this query. Do we have query to include them too?

    ReplyDelete
  8. Hello everyone I want to introduce you guys to a group a private investigators who can help you with information you need in any situation in life and they are ready to follow you step by step until your case is cleared just contact +17078685071 and you will happily ever after

    Premiumhackservices@gmail.com

    ReplyDelete