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...

Monday, November 16, 2015

Purchase Order Receiving and Invoices Details (with Requisitions) - 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) with requisition details.In a previous post, an SQL script for Purchase Order receiving and invoicing details was provided, without any mention for the original requisition (if any). This script completes the cycle by adding the requisition details.The graph below shows the overall fields retrieved.

Purchase Order details with Requisition

The script can be downloaded from here .. Download Link


/*--------------------------------------------------------------------------------

Creation Date: 16, November, 2015
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to retrieve all purchasing details from Requisition to
associated Purchase Order and receiving details

The script has been tested on a very limited sample data.
Revision History:
Revision No.            Revision Date    Description
1                       16/11/2015      Original Version
--------------------------------------------------------------------------------*/


CREATE VIEW [dbo].[DI_RequisitionDetails] AS

SELECT A.[Requisition Number],
              A.[User Defined 1],
              A.[User Defined 2],
              B.[User Defined 1] AS LineDefined1,
              B.[User Defined 2] AS LindeDefined2, 
              C.PONUMBER AS PurchaseOrderNumber,
              A.[Requested By],
              A.[Comment ID],
              A.[Address 1],
              A.[Address 2],
              A.[Address 3]
              FROM PurchaseRequisitions AS A
LEFT OUTER JOIN PurchaseRequisitionLines AS B
ON A.[Requisition Number] = B.[Requisition Number]
LEFT OUTER JOIN SOP60100 AS C
ON A.[Requisition Number] = C.SOPNUMBE




CREATE VIEW [dbo].[DI_ALL_POP_ReceivingandRequisitionDetails]
AS
SELECT  PONUMBER ,
        VENDORID ,
        ISNULL([Requisition Number],'')  [Requisition Number],
        ISNULL([User Defined 1],'') [User Defined 1],
        ISNULL([User Defined 2],'') [User Defined 2],
        ISNULL([LineDefined1],'') [LineDefined1],
        ISNULL([LindeDefined2],'') [LindeDefined2],
        ISNULL([Requested By],'')[Requested By],
        ISNULL([Comment ID],'') [Comment ID],
        POPRCTNM AS POPReceiptNumber,
        receiptdate AS  POPReceiptDate,
        TRXSORCE AS TransactionSource,
        POPTYPE AS POPType,
        QTYSHPPD AS QuantityShipped,
        QTYINVCD AS QuantityInvoiced,
        ITEMNMBR AS ItemNumber,
        TRXLOCTN AS TransactionLocation,
        UOFM AS UnitofMeasurement,
        DATERECD AS DateReceived,
        OREXTCST AS ExtendedCost,
        ISNULL(SERIES,'') AS Series,
        ISNULL(JRNENTRY,'') AS POPReceiptJournalEntryNumber,
        ISNULL(DEBIT,0) AS Debit,
        ISNULL(CREDIT,0) AS Credit,
        ISNULL(ORGNTSRC,'') AS OriginatingTransactionSource,
        ISNULL(ORCTRNUM,'') AS OriginatingTransactionNumber,
        ISNULL(ORDOCNUM,'') AS OriginatingDocumentNumber,
        ISNULL(ORMSTRID,'')  AS OriginatingMasterNumber
FROM    ( SELECT    *
          FROM      ( SELECT    B.PONUMBER ,
                                B.VENDORID ,
                                B.POPRCTNM ,
                                A.receiptdate ,
                                A.TRXSORCE ,
                                CASE B.POPTYPE
                                  WHEN 1 THEN 'Shipment'
                                  WHEN 2 THEN 'Invoice'
                                  WHEN 3 THEN 'Shipment-Invoice'
                                  WHEN 4 THEN 'Return'
                                  WHEN 5 THEN 'Return with Credit'
                                  WHEN 6 THEN 'Inventory Return'
                                  WHEN 7 THEN 'Inventory Return with Credit'
                                  WHEN 8 THEN 'InTransit'
                                  ELSE ''
                                END AS POPTYPE ,
                                B.QTYSHPPD ,
                                B.QTYINVCD ,
                                B.ITEMNMBR ,
                                B.TRXLOCTN ,
                                B.UOFM ,
                                B.DATERECD ,
                                B.INVINDX ,
                                B.ACPURIDX ,
                                B.OREXTCST
                      FROM      dbo.POP30300 AS A
                                LEFT OUTER JOIN dbo.POP10500 AS B
     ON A.POPRCTNM = B.POPRCTNM
                                AND A.VENDORID = B.VENDORID
                    ) AS POP
                    LEFT OUTER JOIN ( SELECT    SERIES ,
                                                JRNENTRY ,
                                                SUM(DEBITAMT) AS DEBIT ,
                                                SUM(CRDTAMNT) AS CREDIT ,
                                                ORGNTSRC ,
                                                ORCTRNUM ,
                                                ORDOCNUM ,
                                                ORMSTRID
                                      FROM      ( SELECT    SERIES ,
                                                            JRNENTRY ,
                                                            DEBITAMT ,
                                                            CRDTAMNT ,
                                                            ORGNTSRC ,
                                                            ORCTRNUM ,
                                                            ORDOCNUM ,
                                                            ORMSTRID
                                                  FROM      dbo.GL20000
                                                  WHERE     SERIES = 4
                                                  UNION ALL
                                                  SELECT    SERIES ,
                                                            JRNENTRY ,
                                                            DEBITAMT ,
                                                            CRDTAMNT ,
                                                            ORGNTSRC ,
                                                            ORCTRNUM ,
                                                            ORDOCNUM ,
                                                            ORMSTRID
                                                  FROM      dbo.GL30000
                                                  WHERE     SERIES = 4
                                                ) AS GL
                                      GROUP BY  SERIES ,
                                                JRNENTRY ,
                                                ORGNTSRC ,
                                                ORCTRNUM ,
                                                ORDOCNUM ,
                                                ORMSTRID
                                    ) AS GL ON ( GL.ORGNTSRC = POP.TRXSORCE
                                                 AND GL.ORDOCNUM = POP.POPRCTNM
                                                 AND GL.ORCTRNUM = POP.POPRCTNM
                                                 AND GL.ORMSTRID = POP.VENDORID
                                               )
        ) AS POP
        LEFT OUTER JOIN DI_RequisitionDetails AS Req
ON POP.PONUMBER = Req.PurchaseOrderNumber
GO
GRANT SELECT ON [DI_ALL_POP_ReceivingandRequisitionDetails]  TO DYNGRP
GRANT SELECT ON [DI_RequisitionDetails]TO DYNGRP



Best Regards, 
Mahmoud M. AlSaadi

4 comments:

  1. Hi Mahmood,

    This is very helpful, is it possible to include requisition workflow detail when and by who the requisition was approved in workflow 2.00. I have been trying but seems not possible.

    thank you

    ReplyDelete
    Replies
    1. I will consider this for a future post including an SQL view for requisition workflow details.

      Stay tuned
      Mahmoud M. AlSaadi

      Delete
  2. MaHmood,

    Ia there a way to add the Purchases GL Account Numbers and the Accounts Payable GL Account Numbers to this view so they print on this report?

    ReplyDelete
  3. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete