Results for “slschnl_fk”

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

Overview

BIM_EDW_OPRNTIES_FSTG is a staging interface table within the Oracle E-Business Suite Marketing Intelligence module (product code BIM), classified as obsolete in later EBS releases. Its documented purpose is to serve as the interface table for the opportunity fact, acting as the landing zone through which opportunity-level transactional and dimensional data is loaded into the Marketing Intelligence data warehouse before being promoted to the permanent fact structures.

In EBS 12.1.1 the table is owned by the BIM schema and is documented with 85 columns. The ETRM metadata records the object as "Not implemented in this database," indicating the staging table is created conditionally and is only materialized when the Marketing Intelligence warehouse components are installed and the relevant concurrent programs are executed. Because the table is a staging structure, its rows are transient: data is inserted by extract/load programs, validated, and then merged or deleted.

Under the heuristic Data Vault classification mined from the foreign-key structure, this object presents as a standalone table. In Data Vault terms it behaves most like a satellite or a raw landing structure rather than a true hub or link, because the documented foreign keys do not establish parent-child relationships with other hubs within the same model — they instead point outward to control tables. This classification should be treated as a modeling suggestion rather than a definitive architectural statement.

Key Information Stored

The table's primary key is defined by the unique constraint BIM_EDW_OPRNTIES_FSTG_U, which is enforced on the OPPR_LINE_PK column — the opportunity line surrogate key. This is the column most often referenced in the user's search and is the single most important identifier in the table. It distinguishes individual opportunity fact line records during the staging cycle.

• OPPR_LINE_PK — surrogate primary key and the unique business identifier for each staged opportunity line.
• CMPGN_FK / CMPGN_FK_KEY — surrogate and business key pair identifying the campaign associated with the opportunity line.
• OPPLINE_FK / OPPLINE_FK_KEY — the underlying opportunity line reference from the operational source.
• OPPSTATUS_FK / OPPSTATUS_FK_KEY and CMPGN_STATUS_FK / CMPGN_STATUS_FK_KEY — status dimension references for opportunity and campaign.
• CUSTOMER_FK / CUSTOMER_FK_KEY — customer dimension reference for the opportunity.
• TIME_FK / TIME_FK_KEY — time dimension reference used for period-based fact aggregation.
• ORG_FK / ORG_FK_KEY — operating unit or organization dimension reference.
• OPPR_AMOUNT_G, OPPR_AMOUNT_B, OPPR_AMOUNT_T — the opportunity amount measures expressed in global, base, and transactional currencies respectively.
• OPPR_UNITS — quantity measure for the opportunity line, paired with UOM_FK / UOM_FK_KEY for the unit of measure.
• REQUEST_ID — the concurrent request that loaded the staging row, essential for troubleshooting and reprocessing.
• OPERATION_CODE, ERROR_CODE, COLLECTION_STATUS — control columns driving the staging lifecycle (insert, update, delete, validation outcome).
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing row-level security partitioning.
• ROW_ID — foreign key to CS_SYSTEMS_ALL_B_TEMP, linking the staged record back to the source collection system.

Together with the USER_ATTRIBUTE1 through USER_ATTRIBUTE15 and USER_FK1 through USER_FK5 extensibility columns, these fields allow the warehouse to carry source-specific context through the staging layer without altering the physical schema.

Common Use Cases and Queries

The principal use case is diagnosing and monitoring the Marketing Intelligence opportunity fact load. A typical diagnostic query isolates rows that failed validation or remain unprocessed:

• Error triage: SELECT OPPR_LINE_PK, ERROR_CODE, COLLECTION_STATUS, REQUEST_ID FROM BIM.BIM_EDW_OPRNTIES_FSTG WHERE ERROR_CODE IS NOT NULL;
• Load audit: filter by REQUEST_ID to confirm how many rows a specific concurrent program inserted or updated.
• Dimensional reconciliation: join the FK/_KEY pairs against their corresponding dimension staging tables to verify that every opportunity line resolves to a valid campaign, customer, time, and organization.
• Measure validation: compare OPPR_AMOUNT_G, OPPR_AMOUNT_B, and OPPR_AMOUNT_T for currency conversion consistency before promotion.
• Duplicate detection: group by OPPR_LINE_PK or by OPPLINE_FK_KEY to surface multiple staging rows for the same opportunity line.

Reporting teams typically do not query this table directly for end-user analytics; instead it is queried by ETL developers and support analysts during load failures or data reconciliation exercises.

Related Objects

The metadata documents two explicit foreign-key relationships and one primary-key-dependent staging flow:

• FND_SECURITY_GROUPS — referenced by BIM_EDW_OPRNTIES_FSTG.SECURITY_GROUP_ID; controls row-level security ownership of staged rows.
• CS_SYSTEMS_ALL_B_TEMP — referenced by BIM_EDW_OPRNTIES_FSTG.ROW_ID; identifies the source collection system for the staged opportunity record.
• BIM_EDW_OPPRTNTY_FACT (and related permanent opportunity fact objects) — the target warehouse structures populated from this staging table once validation completes.
• BIM_EDW_CMPGN_FSTG, BIM_EDW_CUSTOMER_FSTG, and counterpart dimension staging tables — joined via the *_FK_KEY columns to resolve campaign, customer, and time dimensions during promotion.
• Concurrent program / request infrastructure (FND_CONCURRENT_REQUESTS) — related through REQUEST_ID for load traceability and job monitoring.

Because the object is documented as obsolete and not implemented in the reference database, administrators should confirm its physical existence by querying ALL_TABLES for owner BIM before constructing dependent SQL, and should regard any production dependency on it as a legacy artifact of the Marketing Intelligence warehouse design.