5 Purchase Order

Mirror of Oracle documentation

Converted for search and offline reading. Authoritative source: Oracle. Diagrams and some complex tables are simplified — check the PDF when in doubt.

5 Purchase Order

Purchase orders can be created through the Merchandising UI or through integration with thirdparty systems. Once purchase orders are created in Merchandising, there are a number of batch processes that manage PO data.

Program Summary

The following batch designs are included in this functional area:

• Apply Deal Discounts to Purchase Orders (orddscnt)

• Auto Close Purchase Orders (ordautcl)

• Auto Close Purchase Orders (order_auto_close_job) - background job

• Build Purchase Orders for Vendor Generated Orders (vrplbld)

• Generate Pre-Issued Order Numbers (genpreiss)

• Purge Aged Open To Buy Data (otb_purge_job) - background purge process

• Purge Aged Open To Buy Data (otbprg)

• Purge Aged Purchase Orders (order_purge_job) - background purge process

• Purge Aged Purchase Orders (ordprg)

• Purge PO Induction Staging Tables (po_indctn_purge.ksh)

• Scale Purchase Orders Based on Supplier Constraints (supcnstr)

• Update Retail Values on Open Purchase Orders (ordupd)

• Write Purchase Order Information to Purchase Order History Tables (order_revision_job) - background process

• Write Purchase Order Information to Purchase Order History Tables (ordrev)

See also the Merchandising Operations Guide Volume 2 for details on the following purchase order related integrations:

• Download of Purchase Order from Merchandising to Suppliers (edidlord)

• Upload Purchase Order and Purchase Order Change Acknowledgements from Suppliers to Merchandising (ediupack)

• Upload of PO induction data through batch (poindbatch.ksh)

Transportation Upload (tranupld)

Schedule

Oracle Retail Merchandising Batch Schedule.

Design Overview

This program uploads trading-partner transportation data for merchandise from manufacturing through customs clearance. Version 3 supports changes to Vessel, Voyage or Flight, Estimated Departure Date, Order Number, Item, Container, Bill of Lading or Air Waybill, and Commercial Invoice. A key-field update must include every original key value together with the corresponding updated_* value.

The logical unit of work is a valid DTRAN record. Each record is read, validated, and processed independently. The recommended commit maximum is 1000, subject to implementation requirements.

Backward Compatibility

An additional runtime parameter identifies the file version. When omitted, the version defaults to 1, the initial version.

Integration Contract

The file is uploaded to Merchandising. The target is determined by the runtime parameter.

Input File Layout Summary Across Versions

The input includes a transaction trailer and file-type definition, transportation header details (partner, vessel, voyage or flight, departure and arrival dates, ports, service contract, container, and bill of lading or air waybill), and order or item details (order, item, commercial invoice, currency, quantities, weights, cubic volume, packing, and receipt attributes). Version 3 also provides the updated key fields used to locate the existing transportation data.

Apply Deal Discounts to Purchase Orders (orddscnt)

Module Name orddscnt.pc Description Apply Deal Discounts to Purchase Orders Functional Area Purchase Orders Module Type Business Processing Module Technology ProC Catalog ID RMS283 Wrapper Script rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This module applies deals to a purchase order by calculating the discounts and rebates that are applicable to a purchase order. It will fetch orders that need to be recalculated for cost from the DEAL_CALC_QUEUE table. Using the dealordlib shared library, it will update the unit cost and populate the ORDLOC_DISCOUNT and ORDHEAD_DISCOUNT tables.

Restart/Recovery

This program has inherent restart ability, since records are deleted from deal_calc_queue as they are processed. Recommended maximum commit counter is low.

Design Assumptions

N/A

Auto Close Purchase Orders (ordautcl)

Module Name ordautcl.pc Description Auto Close Purchase Orders Functional Area Purchase Orders Module Type Admin Module Technology ProC Catalog ID RMS282 Wrapper Script rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program is used to process POs that need to be deleted or closed that meet certain conditions. The criteria are as mentioned below:

Category 1

• The order is not in ‘C’ompleted status and was previously approved.

• The number of days between the latest ship date and the current date is greater than the ‘Approved PO Close Delay’ system parameter.

