[Home] [Help]
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 module_type
from cn_rulesets
where ruleset_id = p_rule_rec.ruleset_id and
org_id=p_rule_rec.org_id;
SELECT count(*) cnt
FROM cn_rules_hierarchy cnrh,
cn_rules cnr
WHERE cnrh.parent_rule_id = p_parent_rule_id
AND cnr.ruleset_id = p_ruleset_id
AND cnrh.rule_id = cnr.rule_id
AND cnr.name = p_rule_name
AND cnrh.org_id=cnr.org_id
AND cnr.org_id=p_org_id;
SELECT cnrh.parent_rule_id,
cnr.name
FROM cn_rules_hierarchy cnrh,
cn_rules cnr
WHERE cnr.ruleset_id = p_ruleset_id
AND cnrh.rule_id = p_parent_rule_id
AND cnrh.rule_id = cnr.rule_id
AND cnrh.org_id=cnr.org_id
AND cnr.org_id=p_org_id;
x_loading_status := 'CN_INSERTED';
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE ruleset_id = p_rule_rec.ruleset_id
AND org_id= p_rule_rec.org_id;
SELECT count(1)
INTO l_count
FROM cn_rules
WHERE rule_id = p_rule_rec.parent_rule_id and
org_id=p_rule_rec.org_id;
SELECT Decode(p_rule_rec.rule_id, NULL,cn_rules_s.NEXTVAL, p_rule_rec.rule_id)
INTO l_rule_id
FROM dual;
SELECT nvl(MAX(nvl(sequence_number, 0)),0) + 1
INTO l_sequence_number
FROM cn_rules_hierarchy
WHERE ruleset_id = p_rule_rec.ruleset_id;
cn_syin_rules_pkg.insert_row(l_rule_id,
p_rule_rec.rule_name,
p_rule_rec.ruleset_id,
p_rule_rec.revenue_class_id,
p_rule_rec.expense_ccid,
p_rule_rec.liability_ccid,
p_rule_rec.parent_rule_id,
l_sequence_number,
p_rule_rec.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 CN_Rule_PVT.rule_rec_type,
p_rule_rec IN OUT NOCOPY CN_Rule_PVT.rule_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Rule';
SAVEPOINT Update_Rule;
x_loading_status := 'CN_UPDATED';
select object_version_number into l_object_version_number
from cn_rules_all where rule_id = p_old_rule_rec.rule_id
and org_id = p_old_rule_rec.org_id;
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE ruleset_id = p_rule_rec.ruleset_id;
SELECT count(1)
INTO l_count
FROM cn_rules
WHERE rule_id = p_rule_rec.parent_rule_id and
org_id=p_rule_rec.org_id;
SELECT COUNT(1)
INTO l_count
FROM cn_rules
WHERE rule_id = p_old_rule_rec.rule_id
and org_id=p_rule_rec.org_id;
cn_syin_rules_pkg.update_row(p_old_rule_rec.rule_id,
p_rule_rec.ruleset_id,
null,
p_rule_rec.revenue_class_id,
p_rule_rec.expense_ccid,
p_rule_rec.liability_ccid,
p_rule_rec.rule_name,
Sysdate,
g_last_updated_by,
g_last_update_login,
p_rule_rec.org_id,
p_rule_rec.object_version_no);
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_id IN cn_rules_all_b.rule_id%TYPE,
p_ruleset_id IN cn_rules_all_b.ruleset_id%TYPE,
p_org_id IN cn_rules_all_b.org_id%TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rule';
SAVEPOINT Delete_Rule;
x_loading_status := 'CN_DELETED';
SELECT COUNT(1)
INTO l_count
FROM
( SELECT rule_id ,ruleset_id
FROM cn_rules_hierarchy
WHERE ruleset_id=Nvl(p_ruleset_id ,-1002) and org_id=p_org_id
CONNECT BY PRIOR rule_id = parent_rule_id
START WITH rule_id = Nvl(p_rule_id, -1002)
)a WHERE EXISTS
(SELECT 'x'
FROM cn_attribute_rules car
WHERE car.ruleset_id = a.ruleset_id
AND car.rule_id = a.rule_id and car.org_id=p_org_id);
SELECT COUNT(1)
INTO l_count
FROM cn_rules
WHERE ruleset_id = p_ruleset_id
AND rule_id = p_rule_id and
org_id=p_org_id;
cn_syin_rules_pkg.delete_row(p_rule_id, p_ruleset_id,p_org_id);
ROLLBACK TO Delete_Rule;
ROLLBACK TO Delete_Rule;
ROLLBACK TO Delete_Rule;
l_select varchar2(4000) :=
'SELECT rset.ruleset_id ruleset_id,
rset.name ruleset_name,
rule.rule_id rule_id,
rule.name,
rule.revenue_class_id,
rule.expense_ccid,
rule.liability_id,
rule.org_id
FROM cn_rulesets rset, cn_rules rule
WHERE rset.ruleset_id = rule.ruleset_id AND
rset.org_id=rule.org_id AND
rset.org_id=:B1 AND
upper(rset.name) like upper(:B2) ';
x_loading_status := 'SELECTED';
OPEN rule_cur FOR l_select using p_org_id,p_ruleset_name;
x_loading_status := 'SELECTED';
SELECT cnobj.user_name object_name,
cnh.name hierarchy_name, cnattr.column_value column_value,
cnattr.not_flag not_flag, cnattr.high_value high_value,
cnattr.low_value low_value , cnattr.dimension_hierarchy_id dimension_hierarchy_id
FROM cn_attribute_rules cnattr, cn_objects cnobj,
cn_head_hierarchies cnh
WHERE cnattr.rule_id = l_rule_id
AND cnattr.column_id = cnobj.object_id (+)
AND cnattr.org_id = cnobj.org_id
AND cnattr.dimension_hierarchy_id = cnh.head_hierarchy_id(+)
AND cnattr.org_id = cnh.org_id(+);
SELECT COUNT(1)
INTO l_user_expression
FROM CN_RULE_ATTR_EXPRESSION
WHERE RULE_ID = p_rule_id ;
SELECT DISTINCT expression
INTO rule_exp
FROM CN_ATTRIBUTE_RULES
WHERE RULE_ID = p_rule_id ;
SELECT name INTO node_value
FROM cn_hierarchy_nodes
WHERE value_id=l_column_value;