View: CS_INCIDENTS_V

Product:	CS - Service
Description:	Service requests and their attributes. This view is used by the main service request form.
Implementation/DBA Data:	APPS.CS_INCIDENTS_V

View Text

SELECT INC.ROWID ROW_ID
, INC.INCIDENT_ID
, INC.ORG_ID
, INC.LAST_UPDATE_DATE
, INC.LAST_UPDATED_BY
, INC.CREATION_DATE
, INC.CREATED_BY
, INC.LAST_UPDATE_LOGIN
, INC.INCIDENT_NUMBER
, INC.INCIDENT_NUMBER INCIDENT_NUMBER_N
, INC.INCIDENT_STATUS_ID
, INC.INCIDENT_TYPE_ID
, INC.INCIDENT_URGENCY_ID
, INC.INCIDENT_SEVERITY_ID
, TL.SUMMARY
, INC.INCIDENT_OWNER_ID
, INC.RESOURCE_TYPE
, INC.RESOURCE_SUBTYPE_ID
, INC.INVENTORY_ITEM_ID
, INC.SHIP_TO_SITE_USE_ID
, INC.SHIP_TO_CONTACT_ID
, INC.BILL_TO_SITE_USE_ID
, INC.BILL_TO_CONTACT_ID
, INC.INSTALL_SITE_USE_ID
, INC.PROBLEM_CODE
, INC.EXPECTED_RESOLUTION_DATE
, INC.ACTUAL_RESOLUTION_DATE
, INC.INCIDENT_DATE
, INC.CUSTOMER_PRODUCT_ID
, INC.INCIDENT_ATTRIBUTE_1
, INC.INCIDENT_ATTRIBUTE_2
, INC.INCIDENT_ATTRIBUTE_3
, INC.INCIDENT_ATTRIBUTE_4
, INC.INCIDENT_ATTRIBUTE_5
, INC.INCIDENT_ATTRIBUTE_6
, INC.INCIDENT_ATTRIBUTE_7
, INC.INCIDENT_ATTRIBUTE_8
, INC.INCIDENT_ATTRIBUTE_9
, INC.INCIDENT_ATTRIBUTE_10
, INC.INCIDENT_ATTRIBUTE_11
, INC.INCIDENT_ATTRIBUTE_12
, INC.INCIDENT_ATTRIBUTE_13
, INC.INCIDENT_ATTRIBUTE_14
, INC.INCIDENT_ATTRIBUTE_15
, INC.INCIDENT_CONTEXT
, INC.RECORD_IS_VALID_FLAG
, INC.RESOLUTION_CODE
, INC.INTERFACED_TO_DEPOT_FLAG
, TYPE.NAME INCIDENT_TYPE
, SEV.NAME SEVERITY
, STATUS.NAME STATUS_CODE
, URGENCY.NAME URGENCY
, FND.USER_NAME CREATED_BY_NAME
, R.REVISION PRODUCT_REVISION
, INC.FILED_BY_EMPLOYEE_FLAG
, INC.CUSTOMER_ID
, INC.ACCOUNT_ID
, INC.EMPLOYEE_ID
, FND2.FULL_NAME EMPLOYEE_NAME
, FND2.EMPLOYEE_NUMBER
, CP.CURRENT_SERIAL_NUMBER CURRENT_SERIAL_NUMBER
, INC.CURRENT_SERIAL_NUMBER CURRENT_SERIAL_NUMBER_NV
, CP.SYSTEM_ID SYSTEM_ID
, SYS.NAME SYSTEM_NAME
, CP.INSTALL_SITE_USE_ID CP_INSTALL_SITE_USE_ID
, CP.REFERENCE_NUMBER
, CP.CUSTOMER_PRODUCT_STATUS_ID
, CPS.NAME CUSTOMER_PRODUCT_STATUS
, CP.CUSTOMER_ID PRODUCT_SUPPORT_CUSTOMER_ID
, INC.PURCHASE_ORDER_NUM
, INC.ORIGINAL_ORDER_NUMBER
, CPS.INCIDENT_ALLOWED_FLAG
, LOOK3.MEANING PROBLEM_CODE_MEANING
, LOOK3.DESCRIPTION PROBLEM_CODE_DESCRIPTION
, LOOK4.MEANING RESOLUTION_CODE_MEANING
, LOOK4.DESCRIPTION RESOLUTION_CODE_DESCRIPTION
, INC.WORKFLOW_PROCESS_ID
, CS_SR_UTIL_PKG.GET_LAST_UPDATE_DATE(INC.INCIDENT_ID
, INC.LAST_UPDATE_DATE) LAST_INCIDENT_UPDATE_DATE_V
, CS_SR_UTIL_PKG.GET_RELATED_STATUSES_CNT(TYPE.INCIDENT_TYPE_ID) RELATED_STATUSES_CNT_V
, INC.CLOSE_DATE DATE_CLOSED
, FND.USER_NAME LOGGED_BY_NAME
, NVL(STATUS.CLOSE_FLAG
, 'N') CLOSED_FLAG
, INC.PUBLISH_FLAG
, INC.QA_COLLECTION_ID
, INC.CONTRACT_SERVICE_ID
, INC.KB_SOLUTION_ID
, INC.TIME_ZONE_ID
, INC.CUSTOMER_PO_NUMBER
, INC.CUSTOMER_TICKET_NUMBER
, INC.OBLIGATION_DATE
, INC.SITE_ID
, INC.CUSTOMER_SITE_ID
, TL.SR_CREATION_CHANNEL
, INC.PLATFORM_VERSION_ID
, PARTY.PARTY_NUMBER CUSTOMER_NUMBER
, INC.CALLER_TYPE
, INC.CP_COMPONENT_ID
, INC.CP_COMPONENT_VERSION_ID
, INC.CP_SUBCOMPONENT_ID
, INC.CP_SUBCOMPONENT_VERSION_ID
, INC.CP_REVISION_ID
, INC.INV_ITEM_REVISION
, INC.INV_COMPONENT_ID
, INC.INV_COMPONENT_VERSION
, INC.INV_SUBCOMPONENT_ID
, INC.INV_SUBCOMPONENT_VERSION
, INC.INV_ORGANIZATION_ID
, INC.PLATFORM_ID
, INC.LANGUAGE_ID
, INC.TERRITORY_ID
, INC.TIME_DIFFERENCE
, KFV.CONCATENATED_SEGMENTS PRODUCT_NAME
, KFV.DESCRIPTION PRODUCT_DESCRIPTION
, OWN.RESOURCE_NAME OWNER
, PARTY.PARTY_NAME COMPANY_NAME
, SR_PARTY.SUB_FIRST_NAME PERSON_FIRST_NAME
, SR_PARTY.SUB_LAST_NAME PERSON_LAST_NAME
, SR_PARTY.SUB_FIRST_NAME || ' ' || SR_PARTY.SUB_LAST_NAME CONTACT_NAME
, PARTY_CONT.PHONE_COUNTRY_CODE COUNTRY_CODE
, PARTY_CONT.PHONE_AREA_CODE AREA_CODE
, PARTY_CONT.PHONE_NUMBER
, PARTY_CONT.PHONE_COUNTRY_CODE || DECODE(PARTY_CONT.PHONE_AREA_CODE
, ''
, ''
, '-' || PARTY_CONT.PHONE_AREA_CODE || '-') || PARTY_CONT.PHONE_NUMBER PHONE
, PARTY_CONT.PHONE_EXTENSION EXT
, PARTY_CONT.EMAIL_ADDRESS EMAIL
, PARTY_CONT.TELEPHONE_TYPE PHONE_TYPE
, HZ_TIME.GLOBAL_TIMEZONE_NAME TIME_ZONE_NAME
, SR_CONT.PARTY_ID CONTACT_PARTY_ID
, SR_CONT.CONTACT_POINT_ID
, ACCOUNT.ACCOUNT_NUMBER ACCOUNT
, LOOK5.MEANING CALLER_TYPE_NAME
, LOOK5.DESCRIPTION CALLER_TYPE_DESCRIPTION
, INC.OBJECT_VERSION_NUMBER
, SR_PARTY.SUB_TITLE PERSON_TITLE
, SR_CONT.CONTACT_TYPE
, INC.CONTRACT_NUMBER
, INC.CONTRACT_ID
, INC.PROJECT_NUMBER
, INC.ERROR_CODE
FROM FND_USER FND
, CS_INCIDENT_TYPES_VL TYPE
, CS_INCIDENT_SEVERITIES_VL SEV
, CS_INCIDENT_STATUSES_VL STATUS
, CS_INCIDENT_URGENCIES_VL URGENCY
, CS_LOOKUPS LOOK3
, CS_LOOKUPS LOOK4
, CS_LOOKUPS LOOK5
, CS_CP_REVISIONS R
, CS_CUSTOMER_PRODUCT_STATUSES CPS
, PER_ALL_PEOPLE_F FND2
, CS_SYSTEMS SYS
, CS_CUSTOMER_PRODUCTS CP
, CS_INCIDENTS_ALL_B INC
, CS_INCIDENTS_ALL_TL TL
, MTL_SYSTEM_ITEMS_KFV KFV
, CS_SR_OWNERS_V OWN
, JTF_PARTIES_ALL_V PARTY
, CSC_HZ_PARTIES_SELF_V SR_PARTY
, HZ_CONTACT_POINTS PARTY_CONT
, JTF_CUST_ACCOUNTS_ALL_V ACCOUNT
, CS_HZ_SR_CONTACT_POINTS SR_CONT
, HZ_TIMEZONES HZ_TIME
WHERE INC.INCIDENT_ID = TL.INCIDENT_ID
AND TL.LANGUAGE = USERENV('LANG')
AND INC.INCIDENT_TYPE_ID = TYPE.INCIDENT_TYPE_ID
AND INC.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND INC.CREATED_BY = FND.USER_ID
AND INC.EMPLOYEE_ID = FND2.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(FND2.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(FND2.EFFECTIVE_END_DATE
, TRUNC(SYSDATE))
AND INC.INCIDENT_URGENCY_ID = URGENCY.INCIDENT_URGENCY_ID (+)
AND INC.INCIDENT_SEVERITY_ID = SEV.INCIDENT_SEVERITY_ID
AND INC.CUSTOMER_PRODUCT_ID = CP.CUSTOMER_PRODUCT_ID (+)
AND CP.CUSTOMER_PRODUCT_ID = R.CUSTOMER_PRODUCT_ID (+)
AND CP.CURRENT_CP_REVISION_ID = R.CP_REVISION_ID (+)
AND CP.CUSTOMER_PRODUCT_STATUS_ID = CPS.CUSTOMER_PRODUCT_STATUS_ID (+)
AND CP.SYSTEM_ID = SYS.SYSTEM_ID (+)
AND INC.PROBLEM_CODE = LOOK3.LOOKUP_CODE(+)
AND LOOK3.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE'
AND INC.RESOLUTION_CODE = LOOK4.LOOKUP_CODE(+)
AND LOOK4.LOOKUP_TYPE (+) = 'REQUEST_RESOLUTION_CODE'
AND INC.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID (+)
AND KFV.ORGANIZATION_ID (+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND INC.INCIDENT_OWNER_ID = OWN.RESOURCE_ID(+)
AND INC.RESOURCE_TYPE = OWN.RESOURCE_TYPE(+)
AND INC.CUSTOMER_ID = PARTY.PARTY_ID (+)
AND INC.ACCOUNT_ID = ACCOUNT.CUST_ACCOUNT_ID (+)
AND SR_CONT.INCIDENT_ID (+) = INC.INCIDENT_ID
AND SR_CONT.CONTACT_POINT_ID = PARTY_CONT.CONTACT_POINT_ID (+)
AND SR_CONT.PARTY_ID = SR_PARTY.PARTY_ID (+)
AND SR_CONT.PRIMARY_FLAG (+) = 'Y'
AND INC.TIME_ZONE_ID = HZ_TIME.TIMEZONE_ID (+)
AND INC.CALLER_TYPE = LOOK5.LOOKUP_CODE(+)
AND LOOK5.LOOKUP_TYPE (+) = 'CS_SR_CALLER_TYPE'

Columns