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.
/*----------------------------------------------------------------------------
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 Link | Purchase Order Details Script
Best Regards,
Mahmoud M. AlSaadi
Review:
ReplyDeleteFROM 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!
Thank you so much - i turned this into a view and then a SmartList - beautiful!
ReplyDeleteYou are most welcome Linda :)
DeleteBest Regards,
Mahmoud M. AlSaadi
Hello, could you explain what the quantities mean? Like what does it mean when some receipt has QuantityMatch = 200 for example.
ReplyDeletethank you
Hello,
ReplyDeleteIs 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.
I need help plz
ReplyDeleteOne 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?
ReplyDeleteHey 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.
ReplyDeleteWe have some RCT and invoices without PO numbers, those are being excluded from this query. Do we have query to include them too?
ReplyDeleteHello 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
ReplyDeletePremiumhackservices@gmail.com
My name is Eric Bardet. I’m incredibly, unbelievably happy right now:
ReplyDeleteI never thought something like this would happen to me. I’m usually very cautious. One day, I came home from work, and when I turned on my computer, a warning message appeared: My PC was compromised. I was advised to call Microsoft Support. The number looked legitimate, and the person I talked to sounded very professional. He said my crypto wallet had been hacked and compromised, and I needed to transfer my assets to a secure wallet immediately.
Panicking and afraid of losing everything, I followed his instructions. I transferred my $42,000 in cryptocurrencies to the wallet he had given me, unaware that it was fake. Within minutes, everything was gone. I realized too late that it was a scam.
The worst part wasn’t just the loss of money. It was the feeling of being cheated and trusting someone who was taking advantage of me. I reported it to the FBI and the FTC, but they told me the chances of getting my money back were very slim.
Then I came across ICC Recovery Services. At first, I didn’t trust anyone. I’d been scammed before. But they were different. They explained everything clearly, tracked the transactions on the blockchain, and even worked with the exchanges involved. I couldn’t believe it when, just two weeks later, they had recovered almost 85% of my lost money.
If you ever find yourself in this situation, please remember: No reputable tech support will ever ask you to transfer your money. And if you’ve been scammed, don’t give up. There are people who can help. ICC Recovery Services helped me, and maybe they can help you too. Services like ICC Recovery can make all the difference.
Contact: ICC Recovery:
IRON-CLAD-CYBER--TECHIE-.com IRON-CLAD-CYBER-RECOVERY--GMAIL-.com
Thanks to their quick action, I was able to get my money back. Without ICC Recovery, I would have been unhappy.