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