Search Results rci_bp_org_cert_result_v




Overview
Explore in AI Chat

Overview

The view APPS.RCI_BP_ORG_CERT_RESULT_V is a reporting and dimension view belonging to the AMW — Internal Controls Manager (ICM) product within Oracle E-Business Suite releases 12.1.1 and 12.2.2. It supplies the definition of the Organization Certification Results dimension, used by Oracle Business Intelligence (OBIEE) and related ETRM (Enterprise Transaction & Risk Management) dimensional models that underpin ICM certification dashboards and analytical reporting.

In practical terms, the view enumerates the valid set of certification outcome values — the certification results — that can be associated with an organization certification cycle. Because certification outcomes include both configurable opinion values and the predefined internal status NOT_CERTIFIED, the view merges two logical sources into a single, unified list of result identifiers and display values. This makes it the authoritative look-up for reporting prompts, dashboard filters (for example, a filter on not_certified), and ETL loads into the certification results dimension.

The view is defined with status VALID in the APPS schema, indicating it is compiled and available for query at runtime. It is read-only by design, providing a stable interface that shields consumers from the underlying opinion and look-up table structures.

Underlying Base Objects

The view text is a UNION ALL of two queries, each drawing on a distinct set of base objects.

The first branch joins the opinion value base and translation tables to the opinion configuration chain:

The second branch reads the Oracle Application Object Library look-up table:

All joins in the first branch are inner joins on the respective identifier columns, so a value is returned only when the full opinion-to-organization certification chain resolves. The END_DATE predicate excludes expired opinion values. The UNION ALL deliberately preserves the NOT_CERTIFIED row even if it duplicates another value, ensuring this system status is always retrievable as a distinct result.

Key Columns

  • ID — the result identifier. In the first branch this is B.OPINION_VALUE_CODE; in the second branch it is AL.LOOKUP_CODE (which yields the literal NOT_CERTIFIED). This is the value used to key the dimension and to drive filters.
  • VALUE — the human-readable display name. In the first branch this is the translated TL.OPINION_VALUE_NAME; in the second branch it is the look-up MEANING for NOT_CERTIFIED. This is the label shown to users in prompts and reports.

The view exposes only these two columns, keeping the interface minimal: one code for programmatic matching and one description for presentation. Because VALUE is drawn from translation tables, output is language-sensitive and respects the querying session's language setting.

Common Use Cases and Queries

The most frequent use is populating a certification results filter or parameter list, including targeted searches for a specific outcome. A representative query to retrieve all certification results, and to isolate the non-certified status in particular, is:

  • Retrieve all results: SELECT id, value FROM apps.rci_bp_org_cert_result_v ORDER BY value;
  • Find a specific status: SELECT id, value FROM apps.rci_bp_org_cert_result_v WHERE id = 'NOT_CERTIFIED';
  • Fuzzy lookup for dashboards: SELECT id, value FROM apps.rci_bp_org_cert_result_v WHERE UPPER(value) LIKE '%NOT%';
  • Join to certification facts on the result code to label outcomes in an analytical query.

Because the ID values are codes rather than surrogate keys, they are stable across reporting cycles and safe to embed in OBIEE repository mappings and saved analyses. Consumers should note that effective-dated opinion values are filtered to the current date, so historical certification results must be interpreted in the context of the reporting period rather than re-queried from this view alone.