How to Retrieve Open Purchase Orders Through SQL SELECT Statement

Released Date: 9/11/2017

Q - How do I retrieve an Elliott purchase order from a third party system through ODBC or ADO.NET?

A - If you only need the purchase order header, you can use the following SELECT statement:
SELECT * FROM POORDHDR WHERE
ORD_HDR_ORDER_STS = 'P' AND
ORD_HDR_CHG_CANCL_CD <> 'X' AND
ORD_HDR_PRNT_PST_FG = 'X';
Here are more details about this select statement:
  • POORDHDR - This is the Elliott Purchase Order Header table.
  • ORD_HDR_ORDER_STS - Purchase Order Header status.  The possible values are: U - unreleased PO, R - released, P - Printed, C- closed, X - canceled.
  • ORD_HDR_CHG_CANCL_CD - Purchase Order Header Change Cancel Pending Code.  When a PO is marked as C - "Changed" or X - "Canceled," it is in a pending mode.  Only after the changed PO is printed and posted is the status finalized.  In the above SELECT statement, we are excluding the cancel pending PO.
  • ORD_HDR_PRNT_PST_FG - Purchase Order Header Print Post Flag.  When a PO is first printed, this flag is set to "P," but it still needs to be posted to be finalized.  Once the printed PO is posted, this flag is set to "X" = Posted.
The above SELECT statement will give you a list of POs that are open. However, it does not give you the detailed (POORDLIN - Purchase Order Line Item) information.  If you would like to to get both Purchase Order Header and Purchase Order Line Item info, you can use the following SELECT statement:
SELECT POORDHDR.*, POORDLIN.* FROM
POORDHDR, POORDLIN WHERE
ORD_HDR_ORDER_NO = ORD_LINE_ORDER_NO AND
ORD_HDR_REL_NO = ORD_LINE_REL_NO AND
ORD_HDR_ORDER_STS = 'P' AND
ORD_HDR_CHG_CANCL_CD <> 'X' AND
ORD_HDR_PRNT_PST_FG = 'X' AND
ORD_LINE_STS = 'P' AND
ORD_LINE_CHG_CNCL_CD <> 'X' AND
ORD_LINE_CLOSE_FLAG <> 'Y' AND
ORD_LINE_QTY_RECEIVD < ORD_LINE_QTY_ORDERED;
The above SELECT statement performs an inner join between POORDHDR and POORDLIN tables by using their keys:
ORD_HDR_ORDER_NO = ORD_LINE_ORDER_NO AND
ORD_HDR_REL_NO = ORD_LINE_REL_NO
In addition to the explanation above on POORDHDR, here are more details behind this select statement:
  • POORDLIN - This is the Elliott Purchase Order Line Item table.
  • ORD_LINE_STS - Purchase Order Line Item Status. The possible values are: N - New, P - Printed, X - Canceled.
  • ORD_LINE_CHG_CNCL_CD - Purchase Order Line Item Change Cancel Pending flag.  When a line item is marked as "C" - changed or "X" - canceled, it still needs to be printed and posted before it is fianlized.  
  • ORD_LINE_CLOSE_FLAG - Purchase Order Line Item Closed Flag.  The possible value is "Y" - line item is closed or blank.  A line item can be closed even if it is not fully received.
  • ORD_LINE_QTY_RECEIVD - Purchase Order Line Item Qty Received.
  • ORD_LINE_QTY_ORERED - Purchase Order Line Item Qty Ordered.
In the above SELECT statement, we excluded the purchase order line item that's fully received by using the condition ORD_LINE_QTY_RECEVD < ORD_LINE_QTY_ORDERED.  If you'd like to see those fully received line items as long as they are not specifically closed or canceled, then you should remove this condition.

EMK

