VIEW: APPS.EAM_CFR_WORK_ORDER_V

Source

View Text - Preformatted

SELECT DISTINCT WDJ.WIP_ENTITY_ID , WE.WIP_ENTITY_NAME, WE.ENTITY_TYPE, WDJ.ORGANIZATION_ID, WDJ.DESCRIPTION, NVL(WDJ.ASSET_GROUP_ID,WDJ.REBUILD_ITEM_ID) AS ASSET_GROUP_ID, MSIK1.CONCATENATED_SEGMENTS AS ASSET_GROUP, MSIK1.DESCRIPTION AS ASSET_GROUP_DESCRIPTION, CII.INSTANCE_ID AS INSTANCE_ID, CII.INSTANCE_NUMBER AS ASSET_NUMBER, CII.SERIAL_NUMBER AS ASSET_SERIAL_NUMBER, CII.INSTANCE_DESCRIPTION ASSET_NUMBER_DESCRIPTION, MSIK1.eam_item_type, ML16.meaning as asset_type_disp, WDJ.PRIMARY_ITEM_ID ASSET_ACTIVITY_ID, MSIK2.CONCATENATED_SEGMENTS AS ASSET_ACTIVITY, MSIK2.DESCRIPTION AS ASSET_ACTIVITY_DESCRIPTION, WDJ.CLASS_CODE, WDJ.STATUS_TYPE, ML1.MEANING STATUS_TYPE_DISP, WDJ.PM_SCHEDULE_ID, WDJ.MANUAL_REBUILD_FLAG, ML2.MEANING AS MANUAL_REBUILD_FLAG_DISP, WDJ.WORK_ORDER_TYPE, ML3.MEANING WORK_ORDER_TYPE_DISP, WDJ.MATERIAL_ACCOUNT, WDJ.MATERIAL_OVERHEAD_ACCOUNT, WDJ.RESOURCE_ACCOUNT, WDJ.OUTSIDE_PROCESSING_ACCOUNT, WDJ.MATERIAL_VARIANCE_ACCOUNT, WDJ.RESOURCE_VARIANCE_ACCOUNT, WDJ.OUTSIDE_PROC_VARIANCE_ACCOUNT, WDJ.STD_COST_ADJUSTMENT_ACCOUNT, WDJ.OVERHEAD_ACCOUNT, WDJ.OVERHEAD_VARIANCE_ACCOUNT, WDJ.SCHEDULED_START_DATE, WDJ.SCHEDULED_COMPLETION_DATE, WDJ.DATE_RELEASED, WDJ.DATE_COMPLETED, WDJ.DATE_CLOSED, WDJ.OWNING_DEPARTMENT, BD.DEPARTMENT_CODE, WDJ.ACTIVITY_TYPE, ML4.MEANING ACTIVITY_TYPE_DISP, WDJ.ACTIVITY_CAUSE, ML5.MEANING ACTIVITY_CAUSE_DISP, WDJ.PRIORITY, ML6.MEANING PRIORITY_DISP, WDJ.REQUESTED_START_DATE, WDJ.DUE_DATE, WDJ.ESTIMATION_STATUS, ML20.MEANING AS ESTIMATION_STATUS_DISP, WDJ.NOTIFICATION_REQUIRED, ML7.MEANING AS NOTIFICATION_REQUIRED_DISP, WDJ.SHUTDOWN_TYPE, ML8.MEANING SHUTDOWN_TYPE_DISP, EASH.START_DATE, EASH.END_DATE, WDJ.TAGOUT_REQUIRED, ML9.MEANING AS TAGOUT_REQUIRED_DISP, WDJ.PLAN_MAINTENANCE, ML10.MEANING AS PLAN_MAINTENANCE_DISP, WDJ.FIRM_PLANNED_FLAG, ML11.MEANING AS FIRM_PLANNED_FLAG_DISP, WDJ.SCHEDULE_GROUP_ID, WSG.SCHEDULE_GROUP_NAME, PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID) PROJECT_NAME, WDJ.PROJECT_ID, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID) TASK_NUMBER, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID) TASK_NAME, WDJ.TASK_ID, WDJ.PARENT_WIP_ENTITY_ID, WDJ.BOM_REFERENCE_ID, WDJ.ROUTING_REFERENCE_ID, WDJ.COMMON_BOM_SEQUENCE_ID, WDJ.COMMON_ROUTING_SEQUENCE_ID, WDJ.ALTERNATE_BOM_DESIGNATOR, WDJ.BOM_REVISION, WDJ.BOM_REVISION_DATE, WDJ.ALTERNATE_ROUTING_DESIGNATOR, WDJ.ROUTING_REVISION, WDJ.ROUTING_REVISION_DATE, WDJ.COMPLETION_SUBINVENTORY, WDJ.COMPLETION_LOCATOR_ID, WDJ.LOT_NUMBER, WDJ.DEMAND_CLASS, WDJ.ISSUE_ZERO_COST_FLAG, ML12.MEANING AS ISSUE_ZERO_COST_FLAG_DISP, WDJ.ATTRIBUTE_CATEGORY, WDJ.ATTRIBUTE1, WDJ.ATTRIBUTE2, WDJ.ATTRIBUTE3, WDJ.ATTRIBUTE4, WDJ.ATTRIBUTE5, WDJ.ATTRIBUTE6, WDJ.ATTRIBUTE7, WDJ.ATTRIBUTE8, WDJ.ATTRIBUTE9, WDJ.ATTRIBUTE10, WDJ.ATTRIBUTE11, WDJ.ATTRIBUTE12, WDJ.ATTRIBUTE13, WDJ.ATTRIBUTE14, WDJ.ATTRIBUTE15, WDJ.MAINTENANCE_OBJECT_SOURCE, WDJ.MAINTENANCE_OBJECT_TYPE, WDJ.MAINTENANCE_OBJECT_ID, WDJ.MATERIAL_ISSUE_BY_MO, ML13.MEANING AS MATERIAL_ISSUE_BY_MO_DISP, WDJ.ACTIVITY_SOURCE, ML15.MEANING AS ACTIVITY_SOURCE_DISP, WDJ.EAM_LINEAR_LOCATION_ID, PL.BUILDING||PL.FLOOR||PL.OFFICE AS LOCATION_NAME, PL.LOCATION_CODE AS LOCATION_CODE, CII.PN_LOCATION_ID, EAM_COMMON_UTILITIES_PVT.GET_ASSET_AREA(CII.INSTANCE_ID, WDJ.ORGANIZATION_ID) AS AREA, EWOD.PENDING_FLAG, EWOD.MATERIAL_SHORTAGE_CHECK_DATE, EWOD.MATERIAL_SHORTAGE_FLAG, ML14.MEANING AS MATERIAL_SHORTAGE_DISP, EWOD.WORKFLOW_TYPE, ML18.MEANING AS WORKFLOW_TYPE_DISP, EWOD.WARRANTY_CLAIM_STATUS, ML19.MEANING AS WARRANTY_CLAIM_STATUS_DISP, EWOD.ASSIGNMENT_COMPLETE, ML17.MEANING as ASSIGNMENT_COMPLETE_DISP, EWOD.CYCLE_ID, EWOD.SEQ_ID, EWOD.DS_SCHEDULED_FLAG, EWOD.USER_DEFINED_STATUS_ID, DECODE(EWOD.PENDING_FLAG, 'Y', EWSV.WORK_ORDER_STATUS || ' - ' || FND_MESSAGE.GET_STRING('EAM','EAM_PENDING_TEXT'),EWSV.WORK_ORDER_STATUS) , EWOD.PM_SUGGESTED_START_DATE, EWOD.PM_SUGGESTED_END_DATE , EWOD.PM_BASE_METER_READING, EWOD.PM_BASE_METER , CCT.Name FROM WIP_DISCRETE_JOBS WDJ, WIP_ENTITIES WE, MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_KFV MSIK1, MTL_SYSTEM_ITEMS_KFV MSIK2, CSI_ITEM_INSTANCES CII, WIP_SCHEDULE_GROUPS WSG, BOM_DEPARTMENTS BD, PN_LOCATIONS_ALL PL, MFG_LOOKUPS ML1, MFG_LOOKUPS ML2, MFG_LOOKUPS ML3, MFG_LOOKUPS ML4, MFG_LOOKUPS ML5, MFG_LOOKUPS ML6, MFG_LOOKUPS ML7, MFG_LOOKUPS ML8, MFG_LOOKUPS ML9, MFG_LOOKUPS ML10, MFG_LOOKUPS ML11, MFG_LOOKUPS ML12, MFG_LOOKUPS ML13, MFG_LOOKUPS ML14, MFG_LOOKUPS ML15, MFG_LOOKUPS ML16, MFG_LOOKUPS ML17, MFG_LOOKUPS ML18, MFG_LOOKUPS ML19, MFG_LOOKUPS ML20, EAM_WORK_ORDER_DETAILS EWOD, EAM_WO_STATUSES_V EWSV, EAM_ASSET_STATUS_HISTORY EASH, CSI_COUNTERS_TL CCT WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID AND WE.ENTITY_TYPE IN (6, 7) AND WDJ.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID AND WDJ.MAINTENANCE_OBJECT_TYPE IN (2,3) AND decode(WDJ.MAINTENANCE_OBJECT_TYPE,3,WDJ.MAINTENANCE_OBJECT_ID,-999) = CII.INSTANCE_ID (+) AND MSIK1.INVENTORY_ITEM_ID = NVL( WDJ.ASSET_GROUP_ID, WDJ.REBUILD_ITEM_ID) AND MSIK1.ORGANIZATION_ID = MP.ORGANIZATION_ID AND MSIK2.INVENTORY_ITEM_ID (+) = WDJ.PRIMARY_ITEM_ID AND MSIK2.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT AND CII.PN_LOCATION_ID = PL.LOCATION_ID(+) AND SYSDATE >= NVL(PL.ACTIVE_START_DATE, SYSDATE) AND SYSDATE <= NVL(PL.ACTIVE_END_DATE, SYSDATE) AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS' AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE AND ML2.LOOKUP_TYPE = 'SYS_YES_NO' AND ML2.LOOKUP_CODE = DECODE(NVL(WDJ.MANUAL_REBUILD_FLAG,'N'), 'Y', 1, 2) AND ML3.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE AND ML3.LOOKUP_TYPE(+) = 'WIP_EAM_WORK_ORDER_TYPE' AND ML4.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE AND ML4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE' AND ML5.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE AND ML5.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE' AND ML6.LOOKUP_CODE(+) = WDJ.PRIORITY AND ML6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY' AND ML7.LOOKUP_TYPE = 'SYS_YES_NO' AND ML7.LOOKUP_CODE = DECODE(NVL(WDJ.NOTIFICATION_REQUIRED,'N'), 'Y', 1, 2) AND ML8.LOOKUP_CODE (+) = WDJ.SHUTDOWN_TYPE AND ML8.LOOKUP_TYPE (+) = 'BOM_EAM_SHUTDOWN_TYPE' AND ML9.LOOKUP_TYPE = 'SYS_YES_NO' AND ML9.LOOKUP_CODE = DECODE(NVL(WDJ.TAGOUT_REQUIRED,'N'), 'Y', 1, 2) AND ML10.LOOKUP_TYPE = 'SYS_YES_NO' AND ML10.LOOKUP_CODE = DECODE(NVL(WDJ.PLAN_MAINTENANCE,'N'), 'Y', 1, 2) AND ML11.LOOKUP_TYPE = 'SYS_YES_NO' AND ML11.LOOKUP_CODE = WDJ.FIRM_PLANNED_FLAG AND ML12.LOOKUP_TYPE = 'SYS_YES_NO' AND ML12.LOOKUP_CODE = DECODE(NVL(WDJ.ISSUE_ZERO_COST_FLAG,'N'), 'Y', 1, 2) AND ML13.LOOKUP_TYPE = 'SYS_YES_NO' AND ML13.LOOKUP_CODE = DECODE(NVL(WDJ.MATERIAL_ISSUE_BY_MO,'N'), 'Y', 1, 2) AND ML14.ENABLED_FLAG (+) = 'Y' AND ML14.LOOKUP_TYPE (+) ='EAM_MATERIAL_SHORTAGE' AND ML14.LOOKUP_CODE (+) = EWOD.MATERIAL_SHORTAGE_FLAG AND ML15.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE' AND ML15.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE AND ML16.LOOKUP_TYPE = 'MTL_EAM_ASSET_TYPE' AND ML16.LOOKUP_CODE = MSIK1.eam_item_type AND ML17.LOOKUP_TYPE = 'SYS_YES_NO' AND ML17.LOOKUP_CODE = DECODE(NVL(EWOD.ASSIGNMENT_COMPLETE,'N'), 'Y', 1, 2) AND ML18.LOOKUP_TYPE(+) = 'EAM_WORKFLOW_TYPE' AND ML18.LOOKUP_CODE(+) = EWOD.WORKFLOW_TYPE AND ML19.LOOKUP_TYPE(+) = 'EAM_WARRANTY_STATUS' AND ML19.LOOKUP_CODE(+) = EWOD.WARRANTY_CLAIM_STATUS and ML20.lookup_type = 'CST_EAM_ESTIMATION_STATUS' and ML20.lookup_code = nvl (wdj.estimation_status , 1 ) AND WDJ.WIP_ENTITY_ID = EWOD.WIP_ENTITY_ID AND EWOD.USER_DEFINED_STATUS_ID = EWSV.STATUS_ID AND WDJ.WIP_ENTITY_ID = EASH.WIP_ENTITY_ID (+) AND WDJ.ORGANIZATION_ID = EASH.ORGANIZATION_ID (+) AND EASH.OPERATION_SEQ_NUM IS NULL AND EASH.ENABLE_flag (+) = 'Y' AND EWOD.PM_BASE_METER = CCT.COUNTER_ID(+) AND cct.language(+) = userenv('LANG')

