Purchase Order Data Requirements
Purchase order data (ORDER_HEAD.csv
and ORDER_DETAIL.csv
) is used to capture the raw
PO details at the lowest level. Purchase orders are used in Planning, IPO, and RI applications. For Planning, the PO data
is aggregated from the order level to a forward-looking summary based on the open-to-buy (OTB) week date. The raw details
are used as-is in RI and IPO.
Rule | Explanation |
---|---|
Daily Data Requirements |
It is expected that the PO header and detail files start as full daily snapshots of
all active or recently closed orders. The detail data is maintained positionally so that, if no update is received, we will
continue to carry forward the last known value. Once daily batches have started, you can transition the PO details file only
to an incremental update file (header file must always be a complete snapshot). When sending data incrementally, you must
include all order updates for a given date, both for open and closed orders. If an order changed at the header level (such
as closing or cancelling the order), you should send all the detail lines in that order even if some didn’t change. This includes
when order lines are fully received and move to 0 units remaining, these changes must be sent to RAP. If you are unable to
satisfy these incremental data requirements, you may change the parameter |
Historical Data and Past Dates |
The Order interfaces do not support loading historical data or data with past dates
on the detailed order-line records. Every time you load orders, it is for the current set of data for a single business date.
The |
Order Status |
The header file for POs has a variety of attributes but one of the most important is the status,
which should be either |
OTB EOW Date |
The OTB end-of-week date is used for the Planning aggregations to create a forward-looking view of expected receipts from POs. Open order quantities are aggregated to the OTB week before being exported to Planning. If the OTB week has elapsed, the order quantities are included in next week’s OTB roll-up regardless of how far in the past the date is, because the earliest that PO can come as receipts is in the next business week. |
Include On Order Indicator |
There is a required flag on the order header file to tell the system that an order
should be included in calculations for Planning or not. When the flag is set to |
The ORDER_HEAD.csv
and ORDER_DETAIL.csv
files both have a minimum set of required fields
to make the integrations within RAP function, so those will be listed out below with additional usage notes. The two files
are tightly coupled and it’s expected that you send both at the same time; you will never send only one of them.
Table 8-10 ORDER_HEAD.csv
Column Header | Usage |
---|---|
ORDER_NO |
Unique identifier of a purchase order in the source system. This must be the same number used across the entire life of the order, so that you can post updates and revisions to it over time. If your source system changes the order number for any reason, be aware that you may need to keep track of old order numbers to post the updates to RAP for the right orders. |
STATUS |
Tells the system if the order is active ( |
OTB_EOW_DATE |
The week-ending date where the open order quantities should be considered for Open To Buy (OTB)
planning. This will drive the aggregation of the purchase order data into future weeks before it is loaded into Planning applications.
When the |
INCLUDE_ON_ORDER_IND |
A flag to instruct the system on which orders should be considered for Open to Buy and any other
open on-order calculations. If the flag is |
Table 8-11 ORDER_DETAIL.csv
Column Header | Usage |
---|---|
ITEM |
Must be the transaction level item or SKU number and must have a record in the |
ORG_NUM |
Must be the location number where the order was placed from and will be received at. Must have a record in |
DAY_DT |
The current business date in the system for this data load. You cannot send order data for any other date except the current business date, as this is a positional table like Inventory. Past/future dates will not be accepted. The purpose of this column is mainly for reference and archival purposes (for example, when looking at old data files you will know which business date this set of records was for). |
ORDER_NO |
A cross-reference to the order number on |
PO_ONORD_QTY PO_ONORD_COST_AMT_LCL PO_ONORD_RTL_AMT_LCL |
The current outstanding order quantities and total cost/retail value of the units on order. These values represent the expected units that have not been received yet. Because this interface is positional (like inventory) we will carry forward the last known quantities every day unless you update the system with new records. This means that, even when the order is received or cancelled, we must eventually get a record that moves all of these columns to 0, or we will carry forward the non-zero values forever. |
DOC_CURR_CODE LOC_CURR_CODE |
The currency codes linked to the cost and retail amounts on the order data. The values
in these fields will control how the system converts the amount (such as |
It’s also necessary to understand the lifecycle of a purchase order and how that should be reflected in the data files over time. RAP will require data to be sent for each step in the order process as outlined below.
-
When the order is approved, the
ORDER_HEAD
file should contain a row for the order withstatus=A
and theORER_DETAIL
should contain the items on the order with non-zero quantities for the on-order amounts. -
As the lines of the order are received,
ORDER_HEAD
should continue to have the row for the order with every update, andORDER_DETAIL
should be sent with the order lines that require changes from the last known value. If you have the ability to detect which order lines changed, you only need to send those. RAP will remember and carry forward any order lines that were not updated. If you can’t detect the changes to order lines, just send all lines in the order every time. -
If any lines are cancelled from the order, you must send that update as a set of zero values on the
PO_ONORD_*
columns inORDER_DETAIL
to zero out the cancelled lines in RAP. Similarly, if the entire order is canceled or closed before being fully received, you must send all lines of the order with zero values on thePO_ONORD_*
columns inORDER_DETAIL
and also updateORDER_HEAD
to have a status ofC
. If this is not possible in your source system, you must configure the parameterPO_FULL_LOAD_IND
to a value ofY
in Manage System Configurations, then you will be allowed to send full loads of only non-zero order lines and the system will zero out the rest. -
As order lines start to be received normally, send the new order quantities for each change, including when a line is fully received and moves to 0 units on order. When an order becomes fully received we need all rows of data in RAP to move to 0 for that order’s values, so that we stop including it in future on-order rollups. If
PO_FULL_LOAD_IND=Y
then we don’t need zero balance updates from you, just stop sending the order details when it reaches zero and we will zero it out automatically. -
When an order is finally fully-received and closed, send one final update where
ORDER_HEAD
shows the status asC
and theORDER_DETAIL
data is moved to 0 units on order for any lines not updated yet.
Depending on your source system, it can be difficult to detect all of these changes to the purchase orders over time and
send only incremental updates. In such cases, you may always post all orders to RAP which are active or have been closed within
recent history and we will merge the data into the system on top of the existing order records. Then the main requirement
that must be accounted for is the cancelling or removal of order lines from an order, which must still be tracked and sent
to RAP even if your source system deletes the data (unless PO_FULL_LOAD_IND=Y
).