• There are no open shipments for the order.

• End of week date should not be null.

Category 2

• The order is not in ‘C’ompleted status and was previously approved.

• A specified amount of time (‘Approved PO Close Delay’ system parameter) after the not after date of the PO has passed.

• A specified amount of time (‘Partially Received PO Close Delay’ system parameter) after the not after date has passed.

• A specified amount of time (‘Partially Received PO Close Delay’ system parameter) after the expected receipt date (or shipped date if the expected date has not been captured) has passed.

• There are no open appointments in the system for the order.

Category 3

• The order has a status of worksheet or submitted, and the order has never been previously approved.

• The number of days between the current date and the order creation date is greater than the ‘Worksheet PO Clean Up Delay’ system parameter.

• The order is a manual order (not created by replenishment).

• End of week date should not be null.

Retrieved orders are subsequently processed based on their category:

1. Category 1 orders will be closed. Closing an order involves adjusting the order quantities, shipment quantities and OTB. Any allocation associated with the order will also be closed if it is released ‘X’ number of days before vdate. The ‘X’ number of days is defaulted from an external system and set on the Merchandising codes table for code_type ‘DEFT’.

2. For Category 2 orders, orders will be closed if there are no pending receipts or if the ‘Auto Close Partially Received’ system indicator is set to ‘Y’.

3. Category 3 orders will be deleted from the system.

Restart/Recovery

Restart recovery is implicit since the program purges and cancels records in the database one order at a time.

Design Assumptions

N/A

Auto Close Purchase Orders (order_auto_close_job)

Module Name order_auto_close_job Description Auto Close Purchase Orders Functional Area Purchase Orders Module Type Admin - Ad hoc Module Technology Background Processing Catalog ID N/A Wrapper Script b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two steps processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from order header table based on defined criteria. This program is used to process POs that need to be deleted or closed that meet certain conditions. The criteria are as mentioned below:

Category 1

• The order is not in ‘C’ompleted status and was previously approved.

• The number of days between the latest ship date and the current date is greater than the ‘Approved PO Close Delay’ system parameter.

• There are no open shipments for the order.

• End of week date should not be null.

Category 2

• The order is not in ‘C’ompleted status and was previously approved.

• A specified amount of time (‘Approved PO Close Delay’ system parameter) after the not after date of the PO has passed.

• A specified amount of time (‘Partially Received PO Close Delay’ system parameter) after the not after date has passed.

• A specified amount of time (‘Partially Received PO Close Delay’ system parameter) after the expected receipt date (or shipped date if the expected date has not been captured) has passed.

• There are no open appointments in the system for the order.

Category 3

• The order has a status of worksheet or submitted, and the order has never been previously approved.

• The number of days between the current date and the order creation date is greater than the ‘Worksheet PO Clean Up Delay’ system parameter.

• The order is a manual order (not created by replenishment).

• End of week date should not be null.

These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will process retrieved records based on their category:

1. Category 1 orders will be closed. Closing an order involves adjusting the order quantities, shipment quantities and OTB. Any allocation associated with the order will also be closed if it is released ‘X’ number of days before vdate. The ‘X’ number of days is defaulted from an external system and set on the RMFCS codes table for code type ‘DEFT’.

2. For Category 2 orders, orders will be closed if there are no pending receipts or if the ‘Auto Close Partially Received’ system indicator is set to ‘Y’.

3. Category 3 orders will be deleted from the system.

It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

N/A

Restart/Recovery

Key Tables Affected

