VIEW: APPS.WIP_EAM_ENTITIES_V

Source

View Text - Preformatted

SELECT distinct E.WIP_ENTITY_ID , E.ORGANIZATION_ID , E.WIP_ENTITY_NAME , E.DESCRIPTION , E.ENTITY_TYPE , LU2.MEANING ENTITY_TYPE_MEANING, J.PRIMARY_ITEM_ID, J.ASSET_GROUP_ID, decode(m2.eam_item_type, 1, cii.instance_number, null), B1.DEPARTMENT_CODE, J.CLASS_CODE , J.BOM_REVISION , ewodv.USER_DEFINED_STATUS_ID, ewodv.WORK_ORDER_STATUS, M1.PRIMARY_UOM_CODE , J.DATE_CLOSED, J.LAST_UPDATE_DATE , J.LAST_UPDATED_BY , J.CREATION_DATE , J.CREATED_BY , J.LAST_UPDATE_LOGIN , J.REQUEST_ID , J.PROGRAM_APPLICATION_ID , J.PROGRAM_ID , J.PROGRAM_UPDATE_DATE, J.REBUILD_ITEM_ID, decode(m2.eam_item_type, 3, cii.instance_number, null), J.ESTIMATION_STATUS, LU3.MEANING ESTIMATION_STATUS_MEANING, cii.serial_number FROM MTL_SYSTEM_ITEMS_B_KFV M1 , WIP_DISCRETE_JOBS J, WIP_ENTITIES E, MFG_LOOKUPS LU2 , MFG_LOOKUPS LU3, BOM_DEPARTMENTS B1, csi_item_instances cii, mtl_parameters mp, mtl_system_items_b m2, EAM_WORK_ORDER_DETAILS_V ewodv WHERE J.WIP_ENTITY_ID = E.WIP_ENTITY_ID AND J.ORGANIZATION_ID = E.ORGANIZATION_ID AND J.PRIMARY_ITEM_ID = M1.INVENTORY_ITEM_ID (+) and j.organization_id = m1.organization_id (+) AND LU3.LOOKUP_TYPE='CST_EAM_ESTIMATION_STATUS' AND LU3.LOOKUP_CODE=NVL(J.ESTIMATION_STATUS,1) AND LU2.LOOKUP_TYPE = 'WIP_ENTITY' AND LU2.LOOKUP_CODE = E.ENTITY_TYPE AND E.ENTITY_TYPE IN (6,7) AND J.OWNING_DEPARTMENT = B1.DEPARTMENT_ID(+) AND J.ORGANIZATION_ID = B1.ORGANIZATION_ID (+) and j.maintenance_object_type = 3 and j.maintenance_object_id = cii.instance_id and m2.inventory_item_id = cii.inventory_item_id and m2.organization_id = mp.organization_id and mp.maint_organization_id = j.organization_id and ewodv.wip_entity_id = j.wip_entity_id AND ewodv.organization_id = j.organization_id union all select distinct E.WIP_ENTITY_ID , E.ORGANIZATION_ID , E.WIP_ENTITY_NAME , E.DESCRIPTION , E.ENTITY_TYPE , LU2.MEANING ENTITY_TYPE_MEANING, J.PRIMARY_ITEM_ID, J.ASSET_GROUP_ID, null, B1.DEPARTMENT_CODE, J.CLASS_CODE , J.BOM_REVISION , ewodv.USER_DEFINED_STATUS_ID, ewodv.WORK_ORDER_STATUS, M1.PRIMARY_UOM_CODE , J.DATE_CLOSED, J.LAST_UPDATE_DATE , J.LAST_UPDATED_BY , J.CREATION_DATE , J.CREATED_BY , J.LAST_UPDATE_LOGIN , J.REQUEST_ID , J.PROGRAM_APPLICATION_ID , J.PROGRAM_ID , J.PROGRAM_UPDATE_DATE, J.REBUILD_ITEM_ID, null, J.ESTIMATION_STATUS, LU3.MEANING ESTIMATION_STATUS_MEANING, null FROM MTL_SYSTEM_ITEMS_B_KFV M1 , WIP_DISCRETE_JOBS J, WIP_ENTITIES E, MFG_LOOKUPS LU , MFG_LOOKUPS LU2 , MFG_LOOKUPS LU3, BOM_DEPARTMENTS B1, EAM_WORK_ORDER_DETAILS_V ewodv WHERE J.WIP_ENTITY_ID = E.WIP_ENTITY_ID AND J.ORGANIZATION_ID = E.ORGANIZATION_ID AND J.PRIMARY_ITEM_ID = M1.INVENTORY_ITEM_ID (+) and j.organization_id = m1.organization_id (+) AND LU3.LOOKUP_TYPE='CST_EAM_ESTIMATION_STATUS' AND LU3.LOOKUP_CODE=NVL(J.ESTIMATION_STATUS,1) AND LU2.LOOKUP_TYPE = 'WIP_ENTITY' AND LU2.LOOKUP_CODE = E.ENTITY_TYPE AND E.ENTITY_TYPE IN (6,7) AND J.OWNING_DEPARTMENT = B1.DEPARTMENT_ID(+) AND J.ORGANIZATION_ID = B1.ORGANIZATION_ID (+) and j.maintenance_object_type = 2 and ewodv.wip_entity_id = j.wip_entity_id AND ewodv.organization_id = j.organization_id

View Text - HTML Formatted

