[Home] [Help]
283: 11-mar-2003 kmahendr 115.81 Bug#2821279 : Removed the max functions and logic
284: is based on order by clause.
285: 10-mar-2003 pbodla 115.82 Bug 2695023 CWB : If pay proposal is
286: associated with rate remove it also.
287: 20-mar-2003 ikasire 115.83 Bug 2847654 adding more hr_utility to isolate
288: an issue in the hrqa115m which is not replicable
289: 20-mar-2003 ikasire 115.84 Bug 2847654 fix removed the use of p_effective_date
290: removed the hr_utility
291: 16-Apr-2003 tjesumic 115.85 Bug # 2899702 manage_per_type_usage is called while
286: associated with rate remove it also.
287: 20-mar-2003 ikasire 115.83 Bug 2847654 adding more hr_utility to isolate
288: an issue in the hrqa115m which is not replicable
289: 20-mar-2003 ikasire 115.84 Bug 2847654 fix removed the use of p_effective_date
290: removed the hr_utility
291: 16-Apr-2003 tjesumic 115.85 Bug # 2899702 manage_per_type_usage is called while
292: deleting result to delete the parttn usage
293: 24-Jun-2003 tjesumic 115,86 c_futur_del_dpnt cursor is closed
294: 28-Aug-2003 tjesumic 115.87 bug # 3086161 whne the open LE reprocessed on the same day of
497: l_proc varchar2(72) := g_package||'p_backout_contact_pils';
498: --
499: begin
500: --
501: hr_utility.set_location('Entering:'|| l_proc, 10);
502: --
503: -- First check any potentials of detected or unprocessed
504: -- exists. then void them.
505: --
591:
592: --
593: end if;
594: --
595: hr_utility.set_location('Leaving:'|| l_proc, 10);
596: --
597: end p_backout_contact_pils;
598: --
599: -- ----------------------------------------------------------------------------
643: l_bckt_stat_cd hr_lookups.lookup_code%TYPE := p_bckt_stat_cd; --UTF8
644: --
645: begin
646: --
647: hr_utility.set_location('Entering:'|| l_proc, 10);
648: --
649: l_unprocd_dt := trunc(sysdate);
650: --
651: open c_ptnl;
666: -- A similar potential already exists, so just
667: -- void the current potential, instead of making it
668: -- into unprocessed.
669: --
670: hr_utility.set_location('Changing status to VOIDD as a potential ' ||
671: 'already exists',10);
672: l_bckt_stat_cd := 'VOIDD';
673: --
674: end if;
688: l_mnl_dt := p_effective_date;
689: --
690: end if;
691: --
692: hr_utility.set_location('Setting status to '||l_bckt_stat_cd,10);
693: ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
694: (p_validate => false
695: ,p_ptnl_ler_for_per_id => p_per_in_ler.ptnl_ler_for_per_id
696: ,p_ptnl_ler_for_per_stat_cd => l_bckt_stat_cd
703: ,p_unprocd_dt => l_unprocd_dt
704: ,p_voidd_dt => l_voidd_dt
705: ,p_mnl_dt => l_mnl_dt);
706: --
707: hr_utility.set_location('Leaving:'|| l_proc, 90);
708: --
709: end unprocess_ptnl_ler;
710: --
711:
737: l_datetrack_mode varchar2(100);
738: --
739: begin
740: --
741: hr_utility.set_location ('Entering '||l_proc,10);
742: --
743: FOR l_rec IN c_restore_pep(p_per_in_ler_id) LOOP
744: --
745: l_effective_date := l_rec.effective_start_date;
842: delete from ben_le_clsn_n_rstr elig
843: where elig.per_in_ler_ended_id = p_per_in_ler_id
844: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
845: --
846: hr_utility.set_location ('Leaving '||l_proc,10);
847: --
848: end restore_prev_pep ;
849: --
850: procedure restore_cert_completion
884: l_datetrack_mode varchar2(100);
885: --
886: begin
887: --
888: hr_utility.set_location ('Entering '||l_proc,10);
889: --
890: FOR l_rec IN c_restore_pcs(p_per_in_ler_id) LOOP
891: --
892: l_effective_date := l_rec.effective_start_date;
987: delete from ben_le_clsn_n_rstr pcs
988: where pcs.per_in_ler_ended_id = p_per_in_ler_id
989: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_ACTN_F_UPD' ;
990: --
991: hr_utility.set_location ('Leaving '||l_proc,10);
992: --
993: end restore_cert_completion ;
994: --
995: --
1020: l_datetrack_mode varchar2(100);
1021: --
1022: begin
1023: --
1024: hr_utility.set_location ('Entering '||l_proc,10);
1025: --
1026: FOR l_rec IN c_restore_epo(p_per_in_ler_id) LOOP
1027: --
1028: l_effective_date := l_rec.effective_start_date;
1127: delete from ben_le_clsn_n_rstr elig
1128: where elig.per_in_ler_ended_id = p_per_in_ler_id
1129: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT';
1130: --
1131: hr_utility.set_location ('Leaving '||l_proc,10);
1132: --
1133: END restore_prev_epo ;
1134: --
1135: -- This procedure is the main call that does the calls to back out the
1261:
1262: --
1263: begin
1264: --
1265: hr_utility.set_location ('Entering '||l_package,10);
1266: --
1267: -- unrestricted life event not to be backed out
1268: open c_ler_typ;
1269: fetch c_ler_typ into l_ler_typ;
1352: open c_prev_pil_id (l_pil_stat.person_id , l_pil_stat.lf_evt_ocrd_dt) ;
1353: fetch c_prev_pil_id into l_prv_per_in_ler_id,l_prv_lf_evt_ocrd_dt ;
1354: close c_prev_pil_id ;
1355:
1356: hr_utility.set_location ('prv_per_in_ler_id' || l_prv_per_in_ler_id, 99 );
1357: hr_utility.set_location ('l_lf_evt_ocrd_dt' || l_prv_lf_evt_ocrd_dt, 99 );
1358: -- First back out contact persons per in ler's
1359: -- if they are created by the per in ler which is
1360: -- # 2982606 Total of Two Plan Resulkt level backedout introduced
1353: fetch c_prev_pil_id into l_prv_per_in_ler_id,l_prv_lf_evt_ocrd_dt ;
1354: close c_prev_pil_id ;
1355:
1356: hr_utility.set_location ('prv_per_in_ler_id' || l_prv_per_in_ler_id, 99 );
1357: hr_utility.set_location ('l_lf_evt_ocrd_dt' || l_prv_lf_evt_ocrd_dt, 99 );
1358: -- First back out contact persons per in ler's
1359: -- if they are created by the per in ler which is
1360: -- # 2982606 Total of Two Plan Resulkt level backedout introduced
1361: if p_bckdt_prtt_enrt_rslt_id is null then
1399: p_effective_date => p_effective_date);
1400:
1401: -- (404) Until we recognize that pcp's aren't valid if their per-in-ler isn't
1402: -- we need to still delete them. Need to change views? or add per in ler id.
1403: hr_utility.set_location('Effective Date'||p_effective_date,10);
1404:
1405: delete_routine(p_routine => 'BEN_PRMRY_CARE_PRVDR_F',
1406: p_per_in_ler_id => p_per_in_ler_id,
1407: p_business_group_id => p_business_group_id,
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,
1567: fetch c_prev_pil_id into l_prv_per_in_ler_id,l_prv_lf_evt_ocrd_dt ;
1568: close c_prev_pil_id ;
1569: */
1570:
1571: hr_utility.set_location ('prv_per_in_ler_id' || l_prv_per_in_ler_id, 199 );
1572: hr_utility.set_location ('l_lf_evt_ocrd_dt' || l_prv_lf_evt_ocrd_dt, 199 );
1573: --
1574: --IK START
1575: -- Get all the BEN_ELIG_PER_F_CORRECTION and BEN_ELIG_PER_OPT_F_CORRECTION
1568: close c_prev_pil_id ;
1569: */
1570:
1571: hr_utility.set_location ('prv_per_in_ler_id' || l_prv_per_in_ler_id, 199 );
1572: hr_utility.set_location ('l_lf_evt_ocrd_dt' || l_prv_lf_evt_ocrd_dt, 199 );
1573: --
1574: --IK START
1575: -- Get all the BEN_ELIG_PER_F_CORRECTION and BEN_ELIG_PER_OPT_F_CORRECTION
1576: -- records from BEN_LE_CLSN_N_RSTR table for the per_in_ler being backed out
1592: open c_prv_rslt_lvl_bckdt(l_prv_per_in_ler_id,l_prv_lf_evt_ocrd_dt );
1593: fetch c_prv_rslt_lvl_bckdt into l_prv_effective_start_date ;
1594: close c_prv_rslt_lvl_bckdt ;
1595:
1596: hr_utility.set_location ('l_prv_eff dt' || l_prv_effective_start_date, 99 );
1597: hr_utility.set_location ('l_prv_per_in_ler_id' || l_prv_per_in_ler_id, 99 );
1598:
1599: if l_prv_effective_start_date is not null then
1600: hr_utility.set_location ('status to started' , 99 );
1593: fetch c_prv_rslt_lvl_bckdt into l_prv_effective_start_date ;
1594: close c_prv_rslt_lvl_bckdt ;
1595:
1596: hr_utility.set_location ('l_prv_eff dt' || l_prv_effective_start_date, 99 );
1597: hr_utility.set_location ('l_prv_per_in_ler_id' || l_prv_per_in_ler_id, 99 );
1598:
1599: if l_prv_effective_start_date is not null then
1600: hr_utility.set_location ('status to started' , 99 );
1601:
1596: hr_utility.set_location ('l_prv_eff dt' || l_prv_effective_start_date, 99 );
1597: hr_utility.set_location ('l_prv_per_in_ler_id' || l_prv_per_in_ler_id, 99 );
1598:
1599: if l_prv_effective_start_date is not null then
1600: hr_utility.set_location ('status to started' , 99 );
1601:
1602: -- added here bug 7039025
1603: For l_prior_to_prv_rslt_lvl_bckdt in c_prior_to_prv_rslt_lvl_bckdt(l_prv_per_in_ler_id,l_prv_lf_evt_ocrd_dt)
1604: loop
1602: -- added here bug 7039025
1603: For l_prior_to_prv_rslt_lvl_bckdt in c_prior_to_prv_rslt_lvl_bckdt(l_prv_per_in_ler_id,l_prv_lf_evt_ocrd_dt)
1604: loop
1605: --
1606: hr_utility.set_location ('prior to prev life event found in bkup table',44333);
1607: --
1608: -- Insert a correction row before updation as if, the enrollments were corrected by the next pil.
1609: INSERT INTO ben_le_clsn_n_rstr
1610: (COMP_LVL_CD,
1978: l_prior_to_prv_rslt_lvl_bckdt.VAL,
1979: l_prior_to_prv_rslt_lvl_bckdt.STD_PREM_VAL,
1980: l_prior_to_prv_rslt_lvl_bckdt.STD_PREM_UOM);
1981: --
1982: hr_utility.set_location('Inserted corrected Row', 44333);
1983: --
1984: UPDATE ben_le_clsn_n_rstr
1985: SET per_in_ler_id = l_prv_per_in_ler_id
1986: WHERE per_in_ler_id = l_prior_to_prv_rslt_lvl_bckdt.per_in_ler_id
1991: AND effective_end_date = hr_api.g_eot
1992: AND enrt_cvg_strt_dt > l_prv_lf_evt_ocrd_dt
1993: AND enrt_cvg_strt_dt < effective_end_date;
1994: --
1995: hr_utility.set_location ('updated bkup table table with prev per_in_ler', 44333);
1996: --
1997:
1998: end loop;
1999:
1999:
2000: -- added till here bug 7039025
2001:
2002:
2003: hr_utility.set_location ('restoring result ' , 99 );
2004: ben_lf_evt_clps_restore.reinstate_the_prev_enrt_rslt(
2005: p_person_id => l_pil_stat.person_id
2006: ,p_business_group_id => p_business_group_id
2007: ,p_ler_id => l_pil_stat.ler_id
2015: where per_in_ler_id = l_prv_per_in_ler_id
2016: AND business_group_id = p_business_group_id ;
2017:
2018:
2019: hr_utility.set_location ('status to closed' , 99 );
2020: end if ;
2021: --
2022: --Restore Future completed Action Items
2023: --
2030: -- Bug 2526994 This needs to be reset once the process is done.
2031: g_backout_flag := null ;
2032: --
2033: end if;
2034: hr_utility.set_location ('Leaving '||l_package,99);
2035: --
2036: exception
2037: when others then
2038: --
2038: --
2039: -- Bug 4919951 - Reset the flag if any exception raised
2040: --
2041: g_backout_flag := null ;
2042: hr_utility.set_location('Flag g_backout_flag Reset', 9999);
2043: raise;
2044: --
2045: --
2046: end back_out_life_events;
2116: l_prev_enrt_ctfn_prvdd_id number;
2117: --
2118: begin
2119: --
2120: hr_utility.set_location ('Entering ' || l_proc , 1230);
2121: l_per_in_ler_id := p_per_in_ler_id;
2122: --
2123: l_prev_prtt_enrt_actn_id := -1;
2124: l_prev_enrt_ctfn_prvdd_id := -1;
2130: -- Un-enddate action item record
2131: --
2132: l_object_version_number := l_actn_item.object_version_number;
2133: --
2134: hr_utility.set_location('ACE l_prev_prtt_enrt_actn_id = ' || l_prev_prtt_enrt_actn_id, 9999);
2135: hr_utility.set_location('ACE l_prtt_enrt_actn_id = ' || l_prtt_enrt_actn_id, 9999);
2136: if l_prev_prtt_enrt_actn_id <> l_prtt_enrt_actn_id
2137: then
2138: --
2131: --
2132: l_object_version_number := l_actn_item.object_version_number;
2133: --
2134: hr_utility.set_location('ACE l_prev_prtt_enrt_actn_id = ' || l_prev_prtt_enrt_actn_id, 9999);
2135: hr_utility.set_location('ACE l_prtt_enrt_actn_id = ' || l_prtt_enrt_actn_id, 9999);
2136: if l_prev_prtt_enrt_actn_id <> l_prtt_enrt_actn_id
2137: then
2138: --
2139: -- Bug 4642315 : Cursor C_ACTN_ITEM_FOR_PAST_PIL will pick up multiple datetracked
2152: p_datetrack_mode => hr_api.g_future_change);
2153: --
2154: end if;
2155: --
2156: hr_utility.set_location('ACE l_actn_item.prtt_enrt_actn_id = ' || l_actn_item.prtt_enrt_actn_id, 8888);
2157: --
2158: -- Un-enddate enrollment certification record(s)
2159: --
2160: l_prev_enrt_ctfn_prvdd_id := -1;
2206: --
2207: end if;
2208: close c_check_prem_active;
2209: --
2210: hr_utility.set_location ('Leaving ' || l_proc, 1230);
2211: end unprocess_susp_enrt_past_pil;
2212: -- CFW
2213: --
2214: -- This function has been added as part of fix for bug 2518955
2223: l_msg_app VARCHAR2(50);
2224: l_proc VARCHAR2(72) := g_package||'get_msg_name';
2225:
2226: BEGIN
2227: -- hr_utility.set_location('Entering:'|| l_proc, 10);
2228:
2229: l_encoded_msg := fnd_message.get_encoded();
2230: fnd_message.parse_encoded(
2231: encoded_message => l_encoded_msg
2232: ,app_short_name => l_msg_app -- OUT
2233: ,message_name => l_msg_name -- OUT
2234: );
2235:
2236: -- hr_utility.set_location('Leaving:'|| l_proc, 20);
2237:
2238: RETURN l_msg_name;
2239:
2240: END; -- get_msg_name
2288: l_element_start_date date;
2289: --
2290: begin
2291: --
2292: hr_utility.set_location('Entering Adj Prv rate',10);
2293: open c_future_prv;
2294: fetch c_future_prv into l_prv;
2295: close c_future_prv;
2296: --
2295: close c_future_prv;
2296: --
2297: if l_prv.element_entry_value_id is not null then
2298: --
2299: hr_utility.set_location('value Id is not null',11);
2300: open c_element (l_prv.element_entry_value_id);
2301: fetch c_element into l_element;
2302: close c_element;
2303: --
2300: open c_element (l_prv.element_entry_value_id);
2301: fetch c_element into l_element;
2302: close c_element;
2303: --
2304: hr_utility.set_location('effective date'||l_element.effective_start_date,10);
2305: l_element_start_date := l_element.effective_start_date;
2306: py_element_entry_api.delete_element_entry
2307: (p_validate => false
2308: ,p_datetrack_delete_mode => hr_api.g_zap
2316: --
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
2329: --
2330:
2331: if l_prv.element_entry_value_id is not null then
2332: --
2333: hr_utility.set_location ('Recreate element ',14);
2334: ben_element_entry.create_enrollment_element
2335: (p_business_group_id => p_business_group_id
2336: ,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
2337: ,p_person_id => p_person_id
2350: ,p_element_entry_value_id => l_dummy_number
2351: );
2352: --
2353: end if;
2354: hr_utility.set_location ('Leaving Adjust rate',15);
2355: end;
2356: --
2357: -- Added for bug 7206471
2358: --
2372: --
2373: --
2374: begin
2375: --
2376: hr_utility.set_location('Entering Adj Pen Cvg',44333);
2377: --
2378: --
2379: -- adjust the coverage
2380: l_object_version_number := p_object_version_number;
2377: --
2378: --
2379: -- adjust the coverage
2380: l_object_version_number := p_object_version_number;
2381: hr_utility.set_location ('Adjust old coverage',44333);
2382: ben_prtt_enrt_result_api.update_prtt_enrt_result
2383: (p_validate => FALSE,
2384: p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
2385: p_effective_start_date => l_effective_start_date,
2391: p_multi_row_validate => FALSE,
2392: p_enrt_cvg_thru_dt => p_cvg_end_dt
2393: );
2394: --
2395: hr_utility.set_location ('Leaving Adj Pen Cvg',44333);
2396: end;
2397: -- End bug 7206471
2398:
2399: -- When Open LE processed then a LE processed before open starts
3488: l_ended_per_in_ler_id number;
3489: --
3490: begin
3491: --
3492: hr_utility.set_location ('Entering '||l_package,10);
3493: hr_utility.set_location ('Start of '||p_routine,10);
3494: --
3495: -- check which routine we are calling
3496: --
3489: --
3490: begin
3491: --
3492: hr_utility.set_location ('Entering '||l_package,10);
3493: hr_utility.set_location ('Start of '||p_routine,10);
3494: --
3495: -- check which routine we are calling
3496: --
3497: if p_routine = 'BEN_ELIG_CVRD_DPNT_F' then
3517: --
3518: -- Get the maximum of effective end date for the dependents
3519: -- with past per_in_ler 's.
3520: --
3521: hr_utility.set_location ('with in loop '||l_pk_id,10);
3522: --
3523: l_effective_date := null;
3524:
3525: open c_pdp_max_esd_of_past_pil(l_pk_id);
3551: fetch c_bkp_row into l_row_id;
3552: --
3553: if c_bkp_row%notfound
3554: then
3555: hr_utility.set_location ('backup notfound '||l_effective_date,10);
3556: --
3557: close c_bkp_row;
3558: --
3559: insert into BEN_LE_CLSN_N_RSTR (
3765: --
3766: end if; -- l_prev_pk_id <> l_pk_id
3767: -- bud 5668052
3768: else
3769: hr_utility.set_location('Effective date is null ',121);
3770: BEN_ELIG_CVRD_DPNT_API.remove_usage(
3771: p_validate => FALSE
3772: ,p_elig_cvrd_dpnt_id => l_pk_id
3773: ,p_cvg_thru_dt => NULL
3790: ---- first cursor find all the result voided by the PIL and coverage is future dtd
3791: ---- Second cursor find the PDP record end dated for the result
3792: if nvl(p_copy_only,'N') <> 'Y' then
3793: for l_f_pen in c_futur_pen loop
3794: hr_utility.set_location ('with in future loop '||l_f_pen.prtt_enrt_rslt_id,10);
3795: open c_futur_del_dpnt (l_f_pen.prtt_enrt_rslt_id ) ;
3796: Loop
3797: fetch c_futur_del_dpnt into l_pk_id,l_object_version_number,l_effective_date ;
3798: exit when c_futur_del_dpnt%notfound;
3824: for i in c_BEN_LE_CLSN_N_RSTR_dpnt(p_per_in_ler_id )
3825: Loop
3826: if l_prev_elig_cvrd_dpnt_id <> i.elig_cvrd_dpnt_id then
3827: l_object_version_number := i.object_version_number ;
3828: hr_utility.set_location(' correcting ' || i.elig_cvrd_dpnt_id, 999 );
3829: ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
3830: (p_validate => FALSE
3831: ,p_business_group_id => p_business_group_id
3832: ,p_elig_cvrd_dpnt_id => i.elig_cvrd_dpnt_id
4456: --
4457: elsif p_routine = 'BEN_PRTT_PREM_F' then
4458: -------------Bug 7133998
4459: for l_prtt_prem_corr in c_ben_prtt_prem_f_corr loop
4460: hr_utility.set_location('l_prtt_prem_corr.PRTT_PREM_ID : '||l_prtt_prem_corr.PRTT_PREM_ID,1);
4461: hr_utility.set_location('ESD : '||l_prtt_prem_corr.effective_start_date,1);
4462: hr_utility.set_location('EED : '||l_prtt_prem_corr.effective_end_date,1);
4463: for l_bkp_prem_row in c_bkp_prem_row('BEN_PRTT_PREM_F_CORR',
4464: l_prtt_prem_corr.PRTT_PREM_ID,
4457: elsif p_routine = 'BEN_PRTT_PREM_F' then
4458: -------------Bug 7133998
4459: for l_prtt_prem_corr in c_ben_prtt_prem_f_corr loop
4460: hr_utility.set_location('l_prtt_prem_corr.PRTT_PREM_ID : '||l_prtt_prem_corr.PRTT_PREM_ID,1);
4461: hr_utility.set_location('ESD : '||l_prtt_prem_corr.effective_start_date,1);
4462: hr_utility.set_location('EED : '||l_prtt_prem_corr.effective_end_date,1);
4463: for l_bkp_prem_row in c_bkp_prem_row('BEN_PRTT_PREM_F_CORR',
4464: l_prtt_prem_corr.PRTT_PREM_ID,
4465: l_prtt_prem_corr.effective_start_date,
4458: -------------Bug 7133998
4459: for l_prtt_prem_corr in c_ben_prtt_prem_f_corr loop
4460: hr_utility.set_location('l_prtt_prem_corr.PRTT_PREM_ID : '||l_prtt_prem_corr.PRTT_PREM_ID,1);
4461: hr_utility.set_location('ESD : '||l_prtt_prem_corr.effective_start_date,1);
4462: hr_utility.set_location('EED : '||l_prtt_prem_corr.effective_end_date,1);
4463: for l_bkp_prem_row in c_bkp_prem_row('BEN_PRTT_PREM_F_CORR',
4464: l_prtt_prem_corr.PRTT_PREM_ID,
4465: l_prtt_prem_corr.effective_start_date,
4466: l_prtt_prem_corr.effective_end_date) loop
4463: for l_bkp_prem_row in c_bkp_prem_row('BEN_PRTT_PREM_F_CORR',
4464: l_prtt_prem_corr.PRTT_PREM_ID,
4465: l_prtt_prem_corr.effective_start_date,
4466: l_prtt_prem_corr.effective_end_date) loop
4467: hr_utility.set_location('ESD,in loop : '||l_prtt_prem_corr.effective_start_date,1);
4468: ben_prtt_prem_api.update_prtt_prem
4469: ( p_validate => FALSE
4470: ,p_prtt_prem_id => l_prtt_prem_corr.prtt_prem_id
4471: ,p_effective_start_date => l_effective_start_date
5007: --
5008: end if; -- l_prev_pk_id <> l_pk_id
5009: else
5010: -- -- bug 5668052
5011: hr_utility.set_location('effective date null ' ,12.12);
5012: BEN_PLAN_BENEFICIARY_API.remove_usage (
5013: p_validate => FALSE
5014: ,p_pl_bnf_id => l_pk_id
5015: ,p_effective_date => l_bnf_effective_start_date
5106: begin
5107: loop
5108: fetch c_prc into l_prc_rec ;
5109: exit when c_prc%notfound ;
5110: hr_utility.set_location('calling date ' || p_effective_date , 293);
5111: hr_utility.set_location('calling id ' || l_prc_rec.PRTT_REIMBMT_RQST_ID , 293);
5112:
5113: BEN_prtt_reimbmt_rqst_API.delete_prtt_reimbmt_rqst
5114: (p_validate => FALSE
5107: loop
5108: fetch c_prc into l_prc_rec ;
5109: exit when c_prc%notfound ;
5110: hr_utility.set_location('calling date ' || p_effective_date , 293);
5111: hr_utility.set_location('calling id ' || l_prc_rec.PRTT_REIMBMT_RQST_ID , 293);
5112:
5113: BEN_prtt_reimbmt_rqst_API.delete_prtt_reimbmt_rqst
5114: (p_validate => FALSE
5115: ,p_PRTT_REIMBMT_RQST_ID => l_prc_rec.PRTT_REIMBMT_RQST_ID
5164:
5165: IF (fnd_installation.get_app_info('GHR',l_status, l_industry, l_oracle_schema)) THEN
5166:
5167: if l_status = 'I' then
5168: hr_utility.set_location('FOUND GHR',9909);
5169: open c_multiple_rate;
5170: fetch c_multiple_rate into l_multiple_rate;
5171: if c_multiple_rate%found then
5172: --
5169: open c_multiple_rate;
5170: fetch c_multiple_rate into l_multiple_rate;
5171: if c_multiple_rate%found then
5172: --
5173: hr_utility.set_location('Multiple rate',111);
5174: insert into BEN_LE_CLSN_N_RSTR (
5175: BKUP_TBL_TYP_CD,
5176: BKUP_TBL_ID,
5177: per_in_ler_id,
5236: -- may be 11-nov-99, but the rate may start on 01-jan-00.
5237: -- Passing 11-nov-99 as effective date may cause no
5238: -- element entry row found problems.
5239: --
5240: hr_utility.set_location('l_prv_pen.effective_start_date'||l_prv_pen.effective_start_date,1999);
5241: hr_utility.set_location('l_rt_strt_dt'||l_rt_strt_dt,1999);
5242: if l_prv_pen.effective_start_date < l_rt_strt_dt then
5243: l_prv_effective_date := l_rt_strt_dt;
5244: else
5237: -- Passing 11-nov-99 as effective date may cause no
5238: -- element entry row found problems.
5239: --
5240: hr_utility.set_location('l_prv_pen.effective_start_date'||l_prv_pen.effective_start_date,1999);
5241: hr_utility.set_location('l_rt_strt_dt'||l_rt_strt_dt,1999);
5242: if l_prv_pen.effective_start_date < l_rt_strt_dt then
5243: l_prv_effective_date := l_rt_strt_dt;
5244: else
5245: l_prv_effective_date := l_prv_pen.effective_start_date;
5347: loop
5348: fetch c_prv_of_previous_pil into l_prv_bckdt;
5349: exit when c_prv_of_previous_pil%notfound;
5350: --
5351: hr_utility.set_location('prv 0 '||l_prv_bckdt.prtt_rt_val_id,10);
5352: hr_utility.set_location('prv 1 '||l_prv_bckdt.acty_base_rt_id,10);
5353: l_prv_pen.effective_start_date := null;
5354: open c_prv_pen(l_prv_bckdt.prtt_enrt_rslt_id, l_prv_bckdt.rt_strt_dt);
5355: fetch c_prv_pen into l_prv_pen;
5348: fetch c_prv_of_previous_pil into l_prv_bckdt;
5349: exit when c_prv_of_previous_pil%notfound;
5350: --
5351: hr_utility.set_location('prv 0 '||l_prv_bckdt.prtt_rt_val_id,10);
5352: hr_utility.set_location('prv 1 '||l_prv_bckdt.acty_base_rt_id,10);
5353: l_prv_pen.effective_start_date := null;
5354: open c_prv_pen(l_prv_bckdt.prtt_enrt_rslt_id, l_prv_bckdt.rt_strt_dt);
5355: fetch c_prv_pen into l_prv_pen;
5356: close c_prv_pen;
5409: --
5410: open c_prv_ovn (l_rt_adj.bkup_tbl_id);
5411: fetch c_prv_ovn into l_object_version_number;
5412: close c_prv_ovn;
5413: hr_utility.set_location('Ajdust rate end date'||l_rt_adj.bkup_tbl_id,10);
5414: --
5415: adj_prv_rate (p_person_id => l_rt_adj.person_id,
5416: p_prtt_rt_val_id => l_rt_adj.bkup_tbl_id,
5417: p_rt_end_dt => l_rt_adj.RT_END_DT,
5427: --
5428:
5429: elsif p_routine = 'BEN_PRTT_ENRT_RSLT_F' then
5430:
5431: hr_utility.set_location( 'backing out BEN_PRTT_ENRT_RSLT_F ' || p_per_in_ler_id , 99 );
5432: hr_utility.set_location( 'p_bckdt_prtt_enrt_rslt_id ' || p_bckdt_prtt_enrt_rslt_id , 99 );
5433: -- Results need to be 'un-ended' if they were ended due to this
5434: -- per-in-ler. The 'future change' date track mode handles that.
5435:
5428:
5429: elsif p_routine = 'BEN_PRTT_ENRT_RSLT_F' then
5430:
5431: hr_utility.set_location( 'backing out BEN_PRTT_ENRT_RSLT_F ' || p_per_in_ler_id , 99 );
5432: hr_utility.set_location( 'p_bckdt_prtt_enrt_rslt_id ' || p_bckdt_prtt_enrt_rslt_id , 99 );
5433: -- Results need to be 'un-ended' if they were ended due to this
5434: -- per-in-ler. The 'future change' date track mode handles that.
5435:
5436: -- Results that were created due to this per-in-ler need to be marked 'backed out'.
5493: -- The group function "max" returns null when no records found,
5494: -- so check l_effective_date is null to find whether any
5495: -- past records were found.
5496: --
5497: hr_utility.set_location( 'Effective_date ' || l_effective_date , 99 );
5498: hr_utility.set_location( 'l_prev_pk_id ' || l_prev_pk_id , 99 );
5499: hr_utility.set_location( 'l_pk_id ' || l_pk_id , 99 );
5500: -- changed 7176884 begin
5501: --
5494: -- so check l_effective_date is null to find whether any
5495: -- past records were found.
5496: --
5497: hr_utility.set_location( 'Effective_date ' || l_effective_date , 99 );
5498: hr_utility.set_location( 'l_prev_pk_id ' || l_prev_pk_id , 99 );
5499: hr_utility.set_location( 'l_pk_id ' || l_pk_id , 99 );
5500: -- changed 7176884 begin
5501: --
5502: hr_utility.set_location( 'p_per_in_ler_id ' || p_per_in_ler_id , 44333 );
5495: -- past records were found.
5496: --
5497: hr_utility.set_location( 'Effective_date ' || l_effective_date , 99 );
5498: hr_utility.set_location( 'l_prev_pk_id ' || l_prev_pk_id , 99 );
5499: hr_utility.set_location( 'l_pk_id ' || l_pk_id , 99 );
5500: -- changed 7176884 begin
5501: --
5502: hr_utility.set_location( 'p_per_in_ler_id ' || p_per_in_ler_id , 44333 );
5503: hr_utility.set_location( 'l_pk_id ' || l_pk_id , 44333 );
5498: hr_utility.set_location( 'l_prev_pk_id ' || l_prev_pk_id , 99 );
5499: hr_utility.set_location( 'l_pk_id ' || l_pk_id , 99 );
5500: -- changed 7176884 begin
5501: --
5502: hr_utility.set_location( 'p_per_in_ler_id ' || p_per_in_ler_id , 44333 );
5503: hr_utility.set_location( 'l_pk_id ' || l_pk_id , 44333 );
5504: --
5505: open c_get_enrt_mthd_cd(l_pk_id);
5506: fetch c_get_enrt_mthd_cd into l_get_enrt_mthd_cd;
5499: hr_utility.set_location( 'l_pk_id ' || l_pk_id , 99 );
5500: -- changed 7176884 begin
5501: --
5502: hr_utility.set_location( 'p_per_in_ler_id ' || p_per_in_ler_id , 44333 );
5503: hr_utility.set_location( 'l_pk_id ' || l_pk_id , 44333 );
5504: --
5505: open c_get_enrt_mthd_cd(l_pk_id);
5506: fetch c_get_enrt_mthd_cd into l_get_enrt_mthd_cd;
5507: close c_get_enrt_mthd_cd;
5505: open c_get_enrt_mthd_cd(l_pk_id);
5506: fetch c_get_enrt_mthd_cd into l_get_enrt_mthd_cd;
5507: close c_get_enrt_mthd_cd;
5508: --
5509: hr_utility.set_location( 'l_get_enrt_mthd_cd ' || l_get_enrt_mthd_cd , 44333 );
5510: -- changed 7176884 end
5511:
5512: if l_effective_date is not null then
5513: --
5514: -- Do not process the row as it is already processed(deleted).
5515: --
5516: if l_prev_pk_id <> l_pk_id then
5517:
5518: hr_utility.set_location('l_prev_pk_id <> l_pk_id = ' || l_prev_pk_id || ' <> ' || l_pk_id, 999);
5519: hr_utility.set_location( 'l_effective_date ' || l_effective_date , 99 );
5520:
5521: -- now backup all the deleted enrollment rows
5522: --
5515: --
5516: if l_prev_pk_id <> l_pk_id then
5517:
5518: hr_utility.set_location('l_prev_pk_id <> l_pk_id = ' || l_prev_pk_id || ' <> ' || l_pk_id, 999);
5519: hr_utility.set_location( 'l_effective_date ' || l_effective_date , 99 );
5520:
5521: -- now backup all the deleted enrollment rows
5522: --
5523: for l_deleted_pen_rec in c_deleted_pen(l_pk_id, l_effective_date)
5523: for l_deleted_pen_rec in c_deleted_pen(l_pk_id, l_effective_date)
5524: loop
5525: --
5526: --
5527: hr_utility.set_location( ' backup pil ' || l_deleted_pen_rec.PER_IN_LER_ID , 99 );
5528: hr_utility.set_location( ' backup pen ' || l_deleted_pen_rec.PRTT_ENRT_RSLT_ID , 99 );
5529: open c_bkp_row('BEN_PRTT_ENRT_RSLT_F',
5530: l_deleted_pen_rec.PER_IN_LER_ID,
5531: l_deleted_pen_rec.PRTT_ENRT_RSLT_ID,
5524: loop
5525: --
5526: --
5527: hr_utility.set_location( ' backup pil ' || l_deleted_pen_rec.PER_IN_LER_ID , 99 );
5528: hr_utility.set_location( ' backup pen ' || l_deleted_pen_rec.PRTT_ENRT_RSLT_ID , 99 );
5529: open c_bkp_row('BEN_PRTT_ENRT_RSLT_F',
5530: l_deleted_pen_rec.PER_IN_LER_ID,
5531: l_deleted_pen_rec.PRTT_ENRT_RSLT_ID,
5532: l_deleted_pen_rec.object_version_number);
5534: --
5535: if c_bkp_row%notfound
5536: then
5537: --
5538: hr_utility.set_location( ' copying ' || l_deleted_pen_rec.PRTT_ENRT_RSLT_ID , 99 );
5539: close c_bkp_row;
5540: --
5541: insert into BEN_LE_CLSN_N_RSTR (
5542: BKUP_TBL_TYP_CD,
5798: open c_corr_result_exist (p_per_in_ler_id ,
5799: l_pk_id ) ;
5800: fetch c_corr_result_exist into l_dummy ;
5801: close c_corr_result_exist ;
5802: hr_utility.set_location( 'corrected result exist ' || l_dummy , 99 );
5803:
5804: if l_effective_date <> hr_api.g_eot and l_dummy is null and nvl(p_copy_only,'N') <> 'Y' then
5805: ben_prtt_enrt_result_api.delete_prtt_enrt_result
5806: (p_validate => false,
5861:
5862: -- 2982606 if the update for the result level then copy the record to the
5863: -- backup table
5864: if p_bckdt_prtt_enrt_rslt_id is not null then
5865: hr_utility.set_location('l_pk_id = ' || l_pk_id ,99);
5866: hr_utility.set_location('l_pen_esd = ' || l_pen_esd ,99);
5867:
5868: -- now backup all the deleted enrollment rows
5869: --
5862: -- 2982606 if the update for the result level then copy the record to the
5863: -- backup table
5864: if p_bckdt_prtt_enrt_rslt_id is not null then
5865: hr_utility.set_location('l_pk_id = ' || l_pk_id ,99);
5866: hr_utility.set_location('l_pen_esd = ' || l_pen_esd ,99);
5867:
5868: -- now backup all the deleted enrollment rows
5869: --
5870: for l_deleted_pen_rec in c_deleted_pen(l_pk_id, l_pen_esd)
5880: if c_bkp_row%notfound
5881: then
5882: --
5883: close c_bkp_row;
5884: hr_utility.set_location(' creating backup ' || l_pk_id, 999);
5885: insert into BEN_LE_CLSN_N_RSTR (
5886: BKUP_TBL_TYP_CD,
5887: COMP_LVL_CD,
5888: LCR_ATTRIBUTE16,
6197: --- correct the result with with per_in_ler_id and coverage dates bug # 3086161
6198: for i in c_BEN_LE_CLSN_N_RSTR_corr(p_per_in_ler_id)
6199: Loop
6200:
6201: hr_utility.set_location(' corrected result ' || i.bkup_tbl_id, 99 );
6202: if l_prev_bkup_tbl_id <> i.bkup_tbl_id and nvl(p_copy_only,'N') <> 'Y' then
6203:
6204: l_object_version_number := i.object_version_number ;
6205: --bug#5032364
6225: -- Bug 7197868
6226: --
6227: if (i.effective_end_date = hr_api.g_eot
6228: and i.pen_effective_end_date <> i.effective_end_date) then
6229: hr_utility.set_location(' correcting ' || i.bkup_tbl_id, 999 );
6230: --
6231: -- Bug 7197868
6232: --
6233: -- Delete future dated records.
6268: -- delete the row from the backup table -- 7197868
6269: --
6270: if (i.effective_end_date = hr_api.g_eot
6271: and i.pen_effective_end_date <> i.effective_end_date) then
6272: hr_utility.set_location(' correcting ' || i.bkup_tbl_id, 999 );
6273: hr_utility.set_location(' i.per_in_ler_id ' || i.per_in_ler_id, 999 );
6274: hr_utility.set_location(' p_per_in_ler_id ' || p_per_in_ler_id, 999 );
6275: delete from ben_le_clsn_n_rstr cqb
6276: where cqb.per_in_ler_id = i.per_in_ler_id
6269: --
6270: if (i.effective_end_date = hr_api.g_eot
6271: and i.pen_effective_end_date <> i.effective_end_date) then
6272: hr_utility.set_location(' correcting ' || i.bkup_tbl_id, 999 );
6273: hr_utility.set_location(' i.per_in_ler_id ' || i.per_in_ler_id, 999 );
6274: hr_utility.set_location(' p_per_in_ler_id ' || p_per_in_ler_id, 999 );
6275: delete from ben_le_clsn_n_rstr cqb
6276: where cqb.per_in_ler_id = i.per_in_ler_id
6277: and cqb.per_in_ler_ended_id = p_per_in_ler_id
6270: if (i.effective_end_date = hr_api.g_eot
6271: and i.pen_effective_end_date <> i.effective_end_date) then
6272: hr_utility.set_location(' correcting ' || i.bkup_tbl_id, 999 );
6273: hr_utility.set_location(' i.per_in_ler_id ' || i.per_in_ler_id, 999 );
6274: hr_utility.set_location(' p_per_in_ler_id ' || p_per_in_ler_id, 999 );
6275: delete from ben_le_clsn_n_rstr cqb
6276: where cqb.per_in_ler_id = i.per_in_ler_id
6277: and cqb.per_in_ler_ended_id = p_per_in_ler_id
6278: and cqb.bkup_tbl_id = i.bkup_tbl_id ;
6308: l_prev_bkup_tbl_id := -1;
6309: --
6310: for i in c_BEN_LE_CLSN_N_RSTR_del(p_per_in_ler_id)
6311: Loop
6312: hr_utility.set_location(' delete result ' || i.bkup_tbl_id, 99 );
6313: if l_prev_bkup_tbl_id <> i.bkup_tbl_id then
6314:
6315: l_object_version_number := i.object_version_number ;
6316: --bug#5032364
6344: --
6345: open c_pen_ovn (l_cvg_adj.bkup_tbl_id);
6346: fetch c_pen_ovn into l_object_version_number;
6347: close c_pen_ovn;
6348: hr_utility.set_location('Ajdust coverage for '||l_cvg_adj.bkup_tbl_id,44333);
6349: --
6350: adj_pen_cvg (p_person_id => l_cvg_adj.person_id,
6351: p_prtt_enrt_rslt_id => l_cvg_adj.bkup_tbl_id,
6352: p_cvg_end_dt => l_cvg_adj.enrt_cvg_thru_dt,
6369: -- Such BPL Records have been backed up in BEN_LE_CLSN_N_RSTR. See BEN_BPL_DEL.POST_DELETE
6370: -- The part of the code below (which queries cursor c_ben_bnft_prvdd_ldgr_f) takes care of rest
6371: -- of the scenarios and voiding of BPL associated with the life-event being backed out.
6372: --
6373: hr_utility.set_location('ACE p_per_in_ler_id = ' || p_per_in_ler_id, 9999);
6374: --
6375: for l_bpl_from_backup in c_bpl_from_backup
6376: loop
6377: --
6374: --
6375: for l_bpl_from_backup in c_bpl_from_backup
6376: loop
6377: --
6378: hr_utility.set_location('ACE l_bpl_from_backup.bnft_prvdd_ldgr_id = ' || l_bpl_from_backup.BKUP_TBL_ID, 9999);
6379: --
6380: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger
6381: (
6382: p_bnft_prvdd_ldgr_id => l_bpl_from_backup.BKUP_TBL_ID,
6386: p_effective_date => l_bpl_from_backup.effective_start_date,
6387: p_datetrack_mode => hr_api.g_FUTURE_CHANGE,
6388: p_business_group_id => p_business_group_id
6389: );
6390: hr_utility.set_location('ACE Reopened = ' || l_bpl_from_backup.BKUP_TBL_ID, 9999);
6391: --
6392: -- Bug 6376239 : Remove the rows from backup table once the ledger rows are restored
6393: --
6394: delete from ben_le_clsn_n_rstr cls
6434: l_object_version_number := l_max_object_version_number;
6435: --
6436: if l_effective_date <> hr_api.g_eot and nvl(p_copy_only,'N') <> 'Y' then
6437: --
6438: hr_utility.set_location('Deleting ledger='||to_char(l_bpl.bnft_prvdd_ldgr_id), 50);
6439: --
6440: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
6441: p_bnft_prvdd_ldgr_id => l_bpl.bnft_prvdd_ldgr_id,
6442: p_effective_start_date => l_bpl.effective_start_date,
6456: -- added if condition to check if there is any update for the same per_in_ler
6457: if l_prev_pk_id <> l_pk_id and nvl(p_copy_only,'N') <> 'Y' then
6458: --
6459: l_bpl_effective_date := l_bpl.effective_start_date ;
6460: hr_utility.set_location('ledger Id'||l_bpl.bnft_prvdd_ldgr_id,11);
6461: hr_utility.set_location('ovn Id'||l_bpl.object_version_number,11);
6462: --
6463: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
6464: p_bnft_prvdd_ldgr_id => l_bpl.bnft_prvdd_ldgr_id,
6457: if l_prev_pk_id <> l_pk_id and nvl(p_copy_only,'N') <> 'Y' then
6458: --
6459: l_bpl_effective_date := l_bpl.effective_start_date ;
6460: hr_utility.set_location('ledger Id'||l_bpl.bnft_prvdd_ldgr_id,11);
6461: hr_utility.set_location('ovn Id'||l_bpl.object_version_number,11);
6462: --
6463: ben_Benefit_Prvdd_Ledger_api.delete_Benefit_Prvdd_Ledger(
6464: p_bnft_prvdd_ldgr_id => l_bpl.bnft_prvdd_ldgr_id,
6465: p_effective_start_date => l_bpl.effective_start_date,
6586: raise ben_manage_life_events.g_record_error;
6587:
6588: end if;
6589: --
6590: hr_utility.set_location ('End of '||p_routine,10);
6591: hr_utility.set_location ('Leaving '||l_package,10);
6592: --
6593: exception
6594: when others then
6587:
6588: end if;
6589: --
6590: hr_utility.set_location ('End of '||p_routine,10);
6591: hr_utility.set_location ('Leaving '||l_package,10);
6592: --
6593: exception
6594: when others then
6595: --