Table 5-1 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_ORDER_AUTO_CLOSE_S TG	Yes	No	Yes	Yes
ORDHEAD	Yes	No	Yes	Yes
SHIPMENT	Yes	No	Yes	No
APPT_HEAD	Yes	No	No	No
APPT_DETAIL	Yes	No	No	No
SHIPSKU	Yes	No	Yes	No
ORDLOC	No	No	Yes	Yes
ALLOC_DETAIL	No	No	Yes	Yes
OBLIGATION_COMP	No	No	No	Yes
WO_DETAIL	No	No	No	Yes
WO_HEAD	No	No	No	Yes
WO_SKU_LOC	No	No	No	Yes
WO_WIP	No	No	No	Yes
ALLOC_CHRG	No	No	No	Yes
ALLOC_HEADER	No	No	No	Yes
ORDLOC_DISCOUNT	No	No	No	Yes
TIMELINE	No	No	No	Yes
ORDSKU_TEMP	No	No	No	Yes
ORDLOC_TEM	No	No	No	Yes
ALLOC_CHRG_TEMP	No	No	No	Yes
ALLOC_DETAIL_TEMP	No	No	No	Yes
ALLOC_HEADER_TEMP	No	No	No	Yes
ORDLOC_EXP_TEMP	No	No	No	Yes
ORDSKU_HTS_ASSESS_TEMP	No	No	No	Yes
ORDSKU_HTS_TEMP	No	No	No	Yes
ORDLOC_DISCOUNT_TEMP	No	No	No	Yes
TIMELINE_TEMP	No	No	No	Yes
REQ_DOC_TEMP	No	No	No	Yes
WO_DETAIL_TEMP	No	No	No	Yes
WO_HEAD_TEMP	No	No	No	Yes

Table 5-1 (Cont.) Key Tables Affected

Table	Select	Insert	Update	Delete
ORDLOC_WKSHT	No	No	No	Yes
ORDLOC_REV	No	No	No	Yes
ORDSKU_REV	No	No	No	Yes
ORDSKU	No	No	No	Yes
ORDCUST	No	No	No	Yes
ORDHEAD_REV	No	No	No	Yes
ORDLC	No	No	No	Yes
DEAL_COMP_PROM	No	No	No	Yes
DEAL_ITEMLOC	No	No	No	Yes
DEAL_THRESHOLD	No	No	No	Yes
DEAL_DETAIL	No	No	No	Yes
DEAL_QUEUE	No	No	No	Yes
DEAL_CALC_QUEUE	No	No	No	Yes
DEAL_HEAD	No	No	No	Yes
ORD_INV_MGMT	No	No	No	Yes
REPL_RESULTS	No	No	No	Yes
REV_ORDERS	No	No	No	Yes
REQ_DOC	No	No	No	Yes
ORD_PREISSUE	No	No	No	Yes

Design Assumptions

N/A

Build Purchase Orders for Vendor Generated Orders (vrplbld)

Module Name vrplbld.pc Description Build Purchase Orders for Vendor Generated Orders Functional Area Purchase Orders Module Type Business Processing Module Technology ProC Catalog ID RMS387 Wrapper Script rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This purpose of this module is to continue the process started by the batch program ediupack.pc of building purchase orders that reflect the vendor-generated orders as received through the EDI 855. This module will process records from the temporary EDI order table and create the purchase orders on the PO tables.

The post-processing function of this batch on the prepost batch truncates the EDI temporary order table.

Restart/Recovery

The logical unit of work for the program is a vendor order number, department and supplier combination. The program’s restartability is dependent on the value of the Department Level Orders system parameter. Allowing multi-department orders (that is, the indicator is set to ‘N’) will restart the program from the last successfully processed vendor order number and supplier. If the system requires a department on the orders (that is, the indicator is set to ‘Y’), then the program will restart from the last successfully processed vendor order number, department, and supplier.

Design Assumptions

N/A

Generate Pre-Issued Order Numbers (genpreiss)

Module Name genpreiss.pc Description Generate Pre-Issued Order Numbers Functional Area Purchase Orders Module Type Admin Module Technology ProC Catalog ID RMS237 Wrapper Script rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

Based on records on the SUPP_PREISSUE table, this batch program reserves order numbers for suppliers that do Vendor Managed Inventory (VMI) by placing these pre-generated order numbers on the ORD_PREISSUE table.

Restart/Recovery

The logical unit of work for this program is set at thesupplier level, based on a single record from the SUPP_PREISSUE table. It uses v_restart_supplier to achieve restart/recovery.

The changes will be posted when the commit_max_ctr value is reached and the value of the counter is subject to change based on implementation. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of file I/O.

Design Assumptions

N/A

Purge Aged Open To Buy Data (otb_purge_job)

