[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
1284: -- BEN_PRTT_ENRT_RSLT_F (PEN)
1285: -- BEN_PL_BNF_F (PBN) (join to pil to check status)
1286: -- BEN_PRTT_ENRT_ACTN_F (PEA) (join to pil to check status)
1287: -- BEN_BNFT_PRVDD_LDGR_F (BPL)(join to pil to check status)
1288: -- BEN_PRTT_RT_VAL (PRV)
1289: -- BEN_PRTT_REIMBMT_RQST_F
1290: -- BEN_PL_BNF_CTFN_PRVDD_F (PBC)(join to pil to check status)
1291: -- BEN_PRTT_ENRT_CTFN_PRVDD_F (PCS)(join to pil to check status)
1292: -- BEN_ELIG_PER_ELCTBL_CHC (EPE) (not deleted, join to pil to check status)
1434: p_bckdt_prtt_enrt_rslt_id => p_bckdt_prtt_enrt_rslt_id,
1435: p_copy_only => p_copy_only,
1436: p_effective_date => p_effective_date);
1437:
1438: hr_utility.set_location('BEN_PRTT_RT_VAL p_effective_date'||p_effective_date,1999);
1439: -- mark rates 'backed out'
1440: delete_routine(p_routine => 'BEN_PRTT_RT_VAL',
1441: p_per_in_ler_id => p_per_in_ler_id,
1442: p_business_group_id => p_business_group_id,
1436: p_effective_date => p_effective_date);
1437:
1438: hr_utility.set_location('BEN_PRTT_RT_VAL p_effective_date'||p_effective_date,1999);
1439: -- mark rates 'backed out'
1440: delete_routine(p_routine => 'BEN_PRTT_RT_VAL',
1441: p_per_in_ler_id => p_per_in_ler_id,
1442: p_business_group_id => p_business_group_id,
1443: p_bckdt_prtt_enrt_rslt_id => p_bckdt_prtt_enrt_rslt_id,
1444: p_copy_only => p_copy_only,
2247: p_effective_date date) is
2248: --
2249: cursor c_future_prv is
2250: select prv.*
2251: from ben_prtt_rt_val prv,
2252: ben_prtt_enrt_rslt_f pen,
2253: ben_acty_base_rt_f abr
2254: where prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2255: and pen.person_id = p_person_id
2258: and pen.prtt_enrt_rslt_stat_cd is null
2259: and prv.acty_base_rt_id = abr.acty_base_rt_id
2260: and abr.element_type_id =
2261: (select element_type_id from ben_acty_base_rt_f abr2,
2262: ben_prtt_rt_val prv2
2263: where abr2.acty_base_rt_id = prv2.acty_base_rt_id
2264: and prv2.prtt_rt_val_id = p_prtt_rt_val_id
2265: and prv2.rt_strt_dt between abr2.effective_start_date
2266: and abr2.effective_end_date)
2317: end if;
2318: -- adjust the rate
2319: l_object_version_number := p_object_version_number;
2320: hr_utility.set_location ('Adjust old rate',12);
2321: ben_prtt_rt_val_api.update_prtt_rt_val
2322: (P_PRTT_RT_VAL_ID => p_prtt_rt_val_id
2323: ,P_RT_END_DT => p_rt_END_DT
2324: ,p_person_id => p_person_id
2325: ,p_business_group_id => p_business_group_id
2863: order by 1;
2864: --
2865: -- END ----> DELETE ROUTINE FOR BEN_PRMRY_CARE_PRVDR_F
2866: --
2867: -- START ----> DELETE ROUTINE FOR BEN_PRTT_RT_VAL
2868: --
2869: -- Source Table
2870: -- BEN_PER_IN_LER
2871: --
2868: --
2869: -- Source Table
2870: -- BEN_PER_IN_LER
2871: --
2872: cursor c_ben_prtt_rt_val is
2873: select prv.prtt_rt_val_id,
2874: prv.object_version_number,
2875: pil.person_id,
2876: prv.rt_strt_dt,
2877: prv.acty_ref_perd_cd,
2878: prv.prtt_enrt_rslt_id,
2879: prv.pk_id,
2880: prv.pk_id_table_name
2881: from ben_prtt_rt_val prv,
2882: ben_per_in_ler pil
2883: where pil.per_in_ler_id = p_per_in_ler_id
2884: and pil.business_group_id = p_business_group_id
2885: and prv.prtt_rt_val_stat_cd is null
2899: prv.per_in_ler_id,
2900: prv.prtt_enrt_rslt_id,
2901: prv.acty_base_rt_id,
2902: pil.person_id
2903: from ben_prtt_rt_val prv,
2904: ben_per_in_ler pil
2905: where prv.ended_per_in_ler_id = pil.per_in_ler_id
2906: and prv.ended_per_in_ler_id = p_per_in_ler_id
2907: and nvl(prv.prtt_rt_val_stat_cd,'BCKDT') ='BCKDT'
2916: cursor c_next_prv is
2917: select prv.prtt_rt_val_id,
2918: prv.rt_strt_dt,
2919: prv.per_in_ler_id
2920: from ben_prtt_rt_val prv
2921: where prv.ended_per_in_ler_id = p_per_in_ler_id
2922: and prv.prtt_rt_val_stat_cd ='BCKDT'
2923: and prv.prtt_enrt_rslt_id = l_prv_bckdt.prtt_enrt_rslt_id
2924: and prv.acty_base_rt_id = l_prv_bckdt.acty_base_rt_id
2931: --
2932: cursor c_prtt_rt_val_adj (p_per_in_ler_id number) is
2933: select *
2934: from ben_le_clsn_n_rstr
2935: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
2936: AND PER_IN_LER_ID = p_per_in_ler_id;
2937: --
2938: l_rt_adj c_prtt_rt_val_adj%rowtype;
2939: --
2938: l_rt_adj c_prtt_rt_val_adj%rowtype;
2939: --
2940: cursor c_prv_ovn (p_prtt_rt_val_id in number) is
2941: select object_version_number
2942: from ben_prtt_rt_val
2943: where prtt_rt_val_id = p_prtt_rt_val_id;
2944: --
2945: -- Added for bug 7206471
2946: cursor c_prtt_enrt_rslt_adj (p_per_in_ler_id in number) is
2982: --
2983: /* BUG 3507554 Performance Changes
2984: cursor c_multiple_rate is
2985: select 'Y'
2986: from ben_prtt_rt_val prv,
2987: ben_per_in_ler pil
2988: where pil.per_in_ler_id = p_per_in_ler_id
2989: and pil.business_group_id = p_business_group_id
2990: and prv.prtt_rt_val_stat_cd is null
3008: --BUG 3507554 Performance Changes
3009: --
3010: cursor c_multiple_rate is
3011: select 'Y'
3012: from ben_prtt_rt_val prv
3013: where prv.per_in_ler_id = p_per_in_ler_id
3014: and prv.prtt_rt_val_stat_cd is null
3015: and exists (select null
3016: from ben_prtt_enrt_rslt_f pen
3044:
3045:
3046: --
3047: --
3048: -- END ----> DELETE ROUTINE FOR BEN_PRTT_RT_VAL
3049: --
3050: -- START ----> DELETE ROUTINE FOR BEN_PRTT_ENRT_RSLT_F
3051: --
3052: -- Source Table
3300: --
3301: -- Bug 6376239
3302: cursor c_bpl_from_pen is
3303: select bpl.bnft_prvdd_ldgr_id
3304: from ben_bnft_prvdd_ldgr_f bpl, ben_prtt_rt_val prv
3305: where prv.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
3306: and prv.acty_base_rt_id = bpl.acty_base_rt_id
3307: and bpl.per_in_ler_id = p_per_in_ler_id
3308: and prv.per_in_ler_id = bpl.per_in_ler_id
3375: ,prc.OBJECT_VERSION_NUMBER
3376: ,prc.effective_start_date
3377: ,pil.person_id
3378: from ben_prtt_reimbmt_rqst_f prc
3379: ,ben_prtt_rt_val prv
3380: ,ben_per_in_ler pil
3381: where prv.per_in_ler_id = p_per_in_ler_id
3382: and prv.PRTT_REIMBMT_RQST_id = prc.PRTT_REIMBMT_RQST_id
3383: and pil.per_in_ler_id = p_per_in_ler_id
5153: --
5154: close c_prc;
5155:
5156: --
5157: elsif p_routine = 'BEN_PRTT_RT_VAL' then
5158:
5159: -- Rates need to be marked 'backed out' if they were created due to this per-in-ler.
5160: -- the first loop does that.
5161: -- The second loop handles 'un-ending' rates that were ended due to this per-in-ler.
5189: end if; --if l_status
5190: end if;
5191:
5192: --
5193: open c_ben_prtt_rt_val;
5194: loop
5195: fetch c_ben_prtt_rt_val into l_pk_id,
5196: l_object_version_number,
5197: l_person_id,
5191:
5192: --
5193: open c_ben_prtt_rt_val;
5194: loop
5195: fetch c_ben_prtt_rt_val into l_pk_id,
5196: l_object_version_number,
5197: l_person_id,
5198: l_rt_strt_dt,
5199: l_acty_ref_perd_cd,
5199: l_acty_ref_perd_cd,
5200: l_prv_prtt_enrt_rslt_id,
5201: l_ref_obj_pk_id,
5202: l_ref_obj_table_name;
5203: exit when c_ben_prtt_rt_val%notfound;
5204: --
5205: -- Pbodla : Now insert the link between ben_enrt_rt and
5206: -- ben_prtt_rt_val into bacup table lcr
5207: -- as this info is lost when the prv rows are set to BCKDT status.
5202: l_ref_obj_table_name;
5203: exit when c_ben_prtt_rt_val%notfound;
5204: --
5205: -- Pbodla : Now insert the link between ben_enrt_rt and
5206: -- ben_prtt_rt_val into bacup table lcr
5207: -- as this info is lost when the prv rows are set to BCKDT status.
5208: --tilak: in case the cursor fails the previous data carried forward
5209: -- to avoid this the data is initialised
5210: l_ecr.enrt_rt_id := null ;
5247: --
5248: -- Delete from the appropriate API.
5249: --
5250: if nvl(p_copy_only,'N') <> 'Y' then
5251: ben_prtt_rt_val_api.update_prtt_rt_val
5252: (p_validate => false,
5253: p_business_group_id => p_business_group_id,
5254: p_prtt_rt_val_id => l_pk_id,
5255: p_rt_end_dt => (l_rt_strt_dt -1),
5335: end if;
5336: --
5337: end loop;
5338:
5339: close c_ben_prtt_rt_val;
5340: --
5341: -- Reset the rate end date for the original rates.
5342: --
5343: l_prv_bckdt := null;
5382: l_rt_end_dt := hr_api.g_eot;
5383: l_ended_per_in_ler_id := null;
5384: end if;
5385:
5386: ben_prtt_rt_val_api.update_prtt_rt_val
5387: (p_validate => FALSE
5388: ,p_prtt_rt_val_id => l_prv_bckdt.prtt_rt_val_id
5389: ,p_object_version_number => l_prv_bckdt.object_version_number
5390: ,p_rt_end_dt => l_rt_end_dt
6635: close c_ben_prmry_care_prvdr_f;
6636: --
6637: end if;
6638: --
6639: if c_ben_prtt_rt_val%isopen then
6640: --
6641: close c_ben_prtt_rt_val;
6642: --
6643: end if;
6637: end if;
6638: --
6639: if c_ben_prtt_rt_val%isopen then
6640: --
6641: close c_ben_prtt_rt_val;
6642: --
6643: end if;
6644: --
6645: if c_prv_of_previous_pil%isopen then