VIEW: APPS.EAM_PM_SCHEDULINGS_V

Source

View Text - Preformatted

SELECT pm.pm_schedule_id, mp.maint_organization_id organization_id, cii.inventory_item_id, cii.instance_number "ASSET_NUMBER", pm.non_scheduled_flag, pm.from_effective_date, pm.to_effective_date, pm.rescheduling_point, pm.lead_time,pm.created_by, pm.creation_date,pm.last_update_login, pm.last_update_date,pm.last_updated_by, pm.attribute_category,pm.attribute1, pm.attribute2, pm.attribute3, pm.attribute4, pm.attribute5,pm.attribute6, pm.attribute7, pm.attribute8,pm.attribute9, pm.attribute10, pm.attribute11, pm.attribute12, pm.attribute13, pm.attribute14, pm.attribute15, pm.source_code pm_source_code, pm.source_line pm_source_line, pm.default_implement, pm.whichever_first, pm.include_manual, pm.set_name_id, epsn.set_name, pm.scheduling_method_code, pm.type_code, pm.source_tmpl_id, pm.auto_instantiation_flag, pm.name, pm.tmpl_flag,pm.maintenance_object_id, pm.maintenance_object_type, msik.eam_item_type,pm.generate_wo_status, ewsv.work_order_status,pm.interval_per_cycle, pm.current_cycle, pm.current_seq,pm.base_date, pm.base_reading, pm.eam_last_cyclic_act,cii.instance_id,msik.concatenated_segments, msik.description,cii.instance_description,cii.serial_number, epsn.local_flag, epsn.owning_organization_id, nvl(cii.network_asset_flag,'N') NETWORK_ASSET, fu.user_id LAST_REVIEWED_BY_ID, pm.Last_reviewed_date, per.full_name LAST_REVIEWED_BY, pm.generate_next_work_order, pm.PLANNER_MAINTENANCE, ml.meaning planner_maintenance_disp FROM eam_pm_schedulings pm, csi_item_instances cii, mtl_system_items_vl msik, eam_pm_set_names epsn, mtl_parameters mp,eam_wo_statuses_v ewsv , PER_PEOPLE_F per, fnd_user fu, mfg_lookups ml WHERE nvl(pm.tmpl_flag, 'N') = 'N' AND pm.set_name_id = epsn.set_name_id AND nvl(cii.maintainable_flag, 'Y') = 'Y' AND pm.maintenance_object_id = cii.instance_id AND mp.organization_id = cii.last_vld_organization_id AND msik.organization_id = cii.last_vld_organization_id AND msik.inventory_item_id = cii.inventory_item_id AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate AND ewsv.status_id = pm.generate_wo_status AND pm.maintenance_object_type = 3 AND pm.Last_reviewed_by = fu.user_id(+) AND fu.employee_id = per.person_id(+) AND NVL(per.Effective_end_date,sysdate+1)>sysdate AND NVL(per.Effective_start_date,sysdate-1) SYSDATE AND NVL(EAA.TMPL_FLAG, 'N') = 'N') AND pm.PLANNER_MAINTENANCE = ml.lookup_code(+) AND ml.lookup_type(+) = 'EAM_PLANNER' UNION ALL SELECT distinct pm.pm_schedule_id, mp.maint_organization_id organization_id, pm.maintenance_object_id, null "ASSET_NUMBER", pm.non_scheduled_flag, pm.from_effective_date, pm.to_effective_date, pm.rescheduling_point, pm.lead_time, pm.created_by, pm.creation_date,pm.last_update_login, pm.last_update_date,pm.last_updated_by, pm.attribute_category, pm.attribute1, pm.attribute2, pm.attribute3, pm.attribute4, pm.attribute5,pm.attribute6, pm.attribute7, pm.attribute8, pm.attribute9, pm.attribute10, pm.attribute11, pm.attribute12, pm.attribute13, pm.attribute14, pm.attribute15, pm.source_code pm_source_code, pm.source_line pm_source_line, pm.default_implement, pm.whichever_first, pm.include_manual, pm.set_name_id, epsn.set_name, pm.scheduling_method_code, pm.type_code, pm.source_tmpl_id, pm.auto_instantiation_flag, pm.name, pm.tmpl_flag, null, pm.maintenance_object_type, msik.eam_item_type,pm.generate_wo_status, ewsv.work_order_status, pm.interval_per_cycle, pm.current_cycle, pm.current_seq,pm.base_date, pm.base_reading, pm.eam_last_cyclic_act, null,msik.concatenated_segments, msik.description,null,null, epsn.local_flag, epsn.owning_organization_id, 'N', fu.user_id, pm.Last_reviewed_date, per.full_name, pm.generate_next_work_order, pm.PLANNER_MAINTENANCE, ml.meaning planner_maintenance_disp FROM eam_pm_schedulings pm, eam_pm_set_names epsn, mtl_system_items_vl msik, eam_wo_statuses_v ewsv, mtl_parameters mp, PER_PEOPLE_F per, fnd_user fu, mfg_lookups ml WHERE pm.tmpl_flag = 'Y' AND pm.set_name_id = epsn.set_name_id AND pm.maintenance_object_id = msik.inventory_item_id AND ewsv.status_id=pm.generate_wo_status AND msik.organization_id = mp.organization_id AND pm.maintenance_object_type = 2 AND pm.Last_reviewed_by = fu.user_id(+) AND fu.employee_id = per.person_id(+) AND NVL(per.Effective_end_date,sysdate+1)>sysdate AND NVL(per.Effective_start_date,sysdate-1) SYSDATE AND EAA.TMPL_FLAG = 'Y') AND pm.PLANNER_MAINTENANCE = ml.lookup_code(+) AND ml.lookup_type(+) = 'EAM_PLANNER' UNION ALL SELECT distinct pm.pm_schedule_id, mp.maint_organization_id organization_id, pm.maintenance_object_id, null "ASSET_NUMBER", pm.non_scheduled_flag, pm.from_effective_date, pm.to_effective_date, pm.rescheduling_point, pm.lead_time, pm.created_by, pm.creation_date,pm.last_update_login, pm.last_update_date,pm.last_updated_by, pm.attribute_category, pm.attribute1, pm.attribute2, pm.attribute3, pm.attribute4, pm.attribute5,pm.attribute6, pm.attribute7, pm.attribute8, pm.attribute9, pm.attribute10, pm.attribute11, pm.attribute12, pm.attribute13, pm.attribute14, pm.attribute15, pm.source_code pm_source_code, pm.source_line pm_source_line, pm.default_implement, pm.whichever_first, pm.include_manual, pm.set_name_id, epsn.set_name, pm.scheduling_method_code, pm.type_code, pm.source_tmpl_id, pm.auto_instantiation_flag, pm.name, pm.tmpl_flag, null, pm.maintenance_object_type, msik.eam_item_type,pm.generate_wo_status, ewsv.work_order_status, pm.interval_per_cycle, pm.current_cycle, pm.current_seq,pm.base_date, pm.base_reading, pm.eam_last_cyclic_act, null,msik.concatenated_segments, msik.description,null,null, epsn.local_flag, epsn.owning_organization_id, 'N', fu.user_id, pm.Last_reviewed_date, per.full_name, pm.generate_next_work_order, pm.PLANNER_MAINTENANCE, ml.meaning planner_maintenance_disp FROM eam_pm_schedulings pm, eam_pm_set_names epsn, mtl_system_items_vl msik, eam_wo_statuses_v ewsv, mtl_parameters mp, PER_PEOPLE_F per, fnd_user fu, mfg_lookups ml WHERE nvl(PM.tmpl_flag, 'N') = 'N' AND pm.set_name_id = epsn.set_name_id AND pm.maintenance_object_id =msik.inventory_item_id AND msik.eam_item_type = 3 AND msik.serial_number_control_code = 1 AND ewsv.status_id=pm.generate_wo_status AND msik.organization_id = mp.organization_id AND pm.maintenance_object_type = 2 AND pm.Last_reviewed_by = fu.user_id(+) AND fu.employee_id = per.person_id(+) AND NVL(per.Effective_end_date,sysdate+1)>sysdate AND NVL(per.Effective_start_date,sysdate-1) SYSDATE AND NVL(EAA.TMPL_FLAG, 'N') = 'N') AND pm.PLANNER_MAINTENANCE = ml.lookup_code(+) AND ml.lookup_type(+) = 'EAM_PLANNER'

