[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT counter_reading
FROM csi_counter_readings
WHERE counter_id = p_counter_id
AND value_timestamp < p_value_timestamp
AND nvl(disabled_flag,'N') = 'N'
ORDER BY value_timestamp desc;
SELECT net_reading
FROM csi_counter_readings
WHERE counter_id = p_counter_id
AND value_timestamp < p_value_timestamp
AND nvl(disabled_flag,'N') = 'N'
ORDER BY value_timestamp desc;
SELECT ctr_val_max_seq_no
INTO l_counter_value_id
FROM csi_counters_b
WHERE counter_id = p_counter_id
AND EXISTS(SELECT 'Y' FROM csi_counter_readings WHERE counter_value_id=ctr_val_max_seq_no);
select 'x'
into l_exists
from CSI_TRANSACTIONS
where transaction_id = p_transaction_id;
select 'x'
into l_exists
from CSI_COUNTER_READINGS
where counter_value_id = p_ctr_value_id;
select 'x'
into l_exists
from CSI_CTR_PROPERTY_READINGS
where counter_prop_value_id = p_ctr_prop_value_id;
select 'x'
into l_exists
from CSI_CTR_ESTIMATED_READINGS
where estimated_reading_id = p_estimated_rdg_id;
select '1'
into l_exists
from dual
where exists (select 'x'
from CSI_COUNTER_RELATIONSHIPS
where object_counter_id = p_counter_id
and relationship_type_code = l_rel_type);
select '1'
into l_exists
from dual
where exists (select 'x'
from CSI_COUNTER_RELATIONSHIPS
where object_counter_id = p_counter_id
and relationship_type_code = l_rel_type
and nvl(active_end_date,(sysdate+1)) > sysdate);
select 'x'
into l_exists
from CSI_LOOKUPS
where lookup_type = l_adj_type
and lookup_code = p_adjustment_type;
select 'x'
into l_exists
from CSI_LOOKUPS
where lookup_type = l_adj_type
and lookup_code = p_reset_mode;
select 'x'
into l_exists
from CSI_COUNTER_READINGS ccr,
CSI_COUNTER_PROPERTIES_B ccp
where ccr.counter_value_id = p_ctr_value_id
and ccp.counter_id = ccr.counter_id
and ccp.counter_property_id = p_ctr_prop_id
and nvl(ccp.end_date_active,(sysdate+1)) > sysdate;
select 'x'
into l_s_temp
from CSI_CTR_PROPERTY_READINGS
where counter_property_id = p_counter_property_id
and counter_value_id = p_counter_value_id;
select ccr.object_counter_id,nvl(ccr.factor,1) factor,
ccv.direction,ccv.reading_type, ccv.uom_code object_uom_code
from CSI_COUNTER_RELATIONSHIPS ccr,
CSI_COUNTERS_B ccv
where ccr.source_counter_id = p_ctr_rdg_rec.counter_id
and ccr.relationship_type_code =l_rel_type
and nvl(ccr.active_start_date,sysdate) <= p_ctr_rdg_rec.value_timestamp
and nvl(ccr.active_end_date,(sysdate+1)) > p_ctr_rdg_rec.value_timestamp
and ccv.counter_id = ccr.object_counter_id;
select counter_reading,net_reading,life_to_date_reading
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp < p_value_timestamp
ORDER BY value_timestamp desc,counter_value_id desc;
SELECT uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code;
select CSI_COUNTER_READINGS_S.nextval
into l_ctr_rdg_rec.counter_value_id from dual;
SELECT uom_code, direction, reading_type
INTO l_source_uom_code, l_source_direction, l_src_reading_type
FROM csi_counters_b
WHERE counter_id = p_ctr_rdg_rec.counter_id;
CSI_COUNTER_READINGS_PKG.Insert_Row(
px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp
,p_COUNTER_READING => l_ctr_rdg_rec.counter_reading
,p_RESET_MODE => l_ctr_rdg_rec.reset_mode
,p_RESET_REASON => l_ctr_rdg_rec.reset_reason
,p_ADJUSTMENT_TYPE => NULL
,p_ADJUSTMENT_READING => NULL
,p_OBJECT_VERSION_NUMBER => 1
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => fnd_global.user_id
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_ATTRIBUTE1 => l_ctr_rdg_rec.attribute1
,p_ATTRIBUTE2 => l_ctr_rdg_rec.attribute2
,p_ATTRIBUTE3 => l_ctr_rdg_rec.attribute3
,p_ATTRIBUTE4 => l_ctr_rdg_rec.attribute4
,p_ATTRIBUTE5 => l_ctr_rdg_rec.attribute5
,p_ATTRIBUTE6 => l_ctr_rdg_rec.attribute6
,p_ATTRIBUTE7 => l_ctr_rdg_rec.attribute7
,p_ATTRIBUTE8 => l_ctr_rdg_rec.attribute8
,p_ATTRIBUTE9 => l_ctr_rdg_rec.attribute9
,p_ATTRIBUTE10 => l_ctr_rdg_rec.attribute10
,p_ATTRIBUTE11 => l_ctr_rdg_rec.attribute11
,p_ATTRIBUTE12 => l_ctr_rdg_rec.attribute12
,p_ATTRIBUTE13 => l_ctr_rdg_rec.attribute13
,p_ATTRIBUTE14 => l_ctr_rdg_rec.attribute14
,p_ATTRIBUTE15 => l_ctr_rdg_rec.attribute15
,p_ATTRIBUTE16 => l_ctr_rdg_rec.attribute16
,p_ATTRIBUTE17 => l_ctr_rdg_rec.attribute17
,p_ATTRIBUTE18 => l_ctr_rdg_rec.attribute18
,p_ATTRIBUTE19 => l_ctr_rdg_rec.attribute19
,p_ATTRIBUTE20 => l_ctr_rdg_rec.attribute20
,p_ATTRIBUTE21 => l_ctr_rdg_rec.attribute21
,p_ATTRIBUTE22 => l_ctr_rdg_rec.attribute22
,p_ATTRIBUTE23 => l_ctr_rdg_rec.attribute23
,p_ATTRIBUTE24 => l_ctr_rdg_rec.attribute24
,p_ATTRIBUTE25 => l_ctr_rdg_rec.attribute25
,p_ATTRIBUTE26 => l_ctr_rdg_rec.attribute26
,p_ATTRIBUTE27 => l_ctr_rdg_rec.attribute27
,p_ATTRIBUTE28 => l_ctr_rdg_rec.attribute28
,p_ATTRIBUTE29 => l_ctr_rdg_rec.attribute29
,p_ATTRIBUTE30 => l_ctr_rdg_rec.attribute30
,p_ATTRIBUTE_CATEGORY => l_ctr_rdg_rec.attribute_category
,p_MIGRATED_FLAG => 'N'
,p_COMMENTS => l_ctr_rdg_rec.comments
,p_LIFE_TO_DATE_READING => l_ctr_rdg_rec.life_to_date_reading
,p_TRANSACTION_ID => p_txn_rec.transaction_id
,p_AUTOMATIC_ROLLOVER_FLAG => l_ctr_rdg_rec.automatic_rollover_flag
,p_INCLUDE_TARGET_RESETS => l_ctr_rdg_rec.include_target_resets
,p_SOURCE_COUNTER_VALUE_ID => l_ctr_rdg_rec.source_counter_value_id
,p_NET_READING => l_ctr_rdg_rec.net_reading
,p_DISABLED_FLAG => 'N'
,p_SOURCE_CODE => l_ctr_rdg_rec.source_code
,p_SOURCE_LINE_ID => l_ctr_rdg_rec.source_line_id
,p_INITIAL_READING_FLAG => l_ctr_rdg_rec.initial_reading_flag
);
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => l_ctr_rdg_rec.counter_id
,px_ctr_val_max_seq_no => l_ctr_rdg_rec.counter_value_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT counter_value_id, counter_id
FROM csi_counter_readings
WHERE source_counter_value_id = p_src_counter_value_id
AND nvl(disabled_flag,'N') <> 'Y';
UPDATE CSI_COUNTER_READINGS
SET disabled_flag = 'Y',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
object_version_number = object_version_number + 1
WHERE counter_value_id = derived_reading.counter_value_id;
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => derived_reading.counter_id
,px_ctr_val_max_seq_no => l_ctr_val_max_seq_no
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
select CSI_TRANSACTIONS_S.nextval
into p_txn_rec.transaction_id from dual;
SELECT 'x'
INTO l_dummy
FROM csi_txn_types
WHERE transaction_type_id = p_txn_rec.transaction_type_id;
CSI_TRANSACTIONS_PKG.Insert_Row
( px_transaction_id => p_txn_rec.transaction_id,
p_transaction_date => p_txn_rec.transaction_date,
p_source_transaction_date => p_txn_rec.source_transaction_date,
p_transaction_type_id => p_txn_rec.transaction_type_id,
p_txn_sub_type_id => p_txn_rec.txn_sub_type_id,
p_source_group_ref_id => p_txn_rec.source_group_ref_id,
p_source_group_ref => p_txn_rec.source_group_ref,
p_source_header_ref_id => p_txn_rec.source_header_ref_id,
p_source_header_ref => p_txn_rec.source_header_ref,
p_source_line_ref_id => p_txn_rec.source_line_ref_id,
p_source_line_ref => p_txn_rec.source_line_ref,
p_source_dist_ref_id1 => p_txn_rec.source_dist_ref_id1,
p_source_dist_ref_id2 => p_txn_rec.source_dist_ref_id2,
p_inv_material_transaction_id => p_txn_rec.inv_material_transaction_id,
p_transaction_quantity => p_txn_rec.transaction_quantity,
p_transaction_uom_code => p_txn_rec.transaction_uom_code,
p_transacted_by => p_txn_rec.transacted_by,
p_transaction_status_code => p_txn_rec.transaction_status_code,
p_transaction_action_code => p_txn_rec.transaction_action_code,
p_message_id => p_txn_rec.message_id,
p_context => p_txn_rec.context,
p_attribute1 => p_txn_rec.attribute1,
p_attribute2 => p_txn_rec.attribute2,
p_attribute3 => p_txn_rec.attribute3,
p_attribute4 => p_txn_rec.attribute4,
p_attribute5 => p_txn_rec.attribute5,
p_attribute6 => p_txn_rec.attribute6,
p_attribute7 => p_txn_rec.attribute7,
p_attribute8 => p_txn_rec.attribute8,
p_attribute9 => p_txn_rec.attribute9,
p_attribute10 => p_txn_rec.attribute10,
p_attribute11 => p_txn_rec.attribute11,
p_attribute12 => p_txn_rec.attribute12,
p_attribute13 => p_txn_rec.attribute13,
p_attribute14 => p_txn_rec.attribute14,
p_attribute15 => p_txn_rec.attribute15,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => p_txn_rec.object_version_number,
p_split_reason_code => p_txn_rec.split_reason_code,
p_gl_interface_status_code => p_txn_rec.gl_interface_status_code
);
l_update_loop BOOLEAN := FALSE;
select counter_reading,net_reading,life_to_date_reading,
value_timestamp, comments
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp < p_value_timestamp
ORDER BY value_timestamp desc,counter_value_id desc;
select counter_reading,value_timestamp,reset_mode, comments
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp > p_value_timestamp
ORDER BY value_timestamp asc,counter_value_id asc;
select counter_value_id,counter_reading,net_reading,value_timestamp,adjustment_reading
,reset_mode,adjustment_type,include_target_resets, comments
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp > p_value_timestamp
ORDER BY value_timestamp asc, counter_value_id asc;
select name,counter_type,rollover_last_reading,
rollover_first_reading,direction,reading_type,
automatic_rollover
into l_ctr_name,l_ctr_type,l_rollover_last_rdg,l_rollover_first_rdg,
l_direction,l_reading_type,l_auto_rollover
from CSI_COUNTERS_VL -- Need to be changed
where counter_id = p_ctr_rdg_rec.counter_id
and nvl(end_date_active,(sysdate+1)) > sysdate;
csi_ctr_gen_utility_pvt.put_line('Cannot disable the value during Insert...');
select count(*)
into l_rec_count
from CSI_COUNTER_READINGS
where counter_id = p_ctr_rdg_rec.counter_id
and ROWNUM = 1;
select counter_value_id, object_version_number
into l_counter_value_id, l_obj_version_num
from CSI_COUNTER_READINGS
where counter_id = p_ctr_rdg_rec.counter_id
and value_timestamp = p_ctr_rdg_rec.value_timestamp;
select 'x'
into l_exists
from dual
where exists (select 'x'
from CSI_COUNTER_READINGS
where counter_id = p_ctr_rdg_rec.counter_id
and value_timestamp = p_ctr_rdg_rec.value_timestamp
and counter_reading = p_ctr_rdg_rec.counter_reading);
update_counter_reading
(p_api_version => 1.0
,p_commit => p_commit
,p_init_msg_list => p_init_msg_list
,p_validation_level => p_validation_level
,p_ctr_rdg_rec => l_disabled_ctr_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM Update_Counter_Reading when disabling WO ');
select max(reading_lock_date)
into l_rdg_lock_date
from CSI_COUNTER_READING_LOCKS
where counter_id = p_ctr_rdg_rec.counter_id
OR counter_id in (select object_counter_id
from CSI_COUNTER_RELATIONSHIPS
where source_counter_id = p_ctr_rdg_rec.counter_id
and nvl(active_end_date,(p_ctr_rdg_rec.value_timestamp+1)) > p_ctr_rdg_rec.value_timestamp);
select CSI_COUNTER_READINGS_S.nextval
into p_ctr_rdg_rec.counter_value_id from dual;
CSI_COUNTER_READINGS_PKG.Insert_Row(
px_COUNTER_VALUE_ID => p_ctr_rdg_rec.counter_value_id
,p_COUNTER_ID => p_ctr_rdg_rec.counter_id
,p_VALUE_TIMESTAMP => p_ctr_rdg_rec.value_timestamp
,p_COUNTER_READING => p_ctr_rdg_rec.counter_reading
,p_RESET_MODE => p_ctr_rdg_rec.reset_mode -- NULL 6398254
,p_RESET_REASON => p_ctr_rdg_rec.reset_reason --NULL 6398254
,p_ADJUSTMENT_TYPE => p_ctr_rdg_rec.adjustment_type
,p_ADJUSTMENT_READING => p_ctr_rdg_rec.adjustment_reading
,p_OBJECT_VERSION_NUMBER => p_ctr_rdg_rec.object_version_number
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => fnd_global.user_id
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_ATTRIBUTE1 => p_ctr_rdg_rec.attribute1
,p_ATTRIBUTE2 => p_ctr_rdg_rec.attribute2
,p_ATTRIBUTE3 => p_ctr_rdg_rec.attribute3
,p_ATTRIBUTE4 => p_ctr_rdg_rec.attribute4
,p_ATTRIBUTE5 => p_ctr_rdg_rec.attribute5
,p_ATTRIBUTE6 => p_ctr_rdg_rec.attribute6
,p_ATTRIBUTE7 => p_ctr_rdg_rec.attribute7
,p_ATTRIBUTE8 => p_ctr_rdg_rec.attribute8
,p_ATTRIBUTE9 => p_ctr_rdg_rec.attribute9
,p_ATTRIBUTE10 => p_ctr_rdg_rec.attribute10
,p_ATTRIBUTE11 => p_ctr_rdg_rec.attribute11
,p_ATTRIBUTE12 => p_ctr_rdg_rec.attribute12
,p_ATTRIBUTE13 => p_ctr_rdg_rec.attribute13
,p_ATTRIBUTE14 => p_ctr_rdg_rec.attribute14
,p_ATTRIBUTE15 => p_ctr_rdg_rec.attribute15
,p_ATTRIBUTE16 => p_ctr_rdg_rec.attribute16
,p_ATTRIBUTE17 => p_ctr_rdg_rec.attribute17
,p_ATTRIBUTE18 => p_ctr_rdg_rec.attribute18
,p_ATTRIBUTE19 => p_ctr_rdg_rec.attribute19
,p_ATTRIBUTE20 => p_ctr_rdg_rec.attribute20
,p_ATTRIBUTE21 => p_ctr_rdg_rec.attribute21
,p_ATTRIBUTE22 => p_ctr_rdg_rec.attribute22
,p_ATTRIBUTE23 => p_ctr_rdg_rec.attribute23
,p_ATTRIBUTE24 => p_ctr_rdg_rec.attribute24
,p_ATTRIBUTE25 => p_ctr_rdg_rec.attribute25
,p_ATTRIBUTE26 => p_ctr_rdg_rec.attribute26
,p_ATTRIBUTE27 => p_ctr_rdg_rec.attribute27
,p_ATTRIBUTE28 => p_ctr_rdg_rec.attribute28
,p_ATTRIBUTE29 => p_ctr_rdg_rec.attribute29
,p_ATTRIBUTE30 => p_ctr_rdg_rec.attribute30
,p_ATTRIBUTE_CATEGORY => p_ctr_rdg_rec.attribute_category
,p_MIGRATED_FLAG => 'N'
,p_COMMENTS => p_ctr_rdg_rec.comments
,p_LIFE_TO_DATE_READING => p_ctr_rdg_rec.life_to_date_reading
,p_TRANSACTION_ID => p_txn_rec.transaction_id
,p_AUTOMATIC_ROLLOVER_FLAG => p_ctr_rdg_rec.automatic_rollover_flag
,p_INCLUDE_TARGET_RESETS => NULL
,p_SOURCE_COUNTER_VALUE_ID => NULL
,p_NET_READING => p_ctr_rdg_rec.net_reading
,p_DISABLED_FLAG => 'N'
,p_SOURCE_CODE => p_ctr_rdg_rec.source_code
,p_SOURCE_LINE_ID => p_ctr_rdg_rec.source_line_id
,p_INITIAL_READING_FLAG => p_ctr_rdg_rec.initial_reading_flag
);
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => p_ctr_rdg_rec.counter_id
,px_ctr_val_max_seq_no => p_ctr_rdg_rec.counter_value_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
select 'Y'
into l_target_ctr_exist
from CSI_COUNTER_RELATIONSHIPS ccr,
CSI_COUNTERS_B ccv,
CSI_COUNTERS_TL cct
where ccr.source_counter_id = p_ctr_rdg_rec.counter_id
and ccr.relationship_type_code = 'CONFIGURATION'
and nvl(ccr.active_start_date,sysdate) <= p_ctr_rdg_rec.value_timestamp
and nvl(ccr.active_end_date,(sysdate+1)) > p_ctr_rdg_rec.value_timestamp
and ccv.counter_id = ccr.object_counter_id
and ccv.counter_id = cct.counter_id
and cct.language = USERENV('LANG');
l_update_loop := TRUE;
UPDATE CSI_COUNTER_READINGS
set net_reading = later_rdg.counter_reading, -- l_previous_net,
life_to_date_reading = l_previous_ltd,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_value_id = later_rdg.counter_value_id;
UPDATE CSI_COUNTER_READINGS
set net_reading = l_net_reading,
life_to_date_reading = l_ltd_reading,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_value_id = later_rdg.counter_value_id;
csi_ctr_gen_utility_pvt.put_line('Calling Compute_Target_Counters for Update ...');
,p_mode => 'UPDATE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
IF NOT l_update_loop THEN
FOR later_rdg IN LATER_READINGS_CUR(p_ctr_rdg_rec.counter_id,p_ctr_rdg_rec.value_timestamp)
LOOP
csi_ctr_gen_utility_pvt.put_line('6398254: If not update_loop');
csi_ctr_gen_utility_pvt.put_line('Calling Compute_Target_Counters for Update 2...');
,p_mode => 'UPDATE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END IF; -- l_update_loop check
select CSI_COUNTER_READINGS_S.nextval
into l_reset_rdg_rec.counter_value_id from dual;
csi_ctr_gen_utility_pvt.put_line('Inserting Reset Record with Ctr Value ID '||to_char(l_reset_rdg_rec.counter_value_id));
CSI_COUNTER_READINGS_PKG.Insert_Row(
px_COUNTER_VALUE_ID => l_reset_rdg_rec.counter_value_id
,p_COUNTER_ID => l_reset_rdg_rec.counter_id
,p_VALUE_TIMESTAMP => l_reset_rdg_rec.value_timestamp
,p_COUNTER_READING => l_reset_rdg_rec.counter_reading
,p_RESET_MODE => l_reset_rdg_rec.reset_mode
,p_RESET_REASON => l_reset_rdg_rec.reset_reason
,p_ADJUSTMENT_TYPE => NULL
,p_ADJUSTMENT_READING => NULL
,p_OBJECT_VERSION_NUMBER => 1
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => fnd_global.user_id
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_ATTRIBUTE1 => l_reset_rdg_rec.attribute1
,p_ATTRIBUTE2 => l_reset_rdg_rec.attribute2
,p_ATTRIBUTE3 => l_reset_rdg_rec.attribute3
,p_ATTRIBUTE4 => l_reset_rdg_rec.attribute4
,p_ATTRIBUTE5 => l_reset_rdg_rec.attribute5
,p_ATTRIBUTE6 => l_reset_rdg_rec.attribute6
,p_ATTRIBUTE7 => l_reset_rdg_rec.attribute7
,p_ATTRIBUTE8 => l_reset_rdg_rec.attribute8
,p_ATTRIBUTE9 => l_reset_rdg_rec.attribute9
,p_ATTRIBUTE10 => l_reset_rdg_rec.attribute10
,p_ATTRIBUTE11 => l_reset_rdg_rec.attribute11
,p_ATTRIBUTE12 => l_reset_rdg_rec.attribute12
,p_ATTRIBUTE13 => l_reset_rdg_rec.attribute13
,p_ATTRIBUTE14 => l_reset_rdg_rec.attribute14
,p_ATTRIBUTE15 => l_reset_rdg_rec.attribute15
,p_ATTRIBUTE16 => l_reset_rdg_rec.attribute16
,p_ATTRIBUTE17 => l_reset_rdg_rec.attribute17
,p_ATTRIBUTE18 => l_reset_rdg_rec.attribute18
,p_ATTRIBUTE19 => l_reset_rdg_rec.attribute19
,p_ATTRIBUTE20 => l_reset_rdg_rec.attribute20
,p_ATTRIBUTE21 => l_reset_rdg_rec.attribute21
,p_ATTRIBUTE22 => l_reset_rdg_rec.attribute22
,p_ATTRIBUTE23 => l_reset_rdg_rec.attribute23
,p_ATTRIBUTE24 => l_reset_rdg_rec.attribute24
,p_ATTRIBUTE25 => l_reset_rdg_rec.attribute25
,p_ATTRIBUTE26 => l_reset_rdg_rec.attribute26
,p_ATTRIBUTE27 => l_reset_rdg_rec.attribute27
,p_ATTRIBUTE28 => l_reset_rdg_rec.attribute28
,p_ATTRIBUTE29 => l_reset_rdg_rec.attribute29
,p_ATTRIBUTE30 => l_reset_rdg_rec.attribute30
,p_ATTRIBUTE_CATEGORY => l_reset_rdg_rec.attribute_category
,p_MIGRATED_FLAG => 'N'
,p_COMMENTS => l_reset_rdg_rec.comments
,p_LIFE_TO_DATE_READING => l_reset_rdg_rec.life_to_date_reading
,p_TRANSACTION_ID => p_txn_rec.transaction_id
,p_AUTOMATIC_ROLLOVER_FLAG => l_reset_rdg_rec.automatic_rollover_flag
,p_INCLUDE_TARGET_RESETS => p_ctr_rdg_rec.include_target_resets
,p_SOURCE_COUNTER_VALUE_ID => NULL
,p_NET_READING => l_reset_rdg_rec.net_reading
,p_DISABLED_FLAG => 'N'
,p_SOURCE_CODE => l_reset_rdg_rec.source_code
,p_SOURCE_LINE_ID => l_reset_rdg_rec.source_line_id
,p_INITIAL_READING_FLAG => l_reset_rdg_rec.initial_reading_flag
);
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => l_reset_rdg_rec.counter_id
,px_ctr_val_max_seq_no => l_reset_rdg_rec.counter_value_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
select distinct object_counter_id
from csi_counter_relationships
where source_counter_id = p_src_ctr_id
and relationship_type_code = l_rel_type
and nvl(active_end_date,(sysdate+1)) > sysdate;
select source_counter_id,bind_variable_name
from csi_counter_relationships
where object_counter_id = p_obj_ctr_id
and relationship_type_code = l_rel_type
and nvl(active_end_date,(sysdate+1)) > sysdate;
select net_reading
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp <= p_value_timestamp
ORDER BY value_timestamp desc,counter_value_id desc;
select counter_value_id,value_timestamp
from CSI_COUNTER_READINGS
where counter_id = p_obj_ctr_id
and value_timestamp > p_value_timestamp
and nvl(disabled_flag,'N') = 'N'
ORDER BY value_timestamp desc, counter_value_id desc;
select formula_text
into l_formula_text
from CSI_COUNTERS_B -- Need to be changed
where counter_id = obj_cur.object_counter_id
and nvl(end_date_active,(sysdate+1)) > sysdate;
l_formula_text := 'SELECT '||l_formula_text||' FROM DUAL';
l_src_ctr_tbl.DELETE;
select '1'
into l_exists from dual
where exists (select 'x'
from CSI_COUNTER_READINGS
where counter_id = sub_cur.source_counter_id
and value_timestamp = p_ctr_rdg_rec.value_timestamp
and nvl(disabled_flag,'N') <> 'Y');
-- If the source counter reading is not disabled then we update/insert the row for formula
-- counter.
--
IF NVL(p_ctr_rdg_rec.disabled_flag,'N') <> 'Y' THEN
csi_ctr_gen_utility_pvt.put_line('Trying to Update Object Ctr : '||to_char(obj_cur.object_counter_id));
Update CSI_COUNTER_READINGS
set counter_reading = l_counter_reading,
net_reading = l_counter_reading,
life_to_date_reading = l_counter_reading,
disabled_flag = 'N',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_id = obj_cur.object_counter_id
and value_timestamp = p_ctr_rdg_rec.value_timestamp;
IF SQL%ROWCOUNT = 0 THEN -- If update was not successfull
-- Generate the Value_id for insert
l_process_flag := TRUE;
select CSI_COUNTER_READINGS_S.nextval
into l_ctr_value_id from dual;
csi_ctr_gen_utility_pvt.put_line('Unable to Update. Inserting Object Ctr : '||to_char(obj_cur.object_counter_id));
-- Call the Table Handler to insert the new reading
CSI_COUNTER_READINGS_PKG.Insert_Row(
px_COUNTER_VALUE_ID => l_ctr_value_id
,p_COUNTER_ID => obj_cur.object_counter_id
,p_VALUE_TIMESTAMP => p_ctr_rdg_rec.value_timestamp
,p_COUNTER_READING => l_counter_reading
,p_RESET_MODE => NULL
,p_RESET_REASON => NULL
,p_ADJUSTMENT_TYPE => NULL
,p_ADJUSTMENT_READING => NULL
,p_OBJECT_VERSION_NUMBER => 1
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => fnd_global.user_id
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_ATTRIBUTE1 => NULL
,p_ATTRIBUTE2 => NULL
,p_ATTRIBUTE3 => NULL
,p_ATTRIBUTE4 => NULL
,p_ATTRIBUTE5 => NULL
,p_ATTRIBUTE6 => NULL
,p_ATTRIBUTE7 => NULL
,p_ATTRIBUTE8 => NULL
,p_ATTRIBUTE9 => NULL
,p_ATTRIBUTE10 => NULL
,p_ATTRIBUTE11 => NULL
,p_ATTRIBUTE12 => NULL
,p_ATTRIBUTE13 => NULL
,p_ATTRIBUTE14 => NULL
,p_ATTRIBUTE15 => NULL
,p_ATTRIBUTE16 => NULL
,p_ATTRIBUTE17 => NULL
,p_ATTRIBUTE18 => NULL
,p_ATTRIBUTE19 => NULL
,p_ATTRIBUTE20 => NULL
,p_ATTRIBUTE21 => NULL
,p_ATTRIBUTE22 => NULL
,p_ATTRIBUTE23 => NULL
,p_ATTRIBUTE24 => NULL
,p_ATTRIBUTE25 => NULL
,p_ATTRIBUTE26 => NULL
,p_ATTRIBUTE27 => NULL
,p_ATTRIBUTE28 => NULL
,p_ATTRIBUTE29 => NULL
,p_ATTRIBUTE30 => NULL
,p_ATTRIBUTE_CATEGORY => NULL
,p_MIGRATED_FLAG => 'N'
,p_COMMENTS => NULL
,p_LIFE_TO_DATE_READING => l_counter_reading
,p_TRANSACTION_ID => p_txn_rec.transaction_id
,p_AUTOMATIC_ROLLOVER_FLAG => NULL
,p_INCLUDE_TARGET_RESETS => NULL
,p_SOURCE_COUNTER_VALUE_ID => NULL
,p_NET_READING => l_counter_reading
,p_DISABLED_FLAG => 'N'
,p_SOURCE_CODE => p_ctr_rdg_rec.source_code
,p_SOURCE_LINE_ID => p_ctr_rdg_rec.source_line_id
,p_INITIAL_READING_FLAG => p_ctr_rdg_rec.initial_reading_flag
);
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => obj_cur.object_counter_id
,px_ctr_val_max_seq_no => l_ctr_value_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => obj_cur.object_counter_id
,px_ctr_val_max_seq_no => l_tmp_ctr_value_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
ELSE -- If source counter is disabled then the corresponding formula counter reading is updated
csi_ctr_gen_utility_pvt.put_line('Disabling Obj Ctr '||to_char(obj_cur.object_counter_id));
Update CSI_COUNTER_READINGS
set counter_reading = l_counter_reading,
net_reading = l_counter_reading,
life_to_date_reading = l_counter_reading,
disabled_flag = decode(l_disabled_flag,'Y',decode(l_other_src_captured,'T','N','Y'),'N'),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_id = obj_cur.object_counter_id
and value_timestamp = p_ctr_rdg_rec.value_timestamp;
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => obj_cur.object_counter_id
,px_ctr_val_max_seq_no => l_tmp_ctr_value_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
-- l_formula_text := 'SELECT '||l_formula_text||' FROM DUAL';
Update CSI_COUNTER_READINGS
set counter_reading = l_counter_reading,
net_reading = l_counter_reading,
life_to_date_reading = l_counter_reading,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_value_id = later_rec.counter_value_id;
SELECT counter_id AS base_ctr_id,
value_timestamp AS base_ctr_timestamp
FROM csi_counter_readings
WHERE counter_value_id = p_ctr_rdg_rec.counter_value_id;
SELECT counter_id,
derive_function,
derive_counter_id,
derive_property_id,
filter_reading_count,
filter_type,
filter_time_uom
FROM csi_counters_b
WHERE derive_counter_id = base_ctr_id
AND NVL(start_date_active, base_ctr_timestamp) <= base_ctr_timestamp
AND NVL(end_date_active, (base_ctr_timestamp + 1)) > base_ctr_timestamp;
SELECT ctr.counter_id, ctr.derive_function,
ctr.derive_counter_id, ctr.derive_property_id,ctr.filter_reading_count,ctr.filter_type,
ctr.filter_time_uom, cv.value_timestamp as new_rdg_dt, cv.counter_id as base_ctr_id
FROM CSI_COUNTERS_B ctr, CSI_COUNTER_READINGS cv
WHERE cv.counter_value_id = p_ctr_rdg_rec.counter_value_id
AND ctr.derive_counter_id = cv.counter_id
AND NVL(ctr.start_date_active,cv.value_timestamp) <= cv.value_timestamp
AND NVL(ctr.end_date_active,(cv.value_timestamp+1)) > cv.value_timestamp;
SELECT filt.counter_property_id, filt.seq_no,filt.left_parent left_paren,
filt.right_parent right_paren, filt.relational_operator,
filt.logical_operator, filt.right_value,
pro.property_data_type
FROM CSI_COUNTER_DERIVED_FILTERS filt, CSI_COUNTER_PROPERTIES_B pro
WHERE filt.counter_id = b_counter_id
AND pro.counter_property_id(+) = filt.counter_property_id;
SELECT uom_code
FROM OKC_TIME_CODE_UNITS_V
WHERE uom_code=p_filter_time_uom;
SELECT net_reading net_rdg,value_timestamp
FROM CSI_COUNTER_READINGS
WHERE counter_id = d1_counter_id
AND value_timestamp <= p_ctr_rdg_rec.value_timestamp
AND NVL(disabled_flag,'N') = 'N'
order by value_timestamp desc;
select uom_code
from OKC_TIME_CODE_UNITS_V
where uom_code=p_filter_time_uom;
SELECT net_reading,value_timestamp
FROM CSI_COUNTER_READINGS
WHERE counter_id = d3_counter_id
AND NVL(disabled_flag,'N') = 'N'
order by value_timestamp asc;
SELECT net_reading
FROM CSI_COUNTER_READINGS
WHERE counter_value_id = p_ctr_rdg_rec.counter_value_id;
l_sqlstr_init := 'select '||ctrs.derive_function||'( nvl(net_reading,0) )';
l_sqlstr := l_sqlstr || ' select distinct cv1.counter_value_id from ';
IF p_mode = 'UPDATE' THEN
UPDATE CSI_COUNTER_READINGS
set counter_reading = l_ctr_value,
net_reading = l_ctr_value,
life_to_date_reading = l_ctr_value,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_id = ctrs.counter_id
and value_timestamp = l_ctr_in.value_timestamp
and source_counter_value_id = l_ctr_in.source_counter_value_id;
-- Generate the Value_id for insert
l_process_flag := TRUE;
select CSI_COUNTER_READINGS_S.nextval
into l_ctr_value_id from dual;
-- Call the Table Handler to insert the new reading
CSI_COUNTER_READINGS_PKG.Insert_Row(
px_COUNTER_VALUE_ID => l_ctr_value_id
,p_COUNTER_ID => l_ctr_in.counter_id
,p_VALUE_TIMESTAMP => l_ctr_in.value_timestamp
,p_COUNTER_READING => l_ctr_value
,p_RESET_MODE => NULL
,p_RESET_REASON => NULL
,p_ADJUSTMENT_TYPE => NULL
,p_ADJUSTMENT_READING => NULL
,p_OBJECT_VERSION_NUMBER => 1
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => fnd_global.user_id
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_ATTRIBUTE1 => NULL
,p_ATTRIBUTE2 => NULL
,p_ATTRIBUTE3 => NULL
,p_ATTRIBUTE4 => NULL
,p_ATTRIBUTE5 => NULL
,p_ATTRIBUTE6 => NULL
,p_ATTRIBUTE7 => NULL
,p_ATTRIBUTE8 => NULL
,p_ATTRIBUTE9 => NULL
,p_ATTRIBUTE10 => NULL
,p_ATTRIBUTE11 => NULL
,p_ATTRIBUTE12 => NULL
,p_ATTRIBUTE13 => NULL
,p_ATTRIBUTE14 => NULL
,p_ATTRIBUTE15 => NULL
,p_ATTRIBUTE16 => NULL
,p_ATTRIBUTE17 => NULL
,p_ATTRIBUTE18 => NULL
,p_ATTRIBUTE19 => NULL
,p_ATTRIBUTE20 => NULL
,p_ATTRIBUTE21 => NULL
,p_ATTRIBUTE22 => NULL
,p_ATTRIBUTE23 => NULL
,p_ATTRIBUTE24 => NULL
,p_ATTRIBUTE25 => NULL
,p_ATTRIBUTE26 => NULL
,p_ATTRIBUTE27 => NULL
,p_ATTRIBUTE28 => NULL
,p_ATTRIBUTE29 => NULL
,p_ATTRIBUTE30 => NULL
,p_ATTRIBUTE_CATEGORY => NULL
,p_MIGRATED_FLAG => 'N'
,p_COMMENTS => NULL
,p_LIFE_TO_DATE_READING => l_ctr_value
,p_TRANSACTION_ID => p_txn_rec.transaction_id
,p_AUTOMATIC_ROLLOVER_FLAG => NULL
,p_INCLUDE_TARGET_RESETS => NULL
,p_SOURCE_COUNTER_VALUE_ID => l_ctr_in.source_counter_value_id
,p_NET_READING => l_ctr_value
,p_DISABLED_FLAG => 'N'
,p_SOURCE_CODE => l_ctr_in.source_code
,p_SOURCE_LINE_ID => l_ctr_in.source_line_id
,p_INITIAL_READING_FLAG => l_ctr_in.initial_reading_flag
);
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => l_ctr_in.counter_id
,px_ctr_val_max_seq_no => l_ctr_value_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
select ccr.object_counter_id,nvl(ccr.factor,1) factor,
ccv.direction,ccv.reading_type, ccv.uom_code object_uom_code,
cct.name object_counter_name
from CSI_COUNTER_RELATIONSHIPS ccr,
CSI_COUNTERS_B ccv,
CSI_COUNTERS_TL cct
where ccr.source_counter_id = p_ctr_rdg_rec.counter_id
and ccr.relationship_type_code = l_rel_type
and nvl(ccr.active_start_date,sysdate) <= p_ctr_rdg_rec.value_timestamp
and nvl(ccr.active_end_date,(sysdate+1)) > p_ctr_rdg_rec.value_timestamp
and ccv.counter_id = ccr.object_counter_id
and ccv.counter_id = cct.counter_id
and cct.language = USERENV('LANG');
SELECT crg.counter_value_id,crg.value_timestamp,crg.counter_id object_counter_id,
crg.adjustment_reading,crg.counter_reading,nvl(ccr.factor,1) factor,
ccv.direction,ccv.reading_type,crg.reset_mode,
ccv.uom_code object_uom_code, cct.name object_counter_name
from CSI_COUNTER_READINGS crg,
CSI_COUNTER_RELATIONSHIPS ccr,
CSI_COUNTERS_B ccv,
CSI_COUNTERS_TL cct
where crg.source_counter_value_id = p_ctr_rdg_rec.counter_value_id
and ccr.object_counter_id = crg.counter_id
and ccr.source_counter_id = p_ctr_rdg_rec.counter_id
and ccr.relationship_type_code = l_rel_type
and ccv.counter_id = crg.counter_id
and ccv.counter_id = cct.counter_id
and cct.language = USERENV('LANG');
select counter_reading,net_reading,life_to_date_reading,value_timestamp
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp < p_value_timestamp
ORDER BY value_timestamp desc,counter_value_id desc;
SELECT uom_class
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code;
IF p_mode = 'UPDATE' THEN
csi_ctr_gen_utility_pvt.put_line(' Compute Target Counters - UPDATE');
SELECT uom_code, direction, reading_type
INTO l_source_uom_code, l_source_direction, l_src_reading_type
FROM csi_counters_b
WHERE counter_id = p_ctr_rdg_rec.counter_id;
Update CSI_COUNTER_READINGS
set counter_reading = l_counter_reading,
net_reading = l_net_reading,
life_to_date_reading = l_ltd_reading,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_value_id = obj_cur.counter_value_id;
SELECT uom_code, direction, reading_type
INTO l_source_uom_code, l_source_direction, l_src_reading_type
FROM csi_counters_b
WHERE counter_id = p_ctr_rdg_rec.counter_id;
csi_ctr_gen_utility_pvt.put_line('Calling Insert Meter Log...');
Eam_Asset_Log_Pvt.Insert_Meter_Log
(
P_api_version => 1.0,
P_init_msg_list => fnd_api.g_false,
P_commit => fnd_api.g_false,
P_validation_level => fnd_api.g_valid_level_full,
P_event_date => l_ctr_rdg_rec.value_timestamp,
P_instance_id => l_instance_id,
P_ref_id => l_ctr_rdg_rec.counter_id,
X_return_status => x_return_status,
X_msg_count => x_msg_count,
X_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM Insert_Meter_Log API ');
csi_ctr_gen_utility_pvt.put_line('Calling Update_Last_Service_Reading_Wo...');
Eam_Meters_Util.Update_Last_Service_Reading_Wo
(
p_wip_entity_id => p_txn_rec.source_header_ref_id,
p_meter_id => l_ctr_rdg_rec.counter_id,
p_meter_reading => l_counter_reading,
p_wo_end_date => l_ctr_rdg_rec.value_timestamp,
X_return_status => x_return_status,
X_msg_count => x_msg_count,
X_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM Update_Last_Service_Reading_Wo API ');
END IF; -- Call Update_Last_Service_Reading_WO check
-- Generate the Value_id for insert
l_process_flag := TRUE;
select CSI_COUNTER_READINGS_S.nextval
into l_ctr_rdg_rec.counter_value_id from dual;
csi_ctr_gen_utility_pvt.put_line('Inserting Target Counter ID : '||to_char(l_ctr_rdg_rec.counter_id));
-- Call the Table Handler to insert into CSI_COUNTER_READINGS
CSI_COUNTER_READINGS_PKG.Insert_Row(
px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp
,p_COUNTER_READING => l_counter_reading
,p_RESET_MODE => p_ctr_rdg_rec.reset_mode -- NULL 6398254
,p_RESET_REASON => p_ctr_rdg_rec.reset_reason -- NULL 6398254
,p_ADJUSTMENT_TYPE => l_ctr_rdg_rec.adjustment_type
,p_ADJUSTMENT_READING => l_ctr_rdg_rec.adjustment_reading
,p_OBJECT_VERSION_NUMBER => 1
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => fnd_global.user_id
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_ATTRIBUTE1 => l_ctr_rdg_rec.attribute1
,p_ATTRIBUTE2 => l_ctr_rdg_rec.attribute2
,p_ATTRIBUTE3 => l_ctr_rdg_rec.attribute3
,p_ATTRIBUTE4 => l_ctr_rdg_rec.attribute4
,p_ATTRIBUTE5 => l_ctr_rdg_rec.attribute5
,p_ATTRIBUTE6 => l_ctr_rdg_rec.attribute6
,p_ATTRIBUTE7 => l_ctr_rdg_rec.attribute7
,p_ATTRIBUTE8 => l_ctr_rdg_rec.attribute8
,p_ATTRIBUTE9 => l_ctr_rdg_rec.attribute9
,p_ATTRIBUTE10 => l_ctr_rdg_rec.attribute10
,p_ATTRIBUTE11 => l_ctr_rdg_rec.attribute11
,p_ATTRIBUTE12 => l_ctr_rdg_rec.attribute12
,p_ATTRIBUTE13 => l_ctr_rdg_rec.attribute13
,p_ATTRIBUTE14 => l_ctr_rdg_rec.attribute14
,p_ATTRIBUTE15 => l_ctr_rdg_rec.attribute15
,p_ATTRIBUTE16 => l_ctr_rdg_rec.attribute16
,p_ATTRIBUTE17 => l_ctr_rdg_rec.attribute17
,p_ATTRIBUTE18 => l_ctr_rdg_rec.attribute18
,p_ATTRIBUTE19 => l_ctr_rdg_rec.attribute19
,p_ATTRIBUTE20 => l_ctr_rdg_rec.attribute20
,p_ATTRIBUTE21 => l_ctr_rdg_rec.attribute21
,p_ATTRIBUTE22 => l_ctr_rdg_rec.attribute22
,p_ATTRIBUTE23 => l_ctr_rdg_rec.attribute23
,p_ATTRIBUTE24 => l_ctr_rdg_rec.attribute24
,p_ATTRIBUTE25 => l_ctr_rdg_rec.attribute25
,p_ATTRIBUTE26 => l_ctr_rdg_rec.attribute26
,p_ATTRIBUTE27 => l_ctr_rdg_rec.attribute27
,p_ATTRIBUTE28 => l_ctr_rdg_rec.attribute28
,p_ATTRIBUTE29 => l_ctr_rdg_rec.attribute29
,p_ATTRIBUTE30 => l_ctr_rdg_rec.attribute30
,p_ATTRIBUTE_CATEGORY => l_ctr_rdg_rec.attribute_category
,p_MIGRATED_FLAG => 'N'
,p_COMMENTS => l_ctr_rdg_rec.comments
,p_LIFE_TO_DATE_READING => l_ltd_reading
,p_TRANSACTION_ID => p_txn_rec.transaction_id
,p_AUTOMATIC_ROLLOVER_FLAG => l_ctr_rdg_rec.automatic_rollover_flag
,p_INCLUDE_TARGET_RESETS => l_ctr_rdg_rec.include_target_resets
,p_SOURCE_COUNTER_VALUE_ID => l_ctr_rdg_rec.source_counter_value_id
,p_NET_READING => l_net_reading
,p_DISABLED_FLAG => 'N'
,p_SOURCE_CODE => l_ctr_rdg_rec.source_code
,p_SOURCE_LINE_ID => l_ctr_rdg_rec.source_line_id
,p_INITIAL_READING_FLAG => l_ctr_rdg_rec.initial_reading_flag
);
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => l_ctr_rdg_rec.counter_id
,px_ctr_val_max_seq_no => l_ctr_rdg_rec.counter_value_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
/* procedure name: Update_Counter_Reading */
/* description : procedure used to */
/* disable counter readings */
/*----------------------------------------------------*/
PROCEDURE Update_Counter_Reading
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_ctr_rdg_rec IN OUT NOCOPY csi_ctr_datastructures_pub.counter_readings_rec
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COUNTER_READING_PVT';
l_update_loop BOOLEAN := FALSE;
select * from
CSI_COUNTER_READINGS
where counter_value_id = p_counter_value_id;
select counter_reading,net_reading,life_to_date_reading,value_timestamp
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp < p_value_timestamp
ORDER BY value_timestamp desc,counter_value_id desc;
select counter_reading,value_timestamp,reset_mode,
adjustment_type,automatic_rollover_flag
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp > p_value_timestamp
ORDER BY value_timestamp asc,counter_value_id asc;
select counter_value_id,counter_reading,net_reading,value_timestamp,adjustment_reading
,reset_mode,adjustment_type,include_target_resets
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp > p_value_timestamp
ORDER BY value_timestamp asc, counter_value_id asc;
SAVEPOINT update_counter_reading_pvt;
csi_ctr_gen_utility_pvt.put_line( 'update_counter_reading_pvt' ||'-'||
p_api_version ||'-'||
nvl(p_commit,FND_API.G_FALSE) ||'-'||
nvl(p_init_msg_list,FND_API.G_FALSE) ||'-'||
nvl(p_validation_level,FND_API.G_VALID_LEVEL_FULL) );
select transaction_type_id
into l_txn_type_id
from csi_transactions
where transaction_id = l_curr_ctr_rdg_rec.transaction_id;
csi_ctr_gen_utility_pvt.put_line('Cannot Update Counter ID...');
select name,counter_type,rollover_last_reading,
rollover_first_reading,direction,reading_type,
automatic_rollover
into l_ctr_name,l_ctr_type,l_rollover_last_rdg,l_rollover_first_rdg,
l_direction,l_reading_type,l_auto_rollover
from CSI_COUNTERS_VL
where counter_id = l_curr_ctr_rdg_rec.counter_id
and nvl(end_date_active,(sysdate+1)) > sysdate;
csi_ctr_gen_utility_pvt.put_line('Cannot Update Value Timestamp...');
csi_ctr_gen_utility_pvt.put_line('Cannot Update Counter Reading...');
csi_ctr_gen_utility_pvt.put_line('Cannot Update Adjustment Type...');
csi_ctr_gen_utility_pvt.put_line('Cannot Update Adjustment Reading...');
select max(reading_lock_date)
into l_rdg_lock_date
from CSI_COUNTER_READING_LOCKS
where counter_id = l_curr_ctr_rdg_rec.counter_id
OR counter_id in (select object_counter_id
from CSI_COUNTER_RELATIONSHIPS
where source_counter_id = l_curr_ctr_rdg_rec.counter_id
and nvl(active_end_date,(l_curr_ctr_rdg_rec.value_timestamp+1)) > l_curr_ctr_rdg_rec.value_timestamp);
csi_ctr_gen_utility_pvt.put_line('Calling Update Row to Disable the Reading...');
CSI_COUNTER_READINGS_PKG.Update_Row(
p_COUNTER_VALUE_ID => p_ctr_rdg_rec.counter_value_id
,p_COUNTER_ID => NULL
,p_VALUE_TIMESTAMP => NULL
,p_COUNTER_READING => NULL
,p_RESET_MODE => NULL
,p_RESET_REASON => p_ctr_rdg_rec.reset_reason -- NULL 6398254
,p_ADJUSTMENT_TYPE => NULL
,p_ADJUSTMENT_READING => NULL
,p_OBJECT_VERSION_NUMBER => p_ctr_rdg_rec.object_version_number
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_CREATION_DATE => NULL
,p_CREATED_BY => NULL
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_ATTRIBUTE1 => NULL
,p_ATTRIBUTE2 => NULL
,p_ATTRIBUTE3 => NULL
,p_ATTRIBUTE4 => NULL
,p_ATTRIBUTE5 => NULL
,p_ATTRIBUTE6 => NULL
,p_ATTRIBUTE7 => NULL
,p_ATTRIBUTE8 => NULL
,p_ATTRIBUTE9 => NULL
,p_ATTRIBUTE10 => NULL
,p_ATTRIBUTE11 => NULL
,p_ATTRIBUTE12 => NULL
,p_ATTRIBUTE13 => NULL
,p_ATTRIBUTE14 => NULL
,p_ATTRIBUTE15 => NULL
,p_ATTRIBUTE16 => NULL
,p_ATTRIBUTE17 => NULL
,p_ATTRIBUTE18 => NULL
,p_ATTRIBUTE19 => NULL
,p_ATTRIBUTE20 => NULL
,p_ATTRIBUTE21 => NULL
,p_ATTRIBUTE22 => NULL
,p_ATTRIBUTE23 => NULL
,p_ATTRIBUTE24 => NULL
,p_ATTRIBUTE25 => NULL
,p_ATTRIBUTE26 => NULL
,p_ATTRIBUTE27 => NULL
,p_ATTRIBUTE28 => NULL
,p_ATTRIBUTE29 => NULL
,p_ATTRIBUTE30 => NULL
,p_ATTRIBUTE_CATEGORY => NULL
,p_MIGRATED_FLAG => NULL
,p_COMMENTS => NULL
,p_LIFE_TO_DATE_READING => NULL
,p_TRANSACTION_ID => NULL
,p_AUTOMATIC_ROLLOVER_FLAG => NULL
,p_INCLUDE_TARGET_RESETS => NULL
,p_SOURCE_COUNTER_VALUE_ID => NULL
,p_NET_READING => NULL
,p_DISABLED_FLAG => p_ctr_rdg_rec.disabled_flag
,p_SOURCE_CODE => NULL
,p_SOURCE_LINE_ID => NULL
,p_INITIAL_READING_FLAG => NULL
);
CSI_COUNTER_PVT.update_ctr_val_max_seq_no(
p_api_version => 1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_true
,p_validation_level => fnd_api.g_valid_level_full
,p_counter_id => p_ctr_rdg_rec.counter_id
,px_ctr_val_max_seq_no => l_ctr_val_max_seq_no
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('ERROR FROM CSI_COUNTER_PVT.update_ctr_val_max_seq_no');
l_update_loop := TRUE;
-- we need to update the counter reading of that adjustment, provided it took the same disabled
-- counter reading. If a counter rdg was also captured as the part of adjustment then
-- no need to update the counter reading. Just retain the same and pass it to Net rdg calculation.
-- It will come back with the same values.
--
-- Above statements are true only for Absolute counters since we take the usage for Change counters
--
-- l_previous_rdg always takes the un-updated counter reading from the cursor
-- l_upd_previous_rdg takes the updated value which will be used to compute the usage for Targets
-- l_prev_ctr_reading is read outside the loop and never change. Used only for the first record
-- in the following Loop.
--
IF later_rdg.adjustment_type IS NOT NULL AND
l_reading_type = 1 THEN
IF l_adj_ctr = 1 THEN -- First record of Later Readings (Immediate record after disabled rdg)
-- Checking whether it took the same rdg as the disabled one.
IF later_rdg.counter_reading = l_curr_ctr_rdg_rec.counter_reading THEN
l_later_ctr_reading := l_prev_ctr_reading; -- The one before disable rdg
UPDATE CSI_COUNTER_READINGS
set net_reading = l_previous_net,
life_to_date_reading = l_previous_ltd,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_value_id = later_rdg.counter_value_id;
UPDATE CSI_COUNTER_READINGS
set counter_reading = l_later_ctr_reading,
net_reading = l_net_reading,
life_to_date_reading = l_ltd_reading,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id
where counter_value_id = later_rdg.counter_value_id;
csi_ctr_gen_utility_pvt.put_line('Calling Compute_Target_Counters for Update...');
,p_mode => 'UPDATE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('Calling Compute_Derive_Counters for Update...');
,p_mode => 'UPDATE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
IF NOT l_update_loop THEN
FOR later_rdg IN LATER_READINGS_CUR(l_curr_ctr_rdg_rec.counter_id,
l_curr_ctr_rdg_rec.value_timestamp)
LOOP
-- Re-calculate Compute Target Counters
-- For Resets which did not include Targets before, no need to Re-compute
--
IF later_rdg.reset_mode IS NULL OR
(later_rdg.reset_mode IS NOT NULL AND NVL(later_rdg.include_target_resets,'N') = 'Y') THEN
l_target_ctr_rec := l_temp_ctr_rdg_rec;
csi_ctr_gen_utility_pvt.put_line('Calling Compute_Target_Counters for Update...');
,p_mode => 'UPDATE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('Calling Compute_Derive_Counters for Update...');
,p_mode => 'UPDATE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END IF; -- l_update_loop check
csi_ctr_gen_utility_pvt.put_line('Calling Compute_Derive_Counters for Update...');
,p_mode => 'UPDATE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
csi_ctr_gen_utility_pvt.put_line('Calling Compute_Formula_Counters for Update...');
ROLLBACK TO update_counter_reading_pvt;
ROLLBACK TO update_counter_reading_pvt;
ROLLBACK TO update_counter_reading_pvt;
END Update_Counter_Reading;
select lookup_code,meaning --Meaning added for bug #6904836
from CSI_LOOKUPS
where lookup_type = (select property_lov_type
from CSI_COUNTER_PROPERTIES_B
where counter_property_id = p_prop_id);
select property_lov_type,property_data_type,is_nullable
into l_prop_lov_type,l_property_type, l_is_nullable
from CSI_COUNTER_PROPERTIES_B
where counter_property_id = p_ctr_prop_rdg_rec.counter_property_id
and nvl(end_date_active,(sysdate+1)) > sysdate;
select CSI_CTR_PROPERTY_READINGS_S.nextval
into p_ctr_prop_rdg_rec.counter_prop_value_id from dual;
CSI_CTR_PROPERTY_READING_PKG.Insert_Row(
px_COUNTER_PROP_VALUE_ID => p_ctr_prop_rdg_rec.counter_prop_value_id
,p_COUNTER_VALUE_ID => p_ctr_prop_rdg_rec.counter_value_id
,p_COUNTER_PROPERTY_ID => p_ctr_prop_rdg_rec.counter_property_id
,p_PROPERTY_VALUE => p_ctr_prop_rdg_rec.property_value
,p_VALUE_TIMESTAMP => p_ctr_prop_rdg_rec.value_timestamp
,p_OBJECT_VERSION_NUMBER => p_ctr_prop_rdg_rec.object_version_number
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => fnd_global.user_id
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_ATTRIBUTE1 => p_ctr_prop_rdg_rec.attribute1
,p_ATTRIBUTE2 => p_ctr_prop_rdg_rec.attribute2
,p_ATTRIBUTE3 => p_ctr_prop_rdg_rec.attribute3
,p_ATTRIBUTE4 => p_ctr_prop_rdg_rec.attribute4
,p_ATTRIBUTE5 => p_ctr_prop_rdg_rec.attribute5
,p_ATTRIBUTE6 => p_ctr_prop_rdg_rec.attribute6
,p_ATTRIBUTE7 => p_ctr_prop_rdg_rec.attribute7
,p_ATTRIBUTE8 => p_ctr_prop_rdg_rec.attribute8
,p_ATTRIBUTE9 => p_ctr_prop_rdg_rec.attribute9
,p_ATTRIBUTE10 => p_ctr_prop_rdg_rec.attribute10
,p_ATTRIBUTE11 => p_ctr_prop_rdg_rec.attribute11
,p_ATTRIBUTE12 => p_ctr_prop_rdg_rec.attribute12
,p_ATTRIBUTE13 => p_ctr_prop_rdg_rec.attribute13
,p_ATTRIBUTE14 => p_ctr_prop_rdg_rec.attribute14
,p_ATTRIBUTE15 => p_ctr_prop_rdg_rec.attribute15
,p_ATTRIBUTE_CATEGORY => p_ctr_prop_rdg_rec.attribute_category
,p_MIGRATED_FLAG => 'N'
);
select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and b.value_timestamp < b_rdg_date
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp desc;
select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and b.value_timestamp > b_rdg_date
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp asc;
select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and nvl(b.disabled_flag,'N') = 'N'
and trunc(b.value_timestamp) = trunc(b_rdg_date)
order by b.value_timestamp desc;
select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp asc;
select nvl(start_date_active,creation_date) "ctr_start_date"
from CSI_COUNTERS_B
where counter_id = b_counter_id;
select direction
from CSI_COUNTERS_B
where counter_id = b_counter_id;
select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and b.value_timestamp < b_rdg_date
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp desc;
select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and b.value_timestamp > b_rdg_date
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp asc;
select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and nvl(b.disabled_flag,'N') = 'N'
and trunc(b.value_timestamp) = trunc(b_rdg_date)
order by b.value_timestamp desc;
select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp asc;
select nvl(start_date_active,creation_date) "ctr_start_date"
from CSI_COUNTERS_B
where counter_id = b_counter_id;
select direction
from CSI_COUNTERS_B
where counter_id = b_counter_id;
select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and b.value_timestamp < b_rdg_date
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp desc;
select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and b.value_timestamp > b_rdg_date
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp asc;
select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and nvl(b.disabled_flag,'N') = 'N'
and trunc(b.value_timestamp) = trunc(b_rdg_date)
order by b.value_timestamp desc;
select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp asc;
select nvl(start_date_active,creation_date) "ctr_start_date"
from CSI_COUNTERS_B
where counter_id = b_counter_id;
select direction
from CSI_COUNTERS_B
where counter_id = b_counter_id;
select cval.net_reading AS NET_RDG, cval.value_timestamp
from CSI_COUNTER_READINGS cval
where cval.counter_id = d1_counter_id
and cval.value_timestamp < b_prd_rdg_date
and nvl(cval.disabled_flag,'N') = 'N'
order by cval.value_timestamp desc;
select group_id counter_group_id,estimation_id
from CSI_COUNTERS_B
where counter_id = p_counter_id;
select name, estimation_type, fixed_value, usage_markup,
default_value, estimation_avg_type
from CSI_CTR_ESTIMATE_METHODS_VL
where estimation_id = b_estimation_id;
select counter_id
from CSI_COUNTERS_B -- Need to change
where group_id = b_counter_group_id
and created_from_counter_tmpl_id =b_counter_id;
select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and nvl(b.disabled_flag,'N') = 'N'
and trunc(b.value_timestamp) = trunc(b_rdg_date)
order by b.value_timestamp desc;
select counter_value_id
FROM CSI_COUNTER_READINGS
WHERE counter_id = b_counter_id
and nvl(disabled_flag,'N') = 'N';
select 'X' into l_dummy
from CSI_COUNTERS_B
where counter_id = p_Counter_id;
select CSI_CTR_ESTIMATED_READINGS_S.nextval
into l_estimated_rdg_id from dual;
CSI_CTR_ESTIMATED_READINGS_PKG.Insert_Row
(
px_ESTIMATED_READING_ID => l_estimated_rdg_id
,p_COUNTER_ID => p_counter_id
,p_ESTIMATION_ID => l_estimation_id
,p_VALUE_TIMESTAMP => SYSDATE
,p_ESTIMATED_METER_READING => x_estimated_meter_reading
,p_NUM_OF_READINGS => p_number_of_readings
,p_PERIOD_START_DATE => p_estimation_period_start_date
,p_PERIOD_END_DATE => p_estimation_period_end_date
,p_AVG_CALCULATION_START_DATE => p_avg_calculation_start_date
,p_ESTIMATED_USAGE => x_estimated_usage_qty
,p_ATTRIBUTE1 => NULL
,p_ATTRIBUTE2 => NULL
,p_ATTRIBUTE3 => NULL
,p_ATTRIBUTE4 => NULL
,p_ATTRIBUTE5 => NULL
,p_ATTRIBUTE6 => NULL
,p_ATTRIBUTE7 => NULL
,p_ATTRIBUTE8 => NULL
,p_ATTRIBUTE9 => NULL
,p_ATTRIBUTE10 => NULL
,p_ATTRIBUTE11 => NULL
,p_ATTRIBUTE12 => NULL
,p_ATTRIBUTE13 => NULL
,p_ATTRIBUTE14 => NULL
,p_ATTRIBUTE15 => NULL
,p_ATTRIBUTE_CATEGORY => NULL
,p_LAST_UPDATE_DATE => SYSDATE
,p_LAST_UPDATED_BY => fnd_global.user_id
,p_LAST_UPDATE_LOGIN => fnd_global.conc_login_id
,p_CREATION_DATE => SYSDATE
,p_CREATED_BY => fnd_global.user_id
,p_OBJECT_VERSION_NUMBER => 1
,p_MIGRATED_FLAG => 'N'
);
select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and b.value_timestamp < b_rdg_date
and nvl(b.disabled_flag,'N') = 'N'
order by b.value_timestamp desc;
select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
from CSI_COUNTER_READINGS b
where b.counter_id = b_counter_id
and nvl(b.disabled_flag,'N') = 'N'
and trunc(b.value_timestamp) = trunc(b_rdg_date)
order by b.value_timestamp desc;