API used for while generating Preventive Maintenance work orders for an asset.
This API used to insert last service meter reading for an asset while generating work orders.
CREATE TABLE APPS.XX_D24_PM_LAST_SERVICE
(
ASSET_NUMBER VARCHAR2(30 BYTE),
METER_NAME VARCHAR2(80 BYTE),
ACTIVITY VARCHAR2(40 BYTE),
runtime_interval number
)
declare
--type pm_last_service_tbl is table of EAM_PM_LAST_SERVICE_PUB.pm_last_service_rec index by binary_integer;
--v_pm_last_service_tbl pm_last_service_tbl;
v_pm_last_service_tbl EAM_PM_LAST_SERVICE_PUB.pm_last_service_tbl;
cursor c1 is
select (select max(counter_reading) from csi_counter_readings where counter_id = cct.counter_id
and 1=1
group by counter_id
) reading,cct.name,cct.counter_id,cii.instance_number,msib.segment1,msib.inventory_item_id,meaa.ACTIVITY_ASSOCIATION_ID
from csi_counters_tl cct,xx_d24_pm_last_service xdp,csi_counter_associations cca,csi_item_instances cii
, mtl_system_items_b msib
, mtl_eam_asset_activities meaa
where 1=1
and xdp.meter_name = cct.name
and cca.counter_id = cct.counter_id
and cca.source_object_id = cii.instance_id
and upper(trim(xdp.asset_number)) = upper(trim(cii.instance_number))
and upper(trim(msib.segment1)) = upper(trim(xdp.ACTIVITY))
and msib.organization_id = 169
and meaa.ASSET_ACTIVITY_ID = msib.inventory_item_id;
v_return_status varchar2(100);
v_msg_count number;
v_msg_data varchar2(4000);
v_p_init_msg_list VARCHAR2(200):= FND_API.G_FALSE ;
v_p_commit VARCHAR2(200):= FND_API.G_FALSE ;
v_p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
begin
for r1 in c1 loop
v_pm_last_service_tbl(1).METER_ID:= r1.counter_id;-- 894;
v_pm_last_service_tbl(1).LAST_SERVICE_READING := r1.reading;
--v_pm_last_service_tbl(1).PREV_SERVICE_READING:= null;
--v_pm_last_service_tbl(1).WIP_ENTITY_ID := null;
EAM_PM_LAST_SERVICE_PUB.process_pm_last_service
(p_api_version => 1,
p_init_msg_list => v_p_init_msg_list,
p_commit =>v_p_commit,
p_validation_level =>v_p_validation_level,
x_return_status =>v_return_status,
x_msg_count =>v_msg_count,
x_msg_data =>v_msg_data,
p_pm_last_service_tbl =>v_pm_last_service_tbl,
p_actv_assoc_id =>r1.ACTIVITY_ASSOCIATION_ID
);
--dbms_output.put_line('the status is '||v_msg_data);
end loop;
end;
---update last service Reading ---
declare
--type pm_last_service_tbl is table of EAM_PM_LAST_SERVICE_PUB.pm_last_service_rec index by binary_integer;
--v_pm_last_service_tbl pm_last_service_tbl;
v_pm_last_service_tbl EAM_PM_LAST_SERVICE_PUB.pm_last_service_tbl;
cursor c1 is
select * from eam_pm_last_service where meter_id in (
select cct.counter_id
--,(select max(net_reading) from csi_counter_readings where counter_id = cct.counter_id)
from csi_item_instances cii
,csi_counter_associations cca
,csi_counters_tl cct
--, csi_counter_readings ccr
where 1=1
and instance_number like 'D14%'
and instance_number not in ('D14-EQU-DGP028','D14-EQU-DGP029','D14-EQU-KTA38C')
and source_object_id = instance_id
and cca.counter_id = cct.counter_id
--AND COUNTER
--and ccr.counter_id = cct.counter_id
)
AND METER_ID not in (862,892);
v_return_status varchar2(100);
v_msg_count number;
v_msg_data varchar2(4000);
v_p_init_msg_list VARCHAR2(200):= FND_API.G_FALSE ;
v_p_commit VARCHAR2(200):= FND_API.G_FALSE ;
v_p_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
begin
for r1 in c1 loop
v_pm_last_service_tbl(1).METER_ID:= r1.meter_id;-- 894;
v_pm_last_service_tbl(1).LAST_SERVICE_READING := 283;
--v_pm_last_service_tbl(1).PREV_SERVICE_READING:= null;
--v_pm_last_service_tbl(1).WIP_ENTITY_ID := null;
EAM_PM_LAST_SERVICE_PUB.process_pm_last_service
(p_api_version => 1,
p_init_msg_list => v_p_init_msg_list,
p_commit =>v_p_commit,
p_validation_level =>v_p_validation_level,
x_return_status =>v_return_status,
x_msg_count =>v_msg_count,
x_msg_data =>v_msg_data,
p_pm_last_service_tbl =>v_pm_last_service_tbl,
p_actv_assoc_id =>r1.ACTIVITY_ASSOCIATION_ID
);
--dbms_output.put_line('the status is '||v_msg_data);
end loop;
end;
declare
cursor c1 is
select (select max(counter_reading) from csi_counter_readings where counter_id = cct.counter_id
and 1=1
group by counter_id
) reading,cct.name,cct.counter_id,cii.instance_number,msib.segment1,msib.inventory_item_id,meaa.ACTIVITY_ASSOCIATION_ID
, cii.instance_id,xdp.runtime_interval
from csi_counters_tl cct,xx_d24_pm_last_service xdp,csi_counter_associations cca,csi_item_instances cii
, mtl_system_items_b msib
, mtl_eam_asset_activities meaa
where 1=1
and xdp.meter_name = cct.name
and cca.counter_id = cct.counter_id
and cca.source_object_id = cii.instance_id
and upper(trim(xdp.asset_number)) = upper(trim(cii.instance_number))
and upper(trim(msib.segment1)) = upper(trim(xdp.ACTIVITY))
and msib.organization_id = 169
and meaa.ASSET_ACTIVITY_ID = msib.inventory_item_id
and msib.segment1 not in ( 'D24-ALT-1500 HRS MAINT', 'D24-ALT-3000 HRS MAINT');
l_pm_schedule_rec eam_pmdef_pub.PM_Scheduling_Rec_Type;
l_pm_activities_tbl eam_pmdef_pub.pm_activities_grp_tbl_type;
l_pm_day_interval_rules_tbl eam_pmdef_pub.pm_rule_tbl_type;
l_pm_runtime_rules_tbl eam_pmdef_pub.pm_rule_tbl_type;
l_pm_list_date_rules_tbl eam_pmdef_pub.pm_rule_tbl_type;
--------------------------
l_return_status VARCHAR2(240) ;
l_msg_count NUMBER ;
l_msg_data VARCHAR2(240) ;
--l_pm_activities_tbl number;
l_new_pm_schedule_id number ;
l_sch_id number ;
l_rule_id number ;
----------------------------
begin
for r1 in c1 loop
select EAM_PM_SCHEDULINGS_S.nextval into l_sch_id from dual;
select EAM_PM_SCHEDULING_RULES_S.nextval into l_rule_id from dual;
-------------------------------------------
l_pm_schedule_rec.PM_SCHEDULE_ID := l_sch_id;
---l_pm_schedule_rec.ACTIVITY_ASSOCIATION_ID := 19141;
l_pm_schedule_rec.NON_SCHEDULED_FLAG := 'N';
l_pm_schedule_rec.FROM_EFFECTIVE_DATE := sysdate;
l_pm_schedule_rec.TO_EFFECTIVE_DATE := null;
l_pm_schedule_rec.RESCHEDULING_POINT := 6;
l_pm_schedule_rec.LEAD_TIME := null;
l_pm_schedule_rec.DAY_TOLERANCE := null;
l_pm_schedule_rec.SOURCE_CODE := 'EAM';
l_pm_schedule_rec.SOURCE_LINE := 1;
l_pm_schedule_rec.DEFAULT_IMPLEMENT := 'Y';
l_pm_schedule_rec.WHICHEVER_FIRST := 'Y';
l_pm_schedule_rec.INCLUDE_MANUAL := 'N';
l_pm_schedule_rec.SET_NAME_ID := 4011;--from eam_pm_set_names
l_pm_schedule_rec.SCHEDULING_METHOD_CODE := 10;
l_pm_schedule_rec.TYPE_CODE := 10;
l_pm_schedule_rec.NEXT_SERVICE_START_DATE := null;
l_pm_schedule_rec.NEXT_SERVICE_END_DATE := null;
l_pm_schedule_rec.SOURCE_TMPL_ID := null;
l_pm_schedule_rec.AUTO_INSTANTIATION_FLAG := 'N';
l_pm_schedule_rec.NAME := r1.segment1;--'D24-AC-6000 HRS MAINT'; ---from surosr
l_pm_schedule_rec.TMPL_FLAG := 'N';
l_pm_schedule_rec.GENERATE_WO_STATUS := 1;
-----------------------------------------------------
l_pm_schedule_rec.INTERVAL_PER_CYCLE := 1;
l_pm_schedule_rec.CURRENT_CYCLE := 1;
l_pm_schedule_rec.CURRENT_SEQ := 0;
l_pm_schedule_rec.CURRENT_WO_SEQ := null;
l_pm_schedule_rec.BASE_DATE := null;
l_pm_schedule_rec.BASE_READING := r1.reading;--- 380;--from cusrosdr
l_pm_schedule_rec.EAM_LAST_CYCLIC_ACT := r1.ACTIVITY_ASSOCIATION_ID; ---doubt
l_pm_schedule_rec.MAINTENANCE_OBJECT_ID := r1.instance_id;-- 37049;---from curosr
l_pm_schedule_rec.MAINTENANCE_OBJECT_TYPE := 3;
l_pm_schedule_rec.LAST_REVIEWED_DATE := sysdate;
l_pm_schedule_rec.Last_reviewed_by := 11100;
l_pm_schedule_rec.GENERATE_NEXT_WORK_ORDER := 'N';
------------
l_pm_activities_tbl(1).PM_SCHEDULE_ID := l_sch_id;
l_pm_activities_tbl(1).ACTIVITY_ASSOCIATION_ID := r1.ACTIVITY_ASSOCIATION_ID;--19142;--need from durosr
l_pm_activities_tbl(1).INTERVAL_MULTIPLE := 1;
l_pm_activities_tbl(1).ALLOW_REPEAT_IN_CYCLE := 'Y';
l_pm_activities_tbl(1).DAY_TOLERANCE := null;
l_pm_activities_tbl(1).NEXT_SERVICE_START_DATE := null;
l_pm_activities_tbl(1).NEXT_SERVICE_END_DATE := null;
----------------------------
l_pm_runtime_rules_tbl(1).rule_id := l_rule_id;
l_pm_runtime_rules_tbl(1).PM_SCHEDULE_ID := l_sch_id;
l_pm_runtime_rules_tbl(1).RULE_TYPE := 2;
l_pm_runtime_rules_tbl(1).DAY_INTERVAL := null;
l_pm_runtime_rules_tbl(1).METER_ID := r1.counter_id;--922; --need from cursor
l_pm_runtime_rules_tbl(1).RUNTIME_INTERVAL := r1.RUNTIME_INTERVAL;--1500;----from cursor
l_pm_runtime_rules_tbl(1).LAST_SERVICE_READING := r1.reading;
l_pm_runtime_rules_tbl(1).EFFECTIVE_READING_FROM := NULL;
l_pm_runtime_rules_tbl(1).EFFECTIVE_READING_TO := NULL;
l_pm_runtime_rules_tbl(1).EFFECTIVE_DATE_FROM := null;
l_pm_runtime_rules_tbl(1).EFFECTIVE_DATE_TO := null;
l_pm_runtime_rules_tbl(1).LIST_DATE := NULL;
l_pm_runtime_rules_tbl(1).LIST_DATE_DESC := NULL;
-------------------------------------
eam_pmdef_pub.create_pm_def(p_api_version => 1,
p_init_msg_list => NULL,
p_commit => 'T',
p_validation_level => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_pm_schedule_rec => l_pm_schedule_rec,
p_pm_activities_tbl => l_pm_activities_tbl,
p_pm_day_interval_rules_tbl => l_pm_day_interval_rules_tbl,
p_pm_runtime_rules_tbl => l_pm_runtime_rules_tbl,
p_pm_list_date_rules_tbl => l_pm_list_date_rules_tbl,
x_new_pm_schedule_id => l_new_pm_schedule_id
);
------------------------
dbms_output.put_line(l_return_status);
dbms_output.put_line(l_msg_count);
dbms_output.put_line(l_msg_data);
--------------------------------
for i in 1..nvl(l_msg_count,0) loop
dbms_output.put_line(Fnd_Msg_Pub.Get(i, Fnd_Api.G_FALSE ));
end loop;
end loop;
end ;
/
No comments:
Post a Comment