The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM cn_objects
WHERE object_id = p_column_id
AND table_id IN (-11803,-16134) AND
ORG_ID=p_org_id;
SELECT name
FROM cn_rules
WHERE rule_id = p_rule_id
AND ruleset_id = p_ruleset_id AND
ORG_ID=p_org_id;
SELECT name
FROM cn_rulesets
WHERE ruleset_id = p_ruleset_id AND
ORG_ID=p_org_id;
select
low_value,
high_value,
column_value,
not_flag
from cn_attribute_rules where attribute_rule_id=c_attribute_rule_id and org_id=c_org_id;
select
low_value,
high_value,
column_value,
not_flag
from cn_attribute_rules where attribute_rule_id=c_attribute_rule_id and org_id=c_org_id;
SELECT object_id
INTO l_object_column
FROM cn_objects
WHERE name = p_object_name AND org_id=p_org_id
AND table_id IN (-11803,-16134);
SELECT COUNT(*)
INTO l_counter
FROM cn_attribute_rules
WHERE column_id = l_object_column
AND column_value = p_value_1
AND rule_id = p_rule_id and org_id=p_org_id
AND ((p_attribute_rule_id IS NOT NULL AND
attribute_rule_id <> p_attribute_rule_id)
OR
(p_attribute_rule_id IS NULL))
AND not_flag = nvl(l_not_flag,not_flag) ; -- Added Kumar
SELECT COUNT(*)
INTO l_counter
FROM cn_attribute_rules
WHERE column_id = l_object_column
AND low_value = p_value_1
AND high_value = p_value_2
and org_id=p_org_id
AND rule_id = p_rule_id
AND ((p_attribute_rule_id IS NOT NULL AND
attribute_rule_id <> p_attribute_rule_id)
OR
(p_attribute_rule_id IS NULL))
AND not_flag = nvl(l_not_flag,not_flag) ; -- Added Kumar;
SELECT head_hierarchy_id
INTO l_dim_hierarchy_id
FROM cn_head_hierarchies
WHERE head_hierarchy_id = p_value_1 and org_id=p_org_id;
SELECT hn.value_id
INTO l_hierarchy_value
FROM cn_hierarchy_nodes hn,
cn_dim_hierarchies dh
--WHERE hn.name = p_value_2
WHERE hn.value_id = p_value_2
AND hn.dim_hierarchy_id = dh.dim_hierarchy_id
and hn.org_id=dh.org_id
and hn.org_id=p_org_id
AND dh.header_dim_hierarchy_id = l_dim_hierarchy_id;
SELECT COUNT(*)
INTO l_counter
FROM cn_attribute_rules
WHERE column_id = l_object_column
AND dimension_hierarchy_id = l_dim_hierarchy_id
AND column_value = l_hierarchy_value
AND rule_id = p_rule_id
and org_id=p_org_id
AND ((p_attribute_rule_id IS NOT NULL AND
attribute_rule_id <> p_attribute_rule_id)
OR
(p_attribute_rule_id IS NULL))
AND not_flag = nvl(l_not_flag,not_flag) ; -- Added Kumar;
G_LAST_UPDATE_DATE DATE := Sysdate;
G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
x_loading_status := 'CN_INSERTED';
/* This shouldn't be here. select from sequence
IF (cn_api.chk_miss_null_num_para
( p_RuleAttribute_rec.attribute_rule_id,
cn_api.get_lkup_meaning('RULE_ATTRIBUTE_ID', 'RULESET_TYPE'),
x_loading_status,
x_loading_status) = FND_API.G_TRUE )
THEN
RAISE fnd_api.g_exc_error;
SELECT object_id
INTO l_object_id
FROM cn_objects
WHERE name = p_RuleAttribute_rec.object_name and
org_id=p_RuleAttribute_rec.org_id
AND table_id IN (-11803,-16134);
SELECT Nvl(p_RuleAttribute_rec.attribute_rule_id, cn_attribute_rules_s.NEXTVAL)
INTO l_attribute_rule_id
FROM dual;
cn_syin_attr_rules_pkg.insert_row
(l_attribute_rule_id,
l_object_id,
p_RuleAttribute_rec.value_1,
NULL,
NULL,
NULL,
p_RuleAttribute_rec.not_flag,
p_RuleAttribute_rec.rule_id,
p_RuleAttribute_rec.ruleset_id,
g_last_update_date,
g_last_updated_by,
g_creation_date,
g_created_by,
g_last_update_login,
p_RuleAttribute_rec.org_id);
cn_syin_attr_rules_pkg.insert_row
(l_attribute_rule_id,
l_object_id,
NULL,
p_RuleAttribute_rec.value_1,
p_RuleAttribute_rec.value_2,
NULL,
p_RuleAttribute_rec.not_flag,
p_RuleAttribute_rec.rule_id,
p_RuleAttribute_rec.ruleset_id,
g_last_update_date,
g_last_updated_by,
g_creation_date,
g_created_by,
g_last_update_login,
p_RuleAttribute_rec.org_id);
SELECT head_hierarchy_id
INTO l_dim_hierarchy_id
FROM cn_head_hierarchies
--WHERE name = p_RuleAttribute_rec.value_1;
SELECT hn.value_id
INTO l_hierarchy_value
FROM cn_hierarchy_nodes hn,
cn_dim_hierarchies dh
--WHERE hn.name = p_RuleAttribute_rec.value_2
WHERE hn.value_id = p_RuleAttribute_rec.value_2
AND hn.dim_hierarchy_id = dh.dim_hierarchy_id
AND dh.header_dim_hierarchy_id = l_dim_hierarchy_id
AND hn.org_id=dh.org_id
AND hn.org_id=p_RuleAttribute_rec.org_id;
cn_syin_attr_rules_pkg.insert_row
(l_attribute_rule_id,
l_object_id,
l_hierarchy_value,
NULL,
NULL,
l_dim_hierarchy_id,
p_RuleAttribute_rec.not_flag,
p_RuleAttribute_rec.rule_id,
p_RuleAttribute_rec.ruleset_id,
g_last_update_date,
g_last_updated_by,
g_creation_date,
g_created_by,
g_last_update_login,
p_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 OUT NOCOPY CN_RuleAttribute_PVT.RuleAttribute_rec_type,
p_RuleAttribute_rec IN OUT NOCOPY CN_RuleAttribute_PVT.RuleAttribute_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_RuleAttribute';
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 object_version_number
FROM cn_attribute_rules
WHERE attribute_rule_id = p_ruleattribute_rec.attribute_rule_id
AND ruleset_id = p_ruleattribute_rec.ruleset_id
AND rule_id = p_ruleattribute_rec.rule_id
AND ORG_ID=p_RuleAttribute_rec.org_id;
SAVEPOINT Update_RuleAttribute;
x_loading_status := 'CN_UPDATED';
SELECT object_id
INTO l_object_id
FROM cn_objects
WHERE name = p_RuleAttribute_rec.object_name AND org_id=p_RuleAttribute_rec.org_id
AND table_id IN (-11803,-16134);
cn_syin_attr_rules_pkg.update_row
(/*l_attribute_rule_id*/ p_old_RuleAttribute_rec.attribute_rule_id,
p_RuleAttribute_rec.object_version_number,
l_object_id,
p_RuleAttribute_rec.value_1,
NULL,
NULL,
NULL,
p_RuleAttribute_rec.not_flag,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
p_RuleAttribute_rec.org_id);
cn_syin_attr_rules_pkg.update_row
(p_old_RuleAttribute_rec.attribute_rule_id ,
p_RuleAttribute_rec.object_version_number,
l_object_id,
NULL,
p_RuleAttribute_rec.value_1,
p_RuleAttribute_rec.value_2,
NULL,
p_RuleAttribute_rec.not_flag,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
p_RuleAttribute_rec.org_id);
SELECT head_hierarchy_id
INTO l_dim_hierarchy_id
FROM cn_head_hierarchies
WHERE head_hierarchy_id = p_RuleAttribute_rec.value_1
AND org_id=p_RuleAttribute_rec.org_id;
SELECT hn.value_id
INTO l_hierarchy_value
FROM cn_hierarchy_nodes hn,
cn_dim_hierarchies dh
WHERE hn.value_id = p_RuleAttribute_rec.value_2
AND hn.dim_hierarchy_id = dh.dim_hierarchy_id
AND dh.header_dim_hierarchy_id = l_dim_hierarchy_id
AND hn.org_id=dh.org_id
AND hn.org_id=p_RuleAttribute_rec.org_id;
cn_syin_attr_rules_pkg.update_row
(p_old_RuleAttribute_rec.attribute_rule_id,
p_RuleAttribute_rec.object_version_number,
l_object_id,
l_hierarchy_value,
NULL,
NULL,
l_dim_hierarchy_id,
p_RuleAttribute_rec.not_flag,
g_last_update_date,
g_last_updated_by,
g_last_update_login,
p_RuleAttribute_rec.org_id);
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_ruleset_id IN cn_attribute_rules.ruleset_id%TYPE,
p_rule_id IN cn_attribute_rules.rule_id%TYPE,
p_attribute_rule_id IN cn_attribute_rules.attribute_rule_id%TYPE,
p_object_version_number IN cn_attribute_rules.object_version_number%TYPE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_RuleAttribute';
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 object_version_number
FROM cn_attribute_rules
WHERE attribute_rule_id = p_attribute_rule_id
AND ruleset_id = p_ruleset_id
AND rule_id = p_rule_id;
SAVEPOINT Delete_RuleAttribute;
SELECT COUNT(1)
INTO l_count
FROM cn_rule_attr_expression
WHERE ((operand1 = p_attribute_rule_id
AND operand1_ra_rae_flag = 'RA')
OR
(operand2 = p_attribute_rule_id
AND operand2_ra_rae_flag = 'RA'))
AND rule_id = p_rule_id;
SELECT COUNT(1)
INTO l_count
FROM cn_attribute_rules
WHERE attribute_rule_id = p_attribute_rule_id
AND ruleset_id = p_ruleset_id
AND rule_id = p_rule_id;
select org_id into l_org_id
FROM cn_attribute_rules
WHERE attribute_rule_id = p_attribute_rule_id
AND ruleset_id = p_ruleset_id
AND rule_id = p_rule_id;
cn_syin_attr_rules_pkg.delete_row(p_attribute_rule_id);
ROLLBACK TO Delete_RuleAttribute;
ROLLBACK TO Delete_RuleAttribute;
ROLLBACK TO Delete_RuleAttribute;
l_select_stmt VARCHAR2(4000);
l_select VARCHAR2(4000);
l_select := l_valueset_r.table_info.value_column_name ||' column_name, ' ||
NVL(l_valueset_r.table_info.id_column_name, 'null') || ' column_id, ' ||
NVL(l_valueset_r.table_info.meaning_column_name, 'null') || ' column_meaning';
l_select_stmt := 'Select ' || l_select || ' from ' || l_from || ' ' || l_where ;
return l_select_stmt;
x_select OUT NOCOPY VARCHAR2,
x_from OUT NOCOPY VARCHAR2,
x_where OUT NOCOPY VARCHAR2) IS
l_valueset_r fnd_vset.valueset_r;
l_select_stmt VARCHAR2(4000);
SELECT value_set_id
FROM cn_objects
WHERE object_id = p_column_id
AND name = p_column_name
AND object_type = 'COL'
AND org_id=p_org_id
AND value_set_id IS NOT NULL;
x_select := NVL(l_valueset_r.table_info.id_column_name,
l_valueset_r.table_info.value_column_name)
||' column_id , '
||l_valueset_r.table_info.value_column_name
||' column_name ';
x_select := x_select || ', ' || NVL(l_valueset_r.table_info.meaning_column_name,
l_valueset_r.table_info.value_column_name) || ' meaning ' ;
x_select:= NVL(l_valueset_r.table_info.id_column_name,
l_valueset_r.table_info.value_column_name)
||' column_id , '
||l_valueset_r.table_info.value_column_name
||' column_name ';
x_select := x_select || ', ' || NVL(l_valueset_r.table_info.meaning_column_name,
l_valueset_r.table_info.value_column_name) || ' meaning ' ;
l_select_stmt := 'Select ' || x_select ||
' from ' || x_from ||
' ' || x_where ;
dbms_sql.parse(l_cursor_num,l_select_stmt,2);
x_select := ' 1 column_id , 2 column_name ';
x_select := ' 1 column_id , 2 column_name ';