VIEW: APPS.EAM_SERVICE_REQUEST_DETAILS_V

Source

View Text - Preformatted

SELECT cia.incident_id,cia.incident_number,cia.incident_status_id,cis.name status,cia.incident_severity_id,cise.name severity,cia.org_id,cia.inv_organization_id,cia.inventory_item_id,cia.org_id as operating_unit,msibkfv.concatenated_segments as item_name,cia.creation_date as request_date,cia.expected_resolution_date as request_by_date,cia.summary,bd.department_code as department,cia.owning_department_id,cia.status_flag,nvl(type.maintenance_flag,'N') maintenance_flag , cia.maint_organization_id, TO_NUMBER(DECODE(cia.customer_product_id,NULL,(DECODE(cia.inventory_item_id,NULL,NULL,2)),3)), NVL(cia.customer_product_id,cia.inventory_item_id), cii.instance_number FROM cs_incidents_vl_sec cia, cs_incident_statuses_b cis, cs_incident_severities_b cise, bom_departments bd, cs_incident_types_vl_sec type, mtl_system_items_b_kfv msibkfv, csi_item_instances cii WHERE cia.owning_department_id = bd.department_id(+) And cia.maint_organization_id = bd.organization_id (+) and cia.incident_status_id = cis.incident_status_id and cia.incident_severity_id = cise.incident_severity_id and cia.incident_type_id = type.incident_type_id and cia.inventory_item_id = msibkfv.inventory_item_id(+) and cia.inv_organization_id = msibkfv.organization_id(+) and cia.customer_product_id = cii.instance_id(+)

View Text - HTML Formatted

SELECT CIA.INCIDENT_ID
, CIA.INCIDENT_NUMBER
, CIA.INCIDENT_STATUS_ID
, CIS.NAME STATUS
, CIA.INCIDENT_SEVERITY_ID
, CISE.NAME SEVERITY
, CIA.ORG_ID
, CIA.INV_ORGANIZATION_ID
, CIA.INVENTORY_ITEM_ID
, CIA.ORG_ID AS OPERATING_UNIT
, MSIBKFV.CONCATENATED_SEGMENTS AS ITEM_NAME
, CIA.CREATION_DATE AS REQUEST_DATE
, CIA.EXPECTED_RESOLUTION_DATE AS REQUEST_BY_DATE
, CIA.SUMMARY
, BD.DEPARTMENT_CODE AS DEPARTMENT
, CIA.OWNING_DEPARTMENT_ID
, CIA.STATUS_FLAG
, NVL(TYPE.MAINTENANCE_FLAG
, 'N') MAINTENANCE_FLAG
, CIA.MAINT_ORGANIZATION_ID
, TO_NUMBER(DECODE(CIA.CUSTOMER_PRODUCT_ID
, NULL
, (DECODE(CIA.INVENTORY_ITEM_ID
, NULL
, NULL
, 2))
, 3))
, NVL(CIA.CUSTOMER_PRODUCT_ID
, CIA.INVENTORY_ITEM_ID)
, CII.INSTANCE_NUMBER
FROM CS_INCIDENTS_VL_SEC CIA
, CS_INCIDENT_STATUSES_B CIS
, CS_INCIDENT_SEVERITIES_B CISE
, BOM_DEPARTMENTS BD
, CS_INCIDENT_TYPES_VL_SEC TYPE
, MTL_SYSTEM_ITEMS_B_KFV MSIBKFV
, CSI_ITEM_INSTANCES CII
WHERE CIA.OWNING_DEPARTMENT_ID = BD.DEPARTMENT_ID(+)
AND CIA.MAINT_ORGANIZATION_ID = BD.ORGANIZATION_ID (+)
AND CIA.INCIDENT_STATUS_ID = CIS.INCIDENT_STATUS_ID
AND CIA.INCIDENT_SEVERITY_ID = CISE.INCIDENT_SEVERITY_ID
AND CIA.INCIDENT_TYPE_ID = TYPE.INCIDENT_TYPE_ID
AND CIA.INVENTORY_ITEM_ID = MSIBKFV.INVENTORY_ITEM_ID(+)
AND CIA.INV_ORGANIZATION_ID = MSIBKFV.ORGANIZATION_ID(+)
AND CIA.CUSTOMER_PRODUCT_ID = CII.INSTANCE_ID(+)