[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
role_id,
greatest(start_date_active, p_srp.start_date) start_date,
least(nvl(end_date_active, p_srp.end_date), nvl(p_srp.end_date, end_date_active)) end_date,
manager_flag
FROM cn_srp_comp_groups_v
WHERE comp_group_id = p_srp.group_id
AND salesrep_id = p_srp.salesrep_id
AND org_id = p_org_id
AND (end_date_active IS NULL OR p_srp.start_date <= end_date_active)
AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_date_active);
SELECT
salesrep_id,
role_id,
manager_flag,
greatest(start_date_active, p_group.start_date) start_date,
least(nvl(end_date_active, p_group.end_date), nvl(p_group.end_date, end_date_active)) end_date
FROM cn_srp_comp_groups_v
WHERE comp_group_id = p_group.group_id
AND org_id = p_org_id
AND (end_date_active IS NULL OR p_group.start_date <= end_date_active)
AND (p_group.end_date IS NULL OR p_group.end_date >= start_date_active);
SELECT
comp_group_id,
role_id,
manager_flag,
greatest(start_date_active, p_srp.start_date) start_date,
least(nvl(end_date_active, p_srp.end_date), nvl(p_srp.end_date, end_date_active)) end_date
FROM cn_srp_comp_groups_v
WHERE salesrep_id = p_srp.salesrep_id
AND org_id = p_org_id
AND (end_date_active IS NULL OR p_srp.start_date <= end_date_active)
AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_date_active);
SELECT
parent_group_id,
denorm_level,
greatest(start_date_active, p_group.start_date) start_date,
least(nvl(end_date_active, p_group.end_date), nvl(p_group.end_date, end_date_active)) end_date
FROM cn_groups_denorm_v
WHERE group_id = p_group.group_id
AND (end_date_active IS NULL OR p_group.start_date <= end_date_active)
AND (p_group.end_date IS NULL OR p_group.end_date >= start_date_active);
SELECT
group_id,
denorm_level,
greatest(start_date_active, p_group.start_date) start_date,
least(nvl(end_date_active, p_group.end_date), nvl(p_group.end_date, end_date_active)) end_date
FROM cn_groups_denorm_v
WHERE parent_group_id = p_group.group_id
AND (end_date_active IS NULL OR p_group.start_date <= end_date_active)
AND (p_group.end_date IS NULL OR p_group.end_date >= start_date_active);
SELECT
salesrep_id,
greatest(start_date_active, p_srp.start_date) start_date,
least(nvl(end_date_active, p_srp.end_date), nvl(p_srp.end_date, end_date_active)) end_date
FROM cn_srp_comp_groups_v
WHERE comp_group_id = p_srp.group_id
AND salesrep_id <> p_srp.salesrep_id
AND manager_flag = 'Y'
AND org_id = p_org_id
AND (end_date_active IS NULL OR p_srp.start_date <= end_date_active)
AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_date_active);
SELECT salesrep_id,
greatest(start_date_active, p_srp.start_date ) start_date,
least(nvl(end_date_active, p_srp.end_date), nvl(p_srp.end_date, end_date_active))end_date
FROM
(SELECT jrs.salesrep_id,
rrl.START_DATE_ACTIVE ,
rrl.END_DATE_ACTIVE,
jrs.org_id,
mem.resource_id,
mem.group_id comp_group_id,
rol.manager_flag
FROM jtf_rs_group_members mem,
jtf_rs_role_relations rrl,
jtf_rs_roles_b rol,
jtf_rs_group_usages usg,
jtf_rs_salesreps_mo_v jrs
WHERE mem.group_member_id = rrl.role_resource_id
AND NVL(rrl.delete_flag, 'N') <> 'Y'
AND NVL(mem.delete_flag, 'N') <> 'Y'
AND rrl.role_resource_type = 'RS_GROUP_MEMBER'
AND rrl.role_id = rol.role_id
AND rol.role_type_code = 'SALES_COMP'
AND (rol.member_flag='Y'
OR rol.manager_flag = 'Y')
AND mem.group_id = usg.group_id
AND usg.usage ='SALES_COMP'
AND jrs.resource_id = mem.resource_id)
WHERE comp_group_id = p_srp.group_id
AND salesrep_id <> p_srp.salesrep_id
AND manager_flag = 'Y'
AND org_id = p_org_id
AND (end_date_active IS NULL OR p_srp.start_date <= end_date_active)
AND (p_srp.end_date IS NULL OR p_srp.end_date >= start_date_active);
SELECT
salesrep_id,
greatest(start_date_active, p_start_date) start_date,
least(nvl(end_date_active, p_end_date), nvl(p_end_date, end_date_active)) end_date
FROM cn_srp_comp_groups_v
WHERE comp_group_id = p_group_id
AND org_id = p_org_id
AND (end_date_active IS NULL OR p_start_date <= end_date_active)
AND (p_end_date IS NULL OR p_end_date >= start_date_active);
SELECT
salesrep_id,
greatest(start_date_active, p_start_date) start_date,
least(nvl(end_date_active, p_end_date), nvl(p_end_date, end_date_active)) end_date
FROM cn_srp_comp_groups_v
WHERE comp_group_id = p_group_id
AND org_id = p_org_id
AND (end_date_active IS NULL OR p_start_date <= end_date_active)
AND (p_end_date IS NULL OR p_end_date >= start_date_active);
SELECT manager_flag
FROM cn_srp_comp_groups_v
WHERE comp_group_id = p_srp.group_id
AND org_id = p_org_id
AND salesrep_id = p_srp.salesrep_id
AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
SELECT DISTINCT comp_group_id
FROM cn_srp_comp_groups_v
WHERE salesrep_id = p_srp.salesrep_id
AND org_id = p_org_id
AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
SELECT DISTINCT salesrep_id
FROM cn_srp_comp_groups_v
WHERE comp_group_id = p_group_id
AND salesrep_id <> p_srp.salesrep_id
AND org_id = p_org_id
AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);
SELECT DISTINCT salesrep_id
FROM cn_srp_comp_groups_v srp,
cn_groups_denorm_v hier
WHERE srp.comp_group_id = hier.group_id
AND srp.org_id = p_org_id
AND hier.parent_group_id = p_group_id
AND (p_srp.end_date IS NULL OR hier.start_date_active <= p_srp.end_date)
AND (hier.end_date_active IS NULL OR hier.end_date_active >= p_srp.start_date)
AND (p_srp.end_date IS NULL OR srp.start_date_active <= p_srp.end_date)
AND (srp.end_date_active IS NULL OR srp.end_date_active >= p_srp.start_date);
SELECT COUNT(*)
INTO l_temp
FROM cn_srp_comp_groups_v
WHERE salesrep_id = p_srp.salesrep_id
AND org_id = p_org_id
AND comp_group_id = eachgroup.comp_group_id
AND manager_flag = 'Y'
AND (p_srp.end_date IS NULL OR start_date_active <= p_srp.end_date)
AND (end_date_active IS NULL OR end_date_active >= p_srp.start_date);