View: CS_SR_UWQ_TEAM_V

Product:	CS - Service
Description:	This view is used by the UWQ (Universal Work Queue) to query up Service Requests against the user logged in. The view is a snapshot of information like Status, Severity, Escalation.
Implementation/DBA Data:	APPS.CS_SR_UWQ_TEAM_V

View Text

SELECT INC_B.INCIDENT_ID
, INC_B.INCIDENT_NUMBER
, SEV.NAME SERVERITY
, STAT.NAME STATUS
, INC_TL.SUMMARY
, DECODE(HZP.PARTY_TYPE
, 'PERSON'
, HZP.PERSON_LAST_NAME || ' ' ||HZP.PERSON_FIRST_NAME
, 'ORGANIZATION'
, HZP.PARTY_NAME) PERSON_ORG
, INC_B.INCIDENT_OWNER_ID OWNER_ID
, JTFT.TEAM_NAME OWNER
, TYP.NAME SR_TYPE
, LKP1.MEANING ESCALATED
, NULL COVERAGE_NAME
, NULL COVERAGE_LEVEL
, INC_B.INCIDENT_SEVERITY_ID
, INC_B.INCIDENT_STATUS_ID
, INC_B.INCIDENT_TYPE_ID
, INC_B.GROUP_TYPE RESOURCE_TYPE
, INC_B.OWNER_GROUP_ID RESOURCE_ID
, SEV.NAME SEVERITY
, OBJ.OBJECT_FUNCTION IEU_OBJECT_FUNCTION
, OBJ.OBJECT_PARAMETERS IEU_OBJECT_PARAMETERS
, NULL IEU_MEDIA_TYPE_UUID
, 'INCIDENT_ID' IEU_PARAM_PK_COL
, TO_CHAR(INC_B.INCIDENT_ID) IEU_PARAM_PK_VALUE
, INC_B.EXPECTED_RESOLUTION_DATE
, INC_B.OBLIGATION_DATE
, INC_B.CREATION_DATE
, DECODE(CSHZ.CONTACT_TYPE
, 'EMPLOYEE'
, EMP.FULL_NAME
, 'PERSON'
, HZP1.PERSON_LAST_NAME||' '||HZP1.PERSON_FIRST_NAME
, HZP1.PARTY_NAME) CONTACT_NAME
, ST_B.SORT_ORDER
, KFV.CONCATENATED_SEGMENTS PRODUCT_NAME
, LKP.MEANING SR_CREATION_CHANNEL
, INC_B.LAST_UPDATE_DATE MODIFIED_DATE
, USR.USER_NAME MODIFIED_BY
, RES_DT.RESOURCE_NAME OWNER_NAME
, HZP1.EMAIL_ADDRESS
, DECODE(CSHZ.CONTACT_TYPE
, 'EMPLOYEE'
, PER_ADD.ADDRESS_LINE_1 || DECODE(PER_ADD.ADDRESS_LINE_2
, NULL
, NULL
, ';'||PER_ADD.ADDRESS_LINE_2) || DECODE(PER_ADD.ADDRESS_LINE_3
, NULL
, NULL
, ';'||PER_ADD.ADDRESS_LINE_3) || DECODE(PER_ADD.TOWN_OR_CITY
, NULL
, NULL
, ' ' || PER_ADD.TOWN_OR_CITY)
, HZP1.ADDRESS1|| DECODE(HZP1.ADDRESS2
, NULL
, NULL
, ';'||HZP1.ADDRESS2) || DECODE(HZP1.ADDRESS3
, NULL
, NULL
, ';'||HZP1.ADDRESS3) || DECODE(HZP1.ADDRESS4
, NULL
, NULL
, ';'||HZP1.ADDRESS4) || DECODE(HZP1.CITY
, NULL
, NULL
, ' ' || HZP1.CITY) || DECODE(HZP1.STATE
, NULL
, NULL
, ' ' || HZP1.STATE)) CONTACT_ADDRESS
, DECODE(CSHZ.CONTACT_TYPE
, 'EMPLOYEE'
, PER_ADD.COUNTRY
, HZP1.COUNTRY) CONTACT_COUNTRY
, INC_B.PROJECT_NUMBER
, INC_B.CUSTOMER_TICKET_NUMBER
, INC_B.CURRENT_SERIAL_NUMBER
, INC_B.PROBLEM_CODE
, INC_B.CUSTOMER_PO_NUMBER
, INC_B.RESOLUTION_CODE
, CSL.MEANING CONTACT_TYPE
, DECODE(CSHZ.CONTACT_TYPE
, 'EMPLOYEE'
, EMP.EMPLOYEE_NUMBER
, HZP1.PARTY_NUMBER) CONTACT_NUMBER
, CSI.SERIAL_NUMBER
, CSI.EXTERNAL_REFERENCE TAG
, INC_B.INCIDENT_COUNTRY
, FND_U.USER_NAME CREATED_BY
, INC_B.CUST_PREF_LANG_CODE LANGUAGE_CODE
, URG.NAME URGENCY
, OKS.MEANING COVERAGE_TYPE
, OKS.IMPORTANCE_LEVEL
FROM CS_INCIDENTS_ALL_TL INC_TL
, CS_INCIDENTS_B_SEC INC_B
, CS_INCIDENT_STATUSES_B ST_B
, CS_INCIDENT_STATUSES_TL STAT
, CS_INCIDENT_SEVERITIES_TL SEV
, CS_INCIDENT_TYPES_TL TYP
, PER_ALL_PEOPLE_F EMP
, HZ_PARTIES HZP
, HZ_PARTIES HZP1
, CS_HZ_SR_CONTACT_POINTS CSHZ
, JTF_RS_TEAMS_TL JTFT
, JTF_OBJECTS_B OBJ
, MTL_SYSTEM_ITEMS_KFV KFV
, FND_LOOKUP_VALUES LKP
, FND_USER USR
, JTF_RS_RESOURCE_EXTNS_TL RES_DT
, CS_LOOKUPS CSL
, CSI_ITEM_INSTANCES CSI
, FND_USER FND_U
, HR_LOCATIONS PER_ADD
, PER_ASSIGNMENTS_X ASG
, JTF_TASK_REFERENCES_B TSK_REF
, JTF_TASKS_B TSK
, FND_LOOKUP_VALUES LKP1
, CS_INCIDENT_URGENCIES_TL URG
, OKS_COV_TYPES_V OKS
WHERE INC_TL.INCIDENT_ID = INC_B.INCIDENT_ID
AND INC_TL.LANGUAGE = USERENV('LANG')
AND INC_B.GROUP_TYPE='RS_TEAM'
AND INC_B.STATUS_FLAG = 'O'
AND INC_B.INCIDENT_SEVERITY_ID = SEV.INCIDENT_SEVERITY_ID
AND SEV.LANGUAGE = USERENV('LANG')
AND INC_B.INCIDENT_STATUS_ID = ST_B.INCIDENT_STATUS_ID
AND ST_B.INCIDENT_STATUS_ID = STAT.INCIDENT_STATUS_ID
AND STAT.LANGUAGE = USERENV('LANG')
AND INC_B.CUSTOMER_ID = HZP.PARTY_ID(+)
AND INC_B.INCIDENT_TYPE_ID = TYP.INCIDENT_TYPE_ID
AND TYP.LANGUAGE = USERENV('LANG')
AND JTFT.TEAM_ID = INC_B.OWNER_GROUP_ID
AND JTFT.LANGUAGE = USERENV('LANG')
AND OBJ.OBJECT_CODE='SR'
AND CSHZ.INCIDENT_ID(+)=INC_B.INCIDENT_ID
AND CSHZ.PRIMARY_FLAG(+)='Y'
AND HZP1.PARTY_ID(+) = CSHZ.PARTY_ID
AND EMP.PERSON_ID(+) = CSHZ.PARTY_ID
AND SYSDATE BETWEEN EMP.EFFECTIVE_START_DATE(+)
AND EMP.EFFECTIVE_END_DATE(+)
AND CSL.LOOKUP_TYPE(+) = 'CS_SR_CONTACT_TYPE'
AND CSL.LOOKUP_CODE(+) = CSHZ.CONTACT_TYPE
AND KFV.INVENTORY_ITEM_ID(+) = INC_B.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID(+) = INC_B.INV_ORGANIZATION_ID
AND LKP.LOOKUP_TYPE = 'CS_SR_CREATION_CHANNEL'
AND LKP.LOOKUP_CODE = NVL(UPPER(INC_B.SR_CREATION_CHANNEL)
, 'PHONE')
AND LKP.LANGUAGE = USERENV('LANG')
AND LKP.VIEW_APPLICATION_ID = 170
AND LKP.SECURITY_GROUP_ID= FND_GLOBAL.LOOKUP_SECURITY_GROUP('CS_SR_CREATION_CHANNEL'
, 170)
AND USR.USER_ID = INC_B.LAST_UPDATED_BY
AND INC_B.INCIDENT_OWNER_ID = RES_DT.RESOURCE_ID(+)
AND RES_DT.LANGUAGE(+) = USERENV('LANG')
AND RES_DT.CATEGORY(+) = SUBSTR(INC_B.RESOURCE_TYPE
, 4
, LENGTH(INC_B.RESOURCE_TYPE)-3)
AND INC_B.CUSTOMER_PRODUCT_ID = CSI.INSTANCE_ID(+)
AND FND_U.USER_ID = INC_B.CREATED_BY
AND ASG.PERSON_ID(+) = EMP.PERSON_ID
AND ASG.PRIMARY_FLAG(+) = 'Y'
AND ASG.ASSIGNMENT_TYPE(+) = 'E'
AND ASG.EFFECTIVE_END_DATE(+) > TRUNC(SYSDATE)
AND ASG.LOCATION_ID = PER_ADD.LOCATION_ID(+)
AND TSK_REF.OBJECT_ID(+) = INC_B.INCIDENT_ID
AND TSK_REF.OBJECT_TYPE_CODE(+) = 'SR'
AND TSK_REF.REFERENCE_CODE(+) = 'ESC'
AND TSK.TASK_ID(+) = TSK_REF.TASK_ID
AND LKP1.LOOKUP_TYPE(+) = 'JTF_TASK_ESC_LEVEL'
AND LKP1.LOOKUP_CODE(+) = TSK.ESCALATION_LEVEL
AND LKP1.LANGUAGE(+) = USERENV('LANG')
AND LKP1.VIEW_APPLICATION_ID(+) = 0
AND LKP1.SECURITY_GROUP_ID(+) = FND_GLOBAL.LOOKUP_SECURITY_GROUP('JTF_TASK_ESC_LEVEL'
, 170)
AND INC_B.INCIDENT_URGENCY_ID = URG.INCIDENT_URGENCY_ID(+)
AND URG.LANGUAGE(+) = USERENV('LANG')
AND OKS.CODE(+) = INC_B.COVERAGE_TYPE

Columns