Monday, 24 September 2018

EAM Preventive maintenance Last service reading API

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