[Home] [Help]
458: BEN_91711_ENRT_RSLT_NOT_FND
459: 115.197 07-Jan-03 ikasire Bug 2739965 delete enrollment for unrestricted.
460: added p_ler_typ_cd parameter to get_ben_pen_upd_dt_mode
461: procedure
462: 115.199 15-Feb-03 pbodla DEBUG : hr_utility.set_location calls wrpped
463: around if statements.
464: 115.200 14-Mar-03 kmahendr Bug#2785410 - code added in void_enrollment proc.
465: 115.201 14-Mar-03 ikasire Bug2847110 and 2852703 1 Prior Code fixes
466: 115.202 24-Mar-03 ikasire Bug 2739965 Added a warning message while deleting the future
765: --
766: -- Package Variables
767: --
768: g_package Varchar2(33) := ' ben_PRTT_ENRT_RESULT_api.';
769: g_debug boolean := hr_utility.debug_enabled;
770:
771: /* Added for Bug 7561395*/
772:
773: g_new_prtt_enrt_rslt_id number;
789: ,p_step in number
790: ) is
791: begin
792: if g_debug then
793: hr_utility.set_location('Fail in '|| p_proc || ' at step ' ||
794: to_char(p_step),999);
795: end if;
796: end;
797:
870: --
871: begin
872: if g_debug then
873: l_proc := g_package||'.remove_cert_action_items';
874: hr_utility.set_location ('Entering '||l_proc,10);
875: hr_utility.set_location ('p_end_date '||p_end_date,10);
876: end if;
877: --
878: l_step := 10;
871: begin
872: if g_debug then
873: l_proc := g_package||'.remove_cert_action_items';
874: hr_utility.set_location ('Entering '||l_proc,10);
875: hr_utility.set_location ('p_end_date '||p_end_date,10);
876: end if;
877: --
878: l_step := 10;
879: /* leslie removed this call
1227: end if;
1228: end if;
1229: end loop;
1230: if g_debug then
1231: hr_utility.set_location ('Leaving '||l_proc,70);
1232: end if;
1233: --
1234: exception
1235: when others then
1233: --
1234: exception
1235: when others then
1236: if g_debug then
1237: hr_utility.set_location ('Fail in '||l_proc|| ' at step ' ||
1238: to_char(l_step),999);
1239: end if;
1240: raise;
1241: end;
1264: l_step number(9);
1265: l_proc varchar2(80) ; -- := g_package||'.get_ben_pen_upd_dt_mode';
1266: begin
1267: --
1268: g_debug := hr_utility.debug_enabled;
1269: if g_debug then
1270: l_proc := g_package||'.get_ben_pen_upd_dt_mode';
1271: hr_utility.set_location ('Entering '||l_proc,10);
1272: hr_utility.set_location('IK p_effective_date'||p_effective_date,1234);
1267: --
1268: g_debug := hr_utility.debug_enabled;
1269: if g_debug then
1270: l_proc := g_package||'.get_ben_pen_upd_dt_mode';
1271: hr_utility.set_location ('Entering '||l_proc,10);
1272: hr_utility.set_location('IK p_effective_date'||p_effective_date,1234);
1273: hr_utility.set_location('IK P_desired_datetrack_mode '||P_desired_datetrack_mode,1234);
1274: end if;
1275: --
1268: g_debug := hr_utility.debug_enabled;
1269: if g_debug then
1270: l_proc := g_package||'.get_ben_pen_upd_dt_mode';
1271: hr_utility.set_location ('Entering '||l_proc,10);
1272: hr_utility.set_location('IK p_effective_date'||p_effective_date,1234);
1273: hr_utility.set_location('IK P_desired_datetrack_mode '||P_desired_datetrack_mode,1234);
1274: end if;
1275: --
1276: l_step := 10;
1269: if g_debug then
1270: l_proc := g_package||'.get_ben_pen_upd_dt_mode';
1271: hr_utility.set_location ('Entering '||l_proc,10);
1272: hr_utility.set_location('IK p_effective_date'||p_effective_date,1234);
1273: hr_utility.set_location('IK P_desired_datetrack_mode '||P_desired_datetrack_mode,1234);
1274: end if;
1275: --
1276: l_step := 10;
1277: --
1315: l_step := 20;
1316: --
1317: if l_dt_correction then
1318: if g_debug then
1319: hr_utility.set_location( 'l_dt_correction',1234);
1320: end if;
1321: end if;
1322: --
1323: if l_dt_update then
1321: end if;
1322: --
1323: if l_dt_update then
1324: if g_debug then
1325: hr_utility.set_location( 'l_dt_update',1234);
1326: end if;
1327: end if;
1328: --
1329: if l_dt_update_override then
1327: end if;
1328: --
1329: if l_dt_update_override then
1330: if g_debug then
1331: hr_utility.set_location( 'l_dt_update_override',1234);
1332: end if;
1333: end if;
1334: --
1335: if l_dt_update_change_insert then
1333: end if;
1334: --
1335: if l_dt_update_change_insert then
1336: if g_debug then
1337: hr_utility.set_location( 'l_dt_update_change_insert',1234);
1338: end if;
1339: end if;
1340:
1341: if l_dt_delete_next_change then
1339: end if;
1340:
1341: if l_dt_delete_next_change then
1342: if g_debug then
1343: hr_utility.set_location( 'l_dt_delete_next_change',1234);
1344: end if;
1345: end if;
1346:
1347: if l_dt_delete then
1345: end if;
1346:
1347: if l_dt_delete then
1348: if g_debug then
1349: hr_utility.set_location( 'l_dt_delete',1234);
1350: end if;
1351: end if;
1352:
1353: --
1381: fnd_message.raise_error;
1382: end if;
1383: --
1384: if g_debug then
1385: hr_utility.set_location(' P_datetrack_allow '||P_datetrack_allow ,1234);
1386: end if;
1387: l_step := 30;
1388: --
1389: if g_debug then
1386: end if;
1387: l_step := 30;
1388: --
1389: if g_debug then
1390: hr_utility.set_location ('Leaving '||l_proc,70);
1391: end if;
1392: Exception
1393: when others then
1394: rpt_error(p_proc => l_proc, p_step => l_step);
1431: begin
1432: --
1433: if g_debug then
1434: l_proc := g_package || '.change_exists_in_db';
1435: hr_utility.set_location ('Entering '||l_proc,10);
1436: end if;
1437: --
1438: open get_change;
1439: fetch get_change into l_dummy;
1448: --
1449: return (l_return);
1450: --
1451: if g_debug then
1452: hr_utility.set_location ('Leaving '||l_proc,70);
1453: end if;
1454: --
1455: end change_exists_in_db;
1456: --
1742: l_process_bnf boolean := false;
1743: --
1744: begin
1745: --
1746: g_debug := hr_utility.debug_enabled;
1747: if g_debug then
1748: l_proc := g_package||'create_enrollment';
1749: hr_utility.set_location('Entering:'|| l_proc, 10);
1750: end if;
1745: --
1746: g_debug := hr_utility.debug_enabled;
1747: if g_debug then
1748: l_proc := g_package||'create_enrollment';
1749: hr_utility.set_location('Entering:'|| l_proc, 10);
1750: end if;
1751: g_multi_rows_validate := p_multi_row_validate;
1752: l_step := 10;
1753: If (p_multi_row_validate) then
1775: l_step := 15;
1776: --
1777: l_crntly_enrd_flag := 'N';
1778: if g_debug then
1779: hr_utility.set_location(l_proc, 20);
1780: end if;
1781: l_step := 20;
1782: --
1783: -- Get choice data from prtt_per_elctbl_chc table
1946:
1947: /*Added for Bug 7561395*/
1948: g_enrt_list.extend;
1949: g_enrt_list(g_enrt_list.last) := l_prtt_enrt_rslt_id;
1950: hr_utility.set_location(' Extend table ',500);
1951: hr_utility.set_location('New pen_id '|| g_new_prtt_enrt_rslt_id,500);
1952: hr_utility.set_location('Intmr Rslt Id '|| p_rplcs_sspndd_rslt_id,500);
1953: hr_utility.set_location('oipl id cr '|| p_oipl_id,500);
1954: /*Ended for Bug 7561395*/
1947: /*Added for Bug 7561395*/
1948: g_enrt_list.extend;
1949: g_enrt_list(g_enrt_list.last) := l_prtt_enrt_rslt_id;
1950: hr_utility.set_location(' Extend table ',500);
1951: hr_utility.set_location('New pen_id '|| g_new_prtt_enrt_rslt_id,500);
1952: hr_utility.set_location('Intmr Rslt Id '|| p_rplcs_sspndd_rslt_id,500);
1953: hr_utility.set_location('oipl id cr '|| p_oipl_id,500);
1954: /*Ended for Bug 7561395*/
1955:
1948: g_enrt_list.extend;
1949: g_enrt_list(g_enrt_list.last) := l_prtt_enrt_rslt_id;
1950: hr_utility.set_location(' Extend table ',500);
1951: hr_utility.set_location('New pen_id '|| g_new_prtt_enrt_rslt_id,500);
1952: hr_utility.set_location('Intmr Rslt Id '|| p_rplcs_sspndd_rslt_id,500);
1953: hr_utility.set_location('oipl id cr '|| p_oipl_id,500);
1954: /*Ended for Bug 7561395*/
1955:
1956: ben_global_enrt.reload_pen -- result globals loaded after insert.
1949: g_enrt_list(g_enrt_list.last) := l_prtt_enrt_rslt_id;
1950: hr_utility.set_location(' Extend table ',500);
1951: hr_utility.set_location('New pen_id '|| g_new_prtt_enrt_rslt_id,500);
1952: hr_utility.set_location('Intmr Rslt Id '|| p_rplcs_sspndd_rslt_id,500);
1953: hr_utility.set_location('oipl id cr '|| p_oipl_id,500);
1954: /*Ended for Bug 7561395*/
1955:
1956: ben_global_enrt.reload_pen -- result globals loaded after insert.
1957: (p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
2031: ,p_lf_evt_ocrd_dt => nvl(l_prem.lf_evt_ocrd_dt,p_effective_date)
2032: );
2033:
2034: l_ppe_dt_to_use := greatest(p_enrt_cvg_strt_dt,l_rt_strt_dt);
2035: hr_utility.set_location( 'cvg start dt ' || p_enrt_cvg_strt_dt, 99 ) ;
2036: hr_utility.set_location( 'l_ppe_dt_to_use ' || l_ppe_dt_to_use, 99 ) ;
2037:
2038: /* End of Changes for WWBUG: 1646442: addition */
2039: --
2032: );
2033:
2034: l_ppe_dt_to_use := greatest(p_enrt_cvg_strt_dt,l_rt_strt_dt);
2035: hr_utility.set_location( 'cvg start dt ' || p_enrt_cvg_strt_dt, 99 ) ;
2036: hr_utility.set_location( 'l_ppe_dt_to_use ' || l_ppe_dt_to_use, 99 ) ;
2037:
2038: /* End of Changes for WWBUG: 1646442: addition */
2039: --
2040: /*
2231: -- dependent information as well.
2232: --
2233: l_step := 150;
2234: if g_debug then
2235: hr_utility.set_location('p_prtt_enrt_rslt_id_o'||
2236: to_char(p_prtt_enrt_rslt_id_o)||' l_epe.prtt_enrt_rslt_id'||
2237: to_char(l_global_epe_rec.prtt_enrt_rslt_id),150);
2238: hr_utility.set_location(
2239: ' flag'||l_global_epe_rec.crntly_enrd_flag, 150);
2234: if g_debug then
2235: hr_utility.set_location('p_prtt_enrt_rslt_id_o'||
2236: to_char(p_prtt_enrt_rslt_id_o)||' l_epe.prtt_enrt_rslt_id'||
2237: to_char(l_global_epe_rec.prtt_enrt_rslt_id),150);
2238: hr_utility.set_location(
2239: ' flag'||l_global_epe_rec.crntly_enrd_flag, 150);
2240: end if;
2241:
2242: if (p_prtt_enrt_rslt_id_o is not NULL
2252: (p_business_group_id => p_business_group_id
2253: ,p_effective_date => p_effective_date
2254: ,p_pgm_id => l_global_epe_rec.pgm_id
2255: ,p_inst_row => l_pgm_rec);
2256: hr_utility.set_location('process dptn level ' ||l_pgm_rec.dpnt_dsgn_lvl_cd , 150);
2257: if l_pgm_rec.dpnt_dsgn_lvl_cd='PTIP' then
2258: open c_ptip_enrollment_info;
2259: fetch c_ptip_enrollment_info into l_ptip_enrt;
2260: if c_ptip_enrollment_info%found then
2258: open c_ptip_enrollment_info;
2259: fetch c_ptip_enrollment_info into l_ptip_enrt;
2260: if c_ptip_enrollment_info%found then
2261: l_process_dpnt:= true;
2262: hr_utility.set_location('process dptn true ', 150);
2263: end if ;
2264: end if ;
2265: end if ;
2266: end if;
2433:
2434: /* Added for Bug 7561395*/
2435: if(g_enrt_list.count > 1) then
2436: g_enrt_list.trim(1);
2437: hr_utility.set_location(' count gt 1 ',5522);
2438: else
2439: if(g_enrt_list.count = 1) then
2440: hr_utility.set_location(' count is 1 ',5522);
2441: g_new_prtt_enrt_rslt_id := g_enrt_list(g_enrt_list.last);
2436: g_enrt_list.trim(1);
2437: hr_utility.set_location(' count gt 1 ',5522);
2438: else
2439: if(g_enrt_list.count = 1) then
2440: hr_utility.set_location(' count is 1 ',5522);
2441: g_new_prtt_enrt_rslt_id := g_enrt_list(g_enrt_list.last);
2442: g_enrt_list.trim(1);
2443: hr_utility.set_location(' g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id ,5522);
2444: end if;
2439: if(g_enrt_list.count = 1) then
2440: hr_utility.set_location(' count is 1 ',5522);
2441: g_new_prtt_enrt_rslt_id := g_enrt_list(g_enrt_list.last);
2442: g_enrt_list.trim(1);
2443: hr_utility.set_location(' g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id ,5522);
2444: end if;
2445: end if;
2446: /* Ended for Bug 7561395*/
2447:
2445: end if;
2446: /* Ended for Bug 7561395*/
2447:
2448: if g_debug then
2449: hr_utility.set_location(' Leaving:'||l_proc, 999);
2450: end if;
2451: exception
2452: when hr_api.validate_enabled then
2453: -- As the Validate_Enabled exception has been raised
2475: p_effective_start_date := null;
2476: p_effective_end_date := null;
2477: p_object_version_number := null;
2478: if g_debug then
2479: hr_utility.set_location(' Leaving:'||l_proc, 80);
2480: end if;
2481: when others then
2482: --
2483: -- A validation or unexpected error has occured
2591: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2592: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2593: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2594: begin
2595: g_debug := hr_utility.debug_enabled;
2596: if g_debug then
2597: l_proc := g_package||'create_PRTT_ENRT_RESULT';
2598: hr_utility.set_location('Entering:'|| l_proc, 10);
2599: end if;
2594: begin
2595: g_debug := hr_utility.debug_enabled;
2596: if g_debug then
2597: l_proc := g_package||'create_PRTT_ENRT_RESULT';
2598: hr_utility.set_location('Entering:'|| l_proc, 10);
2599: end if;
2600: --
2601: g_multi_rows_validate := p_multi_row_validate;
2602: --
2616: p_benefit_action_id => null);
2617: --
2618: end if;
2619: if g_debug then
2620: hr_utility.set_location(l_proc, 20);
2621: end if;
2622: --
2623: -- Process Logic
2624: --
2868: ,p_hook_type => 'AP'
2869: );
2870: end;
2871: if g_debug then
2872: hr_utility.set_location(l_proc, 60);
2873: end if;
2874: --
2875: -- When in validation only mode raise the Validate_Enabled exception
2876: --
2884: p_effective_start_date := l_effective_start_date;
2885: p_effective_end_date := l_effective_end_date;
2886: p_object_version_number := l_object_version_number;
2887: if g_debug then
2888: hr_utility.set_location(' Leaving:'||l_proc, 70);
2889: end if;
2890: exception
2891: when hr_api.validate_enabled then
2892: --
2903: p_effective_start_date := null;
2904: p_effective_end_date := null;
2905: p_object_version_number := null;
2906: if g_debug then
2907: hr_utility.set_location(' Leaving:'||l_proc, 80);
2908: end if;
2909: when others then
2910: --
2911: -- A validation or unexpected error has occured
3339: --
3340: begin
3341: --
3342: l_step := 10;
3343: g_debug := hr_utility.debug_enabled;
3344: if g_debug then
3345: l_proc := g_package||'update_ENROLLMENT';
3346: hr_utility.set_location('Entering:'|| l_proc, 10);
3347: end if;
3342: l_step := 10;
3343: g_debug := hr_utility.debug_enabled;
3344: if g_debug then
3345: l_proc := g_package||'update_ENROLLMENT';
3346: hr_utility.set_location('Entering:'|| l_proc, 10);
3347: end if;
3348: --
3349: g_multi_rows_validate := p_multi_row_validate;
3350: If (p_multi_row_validate) then
3356: -- Issue a savepoint if operating in validation only mode
3357: --
3358: savepoint update_ENROLLMENT;
3359: if g_debug then
3360: hr_utility.set_location(l_proc, 20);
3361: end if;
3362: --
3363: -- Process Logic
3364: --
3818: fetch c_bpl into l_prvdd_val ;
3819: close c_bpl ;
3820: --if there is amount change call the updating
3821: if g_debug then
3822: hr_utility.set_location( 'prvdd val ' || l_prvdd_val ||
3823: ' ; changed val ' || i.val , 1001);
3824: end if;
3825:
3826: if l_prvdd_val is not null and l_prvdd_val <> i.val then
3953:
3954: l_step := 72;
3955: ---For the Bug : 7133998
3956:
3957: hr_utility.set_location('p_prtt_enrt_rslt_id,srav : '||p_prtt_enrt_rslt_id,1);
3958: hr_utility.set_location('l_prem.actl_prem_id : '||l_prem.actl_prem_id,1);
3959: hr_utility.set_location('l_ppe_dt_to_use : '||l_ppe_dt_to_use,1);
3960:
3961: -----check if there are any future rows corresponding to the Previous LE's.
3954: l_step := 72;
3955: ---For the Bug : 7133998
3956:
3957: hr_utility.set_location('p_prtt_enrt_rslt_id,srav : '||p_prtt_enrt_rslt_id,1);
3958: hr_utility.set_location('l_prem.actl_prem_id : '||l_prem.actl_prem_id,1);
3959: hr_utility.set_location('l_ppe_dt_to_use : '||l_ppe_dt_to_use,1);
3960:
3961: -----check if there are any future rows corresponding to the Previous LE's.
3962: --------7133998
3955: ---For the Bug : 7133998
3956:
3957: hr_utility.set_location('p_prtt_enrt_rslt_id,srav : '||p_prtt_enrt_rslt_id,1);
3958: hr_utility.set_location('l_prem.actl_prem_id : '||l_prem.actl_prem_id,1);
3959: hr_utility.set_location('l_ppe_dt_to_use : '||l_ppe_dt_to_use,1);
3960:
3961: -----check if there are any future rows corresponding to the Previous LE's.
3962: --------7133998
3963: l_count := 0;
3960:
3961: -----check if there are any future rows corresponding to the Previous LE's.
3962: --------7133998
3963: l_count := 0;
3964: hr_utility.set_location('srav,in else',1);
3965: for l_ppe1 in c_ppe1(p_prtt_enrt_rslt_id, l_prem.actl_prem_id,l_ppe_dt_to_use) loop
3966: l_count := l_count + 1;
3967: hr_utility.set_location('c_ppe1 found',1);
3968: hr_utility.set_location('l_ppe1.prtt_prem_id : '||l_ppe1.prtt_prem_id,1);
3963: l_count := 0;
3964: hr_utility.set_location('srav,in else',1);
3965: for l_ppe1 in c_ppe1(p_prtt_enrt_rslt_id, l_prem.actl_prem_id,l_ppe_dt_to_use) loop
3966: l_count := l_count + 1;
3967: hr_utility.set_location('c_ppe1 found',1);
3968: hr_utility.set_location('l_ppe1.prtt_prem_id : '||l_ppe1.prtt_prem_id,1);
3969: ---bkup table
3970: insert into BEN_LE_CLSN_N_RSTR (
3971: BKUP_TBL_TYP_CD,
3964: hr_utility.set_location('srav,in else',1);
3965: for l_ppe1 in c_ppe1(p_prtt_enrt_rslt_id, l_prem.actl_prem_id,l_ppe_dt_to_use) loop
3966: l_count := l_count + 1;
3967: hr_utility.set_location('c_ppe1 found',1);
3968: hr_utility.set_location('l_ppe1.prtt_prem_id : '||l_ppe1.prtt_prem_id,1);
3969: ---bkup table
3970: insert into BEN_LE_CLSN_N_RSTR (
3971: BKUP_TBL_TYP_CD,
3972: LCR_ATTRIBUTE6,
4219: ,p_enrt_cvg_end_dt => l_enrt_cvg_end_dt
4220: );
4221:
4222: l_dpnt_cvg_thru_dt := l_dpnt_cvg_end_dt;
4223: hr_utility.set_location('9881657 l_dpnt_cvg_thru_dt ' || l_dpnt_cvg_thru_dt, 123.123);
4224: --9881657
4225:
4226: --------Bug 8919376
4227: for l_chk_cvrd_dpnt in c_check_cvrd_dpnt(l_dpnt_cvg_thru_dt) loop --9881657
4246: ---check if corresponding to the same per_in_ler,any other records exists with same pdp_id
4247: open c_check_dup_dep(l_egd.elig_cvrd_dpnt_id);
4248: fetch c_check_dup_dep into l_check_dup_dep;
4249: if c_check_dup_dep%found then
4250: hr_utility.set_location('dup dep row found',1);
4251: hr_utility.set_location('pdp_id : '||l_check_dup_dep.elig_cvrd_dpnt_id,1);
4252: ---Get the previous pil,which has enrollment result attached to it.
4253: open c_get_last_pil;
4254: fetch c_get_last_pil into l_get_last_pil;
4247: open c_check_dup_dep(l_egd.elig_cvrd_dpnt_id);
4248: fetch c_check_dup_dep into l_check_dup_dep;
4249: if c_check_dup_dep%found then
4250: hr_utility.set_location('dup dep row found',1);
4251: hr_utility.set_location('pdp_id : '||l_check_dup_dep.elig_cvrd_dpnt_id,1);
4252: ---Get the previous pil,which has enrollment result attached to it.
4253: open c_get_last_pil;
4254: fetch c_get_last_pil into l_get_last_pil;
4255: if c_get_last_pil%found then
4252: ---Get the previous pil,which has enrollment result attached to it.
4253: open c_get_last_pil;
4254: fetch c_get_last_pil into l_get_last_pil;
4255: if c_get_last_pil%found then
4256: hr_utility.set_location('pil_id : '||l_get_last_pil.per_in_ler_id,1);
4257: open c_last_pil_pdp(l_get_last_pil.per_in_ler_id,l_egd.elig_cvrd_dpnt_id);
4258: fetch c_last_pil_pdp into l_last_pil_pdp;
4259: if c_last_pil_pdp%found then
4260: hr_utility.set_location('last pil pdp found',1);
4256: hr_utility.set_location('pil_id : '||l_get_last_pil.per_in_ler_id,1);
4257: open c_last_pil_pdp(l_get_last_pil.per_in_ler_id,l_egd.elig_cvrd_dpnt_id);
4258: fetch c_last_pil_pdp into l_last_pil_pdp;
4259: if c_last_pil_pdp%found then
4260: hr_utility.set_location('last pil pdp found',1);
4261: hr_utility.set_location('l_last_pil_pdp.elig_cvrd_dpnt_id : '|| l_last_pil_pdp.elig_cvrd_dpnt_id,1);
4262: hr_utility.set_location('l_last_pil_pdp.effective_start_date : '|| l_last_pil_pdp.effective_start_date,1);
4263: hr_utility.set_location('l_last_pil_pdp.object_version_number : '|| l_last_pil_pdp.object_version_number,1);
4264: ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt
4257: open c_last_pil_pdp(l_get_last_pil.per_in_ler_id,l_egd.elig_cvrd_dpnt_id);
4258: fetch c_last_pil_pdp into l_last_pil_pdp;
4259: if c_last_pil_pdp%found then
4260: hr_utility.set_location('last pil pdp found',1);
4261: hr_utility.set_location('l_last_pil_pdp.elig_cvrd_dpnt_id : '|| l_last_pil_pdp.elig_cvrd_dpnt_id,1);
4262: hr_utility.set_location('l_last_pil_pdp.effective_start_date : '|| l_last_pil_pdp.effective_start_date,1);
4263: hr_utility.set_location('l_last_pil_pdp.object_version_number : '|| l_last_pil_pdp.object_version_number,1);
4264: ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt
4265: (p_validate => FALSE
4258: fetch c_last_pil_pdp into l_last_pil_pdp;
4259: if c_last_pil_pdp%found then
4260: hr_utility.set_location('last pil pdp found',1);
4261: hr_utility.set_location('l_last_pil_pdp.elig_cvrd_dpnt_id : '|| l_last_pil_pdp.elig_cvrd_dpnt_id,1);
4262: hr_utility.set_location('l_last_pil_pdp.effective_start_date : '|| l_last_pil_pdp.effective_start_date,1);
4263: hr_utility.set_location('l_last_pil_pdp.object_version_number : '|| l_last_pil_pdp.object_version_number,1);
4264: ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt
4265: (p_validate => FALSE
4266: ,p_elig_cvrd_dpnt_id => l_last_pil_pdp.elig_cvrd_dpnt_id
4259: if c_last_pil_pdp%found then
4260: hr_utility.set_location('last pil pdp found',1);
4261: hr_utility.set_location('l_last_pil_pdp.elig_cvrd_dpnt_id : '|| l_last_pil_pdp.elig_cvrd_dpnt_id,1);
4262: hr_utility.set_location('l_last_pil_pdp.effective_start_date : '|| l_last_pil_pdp.effective_start_date,1);
4263: hr_utility.set_location('l_last_pil_pdp.object_version_number : '|| l_last_pil_pdp.object_version_number,1);
4264: ben_elig_cvrd_dpnt_api.delete_elig_cvrd_dpnt
4265: (p_validate => FALSE
4266: ,p_elig_cvrd_dpnt_id => l_last_pil_pdp.elig_cvrd_dpnt_id
4267: ,p_effective_start_date => p_effective_start_date
4278: ---re-fetch the OVN
4279: open c_last_pil_pdp(l_get_last_pil.per_in_ler_id,l_egd.elig_cvrd_dpnt_id);
4280: fetch c_last_pil_pdp into l_last_pil_pdp;
4281: if c_last_pil_pdp%found then
4282: hr_utility.set_location('l_last_pil_pdp.object_version_number : '|| l_last_pil_pdp.object_version_number,1);
4283: l_egd.object_version_number := l_last_pil_pdp.object_version_number;
4284: end if;
4285: close c_last_pil_pdp;
4286: end if;
4313: l_new_dpnt_cvg_thru_dt := hr_api.g_eot;
4314: if l_egd.cvg_thru_dt <> hr_api.g_eot then
4315: l_new_dpnt_cvg_thru_dt := l_dpnt_cvg_thru_dt;
4316: end if;
4317: hr_utility.set_location('9881657 l_new_dpnt_cvg_thru_dt' || l_new_dpnt_cvg_thru_dt, 123.123);
4318: --9881657
4319:
4320: ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt(
4321: p_elig_cvrd_dpnt_id => l_egd.elig_cvrd_dpnt_id,
4442: end if;
4443: end if;
4444: end if;
4445: if g_debug then
4446: hr_utility.set_location(l_proc, 60);
4447: end if;
4448: --
4449: -- When in validation only mode raise the Validate_Enabled exception
4450: --
4457: p_object_version_number := l_object_version_number;
4458: p_effective_start_date := l_effective_start_date;
4459: p_effective_end_date := l_effective_end_date;
4460: if g_debug then
4461: hr_utility.set_location(' Leaving:'||l_proc, 70);
4462: end if;
4463: exception
4464: when hr_api.validate_enabled then
4465: --
4472: -- (Any key or derived arguments must be set to null
4473: -- when validation only mode is being used.)
4474: --
4475: if g_debug then
4476: hr_utility.set_location(' Leaving:'||l_proc, 80);
4477: end if;
4478: when others then
4479: --
4480: -- A validation or unexpected error has occured
4624:
4625: --
4626: begin
4627: --
4628: g_debug := hr_utility.debug_enabled;
4629: if g_debug then
4630: l_proc := g_package||'update_PRTT_ENRT_RESULT';
4631: hr_utility.set_location('Entering:'|| l_proc, 10);
4632: end if;
4627: --
4628: g_debug := hr_utility.debug_enabled;
4629: if g_debug then
4630: l_proc := g_package||'update_PRTT_ENRT_RESULT';
4631: hr_utility.set_location('Entering:'|| l_proc, 10);
4632: end if;
4633: g_multi_rows_validate := p_multi_row_validate;
4634: --
4635: -- Issue a savepoint if operating in validation only mode
4654: --
4655: end if;
4656: --
4657: if g_debug then
4658: hr_utility.set_location(l_proc, 20);
4659: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4660: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4661: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4662:
4655: end if;
4656: --
4657: if g_debug then
4658: hr_utility.set_location(l_proc, 20);
4659: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4660: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4661: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4662:
4663: end if;
4656: --
4657: if g_debug then
4658: hr_utility.set_location(l_proc, 20);
4659: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4660: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4661: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4662:
4663: end if;
4664: --
4657: if g_debug then
4658: hr_utility.set_location(l_proc, 20);
4659: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4660: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4661: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4662:
4663: end if;
4664: --
4665: -- Process Logic
4679: close c_old_rslt ;
4680: if l_old_rslt.per_in_ler_id <> p_per_in_ler_id
4681: and p_effective_date=l_old_rslt.effective_start_date then
4682:
4683: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4684: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4685: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4686: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4687: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4680: if l_old_rslt.per_in_ler_id <> p_per_in_ler_id
4681: and p_effective_date=l_old_rslt.effective_start_date then
4682:
4683: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4684: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4685: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4686: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4687: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4688:
4681: and p_effective_date=l_old_rslt.effective_start_date then
4682:
4683: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4684: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4685: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4686: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4687: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4688:
4689:
4682:
4683: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4684: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4685: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4686: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4687: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4688:
4689:
4690: --- if the update called to reverse the stored row from
4683: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4684: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4685: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4686: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4687: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4688:
4689:
4690: --- if the update called to reverse the stored row from
4691: --- from backup table dont insert to backup table
4699: l_old_rslt.ENRT_CVG_THRU_DT ,
4700: l_old_rslt.PRTT_ENRT_RSLT_STAT_CD ) ;
4701: fetch c_bckup_tbl_restore into l_dummy ;
4702: if c_bckup_tbl_restore%notfound then
4703: hr_utility.set_location(' backup row not found ' || p_prtt_enrt_rslt_id , 99 );
4704:
4705: insert into BEN_LE_CLSN_N_RSTR (
4706: BKUP_TBL_TYP_CD,
4707: COMP_LVL_CD,
4963: -- End of API User Hook for the before hook of update_PRTT_ENRT_RESULT
4964: --
4965: end;
4966: --
4967: hr_utility.set_location ( ' eff date ' || p_effective_date, 99 );
4968: hr_utility.set_location ( ' OVN ' || l_object_version_number, 99 );
4969: ben_pen_upd.upd
4970: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4971: ,p_effective_start_date => l_effective_start_date
4964: --
4965: end;
4966: --
4967: hr_utility.set_location ( ' eff date ' || p_effective_date, 99 );
4968: hr_utility.set_location ( ' OVN ' || l_object_version_number, 99 );
4969: ben_pen_upd.upd
4970: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4971: ,p_effective_start_date => l_effective_start_date
4972: ,p_effective_end_date => l_effective_end_date
5136: --
5137: end;
5138: --
5139: if g_debug then
5140: hr_utility.set_location(l_proc, 60);
5141: end if;
5142: --
5143: -- When in validation only mode raise the Validate_Enabled exception
5144: --
5152: p_effective_start_date := l_effective_start_date;
5153: p_effective_end_date := l_effective_end_date;
5154: --
5155: if g_debug then
5156: hr_utility.set_location(' Leaving:'||l_proc, 70);
5157: end if;
5158: --
5159: exception
5160: --
5169: -- (Any key or derived arguments must be set to null
5170: -- when validation only mode is being used.)
5171: --
5172: if g_debug then
5173: hr_utility.set_location(' Leaving:'||l_proc, 80);
5174: end if;
5175: --
5176: when others then
5177: --
5207: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
5208: --
5209: begin
5210: --
5211: g_debug := hr_utility.debug_enabled;
5212: if g_debug then
5213: l_proc := g_package||'delete_PRTT_ENRT_RESULT';
5214: hr_utility.set_location('Entering:'|| l_proc, 10);
5215: end if;
5210: --
5211: g_debug := hr_utility.debug_enabled;
5212: if g_debug then
5213: l_proc := g_package||'delete_PRTT_ENRT_RESULT';
5214: hr_utility.set_location('Entering:'|| l_proc, 10);
5215: end if;
5216: g_multi_rows_validate := p_multi_row_validate;
5217: --
5218: -- Issue a savepoint if operating in validation only mode
5219: --
5220: savepoint delete_PRTT_ENRT_RESULT;
5221: --
5222: if g_debug then
5223: hr_utility.set_location(l_proc, 20);
5224: end if;
5225: --
5226: -- Process Logic
5227: --
5284: --
5285: end;
5286: --
5287: if g_debug then
5288: hr_utility.set_location(l_proc, 60);
5289: end if;
5290: --
5291: -- When in validation only mode raise the Validate_Enabled exception
5292: --
5298: p_effective_end_date := l_effective_end_date;
5299: p_object_version_number := l_object_version_number;
5300:
5301: if g_debug then
5302: hr_utility.set_location(' Leaving:'||l_proc, 70);
5303: end if;
5304: --
5305: exception
5306: --
5317: --
5318: p_effective_start_date := null;
5319: p_effective_end_date := null;
5320: if g_debug then
5321: hr_utility.set_location(' Leaving:'||l_proc, 80);
5322: end if;
5323: --
5324: when others then
5325: --
5362: l_cvg_end_cd varchar2(30);
5363: l_cvg_end_rl number(15);
5364: l_step integer;
5365: begin
5366: g_debug := hr_utility.debug_enabled;
5367: if g_debug then
5368: l_proc := g_package||'calc_dpnt_cvg_dt';
5369: hr_utility.set_location(' Entering:'||l_proc,10);
5370: end if;
5365: begin
5366: g_debug := hr_utility.debug_enabled;
5367: if g_debug then
5368: l_proc := g_package||'calc_dpnt_cvg_dt';
5369: hr_utility.set_location(' Entering:'||l_proc,10);
5370: end if;
5371: l_step := 10;
5372: if (p_elig_per_elctbl_chc_id is not NULL) then
5373: determine_dpnt_cvg_dt_cd
5379: ,p_cvg_end_cd => l_cvg_end_cd
5380: ,p_cvg_end_rl => l_cvg_end_rl
5381: );
5382: if g_debug then
5383: hr_utility.set_location('dpnt_end_cd' || l_cvg_end_cd,15);
5384: hr_utility.set_location('dpnt_strt_cd'|| l_cvg_strt_cd,15);
5385: end if;
5386: if (p_calc_strt_dt and l_cvg_strt_cd is not NULL) then
5387: l_step := 20;
5380: ,p_cvg_end_rl => l_cvg_end_rl
5381: );
5382: if g_debug then
5383: hr_utility.set_location('dpnt_end_cd' || l_cvg_end_cd,15);
5384: hr_utility.set_location('dpnt_strt_cd'|| l_cvg_strt_cd,15);
5385: end if;
5386: if (p_calc_strt_dt and l_cvg_strt_cd is not NULL) then
5387: l_step := 20;
5388: ben_determine_date.main
5463: l_step := 70;
5464: p_returned_end_dt := l_enrt_cvg_end_dt;
5465: p_returned_strt_dt := l_cvg_strt_dt;
5466: if g_debug then
5467: hr_utility.set_location(' Leaving:'||l_proc, 70);
5468: end if;
5469: exception
5470: when others then
5471: rpt_error(p_proc => l_proc, p_step => l_step);
5539: l_chg c_ler_chg_dep%rowtype;
5540: l_env_rec ben_env_object.g_global_env_rec_type;
5541: --
5542: begin
5543: g_debug := hr_utility.debug_enabled;
5544: if g_debug then
5545: l_proc := 'determine_dpnt_cvg_dt_cd';
5546: hr_utility.set_location ('Entering '||l_proc,10);
5547: end if;
5542: begin
5543: g_debug := hr_utility.debug_enabled;
5544: if g_debug then
5545: l_proc := 'determine_dpnt_cvg_dt_cd';
5546: hr_utility.set_location ('Entering '||l_proc,10);
5547: end if;
5548: l_step := 10;
5549: if fnd_global.conc_request_id in (0,-1) then
5550: --
5568: if c_epe%notfound then
5569: close c_epe;
5570: rpt_error(p_proc => l_proc, p_step => l_step);
5571: if g_debug then
5572: hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND id:'||
5573: to_char(p_elig_per_elctbl_chc_id), 15);
5574: end if;
5575: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
5576: fnd_message.set_token('ID', to_char(p_elig_per_elctbl_chc_id) );
5585: l_epe.ptip_id := p_ptip_id;
5586: l_epe.ler_id := p_ler_id;
5587: end if;
5588: if g_debug then
5589: hr_utility.set_location ('Determining designation level '||l_proc,30);
5590: end if;
5591: l_step := 20;
5592: --
5593: -- If program Id is specified, then use program Id to retreive dependent
5964: l_pcp_effective_date date;
5965:
5966: --
5967: begin
5968: g_debug := hr_utility.debug_enabled;
5969: if g_debug then
5970: l_proc := g_package||'void_enrollment';
5971: hr_utility.set_location('Entering:'|| l_proc, 10);
5972: end if;
5967: begin
5968: g_debug := hr_utility.debug_enabled;
5969: if g_debug then
5970: l_proc := g_package||'void_enrollment';
5971: hr_utility.set_location('Entering:'|| l_proc, 10);
5972: end if;
5973: --
5974: -- Issue a savepoint if operating in validation only mode
5975: --
6014: l_pen.object_version_number := p_object_version_number;
6015: l_pen.enrt_bnft_id := p_enrt_bnft_id; */
6016: --
6017: if g_debug then
6018: hr_utility.set_location('Rate Non recurring',3459);
6019: end if;
6020: for l_prv in c_prv3 (p_per_in_ler_id) loop
6021: --check whether rate is non-recurring
6022: open c_abr(l_prv.acty_base_rt_id);
6030: exit;
6031: end if;
6032:
6033: if g_debug then
6034: hr_utility.set_location('delete prtt',3459);
6035: end if;
6036: update ben_enrt_rt
6037: set prtt_rt_val_id = null
6038: where enrt_rt_id = l_prv.enrt_rt_id;
6052: l_step := 14;
6053: --
6054: -- Update rt_end_dt in prtt_rate_val table
6055: --
6056: hr_utility.set_location(' c_prv EPE'||l_pen.elig_per_elctbl_chc_id,10);
6057: hr_utility.set_location(' c_prv ENB'||l_pen.enrt_bnft_id,10);
6058: hr_utility.set_location(' c_prv PEN'||p_prtt_enrt_rslt_id,10);
6059: --
6060: For l_prv in c_prv loop
6053: --
6054: -- Update rt_end_dt in prtt_rate_val table
6055: --
6056: hr_utility.set_location(' c_prv EPE'||l_pen.elig_per_elctbl_chc_id,10);
6057: hr_utility.set_location(' c_prv ENB'||l_pen.enrt_bnft_id,10);
6058: hr_utility.set_location(' c_prv PEN'||p_prtt_enrt_rslt_id,10);
6059: --
6060: For l_prv in c_prv loop
6061: --
6054: -- Update rt_end_dt in prtt_rate_val table
6055: --
6056: hr_utility.set_location(' c_prv EPE'||l_pen.elig_per_elctbl_chc_id,10);
6057: hr_utility.set_location(' c_prv ENB'||l_pen.enrt_bnft_id,10);
6058: hr_utility.set_location(' c_prv PEN'||p_prtt_enrt_rslt_id,10);
6059: --
6060: For l_prv in c_prv loop
6061: --
6062: hr_utility.set_location(' l_prv PRV'||l_prv.prtt_rt_val_id,10);
6058: hr_utility.set_location(' c_prv PEN'||p_prtt_enrt_rslt_id,10);
6059: --
6060: For l_prv in c_prv loop
6061: --
6062: hr_utility.set_location(' l_prv PRV'||l_prv.prtt_rt_val_id,10);
6063: --
6064: ben_prtt_rt_val_api.update_prtt_rt_val
6065: (P_VALIDATE => FALSE
6066: ,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
6082: -- Set End-date to coverage-end-date.
6083: --
6084: for l_pcp in c_pcp loop
6085: --
6086: hr_utility.set_location('Delete prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id,15);
6087: --
6088: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
6089: (P_VALIDATE => FALSE
6090: ,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
6101: -- Get future PCP records if any and zap - delete all of them.
6102: --
6103: for l_pcp_future in c_pcp_future loop
6104: --
6105: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
6106: --
6107: l_pcp_effective_date := l_pcp_future.effective_start_date ;
6108: --
6109: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
6239: );
6240: end if;
6241:
6242: if g_debug then
6243: hr_utility.set_location(l_proc, 60);
6244: end if;
6245: --
6246: -- When in validation only mode raise the Validate_Enabled exception
6247: --
6249: raise hr_api.validate_enabled;
6250: end if;
6251: --
6252: if g_debug then
6253: hr_utility.set_location(' Leaving:'||l_proc, 70);
6254: end if;
6255: --
6256: Exception
6257: --
6267: -- (Any key or derived arguments must be set to null
6268: -- when validation only mode is being used.)
6269: --
6270: if g_debug then
6271: hr_utility.set_location(' Leaving:'||l_proc, 80);
6272: end if;
6273: when others then
6274: --
6275: -- A validation or unexpected error has occured
7396: -- If the rate was for an enrollment that was previously
7397: -- suspended and the prtt_rt_val rt start date has been updated
7398: -- in the unsuspend process, re-instate the original rt start date.
7399: --
7400: hr_utility.set_location('l_prv.prtt_rt_val_id:'|| l_prv.prtt_rt_val_id, 10);
7401: open c_enrt_rt(l_prv.prtt_rt_val_id);
7402: fetch c_enrt_rt into l_enrt_rt;
7403: if c_enrt_rt%found then
7404: close c_enrt_rt;
7401: open c_enrt_rt(l_prv.prtt_rt_val_id);
7402: fetch c_enrt_rt into l_enrt_rt;
7403: if c_enrt_rt%found then
7404: close c_enrt_rt;
7405: hr_utility.set_location('l_enrt_rt.elig_per_elctbl_chc_id '|| l_enrt_rt.enb_per_elctbl_chc_id, 10);
7406: --
7407: ben_determine_date.rate_and_coverage_dates
7408: (p_which_dates_cd => 'R'
7409: ,p_business_group_id => p_business_group_id
7423: ,p_rt_end_dt_rl => l_dummy_number
7424: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
7425: ,p_effective_date => p_effective_date
7426: );
7427: hr_utility.set_location('l_rt_strt_dt:'|| l_rt_strt_dt, 10);
7428: hr_utility.set_location('l_prv.rt_strt_dt:'|| l_prv.rt_strt_dt, 10);
7429: --
7430: if l_rt_strt_dt <> l_prv.rt_strt_dt then
7431: ben_enrollment_rate_api.update_enrollment_rate
7424: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
7425: ,p_effective_date => p_effective_date
7426: );
7427: hr_utility.set_location('l_rt_strt_dt:'|| l_rt_strt_dt, 10);
7428: hr_utility.set_location('l_prv.rt_strt_dt:'|| l_prv.rt_strt_dt, 10);
7429: --
7430: if l_rt_strt_dt <> l_prv.rt_strt_dt then
7431: ben_enrollment_rate_api.update_enrollment_rate
7432: (p_enrt_rt_id => l_enrt_rt.enrt_rt_id
7460:
7461:
7462: --
7463: begin
7464: g_debug := hr_utility.debug_enabled;
7465:
7466: if g_debug then
7467: l_proc := g_package||'delete_enrollment';
7468: hr_utility.set_location('Entering:'|| l_proc, 10);
7464: g_debug := hr_utility.debug_enabled;
7465:
7466: if g_debug then
7467: l_proc := g_package||'delete_enrollment';
7468: hr_utility.set_location('Entering:'|| l_proc, 10);
7469: hr_utility.set_location('p_prtt_enrt_rslt_id'||
7470: to_char(p_prtt_enrt_rslt_id),10);
7471: end if;
7472:
7465:
7466: if g_debug then
7467: l_proc := g_package||'delete_enrollment';
7468: hr_utility.set_location('Entering:'|| l_proc, 10);
7469: hr_utility.set_location('p_prtt_enrt_rslt_id'||
7470: to_char(p_prtt_enrt_rslt_id),10);
7471: end if;
7472:
7473: /* Added for Bug 7561395*/
7496: --bug#3568529
7497: -- ben_env_object.get(p_rec => l_env_rec);
7498: if l_env_rec.benefit_action_id is null then
7499: --
7500: hr_utility.set_location('Intialise env object',11);
7501: ben_env_object.init(p_business_group_id => p_business_group_id,
7502: p_effective_date => p_effective_date,
7503: p_thread_id => 1,
7504: p_chunk_size => 1,
7700: ben_manage_life_events.g_fonm_cvg_strt_dt := null;
7701: ben_manage_life_events.g_fonm_rt_strt_dt := null;
7702: end if ;
7703: end if ;
7704: hr_utility.set_location (' FONM ' || ben_manage_life_events.fonm , 99 ) ;
7705: hr_utility.set_location (' FONM CVG ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 99 ) ;
7706: -- eof FONM
7707:
7708: ben_determine_date.rate_and_coverage_dates
7701: ben_manage_life_events.g_fonm_rt_strt_dt := null;
7702: end if ;
7703: end if ;
7704: hr_utility.set_location (' FONM ' || ben_manage_life_events.fonm , 99 ) ;
7705: hr_utility.set_location (' FONM CVG ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 99 ) ;
7706: -- eof FONM
7707:
7708: ben_determine_date.rate_and_coverage_dates
7709: (p_which_dates_cd => 'C'
7825: --
7826: --elsif (p_effective_date < nvl(l_pen.erlst_deenrt_dt,hr_api.g_sot) and
7827: -- on the erlst_deenrt_dt then deenrt is allowd so -1 is compared
7828: if g_debug then
7829: hr_utility.set_location('cvg end dt ' || l_enrt_cvg_end_dt , 7086);
7830: hr_utility.set_location('denrt ' || l_pen.erlst_deenrt_dt , 7086);
7831: end if;
7832: elsif (l_enrt_cvg_end_dt < nvl(l_pen.erlst_deenrt_dt,hr_api.g_sot)-1 and
7833: l_pen.sspndd_flag = 'N' /*and p_effective_date > l_pen.enrt_cvg_strt_dt */) then
7826: --elsif (p_effective_date < nvl(l_pen.erlst_deenrt_dt,hr_api.g_sot) and
7827: -- on the erlst_deenrt_dt then deenrt is allowd so -1 is compared
7828: if g_debug then
7829: hr_utility.set_location('cvg end dt ' || l_enrt_cvg_end_dt , 7086);
7830: hr_utility.set_location('denrt ' || l_pen.erlst_deenrt_dt , 7086);
7831: end if;
7832: elsif (l_enrt_cvg_end_dt < nvl(l_pen.erlst_deenrt_dt,hr_api.g_sot)-1 and
7833: l_pen.sspndd_flag = 'N' /*and p_effective_date > l_pen.enrt_cvg_strt_dt */) then
7834: --
7881: end if ;
7882: close c_rslt_ptip ;
7883: elsif l_level = 'PL' then
7884: if g_debug then
7885: hr_utility.set_location('in pl level' , 161);
7886: end if;
7887: open c_rslt_pl(l_pen.person_id ,
7888: l_pen.pgm_id ,
7889: l_pen.pl_id ,
7897: end if ;
7898: close c_rslt_pl ;
7899: else -- OPT,OIPL
7900: if g_debug then
7901: hr_utility.set_location('per in ler ' || l_pen.per_in_ler_id,161);
7902: hr_utility.set_location('start date ' || l_pen.effective_start_date,161);
7903: hr_utility.set_location('in opt level' , 161);
7904: end if;
7905: open c_rslt_opt ( l_pen.person_id ,
7898: close c_rslt_pl ;
7899: else -- OPT,OIPL
7900: if g_debug then
7901: hr_utility.set_location('per in ler ' || l_pen.per_in_ler_id,161);
7902: hr_utility.set_location('start date ' || l_pen.effective_start_date,161);
7903: hr_utility.set_location('in opt level' , 161);
7904: end if;
7905: open c_rslt_opt ( l_pen.person_id ,
7906: l_pen.pgm_id ,
7899: else -- OPT,OIPL
7900: if g_debug then
7901: hr_utility.set_location('per in ler ' || l_pen.per_in_ler_id,161);
7902: hr_utility.set_location('start date ' || l_pen.effective_start_date,161);
7903: hr_utility.set_location('in opt level' , 161);
7904: end if;
7905: open c_rslt_opt ( l_pen.person_id ,
7906: l_pen.pgm_id ,
7907: l_pen.pl_id ,
7916: end if ;
7917: close c_rslt_opt ;
7918: end if;
7919: if g_debug then
7920: hr_utility.set_location(' l_new_enrollment ' || l_new_enrollment , 161 );
7921: end if;
7922: if (l_new_enrollment <> 'Y' or l_pen.per_in_ler_stat_cd = 'PROCD') then
7923: --
7924: -- Got level now see if other enrt in level exists
7937: ,p_level =>l_level
7938: ,p_pen_rec =>l_other_pen_rec
7939: );
7940: if g_debug then
7941: hr_utility.set_location('result ' || l_other_pen_rec.prtt_enrt_rslt_id ,161);
7942: end if;
7943: if l_other_pen_rec.prtt_enrt_rslt_id is null/* or l_crntly_enrd_flag='Y'*/ then -------7458990
7944: hr_utility.set_location('entering',9999);
7945: fnd_message.set_name('BEN','BEN_91902_ENRT_NOT_ALWD_DELETE');
7940: if g_debug then
7941: hr_utility.set_location('result ' || l_other_pen_rec.prtt_enrt_rslt_id ,161);
7942: end if;
7943: if l_other_pen_rec.prtt_enrt_rslt_id is null/* or l_crntly_enrd_flag='Y'*/ then -------7458990
7944: hr_utility.set_location('entering',9999);
7945: fnd_message.set_name('BEN','BEN_91902_ENRT_NOT_ALWD_DELETE');
7946: fnd_message.set_token('PLANNAMEANDOPTIONNAME',l_pen.pl_name || ' '||l_pen.opt_name );
7947: fnd_message.set_token('EARLIESTDEENROLLMENTDATE',fnd_date.date_to_chardate(l_pen.erlst_deenrt_dt));
7948: -- fnd_message.raise_error;
7980: -- always update the result by updating it with that date irrespective
7981: -- of when the coverage starts and when the elections are made.
7982: --
7983:
7984: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7985: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7986: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7987: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7981: -- of when the coverage starts and when the elections are made.
7982: --
7983:
7984: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7985: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7986: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7987: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7989: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7982: --
7983:
7984: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7985: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7986: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7987: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7989: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7990: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7983:
7984: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7985: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7986: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7987: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7989: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7990: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7991: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7984: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7985: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7986: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7987: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7989: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7990: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7991: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7992: hr_utility.set_location('p_mode '|| p_mode , 100);
7985: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7986: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7987: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7989: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7990: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7991: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7992: hr_utility.set_location('p_mode '|| p_mode , 100);
7993:
7986: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7987: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7989: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7990: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7991: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7992: hr_utility.set_location('p_mode '|| p_mode , 100);
7993:
7994: -- future enrollment is deleted
7987: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7989: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7990: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7991: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7992: hr_utility.set_location('p_mode '|| p_mode , 100);
7993:
7994: -- future enrollment is deleted
7995: -- copy the data into backup table
7988: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7989: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7990: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7991: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7992: hr_utility.set_location('p_mode '|| p_mode , 100);
7993:
7994: -- future enrollment is deleted
7995: -- copy the data into backup table
7996:
8045: , l_pen.effective_start_date) ; -- 7197868
8046: fetch c_old_corr_pen into l_corr_pil_id ;
8047: if c_old_corr_pen%found then
8048: l_pil_id := l_corr_pil_id ;
8049: hr_utility.set_location('not found corrected '|| l_proc , 99 ) ;
8050: end if ;
8051: close c_old_corr_pen ;
8052: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
8053: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
8048: l_pil_id := l_corr_pil_id ;
8049: hr_utility.set_location('not found corrected '|| l_proc , 99 ) ;
8050: end if ;
8051: close c_old_corr_pen ;
8052: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
8053: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
8054: hr_utility.set_location('l_pil_id '|| l_pil_id , 100);
8055: hr_utility.set_location('p_mode '|| p_mode , 100);
8056: -- Case 1: Coverage started, Coverage Elected and delete in same day.
8049: hr_utility.set_location('not found corrected '|| l_proc , 99 ) ;
8050: end if ;
8051: close c_old_corr_pen ;
8052: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
8053: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
8054: hr_utility.set_location('l_pil_id '|| l_pil_id , 100);
8055: hr_utility.set_location('p_mode '|| p_mode , 100);
8056: -- Case 1: Coverage started, Coverage Elected and delete in same day.
8057: -- * void result and its rates.
8050: end if ;
8051: close c_old_corr_pen ;
8052: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
8053: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
8054: hr_utility.set_location('l_pil_id '|| l_pil_id , 100);
8055: hr_utility.set_location('p_mode '|| p_mode , 100);
8056: -- Case 1: Coverage started, Coverage Elected and delete in same day.
8057: -- * void result and its rates.
8058: if (l_date = p_effective_date) and
8051: close c_old_corr_pen ;
8052: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
8053: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
8054: hr_utility.set_location('l_pil_id '|| l_pil_id , 100);
8055: hr_utility.set_location('p_mode '|| p_mode , 100);
8056: -- Case 1: Coverage started, Coverage Elected and delete in same day.
8057: -- * void result and its rates.
8058: if (l_date = p_effective_date) and
8059: ((p_per_in_ler_id is not null and p_per_in_ler_id = l_pil_id) or
8059: ((p_per_in_ler_id is not null and p_per_in_ler_id = l_pil_id) or
8060: p_per_in_ler_id is null) and
8061: (p_mode is null or p_mode <> 'CVG_END_DATE_ENTERABLE')then
8062: if g_debug then
8063: hr_utility.set_location('p_effective_date='||p_effective_date,19);
8064: end if;
8065: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
8066: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
8067: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
8061: (p_mode is null or p_mode <> 'CVG_END_DATE_ENTERABLE')then
8062: if g_debug then
8063: hr_utility.set_location('p_effective_date='||p_effective_date,19);
8064: end if;
8065: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
8066: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
8067: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
8068: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
8069: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
8062: if g_debug then
8063: hr_utility.set_location('p_effective_date='||p_effective_date,19);
8064: end if;
8065: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
8066: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
8067: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
8068: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
8069: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
8070: l_step := 21;
8063: hr_utility.set_location('p_effective_date='||p_effective_date,19);
8064: end if;
8065: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
8066: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
8067: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
8068: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
8069: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
8070: l_step := 21;
8071: void_enrollment
8064: end if;
8065: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
8066: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
8067: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
8068: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
8069: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
8070: l_step := 21;
8071: void_enrollment
8072: (p_validate => p_validate
8065: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
8066: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
8067: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
8068: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
8069: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
8070: l_step := 21;
8071: void_enrollment
8072: (p_validate => p_validate
8073: ,p_per_in_ler_id => p_per_in_ler_id
8092: ,p_validate => FALSE
8093: ,p_datetrack_mode => hr_api.g_delete
8094: );
8095: end if;
8096: hr_utility.set_location('l_benmngle_parm_rec.mode_cd '|| l_benmngle_parm_rec.mode_cd , 100);
8097: hr_utility.set_location('l_env_rec.mode_cd '|| l_env_rec.mode_cd , 100);
8098: hr_utility.set_location('p_source '|| p_source , 100);
8099: -- 3398353
8100: /* In case of Unrestricted Enrollment running on the same-day,
8093: ,p_datetrack_mode => hr_api.g_delete
8094: );
8095: end if;
8096: hr_utility.set_location('l_benmngle_parm_rec.mode_cd '|| l_benmngle_parm_rec.mode_cd , 100);
8097: hr_utility.set_location('l_env_rec.mode_cd '|| l_env_rec.mode_cd , 100);
8098: hr_utility.set_location('p_source '|| p_source , 100);
8099: -- 3398353
8100: /* In case of Unrestricted Enrollment running on the same-day,
8101: the dependent designation records (ben_elig_cvrd_dpnt_f) also
8094: );
8095: end if;
8096: hr_utility.set_location('l_benmngle_parm_rec.mode_cd '|| l_benmngle_parm_rec.mode_cd , 100);
8097: hr_utility.set_location('l_env_rec.mode_cd '|| l_env_rec.mode_cd , 100);
8098: hr_utility.set_location('p_source '|| p_source , 100);
8099: -- 3398353
8100: /* In case of Unrestricted Enrollment running on the same-day,
8101: the dependent designation records (ben_elig_cvrd_dpnt_f) also
8102: needs to be end-dated / voided, when the enrollment is voided.
8109: --
8110: -- if (l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' ) or ( p_source = 'benelinf' ) then
8111: --
8112: if g_debug then
8113: hr_utility.set_location('Updating Dependent Records. l_enrt_cvg_end_dt: '||l_enrt_cvg_end_dt, 100);
8114: end if;
8115: --
8116: l_step := 30;
8117: --
8115: --
8116: l_step := 30;
8117: --
8118: if g_debug then
8119: hr_utility.set_location('calc_dpnt_cvg_dt ', 100);
8120: end if;
8121: --
8122: calc_dpnt_cvg_dt
8123: (p_calc_end_dt => TRUE
8151: --
8152: l_step := 50;
8153: --
8154: if g_debug then
8155: hr_utility.set_location('unhook_dpnt cvg-end-date: '||l_dpnt_cvg_thru_dt, 100);
8156: end if;
8157: --
8158: unhook_dpnt
8159: (p_validate => FALSE
8166: ,p_called_from => p_source
8167: );
8168: --
8169: if g_debug then
8170: hr_utility.set_location('unhook_bnf cvg-end-date: '||l_dpnt_cvg_thru_dt, 100);
8171: end if;
8172: --
8173: unhook_bnf
8174: (p_validate => FALSE
8182: -- end if;-- IF-UNRESTRICTED-OR-SCHEDULED
8183: ---
8184:
8185: /*Ended code for Bug 7561395*/
8186: hr_utility.set_location('IF-UNRESTRICTED-OR-SCHEDULED ', 500);
8187: hr_utility.set_location('Unhook dependent rows ', 500);
8188: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 502);
8189: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 502);
8190: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 502);
8183: ---
8184:
8185: /*Ended code for Bug 7561395*/
8186: hr_utility.set_location('IF-UNRESTRICTED-OR-SCHEDULED ', 500);
8187: hr_utility.set_location('Unhook dependent rows ', 500);
8188: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 502);
8189: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 502);
8190: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 502);
8191: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 502);
8184:
8185: /*Ended code for Bug 7561395*/
8186: hr_utility.set_location('IF-UNRESTRICTED-OR-SCHEDULED ', 500);
8187: hr_utility.set_location('Unhook dependent rows ', 500);
8188: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 502);
8189: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 502);
8190: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 502);
8191: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 502);
8192:
8185: /*Ended code for Bug 7561395*/
8186: hr_utility.set_location('IF-UNRESTRICTED-OR-SCHEDULED ', 500);
8187: hr_utility.set_location('Unhook dependent rows ', 500);
8188: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 502);
8189: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 502);
8190: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 502);
8191: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 502);
8192:
8193: /* Bug 8669907:
8186: hr_utility.set_location('IF-UNRESTRICTED-OR-SCHEDULED ', 500);
8187: hr_utility.set_location('Unhook dependent rows ', 500);
8188: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 502);
8189: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 502);
8190: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 502);
8191: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 502);
8192:
8193: /* Bug 8669907:
8194: if(action item exists for the pen_id ) do not create
8187: hr_utility.set_location('Unhook dependent rows ', 500);
8188: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 502);
8189: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 502);
8190: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 502);
8191: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 502);
8192:
8193: /* Bug 8669907:
8194: if(action item exists for the pen_id ) do not create
8195: else
8200:
8201: open c_chk_act_item_exits(g_new_prtt_enrt_rslt_id);
8202: fetch c_chk_act_item_exits into l_dummy1;
8203: if(c_chk_act_item_exits%found) then
8204: hr_utility.set_location('No Create 1',500);
8205: close c_chk_act_item_exits;
8206: l_create_flag := 'N';
8207: else
8208: close c_chk_act_item_exits;
8208: close c_chk_act_item_exits;
8209: open c_chk_same_comp_obj(g_new_prtt_enrt_rslt_id,p_prtt_enrt_rslt_id,p_per_in_ler_id);
8210: fetch c_chk_same_comp_obj into l_create_flag;
8211: if(c_chk_same_comp_obj%notfound) then
8212: hr_utility.set_location('No Create 2',500);
8213: close c_chk_same_comp_obj;
8214: l_create_flag := 'N';
8215: else
8216: close c_chk_same_comp_obj;
8219: close c_bnft_amt;
8220: if(l_bnft.entr_val_at_enrt_flag = 'N') then
8221: if(l_bnft.cvg_mlt_cd = 'FLRNG') then
8222: if(l_bnft.bnft_amt > l_bnft.mx_val) then
8223: hr_utility.set_location('FLRNG Create ',500);
8224: l_create_flag := 'Y';
8225: else
8226: hr_utility.set_location('FLRNG No Create',500);
8227: l_create_flag := 'N';
8222: if(l_bnft.bnft_amt > l_bnft.mx_val) then
8223: hr_utility.set_location('FLRNG Create ',500);
8224: l_create_flag := 'Y';
8225: else
8226: hr_utility.set_location('FLRNG No Create',500);
8227: l_create_flag := 'N';
8228: end if;
8229: else
8230: hr_utility.set_location('Create 1',500);
8226: hr_utility.set_location('FLRNG No Create',500);
8227: l_create_flag := 'N';
8228: end if;
8229: else
8230: hr_utility.set_location('Create 1',500);
8231: l_create_flag := 'Y';
8232: end if;
8233: else
8234: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
8231: l_create_flag := 'Y';
8232: end if;
8233: else
8234: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
8235: hr_utility.set_location('Create 2',500);
8236: l_create_flag := 'Y';
8237: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
8238: hr_utility.set_location('No Create 4',500);
8239: l_create_flag := 'N';
8234: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
8235: hr_utility.set_location('Create 2',500);
8236: l_create_flag := 'Y';
8237: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
8238: hr_utility.set_location('No Create 4',500);
8239: l_create_flag := 'N';
8240: else
8241: hr_utility.set_location('Create 3',500);
8242: l_create_flag := 'Y';
8237: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
8238: hr_utility.set_location('No Create 4',500);
8239: l_create_flag := 'N';
8240: else
8241: hr_utility.set_location('Create 3',500);
8242: l_create_flag := 'Y';
8243: end if;
8244: end if;
8245: end if;
8243: end if;
8244: end if;
8245: end if;
8246: end if;
8247: hr_utility.set_location('l_create_flag '||l_create_flag,500);
8248:
8249: open c_chk_same_event(g_new_prtt_enrt_rslt_id);
8250: fetch c_chk_same_event into l_nper_in_ler_id ;
8251: close c_chk_same_event;
8255: open c_chk_act_item_exits(p_prtt_enrt_rslt_id);
8256: fetch c_chk_act_item_exits into l_dummy1;
8257: if(c_chk_act_item_exits%found) then
8258: l_act_flag := true;
8259: hr_utility.set_location('In loop2 false',500);
8260: else
8261: l_act_flag := false;
8262: hr_utility.set_location('In loop2 true',500);
8263: end if;
8258: l_act_flag := true;
8259: hr_utility.set_location('In loop2 false',500);
8260: else
8261: l_act_flag := false;
8262: hr_utility.set_location('In loop2 true',500);
8263: end if;
8264: close c_chk_act_item_exits;
8265: if(l_nper_in_ler_id = l_oper_in_ler_id and l_act_flag) then
8266:
8265: if(l_nper_in_ler_id = l_oper_in_ler_id and l_act_flag) then
8266:
8267: open c_get_actn_items;
8268: fetch c_get_actn_items BULK COLLECT into l_act_items;
8269: hr_utility.set_location('In loop1',500);
8270: if(l_act_items.count > 0) then
8271: l_act_flag := true;
8272: hr_utility.set_location('In loop10 '||l_act_items.count,500);
8273: else
8268: fetch c_get_actn_items BULK COLLECT into l_act_items;
8269: hr_utility.set_location('In loop1',500);
8270: if(l_act_items.count > 0) then
8271: l_act_flag := true;
8272: hr_utility.set_location('In loop10 '||l_act_items.count,500);
8273: else
8274: l_act_flag := false;
8275: hr_utility.set_location('In loop11',500);
8276: end if;
8271: l_act_flag := true;
8272: hr_utility.set_location('In loop10 '||l_act_items.count,500);
8273: else
8274: l_act_flag := false;
8275: hr_utility.set_location('In loop11',500);
8276: end if;
8277: close c_get_actn_items;
8278:
8279:
8280: open c_get_ctfn;
8281: fetch c_get_ctfn BULK COLLECT into l_ctfn;
8282: if(l_ctfn.count > 0) then
8283: l_ctfn_flag := true;
8284: hr_utility.set_location('In loop10 '||l_ctfn.count,500);
8285: end if;
8286: close c_get_ctfn;
8287:
8288: hr_utility.set_location('Remving action items',500);
8284: hr_utility.set_location('In loop10 '||l_ctfn.count,500);
8285: end if;
8286: close c_get_ctfn;
8287:
8288: hr_utility.set_location('Remving action items',500);
8289: remove_cert_action_items
8290: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
8291: ,p_effective_date => p_effective_date
8292: ,p_business_group_id => p_business_group_id
8297: ,p_per_in_ler_ended_id=> l_pen.per_in_ler_id
8298: );
8299:
8300: if(l_act_flag and l_create_flag = 'Y' ) then
8301: hr_utility.set_location('creating action items',500);
8302: for i IN l_act_items.FIRST..l_act_items.LAST loop
8303: ben_PRTT_ENRT_ACTN_api.create_PRTT_ENRT_ACTN
8304: (p_validate => false
8305: ,p_effective_date => p_effective_date
8350: ,p_effective_end_date => l_actn_end_date
8351: ,p_gnrt_cm => false -- Bug 9256641 : Do not generate communications
8352: );
8353: end loop;
8354: hr_utility.set_location('created action items',500);
8355: end if;
8356:
8357: hr_utility.set_location('before creating cert',500);
8358: if(l_ctfn_flag and l_create_flag = 'Y' ) then
8353: end loop;
8354: hr_utility.set_location('created action items',500);
8355: end if;
8356:
8357: hr_utility.set_location('before creating cert',500);
8358: if(l_ctfn_flag and l_create_flag = 'Y' ) then
8359: hr_utility.set_location('creating certifications ',500);
8360: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
8361: hr_utility.set_location('looping cert ',500);
8355: end if;
8356:
8357: hr_utility.set_location('before creating cert',500);
8358: if(l_ctfn_flag and l_create_flag = 'Y' ) then
8359: hr_utility.set_location('creating certifications ',500);
8360: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
8361: hr_utility.set_location('looping cert ',500);
8362: ben_PRTT_ENRT_CTFN_PRVDD_api.create_PRTT_ENRT_CTFN_PRVDD
8363: (p_validate => false
8357: hr_utility.set_location('before creating cert',500);
8358: if(l_ctfn_flag and l_create_flag = 'Y' ) then
8359: hr_utility.set_location('creating certifications ',500);
8360: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
8361: hr_utility.set_location('looping cert ',500);
8362: ben_PRTT_ENRT_CTFN_PRVDD_api.create_PRTT_ENRT_CTFN_PRVDD
8363: (p_validate => false
8364: ,p_prtt_enrt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id
8365: ,p_effective_start_date => l_ctfn_start_date
8455: l_rt_end_dt_cd in ('LDPPOEFD','LDPPFEFD','LODBEWM','ODBEWM') ) and
8456: (p_per_in_ler_id <> l_pil_id or l_ler_typ_cd = 'SCHEDDU' or
8457: (p_per_in_ler_id = l_pil_id and l_ler_typ_cd not in ('IREC','GSP','COMP','ABS'))) then
8458: -- Bug 2847110
8459: hr_utility.set_location('c_new_cvg_strt_dt,srav : '||l_new_enrt_cvg_strt_dt,199);
8460: open c_new_cvg_strt_dt(l_pen.pl_typ_id, l_pen.ptip_id,l_pen.pl_id, l_pen.oipl_id,
8461: l_enrt_cvg_strt_dt );
8462: fetch c_new_cvg_strt_dt into l_new_enrt_cvg_strt_dt;
8463: close c_new_cvg_strt_dt;
8483: --
8484: end if;
8485: --
8486: if g_debug then
8487: hr_utility.set_location('c_new_cvg_strt_dt'||l_new_enrt_cvg_strt_dt,199);
8488: hr_utility.set_location('l_enrt_cvg_end_dt'||l_enrt_cvg_end_dt,199);
8489: end if;
8490: --
8491: -- Change Rate Dates to 1 Prior
8484: end if;
8485: --
8486: if g_debug then
8487: hr_utility.set_location('c_new_cvg_strt_dt'||l_new_enrt_cvg_strt_dt,199);
8488: hr_utility.set_location('l_enrt_cvg_end_dt'||l_enrt_cvg_end_dt,199);
8489: end if;
8490: --
8491: -- Change Rate Dates to 1 Prior
8492: If (substr(nvl(l_rt_end_dt_cd, 'X'), 1, 1) = 'W' or
8496: -- Bug: 4642299 Added 'LODBEWM','ODBEWM' too for 1 Prior logic.
8497: then
8498: --
8499: if g_debug then
8500: hr_utility.set_location(' pen api rt strt dt ' || l_rt_strt_dt, 299 );
8501: hr_utility.set_location(' pen api rt end dt ' || l_rt_end_dt, 299 );
8502: end if;
8503: --bug#3260564
8504: if l_new_enrt_cvg_strt_dt is not null then
8497: then
8498: --
8499: if g_debug then
8500: hr_utility.set_location(' pen api rt strt dt ' || l_rt_strt_dt, 299 );
8501: hr_utility.set_location(' pen api rt end dt ' || l_rt_end_dt, 299 );
8502: end if;
8503: --bug#3260564
8504: if l_new_enrt_cvg_strt_dt is not null then
8505: l_rt_end_dt := nvl(l_rt_strt_dt - 1,l_rt_end_dt) ;
8544: fetch c_check_carry_fwd_enrt into l_check_carry_fwd_enrt;
8545: close c_check_carry_fwd_enrt;
8546:
8547: if l_check_carry_fwd_enrt is null then
8548: hr_utility.set_location('Void Rate procedure is called',3455);
8549: void_rate (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
8550: p_business_group_id => p_business_group_id,
8551: p_person_id => l_pen.person_id,
8552: p_per_in_ler_id => p_per_in_ler_id,
8558: l_rt_end_dt_cd in ('LDPPOEFD','LDPPFEFD','LODBEWM','ODBEWM') then ---Bug 7209243
8559: /* open c_get_old_prv;
8560: fetch c_get_old_prv into l_get_old_prv;
8561: if c_get_old_prv%found then
8562: hr_utility.set_location('l_get_old_prv.prtt_rt_val_id :'||l_get_old_prv.prtt_rt_val_id,3455); */
8563: --Bug 9290518
8564: l_old_prv_found := false;
8565: for l_old_prv in c_get_old_prv loop
8566: hr_utility.set_location('l_old_prv.prtt_rt_val_id :'|| l_old_prv.prtt_rt_val_id,3455);
8562: hr_utility.set_location('l_get_old_prv.prtt_rt_val_id :'||l_get_old_prv.prtt_rt_val_id,3455); */
8563: --Bug 9290518
8564: l_old_prv_found := false;
8565: for l_old_prv in c_get_old_prv loop
8566: hr_utility.set_location('l_old_prv.prtt_rt_val_id :'|| l_old_prv.prtt_rt_val_id,3455);
8567: l_old_prv_found := true;
8568: ben_prtt_rt_val_api.update_prtt_rt_val
8569: (P_VALIDATE => FALSE
8570: ,P_PRTT_RT_VAL_ID => l_old_prv.prtt_rt_val_id --l_get_old_prv.prtt_rt_val_id
8578: ,P_EFFECTIVE_DATE => p_effective_date
8579: );
8580: end loop;
8581: ---Bug 9290518
8582: hr_utility.set_location('P_RT_END_DT :'||hr_api.g_eot,3455);
8583: -- else ------------Bug 7209243
8584: if not(l_old_prv_found) then --Bug 9290518
8585: hr_utility.set_location('c_get_old_prv not found',3455);
8586: ---get the new rt start dat to compute the end date
8581: ---Bug 9290518
8582: hr_utility.set_location('P_RT_END_DT :'||hr_api.g_eot,3455);
8583: -- else ------------Bug 7209243
8584: if not(l_old_prv_found) then --Bug 9290518
8585: hr_utility.set_location('c_get_old_prv not found',3455);
8586: ---get the new rt start dat to compute the end date
8587: open c_get_epe(l_pen.pl_typ_id);
8588: fetch c_get_epe into l_get_epe;
8589: if c_get_epe%found then
8617: /* open c_get_old_prv1;
8618: fetch c_get_old_prv1 into l_get_old_prv1;
8619: if c_get_old_prv1%found then */
8620: for l_get_old_prv1 in c_get_old_prv1 loop
8621: hr_utility.set_location('l_get_old_prv1.prtt_rt_val_id :'||l_get_old_prv1.prtt_rt_val_id,3455);
8622: hr_utility.set_location('l_get_old_prv1.ovn :'||l_get_old_prv1.object_version_number,3455.1);
8623: hr_utility.set_location('l_rt_end_dt1 :'||l_rt_end_dt1,3455.1);
8624:
8625: ben_prtt_rt_val_api.update_prtt_rt_val
8618: fetch c_get_old_prv1 into l_get_old_prv1;
8619: if c_get_old_prv1%found then */
8620: for l_get_old_prv1 in c_get_old_prv1 loop
8621: hr_utility.set_location('l_get_old_prv1.prtt_rt_val_id :'||l_get_old_prv1.prtt_rt_val_id,3455);
8622: hr_utility.set_location('l_get_old_prv1.ovn :'||l_get_old_prv1.object_version_number,3455.1);
8623: hr_utility.set_location('l_rt_end_dt1 :'||l_rt_end_dt1,3455.1);
8624:
8625: ben_prtt_rt_val_api.update_prtt_rt_val
8626: (P_VALIDATE => FALSE
8619: if c_get_old_prv1%found then */
8620: for l_get_old_prv1 in c_get_old_prv1 loop
8621: hr_utility.set_location('l_get_old_prv1.prtt_rt_val_id :'||l_get_old_prv1.prtt_rt_val_id,3455);
8622: hr_utility.set_location('l_get_old_prv1.ovn :'||l_get_old_prv1.object_version_number,3455.1);
8623: hr_utility.set_location('l_rt_end_dt1 :'||l_rt_end_dt1,3455.1);
8624:
8625: ben_prtt_rt_val_api.update_prtt_rt_val
8626: (P_VALIDATE => FALSE
8627: ,P_PRTT_RT_VAL_ID => l_get_old_prv1.prtt_rt_val_id
8641: end if;
8642: close c_get_epe; -------------Bug 7209243
8643: end if;
8644: -- close c_get_old_prv; ---Bug 9290518
8645: hr_utility.set_location('P_RT_END_DT not fund :'||hr_api.g_eot,3455);
8646: hr_utility.set_location('p_effective_start_date :'||p_effective_start_date,3455);
8647: hr_utility.set_location('p_effective_date :'||p_effective_date,3455);
8648: end if;
8649: -----Bug 6925893
8642: close c_get_epe; -------------Bug 7209243
8643: end if;
8644: -- close c_get_old_prv; ---Bug 9290518
8645: hr_utility.set_location('P_RT_END_DT not fund :'||hr_api.g_eot,3455);
8646: hr_utility.set_location('p_effective_start_date :'||p_effective_start_date,3455);
8647: hr_utility.set_location('p_effective_date :'||p_effective_date,3455);
8648: end if;
8649: -----Bug 6925893
8650: end if;
8643: end if;
8644: -- close c_get_old_prv; ---Bug 9290518
8645: hr_utility.set_location('P_RT_END_DT not fund :'||hr_api.g_eot,3455);
8646: hr_utility.set_location('p_effective_start_date :'||p_effective_start_date,3455);
8647: hr_utility.set_location('p_effective_date :'||p_effective_date,3455);
8648: end if;
8649: -----Bug 6925893
8650: end if;
8651: end if;
8650: end if;
8651: end if;
8652: --End Bug 5499809
8653:
8654: hr_utility.set_location('P_RT_END_DT not fund1 :'||hr_api.g_eot,3455);
8655: --
8656: l_step := 35;
8657: if g_debug then
8658: hr_utility.set_location(l_proc,3456);
8654: hr_utility.set_location('P_RT_END_DT not fund1 :'||hr_api.g_eot,3455);
8655: --
8656: l_step := 35;
8657: if g_debug then
8658: hr_utility.set_location(l_proc,3456);
8659: end if;
8660: -- rate is having non recurring
8661: if g_debug then
8662: hr_utility.set_location(l_deenrol_dt,3459);
8658: hr_utility.set_location(l_proc,3456);
8659: end if;
8660: -- rate is having non recurring
8661: if g_debug then
8662: hr_utility.set_location(l_deenrol_dt,3459);
8663: end if;
8664: for l_prv in c_prv3 (p_per_in_ler_id) loop
8665: --check whether rate is non-recurring
8666: open c_abr(l_prv.acty_base_rt_id);
8674: else
8675: exit;
8676: end if;
8677: if g_debug then
8678: hr_utility.set_location('delete prtt',3459);
8679: end if;
8680: update ben_enrt_rt
8681: set prtt_rt_val_id = null
8682: where enrt_rt_id = l_prv.enrt_rt_id;
8691: );
8692: end loop;
8693: For l_prv in c_prv2 loop
8694: if g_debug then
8695: hr_utility.set_location(l_proc,3457);
8696: end if;
8697: l_prv_count := l_prv_count + 1;
8698: if (nvl(l_prv.per_in_ler_id, -1) <> nvl(p_per_in_ler_id,-1)) and
8699: l_rt_end_dt < l_prv.rt_strt_dt then
8714: ,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
8715: ,P_EFFECTIVE_DATE => p_effective_date
8716: );
8717: if g_debug then
8718: hr_utility.set_location(l_proc,3458);
8719: end if;
8720: end loop;
8721: --
8722: -- Adjust overlapping rates . Bug 5391554
8721: --
8722: -- Adjust overlapping rates . Bug 5391554
8723: --
8724: -- basu
8725: hr_utility.set_location('p_per_in_ler_id '||p_per_in_ler_id,44333);
8726: --
8727: open c_get_pgm_extra_info(l_pen.pgm_id);
8728: fetch c_get_pgm_extra_info into l_adjust;
8729: if c_get_pgm_extra_info%found then
8730: if l_adjust = 'Y' then
8731: for l_prv5 in c_prv5(l_rt_end_dt) loop
8732: --
8733: if g_debug then
8734: hr_utility.set_location('Adjusting rate '||l_rt_end_dt,111);
8735: end if;
8736: ben_prtt_rt_val_api.update_prtt_rt_val
8737: (P_VALIDATE => FALSE
8738: ,P_PRTT_RT_VAL_ID => l_prv5.prtt_rt_val_id
8755: -- date needs to be updated
8756: -- Bug 2739965
8757: if l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' then
8758: if g_debug then
8759: hr_utility.set_location('ben mngle mode',111);
8760: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
8761: end if;
8762: For l_prv4 in c_prv4 (l_rt_end_dt) loop
8763: if g_debug then
8756: -- Bug 2739965
8757: if l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' then
8758: if g_debug then
8759: hr_utility.set_location('ben mngle mode',111);
8760: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
8761: end if;
8762: For l_prv4 in c_prv4 (l_rt_end_dt) loop
8763: if g_debug then
8764: hr_utility.set_location(l_proc,3459);
8760: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
8761: end if;
8762: For l_prv4 in c_prv4 (l_rt_end_dt) loop
8763: if g_debug then
8764: hr_utility.set_location(l_proc,3459);
8765: end if;
8766: -- 2739965 In case of unrestricted enrollment, this happens only when there are future rates
8767: -- and they got deleted as part of c_prv2 process above. Lets us throw this error
8768: -- here warning the user about the deletion of the future rate.
8796: ,P_OBJECT_VERSION_NUMBER => l_prv4.object_version_number
8797: ,P_EFFECTIVE_DATE => p_effective_date
8798: );
8799: if g_debug then
8800: hr_utility.set_location(l_proc,3460);
8801: end if;
8802: --
8803: end if; /* End Bug 5739530 */
8804: --
8805: end loop;
8806: --
8807: end if;
8808: if g_debug then
8809: hr_utility.set_location(l_proc,3459);
8810: end if;
8811: --
8812: -- 3574168: Remove PCP records
8813: -- Set End-date to coverage-end-date.
8815: l_pcp_effective_date := NVL(l_enrt_cvg_end_dt+1, p_effective_date);
8816: --
8817: for l_pcp in c_pcp(l_pcp_effective_date) loop
8818: --
8819: hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
8820: hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
8821: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
8822: hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
8823: -- Since, deletion automatically sets end-date to 1 day less than effective-date,
8816: --
8817: for l_pcp in c_pcp(l_pcp_effective_date) loop
8818: --
8819: hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
8820: hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
8821: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
8822: hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
8823: -- Since, deletion automatically sets end-date to 1 day less than effective-date,
8824: -- call the delete-api with effective_date = cvg_thru_date+1.
8817: for l_pcp in c_pcp(l_pcp_effective_date) loop
8818: --
8819: hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
8820: hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
8821: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
8822: hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
8823: -- Since, deletion automatically sets end-date to 1 day less than effective-date,
8824: -- call the delete-api with effective_date = cvg_thru_date+1.
8825: --
8818: --
8819: hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
8820: hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
8821: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
8822: hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
8823: -- Since, deletion automatically sets end-date to 1 day less than effective-date,
8824: -- call the delete-api with effective_date = cvg_thru_date+1.
8825: --
8826: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
8841: for l_pcp_future in c_pcp_future(l_pcp_effective_date) loop
8842: --
8843: l_pcp_effective_start_date := l_pcp_future.effective_start_date;
8844: --
8845: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
8846: hr_utility.set_location('PCP ESD: EED '|| l_pcp_future.effective_start_date ||': '||l_pcp_future.effective_end_date, 15);
8847: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
8848: --
8849: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
8842: --
8843: l_pcp_effective_start_date := l_pcp_future.effective_start_date;
8844: --
8845: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
8846: hr_utility.set_location('PCP ESD: EED '|| l_pcp_future.effective_start_date ||': '||l_pcp_future.effective_end_date, 15);
8847: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
8848: --
8849: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
8850: (P_VALIDATE => FALSE
8843: l_pcp_effective_start_date := l_pcp_future.effective_start_date;
8844: --
8845: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
8846: hr_utility.set_location('PCP ESD: EED '|| l_pcp_future.effective_start_date ||': '||l_pcp_future.effective_end_date, 15);
8847: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
8848: --
8849: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
8850: (P_VALIDATE => FALSE
8851: ,P_PRMRY_CARE_PRVDR_ID => l_pcp_future.prmry_care_prvdr_id
8914: ,p_datetrack_mode => p_datetrack_mode
8915: );
8916:
8917: /* Added code for Bug 7561395*/
8918: hr_utility.set_location('Unhook dependent rows ', 500);
8919: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 503);
8920: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 503);
8921: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 503);
8922: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 503);
8915: );
8916:
8917: /* Added code for Bug 7561395*/
8918: hr_utility.set_location('Unhook dependent rows ', 500);
8919: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 503);
8920: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 503);
8921: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 503);
8922: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 503);
8923:
8916:
8917: /* Added code for Bug 7561395*/
8918: hr_utility.set_location('Unhook dependent rows ', 500);
8919: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 503);
8920: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 503);
8921: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 503);
8922: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 503);
8923:
8924: /* Bug 8669907:
8917: /* Added code for Bug 7561395*/
8918: hr_utility.set_location('Unhook dependent rows ', 500);
8919: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 503);
8920: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 503);
8921: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 503);
8922: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 503);
8923:
8924: /* Bug 8669907:
8925: if(action item exists for the pen_id ) do not create
8918: hr_utility.set_location('Unhook dependent rows ', 500);
8919: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 503);
8920: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 503);
8921: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 503);
8922: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 503);
8923:
8924: /* Bug 8669907:
8925: if(action item exists for the pen_id ) do not create
8926: else
8931:
8932: open c_chk_act_item_exits(g_new_prtt_enrt_rslt_id);
8933: fetch c_chk_act_item_exits into l_dummy1;
8934: if(c_chk_act_item_exits%found) then
8935: hr_utility.set_location('No Create 1',500);
8936: close c_chk_act_item_exits;
8937: l_create_flag := 'N';
8938: else
8939: close c_chk_act_item_exits;
8939: close c_chk_act_item_exits;
8940: open c_chk_same_comp_obj(g_new_prtt_enrt_rslt_id,p_prtt_enrt_rslt_id,p_per_in_ler_id);
8941: fetch c_chk_same_comp_obj into l_create_flag;
8942: if(c_chk_same_comp_obj%notfound) then
8943: hr_utility.set_location('No Create 2',500);
8944: close c_chk_same_comp_obj;
8945: l_create_flag := 'N';
8946: else
8947: close c_chk_same_comp_obj;
8950: close c_bnft_amt;
8951: if(l_bnft.entr_val_at_enrt_flag = 'N') then
8952: if(l_bnft.cvg_mlt_cd = 'FLRNG') then
8953: if(l_bnft.bnft_amt > l_bnft.mx_val) then
8954: hr_utility.set_location('FLRNG Create ',500);
8955: l_create_flag := 'Y';
8956: else
8957: hr_utility.set_location('FLRNG No Create',500);
8958: l_create_flag := 'N';
8953: if(l_bnft.bnft_amt > l_bnft.mx_val) then
8954: hr_utility.set_location('FLRNG Create ',500);
8955: l_create_flag := 'Y';
8956: else
8957: hr_utility.set_location('FLRNG No Create',500);
8958: l_create_flag := 'N';
8959: end if;
8960: else
8961: hr_utility.set_location('Create 1',500);
8957: hr_utility.set_location('FLRNG No Create',500);
8958: l_create_flag := 'N';
8959: end if;
8960: else
8961: hr_utility.set_location('Create 1',500);
8962: l_create_flag := 'Y';
8963: end if;
8964: else
8965: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
8962: l_create_flag := 'Y';
8963: end if;
8964: else
8965: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
8966: hr_utility.set_location('Create 2',500);
8967: l_create_flag := 'Y';
8968: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
8969: hr_utility.set_location('No Create 4',500);
8970: l_create_flag := 'N';
8965: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
8966: hr_utility.set_location('Create 2',500);
8967: l_create_flag := 'Y';
8968: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
8969: hr_utility.set_location('No Create 4',500);
8970: l_create_flag := 'N';
8971: else
8972: hr_utility.set_location('Create 3',500);
8973: l_create_flag := 'Y';
8968: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
8969: hr_utility.set_location('No Create 4',500);
8970: l_create_flag := 'N';
8971: else
8972: hr_utility.set_location('Create 3',500);
8973: l_create_flag := 'Y';
8974: end if;
8975: end if;
8976: end if;
8974: end if;
8975: end if;
8976: end if;
8977: end if;
8978: hr_utility.set_location('l_create_flag '||l_create_flag,500);
8979:
8980: /* open c_chk_same_comp_obj(g_new_prtt_enrt_rslt_id,p_prtt_enrt_rslt_id,p_per_in_ler_id);
8981: fetch c_chk_same_comp_obj into l_create_flag;
8982: if(c_chk_same_comp_obj%notfound) then
8993: open c_chk_act_item_exits(p_prtt_enrt_rslt_id);
8994: fetch c_chk_act_item_exits into l_dummy1;
8995: if(c_chk_act_item_exits%found) then
8996: l_act_flag := true;
8997: hr_utility.set_location('In loop2 false',500);
8998: else
8999: l_act_flag := false;
9000: hr_utility.set_location('In loop2 true',500);
9001: end if;
8996: l_act_flag := true;
8997: hr_utility.set_location('In loop2 false',500);
8998: else
8999: l_act_flag := false;
9000: hr_utility.set_location('In loop2 true',500);
9001: end if;
9002: close c_chk_act_item_exits;
9003: if(l_nper_in_ler_id = l_oper_in_ler_id and l_act_flag) then
9004:
9003: if(l_nper_in_ler_id = l_oper_in_ler_id and l_act_flag) then
9004:
9005: open c_get_actn_items;
9006: fetch c_get_actn_items BULK COLLECT into l_act_items;
9007: hr_utility.set_location('In loop1',500);
9008: if(l_act_items.count > 0) then
9009: l_act_flag := true;
9010: hr_utility.set_location('In loop10 '||l_act_items.count,500);
9011: else
9006: fetch c_get_actn_items BULK COLLECT into l_act_items;
9007: hr_utility.set_location('In loop1',500);
9008: if(l_act_items.count > 0) then
9009: l_act_flag := true;
9010: hr_utility.set_location('In loop10 '||l_act_items.count,500);
9011: else
9012: l_act_flag := false;
9013: hr_utility.set_location('In loop11',500);
9014: end if;
9009: l_act_flag := true;
9010: hr_utility.set_location('In loop10 '||l_act_items.count,500);
9011: else
9012: l_act_flag := false;
9013: hr_utility.set_location('In loop11',500);
9014: end if;
9015: close c_get_actn_items;
9016:
9017:
9021: l_ctfn_flag := true;
9022: end if;
9023: close c_get_ctfn;
9024:
9025: hr_utility.set_location('Remving action items',500);
9026: remove_cert_action_items
9027: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
9028: ,p_effective_date => p_effective_date
9029: ,p_end_date => l_enrt_cvg_end_dt
9035: ,p_per_in_ler_ended_id=> l_pen.per_in_ler_id
9036: );
9037:
9038: if(l_act_flag and l_create_flag = 'Y' ) then
9039: hr_utility.set_location('creating action items',500);
9040: for i IN l_act_items.FIRST..l_act_items.LAST loop
9041: ben_PRTT_ENRT_ACTN_api.create_PRTT_ENRT_ACTN
9042: (p_validate => false
9043: ,p_effective_date => p_effective_date
9088: ,p_effective_end_date => l_actn_end_date
9089: ,p_gnrt_cm => false -- Bug 9256641 : Do not generate communications
9090: );
9091: end loop;
9092: hr_utility.set_location('created action items',500);
9093: end if;
9094:
9095: hr_utility.set_location('before creating cert',500);
9096: if(l_ctfn_flag and l_create_flag = 'Y' ) then
9091: end loop;
9092: hr_utility.set_location('created action items',500);
9093: end if;
9094:
9095: hr_utility.set_location('before creating cert',500);
9096: if(l_ctfn_flag and l_create_flag = 'Y' ) then
9097: hr_utility.set_location('creating certifications ',500);
9098: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
9099: hr_utility.set_location('looping cert ',500);
9093: end if;
9094:
9095: hr_utility.set_location('before creating cert',500);
9096: if(l_ctfn_flag and l_create_flag = 'Y' ) then
9097: hr_utility.set_location('creating certifications ',500);
9098: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
9099: hr_utility.set_location('looping cert ',500);
9100: ben_PRTT_ENRT_CTFN_PRVDD_api.create_PRTT_ENRT_CTFN_PRVDD
9101: (p_validate => false
9095: hr_utility.set_location('before creating cert',500);
9096: if(l_ctfn_flag and l_create_flag = 'Y' ) then
9097: hr_utility.set_location('creating certifications ',500);
9098: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
9099: hr_utility.set_location('looping cert ',500);
9100: ben_PRTT_ENRT_CTFN_PRVDD_api.create_PRTT_ENRT_CTFN_PRVDD
9101: (p_validate => false
9102: ,p_prtt_enrt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id
9103: ,p_effective_start_date => l_ctfn_start_date
9178:
9179: --
9180: l_step := 120;
9181: --
9182: hr_utility.set_location(' Before get_ben_pen_upd_dt_mode '||p_datetrack_mode,123);
9183: get_ben_pen_upd_dt_mode
9184: (p_effective_date => p_effective_date
9185: ,p_base_key_value => p_prtt_enrt_rslt_id
9186: ,P_desired_datetrack_mode => p_datetrack_mode
9186: ,P_desired_datetrack_mode => p_datetrack_mode
9187: ,P_datetrack_allow => l_datetrack_mode
9188: ,p_ler_typ_cd => l_ler_typ_cd -- Bug 2739965
9189: );
9190: hr_utility.set_location(' After get_ben_pen_upd_dt_mode '||l_datetrack_mode,123);
9191: --
9192: l_step := 125;
9193: --
9194: if g_debug then
9191: --
9192: l_step := 125;
9193: --
9194: if g_debug then
9195: hr_utility.set_location('enrt_cvg_end_dt='||l_enrt_cvg_end_dt,19);
9196: end if;
9197: --
9198: --CFW. If deleting a suspended result, remove interim pen id
9199: -- below code is commenetd as part of 5347887.
9239: END IF;
9240: end if;
9241:
9242: if g_debug then
9243: hr_utility.set_location('prtt stat cd'||l_prtt_enrt_rslt_Stat_cd,20);
9244: hr_utility.set_location('l_ler_typ_cd'||l_ler_typ_cd,20);
9245: end if;
9246: ----------------------------Bug 8222481
9247: /*Bug 13034863: When delete_enrollment is called for end dating the suspended enrollment because of
9240: end if;
9241:
9242: if g_debug then
9243: hr_utility.set_location('prtt stat cd'||l_prtt_enrt_rslt_Stat_cd,20);
9244: hr_utility.set_location('l_ler_typ_cd'||l_ler_typ_cd,20);
9245: end if;
9246: ----------------------------Bug 8222481
9247: /*Bug 13034863: When delete_enrollment is called for end dating the suspended enrollment because of
9248: certification being denied, end dated enrollments of previous LE should remain untouched. Modified the below
9253: substr(nvl(l_cvg_end_dt_cd, 'X'), 1, 2) = 'LW' ) then
9254: -------get the ended pen ID
9255: open c_get_ended_pen(p_per_in_ler_id,l_pen.pl_typ_id,l_pen.pgm_id);
9256: fetch c_get_ended_pen bulk collect into l_get_ended_pen;
9257: hr_utility.set_location('Total ended pen : '||c_get_ended_pen%rowcount,1);
9258: --Removed this part,since the fix works only if one plan in a plan type is de-enrolled.
9259: /* if c_get_ended_pen%rowcount > 1 then
9260: hr_utility.set_location('enrolled into more than 1 plan',1);
9261: open c_get_last_pil(l_get_ended_pen(1).person_id);
9256: fetch c_get_ended_pen bulk collect into l_get_ended_pen;
9257: hr_utility.set_location('Total ended pen : '||c_get_ended_pen%rowcount,1);
9258: --Removed this part,since the fix works only if one plan in a plan type is de-enrolled.
9259: /* if c_get_ended_pen%rowcount > 1 then
9260: hr_utility.set_location('enrolled into more than 1 plan',1);
9261: open c_get_last_pil(l_get_ended_pen(1).person_id);
9262: fetch c_get_last_pil into l_get_last_pil;
9263:
9264: if c_get_last_pil%found then
9266: open c_last_pil_pen1(l_get_last_pil.per_in_ler_id,l_pen.pl_id,l_get_ended_pen(i).pgm_id);
9267: fetch c_last_pil_pen1 into l_last_pil_pen1;
9268: if c_last_pil_pen1%found then
9269: l_del_next_chg_pen := l_last_pil_pen1;
9270: hr_utility.set_location('l_del_next_chg_pen.pen_id : '||l_del_next_chg_pen.prtt_enrt_rslt_id,1);
9271: end if;
9272: close c_last_pil_pen1;
9273: end loop;
9274: end if;
9274: end if;
9275: close c_get_last_pil;
9276: elsif c_get_ended_pen%rowcount = 1 then */
9277: if c_get_ended_pen%rowcount = 1 then
9278: hr_utility.set_location('enroled into 1 plan only',1);
9279: open c_get_last_pil(l_get_ended_pen(1).person_id,l_get_ended_pen(1).prtt_enrt_rslt_id); -----Bug 8688513
9280: fetch c_get_last_pil into l_get_last_pil;
9281:
9282: if c_get_last_pil%found then
9279: open c_get_last_pil(l_get_ended_pen(1).person_id,l_get_ended_pen(1).prtt_enrt_rslt_id); -----Bug 8688513
9280: fetch c_get_last_pil into l_get_last_pil;
9281:
9282: if c_get_last_pil%found then
9283: hr_utility.set_location('l_get_last_pil.per_in_ler_id : '||l_get_last_pil.per_in_ler_id,1);
9284: open c_last_pil_pen(l_get_last_pil.per_in_ler_id,l_get_ended_pen(1).prtt_enrt_rslt_id);
9285: fetch c_last_pil_pen into l_last_pil_pen;
9286: close c_last_pil_pen;
9287: l_del_next_chg_pen := l_last_pil_pen;
9284: open c_last_pil_pen(l_get_last_pil.per_in_ler_id,l_get_ended_pen(1).prtt_enrt_rslt_id);
9285: fetch c_last_pil_pen into l_last_pil_pen;
9286: close c_last_pil_pen;
9287: l_del_next_chg_pen := l_last_pil_pen;
9288: hr_utility.set_location('l_del_next_chg_pen.prtt_enrt_rslt_id : '||l_del_next_chg_pen.prtt_enrt_rslt_id,1);
9289: --------delete the next change corresponding to old pen
9290: ben_prtt_enrt_result_api.delete_prtt_enrt_result
9291: (p_validate => false,
9292: p_prtt_enrt_rslt_id => l_del_next_chg_pen.prtt_enrt_rslt_id,
9378: ,p_pil_id => l_pil_id
9379: );
9380: l_step := 135;
9381: if g_debug then
9382: hr_utility.set_location('p_datetrack_mode='||p_datetrack_mode,20);
9383: end if;
9384: if p_source is null or p_source = 'benelinf' then
9385: -- Bug 2537720
9386: -- This is not allowing to update the enrollment on the same date for
9397: else
9398: l_eff_dt := p_effective_date;
9399: end if;
9400: if g_debug then
9401: hr_utility.set_location('per in ler ='|| p_per_in_ler_id ||' old ' || l_pil_id,21);
9402: hr_utility.set_location('future dated cvg ='||l_pen.enrt_cvg_strt_dt,21);
9403: end if;
9404: --- bug 2546259 the if condition added to treat the future dated Coverage
9405: --- if future dated cvg exisit dont zap the result, if the current LE
9398: l_eff_dt := p_effective_date;
9399: end if;
9400: if g_debug then
9401: hr_utility.set_location('per in ler ='|| p_per_in_ler_id ||' old ' || l_pil_id,21);
9402: hr_utility.set_location('future dated cvg ='||l_pen.enrt_cvg_strt_dt,21);
9403: end if;
9404: --- bug 2546259 the if condition added to treat the future dated Coverage
9405: --- if future dated cvg exisit dont zap the result, if the current LE
9406: --- Backedout then the future dated result hase to be restored
9409: and p_per_in_ler_id <> l_pil_id /* and l_pen.SSPNDD_FLAG = 'N' */ then
9410: -- Voiding future dated coverage started in previous LE
9411: --
9412: if g_debug then
9413: hr_utility.set_location('future dated cvg ='||l_pen.enrt_cvg_strt_dt,21);
9414: end if;
9415: If substr(nvl(l_cvg_end_dt_cd, 'X'), 1, 1) = 'W' or
9416: substr(nvl(l_cvg_end_dt_cd, 'X'), 1, 2) = 'LW' then
9417: --Bug 2847110
9440: --
9441: End if;
9442: --
9443: if g_debug then
9444: hr_utility.set_location('c_new_cvg_strt_dt'||l_new_enrt_cvg_strt_dt,199);
9445: hr_utility.set_location('l_enrt_cvg_end_dt'||l_enrt_cvg_end_dt,199);
9446: end if;
9447: -- ikasire: Added the cases starting with 'LW'
9448: -- fixed typo - changed from l_cvg_end_dt_cd to l_rt_end_dt_cd in
9441: End if;
9442: --
9443: if g_debug then
9444: hr_utility.set_location('c_new_cvg_strt_dt'||l_new_enrt_cvg_strt_dt,199);
9445: hr_utility.set_location('l_enrt_cvg_end_dt'||l_enrt_cvg_end_dt,199);
9446: end if;
9447: -- ikasire: Added the cases starting with 'LW'
9448: -- fixed typo - changed from l_cvg_end_dt_cd to l_rt_end_dt_cd in
9449: -- the if condition
9451: substr(nvl(l_rt_end_dt_cd, 'X'), 1, 2) = 'LW'
9452: then
9453: --
9454: if g_debug then
9455: hr_utility.set_location(' pen api rt strt dt ' || l_rt_strt_dt, 299 );
9456: hr_utility.set_location(' pen api rt end dt ' || l_rt_end_dt, 299 );
9457: end if;
9458: l_rt_end_dt := nvl(l_rt_strt_dt - 1,l_rt_end_dt) ;
9459: End if;
9452: then
9453: --
9454: if g_debug then
9455: hr_utility.set_location(' pen api rt strt dt ' || l_rt_strt_dt, 299 );
9456: hr_utility.set_location(' pen api rt end dt ' || l_rt_end_dt, 299 );
9457: end if;
9458: l_rt_end_dt := nvl(l_rt_strt_dt - 1,l_rt_end_dt) ;
9459: End if;
9460: --
9475: -- Update rt_end_dt in prtt_rate_val table
9476: --
9477: l_step := 35;
9478: if g_debug then
9479: hr_utility.set_location(l_proc,3456);
9480: end if;
9481: -- rate is having non recurring
9482: for l_prv in c_prv3 (p_per_in_ler_id) loop
9483: --check whether rate is non-recurring
9491: else
9492: exit;
9493: end if;
9494: if g_debug then
9495: hr_utility.set_location('delete prtt',3459);
9496: end if;
9497: update ben_enrt_rt
9498: set prtt_rt_val_id = null
9499: where enrt_rt_id = l_prv.enrt_rt_id;
9508: );
9509: end loop;
9510: For l_prv in c_prv2 loop
9511: if g_debug then
9512: hr_utility.set_location(l_proc,3457);
9513: end if;
9514: l_prv_count := l_prv_count + 1;
9515: ben_prtt_rt_val_api.update_prtt_rt_val
9516: (P_VALIDATE => FALSE
9527: ,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
9528: ,P_EFFECTIVE_DATE => p_effective_date
9529: );
9530: if g_debug then
9531: hr_utility.set_location(l_proc,3458);
9532: end if;
9533: end loop;
9534: --
9535: -- Adjust overlapping rates . Bug 5391554
9534: --
9535: -- Adjust overlapping rates . Bug 5391554
9536: --
9537: -- basu
9538: hr_utility.set_location('p_per_in_ler_id '||p_per_in_ler_id,44333);
9539: --
9540: open c_get_pgm_extra_info(l_pen.pgm_id);
9541: fetch c_get_pgm_extra_info into l_adjust;
9542: if c_get_pgm_extra_info%found then
9543: if l_adjust = 'Y' then
9544: for l_prv5 in c_prv5(l_rt_end_dt) loop
9545: --
9546: if g_debug then
9547: hr_utility.set_location('Adjusting rate '||l_rt_end_dt,111);
9548: end if;
9549: ben_prtt_rt_val_api.update_prtt_rt_val
9550: (P_VALIDATE => FALSE
9551: ,P_PRTT_RT_VAL_ID => l_prv5.prtt_rt_val_id
9569: -- date needs to be updated
9570: -- Bug 2739965
9571: if l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' then
9572: if g_debug then
9573: hr_utility.set_location('ben mngle mode',111);
9574: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
9575: end if;
9576: --
9577: For l_prv4 in c_prv4 (l_rt_end_dt) loop
9570: -- Bug 2739965
9571: if l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' then
9572: if g_debug then
9573: hr_utility.set_location('ben mngle mode',111);
9574: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
9575: end if;
9576: --
9577: For l_prv4 in c_prv4 (l_rt_end_dt) loop
9578: -- 2739965 In case of unrestricted enrollment, this happens only when there are future rates
9606: );
9607: end loop;
9608: end if;
9609: if g_debug then
9610: hr_utility.set_location(l_proc,3459);
9611: end if;
9612: --
9613: -- Get dependent coverage End date. If Participant coverage end date
9614: -- is greater than dependent coverage end date, then use participant.
9676: );
9677: --
9678: l_step := 125;
9679: if g_debug then
9680: hr_utility.set_location('enrt_cvg_end_dt='||l_enrt_cvg_end_dt,19);
9681: end if;
9682: ben_prtt_enrt_result_api.update_prtt_enrt_result
9683: (p_validate => FALSE
9684: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
9733: p_source = 'benuneai' then
9734: l_datetrack_mode := p_datetrack_mode;
9735: end if;
9736: if g_debug then
9737: hr_utility.set_location('l_datetrack_mode='||l_datetrack_mode,20);
9738: end if;
9739: l_step := 140;
9740: --
9741: -- Added call the below code for 3797391 to get all dates properly
9740: --
9741: -- Added call the below code for 3797391 to get all dates properly
9742: --
9743: if g_debug then
9744: hr_utility.set_location('calc_dpnt_cvg_dt ', 100);
9745: end if;
9746: --
9747: calc_dpnt_cvg_dt
9748: (p_calc_end_dt => TRUE
9777: ,p_datetrack_mode => l_datetrack_mode
9778: ,p_rslt_delete_flag => TRUE
9779: );
9780:
9781: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
9782: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
9783: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
9784: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
9785: hr_utility.set_location('p_source ='||p_source,20);
9778: ,p_rslt_delete_flag => TRUE
9779: );
9780:
9781: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
9782: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
9783: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
9784: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
9785: hr_utility.set_location('p_source ='||p_source,20);
9786: --
9779: );
9780:
9781: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
9782: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
9783: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
9784: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
9785: hr_utility.set_location('p_source ='||p_source,20);
9786: --
9787: l_step := 150;
9780:
9781: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
9782: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
9783: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
9784: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
9785: hr_utility.set_location('p_source ='||p_source,20);
9786: --
9787: l_step := 150;
9788: --
9781: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
9782: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
9783: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
9784: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
9785: hr_utility.set_location('p_source ='||p_source,20);
9786: --
9787: l_step := 150;
9788: --
9789: unhook_dpnt
9828: l_step := 136;
9829: --
9830:
9831: /* Added code for Bug 7561395*/
9832: hr_utility.set_location('delete_enrollment ',500);
9833: hr_utility.set_location('Unhook dependent rows ', 500);
9834: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 504);
9835: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 504);
9836: hr_utility.set_location('l_pen.per_in_ler_id '||l_pen.per_in_ler_id, 504);
9829: --
9830:
9831: /* Added code for Bug 7561395*/
9832: hr_utility.set_location('delete_enrollment ',500);
9833: hr_utility.set_location('Unhook dependent rows ', 500);
9834: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 504);
9835: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 504);
9836: hr_utility.set_location('l_pen.per_in_ler_id '||l_pen.per_in_ler_id, 504);
9837: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 504);
9830:
9831: /* Added code for Bug 7561395*/
9832: hr_utility.set_location('delete_enrollment ',500);
9833: hr_utility.set_location('Unhook dependent rows ', 500);
9834: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 504);
9835: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 504);
9836: hr_utility.set_location('l_pen.per_in_ler_id '||l_pen.per_in_ler_id, 504);
9837: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 504);
9838: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 504);
9831: /* Added code for Bug 7561395*/
9832: hr_utility.set_location('delete_enrollment ',500);
9833: hr_utility.set_location('Unhook dependent rows ', 500);
9834: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 504);
9835: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 504);
9836: hr_utility.set_location('l_pen.per_in_ler_id '||l_pen.per_in_ler_id, 504);
9837: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 504);
9838: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 504);
9839:
9832: hr_utility.set_location('delete_enrollment ',500);
9833: hr_utility.set_location('Unhook dependent rows ', 500);
9834: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 504);
9835: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 504);
9836: hr_utility.set_location('l_pen.per_in_ler_id '||l_pen.per_in_ler_id, 504);
9837: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 504);
9838: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 504);
9839:
9840: /* Bug 8669907:
9833: hr_utility.set_location('Unhook dependent rows ', 500);
9834: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 504);
9835: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 504);
9836: hr_utility.set_location('l_pen.per_in_ler_id '||l_pen.per_in_ler_id, 504);
9837: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 504);
9838: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 504);
9839:
9840: /* Bug 8669907:
9841: if(action item exists for the pen_id ) do not create
9834: hr_utility.set_location('p_prtt_enrt_rslt_ids '||p_prtt_enrt_rslt_id, 504);
9835: hr_utility.set_location('l_pen.pen_per_in_ler_id '||l_pen.pen_per_in_ler_id, 504);
9836: hr_utility.set_location('l_pen.per_in_ler_id '||l_pen.per_in_ler_id, 504);
9837: hr_utility.set_location('p_pen_per_in_ler_id '||p_per_in_ler_id, 504);
9838: hr_utility.set_location('g_new_prtt_enrt_rslt_id '||g_new_prtt_enrt_rslt_id, 504);
9839:
9840: /* Bug 8669907:
9841: if(action item exists for the pen_id ) do not create
9842: else
9847:
9848: open c_chk_act_item_exits(g_new_prtt_enrt_rslt_id);
9849: fetch c_chk_act_item_exits into l_dummy1;
9850: if(c_chk_act_item_exits%found) then
9851: hr_utility.set_location('No Create 1',500);
9852: close c_chk_act_item_exits;
9853: l_create_flag := 'N';
9854: else
9855: close c_chk_act_item_exits;
9855: close c_chk_act_item_exits;
9856: open c_chk_same_comp_obj(g_new_prtt_enrt_rslt_id,p_prtt_enrt_rslt_id,p_per_in_ler_id);
9857: fetch c_chk_same_comp_obj into l_create_flag;
9858: if(c_chk_same_comp_obj%notfound) then
9859: hr_utility.set_location('No Create 2',500);
9860: close c_chk_same_comp_obj;
9861: l_create_flag := 'N';
9862: else
9863: close c_chk_same_comp_obj;
9866: close c_bnft_amt;
9867: if(l_bnft.entr_val_at_enrt_flag = 'N') then
9868: if(l_bnft.cvg_mlt_cd = 'FLRNG') then
9869: if(l_bnft.bnft_amt > l_bnft.mx_val) then
9870: hr_utility.set_location('FLRNG Create ',500);
9871: l_create_flag := 'Y';
9872: else
9873: hr_utility.set_location('FLRNG No Create',500);
9874: l_create_flag := 'N';
9869: if(l_bnft.bnft_amt > l_bnft.mx_val) then
9870: hr_utility.set_location('FLRNG Create ',500);
9871: l_create_flag := 'Y';
9872: else
9873: hr_utility.set_location('FLRNG No Create',500);
9874: l_create_flag := 'N';
9875: end if;
9876: else
9877: hr_utility.set_location('Create 1',500);
9873: hr_utility.set_location('FLRNG No Create',500);
9874: l_create_flag := 'N';
9875: end if;
9876: else
9877: hr_utility.set_location('Create 1',500);
9878: l_create_flag := 'Y';
9879: end if;
9880: else
9881: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
9878: l_create_flag := 'Y';
9879: end if;
9880: else
9881: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
9882: hr_utility.set_location('Create 2',500);
9883: l_create_flag := 'Y';
9884: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
9885: hr_utility.set_location('No Create 4',500);
9886: l_create_flag := 'N';
9881: if(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt > l_bnft.mx_wout_ctfn_val ) then
9882: hr_utility.set_location('Create 2',500);
9883: l_create_flag := 'Y';
9884: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
9885: hr_utility.set_location('No Create 4',500);
9886: l_create_flag := 'N';
9887: else
9888: hr_utility.set_location('Create 3',500);
9889: l_create_flag := 'Y';
9884: elsif(l_bnft.mx_wout_ctfn_val is not null and l_bnft.bnft_amt <= l_bnft.mx_wout_ctfn_val ) then
9885: hr_utility.set_location('No Create 4',500);
9886: l_create_flag := 'N';
9887: else
9888: hr_utility.set_location('Create 3',500);
9889: l_create_flag := 'Y';
9890: end if;
9891: end if;
9892: end if;
9890: end if;
9891: end if;
9892: end if;
9893: end if;
9894: hr_utility.set_location('l_create_flag '||l_create_flag,500);
9895:
9896: /*open c_chk_same_comp_obj(g_new_prtt_enrt_rslt_id,p_prtt_enrt_rslt_id,p_per_in_ler_id);
9897: fetch c_chk_same_comp_obj into l_create_flag;
9898: if(c_chk_same_comp_obj%notfound) then
9905: close c_chk_same_event;
9906: open c_chk_same_event(p_prtt_enrt_rslt_id);
9907: fetch c_chk_same_event into l_oper_in_ler_id ;
9908: close c_chk_same_event;
9909: hr_utility.set_location('Old per_in_ler '||l_oper_in_ler_id, 500);
9910: hr_utility.set_location('new per_in_ler '||l_nper_in_ler_id, 500);
9911: open c_chk_act_item_exits(p_prtt_enrt_rslt_id);
9912: fetch c_chk_act_item_exits into l_dummy1;
9913: if(c_chk_act_item_exits%found) then
9906: open c_chk_same_event(p_prtt_enrt_rslt_id);
9907: fetch c_chk_same_event into l_oper_in_ler_id ;
9908: close c_chk_same_event;
9909: hr_utility.set_location('Old per_in_ler '||l_oper_in_ler_id, 500);
9910: hr_utility.set_location('new per_in_ler '||l_nper_in_ler_id, 500);
9911: open c_chk_act_item_exits(p_prtt_enrt_rslt_id);
9912: fetch c_chk_act_item_exits into l_dummy1;
9913: if(c_chk_act_item_exits%found) then
9914: l_act_flag := true;
9911: open c_chk_act_item_exits(p_prtt_enrt_rslt_id);
9912: fetch c_chk_act_item_exits into l_dummy1;
9913: if(c_chk_act_item_exits%found) then
9914: l_act_flag := true;
9915: hr_utility.set_location('In loop2 false',500);
9916: else
9917: l_act_flag := false;
9918: hr_utility.set_location('In loop2 true',500);
9919: end if;
9914: l_act_flag := true;
9915: hr_utility.set_location('In loop2 false',500);
9916: else
9917: l_act_flag := false;
9918: hr_utility.set_location('In loop2 true',500);
9919: end if;
9920: close c_chk_act_item_exits;
9921: if(l_nper_in_ler_id = l_oper_in_ler_id and l_act_flag) then
9922: open c_get_actn_items;
9920: close c_chk_act_item_exits;
9921: if(l_nper_in_ler_id = l_oper_in_ler_id and l_act_flag) then
9922: open c_get_actn_items;
9923: fetch c_get_actn_items BULK COLLECT into l_act_items;
9924: hr_utility.set_location('In loop1',500);
9925: if(l_act_items.count > 0) then
9926: l_act_flag := true;
9927: hr_utility.set_location('In loop10 '||l_act_items.count,500);
9928: else
9923: fetch c_get_actn_items BULK COLLECT into l_act_items;
9924: hr_utility.set_location('In loop1',500);
9925: if(l_act_items.count > 0) then
9926: l_act_flag := true;
9927: hr_utility.set_location('In loop10 '||l_act_items.count,500);
9928: else
9929: l_act_flag := false;
9930: hr_utility.set_location('In loop11',500);
9931: end if;
9926: l_act_flag := true;
9927: hr_utility.set_location('In loop10 '||l_act_items.count,500);
9928: else
9929: l_act_flag := false;
9930: hr_utility.set_location('In loop11',500);
9931: end if;
9932: close c_get_actn_items;
9933:
9934:
9938: l_ctfn_flag := true;
9939: end if;
9940: close c_get_ctfn;
9941:
9942: hr_utility.set_location('Remving action items',500);
9943: remove_cert_action_items
9944: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
9945: ,p_effective_date => l_eff_dt
9946: ,p_business_group_id => p_business_group_id
9951: ,p_per_in_ler_ended_id=> l_pen.per_in_ler_id
9952: );
9953:
9954: if(l_act_flag and l_create_flag = 'Y' ) then
9955: hr_utility.set_location('creating action items',500);
9956: for i IN l_act_items.FIRST..l_act_items.LAST loop
9957: ben_PRTT_ENRT_ACTN_api.create_PRTT_ENRT_ACTN
9958: (p_validate => false
9959: ,p_effective_date => p_effective_date
10004: ,p_effective_end_date => l_actn_end_date
10005: ,p_gnrt_cm => false -- Bug 9256641 : Do not generate communications
10006: );
10007: end loop;
10008: hr_utility.set_location('created action items',500);
10009: end if;
10010:
10011: hr_utility.set_location('before creating cert',500);
10012: if(l_ctfn_flag and l_create_flag = 'Y' ) then
10007: end loop;
10008: hr_utility.set_location('created action items',500);
10009: end if;
10010:
10011: hr_utility.set_location('before creating cert',500);
10012: if(l_ctfn_flag and l_create_flag = 'Y' ) then
10013: hr_utility.set_location('creating certifications ',500);
10014: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
10015: hr_utility.set_location('looping cert ',500);
10009: end if;
10010:
10011: hr_utility.set_location('before creating cert',500);
10012: if(l_ctfn_flag and l_create_flag = 'Y' ) then
10013: hr_utility.set_location('creating certifications ',500);
10014: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
10015: hr_utility.set_location('looping cert ',500);
10016: ben_PRTT_ENRT_CTFN_PRVDD_api.create_PRTT_ENRT_CTFN_PRVDD
10017: (p_validate => false
10011: hr_utility.set_location('before creating cert',500);
10012: if(l_ctfn_flag and l_create_flag = 'Y' ) then
10013: hr_utility.set_location('creating certifications ',500);
10014: for j IN l_ctfn.FIRST..l_ctfn.LAST loop
10015: hr_utility.set_location('looping cert ',500);
10016: ben_PRTT_ENRT_CTFN_PRVDD_api.create_PRTT_ENRT_CTFN_PRVDD
10017: (p_validate => false
10018: ,p_prtt_enrt_ctfn_prvdd_id => l_prtt_enrt_ctfn_prvdd_id
10019: ,p_effective_start_date => l_ctfn_start_date
10109: -- Bug 4136432
10110: for l_prv in c_prvdel
10111: loop
10112: --
10113: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
10114: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
10115: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
10116: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
10117: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
10110: for l_prv in c_prvdel
10111: loop
10112: --
10113: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
10114: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
10115: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
10116: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
10117: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
10118: --
10111: loop
10112: --
10113: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
10114: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
10115: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
10116: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
10117: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
10118: --
10119: -- Bug 5626835 : For Enter Value At Enrollment cases, we need to just BCKDT PRV, so that
10112: --
10113: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
10114: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
10115: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
10116: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
10117: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
10118: --
10119: -- Bug 5626835 : For Enter Value At Enrollment cases, we need to just BCKDT PRV, so that
10120: -- during reinstatement of corresponding PEN, we can get the actual PRV
10113: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
10114: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
10115: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
10116: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
10117: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
10118: --
10119: -- Bug 5626835 : For Enter Value At Enrollment cases, we need to just BCKDT PRV, so that
10120: -- during reinstatement of corresponding PEN, we can get the actual PRV
10121: -- and hence the previously selected RT_VAL
10178: );
10179:
10180: ELSE
10181: --Bug#:6641853
10182: hr_utility.set_location ('stee delete rate '||l_prv.prtt_rt_val_id,10);
10183: --
10184: -- bug 12583091
10185: -- If the rate was for an enrollment that was previously
10186: -- suspended and the prtt_rt_val rt start date has been updated
10185: -- If the rate was for an enrollment that was previously
10186: -- suspended and the prtt_rt_val rt start date has been updated
10187: -- in the unsuspend process, re-instate the original rt start date.
10188: --
10189: hr_utility.set_location('l_prv.prtt_rt_val_id:'|| l_prv.prtt_rt_val_id, 10);
10190: open c_enrt_rt(l_prv.prtt_rt_val_id);
10191: fetch c_enrt_rt into l_enrt_rt;
10192: if c_enrt_rt%found then
10193: close c_enrt_rt;
10190: open c_enrt_rt(l_prv.prtt_rt_val_id);
10191: fetch c_enrt_rt into l_enrt_rt;
10192: if c_enrt_rt%found then
10193: close c_enrt_rt;
10194: hr_utility.set_location('l_enrt_rt.elig_per_elctbl_chc_id '|| l_enrt_rt.enb_per_elctbl_chc_id, 10);
10195: --
10196: ben_determine_date.rate_and_coverage_dates
10197: (p_which_dates_cd => 'R'
10198: ,p_business_group_id => p_business_group_id
10212: ,p_rt_end_dt_rl => l_dummy_number
10213: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
10214: ,p_effective_date => p_effective_date
10215: );
10216: hr_utility.set_location('l_prv_rt_strt_dt:'|| l_prv_rt_strt_dt, 10);
10217: hr_utility.set_location('l_prv.rt_strt_dt:'|| l_prv.rt_strt_dt, 10);
10218: --
10219: if l_prv_rt_strt_dt <> l_prv.rt_strt_dt then
10220: ben_enrollment_rate_api.update_enrollment_rate
10213: ,p_acty_base_rt_id => l_enrt_rt.acty_base_rt_id
10214: ,p_effective_date => p_effective_date
10215: );
10216: hr_utility.set_location('l_prv_rt_strt_dt:'|| l_prv_rt_strt_dt, 10);
10217: hr_utility.set_location('l_prv.rt_strt_dt:'|| l_prv.rt_strt_dt, 10);
10218: --
10219: if l_prv_rt_strt_dt <> l_prv.rt_strt_dt then
10220: ben_enrollment_rate_api.update_enrollment_rate
10221: (p_enrt_rt_id => l_enrt_rt.enrt_rt_id
10412: -- if the datetrack mode is other than zap we need to call
10413: -- update_prtt_enrt_result procedure
10414: --
10415: if p_source = 'benuneai' or l_datetrack_mode <> hr_api.g_zap then
10416: hr_utility.set_location('called from benuneai',99);
10417: --
10418: -- 4663971
10419: if l_pen_ovn.effective_start_date = l_eff_dt
10420: or l_datetrack_mode='CORRECTION' then
10447: -- is an interim enrollment for the current event. Delete the interim
10448: -- if the enrollment is deleted. Void the interim of the enrollment is
10449: -- voided.
10450: --
10451: hr_utility.set_location('stee l_datetrack_mode '||l_datetrack_mode, 60);
10452: hr_utility.set_location('stee l_prtt_enrt_rslt_Stat_cd '||l_prtt_enrt_rslt_Stat_cd,60);
10453: if l_pen.sspndd_flag = 'N' and
10454: (p_source is null or
10455: p_source = 'benelinf') and
10448: -- if the enrollment is deleted. Void the interim of the enrollment is
10449: -- voided.
10450: --
10451: hr_utility.set_location('stee l_datetrack_mode '||l_datetrack_mode, 60);
10452: hr_utility.set_location('stee l_prtt_enrt_rslt_Stat_cd '||l_prtt_enrt_rslt_Stat_cd,60);
10453: if l_pen.sspndd_flag = 'N' and
10454: (p_source is null or
10455: p_source = 'benelinf') and
10456: l_datetrack_mode = hr_api.g_zap then
10463: if c_get_interim%found then
10464: --
10465: close c_get_interim;
10466: --
10467: hr_utility.set_location('stee l_datetrack_mode '||l_datetrack_mode, 60);
10468: delete_enrollment(
10469: p_validate => false,
10470: p_per_in_ler_id => p_per_in_ler_id,
10471: p_prtt_enrt_rslt_id => l_interim_enrt.prtt_enrt_rslt_id,
10525: --
10526: end if;
10527: --bug # 3086161
10528: if l_corr_pil_id is not null then
10529: hr_utility.set_location(' correcting the resulst for ' || l_corr_pil_id , 999 );
10530: open c_pen_obj_no (p_per_in_ler_id ,p_prtt_enrt_rslt_id ) ;
10531: fetch c_pen_obj_no into l_object_version_number ;
10532: if c_pen_obj_no%found then
10533: hr_utility.set_location(' correcting the result ovn ' || l_object_version_number , 999 );
10529: hr_utility.set_location(' correcting the resulst for ' || l_corr_pil_id , 999 );
10530: open c_pen_obj_no (p_per_in_ler_id ,p_prtt_enrt_rslt_id ) ;
10531: fetch c_pen_obj_no into l_object_version_number ;
10532: if c_pen_obj_no%found then
10533: hr_utility.set_location(' correcting the result ovn ' || l_object_version_number , 999 );
10534: ben_prtt_enrt_result_api.update_prtt_enrt_result
10535: (p_validate => FALSE
10536: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
10537: ,p_effective_start_date => p_effective_start_date
10618: -- if the enrollment is voided. Void the interim of the enrollment is
10619: -- voided.
10620: --
10621: else
10622: hr_utility.set_location('stee l_datetrack_mode '||l_datetrack_mode, 60);
10623: hr_utility.set_location('stee l_prtt_enrt_rslt_Stat_cd '||l_prtt_enrt_rslt_Stat_cd,60);
10624: if l_pen.sspndd_flag = 'N' and
10625: (p_source is null or
10626: p_source = 'benelinf') and
10619: -- voided.
10620: --
10621: else
10622: hr_utility.set_location('stee l_datetrack_mode '||l_datetrack_mode, 60);
10623: hr_utility.set_location('stee l_prtt_enrt_rslt_Stat_cd '||l_prtt_enrt_rslt_Stat_cd,60);
10624: if l_pen.sspndd_flag = 'N' and
10625: (p_source is null or
10626: p_source = 'benelinf') and
10627: (l_prtt_enrt_rslt_stat_cd = 'VOIDD'
10635: if c_get_interim%found then
10636: --
10637: close c_get_interim;
10638: --
10639: hr_utility.set_location('stee l_enrt_cvg_end_dt '||l_enrt_cvg_end_dt,60);
10640: hr_utility.set_location('stee l_interim stat cd '||l_interim_enrt.prtt_enrt_rslt_stat_cd,60);
10641: if l_interim_enrt.prtt_enrt_rslt_stat_cd is null then
10642: ben_prtt_enrt_result_api.update_prtt_enrt_result
10643: (p_validate => FALSE
10636: --
10637: close c_get_interim;
10638: --
10639: hr_utility.set_location('stee l_enrt_cvg_end_dt '||l_enrt_cvg_end_dt,60);
10640: hr_utility.set_location('stee l_interim stat cd '||l_interim_enrt.prtt_enrt_rslt_stat_cd,60);
10641: if l_interim_enrt.prtt_enrt_rslt_stat_cd is null then
10642: ben_prtt_enrt_result_api.update_prtt_enrt_result
10643: (p_validate => FALSE
10644: ,p_prtt_enrt_rslt_id => l_interim_enrt.prtt_enrt_rslt_id
10670: -- end 12583091
10671: end if;
10672: --
10673: if g_debug then
10674: hr_utility.set_location(l_proc, 60);
10675: end if;
10676: --
10677: -- When in validation only mode raise the Validate_Enabled exception
10678: --
10680: raise hr_api.validate_enabled;
10681: end if;
10682: --
10683: if g_debug then
10684: hr_utility.set_location(' Leaving:'||l_proc, 70);
10685: end if;
10686: --
10687: Exception
10688: --
10698: -- (Any key or derived arguments must be set to null
10699: -- when validation only mode is being used.)
10700: --
10701: if g_debug then
10702: hr_utility.set_location(' Leaving:'||l_proc, 80);
10703: end if;
10704: --
10705: when l_fnd_message_exception then
10706: ROLLBACK TO delete_enrollment;
10801: l_per_in_ler_id number := p_per_in_ler_id;
10802: --
10803: begin
10804: --
10805: g_debug := hr_utility.debug_enabled;
10806: if g_debug then
10807: l_proc := g_package||'unhook_bnf';
10808: hr_utility.set_location('Entering'||l_proc, 5);
10809: end if;
10804: --
10805: g_debug := hr_utility.debug_enabled;
10806: if g_debug then
10807: l_proc := g_package||'unhook_bnf';
10808: hr_utility.set_location('Entering'||l_proc, 5);
10809: end if;
10810: --
10811: l_step := 10;
10812: --
10865: l_datetrack_mode := hr_api.g_correction;
10866: --
10867: end if;
10868: if g_debug then
10869: hr_utility.set_location('dt_mode='||l_datetrack_mode,11);
10870: end if;
10871: --
10872: l_step := 20;
10873: --
10881: l_per_in_ler_id := orec.per_in_ler_id;
10882: end if;
10883: --
10884: if g_debug then
10885: hr_utility.set_location('calling update mode' || l_datetrack_mode , 192 );
10886: end if;
10887: ben_PLAN_BENEFICIARY_api.update_PLAN_BENEFICIARY
10888: (p_validate => FALSE
10889: ,p_business_group_id => p_business_group_id
10930: --
10931: else
10932: --
10933: if g_debug then
10934: hr_utility.set_location('calling delete ' || l_datetrack_mode , 192 );
10935: end if;
10936: ben_plan_beneficiary_api.delete_plan_beneficiary
10937: (p_validate => p_validate
10938: ,p_pl_bnf_id => orec.pl_bnf_id
10952: End if;
10953: end if;
10954: End loop;
10955: if g_debug then
10956: hr_utility.set_location('Exiting'||l_proc, 30);
10957: end if;
10958:
10959: exception
10960: --
11068: l_pcp_effective_date date;
11069: --
11070: begin
11071: --
11072: g_debug := hr_utility.debug_enabled;
11073: if g_debug then
11074: l_proc := g_package||'unhook_dpnt';
11075: hr_utility.set_location('Entering'||l_proc, 5);
11076: hr_utility.set_location('p_effective_date '||to_char(p_effective_date), 5);
11071: --
11072: g_debug := hr_utility.debug_enabled;
11073: if g_debug then
11074: l_proc := g_package||'unhook_dpnt';
11075: hr_utility.set_location('Entering'||l_proc, 5);
11076: hr_utility.set_location('p_effective_date '||to_char(p_effective_date), 5);
11077: end if;
11078: --
11079: l_step := 10;
11072: g_debug := hr_utility.debug_enabled;
11073: if g_debug then
11074: l_proc := g_package||'unhook_dpnt';
11075: hr_utility.set_location('Entering'||l_proc, 5);
11076: hr_utility.set_location('p_effective_date '||to_char(p_effective_date), 5);
11077: end if;
11078: --
11079: l_step := 10;
11080: --
11084: -- This was added to zap future dated rows
11085: -- because result delete fails if children
11086: -- are not ended.
11087: --
11088: hr_utility.set_location('orec.effective_start_date '||to_char(orec.effective_start_date), 5);
11089: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode , 5);
11090: hr_utility.set_location('orec.cvg_strt_dt '||to_char(orec.cvg_strt_dt), 5);
11091: hr_utility.set_location('orec.cvg_thru_dt '||to_char(orec.cvg_thru_dt), 5);
11092:
11085: -- because result delete fails if children
11086: -- are not ended.
11087: --
11088: hr_utility.set_location('orec.effective_start_date '||to_char(orec.effective_start_date), 5);
11089: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode , 5);
11090: hr_utility.set_location('orec.cvg_strt_dt '||to_char(orec.cvg_strt_dt), 5);
11091: hr_utility.set_location('orec.cvg_thru_dt '||to_char(orec.cvg_thru_dt), 5);
11092:
11093: if l_last=-1 or
11086: -- are not ended.
11087: --
11088: hr_utility.set_location('orec.effective_start_date '||to_char(orec.effective_start_date), 5);
11089: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode , 5);
11090: hr_utility.set_location('orec.cvg_strt_dt '||to_char(orec.cvg_strt_dt), 5);
11091: hr_utility.set_location('orec.cvg_thru_dt '||to_char(orec.cvg_thru_dt), 5);
11092:
11093: if l_last=-1 or
11094: l_last<>orec.elig_cvrd_dpnt_id then
11087: --
11088: hr_utility.set_location('orec.effective_start_date '||to_char(orec.effective_start_date), 5);
11089: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode , 5);
11090: hr_utility.set_location('orec.cvg_strt_dt '||to_char(orec.cvg_strt_dt), 5);
11091: hr_utility.set_location('orec.cvg_thru_dt '||to_char(orec.cvg_thru_dt), 5);
11092:
11093: if l_last=-1 or
11094: l_last<>orec.elig_cvrd_dpnt_id then
11095: l_last:=orec.elig_cvrd_dpnt_id;
11147: --
11148:
11149: end if;
11150: if g_debug then
11151: hr_utility.set_location('dt_mode='||l_datetrack_mode,11);
11152: end if;
11153: --
11154: l_step := 20;
11155: --
11200: l_datetrack_mode := hr_api.g_zap;
11201: Else
11202: l_datetrack_mode := hr_api.g_delete;
11203: if g_debug then
11204: hr_utility.set_location('in dt_mode='||l_datetrack_mode,11);
11205: end if;
11206: End if;
11207: --
11208: -- Start of fix for INTERNAL bug 4924
11207: --
11208: -- Start of fix for INTERNAL bug 4924
11209: --
11210: if g_debug then
11211: hr_utility.set_location('dt_mode='||l_datetrack_mode,11);
11212: end if;
11213: if l_datetrack_mode = hr_api.g_delete and
11214: p_effective_date = orec.effective_end_date then
11215: --
11224: -- Set End-date to coverage-end-date.
11225: --
11226: for l_pcp in c_pcp(orec.elig_cvrd_dpnt_id) loop
11227: --
11228: hr_utility.set_location('Delete prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
11229: --
11230: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
11231: (P_VALIDATE => FALSE
11232: ,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
11243: -- Get future PCP records if any and zap - delete all of them.
11244: --
11245: for l_pcp_future in c_pcp_future(orec.elig_cvrd_dpnt_id) loop
11246: --
11247: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
11248: --
11249: l_pcp_effective_date := l_pcp_future.effective_start_date;
11250: --
11251: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
11284: End if;
11285: end if;
11286: End loop;
11287: if g_debug then
11288: hr_utility.set_location('Exiting'||l_proc, 30);
11289: end if;
11290: exception
11291: --
11292: when others then
11316: l_validation_end_date date;
11317: --
11318: begin
11319: --
11320: g_debug := hr_utility.debug_enabled;
11321: if g_debug then
11322: l_proc := g_package||'lck';
11323: hr_utility.set_location('Entering:'|| l_proc, 10);
11324: end if;
11319: --
11320: g_debug := hr_utility.debug_enabled;
11321: if g_debug then
11322: l_proc := g_package||'lck';
11323: hr_utility.set_location('Entering:'|| l_proc, 10);
11324: end if;
11325: --
11326: ben_pen_shd.lck
11327: (
11333: ,p_datetrack_mode => p_datetrack_mode
11334: );
11335: --
11336: if g_debug then
11337: hr_utility.set_location(' Leaving:'||l_proc, 70);
11338: end if;
11339: --
11340: end lck;
11341:
11439:
11440: begin
11441:
11442: if g_debug then
11443: hr_utility.set_location(' Entering '||l_proc, 70);
11444: end if;
11445:
11446: if p_pgm_id is not NULL then
11447: --
11445:
11446: if p_pgm_id is not NULL then
11447: --
11448: if g_debug then
11449: hr_utility.set_location('Check for min/max across ptip', 77);
11450: end if;
11451: --
11452: for l_acrs_ptip in c_acrs_ptip loop
11453: --
11451: --
11452: for l_acrs_ptip in c_acrs_ptip loop
11453: --
11454: if g_debug then
11455: hr_utility.set_location('Check for min/max across ptip', 78);
11456: end if;
11457: open c_prtt_total_bnft_amt(l_acrs_ptip.acrs_ptip_cvg_id);
11458: fetch c_prtt_total_bnft_amt into l_total_amt;
11459: close c_prtt_total_bnft_amt;
11457: open c_prtt_total_bnft_amt(l_acrs_ptip.acrs_ptip_cvg_id);
11458: fetch c_prtt_total_bnft_amt into l_total_amt;
11459: close c_prtt_total_bnft_amt;
11460: if g_debug then
11461: hr_utility.set_location('total='||l_total_amt||' mn='||
11462: l_acrs_ptip.mn_cvg_alwd_amt||' mx='||l_acrs_ptip.mx_cvg_alwd_amt,20);
11463: end if;
11464: --
11465: -- bug 3123698
11522: --
11523: end if;
11524: --
11525: if g_debug then
11526: hr_utility.set_location('total='||l_total_amt||' mn='||
11527: l_acrs_ptip.mn_cvg_alwd_amt||' mx='||l_acrs_ptip.mx_cvg_alwd_amt,20);
11528: end if;
11529: --
11530: end if; --p_suspended_enrt_check_flag
11533: --
11534: end if;
11535:
11536: if g_debug then
11537: hr_utility.set_location(' Leaving '||l_proc, 70);
11538: end if;
11539:
11540:
11541: end chk_coverage_across_plan_types;
11954: l_ler_typ_cd ben_ler_f.typ_cd%type;
11955: --
11956: begin
11957: --
11958: g_debug := hr_utility.debug_enabled;
11959: if g_debug then
11960: l_proc := g_package||'multi_rows_edit';
11961: hr_utility.set_location('Entering:'|| l_proc, 10);
11962: end if;
11957: --
11958: g_debug := hr_utility.debug_enabled;
11959: if g_debug then
11960: l_proc := g_package||'multi_rows_edit';
11961: hr_utility.set_location('Entering:'|| l_proc, 10);
11962: end if;
11963: --
11964: --Bug 4709601 we dont need to call multi_rows_edit in this case
11965: --
11963: --
11964: --Bug 4709601 we dont need to call multi_rows_edit in this case
11965: --
11966: if ben_sspndd_enrollment.g_cfw_flag = 'Y' then
11967: hr_utility.set_location('Leaving- called from CFW action items',15);
11968: return;
11969: end if;
11970: --
11971: if fnd_global.conc_request_id in (0,-1) then
12022: --
12023: For i in 1..l_cnt loop
12024:
12025:
12026: hr_utility.set_location('result id :'|| l_rec(i).prtt_enrt_rslt_id , 10);
12027:
12028:
12029: if l_rec(i).erlst_deenrt_dt is not null and
12030: l_rec(i).erlst_deenrt_dt>p_effective_date then
12138: end if;
12139: End loop;
12140: --
12141: if g_debug then
12142: hr_utility.set_location('Del rslts no lngr elig for', 30);
12143: end if;
12144: --
12145: -- Take care of enrollments no longer eligible for
12146: --
12157: end if ;
12158: --
12159: if g_debug then
12160:
12161: hr_utility.set_location('Del rslt in Pgm no lngr elig for:' ||
12162: to_char(p_pgm_id)||' rslt:'||
12163: to_char(rslt.prtt_enrt_rslt_id), 35);
12164: end if;
12165: delete_enrollment
12185: --
12186: l_pl_typ_id := typ.pl_typ_id;
12187: --
12188: if g_debug then
12189: hr_utility.set_location('Del rslt no lngr elig for for pl type '||
12190: to_char(l_pl_typ_id), 37);
12191: end if;
12192: --
12193: For rslt In no_lngr_elig_pnip_c Loop
12276: -- Bug 6656136
12277: end if; -- p_called_frm_ss
12278: --
12279: if g_debug then
12280: hr_utility.set_location(' Leaving:'||l_proc, 70);
12281: end if;
12282: --
12283: end multi_rows_edit;
12284: --
12323: l_proc varchar2(72); -- := g_package||'update_person_type_usages';
12324: --
12325: Begin
12326:
12327: g_debug := hr_utility.debug_enabled;
12328: if g_debug then
12329: l_proc := g_package||'update_person_type_usages';
12330: hr_utility.set_location('Entering:'||l_proc, 5);
12331: end if;
12326:
12327: g_debug := hr_utility.debug_enabled;
12328: if g_debug then
12329: l_proc := g_package||'update_person_type_usages';
12330: hr_utility.set_location('Entering:'||l_proc, 5);
12331: end if;
12332: --
12333: --
12334: if (g_enrollment_change) then
12344:
12345: g_enrollment_change := FALSE;
12346: --
12347: if g_debug then
12348: hr_utility.set_location(' Leaving:'||l_proc, 10);
12349: end if;
12350: --
12351: End update_person_type_usages;
12352:
12377: begin
12378: fnd_msg_pub.initialize;
12379:
12380: if g_debug then
12381: hr_utility.set_location('Entering:'||l_proc, 5);
12382: end if;
12383:
12384: delete_enrollment
12385: (p_validate => p_validate
12397: ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
12398: ,p_mode => p_mode);
12399:
12400: if g_debug then
12401: hr_utility.set_location('Leaving:'||l_proc, 10);
12402: end if;
12403:
12404: exception
12405: --
12403:
12404: exception
12405: --
12406: when app_exception.application_exception then --Bug 4387247
12407: hr_utility.set_location ('Application Error in delete_enrollment_w.', 88);
12408: fnd_msg_pub.add;
12409: when others then
12410: if g_debug then
12411: hr_utility.set_location('Exception:'||l_proc, 100);
12407: hr_utility.set_location ('Application Error in delete_enrollment_w.', 88);
12408: fnd_msg_pub.add;
12409: when others then
12410: if g_debug then
12411: hr_utility.set_location('Exception:'||l_proc, 100);
12412: end if;
12413: hr_utility.set_location ('Other Error in delete_enrollment_w : '|| sqlerrm , 89);
12414: --Bug 4387247
12415: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
12409: when others then
12410: if g_debug then
12411: hr_utility.set_location('Exception:'||l_proc, 100);
12412: end if;
12413: hr_utility.set_location ('Other Error in delete_enrollment_w : '|| sqlerrm , 89);
12414: --Bug 4387247
12415: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
12416: fnd_message.set_token('2',substr(sqlerrm,1,200));
12417: fnd_msg_pub.add;