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 name
FROM cn_rules cr
WHERE cr.rule_id <> -1002
AND cr.ruleset_id = p_ruleset_id
AND cr.org_id=p_org_id
AND NOT EXISTS (SELECT 1
FROM cn_attribute_rules car
WHERE car.rule_id = cr.rule_id and
car.org_id=cr.org_id);
SELECT attr.ruleset_id ruleset_id,
attr.rule_id rule_id,
attr.column_id column_id ,
attr.column_value column_value,
attr.high_value,
attr.low_value,
cr.name rule_name,
col.user_name,
attr.org_id
FROM cn_rules cr, cn_attribute_rules attr, cn_objects col
WHERE cr.rule_id <> -1002
and col.object_id = attr.column_id
and col.table_id = -11803
AND cr.ruleset_id = p_ruleset_id
AND attr.rule_id = cr.rule_id
and attr.ruleset_id = cr.ruleset_id
and dimension_hierarchy_id is null and
cr.org_id=attr.org_id and
attr.org_id=col.org_id;
FUNCTION check_update_allowed
( p_ruleset_id IN cn_rulesets.ruleset_id%TYPE,
p_module_type In cn_rulesets.module_type%TYPE,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
x_org_id IN cn_rulesets.org_id%TYPE
) RETURN VARCHAR2 IS
l_api_name CONSTANT VARCHAR2(30) := 'check_update_allowed';
SELECT count(1) cnt
FROM cn_rules_all_b
WHERE ruleset_id = p_ruleset_id
AND rule_id <> -1002
AND org_id = l_env_org_id;
SELECT module_type
FROM cn_rulesets
WHERE ruleset_id = p_ruleset_id;
END check_update_allowed;
SELECT count(*) cnt
FROM cn_rulesets cnr
WHERE p_date BETWEEN cnr.start_date AND cnr.end_date
AND nvl(module_type,'X') = nvl(p_module_type,'X')
AND ruleset_id <> p_ruleset_id
AND org_id=p_org_id;
SELECT count(*) cnt
FROM cn_rulesets cnr
WHERE cnr.start_date BETWEEN p_start_date AND p_end_date
AND cnr.end_date BETWEEN p_start_date AND p_end_date
AND nvl(module_type,'X') = nvl(p_module_type,'X')
AND ruleset_id <> p_ruleset_id and
org_id=p_org_id;
x_loading_status := 'CN_INSERTED';
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE name = p_ruleset_rec.ruleset_name
AND module_type = p_ruleset_rec.module_type
AND start_date = p_ruleset_rec.start_date
AND end_date = p_ruleset_rec.end_date and
ORG_ID=p_ruleset_rec.org_id;
SELECT Decode(p_ruleset_rec.ruleset_id, NULL, cn_rulesets_s.NEXTVAL,
p_ruleset_rec.ruleset_id)
INTO l_ruleset_id
FROM dual;
cn_syin_rulesets_pkg.insert_row
(
x_rowid => l_rowid,
x_ruleset_id => l_ruleset_id,
x_end_date => p_ruleset_rec.end_date,
x_ruleset_status => 'UNSYNC',
x_destination_column_id => -11980,
x_repository_id => 100,
x_start_date => p_ruleset_rec.start_date,
x_name => p_ruleset_rec.ruleset_name,
x_module_type => p_ruleset_rec.module_type,
x_creation_date => sysdate,
x_created_by => g_created_by,
x_last_update_date => sysdate,
x_last_updated_by => g_last_updated_by,
x_last_update_login => g_last_update_login,
x_org_id =>p_ruleset_rec.org_id
);
PROCEDURE Update_Ruleset
( 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_ruleset_rec IN OUT NOCOPY CN_Ruleset_PVT.ruleset_rec_type,
p_ruleset_rec IN OUT NOCOPY CN_Ruleset_PVT.ruleset_rec_type
) IS
CURSOR l_ovn_csr IS
SELECT nvl(object_version_number,1)
FROM cn_rulesets
WHERE ruleset_id = p_old_ruleset_rec.ruleset_id AND
ORG_ID=p_old_ruleset_rec.org_id;
SELECT count(1)
FROM cn_rules_all_b
WHERE ruleset_id = p_old_ruleset_rec.ruleset_id
and rule_id <> -1002 and
org_id = p_old_ruleset_rec.org_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Ruleset';
SELECT *
FROM cn_rulesets
WHERE ruleset_id = l_ruleset_id and
org_id=l_org_id;
SAVEPOINT Update_Ruleset;
x_loading_status := 'CN_UPDATED';
select name,
start_date,
end_date,
module_type
into p_old_ruleset_rec.ruleset_name,
p_old_ruleset_rec.start_date,
p_old_ruleset_rec.end_date,
p_old_ruleset_rec.module_type
from cn_rulesets
where ruleset_id = p_ruleset_rec.ruleset_id and
org_id=p_ruleset_rec.org_id;
SELECT count(1)
INTO l_count
FROM cn_rulesets
WHERE ruleset_id = p_old_ruleset_rec.ruleset_id and
org_id=p_old_ruleset_rec.org_id;
IF check_update_allowed
( p_old_ruleset_rec.ruleset_id,
p_ruleset_rec.module_type,
x_loading_status,
x_loading_status,
P_old_ruleset_rec.org_id) = fnd_api.g_true
THEN
RAISE fnd_api.g_exc_error;
cn_syin_rulesets_pkg.update_row
(
x_ruleset_id => p_old_ruleset_rec.ruleset_id,
x_object_version_number => p_ruleset_rec.object_version_number,
x_end_date => p_ruleset_rec.end_date,
x_ruleset_status => 'UNSYNC',
x_destination_column_id => -11980,
x_repository_id => 100,
x_start_date => p_ruleset_rec.start_date,
x_name => p_ruleset_rec.ruleset_name,
x_module_type => p_ruleset_rec.module_type,
x_last_update_date => sysdate,
x_last_updated_by => g_last_updated_by,
x_last_update_login => g_last_update_login,
x_org_id => p_ruleset_rec.org_id
);
cn_syin_rulesets_pkg.update_row(p_ruleset_rec.ruleset_id,
l_get_ruleset_data_rec.object_version_number,
'CONCFAIL',
l_get_ruleset_data_rec.destination_column_id,
l_get_ruleset_data_rec.repository_id,
l_get_ruleset_data_rec.start_date,
l_get_ruleset_data_rec.end_date,
l_get_ruleset_data_rec.name,
l_get_ruleset_data_rec.module_type,
null,
null,
null,
p_ruleset_rec.org_id);
ROLLBACK TO Update_Ruleset;
ROLLBACK TO Update_Ruleset;
ROLLBACK TO Update_Ruleset;