Results for “get_last_day”
8 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
The APPS.RCI_ORG_CERT_SUMM_PKG package body is the engine behind the Organization Certification Summary KPI, a component of the Oracle E-Business Suite governance, risk, and compliance functionality delivered through the Enterprise Risk Management (ERM) and Financial Controls modules. It supports the Organization Certification dashboard, which allows management to track the progress and results of financial certification activities across the organization, including financial statement certifications, sub-certifications by process or control owners, and the identification of deficient processes, unmitigated risks, and open issues. The package was last modified in the 2007 timeframe (per the header revision rciocsb.pls 120.39.12000000.6) and is classified as an OTHER API. Its principal entry point, GET_ORG_KPI, dynamically constructs a SQL query text and its associated column attribute metadata, which is then published to the OracleBI Discoverer / Performance Management Viewer (PMV) region as a portlet data source.
Key Procedures and Functions
The package exposes eleven documented procedures and functions that collectively supply the data behind the Organization Certification dashboard and its drill-down pages:
- GET_ORG_KPI — Dynamically assembles the SQL for the top-level Organization Certification KPI portlet. It accepts a page parameter table (
BIS_PMV_PAGE_PARAMETER_TBL) and returns an export-source SQL string and matching query attributes (BIS_QUERY_ATTRIBUTES_TBL). - GET_ORG_CERTIFICATION_RESULT and GET_ORG_CERT_PRCNT — Return the completed certification result counts and the associated certification percentage for the organization, forming the quantitative core of the KPI.
- GET_ORG_CERTIFICATION_DETAIL and GET_PROCESS_DETAIL — Supply record-level drill-down: the underlying certification rows for an organization, and the detail for a specific process, respectively.
- GET_UNMITIGATED_RISKS — Returns risks that remain unmitigated as of the reporting period, a critical compliance exposure metric.
- GET_CONTROL_LIST — Returns the list of controls associated with the certification context.
- GET_ISSUE_DETAIL — Returns detail on issues raised during the certification process.
- GET_DEFICIENT_PROCESSES — Returns processes identified as deficient, supporting remediation tracking.
- GET_DEFAULT_YEAR and GET_LAST_DAY — Utility routines providing default temporal context: the default fiscal year and the last day of a period or range, used in parameter initialization and date filtering.
Tables Accessed
The package reads from the following documented tables and views via APPS synonyms:
- FII_TIME_DAY — The day-level time dimension. In the
GET_ORG_KPISQL, it is joined toRCI_ORG_CERT_SUMM_Fonreport_date_julianto constrain the fact data to valid reporting dates. This join is central to the date-range filtering driven by theTIME+FII_TIME_ENT_PERIOD_FROMparameter. - DUAL — Used for single-row calculations and utility queries within the procedures.
- PLITBLM — A general-purpose PL/SQL index-by table manipulation package referenced internally. The underlying fact table
RCI_ORG_CERT_SUMM_F(not listed in the documented table inventory but visible in the source excerpt) holds the organization certification summary facts keyed byfin_certification_id,organization_id,certification_type, and report date.
Usage Notes
The package is invoked as the data source for Oracle PMV / Discoverer portlets embedded in the Organization Certification dashboard pages. The p_page_parameter_tbl argument carries user-selected filter values from the dashboard, and the procedure inspects each parameter by parameter_name and parameter_id; the parameter_id value is concatenated directly into the dynamic SQL, so filter parameters must always be validated numeric bindings before reaching this code. Recognized parameters include RCI_FS_CERT+RCI_FS_CERT_TYPE, RCI_FS_CERT+RCI_FS_CERT, and the time period parameters. A 2005 fix (bug 4880422) altered query construction to eliminate spurious "NA" text in the portlet output. The package is referenced by one other package, indicating it is a terminal reporting layer rather than a shared utility. Because the SQL is assembled by string concatenation into a 15,000-character buffer, any extension or customization of the underlying fact table structure or parameter naming convention requires corresponding changes here.