Search Results pv_lead_workflows_u1

Overview

PV.PV_LEAD_WORKFLOWS is a transactional table in the Oracle EBS Partner Management (PV) schema that records the workflow state of a lead as it is routed to channel partners. It stores whether a lead has been assigned to a partner and, if so, the current disposition of that assignment. The table also serves as the trigger point for notifications: whenever a lead is assigned to a partner or a lead status changes, the workflow engine consults the records in this table to dispatch notifications to all concerned parties.

In Oracle EBS 12.1.1 and 12.2.2, the table resides in the APPS_TS_TX_DATA tablespace and is owned by the PV schema, with FND design data registered as PV.PV_LEAD_WORKFLOWS. Its physical schema in 12.2.2 documents 21 columns. From a Data Vault modeling perspective, the dependency structure leans toward a satellite: the table hangs off AS_LEADS_ALL via LEAD_ID and carries descriptive, time-stamped workflow attributes rather than acting as an independent hub or as a pure many-to-many link.

Key Information Stored

The surrogate primary key is LEAD_WORKFLOW_ID, a NUMBER(15) generated per workflow instance and enforced by the PV_LEAD_WORKFLOWS_PK constraint. Two unique indexes provide business-key candidates: PV_LEAD_WORKFLOWS_U1 on LEAD_WORKFLOW_ID and PV_LEAD_WORKFLOWS_U2 on the composite (WF_ITEM_TYPE, WF_ITEM_KEY), which ties each row to exactly one workflow instance.

• LEAD_ID — the foreign key to AS_LEADS_ALL, identifying the lead being routed.
• WF_ITEM_TYPE / WF_ITEM_KEY — the workflow item identifiers; together they form the unique business key.
• WF_STATUS — indicates whether the lead is assigned to a partner or not.
• ROUTING_STATUS / ROUTING_TYPE — the state and classification of the routing event.
• LATEST_ROUTING_FLAG — marks whether the current routing is the most recent one.
• MATCHED_DUE_DATE / OFFERED_DUE_DATE — due dates for matching and offering opportunities to partners.
• ENTITY — the object affected (opportunity).
• FAILURE_CODE / FAILURE_MESSAGE — error diagnostics when routing fails.
• BYPASS_CM_OK_FLAG — flag to bypass collateral management checks.
• SECURITY_GROUP_ID — FK to FND_SECURITY_GROUPS, supporting multi-tenant data isolation.
• Standard Who columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) plus OBJECT_VERSION_NUMBER for optimistic locking.

Common Use Cases and Queries

Typical scenarios include determining the current routing status of a lead, auditing failed partner assignments, and identifying the "latest" routing per lead. Key indexes PV_LEAD_WORKFLOWS_N1 (LEAD_ID) and PV_LEAD_WORKFLOWS_N2 (ROUTING_STATUS, ROUTING_TYPE, LATEST_ROUTING_FLAG) support these patterns efficiently.

• Retrieve all routings for a given lead: SELECT LEAD_WORKFLOW_ID, WF_STATUS, ROUTING_STATUS FROM PV.PV_LEAD_WORKFLOWS WHERE LEAD_ID = :lead_id;
• Find the latest routing per lead: SELECT * FROM PV.PV_LEAD_WORKFLOWS WHERE LEAD_ID = :id AND LATEST_ROUTING_FLAG = 'Y';
• Report failed routings: SELECT LEAD_ID, FAILURE_CODE, FAILURE_MESSAGE FROM PV.PV_LEAD_WORKFLOWS WHERE FAILURE_CODE IS NOT NULL;
• Locate a specific workflow instance via the business key U2: SELECT * FROM PV.PV_LEAD_WORKFLOWS WHERE WF_ITEM_TYPE = :type AND WF_ITEM_KEY = :key;

Related Objects

• AS_LEADS_ALL — joined via PV_LEAD_WORKFLOWS.LEAD_ID; the parent lead record.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID for security group scoping.
• PV_OPPTY_ROUTING_LOGS — references this table through its LEAD_WORKFLOW_ID column, capturing opportunity routing history.
• Oracle Workflow engine references (WF_ITEM_TYPE / WF_ITEM_KEY) link this table to workflow item instances.