Results for “ams_person_interactions_v”

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

Overview

AMS_PERSON_INTERACTIONS_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the AMS (Marketing) product family. It presents interaction records captured by the interaction history framework (JTF) and resolves them against person-type parties and their customer accounts. The view answers a common reporting requirement: listing interactions that are attributable to an identified individual customer rather than to an organization, a contact, or an anonymous channel session.

The view is read-only. It exists to support marketing analytics, telemarketing and telesales reporting, campaign response analysis, and integration extracts that need a flattened, denormalized slice of interaction data. It is closely associated with Oracle TeleSales and Oracle Marketing responsibilities, where the interaction history tables supply the underlying activity stream. Because the filter is hard-coded to HP.PARTY_TYPE = 'PERSON' and HP.STATUS = 'A', the view returns only active person parties; organizational interactions are excluded by design.

Underlying Base Objects

The view is defined over four documented base objects, all referenced through APPS synonyms:

• JTF_IH_INTERACTIONS — the driving table, aliased JIIV. It supplies interaction identifiers, timing, duration, outcome, method, source, and the REFERENCE_FORM column.
• JTF_IH_ACTIVITIES — aliased JIA, joined on INTERACTION_ID. It contributes the customer account linkage used to resolve CUST_ACCOUNT_ID.
• HZ_PARTIES — aliased HP, joined on PARTY_ID. It provides PARTY_NAME and supplies the person/active status filter.
• HZ_CUST_ACCOUNTS — aliased HCA, joined on CUST_ACCOUNT_ID with an outer join (HCA.CUST_ACCOUNT_ID(+)), so interactions without a resolved account are still returned.

The join semantics are significant: the inner joins against JTF_IH_ACTIVITIES and HZ_PARTIES make the activity and party records mandatory, while the outer join to HZ_CUST_ACCOUNTS preserves rows where no account exists. A join to HZ_CUST_ACCOUNTS involving person parties typically resolves through the account's party relationship, so a person with multiple accounts can produce multiple rows for a single interaction.

Key Columns

• INTERACTION_ID — primary key of the interaction record; the join key to JTF_IH_ACTIVITIES and other interaction-history children.
• PARTY_ID and PARTY_NAME — the person party and its display name, sourced from HZ_PARTIES.
• CUST_ACCOUNT_ID and ACCOUNT_NUMBER — the resolved customer account, potentially null due to the outer join.
• REFERENCE_FORM — a marker on the interaction that identifies the form or entry point through which the interaction was referenced or created; it is frequently used to separate channel-specific interactions in reporting.
• RESOURCE_ID and HANDLER_ID — the resource and the handler associated with the interaction.
• START_DATE_TIME, END_DATE_TIME, and DURATION — timing attributes for interval and duration reporting.
• OUTCOME_ID, RESULT_ID, REASON_ID — lookup identifiers describing the disposition of the interaction.
• PRODUCTIVE_TIME_AMOUNT, NON_PRODUCTIVE_TIME_AMOUNT, PREVIEW_TIME_AMOUNT, WRAP_UP_TIME_AMOUNT, INTER_INTERACTION_DURATION — granular telephony timing measures.
• METHOD_CODE, SOURCE_CODE, SOURCE_CODE_ID, OBJECT_ID, OBJECT_TYPE, OBJECT_VERSION_NUMBER, SCRIPT_ID — context and origin attributes.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard DFF (descriptive flexfield) columns.
• CREATED_BY_NAME, LAST_UPDATED_BY_NAME, LAST_UPDATE_LOGIN_NAME — aliased audit columns; note these are aliases of the numeric CREATED_BY, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN values, not resolved user names.

Common Use Cases and Queries

Typical uses include interaction volume by person, telemarketing productivity analysis, and channel attribution by REFERENCE_FORM. The view is also used as a source in AMS marketing extracts and in custom concurrent programs. A representative query filters by reference form and date range:

SELECT interaction_id,
       party_id,
       party_name,
       account_number,
       reference_form,
       method_code,
       start_date_time,
       duration,
       outcome_id
  FROM apps.ams_person_interactions_v
 WHERE reference_form = :p_reference_form
   AND start_date_time >= :p_from_date
   AND start_date_time <  :p_to_date
 ORDER BY start_date_time DESC;

Aggregation by reference form is equally common:

SELECT reference_form,
       COUNT(DISTINCT interaction_id) interaction_count,
       SUM(productive_time_amount) productive_time
  FROM apps.ams_person_interactions_v
 WHERE start_date_time >= TRUNC(SYSDATE) - 30
 GROUP BY reference_form
 ORDER BY interaction_count DESC;

Because the join to HZ_CUST_ACCOUNTS is outer and person parties may hold multiple accounts, queries that count interactions should use COUNT(DISTINCT interaction_id) to avoid double counting. Performance is generally acceptable for date-bounded queries; unrestricted full scans over large interaction-history volumes should be avoided, and indexes on REFERENCE_FORM and START_DATE_TIME in the base interaction table are beneficial. All access should be read-only through the APPS synonym.