View: EAM_MANAGE_REQS_V

Product:	EAM - Enterprise Asset Management
Description:	It brings all the work requests and all the service requests in open status
Implementation/DBA Data:	APPS.EAM_MANAGE_REQS_V

View Text

SELECT WR.WORK_REQUEST_NUMBER AS REQUEST_NUMBER
, (SELECT ML1.MEANING
FROM MFG_LOOKUPS ML1
WHERE ML1.LOOKUP_TYPE = 'EAM_REQUEST_TYPE'
AND ML1.LOOKUP_CODE = 1) AS REQUEST_TYPE
, (SELECT ML2.MEANING
FROM MFG_LOOKUPS ML2
WHERE ML2.LOOKUP_TYPE = 'WIP_EAM_WORK_REQ_STATUS'
AND WR.WORK_REQUEST_STATUS_ID = ML2.LOOKUP_CODE) AS STATUS
, WR.ASSET_NUMBER AS ASSET_NUMBER
, MSI.CONCATENATED_SEGMENTS AS ASSET_GROUP
, BD.DEPARTMENT_CODE AS DEPARTMENT_CODE
, WR.EXPECTED_RESOLUTION_DATE AS EXP_RESOLUTION_DATE
, WR.DESCRIPTION AS DESCRIPTION
, FU.USER_NAME AS ORIGINATOR
, WR.CREATION_DATE AS REPORTED_DATE
, (SELECT ML3.MEANING
FROM MFG_LOOKUPS ML3
WHERE ML3.LOOKUP_TYPE = 'MTL_EAM_ASSET_TYPE'
AND MSI.EAM_ITEM_TYPE = ML3.LOOKUP_CODE) AS ASSET_TYPE
, WR.WIP_ENTITY_ID AS WIP_ENTITY_ID
, WR.WORK_REQUEST_ID AS REQUEST_ID
, WR.ORGANIZATION_ID AS ORGANIZATION_ID
, BD.DEPARTMENT_ID AS DEPARTMENT_ID
, WR.WORK_REQUEST_CREATED_BY AS WR_CREATED_BY
, WR.WORK_REQUEST_STATUS_ID AS WR_STATUS_ID
, WR.WORK_REQUEST_PRIORITY_ID AS WR_PRIORITY_ID
, WR.WORK_REQUEST_TYPE_ID AS WR_TYPE_ID
, TO_NUMBER(NULL) AS SR_CREATED_BY
, TO_NUMBER(NULL) AS SR_STATUS_ID
, TO_NUMBER(NULL) AS SR_PRIORITY_ID
, TO_NUMBER(NULL) AS SR_TYPE_ID
, TO_CHAR(NULL) AS SR_CUSTOMER_TYPE
, TO_NUMBER(NULL) AS SR_CUSTOMER_ID
, TO_CHAR(NULL) AS SR_CUSTOMER_NUMBER
, TO_NUMBER(NULL) AS SR_ACCOUNT_ID
, TO_CHAR(NULL) AS SR_CONTACT_TYPE
, TO_NUMBER(NULL) AS SR_CONTACT_PARTY_ID
, TO_CHAR(NULL) AS SR_CONTACT_NUMBER
, (SELECT ML4.LOOKUP_CODE
FROM MFG_LOOKUPS ML4
WHERE ML4.LOOKUP_TYPE = 'EAM_REQUEST_TYPE'
AND ML4.LOOKUP_CODE = 1) AS REQUEST_TYPE_CODE
, 'Y' AS SR_CLOSE_FLAG
, MSI.EAM_ITEM_TYPE AS ASSET_TYPE_CODE
, TO_CHAR(NULL) AS MAINTENANCE_FLAG
, TO_NUMBER(NULL) AS ORG_ID
, WR.MAINTENANCE_OBJECT_TYPE AS MAINTENANCE_OBJECT_TYPE
, WR.MAINTENANCE_OBJECT_ID AS MAINTENANCE_OBJECT_ID
, CII.INSTANCE_NUMBER
FROM WIP_EAM_WORK_REQUESTS WR
, BOM_DEPARTMENTS BD
, FND_USER FU
, MTL_SYSTEM_ITEMS_B_KFV MSI
, CSI_ITEM_INSTANCES CII
WHERE WR.ORGANIZATION_ID = BD.ORGANIZATION_ID (+)
AND WR.WORK_REQUEST_OWNING_DEPT = BD.DEPARTMENT_ID (+)
AND WR.WORK_REQUEST_CREATED_BY = FU.USER_ID
AND WR.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID(+)
AND CII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND CII.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+) UNION ALL SELECT SRV.INCIDENT_NUMBER
, ( SELECT ML5.MEANING
FROM MFG_LOOKUPS ML5
WHERE ML5.LOOKUP_TYPE = 'EAM_REQUEST_TYPE'
AND ML5.LOOKUP_CODE = 2)
, ( SELECT STATUS.NAME
FROM CS_INCIDENT_STATUSES_TL STATUS
WHERE STATUS.INCIDENT_STATUS_ID = SRV.INCIDENT_STATUS_ID
AND STATUS.LANGUAGE = USERENV('LANG') )
, CII.SERIAL_NUMBER
, MSI.CONCATENATED_SEGMENTS
, BD.DEPARTMENT_CODE
, SRV.EXPECTED_RESOLUTION_DATE
, TL.SUMMARY
, TL.OWNER
, SRV.INCIDENT_DATE
, ( SELECT ML6.MEANING
FROM MFG_LOOKUPS ML6
WHERE ML6.LOOKUP_TYPE = 'MTL_EAM_ASSET_TYPE'
AND MSI.EAM_ITEM_TYPE = ML6.LOOKUP_CODE)
, TO_NUMBER(NULL)
, SRV.INCIDENT_ID
, SRV.MAINT_ORGANIZATION_ID
, BD.DEPARTMENT_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, SRV.INCIDENT_OWNER_ID
, SRV.INCIDENT_STATUS_ID
, SRV.INCIDENT_SEVERITY_ID
, SRV.INCIDENT_TYPE_ID
, SRV.CALLER_TYPE
, SRV.CUSTOMER_ID
, PARTY.PARTY_NUMBER CUSTOMER_NUMBER
, SRV.ACCOUNT_ID
, SR_CONT.CONTACT_TYPE
, SR_CONT.PARTY_ID CONTACT_PARTY_ID
, SR_PARTY.PARTY_NUMBER CONTACT_NUMBER
, ( SELECT ML7.LOOKUP_CODE
FROM MFG_LOOKUPS ML7
WHERE ML7.LOOKUP_TYPE = 'EAM_REQUEST_TYPE'
AND ML7.LOOKUP_CODE = 2 )
, STATUS.CLOSE_FLAG
, MSI.EAM_ITEM_TYPE
, NVL(TYPE.MAINTENANCE_FLAG
, 'N') MAINTENANCE_FLAG
, SRV.ORG_ID AS ORG_ID
, TO_NUMBER(DECODE(SRV.CUSTOMER_PRODUCT_ID
, NULL
, (DECODE(SRV.INVENTORY_ITEM_ID
, NULL
, NULL
, 2))
, 3)) AS MAINTENANCE_OBJECT_TYPE
, NVL(SRV.CUSTOMER_PRODUCT_ID
, SRV.INVENTORY_ITEM_ID) AS MAINTENANCE_OBJECT_ID
, CII.INSTANCE_NUMBER AS INSTANCE_NUMBER
FROM CS_INCIDENTS_B_SEC SRV
, CS_INCIDENTS_VL_SEC TL
, MTL_SYSTEM_ITEMS_KFV MSI
, HZ_PARTIES PARTY
, HZ_PARTIES SR_PARTY
, CS_HZ_SR_CONTACT_POINTS SR_CONT
, BOM_DEPARTMENTS BD
, CS_INCIDENT_TYPES_VL_SEC TYPE
, CS_INCIDENT_STATUSES_B STATUS
, CSI_ITEM_INSTANCES CII
WHERE SRV.INCIDENT_ID = TL.INCIDENT_ID
AND TL.LANGUAGE = USERENV('LANG')
AND SRV.CUSTOMER_ID = PARTY.PARTY_ID (+)
AND SR_CONT.INCIDENT_ID (+) = SRV.INCIDENT_ID
AND SR_CONT.PARTY_ID = SR_PARTY.PARTY_ID (+)
AND SR_CONT.PRIMARY_FLAG (+) = 'Y'
AND SRV.INCIDENT_STATUS_ID = STATUS.INCIDENT_STATUS_ID
AND SRV.INCIDENT_TYPE_ID = TYPE.INCIDENT_TYPE_ID
AND SRV.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND SRV.INV_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND SRV.MAINT_ORGANIZATION_ID = BD.ORGANIZATION_ID(+)
AND SRV.OWNING_DEPARTMENT_ID = BD.DEPARTMENT_ID(+)
AND SRV.CUSTOMER_PRODUCT_ID = CII.INSTANCE_ID(+)

Columns