[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'x'
into l_dummy
from mtl_units_of_measure
where uom_code = p_uom_code;
SELECT 'x'
INTO l_dummy
FROM csi_counters_vl
WHERE name = p_name
AND counter_id <> nvl(p_ctr_id,-1);
sSQL := 'select ' || P_FORMULA_TEXT || ' from dual';
SELECT 'x'
INTO l_dummy
FROM csi_counters_vl
WHERE name = p_name
AND counter_id <> nvl(p_ctr_id,-1);
SELECT 'x'
INTO l_dummy
FROM csi_counter_properties_vl
WHERE name = p_name
AND counter_id = p_ctr_id
AND counter_property_id <> nvl(p_ctr_prop_id,-1);
select 'x'
into l_dummy
from mtl_system_items
where inventory_item_id = p_usage_item_id
and organization_id = l_inv_valdn_org_id
--and organization_id = cs_std.get_item_valdn_orgzn_id
and usage_item_flag = 'Y';
select 'x'
into l_dummy
from csi_counter_groups_v
where counter_group_id = p_group_id;
select 'Y'
into l_time_uom
from mtl_units_of_measure
where uom_code = p_uom_code
and upper(UOM_CLASS)='TIME';
select 'x'
into l_dummy
from csi_counter_template_b
where counter_id = l_created_from_counter_tmpl_id;
select CSI_COUNTERS_B_S.nextval
into l_counter_id from dual;
SELECT CS_COUNTER_GROUPS_S.nextval
INTO l_ctr_group_id FROM dual;
csi_ctr_gen_utility_pvt.put_line('Inserting Counter with Value ID '||to_char(l_counter_id));
CSI_COUNTERS_PKG.Insert_Row
(
px_COUNTER_ID => l_counter_id
,p_GROUP_ID => l_group_id
,p_COUNTER_TYPE => l_counter_type
,p_INITIAL_READING => l_initial_reading
,p_INITIAL_READING_DATE => l_initial_reading_date
,p_TOLERANCE_PLUS => l_tolerance_plus
,p_TOLERANCE_MINUS => l_tolerance_minus
,p_UOM_CODE => l_uom_code
,p_DERIVE_COUNTER_ID => l_derive_counter_id
,p_DERIVE_FUNCTION => l_derive_function
,p_DERIVE_PROPERTY_ID => null
,p_VALID_FLAG => null
,p_FORMULA_INCOMPLETE_FLAG => l_formula_incomplete_flag
,p_FORMULA_TEXT => l_formula_text
,p_ROLLOVER_LAST_READING => l_rollover_last_reading
,p_ROLLOVER_FIRST_READING => l_rollover_first_reading
,p_USAGE_ITEM_ID => l_usage_item_id
,p_CTR_VAL_MAX_SEQ_NO => 0
,p_START_DATE_ACTIVE => l_start_date_active
,p_END_DATE_ACTIVE => l_end_date_active
,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.USER_ID
,p_ATTRIBUTE1 => l_attribute1
,p_ATTRIBUTE2 => l_attribute2
,p_ATTRIBUTE3 => l_attribute3
,p_ATTRIBUTE4 => l_attribute4
,p_ATTRIBUTE5 => l_attribute5
,p_ATTRIBUTE6 => l_attribute6
,p_ATTRIBUTE7 => l_attribute7
,p_ATTRIBUTE8 => l_attribute8
,p_ATTRIBUTE9 => l_attribute9
,p_ATTRIBUTE10 => l_attribute10
,p_ATTRIBUTE11 => l_attribute11
,p_ATTRIBUTE12 => l_attribute12
,p_ATTRIBUTE13 => l_attribute13
,p_ATTRIBUTE14 => l_attribute14
,p_ATTRIBUTE15 => l_attribute15
,p_ATTRIBUTE16 => l_attribute16
,p_ATTRIBUTE17 => l_attribute17
,p_ATTRIBUTE18 => l_attribute18
,p_ATTRIBUTE19 => l_attribute19
,p_ATTRIBUTE20 => l_attribute20
,p_ATTRIBUTE21 => l_attribute21
,p_ATTRIBUTE22 => l_attribute22
,p_ATTRIBUTE23 => l_attribute23
,p_ATTRIBUTE24 => l_attribute24
,p_ATTRIBUTE25 => l_attribute25
,p_ATTRIBUTE26 => l_attribute26
,p_ATTRIBUTE27 => l_attribute27
,p_ATTRIBUTE28 => l_attribute28
,p_ATTRIBUTE29 => l_attribute29
,p_ATTRIBUTE30 => l_attribute30
,p_ATTRIBUTE_CATEGORY => l_attribute_category
,p_MIGRATED_FLAG => null
,p_CUSTOMER_VIEW => null
,p_DIRECTION => l_direction
,p_FILTER_TYPE => l_filter_type
,p_FILTER_READING_COUNT => l_filter_reading_count
,p_FILTER_TIME_UOM => l_filter_time_uom
,p_ESTIMATION_ID => l_estimation_id
--,p_COUNTER_CODE => l_name||'-'||l_counter_id
,p_READING_TYPE => l_reading_type
,p_AUTOMATIC_ROLLOVER => l_automatic_rollover
,p_DEFAULT_USAGE_RATE => l_default_usage_rate
,p_USE_PAST_READING => l_use_past_reading
,p_USED_IN_SCHEDULING => l_used_in_scheduling
,p_DEFAULTED_GROUP_ID => l_defaulted_group_id
,p_CREATED_FROM_COUNTER_TMPL_ID => l_created_from_counter_tmpl_id
,p_SECURITY_GROUP_ID => NULL
,p_STEP_VALUE => l_step_value
--,p_NAME => l_name||'-'||l_counter_id
,p_NAME => l_name
,p_DESCRIPTION => l_description
,p_TIME_BASED_MANUAL_ENTRY => l_time_based_manual_entry
,p_EAM_REQUIRED_FLAG => l_eam_required_flag
,p_comments => NULL
);
select 'x'
into l_dummy
from csi_counters_b
where counter_id = l_counter_id;
select 'x'
into l_dummy
from csi_counter_template_b c, csi_ctr_property_template_b p
where p.counter_id = c.counter_id
and p.counter_property_id = l_created_from_ctrprop_tmpl_id;
CSI_COUNTER_PROPERTIES_PKG.INSERT_ROW
(
px_COUNTER_PROPERTY_ID => l_counter_property_id
,p_COUNTER_ID => l_counter_id
,p_PROPERTY_DATA_TYPE => l_property_data_type
,p_IS_NULLABLE => l_is_nullable
,p_DEFAULT_VALUE => l_default_value
,p_MINIMUM_VALUE => l_minimum_value
,p_MAXIMUM_VALUE => l_maximum_value
,p_UOM_CODE => l_uom_code
,p_START_DATE_ACTIVE => l_start_date_active
,p_END_DATE_ACTIVE => l_end_date_active
,p_OBJECT_VERSION_NUMBER => 1
,p_SECURITY_GROUP_ID => null
,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.USER_ID
,p_ATTRIBUTE1 => l_attribute1
,p_ATTRIBUTE2 => l_attribute2
,p_ATTRIBUTE3 => l_attribute3
,p_ATTRIBUTE4 => l_attribute4
,p_ATTRIBUTE5 => l_attribute5
,p_ATTRIBUTE6 => l_attribute6
,p_ATTRIBUTE7 => l_attribute7
,p_ATTRIBUTE8 => l_attribute8
,p_ATTRIBUTE9 => l_attribute9
,p_ATTRIBUTE10 => l_attribute10
,p_ATTRIBUTE11 => l_attribute11
,p_ATTRIBUTE12 => l_attribute12
,p_ATTRIBUTE13 => l_attribute13
,p_ATTRIBUTE14 => l_attribute14
,p_ATTRIBUTE15 => l_attribute15
,p_ATTRIBUTE_CATEGORY => l_attribute_category
,p_MIGRATED_FLAG => null
,p_PROPERTY_LOV_TYPE => l_property_lov_type
,p_NAME => l_name
,p_DESCRIPTION => l_description
,p_create_from_ctr_prop_tmpl_id => l_created_from_ctrprop_tmpl_id
);
select 'X'
from csi_counter_associations
where counter_id = p_ctr_id
and source_object_id = p_src_obj_id;
select 'x'
into l_dummy
from csi_counters_b
where counter_id = l_counter_id;
select inventory_item_id, last_vld_organization_id, instance_number
into l_cp_inventory_id, l_cp_last_vld_org, l_instance_number
from csi_item_instances
where instance_id = l_source_object_id;
select 'x'
into l_dummy
from okc_k_lines_b
where id = l_source_object_id;
SELECT eam_item_type
INTO l_eam_item_type
FROM mtl_system_items_b
WHERE inventory_item_id = l_cp_inventory_id
AND organization_id = l_cp_last_vld_org;
SELECT maint_organization_id
INTO l_maint_organization_id
FROM mtl_parameters
WHERE organization_id = l_cp_last_vld_org;
CSI_COUNTER_ASSOCIATIONS_PKG.INSERT_ROW
(
px_INSTANCE_ASSOCIATION_ID => l_instance_association_id
,p_SOURCE_OBJECT_CODE => l_source_object_code
,p_SOURCE_OBJECT_ID => l_source_object_id
,p_COUNTER_ID => l_counter_id
,p_START_DATE_ACTIVE => l_start_date_active
,p_END_DATE_ACTIVE => l_end_date_active
,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.USER_ID
,p_ATTRIBUTE1 => l_attribute1
,p_ATTRIBUTE2 => l_attribute2
,p_ATTRIBUTE3 => l_attribute3
,p_ATTRIBUTE4 => l_attribute4
,p_ATTRIBUTE5 => l_attribute5
,p_ATTRIBUTE6 => l_attribute6
,p_ATTRIBUTE7 => l_attribute7
,p_ATTRIBUTE8 => l_attribute8
,p_ATTRIBUTE9 => l_attribute9
,p_ATTRIBUTE10 => l_attribute10
,p_ATTRIBUTE11 => l_attribute11
,p_ATTRIBUTE12 => l_attribute12
,p_ATTRIBUTE13 => l_attribute13
,p_ATTRIBUTE14 => l_attribute14
,p_ATTRIBUTE15 => l_attribute15
,p_ATTRIBUTE_CATEGORY => l_attribute_category
,p_MIGRATED_FLAG => null
,p_SECURITY_GROUP_ID => null
,p_MAINT_ORGANIZATION_ID => l_maint_organization_id
,p_PRIMARY_FAILURE_FLAG => l_primary_failure_flag
);
select value_timestamp
from CSI_COUNTER_READINGS
where counter_id = p_counter_id
and nvl(disabled_flag,'N') = 'N'
and value_timestamp <= p_reading_lock_date
ORDER BY value_timestamp desc;
select 'x'
into l_dummy
from csi_counters_b
where counter_id = l_counter_id;
CSI_COUNTER_READING_LOCK_PKG.INSERT_ROW
(
px_reading_lock_id => l_reading_lock_id
,p_counter_id => l_counter_id
-- ,p_reading_lock_date => l_last_reading_date
,p_reading_lock_date => l_reading_lock_date
--,p_active_start_date => l_active_start_date
--,p_active_end_date => l_active_end_date
,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.USER_ID
,p_SOURCE_GROUP_REF_ID => l_source_group_ref_id
,p_SOURCE_GROUP_REF => l_source_group_ref
,p_SOURCE_HEADER_REF_ID => l_source_header_ref_id
,p_SOURCE_HEADER_REF => l_source_header_ref
,p_SOURCE_LINE_REF_ID => l_source_line_ref_id
,p_SOURCE_LINE_REF => l_source_line_ref
,p_SOURCE_DIST_REF_ID1 => l_source_dist_ref_id1
,p_SOURCE_DIST_REF_ID2 => l_source_dist_ref_id2
);
SELECT max(active_start_date)
INTO l_max_start_date
FROM CSI_COUNTER_USAGE_FORECAST
WHERE counter_id = l_counter_id;
select 'x'
into l_dummy
from csi_counters_b
where counter_id = l_counter_id;
UPDATE CSI_COUNTER_USAGE_FORECAST
SET active_end_date = l_active_start_date
-- SET active_end_date = sysdate
WHERE instance_forecast_id in (select instance_forecast_id
from CSI_COUNTER_USAGE_FORECAST
where counter_id = l_counter_id
and active_end_date is null);
CSI_CTR_USAGE_FORECAST_PKG.insert_row
(
px_instance_forecast_id => l_instance_forecast_id
,p_counter_id => l_counter_id
,p_usage_rate => l_usage_rate
,p_use_past_reading => l_use_past_reading
,p_active_start_date => l_active_start_date
,p_active_end_date => l_active_end_date
,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.USER_ID
);
PROCEDURE update_counter
(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_validation_level IN VARCHAR2
,p_counter_instance_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.Counter_instance_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';
SELECT relationship_id
FROM csi_counter_relationships
WHERE object_counter_id = p_counter_id and relationship_type_code = 'FORMULA';
SELECT counter_derived_filter_id
FROM csi_counter_derived_filters
WHERE counter_id = p_counter_id;
SELECT relationship_id
FROM csi_counter_relationships
WHERE source_counter_id = p_counter_id and relationship_type_code = 'CONFIGURATION';
SELECT counter_value_id
FROM csi_counter_readings
WHERE counter_id = p_counter_id;
SAVEPOINT update_counter_pvt;
csi_ctr_gen_utility_pvt.put_line( 'update_counter');
csi_ctr_gen_utility_pvt.put_line( 'update_counter' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level );
SELECT group_id,
counter_type,
initial_reading,
initial_reading_date,
created_from_counter_tmpl_id,
tolerance_plus,
tolerance_minus,
uom_code,
derive_counter_id,
derive_function,
valid_flag,
formula_incomplete_flag,
formula_text,
rollover_last_reading,
rollover_first_reading,
usage_item_id,
ctr_val_max_seq_no,
start_date_active,
end_date_active,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
customer_view,
direction,
filter_type,
filter_reading_count,
filter_time_uom,
estimation_id,
--counter_code,
reading_type,
automatic_rollover,
default_usage_rate,
use_past_reading,
used_in_scheduling,
defaulted_group_id
,name
,comments
,step_value
,object_version_number
,time_based_manual_entry
,eam_required_flag
INTO l_old_counter_instance_rec.group_id,
l_old_counter_instance_rec.counter_type,
l_old_counter_instance_rec.initial_reading,
l_old_counter_instance_rec.initial_reading_date,
l_old_counter_instance_rec.created_from_counter_tmpl_id,
l_old_counter_instance_rec.tolerance_plus,
l_old_counter_instance_rec.tolerance_minus,
l_old_counter_instance_rec.uom_code,
l_old_counter_instance_rec.derive_counter_id,
l_old_counter_instance_rec.derive_function,
l_old_counter_instance_rec.valid_flag,
l_old_counter_instance_rec.formula_incomplete_flag,
l_old_counter_instance_rec.formula_text,
l_old_counter_instance_rec.rollover_last_reading,
l_old_counter_instance_rec.rollover_first_reading,
l_old_counter_instance_rec.usage_item_id,
l_old_counter_instance_rec.ctr_val_max_seq_no,
l_old_counter_instance_rec.start_date_active,
l_old_counter_instance_rec.end_date_active,
l_old_counter_instance_rec.attribute1,
l_old_counter_instance_rec.attribute2,
l_old_counter_instance_rec.attribute3,
l_old_counter_instance_rec.attribute4,
l_old_counter_instance_rec.attribute5,
l_old_counter_instance_rec.attribute6,
l_old_counter_instance_rec.attribute7,
l_old_counter_instance_rec.attribute8,
l_old_counter_instance_rec.attribute9,
l_old_counter_instance_rec.attribute10,
l_old_counter_instance_rec.attribute11,
l_old_counter_instance_rec.attribute12,
l_old_counter_instance_rec.attribute13,
l_old_counter_instance_rec.attribute14,
l_old_counter_instance_rec.attribute15,
l_old_counter_instance_rec.attribute16,
l_old_counter_instance_rec.attribute17,
l_old_counter_instance_rec.attribute18,
l_old_counter_instance_rec.attribute19,
l_old_counter_instance_rec.attribute20,
l_old_counter_instance_rec.attribute21,
l_old_counter_instance_rec.attribute22,
l_old_counter_instance_rec.attribute23,
l_old_counter_instance_rec.attribute24,
l_old_counter_instance_rec.attribute25,
l_old_counter_instance_rec.attribute26,
l_old_counter_instance_rec.attribute27,
l_old_counter_instance_rec.attribute28,
l_old_counter_instance_rec.attribute29,
l_old_counter_instance_rec.attribute30,
l_old_counter_instance_rec.attribute_category,
l_old_counter_instance_rec.customer_view,
l_old_counter_instance_rec.direction,
l_old_counter_instance_rec.filter_type,
l_old_counter_instance_rec.filter_reading_count,
l_old_counter_instance_rec.filter_time_uom,
l_old_counter_instance_rec.estimation_id,
--l_old_counter_instance_rec.counter_code,
l_old_counter_instance_rec.reading_type,
l_old_counter_instance_rec.automatic_rollover,
l_old_counter_instance_rec.default_usage_rate,
l_old_counter_instance_rec.use_past_reading,
l_old_counter_instance_rec.used_in_scheduling,
l_old_counter_instance_rec.defaulted_group_id
,l_old_counter_instance_rec.name
,l_old_counter_instance_rec.comments
,l_old_counter_instance_rec.step_value
,l_old_counter_instance_rec.object_version_number
,l_old_counter_instance_rec.time_based_manual_entry
,l_old_counter_instance_rec.eam_required_flag
FROM csi_counters_vl
WHERE counter_id = p_counter_instance_rec.counter_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
csi_ctr_gen_utility_pvt.put_line('Object version mismatch in update counter');
csi_ctr_gen_utility_pvt.put_line('used in scheduling cannot be updated');
CSI_COUNTERS_PKG.update_row
(
p_counter_id => p_counter_instance_rec.counter_id
,p_group_id => p_counter_instance_rec.group_id
,p_counter_type => p_counter_instance_rec.counter_type
,p_initial_reading => p_counter_instance_rec.initial_reading
,p_initial_reading_date => p_counter_instance_rec.initial_reading_date
,p_tolerance_plus => p_counter_instance_rec.tolerance_plus
,p_tolerance_minus => p_counter_instance_rec.tolerance_minus
,p_uom_code => p_counter_instance_rec.uom_code
,p_derive_counter_id => p_counter_instance_rec.derive_counter_id
,p_derive_function => p_counter_instance_rec.derive_function
,p_derive_property_id => p_counter_instance_rec.derive_property_id
,p_valid_flag => p_counter_instance_rec.valid_flag
,p_formula_incomplete_flag => p_counter_instance_rec.formula_incomplete_flag
,p_formula_text => p_counter_instance_rec.formula_text
,p_rollover_last_reading => p_counter_instance_rec.rollover_last_reading
,p_rollover_first_reading => p_counter_instance_rec.rollover_first_reading
,p_usage_item_id => p_counter_instance_rec.usage_item_id
,p_ctr_val_max_seq_no => p_counter_instance_rec.ctr_val_max_seq_no
,p_start_date_active => p_counter_instance_rec.start_date_active
,p_end_date_active => p_counter_instance_rec.end_date_active
,p_object_version_number => p_counter_instance_rec.object_version_number + 1
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.USER_ID
,p_creation_date => p_counter_instance_rec.creation_date
,p_created_by => p_counter_instance_rec.created_by
,p_last_update_login => FND_GLOBAL.USER_ID
,p_attribute1 => p_counter_instance_rec.attribute1
,p_attribute2 => p_counter_instance_rec.attribute2
,p_attribute3 => p_counter_instance_rec.attribute3
,p_attribute4 => p_counter_instance_rec.attribute4
,p_attribute5 => p_counter_instance_rec.attribute5
,p_attribute6 => p_counter_instance_rec.attribute6
,p_attribute7 => p_counter_instance_rec.attribute7
,p_attribute8 => p_counter_instance_rec.attribute8
,p_attribute9 => p_counter_instance_rec.attribute9
,p_attribute10 => p_counter_instance_rec.attribute10
,p_attribute11 => p_counter_instance_rec.attribute11
,p_attribute12 => p_counter_instance_rec.attribute12
,p_attribute13 => p_counter_instance_rec.attribute13
,p_attribute14 => p_counter_instance_rec.attribute14
,p_attribute15 => p_counter_instance_rec.attribute15
,p_attribute16 => p_counter_instance_rec.attribute16
,p_attribute17 => p_counter_instance_rec.attribute17
,p_attribute18 => p_counter_instance_rec.attribute18
,p_attribute19 => p_counter_instance_rec.attribute19
,p_attribute20 => p_counter_instance_rec.attribute20
,p_attribute21 => p_counter_instance_rec.attribute21
,p_attribute22 => p_counter_instance_rec.attribute22
,p_attribute23 => p_counter_instance_rec.attribute23
,p_attribute24 => p_counter_instance_rec.attribute24
,p_attribute25 => p_counter_instance_rec.attribute25
,p_attribute26 => p_counter_instance_rec.attribute26
,p_attribute27 => p_counter_instance_rec.attribute27
,p_attribute28 => p_counter_instance_rec.attribute28
,p_attribute29 => p_counter_instance_rec.attribute29
,p_attribute30 => p_counter_instance_rec.attribute30
,p_attribute_category => p_counter_instance_rec.attribute_category
,p_migrated_flag => p_counter_instance_rec.migrated_flag
,p_customer_view => p_counter_instance_rec.customer_view
,p_direction => p_counter_instance_rec.direction
,p_filter_type => p_counter_instance_rec.filter_type
,p_filter_reading_count => p_counter_instance_rec.filter_reading_count
,p_filter_time_uom => p_counter_instance_rec.filter_time_uom
,p_estimation_id => p_counter_instance_rec.estimation_id
--,p_counter_code => p_counter_instance_rec.counter_code
,p_reading_type => p_counter_instance_rec.reading_type
,p_automatic_rollover => p_counter_instance_rec.automatic_rollover
,p_default_usage_rate => p_counter_instance_rec.default_usage_rate
,p_use_past_reading => p_counter_instance_rec.use_past_reading
,p_used_in_scheduling => p_counter_instance_rec.used_in_scheduling
,p_defaulted_group_id => p_counter_instance_rec.defaulted_group_id
,p_created_from_counter_tmpl_id => p_counter_instance_rec.created_from_counter_tmpl_id
,p_SECURITY_GROUP_ID => p_counter_instance_rec.SECURITY_GROUP_ID
,p_STEP_VALUE => p_counter_instance_rec.step_value
,p_name => l_counter_instance_rec.name
,p_description => p_counter_instance_rec.description
,p_time_based_manual_entry => p_counter_instance_rec.time_based_manual_entry
,p_eam_required_flag => p_counter_instance_rec.eam_required_flag
,p_comments => NULL
);
ROLLBACK TO update_counter_pvt;
ROLLBACK TO update_counter_pvt;
ROLLBACK TO update_counter_pvt;
ROLLBACK TO update_counter_pvt;
END update_counter;
PROCEDURE update_ctr_property
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,P_ctr_properties_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.Ctr_properties_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_CTR_PROPERTY';
SELECT counter_prop_value_id
FROM CSI_CTR_PROPERTY_READINGS
WHERE counter_property_id = p_counter_property_id;
SAVEPOINT update_ctr_property_pvt;
csi_ctr_gen_utility_pvt.put_line( 'update_ctr_property');
csi_ctr_gen_utility_pvt.put_line( 'update_ctr_property' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level );
SELECT NAME
,DESCRIPTION
,COUNTER_ID
,PROPERTY_DATA_TYPE
,IS_NULLABLE
,DEFAULT_VALUE
,MINIMUM_VALUE
, MAXIMUM_VALUE
, UOM_CODE
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE_CATEGORY
, PROPERTY_LOV_TYPE
, object_version_number
--, created_from_ctr_prop_tmpl_id
INTO l_old_ctr_properties_rec.NAME
,l_old_ctr_properties_rec.DESCRIPTION
,l_old_ctr_properties_rec.COUNTER_ID
,l_old_ctr_properties_rec.PROPERTY_DATA_TYPE
,l_old_ctr_properties_rec.IS_NULLABLE
,l_old_ctr_properties_rec.DEFAULT_VALUE
,l_old_ctr_properties_rec.MINIMUM_VALUE
,l_old_ctr_properties_rec.MAXIMUM_VALUE
,l_old_ctr_properties_rec.UOM_CODE
,l_old_ctr_properties_rec.START_DATE_ACTIVE
,l_old_ctr_properties_rec.END_DATE_ACTIVE
,l_old_ctr_properties_rec.ATTRIBUTE1
,l_old_ctr_properties_rec.ATTRIBUTE2
,l_old_ctr_properties_rec.ATTRIBUTE3
,l_old_ctr_properties_rec.ATTRIBUTE4
,l_old_ctr_properties_rec.ATTRIBUTE5
,l_old_ctr_properties_rec.ATTRIBUTE6
,l_old_ctr_properties_rec.ATTRIBUTE7
,l_old_ctr_properties_rec.ATTRIBUTE8
,l_old_ctr_properties_rec.ATTRIBUTE9
,l_old_ctr_properties_rec.ATTRIBUTE10
,l_old_ctr_properties_rec.ATTRIBUTE11
,l_old_ctr_properties_rec.ATTRIBUTE12
,l_old_ctr_properties_rec.ATTRIBUTE13
,l_old_ctr_properties_rec.ATTRIBUTE14
,l_old_ctr_properties_rec.ATTRIBUTE15
,l_old_ctr_properties_rec.ATTRIBUTE_CATEGORY
,l_old_ctr_properties_rec.PROPERTY_LOV_TYPE
,l_old_ctr_properties_rec.object_version_number
--,l_old_ctr_properties_rec.created_from_ctr_prop_tmpl_id
FROM csi_counter_properties_vl
WHERE counter_property_id = p_ctr_properties_rec.counter_property_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
csi_ctr_gen_utility_pvt.put_line('Object version mismatch in update counter');
CSI_COUNTER_PROPERTIES_PKG.update_row
(
p_counter_property_id => p_ctr_properties_rec.counter_property_id
,p_counter_id => p_ctr_properties_rec.counter_id
,p_property_data_type => p_ctr_properties_rec.property_data_type
,p_is_nullable => p_ctr_properties_rec.is_nullable
,p_default_value => p_ctr_properties_rec.default_value
,p_minimum_value => p_ctr_properties_rec.minimum_value
,p_maximum_value => p_ctr_properties_rec.maximum_value
,p_uom_code => p_ctr_properties_rec.uom_code
,p_start_date_active => p_ctr_properties_rec.start_date_active
,p_end_date_active => p_ctr_properties_rec.end_date_active
,p_object_version_number => p_ctr_properties_rec.object_version_number + 1
,p_SECURITY_GROUP_ID => null
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.USER_ID
,p_creation_date => p_ctr_properties_rec.creation_date
,p_created_by => p_ctr_properties_rec.created_by
,p_last_update_login => FND_GLOBAL.USER_ID
,p_attribute1 => p_ctr_properties_rec.attribute1
,p_attribute2 => p_ctr_properties_rec.attribute2
,p_attribute3 => p_ctr_properties_rec.attribute3
,p_attribute4 => p_ctr_properties_rec.attribute4
,p_attribute5 => p_ctr_properties_rec.attribute5
,p_attribute6 => p_ctr_properties_rec.attribute6
,p_attribute7 => p_ctr_properties_rec.attribute7
,p_attribute8 => p_ctr_properties_rec.attribute8
,p_attribute9 => p_ctr_properties_rec.attribute9
,p_attribute10 => p_ctr_properties_rec.attribute10
,p_attribute11 => p_ctr_properties_rec.attribute11
,p_attribute12 => p_ctr_properties_rec.attribute12
,p_attribute13 => p_ctr_properties_rec.attribute13
,p_attribute14 => p_ctr_properties_rec.attribute14
,p_attribute15 => p_ctr_properties_rec.attribute15
,p_attribute_category => p_ctr_properties_rec.attribute_category
,p_migrated_flag => p_ctr_properties_rec.migrated_flag
,p_property_lov_type => p_ctr_properties_rec.property_lov_type
,p_name => p_ctr_properties_rec.name
,p_description => p_ctr_properties_rec.description
,p_create_from_ctr_prop_tmpl_id => p_ctr_properties_rec.created_from_ctr_prop_tmpl_id
);
ROLLBACK TO update_ctr_property_pvt;
ROLLBACK TO update_ctr_property_pvt;
ROLLBACK TO update_ctr_property_pvt;
ROLLBACK TO update_ctr_property_pvt;
END update_ctr_property;
PROCEDURE update_ctr_associations
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,P_counter_associations_rec IN out NOCOPY CSI_CTR_DATASTRUCTURES_PUB.counter_associations_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_CTR_ASSOCIATIONS';
SAVEPOINT update_ctr_associations_pvt;
csi_gen_utility_pvt.put_line( 'update_ctr_associations');
csi_gen_utility_pvt.put_line( 'update_ctr_associations' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level );
SELECT SOURCE_OBJECT_CODE
, SOURCE_OBJECT_ID
, COUNTER_ID
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE_CATEGORY
, object_version_number
, maint_organization_id
, primary_failure_flag
INTO l_old_counter_associations_rec.SOURCE_OBJECT_CODE
, l_old_counter_associations_rec.SOURCE_OBJECT_ID
, l_old_counter_associations_rec.COUNTER_ID
, l_old_counter_associations_rec.START_DATE_ACTIVE
, l_old_counter_associations_rec.END_DATE_ACTIVE
, l_old_counter_associations_rec.ATTRIBUTE1
, l_old_counter_associations_rec.ATTRIBUTE2
, l_old_counter_associations_rec.ATTRIBUTE3
, l_old_counter_associations_rec.ATTRIBUTE4
, l_old_counter_associations_rec.ATTRIBUTE5
, l_old_counter_associations_rec.ATTRIBUTE6
, l_old_counter_associations_rec.ATTRIBUTE7
, l_old_counter_associations_rec.ATTRIBUTE8
, l_old_counter_associations_rec.ATTRIBUTE9
, l_old_counter_associations_rec.ATTRIBUTE10
, l_old_counter_associations_rec.ATTRIBUTE11
, l_old_counter_associations_rec.ATTRIBUTE12
, l_old_counter_associations_rec.ATTRIBUTE13
, l_old_counter_associations_rec.ATTRIBUTE14
, l_old_counter_associations_rec.ATTRIBUTE15
, l_old_counter_associations_rec.ATTRIBUTE_CATEGORY
, l_old_counter_associations_rec.object_version_number
, l_old_counter_associations_rec.maint_organization_id
, l_old_counter_associations_rec.primary_failure_flag
FROM CSI_COUNTER_ASSOCIATIONS
WHERE INSTANCE_ASSOCIATION_ID = P_counter_associations_rec.INSTANCE_ASSOCIATION_ID
FOR UPDATE OF OBJECT_VERSION_NUMBER;
csi_ctr_gen_utility_pvt.put_line('Object version mismatch in update counter');
CSI_COUNTER_ASSOCIATIONS_PKG.UPDATE_ROW
(
p_INSTANCE_ASSOCIATION_ID => p_counter_associations_rec.INSTANCE_ASSOCIATION_ID
,p_SOURCE_OBJECT_CODE => p_counter_associations_rec.SOURCE_OBJECT_CODE
,p_SOURCE_OBJECT_ID => p_counter_associations_rec.SOURCE_OBJECT_ID
,p_OBJECT_VERSION_NUMBER => p_counter_associations_rec.OBJECT_VERSION_NUMBER + 1
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
,p_CREATION_DATE => p_counter_associations_rec.CREATION_DATE
,p_CREATED_BY => p_counter_associations_rec.CREATED_BY
,p_ATTRIBUTE1 => p_counter_associations_rec.ATTRIBUTE1
,p_ATTRIBUTE2 => p_counter_associations_rec.ATTRIBUTE2
,p_ATTRIBUTE3 => p_counter_associations_rec.ATTRIBUTE3
,p_ATTRIBUTE4 => p_counter_associations_rec.ATTRIBUTE4
,p_ATTRIBUTE5 => p_counter_associations_rec.ATTRIBUTE5
,p_ATTRIBUTE6 => p_counter_associations_rec.ATTRIBUTE6
,p_ATTRIBUTE7 => p_counter_associations_rec.ATTRIBUTE7
,p_ATTRIBUTE8 => p_counter_associations_rec.ATTRIBUTE8
,p_ATTRIBUTE9 => p_counter_associations_rec.ATTRIBUTE9
,p_ATTRIBUTE10 => p_counter_associations_rec.ATTRIBUTE10
,p_ATTRIBUTE11 => p_counter_associations_rec.ATTRIBUTE11
,p_ATTRIBUTE12 => p_counter_associations_rec.ATTRIBUTE12
,p_ATTRIBUTE13 => p_counter_associations_rec.ATTRIBUTE13
,p_ATTRIBUTE14 => p_counter_associations_rec.ATTRIBUTE14
,p_ATTRIBUTE15 => p_counter_associations_rec.ATTRIBUTE15
,p_ATTRIBUTE_CATEGORY => p_counter_associations_rec.ATTRIBUTE_CATEGORY
,p_SECURITY_GROUP_ID => p_counter_associations_rec.SECURITY_GROUP_ID
,p_MIGRATED_FLAG => p_counter_associations_rec.MIGRATED_FLAG
,p_COUNTER_ID => p_counter_associations_rec.COUNTER_ID
,p_START_DATE_ACTIVE => p_counter_associations_rec.START_DATE_ACTIVE
,p_END_DATE_ACTIVE => p_counter_associations_rec.END_DATE_ACTIVE
,p_MAINT_ORGANIZATION_ID => p_counter_associations_rec.MAINT_ORGANIZATION_ID
,p_PRIMARY_FAILURE_FLAG => p_counter_associations_rec.PRIMARY_FAILURE_FLAG
);
ROLLBACK TO update_ctr_associations_pvt;
ROLLBACK TO update_ctr_associations_pvt;
ROLLBACK TO update_ctr_associations_pvt;
ROLLBACK TO update_ctr_associations_pvt;
END update_ctr_associations;
PROCEDURE update_ctr_val_max_seq_no
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_counter_id IN NUMBER
,px_ctr_val_max_seq_no IN OUT NOCOPY NUMBER
,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_CTR_VAL_MAX_SEQ_NO';
SAVEPOINT update_ctr_val_max_seq_no;
csi_ctr_gen_utility_pvt.put_line( 'update_ctr_val_max_seq_no');
csi_ctr_gen_utility_pvt.put_line('update_ctr_val_max_seq_no' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level);
SELECT ctr_val_max_seq_no, object_version_number
INTO l_old_ctr_val_max_seq_no, l_old_object_version_number
FROM csi_counters_b
WHERE counter_id = p_counter_id;
SELECT 'Y', value_timestamp
INTO l_ctr_val_id_exist, l_new_value_timestamp
FROM csi_counter_readings
WHERE counter_id = p_counter_id
AND counter_value_id = px_ctr_val_max_seq_no
AND NVL(disabled_flag, 'N') = 'N';
SELECT 'Y'
INTO l_new_reading_is_latest
FROM csi_counter_readings
WHERE counter_id = p_counter_id
AND counter_value_id = l_old_ctr_val_max_seq_no
AND NVL(disabled_flag, 'N') = 'N'
AND value_timestamp < l_new_value_timestamp;
SELECT counter_value_id, value_timestamp, 'Y', 'Y'
INTO px_ctr_val_max_seq_no, l_new_value_timestamp,
l_ctr_val_id_exist, l_new_reading_is_latest
FROM csi_counter_readings
WHERE counter_id = p_counter_id
AND value_timestamp =
(SELECT MAX(value_timestamp) FROM csi_counter_readings
WHERE counter_id = p_counter_id AND NVL(disabled_flag, 'N') = 'N');
CSI_COUNTERS_PKG.update_row
(
p_counter_id => p_counter_id
,p_group_id => NULL
,p_counter_type => NULL
,p_initial_reading => NULL
,p_initial_reading_date => NULL
,p_tolerance_plus => NULL
,p_tolerance_minus => NULL
,p_uom_code => NULL
,p_derive_counter_id => NULL
,p_derive_function => NULL
,p_derive_property_id => NULL
,p_valid_flag => NULL
,p_formula_incomplete_flag => NULL
,p_formula_text => NULL
,p_rollover_last_reading => NULL
,p_rollover_first_reading => NULL
,p_usage_item_id => NULL
,p_ctr_val_max_seq_no => px_ctr_val_max_seq_no
,p_start_date_active => NULL
,p_end_date_active => NULL
,p_object_version_number => l_old_object_version_number + 1
,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.USER_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_customer_view => NULL
,p_direction => NULL
,p_filter_type => NULL
,p_filter_reading_count => NULL
,p_filter_time_uom => NULL
,p_estimation_id => NULL
,p_reading_type => NULL
,p_automatic_rollover => NULL
,p_default_usage_rate => NULL
,p_use_past_reading => NULL
,p_used_in_scheduling => NULL
,p_defaulted_group_id => NULL
,p_created_from_counter_tmpl_id => NULL
,p_security_group_id => NULL
,p_step_value => NULL
,p_name => NULL
,p_description => NULL
,p_time_based_manual_entry => NULL
,p_eam_required_flag => NULL
,p_comments => NULL
);
ROLLBACK TO update_ctr_val_max_seq_no;
ROLLBACK TO update_ctr_val_max_seq_no;
ROLLBACK TO update_ctr_val_max_seq_no;
END update_ctr_val_max_seq_no;