How to Retrieve Open Purchase Orders Through SQL SELECT Statement

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


    • Related Articles

    • How to Investigate or Debug Report Desk Problem by Using SQL Statement

      Released Date: 1/25/2025 Version: 8.6 & Higher Report Desk is based on the PSQL relational engine. It sends a SQL statement to the PSQL engine to retrieve data. Sometimes, you don't get the result back as you expected, so what next? For example, when ...
    • CPOPNSSN Customer Order Processing Open Orders by Salesman Report

      Open Orders by Salesman Report Application Overview This report featuring only open orders can be used as a sales analysis auditing tool, measure productivity, and as a customer service tool. You can print the report for a single salesman or a range ...
    • How to Find All Open Sales Orders

      Release Date: 04/24/2025 Version: 8.x & Up Q - I am inquiring if there is a way in Elliot to find out all the open Sales Orders. We are trying to see if there are any orders that have not been closed yet and also have visible of all of the items that ...
    • Enforce UPC in the Purchase Order Print for EDI Trading Partners

      Release Date: 08/22/2022 Version 8.5 & Higher Some EDI trading partners may require that the UPC be provided on outbound EDI purchase orders (850). An 850 may be rejected when a UPC is not provided for a line item. Since the purchase order is ...
    • How to Optimize SQL SELECT Statement for BOMP Product Structure

      Release Date: 11/29/2017 Q - I used the following SQL SELECT statement to retrieve product structure from the BOMP module. Generally speaking, it works. But the performance is not what I would like it to be. Is there anyway to make it run faster? ...