Product: | CSF - Field Service |
---|---|
Description: | Service History Extension View |
Implementation/DBA Data: | APPS.CSF_SR_HIST_V |
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
, CP.REFERENCE_NUMBER
, 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
, (SELECT TYPE.NAME
FROM CS_INCIDENT_TYPES_TL TYPE
WHERE TYPE.INCIDENT_TYPE_ID=INC.INCIDENT_TYPE_ID
AND TYPE.LANGUAGE=USERENV('LANG')) INCIDENT_TYPE
, (SELECT SEV.NAME
FROM CS_INCIDENT_SEVERITIES_TL SEV
WHERE SEV.INCIDENT_SEVERITY_ID =INC.INCIDENT_SEVERITY_ID
AND SEV.LANGUAGE=USERENV('LANG')) SEVERITY
, (SELECT STATUS.NAME
FROM CS_INCIDENT_STATUSES_TL STATUS
WHERE STATUS.INCIDENT_STATUS_ID =INC.INCIDENT_STATUS_ID
AND STATUS.LANGUAGE=USERENV('LANG')) STATUS_CODE
, (SELECT URGENCY.NAME
FROM CS_INCIDENT_URGENCIES_TL URGENCY
WHERE URGENCY.INCIDENT_URGENCY_ID =INC.INCIDENT_URGENCY_ID
AND URGENCY.LANGUAGE(+)=USERENV('LANG')) URGENCY
, INC.FILED_BY_EMPLOYEE_FLAG
, INC.CUSTOMER_ID
, INC.ACCOUNT_ID
, INC.EMPLOYEE_ID
, NULL EMPLOYEE_NAME
, NULL EMPLOYEE_NUMBER
, INC.CURRENT_SERIAL_NUMBER CURRENT_SERIAL_NUMBER_NV
, INC.PURCHASE_ORDER_NUM
, INC.ORIGINAL_ORDER_NUMBER
, INC.WORKFLOW_PROCESS_ID
, INC.CLOSE_DATE DATE_CLOSED
, 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
, 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.PLATFORM_ID
, INC.LANGUAGE_ID
, INC.TERRITORY_ID
, INC.TIME_DIFFERENCE
, INC.INV_ORGANIZATION_ID
, KFV.CONCATENATED_SEGMENTS PRODUCT_NAME
, KFV.DESCRIPTION PRODUCT_DESCRIPTION
, TL.OWNER OWNER
, PARTY.PARTY_NAME COMPANY_NAME
, SR_PARTY.PERSON_FIRST_NAME
, SR_PARTY.PERSON_LAST_NAME
, SR_PARTY.PERSON_FIRST_NAME||' '||SR_PARTY.PERSON_LAST_NAME CONTACT_NAME
, PARTY_CONT.PHONE_COUNTRY_CODE COUNTRY_CODE
, PARTY_CONT.PHONE_AREA_CODE AREA_CODE
, PARTY_CONT.PHONE_NUMBER
, DECODE(PARTY_CONT.PHONE_COUNTRY_CODE
, NULL
, NULL
, PHONE_COUNTRY_CODE||'-')|| DECODE(PARTY_CONT.PHONE_AREA_CODE
, NULL
, NULL
, PHONE_AREA_CODE||'-')|| PARTY_CONT.PHONE_NUMBER PHONE
, PARTY_CONT.PHONE_EXTENSION EXT
, PARTY_CONT.EMAIL_ADDRESS EMAIL
, PARTY_CONT.TELEPHONE_TYPE PHONE_TYPE
, SR_CONT.PARTY_ID CONTACT_PARTY_ID
, SR_CONT.CONTACT_POINT_ID
, (SELECT ACCOUNT.ACCOUNT_NUMBER
FROM HZ_CUST_ACCOUNTS ACCOUNT
WHERE ACCOUNT.CUST_ACCOUNT_ID=INC.ACCOUNT_ID) ACCOUNT
, INC.OBJECT_VERSION_NUMBER
, SR_PARTY.PERSON_PRE_NAME_ADJUNCT PERSON_TITLE
, (SELECT LOOK5.MEANING
FROM CS_LOOKUPS LOOK5
WHERE LOOK5.LOOKUP_TYPE='CS_SR_CALLER_TYPE'
AND LOOK5.LOOKUP_CODE=INC.CALLER_TYPE) CALLER_TYPE_NAME
, (SELECT LOOK5.DESCRIPTION
FROM CS_LOOKUPS LOOK5
WHERE LOOK5.LOOKUP_TYPE='CS_SR_CALLER_TYPE'
AND LOOK5.LOOKUP_CODE=INC.CALLER_TYPE) CALLER_TYPE_DESCRIPTION
, (SELECT HZ_TIME.GLOBAL_TIMEZONE_NAME
FROM HZ_TIMEZONES HZ_TIME
WHERE HZ_TIME.TIMEZONE_ID = INC.TIME_ZONE_ID) TIME_ZONE_NAME
, KFV.ORGANIZATION_ID
, PARTY.COUNTRY
, INC.CONTRACT_NUMBER
, INC.CONTRACT_ID
, INC.PROJECT_NUMBER
, SR_CONT.CONTACT_TYPE
, SR_PARTY.PARTY_NUMBER CONTACT_NUMBER
, INC.GROUP_TYPE
, INC.OWNER_GROUP_ID
, INC.ERROR_CODE
, INC.SYSTEM_ID
, INC.EXTERNAL_REFERENCE
, INC.INCIDENT_OCCURRED_DATE
, TL.GROUP_OWNER
, INC.CATEGORY_ID
FROM CS_INCIDENTS_ALL_B INC
, CS_INCIDENTS_ALL_TL TL
, CS_CUSTOMER_PRODUCTS CP
, MTL_SYSTEM_ITEMS_KFV KFV
, HZ_PARTIES PARTY
, HZ_PARTIES SR_PARTY
, HZ_CONTACT_POINTS PARTY_CONT
, CS_HZ_SR_CONTACT_POINTS SR_CONT
WHERE INC.INVENTORY_ITEM_ID=KFV.INVENTORY_ITEM_ID(+)
AND KFV.ORGANIZATION_ID(+)=FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')
AND CP.CUSTOMER_PRODUCT_ID(+)=INC.CUSTOMER_PRODUCT_ID
AND INC.INCIDENT_ID=TL.INCIDENT_ID
AND TL.LANGUAGE=USERENV('LANG')
AND INC.CUSTOMER_ID=PARTY.PARTY_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'