Store Add Asynchronous Process (CORESVC_STORE_ADD_SQL. ADD

Store Add Asynchronous Process (CORESVC_STORE_ADD_SQL. ADD_STORE)

Module Name	CORESVC_STORE_ADD_SQL. ADD_STORE
Description	Asynchronous Process
Functional Area	Foundation Data
Module Type	Admin
Module Technology	PL SQL
Catalog ID	RMS496
Runtime Parameters	N/A

Business Overview

This asynchronous process creates new stores in Merchandising, along with all their associated records when a new store is initiated online in Merchandising or via the Store Subscription API. Previously, the likestore functionality is also processed within the store add asynchronous process, but this has now been decoupled from the store add program and now runs as a separate hourly batch job, removing the dependency between both processes.

Key Tables Affected

Table 3-8 Key Tables Affected

Table	Select	Insert	Update	Delete
STORE _ADD	Yes	No	No	Yes
STORE	Yes	Yes	No	No
STOCK_LEDGER_INSERTS	No	Yes	No	No
RPM_ZONE	No	Yes	No	No
RPM_ZONE_LOCATION	No	Yes	No	No
RMS_ASYNC_STATUS	Yes	Yes	Yes	No
RMS_ASYNC_RETRY	Yes	Yes	Yes	No
RMS_ASYNC_JON	Yes	No	No	No
LOC_TRAITS_MATRIX	No	Yes	No	No
COST_ZONE	No	Yes	No	No
COST_ZONE_GROUP_LOC	No	Yes	No	No
STORE_HIERARCHY	No	Yes	No	No
WF_COST_RELATIONSHIP	No	Yes	No	No
SOURCE_DLVRY_SCHED	No	Yes	No	No
SOURCE_DLVRY_SCHED_EXC	No	Yes	No	No
SOURCE_DLVRY_SCHED_DAYS	No	Yes	No	No
COMPANY_CLOSED_EXCEP	No	Yes	No	No
LOCATION_CLOSED	No	Yes	No	No
POS_STORE	No	Yes	No	No
PERIOD	Yes	No	No	No
SYSTEM_OPTIONS	Yes	No	No	No
STORE_ADD_L10N_EXT	Yes	Yes	No	Yes
STORE_ADD_CFA_EXT	Yes	Yes	No	Yes

Design Assumptions

The materialized views MV_LOC_SOB, MV_L10N_ENTITY and MV_LOC_PRIM_ADDR will be refreshed after the store has been added. It is assumed that the materialized view will still be available to other processes during the refresh.

Queue Creation

The function RMS_ASYNC_QUEUE_SQL.CREATE_QUEUE_SUBSCRIBER is called to drop and recreate the queue table if one already exists. This function is called with the JOB_TYPE as STORE_ADD (for example, the constant ASYNC_JOB_STORE_ADD) to create a queue for store processing.

Design Overview - Process Steps

This section describes the key design aspect of the store add process.

The overall process consists of 3 steps as outlined below.

New (status-code: 00NEW). This is the status when store is just created.
Store-Add (status-code: 01STOREADD)
Store-Add-Post (status-code: 02STOREADD_POST)

The status-code of the current completed step of the process is updated in store_add.process_status column.

If STORE_ADD.LIKESTORE column is not null for the store, the status will remain in 02STOREADD_POST and the record will be picked up by the likestorebatch.ksh which runs as an hourly job. If not, then the STORE entry will be removed from the STORE_ADD table.

Package Impact

Package name: coresvc_store_add_sql

Spec file name: coresvc_store_adds.pls

File name: coresvc_store_adds/b.pls

Function Level Description - ADD_STORE

Function: ADD_STORE
           (O_error_message          IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
            I_rms_async_id           IN      RMS_ASYNC_STATUS.RMS_ASYNC_ID%TYPE)

This function contains the core logic for adding a new store to Merchandising. The process of adding a store to Merchandising starts with store.fmb form. When a user creates a new store by using the form, an entry is made in the STORE_ADD table. Also entries are made into RMS_ASYNC_STATUS with the status as new and RMS_ASYNC_RETRY tables with a new RMS_ASYNC_ID. The RMS_ASYNC_ID is placed in the queue for processing. The de-queue process picks the RMS_ASYNC_ID generated and based on the JOB_TYPE (STORE_ADD) calls the CORESVC_STORE_ADD_SQL.ADD_STORE for further processing.

This function:

Calls PM_NOTIFY_API_SQL.NEW_LOCATION to create pricing records to update the Pricing tables.
Calls the functions L10N_FLEX_ATTRIB_SQL.ADD_STORE_ATTRIB and CFA_SQL.ADD_STORE_ATTRIB.
Makes entries into cost-zone tables.
If like-store is mentioned and delivery schedule needs to be copied then copy source-delivery-schedule information. Hence entries are made into SOURCE_DLVRY_SCHED, SCHED_EXC and SCHED_DAYS tables.
If like-store is mentioned and locations close information needs to be copied then make entries into COMPANY_CLOSED_EXCEP and LOCATION_CLOSED tables based on like store.
Calls the function STKLEDGR_SQL.STOCK_LEDGER_INSERT to make entry into STOCK_LEDGER_INSERTS table.
Copies WF_COST_RELATIONSHIP and DEAL_PASSTHRU data for the specified costing location.
If like-store is mentioned then call the local function LIKE_STORE.
The MV_LOC_SOB, MV_L10N_ENTITY and MV_LOC_PRIM_ADDR materialized views are refreshed as well.
After completion of the process, it deletes the records from STORE_ADD_L10N_EXT, STORE_ADD_CFA_EXT and STORE_ADD tables.

On successful creation of the store you are prompted with a message saying the RMS_ASYNC_ID is processed successfully. In case there is a failure during the store creation you will also be notified. You have to use the Asynchronous Job log form to view and reprocess the failed store. On clicking on reprocess in the Asynchronous Job log form an entry is made into the RMS_ASYNC_RETRY table. The RMS_ASYNC_ID is again placed in the queue for processing.

Spec file name: rmsasyncprocs/b.pls

Function Level Description - ENQUEUE_STORE_ADD

Function: ENQUEUE_STORE_ADD
           (O_error_message          IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
            I_rms_async_id           IN      RMS_ASYNC_STATUS.RMS_ASYNC_ID%TYPE)

This function adds the RMS_ASYNC_ID associated with the JOB_TYPE STORE_ADD created from the store form to the asynchronous queue. It also makes entries into the RMS_ASYNC_STATUS and RMS_ASYNC_RETRY table to track the status of the asynchronous job.

Function Level Description - ENQUEUE_STORE_ADD_RETRY

Function: ENQUEUE_STORE_ADD_RETRY
           (O_error_message          IN OUT  RTK_ERRORS.RTK_TEXT%TYPE,
            I_rms_async_id           IN      RMS_ASYNC_STATUS.RMS_ASYNC_ID%TYPE)

This function puts the RMS_ASYNC_ID associated with a STORE_ADD event to the asynchronous queue again for re-processing. It is invoked through the asynchronous job log form.

Function Level Description - NOTIFY_STORE_ADD

Procedure: NOTIFY_STORE_ADD(context     raw,
                            reginfo     sys.aq$_reg_info,
                            descr       sys.aq$_descriptor,
                            payload     raw,
                            payloadl    number)

This procedure is called during the de-queue process. This procedure calls the function CORESVC_STORE_ADD_SQL.ADD_STORE for store creation. Once the store creation is completed successfully it calls the function RMS_ASYNC_PROCESS_SQL.WRITE_SUCCESS to update the status of the RMS_ASYNC_ID as success. During a failure in store creation it calls the function RMS_ASYNC_PROCESS_SQL.WRITE_ERROR to update the status as error and also to update the error message. You are notified of the success/failure of the store creation process.

Operations and Monitoring

This section describes the details required for running and monitoring this process.

Running entire Store-Add as Batch in Case of AQ Issues

In case of Oracle AQ issues if a store-add step is not running in async mode then the entire store-add process can also be run in batch using below command.

storeaddbatch.ksh $UP

This is provided only as a workaround in case of AQ issues. The recommended method is to let the store-add step be processed in Async through AQ as it is designed.

Building Schedule Dependencies between Async Process and other Batches

Customers may need to build scheduling dependencies between async processes and other batch programs. For example, making pos-extract batches dependent upon completion of a Like-store step of the store-add process. To do that, create a job in the scheduler by using the following command and make the required batches dependent upon this job.

straddasyncwait.ksh $UP "03LIKESTORE"

Similarly, if the batch program needs to be made dependent upon other steps, schedule jobs by passing desired status.

Monitoring Progress of Store-Add Processes

The current completed step of the store-add process is updated in the store_add.process_status column. In case of a Like-Store step (which is a separate batch program), the status of a store will remain in 02STOREADD_POST, until it is processed by the likestore batch program, which will in turn change the status to 03LIKETORE.

Once the process is completed, the store will be subsequently removed from the STORE_ADD table. If not, then the status will be changed to '05LIKESTORE_FAIL'.