18 Franchise Management

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. Retailers using the Franchise Management component in Merchandising can choose to manage inventory for some, all, or none of the franchise locations in the solution.

The batch processes that are used for Franchise Management in Merchandising fall primarily into the following areas:

• Managing customer groups and customers

• Managing franchise costing

• Managing franchise orders and returns

Program Summary

The following batch designs are included in this functional area:

• Apply Supplier Cost Change to Franchise Orders (wf_apply_supp_cc.ksh)

• Franchise Customer Staging Purge (fcustupldpurge)

• Franchise Order Close (wf_orders_close_job) - background process

• Franchise Order Close (wfordcls)

• Franchise Order Purge (wf_orders_purge_job) - background process

• Franchise Order Purge (wfordprg)

• Franchise Return Close (wf_returns_close_job) - background process

• Franchise Return Close (wfretcls)

• Franchise Return Purge (wf_returns_purge_job) - background process

• Franchise Return Purge (wfrtnprg)

• Process Cost Buildup Template Upload (fcosttmplprocess)

• Process Uploaded Franchise Customers and Customer Groups (fcustomerprocess)

• Purge Staged Cost Template Data (fcosttmplpurge)

• Purge Staged Cost Template Data (wf_cost_template_purge_job) - background process

See also Merchandising Operations Guide Volume 2 for details on Franchise related scheduled integration programs:

• Upload Cost Buildup Template (fcosttmplupld.ksh)

• Franchise Customer Upload (fcustomerupload.ksh)

• Franchise Order Upload (wfordupld.ksh)

• Franchise Return Upload (wfretupld.ksh)

• Upload of Franchise Sales to Merchandising (wfslsupld.ksh)

• Franchise Billing Extract (wfbillex)

Apply Supplier Cost Change to Franchise Orders (wf_apply_supp_cc.ksh)

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
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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.

Restart/Recovery

N/A

Design Assumptions

• The pricing cost for franchise orders in input or pending credit approval status is not updated because the order cost will be updated based on any changes on franchise order approval.

Franchise Customer Staging Purge (fcustupldpurge)

Module Name	fcustomerupldpurge.ksh
Description	Franchise Customer Staging Purge
Functional Area	Franchise Management
Module Type	Admin
Module Technology	ksh
Catalog ID	RMS493
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This module purges data from the staging tables used by the Franchise Customer Upload and Franchise Customer Process scripts. It is designed to purge all the data from the staging tables that have passed the system parameter for Foundation Staging Retention days.

Restart/Recovery

N/A

Design Assumptions

N/A

Franchise Order Close (wf_orders_close_job)

Module Name	wf_orders_close_job
Description	Franchise Order Close
Functional Area	Franchise Management
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Thread assignment program will filter eligible records from franchise order header table based on its conditions 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.

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

The Business logic program will process all records from the staging table. Using bulk processing, this program will update the records from franchise order header table to "D" (Closed) status. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 18-1 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_WF_ORDERS_CLOSE_STG	Yes	Yes	No	Yes
WF_ORDER_HEAD	Yes	No	Yes	No
TSFHEAD	Yes	No	No	No
STORE_ORDERS	Yes	No	No	No
ORDHEAD	Yes	No	No	No
ALLOC_DETAIL	Yes	No	No	No
ALLOC_HEADER	Yes	No	No	No

Design Assumptions

N/A

Franchise Order Close (wfordcls)

Module Name	wfordcls.pc
Description	Franchise Order Close
Functional Area	Franchise Management
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS391
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program is used to close the Franchise 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.

Restart/Recovery

The logical unit of work for this module is defined as a unique franchise order number. The restart franchise order view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the maximum commit counter is reached.

Design Assumptions

N/A

Franchise Order Purge (wf_orders_purge_job)

Module Name	wf_orders_purge_job
Description	Franchise Order Purge
Functional Area	Franchise Management
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Design Overview

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

Thread assignment program will filter eligible records from franchise orders header table based on its conditions are met and number of days elapsed as defined by system parameter setting, Franchise History Months:

• All Franchise Order Details have its NOT_AFTER_DATE not yet elapsed as declared by the system parameter setting.

• All the franchise returns associated with the franchise order were deleted or purged.

• All the billing records associated with the franchise order are not yet extracted or where not enough time has elapsed since they were extracted as defined by the system parameter setting.

• All transfers, Orders and Store Orders associated with the franchise order were purged through their respective purge process.

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

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

Restart/Recovery

N/A

Key Tables Affected

Table 18-2 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_WF_ORDERS_PURGE_STG	Yes	Yes	No	Yes
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

Design Assumptions

N/A

Franchise Order Purge (wfordprg)

Module Name	wfordprg.pc
Description	Franchise Order Purge
Functional Area	Franchise Management
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS392
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program is used to purge franchise orders from Merchandising 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.

Restart/Recovery

The logical unit of work for this module is defined as a unique franchise order number. The restart franchise order view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the maximum commit counter is reached.

Design Assumptions

• Transfers, Allocations, POs and Store Orders associated with franchise orders are deleted through purge processes for those functional areas (e.g. tsfprg for Transfers). Franchise orders will not be allowed to be deleted until these associated records have been removed via the other processes.

Franchise Return Close (wf_returns_close_job)

Module Name	wf_returns_close_job
Description	Franchise Return Close
Functional Area	Franchise Management
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Thread assignment program will filter eligible records from franchise return header table based on if these conditions are met:

• Franchise Returns is not in Input (I) or Closed (D) status.

• All the transfers associated with the franchise return are in closed/deleted status.

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

The Business logic program will process all records from the staging table. Using bulk processing, this program will update the records from franchise return header table to "D" (Closed) status. It will free up and clean the staging table afterwards. There is a STOP ON NEXT feature in bulk processing (through a loop) where Administrators can stop this batch with a flip of this indicator.

