View: WIP_EAM_ENTITIES_V

Product:	WIP - Work in Process
Description:	EAM work order information
Implementation/DBA Data:	APPS.WIP_EAM_ENTITIES_V

View Text

SELECT 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 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

Columns