View: CS_BRM_3D_SERVICE_REQUEST_V

Product:	CS - Service
Description:	3D Service Request View used by the Business Rule Monitor.
Implementation/DBA Data:	APPS.CS_BRM_3D_SERVICE_REQUEST_V

View Text

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
, CIS.NAME INCIDENT_STATUS_NAME
, CIA.OLD_INCIDENT_TYPE_ID INCIDENT_TYPE_ID
, CIT.NAME INCIDENT_TYPE_NAME
, CIA.OLD_INCIDENT_URGENCY_ID INCIDENT_URGENCY_ID
, CUT.NAME INCIDENT_URGENCY_NAME
, CIA.OLD_INCIDENT_SEVERITY_ID INCIDENT_SEVERITY_ID
, CSE.NAME 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
, CS_INCIDENT_STATUSES_TL CIS
, CS_INCIDENT_TYPES_TL CIT
, CS_INCIDENT_URGENCIES_TL CUT
, CS_INCIDENT_SEVERITIES_TL CSE
, 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_STATUS_ID = CIS.INCIDENT_STATUS_ID (+)
AND CIS.LANGUAGE(+) = USERENV('LANG')
AND CIA.OLD_INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID (+)
AND CIT.LANGUAGE(+) = USERENV('LANG')
AND CIA.OLD_INCIDENT_URGENCY_ID = CUT.INCIDENT_URGENCY_ID (+)
AND CUT.LANGUAGE(+) = USERENV('LANG') AND CIA.OLD_INCIDENT_SEVERITY_ID = CSE.INCIDENT_SEVERITY_ID (+)
AND CSE.LANGUAGE(+) = USERENV('LANG') AND CIA.OLD_INCIDENT_OWNER_ID = RSC.RESOURCE_ID (+) AND RSC.SOURCE_ID = PPL.PERSON_ID (+)
AND 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
, CIS.NAME INCIDENT_STATUS_NAME
, CIA.INCIDENT_TYPE_ID INCIDENT_TYPE_ID
, CIT.NAME INCIDENT_TYPE_NAME
, CIA.INCIDENT_URGENCY_ID INCIDENT_URGENCY_ID
, CUT.NAME INCIDENT_URGENCY_NAME
, CIA.INCIDENT_SEVERITY_ID INCIDENT_SEVERITY_ID
, CSE.NAME 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
, CS_INCIDENT_STATUSES_TL CIS
, CS_INCIDENT_TYPES_TL CIT
, CS_INCIDENT_URGENCIES_TL CUT
, CS_INCIDENT_SEVERITIES_TL CSE
, JTF_RS_RESOURCE_EXTNS RSC
, PER_PEOPLE_F PPL
, HZ_PARTIES PRT
, HZ_CUST_ACCOUNTS ACT
, MTL_SYSTEM_ITEMS_VL VL
WHERE CIA.INCIDENT_STATUS_ID = CIS.INCIDENT_STATUS_ID (+)
AND CIS.LANGUAGE = USERENV('LANG')
AND CIA.INCIDENT_TYPE_ID = CIT.INCIDENT_TYPE_ID (+)
AND CIT.LANGUAGE = USERENV('LANG')
AND CIA.INCIDENT_URGENCY_ID = CUT.INCIDENT_URGENCY_ID (+)
AND CUT.LANGUAGE (+) = USERENV('LANG') AND CIA.INCIDENT_SEVERITY_ID = CSE.INCIDENT_SEVERITY_ID (+)
AND CSE.LANGUAGE = USERENV('LANG') AND CIA.INCIDENT_OWNER_ID = RSC.RESOURCE_ID (+) AND RSC.SOURCE_ID = PPL.PERSON_ID (+)
AND 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 (+)

Columns