View Text - HTML Formatted

SELECT DISTINCT WDJ.WIP_ENTITY_ID
, WE.WIP_ENTITY_NAME
, WE.ENTITY_TYPE
, WDJ.ORGANIZATION_ID
, WDJ.DESCRIPTION
, NVL(WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID) AS ASSET_GROUP_ID
, MSIK1.CONCATENATED_SEGMENTS AS ASSET_GROUP
, MSIK1.DESCRIPTION AS ASSET_GROUP_DESCRIPTION
, CII.INSTANCE_ID AS INSTANCE_ID
, CII.INSTANCE_NUMBER AS ASSET_NUMBER
, CII.SERIAL_NUMBER AS ASSET_SERIAL_NUMBER
, CII.INSTANCE_DESCRIPTION ASSET_NUMBER_DESCRIPTION
, MSIK1.EAM_ITEM_TYPE
, ML16.MEANING AS ASSET_TYPE_DISP
, WDJ.PRIMARY_ITEM_ID ASSET_ACTIVITY_ID
, MSIK2.CONCATENATED_SEGMENTS AS ASSET_ACTIVITY
, MSIK2.DESCRIPTION AS ASSET_ACTIVITY_DESCRIPTION
, WDJ.CLASS_CODE
, WDJ.STATUS_TYPE
, ML1.MEANING STATUS_TYPE_DISP
, WDJ.PM_SCHEDULE_ID
, WDJ.MANUAL_REBUILD_FLAG
, ML2.MEANING AS MANUAL_REBUILD_FLAG_DISP
, WDJ.WORK_ORDER_TYPE
, ML3.MEANING WORK_ORDER_TYPE_DISP
, WDJ.MATERIAL_ACCOUNT
, WDJ.MATERIAL_OVERHEAD_ACCOUNT
, WDJ.RESOURCE_ACCOUNT
, WDJ.OUTSIDE_PROCESSING_ACCOUNT
, WDJ.MATERIAL_VARIANCE_ACCOUNT
, WDJ.RESOURCE_VARIANCE_ACCOUNT
, WDJ.OUTSIDE_PROC_VARIANCE_ACCOUNT
, WDJ.STD_COST_ADJUSTMENT_ACCOUNT
, WDJ.OVERHEAD_ACCOUNT
, WDJ.OVERHEAD_VARIANCE_ACCOUNT
, WDJ.SCHEDULED_START_DATE
, WDJ.SCHEDULED_COMPLETION_DATE
, WDJ.DATE_RELEASED
, WDJ.DATE_COMPLETED
, WDJ.DATE_CLOSED
, WDJ.OWNING_DEPARTMENT
, BD.DEPARTMENT_CODE
, WDJ.ACTIVITY_TYPE
, ML4.MEANING ACTIVITY_TYPE_DISP
, WDJ.ACTIVITY_CAUSE
, ML5.MEANING ACTIVITY_CAUSE_DISP
, WDJ.PRIORITY
, ML6.MEANING PRIORITY_DISP
, WDJ.REQUESTED_START_DATE
, WDJ.DUE_DATE
, WDJ.ESTIMATION_STATUS
, ML20.MEANING AS ESTIMATION_STATUS_DISP
, WDJ.NOTIFICATION_REQUIRED
, ML7.MEANING AS NOTIFICATION_REQUIRED_DISP
, WDJ.SHUTDOWN_TYPE
, ML8.MEANING SHUTDOWN_TYPE_DISP
, EASH.START_DATE
, EASH.END_DATE
, WDJ.TAGOUT_REQUIRED
, ML9.MEANING AS TAGOUT_REQUIRED_DISP
, WDJ.PLAN_MAINTENANCE
, ML10.MEANING AS PLAN_MAINTENANCE_DISP
, WDJ.FIRM_PLANNED_FLAG
, ML11.MEANING AS FIRM_PLANNED_FLAG_DISP
, WDJ.SCHEDULE_GROUP_ID
, WSG.SCHEDULE_GROUP_NAME
, PJM_PROJECT.ALL_PROJ_IDTONUM(WDJ.PROJECT_ID) PROJECT_NUMBER
, PJM_PROJECT.ALL_PROJ_IDTONAME(WDJ.PROJECT_ID) PROJECT_NAME
, WDJ.PROJECT_ID
, PJM_PROJECT.ALL_TASK_IDTONUM(WDJ.TASK_ID) TASK_NUMBER
, PJM_PROJECT.ALL_TASK_IDTONAME(WDJ.TASK_ID) TASK_NAME
, WDJ.TASK_ID
, WDJ.PARENT_WIP_ENTITY_ID
, WDJ.BOM_REFERENCE_ID
, WDJ.ROUTING_REFERENCE_ID
, WDJ.COMMON_BOM_SEQUENCE_ID
, WDJ.COMMON_ROUTING_SEQUENCE_ID
, WDJ.ALTERNATE_BOM_DESIGNATOR
, WDJ.BOM_REVISION
, WDJ.BOM_REVISION_DATE
, WDJ.ALTERNATE_ROUTING_DESIGNATOR
, WDJ.ROUTING_REVISION
, WDJ.ROUTING_REVISION_DATE
, WDJ.COMPLETION_SUBINVENTORY
, WDJ.COMPLETION_LOCATOR_ID
, WDJ.LOT_NUMBER
, WDJ.DEMAND_CLASS
, WDJ.ISSUE_ZERO_COST_FLAG
, ML12.MEANING AS ISSUE_ZERO_COST_FLAG_DISP
, WDJ.ATTRIBUTE_CATEGORY
, WDJ.ATTRIBUTE1
, WDJ.ATTRIBUTE2
, WDJ.ATTRIBUTE3
, WDJ.ATTRIBUTE4
, WDJ.ATTRIBUTE5
, WDJ.ATTRIBUTE6
, WDJ.ATTRIBUTE7
, WDJ.ATTRIBUTE8
, WDJ.ATTRIBUTE9
, WDJ.ATTRIBUTE10
, WDJ.ATTRIBUTE11
, WDJ.ATTRIBUTE12
, WDJ.ATTRIBUTE13
, WDJ.ATTRIBUTE14
, WDJ.ATTRIBUTE15
, WDJ.MAINTENANCE_OBJECT_SOURCE
, WDJ.MAINTENANCE_OBJECT_TYPE
, WDJ.MAINTENANCE_OBJECT_ID
, WDJ.MATERIAL_ISSUE_BY_MO
, ML13.MEANING AS MATERIAL_ISSUE_BY_MO_DISP
, WDJ.ACTIVITY_SOURCE
, ML15.MEANING AS ACTIVITY_SOURCE_DISP
, WDJ.EAM_LINEAR_LOCATION_ID
, PL.BUILDING||PL.FLOOR||PL.OFFICE AS LOCATION_NAME
, PL.LOCATION_CODE AS LOCATION_CODE
, CII.PN_LOCATION_ID
, EAM_COMMON_UTILITIES_PVT.GET_ASSET_AREA(CII.INSTANCE_ID
, WDJ.ORGANIZATION_ID) AS AREA
, EWOD.PENDING_FLAG
, EWOD.MATERIAL_SHORTAGE_CHECK_DATE
, EWOD.MATERIAL_SHORTAGE_FLAG
, ML14.MEANING AS MATERIAL_SHORTAGE_DISP
, EWOD.WORKFLOW_TYPE
, ML18.MEANING AS WORKFLOW_TYPE_DISP
, EWOD.WARRANTY_CLAIM_STATUS
, ML19.MEANING AS WARRANTY_CLAIM_STATUS_DISP
, EWOD.ASSIGNMENT_COMPLETE
, ML17.MEANING AS ASSIGNMENT_COMPLETE_DISP
, EWOD.CYCLE_ID
, EWOD.SEQ_ID
, EWOD.DS_SCHEDULED_FLAG
, EWOD.USER_DEFINED_STATUS_ID
, DECODE(EWOD.PENDING_FLAG
, 'Y'
, EWSV.WORK_ORDER_STATUS || ' - ' || FND_MESSAGE.GET_STRING('EAM'
, 'EAM_PENDING_TEXT')
, EWSV.WORK_ORDER_STATUS)
, EWOD.PM_SUGGESTED_START_DATE
, EWOD.PM_SUGGESTED_END_DATE
, EWOD.PM_BASE_METER_READING
, EWOD.PM_BASE_METER
, CCT.NAME
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ENTITIES WE
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_KFV MSIK1
, MTL_SYSTEM_ITEMS_KFV MSIK2
, CSI_ITEM_INSTANCES CII
, WIP_SCHEDULE_GROUPS WSG
, BOM_DEPARTMENTS BD
, PN_LOCATIONS_ALL PL
, MFG_LOOKUPS ML1
, MFG_LOOKUPS ML2
, MFG_LOOKUPS ML3
, MFG_LOOKUPS ML4
, MFG_LOOKUPS ML5
, MFG_LOOKUPS ML6
, MFG_LOOKUPS ML7
, MFG_LOOKUPS ML8
, MFG_LOOKUPS ML9
, MFG_LOOKUPS ML10
, MFG_LOOKUPS ML11
, MFG_LOOKUPS ML12
, MFG_LOOKUPS ML13
, MFG_LOOKUPS ML14
, MFG_LOOKUPS ML15
, MFG_LOOKUPS ML16
, MFG_LOOKUPS ML17
, MFG_LOOKUPS ML18
, MFG_LOOKUPS ML19
, MFG_LOOKUPS ML20
, EAM_WORK_ORDER_DETAILS EWOD
, EAM_WO_STATUSES_V EWSV
, EAM_ASSET_STATUS_HISTORY EASH
, CSI_COUNTERS_TL CCT
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WE.ENTITY_TYPE IN (6
, 7)
AND WDJ.ORGANIZATION_ID = MP.MAINT_ORGANIZATION_ID
AND WDJ.MAINTENANCE_OBJECT_TYPE IN (2
, 3)
AND DECODE(WDJ.MAINTENANCE_OBJECT_TYPE
, 3
, WDJ.MAINTENANCE_OBJECT_ID
, -999) = CII.INSTANCE_ID (+)
AND MSIK1.INVENTORY_ITEM_ID = NVL( WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID)
AND MSIK1.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSIK2.INVENTORY_ITEM_ID (+) = WDJ.PRIMARY_ITEM_ID
AND MSIK2.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID
AND WSG.SCHEDULE_GROUP_ID(+) = WDJ.SCHEDULE_GROUP_ID
AND WSG.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND BD.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID
AND BD.DEPARTMENT_ID(+) = WDJ.OWNING_DEPARTMENT
AND CII.PN_LOCATION_ID = PL.LOCATION_ID(+)
AND SYSDATE >= NVL(PL.ACTIVE_START_DATE
, SYSDATE)
AND SYSDATE <= NVL(PL.ACTIVE_END_DATE
, SYSDATE)
AND ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND ML2.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML2.LOOKUP_CODE = DECODE(NVL(WDJ.MANUAL_REBUILD_FLAG
, 'N')
, 'Y'
, 1
, 2)
AND ML3.LOOKUP_CODE(+) = WDJ.WORK_ORDER_TYPE
AND ML3.LOOKUP_TYPE(+) = 'WIP_EAM_WORK_ORDER_TYPE'
AND ML4.LOOKUP_CODE(+) = WDJ.ACTIVITY_TYPE
AND ML4.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_TYPE'
AND ML5.LOOKUP_CODE(+) = WDJ.ACTIVITY_CAUSE
AND ML5.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_CAUSE'
AND ML6.LOOKUP_CODE(+) = WDJ.PRIORITY
AND ML6.LOOKUP_TYPE(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
AND ML7.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML7.LOOKUP_CODE = DECODE(NVL(WDJ.NOTIFICATION_REQUIRED
, 'N')
, 'Y'
, 1
, 2)
AND ML8.LOOKUP_CODE (+) = WDJ.SHUTDOWN_TYPE
AND ML8.LOOKUP_TYPE (+) = 'BOM_EAM_SHUTDOWN_TYPE'
AND ML9.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML9.LOOKUP_CODE = DECODE(NVL(WDJ.TAGOUT_REQUIRED
, 'N')
, 'Y'
, 1
, 2)
AND ML10.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML10.LOOKUP_CODE = DECODE(NVL(WDJ.PLAN_MAINTENANCE
, 'N')
, 'Y'
, 1
, 2)
AND ML11.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML11.LOOKUP_CODE = WDJ.FIRM_PLANNED_FLAG
AND ML12.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML12.LOOKUP_CODE = DECODE(NVL(WDJ.ISSUE_ZERO_COST_FLAG
, 'N')
, 'Y'
, 1
, 2)
AND ML13.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML13.LOOKUP_CODE = DECODE(NVL(WDJ.MATERIAL_ISSUE_BY_MO
, 'N')
, 'Y'
, 1
, 2)
AND ML14.ENABLED_FLAG (+) = 'Y'
AND ML14.LOOKUP_TYPE (+) ='EAM_MATERIAL_SHORTAGE'
AND ML14.LOOKUP_CODE (+) = EWOD.MATERIAL_SHORTAGE_FLAG
AND ML15.LOOKUP_TYPE(+) = 'MTL_EAM_ACTIVITY_SOURCE'
AND ML15.LOOKUP_CODE(+) = WDJ.ACTIVITY_SOURCE
AND ML16.LOOKUP_TYPE = 'MTL_EAM_ASSET_TYPE'
AND ML16.LOOKUP_CODE = MSIK1.EAM_ITEM_TYPE
AND ML17.LOOKUP_TYPE = 'SYS_YES_NO'
AND ML17.LOOKUP_CODE = DECODE(NVL(EWOD.ASSIGNMENT_COMPLETE
, 'N')
, 'Y'
, 1
, 2)
AND ML18.LOOKUP_TYPE(+) = 'EAM_WORKFLOW_TYPE'
AND ML18.LOOKUP_CODE(+) = EWOD.WORKFLOW_TYPE
AND ML19.LOOKUP_TYPE(+) = 'EAM_WARRANTY_STATUS'
AND ML19.LOOKUP_CODE(+) = EWOD.WARRANTY_CLAIM_STATUS
AND ML20.LOOKUP_TYPE = 'CST_EAM_ESTIMATION_STATUS'
AND ML20.LOOKUP_CODE = NVL (WDJ.ESTIMATION_STATUS
, 1 )
AND WDJ.WIP_ENTITY_ID = EWOD.WIP_ENTITY_ID
AND EWOD.USER_DEFINED_STATUS_ID = EWSV.STATUS_ID
AND WDJ.WIP_ENTITY_ID = EASH.WIP_ENTITY_ID (+)
AND WDJ.ORGANIZATION_ID = EASH.ORGANIZATION_ID (+)
AND EASH.OPERATION_SEQ_NUM IS NULL
AND EASH.ENABLE_FLAG (+) = 'Y'
AND EWOD.PM_BASE_METER = CCT.COUNTER_ID(+)
AND CCT.LANGUAGE(+) = USERENV('LANG')