| Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
 Previous |
 Next |
| Oracle® Retail Merchandising System Operations Guide, Volume 1 - Batch Overviews and Designs 16.0.024 E89599-02 |
|
Previous |
Next |
To scale up business operations and market presence, particularly in new markets, retailers may choose to utilize business partners to manage branded or co-branded stores while retaining the retailer's business processes and value proposition. Businesses who partner with a retailer to expand the retailer's presence are known as franchisees. Franchisees may operate one or more stores under the retailer's banner. RMS supports two types of franchise management:
Franchise inventory is managed by the retailer
For this scenario, the retailer owns/manages the retail experience through planning, ordering, selling and tracking of inventory at franchise stores. In RMS, it is assumed that franchise customer locations will be set up as stockholding stores, with a store type of "Franchise".
Franchise inventory is not managed by the retailer
For this case, the retailer does not own or manage inventory, but mandatorily requires a franchise customer to adhere to business processes across franchise stores. This may also include retailers with smaller scale wholesale operations constitute a small fraction of the retailers business. For both these scenarios, it is assumed that non-stockholding stores will be setup in RMS to represent these franchise (or wholesale) customer locations.
The batch processes that are used for Franchise Management in RMS fall primarily into the following areas:
RMS maintains customer groups and customers pertaining to franchise operations as a hierarchy above customer locations. Customer groups and customers can be entered in RMS or uploaded from an external system. Customer locations are set up as franchise stores in RMS and can be designated as either stockholding or non-stockholding.
For all items that are 'sold' to franchise customer locations from a retailer, a selling price must be determined. The default selling price for franchise stores is calculated and held on FUTURE_COST as the pricing cost. To calculate the cost, RMS uses the concept of templates and it is a template's association with a franchise store and merchandise hierarchy that determines the value on FUTURE_COST. Cost templates and their relationships with franchise locations/merchandise hierarchies can be entered into RMS or uploaded via a batch process.
Franchise orders need to be raised in order to fulfill demand from a franchise customer. A franchise order is considered a sales order between the retailer and the franchise customer. A franchise order contains the item requisition to be sourced from a certain location (vendor, company warehouse or store) and fulfilled at one or more franchise stores by one or more required need dates. A franchise order also contains the price at which the items on the order will be sold to the franchise customer. Franchise Orders can be entered into RMS via one of the following methods:
Manually via the Franchise Sales Order screen.
From an external application using the WF Order Upload (wfordupld) batch.
Automatically through replenishment, store orders, item requests, AIP generated POs/Transfers and Allocations for stockholding franchise stores.
Once a franchise order is created and approved, a transfer (for warehouse or store sourced orders) or purchase order (for supplier sourced orders) will be created to manage the inventory movement. All franchise orders must be for a single customer.
Franchise returns are used whenever inventory moves from a franchise store back to a company owned location. Franchise returns cannot be created directly back to a supplier, it is assumed they will always first come back to a company owned location. Unlike franchise orders, which can be created for multiple franchise stores, franchise returns are always from a single franchise store. A franchise return contains the items being returned and the return price. If known, the original franchise order is referenced with the return and the price from the original order is used as a default. Like franchise orders, franchise returns can be created in three different ways:
Manually via the Franchise Returns screen.
From an external application using the WF Return Upload (wfretupld) batch.
Automatically through store-initiated transfers or transfers sent from an external system for stockholding franchise stores.
The following batch designs are included in this functional area:
fcosttmplupld.ksh (Upload Cost Buildup Template)
fcosttmplprocess.ksh (Process Cost Buildup Template Upload)
fcosttmplpurge.ksh (Purge Staged Cost Template Data)
fcustomerupload.ksh (Franchise Customer Upload)
fcustomerprocess.ksh (Process Uploaded Franchise Customers and Customer Groups)
fcustupldpurge.ksh (Franchise Customer Staging Purge)
wfordupld.ksh (Franchise Order Upload)
wf_apply_supp_cc.ksh (Apply Supplier Cost Change to Franchise Orders)
wfordcls.pc (Franchise Order Close)
wfordprg.pc (Franchise Order Purge)
wfretupld.ksh (Franchise Return Upload)
wfretcls.pc (Franchise Return Close)
wfrtnprg.pc (Franchise Return Purge)
wfslsupld.ksh (Upload of Franchise Sales to RMS)
wfbillex.ksh (Franchise Billing Extract)
| Module Name | fcosttmplupld.ksh |
| Description | Upload Cost Buildup Template |
| Functional Area | Franchise Management |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS125 |
| Runtime Parameters | DB Connection and Input File name |
This module uploads cost buildup templates and franchise cost relationships used for franchise pricing from an external system into RMS staging tables. It also performs both technical and business validation of the data sent in the file; for example, it validates that start and end dates are included for new and updated templates.
The restart recovery is different from the conventional RMS batch. There are three points on the batch upload process where users can evaluate the successful load of the data.
SQL load - SQL load dumps invalid records that do not meet certain technical requirements (for example:. file layout issues, data type inconsistencies, and so on.). The rejected record is written either to a bad file or to a discard file. The discard file contains records that do not satisfy conditions such as missing or invalid record types. Records with other technical issues are written to the bad file. Note that a non-fatal code is returned by the program and a message will be written to the log file if reject files are created.
User Action: When such conditions exist, the user may update either the bad or discard file and attempt to reload using the same files.
Business Validation Level - the data from the files are loaded into the staging tables for validation. PL/SQL functions determine if this loaded data is valid enough to be inserted into the actual RMS tables. Records that do not meet certain technical or business validations are rejected and the information is updated back into the staging table with an appropriate error message and the batch issues a NON-FATAL return code.
User Action: When this condition exists, the user can fix the data upload file and try to reload.
Chunking validated data - At this point the data from staging tables that have passed business validation are chunked based on the number of valid transactions (cost templates) and max_chunk_size from RMS_PLSQL_BATCH_CONFIG table. If there are no valid transactions to be chunked, batch issues a FATAL return code.
User Action: When this condition exists, the user can fix the data upload file and try to reload.
Table 21-2 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
SVC_WF_COST_TMPL_UPLD_FHEAD |
Yes |
Yes |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_THEAD |
Yes |
Yes |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_TDETL |
Yes |
Yes |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_TTAIL |
Yes |
Yes |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_FTAIL |
Yes |
Yes |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_STATUS |
Yes |
Yes |
Yes |
Yes |
|
ELC_COMP |
Yes |
No |
No |
No |
|
STORE |
Yes |
No |
No |
No |
|
CLASS |
Yes |
No |
No |
No |
|
SUBCLASS |
Yes |
No |
No |
No |
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
RMS_PLSQL_BATCH_CONFIG |
Yes |
No |
No |
No |
| Integration Type | Upload to RMS |
| File Name | Determined by runtime parameter |
| Integration Contract | IntCon000021 |
Table 21-3 SQUL Loader Input File Layout
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
File Header |
File Type Record Descriptor |
Char(5) |
NA |
Identifies file record type. Valid value is FHEAD. |
|
File Line Identifier |
Number(10) |
NA |
Sequential file line number |
|
|
File Type Definition |
Char(5) |
CTMPL |
Identifies file as 'Cost Template Upload' |
|
|
File Create Date |
Date |
SYSDATE |
Date on which the file was created by external system |
|
|
Transaction Header |
File Record Descriptor |
Char(5) |
NA |
Identifies transaction header record type. Valid value is THEAD |
|
File Line Identifier |
Number(10) |
NA |
Sequential file line number |
|
|
Message Type |
Char(30) |
NA |
Identifies the action that will be performed on the franchise cost template header information that is provided as part of this record It can be either create or update or delete a franchise cost template. Valid message types are: costtmpadd (for additions), costtmpmod (for updates), costtmpdel (for deletions) |
|
|
Template ID |
Number(10) |
NA |
Template ID |
|
|
Template Description |
Char(120) |
NA |
Template Description |
|
|
Template Type |
Char(1) |
NA |
Indicates the type of the template. Valid values are M = Margin then Up-Charge, U = Up-charges, then Margin, R = % of Retail and C = Cost |
|
|
Percentage |
Number(12,4) |
NA |
Margin percent or % off Retail value; required if template type is M, U and R types of templates |
|
|
Cost |
Number(20,4) |
NA |
Indicates the franchise cost for an item when template type is 'C' This is mandatory and should only be populated if template type is 'C' |
|
|
Final Cost |
Char(1) |
NA |
Signifies if the cost is final or acquisition. Valid values are 'Y' or 'N' |
|
|
Transaction Detail |
File Record Descriptor |
Char(5) |
Identifies transaction detail record type. Valid value is TDETL |
|
|
File Line Identifier |
Number(10) |
Sequential file line number |
||
|
Message Type |
Char(30) |
Identifies the action that will be performed on the franchise cost template relationship information that is provided as part of this record. It can be either create or update or delete a cost relationship. Valid values are: costtmpreladd (for additions), costtmprelmod (for updates), costtmpreldel (for deletions) |
||
|
Dept |
Number(4) |
Department associated with the cost template |
||
|
Class |
Number(4) |
Class associated with the cost template |
||
|
Subclass |
Number(4) |
Subclass associated with the cost template |
||
|
Item |
Char(25) |
Unique number that identifies a valid item associated with the template. Used for template types of 'C' only |
||
|
Location |
Number(10) |
Franchise Store Number associated with the template |
||
|
Start Date |
Date |
Date on which a cost template will be effective for the subclass/item and franchise store (required for update and delete of a cost relationship) |
||
|
End Date |
Date |
Date on which a cost template will expire for a subclass/item and franchise store (required for update and delete of a cost relationship) |
||
|
New Start Date |
Date |
New Date on which a franchise cost relationship will be effective |
||
|
New End Date |
Date |
New Date on which a franchise cost relationship will expire |
||
|
Cost Component ID |
Char(10) |
Unique code which signifies the up-charge cost component when First_Applied is 'U' This should only be populated if First Applied is 'U' |
||
|
Transaction Trailer |
File Record Descriptor |
Char(5) |
NA |
Identifies transaction trailer record type. Valid value is TTAIL |
|
File Line Identifier |
Number(10) |
NA |
Sequential file line number |
|
|
Transaction Record Counter |
Number(10) |
NA |
Number of TDETL records in this transaction set |
|
|
File Trailer |
File Record Descriptor |
Char(5) |
NA |
Identifies file trailer record type. Valid value is TTAIL |
|
File Line Identifier |
Number(10) |
NA |
Sequential file line number |
|
|
File Record Counter |
Number(10) |
NA |
Number of records/transactions processed in current file (only records between FHEAD & FTAIL) |
| Module Name | fcosttmplprocess.ksh |
| Description | Process Cost Buildup Template Upload |
| Functional Area | Franchise Management |
| Module Type | Business Processing |
| Module Technology | ksh |
| Catalog ID | RMS224 |
This module processes franchise cost buildup templates and franchise cost relationships that were uploaded from an external source into staging tables and loads them from the staging tables into RMS base tables. The module is designed to process inserts, updates and deletes for these data elements.
Table 21-4 Scheduling Constraints
| Schedule Information | Description |
|---|---|
|
Frequency |
Daily |
|
Scheduling Considerations |
This program only needs to be scheduled if the client uploads franchise cost information from an external system Should be run after fcosttmplupld.ksh |
|
Pre-Processing |
fcosttmplupld.ksh |
|
Post-Processing |
NA |
|
Threading Scheme |
NA |
The restart recovery is different from the conventional RMS batch. During the batch process users can evaluate the successful processing of data in the following way:
PL/SQL function will load the data from staging tables into RMS tables. For records that result (insert/update/delete) in constraint error or are not found in the RMS tables (for update/delete) are rejected and the information is updated back in the corresponding staging table with appropriate error message. Also, records that do not meet certain business validations (which can only be validated during data processing) are rejected and the information is updated back in the corresponding staging table with appropriate error message.
User Action: When this condition exists, the user can fix the data upload file and try to reload and process the data.
Table 21-5 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
SVC_WF_COST_TMPL_UPLD_FHEAD |
Yes |
No |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_THEAD |
Yes |
No |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_TDETL |
Yes |
No |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_TTAIL |
Yes |
No |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_FTAIL |
Yes |
No |
Yes |
No |
|
SVC_WF_COST_TMPL_UPLD_STATUS |
Yes |
No |
Yes |
No |
|
WF_COST_BUILDUP_TMPL_HEAD |
Yes |
Yes |
Yes |
Yes |
|
WF_COST_BUILDUP_TMPL_DETAIL |
Yes |
Yes |
Yes |
Yes |
|
WF_COST_RELATIONSHIP |
Yes |
Yes |
Yes |
Yes |
|
GTT_WF_COST_RELATIONSHIP |
No |
Yes |
No |
No |
|
COST_EVENT_COST_RELATIONSHIP |
No |
Yes |
No |
No |
|
COST_EVENT |
No |
Yes |
No |
No |
|
COST_EVENT_RESULT |
No |
Yes |
No |
No |
|
COST_EVENT_THREAD |
No |
Yes |
No |
Yes |
|
FUTURE_COST_GTT |
No |
Yes |
No |
No |
|
FUTURE_COST |
No |
No |
No |
Yes |
| Module Name | fcosttmplpurge.ksh |
| Description | Purge Staged Cost Template Data |
| Functional Area | Franchise Management |
| Module Type | Admin |
| Module Technology | ksh |
| Catalog ID | RMS225 |
| Runtime Parameters | N/A |
This module purges data from the staging tables used by the Cost Buildup Template Upload process. The module is designed to purge all the data from the staging tables that have passed the system parameter Foundation Staging Retention days (fdn_stg_retention_days).
Table 21-7 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
SVC_WF_COST_TMPL_UPLD_FHEAD |
No |
No |
No |
Yes |
|
SVC_WF_COST_TMPL_UPLD_THEAD |
No |
No |
No |
Yes |
|
SVC_WF_COST_TMPL_UPLD_TDETL |
No |
No |
No |
Yes |
|
SVC_WF_COST_TMPL_UPLD_TTAIL |
No |
No |
No |
Yes |
|
SVC_WF_COST_TMPL_UPLD_FTAIL |
No |
No |
No |
Yes |
|
SVC_WF_COST_TMPL_UPLD_STATUS |
No |
No |
No |
Yes |
|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
| Module Name | fcustomerupload.ksh |
| Description | Franchise Customers Upload |
| Functional Area | Franchise Management |
| Module Type | Integration |
| Module Technology | ksh |
| Integration Catalog ID | RMS126 |
| Runtime Parameters | DB Connection and Input File name |
This module uploads franchise customers and customer group details from an external system into RMS staging tables. It also performs both technical and business validation of the data sent in the file; for example, it validates that a customer cannot be deleted if a franchise store is associated with it.
The restart recovery is different from the conventional RMS batch. There are three points on the batch upload process where users can evaluate the successful load of the data.
SQL load - SQL load dumps invalid records that do not meet certain technical requirements (for example:. data type inconsistencies, and so on.). The rejected record is written either to a bad file or to a discard file. The discard file contains records that do not satisfy conditions such as missing or invalid record types. Records with other technical issues are written to the bad file. Note that a non-fatal code is returned by the program and a message will be written to the log file if reject files are created.
User Action: When such conditions exist, the user may update either the bad or discard file and attempt to reload using the same files.
File-Based Validations - the data from the files are loaded into the staging tables for validation. PL/SQL functions will validate the tables SVC_FCUSTUPLD_FHEAD and SVC_FCUSTUPLS_FTAIL to determine if there are any issues with FHEAD and FTAIL in the file. These kinds of errors are FATAL errors and the batch ends the file processing immediately with return code 255.
User Action: When this condition exists, the user can fix the data upload file and try to reload.
Business Validation Level - PL/SQL functions determine if the transactions loaded are valid enough to modify the actual RMS tables. Records that do not meet certain technical or business validations are rejected and the information is updated back into the staging table with an appropriate error message and the batch issues a NON-FATAL return code 1.
User Action: When this condition exists, the user can fix the data upload file and try to reload.
Table 21-9 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
SVC_FCUSTUPLD_FHEAD |
Yes |
Yes |
Yes |
No |
|
SVC_FCUSTUPLD_THEAD |
Yes |
Yes |
Yes |
No |
|
SVC_FCUSTUPLD_TDETL |
Yes |
Yes |
Yes |
No |
|
SVC_FCUSTUPLD_TTAIL |
Yes |
Yes |
Yes |
No |
|
SVC_FCUSTUPLD_FTAIL |
Yes |
Yes |
Yes |
No |
|
SVC_FCUSTUPLD_STATUS |
Yes |
Yes |
Yes |
No |
|
WF_CUSTOMER_GROUP |
Yes |
No |
No |
No |
|
WF_CUSTOMER |
Yes |
No |
No |
No |
|
STORE |
Yes |
No |
No |
No |
| Integration Type | Upload to RMS |
| File Name | Determined by runtime parameter |
| Integration Contract | IntCon000022 |
Table 21-10 File Layout
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
File Header |
File Record Descriptor |
Char(5) |
NA |
Identifies file record type. It should be FHEAD |
|
File Line ID |
Number(10) |
NA |
ID of current line being processed by input file |
|
|
File Type |
Char(5) |
FCUST |
Identifies file as 'Franchise customer upload' |
|
|
File Create Date |
Date |
SYSDATE |
Date file was written by external system |
|
|
Transaction Header |
File Record Descriptor |
Char(5) |
NA |
Identifies transaction record type. It should be THEAD |
|
File Line ID |
Number(10) |
NA |
ID of current line being processed by input file |
|
|
Message Type |
Char(30) |
NA |
Identifies the action that will be performed on the franchise customer transaction header record. It can be either create or update or delete a franchise customer group |
|
|
Franchise Customer group ID |
Number(10) |
NA |
Customer group ID |
|
|
Franchise Customer group Name |
Char(120) |
NA |
Customer group name. This field is optional for delete |
|
|
Transaction Detail |
File Record Descriptor |
Char(5) |
NA |
Identifies transaction record type. It should be TDETL |
|
File Line ID |
Number(10) |
NA |
ID of current line being processed by input file |
|
|
Message Type |
Char(30) |
NA |
Identifies the action that will be performed on the franchise customer transaction detail record. It can be either create or update or delete a franchise customer |
|
|
Franchise Customer ID |
Number(10) |
NA |
Customer ID to be processed |
|
|
Franchise Customer Name |
Char(120) |
NA |
Customer Name |
|
|
Credit Ind |
Char(1) |
N |
This field will determine if the franchise customer has good credit. Valid values are Y and N |
|
|
Auto approve Ind |
Char(1) |
N |
To auto approve the externally uploaded orders and returns. Valid values are Y and N |
|
|
Transaction Trailer |
File Record Descriptor |
Char(5) |
NA |
Identifies file record type. It should be TTAIL |
|
File Line ID |
Number(10) |
NA |
ID of current line being processed by input file |
|
|
Transaction Record Count |
Number(10) |
NA |
Number of TDETL records in this transaction set.(total records between THEAD & TTAIL) |
|
|
File Trailer |
File Record Descriptor |
Char(5) |
NA |
Identifies file record type. It should be FTAIL |
|
File Line ID |
Number(10) |
NA |
ID of current line being processed by input file. |
|
|
File Record Counter |
Number(10) |
NA |
Number of records/transactions processed in current file (total records between FHEAD & FTAIL) |
| Module Name | fcustomerprocess.ksh |
| Description | Process Uploaded Franchise Customers and Customer Groups |
| Functional Area | Franchise Management |
| Module Type | Business Processing |
| Module Technology | ksh |
| Integration Catalog ID | RMS492 |
This module processes the franchise customer groups and franchise customers information from the staging tables SVC_FCUSTUPLD_* and loads it into RMS base tables WF_CUSTOMER_GROUP and WF_CUSTOMER. The module is designed to process (insert/update or delete) the validated data that maps to franchise customer groups and franchise customer information.
The restart recovery is different from the conventional RMS batch. During the batch process, users can evaluate the successful processing of data in the following way:
PL/SQL function will load the data from staging tables into RMS tables. For records that result (insert/update/delete) in constraint error or are not found in the RMS tables(for update/delete) are rejected and the information is updated back in the corresponding staging table with appropriate error message. Also, records that do not meet certain business validations (which can only be validated during data processing) are rejected and the information is updated back in the corresponding staging table with appropriate error message.
User Action: When this condition exists, the user can fix the data upload file and try to reload and process the data.
Table 21-12 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
SVC_FCUSTUPLD_FHEAD |
Yes |
No |
Yes |
No |
|
SVC_FCUSTUPLD_THEAD |
Yes |
No |
Yes |
No |
|
SVC_FCUSTUPLD_TDETL |
Yes |
No |
Yes |
No |
|
SVC_FCUSTUPLD_TTAIL |
Yes |
No |
Yes |
No |
|
SVC_FCUSTUPLD_FTAIL |
Yes |
No |
Yes |
No |
|
SVC_FCUSTUPLD_STATUS |
Yes |
No |
Yes |
No |
|
WF_CUSTOMER_GROUP |
Yes |
Yes |
Yes |
Yes |
|
WF_CUSTOMER |
Yes |
Yes |
Yes |
Yes |
|
STORE |
Yes |
No |
No |
No |
| Module Name | fcustomerupldpurge.ksh |
| Description | Franchise Customer Staging Purge |
| Functional Area | Franchise Management |
| Module Type | Admin |
| Module Technology | ksh |
| Integration Catalog ID | RMS493 |
| Runtime Parameters | NA |
This module purges data from the staging tables used by the Franchise Customer Upload and Franchise Customer Process scripts. The module is designed to purge all the data from the staging tables that have passed the system parameter for Foundation Staging Retention days (fdn_stg_retention_days).
| Module Name | wfordupld.ksh |
| Description | Franchise Order Upload |
| Functional Area | Franchise Management |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS60 |
| Runtime Parameters | Database connection, Input File Directory, Output File Directory, Number of threads |
This batch program is used to upload franchisee orders from an external source. These orders will be created with an order type of 'EDI' and will be created for the source type specified in the upload file. If source type is not specified, then the costing location for the item/franchise store will be used. Orders will be created in approved status if the customer is setup for auto approval, assuming that the customer has valid credit.
If the customer fails credit check or if available inventory at the source location is insufficient to fulfill the order, the order will be generated in input status.
Franchise orders from customers that are not identified for 'Auto Approval' are uploaded into RMS in input status. These orders will need to be manually approved in RMS in order to be considered active.
The restart recovery is different from the conventional RMS batch. There are two points on the batch upload process where users can evaluate the successful load of the data.
SQL load - At this point, SQL load dumps invalid records that do not meet certain technical requirements (for example:. file layout issues, data type inconsistencies, and so on.). The rejected record is written to a bad file or to a discard file. The discard file contains records that do not satisfy conditions, such as missing or invalid record types. Records with other technical issues are written to the bad file. Note that a non-fatal code is returned by the program and a message will be written to the log file if reject files are created.
User Action: When such conditions exist, the user may update either the bad or discard file and attempt to reload using the same files.
Business Validation - At this point data from the file(s) are loaded into the staging table(s). PL/SQL functions determine if this loaded data is valid enough to be inserted into the actual RMS tables. For records that do not meet certain technical or business validations, the error message will be updated in staging table.
User Action: When this condition exists, the user can fix the data upload file and try to reload the file with valid data.
Table 21-16 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
FUTURE_COST |
Yes |
No |
No |
No |
|
ITEM_MASTER |
Yes |
No |
No |
No |
|
ITEM_LOC |
Yes |
No |
No |
No |
|
ITEM_LOC_SOH |
Yes |
No |
No |
No |
|
ITEM_SUPP_COUNTRY |
Yes |
No |
No |
No |
|
ITEM_SUPPLIER |
Yes |
No |
No |
No |
|
REPL_ITEM_LOC |
Yes |
No |
No |
No |
|
STORE_ORDERS |
Yes |
No |
No |
No |
|
SVC_WF_ORD_HEAD |
Yes |
Yes |
Yes |
No |
|
SVC_WF_ORD_DETAIL |
Yes |
Yes |
Yes |
No |
|
SVC_WF_ORD_TAIL |
Yes |
Yes |
Yes |
No |
|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
|
WF_COST_RELATIONSHIP |
Yes |
No |
No |
No |
|
WF_COST_BUILDUP_TMPL_HEAD |
Yes |
No |
No |
No |
|
WF_CUSTOMER |
Yes |
No |
No |
No |
|
WF_ORDER_HEAD |
Yes |
Yes |
No |
No |
|
WF_ORDER_DETAIL |
Yes |
Yes |
No |
No |
|
WF_ORDER_EXP |
No |
Yes |
No |
No |
| Integration Type | Download from RMS |
| File Name | wford*.dat |
| Integration Contract | IntCon000108 |
Table 21-17 SQL Loder Input File Layout
| Record Name | Field Name | Field Type | Null allowed? | Default Value | Description |
|---|---|---|---|---|---|
|
FHEAD |
File head descriptor |
Char(5) |
No |
FHEAD |
Describes file line type. |
|
Line Number |
Number(10) |
No |
NA |
Id of the current line being processed. |
|
|
Customer Id |
Number(10) |
No |
NA |
Customer ID of the customer requesting the order. |
|
|
Customer Order Reference number |
Char(20) |
No |
NA |
A reference field used by the customer for their tracking purposes. |
|
|
Currency Code |
Char(3) |
No |
NA |
This is the currency on which the order was transacted. |
|
|
Default Billing location |
Number(10) |
No |
NA |
A customer's location where the billing for the entire order is sent. If blank, each location is billed. |
|
|
Comments |
Char(2000) |
Yes |
NA |
Any other miscellaneous information relating to the order. |
|
|
FDETL |
File record descriptor |
Char(5) |
No |
FDETL |
Describes file line type. |
|
Line Number |
Number(10) |
No |
NA |
Id of the current line being processed. |
|
|
Item |
Char(25) |
No |
NA |
The item on the franchise order. |
|
|
Customer Location |
Number(10) |
No |
NA |
The franchise store requesting the order. |
|
|
Source Loc Type |
Char(2) |
Yes |
NA |
Source location type for which the franchise order has been created. Valid values are ST - Store, WH - warehouse, or SU - Supplier |
|
|
Source Location |
Number(10) |
Yes |
NA |
Source location for which the franchise order has been created. |
|
|
Requested Quantity |
Number (12,4) |
No |
NA |
Number of item units being ordered, includes 4 implied decimal places |
|
|
Unit of Purchase |
Char(3) |
No |
NA |
Unit of purchase can be the item's standard unit of measure, case, inners or pallets. |
|
|
Fixed Cost |
Number (20,4) |
Yes |
NA |
This is cost which will be charged to the customer for the item on the franchise order; value includes 4 implied decimal places. |
|
|
Need Date |
Char(11) |
No |
NA |
Date on which the item is needed in the franchise store, with the following format "DD-MON-YYYY' . |
|
|
Not After Date |
Char(11) |
No |
NA |
Date after which the item may no longer be accepted for a franchise store, with the following format "DD-MON-YYYY'. |
|
|
FTAIL |
File record descriptor |
Char(5) |
NA |
FTAIL |
Marks end of file. |
|
Line Number |
Number(10) |
NA |
NA |
Id of current line being processed. |
|
|
File record count |
Number(10) |
NA |
NA |
Number of detail records. |
| Module Name | wf_apply_supp_cc.ksh |
| Description | Apply Supplier Cost Change to Franchise Orders |
| Functional Area | Franchise Management |
| Module Type | Business Processing |
| Module Technology | ksh |
| Catalog ID | RMS389 |
| Runtime Parameters | NA |
This function updates approved franchise orders for supplier sourced records whose items/franchise stores are impacted by supplier cost changes. Only those item/franchise store combinations that use cost templates based on supplier cost or have not had a fixed cost defined on the order are eligible to be updated. Only those supplier cost changes that were flagged as recalculating orders result in this update.
Table 21-19 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
WF_ORDER_HEAD |
Yes |
No |
No |
No |
|
WF_ORDER_DETAIL |
No |
No |
Yes |
No |
|
WF_ORDER_EXP |
No |
Yes |
No |
Yes |
|
FUTURE_COST |
Yes |
No |
No |
No |
|
COST_SUSP_SUP_HEAD |
Yes |
No |
No |
No |
|
COST_SUSP_SUP_DETAIL |
Yes |
No |
No |
No |
|
COST_SUSP_SUP_DETAIL_LOC |
Yes |
No |
No |
No |
|
WF_COST_RELATIONSHIP |
Yes |
No |
No |
No |
|
WF_COST_BUILDUP_TMPL_HEAD |
Yes |
No |
No |
No |
|
MV_CURRENCY_CONVERSION_RATES |
Yes |
No |
No |
No |
|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
| Module Name | wfordcls.pc |
| Description | Franchise Order Close |
| Functional Area | Franchise Management |
| Module Type | Admin |
| Module Technology | ProC |
| Catalog ID | RMS391 |
| Runtime Parameters | NA |
This batch program is used to close the WF orders if the conditions below are met:
Franchise Order is not in Input (I) or Requires Credit Approval (R) status.
All the transfers associated with the franchise order are in closed/deleted status.
All the allocations associated with franchise order are in closed status.
All the purchase orders associated with franchise order are in closed status.
Store orders associated with franchise order do not have a null processed date or a need qty > 0.
The logical unit of work for this module is defined as a unique franchise order number. The v_restart_wforder view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the commit_max_ctr is reached.
| Module Name | wfordprg.pc |
| Description | Franchise Order Purge |
| Functional Area | Franchise Management |
| Module Type | Admin |
| Module Technology | ProC |
| Catalog ID | RMS392 |
| Runtime Parameters | NA |
This batch program is used to purge franchise orders from RMS after a set number of days have elapsed, as defined by the system parameter Franchise History Months. Additionally, in order to be purged via this process, the franchise orders must have no associated franchise returns and must not have any billing records that have not been extracted or where not enough time has elapsed since they were extracted, as defined by the Franchise History Months system parameter.
The logical unit of work for this module is defined as a unique wf_order_no. The v_restart_wforder view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the commit_max_ctr is reached.
Table 21-23 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
PERIOD |
Yes |
No |
No |
No |
|
SYSTEM_OPTIONS |
Yes |
No |
No |
No |
|
WF_ORDER_HEAD |
Yes |
No |
No |
Yes |
|
WF_ORDER_DETAIL |
Yes |
No |
No |
Yes |
|
WF_BILLING_SALES |
Yes |
No |
No |
Yes |
|
WF_ORDER_AUDIT |
No |
No |
No |
Yes |
|
WF_ORDER_EXP |
No |
No |
No |
Yes |
|
TSFHEAD |
Yes |
No |
No |
No |
|
ORDHEAD |
Yes |
No |
No |
No |
|
ALLOC_DETAIL |
Yes |
No |
No |
No |
|
STORE_ORDERS |
Yes |
No |
No |
No |
Transfers, Allocations, POs and Store Orders associated with franchise orders are deleted through purge processes for those functional areas (such as,. tsfprg for Transfers). Franchise orders will not be allowed to be deleted until these associated records have been removed via the other processes.
| Module Name | wfretupld.ksh |
| Description | Franchise Return Upload |
| Functional Area | Franchise Management |
| Module Type | Integration |
| Module Technology | Ksh |
| Catalog ID | RMS154 |
| Runtime Parameters | Database connection, Input File Directory, Output File Directory, Number of threads |
This batch program is used for uploading franchise returns sent from an external source, such as an external order management application. When returns are uploaded in this manner, the data will be validated and the return will be created in RMS. Additionally, an associated franchise return transfer will also be created.
The restart recovery is different from the conventional RMS batch. There are two points on the batch upload process where users can evaluate the successful load of the data.
SQL load - At this point, SQL load dumps invalid records that do not meet certain technical requirements (for example:. file layout issues, data type inconsistencies, and so on.). The rejected record is written either to a bad file or to a discard file. The discard file contains records that do not satisfy conditions, such as missing or invalid record types. Records with other technical issues are written to the bad file. Note that a non-fatal code is returned by the program and a message will be written to the log file if reject files are created. When such conditions exist, the user may either update the bad or discard file and attempt to reload using the same files.
Business Validation - At this point data from the file(s) are loaded into the staging table(s). PL/SQL functions determine if this loaded data is valid enough to be inserted into the actual RMS tables. For all records that do not meet certain technical or business validations, the error message will be updated in staging table. When this condition exists, the user can fix the data upload file and try to reload the file with valid data.
Table 21-25 Key Tables Affected
| Table | Select | Insert | Update | Delete |
|---|---|---|---|---|
|
SVC_WF_RET_HEAD |
Yes |
Yes |
Yes |
No |
|
SVC_WF_RET_DETAIL |
Yes |
Yes |
Yes |
No |
|
SVC_WF_RET_TAIL |
Yes |
Yes |
Yes |
No |
|
WF_RETURN_HEAD |
Yes |
Yes |
No |
No |
|
WF_RETURN_DETAIL |
Yes |
Yes |
No |
No |
|
TSFHEAD |
Yes |
Yes |
Yes |
No |
|
TSFDETAIL |
Yes |
Yes |
No |
No |
|
ITEM_LOC |
Yes |
Yes |
No |
No |
|
ITEM_LOC_SOH |
Yes |
Yes |
Yes |
No |
|
TRAN_DATA |
Yes |
Yes |
No |
No |
| Integration Type | Upload to RMS |
| File Name | wfreturn*.dat |
| Integration Contract | Intcon000109 |
The following is the file pattern for the upload file. Note that the values are pipe "|" delimited and can optionally be enclosed by " ".
Table 21-26 SQL Loader Input File Layout
| Record Name | Field Name | Field Type | Null Allowed? | Default Value | Description |
|---|---|---|---|---|---|
|
FHEAD |
File head descriptor |
Char(5) |
No |
FHEAD |
Describes file line type. |
|
Line Number |
Number(10) |
No |
Id of the current line being processed. |
||
|
Customer ID |
Number(10) |
No |
Franchise customer ID of the customer making the return. |
||
|
Customer Return Reference number |
Char(20) |
No |
A reference field used by the franchise customer for their tracking purposes. |
||
|
Currency Code |
Char(3) |
No |
This is the return currency. |
||
|
Comments |
Char(2000) |
Yes |
Any other miscellaneous information related to the return. |
||
|
FDETL |
File record descriptor |
Char(5) |
No |
FDETL |
Describes file line type. |
|
Line Number |
Number(10) |
No |
NA |
Id of the current line being processed. |
|
|
Item |
Char(25) |
No |
NA |
The item on the franchise return. |
|
|
Franchise Order Number |
Number(10) |
No |
NA |
The franchise order number against which the return is made. |
|
|
Customer Location |
Number(10) |
No |
NA |
The franchise location which is making the return. |
|
|
Return Loc Type |
Char(1) |
No |
NA |
Return location type for the franchise return; valid values are S - store or W - warehouse. |
|
|
Return Location |
Number(10) |
No |
NA |
Return location for the franchise return. |
|
|
Return Method |
Char(1) |
No |
NA |
The type of return; valid values are: - R-Return to Store/Warehouse - D-Destroy at site |
|
|
Unit of measure |
Char(3) |
No |
NA |
The unit measure of the return quantity. This is assumed to be the items standard UOM. |
|
|
Return qty |
Number(12,4) |
No |
NA |
The quantity of item to be returned |
|
|
Return Reason |
Char(6) |
No |
NA |
Return reason code; valid values are found on the CODE_DETAIL table where CODE_TYPE is 'RTVR'. |
|
|
Return unit cost |
Number(20,4) |
Yes |
NA |
The per unit cost for the return. |
|
|
Restock Type |
Char(1) |
No |
NA |
Indicates how the restocking fee will be calculated per item; valid values are S-specific or V-value. |
|
|
Restock Fee |
Number(20,4) |
No |
NA |
Unit restocking fee. |
|
|
FTAIL |
File record descriptor |
Char(5) |
No |
FTAIL |
Marks end of file. |
|
Line Number |
Number(10) |
No |
NA |
Id of current line being processed. |
|
|
File record count |
Number(10) |
No |
NA |
Number of detail records. |
| Module Name | wfretcls.pc |
| Description | Franchise Return Close |
| Functional Area | Franchise Management |
| Module Type | Admin |
| Module Technology | ProC |
| Catalog ID | RMS394 |
| Runtime Parameters | NA |
This batch program is used to close franchise returns that are not in input status where all the associated transfers for the return are either in closed or deleted status.
The logical unit of work for this module is defined as a unique rma_no (return order no). The v_restart_wfreturn view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the commit_max_ctr is reached.
| Module Name | wfrtnprg.pc |
| Description | Franchise Return Purge |
| Functional Area | Franchise Management |
| Module Type | Admin |
| Module Technology | ProC |
| Catalog ID | RMS396 |
| Runtime Parameters | NA |
This batch program is used to purge franchise returns from RMS after a set number of days have elapsed, as defined by the system parameter Franchise History Months. Additionally, in order to be purged via this process, the franchise returns must have no associated billing records that have not been extracted or where not enough time has elapsed since they were extracted, as defined by the Franchise History Months system parameter.
The logical unit of work for this module is defined as a unique rma_no (return order no). The v_restart_wfreturn view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the commit_max_ctr is reached.
| Module Name | wfslsupld.ksh |
| Description | Upload of Franchise Sales to RMS |
| Functional Area | Franchise Management |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS156 |
| Runtime Parameters | Database connection, Process Mode, Input File (load mode only) |
Non-stockholding franchise stores in RMS are used for retailers who have franchise or other business customers for whom they supply inventory, but don't manage it for them. However, even though inventory information will not be available for these locations in RMS, sales information will be able to be uploaded to RMS via this process to allow retailers to have better visibility to future demand from these customers. In addition to uploading sales information, this same batch script also purges old non-stockholding franchise store sales records from RMS. The script runs in 4 modes:
Load - this mode will load the data from the file into a staging table in RMS for processing; any errors encountered in validating the data on the upload are also written to the staging table (WFSLSUPLD_STAGING).
Process - this mode will process the records in the staging table that did not have errors during load, which includes both writing the data to the WF_NONSTOCKHOLDING_SALES table, as well as purging the processed records from the staging table.
Reject - this mode will process the records on the staging table that had errors on initial load. It will create a reject file for each location/report date with the data in error for that location/date. The records will then be deleted from the staging table.
Purge - this mode is used to purge old sales records from the WF_NON_STOCKHOLDING_SALES table. Records are deleted based on the system parameter Non-stockholding Franchise Sales History days (WF_NON_STOCK_SALES_HIST_DAYS).
The program can be restarted by running the wfslsupld REJECT mode to create an input file of rejected records and wfslsupld LOAD/PROCESS mode to reprocess the rejected records.
| Integration Type | Upload to RMS |
| File Name | Input file name is a parameter during runtime |
| Integration Contract | IntCon000111 |
Table 21-33 Input File Layout
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
FHEAD |
Record descriptor |
Char(5) |
FHEAD |
Identifies the file record type |
|
File Line Id |
Char(10) |
NA |
Sequential file line number |
|
|
File type definition |
Char(4) |
WFSU |
Identifies the file type |
|
|
Customer Location |
Number(10) |
NA |
Store number identifier for the customer location |
|
|
Report Date |
Char(14) |
NA |
Report date of the file in YYYYMMDDHHMMSS format |
|
|
File Create Date |
Char(14) |
NA |
File Create Date in YYYYMMDDHHMMSS format |
|
|
FDETL |
Record descriptor |
Char(5) |
FDETL |
Identifies the file record type |
|
File Line Id |
Char(10) |
NA |
Sequential file line number |
|
|
Item |
Char(25) |
NA |
Item number identifier |
|
|
Net Sales Quantity |
Number(12) |
NA |
Sales Quantity with 4 implied decimal places |
|
|
Net Sales Quantity UOM |
Char(4) |
NA |
Unit of Measure for the Net Sales Quantity |
|
|
Total Retail Amount |
Number(20) |
NA |
Total Retail Amount with 4 implied decimal places |
|
|
Total Retail Amount Currency |
Char(3) |
NA |
Currency code for the Total Retail Amount |
|
|
FTAIL |
Record descriptor |
Char(5) |
FTAIL |
Identifies the file record type |
|
File Line Id |
Number(10) |
NA |
Sequential file line number |
|
|
File Record counter |
Number(10) |
NA |
Number of records/transactions processed in current file (only records between head & tail) |
| Module Name | wfbillex.ksh |
| Description | Franchise Billing Extract |
| Functional Area | Franchise Management |
| Module Type | Integration |
| Module Technology | ksh |
| Catalog ID | RMS155 |
| Runtime Parameters | NA |
The purpose of this shell script module is to fetch all billing information for Franchise sale and return transactions and write these to an output file for integration with an external financial application that manages billing. A file is generated for each customer location (store)/day.
The logical unit of work for this module is defined as the customer location (store). Only one commit will be done for a customer location that has been completely processed. The WFBX formatted output file will be created with a temporary name and renamed just before a customer location commit. In case of failure, all work done will be rolled back.
| Integration Type | Download from RMS |
| File Name | WFBX_<store>_<SYSDATE> |
| Integration Contract | IntCon000110 |
Table 21-36 Output File Layout
| Record Name | Field Name | Field Type | Default Value | Description |
|---|---|---|---|---|
|
FHEAD |
Record descriptor |
Char(5) |
FHEAD |
Identifies the file record type |
|
File Line Id |
Char(10) |
NA |
Sequential file line number |
|
|
File type definition |
Char(4) |
WFBX |
Identifies the file type |
|
|
File Create Date |
Char(14) |
NA |
File Create Date in YYYYMMDDHHMMSS format |
|
|
THEAD |
Record descriptor |
Char(5) |
THEAD |
Identifies the file record type |
|
File Line Id |
Char(10) |
NA |
Sequential file line number |
|
|
Customer Location |
Number(10) |
NA |
Franchise store number |
|
|
Customer Order Reference Number |
Char(20) |
NA |
Reference number provided by the franchise customer |
|
|
Franchise Order Number |
Number(10) |
NA |
Franchise Order Number |
|
|
Transaction Type |
Char(6) |
NA |
SALES or RETURN |
|
|
RMA Number |
Number(10) |
NA |
Return Merchandise Authorization Number for the return |
|
|
Order Return Date |
Number(8) |
NA |
Order return date for Return transaction type or Order date for Sale transaction type in YYYYMMDD format |
|
|
Shipment Date |
Number(8) |
NA |
Date on which the item was shipped to the franchise location or returned to the retailer |
|
|
TDETL |
Record descriptor |
Char(5) |
TDETL |
Identifies the file record type |
|
File Line Id |
Char(10) |
Sequential file line number |
||
|
Item |
Char(25) |
Item sequence number |
||
|
Department |
Number(4) |
Department number of the item |
||
|
Class |
Number(4) |
Class number of the item |
||
|
Subclass |
Char(4) |
Subclass number of the item |
||
|
Order Return Quantity |
Number(12) |
Return quantity with 4 implied decimal places |
||
|
Order Return Quantity UOM |
Char(4) |
Return quantity unit of measure |
||
|
Order Return Cost |
Number(20) |
Return cost for Return transaction type or Customer cost for Sale transaction type. For both it is the per-unit cost |
||
|
Freight Cost |
Number(20) |
Freight associated to the franchise order |
||
|
Return Restocking Fee |
Number(20) |
Unit restocking fee charged for received items |
||
|
VAT Code |
Char(6) |
VAT code for the item |
||
|
VAT Rate |
Number(20) |
VAT rate associated to the VAT code for the item |
||
|
Other Order Charges |
Number(20) |
Other charges for the item |
||
|
TAIL |
Record descriptor |
Char(5) |
TTAIL |
Identifies the file record type |
|
File Line Id |
Char(10) |
NA |
Sequential file line number |
|
|
Tran Record Counter |
Number(6) |
NA |
Number of TDETL records in this transaction set |
|
|
FTAIL |
Record descriptor |
Char(5) |
FTAIL |
Identifies the file record type |
|
File Line Id |
Number(10) |
NA |
Sequential file line number |
|
|
File Record counter |
Number(10) |
NA |
Number of records/transactions processed in current file (only records between head & tail) |
