[Home] [Help]
1867: -- Following are the tables whose data will be compared to
1868: -- find any differences exists between the two runs of same ler.
1869: --
1870: -- ben_elig_cvrd_dpnt_f
1871: -- ben_prtt_enrt_actn_f
1872: -- ben_elig_per_elctbl_chc -- 9999 done
1873: -- ben_enrt_bnft
1874: -- ben_enrt_rt
1875: -- ben_elctbl_chc_ctfn
2253: -- Following are the tables whose data will be compared to
2254: -- find any differences exists between the two runs of same ler.
2255: --
2256: -- ben_elig_cvrd_dpnt_f
2257: -- ben_prtt_enrt_actn_f
2258: -- ben_elig_per_elctbl_chc
2259: -- ben_enrt_bnft
2260: -- ben_enrt_rt
2261: -- ben_elctbl_chc_ctfn
4327: --
4328: cursor c_old_pea is
4329: select pea.CMPLTD_DT,
4330: pea.ACTN_TYP_ID
4331: from ben_prtt_enrt_actn_f pea
4332: where pea.per_in_ler_id = p_bckdt_per_in_ler_id
4333: and pea.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
4334: and pea.business_group_id = p_business_group_id
4335: and nvl(pea.pl_bnf_id, -1) = nvl(p_old_pl_bnf_id, -1)
4351: and nvl(pea.dpnt_person_id, -1) = nvl(p_old_elig_cvrd_dpnt_id, -1)
4352: -- dpnt_person_id used as elig_cvrd_dpnt_id
4353: -- and pea.ENRT_BNFT_ID is null -- used as pea.PL_BNF_ID
4354: -- and pea.DPNT_PERSON_ID is null -- used as ELIG_CVRD_DPNT_ID is null
4355: and pea.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_ACTN_F'
4356: order by 1; -- pbn.effective_end_date; -- Low to High
4357: --
4358: l_prtt_enrt_actn_id number(15);
4359: l_pea_effective_start_date date;
4361: l_pea_object_version_number number(9);
4362: --
4363: cursor c_pea(cp_actn_typ_id in number) is
4364: select pea.*
4365: from ben_prtt_enrt_actn_f pea
4366: where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4367: and pea.actn_typ_id = cp_actn_typ_id
4368: and pea.business_group_id = p_business_group_id
4369: and pea.per_in_ler_id = p_per_in_ler_id
4452: cursor c_old_cpp is
4453: select cpp.DPNT_DSGN_CTFN_RECD_DT,
4454: pea.ACTN_TYP_ID,
4455: cpp.DPNT_DSGN_CTFN_TYP_CD
4456: from ben_prtt_enrt_actn_f pea,
4457: ben_cvrd_dpnt_ctfn_prvdd_f cpp
4458: where pea.per_in_ler_id = p_bckdt_per_in_ler_id
4459: and pea.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
4460: and pea.business_group_id = p_business_group_id
4473: --
4474: cursor c_cpp(cp_actn_typ_id in number,
4475: cp_DPNT_DSGN_CTFN_TYP_CD in varchar2) is
4476: select cpp.*
4477: from ben_prtt_enrt_actn_f pea,
4478: ben_cvrd_dpnt_ctfn_prvdd_f cpp
4479: where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4480: and pea.actn_typ_id = cp_actn_typ_id
4481: and pea.business_group_id = p_business_group_id
5001: cursor c_old_pbc is
5002: select pbc.BNF_CTFN_RECD_DT,
5003: pea.ACTN_TYP_ID,
5004: pbc.BNF_CTFN_TYP_CD
5005: from ben_prtt_enrt_actn_f pea,
5006: ben_pl_bnf_ctfn_prvdd_f pbc
5007: where pea.per_in_ler_id = p_bckdt_per_in_ler_id
5008: and pea.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
5009: and pea.business_group_id = p_business_group_id
5019: --
5020: cursor c_pbc(cp_actn_typ_id in number,
5021: cp_BNF_CTFN_TYP_CD in varchar2) is
5022: select pbc.*
5023: from ben_prtt_enrt_actn_f pea,
5024: ben_pl_bnf_ctfn_prvdd_f pbc
5025: where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5026: and pea.actn_typ_id = cp_actn_typ_id
5027: and pea.business_group_id = p_business_group_id
5493: --
5494: cursor c_old_pcs is
5495: select pcs.*, pea.ACTN_TYP_ID
5496: from ben_prtt_enrt_ctfn_prvdd_f pcs,
5497: ben_prtt_enrt_actn_f pea
5498: where pcs.prtt_enrt_actn_id = pea.prtt_enrt_actn_id
5499: and pcs.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
5500: and pea.per_in_ler_id = p_bckdt_per_in_ler_id
5501: and pea.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id
5513: cp_ENRT_R_BNFT_CTFN_CD in varchar2,
5514: cp_ACTN_TYP_ID in varchar2) is
5515: select pcs.*
5516: from ben_prtt_enrt_ctfn_prvdd_f pcs,
5517: ben_prtt_enrt_actn_f pea
5518: where pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5519: and nvl(pcs.enrt_ctfn_typ_cd, -1) = nvl(cp_enrt_ctfn_typ_cd, -1)
5520: and nvl(pcs.enrt_r_bnft_ctfn_cd, -1) = nvl(cp_enrt_r_bnft_ctfn_cd, -1)
5521: and pea.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id