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_rule_name
AND ORG_ID=p_org_id;
x_loading_status := 'CN_INSERTED';
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE name = l_rule_rec.ruleset_name
AND start_date = l_rule_rec.start_date
AND end_date = l_rule_rec.end_date and
org_id=l_rule_rec.org_id;
SELECT ruleset_id
INTO l_rule_rec_pvt.ruleset_id
FROM cn_rulesets
WHERE name = l_rule_rec.ruleset_name
AND start_date = l_rule_rec.start_date
AND end_date = l_rule_rec.end_date and
org_id=l_rule_rec.org_id;
SELECT count(1)
INTO l_count
FROM cn_revenue_classes
WHERE name = l_rule_rec.revenue_class_name;
SELECT revenue_class_id
INTO l_rule_rec_pvt.revenue_class_id
FROM cn_revenue_classes
WHERE name = l_rule_rec.revenue_class_name;
SELECT count(1)
INTO l_count
FROM gl_code_combinations
WHERE code_combination_id = l_rule_rec.expense_ccid;
SELECT count(1)
INTO l_count
FROM gl_code_combinations
WHERE code_combination_id = l_rule_rec.liability_ccid;
SELECT count(1)
INTO l_count
FROM cn_rules
WHERE name = l_rule_rec.parent_rule_name
AND ruleset_id = l_rule_rec_pvt.ruleset_id
AND ORG_ID=l_rule_rec_pvt.ORG_ID;
select cn_rules_s.nextval
into l_rule_rec_pvt.rule_id
from dual;
SELECT Nvl(MAX(sequence_number) + 1, 1)
INTO l_rule_rec_pvt.sequence_number
FROM cn_rules_hierarchy
WHERE ruleset_id = l_rule_rec_pvt.ruleset_id
AND parent_rule_id = l_rule_rec_pvt.parent_rule_id and
org_id=l_rule_rec_pvt.org_id;
PROCEDURE Update_Rule
( 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_rule_rec IN OUT NOCOPY CN_Rule_PUB.rule_rec_type,
p_rule_rec IN OUT NOCOPY CN_Rule_PUB.rule_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Rule';
SELECT cnrv.rule_id, cnrv.parent_rule_id
FROM cn_rules_v cnrv, cn_rules cnr1, cn_rules cnr2
WHERE cnr1.name = l_old_rule_rec.rule_name
AND cnr2.name = l_old_rule_rec.parent_rule_name
AND cnr1.ruleset_id = p_ruleset_id
AND cnr2.ruleset_id = p_ruleset_id
AND cnr1.rule_id = cnrv.rule_id
AND cnr2.rule_id = cnrv.parent_rule_id
and cnrv.org_id =cnr1.org_id
and cnr1.org_id = cnr2.org_id
and cnrv.org_id = p_org_id;
SELECT rule_id
FROM cn_rules
WHERE name = l_rule_rec.parent_rule_name
AND ruleset_id = p_ruleset_id
and org_id=p_org_id;
SAVEPOINT Update_Rule;
x_loading_status := 'CN_UPDATED';
'UPDATE_RULE',
'B',
'C')
THEN
cn_rule_pub_cuhk.update_rule_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_rule_rec => l_rule_rec,
p_old_rule_rec => l_old_rule_rec);
'UPDATE_RULE',
'B',
'V')
THEN
cn_rule_pub_vuhk.update_rule_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_rule_rec => l_rule_rec,
p_old_rule_rec => l_old_rule_rec);
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE name = l_old_rule_rec.ruleset_name
AND start_date = l_old_rule_rec.start_date
AND end_date = l_old_rule_rec.end_date;
SELECT ruleset_id,org_id
INTO l_old_rule_rec_pvt.ruleset_id,l_old_rule_rec_pvt.org_id
FROM cn_rulesets
WHERE name = l_old_rule_rec.ruleset_name
AND start_date = l_old_rule_rec.start_date
AND end_date = l_old_rule_rec.end_date;
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE name = l_rule_rec.ruleset_name
AND start_date = l_rule_rec.start_date
AND end_date = l_rule_rec.end_date;
SELECT ruleset_id,org_id
INTO l_rule_rec_pvt.ruleset_id,l_rule_rec_pvt.org_id
FROM cn_rulesets
WHERE name = l_rule_rec.ruleset_name
AND start_date = l_rule_rec.start_date
AND end_date = l_rule_rec.end_date;
SELECT count(1)
INTO l_count
FROM cn_rules
WHERE name = l_old_rule_rec.parent_rule_name
AND ruleset_id = l_old_rule_rec_pvt.ruleset_id and
org_id=l_old_rule_rec_pvt.org_id;
SELECT count(1)
INTO l_count
FROM cn_rules
WHERE name = l_rule_rec.parent_rule_name
AND ruleset_id = l_rule_rec_pvt.ruleset_id
and org_id=l_rule_rec_pvt.org_id;
SELECT count(1)
INTO l_count
FROM cn_revenue_classes
WHERE name = l_rule_rec.revenue_class_name;
SELECT revenue_class_id
INTO l_rule_rec_pvt.revenue_class_id
FROM cn_revenue_classes
WHERE name = l_rule_rec.revenue_class_name;
SELECT count(1)
INTO l_count
FROM gl_code_combinations
WHERE code_combination_id = l_rule_rec.expense_ccid;
SELECT count(1)
INTO l_count
FROM gl_code_combinations
WHERE code_combination_id = l_rule_rec.liability_ccid;
cn_rule_pvt.update_rule
(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_old_rule_rec => l_old_rule_rec_pvt,
p_rule_rec => l_rule_rec_pvt);
cn_rule_pvt.update_rule
(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_old_rule_rec => l_old_rule_rec_pvt,
p_rule_rec => l_rule_rec_pvt);
'UPDATE_RULE',
'A',
'V')
THEN
cn_rule_pub_vuhk.update_rule_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_rule_rec => l_rule_rec,
p_old_rule_rec => l_old_rule_rec);
'UPDATE_RULE',
'A',
'C')
THEN
cn_rule_pub_cuhk.update_rule_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_rule_rec => l_rule_rec,
p_old_rule_rec => l_old_rule_rec);
'UPDATE_RULE',
'M',
'M')
THEN
IF cn_rule_pub_cuhk.ok_to_generate_msg
-- (p_rule_rec => l_rule_rec)
(p_rule_name => l_rule_rec.rule_name)
THEN
-- Clear bind variables
-- XMLGEN.clearBindValues;
ROLLBACK TO Update_Rule;
ROLLBACK TO Update_Rule;
ROLLBACK TO Update_Rule;
PROCEDURE Delete_Rule
( 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_rule_name IN cn_rules.name%TYPE,
p_ruleset_name IN cn_rulesets.name%TYPE,
p_ruleset_start_date IN cn_rulesets.start_date%TYPE,
p_ruleset_end_date IN cn_rulesets.end_date%TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rule';
SELECT rule_id
FROM cn_rules
WHERE ruleset_id = p_ruleset_id
AND name = p_rule_name AND
org_id=p_org_id;
SAVEPOINT Delete_Rule;
x_loading_status := 'CN_DELETED';
'DELETE_RULE',
'B',
'C')
THEN
cn_rule_pub_cuhk.delete_rule_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_rule_name => l_rule_name,
p_ruleset_name => l_ruleset_name,
p_ruleset_start_date => l_ruleset_start_date,
p_ruleset_end_date => l_ruleset_end_date);
'DELETE_RULE',
'B',
'V')
THEN
cn_rule_pub_vuhk.delete_rule_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_rule_name => l_rule_name,
p_ruleset_name => l_ruleset_name,
p_ruleset_start_date => l_ruleset_start_date,
p_ruleset_end_date => l_ruleset_end_date);
SELECT COUNT(1)
INTO l_count
FROM cn_rulesets
WHERE name = l_ruleset_name
AND start_date = l_ruleset_start_date
AND end_date = l_ruleset_end_date;
SELECT ruleset_id,org_id
INTO l_ruleset_id,l_org_id
FROM cn_rulesets
WHERE name = l_ruleset_name
AND start_date = l_ruleset_start_date
AND end_date = l_ruleset_end_date;
cn_rule_pvt.delete_rule
(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_ruleset_id,
p_rule_id => i.rule_id,
p_org_id => l_org_id);
'DELETE_RULE',
'A',
'V')
THEN
cn_rule_pub_vuhk.delete_rule_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_rule_name => l_rule_name,
p_ruleset_name => l_ruleset_name,
p_ruleset_start_date => l_ruleset_start_date,
p_ruleset_end_date => l_ruleset_end_date);
'DELETE_RULE',
'A',
'C')
THEN
cn_rule_pub_cuhk.delete_rule_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_rule_name => l_rule_name,
p_ruleset_name => l_ruleset_name,
p_ruleset_start_date => l_ruleset_start_date,
p_ruleset_end_date => l_ruleset_end_date);
'DELETE_RULE',
'M',
'M')
THEN
IF cn_rule_pub_cuhk.ok_to_generate_msg
(p_rule_name => l_rule_name)
THEN
-- Clear bind variables
-- XMLGEN.clearBindValues;
ROLLBACK TO Delete_Rule;
ROLLBACK TO Delete_Rule;
ROLLBACK TO Delete_Rule;
select distinct(name) ruleName
from cn_rules
where rule_id in (
select PARENT_RULE_ID
from cn_rules_hierarchy
where ruleset_id = p_ruleset_id
connect by prior PARENT_RULE_ID = rule_id
start with rule_id = p_rule_id);
select name
from cn_rules
where rule_id = p_rule_id;