web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics GP Essentials / Purchase Order Receiving an...

Purchase Order Receiving and Invoices Details (with Requisitions) - SQL Script

Views (2300)
Mahmoud Saadi Profile Picture Mahmoud Saadi 32,738

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 PurchaseRequisitionsAS A
LEFT OUTER JOIN PurchaseRequisitionLinesAS 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'
                                  WHEN8 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 OUTERJOIN ( 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_RequisitionDetailsAS 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

This was originally posted here.

Comments

*This post is locked for comments