VIEW: APPS.EAM_WORK_REQUEST_ASSOC_V

Source

View Text - Preformatted

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, cii.instance_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 FROM WIP_EAM_WORK_REQUESTS wr, BOM_DEPARTMENTS bd, FND_USER fu , CSI_ITEM_INSTANCES cii, MTL_SYSTEM_ITEMS_B_KFV msi 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.wip_entity_id is not null 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.instance_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) , wosrv.wip_entity_id , srv.incident_id , wosrv.maintenance_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 FROM (SELECT * FROM EAM_WO_SERVICE_ASSOCIATION WHERE ENABLE_FLAG IS NULL OR ENABLE_FLAG='Y') wosrv, 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 wosrv.service_request_id = srv.incident_id and 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(+)

View Text - HTML Formatted

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
, CII.INSTANCE_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
FROM WIP_EAM_WORK_REQUESTS WR
, BOM_DEPARTMENTS BD
, FND_USER FU
, CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS_B_KFV MSI
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.WIP_ENTITY_ID IS NOT NULL
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.INSTANCE_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)
, WOSRV.WIP_ENTITY_ID
, SRV.INCIDENT_ID
, WOSRV.MAINTENANCE_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
FROM (SELECT *
FROM EAM_WO_SERVICE_ASSOCIATION
WHERE ENABLE_FLAG IS NULL OR ENABLE_FLAG='Y') WOSRV
, 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 WOSRV.SERVICE_REQUEST_ID = SRV.INCIDENT_ID
AND 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(+)