[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
- Removed parameter comp_object_selection_rule.
- Added procedures for comm. reports.
- Removed bugs.
115.7 09-May-99 maagrawa Commit after submitting each request.
115.8 10-May-99 maagrawa Corrected the header position.
115.9 10-May-99 jcarpent Check ('VOIDD', 'BCKDT') for pil stat cd
115.10 13-May-99 jcarpent Fixed ben_prtt_enrt_actn query to handle arc.
115.11 14-May-99 maagrawa Changed chr() with fnd_global.local_chr()
115.12 09-Jul-99 jcarpent Added checks for backed out pil
115.13 20-JUL-99 Gperry genutils -> benutils package rename.
115.14 27-JUL-1999 mhoyes - Changed g_report_rec to ref ben_type.
115.15 30-JUL-1999 mhoyes - Changed g_batch_proc_rec to ref ben_type.
115.16 10-SEP-1999 maagrawa -For emerging event, trap the date the life
event occurs.
-For emerging event, restrict the process to
comp. objects where the derived factor in
question is attached when the parameter
use_fctr_to_sel_flag is ON.
-Added 3 parameters comp_selection_rule,
los_det_to_use_cd, use_fctr_to_sel_flag.
115.17 06-Oct-1999 tguy added call to dt_fndate
115.18 11-Oct-1999 maagrawa los_dt_to_use_cd supported to get the start
date. Support available for DOH and ASD only.
115.19 13-Oct-1999 stee Change elig_enrol_cd to not be required for
mss mailing unless a comp object is specified.
115.20 13-Oct-1999 maagrawa Ordered the parameter list in procedure process
as in seeded concurrent procedure.
115.21 19-Oct-1999 maagrawa Moved function get_message_name to benutils.
115.22 09-Feb-2000 maagrawa Generate enrollment reminder letter when the
effective date is within the enrollment
window (1187184, 1183934).
Pass the ler_id of the active life event to
the communications process for enrollment
reminder letters (1187482).
115.23 16-Feb-2000 maagrawa Create emerging event comm. for dependents also
when they cross age boundaries.(1198557).
115.24 23-Feb-2000 maagrawa Forgot to close c_dpnt_enrt in previous version.
115.25 02-Mar-2000 maagrawa In standard_header procedure use application_id
(805) also to get conc.program name.(1167925)
115.26 13-Mar-2000 maagrawa Removed effective_start_date check from
procedure chk_per_cm.
115.27 04-Apr-2000 mmogel Added tokens to the message calls so that
they are more meaningful to the user
115.28 11-Apr-2000 maagrawa Use the global variable g_comm_generated to
identify communications generated in the
procedure chk_per_cm.(4507,4859,4883)
115.29 08-May-2000 maagrawa Added parameter p_status in procedure
standard_header.
115.30 17-Jul-2000 gperry Fixed WWBUG 1351039.
Dependent communications now created correctly.
Added in check for person_type_id parameter
and commented out verify_person_type check.
115.31 05-Sep-2000 pbodla - Bug 5422 : Allow different enrollment periods
for programs for scheduled a enrollment.
115.32 07-Dec-2000 rchase - Bug 1518211. p_dob is in/out now for
determine_age calls.
115.33 11-Jun-2002 pabodla - Added dbdrv command
115.34 14-Jun-2002 pabodla Do not select the contingent worker
assignment when assignment data is
fetched.
115.35 18-Jun-2002 ikasire Bug 2394141 NLS Fix
115.36 27-Sep-2002 rpgupta Fixed bug 2595834
Made changes to standard_header procedure
to take a substring of 80 characters
115.38 11-dec-2002 hmani NoCopy changes
115.39 30-dec-2003 mmudigon Bug 3232205. Modified cursors on
ben_person_actions to drive by
benefit_action_id
115.40 17-Sep-2004 pabodla iRec - c_pil_enrtrmdr : modified not to consider
GSP/irec/comp events.
115.41 27-Dec-2004 tjesumic person_type_id is validated for the communications
115.42 30-Jun-2006 swjain Passing ler_id in call to chk_person_selection
115.43 01-Aug-2006 swjain Bug 5435002 - Updated cursor c_per_actnrmdr, c_per_emrgevt
c_pil_enrtrmdr, c_per_mssmlg in procedure create_actnrmdr_ranges,
create_emrgevt_ranges, create_enrtrmdr_ranges, create_mssmlg_ranges
respectively.
115.44 07-Aug-2006 rtagarra Bug#5444208 Modified the cursor c_per_mssmlg to pick up the person when the assignment
is active on the effective date.
115.45 07-Aug-2006 gsehgal Bug 5446127 Changed the location description to location code
115.46 20-apr-2007 nhunur Bug 5942699 Perf fixes with logging and sql changes
115.47 20-apr-2007 nhunur Bug 6008383 Perf fixes with mass mailing cursors
115.48 06-Apr-2009 velvanop Bug8364821 - Communication is sent to the Employee and not to the Spouse when
Communication is triggered on the Spouse.Spouse is also an Employee.
115.49 10-May-2011 velvanop Bug 12414987: To check whether Participant is enrolled in a comp object,
check the enrollment based on cvg dates and not as effective dates
--------------------------------------------------------------------------------------------------
*/
--
-- GLOBAL VARIABLES
--
g_package varchar2(80) := 'ben_determine_communications';
,comp_selection_rl number(15)
,actn_typ_id number(15)
,elig_enrol_cd varchar2(30)
,use_fctr_to_sel_flag varchar2(30)
,age_fctr_id number(15)
,min_age number(15)
,max_age number(15)
,los_fctr_id number(15)
,min_los number(15)
,max_los number(15)
,los_det_to_use_cd varchar2(30)
,cmbn_age_los_fctr_id number(15)
,date_from date
-- PB : 5422 :
--
,lf_evt_ocrd_dt date
-- ,popl_enrt_typ_cycl_id number(15)
,audit_log_flag varchar2(30));
g_processes_rec.delete;
benutils.g_report_table_object.delete;
benutils.g_batch_action_table_object.delete;
benutils.g_batch_proc_table_object.delete;
benutils.g_batch_commu_table_object.delete;
select null
from fnd_concurrent_requests fnd
where fnd.phase_code <> 'C'
and fnd.request_id = v_request_id;
select 'Y'
from ben_benefit_actions bft
where bft.benefit_action_id = g_parm.benefit_action_id
and bft.request_id = fnd_global.conc_request_id;
select count(*)
from ben_person_actions pac
where pac.benefit_action_id = g_parm.benefit_action_id
and pac.action_status_cd = nvl(p_status_cd,pac.action_status_cd);
,buff => 'Person Selection Rule : ' ||
benutils.iftrue
(p_expression => g_parm.person_sel_rl is null
,p_true => 'All'
,p_false => g_parm.person_sel_rl));
select 'Y'
from ben_prtn_elig_f epa,
ben_prtn_elig_prfl_f cep,
ben_eligy_prfl_f elp,
ben_elig_los_prte_f els
where (epa.pl_id = p_pl_id or
epa.pgm_id = p_pgm_id or
epa.plip_id = p_plip_id or
epa.ptip_id = p_ptip_id )
and epa.prtn_elig_id = cep.prtn_elig_id
and cep.eligy_prfl_id = elp.eligy_prfl_id
and elp.eligy_prfl_id = els.eligy_prfl_id
and els.los_fctr_id = g_parm.los_fctr_id
and g_parm.effective_date between
epa.effective_start_date and epa.effective_end_date
and g_parm.effective_date between
cep.effective_start_date and cep.effective_end_date
and g_parm.effective_date between
elp.effective_start_date and elp.effective_end_date
and g_parm.effective_date between
els.effective_start_date and els.effective_end_date;
select 'Y'
from ben_prtn_elig_f epa,
ben_prtn_elig_prfl_f cep,
ben_eligy_prfl_f elp,
ben_elig_age_prte_f eap
where (epa.pl_id = p_pl_id or
epa.pgm_id = p_pgm_id or
epa.plip_id = p_plip_id or
epa.ptip_id = p_ptip_id )
and epa.prtn_elig_id = cep.prtn_elig_id
and cep.eligy_prfl_id = elp.eligy_prfl_id
and elp.eligy_prfl_id = eap.eligy_prfl_id
and eap.age_fctr_id = g_parm.age_fctr_id
and g_parm.effective_date between
epa.effective_start_date and epa.effective_end_date
and g_parm.effective_date between
cep.effective_start_date and cep.effective_end_date
and g_parm.effective_date between
elp.effective_start_date and elp.effective_end_date
and g_parm.effective_date between
eap.effective_start_date and eap.effective_end_date;
select 'Y'
from ben_prtn_elig_f epa,
ben_prtn_elig_prfl_f cep,
ben_eligy_prfl_f elp,
ben_elig_cmbn_age_los_prte_f ecp
where (epa.pl_id = p_pl_id or
epa.pgm_id = p_pgm_id or
epa.plip_id = p_plip_id or
epa.ptip_id = p_ptip_id )
and epa.prtn_elig_id = cep.prtn_elig_id
and cep.eligy_prfl_id = elp.eligy_prfl_id
and elp.eligy_prfl_id = ecp.eligy_prfl_id
and ecp.cmbn_age_los_fctr_id = g_parm.cmbn_age_los_fctr_id
and g_parm.effective_date between
epa.effective_start_date and epa.effective_end_date
and g_parm.effective_date between
cep.effective_start_date and cep.effective_end_date
and g_parm.effective_date between
elp.effective_start_date and elp.effective_end_date
and g_parm.effective_date between
ecp.effective_start_date and ecp.effective_end_date;
select ptip.ptip_id,
plip.plip_id
from ben_pl_f pl,
ben_ptip_f ptip,
ben_plip_f plip
where pl.pl_id = p_pl_id
and pl.pl_typ_id = ptip.pl_typ_id
and ptip.pgm_id = p_pgm_id
and plip.pl_id = pl.pl_id
and plip.pgm_id = ptip.pgm_id
and g_parm.effective_date between
pl.effective_start_date and pl.effective_end_date
and g_parm.effective_date between
plip.effective_start_date and plip.effective_end_date
and g_parm.effective_date between
ptip.effective_start_date and ptip.effective_end_date;
select ppf.person_id
from per_all_people_f ppf,
per_contact_relationships pcr
where pcr.contact_person_id = p_dpnt_person_id
and pcr.business_group_id = g_parm.business_group_id
and ppf.person_id = pcr.person_id
and ppf.business_group_id = g_parm.business_group_id
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select ppt.system_person_type
from per_person_types ppt
where ppt.person_type_id = p_person_type_id
and ppt.business_group_id = g_parm.business_group_id;
select 'Y'
from per_all_people_f ppf, per_person_types ppt
where ppf.person_id = p_person_id
and ppf.person_type_id = p_person_type_id
and ppf.business_group_id = g_parm.business_group_id
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_type_id = ppt.person_type_id
and ppt.business_group_id = g_parm.business_group_id
and ppt.active_flag = 'Y';
select 'Y'
from per_person_type_usages_f ptu,
per_person_types ppt
where ptu.person_id = p_person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'DPNT'
and g_parm.effective_date between
ptu.effective_start_date and ptu.effective_end_date
and ppt.active_flag = 'Y'
and ppt.business_group_id = g_parm.business_group_id;
select 'Y'
from per_person_type_usages_f ptu,
per_person_types ppt
where ptu.person_id = p_person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EMP'
and g_parm.effective_date between
ptu.effective_start_date and ptu.effective_end_date
and ppt.active_flag = 'Y'
and ppt.business_group_id = g_parm.business_group_id;
select null
from per_all_people_f ppf, per_person_types ppt
where ppf.person_id = g_parm.person_id
and ppf.person_type_id = g_parm.person_type_id
and ppf.business_group_id = g_parm.business_group_id
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.person_type_id = ppt.person_type_id
and ppt.business_group_id = g_parm.business_group_id
and ppt.active_flag = 'Y';
fnd_message.set_token('PER_SELECT_RL',
'person_selection_rule :'||g_parm.person_sel_rl);
select ppf.date_of_birth
from per_all_people_f ppf
where ppf.person_id = p_person_id
and ppf.business_group_id = g_parm.business_group_id
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select *
from ben_age_fctr agf
where agf.age_fctr_id = g_parm.age_fctr_id
and agf.business_group_id = g_parm.business_group_id;
select *
from ben_los_fctr los
where los.los_fctr_id = g_parm.los_fctr_id
and los.business_group_id = g_parm.business_group_id;
select pps.date_start,
pps.adjusted_svc_date
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.business_group_id = g_parm.business_group_id;
select cal.cmbn_age_los_fctr_id,
cal.age_fctr_id,
cal.los_fctr_id,
cal.cmbnd_min_val,
cal.cmbnd_max_val,
agf.mn_age_num,
agf.mx_age_num,
agf.age_det_cd,
lsf.mn_los_num,
lsf.mx_los_num,
lsf.los_det_cd
from ben_cmbn_age_los_fctr cal,
ben_age_fctr agf,
ben_los_fctr lsf
where cal.cmbn_age_los_fctr_id = g_parm.cmbn_age_los_fctr_id
and cal.business_group_id = g_parm.business_group_id
and cal.age_fctr_id = agf.age_fctr_id
and agf.business_group_id = g_parm.business_group_id
and cal.los_fctr_id = lsf.los_fctr_id
and lsf.business_group_id = g_parm.business_group_id;
select *
from ben_person_actions bpa
where bpa.benefit_action_id = g_parm.benefit_action_id
and bpa.person_action_id between p_start_person_action_id
and p_end_person_action_id
and bpa.action_status_cd <> 'P'
order by bpa.person_action_id;
select *
from ben_person_actions bpa
where bpa.benefit_action_id = g_parm.benefit_action_id
and bpa.person_action_id between p_start_person_action_id
and p_end_person_action_id
and bpa.action_status_cd <> 'P'
order by bpa.person_action_id;
select *
from ben_person_actions bpa
where bpa.benefit_action_id = g_parm.benefit_action_id
and bpa.person_action_id between p_start_person_action_id
and p_end_person_action_id
and bpa.benefit_action_id = g_parm.benefit_action_id
order by bpa.person_action_id;
select *
from ben_person_actions bpa
where bpa.benefit_action_id = g_parm.benefit_action_id
and bpa.person_action_id between p_start_person_action_id
and p_end_person_action_id
and bpa.action_status_cd <> 'P'
and bpa.benefit_action_id = g_parm.benefit_action_id
order by bpa.person_action_id;
select *
from ben_benefit_actions ben
where ben.benefit_action_id = p_benefit_action_id;
,p_person_sel_rl => l_parm.person_selection_rl
,p_organization_id => l_parm.organization_id
,p_location_id => l_parm.location_id
,p_ler_id => l_parm.ler_id
,p_pgm_id => l_parm.pgm_id
,p_pl_nip_id => l_parm.pl_id
,p_plan_in_pgm_flag => l_parm.no_programs_flag
,p_comp_selection_rl => l_parm.comp_selection_rl
,p_actn_typ_id => l_parm.actn_typ_id
,p_elig_enrol_cd => l_parm.elig_enrol_cd
,p_use_fctr_to_sel_flag => l_parm.use_fctr_to_sel_flag
,p_age_fctr_id => l_parm.age_fctr_id
,p_min_age => l_parm.min_age
,p_max_age => l_parm.max_age
,p_los_fctr_id => l_parm.los_fctr_id
,p_min_los => l_parm.min_los
,p_max_los => l_parm.max_los
,p_los_det_to_use_cd => l_parm.los_det_to_use_cd
,p_cmbn_age_los_fctr_id => l_parm.cmbn_age_los_fctr_id
,p_date_from => l_parm.date_from
--
-- PB : 5422 :
-- ,p_popl_enrt_typ_cycl_id => l_parm.popl_enrt_typ_cycl_id
,p_lf_evt_ocrd_dt => fnd_date.date_to_canonical(l_parm.lf_evt_ocrd_dt)
,p_audit_log_flag => l_parm.audit_log_flag);
select ran.rowid,
ran.starting_person_action_id,
ran.ending_person_action_id
from ben_batch_ranges ran
where ran.range_status_cd = 'U'
and ran.benefit_action_id = p_benefit_action_id
and rownum < 2
for update of ran.range_status_cd;
select ben.benefit_action_id
,ben.validate_flag
,ben.process_date
,ben.business_group_id
,ben.mode_cd
,ben.cm_trgr_typ_cd
,ben.cm_typ_id
,ben.person_id
,ben.person_type_id
,ben.person_selection_rl
,ben.organization_id
,ben.location_id
,ben.ler_id
,ben.pgm_id
,ben.pl_id
,ben.no_programs_flag
,ben.comp_selection_rl
,ben.actn_typ_id
,ben.elig_enrol_cd
,ben.use_fctr_to_sel_flag
,ben.age_fctr_id
,ben.min_age
,ben.max_age
,ben.los_fctr_id
,ben.min_los
,ben.max_los
,ben.los_det_to_use_cd
,ben.cmbn_age_los_fctr_id
,ben.date_from
-- PB : 5422 :
-- ,ben.popl_enrt_typ_cycl_id
,ben.lf_evt_ocrd_dt
,ben.audit_log_flag
from ben_benefit_actions ben
where ben.benefit_action_id = p_benefit_action_id;
update ben_batch_ranges
set range_status_cd = 'P'
where rowid = l_range_rec.rowid;
if not ben_rules.chk_person_selection
(p_person_id => p_person_id
,p_business_group_id => g_parm.business_group_id
,p_person_selection_rule_id=> g_parm.person_sel_rl
,p_effective_date => g_parm.effective_date
,p_ler_id => p_ler_id) then
--
l_person_ok := 'N';
select distinct pil.person_id, pil.ler_id
from ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel,
ben_ler_f ler
where (g_parm.person_id is null or
pil.person_id = g_parm.person_id)
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.business_group_id = g_parm.business_group_id
and pil.per_in_ler_id = pel.per_in_ler_id
and pil.ler_id = ler.ler_id
and g_parm.effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('COMP', 'GSP', 'ABS')
and pel.elcns_made_dt is null
and g_parm.effective_date between
nvl(pel.enrt_perd_strt_dt, g_parm.effective_date) and
nvl(pel.enrt_perd_end_dt, g_parm.effective_date)
--
and (g_parm.person_type_id is null
or
exists (select null
from per_person_type_usages_f ppu
where pil.person_id = ppu.person_id
and ppu.person_type_id = g_parm.person_type_id
and g_parm.effective_date
between ppu.effective_start_date
and ppu.effective_end_date))
--
and (g_parm.pgm_id is null or
pel.pgm_id = g_parm.pgm_id)
and ((g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'Y'
and pel.pgm_id is not null)
or
(g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'N'
and pel.pgm_id is null)
or
(g_parm.pl_nip_id = pel.pl_id
and g_parm.plan_in_pgm_flag = 'N'))
and (g_parm.ler_id is null or
pil.ler_id = g_parm.ler_id)
and ((g_parm.organization_id is null and
g_parm.location_id is null)
or
/* check if the person belongs to the org or location specified */
/* Bug 5435002 */
exists (select 's'
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = pil.person_id
AND asg.primary_flag = 'Y'
and (g_parm.organization_id is null or
asg.organization_id = g_parm.organization_id)
and (g_parm.location_id is null or
asg.location_id = g_parm.location_id)
AND g_parm.effective_date
BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.business_group_id = g_parm.business_group_id
AND ( ( assignment_type = 'E'
AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
OR ( ast.per_system_status = 'TERM_ASSIGN'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg1, per_assignment_status_types ast1
WHERE asg1.assignment_type = 'B'
AND asg1.primary_flag = 'Y'
AND asg1.person_id = pil.person_id
AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
AND ast1.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg1.effective_start_date AND asg1.effective_end_date
)
)
)
)
OR ( assignment_type = 'B'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg2, per_assignment_status_types ast2
WHERE asg2.assignment_type = 'E'
AND asg2.primary_flag = 'Y'
AND asg2.person_id = pil.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg2.effective_start_date AND asg2.effective_end_date
)
)
)
)
);
select distinct ppf.person_id
from per_all_people_f ppf,
ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_prtt_enrt_actn_f pea
where (g_parm.person_id is null or
ppf.person_id = g_parm.person_id)
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.business_group_id = g_parm.business_group_id
and pen.person_id = ppf.person_id
and pen.ler_id = nvl(g_parm.ler_id, pen.ler_id)
and pen.business_group_id = g_parm.business_group_id
and g_parm.effective_date between pen.effective_start_date
and pen.effective_end_date
and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
and (g_parm.actn_typ_id is null or
pea.actn_typ_id = g_parm.actn_typ_id)
and g_parm.effective_date between pea.effective_start_date
and pea.effective_end_date
and pea.business_group_id = g_parm.business_group_id
--
and (g_parm.person_type_id is null
or
exists (select null
from per_person_type_usages_f ppu
where ppf.person_id = ppu.person_id
and ppu.person_type_id = g_parm.person_type_id
and g_parm.effective_date
between ppu.effective_start_date
and ppu.effective_end_date))
--
and pea.cmpltd_dt is null
and (g_parm.pgm_id is null or
pen.pgm_id = g_parm.pgm_id)
and ((g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'Y'
and pen.pgm_id is not null)
or
(g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'N'
and pen.pgm_id is null)
or
(g_parm.pl_nip_id = pen.pl_id))
and ((g_parm.organization_id is null and
g_parm.location_id is null)
or
/* check if the person belongs to the org or location specified */
/* Bug 5435002 */
exists (select 's'
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = ppf.person_id
AND asg.primary_flag = 'Y'
and (g_parm.organization_id is null or
asg.organization_id = g_parm.organization_id)
and (g_parm.location_id is null or
asg.location_id = g_parm.location_id)
AND g_parm.effective_date
BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.business_group_id = g_parm.business_group_id
AND ( ( assignment_type = 'E'
AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
OR ( ast.per_system_status = 'TERM_ASSIGN'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg1, per_assignment_status_types ast1
WHERE asg1.assignment_type = 'B'
AND asg1.primary_flag = 'Y'
AND asg1.person_id = ppf.person_id
AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
AND ast1.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg1.effective_start_date AND asg1.effective_end_date
)
)
)
)
OR ( assignment_type = 'B'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg2, per_assignment_status_types ast2
WHERE asg2.assignment_type = 'E'
AND asg2.primary_flag = 'Y'
AND asg2.person_id = ppf.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg2.effective_start_date AND asg2.effective_end_date
)
)
)
)
)
and pil.per_in_ler_id(+)=pea.per_in_ler_id
and pil.business_group_id(+)=pea.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
-- found row condition
or pil.per_in_ler_stat_cd is null
-- outer join condition
)
;
select distinct ppf.person_id
from per_people_f ppf
where (g_parm.person_id is null or
ppf.person_id = g_parm.person_id)
and ppf.business_group_id = g_parm.business_group_id
and (g_parm.date_from is null or
g_parm.date_from between ppf.effective_start_date
and ppf.effective_end_date)
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and (g_parm.person_type_id is null
or
exists (select null
from per_person_type_usages_f ppu
where ppf.person_id = ppu.person_id
and ppu.person_type_id = g_parm.person_type_id
and g_parm.effective_date
between ppu.effective_start_date
and ppu.effective_end_date))
and ((g_parm.organization_id is null and
g_parm.location_id is null)
or
/* check if the person belongs to the org or location specified */
/* Bug 5435002 */
exists (select 's'
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = ppf.person_id
AND asg.primary_flag = 'Y'
and (g_parm.organization_id is null or
asg.organization_id = g_parm.organization_id)
and (g_parm.location_id is null or
asg.location_id = g_parm.location_id)
AND g_parm.effective_date
BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.business_group_id = g_parm.business_group_id
AND ( ( assignment_type = 'E'
AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
OR ( ast.per_system_status = 'TERM_ASSIGN'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg1, per_assignment_status_types ast1
WHERE asg1.assignment_type = 'B'
AND asg1.primary_flag = 'Y'
AND asg1.person_id = ppf.person_id
AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
AND ast1.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg1.effective_start_date AND asg1.effective_end_date
)
)
)
)
OR ( assignment_type = 'B'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg2, per_assignment_status_types ast2
WHERE asg2.assignment_type = 'E'
AND asg2.primary_flag = 'Y'
AND asg2.person_id = ppf.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg2.effective_start_date AND asg2.effective_end_date
)
)
)
)
);
select elig.pgm_id,
elig.pl_id
from ben_elig_per_f elig,
ben_per_in_ler pil
where elig.person_id = v_person_id
and (g_parm.pgm_id is null or
elig.pgm_id = g_parm.pgm_id)
and ((g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'Y'
and elig.pgm_id is not null)
or
(g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'N'
and elig.pgm_id is null)
or
(g_parm.pl_nip_id = elig.pl_id))
and elig.elig_flag = 'Y'
and g_parm.effective_date between elig.effective_start_date
and elig.effective_end_date
and pil.per_in_ler_id(+)=elig.per_in_ler_id
and pil.business_group_id(+)=elig.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
-- found row condition
or pil.per_in_ler_stat_cd is null
-- outer join condition
);
select pen.pgm_id,
pen.pl_id
from ben_prtt_enrt_rslt_f pen
where pen.person_id = v_person_id
and (g_parm.pgm_id is null or
pen.pgm_id = g_parm.pgm_id)
and ((g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'Y'
and pen.pgm_id is not null)
or
(g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'N'
and pen.pgm_id is null)
or
(g_parm.pl_nip_id = pen.pl_id))
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = g_parm.business_group_id
/* Bug 12414987: Check whether Participant is covered or not as of g_parm.effective_date */
/* and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
and g_parm.effective_date between pen.effective_start_date
and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot*/
and g_parm.effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date;
select pen.pgm_id,
pen.pl_id
from ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f pdp
where pdp.dpnt_person_id = v_person_id
and pdp.cvg_thru_dt = hr_api.g_eot
and g_parm.effective_date between
pdp.effective_start_date and pdp.effective_end_date
and pdp.effective_end_date = hr_api.g_eot
and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and (g_parm.pgm_id is null or
pen.pgm_id = g_parm.pgm_id)
and ((g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'Y'
and pen.pgm_id is not null)
or
(g_parm.pl_nip_id is null
and g_parm.plan_in_pgm_flag = 'N'
and pen.pgm_id is null)
or
(g_parm.pl_nip_id = pen.pl_id))
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = g_parm.business_group_id
and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
and g_parm.effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.effective_end_date = hr_api.g_eot;
ben_rules.chk_comp_object_selection(
p_oipl_id => null,
p_pl_id => l_pl_id,
p_pgm_id => l_pgm_id,
p_pl_typ_id => null,
p_opt_id => null,
p_business_group_id => g_parm.business_group_id,
p_comp_selection_rule_id => g_parm.comp_selection_rl,
p_effective_date => g_parm.effective_date) then
--
-- Create person actions and batch ranges
--
g_per_slctd := g_per_slctd + 1;
hr_utility.set_location('People selected from database : ' || g_per_slctd,10);
select person_id
from per_all_people_f ppf
where (g_parm.person_id is null or
ppf.person_id = g_parm.person_id)
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.business_group_id = g_parm.business_group_id
and (g_parm.person_type_id is null
or
exists (select null
from per_person_type_usages_f ppu
where ppf.person_id = ppu.person_id
and ppu.person_type_id = g_parm.person_type_id
and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
--
and ((g_parm.elig_enrol_cd = 'ELIG' -- begin ELIG block
and
-- check if the person is eligible for the comp object
exists (select 's'
from ben_elig_per_f elig,
ben_per_in_ler pil
where elig.person_id = ppf.person_id
and elig.pgm_id = g_parm.pgm_id
and (g_parm.pl_nip_id is null and g_parm.plan_in_pgm_flag = 'Y' and elig.pgm_id is not null)
and elig.elig_flag = 'Y'
and g_parm.effective_date between elig.effective_start_date and elig.effective_end_date
and pil.per_in_ler_id(+)=elig.per_in_ler_id
and pil.business_group_id(+)=elig.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
-- found row condition
or pil.per_in_ler_stat_cd is null
-- outer join condition
)
))
)
and ((g_parm.organization_id is null and
g_parm.location_id is null)
or
exists (select 's'
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = ppf.person_id
AND asg.primary_flag = 'Y'
and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.business_group_id = g_parm.business_group_id
AND ( ( assignment_type = 'E'
AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
OR ( ast.per_system_status = 'TERM_ASSIGN'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg1, per_assignment_status_types ast1
WHERE asg1.assignment_type = 'B'
AND asg1.primary_flag = 'Y'
AND asg1.person_id = ppf.person_id
AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
AND ast1.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg1.effective_start_date AND asg1.effective_end_date
)
)
)
)
OR ( assignment_type = 'B'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg2, per_assignment_status_types ast2
WHERE asg2.assignment_type = 'E'
AND asg2.primary_flag = 'Y'
AND asg2.person_id = ppf.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg2.effective_start_date AND asg2.effective_end_date
)
)
)
)
);
select person_id
from per_all_people_f ppf
where (g_parm.person_id is null or
ppf.person_id = g_parm.person_id)
and g_parm.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.business_group_id = g_parm.business_group_id
and (g_parm.person_type_id is null
or
exists (select null
from per_person_type_usages_f ppu
where ppf.person_id = ppu.person_id
and ppu.person_type_id = g_parm.person_type_id
and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
--
and ((g_parm.elig_enrol_cd = 'ELIG' -- begin ELIG block
and
-- check if the person is eligible for the comp object
exists (select 's'
from ben_elig_per_f elig,
ben_per_in_ler pil
where elig.person_id = ppf.person_id
and g_parm.pgm_id is null
and (g_parm.pl_nip_id is not null and g_parm.plan_in_pgm_flag = 'N' and elig.pgm_id is null)
and g_parm.pl_nip_id = elig.pl_id
and elig.elig_flag = 'Y'
and g_parm.effective_date between elig.effective_start_date and elig.effective_end_date
and pil.per_in_ler_id(+)=elig.per_in_ler_id
and pil.business_group_id(+)=elig.business_group_id
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
-- found row condition
or pil.per_in_ler_stat_cd is null
-- outer join condition
)
))
)
and ((g_parm.organization_id is null and
g_parm.location_id is null)
or
exists (select 's'
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = ppf.person_id
AND asg.primary_flag = 'Y'
and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.business_group_id = g_parm.business_group_id
AND ( ( assignment_type = 'E'
AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
OR ( ast.per_system_status = 'TERM_ASSIGN'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg1, per_assignment_status_types ast1
WHERE asg1.assignment_type = 'B'
AND asg1.primary_flag = 'Y'
AND asg1.person_id = ppf.person_id
AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
AND ast1.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg1.effective_start_date AND asg1.effective_end_date
)
)
)
)
OR ( assignment_type = 'B'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg2, per_assignment_status_types ast2
WHERE asg2.assignment_type = 'E'
AND asg2.primary_flag = 'Y'
AND asg2.person_id = ppf.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg2.effective_start_date AND asg2.effective_end_date
)
)
)
)
);
select person_id
from per_all_people_f ppf
where (g_parm.person_id is null or ppf.person_id = g_parm.person_id)
and g_parm.effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.business_group_id = g_parm.business_group_id
and (g_parm.person_type_id is null
or
exists (select null
from per_person_type_usages_f ppu
where ppf.person_id = ppu.person_id
and ppu.person_type_id = g_parm.person_type_id
and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
--
and ( (g_parm.elig_enrol_cd = 'ENROL' -- begin ENROL block
and exists (select 's'
from ben_prtt_enrt_rslt_f pen
where pen.person_id = ppf.person_id
and (pen.pgm_id = g_parm.pgm_id)
and (g_parm.pl_nip_id is null and g_parm.plan_in_pgm_flag = 'Y' and pen.pgm_id is not null)
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = g_parm.business_group_id
/* Bug 12414987: Check whether Participant is covered or not as of g_parm.effective_date */
--and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
--and g_parm.effective_date between pen.effective_start_date and pen.effective_end_date
and g_parm.effective_date between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
and pen.enrt_cvg_thru_dt <= pen.effective_end_date
))
)
and ((g_parm.organization_id is null and
g_parm.location_id is null)
or
exists (select 's'
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = ppf.person_id
AND asg.primary_flag = 'Y'
and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.business_group_id = g_parm.business_group_id
AND ( ( assignment_type = 'E'
AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
OR ( ast.per_system_status = 'TERM_ASSIGN'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg1, per_assignment_status_types ast1
WHERE asg1.assignment_type = 'B'
AND asg1.primary_flag = 'Y'
AND asg1.person_id = ppf.person_id
AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
AND ast1.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg1.effective_start_date AND asg1.effective_end_date
))
))
OR ( assignment_type = 'B'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg2, per_assignment_status_types ast2
WHERE asg2.assignment_type = 'E'
AND asg2.primary_flag = 'Y'
AND asg2.person_id = ppf.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg2.effective_start_date AND asg2.effective_end_date
))
))
);
select person_id
from per_all_people_f ppf
where (g_parm.person_id is null or ppf.person_id = g_parm.person_id)
and g_parm.effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.business_group_id = g_parm.business_group_id
and (g_parm.person_type_id is null
or
exists (select null
from per_person_type_usages_f ppu
where ppf.person_id = ppu.person_id
and ppu.person_type_id = g_parm.person_type_id
and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
--
and ( (g_parm.elig_enrol_cd = 'ENROL' -- begin ENROL block
and exists (select 's'
from ben_prtt_enrt_rslt_f pen
where pen.person_id = ppf.person_id
and (g_parm.pgm_id is null)
and (g_parm.pl_nip_id is not null and g_parm.plan_in_pgm_flag = 'N' and pen.pgm_id is null)
and (g_parm.pl_nip_id = pen.pl_id)
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = g_parm.business_group_id
and nvl(pen.enrt_cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
and g_parm.effective_date between pen.effective_start_date and pen.effective_end_date
))
)
and ((g_parm.organization_id is null and
g_parm.location_id is null)
or
exists (select 's'
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = ppf.person_id
AND asg.primary_flag = 'Y'
and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.business_group_id = g_parm.business_group_id
AND ( ( assignment_type = 'E'
AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
OR ( ast.per_system_status = 'TERM_ASSIGN'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg1, per_assignment_status_types ast1
WHERE asg1.assignment_type = 'B'
AND asg1.primary_flag = 'Y'
AND asg1.person_id = ppf.person_id
AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
AND ast1.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg1.effective_start_date AND asg1.effective_end_date
))
))
OR ( assignment_type = 'B'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg2, per_assignment_status_types ast2
WHERE asg2.assignment_type = 'E'
AND asg2.primary_flag = 'Y'
AND asg2.person_id = ppf.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg2.effective_start_date AND asg2.effective_end_date
))
))
);
select person_id
from per_all_people_f ppf
where (g_parm.person_id is null or ppf.person_id = g_parm.person_id)
and g_parm.effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.business_group_id = g_parm.business_group_id
and (g_parm.person_type_id is null
or
exists (select null
from per_person_type_usages_f ppu
where ppf.person_id = ppu.person_id
and ppu.person_type_id = g_parm.person_type_id
and g_parm.effective_date between ppu.effective_start_date and ppu.effective_end_date))
--
and (g_parm.elig_enrol_cd is null )
and ((g_parm.organization_id is null and
g_parm.location_id is null)
or
exists (select 's'
FROM per_all_assignments_f asg, per_assignment_status_types ast
WHERE asg.person_id = ppf.person_id
AND asg.primary_flag = 'Y'
and (g_parm.organization_id is null or asg.organization_id = g_parm.organization_id)
and (g_parm.location_id is null or asg.location_id = g_parm.location_id)
AND g_parm.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.assignment_status_type_id = ast.assignment_status_type_id
and asg.business_group_id = g_parm.business_group_id
AND ( ( assignment_type = 'E'
AND ( ast.per_system_status = 'ACTIVE_ASSIGN'
OR ( ast.per_system_status = 'TERM_ASSIGN'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg1, per_assignment_status_types ast1
WHERE asg1.assignment_type = 'B'
AND asg1.primary_flag = 'Y'
AND asg1.person_id = ppf.person_id
AND asg1.assignment_status_type_id = ast1.assignment_status_type_id
AND ast1.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg1.effective_start_date AND asg1.effective_end_date
))
))
OR ( assignment_type = 'B'
AND NOT EXISTS (
SELECT assignment_id
FROM per_all_assignments_f asg2, per_assignment_status_types ast2
WHERE asg2.assignment_type = 'E'
AND asg2.primary_flag = 'Y'
AND asg2.person_id = ppf.person_id
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND ast2.per_system_status = 'ACTIVE_ASSIGN'
AND g_parm.effective_date
BETWEEN asg2.effective_start_date AND asg2.effective_end_date
))
))
);
hr_utility.set_location('People selected from database : ' || g_per_slctd,10);
,p_comp_selection_rl in number default null
,p_use_fctr_to_sel_flag in varchar2 default 'N'
,p_los_det_to_use_cd in varchar2 default null
) is
--
l_effective_date date;
g_parm.comp_selection_rl := p_comp_selection_rl;
,p_comp_selection_rl => p_comp_selection_rl
,p_person_selection_rl => p_person_sel_rl
,p_ler_id => p_ler_id
,p_organization_id => p_organization_id
,p_location_id => p_location_id
,p_debug_messages_flag => 'Y'
,p_cm_trgr_typ_cd => p_cm_trgr_typ_cd
,p_cm_typ_id => p_cm_typ_id
,p_use_fctr_to_sel_flag => p_use_fctr_to_sel_flag
,p_age_fctr_id => p_age_fctr_id
,p_min_age => p_min_age
,p_max_age => p_max_age
,p_los_fctr_id => p_los_fctr_id
,p_min_los => p_min_los
,p_max_los => p_max_los
,p_los_det_to_use_cd => p_los_det_to_use_cd
,p_cmbn_age_los_fctr_id => p_cmbn_age_los_fctr_id
,p_date_from => l_date_from
,p_elig_enrol_cd => p_elig_enrol_cd
,p_actn_typ_id => p_actn_typ_id
,p_audit_log_flag => p_audit_log_flag
,p_request_id => fnd_global.conc_request_id
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date);
delete from ben_batch_ranges
where benefit_action_id = g_parm.benefit_action_id;
select count(distinct bmi.person_id)
from ben_batch_commu_info bmi,
ben_benefit_actions bft
where bft.request_id = p_concurrent_request_id
and bft.benefit_action_id = bmi.benefit_action_id;
select count(*)
from ben_batch_commu_info bmi1,
ben_benefit_actions bft
where bft.request_id = p_concurrent_request_id
and bft.benefit_action_id = bmi1.benefit_action_id
and not exists ( select 1
from ben_batch_commu_info bmi2
where bmi2.benefit_action_id = bft.benefit_action_id
and bmi2.person_id = bmi1.person_id
and bmi2.batch_commu_id <> bmi1.batch_commu_id);
p_person_selection_rule out nocopy varchar2,
p_organization out nocopy varchar2,
p_location out nocopy varchar2,
p_ler out nocopy varchar2,
p_program out nocopy varchar2,
p_plan out nocopy varchar2,
p_plan_in_program out nocopy varchar2,
p_actn_typ out nocopy varchar2,
p_elig_enrol out nocopy varchar2,
p_age_fctr out nocopy varchar2,
p_min_age out nocopy number,
p_max_age out nocopy number,
p_los_fctr out nocopy varchar2,
p_min_los out nocopy number,
p_max_los out nocopy number,
p_cmbn_age_los_fctr out nocopy varchar2,
p_date_from out nocopy date,
p_enrollment_period out nocopy varchar2,
p_audit_log out nocopy varchar2,
p_status out nocopy varchar2) is
--
l_all varchar2(80);
select bft.process_date process_date,
hr1.meaning mode_meaning,
hr2.meaning validate_meaning,
nvl(ppf.full_name,l_all) person_name,
nvl(ppt.user_person_type,l_all) person_type,
nvl(pgm1.name,l_all) pgm_name,
pbg.name business_group_name,
nvl(pln1.name,l_all) pln_name,
decode(hr4.meaning,
null,
l_all,
hr4.meaning||
' '||
pln2.name||
' '||
pgm2.name||
' '||
epo.strt_dt||
' '||
epo.end_dt) enrt_perd,
hr3.meaning plan_in_program,
hr5.meaning elig_enrol,
hr6.meaning audit_log,
hr7.meaning cm_trgr_typ,
nvl(cct.name, l_all) cm_typ_name,
decode(loc.location_code,
null, l_all,
-- changed bug: 5446127
-- loc.description) location_desc,
loc.location_code) location_desc,
-- change end
nvl(ff.formula_name,l_none) person_selection_rl,
nvl(ler.name,l_all) ler_name,
nvl(org.name,l_all) org_name,
actn.name actn_typ_name,
agf.name agf_fctr,
bft.min_age min_age,
bft.max_age max_age,
los.name los_fctr,
bft.min_los min_los,
bft.max_los max_los,
cla.name cmbn_age_los_fctr,
bft.date_from date_from,
conc.user_concurrent_program_name conc_prog_name,
fnd1.meaning
from ben_benefit_actions bft,
hr_lookups hr1,
hr_lookups hr2,
hr_lookups hr3,
hr_lookups hr4,
hr_lookups hr5,
hr_lookups hr6,
hr_lookups hr7,
fnd_lookups fnd1,
ben_age_fctr agf,
ben_los_fctr los,
ben_cmbn_age_los_fctr cla,
ben_cm_typ_f cct,
ben_actn_typ actn,
per_people_f ppf,
per_person_types ppt,
ben_pgm_f pgm1,
per_business_groups pbg,
ben_pl_f pln1,
ff_formulas_f ff,
ben_ler_f ler,
hr_all_organization_units org,
ben_popl_enrt_typ_cycl_f pop,
ben_enrt_perd epo,
ben_pl_f pln2,
ben_pgm_f pgm2,
hr_locations_all loc,
fnd_concurrent_requests fnd,
fnd_concurrent_programs_tl conc
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') --NLS Fix
and bft.request_id = fnd.request_id
and hr1.lookup_code = bft.mode_cd
and hr1.lookup_type = 'BEN_BENTMPCM_MD'
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(+) = pop.enrt_typ_cycl_cd
and hr4.lookup_type(+) = 'BEN_ENRT_TYP_CYCL'
and hr5.lookup_code(+) = bft.elig_enrol_cd
and hr5.lookup_type(+) = 'BEN_ELIG_ENROL'
and hr6.lookup_code(+) = bft.audit_log_flag
and hr6.lookup_type(+) = 'YES_NO'
and hr7.lookup_code = bft.cm_trgr_typ_cd
and hr7.lookup_type = 'BEN_CM_TRGR_TYP'
and fnd.status_code = fnd1.lookup_code
and fnd1.lookup_type= 'CP_STATUS_CODE'
and cct.cm_typ_id(+) = bft.cm_typ_id
and bft.process_date
between nvl(cct.effective_start_date,bft.process_date)
and nvl(cct.effective_end_date,bft.process_date)
and loc.location_id(+) = bft.location_id
and actn.actn_typ_id(+) = bft.actn_typ_id
and agf.age_fctr_id(+) = bft.age_fctr_id
and los.los_fctr_id(+) = bft.los_fctr_id
and cla.cmbn_age_los_fctr_id(+) = bft.cmbn_age_los_fctr_id
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)
-- PB : 5422 :
-- and epo.enrt_perd_id(+) = bft.popl_enrt_typ_cycl_id
-- and epo.asnd_lf_evt_dt(+) = bft.lf_evt_ocrd_dt
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 ppt.person_type_id(+) = bft.person_type_id
and pgm1.pgm_id(+) = bft.pgm_id
and bft.process_date
between nvl(pgm1.effective_start_date,bft.process_date)
and nvl(pgm1.effective_end_date,bft.process_date)
and pbg.business_group_id = bft.business_group_id
and pln1.pl_id(+) = bft.pl_id
and bft.process_date
between nvl(pln1.effective_start_date,bft.process_date)
and nvl(pln1.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 ff.formula_id(+) = bft.person_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 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);
p_person_selection_rule,
p_ler,
p_organization,
p_actn_typ,
p_age_fctr,
p_min_age,
p_max_age,
p_los_fctr,
p_min_los,
p_max_los,
p_cmbn_age_los_fctr,
p_date_from,
p_concurrent_program_name,
p_status;