Search Results pv_oppty_routing_logs




Overview
Explore in AI Chat

Overview

The PV_OPPTY_ROUTING_LOGS table is a transactional audit table within the Partner Management (PV) module of Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It records the routing events that occur as leads progress through partner opportunity assignment workflows. When a lead is evaluated and routed to a partner, channel manager, or vendor user, an entry is written here capturing the routing decision, the responsible workflow, and the user's response to the routing action. This table is primarily a diagnostic and historical record of routing behavior, used when troubleshooting why a lead was assigned to a particular partner or why a routing step was bypassed.

From a Data Vault modeling perspective, the metadata classifies this object as standalone, meaning it is not structurally dependent on another entity for its grain. This classification suggests the table behaves most like a satellite attached to lead and assignment hubs: it carries descriptive, time-stamped attributes (event, response, reason code) about routing activity rather than acting as a pure hub or link in its own right. It should be treated as an event-history satellite keyed to the lead and its workflow.

Key Information Stored

The table contains 15 documented columns. The surrogate primary key is OPPTY_ROUTING_LOG_ID, a system-generated identifier for each routing event. The following columns carry the substantive business content:

No unique business-key index is documented; uniqueness is enforced through the surrogate key, with LEAD_ID plus EVENT_DATE plus EVENT serving as a practical composite candidate for identifying a given routing action.

Common Use Cases and Queries

Administrators and support analysts query this table to reconstruct the lifecycle of a lead's routing. A typical pattern retrieves the routing trail for a specific lead:

  • SELECT OPPTY_ROUTING_LOG_ID, EVENT, ROUTING_TYPE, EVENT_DATE, USER_RESPONSE, REASON_CODE FROM PV_OPPTY_ROUTING_LOGS WHERE LEAD_ID = :lead_id ORDER BY EVENT_DATE;
  • Filtering on LATEST_ROUTING_FLAG = 'Y' identifies the current routing state of a lead.
  • Joining to PV_LEAD_ASSIGNMENTS on LEAD_ASSIGNMENT_ID reconciles routing events with the resulting assignment.
  • Aggregating by REASON_CODE or USER_RESPONSE across a date range (EVENT_DATE) surfaces rejection and bypass trends, useful for tuning routing rules.

Related Objects

The documented foreign keys tie this log to three principal reference tables, and these form the most significant relationships:

  • AS_LEADS_ALL — joined on LEAD_ID; the master lead record.
  • PV_LEAD_WORKFLOWS — joined on LEAD_WORKFLOW_ID; the workflow definition driving routing.
  • PV_LEAD_ASSIGNMENTS — joined on LEAD_ASSIGNMENT_ID; the assignment created by the routing.

PV_OPPTY_ROUTING_LOGS should also be considered alongside the partner and vendor user records referenced by USER_TYPE, VENDOR_USER_ID, and VENDOR_BUSINESS_UNIT_ID, since these complete the picture of who received and responded to a routed lead.