[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
g_last_update_login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
select activity_association_id into x_activity_assoc_id
from mtl_eam_asset_activities meaa, wip_discrete_jobs wdj
where meaa.asset_activity_id = wdj.primary_item_id
and meaa.maintenance_object_id = wdj.maintenance_object_id
and meaa.maintenance_object_type = wdj.maintenance_object_type
and wdj.wip_entity_id = p_wip_entity_id;
select * from (
select life_to_date_reading,
VALUE_TIMESTAMP
from csi_counter_readings
where counter_id = p_meter_id
and (reset_mode <> 'SOFT' or reset_mode is null )
and NVL(disabled_flag,'N')<>'Y'
order by VALUE_TIMESTAMP desc) where rownum <3;
select count (*) INTO l_count
from csi_counter_readings
where counter_id = p_meter_id
and (reset_mode <> 'SOFT' or reset_mode is null )
and NVL(disabled_flag,'N')<>'Y'
order by VALUE_TIMESTAMP desc;
select
trunc ((SUM(life_to_date_reading * (current_reading_date-sysdate))
- SUM (life_to_date_reading) * SUM (current_reading_date-sysdate) / count(row_id))/
(SUM((current_reading_date-sysdate) * (current_reading_date-sysdate))
- SUM (current_reading_date-sysdate) * SUM (current_reading_date-sysdate) /
count(row_id)) , 6)
INTO x_average
from
(
select ccr.value_timestamp current_reading_date,
ccr.life_to_date_reading,
rownum row_id
from
csi_counter_readings ccr
where
ccr.counter_id = p_meter_id and
(reset_mode <> 'SOFT' or reset_mode is null ) and
NVL(disabled_flag,'N')<>'Y'
order by VALUE_TIMESTAMP desc
)
where rownum <= p_use_past_reading + 1;
select default_usage_rate,use_past_reading
into l_user_defined_rate, l_use_past_reading
from csi_counters_b
where counter_id = p_meter_id;
select counter_id meter_id from csi_counter_associations
where source_object_id = pp_maintenance_object_id;
select maintenance_object_id into x_maintenance_object_id
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select eam_required_flag
into l_required_flag
from csi_counters_b
where counter_id = p_meter_id;
select used_in_scheduling
from csi_counters_b
where counter_id = p_meter_id
and SYSDATE BETWEEN nvl(start_date_active, SYSDATE-1) AND nvl(end_date_active, SYSDATE+1);
* This procedure updates the last service reading of the meter for the
* asset activity association. It also recursively updates the meter readings
* of the child activity association in the suppression hierarchy.
*/
procedure update_last_service_reading(p_wip_entity_id in number,
p_activity_assoc_id in number,
p_meter_id in number,
p_meter_reading in number) IS
cursor C is
select sup.child_association_id
from eam_suppression_relations sup
where sup.parent_association_id = p_activity_assoc_id;
/* Following select and if condition are
Added for bug no : 2756121 */
select count(*) into x_count from eam_pm_last_service
where
meter_id = p_meter_id and
activity_association_id = p_activity_assoc_id;
insert into eam_pm_last_service(
meter_id,
activity_association_id,
last_service_reading,
wip_entity_id,
creation_date,
created_by,
last_update_login,
last_updated_by,
last_update_date)
values(
p_meter_id,
p_activity_assoc_id,
p_meter_reading,
p_wip_entity_id,
SYSDATE,
g_created_by,
g_last_update_login,
g_last_updated_by,
SYSDATE
);
update eam_pm_last_service
set prev_service_reading = last_service_reading
where meter_id = p_meter_id
and activity_association_id = p_activity_assoc_id;
update eam_pm_last_service
set last_service_reading = p_meter_reading,
wip_entity_id = p_wip_entity_id
where meter_id = p_meter_id
and activity_association_id = p_activity_assoc_id;
update_last_service_reading(p_wip_entity_id, x_child_aa, p_meter_id, p_meter_reading);
* This procedure updates the last service reading of the meter for the
* asset activity association. It also recursively updates the meter readings
* of the child activity association in the suppression hierarchy.
*/
procedure update_last_service_reading_wo(p_wip_entity_id in number,
p_meter_id in number,
p_meter_reading in number,
p_wo_end_date in date,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
x_assoc_id number;
select last_service_end_date into l_last_service_end_date
from mtl_eam_asset_activities
where activity_association_id=x_assoc_id;
update_last_service_reading(p_wip_entity_id, x_assoc_id, p_meter_id, p_meter_reading);
* This procedure updates the last service start/end date for the
* asset activity association. It also recursively updates dates
* of the child activity association in the suppression hierarchy.
*/
procedure update_last_service_dates_wo(p_wip_entity_id in number,
p_start_date in date,
p_end_date in date,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
x_assoc_id number;
update_last_service_dates(p_wip_entity_id, x_assoc_id, p_start_date, p_end_date);
SELECT wdj.pm_schedule_id,ewod.cycle_id,ewod.seq_id
INTO l_pm_schedule_id,l_cycle_id,l_seq_id
FROM WIP_DISCRETE_JOBS wdj,EAM_WORK_ORDER_DETAILS ewod
WHERE wdj.wip_entity_id = p_wip_entity_id and
ewod.wip_entity_id = wdj.wip_entity_id ;
select current_cycle,current_seq into l_pm_cycle_id,l_pm_seq_id from
eam_pm_schedulings where pm_schedule_id =l_pm_schedule_id;
-- 5151820 update pm only when pm cycle and seq are less than or equal to that of work order
--concatenating both the attributes and comparing below
l_pm_seq := to_number(to_char(l_pm_cycle_id) || to_char(l_pm_seq_id));
UPDATE EAM_PM_SCHEDULINGS
SET current_cycle = l_cycle_id,
current_seq = l_seq_id ,
current_wo_seq = l_seq_id,
last_update_date=sysdate,
last_updated_by=g_last_updated_by,
last_update_login=g_last_update_login
WHERE pm_schedule_id = l_pm_schedule_id ;
--if pm generate workorder then update last cyclic actviity of the PM
EAM_PMDEF_PUB.Update_Pm_Last_Cyclic_Act
( X_Return_Status => x_return_status,
p_api_version => 1.0 ,
p_commit => FND_API.G_FALSE ,
X_msg_count => x_msg_count ,
X_msg_data => x_msg_data ,
p_pm_schedule_id =>l_pm_schedule_id
);
* This procedure is a wrapper over update_last_service_dates
* This is getting called from
* EAMPLNWB.fmb -> MASS_COMPLETE block -> Work_Order_Completion
* procedure. Do not call this from other locations
*/
procedure updt_last_srvc_dates_wo_wpr (p_wip_entity_id in number,
p_start_date in date,
p_end_date in date,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
x_assoc_id number;
update_last_service_dates(p_wip_entity_id, x_assoc_id, p_start_date, p_end_date);
SELECT wdj.pm_schedule_id,ewod.cycle_id,ewod.seq_id
INTO l_pm_schedule_id,l_cycle_id,l_seq_id
FROM WIP_DISCRETE_JOBS wdj,EAM_WORK_ORDER_DETAILS ewod
WHERE wdj.wip_entity_id = p_wip_entity_id and
ewod.wip_entity_id = wdj.wip_entity_id ;
UPDATE EAM_PM_SCHEDULINGS
SET current_cycle = l_cycle_id,
current_seq = l_seq_id ,
current_wo_seq = l_seq_id,
last_update_date=sysdate,
last_updated_by=g_last_updated_by,
last_update_login=g_last_update_login
WHERE pm_schedule_id = l_pm_schedule_id ;
--if pm generate workorder then update last cyclic actviity of the PM
EAM_PMDEF_PUB.Update_Pm_Last_Cyclic_Act
( X_Return_Status => x_return_status,
p_api_version => 1.0 ,
p_commit => FND_API.G_FALSE ,
X_msg_count => x_msg_count ,
X_msg_data => x_msg_data ,
p_pm_schedule_id =>l_pm_schedule_id
);
* This procedure updates the last service start/end date for the
* asset activity association. It also recursively updates dates
* of the child activity association in the suppression hierarchy.
*/
procedure update_last_service_dates( p_wip_entity_id in number,
p_activity_assoc_id in number,
p_start_date in date,
p_end_date in date) IS
cursor C is
select sup.child_association_id
from eam_suppression_relations sup
where sup.parent_association_id = p_activity_assoc_id;
select rescheduling_point
from eam_pm_schedulings
where pm_schedule_id in (SELECT pm_schedule_id
FROM eam_pm_activities where activity_association_id=activity_assoc_id)
and default_implement=l_default;
select wdj.scheduled_start_date
,wdj.scheduled_completion_date
into
l_sch_start_date
,l_sch_end_date
from wip_discrete_jobs wdj
where wdj.wip_entity_id=p_wip_entity_id;
update mtl_eam_asset_activities
set prev_service_start_date=last_service_start_date,
prev_service_end_date=last_service_end_date,
prev_scheduled_start_date=last_scheduled_start_date,
prev_scheduled_end_date=last_scheduled_end_date,
PREV_PM_SUGGESTED_START_DATE = LAST_PM_SUGGESTED_START_DATE,
PREV_PM_SUGGESTED_END_DATE = LAST_PM_SUGGESTED_END_DATE
/* Shifted p_start_date,p_end_date for bug #4096193 */
where activity_association_id = p_activity_assoc_id
and (( decode(l_schedule_option,3,last_scheduled_start_date,
4,last_scheduled_end_date,1,last_service_start_date,2,last_service_end_date,5,last_service_start_date,6,last_service_start_date) is null)
or ( decode(l_schedule_option,3,last_scheduled_start_date,4,last_scheduled_end_date,
1,last_service_start_date,2,last_service_end_date,5,last_service_start_date,6,last_service_start_date) <
decode(l_schedule_option,3,l_sch_start_date,4,l_sch_end_date,1,p_start_date,2,p_end_date,5,p_start_date,6,p_start_date)));
update mtl_eam_asset_activities meaa
set (meaa.last_scheduled_start_date,
meaa.last_scheduled_end_date,
meaa.last_service_start_date, --added for bug #4096193
meaa.last_service_end_date,--added for bug #4096193
meaa.wip_entity_id,
meaa.LAST_PM_SUGGESTED_START_DATE,
meaa.LAST_PM_SUGGESTED_END_DATE)
= (select wdj.scheduled_start_date,
wdj.scheduled_completion_date,
p_start_date, --added for bug #4096193
p_end_date, --added for bug #4096193
wdj.wip_entity_id,
ewod.pm_suggested_start_date,
ewod.pm_suggested_end_date
from wip_discrete_jobs wdj, eam_work_order_details ewod
where wdj.wip_entity_id=p_wip_entity_id
and wdj.wip_entity_id = ewod.wip_entity_id)
where meaa.activity_association_id = p_activity_assoc_id
and (( decode(l_schedule_option,3,last_scheduled_start_date,
4,last_scheduled_end_date,1,last_service_start_date,
2,last_service_end_date,5,last_service_start_date,6,last_service_start_date) is null)
or ( decode(l_schedule_option,3,last_scheduled_start_date,4,last_scheduled_end_date,
1,last_service_start_date,2,last_service_end_date,5,last_service_start_date,6,last_service_start_date)<
decode(l_schedule_option,3,l_sch_start_date,4,l_sch_end_date,1,p_start_date,2,p_end_date,5,p_start_date,6,p_start_date))); --added for bug #4096193
update_last_service_dates(p_wip_entity_id, x_child_aa, p_start_date, p_end_date);
* This procedure should be called when resetting a meter. It updates the corresponding
* PM schedule rule data if applicable.
*/
procedure reset_meter(p_meter_id in number,
p_current_reading in number,
p_last_reading in number,
p_change_val in number) is
cursor C is
select 'X'
from csi_counters_b
where counter_id = p_meter_id
and SYSDATE BETWEEN nvl(start_date_active, SYSDATE-1) AND nvl(end_date_active, SYSDATE+1);
update eam_pm_scheduling_rules
set last_service_reading = x_temp - last_service_reading - runtime_interval
where meter_id = p_meter_id
and rule_type = 2;
select
ABS(
trunc ((SUM(life_to_date_reading * (current_reading_date-sysdate))
- SUM (life_to_date_reading) * SUM (current_reading_date-sysdate) / count(rowid))/
(SUM((current_reading_date-sysdate) * (current_reading_date-sysdate))
- SUM (current_reading_date-sysdate) * SUM (current_reading_date-sysdate) /
count(rowid)) , 6)
)
INTO x_average
from
eam_meter_readings_v
where
meter_id = p_meter_id
and
(disable_flag is null or disable_flag = 'N')
and
reset_flag <> 'Y'
and ( p_from_date is null or (current_reading_date > p_from_date))
and ( p_to_date is null or (current_reading_date < p_to_date));
select epac.activity_association_id
from eam_pm_activities epac,
eam_pm_schedulings eps,
eam_suppression_relations sup
where sup.parent_association_id = p_activity_assoc_id
and sup.child_association_id = epac.activity_association_id
and eps.pm_schedule_id = epac.pm_schedule_id
and nvl(eps.from_effective_date, sysdate-1) < sysdate
and nvl(eps.to_effective_date, sysdate+1) > sysdate;
select 'X'
from eam_pm_scheduling_rules pr,
eam_pm_activities epa,
csi_counters_b ccb
where pr.meter_id = ccb.counter_id
and epa.activity_association_id = p_activity_assoc_id
and pr.pm_schedule_id = epa.pm_schedule_id
and pr.rule_type = 2
and pr.meter_id = p_meter_id
and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1);
select epac.pm_schedule_id
from eam_pm_activities epac,
eam_pm_schedulings eps,
eam_pm_activities epap,
eam_suppression_relations sup
where epap.activity_association_id = sup.parent_association_id
and epap.pm_schedule_id = p_parent_pm_id
and sup.child_association_id = epac.activity_association_id
and eps.pm_schedule_id = epac.pm_schedule_id
and nvl(eps.from_effective_date, sysdate-1) < sysdate
and nvl(eps.to_effective_date, sysdate+1) > sysdate;
select count(*) into x_num
from eam_pm_scheduling_rules pr,
csi_counters_b ccb
where pr.meter_id = ccb.counter_id
and pr.pm_schedule_id = p_parent_pm_id
and pr.rule_type = 2
and SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1) AND nvl(ccb.end_date_active, SYSDATE+1);
select min(value_timestamp)
into prev_ceiling_reset_date
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp > p_reading_date
and reset_mode = 'SOFT'
and NVL(disabled_flag,'N')<>'Y';
select min(value_timestamp)
into prev_ceiling_reading_date
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp > p_reading_date
and (reset_mode <> 'SOFT' or reset_mode is null)
and NVL(disabled_flag,'N')<>'Y';
function cannot_update_reset(p_meter_id in number,
p_reading_date in date)
return boolean is
curr_rdg number;
select min(value_timestamp)
into prev_ceiling_reading_date
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp > p_reading_date
and (reset_mode <> 'SOFT' or reset_mode is null)
and NVL(disabled_flag,'N')<>'Y';
end cannot_update_reset;
select counter_reading
into curr_rdg
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp = p_reading_date
and (reset_mode <> 'SOFT' or reset_mode is null)
and NVL(disabled_flag,'N')<>'Y';
select current_reading_date, meter_id
into x_reading_date, x_meter_id
from eam_meter_readings where meter_reading_id=p_meter_reading_id;
select reset_flag into x_reset_flag from eam_meter_readings
where meter_reading_id = p_meter_reading_id;
select min(current_reading_date) into next_reading_date
from eam_meter_readings
where meter_id = x_meter_id
AND current_reading_date > x_reading_date
and (disable_flag is null or disable_flag = 'N');
select reset_flag into next_reset_flag
from eam_meter_readings
where meter_id = x_meter_id
AND current_reading_date =next_reading_date
and (disable_flag is null or disable_flag = 'N');
select min(value_timestamp)
into l_next_reading_date
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp > p_reading_date
and (disabled_flag <> 'Y');
select COUNTER_VALUE_ID into l_meter_reading_id
from csi_counter_readings
where
COUNTER_ID=p_meter_id and
value_timestamp = p_reading_date
and NVL(disabled_flag,'N')<>'Y';
select max(value_timestamp) into l_prev_reading_date
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp < p_reading_date
and NVL(disabled_flag,'N')<>'Y';
select counter_reading into l_prev_reading
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp = l_prev_reading_date
and NVL(disabled_flag,'N')<>'Y';
select min(value_timestamp) into l_next_reading_date
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp > p_reading_date
and NVL(disabled_flag,'N')<>'Y';
select counter_reading, decode(reset_mode,'SOFT','Y','N') reset_flag
into l_next_reading, l_next_reset
from csi_counter_readings
where value_timestamp = l_next_reading_date
and COUNTER_ID = p_meter_id
and NVL(disabled_flag,'N')<>'Y';
select direction into l_meter_type
from csi_counters_b
where counter_id = p_meter_id;
* This procedure updates LTD readings for disabled change meter readings
*/
procedure update_change_meter_ltd(p_meter_id in number,
p_meter_reading_id in number) is
l_reading_date DATE;
select reading_type into l_meter_type
from csi_counters_b
where counter_id = p_meter_id;
select VALUE_TIMESTAMP, COUNTER_READING
into l_reading_date, l_reading_value
from csi_counter_readings
where COUNTER_VALUE_ID = p_meter_reading_id;
update csi_counter_readings
set life_to_date_reading = life_to_date_reading - l_reading_value
where value_timestamp > l_reading_date and counter_id = p_meter_id;
end update_change_meter_ltd;
select max(value_timestamp) into l_prev_reading_date
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp < p_reading_date
and NVL(disabled_flag,'N')<>'Y';
select counter_reading, life_to_date_reading
into l_prev_reading, l_prev_ltd_reading
from csi_counter_readings
where value_timestamp = l_prev_reading_date
and COUNTER_ID = p_meter_id
and NVL(disabled_flag,'N')<>'Y';
select decode(reset_mode,'SOFT','Y','N') reset_flag, VALUE_TIMESTAMP
into l_reset, l_current_reading_date
from CSI_COUNTER_READINGS
where COUNTER_VALUE_ID = p_meter_reading_id
and NVL(disabled_flag,'N')<>'Y';
select min(value_timestamp) into l_next_reading_date
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp > l_current_reading_date
and NVL(disabled_flag,'N')<>'Y';
select decode(reset_mode,'SOFT','Y','N') reset_flag into l_next_reset
from csi_counter_readings
where COUNTER_ID = p_meter_id
and value_timestamp = l_next_reading_date
and NVL(disabled_flag,'N')<>'Y';
SELECT 'Y'
INTO l_exists
FROM EAM_PM_SCHEDULING_RULES
WHERE meter_id = p_meter_id
AND rownum<=1;