Module Name otb_purge_job Description Purge Aged Open To Buy Data Functional Area Open To Buy Module Type Admin - Ad hoc Module Technology Background Processing Catalog ID N/A Wrapper Script b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two-step processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from open-to-buy (OTB) table based on calculated End-of-Week purge date as derived from the current date which is at least one half old. The current and previous half’s OTB data is retained and kept in the system. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the records from open-to-buy (OTB) table. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 5-2 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No

Table 5-2 (Cont.) Key Tables Affected

Table	Select	Insert	Update	Delete
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_OTB_PURGE_STG	Yes	Yes	No	Yes
OTB	No	No	No	Yes

Design Assumptions

N/A

Purge Aged Open To Buy Data (otbprg)

Module Name otbprg.pc Description Purge Aged Open To Buy Data Functional Area Open To Buy Module Type Admin Module Technology ProC Catalog ID RMS291 Wrapper Script rmswrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program runs at the end of the half to delete rows from the OTB table that are at least one half old. The current and previous half’s OTB data is retained. The number of days that OTB records are retained by Merchandising is not configurable via a system parameter.

Restart/Recovery

There is no restart/recovery in this module. Up to 10,000 records are deleted and committed at a time to avoid excessive rollback space in usage.

Design Assumptions

N/A

Purge Aged Purchase Orders (order_purge_job)

Module Name order_purge_job Description Purge Aged Purchase Orders Functional Area Purchase Orders

Module Type Admin - Ad hoc Module Technology Background Processing Catalog ID N/A Wrapper Script b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two-step processing. It will have a threading assignment and a business logic processing.

Thread assignment program will filter eligible records from order header and other associated and order-related tables based on its conditions below:

1. If importing is not enabled in the system (as defined by the import system indicator = ‘N’) and if invoice matching is not installed, then all details associated with an order are deleted when the order has been closed for more months than specified in ‘Order History Months’ purge parameter. Orders will only be deleted if all allocations associated, if any, have been closed.

2. If invoice matching is installed, then all details associated with an order are deleted when the order has been closed for more months than specified in the ‘Order History Months’ purge parameter. Orders are deleted only if allocations associated have been closed, shipments from the order have been completely matched to invoices or closed, and all those invoices have been posted.

3. If importing is enabled in the system (as defined by the import system indicator = ‘Y’) and if invoice matching is not installed, then all details associated with the order are deleted when the order has been closed for more months than specified in the ‘Order History Months’ purge option. This action presupposes that all ALC records associated with an order are in ‘Processed’ status, specified in the ALC header and allocations associated to the order, if any, have been closed.

4. If invoice matching is installed, then all details associated with an order are deleted when the order has been closed for more months than specified in the ‘Order History Months’ purge parameter. This action presupposes that all ALC records associated with an order are in ‘Processed’ status, specified in ALC head, all allocations associated to the order, if any, have been closed, all shipments from the order have been completely matched to invoices or closed, and all those invoices have been posted.

5. If the order to be purged is an import PO and it doesn’t have a letter of credit (LC) then purge the related records related to obligations, ALC and ICB transfers.

These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will delete the records from order header and other associated and order-related tables. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

N/A

Restart/Recovery

Key Tables Affected

Table 5-3 Key Tables Affected

Table	Select	Insert	Update	Delete
PURGE_CONFIG_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_ORDER_PURGE_STG	Yes	Yes	No	Yes
ORDHEAD	Yes	No	No	Yes
ORDLC	Yes	No	No	No
ALLOC_HEADER	Yes	No	No	Yes
SHIPMENT	Yes	No	No	Yes
SHIPSKU	Yes	No	Yes	Yes
INVC_HEAD	Yes	No	No	Yes
ORDLOC_REV	No	No	No	Yes
ORDHEAD_REV	No	No	No	Yes
ALLOC_REV	No	No	No	Yes
ALC_HEAD	Yes	No	No	Yes
ALC_COMP_LOC	No	No	No	Yes
OBLIGATION_COMP_LOC	No	No	No	Yes
OBLIGATION_COMP	No	No	No	Yes
OBLIGATION	No	No	No	Yes
TRANSPORTATION	Yes	No	No	Yes
MISSING_DOC	No	No	No	Yes
TRANS_PACKING	No	No	No	Yes
TRANS_DELIVERY	No	No	No	Yes
TRANS_CLAIMS	No	No	No	Yes
TRANS_LIC_VISA	No	No	No	Yes
TRANS_SKU	No	No	No	Yes
CE_ORD_ITEM	Yes	No	No	Yes
CE_LIC_VISA	No	No	No	Yes
CE_CHARGES	No	No	No	Yes
CE_SHIPMENT	No	No	No	Yes
CE_PROTEST	No	No	No	Yes
CE_FORMS	No	No	No	Yes
CE_HEAD	v	No	No	Yes

