Search Results notification_code

Overview

PV_ROUTING_HISTORY_V is a reporting view owned by the APPS schema within the Partner Management (PV) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented description is simply "Routing history view," and its purpose is to expose a consolidated, chronological record of how leads and opportunities were routed to partners and resources during the channel management lifecycle. The view answers a recurring operational question: who received a given lead, when, under what routing event, and with what outcome.

Because it is a view rather than a table, it carries no storage of its own. Instead it joins the lead workflow engine, assignment logs, lead assignment records, and party notification records to reconstruct routing events, including status transitions such as assignment to a partner, timeout, rejection, abandonment, and final response. The ROUTING_STATUS column, which the user searched for, is one of the most analytically significant outputs because it reflects the processing state of the workflow for each routing event, enabling filtering of active versus closed or stalled routings.

The view is heavily used in reporting layers, extracts, and custom dashboards where Channel Revenue Management or Partner Management teams need an audit trail of lead distribution. It is also a common integration source when downstream systems must reconcile routing outcomes against partner responses.

Underlying Base Objects

The view is defined over five synonym-backed base objects in the APPS schema:

• PV_LEAD_WORKFLOWS — the workflow driver for lead routing; supplies LEAD_ID, LEAD_WORKFLOW_ID, ROUTING_STATUS, and LATEST_ROUTING_FLAG.
• PV_LEAD_ASSIGNMENTS — stores assignment-level detail such as PARTNER_ID, REASON_CODE, STATUS, and WF_ITEM_TYPE/WF_ITEM_KEY linkage.
• PV_ASSIGNMENT_LOGS — records each routing transition via TO_LEAD_STATUS (surfaced as ROUTING_EVENT) and CREATION_DATE (surfaced as EVENT_DATE).
• PV_PARTY_NOTIFICATIONS — captures notification type, resource response, response date, and responding resource.
• JTF_RS_RESOURCE_EXTNS — resource master data used to derive CATEGORY (for example PARTY) and to correlate responder identity.

The joins are keyed on WF_ITEM_TYPE and WF_ITEM_KEY between PV_LEAD_WORKFLOWS and PV_LEAD_ASSIGNMENTS, with the remaining tables linked by assignment, party, and notification identifiers. The outermost SELECT applies DISTINCT and, notably, DECODE logic that nulls out category, reason, notification, response, resource, and routing status fields when ROUTING_EVENT equals 'ACTIVE' — meaning active routings intentionally expose only the base event data.

Key Columns

• ROUTING_STATUS — the workflow routing state; populated from PV_LEAD_WORKFLOWS unless the event is 'ACTIVE'.
• ROUTING_EVENT — the TO_LEAD_STATUS transition that generated the history row.
• EVENT_DATE — timestamp of the routing event from PV_ASSIGNMENT_LOGS.
• PARTNER_ID / LEAD_ID / LEAD_WF_ID / ASSIGNMENT_ID — the primary identifiers linking the history back to partners, leads, workflows, and assignments.
• RESPONSE_CODE and RESPONSE_DATE — the partner or resource response and when it occurred; PT_TIMEOUT responses fall back to assignment status.
• NOTIFICATION_CODE — derived notification category (PT, CM, or SR) based on notification type.
• LATEST_ROUTING_FLAG — identifies the most recent routing for the workflow.
• REASON_CODE — populated for rejected or abandoned partner responses.
• ROUTING_TYPE and BYPASS_CM_OK_FLAG — returned as empty strings in the documented view text.

Common Use Cases and Queries

Typical reporting includes routing audit trails, partner response turnaround analysis, and identification of leads stuck in particular routing states. A representative query filtering on routing status is:

• SELECT LEAD_ID, PARTNER_ID, ROUTING_EVENT, ROUTING_STATUS, RESPONSE_CODE, EVENT_DATE FROM APPS.PV_ROUTING_HISTORY_V WHERE ROUTING_STATUS = '&status';
• SELECT PARTNER_ID, COUNT(*) routed FROM APPS.PV_ROUTING_HISTORY_V WHERE LATEST_ROUTING_FLAG = 'Y' GROUP BY PARTNER_ID;
• SELECT LEAD_ID, ROUTING_EVENT, RESPONSE_CODE, RESPONSE_DATE FROM APPS.PV_ROUTING_HISTORY_V WHERE RESPONSE_CODE = 'PT_REJECTED';

Because the view applies DISTINCT and multi-table joins, performance-sensitive extracts benefit from filtering on LEAD_ID, PARTNER_ID, or date ranges, and from confirming that bind variables match column data types.