Search Results pv_lead_workflows_pk

Overview

PV_LEAD_WORKFLOWS is a table in the PV (Partner Management) schema of Oracle E-Business Suite, present in both the 12.1.1 and 12.2.2 releases. It stores the linkage between partner leads and Oracle Workflow notifications, recording the workflow item generated for each lead assignment so that assignment, offer, and routing decisions can be tracked to completion. The object is central to lead distribution behaviour in Partner Management: when a lead is assigned to a partner or partner contact, a workflow process is instantiated, and a row in PV_LEAD_WORKFLOWS anchors that process instance to the originating lead. Its status as a VALID dictionary object confirms it is part of the shipped schema.

From the documented foreign-key structure, the table is best treated as a satellite-leaning structure in Data Vault terms. It hangs directly off AS_LEADS_ALL through LEAD_ID and carries descriptive and state attributes (status, due dates, failure information) that describe the lead's routing lifecycle rather than serving as a pure transaction link. The heuristic classification is a modelling suggestion only; the table is a standard EBS operational table, not a Data Vault construct.

Key Information Stored

Each row is uniquely identified by the surrogate primary key LEAD_WORKFLOW_ID (PV_LEAD_WORKFLOWS_PK, mirrored by the unique index PV_LEAD_WORKFLOWS_U1). A second documented business-key candidate, PV_LEAD_WORKFLOWS_U2 on (WF_ITEM_TYPE, WF_ITEM_KEY), ties the record to a specific Oracle Workflow item instance, making the workflow identity the principal alternate key.

• LEAD_ID — foreign key to AS_LEADS_ALL; the lead to which the workflow notification belongs.
• WF_ITEM_TYPE and WF_ITEM_KEY — the Oracle Workflow item type and item key identifying the notification process instance.
• WF_STATUS — current status of the associated workflow item.
• ROUTING_STATUS and ROUTING_TYPE — state and category of the routing decision applied to the lead.
• MATCHED_DUE_DATE and OFFERED_DUE_DATE — deadlines for the matched and offered stages of the assignment cycle.
• LATEST_ROUTING_FLAG — indicates whether this row represents the most recent routing event for the lead.
• BYPASS_CM_OK_FLAG — records whether the channel-manager bypass condition was satisfied.
• ENTITY — the entity context against which routing was performed.
• FAILURE_CODE and FAILURE_MESSAGE — diagnostic output when workflow generation or routing fails.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, governing multi-org/security access to the record.
• Standard audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN and OBJECT_VERSION_NUMBER (the latter supporting optimistic locking).

Common Use Cases and Queries

Typical reporting asks which leads have pending workflow notifications, how long matched or offered items have been outstanding, and which routing attempts failed. A representative query joins the workflow records to their leads and filters on status and due dates:

• SELECT w.LEAD_WORKFLOW_ID, w.LEAD_ID, w.WF_ITEM_TYPE, w.WF_ITEM_KEY, w.WF_STATUS, w.ROUTING_STATUS, w.MATCHED_DUE_DATE, w.OFFERED_DUE_DATE FROM PV.PV_LEAD_WORKFLOWS w WHERE w.WF_STATUS = 'NOTIFIED' AND w.OFFERED_DUE_DATE < SYSDATE;
• Identifying the current routing row for each lead: WHERE LATEST_ROUTING_FLAG = 'Y'.
• Exception reporting on failures: WHERE FAILURE_CODE IS NOT NULL, selecting FAILURE_CODE and FAILURE_MESSAGE.
• Cross-referencing a workflow item back to its lead: join on WF_ITEM_TYPE and WF_ITEM_KEY to the Workflow runtime tables, since those two columns form the alternate unique key.
• Auditing routing history per lead by grouping on LEAD_ID and ordering by CREATION_DATE.

Related Objects

The table participates in a small but well-defined dependency cluster:

• AS_LEADS_ALL — parent lead table; joined via PV_LEAD_WORKFLOWS.LEAD_ID = AS_LEADS_ALL.LEAD_ID.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID to enforce security-group visibility.
• PV_OPPTY_ROUTING_LOGS — child table that references PV_LEAD_WORKFLOWS.LEAD_WORKFLOW_ID, capturing routing log entries for each workflow record.
• Oracle Workflow runtime objects (item type/item key tables) — referenced through WF_ITEM_TYPE and WF_ITEM_KEY rather than by foreign key.
• PV lead and opportunity APIs — the Partner Management assignment and routing APIs that create and update these rows during lead distribution.

Together these relationships allow the workflow record to be traced forward to routing logs and back to the originating lead and security context.