Table 5-3 (Cont.) Key Tables Affected

Table	Select	Insert	Update	Delete
APPT_HEAD	Yes	No	No	Yes
APPT_DETAIL	Yes	No	No	Yes
DOC_CLOSE_QUEUE	No	No	No	Yes
DAILY_PURGE	No	Yes	No	No
ORDSKU	Yes	No	No	Yes
ITEM_MASTER	Yes	No	No	No
PACKITEM	Yes	No	No	No
PACK_TMPL_HEAD	Yes	No	No	No
RTV_DETAIL	No	No	No	Yes
WO_DETAIL	No	No	No	Yes
CARTON	No	No	No	Yes
WO_HEAD	Yes	No	No	Yes
ALLOC_CHRG	No	No	No	Yes
ALLOC_DETAIL	No	No	No	Yes
TIMELINE	No	No	No	Yes
ORDLOC	No	No	No	Yes
ORDLOC_DISCOUNT	No	No	No	Yes
ORDLOC_EXP	No	No	No	Yes
ORDSKU_HTS_ASSESS	No	No	No	Yes
ORDSKU_HTS	No	No	No	Yes
REQ_DOC	No	No	No	Yes
ORDSKU_REV	No	No	No	Yes
ORDLOC_INVC_COST	No	No	Yes	Yes
ORDCUST	Yes	No	No	Yes
ORDCUST_DETAIL	No	No	No	Yes
ORDCUST_CUSTOMER_DETAI L	No	No	No	Yes
ORD_XDOCK_TEMP	No	No	No	Yes
INVC_XREF	No	No	No	Yes
INVC_MATCH_WKSHT	No	No	No	Yes
ORDLOC_WKSHT	No	No	No	Yes
SUP_VIOLATION	No	No	No	Yes
REV_ORDERS	No	No	No	Yes
LC_ORDAPPLY	No	No	No	Yes
ORDHEAD_DISCOUNT	No	No	No	Yes
RUA_RIB_INTERFACE	No	No	No	Yes
ORDLOC_TEMP	No	No	No	Yes
ALLOC_CHRG_TEMP	No	No	No	Yes
ALLOC_DETAIL_TEMP	No	No	No	Yes

Table 5-3 (Cont.) Key Tables Affected

Table	Select	Insert	Update	Delete
ALLOC_HEADER_TEMP	No	No	No	Yes
ORDSKU_TEMP	No	No	No	Yes
ORDLOC_EXP_TEMP	No	No	No	Yes
ORDSKU_HTS_ASSESS_TEMP	No	No	No	Yes
ORDSKU_HTS_TEMP	No	No	No	Yes
ORDLOC_DISCOUNT_TEMP	No	No	No	Yes
TIMELINE_TEMP	No	No	No	Yes
REQ_DOC_TEMP	No	No	No	Yes
WO_DETAIL_TEMP	No	No	No	Yes
WO_HEAD_TEMP	No	No	No	Yes
REPL_RESULTS_TEMP	No	No	No	Yes
DEAL_COMP_PROM	No	No	No	Yes
DEAL_HEAD	Yes	No	No	Yes
DEAL_THRESHOLD	No	No	No	Yes
DEAL_DETAIL	No	No	No	Yes
DEAL_QUEUE	No	No	No	Yes
ORD_INV_MGMT	No	No	No	Yes
REPL_RESULTS	No	No	No	Yes
INVC_DETAIL	No	No	No	Yes
INVC_NON_MERCH	No	No	No	Yes
INVC_MERCH_VAT	No	No	No	Yes
INVC_DETAIL_VAT	No	No	No	Yes
INVC_DISCOUNT	No	No	No	Yes
INVC_TOLERANCE	No	No	No	Yes
INVC_MATCH_QUEUE	No	No	No	Yes
TSFHEAD	No	No	No	Yes
TSFDETAIL	No	No	No	Yes
TSFDETAIL_CHRG	No	No	No	Yes
DEAL_ITEMLOC_ITEM	No	No	No	Yes
DEAL_ITEMLOC_DCS	No	No	No	Yes
DEAL_ITEMLOC_DIV_GRP	No	No	No	Yes
DEAL_ITEMLOC_PARENT_DIFF	No	No	No	Yes
ORDHEAD_L10N_EXT	No	No	No	Yes
ORD_TAX_BREAKUP	No	No	No	Yes
ORDHEAD_CFA_EXT	No	No	No	Yes
DEALHEAD_CFA_EXT	No	No	No	Yes
TSFHEAD_CFA_EXT	No	No	No	Yes
SHIPSKU_LOC_PRG_HIST	No	Yes	No	No
SHIPSKU_PRG_HIST	No	Yes	No	No

