1. Operations Guide, Volume 1 - Batch Overviews and Designs
  2. Foundation Data Maintenance
  3. Stock Ledger and Budget Tables Insert
  4. Package Impact
  5. Package Name: CORESVC_STKLEDGR_INSERTS_SQL

Package Name: CORESVC_STKLEDGR_INSERTS_SQL

Spec file name: coresvc_stkledgr_insertss.pls

Body file name: coresvc_stkledgr_insertsb.pls

Function Level Description – PROCESS_STKLEDGR_INSERTS

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

The process of making entries into stock ledger and budget tables are triggered during store, warehouse, partner, dept, and subclass creation. In each of these cases, an entry is made into STOCK_LEDGER_INSERTS 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 (STKLGR_INSERT) calls the CORESVC_STKLEDGR_INSERTS_SQL.PROCESS_STKLEDGR_INSERTS for further processing.

In the function CORESVC_STKLEDGR_INSERTS_SQL.PROCESS_STKLEDGR_INSERTS, based on the TYPE_CODE present in the STOCK_LEDGER_INSERTS table, it calls a specific function in STKLEDGR_INSERTS_SQL package:

    • If the TYPE_CODE is ‘S’ - Store or ‘W’ – Warehouse or ‘E’ – External Finisher then the function STKLEDGR_INSERTS_SQL.ADD_LOC is called for further processing. The function STKLEDGR_INSERTS_SQL.ADD_LOC enters data into the WEEK_DATA, MONTH_DATA, HALF_DATA, MONTH_DATA_BUDGET and HALF_DATA_BUDGET tables.

    • If the TYPE_CODE is ‘B’ – Subclass then the function calls STKLEDGR_INSERTS_SQL.ADD_SUBCLASS for further processing. The function STKLEDGR_INSERTS_SQL.ADD_SUBCLASS enters data into the WEEK_DATA, MONTH_DATA and HALF_DATA tables.

    • If the TYPE_CODE is ‘D’ – Department then the function calls STKLEDGR_INSERTS_SQL.ADD ADD_DEPT_BUDGET for further processing. The function STKLEDGR_INSERTS_SQL. ADD_DEPT_BUDGET enters data into the MONTH_DATA_BUDGET and HALF_DATA_BUDGET tables.

After completion of the process, it deletes the record from STOCK_LEDGER_INSERTS table.

On successful creation of the stock ledger and budget table entries the user is prompted with a message saying the RMS_ASYNC_ID was processed successfully. In case there is a failure during the stock ledger and budget table creation then the user is likewise notified. The user must use the Asynchronous Job log form to view and reprocess the failed transaction. On clicking reprocess in theAsynchronous 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.