View Text - HTML Formatted

SELECT PM.PM_SCHEDULE_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, CII.INVENTORY_ITEM_ID
, CII.INSTANCE_NUMBER "ASSET_NUMBER"
, PM.NON_SCHEDULED_FLAG
, PM.FROM_EFFECTIVE_DATE
, PM.TO_EFFECTIVE_DATE
, PM.RESCHEDULING_POINT
, PM.LEAD_TIME
, PM.CREATED_BY
, PM.CREATION_DATE
, PM.LAST_UPDATE_LOGIN
, PM.LAST_UPDATE_DATE
, PM.LAST_UPDATED_BY
, PM.ATTRIBUTE_CATEGORY
, PM.ATTRIBUTE1
, PM.ATTRIBUTE2
, PM.ATTRIBUTE3
, PM.ATTRIBUTE4
, PM.ATTRIBUTE5
, PM.ATTRIBUTE6
, PM.ATTRIBUTE7
, PM.ATTRIBUTE8
, PM.ATTRIBUTE9
, PM.ATTRIBUTE10
, PM.ATTRIBUTE11
, PM.ATTRIBUTE12
, PM.ATTRIBUTE13
, PM.ATTRIBUTE14
, PM.ATTRIBUTE15
, PM.SOURCE_CODE PM_SOURCE_CODE
, PM.SOURCE_LINE PM_SOURCE_LINE
, PM.DEFAULT_IMPLEMENT
, PM.WHICHEVER_FIRST
, PM.INCLUDE_MANUAL
, PM.SET_NAME_ID
, EPSN.SET_NAME
, PM.SCHEDULING_METHOD_CODE
, PM.TYPE_CODE
, PM.SOURCE_TMPL_ID
, PM.AUTO_INSTANTIATION_FLAG
, PM.NAME
, PM.TMPL_FLAG
, PM.MAINTENANCE_OBJECT_ID
, PM.MAINTENANCE_OBJECT_TYPE
, MSIK.EAM_ITEM_TYPE
, PM.GENERATE_WO_STATUS
, EWSV.WORK_ORDER_STATUS
, PM.INTERVAL_PER_CYCLE
, PM.CURRENT_CYCLE
, PM.CURRENT_SEQ
, PM.BASE_DATE
, PM.BASE_READING
, PM.EAM_LAST_CYCLIC_ACT
, CII.INSTANCE_ID
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, CII.INSTANCE_DESCRIPTION
, CII.SERIAL_NUMBER
, EPSN.LOCAL_FLAG
, EPSN.OWNING_ORGANIZATION_ID
, NVL(CII.NETWORK_ASSET_FLAG
, 'N') NETWORK_ASSET
, FU.USER_ID LAST_REVIEWED_BY_ID
, PM.LAST_REVIEWED_DATE
, PER.FULL_NAME LAST_REVIEWED_BY
, PM.GENERATE_NEXT_WORK_ORDER
, PM.PLANNER_MAINTENANCE
, ML.MEANING PLANNER_MAINTENANCE_DISP
FROM EAM_PM_SCHEDULINGS PM
, CSI_ITEM_INSTANCES CII
, MTL_SYSTEM_ITEMS_VL MSIK
, EAM_PM_SET_NAMES EPSN
, MTL_PARAMETERS MP
, EAM_WO_STATUSES_V EWSV
, PER_PEOPLE_F PER
, FND_USER FU
, MFG_LOOKUPS ML
WHERE NVL(PM.TMPL_FLAG
, 'N') = 'N'
AND PM.SET_NAME_ID = EPSN.SET_NAME_ID
AND NVL(CII.MAINTAINABLE_FLAG
, 'Y') = 'Y'
AND PM.MAINTENANCE_OBJECT_ID = CII.INSTANCE_ID
AND MP.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MSIK.ORGANIZATION_ID = CII.LAST_VLD_ORGANIZATION_ID
AND MSIK.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND NVL(CII.ACTIVE_START_DATE
, SYSDATE-1) <= SYSDATE
AND NVL(CII.ACTIVE_END_DATE
, SYSDATE+1) >= SYSDATE
AND EWSV.STATUS_ID = PM.GENERATE_WO_STATUS
AND PM.MAINTENANCE_OBJECT_TYPE = 3
AND PM.LAST_REVIEWED_BY = FU.USER_ID(+)
AND FU.EMPLOYEE_ID = PER.PERSON_ID(+)
AND NVL(PER.EFFECTIVE_END_DATE
, SYSDATE+1)>SYSDATE
AND NVL(PER.EFFECTIVE_START_DATE
, SYSDATE-1) AND EXISTS (SELECT 1
FROM EAM_PM_ACTIVITIES EPA
, MTL_EAM_ASSET_ACTIVITIES EAA
WHERE EPA.PM_SCHEDULE_ID= PM.PM_SCHEDULE_ID
AND EPA.ACTIVITY_ASSOCIATION_ID=EAA.ACTIVITY_ASSOCIATION_ID
AND NVL(EAA.START_DATE_ACTIVE
, SYSDATE-1) < SYSDATE
AND NVL(EAA.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE
AND NVL(EAA.TMPL_FLAG
, 'N') = 'N')
AND PM.PLANNER_MAINTENANCE = ML.LOOKUP_CODE(+)
AND ML.LOOKUP_TYPE(+) = 'EAM_PLANNER' UNION ALL SELECT DISTINCT PM.PM_SCHEDULE_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, PM.MAINTENANCE_OBJECT_ID
, NULL "ASSET_NUMBER"
, PM.NON_SCHEDULED_FLAG
, PM.FROM_EFFECTIVE_DATE
, PM.TO_EFFECTIVE_DATE
, PM.RESCHEDULING_POINT
, PM.LEAD_TIME
, PM.CREATED_BY
, PM.CREATION_DATE
, PM.LAST_UPDATE_LOGIN
, PM.LAST_UPDATE_DATE
, PM.LAST_UPDATED_BY
, PM.ATTRIBUTE_CATEGORY
, PM.ATTRIBUTE1
, PM.ATTRIBUTE2
, PM.ATTRIBUTE3
, PM.ATTRIBUTE4
, PM.ATTRIBUTE5
, PM.ATTRIBUTE6
, PM.ATTRIBUTE7
, PM.ATTRIBUTE8
, PM.ATTRIBUTE9
, PM.ATTRIBUTE10
, PM.ATTRIBUTE11
, PM.ATTRIBUTE12
, PM.ATTRIBUTE13
, PM.ATTRIBUTE14
, PM.ATTRIBUTE15
, PM.SOURCE_CODE PM_SOURCE_CODE
, PM.SOURCE_LINE PM_SOURCE_LINE
, PM.DEFAULT_IMPLEMENT
, PM.WHICHEVER_FIRST
, PM.INCLUDE_MANUAL
, PM.SET_NAME_ID
, EPSN.SET_NAME
, PM.SCHEDULING_METHOD_CODE
, PM.TYPE_CODE
, PM.SOURCE_TMPL_ID
, PM.AUTO_INSTANTIATION_FLAG
, PM.NAME
, PM.TMPL_FLAG
, NULL
, PM.MAINTENANCE_OBJECT_TYPE
, MSIK.EAM_ITEM_TYPE
, PM.GENERATE_WO_STATUS
, EWSV.WORK_ORDER_STATUS
, PM.INTERVAL_PER_CYCLE
, PM.CURRENT_CYCLE
, PM.CURRENT_SEQ
, PM.BASE_DATE
, PM.BASE_READING
, PM.EAM_LAST_CYCLIC_ACT
, NULL
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, NULL
, NULL
, EPSN.LOCAL_FLAG
, EPSN.OWNING_ORGANIZATION_ID
, 'N'
, FU.USER_ID
, PM.LAST_REVIEWED_DATE
, PER.FULL_NAME
, PM.GENERATE_NEXT_WORK_ORDER
, PM.PLANNER_MAINTENANCE
, ML.MEANING PLANNER_MAINTENANCE_DISP
FROM EAM_PM_SCHEDULINGS PM
, EAM_PM_SET_NAMES EPSN
, MTL_SYSTEM_ITEMS_VL MSIK
, EAM_WO_STATUSES_V EWSV
, MTL_PARAMETERS MP
, PER_PEOPLE_F PER
, FND_USER FU
, MFG_LOOKUPS ML
WHERE PM.TMPL_FLAG = 'Y'
AND PM.SET_NAME_ID = EPSN.SET_NAME_ID
AND PM.MAINTENANCE_OBJECT_ID = MSIK.INVENTORY_ITEM_ID
AND EWSV.STATUS_ID=PM.GENERATE_WO_STATUS
AND MSIK.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND PM.MAINTENANCE_OBJECT_TYPE = 2
AND PM.LAST_REVIEWED_BY = FU.USER_ID(+)
AND FU.EMPLOYEE_ID = PER.PERSON_ID(+)
AND NVL(PER.EFFECTIVE_END_DATE
, SYSDATE+1)>SYSDATE
AND NVL(PER.EFFECTIVE_START_DATE
, SYSDATE-1) AND EXISTS (SELECT 1
FROM EAM_PM_ACTIVITIES EPA
, MTL_EAM_ASSET_ACTIVITIES EAA
WHERE EPA.PM_SCHEDULE_ID= PM.PM_SCHEDULE_ID
AND EPA.ACTIVITY_ASSOCIATION_ID=EAA.ACTIVITY_ASSOCIATION_ID
AND NVL(EAA.START_DATE_ACTIVE
, SYSDATE-1) < SYSDATE
AND NVL(EAA.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE
AND EAA.TMPL_FLAG = 'Y')
AND PM.PLANNER_MAINTENANCE = ML.LOOKUP_CODE(+)
AND ML.LOOKUP_TYPE(+) = 'EAM_PLANNER' UNION ALL SELECT DISTINCT PM.PM_SCHEDULE_ID
, MP.MAINT_ORGANIZATION_ID ORGANIZATION_ID
, PM.MAINTENANCE_OBJECT_ID
, NULL "ASSET_NUMBER"
, PM.NON_SCHEDULED_FLAG
, PM.FROM_EFFECTIVE_DATE
, PM.TO_EFFECTIVE_DATE
, PM.RESCHEDULING_POINT
, PM.LEAD_TIME
, PM.CREATED_BY
, PM.CREATION_DATE
, PM.LAST_UPDATE_LOGIN
, PM.LAST_UPDATE_DATE
, PM.LAST_UPDATED_BY
, PM.ATTRIBUTE_CATEGORY
, PM.ATTRIBUTE1
, PM.ATTRIBUTE2
, PM.ATTRIBUTE3
, PM.ATTRIBUTE4
, PM.ATTRIBUTE5
, PM.ATTRIBUTE6
, PM.ATTRIBUTE7
, PM.ATTRIBUTE8
, PM.ATTRIBUTE9
, PM.ATTRIBUTE10
, PM.ATTRIBUTE11
, PM.ATTRIBUTE12
, PM.ATTRIBUTE13
, PM.ATTRIBUTE14
, PM.ATTRIBUTE15
, PM.SOURCE_CODE PM_SOURCE_CODE
, PM.SOURCE_LINE PM_SOURCE_LINE
, PM.DEFAULT_IMPLEMENT
, PM.WHICHEVER_FIRST
, PM.INCLUDE_MANUAL
, PM.SET_NAME_ID
, EPSN.SET_NAME
, PM.SCHEDULING_METHOD_CODE
, PM.TYPE_CODE
, PM.SOURCE_TMPL_ID
, PM.AUTO_INSTANTIATION_FLAG
, PM.NAME
, PM.TMPL_FLAG
, NULL
, PM.MAINTENANCE_OBJECT_TYPE
, MSIK.EAM_ITEM_TYPE
, PM.GENERATE_WO_STATUS
, EWSV.WORK_ORDER_STATUS
, PM.INTERVAL_PER_CYCLE
, PM.CURRENT_CYCLE
, PM.CURRENT_SEQ
, PM.BASE_DATE
, PM.BASE_READING
, PM.EAM_LAST_CYCLIC_ACT
, NULL
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, NULL
, NULL
, EPSN.LOCAL_FLAG
, EPSN.OWNING_ORGANIZATION_ID
, 'N'
, FU.USER_ID
, PM.LAST_REVIEWED_DATE
, PER.FULL_NAME
, PM.GENERATE_NEXT_WORK_ORDER
, PM.PLANNER_MAINTENANCE
, ML.MEANING PLANNER_MAINTENANCE_DISP
FROM EAM_PM_SCHEDULINGS PM
, EAM_PM_SET_NAMES EPSN
, MTL_SYSTEM_ITEMS_VL MSIK
, EAM_WO_STATUSES_V EWSV
, MTL_PARAMETERS MP
, PER_PEOPLE_F PER
, FND_USER FU
, MFG_LOOKUPS ML
WHERE NVL(PM.TMPL_FLAG
, 'N') = 'N'
AND PM.SET_NAME_ID = EPSN.SET_NAME_ID
AND PM.MAINTENANCE_OBJECT_ID =MSIK.INVENTORY_ITEM_ID
AND MSIK.EAM_ITEM_TYPE = 3
AND MSIK.SERIAL_NUMBER_CONTROL_CODE = 1
AND EWSV.STATUS_ID=PM.GENERATE_WO_STATUS
AND MSIK.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND PM.MAINTENANCE_OBJECT_TYPE = 2
AND PM.LAST_REVIEWED_BY = FU.USER_ID(+)
AND FU.EMPLOYEE_ID = PER.PERSON_ID(+)
AND NVL(PER.EFFECTIVE_END_DATE
, SYSDATE+1)>SYSDATE
AND NVL(PER.EFFECTIVE_START_DATE
, SYSDATE-1) AND EXISTS (SELECT 1
FROM EAM_PM_ACTIVITIES EPA
, MTL_EAM_ASSET_ACTIVITIES EAA
WHERE EPA.PM_SCHEDULE_ID= PM.PM_SCHEDULE_ID
AND EPA.ACTIVITY_ASSOCIATION_ID=EAA.ACTIVITY_ASSOCIATION_ID
AND NVL(EAA.START_DATE_ACTIVE
, SYSDATE-1) < SYSDATE
AND NVL(EAA.END_DATE_ACTIVE
, SYSDATE+1) > SYSDATE
AND NVL(EAA.TMPL_FLAG
, 'N') = 'N')
AND PM.PLANNER_MAINTENANCE = ML.LOOKUP_CODE(+)
AND ML.LOOKUP_TYPE(+) = 'EAM_PLANNER'