Search Results intrctn_result_fk

Overview

The view BIM_EDW_INTRCTNS_FCV belongs to the Oracle EBS Marketing Intelligence (BIM) module, a component of the Enterprise Data Warehouse (EDW) reporting layer. The suffix FCV denotes a flat, conformed view designed specifically for consumption by the EDW and downstream business intelligence tools. Its purpose is to expose interaction-level marketing data — campaigns, activities, parties, and associated segmentation attributes — in a denormalized, warehouse-ready format that abstracts the complexity of the underlying transactional tables.

The ETRM metadata for the 12.2.2 release explicitly records that this view is not implemented in this database. This indicates that the object is obsolete in the current release and is retained only as a historical or reference definition. Its documented base-object list is empty, confirming that no live dependencies are tracked against it. The view is characteristic of the BIM Marketing Intelligence data model, which was superseded by later Oracle marketing analytics offerings.

Underlying Base Objects

No referenced base objects are documented for this view, which is consistent with its obsolete status. The view text, however, reveals that it is constructed from a join between the interaction activity table (aliased JIA), the interaction item/party table (aliased JII), a business source campaign detail object (aliased BSCD), an instance/party-site object (aliased HPS), and an instance-code source (aliased INST). Several derived foreign keys are computed at query time through PL/SQL package calls rather than stored joins, including EDW_BIM_CMPSTATS_PKG.GET_CAMPAIGN_STATUS_FK, EDW_BIM_MKTSGMTS_PKG.GET_MKTSGMT_FK, EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK, EDW_BIM_SRCLISTS_PKG.GET_SRCLIST_FK, and EDW_BIM_IH_MEDIA_PKG.INTRCTN_MEDIA_FK. Because these lookups execute per row, the view carried significant runtime cost and was unsuitable for high-volume interactive queries.

Key Columns

The view exposes a composite primary key (INTERACTION_PK) built from ACTIVITY_ID concatenated with INSTANCE_CODE, along with numerous foreign keys that link the interaction to warehouse dimensions:

• CMPGN_FK, CMPGN_STATUS_FK, EVENT_FK, MEDCHNL_FK, OFFER_FK — campaign, status, event, media channel, and offer references.
• MKTSGMT_FK, TGTSGMT_FK, SRCLST_FK — market segmentation, target segmentation, and source list references derived via package functions.
• INTRCTN_MEDIA_FK, INTRCTN_OUTCOME_FK, INTRCTN_REASON_FK, INTRCTN_RESULT_FK — interaction media, outcome, reason, and result, each defaulting to NA_EDW when the source value is null.
• CUSTOMER_FK — composed as PARTY_ID-INSTANCE_CODE-PARTY-TPRT.
• TIME_FK — derived from START_DATE_TIME in DD-MM-YYYY-DAY format.
• ORG_FK — the column most relevant to the org_fk search. It is generated by DECODE(JII.ORG_ID, '', 'NA_EDW', JII.ORG_ID || '-' || INST.INSTANCE_CODE), encoding the operating unit or organization identifier together with the instance code, defaulting to NA_EDW when ORG_ID is null.
• BILL_TO_SITE_FK and SHIP_TO_SITE_FK — both derived identically from PARTY_SITE_ID.
• Measures and descriptors: ACTIVITY_DURATION, ACTIVITY_START_TIME, ACTIVITY_END_TIME, SOURCE_CODE, and placeholder USER_MEASURE1 through USER_MEASURE3.

Common Use Cases and Queries

In historical BIM deployments, this view supplied fact rows for marketing interaction analysis, allowing reporting on campaign attribution, party targeting, channel effectiveness, and segmentation performance. A typical query joins the generated foreign keys to warehouse dimension tables, or filters on the organization dimension:

SELECT INTERACTION_PK, ORG_FK, CMPGN_FK, CUSTOMER_FK, ACTIVITY_DURATION
FROM   BIM_EDW_INTRCTNS_FCV
WHERE  ORG_FK = :p_org_id || '-' || :p_instance_code;

Because the view is documented as obsolete and not implemented, these queries will not execute in a 12.1.1 or 12.2.2 instance where the object is absent. The definition remains valuable only as a reference for understanding the legacy BIM EDW integration model and for migration or archival analysis.