[Home] [Help]
3: --
4: g_package varchar2(50) := 'ben_populate_rbv.';
5: --
6: Type t_ppl_tab is table of ben_ptnl_ler_for_per%rowtype index by binary_integer;
7: Type t_pil_tab is table of ben_per_in_ler%rowtype index by binary_integer;
8: Type t_crp_tab is table of ben_cbr_per_in_ler%rowtype index by binary_integer;
9: Type t_cqb_tab is table of ben_cbr_quald_bnf%rowtype index by binary_integer;
10: Type t_pep_tab is table of ben_elig_per_f%rowtype index by binary_integer;
11: Type t_epo_tab is table of ben_elig_per_opt_f%rowtype index by binary_integer;
123: --
124: for elenum in p_pil_set.first ..p_pil_set.last
125: loop
126: --
127: insert into ben_per_in_ler_rbv
128: (per_in_ler_id
129: ,per_in_ler_stat_cd
130: ,prvs_stat_cd
131: ,lf_evt_ocrd_dt
2978: ,c_lu_dt date
2979: )
2980: is
2981: select pil.*
2982: from ben_per_in_ler pil
2983: where pil.person_id = c_per_id
2984: and pil.last_update_date
2985: between c_lu_dt and sysdate
2986: order by pil.per_in_ler_id;
2991: )
2992: is
2993: select crp.*
2994: from ben_cbr_per_in_ler crp,
2995: ben_per_in_ler pil
2996: where pil.person_id = c_per_id
2997: and crp.per_in_ler_id = pil.per_in_ler_id
2998: and crp.last_update_date
2999: between c_lu_dt and sysdate
3037: )
3038: is
3039: select epo.*
3040: from ben_elig_per_opt_f epo,
3041: ben_per_in_ler pil
3042: where pil.person_id = c_per_id
3043: and pil.per_in_ler_id = epo.per_in_ler_id
3044: and epo.last_update_date
3045: between c_lu_dt and sysdate
3052: )
3053: is
3054: select epe.*
3055: from ben_elig_per_elctbl_chc epe,
3056: ben_per_in_ler pil
3057: where pil.person_id = c_per_id
3058: and epe.per_in_ler_id = pil.per_in_ler_id
3059: and epe.last_update_date
3060: between c_lu_dt and sysdate
3066: )
3067: is
3068: select pel.*
3069: from ben_pil_elctbl_chc_popl pel,
3070: ben_per_in_ler pil
3071: where pil.person_id = c_per_id
3072: and pel.per_in_ler_id = pil.per_in_ler_id
3073: and pel.last_update_date
3074: between c_lu_dt and sysdate
3082: is
3083: select ecc.*
3084: from ben_elctbl_chc_ctfn ecc,
3085: ben_elig_per_elctbl_chc epe,
3086: ben_per_in_ler pil
3087: where pil.person_id = c_per_id
3088: and epe.per_in_ler_id = pil.per_in_ler_id
3089: and epe.ELIG_PER_ELCTBL_CHC_ID = ecc.ELIG_PER_ELCTBL_CHC_ID
3090: and ecc.last_update_date > c_lu_dt
3092: select ecc.*
3093: from ben_elctbl_chc_ctfn ecc,
3094: ben_enrt_bnft enb,
3095: ben_elig_per_elctbl_chc epe,
3096: ben_per_in_ler pil
3097: where pil.person_id = c_per_id
3098: and epe.per_in_ler_id = pil.per_in_ler_id
3099: and epe.ELIG_PER_ELCTBL_CHC_ID = enb.ELIG_PER_ELCTBL_CHC_ID
3100: and ecc.enrt_bnft_id = enb.enrt_bnft_id
3109: is
3110: select ecc.*
3111: from ben_elctbl_chc_ctfn ecc,
3112: ben_elig_per_elctbl_chc epe,
3113: ben_per_in_ler pil
3114: where pil.person_id = c_per_id
3115: and epe.per_in_ler_id = pil.per_in_ler_id
3116: and epe.ELIG_PER_ELCTBL_CHC_ID = ecc.ELIG_PER_ELCTBL_CHC_ID
3117: and ecc.last_update_date
3128: select ecc.*
3129: from ben_elctbl_chc_ctfn ecc,
3130: ben_enrt_bnft enb,
3131: ben_elig_per_elctbl_chc epe,
3132: ben_per_in_ler pil
3133: where pil.person_id = c_per_id
3134: and epe.per_in_ler_id = pil.per_in_ler_id
3135: and epe.ELIG_PER_ELCTBL_CHC_ID = enb.ELIG_PER_ELCTBL_CHC_ID
3136: and ecc.enrt_bnft_id = enb.enrt_bnft_id
3147: )
3148: is
3149: select egd.*
3150: from ben_elig_dpnt egd,
3151: ben_per_in_ler pil
3152: where pil.person_id = c_per_id
3153: and egd.per_in_ler_id = pil.per_in_ler_id
3154: and egd.last_update_date
3155: between c_lu_dt and sysdate
3161: )
3162: is
3163: select pdp.*
3164: from ben_elig_cvrd_dpnt_f pdp,
3165: ben_per_in_ler pil
3166: where pil.person_id = c_per_id
3167: and pdp.per_in_ler_id = pil.per_in_ler_id
3168: and pdp.last_update_date
3169: between c_lu_dt and sysdate
3188: is
3189: select enb.*
3190: from ben_enrt_bnft enb,
3191: ben_elig_per_elctbl_chc epe,
3192: ben_per_in_ler pil
3193: where pil.person_id = c_per_id
3194: and epe.per_in_ler_id = pil.per_in_ler_id
3195: and epe.ELIG_PER_ELCTBL_CHC_ID = enb.ELIG_PER_ELCTBL_CHC_ID
3196: and enb.last_update_date
3205: is
3206: select epr.*
3207: from ben_enrt_prem epr,
3208: ben_elig_per_elctbl_chc epe,
3209: ben_per_in_ler pil
3210: where pil.person_id = c_per_id
3211: and epe.per_in_ler_id = pil.per_in_ler_id
3212: and epe.ELIG_PER_ELCTBL_CHC_ID = epr.ELIG_PER_ELCTBL_CHC_ID
3213: and epr.last_update_date
3216: select epr.*
3217: from ben_enrt_prem epr,
3218: ben_enrt_bnft enb,
3219: ben_elig_per_elctbl_chc epe,
3220: ben_per_in_ler pil
3221: where pil.person_id = c_per_id
3222: and epe.per_in_ler_id = pil.per_in_ler_id
3223: and epe.ELIG_PER_ELCTBL_CHC_ID = enb.ELIG_PER_ELCTBL_CHC_ID
3224: and epr.enrt_bnft_id = enb.enrt_bnft_id
3234: is
3235: select epr.*
3236: from ben_enrt_prem epr,
3237: ben_elig_per_elctbl_chc epe,
3238: ben_per_in_ler pil
3239: where pil.person_id = c_per_id
3240: and epe.per_in_ler_id = pil.per_in_ler_id
3241: and epe.ELIG_PER_ELCTBL_CHC_ID = epr.ELIG_PER_ELCTBL_CHC_ID
3242: and epr.last_update_date
3251: select epr.*
3252: from ben_enrt_prem epr,
3253: ben_enrt_bnft enb,
3254: ben_elig_per_elctbl_chc epe,
3255: ben_per_in_ler pil
3256: where pil.person_id = c_per_id
3257: and epe.per_in_ler_id = pil.per_in_ler_id
3258: and epe.ELIG_PER_ELCTBL_CHC_ID = enb.ELIG_PER_ELCTBL_CHC_ID
3259: and epr.enrt_bnft_id = enb.enrt_bnft_id
3269: is
3270: select ecr.*
3271: from ben_enrt_rt ecr,
3272: ben_elig_per_elctbl_chc epe,
3273: ben_per_in_ler pil
3274: where pil.person_id = c_per_id
3275: and epe.per_in_ler_id = pil.per_in_ler_id
3276: and epe.ELIG_PER_ELCTBL_CHC_ID = ecr.ELIG_PER_ELCTBL_CHC_ID
3277: and ecr.last_update_date
3280: select ecr.*
3281: from ben_enrt_rt ecr,
3282: ben_enrt_bnft enb,
3283: ben_elig_per_elctbl_chc epe,
3284: ben_per_in_ler pil
3285: where pil.person_id = c_per_id
3286: and epe.per_in_ler_id = pil.per_in_ler_id
3287: and epe.ELIG_PER_ELCTBL_CHC_ID = enb.ELIG_PER_ELCTBL_CHC_ID
3288: and ecr.enrt_bnft_id = enb.enrt_bnft_id
3298: is
3299: select ecr.*
3300: from ben_enrt_rt ecr,
3301: ben_elig_per_elctbl_chc epe,
3302: ben_per_in_ler pil
3303: where pil.person_id = c_per_id
3304: and epe.per_in_ler_id = pil.per_in_ler_id
3305: and ecr.enrt_bnft_id is null
3306: and epe.ELIG_PER_ELCTBL_CHC_ID = ecr.ELIG_PER_ELCTBL_CHC_ID
3316: select ecr.*
3317: from ben_enrt_rt ecr,
3318: ben_enrt_bnft enb,
3319: ben_elig_per_elctbl_chc epe,
3320: ben_per_in_ler pil
3321: where pil.person_id = c_per_id
3322: and epe.per_in_ler_id = pil.per_in_ler_id
3323: and epe.ELIG_PER_ELCTBL_CHC_ID = enb.ELIG_PER_ELCTBL_CHC_ID
3324: and ecr.ELIG_PER_ELCTBL_CHC_ID is null
3333: )
3334: is
3335: select prv.*
3336: from ben_prtt_rt_val prv,
3337: ben_per_in_ler pil
3338: where pil.person_id = c_per_id
3339: and prv.per_in_ler_id = pil.per_in_ler_id
3340: and prv.last_update_date
3341: between c_lu_dt and sysdate
3371: )
3372: is
3373: select bpl.*
3374: from ben_bnft_prvdd_ldgr_f bpl,
3375: ben_per_in_ler pil
3376: where pil.person_id = c_per_id
3377: and bpl.per_in_ler_id = pil.per_in_ler_id
3378: and bpl.last_update_date
3379: between c_lu_dt and sysdate
3385: )
3386: is
3387: select cwbmh.*
3388: from ben_elig_per_elctbl_chc epe,
3389: ben_per_in_ler pil,
3390: ben_cwb_mgr_hrchy cwbmh
3391: where pil.person_id = c_per_id
3392: and epe.per_in_ler_id = pil.per_in_ler_id
3393: and epe.elig_per_elctbl_chc_id = cwbmh.emp_elig_per_elctbl_chc_id
3470: ,p_person_action_id => p_person_action_id
3471: ,p_ppl_set => l_ppl_set
3472: );
3473: --
3474: l_table_name := 'BEN_PER_IN_LER_RBV';
3475: l_elenum := 0;
3476: --
3477: for row in c_pilrbv
3478: (c_per_id => l_person_id