Results for “instance_code”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

The view EDW_OE_SCHN_SCHN_LCV is a collection view within the Oracle E-Business Suite Supply Chain Intelligence (ISC) product family, specifically the Sales Channel dimension of the EDW (Enterprise Data Warehouse) collection layer. It is owned by the APPS schema and reports a status of VALID. Its declared purpose, per the ETRM metadata, is to serve as the collection view for the Sales Channel level of the Sales Channel dimension — that is, the view that materializes the base sales channel dimension level from which the higher-level EDW sales channel hierarchy and other analytics surfaces are populated.

The view is a classic Oracle EBS "lookup-driven" collection view. It reads its attribute data from FND_LOOKUP_VALUES for the lookup type SALES_CHANNEL, qualifies rows by language, application, and security group, and joins to EDW_LOCAL_INSTANCE to attach an instance code to every lookup row. The sales_channel_pk column that triggered this lookup is the synthetic primary key generated by concatenating the lookup code and the instance code, which is the column downstream EDW extracts and reports key against.

Underlying Base Objects

Although the ETRM metadata notes no documented referenced base objects, the view text makes the dependency explicit. It joins two objects:

• FND_LOOKUP_VALUES — the Oracle Application Object Library lookup table, filtered to LOOKUP_TYPE = 'SALES_CHANNEL', with LANGUAGE = USERENV('LANG'), VIEW_APPLICATION_ID = 660 (the ISC/EDW application), and SECURITY_GROUP_ID = 0. The security group filter pins the view to the standard (non-secured) lookup set.
• EDW_LOCAL_INSTANCE — the EDW local instance table, which supplies INSTANCE_CODE. The cross join (no explicit join predicate) combines each lookup row with each local instance row.

The instance code is concatenated with the lookup code to form the composite source key. The lookup table supplies all descriptive and date-related attributes (dates, enabled flag, meaning, description, creation/update dates), while the instance table supplies the instance qualifier.

Key Columns

• SALES_CHANNEL_PK — the synthetic primary key, computed as FND.LOOKUP_CODE || '-' || INST.INSTANCE_CODE. This is the column most commonly referenced when joining to fact or bridge views.
• ALL_FK — a literal 'ALL' value exposed as a foreign key; used internally when the dimension level is expected to participate in an "all" rollup.
• ACTIVE_FROM_DATE / ACTIVE_TO_DATE — mapped from START_DATE_ACTIVE and END_DATE_ACTIVE on the lookup, defining the effective period of the sales channel value.
• CREATION_DATE / LAST_UPDATE_DATE — standard audit timestamps carried from the lookup record.
• ENABLED_FLAG — the lookup enabled flag; indicates whether the sales channel value is active for reporting.
• INSTANCE_CODE — the instance qualifier contributed by EDW_LOCAL_INSTANCE.
• NAME — the lookup meaning, exposed as the human-readable name of the sales channel.
• SALES_CHANNEL_CODE — the lookup code (source code for the sales channel value).
• SALES_CHANNEL_DP — a display column, computed as FND.MEANING || '-' || INST.INSTANCE_CODE, giving the combined display label.
• SALES_CHANNEL_NAME — description carried from the lookup; the descriptive name for the level value.
• USER_ATTRIBUTE1 … USER_ATTRIBUTE5 — five placeholder user-attribute columns, all returned as NULL in this implementation, reserved for customer extension.

Common Use Cases and Queries

This view is typically used as the source for the Sales Channel dimension level in EDW extracts and for validation of sales channel values. A representative query retrieves the key and active values:

SELECT sales_channel_pk,
       sales_channel_code,
       sales_channel_name,
       enabled_flag,
       last_update_date
FROM   apps.edw_oe_schn_schn_lcv
WHERE  enabled_flag = 'Y';

To reconcile the dimension against its lookup source, join back to FND_LOOKUP_VALUES on lookup code. A common troubleshooting pattern is to confirm that a given sales_channel_pk resolves to a single lookup row and one instance:

SELECT l.sales_channel_pk,
       l.sales_channel_code,
       l.instance_code
FROM   apps.edw_oe_schn_schn_lcv l
WHERE  l.sales_channel_pk = '&pk';

In summary, EDW_OE_SCHN_SCHN_LCV is a lookup-and-instance collection view that exposes the Sales Channel dimension level for Supply Chain Intelligence, with SALES_CHANNEL_PK as its composite surrogate key.