The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LAST_UPDATE_DATE DATE := Sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
SELECT rule_id
FROM cn_rules
WHERE ruleset_id = p_ruleset_id
AND name = p_ruleattribute_rec.rule_name
AND org_id=p_org_id;
x_loading_status := 'CN_INSERTED';
SELECT object_id
INTO l_object_id
FROM cn_objects
WHERE name = l_RuleAttribute_rec.object_name
AND table_id = -11803 and
org_id=l_RuleAttribute_rec.org_id;
SELECT cn_attribute_rules_s.NEXTVAL
INTO l_ruleattribute_rec_pvt.attribute_rule_id
FROM dual;
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE name = l_ruleattribute_rec.ruleset_name
AND start_date = l_ruleattribute_rec.start_date
AND end_date = l_ruleattribute_rec.end_date
and org_id=l_RuleAttribute_rec.org_id;
SELECT ruleset_id
INTO l_ruleattribute_rec_pvt.ruleset_id
FROM cn_rulesets
WHERE name = l_ruleattribute_rec.ruleset_name
AND start_date = l_ruleattribute_rec.start_date
AND end_date = l_ruleattribute_rec.end_date and
org_id=l_RuleAttribute_rec.org_id;
PROCEDURE Update_RuleAttribute
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
p_old_RuleAttribute_rec IN CN_RuleAttribute_PUB.RuleAttribute_rec_type,
p_RuleAttribute_rec IN CN_RuleAttribute_PUB.RuleAttribute_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_RuleAttribute';
SELECT rule_id
FROM cn_rules
WHERE ruleset_id = p_ruleset_id
AND name = p_rule_name and
org_id=p_org_id;
SELECT attribute_rule_id
FROM cn_attribute_rules
WHERE rule_id = p_rule_id
AND column_id = p_column_id and
org_id=p_org_id;
SAVEPOINT Update_RuleAttribute;
x_loading_status := 'CN_UPDATED';
'UPDATE_RULEATTRIBUTE',
'B',
'C')
THEN
cn_ruleattribute_pub_cuhk.update_ruleattribute_pre
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec,
p_old_RuleAttribute_rec => l_old_RuleAttribute_rec);
'UPDATE_RULEATTRIBUTE',
'B',
'V')
THEN
cn_ruleattribute_pub_vuhk.update_ruleattribute_pre
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec,
p_old_RuleAttribute_rec => l_old_RuleAttribute_rec);
SELECT object_id
INTO l_old_object_id
FROM cn_objects
WHERE name = l_old_RuleAttribute_rec.object_name
AND table_id = -11803;
SELECT object_id
INTO l_object_id
FROM cn_objects
WHERE name = l_RuleAttribute_rec.object_name
AND table_id = -11803;
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE name = l_ruleattribute_rec.ruleset_name
AND start_date = l_ruleattribute_rec.start_date
AND end_date = l_ruleattribute_rec.end_date;
SELECT ruleset_id
INTO l_ruleattribute_rec_pvt.ruleset_id
FROM cn_rulesets
WHERE name = l_ruleattribute_rec.ruleset_name
AND start_date = l_ruleattribute_rec.start_date
AND end_date = l_ruleattribute_rec.end_date;
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE name = l_old_ruleattribute_rec.ruleset_name
AND start_date = l_old_ruleattribute_rec.start_date
AND end_date = l_old_ruleattribute_rec.end_date;
SELECT ruleset_id
INTO l_old_ruleattribute_rec_pvt.ruleset_id
FROM cn_rulesets
WHERE name = l_old_ruleattribute_rec.ruleset_name
AND start_date = l_old_ruleattribute_rec.start_date
AND end_date = l_old_ruleattribute_rec.end_date and
org_id=l_old_ruleattribute_rec.org_id;
-- giving error as multiple records are found with a single select below
/*
SELECT attribute_rule_id
INTO l_ruleattribute_rec_pvt.attribute_rule_id
FROM cn_attribute_rules
WHERE rule_id = i.rule_id
AND column_id = l_object_id;
SELECT attribute_rule_id
INTO l_old_ruleattribute_rec_pvt.attribute_rule_id
FROM cn_attribute_rules
WHERE rule_id = i.rule_id
AND column_id = l_old_object_id;
cn_ruleattribute_pvt.update_ruleattribute
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec_pvt,
p_old_ruleattribute_rec => l_old_ruleattribute_rec_pvt);
'UPDATE_RULEATTRIBUTE',
'A',
'V')
THEN
cn_ruleattribute_pub_vuhk.update_ruleattribute_post
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec,
p_old_RuleAttribute_rec => l_old_RuleAttribute_rec);
'UPDATE_RULEATTRIBUTE',
'A',
'C')
THEN
cn_ruleattribute_pub_cuhk.update_ruleattribute_post
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec,
p_old_RuleAttribute_rec => l_old_RuleAttribute_rec);
'UPDATE_RULEATTRIBUTE',
'M',
'M')
THEN
IF cn_ruleattribute_pub_cuhk.ok_to_generate_msg
(p_ruleattribute_rec => l_ruleattribute_rec)
THEN
-- Clear bind variables
-- XMLGEN.clearBindValues;
ROLLBACK TO Update_RuleAttribute;
ROLLBACK TO Update_RuleAttribute;
ROLLBACK TO Update_RuleAttribute;
PROCEDURE Delete_RuleAttribute
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
p_ruleattribute_rec IN CN_RuleAttribute_PUB.ruleattribute_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_RuleAttribute';
SELECT rule_id
FROM cn_rules
WHERE ruleset_id = p_ruleset_id
AND name = p_rule_name
AND org_id = p_org_id;
SELECT attribute_rule_id,object_version_number
FROM cn_attribute_rules
WHERE rule_id = p_rule_id
AND column_id = p_column_id
AND org_id = p_org_id;
SAVEPOINT Delete_RuleAttribute;
'DELETE_RULEATTRIBUTE',
'B',
'C')
THEN
cn_ruleattribute_pub_cuhk.delete_ruleattribute_pre
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec);
'DELETE_RULEATTRIBUTE',
'B',
'V')
THEN
cn_ruleattribute_pub_vuhk.delete_ruleattribute_pre
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec);
SELECT object_id
INTO l_object_id
FROM cn_objects
WHERE name = l_RuleAttribute_rec.object_name
AND table_id = -11803
and org_id = l_RuleAttribute_rec.org_id;
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE name = l_ruleattribute_rec.ruleset_name
AND start_date = l_ruleattribute_rec.start_date
AND end_date = l_ruleattribute_rec.end_date;
SELECT ruleset_id
INTO l_ruleattribute_rec_pvt.ruleset_id
FROM cn_rulesets
WHERE name = l_ruleattribute_rec.ruleset_name
AND start_date = l_ruleattribute_rec.start_date
AND end_date = l_ruleattribute_rec.end_date;
-- giving error as multiple records are found with a single select below
/*
SELECT attribute_rule_id
INTO l_ruleattribute_rec_pvt.attribute_rule_id
FROM cn_attribute_rules
WHERE rule_id = i.rule_id
AND column_id = l_object_id;
cn_ruleattribute_pvt.delete_ruleattribute
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleset_id => l_ruleattribute_rec_pvt.ruleset_id,
p_rule_id => l_ruleattribute_rec_pvt.rule_id,
p_object_version_number => l_ruleattribute_rec_pvt.object_version_number,
p_attribute_rule_id => l_ruleattribute_rec_pvt.attribute_rule_id);
'DELETE_RULEATTRIBUTE',
'A',
'V')
THEN
cn_ruleattribute_pub_vuhk.delete_ruleattribute_pre
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec);
'DELETE_RULEATTRIBUTE',
'A',
'C')
THEN
cn_ruleattribute_pub_cuhk.delete_ruleattribute_pre
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status,
p_ruleattribute_rec => l_ruleattribute_rec);
'DELETE_RULEATTRIBUTE',
'M',
'M')
THEN
IF cn_ruleattribute_pub_cuhk.ok_to_generate_msg
(p_ruleattribute_rec => l_ruleattribute_rec)
THEN
-- Clear bind variables
-- XMLGEN.clearBindValues;
ROLLBACK TO Delete_RuleAttribute;
ROLLBACK TO Delete_RuleAttribute;
ROLLBACK TO Delete_RuleAttribute;