Search Results pv_assignment_logs_n1

Overview

PV.PV_ASSIGNMENT_LOGS is a transactional history table in the Oracle E-Business Suite Partner (Channel) Management and Trade Management module, owned by the PV schema. It stores the complete audit history of every lead assignment processed through the lead assignment engine, capturing each transition of a lead as it is routed to a partner, a channel manager, or a workflow-driven assignment path. Because it records successive states of the same lead assignment over time, it functions as the system of record for assignment lifecycle tracking and workflow diagnostics. The table resides in the APPS_TS_TX_DATA tablespace with PCT Free 10, and its indexes are placed in APPS_TS_TX_IDX.

From a Data Vault modeling perspective, the mined relationship structure suggests this object is best classified as a link. It resolves and records associations between a lead (AS_LEADS_ALL), a lead assignment (PV_LEAD_ASSIGNMENTS), a partner (HZ_PARTIES), and a workflow instance (HR_WORKFLOWS), while also carrying descriptive status and workflow attributes that could be modeled as satellite content. The documented primary key is PV_ASSIGNMENT_LOGS_PK on ASSIGNMENT_ID; the unique index PV_ASSIGNMENT_LOGS_U1 (also on ASSIGNMENT_ID, in APPS_TS_TX_IDX) is the corresponding business-key candidate surfaced by the object name the user searched for.

Key Information Stored

The most significant columns in PV_ASSIGNMENT_LOGS are:

• ASSIGNMENT_ID — Surrogate lead assignment identifier; the primary key and the single column of the unique index PV_ASSIGNMENT_LOGS_U1.
• LEAD_ASSIGNMENT_ID — Foreign key to PV_LEAD_ASSIGNMENTS, linking each log row to the parent assignment record.
• LEAD_ID — Foreign key to AS_LEADS_ALL, identifying the lead being assigned.
• PARTNER_ID — Foreign key to HZ_PARTIES, identifying the partner receiving the lead.
• CM_ID — Identifier of the supplier employee acting as channel manager for the assignment.
• ASSIGN_SEQUENCE — Serial order in which the lead is assigned to partners when running in serial mode.
• DURATION — Fixed time window in which the supplier or partner must act on the lead.
• FROM_LEAD_STATUS and TO_LEAD_STATUS — The lead status transitioning from and to, forming the change pair for audit reporting.
• STATUS and STATUS_DATE — The stage of the lead within the current assignment cycle and the date it was set.
• WF_ITEM_TYPE, WF_ITEM_KEY, WORKFLOW_ID — Workflow identifiers that tie the row to the Oracle Workflow engine; WORKFLOW_ID references HR_WORKFLOWS.
• WF_PT_USER and WF_CM_USER — Partner and channel manager user names captured for workflow routing.
• ERROR_TXT — Workflow error messages for failed assignment processing.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS supporting multi-org data security.
• Standard Who columns (LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN) and OBJECT_VERSION_NUMBER for concurrency control.

Common Use Cases and Queries

Typical reporting scenarios include reconstructing the assignment history of a lead, measuring partner response against DURATION windows, and diagnosing workflow failures.

• Lead history: select all rows for a given LEAD_ID ordered by STATUS_DATE to trace status progression.
• Partner performance: aggregate assignments per PARTNER_ID and count rows where TO_LEAD_STATUS indicates acceptance or expiration.
• Workflow troubleshooting: filter rows where ERROR_TXT is not null, joined to HR_WORKFLOWS via WORKFLOW_ID.
• Channel manager oversight: group by CM_ID to review assignments handled by a specific channel manager.

A representative query joins the parent assignment to its history:

SELECT l.ASSIGNMENT_ID, l.LEAD_ID, l.PARTNER_ID, l.FROM_LEAD_STATUS, l.TO_LEAD_STATUS, l.STATUS_DATE FROM PV.PV_ASSIGNMENT_LOGS l WHERE l.LEAD_ASSIGNMENT_ID = :p_assignment_id ORDER BY l.STATUS_DATE;

Nonunique indexes PV_ASSIGNMENT_LOGS_N1, N2, and N3 support lookups on LEAD_ASSIGNMENT_ID, LEAD_ID, and PARTNER_ID respectively, ensuring these common access paths remain performant.

Related Objects

• PV.PV_LEAD_ASSIGNMENTS — joined on LEAD_ASSIGNMENT_ID; the parent assignment header.
• AS.AS_LEADS_ALL — joined on LEAD_ID; source of lead master data.
• HZ.HZ_PARTIES — joined on PARTNER_ID; supplies partner identity and profile information.
• HR.HR_WORKFLOWS — joined on WORKFLOW_ID; workflow instance definition.
• FND.FND_SECURITY_GROUPS — joined on SECURITY_GROUP_ID; enforces data security partitioning.
• Oracle Workflow runtime tables (WF_ITEM_TYPE / WF_ITEM_KEY) — referenced logically for item-level diagnostics.