[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
INTO l_dummy
FROM cs_csi_counter_groups
WHERE name = p_name
AND template_flag = p_template_flag;
SELECT 'x'
INTO l_dummy
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = l_inv_valdn_org_id;
SELECT 'x'
INTO l_dummy
FROM csi_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
OR meaning= p_lookup_code; --Added for bug #6904836
SELECT 'x'
INTO l_dummy
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code;
SELECT direction, start_date_active, end_date_active
INTO x_direction, x_start_date, x_end_date
FROM csi_counters_b
WHERE counter_id = p_counter_id;
,p_update_flag VARCHAR2
) IS
l_dummy varchar2(1);
--Changed for Bug 7462345, validating the name while update
BEGIN
SELECT 'x'
INTO l_exists
FROM csi_counter_template_vl
WHERE upper(name) = upper(p_name)
AND COUNTER_ID <> Nvl(p_counter_id, FND_API.G_MISS_NUM);
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 cs_csi_counter_groups
where counter_group_id = p_group_id;
select 'x'
into l_ctr_type_valid
from csi_lookups
where lookup_type = 'CSI_COUNTER_TYPE'
and lookup_code = p_counter_type;
select 'Y'
into l_time_uom
from mtl_units_of_measure
where uom_code = p_uom_code
and upper(UOM_CLASS) = 'TIME';
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
CSI_GROUPING_PKG.Insert_Row(
px_COUNTER_GROUP_ID => l_counter_group_id
,p_NAME => l_name
,p_DESCRIPTION => l_description
,p_TEMPLATE_FLAG => l_template_flag
,p_CP_SERVICE_ID => NULL
,p_CUSTOMER_PRODUCT_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_START_DATE_ACTIVE => l_start_date_active
,p_END_DATE_ACTIVE => l_end_date_active
,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_CONTEXT => l_context
,p_OBJECT_VERSION_NUMBER => 1
,p_CREATED_FROM_CTR_GRP_TMPL_ID => l_created_from_ctr_grp_tmpl_id
,p_ASSOCIATION_TYPE => l_association_type
,p_SOURCE_OBJECT_CODE => l_source_object_code
,p_SOURCE_OBJECT_ID => l_source_object_id
,p_SOURCE_COUNTER_GROUP_ID => l_source_counter_group_id
,p_SECURITY_GROUP_ID => l_security_group_id
);
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
select start_date_active
from cs_csi_counter_groups
where counter_group_id = p_group_id;
select start_date_active
from csi_counter_template_b
where counter_id = p_counter_id;
SELECT distinct nvl(eam_item_type,0) eam_item_type
INTO l_eam_item_type
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id;
SELECT 'x'
INTO l_item_invalid
FROM csi_ctr_item_associations
WHERE inventory_item_id = l_inventory_item_id
AND nvl(associated_to_group,'N') = 'Y';
SELECT 'x'
INTO l_item_invalid
FROM csi_ctr_item_associations
WHERE inventory_item_id = l_inventory_item_id
AND associated_to_group = l_associated_to_group
-- AND group_id = l_group_id
AND counter_id = l_counter_id;
SELECT 'Y'
INTO l_item_found
FROM csi_ctr_item_associations
WHERE inventory_item_id = l_inventory_item_id
AND associated_to_group = 'Y';
SELECT 'x'
INTO l_item_invalid
FROM csi_ctr_item_associations
WHERE inventory_item_id = l_inventory_item_id
AND associated_to_group = l_associated_to_group
AND group_id IS NULL
AND counter_id = l_counter_id;
CSI_CTR_ITEM_ASSOCIATIONS_PKG.Insert_Row(
px_CTR_ASSOCIATION_ID => l_ctr_association_id
,p_GROUP_ID => l_group_id
,p_INVENTORY_ITEM_ID => l_inventory_item_id
,p_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 => sysdate
,p_CREATED_BY => 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_SECURITY_GROUP_ID => l_security_group_id
,p_MIGRATED_FLAG => l_migrated_flag
,p_COUNTER_ID => l_counter_id
,p_START_DATE_ACTIVE => l_start_date_active
,p_END_DATE_ACTIVE => l_end_date_active
,p_USAGE_RATE => l_usage_rate
-- ,p_ASSOCIATION_TYPE => l_association_type
,p_USE_PAST_READING => l_use_past_reading
,p_ASSOCIATED_TO_GROUP => l_associated_to_group
,p_MAINT_ORGANIZATION_ID => NULL
,p_PRIMARY_FAILURE_FLAG => l_primary_failure_flag
);
select start_date_active
from cs_csi_counter_groups
where counter_group_id = p_group_id;
CSI_COUNTER_TEMPLATE_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 => 'Y'
,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 => 1
,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 => l_security_group_id
,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 => l_migrated_flag
,p_CUSTOMER_VIEW => l_customer_view
,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_ASSOCIATION_TYPE => l_association_type
,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_STEP_VALUE => l_step_value
,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
);
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
CSI_CTR_PROPERTY_TEMPLATE_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_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 => l_migrated_flag
,p_PROPERTY_LOV_TYPE => l_property_lov_type
,p_SECURITY_GROUP_ID => l_security_group_id
,p_NAME => l_name
,p_DESCRIPTION => l_description
);
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
SELECT max(value_timestamp)
FROM csi_counter_readings
WHERE counter_id = p_counter_id;
SELECT 'N'
INTO l_valid_flag
FROM csi_counter_relationships
WHERE relationship_type_code = 'CONFIGURATION'
AND source_counter_id = l_object_counter_id;
CSI_COUNTER_RELATIONSHIP_PKG.Insert_Row(
px_RELATIONSHIP_ID => l_relationship_id
,p_CTR_ASSOCIATION_ID => l_ctr_association_id
,p_RELATIONSHIP_TYPE_CODE => l_relationship_type_code
,p_SOURCE_COUNTER_ID => l_source_counter_id
,p_OBJECT_COUNTER_ID => l_object_counter_id
,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_ATTRIBUTE_CATEGORY => l_attribute_category
,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_SECURITY_GROUP_ID => l_security_group_id
,p_MIGRATED_FLAG => l_migrated_flag
,p_BIND_VARIABLE_NAME => l_bind_variable_name
,p_FACTOR => l_factor);
SELECT nvl(type, 'REGULAR'), name
into l_type, l_name
FROM csi_counters_bc_v
WHERE counter_id = l_ctr_derived_filters_rec.counter_id;
SELECT 'x'
into l_dummy
FROM csi_ctr_properties_bc_v
WHERE counter_property_id = l_ctr_derived_filters_rec.counter_property_id;
SELECT nvl(max(seq_no), 0) + 1
into l_seq_no
FROM CSI_COUNTER_DERIVED_FILTERS
WHERE counter_id = l_ctr_derived_filters_rec.COUNTER_ID;
SELECT 'x'
into l_dummy
FROM CSI_COUNTER_DERIVED_FILTERS
WHERE counter_id = l_ctr_derived_filters_rec.COUNTER_ID
AND seq_no = l_ctr_derived_filters_rec.SEQ_NO;
CSI_CTR_DERIVED_FILTERS_PKG.Insert_Row
(
l_ctr_derived_filters_rec.COUNTER_DERIVED_FILTER_ID
,l_ctr_derived_filters_rec.COUNTER_ID
,l_seq_no
,l_ctr_derived_filters_rec.LEFT_PARENT
,l_ctr_derived_filters_rec.COUNTER_PROPERTY_ID
,l_ctr_derived_filters_rec.RELATIONAL_OPERATOR
,l_ctr_derived_filters_rec.RIGHT_VALUE
,l_ctr_derived_filters_rec.RIGHT_PARENT
,l_ctr_derived_filters_rec.LOGICAL_OPERATOR
,l_ctr_derived_filters_rec.START_DATE_ACTIVE
,l_ctr_derived_filters_rec.END_DATE_ACTIVE
,1
,sysdate
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.USER_ID
,l_ctr_derived_filters_rec.ATTRIBUTE1
,l_ctr_derived_filters_rec.ATTRIBUTE2
,l_ctr_derived_filters_rec.ATTRIBUTE3
,l_ctr_derived_filters_rec.ATTRIBUTE4
,l_ctr_derived_filters_rec.ATTRIBUTE5
,l_ctr_derived_filters_rec.ATTRIBUTE6
,l_ctr_derived_filters_rec.ATTRIBUTE7
,l_ctr_derived_filters_rec.ATTRIBUTE8
,l_ctr_derived_filters_rec.ATTRIBUTE9
,l_ctr_derived_filters_rec.ATTRIBUTE10
,l_ctr_derived_filters_rec.ATTRIBUTE11
,l_ctr_derived_filters_rec.ATTRIBUTE12
,l_ctr_derived_filters_rec.ATTRIBUTE13
,l_ctr_derived_filters_rec.ATTRIBUTE14
,l_ctr_derived_filters_rec.ATTRIBUTE15
,l_ctr_derived_filters_rec.ATTRIBUTE_CATEGORY
,l_ctr_derived_filters_rec.SECURITY_GROUP_ID
,l_ctr_derived_filters_rec.MIGRATED_FLAG
);
UPDATE csi_counters_b
SET valid_flag = decode(l_valid_flag, 'Y', 'Y', 'N')
WHERE counter_id = l_ctr_derived_filters_rec.COUNTER_ID;
PROCEDURE update_counter_group
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_counter_groups_rec IN OUT NOCOPY CSI_CTR_DATASTRUCTURES_PUB.counter_groups_rec
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR cur_group_rec(p_counter_group_id IN NUMBER) IS
SELECT name
,description
,template_flag
,cp_service_id
,customer_product_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,start_date_active
,end_date_active
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,context
,object_version_number
,created_from_ctr_grp_tmpl_id
,association_type
,source_object_code
,source_object_id
,source_counter_group_id
,security_group_id
FROM cs_csi_counter_groups
WHERE counter_group_id = p_counter_group_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COUNTER_GROUP';
SAVEPOINT update_counter_group_pvt;
( 'update_counter_group_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) );
CSI_GROUPING_PKG.Update_Row(
p_COUNTER_GROUP_ID => p_counter_groups_rec.counter_group_id
,p_NAME => p_counter_groups_rec.name
,p_DESCRIPTION => p_counter_groups_rec.description
,p_TEMPLATE_FLAG => p_counter_groups_rec.template_flag
,p_CP_SERVICE_ID => NULL
,p_CUSTOMER_PRODUCT_ID => NULL
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_CREATION_DATE => p_counter_groups_rec.creation_date
,p_CREATED_BY => p_counter_groups_rec.created_by
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
,p_START_DATE_ACTIVE => p_counter_groups_rec.start_date_active
,p_END_DATE_ACTIVE => p_counter_groups_rec.end_date_active
,p_ATTRIBUTE1 => p_counter_groups_rec.attribute1
,p_ATTRIBUTE2 => p_counter_groups_rec.attribute2
,p_ATTRIBUTE3 => p_counter_groups_rec.attribute3
,p_ATTRIBUTE4 => p_counter_groups_rec.attribute4
,p_ATTRIBUTE5 => p_counter_groups_rec.attribute5
,p_ATTRIBUTE6 => p_counter_groups_rec.attribute6
,p_ATTRIBUTE7 => p_counter_groups_rec.attribute7
,p_ATTRIBUTE8 => p_counter_groups_rec.attribute8
,p_ATTRIBUTE9 => p_counter_groups_rec.attribute9
,p_ATTRIBUTE10 => p_counter_groups_rec.attribute10
,p_ATTRIBUTE11 => p_counter_groups_rec.attribute11
,p_ATTRIBUTE12 => p_counter_groups_rec.attribute12
,p_ATTRIBUTE13 => p_counter_groups_rec.attribute13
,p_ATTRIBUTE14 => p_counter_groups_rec.attribute14
,p_ATTRIBUTE15 => p_counter_groups_rec.attribute15
,p_CONTEXT => p_counter_groups_rec.context
,p_OBJECT_VERSION_NUMBER => p_counter_groups_rec.object_version_number + 1
,p_CREATED_FROM_CTR_GRP_TMPL_ID => p_counter_groups_rec.created_from_ctr_grp_tmpl_id
,p_ASSOCIATION_TYPE => p_counter_groups_rec.association_type
,p_SOURCE_OBJECT_CODE => p_counter_groups_rec.source_object_code
,p_SOURCE_OBJECT_ID => p_counter_groups_rec.source_object_id
,p_SOURCE_COUNTER_GROUP_ID => p_counter_groups_rec.source_counter_group_id
,p_SECURITY_GROUP_ID => p_counter_groups_rec.security_group_id
);
ROLLBACK TO update_counter_group_pvt;
ROLLBACK TO update_counter_group_pvt;
ROLLBACK TO update_counter_group_pvt;
ROLLBACK TO update_counter_group_pvt;
END update_counter_group;
PROCEDURE update_item_association
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_ctr_item_associations_rec IN OUT NOCOPY CSI_CTR_DATASTRUCTURES_PUB.ctr_item_associations_rec
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR cur_item_assoc_rec(p_ctr_association_id IN NUMBER) IS
SELECT group_id
,inventory_item_id
,object_version_number
,last_update_date
,last_updated_by
,last_update_login
,creation_date
,created_by
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
,security_group_id
,migrated_flag
,counter_id
,start_date_active
,end_date_active
,usage_rate
-- ,association_type
,use_past_reading
,associated_to_group
,maint_organization_id
,primary_failure_flag
FROM csi_ctr_item_associations_v
WHERE ctr_association_id = p_ctr_association_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ITEM_ASSOCIATION';
SAVEPOINT update_item_association_pvt;
csi_ctr_gen_utility_pvt.put_line( 'update_item_association_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 'x'
INTO l_item_invalid
FROM csi_ctr_item_associations
WHERE inventory_item_id = l_item_associations_rec.inventory_item_id;
SELECT 'x'
INTO l_item_invalid
FROM csi_ctr_item_associations
WHERE inventory_item_id = l_item_associations_rec.inventory_item_id
AND associated_to_group = l_item_associations_rec.associated_to_group
-- AND group_id = l_group_id
AND counter_id = l_item_associations_rec.counter_id;
SELECT 'Y'
INTO l_item_found
FROM csi_ctr_item_associations
WHERE inventory_item_id = l_item_associations_rec.inventory_item_id
AND associated_to_group = 'Y';
SELECT 'x'
INTO l_item_invalid
FROM csi_ctr_item_associations
WHERE inventory_item_id = l_item_associations_rec.inventory_item_id
AND associated_to_group = l_item_associations_rec.associated_to_group
AND group_id IS NULL
AND counter_id = l_item_associations_rec.counter_id;
CSI_CTR_ITEM_ASSOCIATIONS_PKG.Update_Row(
p_CTR_ASSOCIATION_ID => p_ctr_item_associations_rec.ctr_association_id
,p_GROUP_ID => p_ctr_item_associations_rec.group_id
,p_INVENTORY_ITEM_ID => p_ctr_item_associations_rec.inventory_item_id
,p_OBJECT_VERSION_NUMBER => p_ctr_item_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_ctr_item_associations_rec.creation_date
,p_CREATED_BY => p_ctr_item_associations_rec.created_by
,p_ATTRIBUTE1 => p_ctr_item_associations_rec.attribute1
,p_ATTRIBUTE2 => p_ctr_item_associations_rec.attribute2
,p_ATTRIBUTE3 => p_ctr_item_associations_rec.attribute3
,p_ATTRIBUTE4 => p_ctr_item_associations_rec.attribute4
,p_ATTRIBUTE5 => p_ctr_item_associations_rec.attribute5
,p_ATTRIBUTE6 => p_ctr_item_associations_rec.attribute6
,p_ATTRIBUTE7 => p_ctr_item_associations_rec.attribute7
,p_ATTRIBUTE8 => p_ctr_item_associations_rec.attribute8
,p_ATTRIBUTE9 => p_ctr_item_associations_rec.attribute9
,p_ATTRIBUTE10 => p_ctr_item_associations_rec.attribute10
,p_ATTRIBUTE11 => p_ctr_item_associations_rec.attribute11
,p_ATTRIBUTE12 => p_ctr_item_associations_rec.attribute12
,p_ATTRIBUTE13 => p_ctr_item_associations_rec.attribute13
,p_ATTRIBUTE14 => p_ctr_item_associations_rec.attribute14
,p_ATTRIBUTE15 => p_ctr_item_associations_rec.attribute15
,p_ATTRIBUTE_CATEGORY => p_ctr_item_associations_rec.attribute_category
,p_SECURITY_GROUP_ID => p_ctr_item_associations_rec.security_group_id
,p_MIGRATED_FLAG => p_ctr_item_associations_rec.migrated_flag
,p_COUNTER_ID => p_ctr_item_associations_rec.counter_id
,p_START_DATE_ACTIVE => p_ctr_item_associations_rec.start_date_active
,p_END_DATE_ACTIVE => p_ctr_item_associations_rec.end_date_active
,p_USAGE_RATE => p_ctr_item_associations_rec.usage_rate
-- ,p_ASSOCIATION_TYPE => p_ctr_item_associations_rec.association_type
,p_USE_PAST_READING => p_ctr_item_associations_rec.use_past_reading
,p_ASSOCIATED_TO_GROUP => p_ctr_item_associations_rec.associated_to_group
,p_MAINT_ORGANIZATION_ID => NULL
,p_PRIMARY_FAILURE_FLAG => p_ctr_item_associations_rec.primary_failure_flag
);
ROLLBACK TO update_item_association_pvt;
ROLLBACK TO update_item_association_pvt;
ROLLBACK TO update_item_association_pvt;
ROLLBACK TO update_item_association_pvt;
END update_item_association;
PROCEDURE update_counter_template
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_counter_template_rec IN OUT NOCOPY CSI_CTR_DATASTRUCTURES_PUB.counter_template_rec
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR cur_ctr_template_rec(p_counter_id IN NUMBER) IS
SELECT counter_id,
group_id,
counter_type,
initial_reading,
initial_reading_date,
step_value,
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,
reading_type,
automatic_rollover,
default_usage_rate,
use_past_reading,
used_in_scheduling,
defaulted_group_id,
object_version_number,
comments,
association_type,
time_based_manual_entry,
eam_required_flag
FROM csi_counter_template_vl
WHERE counter_id = p_counter_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
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;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COUNTER_TEMPLATE';
SAVEPOINT update_counter_template_pvt;
csi_ctr_gen_utility_pvt.put_line( 'update_counter_template_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) );
csi_ctr_gen_utility_pvt.put_line('used in scheduling cannot be updated');
SELECT name
INTO l_counter_template_rec.name
FROM csi_counter_template_tl
WHERE counter_id = l_old_counter_template_rec.counter_id;
CSI_COUNTER_TEMPLATE_PKG.update_row
(
p_counter_id => p_counter_template_rec.counter_id
,p_group_id => p_counter_template_rec.group_id
,p_counter_type => p_counter_template_rec.counter_type
,p_initial_reading => p_counter_template_rec.initial_reading
,p_initial_reading_date => p_counter_template_rec.initial_reading_date
,p_tolerance_plus => p_counter_template_rec.tolerance_plus
,p_tolerance_minus => p_counter_template_rec.tolerance_minus
,p_uom_code => p_counter_template_rec.uom_code
,p_derive_counter_id => p_counter_template_rec.derive_counter_id
,p_derive_function => p_counter_template_rec.derive_function
,p_derive_property_id => p_counter_template_rec.derive_property_id
,p_valid_flag => p_counter_template_rec.valid_flag
,p_formula_incomplete_flag => p_counter_template_rec.formula_incomplete_flag
,p_formula_text => p_counter_template_rec.formula_text
,p_rollover_last_reading => p_counter_template_rec.rollover_last_reading
,p_rollover_first_reading => p_counter_template_rec.rollover_first_reading
,p_usage_item_id => p_counter_template_rec.usage_item_id
,p_ctr_val_max_seq_no => nvl(p_counter_template_rec.ctr_val_max_seq_no,1)
,p_start_date_active => p_counter_template_rec.start_date_active
,p_end_date_active => p_counter_template_rec.end_date_active
,p_object_version_number => p_counter_template_rec.object_version_number
,p_last_update_date => sysdate
,p_last_updated_by => FND_GLOBAL.USER_ID
,p_creation_date => p_counter_template_rec.creation_date
,p_created_by => p_counter_template_rec.created_by
,p_last_update_login => FND_GLOBAL.USER_ID
,p_attribute1 => p_counter_template_rec.attribute1
,p_attribute2 => p_counter_template_rec.attribute2
,p_attribute3 => p_counter_template_rec.attribute3
,p_attribute4 => p_counter_template_rec.attribute4
,p_attribute5 => p_counter_template_rec.attribute5
,p_attribute6 => p_counter_template_rec.attribute6
,p_attribute7 => p_counter_template_rec.attribute7
,p_attribute8 => p_counter_template_rec.attribute8
,p_attribute9 => p_counter_template_rec.attribute9
,p_attribute10 => p_counter_template_rec.attribute10
,p_attribute11 => p_counter_template_rec.attribute11
,p_attribute12 => p_counter_template_rec.attribute12
,p_attribute13 => p_counter_template_rec.attribute13
,p_attribute14 => p_counter_template_rec.attribute14
,p_attribute15 => p_counter_template_rec.attribute15
,p_attribute16 => p_counter_template_rec.attribute16
,p_attribute17 => p_counter_template_rec.attribute17
,p_attribute18 => p_counter_template_rec.attribute18
,p_attribute19 => p_counter_template_rec.attribute19
,p_attribute20 => p_counter_template_rec.attribute20
,p_attribute21 => p_counter_template_rec.attribute21
,p_attribute22 => p_counter_template_rec.attribute22
,p_attribute23 => p_counter_template_rec.attribute23
,p_attribute24 => p_counter_template_rec.attribute24
,p_attribute25 => p_counter_template_rec.attribute25
,p_attribute26 => p_counter_template_rec.attribute26
,p_attribute27 => p_counter_template_rec.attribute27
,p_attribute28 => p_counter_template_rec.attribute28
,p_attribute29 => p_counter_template_rec.attribute29
,p_attribute30 => p_counter_template_rec.attribute30
,p_attribute_category => p_counter_template_rec.attribute_category
,p_migrated_flag => null
,p_customer_view => p_counter_template_rec.customer_view
,p_direction => p_counter_template_rec.direction
,p_filter_type => p_counter_template_rec.filter_type
,p_filter_reading_count => p_counter_template_rec.filter_reading_count
,p_filter_time_uom => p_counter_template_rec.filter_time_uom
,p_estimation_id => p_counter_template_rec.estimation_id
,p_association_type => p_counter_template_rec.association_type
,p_reading_type => p_counter_template_rec.reading_type
,p_automatic_rollover => p_counter_template_rec.automatic_rollover
,p_default_usage_rate => p_counter_template_rec.default_usage_rate
,p_use_past_reading => p_counter_template_rec.use_past_reading
,p_used_in_scheduling => p_counter_template_rec.used_in_scheduling
,p_defaulted_group_id => p_counter_template_rec.defaulted_group_id
,p_SECURITY_GROUP_ID => p_counter_template_rec.step_value
,p_STEP_VALUE => p_counter_template_rec.step_value
,p_name => p_counter_template_rec.name
,p_description => p_counter_template_rec.description
,p_time_based_manual_entry => p_counter_template_rec.time_based_manual_entry
,p_eam_required_flag => p_counter_template_rec.eam_required_flag
);
ROLLBACK TO update_counter_template_pvt;
ROLLBACK TO update_counter_template_pvt;
ROLLBACK TO update_counter_template_pvt;
ROLLBACK TO update_counter_template_pvt;
END update_counter_template;
PROCEDURE update_ctr_property_template
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_ctr_property_template_rec IN OUT NOCOPY CSI_CTR_DATASTRUCTURES_PUB.ctr_property_template_rec
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR cur_ctr_prop_rec(p_counter_property_id IN NUMBER) IS
SELECT name
,description
,counter_id
,property_data_type
,is_nullable
,default_value
,minimum_value
,maximum_value
,uom_code
,start_date_active
,end_date_active
,object_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
,migrated_flag
,property_lov_type
,security_group_id
FROM csi_ctr_prop_template_vl
WHERE counter_property_id = p_counter_property_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CTR_PROPERTY_TEMPLATE';
SAVEPOINT update_ctr_property_tmpl_pvt;
csi_ctr_gen_utility_pvt.put_line( 'update_ctr_property_tmpl_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) );
CSI_CTR_PROPERTY_TEMPLATE_PKG.update_Row(
p_COUNTER_PROPERTY_ID => p_ctr_property_template_rec.counter_property_id
,p_COUNTER_ID => p_ctr_property_template_rec.counter_id
,p_PROPERTY_DATA_TYPE => p_ctr_property_template_rec.property_data_type
,p_IS_NULLABLE => p_ctr_property_template_rec.is_nullable
,p_DEFAULT_VALUE => p_ctr_property_template_rec.default_value
,p_MINIMUM_VALUE => p_ctr_property_template_rec.minimum_value
,p_MAXIMUM_VALUE => p_ctr_property_template_rec.maximum_value
,p_UOM_CODE => p_ctr_property_template_rec.uom_code
,p_START_DATE_ACTIVE => p_ctr_property_template_rec.start_date_active
,p_END_DATE_ACTIVE => p_ctr_property_template_rec.end_date_active
,p_OBJECT_VERSION_NUMBER => p_ctr_property_template_rec.object_version_number + 1
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_CREATION_DATE => p_ctr_property_template_rec.creation_date
,p_CREATED_BY => p_ctr_property_template_rec.created_by
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
,p_ATTRIBUTE1 => p_ctr_property_template_rec.attribute1
,p_ATTRIBUTE2 => p_ctr_property_template_rec.attribute2
,p_ATTRIBUTE3 => p_ctr_property_template_rec.attribute3
,p_ATTRIBUTE4 => p_ctr_property_template_rec.attribute4
,p_ATTRIBUTE5 => p_ctr_property_template_rec.attribute5
,p_ATTRIBUTE6 => p_ctr_property_template_rec.attribute6
,p_ATTRIBUTE7 => p_ctr_property_template_rec.attribute7
,p_ATTRIBUTE8 => p_ctr_property_template_rec.attribute8
,p_ATTRIBUTE9 => p_ctr_property_template_rec.attribute9
,p_ATTRIBUTE10 => p_ctr_property_template_rec.attribute10
,p_ATTRIBUTE11 => p_ctr_property_template_rec.attribute11
,p_ATTRIBUTE12 => p_ctr_property_template_rec.attribute12
,p_ATTRIBUTE13 => p_ctr_property_template_rec.attribute13
,p_ATTRIBUTE14 => p_ctr_property_template_rec.attribute14
,p_ATTRIBUTE15 => p_ctr_property_template_rec.attribute15
,p_ATTRIBUTE_CATEGORY => p_ctr_property_template_rec.attribute_category
,p_MIGRATED_FLAG => p_ctr_property_template_rec.migrated_flag
,p_PROPERTY_LOV_TYPE => p_ctr_property_template_rec.property_lov_type
,p_SECURITY_GROUP_ID => p_ctr_property_template_rec.security_group_id
,p_NAME => p_ctr_property_template_rec.name
,p_DESCRIPTION => p_ctr_property_template_rec.description
);
ROLLBACK TO update_ctr_property_tmpl_pvt;
ROLLBACK TO update_ctr_property_tmpl_pvt;
ROLLBACK TO update_ctr_property_tmpl_pvt;
ROLLBACK TO update_ctr_property_tmpl_pvt;
END update_ctr_property_template;
PROCEDURE update_counter_relationship
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_counter_relationships_rec IN OUT NOCOPY CSI_CTR_DATASTRUCTURES_PUB.counter_relationships_rec
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR cur_rel_rec(p_relationship_id IN NUMBER) IS
SELECT ctr_association_id
,relationship_type_code
,source_counter_id
,object_counter_id
,active_start_date
,active_end_date
,object_version_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,security_group_id
,migrated_flag
,bind_variable_name
,factor
FROM csi_ctr_relationships_v
WHERE relationship_id = p_relationship_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_COUNTER_RELATIONSHIP';
SELECT max(value_timestamp)
FROM csi_counter_readings
WHERE counter_id = p_counter_id;
SAVEPOINT update_ctr_relationship_pvt;
csi_ctr_gen_utility_pvt.put_line( 'update_ctr_relationship_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 'N'
INTO l_valid_flag
FROM csi_counter_relationships
WHERE relationship_type_code = 'CONFIGURATION'
AND source_counter_id = l_ctr_relationships_rec.object_counter_id;
CSI_COUNTER_RELATIONSHIP_PKG.update_Row(
p_RELATIONSHIP_ID => p_counter_relationships_rec.relationship_id
,p_CTR_ASSOCIATION_ID => p_counter_relationships_rec.ctr_association_id
,p_RELATIONSHIP_TYPE_CODE => p_counter_relationships_rec.relationship_type_code
,p_SOURCE_COUNTER_ID => p_counter_relationships_rec.source_counter_id
,p_OBJECT_COUNTER_ID => p_counter_relationships_rec.object_counter_id
,p_ACTIVE_START_DATE => p_counter_relationships_rec.active_start_date
,p_ACTIVE_END_DATE => p_counter_relationships_rec.active_end_date
,p_OBJECT_VERSION_NUMBER => p_counter_relationships_rec.object_version_number + 1
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_CREATION_DATE => p_counter_relationships_rec.creation_date
,p_CREATED_BY => p_counter_relationships_rec.created_by
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
,p_ATTRIBUTE_CATEGORY => p_counter_relationships_rec.attribute_category
,p_ATTRIBUTE1 => p_counter_relationships_rec.attribute1
,p_ATTRIBUTE2 => p_counter_relationships_rec.attribute2
,p_ATTRIBUTE3 => p_counter_relationships_rec.attribute3
,p_ATTRIBUTE4 => p_counter_relationships_rec.attribute4
,p_ATTRIBUTE5 => p_counter_relationships_rec.attribute5
,p_ATTRIBUTE6 => p_counter_relationships_rec.attribute6
,p_ATTRIBUTE7 => p_counter_relationships_rec.attribute7
,p_ATTRIBUTE8 => p_counter_relationships_rec.attribute8
,p_ATTRIBUTE9 => p_counter_relationships_rec.attribute9
,p_ATTRIBUTE10 => p_counter_relationships_rec.attribute10
,p_ATTRIBUTE11 => p_counter_relationships_rec.attribute11
,p_ATTRIBUTE12 => p_counter_relationships_rec.attribute12
,p_ATTRIBUTE13 => p_counter_relationships_rec.attribute13
,p_ATTRIBUTE14 => p_counter_relationships_rec.attribute14
,p_ATTRIBUTE15 => p_counter_relationships_rec.attribute15
,p_SECURITY_GROUP_ID => p_counter_relationships_rec.security_group_id
,p_MIGRATED_FLAG => p_counter_relationships_rec.migrated_flag
,p_BIND_VARIABLE_NAME => p_counter_relationships_rec.bind_variable_name
,p_FACTOR => p_counter_relationships_rec.factor);
ROLLBACK TO update_ctr_relationship_pvt;
ROLLBACK TO update_ctr_relationship_pvt;
ROLLBACK TO update_ctr_relationship_pvt;
ROLLBACK TO update_ctr_relationship_pvt;
END update_counter_relationship;
PROCEDURE update_derived_filters
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_ctr_derived_filters_tbl IN OUT NOCOPY CSI_CTR_DATASTRUCTURES_PUB.ctr_derived_filters_tbl
,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_DERIVED_FILTERS';
SAVEPOINT update_derived_filters;
csi_ctr_gen_utility_pvt.put_line( 'update_derived_filters');
csi_ctr_gen_utility_pvt.put_line( 'update_derived_filters' ||
p_api_version ||'-'||
p_commit ||'-'||
p_init_msg_list ||'-'||
p_validation_level );
SELECT counter_id,
counter_property_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category
INTO l_old_ctr_derived_filters_rec.counter_id,
l_old_ctr_derived_filters_rec.counter_property_id,
l_old_ctr_derived_filters_rec.attribute1,
l_old_ctr_derived_filters_rec.attribute2,
l_old_ctr_derived_filters_rec.attribute3,
l_old_ctr_derived_filters_rec.attribute4,
l_old_ctr_derived_filters_rec.attribute5,
l_old_ctr_derived_filters_rec.attribute6,
l_old_ctr_derived_filters_rec.attribute7,
l_old_ctr_derived_filters_rec.attribute8,
l_old_ctr_derived_filters_rec.attribute9,
l_old_ctr_derived_filters_rec.attribute10,
l_old_ctr_derived_filters_rec.attribute11,
l_old_ctr_derived_filters_rec.attribute12,
l_old_ctr_derived_filters_rec.attribute13,
l_old_ctr_derived_filters_rec.attribute14,
l_old_ctr_derived_filters_rec.attribute15,
l_old_ctr_derived_filters_rec.attribute_category
FROM CSI_COUNTER_DERIVED_FILTERS
-- WHERE COUNTER_DERIVED_FILTER_ID = l_ctr_derived_filters_rec.COUNTER_DERIVED_FILTER_ID;
SELECT NVL(type, 'REGULAR'), name
INTO l_type, l_name
FROM csi_counters_bc_v
WHERE counter_id = p_ctr_derived_filters_tbl(tab_row).counter_id;
SELECT 'x'
INTO l_dummy
FROM csi_ctr_properties_bc_v
WHERE counter_property_id = l_ctr_derived_filters_rec.counter_property_id;
SELECT 'x'
INTO l_dummy
FROM CSI_COUNTER_DERIVED_FILTERS
WHERE counter_id = p_ctr_derived_filters_tbl(tab_row).counter_id
AND seq_no = p_ctr_derived_filters_tbl(tab_row).seq_no
AND counter_derived_filter_id <> p_ctr_derived_filters_tbl(tab_row).counter_derived_filter_id;
CSI_CTR_DERIVED_FILTERS_PKG.Update_Row
(
p_ctr_derived_filters_tbl(tab_row).COUNTER_DERIVED_FILTER_ID
,p_ctr_derived_filters_tbl(tab_row).COUNTER_ID
,p_ctr_derived_filters_tbl(tab_row).SEQ_NO
,p_ctr_derived_filters_tbl(tab_row).LEFT_PARENT
,p_ctr_derived_filters_tbl(tab_row).COUNTER_PROPERTY_ID
,p_ctr_derived_filters_tbl(tab_row).RELATIONAL_OPERATOR
,p_ctr_derived_filters_tbl(tab_row).RIGHT_VALUE
,p_ctr_derived_filters_tbl(tab_row).RIGHT_PARENT
,p_ctr_derived_filters_tbl(tab_row).LOGICAL_OPERATOR
,p_ctr_derived_filters_tbl(tab_row).START_DATE_ACTIVE
,p_ctr_derived_filters_tbl(tab_row).END_DATE_ACTIVE
,p_ctr_derived_filters_tbl(tab_row).OBJECT_VERSION_NUMBER +1
,sysdate
,FND_GLOBAL.USER_ID
,p_ctr_derived_filters_tbl(tab_row).CREATION_DATE
,p_ctr_derived_filters_tbl(tab_row).CREATED_BY
,FND_GLOBAL.USER_ID
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE1
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE2
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE3
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE4
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE5
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE6
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE7
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE8
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE9
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE10
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE11
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE12
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE13
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE14
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE15
,p_ctr_derived_filters_tbl(tab_row).ATTRIBUTE_CATEGORY
,p_ctr_derived_filters_tbl(tab_row).SECURITY_GROUP_ID
,p_ctr_derived_filters_tbl(tab_row).MIGRATED_FLAG
);
SELECT 'x'
INTO l_found
FROM csi_counter_template_vl
WHERE counter_id = p_ctr_derived_filters_tbl(tab_row).COUNTER_ID;
UPDATE csi_counter_template_b
SET valid_flag = decode(l_valid_flag, 'Y', 'Y', 'N')
WHERE counter_id = p_ctr_derived_filters_tbl(tab_row).COUNTER_ID;
UPDATE csi_counters_b
SET valid_flag = decode(l_valid_flag, 'Y', 'Y', 'N')
WHERE counter_id = p_ctr_derived_filters_tbl(tab_row).COUNTER_ID;
ROLLBACK TO update_derived_filters;
ROLLBACK TO update_derived_filters;
ROLLBACK TO update_derived_filters;
END update_derived_filters;
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
SELECT 'X'
INTO l_dummy
FROM csi_ctr_estimate_methods_vl
WHERE name = l_name;
CSI_CTR_ESTIMATE_METHODS_PKG.Insert_Row(
px_ESTIMATION_ID => l_estimation_id
,p_ESTIMATION_TYPE => l_estimation_type
,p_FIXED_VALUE => l_fixed_value
,p_USAGE_MARKUP => l_usage_markup
,p_DEFAULT_VALUE => l_default_value
,p_ESTIMATION_AVG_TYPE => l_estimation_avg_type
,p_START_DATE_ACTIVE => l_start_date_active
,p_END_DATE_ACTIVE => l_end_date_active
,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_OBJECT_VERSION_NUMBER => 1
,p_MIGRATED_FLAG => l_migrated_flag
,p_NAME => l_name
,p_DESCRIPTION => l_description
);
PROCEDURE Update_Estimation_Method
(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,p_validation_level IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_ctr_estimation_rec IN CSI_CTR_DATASTRUCTURES_PUB.ctr_estimation_methods_rec
) IS
CURSOR cur_estimation_rec(p_estimation_id IN NUMBER) IS
SELECT name
,description
,estimation_type
,fixed_value
,usage_markup
,default_value
,estimation_avg_type
,start_date_active
,end_date_active
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category
,object_version_number
,migrated_flag
FROM csi_ctr_estimate_methods_vl
WHERE estimation_id = p_estimation_id
FOR UPDATE OF OBJECT_VERSION_NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ESTIMATION_METHOD';
SAVEPOINT update_estimation_method_pvt;
csi_ctr_gen_utility_pvt.put_line( 'update_estimation_method_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) );
CSI_CTR_ESTIMATE_METHODS_PKG.update_Row(
p_ESTIMATION_ID => p_ctr_estimation_rec.estimation_id
,p_ESTIMATION_TYPE => p_ctr_estimation_rec.estimation_type
,p_FIXED_VALUE => p_ctr_estimation_rec.fixed_value
,p_USAGE_MARKUP => p_ctr_estimation_rec.usage_markup
,p_DEFAULT_VALUE => p_ctr_estimation_rec.default_value
,p_ESTIMATION_AVG_TYPE => p_ctr_estimation_rec.estimation_avg_type
,p_START_DATE_ACTIVE => p_ctr_estimation_rec.start_date_active
,p_END_DATE_ACTIVE => p_ctr_estimation_rec.end_date_active
,p_LAST_UPDATE_DATE => sysdate
,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,p_CREATION_DATE => p_ctr_estimation_rec.creation_date
,p_CREATED_BY => p_ctr_estimation_rec.created_by
,p_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
,p_ATTRIBUTE1 => p_ctr_estimation_rec.attribute1
,p_ATTRIBUTE2 => p_ctr_estimation_rec.attribute2
,p_ATTRIBUTE3 => p_ctr_estimation_rec.attribute3
,p_ATTRIBUTE4 => p_ctr_estimation_rec.attribute4
,p_ATTRIBUTE5 => p_ctr_estimation_rec.attribute5
,p_ATTRIBUTE6 => p_ctr_estimation_rec.attribute6
,p_ATTRIBUTE7 => p_ctr_estimation_rec.attribute7
,p_ATTRIBUTE8 => p_ctr_estimation_rec.attribute8
,p_ATTRIBUTE9 => p_ctr_estimation_rec.attribute9
,p_ATTRIBUTE10 => p_ctr_estimation_rec.attribute10
,p_ATTRIBUTE11 => p_ctr_estimation_rec.attribute11
,p_ATTRIBUTE12 => p_ctr_estimation_rec.attribute12
,p_ATTRIBUTE13 => p_ctr_estimation_rec.attribute13
,p_ATTRIBUTE14 => p_ctr_estimation_rec.attribute14
,p_ATTRIBUTE15 => p_ctr_estimation_rec.attribute15
,p_ATTRIBUTE_CATEGORY => p_ctr_estimation_rec.attribute_category
,p_OBJECT_VERSION_NUMBER => p_ctr_estimation_rec.object_version_number + 1
,p_MIGRATED_FLAG => p_ctr_estimation_rec.migrated_flag
,p_NAME => p_ctr_estimation_rec.name
,p_DESCRIPTION => p_ctr_estimation_rec.description
);
ROLLBACK TO update_estimation_method_pvt;
ROLLBACK TO update_estimation_method_pvt;
ROLLBACK TO update_estimation_method_pvt;
ROLLBACK TO update_estimation_method_pvt;
END update_estimation_method;
SELECT ccp.counter_property_id,ccp.default_value,ccp.property_data_type, ccp.is_nullable
FROM CSI_COUNTER_PROPERTIES_B ccp
WHERE ccp.counter_id = p_counter_id AND NVL(ccp.is_nullable,'N') = 'N'
AND NVL(end_date_active,(SYSDATE+1)) > SYSDATE;
SELECT counter_id,
name,
description,
counter_type,
initial_reading,
initial_reading_date,
tolerance_plus,
tolerance_minus,
uom_code,
derive_function,
derive_counter_id,
derive_property_id,
valid_flag,
formula_incomplete_flag,
formula_text,
rollover_last_reading,
rollover_first_reading,
comments,
usage_item_id,
ctr_val_max_seq_no,
start_date_active,
end_date_active ,
customer_view,
direction,
filter_reading_count,
filter_type,
filter_time_uom,
estimation_id,
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,
group_id,
defaulted_group_id,
reading_type,
automatic_rollover,
default_usage_rate,
use_past_reading,
used_in_scheduling,
security_group_id,
nvl(time_based_manual_entry, 'N') tm_based_manual_entry,
eam_required_flag
FROM csi_counter_template_vl
WHERE counter_id = p_counter_id;
SELECT counter_id,
name,
description,
counter_type,
initial_reading,
tolerance_plus,
tolerance_minus,
uom_code,
derive_function,
derive_counter_id,
derive_property_id,
valid_flag,
formula_incomplete_flag,
formula_text,
rollover_last_reading,
rollover_first_reading,
comments,
usage_item_id,
ctr_val_max_seq_no,
start_date_active,
end_date_active,
-- created_from_counter_tmpl_id,
customer_view,
filter_reading_count,
filter_type,
filter_time_uom,
estimation_id,
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,
eam_required_flag
FROM csi_counters_vl
WHERE counter_id = p_counter_id
AND counter_type = 'FORMULA';
SELECT counter_property_id,
name,
description,
property_data_type,
is_nullable,
default_value,
minimum_value,
maximum_value,
uom_code,
start_date_active,
end_date_active ,
property_lov_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
security_group_id
FROM csi_ctr_prop_template_vl
WHERE counter_id = p_counter_id;
SELECT relationship_id,
ctr_association_id,
relationship_type_code,
source_counter_id,
object_counter_id,
bind_variable_name,
factor,
active_start_date,
active_end_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
security_group_id
FROM csi_counter_relationships
-- WHERE source_counter_id = p_counter_id;
SELECT counter_id,
seq_no,
left_parent,
counter_property_id,
relational_operator,
right_value,
right_parent,
logical_operator,
start_date_active,
end_date_active,
security_group_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category
FROM csi_counter_derived_filters
WHERE counter_id = p_counter_id;
SELECT max(counter_id)
INTO l_new_derive_counter_id
FROM csi_counters_b
WHERE created_from_counter_tmpl_id = ctr_cur_rec.derive_counter_id;
l_counter_instance_rec.last_update_date := sysdate;
l_counter_instance_rec.last_Updated_by := fnd_global.user_id;
l_counter_instance_rec.last_update_login := FND_GLOBAL.user_id;
l_ctr_properties_rec.last_update_date := sysdate;
l_ctr_properties_rec.last_Updated_by := fnd_global.user_id;
l_ctr_properties_rec.last_update_login := FND_GLOBAL.user_id;
SELECT counter_property_id
INTO l_new_der_ctr_prop_id
FROM csi_counter_template_vl ctr,
csi_ctr_prop_template_vl ctrprop
WHERE ctrprop.counter_property_id = ctr_der_filter_cur_rec.counter_property_id
AND ctrprop.counter_id = l_new_ctr_id;
l_ctr_derived_filters_tbl.last_update_date := sysdate;
l_ctr_derived_filters_tbl.last_Updated_by := fnd_global.user_id;
l_ctr_derived_filters_tbl.last_update_login := FND_GLOBAL.user_id;
SELECT max(counter_id)
INTO l_new_mapped_ctr_id
FROM csi_counters_b
WHERE created_from_counter_tmpl_id = ctr_formula_bvars_cur_rec.source_counter_id;
SELECT inventory_item_id
INTO l_mapped_item_id
FROM csi_item_instances
WHERE instance_id = x_relationship_tbl(i).subject_id
AND inv_master_organization_id = l_org_id;
l_counter_relationships_rec.last_update_date := sysdate;
l_counter_relationships_rec.last_Updated_by := fnd_global.user_id;
l_counter_relationships_rec.last_update_login := FND_GLOBAL.user_id;
SELECT counter_id,
name,
description,
counter_type,
initial_reading,
initial_reading_date,
tolerance_plus,
tolerance_minus,
uom_code,
derive_function,
derive_counter_id,
derive_property_id,
valid_flag,
formula_incomplete_flag,
formula_text,
rollover_last_reading,
rollover_first_reading,
comments,
usage_item_id,
ctr_val_max_seq_no,
start_date_active,
end_date_active ,
customer_view,
direction,
filter_reading_count,
filter_type,
filter_time_uom,
estimation_id,
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,
group_id,
defaulted_group_id,
reading_type,
automatic_rollover,
default_usage_rate,
use_past_reading,
used_in_scheduling,
security_group_id,
nvl(time_based_manual_entry, 'N') tm_based_manual_entry,
eam_required_flag
FROM csi_counter_template_vl
WHERE group_id = p_counter_group_id;
SELECT counter_id,
name,
description,
counter_type,
initial_reading,
tolerance_plus,
tolerance_minus,
uom_code,
derive_function,
derive_counter_id,
derive_property_id,
valid_flag,
formula_incomplete_flag,
formula_text,
rollover_last_reading,
rollover_first_reading,
comments,
usage_item_id,
ctr_val_max_seq_no,
start_date_active,
end_date_active,
-- created_from_counter_tmpl_id,
customer_view,
filter_reading_count,
filter_type,
filter_time_uom,
estimation_id,
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,
eam_required_flag
FROM csi_counters_vl
WHERE group_id = p_counter_group_id
AND counter_type = 'FORMULA';
SELECT counter_property_id,
name,
description,
property_data_type,
is_nullable,
default_value,
minimum_value,
maximum_value,
uom_code,
start_date_active,
end_date_active ,
property_lov_type,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
security_group_id
FROM csi_ctr_prop_template_vl
WHERE counter_id = p_counter_id;
SELECT relationship_id,
ctr_association_id,
relationship_type_code,
source_counter_id,
object_counter_id,
bind_variable_name,
factor,
active_start_date,
active_end_date,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
security_group_id
FROM csi_counter_relationships
-- WHERE source_counter_id = p_counter_id;
SELECT counter_id,
seq_no,
left_parent,
counter_property_id,
relational_operator,
right_value,
right_parent,
logical_operator,
start_date_active,
end_date_active,
security_group_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category
FROM csi_counter_derived_filters
WHERE counter_id = p_counter_id;
SELECT name,
description,
association_type,
start_date_active,
end_date_active ,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
context,
'N'
INTO l_ctr_grp_rec.name,
l_ctr_grp_rec.description,
l_ctr_grp_rec.association_type,
l_ctr_grp_rec.start_date_active,
l_ctr_grp_rec.end_date_active ,
l_ctr_grp_rec.attribute1,
l_ctr_grp_rec.attribute2,
l_ctr_grp_rec.attribute3,
l_ctr_grp_rec.attribute4,
l_ctr_grp_rec.attribute5,
l_ctr_grp_rec.attribute6,
l_ctr_grp_rec.attribute7,
l_ctr_grp_rec.attribute8,
l_ctr_grp_rec.attribute9,
l_ctr_grp_rec.attribute10,
l_ctr_grp_rec.attribute11,
l_ctr_grp_rec.attribute12,
l_ctr_grp_rec.attribute13,
l_ctr_grp_rec.attribute14,
l_ctr_grp_rec.attribute15,
l_ctr_grp_rec.context,
l_ctr_grp_rec.template_flag
FROM cs_csi_counter_groups
WHERE counter_group_id = l_counter_group_id;
SELECT max(counter_id)
INTO l_new_derive_counter_id
FROM csi_counters_b
WHERE created_from_counter_tmpl_id = ctr_cur_rec.derive_counter_id;
l_counter_instance_rec.last_update_date := sysdate;
l_counter_instance_rec.last_Updated_by := fnd_global.user_id;
l_counter_instance_rec.last_update_login := FND_GLOBAL.user_id;
l_ctr_properties_rec.last_update_date := sysdate;
l_ctr_properties_rec.last_Updated_by := fnd_global.user_id;
l_ctr_properties_rec.last_update_login := FND_GLOBAL.user_id;
SELECT counter_property_id
INTO l_new_der_ctr_prop_id
FROM csi_counter_template_vl ctr,
csi_ctr_prop_template_vl ctrprop
WHERE ctrprop.counter_property_id = ctr_der_filter_cur_rec.counter_property_id
AND ctrprop.counter_id = l_new_ctr_id;
SELECT created_from_counter_tmpl_id
INTO l_created_map_id
FROM csi_counters_b
WHERE counter_id = fmla_ctr_cur_rec.counter_id;
SELECT max(counter_id)
INTO l_new_mapped_ctr_id
FROM csi_counters_b
WHERE created_from_counter_tmpl_id = ctr_formula_bvars_cur_rec.source_counter_id;
SELECT inventory_item_id
INTO l_mapped_item_id
FROM csi_item_instances
WHERE instance_id = x_relationship_tbl(i).subject_id
AND inv_master_organization_id = l_org_id;
l_counter_relationships_rec.last_update_date := sysdate;
l_counter_relationships_rec.last_Updated_by := fnd_global.user_id;
l_counter_relationships_rec.last_update_login := FND_GLOBAL.user_id;
PROCEDURE delete_item_association
(
p_api_version IN NUMBER
,p_commit IN VARCHAR2
,p_init_msg_list IN VARCHAR2
,p_validation_level IN NUMBER
,p_ctr_associations_id IN 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) := 'DELETE_ITEM_ASSOCIATION';
SELECT ctr_association_id, counter_id
FROM csi_ctr_item_associations
WHERE counter_id IS NOT NULL
AND associated_to_group = 'Y'
AND group_id = p_group_id;
SELECT counter_id instantiated_counter_id
FROM csi_counters_b
WHERE created_from_counter_tmpl_id = p_counter_id;
SAVEPOINT delete_item_association_pvt;
( 'delete_item_association_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) );
csi_ctr_gen_utility_pvt.put_line('Inside delete Item association = '||to_char(l_ctr_associations_id));
SELECT associated_to_group, group_id, counter_id
INTO l_associated_to_group, l_group_id, l_counter_id
FROM csi_ctr_item_associations
WHERE ctr_association_id = l_ctr_associations_id;
SELECT 'x'
INTO l_associations_exists
FROM csi_counter_associations
WHERE counter_id = get_ctr_rec.instantiated_counter_id;
/* Now Delete the data that was verified */
FOR get_item_rec in get_item_details(l_group_id) LOOP
/* Call the table Handler */
CSI_CTR_ITEM_ASSOCIATIONS_PKG.Delete_Row
(p_CTR_ASSOCIATION_ID => get_item_rec.ctr_association_id);
ROLLBACK TO delete_item_association_pvt;
/* Now Delete the main group-item association */
/* Call the table Handler */
CSI_CTR_ITEM_ASSOCIATIONS_PKG.Delete_Row
(p_CTR_ASSOCIATION_ID => l_ctr_associations_id);
ROLLBACK TO delete_item_association_pvt;
SELECT 'x'
INTO l_associations_exists
FROM csi_counter_associations
WHERE counter_id = get_ctr_rec.instantiated_counter_id;
/* Now Delete the data that was verified */
/* Call the table Handler */
CSI_CTR_ITEM_ASSOCIATIONS_PKG.Delete_Row
(p_CTR_ASSOCIATION_ID => l_ctr_associations_id);
ROLLBACK TO delete_item_association_pvt;
ROLLBACK TO delete_item_association_pvt;
ROLLBACK TO delete_item_association_pvt;
END delete_item_association;