View: EAM_FORECAST_WORK_ORDER_V

Product:	EAM - Enterprise Asset Management
Description:	This view resolves all foreign keys for forecasted work orders used in eAM maintenance budgeting and forecasting.
Implementation/DBA Data:	APPS.EAM_FORECAST_WORK_ORDER_V

View Text

SELECT JOB.FORECAST_ID
, JOB.WIP_ENTITY_ID
, ENTITY.WIP_ENTITY_NAME
, JOB.ORGANIZATION_ID
, CII.INSTANCE_NUMBER
, CII.INSTANCE_DESCRIPTION
, MSI.CONCATENATED_SEGMENTS
, MSI2.CONCATENATED_SEGMENTS
, LOC.LOCATION_CODES
, WOTYPE.MEANING
, JOB.DESCRIPTION
, JOB.SCHEDULED_START_DATE
, JOB.SCHEDULED_COMPLETION_DATE
, ACTTYPE.MEANING
, ACTCAUSE.MEANING
, BD.DEPARTMENT_CODE
, JOB.CLASS_CODE
, JOB.PLAN_MAINTENANCE
, PPV.PROJECT_NAME
, ACTSOURCE.MEANING
, JOB.PM_SCHEDULE_ID
, PPV.TASK_NAME
FROM EAM_FORECAST_WDJ JOB
, WIP_ENTITIES ENTITY
, MTL_SYSTEM_ITEMS_KFV MSI
, CSI_ITEM_INSTANCES CII
, MTL_EAM_LOCATIONS LOC
, MTL_SYSTEM_ITEMS_KFV MSI2
, BOM_DEPARTMENTS BD
, MTL_PARAMETERS MP
, EAM_ORG_MAINT_DEFAULTS EOMD
, PJM_TASKS_V PPV
, MFG_LOOKUPS WOTYPE
, MFG_LOOKUPS ACTCAUSE
, MFG_LOOKUPS ACTSOURCE
, MFG_LOOKUPS ACTTYPE
WHERE ENTITY.WIP_ENTITY_ID (+) = JOB.WIP_ENTITY_ID
AND ENTITY.ENTITY_TYPE (+) = DECODE(JOB.STATUS_TYPE
, 12
, 7
, 6)
AND JOB.MAINTENANCE_OBJECT_TYPE = 3
AND JOB.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND MSI.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND EOMD.OBJECT_ID(+) = CII.INSTANCE_ID
AND (EOMD.ORGANIZATION_ID IS NULL OR EOMD.ORGANIZATION_ID = JOB.ORGANIZATION_ID)
AND EOMD.OBJECT_TYPE (+) = 50
AND LOC.LOCATION_ID (+) = EOMD.AREA_ID
AND MSI2.INVENTORY_ITEM_ID (+) = JOB.PRIMARY_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID (+) = JOB.OWNING_DEPARTMENT
AND BD.ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND PPV.PROJECT_ID (+) = JOB.PROJECT_ID
AND PPV.TASK_ID (+) = JOB.TASK_ID
AND ACTCAUSE.LOOKUP_TYPE (+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND ACTCAUSE.LOOKUP_CODE (+) = TO_NUMBER(JOB.ACTIVITY_CAUSE)
AND ACTSOURCE.LOOKUP_TYPE (+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND ACTSOURCE.LOOKUP_CODE (+) = TO_NUMBER(JOB.ACTIVITY_SOURCE)
AND ACTTYPE.LOOKUP_TYPE (+) = 'MTL_EAM_ACTIVITY_TYPE'
AND ACTTYPE.LOOKUP_CODE (+) = TO_NUMBER(JOB.ACTIVITY_TYPE)
AND WOTYPE.LOOKUP_TYPE (+) = 'WIP_EAM_WORK_ORDER_TYPE'
AND WOTYPE.LOOKUP_CODE (+) = TO_NUMBER(JOB.WORK_ORDER_TYPE) UNION SELECT JOB.FORECAST_ID
, JOB.WIP_ENTITY_ID
, ENTITY.WIP_ENTITY_NAME
, JOB.ORGANIZATION_ID
, NULL
, NULL
, MSI.CONCATENATED_SEGMENTS
, MSI2.CONCATENATED_SEGMENTS
, NULL
, WOTYPE.MEANING
, JOB.DESCRIPTION
, JOB.SCHEDULED_START_DATE
, JOB.SCHEDULED_COMPLETION_DATE
, ACTTYPE.MEANING
, ACTCAUSE.MEANING
, BD.DEPARTMENT_CODE
, JOB.CLASS_CODE
, JOB.PLAN_MAINTENANCE
, PPV.PROJECT_NAME
, ACTSOURCE.MEANING
, JOB.PM_SCHEDULE_ID
, PPV.TASK_NAME
FROM EAM_FORECAST_WDJ JOB
, WIP_ENTITIES ENTITY
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_SYSTEM_ITEMS_KFV MSI2
, MTL_PARAMETERS MP
, BOM_DEPARTMENTS BD
, PJM_TASKS_V PPV
, MFG_LOOKUPS WOTYPE
, MFG_LOOKUPS ACTCAUSE
, MFG_LOOKUPS ACTSOURCE
, MFG_LOOKUPS ACTTYPE
WHERE ENTITY.WIP_ENTITY_ID (+) = JOB.WIP_ENTITY_ID
AND ENTITY.ENTITY_TYPE(+) = 6
AND MSI.EAM_ITEM_TYPE = 3
AND JOB.MAINTENANCE_OBJECT_TYPE = 2
AND JOB.MAINTENANCE_OBJECT_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND MSI2.INVENTORY_ITEM_ID (+) = JOB.PRIMARY_ITEM_ID
AND MSI2.ORGANIZATION_ID (+) = JOB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID (+) = JOB.OWNING_DEPARTMENT
AND BD.ORGANIZATION_ID = JOB.ORGANIZATION_ID
AND PPV.PROJECT_ID (+) = JOB.PROJECT_ID
AND PPV.TASK_ID = JOB.TASK_ID
AND ACTCAUSE.LOOKUP_TYPE (+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND ACTCAUSE.LOOKUP_CODE (+) = TO_NUMBER(JOB.ACTIVITY_CAUSE)
AND ACTSOURCE.LOOKUP_TYPE (+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND ACTSOURCE.LOOKUP_CODE (+) = TO_NUMBER(JOB.ACTIVITY_SOURCE)
AND ACTTYPE.LOOKUP_TYPE (+) = 'MTL_EAM_ACTIVITY_TYPE'
AND ACTTYPE.LOOKUP_CODE (+) = TO_NUMBER(JOB.ACTIVITY_TYPE)
AND WOTYPE.LOOKUP_TYPE (+) = 'WIP_EAM_WORK_ORDER_TYPE'
AND WOTYPE.LOOKUP_CODE (+) = TO_NUMBER(JOB.WORK_ORDER_TYPE)

Columns