Search Results jtf_ih_interactions_campaig_pk

Overview

The JTF_IH_INTERACTIONS_CAMPAIGNS table is a core intersection table within the Oracle E-Business Suite (EBS) CRM Foundation module (JTF). Its primary role is to establish and manage the many-to-many relationship between customer interactions and marketing campaigns. This table is essential for linking transactional interaction history, managed by the Interaction History module, with campaign management data from the Marketing module (AMS). By maintaining these associations, it enables comprehensive tracking of which customer interactions originated from or are related to specific marketing initiatives, thereby facilitating closed-loop marketing analysis and campaign performance measurement in both EBS 12.1.1 and 12.2.2.

Key Information Stored

The table's structure is designed to store foreign key references that link the two primary entities. The most critical columns are the foreign key columns themselves. The INTERACTION_ID column references the JTF_IH_INTERACTIONS table, uniquely identifying a specific customer interaction, such as a call, email, or service request. The SOURCE_CODE_ID column references the AMS_SOURCE_CODES table, which contains definitions for marketing campaign sources and related codes. This column effectively links the interaction to a specific campaign or marketing source. The primary key constraint, JTF_IH_INTERACTIONS_CAMPAIG_PK, is defined on the INTERACTION_ID, ensuring that a given interaction record has a unique entry for its campaign association within this table.

Common Use Cases and Queries

This table is central to reporting and analytics that measure campaign effectiveness. A typical use case involves analyzing the volume and type of customer service interactions generated by a particular marketing campaign to calculate response rates or assess lead quality. For instance, a support manager might query to identify all service requests linked to a recent product launch campaign. A common SQL pattern joins this intersection table to both the interactions and campaigns details:

• Identifying interactions for a campaign: SELECT i.interaction_id, i.start_date_time FROM jtf_ih_interactions i, jtf_ih_interactions_campaigns ic, ams_source_codes sc WHERE i.interaction_id = ic.interaction_id AND ic.source_code_id = sc.source_code_id AND sc.name = '&CAMPAIGN_NAME';
• Reporting on campaign-driven interaction counts: SELECT sc.name AS campaign_name, COUNT(*) AS interaction_count FROM jtf_ih_interactions_campaigns ic, ams_source_codes sc WHERE ic.source_code_id = sc.source_code_id GROUP BY sc.name;

Related Objects

The JTF_IH_INTERACTIONS_CAMPAIGNS table has defined dependencies on two primary tables, as indicated by its foreign keys. The JTF_IH_INTERACTIONS table is the parent for all interaction data, providing the core details of the customer contact. The AMS_SOURCE_CODES table is the parent for campaign and source code definitions within the Marketing module. This table acts as a critical bridge between these two major functional areas. While not explicitly listed in the provided metadata, it is also commonly referenced in custom reports and analytics views that combine service history with marketing data, and it may be accessed indirectly through public APIs provided by the Interaction History and Marketing modules.