Search Results pv_assignment_logs




Overview
Explore in AI Chat

Overview

PV_ASSIGNMENT_LOGS is a transaction and audit table in the Partner Management (PV) module of Oracle E-Business Suite, owned by the PV schema. It records the assignment history of leads to partners, capturing each assignment event, its sequencing, workflow context, and the resulting change in lead status. The table functions as the operational log that accompanies the PV_LEAD_ASSIGNMENTS entity, providing a chronological trail of how leads are distributed and reassigned across the partner channel.

Based on the mined foreign-key structure, the ETRM metadata classifies PV_ASSIGNMENT_LOGS heuristically as a link table in a Data Vault modeling sense. This is consistent with its role: it joins a lead, a partner, and a lead-assignment record while carrying its own descriptive attributes (durations, status transitions, workflow identifiers, and error text). Analysts building a Data Vault or dimensional model should treat PV_ASSIGNMENT_LOGS as a link saturating the relationships among AS_LEADS_ALL, HZ_PARTIES, and PV_LEAD_ASSIGNMENTS, with the event-level columns suited to a satellite.

Key Information Stored

The documented physical schema contains 26 columns. The most significant are described below.

Common Use Cases and Queries

The table supports assignment auditing, partner performance reporting, and troubleshooting of workflow-driven lead routing. A typical join reconstructs a lead's assignment history:

SELECT l.lead_id, l.assignment_id, l.assigned_to, a.assignment_id, a.from_lead_status, a.to_lead_status, a.status, a.status_date
FROM pv_lead_assignments l, pv_assignment_logs a
WHERE l.assignment_id = a.lead_assignment_id
AND l.lead_id = :p_lead_id
ORDER BY a.assign_sequence;

Partner-level throughput and error analysis join to HZ_PARTIES and filter on ERROR_TXT or status:

SELECT p.party_name, COUNT(*) assignments,
AVG(a.duration) avg_duration
FROM pv_assignment_logs a, hz_parties p
WHERE a.partner_id = p.party_id
AND a.status_date BETWEEN :from_date AND :to_date
GROUP BY p.party_name;

Because SECURITY_GROUP_ID is present, all reporting queries in a multi-org environment should include the appropriate security-group predicate to respect data access rules.

Related Objects

  • PV_LEAD_ASSIGNMENTS — parent assignment entity; joined on PV_ASSIGNMENT_LOGS.LEAD_ASSIGNMENT_ID = PV_LEAD_ASSIGNMENTS.ASSIGNMENT_ID.
  • AS_LEADS_ALL — the lead master; joined on PV_ASSIGNMENT_LOGS.LEAD_ID = AS_LEADS_ALL.LEAD_ID.
  • HZ_PARTIES — the partner registry; joined on PV_ASSIGNMENT_LOGS.PARTNER_ID = HZ_PARTIES.PARTY_ID.
  • HR_WORKFLOWS — workflow context; joined on PV_ASSIGNMENT_LOGS.WORKFLOW_ID = HR_WORKFLOWS.WORKFLOW_ID.
  • FND_SECURITY_GROUPS — security filtering; joined on PV_ASSIGNMENT_LOGS.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID.

These relationships make PV_ASSIGNMENT_LOGS the central linkage for lead-to-partner assignment analysis within the Partner Management module.