Search Results iex_strategy_work_items_u1

Overview

IEX.IEX_STRATEGY_WORK_ITEMS is a transactional table within the Oracle E-Business Suite Interaction Center (IEX) schema. It stores the individual work items that constitute a Collection Strategy — the ordered, reusable tasks and activities that agents or automated processes must complete against a delinquent customer account. In EBS 12.1.1 and 12.2.2, this table sits at the operational heart of Advanced Collections, where a strategy acts as a template-driven workflow of collection activities such as phone calls, dunning letters, promises to pay, or dispute resolutions.

Each row represents one discrete step within a specific strategy instance, linking a strategy header to a work item template and optionally to a resource owner. The table therefore bridges strategic planning (the strategy definition) and execution tracking (the actual activities performed by collection agents).

From a Data Vault modeling perspective, the metadata relationship analysis classifies this object as satellite-leaning. It is best modeled as a satellite attached to the strategy hub (identified by STRATEGY_ID) and the resource hub, carrying descriptive and status attributes that change over the lifecycle of the work item.

Key Information Stored

The surrogate primary key is WORK_ITEM_ID, enforced through the unique index IEX_STRATEGY_WORK_ITEMS_U1 on the WORK_ITEM_ID column. This column is the only documented business-key candidate (unique index), serving as the technical identifier for each work item row. The most significant columns include:

• WORK_ITEM_ID – Surrogate primary key, uniquely identifying each work item.
• STRATEGY_ID – Foreign reference to the parent strategy instance; indexed by IEX_STRATEGY_WORK_ITEMS_N3.
• WORK_ITEM_TEMPLATE_ID – References the reusable work item template that defines the task type; indexed by IEX_STRATEGY_WORK_ITEMS_N6.
• RESOURCE_ID – The collection agent or resource assigned to execute the work item (FK to JTF_RS_RESOURCE_EXTNS).
• STATUS_CODE – Current lifecycle status of the work item (e.g., open, complete, escalated).
• EXECUTE_START / EXECUTE_END – Actual execution window for the activity.
• SCHEDULE_START / SCHEDULE_END – Planned schedule; SCHEDULE_START is indexed via the function-based index IEX_STRATEGY_WORK_ITEMS_N4 using TRUNC("SCHEDULE_START").
• STRATEGY_TEMP_ID / WORK_ITEM_ORDER – Together indexed by IEX_STRATEGY_WORK_ITEMS_N1, defining sequencing of items within a strategy.
• UWQ_STATUS, UWQ_ACTIVE_DATE, UWQ_COMPLETE_DATE – Universal Work Queue integration columns tracking queue routing and completion.
• ESCALATED_YN / ESCALATE_YN / ESCALATE_WAIT_TIME – Escalation control attributes.
• OPTIONAL_YN, OPTIONAL_WAIT_TIME, NOTIFY_YN – Execution options governing mandatory versus optional steps.
• SECURITY_GROUP_ID – Subscriber identifier (FK to FND_SECURITY_GROUPS) for CRM Online Services.
• OBJECT_VERSION_NUMBER – Optimistic locking column used by the HTML/ADF front end.

Common Use Cases and Queries

Typical reporting scenarios include identifying overdue work items, monitoring agent workload, and measuring strategy execution effectiveness. A representative query lists open work items per resource:

• SELECT work_item_id, strategy_id, resource_id, status_code FROM iex_strategy_work_items WHERE status_code = 'OPEN' AND RESOURCE_ID = :p_resource_id;
• Joining to JTF_RS_RESOURCE_EXTNS to resolve the agent name for workload dashboards.
• Using TRUNC(SCHEDULE_START) filtering to produce daily schedules, leveraging index IEX_STRATEGY_WORK_ITEMS_N4.
• Aggregating ESCALATED_YN flags to report strategies breaching escalation thresholds.

Related Objects

The following objects are most significant for joins and dependencies:

• JTF_RS_RESOURCE_EXTNS – joined on RESOURCE_ID to resolve resource identities.
• FND_SECURITY_GROUPS – joined on SECURITY_GROUP_ID.
• IEX_STRATEGIES – parent strategy header, joined on STRATEGY_ID.
• IEX_WORK_ITEM_TEMPLATES – template definitions, joined on WORK_ITEM_TEMPLATE_ID.
• JTF_RS_RESOURCE_ASSIGNMENTS – supporting resource ownership context.
• Standard WHO columns reference FND_USER, FND_LOGINS, FND_CONCURRENT_PROGRAM, and FND_CONCURRENT_REQUESTS.