Search Results orig_system_refernce

Overview

APPS.AS_ISSUE_GROUP_DETAILS_V is a reporting and integration view in the Oracle E-Business Suite (12.1.1 and 12.2.2) that consolidates issue group header, translation, type, and identifier information into a single denormalized row per issue group. The view belongs to the Oracle Telephony / Interaction Center (Service) product family and is defined in the APPS schema over four synonym-referenced base objects. Its principal design objective is to present human-readable issue group names in the session language alongside the multilingual type meaning and description, while also surfacing the external system references used for data exchange with third-party or legacy applications.

The view is commonly consumed by reports, concurrent programs, and inbound/outbound interfaces that must resolve an issue group's internal primary key to its translated display name and to the concatenated identifier columns (scheme, value, search code) used by external systems. Because the view filters on userenv('LANG'), it always returns rows in the language of the active session, which makes it suitable for locale-aware reporting.

Underlying Base Objects

The ETRM metadata documents four base objects, all referenced through public synonyms in APPS:

• AS_ISSUE_GROUPS_B — the base (non-translated) table holding issue group definitions, active date ranges, orig system references, audit columns, and the issue group type code.
• AS_ISSUE_GROUPS_TL — the translated table supplying the NAME column, joined on ISSUE_GROUP_ID and constrained by IGTL.LANGUAGE = userenv('LANG').
• AS_ISSUE_GROUP_TYPES — the reference table of issue group types, providing the type code, meaning, description, and the CLIENT_CONTACT / CLIENT_ORG enabled flags. It is also language-filtered via userenv('LANG').
• AS_ISSUE_GROUP_IDFR — the identifier table holding scheme, value, and search code attributes. It is outer-joined with (+) to the base table, so issue groups without identifiers still return a row with NULL identifier columns.

Joins are performed on ISSUE_GROUP_ID (B→TL and B→IDFR) and on ISSUE_GROUP_TYPE_CODE (B→TYPE).

Key Columns

• ISSUE_GROUP_ID, ISSUE_GROUP_NAME — the primary key and language-translated name.
• START_DATE_ACTIVE, END_DATE_ACTIVE — validity window for the issue group.
• ORIG_SYSTEM_REFERNCE — note the typographical alias in the view text (the underlying column is ORIG_SYSTEM_REFERENCE). This column identifies the originating external system. Users searching for "orig_system_refernce" are likely targeting this column.
• ORIG_SYSTEM_REFERENCE_ID — the external system's identifier for the same record, enabling cross-system reconciliation.
• ISSUE_GROUP_TYPE_CODE, ISSUE_GROUP_TYPE_MEANING, ISSUE_GROUP_TYPE_DESCRIPTION — type classification and its translated meaning/description.
• CLIENT_CONTACT_ENABLED_FLAG, CLIENT_ORG_ENABLED_FLAG — indicate whether the type supports client contact and client organization assignment.
• ISSUE_GROUP_IDFR_ID, SCHEME, VALUE, SEARCH_CODE — identifier attributes; NULL when no AS_ISSUE_GROUP_IDFR row exists.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE — standard audit columns.

Common Use Cases and Queries

Typical scenarios include resolving issue group names for interaction or service request reporting, driving picklists in custom forms, and mapping legacy/third-party records via the ORIG_SYSTEM_REFERENCE pair. A representative query that surfaces all issue groups for the current session language is:

SELECT ISSUE_GROUP_ID, ISSUE_GROUP_NAME, ISSUE_GROUP_TYPE_MEANING, ORIG_SYSTEM_REFERNCE, ORIG_SYSTEM_REFERENCE_ID FROM APPS.AS_ISSUE_GROUP_DETAILS_V WHERE START_DATE_ACTIVE <= SYSDATE AND NVL(END_DATE_ACTIVE, SYSDATE+1) > SYSDATE;

To locate a record by external reference (the common use of the searched term):

SELECT ISSUE_GROUP_ID, ISSUE_GROUP_NAME, ORIG_SYSTEM_REFERENCE_ID FROM APPS.AS_ISSUE_GROUP_DETAILS_V WHERE ORIG_SYSTEM_REFERNCE = :p_orig_system;

Because the view exposes both the translated name and the identifier scheme/value, it is also convenient for validating identifier completeness, e.g. selecting rows WHERE ISSUE_GROUP_IDFR_ID IS NULL to detect issue groups lacking an identifier. When querying as a non-APPS user, grant access to the corresponding synonym rather than the underlying tables.