[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* not, it will update the status to "not covered" and end date the row.
*
* History
* Date Who Version What?
* ------- --------- ------- --------------------------------------
* 30-Nov-98 jcarpent 115.0 Created
* 22-Dec-98 jcarpent 115.1 Changed end date logic
* 03-Jan-98 Tmathers 115.2 Made compilable.
* 06-Jan-98 jcarpent 115.3 Added reporting.
* 19-Feb-99 gperry 115.4 Added call to communciations for when
* a dependent is being deleted.
* 23-Feb-99 jcarpent 115.5 Changed effective date format
* 03-Mar-99 stee 115.6 Removed dbms_output.put_line.
* 22-Mar-99 Tmathers 115.8 Changed -MON- to /MM/'
* 28-Apr-99 lmcdonal 115.10 prtt_enrt_rslt now has a status code.
* 30-apr-99 shdas 115.11 modified contexts to rule calls.
* 10-may-99 jcarpent 115.12 Check ('VOIDD', 'BCKDT') for pil stat
* 18-may-99 jcarpent 115.13 Changed delete_pdp to update_pdp,
* init PROC_INFO, and added commits
* 25-may-99 jcarpent 115.14 Changed end date handling if bendetdt
* returns null. Removed sub-titles
* for error reports to fix concurrent
* manager display.
* 05-JUL-99 stee 115.15 Create benefit assignments for
* dependents who become ineligible.
* 20-JUL-99 Gperry 115.16 genutils -> benutils package rename.
* 24-Aug-99 maagrawa 115.17 Dependents related changes.
* 30-Aug-99 maagrawa 115.18 Added parameter p_dpnt_inelig_rsn_cd
* to dependent eligibility process
* to return ineligible reason.
* 02-Sep-99 maagrawa 115.19 Added HIPAA communications.
* 07-Sep-99 tguy 115.20 fixed call to pay_mag_util
* 06-Oct-99 tguy 115.21 added call to dt_fndate
* 12-Oct-99 maagrawa 115.22 ben_env_object.init call added
* to do_multithread.
* 02-Nov-99 maagrawa 115.23 Coverage through date to be effective
* date if the calculated date is less
* than coverage start date.
* 03-Jan-00 maagrawa 115.24 Added pl_typ_id to comm. process.
* 17-Jan-00 maagrawa 115.25 Moved the HIPAA logic to bencommu
* and bencomde.
* 28-Jan-00 pbodla 115.26 - Fidelity Bug : 5445, Move the benefits
* assignment creation out nocopy of for loop.
* - Submit_all_reports is modified
* 17-Feb-00 jcarpent 115.27 - Pass correct elig_thru_dt to
* update_elig_dpnt. bug 4719
* 18-Feb-00 gperry 115.28 Multithread was calling wrong process.
* now calling BENMDSGL.
* WWBUG 1201093.
* 25-Feb-00 jcarpent 115.29 - Pass person_id to person selection rl.
* 14-Mar-00 maagrawa 115.30 Pass the elig_change_dt as the life event
* occured date to comm. process.(1230019)
* 31-Mar-00 maagrawa 115.31 Pass the cvg_strt_dt to dpnt.
* eligibilty process (4929).
* If found ineligible, end the eligibiltity
* for all the comp. objects depending on
* the level at which eligibilty is
* defined.
* 03-Apr-00 mmogel 115.32 - Added tokens to messages to make them
* more meaningful to the user
* 07-Jun-00 jcarpent 115.33 - Added init of l_returned_end_dt since
* was bleeding to other comp objects
* for dependent. (4728)
* 29-aug-01 pbodla 115.34 - bug:1949361 jurisdiction code is
* derived inside benutils.formula
* 18-dec-01 tjesumic 115.35 - cwb changes
* 20-dec-01 ikasire 115.36 added dbdrv lines
* 20-dec-01 ikasire 115.37 added commit
* 12-feb-02 pabodla 115.38 Bug 1579948 Fix: if g_profile_value is
* 'N' then do not write no designation
* change records into the batch log tables
* 04-App-02 pabodla 115.39 Bug 1579948 Fix: added if stmt to
* restrict a row to write into the
* ben_batch_dpnt_info if
* g_profile_value set to "No". This is
* done for improving the performance
* of audit log
* 30-Apr-02 kmahendr 115.40 Added write calls to capture error messages.
* 08-Jun-02 pabodla 115.41 Do not select the contingent worker
* assignment when assignment data is
* fetched.
* 11-jul-02 tjesumic 115.42 # 2455430 The coverage end date is calculated
* on the basis of event occured date
* 19-dec-2002 pabodla 115.43 NOCOPY Changes
* 02-Jun-2003 rpgupta 115.44 Bug 2985206
* Changed cursor c_person
* 02-Jun-2003 rpgupta 115.45 Bug 2985206
* Changed cursor c_person again to merge
* similar join conditions
* 02-Jun-2003 hmani 115.46 Bug 2985206
* Changed cursor c_person again to merge
* similar join conditions - Rearcsed in
* 27-Jul-2003 pabodla 115.47 Bug 3056894 - Added personal_flag to
* c_designation to consider only
* personal relationships.
* 16-Jun-2004 bmanyam 115.48 Bug 3657077 - In calc_dpnt_cvg_end_dt procedure
* passed the correct param (l_cvg_end_rl) ot
* ben_determine_date.main
* 05-Aug-2004 rpgupta 115.49 Bug 3808703 - c_person does not return any
* rows when the pl_id is specified. Its coded to
* use pl_id only for plans not in programs.
* 03-Dec-2004 ikasire 115.50 Bug 4046914
* 30-dec-2004 nhunur 115.51 4031733 - No need to open cursor c_state
* 08-Jan-2005 mmudigon115.52 4398114 - changed <= to >= in line 1836
* 05-May-06 rtagarra 115.53 Bug#5070692:Changed the parameter value from FALSE to TRUE so that when run the
Maintain Designee Eligibility warnings will come in the log file when dependent coverage
start and end date has date earlier than LE occured date.
* 09-May-06 rtagarra 115.54 Change in exception block for process_designee_elig.
* 26-May-06 bmanyam 115.55 5100008 - EGD elig_thru_dt is the
date eligibility is lost. Previously the elig_thru_dt
was updated with PDP cvg_thru_dt.
07-aug-06 ssarkar 115.56 5442301 - dont update elig_cvrd_dpnt if it is overriden
* 01-Dec-06 rtagarra 115.57 5662220 - Added check so that when there is no condition for a person in person_selection_rule
then skip the person.
* 05-Apr-07 rtagarra 115.58 5908080 - Continued for fix 5442301: Added date condition for c_designation.
* 14-Jan-07 rtagarra 115.59 6747807 - Fixed cursor c_person and c_designation
* 05-Feb-09 krupani 115.61 7718592 - Reverted back fix against 5908080
* 27-Sep-12 usaraswa 115.62 14552686 - when dependent enrollment record(other than coverage thru date) is
overrided through enrollment override form then dependent coverage is not ending though the dependent became ineligible.
10-Dec-12 usaraswa 115.63 14775822 - Modified cursor c_designation to fetch only one row though multiple rows
are present for dependent in per_contact_relationships table.
* -----------------------------------------------------------------------------
*/
--
-- Global cursor and variables declaration
--
g_package varchar2(80) := 'Ben_maintain_designee_elig';
Select ran.range_id
,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.person_id
,ben.person_action_id
,ben.object_version_number
,ben.ler_id
From ben_person_actions ben
Where ben.benefit_action_id = p_benefit_action_id
And ben.action_status_cd <> 'P'
And ben.person_action_id between
l_start_person_action_id and l_end_person_action_id
Order by ben.person_action_id
;
Select *
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id
;
,p_person_selection_rule_id => l_parm.person_selection_rl
,p_comp_selection_rule_id => l_parm.comp_selection_rl
,p_pgm_id => l_parm.pgm_id
,p_pl_id => l_parm.pl_id
,p_person_type_id => l_parm.person_type_id
,p_ler_id => null
,p_organization_id => l_parm.organization_id
,p_benfts_grp_id => l_parm.benfts_grp_id
,p_location_id => l_parm.location_id
,p_legal_entity_id => l_parm.legal_entity_id
,p_payroll_id => l_parm.payroll_id
);
update ben_batch_ranges ran set ran.range_status_cd = 'P'
where ran.range_id = l_range_id;
g_cache_person_process.delete;
,p_comp_selection_rl => l_parm.comp_selection_rl
,p_pgm_id => l_parm.pgm_id
,p_pl_id => l_parm.pl_id
,p_object_version_number => g_cache_person_process(l_cnt).object_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => l_effective_date
);
Select process_date
,mode_cd
,validate_flag
,person_id
,person_type_id
,pgm_id
,business_group_id
,pl_id
,popl_enrt_typ_cycl_id
,person_selection_rl
,comp_selection_rl
,ler_id
,organization_id
,benfts_grp_id
,location_id
,legal_entity_id
,payroll_id
,debug_messages_flag
From ben_benefit_actions ben
Where ben.benefit_action_id = p_benefit_action_id;
,p_person_selection_rule_id => l_parameters.person_selection_rl
,p_comp_selection_rule_id => l_parameters.comp_selection_rl
,p_pgm_id => l_parameters.pgm_id
,p_pl_id => l_parameters.pl_id
,p_organization_id => l_parameters.organization_id
,p_benfts_grp_id => l_parameters.benfts_grp_id
,p_location_id => l_parameters.location_id
,p_legal_entity_id => l_parameters.legal_entity_id
,p_payroll_id => l_parameters.payroll_id
,p_debug_messages => l_parameters.debug_messages_flag
);
,p_person_selection_rule_id in number default null
,p_comp_selection_rule_id in number default null
,p_pgm_id in number default null
,p_pl_id in number default null
,p_organization_id in number default null
,p_benfts_grp_id in number default null
,p_location_id in number default null
,p_legal_entity_id in number default null
,p_payroll_id in number default null
,p_debug_messages in varchar2 default 'N'
) is
l_effective_date date;
Select distinct ecd.dpnt_person_id, pil.person_id
From ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
Where pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and ( p_pgm_id is null or pen.pgm_id=p_pgm_id)
and ( p_pl_id is null or pen.pl_id = p_pl_id)--3808703
--(pen.pl_id=p_pl_id and pen.pgm_id is null))
and l_effective_date between
pen.effective_start_date and pen.effective_end_date
and l_effective_date between
pen.enrt_cvg_strt_dt and nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot)
and ecd.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
and (nvl(ecd.ovrdn_flag,'N')='N' or
nvl(ecd.ovrdn_thru_dt,l_effective_date)>=l_effective_date)
and ecd.business_group_id = pen.business_group_id
and l_effective_date between
ecd.effective_start_date and ecd.effective_end_date
and l_effective_date between
ecd.cvg_strt_dt and ecd.cvg_thru_dt
and (p_person_id is NULL or pen.person_id = P_person_id)
and (p_person_type_id is null
or exists (select null
from per_person_type_usages ptu
where ptu.person_id = pen.person_id
and ptu.person_type_id = P_person_type_id
)
)
/* bug 2985206 */
/* and ( (p_location_id is null and p_organization_id is null
and p_legal_entity_id is null and p_payroll_id is null
) or exists (select null
from per_assignments_f asg
where nvl(asg.location_id,hr_api.g_number) =
nvl(p_location_id,hr_api.g_number)
and nvl(asg.organization_id,hr_api.g_number) =
nvl(p_organization_id,hr_api.g_number)
and nvl(asg.soft_coding_keyflex_id,
hr_api.g_number) =
nvl(p_legal_entity_id,hr_api.g_number)
and nvl(asg.payroll_id, hr_api.g_number)=
nvl(p_payroll_id,hr_api.g_number)
and asg.person_id = pen.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id =
pen.business_group_id
and l_effective_date between
asg.effective_start_date and
asg.effective_end_date
)
)
*/
and (
(p_location_id is null and p_organization_id is null
and p_payroll_id is null
)
or exists (select null
from per_assignments_f asg
where nvl(asg.location_id,hr_api.g_number) =
nvl(p_location_id,nvl(asg.location_id,hr_api.g_number))
and nvl(asg.organization_id,hr_api.g_number) =
nvl(p_organization_id,nvl(asg.organization_id,hr_api.g_number))
and nvl(asg.payroll_id,hr_api.g_number) =
nvl(p_payroll_id,nvl(asg.payroll_id,hr_api.g_number))
and asg.person_id = pen.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id =
pen.business_group_id
and l_effective_date between
asg.effective_start_date and
asg.effective_end_date
)
)
and (p_legal_entity_id is null
or exists (select null
from hr_soft_coding_keyflex hsc,
per_all_assignments_f asg
where asg.person_id = pil.person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.business_group_id = pil.business_group_id
and l_effective_date
between asg.effective_start_date and asg.effective_end_date
and asg.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = to_char(p_legal_entity_id)
)
)
/* end bug 2985206 */
and (p_benfts_grp_id is null
or exists(select null
from ben_benfts_grp bng
,/*per_people_f*/ per_all_people_f ppf
where bng.benfts_grp_id = p_benfts_grp_id
and bng.business_group_id = pen.business_group_id
and ppf.person_id = pen.person_id
and ppf.benefit_group_id = bng.benfts_grp_id
and l_effective_date between
ppf.effective_start_date and
ppf.effective_end_date
)
)
and pil.per_in_ler_id=ecd.per_in_ler_id
--and pil.business_group_id=ecd.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
--and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
and pen.prtt_enrt_rslt_stat_cd is null
;
,p_comp_selection_rl => p_comp_selection_rule_id
,p_person_selection_rl => p_person_selection_rule_id
,p_ler_id => null
,p_organization_id => p_organization_id
,p_benfts_grp_id => p_benfts_grp_id
,p_location_id => p_location_id
,p_pstl_zip_rng_id => NULL
,p_rptg_grp_id => NULL
,p_pl_typ_id => NULL
,p_opt_id => NULL
,p_eligy_prfl_id => NULL
,p_vrbl_rt_prfl_id => NULL
,p_legal_entity_id => p_legal_entity_id
,p_payroll_id => p_payroll_id
,p_debug_messages_flag => p_debug_messages
,p_object_version_number => l_object_version_number
,p_effective_date => l_effective_date
,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
);
l_actn := 'Delete rows from ben_batch_ranges..';
Delete from ben_batch_ranges
Where benefit_action_id = l_benefit_action_id;
l_actn := 'Calling ben_batch_utils.person_selection_rule...';
If (p_person_selection_rule_id is not NULL) then
l_actn := 'Calling Ben_batch_utils.person_selection_rule...';
rl_ret := ben_batch_utils.person_selection_rule
(p_person_id => l_part_person_id
,p_business_group_id => p_business_group_id
,p_person_selection_rule_id=> p_person_selection_rule_id
,p_effective_date => l_effective_date
);
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_pgm_id => p_pgm_id
,p_pl_id => p_pl_id
,p_popl_enrt_typ_cycl_id => null
,p_person_id => p_person_id
,p_person_selection_rule_id => p_person_selection_rule_id
,p_person_type_id => p_person_type_id
,p_ler_id => null
,p_organization_id => p_organization_id
,p_benfts_grp_id => p_benfts_grp_id
,p_location_id => p_location_id
,p_legal_entity_id => p_legal_entity_id
,p_payroll_id => p_payroll_id
);
'<< No Person got selected with above selection criteria >>' );
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id);
,p_person_selected => l_person_cnt
,p_business_group_id => p_business_group_id
) ;
function comp_selection_rule
(p_person_id in number
,p_business_group_id in number
,p_pgm_id in number
,p_pl_id in number
,p_pl_typ_id in number
,p_opt_id in number
,p_ler_id in number
,p_oipl_id in number
,p_comp_selection_rule_id in number
,p_effective_date in date
) return char is
cursor c1 is
select assignment_id,organization_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 region_2
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.person_id = p_person_id
and asg.primary_flag = 'Y'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and asg.business_group_id=p_business_group_id;
l_proc varchar2(80) := g_package||'.person_selection_rule';
(p_formula_id => p_comp_selection_rule_id
,p_effective_date => p_effective_date
,p_pgm_id => p_pgm_id
,p_pl_id => p_pl_id
,p_pl_typ_id => p_pl_typ_id
,p_opt_id => p_opt_id
,p_ler_id => p_ler_id
,p_business_group_id => p_business_group_id
,p_assignment_id => l_assignment_id
,p_organization_id => l_organization_id
,p_jurisdiction_code => l_jurisdiction_code);
fnd_message.set_token('RL','formula_id :'||p_comp_selection_rule_id);
end comp_selection_rule;
,p_comp_selection_rl in number
,p_pgm_id in number
,p_pl_id in number
,p_object_version_number in out nocopy number
,p_business_group_id in number
,p_effective_date in date
) is
--
-- Local Cursor
--
cursor c_designation is
Select pen.pgm_id,
pen.pl_id,
pen.pl_typ_id,
pen.oipl_id,
pen.person_id,
pen.ptip_id,
pen.ler_id,
ecd.elig_cvrd_dpnt_id,
ecd.object_version_number,
ecd.ovrdn_flag,
ecd.ovrdn_thru_dt,
ecd.cvg_strt_dt,
ecd.effective_start_date,
ctr.contact_relationship_id,
ctr.contact_type,
ctr.date_end, -- 5100008 Added this
-- CWB Changes.
ecd.per_in_ler_id
From ben_prtt_enrt_rslt_f pen,
ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil,
per_contact_relationships ctr
Where pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and ecd.dpnt_person_id = p_person_id
and (nvl(ecd.ovrdn_flag,'N')='N' or
nvl(ecd.ovrdn_thru_dt,p_effective_date)>=p_effective_date)
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and p_effective_date between
pen.enrt_cvg_strt_dt and nvl(pen.enrt_cvg_thru_dt,
hr_api.g_eot)
and ecd.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
and ecd.business_group_id = pen.business_group_id
and p_effective_date between
ecd.effective_start_date and ecd.effective_end_date
and p_effective_date between
ecd.cvg_strt_dt and ecd.cvg_thru_dt
and ctr.person_id=pen.person_id
-- Bug 3056894
and ctr.personal_flag='Y'
and ctr.business_group_id=p_business_group_id
and ctr.contact_person_id=p_person_id
--Bug 14775822
and ctr.date_start in (SELECT max (date_start)
FROM per_contact_relationships
WHERE contact_person_id = p_person_id
AND person_id = pen.person_id
AND personal_flag = 'Y'
AND business_group_id = p_business_group_id
AND date_start <= p_effective_date)
--Bug 14775822
and pil.per_in_ler_id=ecd.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
--and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
and pen.prtt_enrt_rslt_stat_cd is null
;
select opt_id from ben_oipl_f oipl
where oipl.oipl_id = l_oipl_id
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date;
select pgm.dpnt_dsgn_lvl_cd
from ben_pgm_f pgm
where pgm.pgm_id = v_pgm_id
and pgm.business_group_id = p_business_group_id
and p_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select pep.elig_per_id
from ben_elig_per_f pep,
ben_pl_f pln,
ben_ptip_f ptip,
ben_per_in_ler pil
where pep.person_id = v_person_id
and pep.pl_id = pln.pl_id
and pln.pl_typ_id = ptip.pl_typ_id
and ptip.ptip_id = v_ptip_id
and ptip.pgm_id = pep.pgm_id
and p_effective_date between
pep.effective_start_date and pep.effective_end_date
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and p_effective_date between
ptip.effective_start_date and ptip.effective_end_date
and pep.per_in_ler_id = pil.per_in_ler_id(+)
and (pil.per_in_ler_stat_cd is null OR
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT'));
select pep.elig_per_id
from ben_elig_per_f pep,
ben_per_in_ler pil
where pep.person_id = v_person_id
and pep.pgm_id = v_pgm_id
and pep.pl_id is not null
and p_effective_date between
pep.effective_start_date and pep.effective_end_date
and pep.per_in_ler_id = pil.per_in_ler_id(+)
and (pil.per_in_ler_stat_cd is null OR
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT'));
select pep.elig_per_id
from ben_elig_per_f pep,
ben_per_in_ler pil
where pep.person_id = v_person_id
and pep.pl_id = v_pl_id
and ((v_pgm_id is null and pep.pgm_id is null) OR
(pep.pgm_id = v_pgm_id))
and p_effective_date between
pep.effective_start_date and pep.effective_end_date
and pep.per_in_ler_id = pil.per_in_ler_id(+)
and (pil.per_in_ler_stat_cd is null OR
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT'));
select max(egd.create_dt)
from ben_elig_dpnt egd,
ben_per_in_ler pil
where egd.elig_per_id = v_elig_per_id
and egd.dpnt_person_id = p_person_id
and egd.per_in_ler_id = pil.per_in_ler_id (+)
and (pil.per_in_ler_stat_cd is null OR
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT'));
select egd.*
from ben_elig_dpnt egd,
ben_per_in_ler pil
where egd.elig_per_id = v_elig_per_id
and egd.dpnt_person_id = p_person_id
and egd.dpnt_inelig_flag = 'N'
and egd.create_dt = v_create_dt
and egd.per_in_ler_id = pil.per_in_ler_id (+)
and (pil.per_in_ler_stat_cd is null OR
pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')) ;
if p_comp_selection_rl is not null then
hr_utility.set_location(l_proc,102);
l_rule_ret:=comp_selection_rule(
p_person_id => p_person_id
,p_business_group_id => p_business_group_id
,p_pgm_id => l_rec.pgm_id
,p_pl_id => l_rec.pl_id
,p_pl_typ_id => l_rec.pl_typ_id
,p_opt_id => l_opt.opt_id
,p_oipl_id => l_rec.oipl_id
,p_ler_id => l_rec.ler_id
,p_comp_selection_rule_id => p_comp_selection_rl
,p_effective_date => p_effective_date
);
l_datetrack_mode:=hr_api.g_update;
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
p_elig_cvrd_dpnt_id => l_rec.elig_cvrd_dpnt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_rec.object_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date
,p_cvg_thru_dt => l_returned_end_dt
,p_datetrack_mode => l_datetrack_mode
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
,p_multi_row_actn => false
);
ben_elig_dpnt_api.update_elig_dpnt(
p_elig_dpnt_id => l_egd_rec.elig_dpnt_id
,p_object_version_number => l_egd_rec.object_version_number
,p_effective_date => p_effective_date
,p_elig_thru_dt => l_egd_elig_thru_dt --l_returned_end_dt 5100008
,p_dpnt_inelig_flag => 'Y'
,p_inelg_rsn_cd => l_inelig_rsn_cd
);
ben_elig_dpnt_api.update_elig_dpnt(
p_elig_dpnt_id => l_egd.elig_dpnt_id
,p_object_version_number => l_egd.object_version_number
,p_effective_date => p_effective_date
,p_elig_thru_dt => l_egd_elig_thru_dt --l_returned_end_dt
,p_dpnt_inelig_flag => 'Y'
,p_inelg_rsn_cd => l_inelig_rsn_cd);
l_actn := 'Calling ben_person_actions_api.update_person_actions...';
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
,p_action_status_cd => 'P'
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
);
ben_person_actions_api.update_person_actions
(p_person_action_id => p_person_action_id
,p_action_status_cd => 'E'
,p_object_version_number => p_object_version_number
,p_effective_date => p_effective_date
);