eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CSD_INCIDENTS_V

Source

View Text - Preformatted


SELECT INC.ROWID ROW_ID, INC.INCIDENT_ID, INC.INCIDENT_NUMBER, INC.INCIDENT_STATUS_ID, INC.INCIDENT_TYPE_ID, INC.INCIDENT_URGENCY_ID, INC.INCIDENT_SEVERITY_ID, INC.INVENTORY_ITEM_ID, KFV.CONCATENATED_SEGMENTS ITEM, KFV.DESCRIPTION ITEM_DESC, nvl(INC.INV_ITEM_REVISION, INC.PRODUCT_REVISION) "INV_ITEM_REVISION", INC.INV_ORGANIZATION_ID, decode(INC.CALLER_TYPE, 'ORGANIZATION', PARTY_CONT1.PHONE_COUNTRY_CODE|| decode(PARTY_CONT1.PHONE_AREA_CODE, '', '', decode(PARTY_CONT1.PHONE_COUNTRY_CODE, '', PARTY_CONT1.PHONE_AREA_CODE, '-' ||PARTY_CONT1.PHONE_AREA_CODE)) || decode(PARTY_CONT1.PHONE_NUMBER, '', '',decode(PARTY_CONT1.PHONE_AREA_CODE, '', decode(PARTY_CONT1.PHONE_COUNTRY_CODE, '',PARTY_CONT1.PHONE_NUMBER, '-' || PARTY_CONT1.PHONE_NUMBER), '-' || PARTY_CONT1.PHONE_NUMBER)), 'PERSON',PARTY_CONT1.PHONE_COUNTRY_CODE || decode(PARTY_CONT1.PHONE_AREA_CODE, '', '', decode(PARTY_CONT1.PHONE_COUNTRY_CODE, '',PARTY_CONT1.PHONE_AREA_CODE, '-' ||PARTY_CONT1.PHONE_AREA_CODE)) || decode(PARTY_CONT1.PHONE_NUMBER, '', '', decode(PARTY_CONT1.PHONE_AREA_CODE, '', decode(PARTY_CONT1.PHONE_COUNTRY_CODE , '', PARTY_CONT1.PHONE_NUMBER , '-' || PARTY_CONT1.PHONE_NUMBER ), '-' || PARTY_CONT1.PHONE_NUMBER )), NULL) "CUST_PHONE", ARL2.meaning "CUST_PHONE_LINE_TYPE", decode(INC.CALLER_TYPE, 'ORGANIZATION', PARTY_CONT1.PHONE_LINE_TYPE , 'PERSON', PARTY_CONT1.PHONE_LINE_TYPE , NULL) "CUST_PHONE_LINE_CODE", decode(INC.CALLER_TYPE, 'ORGANIZATION', PARTY_CONT1.PHONE_EXTENSION , 'PERSON', PARTY_CONT1.PHONE_EXTENSION , NULL) "EXT", decode(INC.CALLER_TYPE, 'ORGANIZATION', PARTY.PERSON_FIRST_NAME || ' ' || PARTY.PERSON_LAST_NAME , 'PERSON', PARTY.PERSON_FIRST_NAME || ' ' || PARTY.PERSON_LAST_NAME , FND2.FULL_NAME) "CALLER", INC.INCIDENT_DATE "INCIDENT_DATE", CII.INSTANCE_ID CUSTOMER_PRODUCT_ID, INC.RECORD_IS_VALID_FLAG, INC.ORG_ID, SUBSTR(INC.RESOURCE_TYPE,4,30) "OWNER_TYPE", INC.INCIDENT_OWNER_ID, DECODE(NVL(INC.INCIDENT_OWNER_ID,'-999'),'-999', '', CSD_PROCESS_UTIL.GET_RES_NAME(SUBSTR(INC.RESOURCE_TYPE,4,30), INC.INCIDENT_OWNER_ID)) " INCIDENT_OWNER", INC.CALLER_TYPE, TYPE.NAME INCIDENT_TYPE, SEV.NAME SEVERITY, STATUS.NAME STATUS, URGENCY_T.NAME URGENCY, FND.USER_NAME CREATED_BY_NAME, INC.FILED_BY_EMPLOYEE_FLAG, decode(INC.CALLER_TYPE, 'ORGANIZATION', INC.CUSTOMER_ID,'PERSON',INC.CUSTOMER_ID, INC.EMPLOYEE_ID) "CUSTOMER_ID", decode(SR_CONT.CONTACT_TYPE, 'EMPLOYEE' , SR_CONT.PARTY_ID, NULL) "EMPLOYEE_ID", decode(INC.CALLER_TYPE, 'ORGANIZATION',PARTY.PARTY_NUMBER, 'PERSON', PARTY.PARTY_NUMBER,'') "PARTY_NUMBER", decode(INC.CALLER_TYPE, 'ORGANIZATION', PARTY.PARTY_NAME , 'PERSON', PARTY.PERSON_FIRST_NAME || ' ' || PARTY.PERSON_LAST_NAME , FND2.FULL_NAME) "PARTY_NAME", decode(CII.INSTANCE_NUMBER, null, INC.CURRENT_SERIAL_NUMBER, CII.SERIAL_NUMBER) "CURRENT_SERIAL_NUMBER", INC.INSTALL_SITE_USE_ID, CII.LOCATION_ID CP_INSTALL_SITE_USE_ID, HZSITE1.PARTY_SITE_NUMBER "SITE", CPS.INCIDENT_ALLOWED_FLAG, HZLOC1.ADDRESS_STYLE, HZLOC1.ADDRESS1, HZLOC1.ADDRESS2, HZLOC1.ADDRESS3, HZLOC1.ADDRESS4, HZLOC1.ADDRESS_LINES_PHONETIC, HZLOC1.CITY, HZLOC1.COUNTY, HZLOC1.POSTAL_CODE, HZLOC1.PROVINCE, HZLOC1.STATE, CII.INSTANCE_NUMBER REFERENCE_NUMBER, INC.CLOSE_DATE DATE_CLOSED, NVL(STATUS.CLOSE_FLAG, 'N') CLOSED_FLAG, FND.USER_NAME LOGGED_BY_NAME, INC.PUBLISH_FLAG, INC.CONTRACT_SERVICE_ID, INC.CONTRACT_NUMBER, INC.CONTRACT_ID, INC.ACCOUNT_ID, INC.CUSTOMER_PO_NUMBER, ACCOUNT.ACCOUNT_NUMBER ACCOUNT, INC.SUMMARY, DECODE(NVL(INC.BILL_TO_SITE_USE_ID,'-9999'), '-9999','',SUBSTR(HZLOC2.ADDRESS1||', '|| HZLOC2.ADDRESS2||', '|| HZLOC2.ADDRESS3||', '|| HZLOC2.ADDRESS4, 1, 440) ||', '|| HZLOC2.CITY||', '||HZLOC2.COUNTY||', '|| HZLOC2.POSTAL_CODE||', '|| HZLOC2.PROVINCE||', '|| HZLOC2.STATE||', '||HZLOC2.COUNTRY) BILL_TO_ADDRESS , DECODE(NVL(INC.SHIP_TO_SITE_USE_ID,'-9999'), '-9999','',SUBSTR(HZLOC3.ADDRESS1||', '|| HZLOC3.ADDRESS2||', '|| HZLOC3.ADDRESS3||', '|| HZLOC3.ADDRESS4, 1, 440) ||', '|| HZLOC3.CITY||', '||HZLOC3.COUNTY||', '|| HZLOC3.POSTAL_CODE||', '|| HZLOC3.PROVINCE||', '|| HZLOC3.STATE||', '||HZLOC3.COUNTRY) SHIP_TO_ADDRESS , INC.BILL_TO_SITE_USE_ID, INC.SHIP_TO_SITE_USE_ID, SR_CONT.contact_point_id, SR_CONT.contact_type, decode(SR_CONT.CONTACT_TYPE, 'EMPLOYEE',FND2.LAST_NAME ,'PERSON',SR_CONT_PARTY.PERSON_LAST_NAME,'PARTY_RELATIONSHIP','' ) "last_name" , decode(SR_CONT.CONTACT_TYPE, 'EMPLOYEE',FND2.FIRST_NAME,'PERSON',SR_CONT_PARTY.PERSON_FIRST_NAME,'PARTY_RELATIONSHIP','' ) "first_name" , decode(SR_CONT.CONTACT_TYPE, 'EMPLOYEE',FND2.FULL_NAME ,'PERSON','','PARTY_RELATIONSHIP',SR_CONT_PARTY.PARTY_NAME ) "full_name" , decode(SR_CONT.CONTACT_TYPE,'PARTY_RELATIONSHIP', HZ_REL.RELATIONSHIP_CODE, '') , arl.meaning, decode(SR_CONT.CONTACT_TYPE,'PARTY_RELATIONSHIP',SR_CONT_PARTY.PARTY_NUMBER,'PERSON', SR_CONT_PARTY.PARTY_NUMBER, 'EMPLOYEE', FND2.EMPLOYEE_NUMBER), decode(SR_CONT.CONTACT_TYPE,'PARTY_RELATIONSHIP', PARTY_CONT.PHONE_COUNTRY_CODE|| decode(PARTY_CONT.PHONE_AREA_CODE, '', '', decode(PARTY_CONT.PHONE_COUNTRY_CODE, '', PARTY_CONT.PHONE_AREA_CODE, '-' ||PARTY_CONT.PHONE_AREA_CODE)) || decode(PARTY_CONT.PHONE_NUMBER, '', '',decode(PARTY_CONT.PHONE_AREA_CODE, '', decode(PARTY_CONT.PHONE_COUNTRY_CODE, '', PARTY_CONT.PHONE_NUMBER, '-' || PARTY_CONT.PHONE_NUMBER), '-' || PARTY_CONT.PHONE_NUMBER)), 'PERSON', PARTY_CONT.PHONE_COUNTRY_CODE || decode(PARTY_CONT.PHONE_AREA_CODE, '', '', decode(PARTY_CONT.PHONE_COUNTRY_CODE, '',PARTY_CONT.PHONE_AREA_CODE, '-' || PARTY_CONT.PHONE_AREA_CODE)) || decode(PARTY_CONT.PHONE_NUMBER, '', '', decode(PARTY_CONT.PHONE_AREA_CODE, '', decode(PARTY_CONT.PHONE_COUNTRY_CODE , '', PARTY_CONT.PHONE_NUMBER , '-' || PARTY_CONT.PHONE_NUMBER ), '-' || PARTY_CONT.PHONE_NUMBER )), 'EMPLOYEE', PERP.PHONE_NUMBER, NULL ) "cont_phone", decode(SR_CONT.CONTACT_TYPE, 'PARTY_RELATIONSHIP', PARTY_CONT.PHONE_LINE_TYPE ,'PERSON', PARTY_CONT.PHONE_LINE_TYPE , 'EMPLOYEE',PERP.PHONE_TYPE,NULL) "cont_phone_line_code", decode(SR_CONT.CONTACT_TYPE, 'PARTY_RELATIONSHIP', ARL3.meaning, 'PERSON', ARL3.meaning, 'EMPLOYEE',HRL.meaning ,NULL) "cont_phone_line_type", decode(SR_CONT.CONTACT_TYPE, 'PARTY_RELATIONSHIP', PARTY_CONT2.email_address, 'PERSON', PARTY_CONT2.email_address, 'EMPLOYEE',PEREMPS.email_address ,NULL) "cont_email" , csprob.lookup_code Problem_Code , csprob.meaning Problem_Code_Meaning , csprob.description Problem_Code_Description , INC.EXTERNAL_CONTEXT , INC.EXTERNAL_ATTRIBUTE_1 , INC.EXTERNAL_ATTRIBUTE_2 , INC.EXTERNAL_ATTRIBUTE_3 , INC.EXTERNAL_ATTRIBUTE_4 , INC.EXTERNAL_ATTRIBUTE_5 , INC.EXTERNAL_ATTRIBUTE_6 , INC.EXTERNAL_ATTRIBUTE_7 , INC.EXTERNAL_ATTRIBUTE_8 , INC.EXTERNAL_ATTRIBUTE_9 , INC.EXTERNAL_ATTRIBUTE_10 , INC.EXTERNAL_ATTRIBUTE_11, INC.EXTERNAL_ATTRIBUTE_12 , INC.EXTERNAL_ATTRIBUTE_13 , INC.EXTERNAL_ATTRIBUTE_14 , INC.EXTERNAL_ATTRIBUTE_15 FROM FND_USER FND, CS_INCIDENT_TYPES_VL TYPE, CS_INCIDENT_SEVERITIES_VL SEV, CS_INCIDENT_STATUSES_VL STATUS, CS_INCIDENT_URGENCIES_B URGENCY_B, CS_INCIDENT_URGENCIES_TL URGENCY_T, CSI_INSTANCE_STATUSES CPS, PER_ALL_PEOPLE_F FND2, CSI_ITEM_INSTANCES CII, CS_INCIDENTS_VL_SEC INC, MTL_SYSTEM_ITEMS_VL KFV, HZ_PARTY_SITES HZSITE1, HZ_LOCATIONS HZLOC1, HZ_PARTY_SITES HZSITE2, HZ_PARTY_SITE_USES HZUSES2, HZ_LOCATIONS HZLOC2, HZ_PARTY_SITES HZSITE3, HZ_PARTY_SITE_USES HZUSES3, HZ_LOCATIONS HZLOC3, JTF_PARTIES_ALL_V PARTY, HZ_PARTIES SR_CONT_PARTY, HZ_RELATIONSHIPS HZ_REL, HZ_CONTACT_POINTS PARTY_CONT, HZ_CONTACT_POINTS PARTY_CONT1, HZ_CONTACT_POINTS PARTY_CONT2, PER_EMPLOYEES_CURRENT_X PEREMPS, JTF_CUST_ACCOUNTS_ALL_V ACCOUNT, CS_HZ_SR_CONTACT_POINTS SR_CONT, HZ_TIMEZONES HZ_TIME, PER_PHONES PERP, HR_LOOKUPS HRL, AR_LOOKUPS ARL, AR_LOOKUPS ARL2, AR_LOOKUPS ARL3 , cs_lookups csprob WHERE 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 decode(SR_CONT.CONTACT_TYPE, 'EMPLOYEE' , SR_CONT.PARTY_ID, NULL) = FND2.PERSON_ID(+) AND TRUNC(SYSDATE) BETWEEN NVL(FND2.EFFECTIVE_START_DATE,TRUNC(SYSDATE)) AND NVL(FND2.EFFECTIVE_END_DATE, TRUNC(SYSDATE+1)) AND INC.INCIDENT_URGENCY_ID = URGENCY_B.INCIDENT_URGENCY_ID (+) AND URGENCY_T.INCIDENT_URGENCY_ID (+) = URGENCY_B.INCIDENT_URGENCY_ID AND URGENCY_T.LANGUAGE (+) = USERENV('LANG') AND INC.INCIDENT_SEVERITY_ID = SEV.INCIDENT_SEVERITY_ID AND INC.CUSTOMER_PRODUCT_ID = CII.INSTANCE_ID (+) AND CII.INSTANCE_STATUS_ID = CPS.INSTANCE_STATUS_ID (+) AND INC.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID (+) AND KFV.ORGANIZATION_ID (+) = CS_STD.Get_Item_Valdn_Orgzn_Id 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_CONT_PARTY.PARTY_ID (+) AND SR_CONT.PRIMARY_FLAG (+) = 'Y' AND INC.TIME_ZONE_ID = HZ_TIME.TIMEZONE_ID (+) AND INC.INSTALL_SITE_USE_ID = HZSITE1.PARTY_SITE_ID(+) AND HZSITE1.LOCATION_ID = HZLOC1.LOCATION_ID(+) AND INC.BILL_TO_SITE_USE_ID = HZUSES2.PARTY_SITE_USE_ID(+) AND HZUSES2.PARTY_SITE_ID = HZSITE2.PARTY_SITE_ID(+) AND HZSITE2.LOCATION_ID = HZLOC2.LOCATION_ID(+) AND INC.SHIP_TO_SITE_USE_ID = HZUSES3.PARTY_SITE_USE_ID(+) AND HZUSES3.PARTY_SITE_ID = HZSITE3.PARTY_SITE_ID(+) AND HZSITE3.LOCATION_ID = HZLOC3.LOCATION_ID(+) AND PERP.PARENT_ID(+) = DECODE(SR_CONT.contact_type,'EMPLOYEE',SR_CONT.party_id,NULL) AND PERP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F' AND PERP.PHONE_ID(+) = DECODE(SR_CONT.contact_type,'EMPLOYEE',SR_CONT.contact_point_id,NULL) AND ARL.LOOKUP_TYPE (+) = 'PARTY_RELATIONS_TYPE' AND ARL.LOOKUP_CODE (+) = HZ_REL.RELATIONSHIP_CODE AND ARL2.LOOKUP_TYPE (+) = 'PHONE_LINE_TYPE' AND ARL2.LOOKUP_CODE (+) = PARTY_CONT1.PHONE_LINE_TYPE AND ARL3.LOOKUP_TYPE (+) = 'PHONE_LINE_TYPE' AND ARL3.LOOKUP_CODE (+) = PARTY_CONT.PHONE_LINE_TYPE AND HRL.LOOKUP_TYPE (+) = 'PHONE_TYPE' AND HRL.LOOKUP_CODE (+) = PERP.PHONE_TYPE AND SR_CONT_PARTY.PARTY_ID = HZ_REL.PARTY_ID (+) AND HZ_REL.SUBJECT_TABLE_NAME (+)= 'HZ_PARTIES' AND HZ_REL.OBJECT_TABLE_NAME (+)= 'HZ_PARTIES' AND HZ_REL.DIRECTIONAL_FLAG(+) = 'F' AND PARTY_CONT1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND PARTY_CONT1.OWNER_TABLE_ID(+) = INC.customer_id AND PARTY_CONT1.PRIMARY_FLAG (+) = 'Y' AND PARTY_CONT1.CONTACT_POINT_TYPE (+) = 'PHONE' AND PARTY_CONT1.STATUS (+) = 'A' AND PARTY_CONT2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND PARTY_CONT2.OWNER_TABLE_ID(+) = SR_CONT.party_id AND PARTY_CONT2.PRIMARY_FLAG (+) = 'Y' AND PARTY_CONT2.CONTACT_POINT_TYPE (+) = 'EMAIL' AND PARTY_CONT2.STATUS (+) = 'A' AND PEREMPS.EMPLOYEE_ID(+) = DECODE(SR_CONT.contact_type,'EMPLOYEE',SR_CONT.party_id,NULL) AND inc.problem_code = csprob.lookup_code (+) AND csprob.lookup_type (+) = 'REQUEST_PROBLEM_CODE'