Purchase Order

  1. Why Receiving Non-Inventory Items in PO Will Cause General Ledger Inventory Account Out of Balance?
  2. Order Has Been Printed But Not Posted
  3. When does the Purchase Line History data get updated?
  4. Can I Edit the Ship Date Field in PO Line Item Screen?
  5. Getting PO Lock File Problem During PO Printing
  6. Feature - Notes Tracking from PO Warehouse Receiving to Inventory Transfer Management to Inventory Transaction
  7. Feature - Prevent MM Item from Purchasing
  8. Feature - Include Or Exclude UDC in Buyer's Desk
  9. Feature - Item Number Verification After Serial Number Scan
  10. PO05S1 Purchase Order and Receiving Receivings Audit Trail Report
  11. PO05S2 Purchase Order and Receiving Drop Shipment Receivings Report
  12. PO0104 Purchase Order and Receiving Release Blanket Orders
  13. PO0105 Purchase Order and Receiving Purge Closed Purchase Orders
  14. PO0200 Purchase Order and Receiving Print Purchase Orders
  15. PO0300 Purchase Order and Receiving Purchase Order Inquiry
  16. PO0400 Purchase Order and Receiving Scheduled Receipts Reports
  17. PO0600 Purchase Order and Receiving Purchase Line History Report
  18. PO0700 Purchase Order and Receiving Cash Requirements Projection Report
  19. PO0800 Purchase Order and Receiving Commodity Code Vendor Report
  20. PO0900M Purchase Order and Receiving Vendor Performance Analysis Report
  21. PO1000 Purchase Order and Receiving P/O Setup
  22. PO1101 Purchase Order and Receiving Buyer Code File Maintenance
  23. PO1201 Purchase Order and Receiving Comment Code File Maintenance
  24. PO1301 Purchase Order and Receiving Reject Reason Code File Maintenance
  25. PO1401 Purchase Order and Receiving Ship-To File Maintenance
  26. PO1501 Purchase Order and Receiving Item Vendor File Maintenance
  27. PO1502 Purchase Order and Receiving Purge Item Vendor File Maintenance
  28. PO1601 Purchase Order and Receiving Vendor Service Address
  29. PO1700 Purchase Order and Receiving Clear Year-to-Date Statistics
  30. PO1800 Purchase Order and Receiving Open Purchase Orders Reports
  31. POCLSMNU Purchase Order and Receiving PO Close Processing
  32. POIMPMNU Purchase Order and Receiving Purchase Order Import
  33. POINVMNU Purchase Order and Receiving Invoice Processing
  34. POORDEXS Purchase Order and Receiving Purchase Order Export
  35. POEQMNT Purchase Order and Receiving Purchase Order Requisition
  36. POREQPST Purchase Order and Receiving Post Requisitions
  37. PORSTSCN Purchase Order and Receiving Reset P/O Rec/Inv Qty
  38. POUPDSTA Purchase Order and Receiving Update Vendor Statistics
  39. POWHRMNT Purchase Order and Receiving Warehouse Recovery Process
  40. POWRIMNT Purchase Order and Receiving Warehouse Receiving Import
  41. Feature -- PO Ship-to File Maintenance Determine Drop Ship Location by State/Zip Code Tables
  42. Feature - Warehouse Receiving Serial Number Validation
  43. Feature - Buyer's Desk Inventory Class Selection Criteria
  44. Feature - PO Ship-To File Received to Transit Location
  45. Purchase Order Line Item Add or Change Event
  46. PO0000 Purchase Order and Receiving Overview 1
  47. PO0000 Purchase Order and Receiving Overview 2
  48. PO0000 Purchase Order and Receiving Overview 3
  49. PO0000 Purchase Order and Receiving Overview 4
  50. PO0000 Purchase Order and Receiving Overview 5
  51. PO0000 Purchase Order and Receiving Overview : Index
  52. PO0100 P/O and Receiving Purchase Order Processing 1
  53. PO0100 P/O and Receiving Purchase Order Processing 2
  54. PO0100 P/O and Receiving Purchase Order Processing 3
  55. PO0100 P/O and Receiving Purchase Order Processing 4
  56. PO0100 P/O and Receiving Purchase Order Processing 5
  57. PO0100 P/O and Receiving Purchase Order Processing: Index
  58. PO2000 P/O and Receiving Receivings Processing 1
  59. PO2000 P/O and Receiving Receivings Processing 2
  60. PO2000 P/O and Receiving Receivings Processing 3
  61. PO2000 P/O and Receiving Receivings Processing 4
  62. PO2000 P/O and Receiving Receivings Processing 5
  63. PO2000 P/O and Receiving Receivings Processing 6
  64. PO2000 P/O and Receiving Receivings Processing 7
  65. PO2000 P/O and Receiving Receivings Processing 8
  66. PO2000 P/O and Receiving Receivings Processing 9
  67. PO2000 P/O and Receiving Receivings Processing: Index
  68. POFRMMNT P/O and Receiving P/O Form Setup 1
  69. POFRMMNT P/O and Receiving P/O Form Setup 2
  70. POFRMMNT P/O and Receiving P/O Form Setup 3
  71. POFRMMNT P/O and Receiving P/O Form Setup 4
  72. POFRMMNT P/O and Receiving P/O Form Setup 5
  73. POFRMMNT P/O and Receiving P/O Form Setup: Index
  74. PORCNMNU P/O and Receiving P/O Reconciliation Process 1
  75. PORCNMNU P/O and Receiving P/O Reconciliation Process 2
  76. PORCNMNU P/O and Receiving P/O Reconciliation Process 3
  77. PORCNMNU P/O and Receiving P/O Reconciliation Process: Index
  78. How to Retrieve Open Purchase Orders Through SQL SELECT Statement
  79. Feature - Receivings Processing Mass Receiving Changes
  80. Feature - Warehouse Receiving Mass Receiving
  81. How to Remove Outstanding PO from Received Not Invoiced Report
  82. Feature - Add Events for Addition of PO Header and Line
  83. Feature - Allow to Enter Unit Cost in PO Warehouse Receiving Add Mode

Feedback and Knowledge Base