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 `PO_FULL_LOAD_IND` to `Y` to instead provide full snapshots of only non-zero order lines and the system will zero out the rest of the orders automatically.
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 `DAY_DT` value on the detail file should be the same on all rows and be set to the business date the data is for. You also cannot reload the same date multiple times; the detail table follows the rules for positional facts as described in Positional Data Handling section below.
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 `A` (active) or `C` (closed). Active orders are used in the PO calculations. When sending daily PO files, you must include both active and closed order updates, because we need to know an order has been completed so it can stop being included in calculations.
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 `Y`, that order’s details will be used for the aggregated on-order values. If set to `N`, the order details will not be used (but will still be present in the database for other purposes like RI reporting and inventory optimization).

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 (`A`) or closed/cancelled (`C`). Only active orders will be used for Planning applications, even if a closed order still has on-order quantities on the records. It is important that you post closed orders to RAP when they close, so that we have an accurate status for all orders.
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 `OTB_EOW_DATE` is in the past, any remaining open order quantities will be pushed into the next possible week-ending date that is in the future, because those units cannot be received before that time.
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 `N` then the order will not be sent to Planning, but it can still be used in Retail Insights reporting or custom extensions.

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 `PRODUCT.csv` file. Should not be a pack item, even if the supplier would be delivering in packs. It is expected that inventory and purchase order data are always at the component item level. Pack item data should be spread to their components before sending this file.
ORG_NUM	Must be the location number where the order was placed from and will be received at. Must have a record in `ORGANIZATION.csv` file. It is usually a warehouse but can be a store if you allow direct-to-store deliveries.
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 `ORDER_HEAD.csv`.
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 `PO_ONORD_COST_AMT_LCL`) and how the `EXCH_RATE.csv` file data will be used. If you are providing the data in the source currency from the store or warehouse location, then `DOC_CURR_CODE` will be the currency of the source location. If the data is already all on one currency, then `DOC_CURR_CODE` will be that currency code. `LOC_CURR_CODE` is the primary reporting currency you wish RAP applications to operate in, so it will generally be a single hard-coded value on all your data files. Review the section on Exchange Rate dimension data for additional scenarios.

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 with status=A and the ORER_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, and ORDER_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 in ORDER_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 the PO_ONORD_* columns in ORDER_DETAIL and also update ORDER_HEAD to have a status of C. If this is not possible in your source system, you must configure the parameter PO_FULL_LOAD_IND to a value of Y 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 as C and the ORDER_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).