Table 5-3 (Cont.) Key Tables Affected

Table	Select	Insert	Update	Delete
SHIPMENT_PRG_HIST	No	Yes	No	No
ALLOC_CHRG_PRG_HIST	No	Yes	No	No
ALLOC_DETAIL_PRG_HIST	No	Yes	No	No
ALLOC_HEADER_PRG_HIST	No	Yes	No	No
ORDLOC_REV_PRG_HIST	No	Yes	No	No
ORDSKU_REV_PRG_HIST	No	Yes	No	No
ORDHEAD_REV_PRG_HIST	No	Yes	No	No
ORDCUST_DETAIL_PRG_HIST	No	Yes	No	No
ORDCUST_PRG_HIST	No	Yes	No	No
ORDLOC_CFA_EXT_PRG_HIST	No	Yes	No	No
ORDLOC_PRG_HIST	No	Yes	No	No
ORDLOC_DISCOUNT_PRG_HIS T	No	Yes	No	No
ORDLOC_EXP_PRG_HIST	No	Yes	No	No
ORDSKU_HTS_ASSESS_PRG_ HIST	No	Yes	No	No
ORDSKU_HTS_PRG_HIST	No	Yes	No	No
ORDSKU_CFA_EXT_PRG_HIST	No	Yes	No	No
ORDSKU_PRG_HIST	No	Yes	No	No
ORDHEAD_DISCOUNT_PRG_H IST	No	Yes	No	No
ORDHEAD_L10N_EXT_PRG_HI ST	No	Yes	No	No
ORD_TAX_BREAKUP_PRG_HIS T	No	Yes	No	No
ORDHEAD_CFA_EXT_PRG_HIS T	No	Yes	No	No
ORDHEAD_PRG_HIST	No	Yes	No	No

Design Assumptions

N/A

Purge Aged Purchase Orders (ordprg)

Module Name ordprg.pc Description Purge Aged Purchase Orders Functional Area Purchase Orders Module Type Admin Module Technology ProC Catalog ID RMS285

Runtime Parameters N/A

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The purpose of this module is to remove old purchase orders from the system.

If importing is not enabled in the system (as defined by the import system indicator = ‘N’) and if invoice matching is not installed, then all details associated with an order are deleted when the order has been closed for more months than specified in ‘Order History Months’ purge parameter. Orders will only be deleted if all allocations associated, if any, have been closed.

If invoice matching is installed, then all details associated with an order are deleted when the order has been closed for more months than specified in the ‘Order History Months’ purge parameter. Orders are deleted only if allocations associated have been closed, shipments from the order have been completely matched to invoices or closed, and all those invoices have been posted.

If importing is enabled in the system (as defined by the import system indicator = ‘Y’) and if invoice matching is not installed, then all details associated with the order are deleted when the order has been closed for more months than specified in the ‘Order History Months’ purge option. This action presupposes that all ALC records associated with an order are in ‘Processed’ status, specified in ALC_HEAD (status) and allocations associated to the order, if any, have been closed.

