[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_failed varchar2(1);
select
PM_SCHEDULE_ID,
RULE_TYPE,
DAY_INTERVAL,
METER_ID,
RUNTIME_INTERVAL,
EFFECTIVE_READING_FROM,
EFFECTIVE_READING_TO,
EFFECTIVE_DATE_FROM,
EFFECTIVE_DATE_TO,
LIST_DATE,
LIST_DATE_DESC
from eam_pm_scheduling_rules
where pm_schedule_id = p_pm_schedule_id;
select
epa.pm_schedule_id,
meaa.activity_association_id,
epa.interval_multiple,
epa.allow_repeat_in_cycle,
epa.day_tolerance,
epa.next_service_start_date,
epa.next_service_end_date
from eam_pm_activities epa, mtl_eam_asset_activities meaa
where epa.pm_schedule_id=p_pm_schedule_id
and epa.activity_association_id =meaa.source_tmpl_id
and meaa.maintenance_object_id=l_maintenance_object_id;
select
PM_SCHEDULE_ID,
ACTIVITY_ASSOCIATION_ID,
NON_SCHEDULED_FLAG,
FROM_EFFECTIVE_DATE,
TO_EFFECTIVE_DATE,
RESCHEDULING_POINT,
LEAD_TIME,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
DAY_TOLERANCE ,
SOURCE_CODE ,
SOURCE_LINE ,
DEFAULT_IMPLEMENT,
WHICHEVER_FIRST ,
INCLUDE_MANUAL ,
SET_NAME_ID ,
SCHEDULING_METHOD_CODE,
TYPE_CODE,
NEXT_SERVICE_START_DATE,
NEXT_SERVICE_END_DATE,
SOURCE_TMPL_ID ,
AUTO_INSTANTIATION_FLAG ,
NAME ,
TMPL_FLAG ,
GENERATE_WO_STATUS ,
INTERVAL_PER_CYCLE ,
CURRENT_CYCLE ,
CURRENT_SEQ ,
CURRENT_WO_SEQ ,
BASE_DATE ,
BASE_READING ,
EAM_LAST_CYCLIC_ACT ,
MAINTENANCE_OBJECT_ID ,
MAINTENANCE_OBJECT_TYPE ,
/* added for PM Reviewer -- start-- */
LAST_REVIEWED_DATE ,
Last_reviewed_by ,
/* ---PM reviewer--- end---- */
generate_next_work_order
into pm_header_row
from eam_pm_schedulings
where pm_schedule_id=p_pm_schedule_id;
select eam_pm_schedulings_s.nextval into l_pm_schedule_id from dual;
select maintenance_object_id, maintenance_object_type
into l_maintenance_object_id, l_maintenance_object_type
from mtl_eam_asset_activities
where activity_association_id=p_activity_assoc_id;
insert into eam_pm_schedulings (
PM_SCHEDULE_ID,
ACTIVITY_ASSOCIATION_ID,
NON_SCHEDULED_FLAG,
FROM_EFFECTIVE_DATE,
TO_EFFECTIVE_DATE,
RESCHEDULING_POINT,
LEAD_TIME,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
DAY_TOLERANCE ,
SOURCE_CODE ,
SOURCE_LINE ,
DEFAULT_IMPLEMENT,
WHICHEVER_FIRST ,
INCLUDE_MANUAL ,
SET_NAME_ID,
SCHEDULING_METHOD_CODE,
TYPE_CODE,
NEXT_SERVICE_START_DATE,
NEXT_SERVICE_END_DATE,
SOURCE_TMPL_ID,
AUTO_INSTANTIATION_FLAG,
NAME ,
TMPL_FLAG ,
GENERATE_WO_STATUS ,
INTERVAL_PER_CYCLE ,
CURRENT_CYCLE ,
CURRENT_SEQ ,
CURRENT_WO_SEQ ,
BASE_DATE ,
BASE_READING ,
EAM_LAST_CYCLIC_ACT ,
MAINTENANCE_OBJECT_ID ,
MAINTENANCE_OBJECT_TYPE ,
/* added for PM Reviewer -- start-- */
LAST_REVIEWED_DATE ,
Last_reviewed_by ,
/* ---PM reviewer--- end---- */
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date ,
generate_next_work_order )
values (
l_pm_schedule_id,
pm_header_row.ACTIVITY_ASSOCIATION_ID,
pm_header_row.NON_SCHEDULED_FLAG,
pm_header_row.FROM_EFFECTIVE_DATE,
pm_header_row.TO_EFFECTIVE_DATE,
pm_header_row.RESCHEDULING_POINT,
pm_header_row.LEAD_TIME,
pm_header_row.ATTRIBUTE_CATEGORY,
pm_header_row.ATTRIBUTE1,
pm_header_row.ATTRIBUTE2 ,
pm_header_row.ATTRIBUTE3 ,
pm_header_row.ATTRIBUTE4 ,
pm_header_row.ATTRIBUTE5 ,
pm_header_row.ATTRIBUTE6 ,
pm_header_row.ATTRIBUTE7 ,
pm_header_row.ATTRIBUTE8 ,
pm_header_row.ATTRIBUTE9 ,
pm_header_row.ATTRIBUTE10 ,
pm_header_row.ATTRIBUTE11 ,
pm_header_row.ATTRIBUTE12 ,
pm_header_row.ATTRIBUTE13 ,
pm_header_row.ATTRIBUTE14 ,
pm_header_row.ATTRIBUTE15 ,
pm_header_row.DAY_TOLERANCE ,
pm_header_row.SOURCE_CODE ,
pm_header_row.SOURCE_LINE ,
pm_header_row.DEFAULT_IMPLEMENT,
pm_header_row.WHICHEVER_FIRST ,
pm_header_row.INCLUDE_MANUAL ,
pm_header_row.SET_NAME_ID,
pm_header_row.SCHEDULING_METHOD_CODE ,
pm_header_row.TYPE_CODE,
pm_header_row.NEXT_SERVICE_START_DATE,
pm_header_row.NEXT_SERVICE_END_DATE,
pm_header_row.SOURCE_TMPL_ID,
pm_header_row.AUTO_INSTANTIATION_FLAG,
pm_header_row.NAME ,
pm_header_row.TMPL_FLAG ,
pm_header_row.GENERATE_WO_STATUS ,
pm_header_row.INTERVAL_PER_CYCLE ,
pm_header_row.CURRENT_CYCLE ,
pm_header_row.CURRENT_SEQ ,
pm_header_row.CURRENT_WO_SEQ ,
pm_header_row.BASE_DATE ,
pm_header_row.BASE_READING ,
pm_header_row.EAM_LAST_CYCLIC_ACT ,
pm_header_row.MAINTENANCE_OBJECT_ID ,
pm_header_row.MAINTENANCE_OBJECT_TYPE ,
/* added for PM Reviewer -- start-- */
pm_header_row.LAST_REVIEWED_DATE ,
pm_header_row.Last_reviewed_by ,
/* ---PM reviewer--- end---- */
fnd_global.user_id,
sysdate,
fnd_global.login_id,
fnd_global.user_id,
sysdate ,
pm_header_row.generate_next_work_order );
insert into eam_pm_activities
(pm_schedule_id,
activity_association_id,
interval_multiple,
allow_repeat_in_cycle,
day_tolerance,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date )
values
(l_pm_schedule_id,
a_pm_activity.activity_association_id,
a_pm_activity.interval_multiple,
a_pm_activity.allow_repeat_in_cycle,
a_pm_activity.day_tolerance,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
fnd_global.user_id,
sysdate );
eam_pmdef_pub.update_pm_last_cyclic_act
( p_api_version => 1.0 ,
p_init_msg_list => 'F' ,
p_commit => 'F' ,
p_validation_level => 100 ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_pm_schedule_id => l_pm_schedule_id
);
l_update_failed := 'Y';
select maintenance_object_id, maintenance_object_type
into l_maintenance_object_id, l_maintenance_object_type
from mtl_eam_asset_activities
where activity_association_id=p_activity_assoc_id;
select cca.counter_id into l_meter_id
from CSI_COUNTER_ASSOCIATIONS cca, CSI_COUNTERS_B ccb
where cca.counter_id = ccb.counter_id
and ccb.created_from_counter_tmpl_id=a_pm_rule.meter_id
and cca.source_object_id= l_maintenance_object_id ;
insert into eam_pm_scheduling_rules
( PM_SCHEDULE_ID,
RULE_TYPE,
DAY_INTERVAL,
METER_ID ,
RUNTIME_INTERVAL,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
EFFECTIVE_READING_FROM ,
EFFECTIVE_READING_TO ,
EFFECTIVE_DATE_FROM ,
EFFECTIVE_DATE_TO ,
LIST_DATE ,
LIST_DATE_DESC,
RULE_ID)
values
(l_pm_schedule_id,
a_pm_rule.RULE_TYPE,
a_pm_rule.DAY_INTERVAL,
l_meter_id,
a_pm_rule.RUNTIME_INTERVAL,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate ,
fnd_global.user_id,
a_pm_rule.EFFECTIVE_READING_FROM ,
a_pm_rule.EFFECTIVE_READING_TO ,
a_pm_rule.EFFECTIVE_DATE_FROM ,
a_pm_rule.EFFECTIVE_DATE_TO ,
a_pm_rule.LIST_DATE ,
a_pm_rule.LIST_DATE_DESC ,
eam_pm_scheduling_rules_s.nextval);
select initial_reading into l_initial_reading from
(select initial_reading from CSI_COUNTERS_B where counter_id = a_pm_rule.meter_id
union
select initial_reading from CSI_COUNTER_TEMPLATE_B where counter_id = a_pm_rule.meter_id
);
select EAM_LAST_CYCLIC_ACT into l_last_act from eam_pm_schedulings
where pm_schedule_id = l_pm_schedule_id;
select count (*) into l_count
from eam_pm_last_service
where meter_id=l_meter_id and ACTIVITY_ASSOCIATION_ID =l_last_act;
insert into eam_pm_last_service
( METER_ID ,
ACTIVITY_ASSOCIATION_ID ,
LAST_SERVICE_READING ,
PREV_SERVICE_READING ,
WIP_ENTITY_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
values
(
l_meter_id,
l_last_act,
l_initial_reading,
null,
null,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
);
eam_pmdef_pub.update_pm_last_service_reading
( p_api_version => 1.0 ,
p_init_msg_list => 'F' ,
p_commit => 'F' ,
p_validation_level => 100 ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_pm_schedule_id => l_pm_schedule_id
);
l_update_failed := 'Y';
if l_update_failed = 'Y' then
x_return_status := fnd_api.g_ret_sts_error;
select pm_schedule_id
from eam_pm_schedulings eps, mtl_eam_asset_activities meaa, eam_pm_set_names epsn
where eps.activity_association_id=meaa.source_tmpl_id
and meaa.activity_association_id=l_activity_assoc_id
and eps.tmpl_flag='Y'
and eps.auto_instantiation_flag='Y'
and eps.set_name_id=epsn.set_name_id
and (epsn.end_date is null or (epsn.end_date > sysdate and epsn.end_date > nvl(eps.to_effective_date, sysdate)));
select distinct eps.pm_schedule_id
from eam_pm_schedulings eps, eam_pm_activities epa,mtl_eam_asset_activities meaa, eam_pm_set_names epsn
where eps.pm_schedule_id=epa.pm_schedule_id
and epa.activity_association_id=meaa.source_tmpl_id
and meaa.activity_association_id=l_activity_assoc_id
and eps.tmpl_flag='Y'
and eps.auto_instantiation_flag='Y'
and eps.set_name_id=epsn.set_name_id
and (epsn.end_date is null or (epsn.end_date > sysdate and epsn.end_date > nvl(eps.to_effective_date, sysdate)));
select eam_pm_schedulings_s.nextval into l_pm_schedule_id from dual;
insert into eam_pm_schedulings (
PM_SCHEDULE_ID,
ACTIVITY_ASSOCIATION_ID,
NON_SCHEDULED_FLAG,
FROM_EFFECTIVE_DATE,
TO_EFFECTIVE_DATE,
RESCHEDULING_POINT,
LEAD_TIME,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
DAY_TOLERANCE ,
SOURCE_CODE ,
SOURCE_LINE ,
DEFAULT_IMPLEMENT,
WHICHEVER_FIRST ,
INCLUDE_MANUAL ,
SET_NAME_ID,
SCHEDULING_METHOD_CODE,
TYPE_CODE,
NEXT_SERVICE_START_DATE,
NEXT_SERVICE_END_DATE,
SOURCE_TMPL_ID ,
AUTO_INSTANTIATION_FLAG,
NAME ,
TMPL_FLAG ,
created_by,
creation_date,
last_update_login,
last_updated_by,
last_update_date,
GENERATE_WO_STATUS,
INTERVAL_PER_CYCLE,
CURRENT_CYCLE,
CURRENT_SEQ,
CURRENT_WO_SEQ,
BASE_DATE,
BASE_READING,
EAM_LAST_CYCLIC_ACT,
MAINTENANCE_OBJECT_ID,
MAINTENANCE_OBJECT_TYPE,
/* added for PM Reviewer -- start-- */
LAST_REVIEWED_DATE,
Last_reviewed_by
/* ---PM reviewer--- end---- */,
GENERATE_NEXT_WORK_ORDER )
values (
l_pm_schedule_id,
p_pm_schedule_rec.ACTIVITY_ASSOCIATION_ID,
p_pm_schedule_rec.NON_SCHEDULED_FLAG,
p_pm_schedule_rec.FROM_EFFECTIVE_DATE,
p_pm_schedule_rec.TO_EFFECTIVE_DATE,
p_pm_schedule_rec.RESCHEDULING_POINT,
p_pm_schedule_rec.LEAD_TIME,
p_pm_schedule_rec.ATTRIBUTE_CATEGORY,
p_pm_schedule_rec.ATTRIBUTE1,
p_pm_schedule_rec.ATTRIBUTE2 ,
p_pm_schedule_rec.ATTRIBUTE3 ,
p_pm_schedule_rec.ATTRIBUTE4 ,
p_pm_schedule_rec.ATTRIBUTE5 ,
p_pm_schedule_rec.ATTRIBUTE6 ,
p_pm_schedule_rec.ATTRIBUTE7 ,
p_pm_schedule_rec.ATTRIBUTE8 ,
p_pm_schedule_rec.ATTRIBUTE9 ,
p_pm_schedule_rec.ATTRIBUTE10 ,
p_pm_schedule_rec.ATTRIBUTE11 ,
p_pm_schedule_rec.ATTRIBUTE12 ,
p_pm_schedule_rec.ATTRIBUTE13 ,
p_pm_schedule_rec.ATTRIBUTE14 ,
p_pm_schedule_rec.ATTRIBUTE15 ,
p_pm_schedule_rec.DAY_TOLERANCE ,
p_pm_schedule_rec.SOURCE_CODE ,
p_pm_schedule_rec.SOURCE_LINE ,
p_pm_schedule_rec.DEFAULT_IMPLEMENT,
p_pm_schedule_rec.WHICHEVER_FIRST ,
p_pm_schedule_rec.INCLUDE_MANUAL ,
p_pm_schedule_rec.SET_NAME_ID,
p_pm_schedule_rec.SCHEDULING_METHOD_CODE ,
p_pm_schedule_rec.TYPE_CODE,
p_pm_schedule_rec.NEXT_SERVICE_START_DATE,
p_pm_schedule_rec.NEXT_SERVICE_END_DATE,
p_pm_schedule_rec.SOURCE_TMPL_ID ,
p_pm_schedule_rec.AUTO_INSTANTIATION_FLAG,
p_pm_schedule_rec.NAME ,
p_pm_schedule_rec.TMPL_FLAG ,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
fnd_global.user_id,
sysdate,
p_pm_schedule_rec.GENERATE_WO_STATUS ,
p_pm_schedule_rec.INTERVAL_PER_CYCLE ,
p_pm_schedule_rec.CURRENT_CYCLE ,
p_pm_schedule_rec.CURRENT_SEQ ,
p_pm_schedule_rec.CURRENT_WO_SEQ ,
p_pm_schedule_rec.BASE_DATE ,
p_pm_schedule_rec.BASE_READING ,
p_pm_schedule_rec.EAM_LAST_CYCLIC_ACT ,
p_pm_schedule_rec.MAINTENANCE_OBJECT_ID ,
p_pm_schedule_rec.MAINTENANCE_OBJECT_TYPE,
/* added for PM Reviewer -- start-- */
p_pm_schedule_rec.LAST_REVIEWED_DATE,
p_pm_schedule_rec.Last_reviewed_by,
/* ---PM reviewer--- end---- */
p_pm_schedule_rec.generate_next_work_order );
insert into eam_pm_activities(
pm_schedule_id,
activity_association_id,
interval_multiple,
allow_repeat_in_cycle,
day_tolerance,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
)
values(
l_pm_schedule_id,
p_pm_activities_tbl(i).activity_association_id,
p_pm_activities_tbl(i).interval_multiple,
p_pm_activities_tbl(i).allow_repeat_in_cycle,
p_pm_activities_tbl(i).day_tolerance,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate ,
fnd_global.user_id
);
insert into eam_pm_scheduling_rules
(RULE_ID,
PM_SCHEDULE_ID,
RULE_TYPE,
DAY_INTERVAL,
METER_ID ,
RUNTIME_INTERVAL,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
EFFECTIVE_READING_FROM ,
EFFECTIVE_READING_TO ,
EFFECTIVE_DATE_FROM ,
EFFECTIVE_DATE_TO ,
LIST_DATE ,
LIST_DATE_DESC
)
values
(eam_pm_scheduling_rules_s.nextval,
l_pm_schedule_id,
p_pm_day_interval_rules_tbl(i).RULE_TYPE,
p_pm_day_interval_rules_tbl(i).DAY_INTERVAL,
p_pm_day_interval_rules_tbl(i).METER_ID ,
p_pm_day_interval_rules_tbl(i).RUNTIME_INTERVAL,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate ,
fnd_global.user_id,
p_pm_day_interval_rules_tbl(i).EFFECTIVE_READING_FROM ,
p_pm_day_interval_rules_tbl(i).EFFECTIVE_READING_TO ,
p_pm_day_interval_rules_tbl(i).EFFECTIVE_DATE_FROM ,
p_pm_day_interval_rules_tbl(i).EFFECTIVE_DATE_TO ,
p_pm_day_interval_rules_tbl(i).LIST_DATE ,
p_pm_day_interval_rules_tbl(i).LIST_DATE_DESC
);
insert into eam_pm_scheduling_rules
( RULE_ID,
PM_SCHEDULE_ID,
RULE_TYPE,
DAY_INTERVAL,
METER_ID ,
RUNTIME_INTERVAL,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
EFFECTIVE_READING_FROM ,
EFFECTIVE_READING_TO ,
EFFECTIVE_DATE_FROM ,
EFFECTIVE_DATE_TO ,
LIST_DATE ,
LIST_DATE_DESC
)
values
(eam_pm_scheduling_rules_s.nextval,
l_pm_schedule_id,
p_pm_runtime_rules_tbl(i).RULE_TYPE,
p_pm_runtime_rules_tbl(i).DAY_INTERVAL,
p_pm_runtime_rules_tbl(i).METER_ID ,
p_pm_runtime_rules_tbl(i).RUNTIME_INTERVAL,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate ,
fnd_global.user_id,
p_pm_runtime_rules_tbl(i).EFFECTIVE_READING_FROM ,
p_pm_runtime_rules_tbl(i).EFFECTIVE_READING_TO ,
p_pm_runtime_rules_tbl(i).EFFECTIVE_DATE_FROM ,
p_pm_runtime_rules_tbl(i).EFFECTIVE_DATE_TO ,
p_pm_runtime_rules_tbl(i).LIST_DATE ,
p_pm_runtime_rules_tbl(i).LIST_DATE_DESC
);
insert into eam_pm_scheduling_rules
( RULE_ID,
PM_SCHEDULE_ID,
RULE_TYPE,
DAY_INTERVAL,
METER_ID ,
RUNTIME_INTERVAL,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
EFFECTIVE_READING_FROM ,
EFFECTIVE_READING_TO ,
EFFECTIVE_DATE_FROM ,
EFFECTIVE_DATE_TO ,
LIST_DATE ,
LIST_DATE_DESC
)
values
(eam_pm_scheduling_rules_s.nextval,
l_pm_schedule_id,
p_pm_list_date_rules_tbl(i).RULE_TYPE,
p_pm_list_date_rules_tbl(i).DAY_INTERVAL,
p_pm_list_date_rules_tbl(i).METER_ID ,
p_pm_list_date_rules_tbl(i).RUNTIME_INTERVAL,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate ,
fnd_global.user_id,
p_pm_list_date_rules_tbl(i).EFFECTIVE_READING_FROM ,
p_pm_list_date_rules_tbl(i).EFFECTIVE_READING_TO ,
p_pm_list_date_rules_tbl(i).EFFECTIVE_DATE_FROM ,
p_pm_list_date_rules_tbl(i).EFFECTIVE_DATE_TO ,
p_pm_list_date_rules_tbl(i).LIST_DATE ,
p_pm_list_date_rules_tbl(i).LIST_DATE_DESC
);
procedure update_pm_def
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_pm_schedule_rec IN pm_scheduling_rec_type:=null,
p_pm_activities_tbl IN pm_activities_grp_tbl_type,
p_pm_day_interval_rules_tbl IN pm_rule_tbl_type,
p_pm_runtime_rules_tbl IN pm_rule_tbl_type,
p_pm_list_date_rules_tbl IN pm_rule_tbl_type
)
is
l_api_name CONSTANT VARCHAR2(30) :='update_pm_def';
l_selected_day_rules_tbl pm_rule_tbl_type;
l_selected_runtime_rules_tbl pm_rule_tbl_type;
l_selected_list_date_rules_tbl pm_rule_tbl_type;
SAVEPOINT update_pm_def_pub;
update eam_pm_schedulings set
ACTIVITY_ASSOCIATION_ID=p_pm_schedule_rec.ACTIVITY_ASSOCIATION_ID,
NON_SCHEDULED_FLAG=p_pm_schedule_rec.NON_SCHEDULED_FLAG,
FROM_EFFECTIVE_DATE=p_pm_schedule_rec.FROM_EFFECTIVE_DATE,
TO_EFFECTIVE_DATE=p_pm_schedule_rec.TO_EFFECTIVE_DATE,
RESCHEDULING_POINT=p_pm_schedule_rec.RESCHEDULING_POINT,
LEAD_TIME=p_pm_schedule_rec.LEAD_TIME,
ATTRIBUTE_CATEGORY=p_pm_schedule_rec.ATTRIBUTE_CATEGORY,
ATTRIBUTE1=p_pm_schedule_rec.ATTRIBUTE1,
ATTRIBUTE2=p_pm_schedule_rec.ATTRIBUTE2 ,
ATTRIBUTE3=p_pm_schedule_rec.ATTRIBUTE3 ,
ATTRIBUTE4=p_pm_schedule_rec.ATTRIBUTE4 ,
ATTRIBUTE5=p_pm_schedule_rec.ATTRIBUTE5 ,
ATTRIBUTE6=p_pm_schedule_rec.ATTRIBUTE6 ,
ATTRIBUTE7=p_pm_schedule_rec.ATTRIBUTE7 ,
ATTRIBUTE8=p_pm_schedule_rec.ATTRIBUTE8 ,
ATTRIBUTE9=p_pm_schedule_rec.ATTRIBUTE9 ,
ATTRIBUTE10=p_pm_schedule_rec.ATTRIBUTE10 ,
ATTRIBUTE11=p_pm_schedule_rec.ATTRIBUTE11 ,
ATTRIBUTE12=p_pm_schedule_rec.ATTRIBUTE12 ,
ATTRIBUTE13=p_pm_schedule_rec.ATTRIBUTE13 ,
ATTRIBUTE14=p_pm_schedule_rec.ATTRIBUTE14 ,
ATTRIBUTE15=p_pm_schedule_rec.ATTRIBUTE15 ,
DAY_TOLERANCE =p_pm_schedule_rec.DAY_TOLERANCE ,
SOURCE_CODE=p_pm_schedule_rec.SOURCE_CODE ,
SOURCE_LINE= p_pm_schedule_rec.SOURCE_LINE ,
DEFAULT_IMPLEMENT=p_pm_schedule_rec.DEFAULT_IMPLEMENT,
WHICHEVER_FIRST = p_pm_schedule_rec.WHICHEVER_FIRST ,
INCLUDE_MANUAL = p_pm_schedule_rec.INCLUDE_MANUAL ,
SET_NAME_ID=p_pm_schedule_rec.SET_NAME_ID,
SCHEDULING_METHOD_CODE= p_pm_schedule_rec.SCHEDULING_METHOD_CODE ,
TYPE_CODE=p_pm_schedule_rec.TYPE_CODE,
NEXT_SERVICE_START_DATE=p_pm_schedule_rec.NEXT_SERVICE_START_DATE,
NEXT_SERVICE_END_DATE=p_pm_schedule_rec.NEXT_SERVICE_END_DATE,
SOURCE_TMPL_ID = p_pm_schedule_rec.SOURCE_TMPL_ID,
AUTO_INSTANTIATION_FLAG =p_pm_schedule_rec.AUTO_INSTANTIATION_FLAG,
NAME =p_pm_schedule_rec.NAME,
TMPL_FLAG =p_pm_schedule_rec.TMPL_FLAG,
LAST_REVIEWED_BY = p_pm_schedule_rec.LAST_REVIEWED_BY,
LAST_REVIEWED_DATE = p_pm_schedule_rec.LAST_REVIEWED_DATE ,
CREATED_BY=fnd_global.user_id,
CREATION_DATE=sysdate,
LAST_UPDATE_LOGIN=fnd_global.login_id,
LAST_UPDATED_BY=fnd_global.user_id,
LAST_UPDATE_DATE=sysdate ,
generate_next_work_order =p_pm_schedule_rec.generate_next_work_order
where PM_SCHEDULE_ID=l_pm_schedule_id;
delete from eam_pm_activities
where pm_schedule_id=l_pm_schedule_id;
insert into eam_pm_activities(
pm_schedule_id,
activity_association_id,
interval_multiple,
allow_repeat_in_cycle,
day_tolerance,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by
)
values(
l_pm_schedule_id,
p_pm_activities_tbl(i).activity_association_id,
p_pm_activities_tbl(i).interval_multiple,
p_pm_activities_tbl(i).allow_repeat_in_cycle,
p_pm_activities_tbl(i).day_tolerance,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate ,
fnd_global.user_id
);
delete from eam_pm_scheduling_rules
where pm_schedule_id=l_pm_schedule_id;
select eam_pm_scheduling_rules_s.nextval into l_rule_id from dual; --Bug#5453536
insert into eam_pm_scheduling_rules
( rule_id,
PM_SCHEDULE_ID,
RULE_TYPE,
DAY_INTERVAL,
METER_ID ,
RUNTIME_INTERVAL,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
EFFECTIVE_READING_FROM ,
EFFECTIVE_READING_TO ,
EFFECTIVE_DATE_FROM ,
EFFECTIVE_DATE_TO ,
LIST_DATE ,
LIST_DATE_DESC
)
values
(l_rule_id,
l_pm_schedule_id,
l_current_rules_tbl(n).RULE_TYPE,
l_current_rules_tbl(n).DAY_INTERVAL,
l_current_rules_tbl(n).METER_ID ,
l_current_rules_tbl(n).RUNTIME_INTERVAL,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate ,
fnd_global.user_id,
l_current_rules_tbl(n).EFFECTIVE_READING_FROM ,
l_current_rules_tbl(n).EFFECTIVE_READING_TO ,
l_current_rules_tbl(n).EFFECTIVE_DATE_FROM ,
l_current_rules_tbl(n).EFFECTIVE_DATE_TO ,
l_current_rules_tbl(n).LIST_DATE ,
l_current_rules_tbl(n).LIST_DATE_DESC
);
ROLLBACK TO update_pm_def_pub;
ROLLBACK TO update_pm_def_pub;
ROLLBACK TO update_pm_def_pub;
END update_PM_def;
select count(*) into l_count
from mfg_lookups
where lookup_type = l_lookup_type
and lookup_code=p_pm_schedule_rec.type_code;
select count(*) into l_count
from mfg_lookups
where lookup_type = l_lookup_type
and lookup_code=p_pm_schedule_rec.scheduling_method_code;
select count(*) into l_count
from eam_pm_set_names
where set_name_id = p_pm_schedule_rec.set_name_id;
SELECT pm_schedule_id into l_pm_schedule_id
FROM eam_pm_schedulings
WHERE name=p_pm_schedule_rec.name
AND tmpl_flag=p_pm_schedule_rec.tmpl_flag;
SELECT status_id into l_status_id
FROM EAM_WO_STATUSES_V
WHERE ENABLED_FLAG='Y' AND SYSTEM_STATUS IN (1,3,6,17)
and status_id=p_pm_schedule_rec.generate_wo_status;
select 1 into l_count from dual where exists
(select COUNTER_ID from CSI_COUNTERS_B where counter_id = p_pm_rule_rec.meter_id
union
select COUNTER_ID from CSI_COUNTER_TEMPLATE_B where counter_id = p_pm_rule_rec.meter_id) ;
select direction into l_value_change_dir from
(select direction from CSI_COUNTERS_B where counter_id = p_pm_rule_rec.meter_id
union
select direction from CSI_COUNTER_TEMPLATE_B where counter_id = p_pm_rule_rec.meter_id);
l_lsi_updated varchar2(1);
select last_service_start_date, last_service_end_date,last_scheduled_start_date,last_scheduled_end_date,
maintenance_object_id,asset_activity_id
into l_last_service_start_date, l_last_service_end_date,l_last_scheduled_start_date,l_last_scheduled_end_date,
l_maintenance_object_id,l_asset_activity_id
from mtl_eam_asset_activities
where activity_association_id=p_pm_activity_grp_rec.activity_association_id;
select distinct msi.concatenated_segments into l_act_name
from mtl_system_items_b_kfv msi
where msi.inventory_item_id=l_asset_activity_id
and msi.eam_item_type=2;
select max(ejct.actual_start_date),
max(ejct.actual_end_date) into
l_last_service_start_date,
l_last_service_end_date
from eam_job_completion_txns ejct, wip_discrete_jobs wdj
where wdj.wip_entity_id = ejct.wip_entity_id
and wdj.maintenance_object_id=l_maintenance_object_id
and wdj.primary_item_id=l_asset_activity_id
and ejct.transaction_type=1;
l_lsi_updated := 'Y';
UPDATE mtl_eam_asset_activities
SET last_service_start_date = l_last_service_start_date,
last_service_end_date = l_last_service_end_date,
last_update_date=sysdate, last_updated_by=fnd_global.user_id, creation_date=sysdate,
last_update_login=fnd_global.login_id, created_by = fnd_global.user_id
WHERE activity_association_id = p_pm_activity_grp_rec.activity_association_id;
select transaction_id into l_transaction_id from
( select max(actual_end_date),transaction_id from eam_job_completion_txns ejct,
wip_discrete_jobs wdj
where ejct.transaction_type=1 and
wdj.wip_entity_id = ejct.wip_entity_id and
wdj.maintenance_object_id = l_maintenance_object_id and
wdj.primary_item_id = l_asset_activity_id group by ejct.transaction_id order by ejct.transaction_id desc) where rownum = 1;
l_lsi_updated := 'Y';
SELECT wdj.scheduled_start_date,
wdj.scheduled_completion_date,wdj.wip_entity_id
into l_last_scheduled_start_date,l_last_scheduled_end_date,l_wip_entity_id
FROM eam_job_completion_txns ejct, wip_discrete_jobs wdj WHERE wdj.primary_item_id=l_asset_activity_id
and
wdj.maintenance_object_id=l_maintenance_object_id and
ejct.transaction_id=l_transaction_id and ejct.transaction_type=1 and
ejct.wip_entity_id=wdj.wip_entity_id;
l_lsi_updated := 'Y';
UPDATE mtl_eam_asset_activities meaa
SET meaa.last_scheduled_start_date = l_last_scheduled_start_date,
meaa.last_scheduled_end_date = l_last_scheduled_end_date,
meaa.wip_entity_id=l_wip_entity_id,
last_update_date=sysdate, last_updated_by=fnd_global.user_id, creation_date=sysdate,
last_update_login=fnd_global.login_id, created_by = fnd_global.user_id
WHERE (meaa.last_scheduled_start_date is null OR
meaa.last_scheduled_end_date is null) AND
meaa.activity_association_id = p_pm_activity_grp_rec.activity_association_id;
select count(*) into l_count
from eam_pm_last_service
where meter_id=p_pm_runtime_rules_tbl(i).meter_id
and activity_association_id=p_pm_activity_grp_rec.activity_association_id;
INSERT INTO eam_pm_last_service (activity_association_id, meter_id,
last_service_reading, last_update_date, last_updated_by, creation_date,
last_update_login, created_by)
VALUES (p_pm_activity_grp_rec.activity_association_id,
p_pm_runtime_rules_tbl(i).meter_id,
nvl(p_pm_runtime_rules_tbl(i).last_service_reading, 0),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
fnd_global.user_id);
l_lsi_updated := 'Y';
select eps.pm_schedule_id into l_pm_schedule_id from
eam_pm_schedulings eps,eam_pm_activities epa
where eps.pm_schedule_id=epa.pm_schedule_id
and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
and eps.set_name_id=p_pm_schedule_rec.set_name_id
and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
select eps.pm_schedule_id into l_pm_schedule_id from
eam_pm_schedulings eps,eam_pm_activities epa
where eps.pm_schedule_id=epa.pm_schedule_id
and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
and eps.default_implement = 'Y'
and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
if l_lsi_updated = 'Y' then
x_message := 'EAM_PM_LAST_SERVICE_DEFAULT';
select last_service_start_date, last_service_end_date
into l_last_service_start_date, l_last_service_end_date
from mtl_eam_asset_activities
where activity_association_id=p_pm_activity_grp_rec.activity_association_id;
select eps.pm_schedule_id into l_pm_schedule_id from
eam_pm_schedulings eps,eam_pm_activities epa
where eps.pm_schedule_id=epa.pm_schedule_id
and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
and eps.set_name_id=p_pm_schedule_rec.set_name_id
and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
select eps.pm_schedule_id into l_pm_schedule_id from
eam_pm_schedulings eps,eam_pm_activities epa
where eps.pm_schedule_id=epa.pm_schedule_id
and epa.activity_association_id=p_pm_activity_grp_rec.activity_association_id
and eps.default_implement = 'Y'
and eps.tmpl_flag=p_pm_schedule_rec.tmpl_flag;
select rescheduling_point,current_seq into l_rescheduling_point,l_current_seq
from eam_pm_schedulings
where pm_schedule_id = p_pm_schedule_id;
select max(meaa.last_service_start_date) into l_last_service_date
from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
where epa.activity_association_id=meaa.activity_association_id and
epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
(epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0));
select max(meaa.last_service_end_date) into l_last_service_date
from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
where epa.activity_association_id=meaa.activity_association_id and
epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
(epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0));
select max(meaa.last_scheduled_start_date) into l_last_service_date
from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
where epa.activity_association_id=meaa.activity_association_id and
epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
(epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0));
select max(meaa.last_scheduled_end_date) into l_last_service_date
from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
where epa.activity_association_id=meaa.activity_association_id and
epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id and
((epa.ALLOW_REPEAT_IN_CYCLE='N' and epa.interval_multiple=eps.current_seq) or
(epa.ALLOW_REPEAT_IN_CYCLE='Y' and mod(eps.current_seq, epa.interval_multiple)=0));
select max(meaa.last_service_start_date) into l_last_service_date
from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
where epa.activity_association_id=meaa.activity_association_id and
epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id;
select max(meaa.last_service_end_date) into l_last_service_date
from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
where epa.activity_association_id=meaa.activity_association_id and
epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id;
select max(meaa.last_scheduled_start_date) into l_last_service_date
from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
where epa.activity_association_id=meaa.activity_association_id and
epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id;
select max(meaa.last_scheduled_end_date) into l_last_service_date
from mtl_eam_asset_activities meaa, eam_pm_activities epa, eam_pm_schedulings eps
where epa.activity_association_id=meaa.activity_association_id and
epa.pm_schedule_id=eps.pm_schedule_id and eps.pm_schedule_id=p_pm_schedule_id;
select * into l_act_assoc_id from (
SELECT meaa.activity_association_id FROM mtl_eam_asset_activities meaa, eam_pm_activities epa
WHERE decode(l_rescheduling_point,2,last_service_end_date ,3,last_scheduled_start_date, 4,last_scheduled_end_date ,last_service_start_date)
= l_last_service_date
AND meaa.activity_association_id = epa.activity_association_id AND epa.pm_schedule_id = p_pm_schedule_id
order by interval_multiple
)
where rownum=1 ;
procedure update_pm_last_cyclic_act
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_pm_schedule_id IN NUMBER
)
is
l_api_name CONSTANT VARCHAR2(30) :='update_pm_last_cyclic_act';
SAVEPOINT update_pm_last_cyclic_act;
update eam_pm_schedulings set EAM_LAST_CYCLIC_ACT = l_act_assoc_id,
last_update_date=sysdate,
last_updated_by=fnd_global.user_id,
creation_date=sysdate,
last_update_login=fnd_global.login_id,
created_by = fnd_global.user_id where
pm_schedule_id = p_pm_schedule_id;
ROLLBACK TO update_pm_last_cyclic_act;
ROLLBACK TO update_pm_last_cyclic_act;
ROLLBACK TO update_pm_last_cyclic_act;
END update_pm_last_cyclic_act;
procedure update_pm_last_service_reading
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
p_commit IN VARCHAR2 := FND_API.G_FALSE ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_pm_schedule_id IN NUMBER
)
is
l_api_name CONSTANT VARCHAR2(30) :='update_pm_last_service_reading';
select pm_schedule_id,
meter_id,
rule_id
from eam_pm_scheduling_rules
where pm_schedule_id = l_pm_schedule_id
and rule_type = 2;
SAVEPOINT update_pm_last_service_reading;
select last_service_reading into lsr
from eam_pm_last_service
where meter_id = runtime_rec.meter_id and activity_association_id = l_act_assoc_id;
update eam_pm_scheduling_rules set last_service_reading = lsr,
last_update_date=sysdate,
last_updated_by=fnd_global.user_id,
creation_date=sysdate,
last_update_login=fnd_global.login_id,
created_by = fnd_global.user_id
where pm_schedule_id=runtime_rec.pm_schedule_id and
meter_id = runtime_rec.meter_id and
rule_id = runtime_rec.rule_id;
ROLLBACK TO update_pm_last_service_reading;
ROLLBACK TO update_pm_last_service_reading;
ROLLBACK TO update_pm_last_service_reading;
END update_pm_last_service_reading;