VIEW: APPS.CS_BRM_3D_SERVICE_REQUEST_V

Source

View Text - Preformatted

SELECT 'HISTORIC' record_status , 'CS_BRM_3D_SERVICE_REQUEST_V' object_type , cia.incident_id object_id , cia.incident_id incident_id , cia.created_by created_by , cia.last_update_date creation_date , cia.last_updated_by last_update_by , cia.last_update_date last_update_date , cia.last_update_login last_update_login , cia.old_incident_status_id incident_status_id , (select cis.name from cs_incident_statuses_tl cis where cia.old_incident_status_id = cis.incident_status_id AND cis.language = USERENV('LANG')) incident_status_name , cia.old_incident_type_id incident_type_id , (select cit.name from cs_incident_types_tl cit where cia.old_incident_type_id = cit.incident_type_id AND cit.language = USERENV('LANG') ) incident_type_name , cia.old_incident_urgency_id incident_urgency_id , (select cut.name from cs_incident_urgencies_tl cut where cia.old_incident_urgency_id = cut.incident_urgency_id AND cut.language = USERENV('LANG') ) incident_urgency_name , cia.old_incident_severity_id incident_severity_id , (select cse.name from cs_incident_severities_tl cse where cia.old_incident_severity_id = cse.incident_severity_id AND cse.language = USERENV('LANG'))incident_severity_name , cia.old_incident_owner_id incident_owner_id , ppl.full_name incident_owner_name , cia.old_incident_date incident_date , cia.old_expected_resolution_date expected_resolution_date , cia.old_obligation_date obligation_date , cia.old_close_date close_date , cia.old_site_id site_id , cib.customer_id customer_id , prt.party_name company_name , act.account_number account_number , cia.old_customer_product_id customer_product_id , VL.concatenated_segments product_name , VL.description product_descripition FROM cs_incidents_b_sec cib , cs_incidents_audit_b cia , jtf_rs_resource_extns rsc , per_people_f ppl , hz_parties prt , hz_cust_accounts act , MTL_SYSTEM_ITEMS_VL VL WHERE cia.incident_id = cib.incident_id AND cia.old_incident_owner_id = rsc.resource_id (+) AND rsc.source_id = ppl.person_id (+) AND sysdate between nvl(ppl.effective_start_date(+),sysdate) AND nvl(ppl.effective_end_date(+),sysdate) AND cib.customer_id = prt.party_id (+) AND cib.account_id = act.cust_account_id (+) AND cib.inventory_item_id = VL.inventory_item_id (+) AND cib.inv_organization_id = VL.organization_id (+) UNION ALL SELECT 'PRESENT' record_status , 'CS_BRM_3D_SERVICE_REQUEST_V' object_type , cia.incident_id object_id , cia.incident_id incident_id , cia.created_by created_by , cia.creation_date creation_date , cia.last_updated_by last_update_by , cia.last_update_date last_update_date , cia.last_update_login last_update_login , cia.incident_status_id incident_status_id , (select cis.name from cs_incident_statuses_tl cis where cia.incident_status_id = cis.incident_status_id AND cis.language = USERENV('LANG') ) incident_status_name , cia.incident_type_id incident_type_id , (select cit.name from cs_incident_types_tl cit where cia.incident_type_id = cit.incident_type_id AND cit.language = USERENV('LANG') ) incident_type_name , cia.incident_urgency_id incident_urgency_id , (select cut.name from cs_incident_urgencies_tl cut where cia.incident_urgency_id = cut.incident_urgency_id AND cut.language = USERENV('LANG') ) incident_urgency_name , cia.incident_severity_id incident_severity_id , (select cse.name from cs_incident_severities_tl cse where cia.incident_severity_id = cse.incident_severity_id AND cse.language = USERENV('LANG')) incident_severity_name , cia.incident_owner_id incident_owner_id , ppl.full_name incident_owner_name , cia.incident_date incident_date , cia.expected_resolution_date expected_resolution_date , cia.obligation_date obligation_date , cia.close_date close_date , cia.site_id site_id , cia.customer_id customer_id , prt.party_name company_name , act.account_number account_number , cia.customer_product_id customer_product_id , VL.concatenated_segments product_name , VL.description product_descripition FROM cs_incidents_all_b cia , jtf_rs_resource_extns rsc , per_people_f ppl , hz_parties prt , hz_cust_accounts act , MTL_SYSTEM_ITEMS_VL VL WHERE cia.incident_owner_id = rsc.resource_id (+) AND rsc.source_id = ppl.person_id (+) AND sysdate between nvl(ppl.effective_start_date(+),sysdate) AND nvl(ppl.effective_end_date(+),sysdate) AND cia.customer_id = prt.party_id (+) AND cia.account_id = act.cust_account_id (+) AND cia.inventory_item_id = VL.inventory_item_id (+) AND cia.inv_organization_id = VL.organization_id (+)