If invoice matching is installed, then all details associated with an order are deleted when the order has been closed for more months than specified in the ‘Order History Months’ purge parameter. This action presupposes that all ALC records associated with an order are in ‘Processed’ status, specified in ALC_HEAD (status), all allocations associated to the order, if any, have been closed, all shipments from the order have been completely matched to invoices or closed, and all those invoices have been posted.

If the order to be purged is an import PO and it doesn’t have a letter of credit (LC) then purge the related records related to obligations, ALC and ICB transfers.

Restart/Recovery

Restart ability will be implied, because the records that are selected from the driving cursor will be deleted before the commit. Restart library functions will still be included to ensure that rollback segments are not exceeded (by committing at intervals) and to perform basic record keeping functionality.

Design Assumptions

N/A

Purge PO Induction Staging Tables (po_indctn_purge.ksh)

Module Name

po_indctn_purge.ksh

Description Purge PO induction staging tables Functional Area Purchase Orders Module Type Admin Module Technology Shell Script Catalog ID RMS499 Wrapper Script rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

The purpose of this module is to remove old purchase order records from the staging tables. Records that are candidates for deletion are:

• Processes that have successfully been processed or processed with warnings that have been uploaded to Merchandising or downloaded to S9T

• Processes that have status = ‘PE’ processed with errors and have no liked data

• Processes in error status where all other related records containing the process ID have been processed successfully

• Processes that are passed the data retention days (system_options.proc_data_retention_days)

• All order records within a process where all related records for the order in the other staging tables are successfully uploaded to Merchandising. The process tracker record should not be deleted if there are other orders that are not uploaded to Merchandising.

Restart/Recovery

Restart ability will be implied, because the records that are selected from the cursor will be deleted before the commit.

Design Assumptions

N/A

Scale Purchase Orders Based on Supplier Constraints (supcnstr)

Module Name supcnstr.pc Description Scale Purchase Orders Based on Supplier Constraints Functional Area Purchase Orders Module Type Business Processing Module Technology ProC Catalog ID RMS368 Wrapper Script rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program will process all orders eligible for scaling during the nightly replenishment run. The purpose of this program is to select all of the orders created by the replenishment programs which are eligible for scaling. Once selected, the program will serve as a wrapper program and send each order number into the supplier constraint scaling library to actually perform the scaling on the order.

The orders which will be eligible for scaling are as follows:

If due order processing was used, only orders with a written date of today, origin type of zero (0) (replenishment order), due order processing indicator of Yes, due order indicator of Yes and a scale order to constraint indicator of Y will be processed. This encompasses all due orders created by replenishment which have constraints associated with them.

If due order processing was not used, only orders with a written date of today, origin type of zero (0) (replenishment order), order approve indicator of Yes, status of ‘W’orksheet, due order processing indicator of No, due order indicator of Yes, and a scale order to constraint indicator of Yes will be processed. This encompasses all approved orders created by replenishment which have constraints associated with them.

For Franchise POs, their associated Franchise Orders will be updated when quantities of the franchise POs are changed due to supplier constraint.

Restart/Recovery

The logic unit of work for this program is an order number.

Locking Strategy

This batch locks order inventory management and order header tables during day runs.

Design Assumptions

N/A

Update Retail Values on Open Purchase Orders (ordupd)

Module Name ordupc.pc Description Update Retail Values on Open Purchase Orders Functional Area Purchase Orders Module Type Business Processing Module Technology ProC Catalog ID RMS287 Wrapper Script rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This program will be used to automatically change all retail prices on purchase orders when a retail price change is implemented for an item on the order with the status of ‘Worksheet’,’ Submit’ and ‘Approve’.

Open to buy is updated to give a more accurate picture of the retail value of open orders if the order is ‘Approved’ and if the department calculate the OTB as retail.

Restart/Recovery

This program does not contain restart/recovery logic.

Design Assumptions

N/A

Write Purchase Order Information to Purchase Order History Tables (order_revision_job)