SELECT DISTINCT E.WIP_ENTITY_ID
, E.ORGANIZATION_ID
, E.WIP_ENTITY_NAME
, E.DESCRIPTION
, E.ENTITY_TYPE
, LU2.MEANING ENTITY_TYPE_MEANING
, J.PRIMARY_ITEM_ID
, J.ASSET_GROUP_ID
, DECODE(M2.EAM_ITEM_TYPE
, 1
, CII.INSTANCE_NUMBER
, NULL)
, B1.DEPARTMENT_CODE
, J.CLASS_CODE
, J.BOM_REVISION
, EWODV.USER_DEFINED_STATUS_ID
, EWODV.WORK_ORDER_STATUS
, M1.PRIMARY_UOM_CODE
, J.DATE_CLOSED
, J.LAST_UPDATE_DATE
, J.LAST_UPDATED_BY
, J.CREATION_DATE
, J.CREATED_BY
, J.LAST_UPDATE_LOGIN
, J.REQUEST_ID
, J.PROGRAM_APPLICATION_ID
, J.PROGRAM_ID
, J.PROGRAM_UPDATE_DATE
, J.REBUILD_ITEM_ID
, DECODE(M2.EAM_ITEM_TYPE
, 3
, CII.INSTANCE_NUMBER
, NULL)
, J.ESTIMATION_STATUS
, LU3.MEANING ESTIMATION_STATUS_MEANING
, CII.SERIAL_NUMBER
FROM MTL_SYSTEM_ITEMS_B_KFV M1
, WIP_DISCRETE_JOBS J
, WIP_ENTITIES E
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, BOM_DEPARTMENTS B1
, CSI_ITEM_INSTANCES CII
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_B M2
, EAM_WORK_ORDER_DETAILS_V EWODV
WHERE J.WIP_ENTITY_ID = E.WIP_ENTITY_ID
AND J.ORGANIZATION_ID = E.ORGANIZATION_ID
AND J.PRIMARY_ITEM_ID = M1.INVENTORY_ITEM_ID (+)
AND J.ORGANIZATION_ID = M1.ORGANIZATION_ID (+)
AND LU3.LOOKUP_TYPE='CST_EAM_ESTIMATION_STATUS'
AND LU3.LOOKUP_CODE=NVL(J.ESTIMATION_STATUS
, 1)
AND LU2.LOOKUP_TYPE = 'WIP_ENTITY'
AND LU2.LOOKUP_CODE = E.ENTITY_TYPE
AND E.ENTITY_TYPE IN (6
, 7)
AND J.OWNING_DEPARTMENT = B1.DEPARTMENT_ID(+)
AND J.ORGANIZATION_ID = B1.ORGANIZATION_ID (+)
AND J.MAINTENANCE_OBJECT_TYPE = 3
AND J.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND M2.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND M2.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = J.ORGANIZATION_ID
AND EWODV.WIP_ENTITY_ID = J.WIP_ENTITY_ID
AND EWODV.ORGANIZATION_ID = J.ORGANIZATION_ID UNION ALL SELECT DISTINCT E.WIP_ENTITY_ID
, E.ORGANIZATION_ID
, E.WIP_ENTITY_NAME
, E.DESCRIPTION
, E.ENTITY_TYPE
, LU2.MEANING ENTITY_TYPE_MEANING
, J.PRIMARY_ITEM_ID
, J.ASSET_GROUP_ID
, NULL
, B1.DEPARTMENT_CODE
, J.CLASS_CODE
, J.BOM_REVISION
, EWODV.USER_DEFINED_STATUS_ID
, EWODV.WORK_ORDER_STATUS
, M1.PRIMARY_UOM_CODE
, J.DATE_CLOSED
, J.LAST_UPDATE_DATE
, J.LAST_UPDATED_BY
, J.CREATION_DATE
, J.CREATED_BY
, J.LAST_UPDATE_LOGIN
, J.REQUEST_ID
, J.PROGRAM_APPLICATION_ID
, J.PROGRAM_ID
, J.PROGRAM_UPDATE_DATE
, J.REBUILD_ITEM_ID
, NULL
, J.ESTIMATION_STATUS
, LU3.MEANING ESTIMATION_STATUS_MEANING
, NULL
FROM MTL_SYSTEM_ITEMS_B_KFV M1
, WIP_DISCRETE_JOBS J
, WIP_ENTITIES E
, MFG_LOOKUPS LU
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, BOM_DEPARTMENTS B1
, EAM_WORK_ORDER_DETAILS_V EWODV
WHERE J.WIP_ENTITY_ID = E.WIP_ENTITY_ID
AND J.ORGANIZATION_ID = E.ORGANIZATION_ID
AND J.PRIMARY_ITEM_ID = M1.INVENTORY_ITEM_ID (+)
AND J.ORGANIZATION_ID = M1.ORGANIZATION_ID (+)
AND LU3.LOOKUP_TYPE='CST_EAM_ESTIMATION_STATUS'
AND LU3.LOOKUP_CODE=NVL(J.ESTIMATION_STATUS
, 1)
AND LU2.LOOKUP_TYPE = 'WIP_ENTITY'
AND LU2.LOOKUP_CODE = E.ENTITY_TYPE
AND E.ENTITY_TYPE IN (6
, 7)
AND J.OWNING_DEPARTMENT = B1.DEPARTMENT_ID(+)
AND J.ORGANIZATION_ID = B1.ORGANIZATION_ID (+)
AND J.MAINTENANCE_OBJECT_TYPE = 2
AND EWODV.WIP_ENTITY_ID = J.WIP_ENTITY_ID
AND EWODV.ORGANIZATION_ID = J.ORGANIZATION_ID