APPS.BEN_GENERATE_DPNT_COMM dependencies on HR_API

Line 1538: and pen.enrt_cvg_thru_dt < hr_api.g_eot

1534: between ctu.effective_start_date
1535: and ctu.effective_end_date
1536: and ctu.cm_typ_id = p_cm_typ_id
1537: and ctu.all_r_any_cd = 'ALL'
1538: and pen.enrt_cvg_thru_dt < hr_api.g_eot
1539: and exists (select null
1540: from ben_pl_bnf_f pbn1,
1541: ben_per_in_ler pil
1542: where pbn1.bnf_person_id = p_rcpent_person_id

Line 1557: and pen.enrt_cvg_thru_dt < hr_api.g_eot

1553: from ben_prtt_enrt_rslt_f pen
1554: where pen.business_group_id = p_business_group_id
1555: and pen.prtt_enrt_rslt_stat_cd is null
1556: and pen.per_in_ler_id = p_per_in_ler_id
1557: and pen.enrt_cvg_thru_dt < hr_api.g_eot
1558: and exists (select null
1559: from ben_pl_bnf_f pbn,
1560: ben_per_in_ler pil
1561: where pbn.bnf_person_id = p_rcpent_person_id

Line 1617: and pen.enrt_cvg_thru_dt < hr_api.g_eot

1613: between ctu.effective_start_date
1614: and ctu.effective_end_date
1615: and ctu.cm_typ_id = p_cm_typ_id
1616: and ctu.all_r_any_cd = 'ALL'
1617: and pen.enrt_cvg_thru_dt < hr_api.g_eot
1618: and exists (select null
1619: from ben_elig_cvrd_dpnt_f pdp,
1620: ben_per_in_ler pil
1621: where pdp.dpnt_person_id = p_rcpent_person_id

Line 1635: and pen.enrt_cvg_thru_dt < hr_api.g_eot

1631: from ben_prtt_enrt_rslt_f pen
1632: where pen.business_group_id = p_business_group_id
1633: and pen.prtt_enrt_rslt_stat_cd is null
1634: and pen.per_in_ler_id = p_per_in_ler_id
1635: and pen.enrt_cvg_thru_dt < hr_api.g_eot
1636: and exists (select null
1637: from ben_elig_cvrd_dpnt_f pdp,
1638: ben_per_in_ler pil
1639: where pdp.dpnt_person_id = p_rcpent_person_id

Line 4295: and pdp.cvg_thru_dt <> hr_api.g_eot

4291: ben_oipl_f cop,
4292: ben_opt_f opt
4293: where pdp.per_in_ler_id = p_per_in_ler_id
4294: and pdp.dpnt_person_id = p_rcpent_person_id
4295: and pdp.cvg_thru_dt <> hr_api.g_eot
4296: and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
4297: and pen.sspndd_flag = 'N'
4298: and pen.prtt_enrt_rslt_stat_cd is null
4299: and p_effective_date between

Line 4341: and pdp.cvg_thru_dt <> hr_api.g_eot

4337: ben_opt_f opt
4338: where pdp.effective_start_date = p_effective_date
4339: and pdp.dpnt_person_id = p_rcpent_person_id
4340: and pdp.request_id = fnd_global.conc_request_id
4341: and pdp.cvg_thru_dt <> hr_api.g_eot
4342: and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
4343: and pen.pgm_id = p_pgm_id
4344: and pen.pl_typ_id = p_pl_typ_id
4345: and pen.sspndd_flag = 'N'

Line 4422: and pen.enrt_cvg_thru_dt = hr_api.g_eot

4418: where pen.person_id = p_person_id
4419: and pen.prtt_enrt_rslt_stat_cd is null
4420: and pen.pl_typ_id = l_pl_typ_id
4421: and pen.sspndd_flag = 'N'
4422: and pen.enrt_cvg_thru_dt = hr_api.g_eot
4423: and pen.effective_end_date = hr_api.g_eot
4424: and p_effective_date between pen.effective_start_date
4425: and pen.effective_end_date
4426: and pen.business_group_id = p_business_group_id

Line 4423: and pen.effective_end_date = hr_api.g_eot

4419: and pen.prtt_enrt_rslt_stat_cd is null
4420: and pen.pl_typ_id = l_pl_typ_id
4421: and pen.sspndd_flag = 'N'
4422: and pen.enrt_cvg_thru_dt = hr_api.g_eot
4423: and pen.effective_end_date = hr_api.g_eot
4424: and p_effective_date between pen.effective_start_date
4425: and pen.effective_end_date
4426: and pen.business_group_id = p_business_group_id
4427: and pen.pl_id = pln.pl_id

Line 4462: where pdp.cvg_thru_dt = hr_api.g_eot

4458: ben_pl_f pln,
4459: ben_oipl_f cop,
4460: ben_opt_f opt,
4461: ben_per_in_ler pil
4462: where pdp.cvg_thru_dt = hr_api.g_eot
4463: and pdp.dpnt_person_id = p_rcpent_person_id
4464: and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
4465: -- and nvl(pen.pgm_id,-1) = nvl(l_pgm_id,-1) maagrawa(02/11/00)
4466: and pen.pl_typ_id = l_pl_typ_id

Line 4467: and pen.enrt_cvg_thru_dt = hr_api.g_eot

4463: and pdp.dpnt_person_id = p_rcpent_person_id
4464: and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
4465: -- and nvl(pen.pgm_id,-1) = nvl(l_pgm_id,-1) maagrawa(02/11/00)
4466: and pen.pl_typ_id = l_pl_typ_id
4467: and pen.enrt_cvg_thru_dt = hr_api.g_eot
4468: and pen.sspndd_flag = 'N'
4469: and pen.prtt_enrt_rslt_stat_cd is null
4470: -- Bug 8271809 To check dependent is coverage is ended for the Current LifeEvent
4471: and pdp.per_in_ler_id = pil.per_in_ler_id

Line 4641: if l_max_enrt_cvg_thru_dt = hr_api.g_eot or

4637: -- also get de-enrolled. But in this case, we generate comm. for
4638: -- dpnt. only if they do not reside with the participant.
4639: -- Also send comm., when the prtt. is dead.
4640: --
4641: if l_max_enrt_cvg_thru_dt = hr_api.g_eot or
4642: p_rsds_with_prtt = 'N' or
4643: (p_rsds_with_prtt = 'Y'and l_max_enrt_cvg_thru_dt <> hr_api.g_eot) or
4644: l_prtt_per_rec.date_of_death is not null then
4645: --

Line 4643: (p_rsds_with_prtt = 'Y'and l_max_enrt_cvg_thru_dt <> hr_api.g_eot) or

4639: -- Also send comm., when the prtt. is dead.
4640: --
4641: if l_max_enrt_cvg_thru_dt = hr_api.g_eot or
4642: p_rsds_with_prtt = 'N' or
4643: (p_rsds_with_prtt = 'Y'and l_max_enrt_cvg_thru_dt <> hr_api.g_eot) or
4644: l_prtt_per_rec.date_of_death is not null then
4645: --
4646: l_epe_exists := 'N';
4647: l_crntly_enrd := 'N';

Line 4685: l_max_enrt_cvg_thru_dt <> hr_api.g_eot and

4681: -- If dependent resides with the participant, generate a
4682: -- communication only if the participant is still enrolled.
4683: --
4684: if (p_rsds_with_prtt = 'Y' and
4685: l_max_enrt_cvg_thru_dt <> hr_api.g_eot and
4686: l_prtt_per_rec.date_of_death is null) then
4687: --
4688: -- Check if the participant is still enrolled.
4689: --

Line 4754: l_max_enrt_cvg_thru_dt = hr_api.g_eot or

4750: hr_utility.set_location('l_prtt_crntly_enrd: '||l_prtt_crntly_enrd,40);
4751: -- 3585939
4752: if ( (l_prtt_crntly_enrd = 'Y' or l_prtt_epe_exists = 'Y')
4753: or
4754: l_max_enrt_cvg_thru_dt = hr_api.g_eot or
4755: p_rsds_with_prtt = 'N' or
4756: l_prtt_per_rec.date_of_death is not null) then
4757: --
4758: hr_utility.set_location('No Epe: '||l_proc,40);