Search Results lead_assignment_id

Overview

PV.PV_LEAD_ASSIGNMENTS is a transactional table within the Oracle E-Business Suite Partner Management (PV) module. It records the association between a lead and a partner (or partner resource), capturing the routing and assignment of leads through Oracle Workflow. In release 12.1.1 and 12.2.2, the table serves as the persistence layer for lead routing decisions, tracking the status of each assignment as it progresses from initial routing through partner acceptance or rejection.

Under a heuristic Data Vault classification derived from its foreign-key structure, the table leans toward a satellite: it holds a large number of descriptive and status attributes surrounding a small set of business keys. This classification should be treated as a modeling suggestion rather than a documented design intent. The physical schema in ETRM 12.2.2 defines the table as owned by the PV schema with 25 columns.

Key Information Stored

The surrogate primary key is LEAD_ASSIGNMENT_ID, backed by the PV_LEAD_ASSIGNMENTS_PK constraint and a unique index PV_LEAD_ASSIGNMENTS_U1. Two business-key candidates are documented: LEAD_ASSIGNMENT_ID and the composite unique index PV_LEAD_ASSIGNMENTS_U2 over LEAD_ID, PARTNER_ID, WF_ITEM_KEY, and WF_ITEM_TYPE.

The most significant columns include:

• LEAD_ID — foreign key to AS_LEADS_ALL, identifying the lead being assigned.
• PARTNER_ID — foreign key to HZ_PARTIES, identifying the receiving partner.
• CM_ID — foreign key to JTF_RS_RESOURCE_EXTNS, referencing the partner resource or channel manager.
• RELATED_PARTY_ID — foreign key to HZ_PARTIES, pointing to a secondary party associated with the assignment.
• ASSIGN_SEQUENCE — ordinal controlling the order in which multiple partners are approached.
• STATUS and STATUS_DATE — current state of the assignment and when it was set.
• DURATION — elapsed time window allotted for the assignment.
• WF_ITEM_TYPE and WF_ITEM_KEY — Oracle Workflow item identifiers linking the assignment to its workflow process.
• WF_PT_USER and WF_CM_USER — workflow users representing the partner and channel manager roles.
• REASON_CODE and ERROR_TXT — rationale for the assignment and any error captured during processing.
• SOURCE_TYPE — classification of the originating lead source.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-org data isolation.
• Standard WHO columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

Typical reporting scenarios include determining which partner currently holds a lead, auditing routing history, and measuring assignment aging or acceptance rates.

• Active assignments by partner: join PV_LEAD_ASSIGNMENTS to HZ_PARTIES on PARTNER_ID, filtering on STATUS.
• Lead assignment history: join to AS_LEADS_ALL on LEAD_ID ordered by ASSIGN_SEQUENCE.
• Workflow failure analysis: filter rows where ERROR_TXT IS NOT NULL and correlate WF_ITEM_KEY with workflow runtime tables.
• Aging report: compute SYSDATE - STATUS_DATE against DURATION to identify stalled assignments.

A representative query pattern:

• SELECT la.LEAD_ASSIGNMENT_ID, la.LEAD_ID, la.STATUS, p.PARTY_NAME FROM PV_LEAD_ASSIGNMENTS la, HZ_PARTIES p WHERE la.PARTNER_ID = p.PARTY_ID AND la.STATUS = 'ACTIVE';

Related Objects

The table participates in several referential relationships central to lead routing:

• HZ_PARTIES — referenced via PARTNER_ID and RELATED_PARTY_ID.
• AS_LEADS_ALL — referenced via LEAD_ID; the source lead record.
• JTF_RS_RESOURCE_EXTNS — referenced via CM_ID for the assigned resource.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID.
• PV_ASSIGNMENT_LOGS — child table referencing LEAD_ASSIGNMENT_ID; stores audit trail entries.
• PV_PARTY_NOTIFICATIONS — child table referencing LEAD_ASSIGNMENT_ID; tracks notifications sent to parties.
• PV_OPPTY_ROUTING_LOGS — child table referencing LEAD_ASSIGNMENT_ID; captures opportunity routing events.

These relationships make PV_LEAD_ASSIGNMENTS the central junction for lead distribution analytics and workflow diagnostics in the Partner Management module.