[Home] [Help]
54: Delete logic changed for
55: BEN_PRTT_ENRT_RSLT_F
56: BEN_ELIG_CVRD_DPNT_F
57: BEN_PL_BNF_F
58: BEN_PRTT_RT_VAL and
59: communication tables
60: to use datetrack mode as future
61: delete if past records exist
62: for a different per_in_ler.
88: bnft_prvdd_ldgr, prtt_enrt_actn,
89: prtt_enrt_ctfn_prvdd,cvrd_dpnt_ctfn_prvdd,
90: pl_bnf_ctfn_prvdd,all per_cm% tables.
91: Removed because of per_in_ler status checks
92: 08-jul-99 pbodla 115.17 Code to backup data from ben_pl_bnf_f, BEN_PRTT_RT_VAL,
93: ben_prtt_prem, ben_elig_per, ben_elig_per_opt
94: ben_elig_cvrd_dpnt_f, ben_pil_elctbl_chc_popl,
95: ben_prtt_enrt_rslt_f added.
96: 09-jul-99 shdas 115.18 Moved unprocess_ptnl_ler proc here from benptnle.
216: pil. Also modified c_prv_of_
217: previous_pil to include join to
218: ben_per_in_ler to get l_person_id
219: based on this cursor rather than
220: c_ben_prtt_rt_val.
221: 03-JAN-01 tilak 115.53 - Bug 1182293 reimburement reqist table added
222: 09-feb-01 ikasire 115.54 Bug 1627373 and 1584238 to fix the
223: issue - can't backout if the
224: rate start date < life event occured date
298: fixed by copying the result of LE to backop table and copy
299: back to result when the opne is backed out
300: New three cursor created for copying back result , dpnt and bnf
301: 03-Sep-2003 rpgupta 115.88 3136058 Grade Step Backout
302: 26-Sep-2003 kmahendr 115.89 Modified cursor c_ben_prtt_rt_val for GHR fix
303: 29-Oct-2003 tjesumic 115.90 # 2982606 Result level backup added, new parameter
304: p_bckdt_prtt_enrt_rslt_id added for the purpose. if the param is not null
305: only the result of the p_bckdt_prtt_enrt_rslt_id is backed out
306: if the per_in_ler careated the result level backout then
1632: -- BEN_PRTT_ENRT_RSLT_F (PEN)
1633: -- BEN_PL_BNF_F (PBN) (join to pil to check status)
1634: -- BEN_PRTT_ENRT_ACTN_F (PEA) (join to pil to check status)
1635: -- BEN_BNFT_PRVDD_LDGR_F (BPL)(join to pil to check status)
1636: -- BEN_PRTT_RT_VAL (PRV)
1637: -- BEN_PRTT_REIMBMT_RQST_F
1638: -- BEN_PL_BNF_CTFN_PRVDD_F (PBC)(join to pil to check status)
1639: -- BEN_PRTT_ENRT_CTFN_PRVDD_F (PCS)(join to pil to check status)
1640: -- BEN_ELIG_PER_ELCTBL_CHC (EPE) (not deleted, join to pil to check status)
1782: p_bckdt_prtt_enrt_rslt_id => p_bckdt_prtt_enrt_rslt_id,
1783: p_copy_only => p_copy_only,
1784: p_effective_date => p_effective_date);
1785:
1786: hr_utility.set_location('BEN_PRTT_RT_VAL p_effective_date'||p_effective_date,1999);
1787: -- mark rates 'backed out'
1788: delete_routine(p_routine => 'BEN_PRTT_RT_VAL',
1789: p_per_in_ler_id => p_per_in_ler_id,
1790: p_business_group_id => p_business_group_id,
1784: p_effective_date => p_effective_date);
1785:
1786: hr_utility.set_location('BEN_PRTT_RT_VAL p_effective_date'||p_effective_date,1999);
1787: -- mark rates 'backed out'
1788: delete_routine(p_routine => 'BEN_PRTT_RT_VAL',
1789: p_per_in_ler_id => p_per_in_ler_id,
1790: p_business_group_id => p_business_group_id,
1791: p_bckdt_prtt_enrt_rslt_id => p_bckdt_prtt_enrt_rslt_id,
1792: p_copy_only => p_copy_only,
2632: p_effective_date date) is
2633: --
2634: cursor c_future_prv is
2635: select prv.*
2636: from ben_prtt_rt_val prv,
2637: ben_prtt_enrt_rslt_f pen,
2638: ben_acty_base_rt_f abr
2639: where prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2640: and pen.person_id = p_person_id
2643: and pen.prtt_enrt_rslt_stat_cd is null
2644: and prv.acty_base_rt_id = abr.acty_base_rt_id
2645: and abr.element_type_id =
2646: (select element_type_id from ben_acty_base_rt_f abr2,
2647: ben_prtt_rt_val prv2
2648: where abr2.acty_base_rt_id = prv2.acty_base_rt_id
2649: and prv2.prtt_rt_val_id = p_prtt_rt_val_id
2650: and prv2.rt_strt_dt between abr2.effective_start_date
2651: and abr2.effective_end_date)
2702: end if;
2703: -- adjust the rate
2704: l_object_version_number := p_object_version_number;
2705: hr_utility.set_location ('Adjust old rate',12);
2706: ben_prtt_rt_val_api.update_prtt_rt_val
2707: (P_PRTT_RT_VAL_ID => p_prtt_rt_val_id
2708: ,P_RT_END_DT => p_rt_END_DT
2709: ,p_person_id => p_person_id
2710: ,p_business_group_id => p_business_group_id
3269: order by 1;
3270: --
3271: -- END ----> DELETE ROUTINE FOR BEN_PRMRY_CARE_PRVDR_F
3272: --
3273: -- START ----> DELETE ROUTINE FOR BEN_PRTT_RT_VAL
3274: --
3275: -- Source Table
3276: -- BEN_PER_IN_LER
3277: --
3274: --
3275: -- Source Table
3276: -- BEN_PER_IN_LER
3277: --
3278: cursor c_ben_prtt_rt_val is
3279: select prv.prtt_rt_val_id,
3280: prv.object_version_number,
3281: pil.person_id,
3282: prv.rt_strt_dt,
3283: prv.acty_ref_perd_cd,
3284: prv.prtt_enrt_rslt_id,
3285: prv.pk_id,
3286: prv.pk_id_table_name
3287: from ben_prtt_rt_val prv,
3288: ben_per_in_ler pil
3289: where pil.per_in_ler_id = p_per_in_ler_id
3290: and pil.business_group_id = p_business_group_id
3291: and prv.prtt_rt_val_stat_cd is null
3310: decode( (select '1' from dual
3311: where exists (select '1' from ben_le_clsn_n_rstr bkp
3312: where bkp.per_in_ler_ended_id = p_per_in_ler_id
3313: and bkp.BKUP_TBL_ID = prv.prtt_rt_val_id
3314: and bkp.BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
3315: )
3316: ), '1',2,1 ) adj_rec /* Bug 12576710: Added decode statement*/
3317: from ben_prtt_rt_val prv,
3318: ben_per_in_ler pil
3313: and bkp.BKUP_TBL_ID = prv.prtt_rt_val_id
3314: and bkp.BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
3315: )
3316: ), '1',2,1 ) adj_rec /* Bug 12576710: Added decode statement*/
3317: from ben_prtt_rt_val prv,
3318: ben_per_in_ler pil
3319: where prv.ended_per_in_ler_id = pil.per_in_ler_id
3320: and prv.ended_per_in_ler_id = p_per_in_ler_id
3321: and prv.per_in_ler_id <> p_per_in_ler_id -- Bug 8234902, not to consider the
3334: cursor c_next_prv is
3335: select prv.prtt_rt_val_id,
3336: prv.rt_strt_dt,
3337: prv.per_in_ler_id
3338: from ben_prtt_rt_val prv
3339: where prv.ended_per_in_ler_id = p_per_in_ler_id
3340: and prv.prtt_rt_val_stat_cd ='BCKDT'
3341: and prv.per_in_ler_id <> p_per_in_ler_id -- Bug 8234902, not to consider the rates of backed out LifeEvent
3342: and prv.prtt_enrt_rslt_id = l_prv_bckdt.prtt_enrt_rslt_id
3354: select lcr.rt_end_dt,
3355: lcr.per_in_ler_ended_id
3356: from ben_le_clsn_n_rstr lcr
3357: where lcr.per_in_ler_id = p_per_in_ler_id
3358: and bkup_tbl_typ_cd = 'BEN_PRTT_RT_VAL'
3359: and bkup_tbl_id = p_prtt_rt_val_id;
3360: --
3361: l_bckdt_prv_rec c_get_bckdt_prv%rowtype;
3362: --
3364: --
3365: cursor c_prtt_rt_val_adj (p_per_in_ler_id number) is
3366: select *
3367: from ben_le_clsn_n_rstr
3368: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
3369: AND PER_IN_LER_ID = p_per_in_ler_id;
3370: --
3371: l_rt_adj c_prtt_rt_val_adj%rowtype;
3372: --
3371: l_rt_adj c_prtt_rt_val_adj%rowtype;
3372: --
3373: cursor c_prv_ovn (p_prtt_rt_val_id in number) is
3374: select object_version_number
3375: from ben_prtt_rt_val
3376: where prtt_rt_val_id = p_prtt_rt_val_id;
3377: --
3378: -- Added for bug 7206471
3379: cursor c_prtt_enrt_rslt_adj (p_per_in_ler_id in number) is
3391:
3392: cursor c_adj_rate_curr_per_in_ler (p_per_in_ler_id number,p_bkp_prv_id number) is
3393: select *
3394: from ben_le_clsn_n_rstr
3395: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
3396: AND per_in_ler_ended_id = p_per_in_ler_id
3397: and BKUP_TBL_ID = nvl(p_bkp_prv_id,BKUP_TBL_ID);
3398: --
3399: l_cvg_adj c_prtt_enrt_rslt_adj%rowtype;
3432: --
3433: /* BUG 3507554 Performance Changes
3434: cursor c_multiple_rate is
3435: select 'Y'
3436: from ben_prtt_rt_val prv,
3437: ben_per_in_ler pil
3438: where pil.per_in_ler_id = p_per_in_ler_id
3439: and pil.business_group_id = p_business_group_id
3440: and prv.prtt_rt_val_stat_cd is null
3458: --BUG 3507554 Performance Changes
3459: --
3460: cursor c_multiple_rate is
3461: select 'Y'
3462: from ben_prtt_rt_val prv
3463: where prv.per_in_ler_id = p_per_in_ler_id
3464: and prv.prtt_rt_val_stat_cd is null
3465: and exists (select null
3466: from ben_prtt_enrt_rslt_f pen
3494:
3495:
3496: --
3497: --
3498: -- END ----> DELETE ROUTINE FOR BEN_PRTT_RT_VAL
3499: --
3500: -- START ----> DELETE ROUTINE FOR BEN_PRTT_ENRT_RSLT_F
3501: --
3502: -- Source Table
3752: --
3753: -- Bug 6376239
3754: cursor c_bpl_from_pen is
3755: select bpl.bnft_prvdd_ldgr_id
3756: from ben_bnft_prvdd_ldgr_f bpl, ben_prtt_rt_val prv
3757: where prv.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
3758: and prv.acty_base_rt_id = bpl.acty_base_rt_id
3759: and bpl.per_in_ler_id = p_per_in_ler_id
3760: and prv.per_in_ler_id = bpl.per_in_ler_id
3827: ,prc.OBJECT_VERSION_NUMBER
3828: ,prc.effective_start_date
3829: ,pil.person_id
3830: from ben_prtt_reimbmt_rqst_f prc
3831: ,ben_prtt_rt_val prv
3832: ,ben_per_in_ler pil
3833: where prv.per_in_ler_id = p_per_in_ler_id
3834: and prv.PRTT_REIMBMT_RQST_id = prc.PRTT_REIMBMT_RQST_id
3835: and pil.per_in_ler_id = p_per_in_ler_id
5635: --
5636: close c_prc;
5637:
5638: --
5639: elsif p_routine = 'BEN_PRTT_RT_VAL' then
5640:
5641: -- Rates need to be marked 'backed out' if they were created due to this per-in-ler.
5642: -- the first loop does that.
5643: -- The second loop handles 'un-ending' rates that were ended due to this per-in-ler.
5671: end if; --if l_status
5672: end if;
5673:
5674: --
5675: open c_ben_prtt_rt_val;
5676: loop
5677: fetch c_ben_prtt_rt_val into l_pk_id,
5678: l_object_version_number,
5679: l_person_id,
5673:
5674: --
5675: open c_ben_prtt_rt_val;
5676: loop
5677: fetch c_ben_prtt_rt_val into l_pk_id,
5678: l_object_version_number,
5679: l_person_id,
5680: l_rt_strt_dt,
5681: l_acty_ref_perd_cd,
5681: l_acty_ref_perd_cd,
5682: l_prv_prtt_enrt_rslt_id,
5683: l_ref_obj_pk_id,
5684: l_ref_obj_table_name;
5685: exit when c_ben_prtt_rt_val%notfound;
5686: --
5687: -- Pbodla : Now insert the link between ben_enrt_rt and
5688: -- ben_prtt_rt_val into bacup table lcr
5689: -- as this info is lost when the prv rows are set to BCKDT status.
5684: l_ref_obj_table_name;
5685: exit when c_ben_prtt_rt_val%notfound;
5686: --
5687: -- Pbodla : Now insert the link between ben_enrt_rt and
5688: -- ben_prtt_rt_val into bacup table lcr
5689: -- as this info is lost when the prv rows are set to BCKDT status.
5690: --tilak: in case the cursor fails the previous data carried forward
5691: -- to avoid this the data is initialised
5692: l_ecr.enrt_rt_id := null ;
5729: --
5730: -- Delete from the appropriate API.
5731: --
5732: if nvl(p_copy_only,'N') <> 'Y' then
5733: ben_prtt_rt_val_api.update_prtt_rt_val
5734: (p_validate => false,
5735: p_business_group_id => p_business_group_id,
5736: p_prtt_rt_val_id => l_pk_id,
5737: p_rt_end_dt => (l_rt_strt_dt -1),
5817: end if;
5818: --
5819: end loop;
5820:
5821: close c_ben_prtt_rt_val;
5822:
5823: /* Bug 8507247:First Adjust the rates and then open the backed out rates till EOT. This issue resolves Bug 8293106.
5824: Fix for Bug 8293106 is handled through Bug 8507247*/
5825:
5916: -- end 8896851
5917: --
5918: end if;
5919:
5920: ben_prtt_rt_val_api.update_prtt_rt_val
5921: (p_validate => FALSE
5922: ,p_prtt_rt_val_id => l_prv_bckdt.prtt_rt_val_id
5923: ,p_object_version_number => l_prv_bckdt.object_version_number
5924: ,p_rt_end_dt => l_rt_end_dt
7287: close c_ben_prmry_care_prvdr_f;
7288: --
7289: end if;
7290: --
7291: if c_ben_prtt_rt_val%isopen then
7292: --
7293: close c_ben_prtt_rt_val;
7294: --
7295: end if;
7289: end if;
7290: --
7291: if c_ben_prtt_rt_val%isopen then
7292: --
7293: close c_ben_prtt_rt_val;
7294: --
7295: end if;
7296: --
7297: if c_prv_of_previous_pil%isopen then