View: PV_ROUTING_HISTORY_V

Product:	PV - Partner Management
Description:	Routing history view
Implementation/DBA Data:	APPS.PV_ROUTING_HISTORY_V

View Text

SELECT ASSIGNMENT_ID
, ROUTING_EVENT
, EVENT_DATE
, PARTNER_ID
, CATEGORY
, LEAD_ID
, REASON_CODE
, RESOURCE_NAME
, LEAD_WF_ID
, LAST_UPDATE_DATE
, ORG_NAME
, LAST_UPDATE_BY
, NOTIFICATION_CODE
, RESPONSE_CODE
, RESOURCE_ID
, ROUTING_STATUS
, RESPONSE_MEANING
, RESPONSE_DATE
, LATEST_ROUTING_FLAG
, ROUTING_TYPE
, BYPASS_CM_OK_FLAG
FROM ( SELECT DISTINCT PAL.ASSIGNMENT_ID
, PAL.TO_LEAD_STATUS ROUTING_EVENT
, PAL.CREATION_DATE EVENT_DATE
, PVLA.PARTNER_ID
, DECODE(PAL.TO_LEAD_STATUS
, 'ACTIVE'
, NULL
, JTFRE.CATEGORY) CATEGORY
, PVLW.LEAD_ID LEAD_ID
, DECODE(PAL.TO_LEAD_STATUS
, 'ACTIVE'
, NULL
, DECODE(PVPN.RESOURCE_RESPONSE
, 'PT_REJECTED'
, PVLA.REASON_CODE
, 'PT_ABANDONED'
, PVLA.REASON_CODE
, NULL)) REASON_CODE
, NULL RESOURCE_NAME
, PVLW.LEAD_WORKFLOW_ID LEAD_WF_ID
, PVLW.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL ORG_NAME
, PVLW.LAST_UPDATED_BY LAST_UPDATE_BY
, DECODE(PAL.TO_LEAD_STATUS
, 'ACTIVE'
, NULL
, DECODE(PVPN.NOTIFICATION_TYPE
, 'ABANDONED_BY'
, 'PT'
, 'OFFERED_TO'
, 'PT'
, 'MATCHED_TO'
, 'CM'
, 'BEHALF_OF'
, 'CM'
, 'PTCR_FYI'
, 'SR')) NOTIFICATION_CODE
, DECODE(PAL.TO_LEAD_STATUS
, 'ACTIVE'
, NULL
, DECODE( DECODE(JTFRE.CATEGORY
, 'PARTY'
, PVLA.STATUS)
, 'PT_TIMEOUT'
, PVLA.STATUS
, PVPN.RESOURCE_RESPONSE)) RESPONSE_CODE
, DECODE(PAL.TO_LEAD_STATUS
, 'ACTIVE'
, TO_NUMBER(NULL)
, PVPN.RESOURCE_ID) RESOURCE_ID
, DECODE(PAL.TO_LEAD_STATUS
, 'ACTIVE'
, NULL
, PVLW.ROUTING_STATUS) ROUTING_STATUS
, NULL RESPONSE_MEANING
, DECODE(PAL.TO_LEAD_STATUS
, 'ACTIVE'
, TO_DATE(NULL)
, DECODE( DECODE(JTFRE.CATEGORY
, 'PARTY'
, PVLA.STATUS)
, 'PT_TIMEOUT'
, PVLA.LAST_UPDATE_DATE
, PVPN.RESPONSE_DATE)) RESPONSE_DATE
, PVLW.LATEST_ROUTING_FLAG LATEST_ROUTING_FLAG
, '' ROUTING_TYPE
, '' BYPASS_CM_OK_FLAG
FROM PV_LEAD_WORKFLOWS PVLW
, PV_ASSIGNMENT_LOGS PAL
, PV_LEAD_ASSIGNMENTS PVLA
, PV_PARTY_NOTIFICATIONS PVPN
, JTF_RS_RESOURCE_EXTNS JTFRE
WHERE PVLW.WF_ITEM_TYPE = PVLA.WF_ITEM_TYPE
AND PVLW.WF_ITEM_KEY = PVLA.WF_ITEM_KEY
AND PVLA.LEAD_ASSIGNMENT_ID = PAL.LEAD_ASSIGNMENT_ID
AND ( (PAL.TO_LEAD_STATUS = 'OFFERED'
AND PVPN.NOTIFICATION_TYPE IN ('BEHALF_OF'
, 'OFFERED_TO')) OR (PAL.TO_LEAD_STATUS = 'ACTIVE'
AND PVPN.NOTIFICATION_TYPE IN ('BEHALF_OF'
, 'OFFERED_TO')) OR (PAL.TO_LEAD_STATUS = 'ABANDONED'
AND PVPN.NOTIFICATION_TYPE = 'ABANDONED_BY'))
AND PVLA.LEAD_ASSIGNMENT_ID = PVPN.LEAD_ASSIGNMENT_ID
AND PVPN.RESOURCE_ID = JTFRE.RESOURCE_ID UNION ALL SELECT NVL(PAL.ASSIGNMENT_ID
, 0) ASSIGNMENT_ID
, 'MATCHED' ROUTING_EVENT
, PVLW.CREATION_DATE EVENT_DATE
, NVL(PAL.PARTNER_ID
, PVLA.PARTNER_ID) PARTNER_ID
, JTFRE.CATEGORY
, PVLW.LEAD_ID LEAD_ID
, NULL REASON_CODE
, NULL RESOURCE_NAME
, PVLW.LEAD_WORKFLOW_ID LEAD_WF_ID
, PVLW.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL ORG_NAME
, PVLW.LAST_UPDATED_BY LAST_UPDATE_BY
, DECODE(PVPN.NOTIFICATION_TYPE
, 'ABANDONED_BY'
, 'PT'
, 'OFFERED_TO'
, 'PT'
, 'MATCHED_TO'
, 'CM'
, 'BEHALF_OF'
, 'CM'
, 'PTCR_FYI'
, 'SR') NOTIFICATION_CODE
, DECODE( PVPN.NOTIFICATION_TYPE
, 'MATCHED_TO'
, PVPN.RESOURCE_RESPONSE
, NULL) RESPONSE_CODE
, PVPN.RESOURCE_ID RESOURCE_ID
, PVLW.ROUTING_STATUS ROUTING_STATUS
, NULL RESPONSE_MEANING
, DECODE( PVPN.NOTIFICATION_TYPE
, 'MATCHED_TO'
, PVPN.RESPONSE_DATE
, NULL) RESPONSE_DATE
, PVLW.LATEST_ROUTING_FLAG LATEST_ROUTING_FLAG
, PVLW.ROUTING_TYPE
, PVLW.BYPASS_CM_OK_FLAG
FROM PV_LEAD_WORKFLOWS PVLW
, PV_LEAD_ASSIGNMENTS PVLA
, PV_ASSIGNMENT_LOGS PAL
, PV_PARTY_NOTIFICATIONS PVPN
, JTF_RS_RESOURCE_EXTNS JTFRE
WHERE PVLW.WF_ITEM_TYPE = PVLA.WF_ITEM_TYPE
AND PVLW.WF_ITEM_KEY = PVLA.WF_ITEM_KEY
AND PVLA.LEAD_ASSIGNMENT_ID = PAL.LEAD_ASSIGNMENT_ID (+)
AND PAL.STATUS (+) = 'ASSIGNED'
AND PVLA.LEAD_ASSIGNMENT_ID = PVPN.LEAD_ASSIGNMENT_ID
AND PVPN.RESOURCE_ID = JTFRE.RESOURCE_ID
AND PVPN.NOTIFICATION_TYPE IN ('OFFERED_TO'
, 'MATCHED_TO') UNION ALL SELECT PAL.ASSIGNMENT_ID ASSIGNMENT_ID
, PAL.TO_LEAD_STATUS ROUTING_EVENT
, PAL.CREATION_DATE EVENT_DATE
, TO_NUMBER(NULL) PARTNER_ID
, NULL CATEGORY
, PVLW.LEAD_ID LEAD_ID
, NULL REASON_CODE
, NULL RESOURCE_NAME
, PVLW.LEAD_WORKFLOW_ID LEAD_WF_ID
, PVLW.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL ORG_NAME
, PVLW.LAST_UPDATED_BY LAST_UPDATE_BY
, NULL NOTIFICATION_CODE
, NULL RESPONSE_CODE
, TO_NUMBER(NULL) RESOURCE_ID
, PVLW.ROUTING_STATUS ROUTING_STATUS
, NULL RESPONSE_MEANING
, TO_DATE(NULL) RESPONSE_DATE
, NULL LATEST_ROUTING_FLAG
, '' ROUTING_TYPE
, '' BYPASS_CM_OK_FLAG
FROM PV_LEAD_WORKFLOWS PVLW
, PV_ASSIGNMENT_LOGS PAL
WHERE PVLW.LEAD_WORKFLOW_ID = PAL.WORKFLOW_ID
AND PAL.TO_LEAD_STATUS IN ('RECYCLED'
, 'WITHDRAWN') ) ORDER BY LEAD_WF_ID DESC
, ASSIGNMENT_ID DESC
, PARTNER_ID
, CATEGORY
, RESPONSE_DATE DESC

Columns