Product: | CS - Service |
---|---|
Description: | This view is used in Service Request Summary/Detail report. |
Implementation/DBA Data: | APPS.CS_SR_REPORT_V |
SELECT INC.INCIDENT_ID
, INC.INCIDENT_NUMBER
, INC.INVENTORY_ITEM_ID
, KFV.CONCATENATED_SEGMENTS PRODUCT_NAME
, KFV.DESCRIPTION PRODUCT_DESCRIPTION
, INC.INCIDENT_DATE
, INC.CUSTOMER_ID
, PARTY.PARTY_NUMBER CUSTOMER_NUMBER
, PARTY.PARTY_NAME COMPANY_NAME
, INC.CURRENT_SERIAL_NUMBER
, INC.CUSTOMER_PRODUCT_ID
, CP.REFERENCE_NUMBER
, SR_PROB.LOOKUP_CODE PROBLEM_CODE
, SR_PROB.MEANING PROBLEM_CODE_MEANING
, SR_PROB.DESCRIPTION PROBLEM_CODE_DESCRIPTION
, SR_RSLV.LOOKUP_CODE RESOLUTION_CODE
, SR_RSLV.MEANING RESOLUTION_CODE_MEANING
, SR_RSLV.DESCRIPTION RESOLUTION_CODE_DESCRIPTION
, INC.INCIDENT_TYPE_ID
, TYPE.NAME INCIDENT_TYPE
, INC.INCIDENT_SEVERITY_ID
, SEV.NAME SEVERITY
, INC.INCIDENT_STATUS_ID
, STATUS.NAME STATUS_CODE
, INC.INCIDENT_URGENCY_ID
, URGENCY.NAME URGENCY
, OWN.RESOURCE_NAME OWNER
, INC.SUMMARY
, INC.CLOSE_DATE DATE_CLOSED
, NVL(STATUS.CLOSE_FLAG
, 'N') CLOSED_FLAG
, FND.USER_NAME LOGGED_BY_NAME
, INC.RECORD_IS_VALID_FLAG
FROM CS_INCIDENTS_ALL_VL INC
, MTL_SYSTEM_ITEMS_KFV KFV
, CS_INCIDENT_TYPES_TL TYPE
, CS_INCIDENT_SEVERITIES_TL SEV
, CS_INCIDENT_STATUSES_VL STATUS
, CS_INCIDENT_URGENCIES_TL URGENCY
, HZ_PARTIES PARTY
, CS_LOOKUPS SR_PROB
, CS_LOOKUPS SR_RSLV
, CS_CUSTOMER_PRODUCTS CP
, JTF_RS_RESOURCE_EXTNS_VL OWN
, FND_USER FND
WHERE INC.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID (+)
AND INC.ORG_ID = KFV.ORGANIZATION_ID (+)
AND INC.PROBLEM_CODE = SR_PROB.LOOKUP_CODE(+)
AND SR_PROB.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE'
AND INC.RESOLUTION_CODE = SR_RSLV.LOOKUP_CODE(+)
AND SR_RSLV.LOOKUP_TYPE (+) = 'REQUEST_RESOLUTION_CODE'
AND INC.CUSTOMER_PRODUCT_ID = CP.CUSTOMER_PRODUCT_ID (+)
AND INC.CUSTOMER_ID = PARTY.PARTY_ID (+)
AND INC.INCIDENT_TYPE_ID = TYPE.INCIDENT_TYPE_ID
AND TYPE.LANGUAGE = USERENV('LANG')
AND INC.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND STATUS.LANGUAGE = USERENV('LANG')
AND INC.INCIDENT_URGENCY_ID = URGENCY.INCIDENT_URGENCY_ID (+)
AND URGENCY.LANGUAGE = USERENV('LANG')
AND INC.INCIDENT_SEVERITY_ID = SEV.INCIDENT_SEVERITY_ID
AND SEV.LANGUAGE = USERENV('LANG')
AND INC.INCIDENT_OWNER_ID = OWN.RESOURCE_ID(+)
AND INC.CREATED_BY = FND.USER_ID