VIEW: APPS.WIP_EAM_CST_HEADER_V

Source

View Text - Preformatted

SELECT wdj.wip_entity_id, wdj.organization_id, ewodv.USER_DEFINED_STATUS_ID, ewodv.WORK_ORDER_STATUS as job_status, wdj.primary_item_id, wdj.scheduled_completion_date, wdj.asset_group_id, decode(wdj.asset_group_id, null, null, cii.instance_number), m5.meaning as estimation_status, b1.department_code as owning_department, wdj.rebuild_item_id, decode(wdj.asset_group_id, null, cii.instance_number, null), we.wip_entity_name as parent_wip_entity_name, wdj.last_estimation_req_id, wdj.last_estimation_date, wdj.estimation_status, cii.serial_number from wip_discrete_jobs wdj, wip_entities we, bom_departments b1, mfg_lookups m5, csi_item_instances cii, eam_work_order_details_v ewodv where wdj.parent_wip_entity_id = we.wip_entity_id (+) and wdj.organization_id =we.organization_id(+) and wdj.owning_department = b1.department_id (+) and b1.organization_id(+) = wdj.organization_id and decode(wdj.estimation_status,1,1,2,2,3,3,7,7,8,8,1) = m5.lookup_code and m5.lookup_type like 'CST_EAM_ESTIMATION_STATUS' and wdj.maintenance_object_type = 3 and cii.instance_id = wdj.maintenance_object_id and ewodv.wip_entity_id = wdj.wip_entity_id AND ewodv.organization_id = wdj.organization_id union all select wdj.wip_entity_id, wdj.organization_id, wdj.status_type, ewodv.work_order_status as job_status, wdj.primary_item_id, wdj.scheduled_completion_date, wdj.asset_group_id, null, m5.meaning as estimation_status, b1.department_code as owning_department, wdj.rebuild_item_id, null, we.wip_entity_name as parent_wip_entity_name, wdj.last_estimation_req_id, wdj.last_estimation_date, wdj.estimation_status, null from wip_discrete_jobs wdj, wip_entities we, bom_departments b1, mfg_lookups m5, eam_work_order_details_v ewodv where wdj.parent_wip_entity_id = we.wip_entity_id (+) and wdj.organization_id =we.organization_id(+) and wdj.owning_department = b1.department_id (+) and b1.organization_id(+) = wdj.organization_id and decode(wdj.estimation_status,1,1,2,2,3,3,7,7,8,8,1) = m5.lookup_code and m5.lookup_type like 'CST_EAM_ESTIMATION_STATUS' and wdj.maintenance_object_type = 2 and ewodv.wip_entity_id = wdj.wip_entity_id and ewodv.organization_id = wdj.organization_id

View Text - HTML Formatted

SELECT WDJ.WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID
, EWODV.USER_DEFINED_STATUS_ID
, EWODV.WORK_ORDER_STATUS AS JOB_STATUS
, WDJ.PRIMARY_ITEM_ID
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.ASSET_GROUP_ID
, DECODE(WDJ.ASSET_GROUP_ID
, NULL
, NULL
, CII.INSTANCE_NUMBER)
, M5.MEANING AS ESTIMATION_STATUS
, B1.DEPARTMENT_CODE AS OWNING_DEPARTMENT
, WDJ.REBUILD_ITEM_ID
, DECODE(WDJ.ASSET_GROUP_ID
, NULL
, CII.INSTANCE_NUMBER
, NULL)
, WE.WIP_ENTITY_NAME AS PARENT_WIP_ENTITY_NAME
, WDJ.LAST_ESTIMATION_REQ_ID
, WDJ.LAST_ESTIMATION_DATE
, WDJ.ESTIMATION_STATUS
, CII.SERIAL_NUMBER
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, BOM_DEPARTMENTS B1
, MFG_LOOKUPS M5
, CSI_ITEM_INSTANCES CII
, EAM_WORK_ORDER_DETAILS_V EWODV
WHERE WDJ.PARENT_WIP_ENTITY_ID = WE.WIP_ENTITY_ID (+)
AND WDJ.ORGANIZATION_ID =WE.ORGANIZATION_ID(+)
AND WDJ.OWNING_DEPARTMENT = B1.DEPARTMENT_ID (+)
AND B1.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND DECODE(WDJ.ESTIMATION_STATUS
, 1
, 1
, 2
, 2
, 3
, 3
, 7
, 7
, 8
, 8
, 1) = M5.LOOKUP_CODE
AND M5.LOOKUP_TYPE LIKE 'CST_EAM_ESTIMATION_STATUS'
AND WDJ.MAINTENANCE_OBJECT_TYPE = 3
AND CII.INSTANCE_ID = WDJ.MAINTENANCE_OBJECT_ID
AND EWODV.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND EWODV.ORGANIZATION_ID = WDJ.ORGANIZATION_ID UNION ALL SELECT WDJ.WIP_ENTITY_ID
, WDJ.ORGANIZATION_ID
, WDJ.STATUS_TYPE
, EWODV.WORK_ORDER_STATUS AS JOB_STATUS
, WDJ.PRIMARY_ITEM_ID
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.ASSET_GROUP_ID
, NULL
, M5.MEANING AS ESTIMATION_STATUS
, B1.DEPARTMENT_CODE AS OWNING_DEPARTMENT
, WDJ.REBUILD_ITEM_ID
, NULL
, WE.WIP_ENTITY_NAME AS PARENT_WIP_ENTITY_NAME
, WDJ.LAST_ESTIMATION_REQ_ID
, WDJ.LAST_ESTIMATION_DATE
, WDJ.ESTIMATION_STATUS
, NULL
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, BOM_DEPARTMENTS B1
, MFG_LOOKUPS M5
, EAM_WORK_ORDER_DETAILS_V EWODV
WHERE WDJ.PARENT_WIP_ENTITY_ID = WE.WIP_ENTITY_ID (+)
AND WDJ.ORGANIZATION_ID =WE.ORGANIZATION_ID(+)
AND WDJ.OWNING_DEPARTMENT = B1.DEPARTMENT_ID (+)
AND B1.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND DECODE(WDJ.ESTIMATION_STATUS
, 1
, 1
, 2
, 2
, 3
, 3
, 7
, 7
, 8
, 8
, 1) = M5.LOOKUP_CODE
AND M5.LOOKUP_TYPE LIKE 'CST_EAM_ESTIMATION_STATUS'
AND WDJ.MAINTENANCE_OBJECT_TYPE = 2
AND EWODV.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND EWODV.ORGANIZATION_ID = WDJ.ORGANIZATION_ID