Results for “script_trans_id”

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

Overview

AMS_DS_INTERACTIONS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the AMS (Marketing) product family and is defined over the Interaction History (JTF_IH) tables that Oracle Marketing and TeleSales use to record customer interactions. Despite the "AMS_DS" prefix, which suggests a Marketing data-source or datastore construct, the view is a denormalized projection over the JTF Interaction History schema, joining activities, interactions, parties, outcomes, results, reasons, action items, actions, and media items into a single wide row.

The view's principal purpose is to present a flattened, human-readable representation of an activity performed during an interaction. Each row corresponds to an action item (ACT.ACTION_ITEM_ID) with its parent action, the interaction it belongs to, the party involved, and the descriptive lookup values for outcome, result, and reason. Because the descriptive columns are outer-joined to the _TL / _VL translation tables with the LANGUAGE(+) = USERENV('LANG') predicate, the view returns descriptions in the session language while preserving rows that have no matching translation. This makes it convenient for concurrent programs, OBIEE/BI Publisher reports, and downstream integrations that need a single row per action item without writing the join logic themselves.

The view is relevant to users searching for "action_item" because the ACTION_ITEM_ID and ACTION_ITEM columns expose exactly that concept: the specific action item recorded against an interaction, together with the concatenated display value ACTION_ITEM || ACTION surfaced as the ACTIVITY alias.

Underlying Base Objects

The view is defined over nine documented base objects, all accessed via public synonyms in APPS:

• JTF_IH_ACTIVITIES (alias ACT) — the driving table, supplying the action item activity record, dates, foreign keys, and source/object identifiers.
• JTF_IH_INTERACTIONS (alias INT) — the parent interaction, providing RESOURCE_ID, HANDLER_ID, PARTY_ID, and CREATION_DATE.
• HZ_PARTIES (alias HZ) — the trading community party, supplying PARTY_NAME.
• JTF_IH_OUTCOMES_TL (alias OUT) — outcome code and description, outer-joined by language.
• JTF_IH_RESULTS_VL (alias RSL) — result code, description, and POSITIVE_RESPONSE_FLAG.
• JTF_IH_REASONS_TL (alias REA) — reason code and description, outer-joined by language.
• JTF_IH_ACTION_ITEMS_TL (alias ACI) — the action item text, outer-joined by language.
• JTF_IH_ACTIONS_TL (alias ACTN) — the action text, outer-joined by language.
• JTF_IH_MEDIA_ITEMS (alias JMI) — media item type and direction, outer-joined.

The join chain is anchored on ACT.INTERACTION_ID = INT.INTERACTION_ID and INT.PARTY_ID = HZ.PARTY_ID. All lookup and media joins are outer joins, so an activity row survives even when no outcome, result, reason, action item, action, or media item is recorded.

Key Columns

• ACTION_ITEM_ID / ACTION_ID / ACTIVITY_ID / INTERACTION_ID — primary keys and foreign keys identifying the action item, its parent action, the activity, and the interaction.
• ACTIVE, START_DATE_TIME, END_DATE_TIME — lifecycle state and timing of the activity.
• PARTY_NAME, PARTY_ID, CUST_ACCOUNT_ID, RESOURCE_ID, HANDLER_ID — the party and the resources (agent, handler) involved.
• ACTION_ITEM, ACTION, ACTIVITY — the action item text, the action text, and the concatenated ACTION_ITEM || ACTION display value.
• OUTCOME_CODE, RESULT_CODE, REASON_CODE and their DESCRIPTION columns — coded and descriptive outcome of the interaction.
• POSITIVE_RESPONSE_FLAG — indicates whether the result represents a positive customer response.
• MEDIA_ITEM_TYPE, DIRECTION, DOC_ID, DOC_REF, SOURCE_CODE, OBJECT_ID — context about media used and the source document or object.
• CREATION_DATE, SCRIPT_TRANS_ID, AVT_SCRIPT_ID/SCRIPT_ID — creation timestamp and script linkage for telesales scripting.

Common Use Cases and Queries

Typical uses include interaction-history reporting for telesales and marketing campaigns, extraction of action items for follow-up tracking, and BI Publisher or OBIEE data sources that need party-level interaction detail.

List action items for a given interaction:

• SELECT action_item_id, action_item, action, activity, outcome_code, result_code, reason_code FROM ams_ds_interactions_v WHERE interaction_id = :p_interaction_id;

Retrieve recent interactions for a party with positive responses:

• SELECT party_name, creation_date, action_item, result_description FROM ams_ds_interactions_v WHERE party_id = :p_party_id AND positive_response_flag = 'Y' ORDER BY creation_date DESC;

Count action items by outcome for a date range:

• SELECT outcome_code, COUNT(*) FROM ams_ds_interactions_v WHERE creation_date BETWEEN :p_from AND :p_to GROUP BY outcome_code ORDER BY 2 DESC;

Because the view performs multiple outer joins, report queries should filter on indexed columns such as INTERACTION_ID, PARTY_ID, or CREATION_DATE to limit the row source before the joins are evaluated. Joins to HZ_PARTIES and the _TL tables rely on the session NLS language, so concurrent programs should set the language environment consistently to obtain the expected descriptions.