View Text - HTML Formatted

SELECT 'HISTORIC' RECORD_STATUS
, 'CS_BRM_3D_SERVICE_REQUEST_V' OBJECT_TYPE
, CIA.INCIDENT_ID OBJECT_ID
, CIA.INCIDENT_ID INCIDENT_ID
, CIA.CREATED_BY CREATED_BY
, CIA.LAST_UPDATE_DATE CREATION_DATE
, CIA.LAST_UPDATED_BY LAST_UPDATE_BY
, CIA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIA.OLD_INCIDENT_STATUS_ID INCIDENT_STATUS_ID
, (SELECT CIS.NAME
FROM CS_INCIDENT_STATUSES_TL CIS
WHERE CIA.OLD_INCIDENT_STATUS_ID = CIS.INCIDENT_STATUS_ID
AND CIS.LANGUAGE = USERENV('LANG')) INCIDENT_STATUS_NAME
, CIA.OLD_INCIDENT_TYPE_ID INCIDENT_TYPE_ID
, (SELECT CIT.NAME
FROM CS_INCIDENT_TYPES_TL CIT
WHERE CIA.OLD_INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
AND CIT.LANGUAGE = USERENV('LANG') ) INCIDENT_TYPE_NAME
, CIA.OLD_INCIDENT_URGENCY_ID INCIDENT_URGENCY_ID
, (SELECT CUT.NAME
FROM CS_INCIDENT_URGENCIES_TL CUT
WHERE CIA.OLD_INCIDENT_URGENCY_ID = CUT.INCIDENT_URGENCY_ID
AND CUT.LANGUAGE = USERENV('LANG') ) INCIDENT_URGENCY_NAME
, CIA.OLD_INCIDENT_SEVERITY_ID INCIDENT_SEVERITY_ID
, (SELECT CSE.NAME
FROM CS_INCIDENT_SEVERITIES_TL CSE
WHERE CIA.OLD_INCIDENT_SEVERITY_ID = CSE.INCIDENT_SEVERITY_ID
AND CSE.LANGUAGE = USERENV('LANG'))INCIDENT_SEVERITY_NAME
, CIA.OLD_INCIDENT_OWNER_ID INCIDENT_OWNER_ID
, PPL.FULL_NAME INCIDENT_OWNER_NAME
, CIA.OLD_INCIDENT_DATE INCIDENT_DATE
, CIA.OLD_EXPECTED_RESOLUTION_DATE EXPECTED_RESOLUTION_DATE
, CIA.OLD_OBLIGATION_DATE OBLIGATION_DATE
, CIA.OLD_CLOSE_DATE CLOSE_DATE
, CIA.OLD_SITE_ID SITE_ID
, CIB.CUSTOMER_ID CUSTOMER_ID
, PRT.PARTY_NAME COMPANY_NAME
, ACT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, CIA.OLD_CUSTOMER_PRODUCT_ID CUSTOMER_PRODUCT_ID
, VL.CONCATENATED_SEGMENTS PRODUCT_NAME
, VL.DESCRIPTION PRODUCT_DESCRIPITION
FROM CS_INCIDENTS_B_SEC CIB
, CS_INCIDENTS_AUDIT_B CIA
, JTF_RS_RESOURCE_EXTNS RSC
, PER_PEOPLE_F PPL
, HZ_PARTIES PRT
, HZ_CUST_ACCOUNTS ACT
, MTL_SYSTEM_ITEMS_VL VL
WHERE CIA.INCIDENT_ID = CIB.INCIDENT_ID
AND CIA.OLD_INCIDENT_OWNER_ID = RSC.RESOURCE_ID (+)
AND RSC.SOURCE_ID = PPL.PERSON_ID (+)
AND SYSDATE BETWEEN NVL(PPL.EFFECTIVE_START_DATE(+)
, SYSDATE)
AND NVL(PPL.EFFECTIVE_END_DATE(+)
, SYSDATE)
AND CIB.CUSTOMER_ID = PRT.PARTY_ID (+)
AND CIB.ACCOUNT_ID = ACT.CUST_ACCOUNT_ID (+)
AND CIB.INVENTORY_ITEM_ID = VL.INVENTORY_ITEM_ID (+)
AND CIB.INV_ORGANIZATION_ID = VL.ORGANIZATION_ID (+) UNION ALL SELECT 'PRESENT' RECORD_STATUS
, 'CS_BRM_3D_SERVICE_REQUEST_V' OBJECT_TYPE
, CIA.INCIDENT_ID OBJECT_ID
, CIA.INCIDENT_ID INCIDENT_ID
, CIA.CREATED_BY CREATED_BY
, CIA.CREATION_DATE CREATION_DATE
, CIA.LAST_UPDATED_BY LAST_UPDATE_BY
, CIA.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CIA.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CIA.INCIDENT_STATUS_ID INCIDENT_STATUS_ID
, (SELECT CIS.NAME
FROM CS_INCIDENT_STATUSES_TL CIS
WHERE CIA.INCIDENT_STATUS_ID = CIS.INCIDENT_STATUS_ID
AND CIS.LANGUAGE = USERENV('LANG') ) INCIDENT_STATUS_NAME
, CIA.INCIDENT_TYPE_ID INCIDENT_TYPE_ID
, (SELECT CIT.NAME
FROM CS_INCIDENT_TYPES_TL CIT
WHERE CIA.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID
AND CIT.LANGUAGE = USERENV('LANG') ) INCIDENT_TYPE_NAME
, CIA.INCIDENT_URGENCY_ID INCIDENT_URGENCY_ID
, (SELECT CUT.NAME
FROM CS_INCIDENT_URGENCIES_TL CUT
WHERE CIA.INCIDENT_URGENCY_ID = CUT.INCIDENT_URGENCY_ID
AND CUT.LANGUAGE = USERENV('LANG') ) INCIDENT_URGENCY_NAME
, CIA.INCIDENT_SEVERITY_ID INCIDENT_SEVERITY_ID
, (SELECT CSE.NAME
FROM CS_INCIDENT_SEVERITIES_TL CSE
WHERE CIA.INCIDENT_SEVERITY_ID = CSE.INCIDENT_SEVERITY_ID
AND CSE.LANGUAGE = USERENV('LANG')) INCIDENT_SEVERITY_NAME
, CIA.INCIDENT_OWNER_ID INCIDENT_OWNER_ID
, PPL.FULL_NAME INCIDENT_OWNER_NAME
, CIA.INCIDENT_DATE INCIDENT_DATE
, CIA.EXPECTED_RESOLUTION_DATE EXPECTED_RESOLUTION_DATE
, CIA.OBLIGATION_DATE OBLIGATION_DATE
, CIA.CLOSE_DATE CLOSE_DATE
, CIA.SITE_ID SITE_ID
, CIA.CUSTOMER_ID CUSTOMER_ID
, PRT.PARTY_NAME COMPANY_NAME
, ACT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, CIA.CUSTOMER_PRODUCT_ID CUSTOMER_PRODUCT_ID
, VL.CONCATENATED_SEGMENTS PRODUCT_NAME
, VL.DESCRIPTION PRODUCT_DESCRIPITION
FROM CS_INCIDENTS_ALL_B CIA
, JTF_RS_RESOURCE_EXTNS RSC
, PER_PEOPLE_F PPL
, HZ_PARTIES PRT
, HZ_CUST_ACCOUNTS ACT
, MTL_SYSTEM_ITEMS_VL VL
WHERE CIA.INCIDENT_OWNER_ID = RSC.RESOURCE_ID (+)
AND RSC.SOURCE_ID = PPL.PERSON_ID (+)
AND SYSDATE BETWEEN NVL(PPL.EFFECTIVE_START_DATE(+)
, SYSDATE)
AND NVL(PPL.EFFECTIVE_END_DATE(+)
, SYSDATE)
AND CIA.CUSTOMER_ID = PRT.PARTY_ID (+)
AND CIA.ACCOUNT_ID = ACT.CUST_ACCOUNT_ID (+)
AND CIA.INVENTORY_ITEM_ID = VL.INVENTORY_ITEM_ID (+)
AND CIA.INV_ORGANIZATION_ID = VL.ORGANIZATION_ID (+)