[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE srp_plan_assignment_for_insert
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2 ) IS
CURSOR l_cur IS
SELECT role_plan_id, create_module
FROM cn_role_plans
WHERE role_id = p_role_id;
debugmsg('insert into cn_srp_plan_assigns...');
END srp_plan_assignment_for_insert;
PROCEDURE srp_pmt_plan_asgn_for_insert
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2 ) IS
CURSOR l_cur IS
SELECT role_pmt_plan_id
FROM cn_role_pmt_plans
WHERE role_id = p_role_id;
debugmsg('insert into cn_srp_pmt_plans...');
END srp_pmt_plan_asgn_for_insert;
PROCEDURE srp_pay_groups_asgn_for_insert
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2 ) IS
CURSOR l_cur IS
SELECT role_pay_group_id
FROM cn_role_pay_groups
WHERE role_id = p_role_id;
debugmsg('insert into cn_srp_pay_groups...');
END srp_pay_groups_asgn_for_insert;
SELECT a1.period_id, a1.start_date, a1.end_date
FROM cn_period_statuses a1, cn_repositories r
WHERE (a1.period_id BETWEEN l_s_prd_id AND l_e_prd_id)
AND a1.period_status in ('O', 'F')
AND a1.period_set_id = r.period_set_id
AND a1.period_type_id = r.period_type_id
AND a1.org_id = r.org_id
AND NOT exists ( SELECT * FROM cn_srp_intel_periods a2
WHERE a2.salesrep_id = l_srp_id AND
a2.period_id = a1.period_id AND
a2.org_id = a1.org_id);
cn_intel_calc_pkg.insert_row
(x_srp_intel_period_id => '',
x_salesrep_id => p_salesrep_id,
x_org_id => l_org_id,
x_period_id => l_rec.period_id,
x_start_date => l_rec.start_date,
x_end_date => l_rec.end_date,
x_processing_status_code => 'CLEAN',
x_process_all_flag => 'Y',
x_attribute_category => '',
x_attribute1 => '',
x_attribute2 => '',
x_attribute3 => '',
x_attribute4 => '',
x_attribute5 => '',
x_attribute6 => '',
x_attribute7 => '',
x_attribute8 => '',
x_attribute9 => '',
x_attribute10 => '',
x_attribute11 => '',
x_attribute12 => '',
x_attribute13 => '',
x_attribute14 => '',
x_attribute15 => '',
x_created_by => fnd_global.user_id,
x_creation_date => sysdate,
x_last_update_login => fnd_global.login_id,
x_last_update_date => sysdate,
x_last_updated_by => fnd_global.user_id);
PROCEDURE srp_plan_assignment_for_update
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_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 ) IS
CURSOR l_cur IS
SELECT role_plan_id, create_module
FROM cn_role_plans
WHERE role_id = p_role_id;
IF (p_date_update_only = FND_API.G_TRUE) THEN
FOR l_rec IN l_cur LOOP
debugmsg('update cn_srp_plan_assigns.......');
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 => p_srp_role_id,
p_role_plan_id => l_rec.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 => p_srp_role_id,
p_role_plan_id => l_rec.role_plan_id,
x_loading_status => l_loading_status);
END srp_plan_assignment_for_update;
PROCEDURE srp_pmt_plan_asgn_for_update
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_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 ) IS
CURSOR l_cur IS
SELECT role_pmt_plan_id
FROM cn_role_pmt_plans
WHERE role_id = p_role_id;
debugmsg('update cn_srp_pmt_plans.......');
cn_srp_pmt_plans_pvt.update_mass_asgn_srp_pmt_plan
(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 => p_srp_role_id,
p_role_pmt_plan_id => l_rec.role_pmt_plan_id,
x_loading_status => l_loading_status);
END srp_pmt_plan_asgn_for_update;
PROCEDURE srp_pay_group_asgn_for_update
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_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 ) IS
CURSOR l_cur IS
SELECT role_pay_group_id
FROM cn_role_pay_groups
WHERE role_id = p_role_id;
debugmsg('update cn_srp_pay_groups.......');
cn_srp_paygroup_pub.update_mass_asgn_srp_pay
(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 => p_srp_role_id,
p_role_pay_group_id => l_rec.role_pay_group_id,
x_srp_pay_group_id => l_srp_pay_group_id,
x_loading_status => l_loading_status);
END srp_pay_group_asgn_for_update;
PROCEDURE srp_plan_assignment_for_delete
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2 ) IS
CURSOR l_cur IS
SELECT role_plan_id
FROM cn_role_plans
WHERE role_id = p_role_id;
cn_srp_plan_assigns_pvt.delete_srp_plan_assigns
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
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 => p_srp_role_id,
p_role_plan_id => l_rec.role_plan_id,
x_loading_status => l_loading_status);
END srp_plan_assignment_for_delete;
PROCEDURE srp_pmt_plan_asgn_for_delete
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2 ) IS
CURSOR l_cur IS
SELECT role_pmt_plan_id
FROM cn_role_pmt_plans
WHERE role_id = p_role_id;
cn_srp_pmt_plans_pvt.delete_mass_asgn_srp_pmt_plan
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
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 => p_srp_role_id,
p_role_pmt_plan_id => l_rec.role_pmt_plan_id,
x_loading_status => l_loading_status);
END srp_pmt_plan_asgn_for_delete;
SELECT srp_role_id, org_id, start_date, salesrep_id
FROM cn_srp_roles
WHERE srp_role_id = p_srp_role_id;
SELECT count(*)
INTO l_count
FROM cn_srp_roles srp, cn_srp_plan_assigns plan
WHERE srp.srp_role_id = srp_role.srp_role_id
AND srp.org_id = srp_role.org_id
AND srp.org_id = plan.org_id
AND srp.salesrep_id = plan.salesrep_id
AND ( srp.start_date > plan.end_date OR nvl(srp.end_date, l_null_date) < plan.start_date);
SELECT count(*) into l_count_pay
FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
WHERE w.salesrep_id = l_salesrep_id
AND w.org_id = srp_role.org_id
AND w.org_id = prd.org_id
AND prd.org_id = prun.org_id
AND prun.pay_period_id = prd.period_id
AND prun.payrun_id = w.payrun_id;
select max(pay_date) into l_max_pay_date
from cn_payment_worksheets W, cn_payruns prun
WHERE w.salesrep_id = l_salesrep_id
AND prun.payrun_id = w.payrun_id
AND prun.org_id = w.org_id
AND w.org_id = srp_role.org_id;
SELECT cn_api.get_acc_period_id(prd.end_date, srp_role.org_id)
into l_pay_run_period_id
FROM cn_payment_worksheets W, cn_period_statuses prd, cn_payruns prun
WHERE w.salesrep_id = l_salesrep_id
AND w.org_id = srp_role.org_id
AND w.org_id = prun.org_id
AND prun.org_id = prd.org_id
AND prun.pay_period_id = prd.period_id
AND prun.payrun_id = w.payrun_id
AND prun.pay_date = l_max_pay_date
AND ROWNUM = 1; -- this check is for offcycle payruns created with the same pay dates.
PROCEDURE srp_pay_group_asgn_for_delete
(p_role_id IN cn_roles.role_id%TYPE,
p_srp_role_id IN cn_srp_roles.srp_role_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
p_loading_status IN VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2 ) IS
CURSOR l_cur IS
SELECT role_pay_group_id
FROM cn_role_pay_groups
WHERE role_id = p_role_id;
/* --Prevent delete if wrksheet exist - vensrini
val_srp_pg_asgn_for_del(p_srp_role_id => p_srp_role_id,
x_return_status => x_return_status);
cn_srp_paygroup_pvt.delete_mass_asgn_srp_pay
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
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 => p_srp_role_id,
p_role_pay_group_id => l_rec.role_pay_group_id,
x_loading_status => l_loading_status);
END srp_pay_group_asgn_for_delete;
SELECT p.period_id,
greatest(p_start_date, p.start_date) start_date,
Decode(p_end_date, NULL, p.end_date,
Least(p_end_date, p.end_date)) end_date
FROM cn_srp_intel_periods p
WHERE p.salesrep_id = p_salesrep_id
AND p.org_id = l_org_id
AND (p_end_date IS NULL OR p.start_date <= p_end_date)
AND (p.end_date >= p_start_date);
l_action := 'DELETE_DEST';
l_srp_tbl.DELETE;
select salesrep_id, org_id
from jtf_rs_salesreps
where resource_id = p_role_resource_id
AND p_role_resource_type = 'RS_INDIVIDUAL'
UNION ALL
select salesrep_id, org_id
from jtf_rs_group_members gm, jtf_rs_salesreps s
where gm.group_member_id = p_role_resource_id
and gm.resource_id = s.resource_id
and delete_flag = 'N'
AND p_role_resource_type = 'RS_GROUP_MEMBER'
UNION ALL
select salesrep_id, org_id
from jtf_rs_team_members tm, jtf_rs_salesreps s
where tm.team_member_id = p_role_resource_id
and tm.team_resource_id = s.resource_id
and resource_type = 'INDIVIDUAL'
and delete_flag = 'N'
AND p_role_resource_type = 'RS_TEAM_MEMBER';
SELECT t.start_date_active, t.end_date_active, rr.role_id,
sr.salesrep_id, tm.team_id, t.team_name
from jtf_rs_team_members tm,
jtf_rs_salesreps sr,
jtf_rs_team_usages tu,
jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_teams_vl t
where rr.role_relate_id = p_role_relate_id
and rr.role_resource_type(+) = 'RS_TEAM_MEMBER'
and tm.resource_type = 'INDIVIDUAL'
and tm.delete_flag = 'N'
and tu.team_id = tm.team_id
and tu.usage = 'SALES_COMP'
and sr.resource_id = tm.team_resource_id
and (sr.org_id is null or sr.org_id = (select org_id from cn_repositories))
and rr.role_resource_id(+) = tm.team_member_id
and rr.delete_flag(+) = 'N'
and rb.role_id(+) = rr.role_id
and rb.role_type_code(+) = 'SALES_COMP'
and t.team_id = tm.team_id;
SELECT r.manager_flag, r.group_id, s.salesrep_id
FROM jtf_rs_group_usages u,
jtf_rs_group_mbr_role_vl r,
cn_rs_salesreps s,
jtf_rs_roles_b ro
WHERE r.role_relate_id = p_role_relate_id
AND u.group_id = r.group_id
AND u.usage = 'SALES_COMP'
AND ro.role_id = r.role_id
AND ro.role_type_code = 'SALES_COMP'
AND s.resource_id = r.resource_id;
SELECT ct.name name,
ct.comp_team_id team_id,
greatest(r.start_date_active, ct.start_date_active) start_date,
least(nvl(ct.end_date_active, l_max_date),
nvl(r.end_date_active, l_max_date)) end_date
FROM jtf_rs_group_usages u,
jtf_rs_group_mbr_role_vl r,
cn_rs_salesreps s, -- single-org view
jtf_rs_roles_b ro,
cn_srp_comp_teams_v srt,
cn_comp_teams ct
WHERE r.role_relate_id = p_role_relate_id
AND s.salesrep_id = p_salesrep_id -- safe since single-org context
AND u.group_id = r.group_id
AND u.usage = 'SALES_COMP'
AND ro.role_id = r.role_id
AND ro.role_type_code = 'SALES_COMP'
AND s.resource_id = r.resource_id
AND s.salesrep_id = srt.salesrep_id
AND srt.comp_team_id = ct.comp_team_id
AND (r.start_date_active <= ct.start_date_active
or r.start_date_active between ct.start_date_active
and nvl (ct.end_date_active, r.start_date_active))
AND nvl(r.end_date_active, ct.start_date_active) >= ct.start_date_active;
SELECT category
FROM jtf_rs_resource_extns
where resource_id = P_ROLE_RESOURCE_ID;
select role_type_code into l_usage
from jtf_rs_roles_b
where role_id = P_ROLE_ID;
select count(1) into l_count
from jtf_rs_role_relations rr, jtf_rs_roles_b r
where rr.role_resource_id = P_ROLE_RESOURCE_ID
and rr.role_resource_type = 'RS_INDIVIDUAL'
and rr.delete_flag = 'N'
and r.role_id = rr.role_id
and r.role_type_code = 'SALES_COMP'
-- Bug 4083951 by mnativ
-- AND NVL(e1,s2) >= s2 AND s1 <= NVL(e2,s1)
-- s1,e1 = IN params, s2,e2 = existing role assignment
AND NVL(TRUNC(P_END_DATE_ACTIVE),TRUNC(rr.start_date_active))
>= TRUNC(rr.start_date_active)
AND TRUNC(P_START_DATE_ACTIVE)
<= NVL(TRUNC(rr.end_date_active),TRUNC(P_START_DATE_ACTIVE));
select count(1) into l_count
from jtf_rs_role_relations rr, jtf_rs_roles_b r
where rr.role_resource_id = P_ROLE_RESOURCE_ID
and rr.role_resource_type = 'RS_INDIVIDUAL'
and rr.delete_flag = 'N'
and r.role_id = rr.role_id
and r.role_type_code = 'SALES_COMP_PAYMENT_ANALIST'
-- Bug 4083951 by mnativ
-- AND NVL(e1,s2) >= s2 AND s1 <= NVL(e2,s1)
-- s1,e1 = IN params, s2,e2 = existing role assignment
AND NVL(TRUNC(P_END_DATE_ACTIVE),TRUNC(rr.start_date_active))
>= TRUNC(rr.start_date_active)
AND TRUNC(P_START_DATE_ACTIVE)
<= NVL(TRUNC(rr.end_date_active),TRUNC(P_START_DATE_ACTIVE));
select count(1) into l_count
from jtf_rs_role_relations rr, jtf_rs_roles_b r
where rr.role_resource_id = P_ROLE_RESOURCE_ID
and rr.role_resource_type = 'RS_INDIVIDUAL'
and rr.delete_flag = 'N'
and r.role_id = rr.role_id
and r.role_id <> G_PAYEE_ROLE
and r.role_type_code = 'SALES_COMP';
select count(1) into l_count
from jtf_rs_role_relations rr
where rr.role_resource_id = P_ROLE_RESOURCE_ID
and rr.role_resource_type = 'RS_INDIVIDUAL'
and rr.delete_flag = 'N'
and rr.role_id = G_PAYEE_ROLE;
x_loading_status := 'CN_INSERTED';
-- insert into the sales comp tables
-- we're already in a loop to cycle through all the applicable orgs
-- associated with the salesreps assigned to the given resource
srp_plan_assignment_for_insert
(p_role_id => P_ROLE_ID,
p_srp_role_id => P_ROLE_RELATE_ID,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
srp_pmt_plan_asgn_for_insert
(p_role_id => P_ROLE_ID,
p_srp_role_id => P_ROLE_RELATE_ID,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
srp_pay_groups_asgn_for_insert
(p_role_id => P_ROLE_ID,
p_srp_role_id => P_ROLE_RELATE_ID,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
-- Insert into cn_srp_intel_periods for intelligent calculation
ins_srp_intel_prd
(p_salesrep_id => r.salesrep_id,
p_start_date => p_start_date_active,
p_end_date => p_end_date_active,
x_msg_count => P_COUNT,
x_msg_data => P_DATA,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
PROCEDURE update_res_role_relate_post
(P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE
DEFAULT FND_API.G_MISS_DATE,
P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE
DEFAULT FND_API.G_MISS_DATE,
P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
P_DATA OUT NOCOPY VARCHAR2,
P_COUNT OUT NOCOPY NUMBER,
P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
l_role_id NUMBER := NULL;
l_api_name VARCHAR2(30) := 'update_res_role_relate_post';
select salesrep_id, org_id
from jtf_rs_salesreps
where resource_id = l_role_resource_id
AND l_role_resource_type = 'RS_INDIVIDUAL'
UNION ALL
select salesrep_id, org_id
from jtf_rs_group_members gm, jtf_rs_salesreps s
where gm.group_member_id = l_role_resource_id
and gm.resource_id = s.resource_id
and delete_flag = 'N'
AND l_role_resource_type = 'RS_GROUP_MEMBER'
UNION ALL
select salesrep_id, org_id
from jtf_rs_team_members tm, jtf_rs_salesreps s
where tm.team_member_id = l_role_resource_id
and tm.team_resource_id = s.resource_id
and resource_type = 'INDIVIDUAL'
and delete_flag = 'N'
AND l_role_resource_type = 'RS_TEAM_MEMBER';
SELECT ct.name name,
ct.comp_team_id team_id,
greatest(l_start_date, ct.start_date_active) start_date,
least(nvl(ct.end_date_active, l_max_date),
nvl(l_end_date, l_max_date)) end_date
FROM jtf_rs_group_usages u,
jtf_rs_group_mbr_role_vl r,
cn_rs_salesreps s,
jtf_rs_roles_b ro,
cn_srp_comp_teams_v srt,
cn_comp_teams ct
WHERE r.role_relate_id = p_role_relate_id
AND s.salesrep_id = p_salesrep_id
AND u.group_id = r.group_id
AND u.usage = 'SALES_COMP'
AND ro.role_id = r.role_id
AND s.resource_id = r.resource_id
AND s.salesrep_id = srt.salesrep_id
AND srt.comp_team_id = ct.comp_team_id
AND (l_start_date <= ct.start_date_active
or l_start_date between ct.start_date_active
and nvl (ct.end_date_active, l_start_date));
select category
from jtf_rs_resource_extns re, jtf_rs_role_relations rr
where re.resource_id = rr.role_resource_id
and rr.role_relate_id = P_ROLE_RELATE_ID;
debugmsg('Inside vertical hook update_role_relate_post');
select rr.role_resource_type, r.role_type_code, r.role_id, rr.role_resource_id
INTO l_role_resource_type, l_usage, l_role_id, l_role_resource_id
from jtf_rs_role_relations rr, jtf_rs_roles_b r
where rr.role_relate_id = p_role_relate_id
and rr.role_id = r.role_id;
SELECT COUNT(1) INTO l_count
FROM jtf_rs_role_relations rrr
WHERE role_relate_id = P_ROLE_RELATE_ID
AND EXISTS
(
SELECT NULL
FROM jtf_rs_role_relations rr,
jtf_rs_roles_b r
WHERE rrr.role_resource_id = rr.role_resource_id
AND rrr.role_relate_id <> rr.role_relate_id
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND rr.delete_flag = 'N'
AND r.role_id = rr.role_id
AND r.role_type_code = 'SALES_COMP_PAYMENT_ANALIST'
-- AND NVL(e1,s2) >= s2 AND s1 <= NVL(e2,s1)
-- s1,e1 = IN params, s2,e2 = existing role assignment
AND NVL(TRUNC(P_END_DATE_ACTIVE),TRUNC(rr.start_date_active))
>= TRUNC(rr.start_date_active)
AND TRUNC(P_START_DATE_ACTIVE)
<= NVL(TRUNC(rr.end_date_active),TRUNC(P_START_DATE_ACTIVE)));
SELECT COUNT(1) INTO l_count
FROM jtf_rs_role_relations rrr
WHERE role_relate_id = P_ROLE_RELATE_ID
AND EXISTS
(
SELECT NULL
FROM jtf_rs_role_relations rr,
jtf_rs_roles_b r
WHERE rrr.role_resource_id = rr.role_resource_id
AND rrr.role_relate_id <> rr.role_relate_id
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND rr.delete_flag = 'N'
AND r.role_id = rr.role_id
AND r.role_type_code = 'SALES_COMP'
-- AND NVL(e1,s2) >= s2 AND s1 <= NVL(e2,s1)
-- s1,e1 = IN params, s2,e2 = existing role assignment
AND NVL(TRUNC(P_END_DATE_ACTIVE),TRUNC(rr.start_date_active))
>= TRUNC(rr.start_date_active)
AND TRUNC(P_START_DATE_ACTIVE)
<= NVL(TRUNC(rr.end_date_active),TRUNC(P_START_DATE_ACTIVE))
);
srp_plan_assignment_for_update
(p_role_id => l_role_id,
p_srp_role_id => P_ROLE_RELATE_ID,
p_date_update_only => fnd_api.g_true,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
srp_pmt_plan_asgn_for_update
(p_role_id => l_role_id,
p_srp_role_id => P_ROLE_RELATE_ID,
p_date_update_only => fnd_api.g_true,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
srp_pay_group_asgn_for_update
(p_role_id => l_role_id,
p_srp_role_id => P_ROLE_RELATE_ID,
p_date_update_only => fnd_api.g_true,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
-- insert the period (p_start_date_active, g_start_date_old)
-- which becomes active.
IF (p_start_date_active < g_start_date_old) THEN
IF (p_end_date_active IS NOT NULL
AND p_end_date_active < g_start_date_old) THEN
l_end_date := p_end_date_active;
-- insert the period (g_end_date_old, p_end_date_active)
-- which becomes active.
IF ((p_end_date_active IS NULL AND g_end_date_old IS NOT NULL) OR p_end_date_active > g_end_date_old) THEN
IF (g_end_date_old < p_start_date_active) THEN
l_start_date := p_start_date_active;
END update_res_role_relate_post;
PROCEDURE delete_res_role_relate_pre
(P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
P_DATA OUT NOCOPY VARCHAR2,
P_COUNT OUT NOCOPY NUMBER,
P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
cursor get_role_id is
select rr.role_id
from jtf_rs_role_relations rr,
jtf_rs_roles_b r
where rr.role_relate_id = p_role_relate_id
AND rr.role_id = r.role_id
AND r.role_type_code = 'SALES_COMP'
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND nvl(rr.delete_flag,'N') = 'N';
l_api_name VARCHAR2(30) := 'delete_res_role_relate_pre';
select salesrep_id, org_id
from jtf_rs_salesreps
where resource_id = l_role_resource_id
AND l_role_resource_type = 'RS_INDIVIDUAL'
UNION ALL
select salesrep_id, org_id
from jtf_rs_group_members gm, jtf_rs_salesreps s
where gm.group_member_id = l_role_resource_id
and gm.resource_id = s.resource_id
and delete_flag = 'N'
AND l_role_resource_type = 'RS_GROUP_MEMBER'
UNION ALL
select salesrep_id, org_id
from jtf_rs_team_members tm, jtf_rs_salesreps s
where tm.team_member_id = l_role_resource_id
and tm.team_resource_id = s.resource_id
and resource_type = 'INDIVIDUAL'
and delete_flag = 'N'
AND l_role_resource_type = 'RS_TEAM_MEMBER';
SELECT t.start_date_active, t.end_date_active, rr.role_id,
sr.salesrep_id, tm.team_id, t.team_name
from jtf_rs_team_members tm,
jtf_rs_salesreps sr,
jtf_rs_team_usages tu,
jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_teams_vl t
where rr.role_relate_id = p_role_relate_id
and rr.role_resource_type(+) = 'RS_TEAM_MEMBER'
and tm.resource_type = 'INDIVIDUAL'
and tm.delete_flag = 'N'
and tu.team_id = tm.team_id
and tu.usage = 'SALES_COMP'
and sr.resource_id = tm.team_resource_id
and (sr.org_id is null or sr.org_id = (select org_id from cn_repositories))
and rr.role_resource_id(+) = tm.team_member_id
and rr.delete_flag(+) = 'N'
and rb.role_id(+) = rr.role_id
and rb.role_type_code(+) = 'SALES_COMP'
and t.team_id = tm.team_id;
SELECT r.manager_flag, r.group_id, s.salesrep_id,
r.start_date_active, r.end_date_active
FROM jtf_rs_group_usages u,
jtf_rs_group_mbr_role_vl r,
cn_rs_salesreps s
WHERE r.role_relate_id = p_role_relate_id
AND u.group_id = r.group_id
AND u.usage = 'SALES_COMP'
AND s.resource_id = r.resource_id;
SELECT ct.name name,
ct.comp_team_id team_id,
greatest(r.start_date_active, ct.start_date_active) start_date,
Least(nvl(ct.end_date_active, l_max_date), nvl(r.end_date_active, l_max_date)) end_date
FROM jtf_rs_group_usages u,
jtf_rs_group_mbr_role_vl r,
cn_rs_salesreps s,
jtf_rs_roles_b ro,
cn_srp_comp_teams_v srt,
cn_comp_teams ct
WHERE r.role_relate_id = p_role_relate_id
AND s.salesrep_id = p_salesrep_id
AND u.group_id = r.group_id
AND u.usage = 'SALES_COMP'
AND ro.role_id = r.role_id
AND ro.role_type_code = 'SALES_COMP'
AND s.resource_id = r.resource_id
AND s.salesrep_id = srt.salesrep_id
AND srt.comp_team_id = ct.comp_team_id
AND (r.start_date_active <= ct.start_date_active
or r.start_date_active between ct.start_date_active and nvl (ct.end_date_active, r.start_date_active))
AND nvl(r.end_date_active, ct.start_date_active) >= ct.start_date_active;
select category
from jtf_rs_resource_extns re, jtf_rs_role_relations rr
where re.resource_id = rr.role_resource_id
and rr.role_relate_id = P_ROLE_RELATE_ID;
select rr.role_resource_type, r.role_type_code, r.role_id,
rr.role_resource_id, start_date_active, end_date_active
INTO l_role_resource_type, l_usage, l_role_id,
l_role_resource_id, l_rr_start_date, l_rr_end_date
from jtf_rs_role_relations rr, jtf_rs_roles_b r
where rr.role_relate_id = p_role_relate_id
and rr.role_id = r.role_id;
select count(1) into l_count
from cn_srp_roles sr, cn_srp_payee_assigns spa
where sr.srp_role_id = p_role_relate_id
and spa.payee_id = sr.salesrep_id
and sr.start_date <= nvl(spa.end_date, sr.start_date)
and spa.start_date <= nvl(sr.end_date, spa.start_date);
-- update the sales comp tables
-- we're already in a loop to cycle through all the applicable orgs
-- associated with the salesreps assigned to the given resource
-- clku 3718575
open resource_category_info;
srp_pay_group_asgn_for_delete
(p_role_id => l_role_id,
p_srp_role_id => P_ROLE_RELATE_ID,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
srp_plan_assignment_for_delete
(p_role_id => l_role_id,
p_srp_role_id => P_ROLE_RELATE_ID,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
srp_pmt_plan_asgn_for_delete
(p_role_id => l_role_id,
p_srp_role_id => P_ROLE_RELATE_ID,
x_return_status => P_RETURN_CODE,
p_loading_status => x_loading_status,
x_loading_status => x_loading_status);
l_event_name := 'CHANGE_CP_DELETE_SRP';
l_event_name := 'CHANGE_CP_DELETE_MGR';
PROCEDURE update_res_role_relate_pre
(P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.START_DATE_ACTIVE%TYPE
DEFAULT FND_API.G_MISS_DATE,
P_END_DATE_ACTIVE IN JTF_RS_ROLE_RELATIONS.END_DATE_ACTIVE%TYPE
DEFAULT FND_API.G_MISS_DATE,
P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
P_DATA OUT NOCOPY VARCHAR2,
P_COUNT OUT NOCOPY NUMBER,
P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30) := 'update_res_role_relate_pre';
select salesrep_id, start_date, end_date from cn_srp_payee_assigns
where payee_id = l_payee_id;
select salesrep_id, org_id
from jtf_rs_salesreps
where resource_id = l_role_resource_id
AND l_role_resource_type = 'RS_INDIVIDUAL'
UNION ALL
select salesrep_id, org_id
from jtf_rs_group_members gm, jtf_rs_salesreps s
where gm.group_member_id = l_role_resource_id
and gm.resource_id = s.resource_id
and delete_flag = 'N'
AND l_role_resource_type = 'RS_GROUP_MEMBER'
UNION ALL
select salesrep_id, org_id
from jtf_rs_team_members tm, jtf_rs_salesreps s
where tm.team_member_id = l_role_resource_id
and tm.team_resource_id = s.resource_id
and resource_type = 'INDIVIDUAL'
and delete_flag = 'N'
AND l_role_resource_type = 'RS_TEAM_MEMBER';
SELECT rr.start_date_active, rr.end_date_active, tm.team_id, t.team_name
from jtf_rs_team_members tm,
jtf_rs_salesreps sr,
jtf_rs_team_usages tu,
jtf_rs_role_relations rr,
jtf_rs_roles_b rb,
jtf_rs_teams_vl t
where rr.role_relate_id = p_role_relate_id
and rr.role_resource_type(+) = 'RS_TEAM_MEMBER'
and tm.resource_type = 'INDIVIDUAL'
and tm.delete_flag = 'N'
and tu.team_id = tm.team_id
and tu.usage = 'SALES_COMP'
and sr.resource_id = tm.team_resource_id
and (sr.org_id is null or sr.org_id = (select org_id from cn_repositories))
and rr.role_resource_id(+) = tm.team_member_id
and rr.delete_flag(+) = 'N'
and rb.role_id(+) = rr.role_id
and rb.role_type_code(+) = 'SALES_COMP'
and t.team_id = tm.team_id;
SELECT r.manager_flag, r.group_id, s.salesrep_id, s.resource_id,
r.start_date_active, r.end_date_active, r.role_id
FROM jtf_rs_group_usages u,
jtf_rs_group_mbr_role_vl r,
cn_rs_salesreps s
WHERE r.role_relate_id = p_role_relate_id
AND u.group_id = r.group_id
AND u.usage = 'SALES_COMP'
AND s.resource_id = r.resource_id;
SELECT ct.name name,
ct.comp_team_id team_id,
greatest(l_start_date, ct.start_date_active) start_date,
Least(nvl(ct.end_date_active, l_max_date), nvl(l_end_date, l_max_date)) end_date
FROM jtf_rs_group_usages u,
jtf_rs_group_mbr_role_vl r,
cn_rs_salesreps s,
jtf_rs_roles_b ro,
cn_srp_comp_teams_v srt,
cn_comp_teams ct
WHERE r.role_relate_id = p_role_relate_id
AND s.salesrep_id = p_salesrep_id
AND u.group_id = r.group_id
AND u.usage = 'SALES_COMP'
AND ro.role_id = r.role_id
AND s.resource_id = r.resource_id
AND s.salesrep_id = srt.salesrep_id
AND srt.comp_team_id = ct.comp_team_id
AND (l_start_date <= ct.start_date_active
or l_start_date between ct.start_date_active and nvl (ct.end_date_active, l_start_date));
SELECT salesrep_id, start_date, end_date, role_id
FROM cn_srp_roles
WHERE srp_role_id = p_role_relate_id;
debugmsg('Inside vertical hook update_role_relate_pre');
select rr.role_resource_type, r.role_type_code, r.role_id, rr.role_resource_id
INTO l_role_resource_type, l_usage, l_role_id, l_role_resource_id
from jtf_rs_role_relations rr, jtf_rs_roles_b r
where rr.role_relate_id = p_role_relate_id
and rr.role_id = r.role_id;
-- delete the period (g_start_date_old, p_start_date_active)
-- which is not active any more
IF (p_start_date_active > g_start_date_old) THEN
IF (g_end_date_old IS NOT NULL AND g_end_date_old < p_start_date_active) THEN
l_end_date := g_end_date_old;
-- delete the period (p_end_date_active, g_end_date_old)
-- which is not active any more
IF ((g_end_date_old IS NULL AND p_end_date_active IS NOT NULL)
OR p_end_date_active < g_end_date_old) THEN
IF (p_end_date_active < g_start_date_old) THEN
l_start_date := g_start_date_old;
END update_res_role_relate_pre;
PROCEDURE delete_res_role_relate_post
(P_ROLE_RELATE_ID IN JTF_RS_ROLE_RELATIONS.ROLE_RELATE_ID%TYPE,
P_OBJECT_VERSION_NUM IN JTF_RS_ROLE_RELATIONS.OBJECT_VERSION_NUMBER%TYPE,
P_DATA OUT NOCOPY VARCHAR2,
P_COUNT OUT NOCOPY NUMBER,
P_RETURN_CODE OUT NOCOPY VARCHAR2) IS
BEGIN
p_return_code := fnd_api.g_ret_sts_success;
END delete_res_role_relate_post;