Search Results pv_lead_assignments




Overview
Explore in AI Chat

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:

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.