Skip to content

How to Retrieve Open Purchase Orders Through SQL SELECT Statement

← Purchase Order
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

Feedback and Knowledge Base

(thinking…)