VIEW: APPS.CS_SR_SERVICE_HISTORY_V

Source

View Text - Preformatted

SELECT INC.INCIDENT_NUMBER, INC.INCIDENT_ID, INC.CUSTOMER_ID, PARTY.PARTY_NAME CUSTOMER_NAME, KFV.CONCATENATED_SEGMENTS PRODUCT_NAME, KFV.DESCRIPTION PRODUCT_DESCRIPTION, INC.INCIDENT_DATE DATE_OPENED, INC.CLOSE_DATE DATE_CLOSED, STATUS.NAME STATUS_NAME, INC.INCIDENT_STATUS_ID, TYPE.NAME TYPE_NAME, INC.INCIDENT_TYPE_ID, INC_TL.SUMMARY, DECODE(INC.CUSTOMER_PRODUCT_ID,'  ',INC.CURRENT_SERIAL_NUMBER,CP.SERIAL_NUMBER) SERIAL_NUMBER, PROB_LOOK.MEANING PROBLEM_CODE_MEANING, RES_LOOK.MEANING RESOLUTION_CODE_MEANING, INC.CUSTOMER_PRODUCT_ID, INC.CP_COMPONENT_ID, INC.CP_SUBCOMPONENT_ID, INC.INVENTORY_ITEM_ID, INC.INV_COMPONENT_ID, INC.INV_SUBCOMPONENT_ID, OBJ_G.OBJECT_CODE GROUP_TYPE, OBJ_G.NAME GROUP_NAME, OBJ.OBJECT_CODE INDIVIDUAL_TYPE, OBJ.NAME INDIVIDUAL_NAME, inc.owner_group_id, inc.incident_owner_id, INC_TL.GROUP_OWNER, INC_TL.OWNER FROM CS_INCIDENTS_B_SEC INC, CS_INCIDENTS_ALL_TL INC_TL, MTL_SYSTEM_ITEMS_KFV KFV, CSI_ITEM_INSTANCES CP, CS_INCIDENT_TYPES_TL TYPE, CS_INCIDENT_STATUSES_TL STATUS, HZ_PARTIES PARTY, CS_LOOKUPS PROB_LOOK, CS_LOOKUPS RES_LOOK, JTF_OBJECTS_VL OBJ, JTF_OBJECTS_VL OBJ_G WHERE INC_TL.INCIDENT_ID = INC.INCIDENT_ID AND INC_TL.LANGUAGE = USERENV('LANG') AND INC.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID (+) AND KFV.ORGANIZATION_ID (+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID AND INC.CUSTOMER_PRODUCT_ID = CP.INSTANCE_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.CUSTOMER_ID = PARTY.PARTY_ID AND INC.PROBLEM_CODE = PROB_LOOK.LOOKUP_CODE(+) AND PROB_LOOK.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE' AND INC.RESOLUTION_CODE = RES_LOOK.LOOKUP_CODE (+) AND RES_LOOK.LOOKUP_TYPE(+) = 'REQUEST_RESOLUTION_CODE' AND INC.GROUP_TYPE = OBJ_G.OBJECT_CODE(+) AND INC.RESOURCE_TYPE = OBJ.OBJECT_CODE(+)

View Text - HTML Formatted

SELECT INC.INCIDENT_NUMBER
, INC.INCIDENT_ID
, INC.CUSTOMER_ID
, PARTY.PARTY_NAME CUSTOMER_NAME
, KFV.CONCATENATED_SEGMENTS PRODUCT_NAME
, KFV.DESCRIPTION PRODUCT_DESCRIPTION
, INC.INCIDENT_DATE DATE_OPENED
, INC.CLOSE_DATE DATE_CLOSED
, STATUS.NAME STATUS_NAME
, INC.INCIDENT_STATUS_ID
, TYPE.NAME TYPE_NAME
, INC.INCIDENT_TYPE_ID
, INC_TL.SUMMARY
, DECODE(INC.CUSTOMER_PRODUCT_ID
, ' '
, INC.CURRENT_SERIAL_NUMBER
, CP.SERIAL_NUMBER) SERIAL_NUMBER
, PROB_LOOK.MEANING PROBLEM_CODE_MEANING
, RES_LOOK.MEANING RESOLUTION_CODE_MEANING
, INC.CUSTOMER_PRODUCT_ID
, INC.CP_COMPONENT_ID
, INC.CP_SUBCOMPONENT_ID
, INC.INVENTORY_ITEM_ID
, INC.INV_COMPONENT_ID
, INC.INV_SUBCOMPONENT_ID
, OBJ_G.OBJECT_CODE GROUP_TYPE
, OBJ_G.NAME GROUP_NAME
, OBJ.OBJECT_CODE INDIVIDUAL_TYPE
, OBJ.NAME INDIVIDUAL_NAME
, INC.OWNER_GROUP_ID
, INC.INCIDENT_OWNER_ID
, INC_TL.GROUP_OWNER
, INC_TL.OWNER
FROM CS_INCIDENTS_B_SEC INC
, CS_INCIDENTS_ALL_TL INC_TL
, MTL_SYSTEM_ITEMS_KFV KFV
, CSI_ITEM_INSTANCES CP
, CS_INCIDENT_TYPES_TL TYPE
, CS_INCIDENT_STATUSES_TL STATUS
, HZ_PARTIES PARTY
, CS_LOOKUPS PROB_LOOK
, CS_LOOKUPS RES_LOOK
, JTF_OBJECTS_VL OBJ
, JTF_OBJECTS_VL OBJ_G
WHERE INC_TL.INCIDENT_ID = INC.INCIDENT_ID
AND INC_TL.LANGUAGE = USERENV('LANG')
AND INC.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID (+)
AND KFV.ORGANIZATION_ID (+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND INC.CUSTOMER_PRODUCT_ID = CP.INSTANCE_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.CUSTOMER_ID = PARTY.PARTY_ID
AND INC.PROBLEM_CODE = PROB_LOOK.LOOKUP_CODE(+)
AND PROB_LOOK.LOOKUP_TYPE(+) = 'REQUEST_PROBLEM_CODE'
AND INC.RESOLUTION_CODE = RES_LOOK.LOOKUP_CODE (+)
AND RES_LOOK.LOOKUP_TYPE(+) = 'REQUEST_RESOLUTION_CODE'
AND INC.GROUP_TYPE = OBJ_G.OBJECT_CODE(+)
AND INC.RESOURCE_TYPE = OBJ.OBJECT_CODE(+)