Search Results routing_event

Overview

APPS.PV_ROUTING_HISTORY_V is a reporting view in the Oracle EBS Partner/Distribution (PV) module that consolidates the routing lifecycle of leads and opportunities from assignment through partner notification and response. It presents a chronological audit trail of routing events, exposing how a lead was assigned, which resource or partner received it, what response was recorded, and whether the routing attempt remains the latest active one. In Oracle EBS 12.1.1 and 12.2.2 the view is defined in the APPS schema and is commonly consumed by concurrent programs, OBIEE/XML Publisher reports, and inbound/outbound integrations that track channel pipeline activity.

The name and structure make it particularly useful for troubleshooting routing anomalies—such as a lead that was offered but never answered. This is directly relevant to the search term pt_timeout: the view explicitly surfaces partner-timeout conditions by mapping PVLA.STATUS = 'PT_TIMEOUT' into the RESPONSE_CODE and RESPONSE_DATE columns.

Underlying Base Objects

The view is defined over five documented base objects (referenced as synonyms in APPS):

• PV_LEAD_WORKFLOWS — the driving lead/workflow record, supplying LEAD_WORKFLOW_ID, ROUTING_STATUS, LATEST_ROUTING_FLAG and LAST_UPDATE_DATE.
• PV_LEAD_ASSIGNMENTS — the assignment detail, joined by WF_ITEM_TYPE and WF_ITEM_KEY, and by LEAD_ASSIGNMENT_ID to the assignment log.
• PV_ASSIGNMENT_LOGS — the event history source, providing ASSIGNMENT_ID, TO_LEAD_STATUS (the ROUTING_EVENT value) and CREATION_DATE (EVENT_DATE).
• PV_PARTY_NOTIFICATIONS — partner notifications, contributing RESOURCE_ID, RESOURCE_RESPONSE, RESPONSE_DATE, NOTIFICATION_TYPE and REASON_CODE.
• JTF_RS_RESOURCE_EXTNS — the resource extension table, supplying CATEGORY (for example 'PARTY') used to distinguish party-level responses from resource-level responses.

The joins are on WF_ITEM_TYPE/WF_ITEM_KEY between workflows and assignments, and on LEAD_ASSIGNMENT_ID between assignments and the assignment log. The view applies a DISTINCT to the inner result set, collapsing duplicate routing rows.

Key Columns

• ASSIGNMENT_ID / LEAD_ID / LEAD_WF_ID — identity of the assignment, lead and workflow instance.
• ROUTING_EVENT / EVENT_DATE — the routing status transition (from TO_LEAD_STATUS) and when it occurred.
• PARTNER_ID / RESOURCE_ID — the partner and the specific resource targeted; RESOURCE_ID is nulled for ACTIVE events.
• CATEGORY — resource category, used to decide whether the response is party-driven.
• RESPONSE_CODE / RESPONSE_DATE — the partner's answer and its timestamp. For party categories this can resolve to PT_TIMEOUT, the timeout signal referenced in user searches.
• REASON_CODE — populated for PT_REJECTED and PT_ABANDONED responses.
• NOTIFICATION_CODE — derived from NOTIFICATION_TYPE (PT for offered/abandoned, CM for matched, SR for FYI notifications).
• ROUTING_STATUS / LATEST_ROUTING_FLAG — current status and a marker identifying the newest routing record per workflow.
• LAST_UPDATE_DATE / LAST_UPDATE_BY — auditing columns inherited from PV_LEAD_WORKFLOWS.

Common Use Cases and Queries

A frequent requirement is identifying leads where partners failed to respond before the timeout window elapsed:

SELECT lead_id, partner_id, response_code, response_date
FROM   apps.pv_routing_history_v
WHERE  response_code = 'PT_TIMEOUT'
  AND  routing_event <> 'ACTIVE';

To retrieve only the active routing record for each lead:

SELECT lead_id, routing_event, routing_status, event_date
FROM   apps.pv_routing_history_v
WHERE  latest_routing_flag = 'Y';

To audit a partner's activity across a date range:

SELECT partner_id, routing_event, response_code, event_date, response_date
FROM   apps.pv_routing_history_v
WHERE  partner_id = :p_partner_id
  AND  event_date BETWEEN :p_from AND :p_to
ORDER  BY event_date;

Because the view normalises both party-level and resource-level responses into common columns, it is well suited to pipeline dashboards, SLA measurement of partner response times, and root-cause analysis of stalled or timed-out routing events.