Module Name order_revision_job Description Write Purchase Order Information to Purchase Order History Tables Functional Area Purchase Orders Module Type Admin - Ad hoc Module Technology Background Processing Catalog ID N/A Wrapper Script b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of two-step processing. It will have a threading assignment and a business logic processing.

Order changes made by the client that may need to be sent to the vendor. The order changes should always be referred to as ‘versions’ and kept clearly distinct from order ‘revisions’ which are vendor changes uploaded via the ediupack program.

Thread assignment program will filter eligible records from order revision history and order header tables based on its order status (‘A’pproved or ‘C’losed) and supplier that exists from supplier table. When orders are approved or when approved orders are modified, this program

selects order numbers from the order revision history table. These records are chunked and Thread ID is assigned for each. They will be stored temporarily in a staging table.

The Business logic program will process all records from the staging table. Using bulk processing, this program will writes versions of approved orders and its current order information to the order/allocation revision history tables. After the new version has been written to the order revision tables, all records will be deleted from the order revision history table for that order record. If an order is not in approved status at the time the batch program runs, then none of the above processing will occur. These records will stay on the order revision history table until the PO is approved or deleted. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 5-4 Key Tables Affected

Table	Select	Insert	Update	Delete
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_ORDER_REVISION_STG	Yes	Yes	No	Yes
REV_ORDERS	Yes	No	No	Yes
ORDHEAD	Yes	No	Yes	No
SUPS	Yes	No	No	No
ORDHEAD_REV	Yes	Yes	No	No
ORDSKU	Yes	No	No	No
ORDLOC	Yes	No	No	No
ALLOC_HEADER	Yes	No	No	No
ALLOC_DETAIL	Yes	No	No	No
ORDSKU_REV	No	Yes	No	No
ORDLOC_REV	No	Yes	No	No
ALLOC_REV	No	Yes	No	No

Design Assumptions

N/A

Write Purchase Order Information to Purchase Order History Tables (ordrev)

Module Name

ordrev.pc

Description Write Purchase Order Information to Purchase Order History Tables Functional Area Purchase Orders Module Type Admin Module Technology ProC Catalog ID RMS286 Wrapper Script rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

Ordrev.pc will write versions of approved orders to the order revision history tables. When orders are approved or when approved orders are modified, this program selects order numbers from the REV_ORDERS table and writes current order information to the order/ allocation revision tables. After the new version has been written to the order revision tables, all records will be deleted from the REV_ORDERS table for that order_no.

This program processes order changes made by the client that may need to be sent to the vendor. The order changes should always be referred to as ‘versions’ and kept clearly distinct from order ‘revisions’ which are vendor changes uploaded via the ediupack program.

If an order is not in approved status at the time the batch program runs, then none of the above processing will occur. These records will stay on the REV_ORDERS table until the PO is approved or deleted.

Restart/Recovery

Restart ability will be implied because the records that are selected from the driving cursor will be deleted before the commit. Restart library functions will still be included to ensure that rollback segments are not exceeded (by committing at intervals) and to perform basic record keeping functionality.

Design Assumptions

N/A

---

In this guide

• Guide: Batch Operations Guide
• Previous: 4 Item Maintenance
• Next: 6 Deals

Related chapters

• 13 Transfers, Allocation, and RTV — Batch Operations Guide · shares ALLOC_CHRG, ALLOC_DETAIL, ALLOC_HEADER, APPT_DETAIL
• 5 ReSTful Web Services — Inbound and Outbound Integration Guide · shares ALC_COMP_LOC, ALC_HEAD, ALLOC_CHRG, ALLOC_CHRG_TEMP
• 6 Deals — Batch Operations Guide · shares DEAL_CALC_QUEUE, DEAL_COMP_PROM, DEAL_DETAIL, DEAL_HEAD
• 3 Custom Validation Rules — Merchandising Cloud Services Customization and Extension Guide · shares ALLOC_DETAIL, ALLOC_HEADER, ORDCUST_DETAIL, ORDHEAD_CFA_EXT
• F Appendix: Publication Tables and Triggers — Merchandising Cloud Services Data Conversion Implementation Guide · shares ALLOC_DETAIL, ALLOC_HEADER, ORDHEAD_CFA_EXT, RTV_DETAIL