Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

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

2 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