Search Results get_last_day

Overview

RCI_OPEN_REMED_SUMM_PKG is a PL/SQL package body owned by the APPS schema that supports the Oracle E-Business Suite Risk and Compliance (formerly Enterprise Governance, Risk, and Compliance / ETRM) functionality for tracking open remediation activity. Its principal business purpose is to populate and maintain the summary and detail data used by the Open Remediations dashboard pages and the underlying summary tables. The package assembles the SQL that drives KPI page regions, retrieves open remediation results and drill-down details for findings and remediations, and performs initial and incremental data loads that synchronize RCI summary entity tables with their source engineering change and audit data.

The package is registered with an API classification of OTHER and is not referenced by any other documented package, indicating it is invoked directly by dashboard regions, concurrent programs, or setup routines rather than called from other packaged APIs. Global constants C_ERROR (-1), C_WARNING (1), and C_OK (0) conform to standard concurrent manager exit codes, and the INITIALIZATION_ERROR exception (error -20900, "Error in Global setup") signals failures in required environment or setup validation. File-scope variables include g_global_start_date and g_rci_schema, while G_USER_ID and G_LOGIN_ID are initialized from FND_GLOBAL.

Key Procedures and Functions

The package exposes sixteen documented program units:

• GET_KPI — Builds the dynamic SQL and bind attributes that back the Open Remediations KPI page region, iterating over page parameters to assemble WHERE clauses (period, quarter, year, certification, and certification type).
• GET_OPEN_REMEDIATION_RESULT — Returns the result set displayed on the open remediation summary page.
• GET_FINDINGS_DETAILS — Retrieves drill-down detail rows for findings associated with the summary.
• GET_REMEDIATIONS_DETAILS — Retrieves drill-down detail rows for individual remediations.
• OPEN_REMED_ACT_INITIAL_LOAD — Performs the full initial load of open remediation activity data.
• OPEN_REMED_ACT_INCREMENT_LOAD / OPEN_REMED_ACT_INCREMENTAL_LOAD — Perform incremental (delta) loads of new or changed open remediation activity since the last run.
• FN_GET_BUCKET_ID — Returns the aging bucket identifier used to classify remediation records by age.
• GET_LAST_DAY — Returns the last day of a period, used for period-end and aging calculations. This function corresponds to the user search term "get_last_day" and is central to determining period boundaries within the summary logic.
• INSERT_FINDINGS_INITIAL_LOAD / INSERT_FINDINGS_INCREMENT_LOAD — Insert finding records into the summary table during initial and incremental loads.
• INSERT_REMEDS_INITIAL_LOAD / INSERT_REMEDS_INCREMENT_LOAD — Insert remediation records into the summary table during initial and incremental loads.
• CHECK_INITIAL_LOAD_SETUP — Validates global setup prior to running the initial load, raising INITIALIZATION_ERROR when prerequisites are not met.
• ERR_MESG — Formats and returns standardized error message text for the calling process.

Tables Accessed

The package reads and writes the following tables through APPS synonyms:

• RCI_OPEN_FINDINGS_F and RCI_OPEN_REMEDIATIONS_F — The primary summary/fact tables holding open finding and remediation records that are inserted and queried by the load and detail procedures.
• RCI_DR_INC — The incremental driver table used to identify records requiring incremental load processing.
• ENG_ENGINEERING_CHANGES, ENG_CHANGE_SUBJECTS, and ENG_CHANGE_OBJ_RELATIONSHIPS — Engineering change source tables joined to derive change-related remediation content.
• PLITBLM — The standard PL/SQL index-by table used for bulk processing during the load procedures.

Usage Notes

RCI_OPEN_REMED_SUMM_PKG is typically invoked in three contexts. First, the KPI and detail functions (GET_KPI, GET_OPEN_REMEDIATION_RESULT, GET_FINDINGS_DETAILS, GET_REMEDIATIONS_DETAILS) are called by Oracle Applications Framework dashboard regions to render the Open Remediations pages, invoked under the FND_GLOBAL session user. Second, the initial and incremental load procedures are executed as concurrent programs or scheduled jobs to keep the summary tables current with source engineering change data; CHECK_INITIAL_LOAD_SETUP should be run first to confirm global setup. Third, the package may be called from custom code for ad hoc remediation reporting. Because it is not referenced by other packaged APIs and contains dynamic SQL, DBAs should review its WHERE-clause construction and ensure the required global setup records (g_rci_schema) are configured before execution.