Store Add Asynchronous Process

Functional Area

Financials

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 through the Store Subscription API.

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 (that is, the constant ASYNC_JOB_STORE_ADD) to create a queue for store processing.

Package Impact

Package Name: RMS_CONSTANTS

Package name: RMS_CONSTANTS

Spec file name: rmsconstantss.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 user creates a new store using the form an entry is made to STORE_ADD table. Also entries are made into RMS_ASYNC_STATUS with 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.

The 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 the delivery schedule needs to be copied, then it copies source-delivery-schedule information. Then entries are made in the SOURCE_DLVRY_SCHED, SCHED_EXC and SCHED_DAYS tables.
If like-store is mentioned and locations close information needs to be copied, then it makes entries into the COMPANY_CLOSED_EXCEP and LOCATION_CLOSED tables based on like store.
Calls the function STKLEDGR_SQL.STOCK_LEDGER_INSERT to create an entry in the STOCK_LEDGER_INSERTS table.
Copies WF_COST_RELATIONSHIP and DEAL_PASSTHRU data for the specified costing location.
If like-store is mentioned, it then calls 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 the STORE_ADD_L10N_EXT, STORE_ADD_CFA_EXT and STORE_ADD tables.

On successful creation of the store the user is prompted with a message saying the RMS_ASYNC_ID is processed successfully. In case if there is failure during the store creation then the same if notified to the user. The user has to use the Asynchronous Job log form to view and reprocess the failed store. On clicking on reprocess in Asynchronous Job log form an entry is made into RMS_ASYNC_RETRY table. The RMS_ASYNC_ID is again placed in the queue for processing.

Function Level Description – LIKE_STORE

Function: LIKE_STORE
           (O_error_message         IN OUT   RTK_ERRORS.RTK_TEXT%TYPE,
            I_new_store             IN       STORE_ADD.STORE%TYPE,
            I_like_store            IN       STORE_ADD.LIKE_STORE%TYPE,
            I_new_store_currency    IN       STORE.CURRENCY_CODE%TYPE,
            I_copy_repl_ind         IN       VARCHAR2)

This function contains the logic for copying items from the like store for the new store to Merchandising.

The item expenses details are added for the new store, and it creates entries into ITEM_EXP_HEAD and ITEM_EXP_DETAIL tables.
Makes an entry into the POS_STORE table.
Calls the function NEW_ITEM_LOC_SQL.NEW_ITEM_LOC to create new item location combinations.
If the copy replenishment indicator is set ‘Y’, then it creates entries in the replenishment tables (REPL_ITEM_LOC, REPL_DAY and REPL_ITEM_LOC_UPDATES).

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 enters data into the RMS_ASYNC_STATUS and RMS_ASYNC_RETRY tables 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 to ‘success’. During a failure in store creation it calls the function RMS_ASYNC_PROCESS_SQL.WRITE_ERROR to update the status to ‘error’ and update the error message. The user is notified about the success/failure of the store creation process.

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.

Table Impact

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
ITEM_EXP_HEAD	No	Yes	No	No
ITEM_EXP_DETAIL	No	Yes	No	No
ITEM_LOC	No	Yes	No	No
ITEM_LOC_SOH	No	Yes	No	No
PRICE_HIST	No	Yes	No	No
ITEM_SUPP_COUNTRY_LOC	No	Yes	No	No
REPL_ITEM_LOC	No	Yes	No	No
REPL_DAY	No	Yes	No	No
REPL_ITEM_LOC_UPDATES	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