VIEW: APPS.PV_ROUTING_HISTORY_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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