[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_selected_persons g_person_sel_table_type := g_person_sel_table_type();
SELECT txn.transaction_id
FROM ben_transaction txn
WHERE txn.transaction_type = 'CWBMASSNOTIF'
AND txn.attribute1 = v_user_name
AND txn.attribute2 = v_plan_id||','||to_char(v_lf_evt_orcd_date,'yyyy/mm/dd')
AND txn.attribute3 = v_messg_txt_body;
SELECT ppf.full_name
,bg.name
,ppf.employee_number
,ppf.person_id
,null
,ppf.business_group_id
FROM per_all_people_f ppf
, per_person_types ppt
, per_assignments_f paf
, per_business_groups_perf bg
WHERE ppf.person_id=paf.person_id
AND sysdate BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND paf.primary_flag = 'Y'
AND paf.business_group_id = ppf.business_group_id
AND paf.person_id = ppf.person_id
AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppt.person_type_id=ppf.person_type_id
AND ppt.system_person_type = 'EMP'
AND bg.business_group_id = ppf.business_group_id;
SELECT ppf.full_name
,bg.name
,ppf.employee_number
,ppf.person_id
,null
,ppf.business_group_id
,cursor ( select user_name from fnd_user usr
where usr.employee_id = ppf.person_id )
FROM per_all_people_f ppf
,per_business_groups_perf bg
WHERE sysdate BETWEEN NVL(EFFECTIVE_START_DATE,sysdate)
AND NVL(EFFECTIVE_END_DATE,sysdate)
AND ppf.business_group_id = bg.business_group_id;
CURSOR c_user_selection (
v_person_id IN NUMBER
)
IS
SELECT user_name
FROM fnd_user usr
WHERE usr.employee_id = v_person_id;
CURSOR c_person_selection (
v_group_pl_id IN NUMBER
, v_lf_evt_orcd_date IN DATE
, v_target_pop IN VARCHAR2
, v_req_acc_lvl IN VARCHAR2
)
IS
SELECT DISTINCT max(DECODE (ben_cwb_utils.get_profile ('BEN_DISPLAY_EMPLOYEE_NAME'),
'BN', per.brief_name,
'CN', per.custom_name,
per.full_name)) full_name
,max(bg.name)
,max(per.employee_number)
,max(pil.person_id)
,max(pil.per_in_ler_id)
FROM ben_cwb_person_info per
,ben_per_in_ler pil
,ben_cwb_group_hrchy hrchy
,hr_all_organization_units bg
WHERE per.group_pl_id = v_group_pl_id
AND per.lf_evt_ocrd_dt = v_lf_evt_orcd_date
AND pil.per_in_ler_id = per.group_per_in_ler_id
AND bg.organization_id (+) = per.business_group_id
AND hrchy.emp_per_in_ler_id = pil.per_in_ler_id
AND (((v_target_pop IS NOT NULL)
AND (
((v_target_pop = 'ELI')
AND EXISTS( select person_rates.elig_flag from ben_cwb_person_rates person_rates
where person_rates.group_per_in_ler_id = pil.per_in_ler_id
and person_rates.group_pl_id = pil.group_pl_id
AND person_rates.elig_flag = 'Y')
AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
FROM ben_cwb_group_hrchy hr
WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
OR
((v_target_pop = 'EPO')
AND EXISTS( select * from ben_cwb_person_rates person_rates
where person_rates.group_per_in_ler_id = pil.per_in_ler_id
and person_rates.group_pl_id = pil.group_pl_id
and ((person_rates.pay_proposal_id is not null)
or (person_rates.element_entry_value_id is not null)))
AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
FROM ben_cwb_group_hrchy hr
WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
OR
((v_target_pop = 'MAN')
AND (hrchy.lvl_num = 0))
OR
((v_target_pop = 'MFU')
AND (hrchy.lvl_num = 0)
AND (pil.per_in_ler_stat_cd = 'PROCD'))
OR
((v_target_pop = 'MNS')
AND (hrchy.lvl_num = 0)
AND EXISTS(select groups.submit_cd
from ben_cwb_person_groups groups
where groups.group_per_in_ler_id = pil.per_in_ler_id
and groups.group_pl_id = pil.group_pl_id
and groups.group_oipl_id = -1
and groups.submit_cd = 'NS'))
OR
((v_target_pop = 'MOB')
AND (hrchy.lvl_num = 0)
AND EXISTS(select groups.submit_cd
from ben_cwb_person_groups groups
where groups.group_per_in_ler_id = pil.per_in_ler_id
and groups.group_pl_id = pil.group_pl_id
and groups.group_oipl_id = -1
and groups.submit_cd = 'SU'
and groups.approval_cd = 'AP'))
OR
((v_target_pop = 'MWA')
AND (hrchy.lvl_num = 0)
AND EXISTS(select groups.approval_cd
from ben_cwb_person_groups groups
where groups.group_per_in_ler_id = pil.per_in_ler_id
and groups.group_pl_id = pil.group_pl_id
and groups.group_oipl_id = -1
and nvl(groups.approval_cd,'NULL') <> 'AP'))
OR
((v_target_pop = 'MWB')
AND (hrchy.lvl_num = 0)
AND EXISTS(select groups.submit_cd
from ben_cwb_person_groups groups
where groups.group_per_in_ler_id = pil.per_in_ler_id
and groups.group_pl_id = pil.group_pl_id
and groups.group_oipl_id = -1
and (groups.dist_bdgt_val is not null
or groups.ws_bdgt_val is not null)))
OR
((v_target_pop = 'MFX')
AND (hrchy.lvl_num = 0)
AND EXISTS (select ler.per_in_ler_stat_cd
from ben_per_in_ler ler
,ben_cwb_group_hrchy hier
where hier.mgr_per_in_ler_id = pil.per_in_ler_id
and ler.per_in_ler_id = hier.emp_per_in_ler_id
and ler.per_in_ler_stat_cd = 'PROCD'))
OR
((v_target_pop = 'MEL')
AND (hrchy.lvl_num = 0)
AND EXISTS (select rts.elig_flag
from ben_cwb_person_rates rts
,ben_cwb_group_hrchy hier
where hier.mgr_per_in_ler_id = hrchy.mgr_per_in_ler_id
and rts.group_per_in_ler_id = hier.emp_per_in_ler_id
and rts.elig_flag = 'Y'
and hier.lvl_num <> 0))
OR
((v_target_pop = 'MWT')
AND (hrchy.lvl_num = 0)
AND NOT EXISTS (select tsk.task_id
from ben_cwb_person_tasks tsk
where tsk.group_per_in_ler_id = hrchy.mgr_per_in_ler_id
and tsk.status_cd in ('IP','NA','CO'))
)
)
)
OR
((v_target_pop IS NULL)
AND (hrchy.lvl_num = (SELECT MAX (lvl_num)
FROM ben_cwb_group_hrchy hr
WHERE hr.emp_per_in_ler_id = hrchy.emp_per_in_ler_id)))
)
AND (((v_req_acc_lvl IS NOT NULL)
AND
(
((v_req_acc_lvl = 'AL')
AND EXISTS (select gr.access_cd
from ben_cwb_person_groups gr
, ben_cwb_group_hrchy hr
where hr.emp_per_in_ler_id = pil.per_in_ler_id
and hr.lvl_num = 0
and gr.group_per_in_ler_id = hr.emp_per_in_ler_id
and gr.group_pl_id = pil.group_pl_id
and gr.group_oipl_id = -1
and gr.access_cd IN ('UP','RO','NA')))
OR
((v_req_acc_lvl = 'FR')
AND EXISTS (select gr.access_cd
from ben_cwb_person_groups gr
, ben_cwb_group_hrchy hr
where hr.emp_per_in_ler_id = pil.per_in_ler_id
and hr.lvl_num = 0
and gr.group_per_in_ler_id = pil.per_in_ler_id
and gr.group_pl_id = pil.group_pl_id
and gr.group_oipl_id = -1
and gr.access_cd IN ('UP','RO')))
OR
((v_req_acc_lvl = 'FU')
AND EXISTS (select gr.access_cd
from ben_cwb_person_groups gr
, ben_cwb_group_hrchy hr
where hr.emp_per_in_ler_id = pil.per_in_ler_id
and hr.lvl_num = 0
and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and gr.group_pl_id = pil.group_pl_id
and gr.group_oipl_id = -1
and gr.access_cd IN ('UP')))
OR
((v_req_acc_lvl = 'NA')
AND EXISTS (select gr.access_cd
from ben_cwb_person_groups gr
, ben_cwb_group_hrchy hr
where hr.emp_per_in_ler_id = pil.per_in_ler_id
and hr.lvl_num = 0
and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and gr.group_pl_id = pil.group_pl_id
and gr.group_oipl_id = -1
and gr.access_cd IN ('NA')))
OR
((v_req_acc_lvl = 'RO')
AND EXISTS (select gr.access_cd
from ben_cwb_person_groups gr
, ben_cwb_group_hrchy hr
where hr.emp_per_in_ler_id = pil.per_in_ler_id
and hr.lvl_num = 0
and gr.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and gr.group_pl_id = pil.group_pl_id
and gr.group_oipl_id = -1
and gr.access_cd IN ('RO')))
)
)
OR (v_req_acc_lvl IS NULL))
group by per.person_id;
SELECT ppt.system_person_type
FROM per_person_types ppt
,per_all_people_f ppf
WHERE ppf.person_id = v_person_id
AND sysdate BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppt.person_type_id = ppf.person_type_id;
SELECT info.assignment_id
FROM ben_cwb_person_info info
,per_assignments_f paf
WHERE info.group_per_in_ler_id = v_group_per_in_ler_id
AND paf.assignment_id = info.assignment_id
AND sysdate BETWEEN paf.effective_start_date AND paf.effective_end_date;
FUNCTION check_selection_rule(
p_person_selection_rule_id IN NUMBER
,p_person_id IN NUMBER
,p_business_group_id IN NUMBER
,p_effective_date IN DATE
,p_input1 in varchar2 default null -- Bug 5331889
,p_input1_value in varchar2 default null)
RETURN BOOLEAN IS
--
l_outputs ff_exec.outputs_t;
l_package VARCHAR2(80) := g_package || '.check_selection_rule';
IF p_person_selection_rule_id IS NULL THEN
--
RETURN TRUE;
benutils.formula(p_formula_id => p_person_selection_rule_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_assignment_id => l_assignment_id
,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
,p_param1_value => to_char(p_person_id)
,p_param2 => p_input1
,p_param2_value => p_input1_value);
fnd_message.set_token('RL','person_selection_rule_id :'||p_person_selection_rule_id);
END check_selection_rule;
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = fnd_global.user_id;
SELECT name
,yr_perd_start_dt
,yr_perd_end_dt
,wthn_yr_start_dt
,wthn_yr_end_dt
INTO l_plan_name
,l_yr_perd_start_dt
,l_yr_perd_end_dt
,l_wthn_yr_start_dt
,l_wthn_yr_end_dt
FROM ben_cwb_pl_dsgn
WHERE pl_id = p_pl_id
AND oipl_id = -1
AND lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
select BEN_CWB_WF_NTF_S.NEXTVAL into l_wf_key from dual;
select BEN_TRANSACTION_S.NEXTVAL into l_transaction_key from dual;
insert into ben_transaction
(transaction_id ,transaction_type
,attribute1 ,attribute2
,attribute3 ,attribute4
,attribute5 ,attribute6
,attribute7 ,attribute8
,attribute9 ,attribute10
,attribute11 ,attribute12
,attribute13 ,attribute14
,attribute15 ,attribute16
,attribute17 ,attribute18
,attribute19 ,attribute20
,attribute21 ,attribute22
,attribute23 ,attribute24
,attribute25 ,attribute26
,attribute27 ,attribute28
,attribute29 ,attribute30
,attribute31 ,attribute32
,attribute33 ,attribute34
,attribute35 ,attribute36
,attribute37 ,attribute38
,attribute39 ,attribute40)
values
(l_transaction_key ,'CWBMASSNOTIF'
,l_trans_rec.attribute1 ,l_trans_rec.attribute2
,l_trans_rec.attribute3 ,l_trans_rec.attribute4
,l_trans_rec.attribute5 ,l_trans_rec.attribute6
,l_trans_rec.attribute7 ,l_trans_rec.attribute8
,l_trans_rec.attribute9 ,l_trans_rec.attribute10
,l_trans_rec.attribute11 ,l_trans_rec.attribute12
,l_trans_rec.attribute13 ,l_trans_rec.attribute14
,l_trans_rec.attribute15 ,l_trans_rec.attribute16
,l_trans_rec.attribute17 ,l_trans_rec.attribute18
,l_trans_rec.attribute19 ,l_trans_rec.attribute20
,l_trans_rec.attribute21 ,l_trans_rec.attribute22
,l_trans_rec.attribute23 ,l_trans_rec.attribute24
,l_trans_rec.attribute25 ,l_trans_rec.attribute26
,l_trans_rec.attribute27 ,l_trans_rec.attribute28
,l_trans_rec.attribute29 ,l_trans_rec.attribute30
,l_trans_rec.attribute31 ,l_trans_rec.attribute32
,l_trans_rec.attribute33 ,l_trans_rec.attribute34
,l_trans_rec.attribute35 ,l_trans_rec.attribute36
,l_trans_rec.attribute37 ,l_trans_rec.attribute38
,l_trans_rec.attribute39 ,l_trans_rec.attribute40);
,p_person_selection_rule_id in number default null
,p_include_cwb_link in varchar2 default 'N'
,p_resend_if_prev_sent in varchar2 default 'N'
,p_mail_to_user in varchar2 default null
,p_withhold_notifn in varchar2 default 'N'
)
is
ps_rec person_info;
WRITE ('p_person_selection_rule_id - ' || p_person_selection_rule_id);
, p_program_update_date => SYSDATE/*
, p_bft_attribute1 => l_process_compents
, p_bft_attribute3 => p_employees_in_bg
, p_bft_attribute4 => p_manager_id*/
, p_bft_attribute30 => 'N'
);
elsif (p_person_selection_rule_id is not null) then
WRITE('STATUS - USERNAME - NAME - BUSINESS GROUP - EMP.NO. - PERSON ID - PER IN LER ID');
IF (check_selection_rule(p_person_selection_rule_id => p_person_selection_rule_id
,p_person_id => ps_rec.person_id
,p_business_group_id => l_business_group_id
,p_effective_date => sysdate)) then
/*WRITE('===========================================================================');
OPEN c_user_selection (ps_rec.person_id);
FETCH c_user_selection bulk collect into v_users;
CLOSE c_user_selection;
v_users.delete;
OPEN c_person_selection (p_pl_id
,l_lf_evt_ocrd_dt
,p_target_pop
,p_req_acc_lvl
);
FETCH c_person_selection
INTO ps_rec.full_name,
ps_rec.name,
ps_rec.employee_number,
ps_rec.person_id,
ps_rec.per_in_ler_id;
EXIT WHEN c_person_selection%NOTFOUND;
OPEN c_user_selection (ps_rec.person_id);
FETCH c_user_selection bulk collect into v_users;
insert into nt values (v_users(indx));
CLOSE c_user_selection;
CLOSE c_person_selection;