Search Results initial_load

Overview

APPS.RCI_PROC_DETAIL_ETL_PKG is a PL/SQL extraction, transformation, and load (ETL) package used by Oracle E-Business Suite's Enterprise Tax, Regulatory, and Compliance (ETRM) product family. In the EBS 12.1.1 and 12.2.2 releases, this package supports the loading and staging of "Process Detail" fact data — financial statement and process certification information — into the AMW (Applications Management Warehouse) analytical tables. The package is declared with AUTHID CURRENT_USER, meaning that the executing user's privileges, rather than the definer's, govern object access. This is significant because the package must resolve tables through APPS synonyms while respecting role-based security for the calling session.

The header comment identifies a substantive redesign dated 01.01.2006 by developer npanandi, which obsoleted the original initial-load and incremental-load logic in favor of a new financial-statement ETL query. The package therefore retains legacy entry points in parallel with the current ones, giving existing callers a migration path while ensuring the revised COGS and financial-statement processing logic is used for new data.

Key Procedures and Functions

• INITIAL_LOAD — The current full-load entry point, called as a concurrent program or from a job scheduler. Its errbuf and retcode arguments conform to the standard PL/SQL concurrent program signature. It performs the first population of the process-detail fact table for a given scope.
• INITIAL_LOAD_OBSOLETE — Retained version of the prior initial-load logic, kept for backward compatibility after the 2006 ETL rewrite. Should not be used for new deployments.
• INCR_LOAD — The current incremental-load entry point, using the same standard concurrent program signature. It appends delta records identified since the last successful run.
• INCR_LOAD_OBSOLETE — Retained version of the earlier incremental-load logic, superseded by the rewritten ETL query.
• GET_LAST_RUN_DATE — Returns the most recent run date for a named fact, based on the p_fact_name parameter. Used by the incremental load to determine the processing window.
• ERR_MESG — Formats a diagnostic message using an input message, an optional procedure name, and an optional statement identifier, returning a formatted VARCHAR2 suitable for errbuf.
• CHECK_INITIAL_LOAD_SETUP — Validation helper that returns the global start date (x_global_start_date) and the RCI schema name (x_rci_schema). It is typically invoked before a first load to confirm that configuration prerequisites exist.

Tables Accessed

The package reads and writes across several functional areas. The AMW tables — AMW_ACCT_ASSOCIATIONS, AMW_CERTIFICATION_B, AMW_EXECUTION_SCOPE, AMW_FIN_CERT_SCOPE, AMW_FIN_PROCESS_EVAL_SUM, AMW_FIN_PROC_CERT_RELAN, and AMW_PROC_CERT_EVAL_SUM — supply certification definitions, account associations, and aggregated process/financial evaluation results that drive the fact population. Human Resources tables HR_ALL_ORGANIZATION_UNITS and HR_ALL_ORGANIZATION_UNITS_TL resolve organizational context and translated names for the target records, while MTL_PARAMETERS supplies the inventory organization identifier used to scope processing. RCI_DR_INC is a staging or driving table for incremental extract rows, and RCI_PROCESS_DETAIL_F is the principal fact table into which the ETL writes.

Usage Notes

This package is invoked almost exclusively through concurrent programs registered in the ETRM application, because the errbuf/retcode signatures match the standard PL/SQL concurrent program contract. Administrators schedule INITIAL_LOAD once to seed the warehouse and INCR_LOAD periodically thereafter; GET_LAST_RUN_DATE protects against overlapping or duplicate processing. The _OBSOLETE procedures exist solely for backward compatibility and should be treated as deprecated. The package is referenced by no other database packages, so dependencies are confined to the tables listed above and the FND concurrent manager. Direct calls from custom code are possible but should first invoke CHECK_INITIAL_LOAD_SETUP to confirm schema and date-range configuration on both 12.1.1 and 12.2.2 environments.