[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
hr1.emp_num tsr_emp_no,
hr1.name tsr_name,
hr1.srp_id tsr_srp_id
FROM
cn_srp_hr_data hr1
WHERE
NOT EXISTS (
SELECT 1
from jtf_rs_groups_vl jg,
jtf_rs_role_relations jrr,
jtf_rs_salesreps jrs,
jtf_rs_roles_b jr,
jtf_rs_group_mbr_role_vl jgm,
jtf_rs_group_usages u
WHERE jg.group_id = jgm.group_id
and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
and jrs.resource_id = jgm.resource_id
and u.group_id = jgm.group_id
and u.usage = 'SF_PLANNING'
and jrr.role_resource_type = 'RS_INDIVIDUAL'
and jrr.role_resource_id = jrs.resource_id
and jrr.role_id = jgm.role_id and jrr.role_id = jr.role_id
and jr.role_type_code = 'SALES_COMP'
and TRUNC(p_period_id) between trunc(jrr.start_date_active)
and NVL(TRUNC(jrr.end_date_active), TRUNC(p_period_id))
and jrr.delete_flag <> 'Y' and
jrr.start_date_active <= jgm.start_date_active
and (jrr.end_date_active is null
or jrr.end_date_active >= jgm.end_date_active) AND jrs.SALESREP_ID > 0
AND NVL(jrs.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND jrs.salesrep_id = P_SRP_ID
AND jg.group_id = P_GROUP_ID
)
and hr1.srp_id = P_SRP_ID
UNION ALL
SELECT
hr1.emp_num tsr_emp_no,
hr1.name tsr_name,
hr1.srp_id tsr_srp_id
FROM
cn_srp_hr_data hr1
WHERE
EXISTS (
SELECT 1
from jtf_rs_groups_vl jg,
jtf_rs_role_relations jrr,
jtf_rs_salesreps jrs,
jtf_rs_roles_b jr,
jtf_rs_group_mbr_role_vl jgm,
cn_srp_role_dtls srd,
jtf_rs_group_usages u
WHERE jg.group_id = jgm.group_id
and (jgm.manager_flag = 'Y' or jgm.member_flag = 'Y')
and jrs.resource_id = jgm.resource_id
and u.group_id = jgm.group_id
and u.usage = 'SF_PLANNING'
and jrr.role_resource_type = 'RS_INDIVIDUAL'
and jrr.role_resource_id = jrs.resource_id
and jrr.role_id = jgm.role_id and jrr.role_id = jr.role_id
and jr.role_type_code = 'SALES_COMP'
AND TRUNC(p_period_id) between trunc(jrr.start_date_active)
AND NVL(TRUNC(jrr.end_date_active), TRUNC(p_period_id))
and jrr.delete_flag <> 'Y' and
jrr.start_date_active <= jgm.start_date_active
and (jrr.end_date_active is null
or jrr.end_date_active >= jgm.end_date_active) AND jrs.SALESREP_ID > 0
AND NVL(jrs.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND jrs.salesrep_id = P_SRP_ID
AND jg.group_id = P_GROUP_ID
AND srd.srp_role_id = jrr.role_relate_id
AND srd.role_model_id is null -- "CHANGED FOR MODELING IMPACT"
AND srd.job_title_id = -99
)
/*
AND NOT EXISTS --- Check this
(
SELECT s.salesrep_id
from jtf_rs_role_relations rr,
jtf_rs_salesreps s,
cn_srp_role_dtls srd,
jtf_rs_roles_b r
WHERE rr.role_resource_id = s.resource_id
and rr.role_relate_id = srd.srp_role_id
and rr.role_resource_type = 'RS_INDIVIDUAL'
and rr.delete_flag = 'N'
AND NVL(S.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),
' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND s.salesrep_id = P_SRP_ID
AND TRUNC(p_period_id) between trunc(rr.start_date_active)
AND NVL(TRUNC(rr.end_date_active), TRUNC(p_period_id))
AND srd.job_title_id <> -99
AND r.role_id = rr.role_id
AND r.role_type_code = 'SALES_COMP'
)
*/
AND hr1.SRP_ID = P_SRP_ID
ORDER BY
tsr_name, tsr_emp_no;
SELECT
emp_num mgr_emp_no,
name mgr_name,
srp_id tsr_mgr_id
FROM
cn_srp_hr_data
WHERE srp_id = P_MGR_SRP_ID;