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
Mahmoud M. AlSaadi
Hi Mahmood,
ReplyDeleteThis 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
I will consider this for a future post including an SQL view for requisition workflow details.
DeleteStay tuned
Mahmoud M. AlSaadi
MaHmood,
ReplyDeleteIa 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?
BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast
ReplyDeleteyou 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