View Text - HTML Formatted

SELECT INC.ROWID ROW_ID
, INC.INCIDENT_ID
, INC.INCIDENT_NUMBER
, INC.INCIDENT_STATUS_ID
, INC.INCIDENT_TYPE_ID
, INC.INCIDENT_URGENCY_ID
, INC.INCIDENT_SEVERITY_ID
, INC.INVENTORY_ITEM_ID
, KFV.CONCATENATED_SEGMENTS ITEM
, KFV.DESCRIPTION ITEM_DESC
, NVL(INC.INV_ITEM_REVISION
, INC.PRODUCT_REVISION) "INV_ITEM_REVISION"
, INC.INV_ORGANIZATION_ID
, DECODE(INC.CALLER_TYPE
, 'ORGANIZATION'
, PARTY_CONT1.PHONE_COUNTRY_CODE|| DECODE(PARTY_CONT1.PHONE_AREA_CODE
, ''
, ''
, DECODE(PARTY_CONT1.PHONE_COUNTRY_CODE
, ''
, PARTY_CONT1.PHONE_AREA_CODE
, '-' ||PARTY_CONT1.PHONE_AREA_CODE)) || DECODE(PARTY_CONT1.PHONE_NUMBER
, ''
, ''
, DECODE(PARTY_CONT1.PHONE_AREA_CODE
, ''
, DECODE(PARTY_CONT1.PHONE_COUNTRY_CODE
, ''
, PARTY_CONT1.PHONE_NUMBER
, '-' || PARTY_CONT1.PHONE_NUMBER)
, '-' || PARTY_CONT1.PHONE_NUMBER))
, 'PERSON'
, PARTY_CONT1.PHONE_COUNTRY_CODE || DECODE(PARTY_CONT1.PHONE_AREA_CODE
, ''
, ''
, DECODE(PARTY_CONT1.PHONE_COUNTRY_CODE
, ''
, PARTY_CONT1.PHONE_AREA_CODE
, '-' ||PARTY_CONT1.PHONE_AREA_CODE)) || DECODE(PARTY_CONT1.PHONE_NUMBER
, ''
, ''
, DECODE(PARTY_CONT1.PHONE_AREA_CODE
, ''
, DECODE(PARTY_CONT1.PHONE_COUNTRY_CODE
, ''
, PARTY_CONT1.PHONE_NUMBER
, '-' || PARTY_CONT1.PHONE_NUMBER )
, '-' || PARTY_CONT1.PHONE_NUMBER ))
, NULL) "CUST_PHONE"
, ARL2.MEANING "CUST_PHONE_LINE_TYPE"
, DECODE(INC.CALLER_TYPE
, 'ORGANIZATION'
, PARTY_CONT1.PHONE_LINE_TYPE
, 'PERSON'
, PARTY_CONT1.PHONE_LINE_TYPE
, NULL) "CUST_PHONE_LINE_CODE"
, DECODE(INC.CALLER_TYPE
, 'ORGANIZATION'
, PARTY_CONT1.PHONE_EXTENSION
, 'PERSON'
, PARTY_CONT1.PHONE_EXTENSION
, NULL) "EXT"
, DECODE(INC.CALLER_TYPE
, 'ORGANIZATION'
, PARTY.PERSON_FIRST_NAME || ' ' || PARTY.PERSON_LAST_NAME
, 'PERSON'
, PARTY.PERSON_FIRST_NAME || ' ' || PARTY.PERSON_LAST_NAME
, FND2.FULL_NAME) "CALLER"
, INC.INCIDENT_DATE "INCIDENT_DATE"
, CII.INSTANCE_ID CUSTOMER_PRODUCT_ID
, INC.RECORD_IS_VALID_FLAG
, INC.ORG_ID
, SUBSTR(INC.RESOURCE_TYPE
, 4
, 30) "OWNER_TYPE"
, INC.INCIDENT_OWNER_ID
, DECODE(NVL(INC.INCIDENT_OWNER_ID
, '-999')
, '-999'
, ''
, CSD_PROCESS_UTIL.GET_RES_NAME(SUBSTR(INC.RESOURCE_TYPE
, 4
, 30)
, INC.INCIDENT_OWNER_ID)) " INCIDENT_OWNER"
, INC.CALLER_TYPE
, TYPE.NAME INCIDENT_TYPE
, SEV.NAME SEVERITY
, STATUS.NAME STATUS
, URGENCY_T.NAME URGENCY
, FND.USER_NAME CREATED_BY_NAME
, INC.FILED_BY_EMPLOYEE_FLAG
, DECODE(INC.CALLER_TYPE
, 'ORGANIZATION'
, INC.CUSTOMER_ID
, 'PERSON'
, INC.CUSTOMER_ID
, INC.EMPLOYEE_ID) "CUSTOMER_ID"
, DECODE(SR_CONT.CONTACT_TYPE
, 'EMPLOYEE'
, SR_CONT.PARTY_ID
, NULL) "EMPLOYEE_ID"
, DECODE(INC.CALLER_TYPE
, 'ORGANIZATION'
, PARTY.PARTY_NUMBER
, 'PERSON'
, PARTY.PARTY_NUMBER
, '') "PARTY_NUMBER"
, DECODE(INC.CALLER_TYPE
, 'ORGANIZATION'
, PARTY.PARTY_NAME
, 'PERSON'
, PARTY.PERSON_FIRST_NAME || ' ' || PARTY.PERSON_LAST_NAME
, FND2.FULL_NAME) "PARTY_NAME"
, DECODE(CII.INSTANCE_NUMBER
, NULL
, INC.CURRENT_SERIAL_NUMBER
, CII.SERIAL_NUMBER) "CURRENT_SERIAL_NUMBER"
, INC.INSTALL_SITE_USE_ID
, CII.LOCATION_ID CP_INSTALL_SITE_USE_ID
, HZSITE1.PARTY_SITE_NUMBER "SITE"
, CPS.INCIDENT_ALLOWED_FLAG
, HZLOC1.ADDRESS_STYLE
, HZLOC1.ADDRESS1
, HZLOC1.ADDRESS2
, HZLOC1.ADDRESS3
, HZLOC1.ADDRESS4
, HZLOC1.ADDRESS_LINES_PHONETIC
, HZLOC1.CITY
, HZLOC1.COUNTY
, HZLOC1.POSTAL_CODE
, HZLOC1.PROVINCE
, HZLOC1.STATE
, CII.INSTANCE_NUMBER REFERENCE_NUMBER
, INC.CLOSE_DATE DATE_CLOSED
, NVL(STATUS.CLOSE_FLAG
, 'N') CLOSED_FLAG
, FND.USER_NAME LOGGED_BY_NAME
, INC.PUBLISH_FLAG
, INC.CONTRACT_SERVICE_ID
, INC.CONTRACT_NUMBER
, INC.CONTRACT_ID
, INC.ACCOUNT_ID
, INC.CUSTOMER_PO_NUMBER
, ACCOUNT.ACCOUNT_NUMBER ACCOUNT
, INC.SUMMARY
, DECODE(NVL(INC.BILL_TO_SITE_USE_ID
, '-9999')
, '-9999'
, ''
, SUBSTR(HZLOC2.ADDRESS1||'
, '|| HZLOC2.ADDRESS2||'
, '|| HZLOC2.ADDRESS3||'
, '|| HZLOC2.ADDRESS4
, 1
, 440) ||'
, '|| HZLOC2.CITY||'
, '||HZLOC2.COUNTY||'
, '|| HZLOC2.POSTAL_CODE||'
, '|| HZLOC2.PROVINCE||'
, '|| HZLOC2.STATE||'
, '||HZLOC2.COUNTRY) BILL_TO_ADDRESS
, DECODE(NVL(INC.SHIP_TO_SITE_USE_ID
, '-9999')
, '-9999'
, ''
, SUBSTR(HZLOC3.ADDRESS1||'
, '|| HZLOC3.ADDRESS2||'
, '|| HZLOC3.ADDRESS3||'
, '|| HZLOC3.ADDRESS4
, 1
, 440) ||'
, '|| HZLOC3.CITY||'
, '||HZLOC3.COUNTY||'
, '|| HZLOC3.POSTAL_CODE||'
, '|| HZLOC3.PROVINCE||'
, '|| HZLOC3.STATE||'
, '||HZLOC3.COUNTRY) SHIP_TO_ADDRESS
, INC.BILL_TO_SITE_USE_ID
, INC.SHIP_TO_SITE_USE_ID
, SR_CONT.CONTACT_POINT_ID
, SR_CONT.CONTACT_TYPE
, DECODE(SR_CONT.CONTACT_TYPE
, 'EMPLOYEE'
, FND2.LAST_NAME
, 'PERSON'
, SR_CONT_PARTY.PERSON_LAST_NAME
, 'PARTY_RELATIONSHIP'
, '' ) "LAST_NAME"
, DECODE(SR_CONT.CONTACT_TYPE
, 'EMPLOYEE'
, FND2.FIRST_NAME
, 'PERSON'
, SR_CONT_PARTY.PERSON_FIRST_NAME
, 'PARTY_RELATIONSHIP'
, '' ) "FIRST_NAME"
, DECODE(SR_CONT.CONTACT_TYPE
, 'EMPLOYEE'
, FND2.FULL_NAME
, 'PERSON'
, ''
, 'PARTY_RELATIONSHIP'
, SR_CONT_PARTY.PARTY_NAME ) "FULL_NAME"
, DECODE(SR_CONT.CONTACT_TYPE
, 'PARTY_RELATIONSHIP'
, HZ_REL.RELATIONSHIP_CODE
, '')
, ARL.MEANING
, DECODE(SR_CONT.CONTACT_TYPE
, 'PARTY_RELATIONSHIP'
, SR_CONT_PARTY.PARTY_NUMBER
, 'PERSON'
, SR_CONT_PARTY.PARTY_NUMBER
, 'EMPLOYEE'
, FND2.EMPLOYEE_NUMBER)
, DECODE(SR_CONT.CONTACT_TYPE
, 'PARTY_RELATIONSHIP'
, PARTY_CONT.PHONE_COUNTRY_CODE|| DECODE(PARTY_CONT.PHONE_AREA_CODE
, ''
, ''
, DECODE(PARTY_CONT.PHONE_COUNTRY_CODE
, ''
, PARTY_CONT.PHONE_AREA_CODE
, '-' ||PARTY_CONT.PHONE_AREA_CODE)) || DECODE(PARTY_CONT.PHONE_NUMBER
, ''
, ''
, DECODE(PARTY_CONT.PHONE_AREA_CODE
, ''
, DECODE(PARTY_CONT.PHONE_COUNTRY_CODE
, ''
, PARTY_CONT.PHONE_NUMBER
, '-' || PARTY_CONT.PHONE_NUMBER)
, '-' || PARTY_CONT.PHONE_NUMBER))
, 'PERSON'
, PARTY_CONT.PHONE_COUNTRY_CODE || DECODE(PARTY_CONT.PHONE_AREA_CODE
, ''
, ''
, DECODE(PARTY_CONT.PHONE_COUNTRY_CODE
, ''
, PARTY_CONT.PHONE_AREA_CODE
, '-' || PARTY_CONT.PHONE_AREA_CODE)) || DECODE(PARTY_CONT.PHONE_NUMBER
, ''
, ''
, DECODE(PARTY_CONT.PHONE_AREA_CODE
, ''
, DECODE(PARTY_CONT.PHONE_COUNTRY_CODE
, ''
, PARTY_CONT.PHONE_NUMBER
, '-' || PARTY_CONT.PHONE_NUMBER )
, '-' || PARTY_CONT.PHONE_NUMBER ))
, 'EMPLOYEE'
, PERP.PHONE_NUMBER
, NULL ) "CONT_PHONE"
, DECODE(SR_CONT.CONTACT_TYPE
, 'PARTY_RELATIONSHIP'
, PARTY_CONT.PHONE_LINE_TYPE
, 'PERSON'
, PARTY_CONT.PHONE_LINE_TYPE
, 'EMPLOYEE'
, PERP.PHONE_TYPE
, NULL) "CONT_PHONE_LINE_CODE"
, DECODE(SR_CONT.CONTACT_TYPE
, 'PARTY_RELATIONSHIP'
, ARL3.MEANING
, 'PERSON'
, ARL3.MEANING
, 'EMPLOYEE'
, HRL.MEANING
, NULL) "CONT_PHONE_LINE_TYPE"
, DECODE(SR_CONT.CONTACT_TYPE
, 'PARTY_RELATIONSHIP'
, PARTY_CONT2.EMAIL_ADDRESS
, 'PERSON'
, PARTY_CONT2.EMAIL_ADDRESS
, 'EMPLOYEE'
, PEREMPS.EMAIL_ADDRESS
, NULL) "CONT_EMAIL"
, CSPROB.LOOKUP_CODE PROBLEM_CODE
, CSPROB.MEANING PROBLEM_CODE_MEANING
, CSPROB.DESCRIPTION PROBLEM_CODE_DESCRIPTION
, INC.EXTERNAL_CONTEXT
, INC.EXTERNAL_ATTRIBUTE_1
, INC.EXTERNAL_ATTRIBUTE_2
, INC.EXTERNAL_ATTRIBUTE_3
, INC.EXTERNAL_ATTRIBUTE_4
, INC.EXTERNAL_ATTRIBUTE_5
, INC.EXTERNAL_ATTRIBUTE_6
, INC.EXTERNAL_ATTRIBUTE_7
, INC.EXTERNAL_ATTRIBUTE_8
, INC.EXTERNAL_ATTRIBUTE_9
, INC.EXTERNAL_ATTRIBUTE_10
, INC.EXTERNAL_ATTRIBUTE_11
, INC.EXTERNAL_ATTRIBUTE_12
, INC.EXTERNAL_ATTRIBUTE_13
, INC.EXTERNAL_ATTRIBUTE_14
, INC.EXTERNAL_ATTRIBUTE_15
FROM FND_USER FND
, CS_INCIDENT_TYPES_VL TYPE
, CS_INCIDENT_SEVERITIES_VL SEV
, CS_INCIDENT_STATUSES_VL STATUS
, CS_INCIDENT_URGENCIES_B URGENCY_B
, CS_INCIDENT_URGENCIES_TL URGENCY_T
, CSI_INSTANCE_STATUSES CPS
, PER_ALL_PEOPLE_F FND2
, CSI_ITEM_INSTANCES CII
, CS_INCIDENTS_VL_SEC INC
, MTL_SYSTEM_ITEMS_VL KFV
, HZ_PARTY_SITES HZSITE1
, HZ_LOCATIONS HZLOC1
, HZ_PARTY_SITES HZSITE2
, HZ_PARTY_SITE_USES HZUSES2
, HZ_LOCATIONS HZLOC2
, HZ_PARTY_SITES HZSITE3
, HZ_PARTY_SITE_USES HZUSES3
, HZ_LOCATIONS HZLOC3
, JTF_PARTIES_ALL_V PARTY
, HZ_PARTIES SR_CONT_PARTY
, HZ_RELATIONSHIPS HZ_REL
, HZ_CONTACT_POINTS PARTY_CONT
, HZ_CONTACT_POINTS PARTY_CONT1
, HZ_CONTACT_POINTS PARTY_CONT2
, PER_EMPLOYEES_CURRENT_X PEREMPS
, JTF_CUST_ACCOUNTS_ALL_V ACCOUNT
, CS_HZ_SR_CONTACT_POINTS SR_CONT
, HZ_TIMEZONES HZ_TIME
, PER_PHONES PERP
, HR_LOOKUPS HRL
, AR_LOOKUPS ARL
, AR_LOOKUPS ARL2
, AR_LOOKUPS ARL3
, CS_LOOKUPS CSPROB
WHERE 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 DECODE(SR_CONT.CONTACT_TYPE
, 'EMPLOYEE'
, SR_CONT.PARTY_ID
, NULL) = FND2.PERSON_ID(+)
AND TRUNC(SYSDATE) BETWEEN NVL(FND2.EFFECTIVE_START_DATE
, TRUNC(SYSDATE))
AND NVL(FND2.EFFECTIVE_END_DATE
, TRUNC(SYSDATE+1))
AND INC.INCIDENT_URGENCY_ID = URGENCY_B.INCIDENT_URGENCY_ID (+)
AND URGENCY_T.INCIDENT_URGENCY_ID (+) = URGENCY_B.INCIDENT_URGENCY_ID
AND URGENCY_T.LANGUAGE (+) = USERENV('LANG')
AND INC.INCIDENT_SEVERITY_ID = SEV.INCIDENT_SEVERITY_ID
AND INC.CUSTOMER_PRODUCT_ID = CII.INSTANCE_ID (+)
AND CII.INSTANCE_STATUS_ID = CPS.INSTANCE_STATUS_ID (+)
AND INC.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID (+)
AND KFV.ORGANIZATION_ID (+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
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_CONT_PARTY.PARTY_ID (+)
AND SR_CONT.PRIMARY_FLAG (+) = 'Y'
AND INC.TIME_ZONE_ID = HZ_TIME.TIMEZONE_ID (+)
AND INC.INSTALL_SITE_USE_ID = HZSITE1.PARTY_SITE_ID(+)
AND HZSITE1.LOCATION_ID = HZLOC1.LOCATION_ID(+)
AND INC.BILL_TO_SITE_USE_ID = HZUSES2.PARTY_SITE_USE_ID(+)
AND HZUSES2.PARTY_SITE_ID = HZSITE2.PARTY_SITE_ID(+)
AND HZSITE2.LOCATION_ID = HZLOC2.LOCATION_ID(+)
AND INC.SHIP_TO_SITE_USE_ID = HZUSES3.PARTY_SITE_USE_ID(+)
AND HZUSES3.PARTY_SITE_ID = HZSITE3.PARTY_SITE_ID(+)
AND HZSITE3.LOCATION_ID = HZLOC3.LOCATION_ID(+)
AND PERP.PARENT_ID(+) = DECODE(SR_CONT.CONTACT_TYPE
, 'EMPLOYEE'
, SR_CONT.PARTY_ID
, NULL)
AND PERP.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
AND PERP.PHONE_ID(+) = DECODE(SR_CONT.CONTACT_TYPE
, 'EMPLOYEE'
, SR_CONT.CONTACT_POINT_ID
, NULL)
AND ARL.LOOKUP_TYPE (+) = 'PARTY_RELATIONS_TYPE'
AND ARL.LOOKUP_CODE (+) = HZ_REL.RELATIONSHIP_CODE
AND ARL2.LOOKUP_TYPE (+) = 'PHONE_LINE_TYPE'
AND ARL2.LOOKUP_CODE (+) = PARTY_CONT1.PHONE_LINE_TYPE
AND ARL3.LOOKUP_TYPE (+) = 'PHONE_LINE_TYPE'
AND ARL3.LOOKUP_CODE (+) = PARTY_CONT.PHONE_LINE_TYPE
AND HRL.LOOKUP_TYPE (+) = 'PHONE_TYPE'
AND HRL.LOOKUP_CODE (+) = PERP.PHONE_TYPE
AND SR_CONT_PARTY.PARTY_ID = HZ_REL.PARTY_ID (+)
AND HZ_REL.SUBJECT_TABLE_NAME (+)= 'HZ_PARTIES'
AND HZ_REL.OBJECT_TABLE_NAME (+)= 'HZ_PARTIES'
AND HZ_REL.DIRECTIONAL_FLAG(+) = 'F'
AND PARTY_CONT1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PARTY_CONT1.OWNER_TABLE_ID(+) = INC.CUSTOMER_ID
AND PARTY_CONT1.PRIMARY_FLAG (+) = 'Y'
AND PARTY_CONT1.CONTACT_POINT_TYPE (+) = 'PHONE'
AND PARTY_CONT1.STATUS (+) = 'A'
AND PARTY_CONT2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND PARTY_CONT2.OWNER_TABLE_ID(+) = SR_CONT.PARTY_ID
AND PARTY_CONT2.PRIMARY_FLAG (+) = 'Y'
AND PARTY_CONT2.CONTACT_POINT_TYPE (+) = 'EMAIL'
AND PARTY_CONT2.STATUS (+) = 'A'
AND PEREMPS.EMPLOYEE_ID(+) = DECODE(SR_CONT.CONTACT_TYPE
, 'EMPLOYEE'
, SR_CONT.PARTY_ID
, NULL)
AND INC.PROBLEM_CODE = CSPROB.LOOKUP_CODE (+)
AND CSPROB.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'