[Home] [Help]
5452: pbn.PER_IN_LER_ID,
5453: pbn.pbn_ATTRIBUTE_CATEGORY,
5454: /* Bug 12964358: Check whether there are date track updated records after the effective_start_date*/
5455: decode( (select 'Y' from dual where exists
5456: (select 'Y' from ben_pl_bnf_f pbn1
5457: where pbn1.per_in_ler_id = pbn.per_in_ler_id
5458: and pbn1.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
5459: and pbn1.business_group_id = pbn.business_group_id
5460: and pbn1.effective_start_date > pbn.effective_start_date
5459: and pbn1.business_group_id = pbn.business_group_id
5460: and pbn1.effective_start_date > pbn.effective_start_date
5461: and pbn1.pl_bnf_id = pbn.pl_bnf_id)),
5462: 'Y','Y','N') ftr_rec
5463: from ben_pl_bnf_f pbn
5464: where pbn.per_in_ler_id = p_bckdt_per_in_ler_id
5465: and pbn.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
5466: and pbn.business_group_id = p_business_group_id
5467: union
5537: and pbn.business_group_id = p_business_group_id
5538: and pbn.per_in_ler_id = p_bckdt_per_in_ler_id
5539: and p_effective_date between pbn.effective_start_date
5540: and pbn.effective_end_date
5541: and pbn.bkup_tbl_typ_cd = 'BEN_PL_BNF_F'
5542: order by 1;
5543: --
5544: l_old_pl_bnf_id number(15);
5545: l_pl_bnf_id number(15);
5548: l_bnf_object_version_number number(9);
5549: --
5550: cursor c_bnf(cp_bnf_person_id in number) is
5551: select pbn.*
5552: from ben_pl_bnf_f pbn
5553: where pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5554: and pbn.bnf_person_id = cp_bnf_person_id
5555: and pbn.business_group_id = p_business_group_id
5556: -- and pbn.per_in_ler_id = p_per_in_ler_id --BUG Bug 4178570
5562:
5563: /* Bug 12964358 */
5564: cursor c_bnf_rec(c_bnf_pl_id number,c_effective_date date) is
5565: select pbn.*
5566: from ben_pl_bnf_f pbn
5567: where pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5568: and pbn.pl_bnf_id = c_bnf_pl_id
5569: and pbn.business_group_id = p_business_group_id
5570: and c_effective_date between pbn.effective_start_date