ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E01A2B3C4D5E6F708192A3B4C5D6E701Y with q_base as ( select eps.pm_schedule_id, mp.organization_code, mp.organization_id, eps.name schedule_name, xxen_util.meaning(to_char(nvl(msibk_asset.eam_item_type,msibk_group.eam_item_type)),'MTL_EAM_ASSET_TYPE',700) asset_type, case when eps.maintenance_object_type=3 then cii.instance_number end asset_number, nvl(msibk_asset.concatenated_segments,msibk_group.concatenated_segments) asset_group, nvl(msibk_asset.inventory_item_id,msibk_group.inventory_item_id) asset_group_id, msibk_act.concatenated_segments activity, eps.pm_activity_association_id activity_id, epsn.set_name, to_char(null) set_name_description, to_date(null) set_name_end_date, to_char(null) set_name_local_flag, decode(eps.type_code,10,'Rule Based',17,'Day Interval',20,'List Dates',xxen_util.meaning(to_char(eps.type_code),'EAM_PM_TYPE',700)) schedule_type, eps.type_code schedule_type_code, eps.from_effective_date effective_from, eps.to_effective_date effective_to, eps.lead_time lead_time_in_days, ewsv.work_order_status, ewsv.status_id work_order_status_id, xxen_util.meaning(eps.generate_next_work_order,'YES_NO',0) next_wo_only, eps.generate_next_work_order generate_next_wo, eps.day_tolerance, decode(eps.rescheduling_point,1,'Actual Start Date',2,'Actual End Date',3,'Scheduled Start Date',4,'Scheduled End Date',5,'Base Date',6,'Base Meter Reading') use, eps.rescheduling_point, eps.base_date, eps.base_reading, decode(eps.scheduling_method_code,10,'Start Date',20,'End Date',xxen_util.meaning(to_char(eps.scheduling_method_code),'EAM_PM_SCHEDULING_METHOD',700)) suggest_next_service, eps.scheduling_method_code scheduling_method, decode(eps.whichever_first,'Y','First','N','Last') use_date_that_comes, eps.whichever_first, eps.interval_per_cycle intervals_per_cycle, xxen_util.meaning(eps.include_manual,'YES_NO',0) reschedule_manual_work_orders, eps.include_manual, xxen_util.meaning(eps.non_scheduled_flag,'YES_NO',0) run_to_failure, eps.non_scheduled_flag, xxen_util.meaning(eps.default_implement,'YES_NO',0) default_schedule, eps.default_implement, xxen_util.meaning(eps.auto_instantiation_flag,'YES_NO',0) automatic_instantiation, eps.auto_instantiation_flag auto_instantiation from ( select eps.*, nvl(eps.activity_association_id,(select min(epa.activity_association_id) from eam_pm_activities epa where eps.pm_schedule_id=epa.pm_schedule_id)) pm_activity_association_id from eam_pm_schedulings eps ) eps left join csi_item_instances cii on eps.maintenance_object_type=3 and eps.maintenance_object_id=cii.instance_id and sysdate between nvl(cii.active_start_date,sysdate) and nvl(cii.active_end_date,sysdate) left join mtl_eam_asset_activities mea on eps.pm_activity_association_id=mea.activity_association_id left join mtl_system_items_b_kfv msibk_act on mea.asset_activity_id=msibk_act.inventory_item_id and mea.organization_id=msibk_act.organization_id left join mtl_system_items_b_kfv msibk_asset on cii.inventory_item_id=msibk_asset.inventory_item_id and cii.last_vld_organization_id=msibk_asset.organization_id left join mtl_system_items_b_kfv msibk_group on eps.maintenance_object_type=2 and eps.maintenance_object_id=msibk_group.inventory_item_id and mea.organization_id=msibk_group.organization_id join mtl_parameters mp on mp.organization_id=nvl(cii.last_vld_organization_id,mea.organization_id) left join eam_pm_set_names epsn on eps.set_name_id=epsn.set_name_id left join eam_wo_statuses_v ewsv on ewsv.status_id=eps.generate_wo_status where nvl(eps.tmpl_flag,'N')='N' and mp.eam_enabled_flag='Y' and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') ) select null action_, null status_, null message_, null modified_columns_, row_number() over (order by x.organization_code,x.schedule_name,x.pm_schedule_id,x.row_sort,x.child_sort) upload_row, x.organization_code, x.organization_id, x.schedule_name, x.asset_type, x.asset_number, x.asset_group, x.asset_group_id, x.activity, x.activity_id, x.set_name, x.set_name_description, x.set_name_end_date, x.set_name_local_flag, x.schedule_type, x.schedule_type_code, x.effective_from, x.effective_to, x.lead_time_in_days, x.work_order_status, x.work_order_status_id, x.next_wo_only, x.generate_next_wo, x.day_tolerance, x.use, x.rescheduling_point, x.base_date, x.base_reading, x.suggest_next_service, x.scheduling_method, x.use_date_that_comes, x.whichever_first, x.intervals_per_cycle, x.reschedule_manual_work_orders, x.include_manual, x.run_to_failure, x.non_scheduled_flag, x.default_schedule, x.default_implement, x.automatic_instantiation, x.auto_instantiation, x.act_activity, x.act_activity_id, x.act_interval_multiple, x.act_allow_repeat, x.act_day_tolerance, x.act_next_svc_start, x.act_next_svc_end, x.act_last_svc_start, x.act_last_svc_end, x.rule_type_dsp, x.rule_type, x.rule_day_interval, x.rule_meter, x.rule_meter_id, x.rule_runtime_interval, x.rule_last_service_reading, x.rule_effective_reading_from, x.rule_effective_reading_to, x.rule_effective_date_from, x.rule_effective_date_to, x.rule_list_date, x.rule_list_date_desc, x.rule_base_date, x.delete_rule, x.rule_id, x.lsi_meter, x.lsi_meter_id, x.lsi_last_service_reading, x.lsi_prev_service_reading, x.lsi_wip_job, x.lsi_wip_entity_id from ( select qb.*, to_char(null) act_activity, to_number(null) act_activity_id, to_number(null) act_interval_multiple, to_char(null) act_allow_repeat, to_number(null) act_day_tolerance, to_date(null) act_next_svc_start, to_date(null) act_next_svc_end, to_date(null) act_last_svc_start, to_date(null) act_last_svc_end, to_char(null) rule_type_dsp, to_number(null) rule_type, to_number(null) rule_day_interval, to_char(null) rule_meter, to_number(null) rule_meter_id, to_number(null) rule_runtime_interval, to_number(null) rule_last_service_reading, to_number(null) rule_effective_reading_from, to_number(null) rule_effective_reading_to, to_date(null) rule_effective_date_from, to_date(null) rule_effective_date_to, to_date(null) rule_list_date, to_char(null) rule_list_date_desc, to_date(null) rule_base_date, to_char(null) delete_rule, to_number(null) rule_id, to_char(null) lsi_meter, to_number(null) lsi_meter_id, to_number(null) lsi_last_service_reading, to_number(null) lsi_prev_service_reading, to_char(null) lsi_wip_job, to_number(null) lsi_wip_entity_id, 0 row_sort, 0 child_sort from q_base qb union all select qb.*, msibk_act.concatenated_segments act_activity, epa.activity_association_id act_activity_id, epa.interval_multiple act_interval_multiple, xxen_util.meaning(epa.allow_repeat_in_cycle,'YES_NO',0) act_allow_repeat, epa.day_tolerance act_day_tolerance, epa.next_service_start_date act_next_svc_start, epa.next_service_end_date act_next_svc_end, mea.last_service_start_date act_last_svc_start, mea.last_service_end_date act_last_svc_end, to_char(null) rule_type_dsp, to_number(null) rule_type, to_number(null) rule_day_interval, to_char(null) rule_meter, to_number(null) rule_meter_id, to_number(null) rule_runtime_interval, to_number(null) rule_last_service_reading, to_number(null) rule_effective_reading_from, to_number(null) rule_effective_reading_to, to_date(null) rule_effective_date_from, to_date(null) rule_effective_date_to, to_date(null) rule_list_date, to_char(null) rule_list_date_desc, to_date(null) rule_base_date, to_char(null) delete_rule, to_number(null) rule_id, to_char(null) lsi_meter, to_number(null) lsi_meter_id, to_number(null) lsi_last_service_reading, to_number(null) lsi_prev_service_reading, to_char(null) lsi_wip_job, to_number(null) lsi_wip_entity_id, 1 row_sort, epa.activity_association_id child_sort from q_base qb, eam_pm_activities epa, mtl_eam_asset_activities mea, mtl_system_items_b_kfv msibk_act where qb.pm_schedule_id=epa.pm_schedule_id and epa.activity_association_id=mea.activity_association_id and mea.asset_activity_id=msibk_act.inventory_item_id and mea.organization_id=msibk_act.organization_id and :p_update_activities='Y' union all select qb.*, to_char(null) act_activity, to_number(null) act_activity_id, to_number(null) act_interval_multiple, to_char(null) act_allow_repeat, to_number(null) act_day_tolerance, to_date(null) act_next_svc_start, to_date(null) act_next_svc_end, to_date(null) act_last_svc_start, to_date(null) act_last_svc_end, decode(epsr.rule_type,1,'Day Interval',2,'Runtime Meter',3,'List Date') rule_type_dsp, epsr.rule_type, epsr.day_interval rule_day_interval, ccv.name rule_meter, epsr.meter_id rule_meter_id, epsr.runtime_interval rule_runtime_interval, epsr.last_service_reading rule_last_service_reading, epsr.effective_reading_from rule_effective_reading_from, epsr.effective_reading_to rule_effective_reading_to, epsr.effective_date_from rule_effective_date_from, epsr.effective_date_to rule_effective_date_to, epsr.list_date rule_list_date, epsr.list_date_desc rule_list_date_desc, epsr.base_date rule_base_date, to_char(null) delete_rule, epsr.rule_id, to_char(null) lsi_meter, to_number(null) lsi_meter_id, to_number(null) lsi_last_service_reading, to_number(null) lsi_prev_service_reading, to_char(null) lsi_wip_job, to_number(null) lsi_wip_entity_id, 2 row_sort, epsr.rule_id child_sort from q_base qb, eam_pm_scheduling_rules epsr, csi_counters_vl ccv where qb.pm_schedule_id=epsr.pm_schedule_id and epsr.meter_id=ccv.counter_id(+) and :p_update_rules='Y' union all select qb.*, to_char(null) act_activity, to_number(null) act_activity_id, to_number(null) act_interval_multiple, to_char(null) act_allow_repeat, to_number(null) act_day_tolerance, to_date(null) act_next_svc_start, to_date(null) act_next_svc_end, to_date(null) act_last_svc_start, to_date(null) act_last_svc_end, to_char(null) rule_type_dsp, to_number(null) rule_type, to_number(null) rule_day_interval, to_char(null) rule_meter, to_number(null) rule_meter_id, to_number(null) rule_runtime_interval, to_number(null) rule_last_service_reading, to_number(null) rule_effective_reading_from, to_number(null) rule_effective_reading_to, to_date(null) rule_effective_date_from, to_date(null) rule_effective_date_to, to_date(null) rule_list_date, to_char(null) rule_list_date_desc, to_date(null) rule_base_date, to_char(null) delete_rule, to_number(null) rule_id, ccv.name lsi_meter, epls.meter_id lsi_meter_id, epls.last_service_reading lsi_last_service_reading, epls.prev_service_reading lsi_prev_service_reading, we.wip_entity_name lsi_wip_job, epls.wip_entity_id lsi_wip_entity_id, 3 row_sort, epls.meter_id child_sort from q_base qb, eam_pm_last_service epls, csi_counters_vl ccv, wip_entities we where qb.activity_id=epls.activity_association_id and epls.meter_id=ccv.counter_id(+) and epls.wip_entity_id=we.wip_entity_id(+) and :p_update_last_service='Y' ) x where 1=1 Organization security from the Blitz Report organization access, which follows profiles Blitz Report Organization Access All Responsibilities, Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. U A xxen_eam_upload.upload_pm_schedule xxen_eam_upload.validate_pm_schedule_row( :$column$.organization_code, :$column$.schedule_name, :$column$.set_name, :$column$.asset_type, :$column$.asset_number, :$column$.asset_group, :$column$.activity, :$column$.schedule_type, :$column$.effective_from, :$column$.work_order_status, :$column$.next_wo_only, :$column$.use, :$column$.base_date, :$column$.base_reading, :$column$.suggest_next_service, :$column$.use_date_that_comes, :$column$.intervals_per_cycle, :$column$.act_activity, :$column$.rule_type_dsp, :$column$.rule_day_interval, :$column$.rule_meter, :$column$.rule_runtime_interval, :$column$.rule_list_date, :$column$.lsi_meter ) US EAM PM Schedule Upload Create and update preventive maintenance schedules of assets: the schedule header, its activities, its day interval, runtime meter and list date rules, and optional last service readings per meter. A schedule spans several rows, which repeat the Organization Code, Schedule Name, Asset Number and Activity and stay together. The header is processed once per schedule; further rows add activities (Act columns), rules (Rule columns) and last service readings (Lsi columns). If no Act Activity is entered for a new Rule Based schedule, the header Activity is its first activity. Whether a schedule has the rules its type needs is checked for the whole schedule on upload. Schedules are saved as the Preventive Maintenance form saves them: a changed rule is updated in place, Delete Rule removes it, and a rule row without a rule id is added. Rule Last Service Reading is derived from the last service information on save, and Act Last Svc Start and Act Last Svc End show the dates that work order completion records; all three are read-only. Every schedule needs an Asset Number. Asset group schedule templates are not downloaded and cannot be uploaded. In Create, Update mode, Update Activities, Update Rules and Update Last Service include those rows in the download. Update Activities defaults to Yes. Set Name takes an existing set name, or a new one together with Set Name Description, End Date and Local Flag, which the upload creates. Uploading a schedule does not create work orders: run the concurrent program Generate Preventive Maintenance Work Orders afterwards. Enginatics R12 only Upload 1=1 :p_update_activities :p_update_last_service :p_update_rules 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C Y US Upload Mode 2 20 1=1 x.organization_code=:p_organization LOV EAM Maintenance Organization 7141C5E32F8343C7A65351A16ADDD82E select mp.organization_code value, ood.organization_name description from mtl_parameters mp, org_organization_definitions ood where mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=ood.organization_id and nvl(ood.disable_date,sysdate)>=sysdate and mp.eam_enabled_flag='Y' order by mp.organization_code US Organization 3 30 :p_update_activities LOV custom select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.Upload_Mode like '%'||xxen_upload.action_update select 'Y' from dual where :$flex$.Upload_Mode like '%'||xxen_upload.action_update US Update Activities 4 40 :p_update_rules LOV custom select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.Upload_Mode like '%'||xxen_upload.action_update US Update Rules 5 50 :p_update_last_service LOV custom select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.Upload_Mode like '%'||xxen_upload.action_update US Update Last Service 1E28ADC32E134A4BBBF42D510FE7F230 Default All columns. ENGINATICS 6 ACTIVITY 28 ACT_ACTIVITY 30 ACT_ALLOW_REPEAT 31 ACT_DAY_TOLERANCE 29 ACT_INTERVAL_MULTIPLE 35 ACT_LAST_SVC_END 34 ACT_LAST_SVC_START 33 ACT_NEXT_SVC_END 32 ACT_NEXT_SVC_START 5 ASSET_GROUP 4 ASSET_NUMBER 3 ASSET_TYPE Y 27 AUTOMATIC_INSTANTIATION 19 BASE_DATE 20 BASE_READING 17 DAY_TOLERANCE 26 DEFAULT_SCHEDULE 47 DELETE_RULE 12 EFFECTIVE_FROM 13 EFFECTIVE_TO 23 INTERVALS_PER_CYCLE 14 LEAD_TIME_IN_DAYS 49 LSI_LAST_SERVICE_READING 48 LSI_METER 50 LSI_PREV_SERVICE_READING 51 LSI_WIP_JOB 16 NEXT_WO_ONLY 1 ORGANIZATION_CODE 24 RESCHEDULE_MANUAL_WORK_ORDERS 46 RULE_BASE_DATE 36 RULE_DAY_INTERVAL 42 RULE_EFFECTIVE_DATE_FROM 43 RULE_EFFECTIVE_DATE_TO 40 RULE_EFFECTIVE_READING_FROM 41 RULE_EFFECTIVE_READING_TO 39 RULE_LAST_SERVICE_READING 44 RULE_LIST_DATE 45 RULE_LIST_DATE_DESC 37 RULE_METER 38 RULE_RUNTIME_INTERVAL 25 RUN_TO_FAILURE 2 SCHEDULE_NAME 11 SCHEDULE_TYPE 7 SET_NAME 8 SET_NAME_DESCRIPTION 9 SET_NAME_END_DATE 10 SET_NAME_LOCAL_FLAG 21 SUGGEST_NEXT_SERVICE 18 USE 22 USE_DATE_THAT_COMES 15 WORK_ORDER_STATUS S Site E5C30B4A5B5D4AFF857E2A5CDA6AE7F5 PM Schedules Next Due Next and last service dates of each PM schedule activity, for reviewing and rescheduling upcoming preventive maintenance. Downloads schedules with their activities, without rules and last service readings. The schedule header columns stay included and the rows of a schedule stay together, because the upload saves the whole schedule from its rows. ENGINATICS 6 ACTIVITY 5 7 ACT_ACTIVITY 13 ACT_ALLOW_REPEAT 14 ACT_DAY_TOLERANCE 12 ACT_INTERVAL_MULTIPLE 11 ACT_LAST_SVC_END 10 ACT_LAST_SVC_START 9 ACT_NEXT_SVC_END 8 ACT_NEXT_SVC_START 6 5 ASSET_GROUP 4 4 ASSET_NUMBER 3 3 ASSET_TYPE Y 35 AUTOMATIC_INSTANTIATION 27 BASE_DATE 28 BASE_READING 25 DAY_TOLERANCE 34 DEFAULT_SCHEDULE 16 EFFECTIVE_FROM 17 EFFECTIVE_TO 31 INTERVALS_PER_CYCLE 22 LEAD_TIME_IN_DAYS 24 NEXT_WO_ONLY 1 ORGANIZATION_CODE 1 32 RESCHEDULE_MANUAL_WORK_ORDERS 33 RUN_TO_FAILURE 2 SCHEDULE_NAME 2 15 SCHEDULE_TYPE 18 SET_NAME 19 SET_NAME_DESCRIPTION 20 SET_NAME_END_DATE 21 SET_NAME_LOCAL_FLAG 29 SUGGEST_NEXT_SERVICE 26 USE 30 USE_DATE_THAT_COMES 23 WORK_ORDER_STATUS Update Activities Y Upload Mode C,U S Site Update Last Service Update Rules 1E28ADC32E134A4BBBF42D510FE7F230 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 UPLOAD_ROW Number number Y 6 ORGANIZATION_CODE LOV varchar2 3 INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code :$flex$.Organization Y 7 ORGANIZATION_ID Number number select mp.organization_id from mtl_parameters mp where mp.eam_enabled_flag='Y' and mp.organization_code=:$column$.organization_code and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') Y Y 8 SCHEDULE_NAME Char varchar2 50 Y 9 ASSET_TYPE LOV custom varchar2 select ml.lookup_code id, ml.meaning value, ml.description from mfg_lookups ml where ml.lookup_type='MTL_EAM_ASSET_TYPE' and ml.lookup_code in (1,3) and sysdate between nvl(ml.start_date_active,sysdate) and nvl(ml.end_date_active,sysdate) and nvl(ml.enabled_flag,'N')='Y' order by ml.lookup_code select ml.meaning from mfg_lookups ml where ml.lookup_type='MTL_EAM_ASSET_TYPE' and ml.lookup_code=1 Y 10 ASSET_NUMBER LOV custom varchar2 30 select cii.instance_number value, cii.instance_description description from csi_item_instances cii, mtl_system_items_vl msi, mtl_parameters mp, mtl_parameters mp_sel where mp_sel.organization_code=:$column$.organization_code and mp_sel.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and msi.inventory_item_id=cii.inventory_item_id and msi.organization_id=cii.last_vld_organization_id and msi.organization_id=mp.organization_id and mp.maint_organization_id=mp_sel.organization_id and nvl(cii.maintainable_flag,'Y')='Y' and nvl(cii.active_start_date,sysdate-1)<=sysdate and nvl(cii.active_end_date,sysdate+1)>=sysdate and ( (:$column$.asset_type='Capital' and msi.eam_item_type=1 and msi.serial_number_control_code<>1) or (:$column$.asset_type='Rebuildable Inventory' and msi.eam_item_type=3 and nvl(cii.network_asset_flag,'N')='N') ) and exists ( select 1 from mtl_eam_asset_activities eaa where eaa.maintenance_object_id=cii.instance_id and eaa.maintenance_object_type=3 and nvl(eaa.start_date_active,sysdate-1)<sysdate and nvl(eaa.end_date_active,sysdate+1)>sysdate ) order by cii.instance_number Y 11 ASSET_GROUP Char varchar2 40 select msik.concatenated_segments from mtl_parameters mp, csi_item_instances cii, mtl_system_items_kfv msik where mp.organization_code=:$column$.organization_code and mp.organization_id=cii.last_vld_organization_id and cii.instance_number=:$column$.asset_number and cii.last_vld_organization_id=msik.organization_id and cii.inventory_item_id=msik.inventory_item_id and msik.eam_item_type in (1,3) Y 12 ASSET_GROUP_ID Number number select msik.inventory_item_id from mtl_parameters mp, csi_item_instances cii, mtl_system_items_kfv msik where mp.organization_code=:$column$.organization_code and mp.organization_id=cii.last_vld_organization_id and cii.instance_number=:$column$.asset_number and cii.last_vld_organization_id=msik.organization_id and cii.inventory_item_id=msik.inventory_item_id and msik.eam_item_type in (1,3) Y Y 13 ACTIVITY LOV custom varchar2 40 select distinct msibk.concatenated_segments value, msibk.description from mtl_eam_asset_activities mea, mtl_system_items_b_kfv msibk, mtl_parameters mp, csi_item_instances cii where mp.organization_code=:$column$.organization_code and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and cii.instance_number=:$column$.asset_number and cii.last_vld_organization_id=mp.organization_id and mea.maintenance_object_id=cii.instance_id and mea.maintenance_object_type=3 and mea.organization_id=mp.organization_id and mea.asset_activity_id=msibk.inventory_item_id and mea.organization_id=msibk.organization_id and msibk.eam_item_type=2 and nvl(mea.tmpl_flag,'N')='N' and nvl(mea.start_date_active,sysdate-1)<sysdate and nvl(mea.end_date_active,sysdate+1)>sysdate order by msibk.concatenated_segments Y 14 ACTIVITY_ID Number number select mea.activity_association_id from mtl_eam_asset_activities mea, mtl_system_items_b_kfv msibk, mtl_parameters mp, csi_item_instances cii where mp.organization_code=:$column$.organization_code and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and cii.instance_number=:$column$.asset_number and cii.last_vld_organization_id=mp.organization_id and mea.maintenance_object_id=cii.instance_id and mea.maintenance_object_type=3 and mea.organization_id=mp.organization_id and mea.asset_activity_id=msibk.inventory_item_id and mea.organization_id=msibk.organization_id and msibk.concatenated_segments=:$column$.activity and msibk.eam_item_type=2 and nvl(mea.tmpl_flag,'N')='N' and nvl(mea.start_date_active,sysdate-1)<sysdate and nvl(mea.end_date_active,sysdate+1)>sysdate and rownum=1 Y Y 15 SET_NAME LOV custom varchar2 80 Y select epsn.set_name value, epsn.description from eam_pm_set_names epsn, mtl_parameters mp where mp.organization_code=:$column$.organization_code and (nvl(epsn.local_flag,'N')='N' or epsn.owning_organization_id=mp.organization_id) and (epsn.end_date is null or epsn.end_date>sysdate) order by epsn.set_name Y 16 SET_NAME_DESCRIPTION Char varchar2 Used only when creating a new PM Set Name. Ignored for existing set names. 17 SET_NAME_END_DATE Date date Used only when creating a new PM Set Name. 18 SET_NAME_LOCAL_FLAG LOV custom varchar2 select flv.lookup_code id, flv.meaning value, flv.description from fnd_lookup_values_vl flv where flv.lookup_type='YES_NO' and flv.view_application_id=0 and flv.security_group_id=fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) and flv.lookup_code in ('Y','N') and flv.enabled_flag='Y' order by flv.meaning Used only when creating a new PM Set Name. Y = set name is specific to the organization; N = global across orgs. 19 SCHEDULE_TYPE LOV custom varchar2 select 10 id, 'Rule Based' value, 'Day and meter rules' description from dual union all select 17 id, 'Day Interval' value, 'Day rules only' description from dual union all select 20 id, 'List Dates' value, 'Explicit list dates' description from dual Rule Based Y 20 SCHEDULE_TYPE_CODE Number number decode(:$column$.schedule_type,'Rule Based',10,'Day Interval',17,'List Dates',20) Y Y 21 EFFECTIVE_FROM Date date 22 EFFECTIVE_TO Date date 23 LEAD_TIME_IN_DAYS Number number 24 WORK_ORDER_STATUS LOV custom varchar2 80 select ewsv.work_order_status value, null description from eam_wo_statuses_v ewsv where ewsv.system_status in (1,3,6,17) order by ewsv.work_order_status select ewsv.work_order_status from eam_wo_statuses_v ewsv where ewsv.system_status=1 and rownum=1 Y 25 WORK_ORDER_STATUS_ID Number number select ewsv.status_id from eam_wo_statuses_v ewsv where ewsv.work_order_status=:$column$.work_order_status Y Y 26 NEXT_WO_ONLY LOV custom varchar2 select flv.lookup_code id, flv.meaning value, flv.description from fnd_lookup_values_vl flv where flv.lookup_type='YES_NO' and flv.view_application_id=0 and flv.security_group_id=fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) and flv.lookup_code in ('Y','N') and flv.enabled_flag='Y' order by flv.meaning xxen_util.meaning('N','YES_NO',0) Y 27 GENERATE_NEXT_WO Char varchar2 1 xxen_util.lookup_code(:$column$.next_wo_only,'YES_NO',0) Y Y 28 DAY_TOLERANCE Number number Y 29 USE LOV custom varchar2 20 select 1 id, 'Actual Start Date' value, null description from dual union all select 2 id, 'Actual End Date' value, null description from dual union all select 3 id, 'Scheduled Start Date' value, null description from dual union all select 4 id, 'Scheduled End Date' value, null description from dual union all select 5 id, 'Base Date' value, null description from dual union all select 6 id, 'Base Meter Reading' value, null description from dual Actual Start Date Y 30 RESCHEDULING_POINT Number number decode(:$column$.use,'Actual Start Date',1,'Actual End Date',2,'Scheduled Start Date',3,'Scheduled End Date',4,'Base Date',5,'Base Meter Reading',6) Y Y 31 BASE_DATE Date date Required when Use is "Base Date". 32 BASE_READING Number number Required when Use is "Base Meter Reading". 33 SUGGEST_NEXT_SERVICE LOV custom varchar2 select 10 id, 'Start Date' value, null description from dual union all select 20 id, 'End Date' value, null description from dual Start Date Y 34 SCHEDULING_METHOD Number number decode(:$column$.suggest_next_service,'Start Date',10,'End Date',20) Y Y 35 USE_DATE_THAT_COMES LOV custom varchar2 5 select 'Y' id, 'First' value, null description from dual union all select 'N' id, 'Last' value, null description from dual First Y 36 WHICHEVER_FIRST Char varchar2 1 decode(:$column$.use_date_that_comes,'First','Y','Last','N') Y Y 37 INTERVALS_PER_CYCLE Number number Y 38 RESCHEDULE_MANUAL_WORK_ORDERS LOV custom varchar2 select flv.lookup_code id, flv.meaning value, flv.description from fnd_lookup_values_vl flv where flv.lookup_type='YES_NO' and flv.view_application_id=0 and flv.security_group_id=fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) and flv.lookup_code in ('Y','N') and flv.enabled_flag='Y' order by flv.meaning xxen_util.meaning('N','YES_NO',0) 39 INCLUDE_MANUAL Char varchar2 1 xxen_util.lookup_code(:$column$.reschedule_manual_work_orders,'YES_NO',0) Y Y 40 RUN_TO_FAILURE LOV custom varchar2 select flv.lookup_code id, flv.meaning value, flv.description from fnd_lookup_values_vl flv where flv.lookup_type='YES_NO' and flv.view_application_id=0 and flv.security_group_id=fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) and flv.lookup_code in ('Y','N') and flv.enabled_flag='Y' order by flv.meaning xxen_util.meaning('N','YES_NO',0) 41 NON_SCHEDULED_FLAG Char varchar2 1 xxen_util.lookup_code(:$column$.run_to_failure,'YES_NO',0) Y Y 42 DEFAULT_SCHEDULE LOV custom varchar2 select flv.lookup_code id, flv.meaning value, flv.description from fnd_lookup_values_vl flv where flv.lookup_type='YES_NO' and flv.view_application_id=0 and flv.security_group_id=fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) and flv.lookup_code in ('Y','N') and flv.enabled_flag='Y' order by flv.meaning xxen_util.meaning('Y','YES_NO',0) 43 DEFAULT_IMPLEMENT Char varchar2 1 xxen_util.lookup_code(:$column$.default_schedule,'YES_NO',0) Y Y 44 AUTOMATIC_INSTANTIATION LOV custom varchar2 select flv.lookup_code id, flv.meaning value, flv.description from fnd_lookup_values_vl flv where flv.lookup_type='YES_NO' and flv.view_application_id=0 and flv.security_group_id=fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) and flv.lookup_code in ('Y','N') and flv.enabled_flag='Y' order by flv.meaning xxen_util.meaning('N','YES_NO',0) 45 AUTO_INSTANTIATION Char varchar2 1 xxen_util.lookup_code(:$column$.automatic_instantiation,'YES_NO',0) Y Y 46 ACT_ACTIVITY LOV custom varchar2 40 select distinct msibk.concatenated_segments value, msibk.description from mtl_eam_asset_activities mea, mtl_system_items_b_kfv msibk, mtl_parameters mp, csi_item_instances cii where mp.organization_code=:$column$.organization_code and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and cii.instance_number=:$column$.asset_number and cii.last_vld_organization_id=mp.organization_id and mea.maintenance_object_id=cii.instance_id and mea.maintenance_object_type=3 and mea.organization_id=mp.organization_id and mea.asset_activity_id=msibk.inventory_item_id and mea.organization_id=msibk.organization_id and msibk.eam_item_type=2 and nvl(mea.tmpl_flag,'N')='N' and nvl(mea.start_date_active,sysdate-1)<sysdate and nvl(mea.end_date_active,sysdate+1)>sysdate order by msibk.concatenated_segments Activity for Activity row. Must be associated with the same asset/asset group as the header. 47 ACT_ACTIVITY_ID Number number select mea.activity_association_id from mtl_eam_asset_activities mea, mtl_system_items_b_kfv msibk, mtl_parameters mp, csi_item_instances cii where mp.organization_code=:$column$.organization_code and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and cii.instance_number=:$column$.asset_number and cii.last_vld_organization_id=mp.organization_id and mea.maintenance_object_id=cii.instance_id and mea.maintenance_object_type=3 and mea.organization_id=mp.organization_id and mea.asset_activity_id=msibk.inventory_item_id and mea.organization_id=msibk.organization_id and msibk.concatenated_segments=:$column$.act_activity and msibk.eam_item_type=2 and nvl(mea.tmpl_flag,'N')='N' and nvl(mea.start_date_active,sysdate-1)<sysdate and nvl(mea.end_date_active,sysdate+1)>sysdate and rownum=1 Y Y 48 ACT_INTERVAL_MULTIPLE Number number 1 49 ACT_ALLOW_REPEAT LOV custom varchar2 select flv.lookup_code id, flv.meaning value, flv.description from fnd_lookup_values_vl flv where flv.lookup_type='YES_NO' and flv.view_application_id=0 and flv.security_group_id=fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id) and flv.lookup_code in ('Y','N') and flv.enabled_flag='Y' order by flv.meaning 50 ACT_DAY_TOLERANCE Number number 51 ACT_NEXT_SVC_START Date date Y 52 ACT_NEXT_SVC_END Date date Y 53 ACT_LAST_SVC_START Date date Activity rows: last service start date of the activity association, recorded by work order completion. Read-only. Y 54 ACT_LAST_SVC_END Date date Activity rows: last service end date of the activity association, recorded by work order completion. Read-only. Y 55 RULE_TYPE_DSP LOV custom varchar2 13 select 1 id, 'Day Interval' value, null description from dual union all select 2 id, 'Runtime Meter' value, null description from dual union all select 3 id, 'List Date' value, null description from dual Internal value derived from the populated Date Rules, Meter Rules, or List Dates columns. Leave blank. Y Y 56 RULE_TYPE Number number select case when :$column$.rule_day_interval is not null or :$column$.rule_effective_date_from is not null or :$column$.rule_effective_date_to is not null or :$column$.rule_base_date is not null then 1 when :$column$.rule_meter is not null or :$column$.rule_runtime_interval is not null or :$column$.rule_last_service_reading is not null or :$column$.rule_effective_reading_from is not null or :$column$.rule_effective_reading_to is not null then 2 when :$column$.rule_list_date is not null or :$column$.rule_list_date_desc is not null then 3 end from dual Y Y 57 RULE_DAY_INTERVAL Number number Date Rule: number of days between services. Required for Day Interval schedules and for Rule Based schedules that use date rules. 58 RULE_METER LOV custom varchar2 80 select distinct ccv.name value, ccv.description from csi_counters_vl ccv, csi_counter_associations cca, csi_item_instances cii, mtl_system_items_b_kfv msibk, mtl_parameters mp where ccv.counter_id=cca.counter_id and cca.source_object_code in ('CP','REGULAR') and ccv.used_in_scheduling='Y' and mp.organization_code=:$column$.organization_code and ( (cca.source_object_id=cii.instance_id and cii.instance_number=:$column$.asset_number and cii.last_vld_organization_id=mp.organization_id) or (cca.source_object_id=msibk.inventory_item_id and msibk.concatenated_segments=:$column$.asset_group and msibk.organization_id=mp.organization_id) ) order by ccv.name Meter Rule: meter/counter used for runtime scheduling. Required when using meter rules. 59 RULE_METER_ID Number number select ccv.counter_id from csi_counters_vl ccv where ccv.name=:$column$.rule_meter and rownum=1 Y Y 60 RULE_RUNTIME_INTERVAL Number number Meter Rule: runtime interval between services. Required when using meter rules. 61 RULE_LAST_SERVICE_READING Number number Y 62 RULE_EFFECTIVE_READING_FROM Number number 63 RULE_EFFECTIVE_READING_TO Number number 64 RULE_EFFECTIVE_DATE_FROM Date date 65 RULE_EFFECTIVE_DATE_TO Date date 66 RULE_LIST_DATE Date date List Dates: service date. Required when Schedule Type is List Dates. 67 RULE_LIST_DATE_DESC Char varchar2 50 68 RULE_BASE_DATE Date date 69 DELETE_RULE LOV custom varchar2 select xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$column$.rule_id is not null 70 RULE_ID Number number Y Y 71 LSI_METER LOV custom varchar2 80 select distinct ccv.name value, ccv.description from csi_counters_vl ccv, csi_counter_associations cca, csi_item_instances cii, mtl_system_items_b_kfv msibk, mtl_parameters mp where ccv.counter_id=cca.counter_id and cca.source_object_code in ('CP','REGULAR') and ccv.used_in_scheduling='Y' and mp.organization_code=:$column$.organization_code and ( (cca.source_object_id=cii.instance_id and cii.instance_number=:$column$.asset_number and cii.last_vld_organization_id=mp.organization_id) or (cca.source_object_id=msibk.inventory_item_id and msibk.concatenated_segments=:$column$.asset_group and msibk.organization_id=mp.organization_id) ) order by ccv.name 72 LSI_METER_ID Number number select ccv.counter_id from csi_counters_vl ccv where ccv.name=:$column$.lsi_meter and rownum=1 Y Y 73 LSI_LAST_SERVICE_READING Number number 74 LSI_PREV_SERVICE_READING Number number 75 LSI_WIP_JOB Char varchar2 240 Optional WIP work order number associated with the last service reading. 76 LSI_WIP_ENTITY_ID Number number select we.wip_entity_id from wip_entities we, mtl_parameters mp where we.wip_entity_name=:$column$.lsi_wip_job and we.organization_id=mp.organization_id and mp.organization_code=:$column$.organization_code and rownum=1 Y Y Error SQL Order By upload_row Success SQL select x.action_, x.status_, x.message_, x.modified_columns_, x.upload_row, x.organization_code, x.organization_id, x.schedule_name, x.asset_type, x.asset_number, x.asset_group, x.asset_group_id, x.activity, x.activity_id, x.set_name, x.set_name_description, x.set_name_end_date, x.set_name_local_flag, x.schedule_type, x.schedule_type_code, x.effective_from, x.effective_to, x.lead_time_in_days, x.work_order_status, x.work_order_status_id, x.next_wo_only, x.generate_next_wo, x.day_tolerance, x.use, x.rescheduling_point, x.base_date, x.base_reading, x.suggest_next_service, x.scheduling_method, x.use_date_that_comes, x.whichever_first, x.intervals_per_cycle, x.reschedule_manual_work_orders, x.include_manual, x.run_to_failure, x.non_scheduled_flag, x.default_schedule, x.default_implement, x.automatic_instantiation, x.auto_instantiation, x.act_activity, x.act_activity_id, nvl(epa.interval_multiple,x.act_interval_multiple) act_interval_multiple, nvl(xxen_util.meaning(epa.allow_repeat_in_cycle,'YES_NO',0),x.act_allow_repeat) act_allow_repeat, nvl(epa.day_tolerance,x.act_day_tolerance) act_day_tolerance, nvl(epa.next_service_start_date,x.act_next_svc_start) act_next_svc_start, nvl(epa.next_service_end_date,x.act_next_svc_end) act_next_svc_end, mea.last_service_start_date act_last_svc_start, mea.last_service_end_date act_last_svc_end, x.rule_type_dsp, x.rule_type, x.rule_day_interval, x.rule_meter, x.rule_meter_id, x.rule_runtime_interval, nvl((select epsr.last_service_reading from eam_pm_scheduling_rules epsr where x.rule_id=epsr.rule_id),x.rule_last_service_reading) rule_last_service_reading, x.rule_effective_reading_from, x.rule_effective_reading_to, x.rule_effective_date_from, x.rule_effective_date_to, x.rule_list_date, x.rule_list_date_desc, x.rule_base_date, x.delete_rule, x.rule_id, x.lsi_meter, x.lsi_meter_id, nvl(epls.last_service_reading,x.lsi_last_service_reading) lsi_last_service_reading, nvl(epls.prev_service_reading,x.lsi_prev_service_reading) lsi_prev_service_reading, nvl(we.wip_entity_name,x.lsi_wip_job) lsi_wip_job, nvl(epls.wip_entity_id,x.lsi_wip_entity_id) lsi_wip_entity_id from ( select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.upload_row, xu.organization_code, xu.organization_id, nvl(eps.name,xu.schedule_name) schedule_name, xu.asset_type, xu.asset_number, xu.asset_group, xu.asset_group_id, xu.activity, xu.activity_id, nvl(epsn.set_name,xu.set_name) set_name, nvl(epsn.description,xu.set_name_description) set_name_description, nvl(epsn.end_date,xu.set_name_end_date) set_name_end_date, xu.set_name_local_flag, nvl(decode(eps.type_code,10,'Rule Based',17,'Day Interval',20,'List Dates',xxen_util.meaning(to_char(eps.type_code),'EAM_PM_TYPE',700)),xu.schedule_type) schedule_type, nvl(eps.type_code,xu.schedule_type_code) schedule_type_code, nvl(eps.from_effective_date,xu.effective_from) effective_from, nvl(eps.to_effective_date,xu.effective_to) effective_to, nvl(eps.lead_time,xu.lead_time_in_days) lead_time_in_days, nvl(ewsv.work_order_status,xu.work_order_status) work_order_status, nvl(eps.generate_wo_status,xu.work_order_status_id) work_order_status_id, nvl(xxen_util.meaning(eps.generate_next_work_order,'YES_NO',0),xu.next_wo_only) next_wo_only, nvl(eps.generate_next_work_order,xu.generate_next_wo) generate_next_wo, nvl(eps.day_tolerance,xu.day_tolerance) day_tolerance, nvl(decode(eps.rescheduling_point,1,'Actual Start Date',2,'Actual End Date',3,'Scheduled Start Date',4,'Scheduled End Date',5,'Base Date',6,'Base Meter Reading'),xu.use) use, nvl(eps.rescheduling_point,xu.rescheduling_point) rescheduling_point, nvl(eps.base_date,xu.base_date) base_date, nvl(eps.base_reading,xu.base_reading) base_reading, nvl(decode(eps.scheduling_method_code,10,'Start Date',20,'End Date',xxen_util.meaning(to_char(eps.scheduling_method_code),'EAM_PM_SCHEDULING_METHOD',700)),xu.suggest_next_service) suggest_next_service, nvl(eps.scheduling_method_code,xu.scheduling_method) scheduling_method, nvl(decode(eps.whichever_first,'Y','First','N','Last'),xu.use_date_that_comes) use_date_that_comes, nvl(eps.whichever_first,xu.whichever_first) whichever_first, nvl(eps.interval_per_cycle,xu.intervals_per_cycle) intervals_per_cycle, nvl(xxen_util.meaning(eps.include_manual,'YES_NO',0),xu.reschedule_manual_work_orders) reschedule_manual_work_orders, nvl(eps.include_manual,xu.include_manual) include_manual, nvl(xxen_util.meaning(eps.non_scheduled_flag,'YES_NO',0),xu.run_to_failure) run_to_failure, nvl(eps.non_scheduled_flag,xu.non_scheduled_flag) non_scheduled_flag, nvl(xxen_util.meaning(eps.default_implement,'YES_NO',0),xu.default_schedule) default_schedule, nvl(eps.default_implement,xu.default_implement) default_implement, nvl(xxen_util.meaning(eps.auto_instantiation_flag,'YES_NO',0),xu.automatic_instantiation) automatic_instantiation, nvl(eps.auto_instantiation_flag,xu.auto_instantiation) auto_instantiation, xu.act_activity, xu.act_activity_id, xu.act_interval_multiple, xu.act_allow_repeat, xu.act_day_tolerance, xu.act_next_svc_start, xu.act_next_svc_end, xu.rule_type_dsp, xu.rule_type, xu.rule_day_interval, xu.rule_meter, xu.rule_meter_id, xu.rule_runtime_interval, xu.rule_last_service_reading, xu.rule_effective_reading_from, xu.rule_effective_reading_to, xu.rule_effective_date_from, xu.rule_effective_date_to, xu.rule_list_date, xu.rule_list_date_desc, xu.rule_base_date, xu.delete_rule, xu.rule_id, xu.lsi_meter, xu.lsi_meter_id, xu.lsi_last_service_reading, xu.lsi_prev_service_reading, xu.lsi_wip_job, xu.lsi_wip_entity_id, eps.pm_schedule_id from xxen_eam_pm_schedule_up_2458_u xu, eam_pm_schedulings eps, eam_pm_set_names epsn, eam_wo_statuses_v ewsv where xu.status_code_=xxen_upload.status_success and xu.schedule_name=eps.name(+) and nvl(eps.tmpl_flag(+),'N')='N' and eps.set_name_id=epsn.set_name_id(+) and eps.generate_wo_status=ewsv.status_id(+) ) x, --before 12c an outer joined table may reference at most one other table, so the upload rows and their schedule are wrapped eam_pm_activities epa, mtl_eam_asset_activities mea, eam_pm_last_service epls, wip_entities we where x.pm_schedule_id=epa.pm_schedule_id(+) and x.act_activity_id=epa.activity_association_id(+) and x.act_activity_id=mea.activity_association_id(+) and x.activity_id=epls.activity_association_id(+) and x.lsi_meter_id=epls.meter_id(+) and epls.wip_entity_id=we.wip_entity_id(+)