Restart/Recovery

N/A

Key Tables Affected

Table 18-3 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_WF_RETURNS_CLOSE_STG	Yes	Yes	No	Yes
WF_RETURN_HEAD	Yes	No	Yes	No
TSFHEAD	Yes	No	No	No

Design Assumptions

N/A

Franchise Return Close (wfretcls)

Module Name	wfretcls.pc
Description	Franchise Return Close
Functional Area	Franchise Management
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS394
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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.

Restart/Recovery

The logical unit of work for this module is defined as a unique return order number. The restart franchise return view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the maximum commit counter is reached.

Design Assumptions

N/A

Franchise Return Purge (wf_returns_purge_job)

Module Name	wf_returns_purge_job
Description	Franchise Return Purge
Functional Area	Franchise Management
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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

Thread assignment program will filter eligible records from franchise returns header table based on its conditions are met and number of days elapsed as defined by system parameter setting, Franchise History Months:

• All the billing records associated with the franchise order are not yet extracted or where not enough time has elapsed since they were extracted as defined by the system parameter setting.

• All transfers associated with the franchise order were purged through their respective purge process.

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

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

Restart/Recovery

N/A

Key Tables Affected

Table 18-4 Key Tables Affected

Table	Select	Insert	Update	Delete
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
RMS_BATCH_STATUS	Yes	No	No	No
B8D_PROCESS_CONFIG	Yes	No	No	No
JOB_AUDIT_LOGS	No	Yes	No	No
B8D_WF_RETURNS_PURGE_STG	Yes	Yes	No	Yes
WF_RETURN_HEAD	Yes	No	No	Yes
WF_RETURN_DETAIL	No	No	No	Yes
WF_BILLING_RETURNS	Yes	No	No	Yes
TSFHEAD	Yes	No	No	No

Design Assumptions

N/A

Franchise Return Purge (wfrtnprg)

Module Name	wfrtnprg.pc
Description	Franchise Return Purge
Functional Area	Franchise Management
Module Type	Admin
Module Technology	ProC
Catalog ID	RMS396
Wrapper Script	rmswrap_multi.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This batch program is used to purge franchise returns from the Merchandising 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.

Restart/Recovery

The logical unit of work for this module is defined as a unique return order no. The restart franchise return view is used for threading. This batch program uses table-based restart/recovery. The commit happens in the database when the maximum commit counter is reached.

Design Assumptions

• Transfers associated with franchise returns are deleted through the Transfer Purge (tsfprg) process. Franchise returns will not be allowed to be deleted until these associated records have been removed via that process.

Process Cost Buildup Template Upload (fcosttmplprocess)

Module Name	fcosttmplprocess.ksh
Description	Process Cost Buildup Template Upload
Functional Area	Franchise Management
Module Type	Business Processing
Module Technology	ksh
Catalog ID	RMS224
Wrapper Script	batch_fprocess.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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 Merchandising base tables. The module is designed to process inserts, updates and deletes for these data elements.

Restart/Recovery

The restart recovery is different from the conventional Merchandising batch.

During the batch process, you can evaluate the successful processing of data in the following way:

PL/SQL function will load the data from staging tables into Merchandising tables. For records that result (insert/update/delete) in constraint error or are not found in the Merchandising 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.

Action Required: When this condition exists, you can fix the data upload file and try to reload and process the data.

Design Assumptions

N/A

Process Uploaded Franchise Customers and Customer Groups (fcustomerprocess)

Module Name	fcustomerprocess.ksh
Description	Process Uploaded Franchise Customers and Customer Groups
Functional Area	Franchise Management
Module Type	Business Processing
Module Technology	ksh
Catalog ID	RMS492
Wrapper Script	batch_fprocess.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This module processes the franchise customer groups and franchise customers information from the staging tables and loads it into Merchandising base tables for franchise customer groups and franchise customer information. It is also designed to process (insert/update or delete) the validated data that maps to franchise customer groups and franchise customer information.

Restart/Recovery

The restart recovery is different from the conventional Merchandising batch. During the batch process, you can evaluate the successful processing of data in the following way:

• PL/SQL function will load the data from staging tables into Merchandising tables. For records that result (insert/update/delete) in constraint error or are not found in the Merchandising 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.

Action Required: When this condition exists, you can fix the data upload file and try to reload and process the data.

Commit Points

Commit points are performed per transaction.

Design Assumptions

N/A

Program Flow

This diagram describes the process flow of the fcustomerprocess.ksh module.

Figure 18-1 Process Flow

Purge Staged Cost Template Data (fcosttmplpurge)

Module Name	fcosttmplpurge.ksh
Description	Purge Staged Cost Template Data
Functional Area	Franchise Management
Module Type	Admin
Module Technology	ksh
Catalog ID	RMS225
Wrapper Script	rmswrap_shell.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

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.

Restart/Recovery

N/A

Design Assumptions

N/A

Purge Staged Cost Template Data (wf_cost_template_purge_job)

Module Name	wf_cost_template_purge_job
Description	Purge Staged Cost Template Data
Functional Area	Franchise Management
Module Type	Admin - Ad hoc
Module Technology	Background Processing
Catalog ID	N/A
Wrapper Script	b8dwrap.ksh

Schedule

Oracle Retail Merchandising Batch Schedule

Design Overview

This background job is composed of one step processing only. It will retain the business logic processing from original KSH script algorithm.

The Business logic program will remove all old/aged records from the staging tables used by the Cost Buildup Template Upload process which have passed the purge criteria from the system parameter setting, Foundation Staging Retention Days.

Restart/Recovery

N/A

Key Tables Affected

Table 18-5 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

Design Assumptions

N/A