[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;
ELSIF (p_operation = 'update') THEN
l_key := l_key || '-' || p_role_plan_rec.object_version_number;
ELSIF (p_operation = 'delete') THEN
wf_event.AddParameterToList('COMP_PLAN_ID',p_role_plan_rec.comp_plan_id,l_list);
l_list.DELETE;
SELECT *
FROM cn_roles
WHERE NAME = l_role_name;
SELECT *
FROM cn_comp_plans
WHERE NAME = l_comp_plan_name AND org_id = l_org_id;
SELECT *
FROM cn_role_plans
WHERE role_plan_id = l_role_plan_id;
SELECT *
FROM cn_role_plans
WHERE role_plan_id = l_role_plan_id;
PROCEDURE check_valid_insert (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_role_plan_rec IN role_plan_rec_type,
x_role_id OUT NOCOPY cn_roles.role_id%TYPE,
x_comp_plan_id OUT NOCOPY cn_comp_plans.comp_plan_id%TYPE,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'check_valid_insert';
SELECT start_date, end_date, comp_plan_id
FROM cn_role_plans
WHERE role_id = l_role_id AND org_id = l_org_id;
SELECT start_date, end_date
FROM cn_comp_plans
WHERE NAME = l_comp_plan_name AND org_id = l_org_id;
END check_valid_insert;
PROCEDURE check_valid_update (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_role_plan_rec_old IN role_plan_rec_type,
p_role_plan_rec_new IN role_plan_rec_type,
x_role_plan_id_old OUT NOCOPY cn_role_plans.role_plan_id%TYPE,
x_role_id OUT NOCOPY cn_roles.role_id%TYPE,
x_comp_plan_id OUT NOCOPY cn_comp_plans.comp_plan_id%TYPE,
x_date_update_only OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'check_valid_update';
SELECT start_date, end_date, comp_plan_id
FROM cn_role_plans
WHERE role_id = l_role_id
AND org_id = l_org_id
AND role_plan_id <> l_role_plan_id;
SELECT *
FROM cn_role_plans
WHERE role_plan_id = l_role_plan_id;
SELECT start_date, end_date
FROM cn_comp_plans
WHERE comp_plan_id = l_comp_plan_id;
x_date_update_only := fnd_api.g_false;
x_date_update_only := fnd_api.g_true;
END check_valid_update;
PROCEDURE check_valid_delete (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_role_plan_rec IN role_plan_rec_type,
x_role_plan_id OUT NOCOPY NUMBER,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'check_valid_delete';
END check_valid_delete;
PROCEDURE srp_plan_assignment_for_insert (
p_role_id IN cn_roles.role_id%TYPE,
p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
p_org_id IN cn_role_plans.org_id%TYPE
)
IS
/* CURSOR l_cur IS
select sr.srp_role_id srp_role_id,
nvl(srd.job_title_id, G_MISS_JOB_TITLE) job_title_id,
nvl(srd.plan_activate_status, 'NOT_PUSHED') push_status
from cn_srp_roles sr,
cn_srp_role_dtls srd
where role_id = p_role_id
and srd.role_model_id is NULL
-- CHANGED FOR MODELING IMPACT
and sr.srp_role_id = srd.srp_role_id(+);*/
SELECT srp_role_id
FROM cn_srp_roles
WHERE role_id = p_role_id AND org_id = p_org_id;
END srp_plan_assignment_for_insert;
PROCEDURE srp_plan_assignment_for_update (
p_role_id IN cn_roles.role_id%TYPE,
p_role_id_old IN cn_roles.role_id%TYPE,
p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
p_date_update_only IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
p_org_id IN cn_role_plans.org_id%TYPE,
p_org_id_old IN cn_role_plans.org_id%TYPE
)
IS
/* CURSOR l_cur IS
select sr.srp_role_id srp_role_id,
nvl(srd.job_title_id, G_MISS_JOB_TITLE) job_title_id,
nvl(srd.plan_activate_status, 'NOT_PUSHED') push_status
from cn_srp_roles sr,
cn_srp_role_dtls srd
where role_id = p_role_id
and srd.role_model_id is NULL
-- CHANGED FOR MODELING IMPACT
and sr.srp_role_id = srd.srp_role_id(+);*/
SELECT srp_role_id
FROM cn_srp_roles
WHERE role_id = p_role_id AND org_id = p_org_id;
SELECT srp_role_id
FROM cn_srp_roles
WHERE role_id = p_role_id_old AND org_id = p_org_id_old;
IF (p_date_update_only = fnd_api.g_true)
THEN
FOR l_rec IN l_cur
LOOP
-- if l_rec.job_title_id = G_MISS_JOB_TITLE OR
-- l_rec.push_status = 'PUSHED' THEN
cn_srp_plan_assigns_pvt.update_srp_plan_assigns
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_srp_role_id => l_rec.srp_role_id,
p_role_plan_id => p_role_plan_id,
x_loading_status => l_loading_status
);
cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_srp_role_id => l_rec_del.srp_role_id,
p_role_plan_id => p_role_plan_id,
x_loading_status => l_loading_status
);
END srp_plan_assignment_for_update;
PROCEDURE srp_plan_assignment_for_delete (
p_role_id IN cn_roles.role_id%TYPE,
p_role_plan_id IN cn_role_plans.role_plan_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
p_org_id IN cn_role_plans.org_id%TYPE
)
IS
CURSOR l_cur
IS
SELECT srp_role_id
FROM cn_srp_roles
WHERE role_id = p_role_id AND org_id = p_org_id;
cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
(p_api_version => 1.0,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_srp_role_id => l_rec.srp_role_id,
p_role_plan_id => p_role_plan_id,
x_loading_status => l_loading_status
);
END srp_plan_assignment_for_delete;
x_loading_status := 'CN_INSERTED';
check_valid_insert (x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec => l_rec,
x_role_id => l_role_id,
x_comp_plan_id => l_comp_plan_id,
p_loading_status => x_loading_status, -- in
x_loading_status => x_loading_status -- out
);
SELECT cn_role_plans_s.NEXTVAL
INTO l_role_plan_id
FROM DUAL;
cn_role_plans_pkg.insert_row
(x_rowid => g_rowid,
x_role_plan_id => l_role_plan_id,
x_role_id => l_role_id,
x_comp_plan_id => l_comp_plan_id,
x_start_date => p_role_plan_rec.start_date,
x_end_date => p_role_plan_rec.end_date,
x_create_module => 'OSC',
x_attribute_category => p_role_plan_rec.attribute_category,
x_attribute1 => p_role_plan_rec.attribute1,
x_attribute2 => p_role_plan_rec.attribute2,
x_attribute3 => p_role_plan_rec.attribute3,
x_attribute4 => p_role_plan_rec.attribute4,
x_attribute5 => p_role_plan_rec.attribute5,
x_attribute6 => p_role_plan_rec.attribute6,
x_attribute7 => p_role_plan_rec.attribute7,
x_attribute8 => p_role_plan_rec.attribute8,
x_attribute9 => p_role_plan_rec.attribute9,
x_attribute10 => p_role_plan_rec.attribute10,
x_attribute11 => p_role_plan_rec.attribute11,
x_attribute12 => p_role_plan_rec.attribute12,
x_attribute13 => p_role_plan_rec.attribute13,
x_attribute14 => p_role_plan_rec.attribute14,
x_attribute15 => p_role_plan_rec.attribute15,
x_created_by => g_created_by,
x_creation_date => g_creation_date,
x_last_update_login => g_last_update_login,
x_last_update_date => g_last_update_date,
x_last_updated_by => g_last_updated_by,
x_org_id => p_role_plan_rec.org_id,
x_object_version_number => l_object_version_number
);
srp_plan_assignment_for_insert (p_role_id => l_role_id,
p_role_plan_id => l_role_plan_id,
x_return_status => x_return_status,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status,
p_org_id => l_rec.org_id
);
PROCEDURE update_role_plan (
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_loading_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_role_plan_rec_old IN role_plan_rec_type
:= g_miss_role_plan_rec,
p_ovn IN OUT NOCOPY cn_role_plans.object_version_number%TYPE,
p_role_plan_rec_new IN role_plan_rec_type
:= g_miss_role_plan_rec,
p_role_plan_id IN cn_role_plans.role_plan_id%TYPE
)
IS
--p_role_plan_id should be NULL if passing p_role_plan_rec_old
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Role_Plan';
l_date_update_only VARCHAR2 (1);
SAVEPOINT update_role_plan;
x_loading_status := 'CN_UPDATED';
SELECT cn_api.g_miss_char role_name,
role_id,
cn_api.g_miss_char comp_plan_name,
comp_plan_id,
start_date,
end_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
object_version_number,
org_id
INTO l_rec_old
FROM cn_role_plans
WHERE role_plan_id = p_role_plan_id;
'cn.plsql.cn_role_plans_pub.update_role_plan.org_validate',
'Validated org_id = ' || l_org_id || ' status = '||l_status);
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'cn.plsql.cn_role_plans_pub.update_role_plan.error',
true);
FND_MESSAGE.SET_NAME ('FND' , 'FND_MO_OU_CANNOT_UPDATE');
'UPDATE_ROLE_PLAN',
'B',
'C'
)
THEN
cn_role_plans_pub_cuhk.update_role_plan_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_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec_old => l_rec_old,
p_role_plan_rec_new => l_rec_new
);
'UPDATE_ROLE_PLAN',
'B',
'V'
)
THEN
cn_role_plans_pub_vuhk.update_role_plan_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_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec_old => l_rec_old,
p_role_plan_rec_new => l_rec_new
);
check_valid_update (x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec_old => l_rec_old,
p_role_plan_rec_new => l_rec_new,
x_role_plan_id_old => l_role_plan_id_old,
x_role_id => l_role_id,
x_comp_plan_id => l_comp_plan_id,
x_date_update_only => l_date_update_only,
p_loading_status => x_loading_status, -- in
x_loading_status => x_loading_status -- out
);
(p_operation => 'update',
p_pre_or_post => 'pre',
p_role_plan_id => l_role_plan_id_old,
p_role_plan_rec => l_rec_old);
cn_role_plans_pkg.update_row
(x_role_plan_id => l_role_plan_id_old,
x_role_id => l_role_id,
x_comp_plan_id => l_comp_plan_id,
x_start_date => p_role_plan_rec_new.start_date,
x_end_date => p_role_plan_rec_new.end_date,
x_attribute_category => p_role_plan_rec_new.attribute_category,
x_attribute1 => p_role_plan_rec_new.attribute1,
x_attribute2 => p_role_plan_rec_new.attribute2,
x_attribute3 => p_role_plan_rec_new.attribute3,
x_attribute4 => p_role_plan_rec_new.attribute4,
x_attribute5 => p_role_plan_rec_new.attribute5,
x_attribute6 => p_role_plan_rec_new.attribute6,
x_attribute7 => p_role_plan_rec_new.attribute7,
x_attribute8 => p_role_plan_rec_new.attribute8,
x_attribute9 => p_role_plan_rec_new.attribute9,
x_attribute10 => p_role_plan_rec_new.attribute10,
x_attribute11 => p_role_plan_rec_new.attribute11,
x_attribute12 => p_role_plan_rec_new.attribute12,
x_attribute13 => p_role_plan_rec_new.attribute13,
x_attribute14 => p_role_plan_rec_new.attribute14,
x_attribute15 => p_role_plan_rec_new.attribute15,
x_created_by => g_created_by,
x_creation_date => g_creation_date,
x_last_update_login => g_last_update_login,
x_last_update_date => g_last_update_date,
x_last_updated_by => g_last_updated_by,
x_object_version_number => p_ovn,
x_org_id => l_rec_new.org_id
);
SELECT object_version_number
INTO p_ovn
FROM cn_role_plans
WHERE role_plan_id = l_role_plan_id_old;
srp_plan_assignment_for_update
(p_role_id => l_role_id,
p_role_id_old => l_rec_old.role_id,
p_role_plan_id => l_role_plan_id_old,
p_date_update_only => l_date_update_only,
x_return_status => x_return_status,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status,
p_org_id => l_rec_new.org_id,
p_org_id_old => l_rec_old.org_id
);
(p_operation => 'update',
p_pre_or_post => 'post',
p_role_plan_id => l_role_plan_id_old,
p_role_plan_rec => l_rec_new);
'UPDATE_ROLE_PLAN',
'A',
'V'
)
THEN
cn_role_plans_pub_cuhk.update_role_plan_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_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec_old => l_rec_old,
p_role_plan_rec_new => l_rec_new
);
'UPDATE_ROLE_PLAN',
'A',
'C'
)
THEN
cn_role_plans_pub_vuhk.update_role_plan_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_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec_old => l_rec_old,
p_role_plan_rec_new => l_rec_new
);
'UPDATE_ROLE_PLAN',
'M',
'M'
)
THEN
IF cn_role_plans_pub_cuhk.ok_to_generate_msg
(p_role_plan_rec => l_rec_new)
THEN
-- Clear bind variables
-- XMLGEN.clearBindValues;
ROLLBACK TO update_role_plan;
ROLLBACK TO update_role_plan;
ROLLBACK TO update_role_plan;
END update_role_plan;
PROCEDURE delete_role_plan (
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_loading_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_role_plan_rec IN role_plan_rec_type
:= g_miss_role_plan_rec
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Role_Plan';
SAVEPOINT delete_role_plan;
x_loading_status := 'CN_DELETED';
'cn.plsql.cn_role_plans_pub.delete_role_plan.org_validate',
'Validated org_id = ' || l_org_id || ' status = '||l_status);
'DELETE_ROLE_PLAN',
'B',
'C'
)
THEN
cn_role_plans_pub_cuhk.delete_role_plan_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_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec => l_rec
);
'DELETE_ROLE_PLAN',
'B',
'V'
)
THEN
cn_role_plans_pub_vuhk.delete_role_plan_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_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec => l_rec
);
(p_operation => 'delete',
p_pre_or_post => 'pre',
p_role_plan_id => l_role_plan_id,
p_role_plan_rec => l_rec);
check_valid_delete (x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec => l_rec,
x_role_plan_id => l_role_plan_id,
p_loading_status => x_loading_status, -- in
x_loading_status => x_loading_status -- out
);
srp_plan_assignment_for_delete
(p_role_id => l_role_id,
p_role_plan_id => l_role_plan_id,
x_return_status => x_return_status,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status,
p_org_id => l_rec.org_id
);
CN_SCENARIOS_PVT.delete_scenario_plans(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_role_plan_id => l_role_plan_id,
p_comp_plan_id => null,
p_role_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
cn_role_plans_pkg.delete_row (x_role_plan_id => l_role_plan_id);
(p_operation => 'delete',
p_pre_or_post => 'post',
p_role_plan_id => l_role_plan_id,
p_role_plan_rec => l_rec);
'DELETE_ROLE_PLAN',
'A',
'V'
)
THEN
cn_role_plans_pub_cuhk.delete_role_plan_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_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec => l_rec
);
'DELETE_ROLE_PLAN',
'A',
'C'
)
THEN
cn_role_plans_pub_vuhk.delete_role_plan_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_loading_status => x_loading_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_role_plan_rec => l_rec
);
'DELETE_ROLE_PLAN',
'M',
'M'
)
THEN
IF cn_role_plans_pub_cuhk.ok_to_generate_msg
(p_role_plan_rec => l_rec)
THEN
-- Clear bind variables
-- XMLGEN.clearBindValues;
ROLLBACK TO delete_role_plan;
ROLLBACK TO delete_role_plan;
ROLLBACK TO delete_role_plan;
END delete_role_plan;