VIEW: APPS.CS_INCIDENTS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'