Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
Next |
RMS is the system of record in the Oracle Retail Suite for Purchase Orders (POs). Purchase orders can be created via the RMS UI, integration with products such as Oracle Retail Advanced Inventory Planning or integration with other 3rd party systems. Once purchase orders are created in RMS, there are a number of batch processes that manage PO data.
The following batch designs are included in this functional area:
edidlord.pc (Download of Purchase Order from RMS to Suppliers)
ediupack.pc (Upload Purchase Order and Purchase Order Change Acknowledgements from Suppliers to RMS)
vrplbld.pc (Build Purchase Orders for Vendor Generated Orders)
genpreiss.pc (Generate Pre-Issued Order Numbers)supcnstr.pc (Scale Purchase Orders Based on Supplier Constraints)
supcnstr.pc (Scale Purchase Orders Based on Supplier Constraints)
orddscnt.pc (Apply Deal Discounts to Purchase Orders)
ordupd.pc (Update Retail Values on Open Purchase Orders)
ordautcl.pc (Auto Close Purchase Orders)
ordrev.pc (Write Purchase Order Information to Purchase Order History Tables)
ordprg.pc (Purge Aged Purchase Orders)
poindbatch.ksh(Upload of PO induction data through batch)
po_indctn_purge.ksh(Purge data from PO induction staging tables)
Module Name | edidlord.pc |
Description | Download of Purchase Order from RMS to Suppliers |
Functional Area | Purchase Order |
Module Type | Integration |
Module Technology | ProC |
Catalog ID | RMS46 |
Runtime Parameters | NA |
Orders created within the Oracle Retail system are written to a flat file if they are approved and marked as EDI orders. This module is used to write new and changed purchase order data to a flat file in the Oracle Retail standard format. The translation to EDI format is expected to take place via a 3rd party translation utility. The order revision tables and allocation revision tables are also used to ensure that the latest changes are being sent and to allow both original and modified values to be sent. These revision tables are populated during the online ordering process and the batch replenishment process whenever an order has been approved, and constitutes a history of all revisions to the order.
The program sums up all quantities to the physical warehouse level from the virtual warehouse level for an order, before writing it into the output file.
If shipments are to be pre-marked by the supplier for cross docking, then along with the order information: allocation, location and quantities are also sent.
If the backhaul type is specified as ”Calculated”, then the backhaul allowances will be calculated.
If the order contains pack items; hierarchical pack information is sent (this may include outer packs, inner packs, and fashion styles with associated pack templates as well as component item information).
If the order is a Drop Ship Customer Order (location is a non-stockholding store), the customer billing and delivery information will be written to the flat file.
The logical unit of work for this program is set at the supplier level. Threading is performed by the supplier using the v_restart_supplier view.
Restart ability is implied because the program updates ordhead.edi_sent_ind as records and are written out. The commit_max_ctr field should be set to prevent excessive rollback space usage, and to reduce the overhead of the file I/O. The recommended commit counter setting is 10000 records.
Table 6-2 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
ORDHEAD |
Yes |
No |
Yes |
No |
ORDHEAD_REV |
Yes |
No |
No |
No |
TERM |
Yes |
No |
No |
No |
SUPS |
Yes |
No |
No |
No |
ORDSKU |
Yes |
No |
No |
No |
ORDSKU_REV |
Yes |
No |
No |
No |
ITEM_MASTER |
Yes |
No |
No |
No |
ORDLOC |
Yes |
No |
No |
No |
ORDLOC_REV |
Yes |
No |
No |
No |
ORDLOC_DISCOUNT |
Yes |
No |
No |
No |
ORDCUST |
Yes |
No |
No |
No |
ALLOC_HEADER |
Yes |
No |
No |
No |
ALLOC_DETAIL |
Yes |
No |
No |
No |
ALLOC_REV |
Yes |
No |
No |
No |
WH |
Yes |
No |
No |
No |
PACKITEM_BREAKOUT |
Yes |
No |
No |
No |
SUPS_PACK_TMPL_DESC |
Yes |
No |
No |
No |
ITEM_SUPPLIER |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY_DIM |
Yes |
No |
No |
No |
STORE |
Yes |
No |
No |
No |
ADDR |
Yes |
No |
No |
No |
Integration Type | Download from RMS |
File Name | Determined by runtime parameter |
Integration Contract | IntCon000012 |
Table 6-3 File Layout
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
Record descriptor |
Char(5) |
FHEAD |
File head marker |
Line id |
Number(10) |
0000000001 |
Unique line id |
|
Translator id |
Char(5) |
DLORD |
Identifies transaction type |
|
File create date |
Char(14) |
NA |
Vdate in YYYYMMDDHH24MISS format |
|
TORDR |
Record descriptor |
Char(5) |
TORDR |
Order header information |
Line id |
Number(10) |
NA |
Unique file line id |
|
Transaction id |
Number(10) |
NA |
Unique transaction id |
|
Order change type |
Char(2) |
NA |
’CH' (changed) or ’NW' (new) |
|
Order number |
Number(12) |
NA |
Internal Oracle Retail order no |
|
Supplier |
Number(10) |
NA |
Internal Oracle Retail supplier id |
|
Vendor order id |
Char(15) |
NA |
External vendor_order_no (if available) |
|
Order written date |
Char(14) |
NA |
Order created date in YYYYMMDDHH24MISS format |
|
Original order approval date |
Char(14) |
NA |
Original order approval date in YYYYMMDDHH24MISS format |
|
Old Currency Code |
Char(3) |
NA |
Old order currency_code (ISO standard) |
|
New Currency Code |
Char(3) |
NA |
Changed order currency_code (ISO standard) |
|
Old Shipment Method of Payment |
Char(2) |
NA |
Old ship_pay_method |
|
New Shipment Method of Payment |
Char(2) |
NA |
Changed ship_pay_method |
|
Old Transportation Responsibility |
Char(2) |
NA |
Old fob_trans_res |
|
Old Transportation Responsibility Description |
Char(250) |
NA |
Old fob_trans_res_desc |
|
New Transportation Responsibility |
Char(2) |
NA |
Changed fob_trans_res |
|
New Trans. Resp. Description |
Char(250) |
NA |
New fob_trans_res_desc |
|
Old Title Passage Location |
Char(2) |
NA |
Old fob_title_pass |
|
New Title Passage Location |
Char(2) |
NA |
Changed fob_title_pass |
|
Old Title Passage Description |
Char(250) |
NA |
Old fob_title_pass_desc |
|
New Title Passage Description |
Char(250) |
NA |
Changed fob_title_pass_desc |
|
Old not before date |
Char(14) |
NA |
Old not_before_date in YYYYMMDDHH24MISS format |
|
New not before date |
Char(14) |
NA |
Changed not_before_date in YYYYMMDDHH24MISS format |
|
Old not after date |
Char(14) |
NA |
Old not_after_date in YYYYMMDDHH24MISS format |
|
New not after date |
Char(14) |
NA |
Changed not_after_date in YYYYMMDDHH24MISS format |
|
Old Purchase type |
Char(6) |
NA |
Old Purchase type |
|
New Purchase type |
Char(6) |
NA |
New Purchase type |
|
Backhaul allowance |
Char(20) |
NA |
Backhaul allowance |
|
Old terms description |
Char(240) |
NA |
Old terms description from terms table |
|
New terms description |
Char(240) |
NA |
New terms description from terms table |
|
Old pickup date |
Char(14) |
NA |
Old pickup date YYYYMMDDHH24MISS |
|
New pickup date |
Char(14) |
NA |
New pickup date YYYYMMDDHH24MISS |
|
Old ship method |
Char(6) |
NA |
Old ship method |
|
New ship method |
Char(6) |
NA |
New ship method |
|
Old comment description |
Char(2000) |
NA |
Old comment description |
|
New comment description |
Char(2000) |
NA |
New comment description |
|
Supplier DUNS number |
Char(9) |
NA |
Supplier DUNS number |
|
Supplier DUNS location |
Char(4) |
NA |
Supplier DUNS location |
|
Customer order number |
Char(48) |
NA |
Master customer order number from the Order Management System |
|
TITEM |
File record descriptor |
Char(5) |
TITEM |
Item info |
Line id |
Number(10) |
NA |
Unique line id |
|
Transaction id |
Number(10) |
NA |
Unique transaction id |
|
Item Number Type |
Char(6) |
NA |
Item_number_type |
|
Item |
Char(25) |
NA |
Item (For a pack item, this will be the pack number) |
|
Old Ref Item Number type |
Char(6) |
NA |
Item_number_type for old ref_item |
|
Old Ref Item |
Char(25) |
NA |
Old Ref_Item |
|
New Ref Item Number type |
Char(6) |
NA |
Item_number_type for new ref_item |
|
New Ref Item |
Char(25) |
NA |
Changed Ref_Item |
|
Vendor catalog number |
Char(30) |
NA |
Supplier_item (VPN) |
|
Free Form Description |
Char(250) |
NA |
Item_desc |
|
Supplier Diff 1 |
Char(120) |
NA |
Supplier's diff 1 |
|
Supplier Diff 2 |
Char(120) |
NA |
Supplier's diff 2 |
|
Supplier Diff 3 |
Char(120) |
NA |
Supplier's diff 3 |
|
Supplier Diff 4 |
Char(120) |
NA |
Supplier's diff 4 |
|
Pack Size |
Number(12) |
NA |
Supplier defined pack size * 10000 (4 implied decimal places) |
|
TPACK |
File record descriptor |
Char(5) |
TPACK |
Pack component info |
Line id |
Number(10) |
NA |
Unique line id |
|
Transaction id |
Number(10) |
NA |
Unique transaction id |
|
Pack id |
Char(25) |
NA |
Packitem_breakout.pack_no (same as item for the pack item) |
|
Inner pack id |
Char(25) |
NA |
Inner pack identification |
|
Pack Quantity |
Number(12) |
NA |
Packitem_breakout.pack_item_qty*10000 (4 implied decimal places) |
|
Component Pack Quantity |
Number(12) |
NA |
Packitem_breakout.comp_pack_qty*10000 (4 implied decimal places) |
|
Item Parent Part Quantity |
Number(12) |
NA |
Packitem_breakout.item_parent_pt_qty*10000 (4 implied decimal places) |
|
Item Quantity |
Number(12) |
NA |
Packitem_breakout.item_qty*10000 (4 implied decimal places) |
|
Item Number Type |
Char(6) |
NA |
Item number type |
|
Item |
Char(25) |
NA |
Item |
|
Ref Item Number Type |
Char(6) |
NA |
Ref_item_number_type |
|
Ref Item |
Char(25) |
NA |
Ref_item |
|
VPN |
Char(30) |
NA |
Supplier item (vpn) |
|
Supplier Diff 1 |
Char(120) |
NA |
Supplier's diff 1 |
|
Supplier Diff 2 |
Char(120) |
NA |
Supplier's diff 2 |
|
Supplier Diff 3 |
Char(120) |
NA |
Supplier's diff 3 |
|
Supplier Diff 4 |
Char(120) |
NA |
Supplier's diff 4 |
|
Item Parent |
Char(25) |
NA |
Required when Pack Template is not NULL |
|
Pack template |
Number(8) |
NA |
Pack template associated w/style (packitem_breakout.pack_tmpl_id) |
|
Template description |
Char(250) |
NA |
Description of pack template. sups_pack_tmpl_desc.supp_pack_desc |
|
TSHIP |
Record type |
Char(5) |
TSHIP |
Describes the file record-shipment information |
Line id |
Number(10) |
NA |
Unique file line number |
|
Transaction id |
Number(10) |
NA |
Unique transaction number |
|
Location type |
Char(2) |
NA |
’ST' store or ’WH' warehouse |
|
Ship to location |
Number(10) |
NA |
Location value form ordloc (store or warehouse – For warehouse,if multichannel option is ON, physical warehouse value is taken from warehouse) |
|
Old unit cost |
Number(20) |
NA |
Old unit cost*10000 (4 implied decimal places) |
|
New unit cost |
Number(20) |
NA |
New unit cost*10000 (4 implied decimal places) |
|
Old quantity |
Number(12) |
NA |
Old qty_ordered *10000 or qty_allocated*10000 (4 implied decimal places) |
|
New quantity |
Number(12) |
NA |
Changed qty_ordered*10000 or qty_allocated*10000 (4 implied decimal places) |
|
Old outstanding quantity |
Number(12) |
NA |
Old (qty_ordered-qty_received)*10000 or (qty_allocated-qty transferred)*10000 for an allocation (4 implied decimal places) |
|
New outstanding quantity |
Number(12) |
NA |
Changed qty_ordered-qty_received (4 implied decimal places)(or qty_allocated-qty_transferred, for an allocation) |
|
Cancel code |
Char(1) |
NA |
NA |
|
Old cancelled quantity |
Number(12) |
NA |
Previous quantity cancelled (4 implied decimal places) |
|
New cancelled quantity |
Number(12) |
NA |
Changed quantity cancelled (4 implied decimal places) |
|
Quantity type flag |
Char(1) |
NA |
’S'hip to ’A'llocate |
|
Store or warehouse indicator |
Char(2) |
NA |
’ST' (store) or ’WH' (warehouse) |
|
Old x-dock location |
Number(10) |
NA |
Alloc_detail location (store or wh) |
|
New x-dock location |
Number(10) |
NA |
Alloc_detail location (store or wh) |
|
Case length |
Number(12) |
NA |
Case length (4 implied decimal places) |
|
Case width |
Number(12) |
NA |
Case width (4 implied decimal places) |
|
Case height |
Number(12) |
NA |
Case height (4 implied decimal places) |
|
Case LWH unit of measure |
Char(4) |
NA |
Case LWH unit of measure |
|
Case weight |
Number(12) |
NA |
Case weight (4 implied decimal places) |
|
Case weight unit of measure |
Char(4) |
NA |
Case weight unit of measure |
|
Case liquid volume |
Number(12) |
NA |
Case liquid volume (4 implied decimal places) |
|
Case liquid volume unit of measure |
Char(4) |
NA |
Case liquid volume unit of measure |
|
Location DUNS number |
Char(9) |
NA |
Location DUNS number |
|
Location DUNS loc |
Char(4) |
NA |
Location DUNS loc |
|
Old unit cost init |
Number(20) |
NA |
Old unit cost init (4 implied decimal places) |
|
New unit cost init |
Number(20) |
NA |
New unit cost init (4 implied decimal places) |
|
Item/loc discounts |
Number(20) |
NA |
Item/loc discounts (4 implied decimal places) |
|
TCUST |
Record type |
Char(5) |
TCUST |
Describes the file record-customer order information |
Line id |
Number(10) |
NA |
Unique file line number |
|
Transaction id |
Number(10) |
NA |
Unique transaction number |
|
Delivery first name |
Char(120) |
NA |
First name for the delivery address on the order |
|
Delivery phonetic first name |
Char(120) |
NA |
Phonetic first name for the delivery address on the order |
|
Delivery last name |
Char(120) |
NA |
Last name for the delivery address on the order |
|
Delivery phonetic last name |
Char(120) |
NA |
Phonetic last name for the delivery address on the order |
|
Delivery preferred name |
Char(120) |
NA |
Preferred name for the delivery address on the order |
|
Delivery company name |
Char(120) |
NA |
Company name for the delivery address on the order |
|
Delivery address Line 1 |
Char(240) |
NA |
First line of the delivery address of the customer |
|
Delivery address Line 2 |
Char(240) |
NA |
Second line of the delivery address of the customer |
|
Delivery address Line 3 |
Char(240) |
NA |
Third line of the delivery address of the customer |
|
Delivery county |
Char(250) |
NA |
County portion of the delivery address |
|
Delivery city |
Char(120) |
NA |
City portion of the delivery address |
|
Delivery state |
Char(3) |
NA |
State portion of the delivery address |
|
Delivery country ID |
Char(3) |
NA |
Country portion of the delivery address |
|
Delivery post |
Char(30) |
NA |
Postal code portion of the delivery address |
|
Delivery jurisdiction |
Char(10) |
NA |
Jurisdiction code of the delivery country-state relationship |
|
Delivery phone |
Char(20) |
NA |
Phone number in the delivery information |
|
Billing first name |
Char(120) |
NA |
First name for the billing address on the order |
|
Billing phonetic first name |
Char(120) |
NA |
Phonetic first name for the billing address on the order |
|
Billing last name |
Char(120) |
NA |
Last name for the billing address on the order |
|
Billing phonetic last name |
Char(120) |
NA |
Phonetic last name for the billing address on the order |
|
Billing preferred name |
Char(120) |
NA |
Preferred name for the billing address on the order |
|
Billing company name |
Char(120) |
NA |
Company name for the billing address on the order |
|
Billing address Line 1 |
Char(240) |
NA |
First line of the billing address of the customer |
|
Billing address Line 2 |
Char(240) |
NA |
Second line of the billing address of the customer |
|
Billing address Line 3 |
Char(240) |
NA |
Third line of the billing address of the customer |
|
Billing county |
Char(250) |
NA |
County portion of the billing address |
|
Billing city |
Char(120) |
NA |
City portion of the billing address |
|
Billing state |
Char(3) |
NA |
State portion of the billing address |
|
Billing country ID |
Char(3) |
NA |
Country portion of the billing address |
|
Billing post |
Char(30) |
NA |
Postal code portion of the billing address |
|
Billing jurisdiction |
Char(10) |
NA |
Jurisdiction code of the billing country-state relationship |
|
Billing phone |
Char(20) |
NA |
Phone number in the billing information |
|
TTAIL |
Record type |
Char(5) |
TTAIL |
Describes file record – marks end of order |
Line id |
Number(10) |
NA |
Unique file line id |
|
Transaction id |
Number(10) |
NA |
Unique transaction id |
|
#Lines in transaction |
Number(10) |
NA |
Number of lines in transaction |
|
FTAIL |
Record type |
Char(5) |
FTAIL |
Describes file record – marks end of file |
Line id |
Number(10) |
NA |
Unique file line id |
|
#lines |
Number(10) |
NA |
Total number of transaction lines in file (not including FHEAD and FTAIL) |
For a new order, the ”old” fields should be blank. For a changed order, both old and new fields should hold values. If the value has changed, ”old” values come from the revision tables for the latest revision before the current one (the last one sent), while new orders come from the ordering tables.
FHEAD - REQUIRED: File identification, one line per file.
TORDR - REQUIRED: Order level information, one line per order.
TITEM - REQUIRED: Item description, multiple lines per order possible.
TPACK - OPTIONAL: Pack contents, multiple lines per order possible. This line will be written only for pack items.
TSHIP - REQUIRED: Ship to location and quantity, allocation location, multiple lines per item possible. Allocation information is optional on this line-will exist if premark_ind is 'Y'.
TCUST - OPTIONAL: Customer order information, one line per order. This line will be written only for Drop Ship Customer Orders.
TTAIL - REQUIRED: Order end, one line per order.
FTAIL - REQUIRED: End of file marker, one line per file.Output File Layout
Module Name | ediupack.pc |
Description | Upload Purchase Order and Purchase Order Change Acknowledgements from Suppliers to RMS |
Functional Area | Purchase Orders |
Module Type | Integration |
Module Technology | ProC |
Catalog ID | RMS48 |
Runtime Parameters | NA |
This program has four functions:
to acknowledge vendor receipt of a buyer-generated order without changes,
to acknowledge vendor receipt of a buyer-generated order with date, cost or quantity modifications,
to notify buyer of a vendor-generated order, and
to acknowledge order cancellations.
All acknowledgements update the ORDHEAD table with acknowledgement information.
When the supplier sends the acknowledgement with modifications, they can send the entire purchase order or only the changes. The file details are matched to the current order. If the Not Before Date, Not After Date, Quantity, Price, and item all match the current order, then no changes were submitted. If one of the variables is blank, for example the price, assume that no pricing changes were made. As soon as one of the variables does not match, the order has been changed. These changes will not be written directly to the order; they will be written to the revision tables. Revisions will be accepted in the on-line ordering screens and changed orders will be resubmitted via EDIDLORD.
Vendor generated orders will create new orders by inserting new records on the EDI temporary order tables.
For Customer Order POs created through an external Order Management System (OMS) and Franchise Order POs, the modifications to the dates, quantity and cost are applied automatically (and will not need to be accepted online). Also, changes to Franchise POs through this program will not affect their associated Franchise orders.
The files will not have enough volume to warrant the implementation of restart recovery for commit/rollback considerations but minimal file-based restart/recovery capability will be added. The logical unit of work is a complete transaction represented by detail lines between the transaction header and transaction tail.
A savepoint will be issued before each transaction header record is successfully processed. If a non-fatal error occurs, a rollback to the last savepoint will be issued so that the rejected records are not posted to the database. If a fatal error occurs and restart is necessary, processing will restart at the last commit point.
Table 6-5 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
PERIOD |
Yes |
No |
No |
No |
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
EDI_ORD_TEMP |
No |
Yes |
Yes |
No |
DAILY_PURGE |
Yes |
No |
No |
No |
ITEM_MASTER |
Yes |
No |
No |
No |
ITEM_LOC |
Yes |
No |
No |
No |
ITEM_SUPPLIER |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY_LOC |
Yes |
Yes |
Yes |
No |
ORDHEAD |
Yes |
No |
Yes |
No |
ORDLOC |
Yes |
No |
No |
No |
ORDSKU |
Yes |
No |
No |
No |
ORDHEAD_REV |
Yes |
Yes |
No |
No |
ORDLOC_REV |
No |
Yes |
Yes |
No |
ORDSKU_REV |
No |
Yes |
No |
No |
ORG_UNIT |
Yes |
No |
No |
No |
PARTNER_ORG_UNIT |
Yes |
No |
No |
No |
SUPS |
Yes |
No |
No |
No |
PRICE_HIST |
No |
Yes |
No |
No |
ITEM_LOC_SOH |
No |
Yes |
No |
No |
STORE |
Yes |
No |
No |
No |
WH |
Yes |
No |
No |
No |
Integration Type | Upload to RMS |
File Name | Determined by runtime parameter |
Integration Contract | IntCon000014 |
Table 6-6 ediupack - Input File
Record Name | Field Name | Field Type | Default Value | Description |
---|---|---|---|---|
FHEAD |
File head descriptor |
Char(5) |
FHEAD |
Describes file line type |
Line id |
Number(10) |
0000000001 |
Sequential file line number |
|
File Type Definition |
Char(4) |
ORAK |
Identifies file as ’Order Acknowledgment Import' |
|
THEAD |
File record descriptor |
Char(5) |
THEAD |
Describes file line type |
Line id |
Number(10) |
Line number in file |
Sequential file line number |
|
Transaction number |
Number(10) |
NA |
Sequential transaction number |
|
Acknowledge type |
Char(2) |
NA |
AP-product replenishment AK- Acknowledge or change CA-cancel order (no detail) |
|
Order number |
Char(15) |
NA |
May be external order number (vendor order number) OR Oracle Retail order number |
|
Written_date |
Char(8) |
NA |
Written date in YYYYMMDD format |
|
Supplier number |
Number(10) |
NA |
Supplier number |
|
Not before date |
Char(8) |
NA |
Not_before_date YYYYMMDD |
|
Not after date |
Char(8) |
NA |
Not_after_date YYYYMMDD |
|
Purchase type |
Char(6) |
NA |
Specifies type of purchase – may be blank |
|
Pickup date |
Char(8) |
NA |
Pickup_date YYYYMMDD – may be blank |
|
TITEM |
File record descriptor |
Char(5) |
TITEM |
Describes file line type |
Line id |
Number(10) |
Line number in file |
Sequential file line number |
|
Transaction number |
Number(10) |
NA |
Sequential transaction number |
|
ITEM |
Char(25) |
NA |
Item (either item or ref_item must be defined) |
|
Ref_item |
Char(25) |
NA |
Reference item (either item or ref_item must be defined) |
|
Vendor catalog number |
Char(30) |
NA |
VPN (Vendor Product Number) |
|
Unit cost value |
Number(20) |
NA |
Unit_cost * 10000 (4 implied decimal places) |
|
Loc_type |
Char(2) |
NA |
’ST' for store, ’WH' for warehouse |
|
Location |
Number(10) |
NA |
If NULL, apply to all locations for this item |
|
Pickup location |
Char(250) |
NA |
Location to pick up item – may be blank |
|
TSHIP |
File record descriptor |
Char(5) |
TSHIP |
Describes file line type |
Line id |
Number(10) |
Line number in file |
Sequential file line number |
|
Transaction number |
Number(10) |
NA |
Sequential transaction number |
|
Store/wh indicator |
Char(2) |
NA |
’ST' for store, ’WH' for warehouse |
|
Ship to location |
Number(10) |
NA |
Store or warehouse number |
|
Quantity |
Number(12) |
NA |
Quantity ordered * 10000 (4 implied decimal places) |
|
TTAIL |
File record descriptor |
Char(5) |
TTAIL |
Describes file line type |
Line id |
Number(10) |
Line number in file |
Sequential file line number |
|
Transaction number |
Number(10) |
NA |
Sequential transaction number |
|
Lines in transaction |
Number(6) |
NA |
Total number of lines in this transaction |
|
FTAIL |
File record descriptor |
Char(5) |
FTAIL |
Marks end of file |
Line id |
Number(10) |
Line number in file |
Sequential file line number |
|
Number of transactions |
Number(10) |
´NA |
Number of lines between FHEAD and FTAIL |
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 |
Runtime Parameters | NA |
The 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 EDI_ORD_TEMP table and create the purchase orders on the PO tables.
prepost vrplbld post - truncates EDI_ORD_TEMP table.
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 dept_level_orders column on the PROCUREMENT_UNIT_OPTIONS. Allowing multi-department orders (’N') will restart the program from the last successfully processed vendor order number and supplier. If the system requires a department on the orders (’Y'), then the program will restart from the last successfully processed vendor order number, department, and supplier.
Table 6-8 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
ITEM_MASTER |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
ITEM_SUPP_COUNTRY_LOC |
Yes |
No |
No |
No |
SUP_IMPORT_ATTR |
Yes |
No |
No |
No |
SUPS |
Yes |
No |
No |
No |
EDI_ORD_TEMP |
Yes |
No |
No |
No |
WH |
Yes |
No |
No |
No |
ORDSKU |
Yes |
Yes |
Yes |
No |
ORDHEAD |
Yes |
Yes |
Yes |
No |
ORDLOC |
No |
Yes |
No |
No |
DEAL_CALC_QUEUE |
Yes |
Yes |
Yes |
No |
PERIOD |
Yes |
No |
No |
No |
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
PROCUREMENT_UNIT_OPTIONS |
Yes |
No |
No |
No |
L10N_DOC_DETAILS_GTT |
Yes |
Yes |
No |
No |
MV_L10N_ENTITY |
Yes |
No |
No |
No |
COUNTRY_ATTRIB |
Yes |
No |
No |
No |
L10N_PKG_CONFIG |
Yes |
No |
No |
No |
TSFHEAD |
Yes |
No |
No |
No |
ORDHEAD_L10N_EXT |
No |
Yes |
No |
No |
TSFHEAD_L10N_EXT |
No |
Yes |
No |
No |
MRT_L10N_EXT |
No |
Yes |
No |
No |
FM_SYSTEM_OPTIONS |
Yes |
No |
No |
No |
REV_ORDERS |
No |
No |
No |
Yes |
ORDLOC_REV |
No |
Yes |
No |
No |
ORDSKU_REV |
No |
Yes |
No |
No |
ORDHEAD_REV |
Yes |
Yes |
No |
No |
Module Name | genpreiss.pc |
Description | Generate Pre-Issued Order Numbers |
Functional Area | Purchase Orders |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS237 |
Runtime Parameters | NA |
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.
Table 6-9 Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
As needed |
Scheduling Considerations |
This module can be run at any stage in the batch schedule. It is independent of other programs. If a custom program is created to download the pre-issued numbers, it will need to be run after genpreiss.pc |
Pre-Processing |
NA |
Post-Processing |
NA |
Threading Scheme |
Multi-threaded by supplier |
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.
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 |
Runtime Parameters | NA |
This batch program will process all orders eligible for scaling during the nightly replenishment run. The purpose of this program will be 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 = 0 (replenishment order), due order processing indicator = ’Y', due order indicator = ’Y' and a scale order to constraint indicator = ’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 = 0 (replenishment order), ord_approve_ind = ’Y', status = ’W'orksheet, due order processing indicator = ’N', due order indicator = ’Y', and a scale order to constraint indicator = ’Y' 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.
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 |
Runtime Parameters | NA |
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.
Table 6-13 Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
Daily |
Scheduling Considerations |
This program should run after DITINSRT. It should run before DISCOTBAPPLY, and before DEALCLS or DEALPRG in the deals batch schdeule. |
Pre-Processing |
Ditinsrt, sccext, reclsdly |
Post-Processing |
Discotapply, dealcls |
Threading Scheme |
Multithreaded by supplier |
This program has inherent restart ability, since records are deleted from deal_calc_queue as they are processed. Recommended maximum commit counter is low.
Table 6-14 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
DISC_OTB_APPLY |
No |
Yes |
No |
No |
REV_ORDERS |
No |
Yes |
No |
No |
ORD_LC_AMENDMENTS |
No |
Yes |
Yes |
Yes |
DEAL_CALC_QUEUE |
Yes |
No |
No |
Yes |
ORDHEAD |
Yes |
No |
No |
No |
SUPS |
Yes |
No |
No |
No |
CURRENCIES |
Yes |
No |
No |
No |
ORDLOC_INVC_COST |
No |
Yes |
Yes |
Yes |
ORDLOC |
Yes |
No |
Yes |
No |
ORDLOC_DISCOUNT |
No |
Yes |
Yes |
Yes |
ORDHEAD_DISCOUNT |
No |
Yes |
No |
Yes |
ORDLOC_DISCOUNT_BUILD |
No |
Yes |
No |
Yes |
ORD_LC_AMENDMENTS |
No |
Yes |
Yes |
Yes |
L10N_DOC_DETAILS_GTT |
Yes |
Yes |
No |
No |
MV_L10N_ENTITY |
Yes |
No |
No |
No |
COUNTRY_ATTRIB |
Yes |
No |
No |
No |
L10N_PKG_CONFIG |
Yes |
No |
No |
No |
TSFHEAD |
Yes |
No |
No |
No |
FM_SYSTEM_OPTIONS |
Yes |
No |
No |
No |
WH |
Yes |
No |
No |
No |
EXCHANGE_RATES |
Yes |
No |
No |
No |
STATE |
Yes |
No |
No |
No |
COUNTRY |
Yes |
No |
No |
No |
ADDR |
Yes |
No |
No |
No |
COUNTRY_TAX_JURISDICTION |
Yes |
No |
No |
No |
VAT_CODES |
Yes |
No |
No |
No |
ELC_COMP |
Yes |
No |
No |
No |
FM_FISCAL_UTILIZATION |
Yes |
No |
No |
No |
RURAL_PROD_IND |
Yes |
No |
No |
No |
RETAIL_SERVICE_REPORT_URL |
Yes |
No |
No |
No |
ORD_TAX_BREAKUP |
Yes |
Yes |
Yes |
No |
GTAX_ITEM_ROLLUP |
Yes |
Yes |
Yes |
No |
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 |
Runtime Parameters | NA |
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.
Table 6-15 Scheduling Constraints
Schedule Information | Description |
---|---|
Frequency |
Daily |
Scheduling Considerations |
This program should be run after RPM price change extraction process to ensure that all price changes have been handled by batch processing. |
Pre-Processing |
sccext |
Post-Processing |
Otbdnld, otbdlsal, otbdlord |
Threading Scheme |
Multithreaded on location |
Module Name | ordautcl.pc |
Description | Auto Close Purchase Orders |
Functional Area | Purchase Orders |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS282 |
Runtime Parameters | NA |
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:
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.
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.
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:
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 RMS codes table for code_type ’DEFT'.
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'.
Category 3 orders will be deleted from the system.
Restart recovery is implicit since the program purges and cancels records in the database one order at a time.
Table 6-18 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
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 |
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 |
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 |
Runtime Parameters | NA |
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 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.
Table 6-20 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
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 |
FIF_ORDHEAD |
No |
Yes |
No |
No |
Module Name | ordprg.pc |
Description | Purge Aged Purchase Orders |
Functional Area | Purchase Orders |
Module Type | Admin |
Module Technology | ProC |
Catalog ID | RMS285 |
Runtime Parameters | NA |
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 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.
Table 6-22 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
PURGE_CONFIG_OPTIONS |
Yes |
No |
No |
No |
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 |
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 |
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 |
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 |
Module Name | poindbatch.ksh |
Description | Upload Order Data |
Functional Area | Purchase Order Maintenance |
Module Type | Integration |
Module Technology | Ksh |
Catalog ID | RMS234 |
Runtime Parameters | Database connection,
Input File Name, Template Name, Destination (Optional Input Parameter.) |
This batch program is used to Bulk upload xml file data from template files to S9T_FOLDER table (into content_xml column).
This batch will be responsible for validating the input parameters, below are the list of validations.
The Input file should exist.
The Input file's extension must be ”.xml”.
The template_name should be valid. Function S9T_PKG.CHECK_TEMPLATE is called for validation.
Destination (Optional Parameter) should be STG or RMS. If destination is not passed then default it to STG.
Once xml data is loaded into S9T_FOLDER table, the script will do post processing by calling the packages listed below:
PO_INDUCT_SQL.INIT_PROCESS - This initialize a row in svc_process_tracker for asynchronous processing.
PO_INDUCT_SQL.EXEC_ASYNC - This function calls the main induction process that uploads data into the staging tables, validates and inserts data into the base RMS purchase order tables.
Table 6-24 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
S9T_FOLDER |
No |
Yes |
No |
No |
S9T_TEMPLATE |
Yes |
No |
No |
No |
SVC_PROCESS_TRACKER |
No |
Yes |
No |
No |
SVC_ORDHEAD |
Yes |
Yes |
Yes |
Yes |
SVC_ORDDETAIL |
Yes |
Yes |
Yes |
Yes |
SVC_ORDLC |
Yes |
Yes |
Yes |
Yes |
SVC_ORDLOC_EXP |
Yes |
Yes |
Yes |
Yes |
SVC_ORDSKU_HTS |
Yes |
Yes |
Yes |
Yes |
SVC_ORDSKU_HTS_ASSESS |
Yes |
Yes |
Yes |
Yes |
ORDHEAD |
Yes |
Yes |
Yes |
Yes |
ORDSKU |
Yes |
Yes |
Yes |
Yes |
ORDLOC |
Yes |
Yes |
Yes |
Yes |
ORDLC |
Yes |
Yes |
Yes |
Yes |
ORDLOC_EXP |
Yes |
Yes |
Yes |
Yes |
ORDSKU_HTS |
Yes |
Yes |
Yes |
Yes |
ORDSKU_HTS_ASSESS |
Yes |
Yes |
Yes |
Yes |
CORESVC_PO_ERR |
Yes |
Yes |
No |
No |
S9T_ERRORS |
Yes |
Yes |
No |
No |
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 |
Runtime Parameters | NA |
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 RMS 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 RMS. The process tracker record should not be deleted if there are other orders that are not uploaded to RMS.
Restart ability will be implied, because the records that are selected from the cursor will be deleted before the commit.
Table 6-26 Key Tables Affected
Table | Select | Insert | Update | Delete |
---|---|---|---|---|
PROC_DATA_RETENTION_DAYS |
Yes |
No |
No |
No |
SVC_PROCESS_TRACKER |
Yes |
No |
No |
Yes |
SVC_ORDHEAD |
Yes |
No |
No |
Yes |
SVC_ORDDETAIL |
Yes |
No |
No |
Yes |
SVC_ORDLOC_EXP |
Yes |
No |
No |
Yes |
SVC_ORDLC |
Yes |
No |
No |
Yes |
SVC_ORDSKU_HTS |
Yes |
No |
No |
Yes |
SVC_ORDSKU_HTS_ASSESS |
Yes |
No |
No |
Yes |
SVC_CFA_EXT |
No |
No |
No |
Yes |
CORESVC_PO_ERR |
No |
No |
No |
Yes |
S9T_ERRORS |
Yes |
No |
No |
Yes |
CORESVC_PO_CHUNKS |
Yes |
No |
No |
Yes |
S9T_FOLDER |
Yes |
No |
No |
Yes |