[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 08-Jun-02 pabodla 115.29 Do not select the contingent worker
* assignment when assignment data is
* fetched.
* 18-Jun-02 ikasire 115.30 Bug 2394141 fixes
* 26-Dec-02 rpillay 115.33 NOCOPY changes
* 13-Feb-03 stee 115.34 HR MLS changes.
* 14-Feb-03 tmathers 115.35 Added whenever oserror.
* 02-Jun-03 glingapp 115.36 bug 2978945 Added function rows_exist. This is
* called to check for child records of derived
* factors.
* 30-Jun-03 vsethi 115.37 Changed reference for table ben_rptg_grp
* MLS compliant view ben_rptg_grp_v
* 12-Jan-04 vvprabhu 115.38 Changed the calls to dbms_describe.dbms_procedure
* to hr_general.describe_procedure in procedure
* get_rpt_header
* 21-Jun-04 kmahendr 115.39 Corrected Prompt for legal Entity.
* 20-Aug-04 nhunur 115.40 Added a procedure for person selection rule
* with proper error handling.
* 02-Nov-04 abparekh 115.41 Bug 3517604 - Added p_date_From to procedure
* standard_header
* 03-Nov-06 swjain 115.42 Bug 5331889 - passed person_id as input param
* in person_selection_rule and added input1 as
* additional param for future use
* 16-aug-06 gsehgal 115.43 Bug: 5450842 -- now p_mode will not be printed when
* passed as null
* 12-Dec-06 nkkrishn 115.44 5643310 - Invalid Person Records in Person Seleciton
* Rule will now be logged instead of erroring out the
* entire process
* 22-Jun-07 nhunur 115.45 perf changes
* 09-Aug-07 vvprabhu 115.23 Bug 5857493 - added g_audit_flag to
* control person selection rule error logging
* 22-Feb-2008 rtagarra 115.24 Bug 6840074
* ===========================================================================
*/
--
-- Global variables declaration.
--
g_package varchar2(30) := 'ben_batch_utils.';
g_cache_comp.delete;
Select a.pgm_id, a.pl_typ_id, a.pl_id, a.oipl_id, b.opt_id
,a.bnft_amt, a.uom
,a.enrt_cvg_strt_dt, a.enrt_cvg_thru_dt
From ben_prtt_enrt_rslt_f a
,ben_oipl_f b
Where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and a.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
a.effective_start_date and a.effective_end_date
and a.oipl_id = b.oipl_id(+)
and p_effective_date between
nvl(b.effective_start_date,p_effective_date)
and nvl(b.effective_end_date,p_effective_date)
;
l_upd := 'Updated ';
l_ins := 'Inserted ';
g_pgm_tbl.delete;
g_pl_tbl.delete;
g_pl_typ_tbl.delete;
g_opt_tbl.delete;
g_proc_info.num_persons_selected := 0;
g_processes_tbl.delete;
Select pgm_id, name
From ben_pgm_f
Where pgm_id = p_pgm_id
And business_group_id = p_business_group_id
And p_effective_date between
effective_start_date and effective_end_date
;
Select pl_typ_id, name
From ben_pl_typ_f
Where pl_typ_id = p_pl_typ_id
And business_group_id = p_business_group_id
And p_effective_date between
effective_start_date and effective_end_date
;
Select pl_id, name
From ben_pl_f
Where pl_id = p_pl_id
And business_group_id = p_business_group_id
And p_effective_date between
effective_start_date and effective_end_date
;
Select oipl.oipl_id, opt.opt_id, opt.name
From ben_oipl_f oipl
,ben_opt_f opt
Where oipl.oipl_id = p_oipl_id
And oipl.opt_id = opt.opt_id
And oipl.business_group_id = p_business_group_id
And opt.business_group_id = p_business_group_id
And p_effective_date between
opt.effective_start_date and opt.effective_end_date
And p_effective_date between
oipl.effective_start_date and oipl.effective_end_date
;
,p_person_selected in number
,p_business_group_id in number default NULL
,p_non_person_cd in varchar2 default null
) is
cursor c_actions is
Select count(*) amount,action_status_cd
from ben_person_actions act
where act.benefit_action_id = p_benefit_action_id
and act.action_status_cd in ('P','E','U')
and nvl(act.non_person_cd,'x') = nvl(p_non_person_cd,'x')
group by action_status_cd
;
g_proc_info.num_persons_selected := p_person_selected;
,P_PER_SLCTD => g_proc_info.num_persons_selected
,P_PER_PROC => g_proc_info.num_persons_processed
,P_PER_UNPROC => g_proc_info.num_persons_unprocessed
,P_PER_PROC_SUCC => g_proc_info.num_persons_processed_succ
,P_PER_ERR => g_proc_info.num_persons_errored
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_OBJECT_VERSION_NUMBER=> l_object_version_number
);
select ppf.full_name
,ppf.date_of_birth
,ppf.date_of_death
,ppf.benefit_group_id
,bng.name
,pps.date_start
,pps.adjusted_svc_date
,pad.postal_code
,ppf.national_identifier
,hao.name
From per_all_people_f ppf
,per_periods_of_service pps
,per_addresses pad
,hr_all_organization_units_vl hao
,ben_benfts_grp bng
Where ppf.person_id = p_person_id
And ppf.business_group_id = p_business_group_id
And ppf.business_group_id = hao.organization_id
And pps.person_id (+) = ppf.person_id
And nvl(pps.business_group_id(+),ppf.business_group_id)
= ppf.business_group_id
And pad.person_id (+) = ppf.person_id
And nvl(pad.business_group_id(+),ppf.business_group_id)
= ppf.business_group_id
And nvl(pad.primary_flag,'Y') = 'Y'
And p_effective_date between
nvl(pad.date_from(+),p_effective_date)
and nvl(pad.date_to(+),p_effective_date)
And bng.benfts_grp_id (+) = ppf.benefit_group_id
And nvl(bng.business_group_id(+),ppf.business_group_id)
= ppf.business_group_id
And p_effective_date between
ppf.effective_start_date and ppf.effective_end_date
;
select paf.assignment_id
,pbv.value
,pat.per_system_status
,paf.grade_id
,paf.job_id
,paf.pay_basis_id
,paf.payroll_id
,paf.location_id
,paf.organization_id
,paf.normal_hours
,paf.frequency
,paf.bargaining_unit_code
,paf.labour_union_member_flag
,paf.hourly_salaried_code
,paf.assignment_status_type_id
,paf.change_reason
,paf.employment_category
,ori.org_information1
,oru.organization_id
,oru.name
,loc.location_code
,ppf.payroll_name
From per_all_assignments_f paf
,per_assignment_status_types pat
,hr_organization_information ori
,hr_all_organization_units_vl oru
,hr_locations loc
,pay_payrolls_f ppf
,per_assignment_budget_values_f pbv
Where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
and paf.primary_flag = 'Y'
and paf.business_group_id = p_business_group_id
and paf.location_id = loc.location_id (+)
and oru.organization_id (+) = paf.organization_id
and ori.organization_id (+) = oru.organization_id
and ori.org_information1(+) = 'HR_LEGAL'
and ppf.payroll_id (+) = paf.payroll_id
and p_effective_date between
nvl(ppf.effective_start_date,p_effective_date)
and nvl(ppf.effective_end_date,p_effective_date)
and pat.assignment_status_type_id (+) = paf.assignment_status_type_id
and pbv.assignment_id(+) = paf.assignment_id
and pbv.unit(+) = 'FTE'
and p_effective_date between
nvl(pbv.effective_start_date,p_effective_date)
and nvl(pbv.effective_end_date,p_effective_date)
and p_effective_date between
paf.effective_start_date and paf.effective_end_date
;
select tpe.start_date
,tpe.end_date
From per_time_periods tpe
,per_all_assignments_f paf
Where paf.person_id = p_person_id
and paf.primary_flag = 'Y'
and paf.assignment_type <> 'C'
and paf.business_group_id = p_business_group_id
and p_effective_date between
paf.effective_start_date and paf.effective_end_date
and tpe.payroll_id (+) = paf.payroll_id
and p_effective_date between
nvl(tpe.start_date,p_effective_date)
and nvl(tpe.end_date,p_effective_date)
;
select per.person_type_id
,ppt.user_person_type
,ppt.system_person_type
From per_person_type_usages_f per
,per_person_types ppt
Where per.person_id = p_person_id
and p_effective_date between
per.effective_start_date and per.effective_end_date
and per.person_type_id = ppt.person_type_id
order by decode(ppt.system_person_type,'EMP',1,2)
;
select tpe.start_date
,tpe.end_date
from per_time_periods tpe
,per_all_assignments_f asg
where tpe.payroll_id = asg.payroll_id
and asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and tpe.start_date > p_effective_date
order by tpe.start_date
;
select sum(pab.value)
from per_all_people_f ppf
,per_all_assignments_f paf
,per_assignment_budget_values_f pab
where ppf.person_id = p_person_id
and paf.assignment_type <> 'C'
and ppf.business_group_id = p_business_group_id
and p_effective_date between
ppf.effective_start_date and ppf.effective_end_date
and ppf.person_id = paf.person_id
and paf.business_group_id = ppf.business_group_id
and p_effective_date between
paf.effective_start_date and paf.effective_end_date
and pab.business_group_id = paf.business_group_id
and pab.assignment_id = paf.assignment_id
and pab.unit = 'FTE'
and p_effective_date between
pab.effective_start_date and pab.effective_end_date
;
g_cache_person_types.delete;
,p_person_selection_rule_id in number default hr_api.g_number
,p_comp_selection_rule_id in number default hr_api.g_number
,p_enrt_perd_id in number default hr_api.g_number
,p_derivable_factors in varchar2 default hr_api.g_varchar2
,p_audit_log in varchar2 default hr_api.g_varchar2
) is
l_proc varchar2(80) := g_package||'.print_parameters';
l_actn := 'Printting p_person_selection_rule_id...';
If (nvl(p_person_selection_rule_id,-1) <> hr_api.g_number) then
write(p_text => 'Person Selection Rule :'||
benutils.iftrue
(p_expression => p_person_selection_rule_id is null
,p_true => 'None'
,p_false => p_person_selection_rule_id));
l_actn := 'Printting p_comp_selection_rule_id...';
If (nvl(p_comp_selection_rule_id,-1) <> hr_api.g_number) then
write(p_text => 'Comp Object Selection Rule :'||
benutils.iftrue
(p_expression => p_comp_selection_rule_id is null
,p_true => 'None'
,p_false => p_comp_selection_rule_id));
Function person_selection_rule
(p_person_id in Number
,p_business_group_id in Number
,p_person_selection_rule_id in Number
,p_effective_date in Date
,p_batch_flag in Boolean default FALSE
,p_input1 in varchar2 default null -- Bug 5331889
,p_input1_value in varchar2 default null
) return char is
Cursor c1 is
Select assignment_id
From per_assignments_f paf
Where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
And paf.primary_flag = 'Y'
And paf.business_group_id = p_business_group_id
And p_effective_date between
paf.effective_start_date and paf.effective_end_date;
Select assignment_id
From per_all_assignments_f paf
Where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
And paf.primary_flag = 'Y'
And paf.business_group_id = p_business_group_id
And p_effective_date between
paf.effective_start_date and paf.effective_end_date;
l_proc varchar2(80) := g_package||'.person_selection_rule';
(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);
'person_selection_rule_id :'||p_person_selection_rule_id);
End person_selection_rule;
Select null
From fnd_concurrent_requests fnd
Where fnd.phase_code <> 'C'
And fnd.request_id = p_request_id;
Select act.person_action_id
,act.person_id
,act.ler_id
From ben_person_actions act
Where act.action_status_cd <> 'P'
and nvl(act.non_person_cd,'x') = nvl(p_non_person_cd,'x')
and act.benefit_action_id = p_benefit_action_id
;
l_actn := 'Calling ben_benmngle_purge.delete_batch_range_rows...';
ben_benmngle_purge.delete_batch_range_rows
(p_benefit_action_id => p_benefit_action_id,
p_rows => l_rows);
Select count(*) amount, rep.rep_typ_cd
from ben_reporting rep
,ben_benefit_actions bft
where bft.benefit_action_id = rep.benefit_action_id
and bft.request_id = p_concurrent_request_id
and rep.rep_typ_cd in (p_cd_1, p_cd_2, p_cd_3, p_cd_4, p_cd_5
,p_cd_6, p_cd_7, p_cd_8, p_cd_9, p_cd_10)
group by rep_typ_cd;
select upper(c.execution_file_name)
from fnd_concurrent_requests a
,fnd_concurrent_programs b
,fnd_executables c
where a.request_id = p_concurrent_request_id
and a.concurrent_program_id = b.concurrent_program_id
and b.application_id = 805
and c.application_id = 805
and b.executable_id = c.executable_id
;
p_comp_object_selection_rule out nocopy varchar2,
p_person_selection_rule out nocopy varchar2,
p_life_event_reason out nocopy varchar2,
p_organization out nocopy varchar2,
p_postal_zip_range out nocopy varchar2,
p_reporting_group out nocopy varchar2,
p_plan_type out nocopy varchar2,
p_option out nocopy varchar2,
p_eligibility_profile out nocopy varchar2,
p_variable_rate_profile out nocopy varchar2,
p_legal_entity out nocopy varchar2,
p_payroll out nocopy varchar2,
p_debug_message out nocopy varchar2,
p_location out nocopy varchar2,
p_audit_log out nocopy varchar2,
p_benfts_group out nocopy varchar2,
p_date_from out nocopy date, /* Bug 3517604 */
p_status out nocopy varchar2) is
--
l_proc varchar2(80) := g_package||'.standard_header';
select bft.process_date,
hr.meaning,
hr1.meaning,
hr2.meaning,
nvl(ppf.full_name,l_all),
nvl(ppt.user_person_type,l_all),
nvl(pgm.name,l_all),
pbg.name,
nvl(pln.name,l_all),
decode(hr5.meaning,
null,
l_all,
hr5.meaning||
' '||
pln2.name||
' '||
pgm2.name||
' '||
epo.strt_dt||
' '||
epo.end_dt),
hr3.meaning,
hr4.meaning,
nvl(ff.formula_name,l_none),
nvl(ff2.formula_name,l_none),
nvl(ler.name,l_all),
nvl(org.name,l_all),
decode(rzr.from_value||'-'||rzr.to_value,
'-',
l_all,
rzr.from_value||'-'||rzr.to_value),
nvl(bnr.name,l_all),
nvl(ptp.name,l_all),
nvl(opt.name,l_all),
nvl(elp.name,l_all),
nvl(vpf.name,l_all),
nvl(tax.name,l_all),
nvl(pay.payroll_name,l_all),
decode(debug_messages_flag, 'Y', 'Yes', 'N', 'No', l_all) dg_msg,
nvl(lc.location_code, l_all),
decode(audit_log_flag, 'Y', 'Yes', 'N', 'No', l_all) audit_log,
nvl(bnfg.name,l_all),
conc.user_concurrent_program_name,
fnd1.meaning,
bft.date_from /* Bug 3517604 */
from ben_benefit_actions bft,
hr_lookups hr,
hr_lookups hr1,
hr_lookups hr2,
hr_lookups hr3,
hr_lookups hr4,
hr_lookups hr5,
fnd_lookups fnd1,
per_people_f ppf,
per_person_types ppt,
ben_pgm_f pgm,
per_business_groups pbg,
ben_pl_f pln,
ff_formulas_f ff,
ff_formulas_f ff2,
ben_ler_f ler,
hr_all_organization_units_vl org,
ben_rptg_grp_v bnr,
ben_pl_typ_f ptp,
ben_opt_f opt,
ben_eligy_prfl_f elp,
ben_vrbl_rt_prfl_f vpf,
pay_payrolls_f pay,
ben_pstl_zip_rng_f rzr,
hr_tax_units_v tax,
ben_popl_enrt_typ_cycl_f pop,
ben_enrt_perd epo,
ben_pl_f pln2,
ben_pgm_f pgm2,
fnd_concurrent_requests fnd,
fnd_concurrent_programs_tl conc,
hr_locations lc,
ben_benfts_grp bnfg
where fnd.request_id = p_concurrent_request_id
and conc.concurrent_program_id = fnd.concurrent_program_id
and conc.application_id = 805
and conc.language = userenv('LANG') --Bug 2394141
and bft.request_id = fnd.request_id
and hr.lookup_code = bft.mode_cd
and hr.lookup_type = 'BEN_BENMNGLE_MD'
and hr1.lookup_code = bft.derivable_factors_flag
and hr1.lookup_type = 'YES_NO'
and hr2.lookup_code = bft.validate_flag
and hr2.lookup_type = 'YES_NO'
and hr3.lookup_code = bft.no_programs_flag
and hr3.lookup_type = 'YES_NO'
and hr4.lookup_code = bft.no_plans_flag
and hr4.lookup_type = 'YES_NO'
and hr5.lookup_code(+) = pop.enrt_typ_cycl_cd
and hr5.lookup_type(+) = 'BEN_ENRT_TYP_CYCL'
and fnd.status_code = fnd1.lookup_code
and fnd1.lookup_type = 'CP_STATUS_CODE'
and pop.popl_enrt_typ_cycl_id(+) = epo.popl_enrt_typ_cycl_id
and bft.process_date
between nvl(pop.effective_start_date,bft.process_date)
and nvl(pop.effective_end_date,bft.process_date)
and epo.enrt_perd_id(+) = bft.popl_enrt_typ_cycl_id
and pln2.pl_id(+) = pop.pl_id
and bft.process_date
between nvl(pln2.effective_start_date,bft.process_date)
and nvl(pln2.effective_end_date,bft.process_date)
and pgm2.pgm_id(+) = pop.pgm_id
and bft.process_date
between nvl(pgm2.effective_start_date,bft.process_date)
and nvl(pgm2.effective_end_date,bft.process_date)
and ppf.person_id(+) = bft.person_id
and bft.process_date
between nvl(ppf.effective_start_date,bft.process_date)
and nvl(ppf.effective_end_date,bft.process_date)
and pay.payroll_id(+) = bft.payroll_id
and bft.process_date
between nvl(pay.effective_start_date,bft.process_date)
and nvl(pay.effective_end_date,bft.process_date)
and ppt.person_type_id(+) = bft.person_type_id
and pgm.pgm_id(+) = bft.pgm_id
and bft.process_date
between nvl(pgm.effective_start_date,bft.process_date)
and nvl(pgm.effective_end_date,bft.process_date)
and pbg.business_group_id = bft.business_group_id
and tax.tax_unit_id(+) = bft.legal_entity_id
and pln.pl_id(+) = bft.pl_id
and bft.process_date
between nvl(pln.effective_start_date,bft.process_date)
and nvl(pln.effective_end_date,bft.process_date)
and ler.ler_id(+) = bft.ler_id
and bft.process_date
between nvl(ler.effective_start_date,bft.process_date)
and nvl(ler.effective_end_date,bft.process_date)
and rzr.pstl_zip_rng_id(+) = bft.pstl_zip_rng_id
and bft.process_date
between nvl(rzr.effective_start_date,bft.process_date)
and nvl(rzr.effective_end_date,bft.process_date)
and ptp.pl_typ_id(+) = bft.pl_typ_id
and bft.process_date
between nvl(ptp.effective_start_date,bft.process_date)
and nvl(ptp.effective_end_date,bft.process_date)
and opt.opt_id(+) = bft.opt_id
and bft.process_date
between nvl(opt.effective_start_date,bft.process_date)
and nvl(opt.effective_end_date,bft.process_date)
and ff.formula_id(+) = bft.comp_selection_rl
and bft.process_date between
nvl(ff.effective_start_date,bft.process_date)
and nvl(ff.effective_end_date,bft.process_date)
and ff2.formula_id(+) = bft.person_selection_rl
and bft.process_date between
nvl(ff2.effective_start_date,bft.process_date)
and nvl(ff2.effective_end_date,bft.process_date)
and bnr.rptg_grp_id(+) = bft.rptg_grp_id
and elp.eligy_prfl_id(+) = bft.eligy_prfl_id
and bft.process_date between
nvl(elp.effective_start_date,bft.process_date)
and nvl(elp.effective_end_date,bft.process_date)
and vpf.vrbl_rt_prfl_id(+) = bft.vrbl_rt_prfl_id
and bft.process_date between
nvl(vpf.effective_start_date,bft.process_date)
and nvl(vpf.effective_end_date,bft.process_date)
and org.organization_id(+) = bft.organization_id
and bft.process_date between
nvl(org.date_from,bft.process_date)
and nvl(org.date_to,bft.process_date)
and nvl(bft.location_id,-1) = lc.location_id (+)
and nvl(bft.benfts_grp_id,-1) = bnfg.benfts_grp_id (+)
;
p_comp_object_selection_rule,
p_person_selection_rule,
p_life_event_reason,
p_organization,
p_postal_zip_range,
p_reporting_group,
p_plan_type,
p_option,
p_eligibility_profile,
p_variable_rate_profile,
p_legal_entity,
p_payroll,
p_debug_message,
p_location,
p_audit_log,
p_benfts_group,
p_concurrent_program_name,
p_status,
p_date_from; /* Bug 3517604 */
'SELECT NULL '||
'FROM '||LOWER(p_base_table_name)||' t '||
'WHERE t.'||LOWER(p_base_key_column)||' = :p_base_key_value ';
procedure person_selection_rule
(p_person_id in Number
,p_business_group_id in Number
,p_person_selection_rule_id in Number
,p_effective_date in Date
,p_input1 in varchar2 default null -- Bug 5331889
,p_input1_value in varchar2 default null
,p_return in out nocopy varchar2
,p_err_message in out nocopy varchar2 ) as
Cursor c1 is
Select assignment_id
From per_assignments_f paf
Where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
And paf.primary_flag = 'Y'
And paf.business_group_id = p_business_group_id
And p_effective_date between paf.effective_start_date and paf.effective_end_date ;
Select assignment_id
From per_all_assignments_f paf
Where paf.person_id = p_person_id
and paf.assignment_type <> 'C'
And paf.primary_flag = 'Y'
And paf.business_group_id = p_business_group_id
And p_effective_date between paf.effective_start_date and paf.effective_end_date ;
l_proc varchar2(80) := g_package||'.person_selection_rule';
(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 person_selection_rule;