[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
702: --
703: -- Package Variables
704: --
705: g_package Varchar2(33) := ' ben_PRTT_ENRT_RESULT_api.';
706: g_debug boolean := hr_utility.debug_enabled;
707: --
708: Function display_boolean(p_condition boolean) return varchar2 is
709: begin
710: if (p_condition) then
718: ,p_step in number
719: ) is
720: begin
721: if g_debug then
722: hr_utility.set_location('Fail in '|| p_proc || ' at step ' ||
723: to_char(p_step),999);
724: end if;
725: end;
726:
799: --
800: begin
801: if g_debug then
802: l_proc := g_package||'.remove_cert_action_items';
803: hr_utility.set_location ('Entering '||l_proc,10);
804: hr_utility.set_location ('p_end_date '||p_end_date,10);
805: end if;
806: --
807: l_step := 10;
800: begin
801: if g_debug then
802: l_proc := g_package||'.remove_cert_action_items';
803: hr_utility.set_location ('Entering '||l_proc,10);
804: hr_utility.set_location ('p_end_date '||p_end_date,10);
805: end if;
806: --
807: l_step := 10;
808: /* leslie removed this call
1154: end if;
1155: end if;
1156: end loop;
1157: if g_debug then
1158: hr_utility.set_location ('Leaving '||l_proc,70);
1159: end if;
1160: --
1161: exception
1162: when others then
1160: --
1161: exception
1162: when others then
1163: if g_debug then
1164: hr_utility.set_location ('Fail in '||l_proc|| ' at step ' ||
1165: to_char(l_step),999);
1166: end if;
1167: raise;
1168: end;
1191: l_step number(9);
1192: l_proc varchar2(80) ; -- := g_package||'.get_ben_pen_upd_dt_mode';
1193: begin
1194: --
1195: g_debug := hr_utility.debug_enabled;
1196: if g_debug then
1197: l_proc := g_package||'.get_ben_pen_upd_dt_mode';
1198: hr_utility.set_location ('Entering '||l_proc,10);
1199: hr_utility.set_location('IK p_effective_date'||p_effective_date,1234);
1194: --
1195: g_debug := hr_utility.debug_enabled;
1196: if g_debug then
1197: l_proc := g_package||'.get_ben_pen_upd_dt_mode';
1198: hr_utility.set_location ('Entering '||l_proc,10);
1199: hr_utility.set_location('IK p_effective_date'||p_effective_date,1234);
1200: hr_utility.set_location('IK P_desired_datetrack_mode '||P_desired_datetrack_mode,1234);
1201: end if;
1202: --
1195: g_debug := hr_utility.debug_enabled;
1196: if g_debug then
1197: l_proc := g_package||'.get_ben_pen_upd_dt_mode';
1198: hr_utility.set_location ('Entering '||l_proc,10);
1199: hr_utility.set_location('IK p_effective_date'||p_effective_date,1234);
1200: hr_utility.set_location('IK P_desired_datetrack_mode '||P_desired_datetrack_mode,1234);
1201: end if;
1202: --
1203: l_step := 10;
1196: if g_debug then
1197: l_proc := g_package||'.get_ben_pen_upd_dt_mode';
1198: hr_utility.set_location ('Entering '||l_proc,10);
1199: hr_utility.set_location('IK p_effective_date'||p_effective_date,1234);
1200: hr_utility.set_location('IK P_desired_datetrack_mode '||P_desired_datetrack_mode,1234);
1201: end if;
1202: --
1203: l_step := 10;
1204: --
1242: l_step := 20;
1243: --
1244: if l_dt_correction then
1245: if g_debug then
1246: hr_utility.set_location( 'l_dt_correction',1234);
1247: end if;
1248: end if;
1249: --
1250: if l_dt_update then
1248: end if;
1249: --
1250: if l_dt_update then
1251: if g_debug then
1252: hr_utility.set_location( 'l_dt_update',1234);
1253: end if;
1254: end if;
1255: --
1256: if l_dt_update_override then
1254: end if;
1255: --
1256: if l_dt_update_override then
1257: if g_debug then
1258: hr_utility.set_location( 'l_dt_update_override',1234);
1259: end if;
1260: end if;
1261: --
1262: if l_dt_update_change_insert then
1260: end if;
1261: --
1262: if l_dt_update_change_insert then
1263: if g_debug then
1264: hr_utility.set_location( 'l_dt_update_change_insert',1234);
1265: end if;
1266: end if;
1267:
1268: if l_dt_delete_next_change then
1266: end if;
1267:
1268: if l_dt_delete_next_change then
1269: if g_debug then
1270: hr_utility.set_location( 'l_dt_delete_next_change',1234);
1271: end if;
1272: end if;
1273:
1274: if l_dt_delete then
1272: end if;
1273:
1274: if l_dt_delete then
1275: if g_debug then
1276: hr_utility.set_location( 'l_dt_delete',1234);
1277: end if;
1278: end if;
1279:
1280: --
1308: fnd_message.raise_error;
1309: end if;
1310: --
1311: if g_debug then
1312: hr_utility.set_location(' P_datetrack_allow '||P_datetrack_allow ,1234);
1313: end if;
1314: l_step := 30;
1315: --
1316: if g_debug then
1313: end if;
1314: l_step := 30;
1315: --
1316: if g_debug then
1317: hr_utility.set_location ('Leaving '||l_proc,70);
1318: end if;
1319: Exception
1320: when others then
1321: rpt_error(p_proc => l_proc, p_step => l_step);
1358: begin
1359: --
1360: if g_debug then
1361: l_proc := g_package || '.change_exists_in_db';
1362: hr_utility.set_location ('Entering '||l_proc,10);
1363: end if;
1364: --
1365: open get_change;
1366: fetch get_change into l_dummy;
1375: --
1376: return (l_return);
1377: --
1378: if g_debug then
1379: hr_utility.set_location ('Leaving '||l_proc,70);
1380: end if;
1381: --
1382: end change_exists_in_db;
1383: --
1669: l_process_bnf boolean := false;
1670: --
1671: begin
1672: --
1673: g_debug := hr_utility.debug_enabled;
1674: if g_debug then
1675: l_proc := g_package||'create_enrollment';
1676: hr_utility.set_location('Entering:'|| l_proc, 10);
1677: end if;
1672: --
1673: g_debug := hr_utility.debug_enabled;
1674: if g_debug then
1675: l_proc := g_package||'create_enrollment';
1676: hr_utility.set_location('Entering:'|| l_proc, 10);
1677: end if;
1678: g_multi_rows_validate := p_multi_row_validate;
1679: l_step := 10;
1680: If (p_multi_row_validate) then
1702: l_step := 15;
1703: --
1704: l_crntly_enrd_flag := 'N';
1705: if g_debug then
1706: hr_utility.set_location(l_proc, 20);
1707: end if;
1708: l_step := 20;
1709: --
1710: -- Get choice data from prtt_per_elctbl_chc table
1949: ,p_lf_evt_ocrd_dt => nvl(l_prem.lf_evt_ocrd_dt,p_effective_date)
1950: );
1951:
1952: l_ppe_dt_to_use := greatest(p_enrt_cvg_strt_dt,l_rt_strt_dt);
1953: hr_utility.set_location( 'cvg start dt ' || p_enrt_cvg_strt_dt, 99 ) ;
1954: hr_utility.set_location( 'l_ppe_dt_to_use ' || l_ppe_dt_to_use, 99 ) ;
1955:
1956: /* End of Changes for WWBUG: 1646442: addition */
1957: --
1950: );
1951:
1952: l_ppe_dt_to_use := greatest(p_enrt_cvg_strt_dt,l_rt_strt_dt);
1953: hr_utility.set_location( 'cvg start dt ' || p_enrt_cvg_strt_dt, 99 ) ;
1954: hr_utility.set_location( 'l_ppe_dt_to_use ' || l_ppe_dt_to_use, 99 ) ;
1955:
1956: /* End of Changes for WWBUG: 1646442: addition */
1957: --
1958: /*
2149: -- dependent information as well.
2150: --
2151: l_step := 150;
2152: if g_debug then
2153: hr_utility.set_location('p_prtt_enrt_rslt_id_o'||
2154: to_char(p_prtt_enrt_rslt_id_o)||' l_epe.prtt_enrt_rslt_id'||
2155: to_char(l_global_epe_rec.prtt_enrt_rslt_id),150);
2156: hr_utility.set_location(
2157: ' flag'||l_global_epe_rec.crntly_enrd_flag, 150);
2152: if g_debug then
2153: hr_utility.set_location('p_prtt_enrt_rslt_id_o'||
2154: to_char(p_prtt_enrt_rslt_id_o)||' l_epe.prtt_enrt_rslt_id'||
2155: to_char(l_global_epe_rec.prtt_enrt_rslt_id),150);
2156: hr_utility.set_location(
2157: ' flag'||l_global_epe_rec.crntly_enrd_flag, 150);
2158: end if;
2159:
2160: if (p_prtt_enrt_rslt_id_o is not NULL
2170: (p_business_group_id => p_business_group_id
2171: ,p_effective_date => p_effective_date
2172: ,p_pgm_id => l_global_epe_rec.pgm_id
2173: ,p_inst_row => l_pgm_rec);
2174: hr_utility.set_location('process dptn level ' ||l_pgm_rec.dpnt_dsgn_lvl_cd , 150);
2175: if l_pgm_rec.dpnt_dsgn_lvl_cd='PTIP' then
2176: open c_ptip_enrollment_info;
2177: fetch c_ptip_enrollment_info into l_ptip_enrt;
2178: if c_ptip_enrollment_info%found then
2176: open c_ptip_enrollment_info;
2177: fetch c_ptip_enrollment_info into l_ptip_enrt;
2178: if c_ptip_enrollment_info%found then
2179: l_process_dpnt:= true;
2180: hr_utility.set_location('process dptn true ', 150);
2181: end if ;
2182: end if ;
2183: end if ;
2184: end if;
2348: p_effective_start_date := l_effective_start_date;
2349: p_effective_end_date := l_effective_end_date;
2350: p_object_version_number := l_object_version_number;
2351: if g_debug then
2352: hr_utility.set_location(' Leaving:'||l_proc, 999);
2353: end if;
2354: exception
2355: when hr_api.validate_enabled then
2356: -- As the Validate_Enabled exception has been raised
2371: p_effective_start_date := null;
2372: p_effective_end_date := null;
2373: p_object_version_number := null;
2374: if g_debug then
2375: hr_utility.set_location(' Leaving:'||l_proc, 80);
2376: end if;
2377: when others then
2378: --
2379: -- A validation or unexpected error has occured
2481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2482: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2483: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2484: begin
2485: g_debug := hr_utility.debug_enabled;
2486: if g_debug then
2487: l_proc := g_package||'create_PRTT_ENRT_RESULT';
2488: hr_utility.set_location('Entering:'|| l_proc, 10);
2489: end if;
2484: begin
2485: g_debug := hr_utility.debug_enabled;
2486: if g_debug then
2487: l_proc := g_package||'create_PRTT_ENRT_RESULT';
2488: hr_utility.set_location('Entering:'|| l_proc, 10);
2489: end if;
2490: --
2491: g_multi_rows_validate := p_multi_row_validate;
2492: --
2506: p_benefit_action_id => null);
2507: --
2508: end if;
2509: if g_debug then
2510: hr_utility.set_location(l_proc, 20);
2511: end if;
2512: --
2513: -- Process Logic
2514: --
2758: ,p_hook_type => 'AP'
2759: );
2760: end;
2761: if g_debug then
2762: hr_utility.set_location(l_proc, 60);
2763: end if;
2764: --
2765: -- When in validation only mode raise the Validate_Enabled exception
2766: --
2774: p_effective_start_date := l_effective_start_date;
2775: p_effective_end_date := l_effective_end_date;
2776: p_object_version_number := l_object_version_number;
2777: if g_debug then
2778: hr_utility.set_location(' Leaving:'||l_proc, 70);
2779: end if;
2780: exception
2781: when hr_api.validate_enabled then
2782: --
2793: p_effective_start_date := null;
2794: p_effective_end_date := null;
2795: p_object_version_number := null;
2796: if g_debug then
2797: hr_utility.set_location(' Leaving:'||l_proc, 80);
2798: end if;
2799: when others then
2800: --
2801: -- A validation or unexpected error has occured
3144: --
3145: begin
3146: --
3147: l_step := 10;
3148: g_debug := hr_utility.debug_enabled;
3149: if g_debug then
3150: l_proc := g_package||'update_ENROLLMENT';
3151: hr_utility.set_location('Entering:'|| l_proc, 10);
3152: end if;
3147: l_step := 10;
3148: g_debug := hr_utility.debug_enabled;
3149: if g_debug then
3150: l_proc := g_package||'update_ENROLLMENT';
3151: hr_utility.set_location('Entering:'|| l_proc, 10);
3152: end if;
3153: --
3154: g_multi_rows_validate := p_multi_row_validate;
3155: If (p_multi_row_validate) then
3161: -- Issue a savepoint if operating in validation only mode
3162: --
3163: savepoint update_ENROLLMENT;
3164: if g_debug then
3165: hr_utility.set_location(l_proc, 20);
3166: end if;
3167: --
3168: -- Process Logic
3169: --
3623: fetch c_bpl into l_prvdd_val ;
3624: close c_bpl ;
3625: --if there is amount change call the updating
3626: if g_debug then
3627: hr_utility.set_location( 'prvdd val ' || l_prvdd_val ||
3628: ' ; changed val ' || i.val , 1001);
3629: end if;
3630:
3631: if l_prvdd_val is not null and l_prvdd_val <> i.val then
3758:
3759: l_step := 72;
3760: ---For the Bug : 7133998
3761:
3762: hr_utility.set_location('p_prtt_enrt_rslt_id,srav : '||p_prtt_enrt_rslt_id,1);
3763: hr_utility.set_location('l_prem.actl_prem_id : '||l_prem.actl_prem_id,1);
3764: hr_utility.set_location('l_ppe_dt_to_use : '||l_ppe_dt_to_use,1);
3765:
3766: -----check if there are any future rows corresponding to the Previous LE's.
3759: l_step := 72;
3760: ---For the Bug : 7133998
3761:
3762: hr_utility.set_location('p_prtt_enrt_rslt_id,srav : '||p_prtt_enrt_rslt_id,1);
3763: hr_utility.set_location('l_prem.actl_prem_id : '||l_prem.actl_prem_id,1);
3764: hr_utility.set_location('l_ppe_dt_to_use : '||l_ppe_dt_to_use,1);
3765:
3766: -----check if there are any future rows corresponding to the Previous LE's.
3767: --------7133998
3760: ---For the Bug : 7133998
3761:
3762: hr_utility.set_location('p_prtt_enrt_rslt_id,srav : '||p_prtt_enrt_rslt_id,1);
3763: hr_utility.set_location('l_prem.actl_prem_id : '||l_prem.actl_prem_id,1);
3764: hr_utility.set_location('l_ppe_dt_to_use : '||l_ppe_dt_to_use,1);
3765:
3766: -----check if there are any future rows corresponding to the Previous LE's.
3767: --------7133998
3768: l_count := 0;
3765:
3766: -----check if there are any future rows corresponding to the Previous LE's.
3767: --------7133998
3768: l_count := 0;
3769: hr_utility.set_location('srav,in else',1);
3770: for l_ppe1 in c_ppe1(p_prtt_enrt_rslt_id, l_prem.actl_prem_id,l_ppe_dt_to_use) loop
3771: l_count := l_count + 1;
3772: hr_utility.set_location('c_ppe1 found',1);
3773: hr_utility.set_location('l_ppe1.prtt_prem_id : '||l_ppe1.prtt_prem_id,1);
3768: l_count := 0;
3769: hr_utility.set_location('srav,in else',1);
3770: for l_ppe1 in c_ppe1(p_prtt_enrt_rslt_id, l_prem.actl_prem_id,l_ppe_dt_to_use) loop
3771: l_count := l_count + 1;
3772: hr_utility.set_location('c_ppe1 found',1);
3773: hr_utility.set_location('l_ppe1.prtt_prem_id : '||l_ppe1.prtt_prem_id,1);
3774: ---bkup table
3775: insert into BEN_LE_CLSN_N_RSTR (
3776: BKUP_TBL_TYP_CD,
3769: hr_utility.set_location('srav,in else',1);
3770: for l_ppe1 in c_ppe1(p_prtt_enrt_rslt_id, l_prem.actl_prem_id,l_ppe_dt_to_use) loop
3771: l_count := l_count + 1;
3772: hr_utility.set_location('c_ppe1 found',1);
3773: hr_utility.set_location('l_ppe1.prtt_prem_id : '||l_ppe1.prtt_prem_id,1);
3774: ---bkup table
3775: insert into BEN_LE_CLSN_N_RSTR (
3776: BKUP_TBL_TYP_CD,
3777: LCR_ATTRIBUTE6,
4154: end if;
4155: end if;
4156: end if;
4157: if g_debug then
4158: hr_utility.set_location(l_proc, 60);
4159: end if;
4160: --
4161: -- When in validation only mode raise the Validate_Enabled exception
4162: --
4169: p_object_version_number := l_object_version_number;
4170: p_effective_start_date := l_effective_start_date;
4171: p_effective_end_date := l_effective_end_date;
4172: if g_debug then
4173: hr_utility.set_location(' Leaving:'||l_proc, 70);
4174: end if;
4175: exception
4176: when hr_api.validate_enabled then
4177: --
4184: -- (Any key or derived arguments must be set to null
4185: -- when validation only mode is being used.)
4186: --
4187: if g_debug then
4188: hr_utility.set_location(' Leaving:'||l_proc, 80);
4189: end if;
4190: when others then
4191: --
4192: -- A validation or unexpected error has occured
4335:
4336: --
4337: begin
4338: --
4339: g_debug := hr_utility.debug_enabled;
4340: if g_debug then
4341: l_proc := g_package||'update_PRTT_ENRT_RESULT';
4342: hr_utility.set_location('Entering:'|| l_proc, 10);
4343: end if;
4338: --
4339: g_debug := hr_utility.debug_enabled;
4340: if g_debug then
4341: l_proc := g_package||'update_PRTT_ENRT_RESULT';
4342: hr_utility.set_location('Entering:'|| l_proc, 10);
4343: end if;
4344: g_multi_rows_validate := p_multi_row_validate;
4345: --
4346: -- Issue a savepoint if operating in validation only mode
4365: --
4366: end if;
4367: --
4368: if g_debug then
4369: hr_utility.set_location(l_proc, 20);
4370: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4371: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4372: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4373:
4366: end if;
4367: --
4368: if g_debug then
4369: hr_utility.set_location(l_proc, 20);
4370: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4371: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4372: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4373:
4374: end if;
4367: --
4368: if g_debug then
4369: hr_utility.set_location(l_proc, 20);
4370: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4371: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4372: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4373:
4374: end if;
4375: --
4368: if g_debug then
4369: hr_utility.set_location(l_proc, 20);
4370: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4371: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4372: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4373:
4374: end if;
4375: --
4376: -- Process Logic
4390: close c_old_rslt ;
4391: if l_old_rslt.per_in_ler_id <> p_per_in_ler_id
4392: and p_effective_date=l_old_rslt.effective_start_date then
4393:
4394: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4395: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4396: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4397: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4398: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4391: if l_old_rslt.per_in_ler_id <> p_per_in_ler_id
4392: and p_effective_date=l_old_rslt.effective_start_date then
4393:
4394: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4395: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4396: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4397: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4398: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4399:
4392: and p_effective_date=l_old_rslt.effective_start_date then
4393:
4394: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4395: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4396: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4397: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4398: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4399:
4400:
4393:
4394: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4395: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4396: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4397: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4398: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4399:
4400:
4401: --- if the update called to reverse the stored row from
4394: hr_utility.set_location(' old per in ler id ' || l_old_rslt.per_in_ler_id, 99 );
4395: hr_utility.set_location(' new per in ler id ' || p_per_in_ler_id, 99 );
4396: hr_utility.set_location(' corrected result id ' ||p_prtt_enrt_rslt_id , 99 );
4397: hr_utility.set_location(' mode ' || p_datetrack_mode, 99 );
4398: hr_utility.set_location(' insertin the row of ' || p_prtt_enrt_rslt_id , 99 );
4399:
4400:
4401: --- if the update called to reverse the stored row from
4402: --- from backup table dont insert to backup table
4410: l_old_rslt.ENRT_CVG_THRU_DT ,
4411: l_old_rslt.PRTT_ENRT_RSLT_STAT_CD ) ;
4412: fetch c_bckup_tbl_restore into l_dummy ;
4413: if c_bckup_tbl_restore%notfound then
4414: hr_utility.set_location(' backup row not found ' || p_prtt_enrt_rslt_id , 99 );
4415:
4416: insert into BEN_LE_CLSN_N_RSTR (
4417: BKUP_TBL_TYP_CD,
4418: COMP_LVL_CD,
4674: -- End of API User Hook for the before hook of update_PRTT_ENRT_RESULT
4675: --
4676: end;
4677: --
4678: hr_utility.set_location ( ' eff date ' || p_effective_date, 99 );
4679: hr_utility.set_location ( ' OVN ' || l_object_version_number, 99 );
4680: ben_pen_upd.upd
4681: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4682: ,p_effective_start_date => l_effective_start_date
4675: --
4676: end;
4677: --
4678: hr_utility.set_location ( ' eff date ' || p_effective_date, 99 );
4679: hr_utility.set_location ( ' OVN ' || l_object_version_number, 99 );
4680: ben_pen_upd.upd
4681: (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
4682: ,p_effective_start_date => l_effective_start_date
4683: ,p_effective_end_date => l_effective_end_date
4847: --
4848: end;
4849: --
4850: if g_debug then
4851: hr_utility.set_location(l_proc, 60);
4852: end if;
4853: --
4854: -- When in validation only mode raise the Validate_Enabled exception
4855: --
4863: p_effective_start_date := l_effective_start_date;
4864: p_effective_end_date := l_effective_end_date;
4865: --
4866: if g_debug then
4867: hr_utility.set_location(' Leaving:'||l_proc, 70);
4868: end if;
4869: --
4870: exception
4871: --
4880: -- (Any key or derived arguments must be set to null
4881: -- when validation only mode is being used.)
4882: --
4883: if g_debug then
4884: hr_utility.set_location(' Leaving:'||l_proc, 80);
4885: end if;
4886: --
4887: when others then
4888: --
4918: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4919: --
4920: begin
4921: --
4922: g_debug := hr_utility.debug_enabled;
4923: if g_debug then
4924: l_proc := g_package||'delete_PRTT_ENRT_RESULT';
4925: hr_utility.set_location('Entering:'|| l_proc, 10);
4926: end if;
4921: --
4922: g_debug := hr_utility.debug_enabled;
4923: if g_debug then
4924: l_proc := g_package||'delete_PRTT_ENRT_RESULT';
4925: hr_utility.set_location('Entering:'|| l_proc, 10);
4926: end if;
4927: g_multi_rows_validate := p_multi_row_validate;
4928: --
4929: -- Issue a savepoint if operating in validation only mode
4930: --
4931: savepoint delete_PRTT_ENRT_RESULT;
4932: --
4933: if g_debug then
4934: hr_utility.set_location(l_proc, 20);
4935: end if;
4936: --
4937: -- Process Logic
4938: --
4995: --
4996: end;
4997: --
4998: if g_debug then
4999: hr_utility.set_location(l_proc, 60);
5000: end if;
5001: --
5002: -- When in validation only mode raise the Validate_Enabled exception
5003: --
5009: p_effective_end_date := l_effective_end_date;
5010: p_object_version_number := l_object_version_number;
5011:
5012: if g_debug then
5013: hr_utility.set_location(' Leaving:'||l_proc, 70);
5014: end if;
5015: --
5016: exception
5017: --
5028: --
5029: p_effective_start_date := null;
5030: p_effective_end_date := null;
5031: if g_debug then
5032: hr_utility.set_location(' Leaving:'||l_proc, 80);
5033: end if;
5034: --
5035: when others then
5036: --
5073: l_cvg_end_cd varchar2(30);
5074: l_cvg_end_rl number(15);
5075: l_step integer;
5076: begin
5077: g_debug := hr_utility.debug_enabled;
5078: if g_debug then
5079: l_proc := g_package||'calc_dpnt_cvg_dt';
5080: hr_utility.set_location(' Entering:'||l_proc,10);
5081: end if;
5076: begin
5077: g_debug := hr_utility.debug_enabled;
5078: if g_debug then
5079: l_proc := g_package||'calc_dpnt_cvg_dt';
5080: hr_utility.set_location(' Entering:'||l_proc,10);
5081: end if;
5082: l_step := 10;
5083: if (p_elig_per_elctbl_chc_id is not NULL) then
5084: determine_dpnt_cvg_dt_cd
5090: ,p_cvg_end_cd => l_cvg_end_cd
5091: ,p_cvg_end_rl => l_cvg_end_rl
5092: );
5093: if g_debug then
5094: hr_utility.set_location('dpnt_end_cd' || l_cvg_end_cd,15);
5095: hr_utility.set_location('dpnt_strt_cd'|| l_cvg_strt_cd,15);
5096: end if;
5097: if (p_calc_strt_dt and l_cvg_strt_cd is not NULL) then
5098: l_step := 20;
5091: ,p_cvg_end_rl => l_cvg_end_rl
5092: );
5093: if g_debug then
5094: hr_utility.set_location('dpnt_end_cd' || l_cvg_end_cd,15);
5095: hr_utility.set_location('dpnt_strt_cd'|| l_cvg_strt_cd,15);
5096: end if;
5097: if (p_calc_strt_dt and l_cvg_strt_cd is not NULL) then
5098: l_step := 20;
5099: ben_determine_date.main
5174: l_step := 70;
5175: p_returned_end_dt := l_enrt_cvg_end_dt;
5176: p_returned_strt_dt := l_cvg_strt_dt;
5177: if g_debug then
5178: hr_utility.set_location(' Leaving:'||l_proc, 70);
5179: end if;
5180: exception
5181: when others then
5182: rpt_error(p_proc => l_proc, p_step => l_step);
5250: l_chg c_ler_chg_dep%rowtype;
5251: l_env_rec ben_env_object.g_global_env_rec_type;
5252: --
5253: begin
5254: g_debug := hr_utility.debug_enabled;
5255: if g_debug then
5256: l_proc := 'determine_dpnt_cvg_dt_cd';
5257: hr_utility.set_location ('Entering '||l_proc,10);
5258: end if;
5253: begin
5254: g_debug := hr_utility.debug_enabled;
5255: if g_debug then
5256: l_proc := 'determine_dpnt_cvg_dt_cd';
5257: hr_utility.set_location ('Entering '||l_proc,10);
5258: end if;
5259: l_step := 10;
5260: if fnd_global.conc_request_id in (0,-1) then
5261: --
5279: if c_epe%notfound then
5280: close c_epe;
5281: rpt_error(p_proc => l_proc, p_step => l_step);
5282: if g_debug then
5283: hr_utility.set_location('BEN_91457_ELCTBL_CHC_NOT_FOUND id:'||
5284: to_char(p_elig_per_elctbl_chc_id), 15);
5285: end if;
5286: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
5287: fnd_message.set_token('ID', to_char(p_elig_per_elctbl_chc_id) );
5296: l_epe.ptip_id := p_ptip_id;
5297: l_epe.ler_id := p_ler_id;
5298: end if;
5299: if g_debug then
5300: hr_utility.set_location ('Determining designation level '||l_proc,30);
5301: end if;
5302: l_step := 20;
5303: --
5304: -- If program Id is specified, then use program Id to retreive dependent
5675: l_pcp_effective_date date;
5676:
5677: --
5678: begin
5679: g_debug := hr_utility.debug_enabled;
5680: if g_debug then
5681: l_proc := g_package||'void_enrollment';
5682: hr_utility.set_location('Entering:'|| l_proc, 10);
5683: end if;
5678: begin
5679: g_debug := hr_utility.debug_enabled;
5680: if g_debug then
5681: l_proc := g_package||'void_enrollment';
5682: hr_utility.set_location('Entering:'|| l_proc, 10);
5683: end if;
5684: --
5685: -- Issue a savepoint if operating in validation only mode
5686: --
5725: l_pen.object_version_number := p_object_version_number;
5726: l_pen.enrt_bnft_id := p_enrt_bnft_id; */
5727: --
5728: if g_debug then
5729: hr_utility.set_location('Rate Non recurring',3459);
5730: end if;
5731: for l_prv in c_prv3 (p_per_in_ler_id) loop
5732: --check whether rate is non-recurring
5733: open c_abr(l_prv.acty_base_rt_id);
5741: exit;
5742: end if;
5743:
5744: if g_debug then
5745: hr_utility.set_location('delete prtt',3459);
5746: end if;
5747: update ben_enrt_rt
5748: set prtt_rt_val_id = null
5749: where enrt_rt_id = l_prv.enrt_rt_id;
5763: l_step := 14;
5764: --
5765: -- Update rt_end_dt in prtt_rate_val table
5766: --
5767: hr_utility.set_location(' c_prv EPE'||l_pen.elig_per_elctbl_chc_id,10);
5768: hr_utility.set_location(' c_prv ENB'||l_pen.enrt_bnft_id,10);
5769: hr_utility.set_location(' c_prv PEN'||p_prtt_enrt_rslt_id,10);
5770: --
5771: For l_prv in c_prv loop
5764: --
5765: -- Update rt_end_dt in prtt_rate_val table
5766: --
5767: hr_utility.set_location(' c_prv EPE'||l_pen.elig_per_elctbl_chc_id,10);
5768: hr_utility.set_location(' c_prv ENB'||l_pen.enrt_bnft_id,10);
5769: hr_utility.set_location(' c_prv PEN'||p_prtt_enrt_rslt_id,10);
5770: --
5771: For l_prv in c_prv loop
5772: --
5765: -- Update rt_end_dt in prtt_rate_val table
5766: --
5767: hr_utility.set_location(' c_prv EPE'||l_pen.elig_per_elctbl_chc_id,10);
5768: hr_utility.set_location(' c_prv ENB'||l_pen.enrt_bnft_id,10);
5769: hr_utility.set_location(' c_prv PEN'||p_prtt_enrt_rslt_id,10);
5770: --
5771: For l_prv in c_prv loop
5772: --
5773: hr_utility.set_location(' l_prv PRV'||l_prv.prtt_rt_val_id,10);
5769: hr_utility.set_location(' c_prv PEN'||p_prtt_enrt_rslt_id,10);
5770: --
5771: For l_prv in c_prv loop
5772: --
5773: hr_utility.set_location(' l_prv PRV'||l_prv.prtt_rt_val_id,10);
5774: --
5775: ben_prtt_rt_val_api.update_prtt_rt_val
5776: (P_VALIDATE => FALSE
5777: ,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
5793: -- Set End-date to coverage-end-date.
5794: --
5795: for l_pcp in c_pcp loop
5796: --
5797: hr_utility.set_location('Delete prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id,15);
5798: --
5799: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
5800: (P_VALIDATE => FALSE
5801: ,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
5812: -- Get future PCP records if any and zap - delete all of them.
5813: --
5814: for l_pcp_future in c_pcp_future loop
5815: --
5816: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
5817: --
5818: l_pcp_effective_date := l_pcp_future.effective_start_date ;
5819: --
5820: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
5950: );
5951: end if;
5952:
5953: if g_debug then
5954: hr_utility.set_location(l_proc, 60);
5955: end if;
5956: --
5957: -- When in validation only mode raise the Validate_Enabled exception
5958: --
5960: raise hr_api.validate_enabled;
5961: end if;
5962: --
5963: if g_debug then
5964: hr_utility.set_location(' Leaving:'||l_proc, 70);
5965: end if;
5966: --
5967: Exception
5968: --
5978: -- (Any key or derived arguments must be set to null
5979: -- when validation only mode is being used.)
5980: --
5981: if g_debug then
5982: hr_utility.set_location(' Leaving:'||l_proc, 80);
5983: end if;
5984: when others then
5985: --
5986: -- A validation or unexpected error has occured
6823:
6824:
6825: --
6826: begin
6827: g_debug := hr_utility.debug_enabled;
6828:
6829: if g_debug then
6830: l_proc := g_package||'delete_enrollment';
6831: hr_utility.set_location('Entering:'|| l_proc, 10);
6827: g_debug := hr_utility.debug_enabled;
6828:
6829: if g_debug then
6830: l_proc := g_package||'delete_enrollment';
6831: hr_utility.set_location('Entering:'|| l_proc, 10);
6832: hr_utility.set_location('p_prtt_enrt_rslt_id'||
6833: to_char(p_prtt_enrt_rslt_id),10);
6834: end if;
6835:
6828:
6829: if g_debug then
6830: l_proc := g_package||'delete_enrollment';
6831: hr_utility.set_location('Entering:'|| l_proc, 10);
6832: hr_utility.set_location('p_prtt_enrt_rslt_id'||
6833: to_char(p_prtt_enrt_rslt_id),10);
6834: end if;
6835:
6836: --
6853: --bug#3568529
6854: -- ben_env_object.get(p_rec => l_env_rec);
6855: if l_env_rec.benefit_action_id is null then
6856: --
6857: hr_utility.set_location('Intialise env object',11);
6858: ben_env_object.init(p_business_group_id => p_business_group_id,
6859: p_effective_date => p_effective_date,
6860: p_thread_id => 1,
6861: p_chunk_size => 1,
7057: ben_manage_life_events.g_fonm_cvg_strt_dt := null;
7058: ben_manage_life_events.g_fonm_rt_strt_dt := null;
7059: end if ;
7060: end if ;
7061: hr_utility.set_location (' FONM ' || ben_manage_life_events.fonm , 99 ) ;
7062: hr_utility.set_location (' FONM CVG ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 99 ) ;
7063: -- eof FONM
7064:
7065: ben_determine_date.rate_and_coverage_dates
7058: ben_manage_life_events.g_fonm_rt_strt_dt := null;
7059: end if ;
7060: end if ;
7061: hr_utility.set_location (' FONM ' || ben_manage_life_events.fonm , 99 ) ;
7062: hr_utility.set_location (' FONM CVG ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 99 ) ;
7063: -- eof FONM
7064:
7065: ben_determine_date.rate_and_coverage_dates
7066: (p_which_dates_cd => 'C'
7180: --
7181: --elsif (p_effective_date < nvl(l_pen.erlst_deenrt_dt,hr_api.g_sot) and
7182: -- on the erlst_deenrt_dt then deenrt is allowd so -1 is compared
7183: if g_debug then
7184: hr_utility.set_location('cvg end dt ' || l_enrt_cvg_end_dt , 7086);
7185: hr_utility.set_location('denrt ' || l_pen.erlst_deenrt_dt , 7086);
7186: end if;
7187: elsif (l_enrt_cvg_end_dt < nvl(l_pen.erlst_deenrt_dt,hr_api.g_sot)-1 and
7188: l_pen.sspndd_flag = 'N' /*and p_effective_date > l_pen.enrt_cvg_strt_dt */) then
7181: --elsif (p_effective_date < nvl(l_pen.erlst_deenrt_dt,hr_api.g_sot) and
7182: -- on the erlst_deenrt_dt then deenrt is allowd so -1 is compared
7183: if g_debug then
7184: hr_utility.set_location('cvg end dt ' || l_enrt_cvg_end_dt , 7086);
7185: hr_utility.set_location('denrt ' || l_pen.erlst_deenrt_dt , 7086);
7186: end if;
7187: elsif (l_enrt_cvg_end_dt < nvl(l_pen.erlst_deenrt_dt,hr_api.g_sot)-1 and
7188: l_pen.sspndd_flag = 'N' /*and p_effective_date > l_pen.enrt_cvg_strt_dt */) then
7189: --
7236: end if ;
7237: close c_rslt_ptip ;
7238: elsif l_level = 'PL' then
7239: if g_debug then
7240: hr_utility.set_location('in pl level' , 161);
7241: end if;
7242: open c_rslt_pl(l_pen.person_id ,
7243: l_pen.pgm_id ,
7244: l_pen.pl_id ,
7252: end if ;
7253: close c_rslt_pl ;
7254: else -- OPT,OIPL
7255: if g_debug then
7256: hr_utility.set_location('per in ler ' || l_pen.per_in_ler_id,161);
7257: hr_utility.set_location('start date ' || l_pen.effective_start_date,161);
7258: hr_utility.set_location('in opt level' , 161);
7259: end if;
7260: open c_rslt_opt ( l_pen.person_id ,
7253: close c_rslt_pl ;
7254: else -- OPT,OIPL
7255: if g_debug then
7256: hr_utility.set_location('per in ler ' || l_pen.per_in_ler_id,161);
7257: hr_utility.set_location('start date ' || l_pen.effective_start_date,161);
7258: hr_utility.set_location('in opt level' , 161);
7259: end if;
7260: open c_rslt_opt ( l_pen.person_id ,
7261: l_pen.pgm_id ,
7254: else -- OPT,OIPL
7255: if g_debug then
7256: hr_utility.set_location('per in ler ' || l_pen.per_in_ler_id,161);
7257: hr_utility.set_location('start date ' || l_pen.effective_start_date,161);
7258: hr_utility.set_location('in opt level' , 161);
7259: end if;
7260: open c_rslt_opt ( l_pen.person_id ,
7261: l_pen.pgm_id ,
7262: l_pen.pl_id ,
7271: end if ;
7272: close c_rslt_opt ;
7273: end if;
7274: if g_debug then
7275: hr_utility.set_location(' l_new_enrollment ' || l_new_enrollment , 161 );
7276: end if;
7277: if (l_new_enrollment <> 'Y' or l_pen.per_in_ler_stat_cd = 'PROCD') then
7278: --
7279: -- Got level now see if other enrt in level exists
7292: ,p_level =>l_level
7293: ,p_pen_rec =>l_other_pen_rec
7294: );
7295: if g_debug then
7296: hr_utility.set_location('result ' || l_other_pen_rec.prtt_enrt_rslt_id ,161);
7297: end if;
7298: if l_other_pen_rec.prtt_enrt_rslt_id is null/* or l_crntly_enrd_flag='Y'*/ then -------7458990
7299: hr_utility.set_location('entering',9999);
7300: fnd_message.set_name('BEN','BEN_91902_ENRT_NOT_ALWD_DELETE');
7295: if g_debug then
7296: hr_utility.set_location('result ' || l_other_pen_rec.prtt_enrt_rslt_id ,161);
7297: end if;
7298: if l_other_pen_rec.prtt_enrt_rslt_id is null/* or l_crntly_enrd_flag='Y'*/ then -------7458990
7299: hr_utility.set_location('entering',9999);
7300: fnd_message.set_name('BEN','BEN_91902_ENRT_NOT_ALWD_DELETE');
7301: fnd_message.set_token('PLANNAMEANDOPTIONNAME',l_pen.pl_name || ' '||l_pen.opt_name );
7302: fnd_message.set_token('EARLIESTDEENROLLMENTDATE',fnd_date.date_to_chardate(l_pen.erlst_deenrt_dt));
7303: -- fnd_message.raise_error;
7335: -- always update the result by updating it with that date irrespective
7336: -- of when the coverage starts and when the elections are made.
7337: --
7338:
7339: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7340: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7341: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7342: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7336: -- of when the coverage starts and when the elections are made.
7337: --
7338:
7339: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7340: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7341: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7342: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7344: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7337: --
7338:
7339: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7340: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7341: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7342: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7344: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7345: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7338:
7339: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7340: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7341: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7342: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7344: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7345: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7346: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7339: hr_utility.set_location('p_effective_date '|| to_char(p_effective_date) , 100);
7340: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7341: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7342: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7344: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7345: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7346: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7347: hr_utility.set_location('p_mode '|| p_mode , 100);
7340: hr_utility.set_location('l_pen.ENRT_CVG_STRT_DT '|| to_char(l_pen.ENRT_CVG_STRT_DT) , 100);
7341: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7342: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7344: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7345: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7346: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7347: hr_utility.set_location('p_mode '|| p_mode , 100);
7348:
7341: hr_utility.set_location('l_pen.enrt_cvg_thru_dt '|| to_char(l_pen.enrt_cvg_thru_dt) , 100);
7342: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7344: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7345: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7346: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7347: hr_utility.set_location('p_mode '|| p_mode , 100);
7348:
7349: -- future enrollment is deleted
7342: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7344: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7345: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7346: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7347: hr_utility.set_location('p_mode '|| p_mode , 100);
7348:
7349: -- future enrollment is deleted
7350: -- copy the data into backup table
7343: hr_utility.set_location('enrt_cvg_start_dt tilak '|| l_enrt_cvg_strt_dt , 100);
7344: hr_utility.set_location('prtt_id tilak '|| p_prtt_enrt_rslt_id , 100);
7345: hr_utility.set_location('PLIPID tilak '|| l_pen.per_in_ler_id , 100);
7346: hr_utility.set_location('PLIPID tilak '|| l_pen.pen_per_in_ler_id , 100);
7347: hr_utility.set_location('p_mode '|| p_mode , 100);
7348:
7349: -- future enrollment is deleted
7350: -- copy the data into backup table
7351:
7400: , l_pen.effective_start_date) ; -- 7197868
7401: fetch c_old_corr_pen into l_corr_pil_id ;
7402: if c_old_corr_pen%found then
7403: l_pil_id := l_corr_pil_id ;
7404: hr_utility.set_location('not found corrected '|| l_proc , 99 ) ;
7405: end if ;
7406: close c_old_corr_pen ;
7407: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
7408: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
7403: l_pil_id := l_corr_pil_id ;
7404: hr_utility.set_location('not found corrected '|| l_proc , 99 ) ;
7405: end if ;
7406: close c_old_corr_pen ;
7407: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
7408: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
7409: hr_utility.set_location('l_pil_id '|| l_pil_id , 100);
7410: hr_utility.set_location('p_mode '|| p_mode , 100);
7411: -- Case 1: Coverage started, Coverage Elected and delete in same day.
7404: hr_utility.set_location('not found corrected '|| l_proc , 99 ) ;
7405: end if ;
7406: close c_old_corr_pen ;
7407: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
7408: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
7409: hr_utility.set_location('l_pil_id '|| l_pil_id , 100);
7410: hr_utility.set_location('p_mode '|| p_mode , 100);
7411: -- Case 1: Coverage started, Coverage Elected and delete in same day.
7412: -- * void result and its rates.
7405: end if ;
7406: close c_old_corr_pen ;
7407: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
7408: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
7409: hr_utility.set_location('l_pil_id '|| l_pil_id , 100);
7410: hr_utility.set_location('p_mode '|| p_mode , 100);
7411: -- Case 1: Coverage started, Coverage Elected and delete in same day.
7412: -- * void result and its rates.
7413: if (l_date = p_effective_date) and
7406: close c_old_corr_pen ;
7407: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 100);
7408: hr_utility.set_location('l_date '|| to_char(l_date) , 100);
7409: hr_utility.set_location('l_pil_id '|| l_pil_id , 100);
7410: hr_utility.set_location('p_mode '|| p_mode , 100);
7411: -- Case 1: Coverage started, Coverage Elected and delete in same day.
7412: -- * void result and its rates.
7413: if (l_date = p_effective_date) and
7414: ((p_per_in_ler_id is not null and p_per_in_ler_id = l_pil_id) or
7414: ((p_per_in_ler_id is not null and p_per_in_ler_id = l_pil_id) or
7415: p_per_in_ler_id is null) and
7416: (p_mode is null or p_mode <> 'CVG_END_DATE_ENTERABLE')then
7417: if g_debug then
7418: hr_utility.set_location('p_effective_date='||p_effective_date,19);
7419: end if;
7420: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
7421: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
7422: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
7416: (p_mode is null or p_mode <> 'CVG_END_DATE_ENTERABLE')then
7417: if g_debug then
7418: hr_utility.set_location('p_effective_date='||p_effective_date,19);
7419: end if;
7420: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
7421: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
7422: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
7423: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
7424: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
7417: if g_debug then
7418: hr_utility.set_location('p_effective_date='||p_effective_date,19);
7419: end if;
7420: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
7421: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
7422: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
7423: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
7424: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
7425: l_step := 21;
7418: hr_utility.set_location('p_effective_date='||p_effective_date,19);
7419: end if;
7420: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
7421: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
7422: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
7423: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
7424: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
7425: l_step := 21;
7426: void_enrollment
7419: end if;
7420: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
7421: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
7422: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
7423: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
7424: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
7425: l_step := 21;
7426: void_enrollment
7427: (p_validate => p_validate
7420: hr_utility.set_location(' p_date '|| p_effective_date || '--'||l_pen.effective_start_date , 99 ) ;
7421: hr_utility.set_location(' p_datetrack_mode '|| p_datetrack_mode , 99 ) ;
7422: hr_utility.set_location(' p_source '|| p_source , 99 ) ;
7423: hr_utility.set_location(' result ' || p_prtt_enrt_rslt_id , 99 ) ;
7424: hr_utility.set_location(' thru date ' || p_enrt_cvg_thru_dt , 99 ) ;
7425: l_step := 21;
7426: void_enrollment
7427: (p_validate => p_validate
7428: ,p_per_in_ler_id => p_per_in_ler_id
7447: ,p_validate => FALSE
7448: ,p_datetrack_mode => hr_api.g_delete
7449: );
7450: end if;
7451: hr_utility.set_location('l_benmngle_parm_rec.mode_cd '|| l_benmngle_parm_rec.mode_cd , 100);
7452: hr_utility.set_location('l_env_rec.mode_cd '|| l_env_rec.mode_cd , 100);
7453: hr_utility.set_location('p_source '|| p_source , 100);
7454: -- 3398353
7455: /* In case of Unrestricted Enrollment running on the same-day,
7448: ,p_datetrack_mode => hr_api.g_delete
7449: );
7450: end if;
7451: hr_utility.set_location('l_benmngle_parm_rec.mode_cd '|| l_benmngle_parm_rec.mode_cd , 100);
7452: hr_utility.set_location('l_env_rec.mode_cd '|| l_env_rec.mode_cd , 100);
7453: hr_utility.set_location('p_source '|| p_source , 100);
7454: -- 3398353
7455: /* In case of Unrestricted Enrollment running on the same-day,
7456: the dependent designation records (ben_elig_cvrd_dpnt_f) also
7449: );
7450: end if;
7451: hr_utility.set_location('l_benmngle_parm_rec.mode_cd '|| l_benmngle_parm_rec.mode_cd , 100);
7452: hr_utility.set_location('l_env_rec.mode_cd '|| l_env_rec.mode_cd , 100);
7453: hr_utility.set_location('p_source '|| p_source , 100);
7454: -- 3398353
7455: /* In case of Unrestricted Enrollment running on the same-day,
7456: the dependent designation records (ben_elig_cvrd_dpnt_f) also
7457: needs to be end-dated / voided, when the enrollment is voided.
7464: --
7465: -- if (l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' ) or ( p_source = 'benelinf' ) then
7466: --
7467: if g_debug then
7468: hr_utility.set_location('Updating Dependent Records. l_enrt_cvg_end_dt: '||l_enrt_cvg_end_dt, 100);
7469: end if;
7470: --
7471: l_step := 30;
7472: --
7470: --
7471: l_step := 30;
7472: --
7473: if g_debug then
7474: hr_utility.set_location('calc_dpnt_cvg_dt ', 100);
7475: end if;
7476: --
7477: calc_dpnt_cvg_dt
7478: (p_calc_end_dt => TRUE
7506: --
7507: l_step := 50;
7508: --
7509: if g_debug then
7510: hr_utility.set_location('unhook_dpnt cvg-end-date: '||l_dpnt_cvg_thru_dt, 100);
7511: end if;
7512: --
7513: unhook_dpnt
7514: (p_validate => FALSE
7521: ,p_called_from => p_source
7522: );
7523: --
7524: if g_debug then
7525: hr_utility.set_location('unhook_bnf cvg-end-date: '||l_dpnt_cvg_thru_dt, 100);
7526: end if;
7527: --
7528: unhook_bnf
7529: (p_validate => FALSE
7563: l_rt_end_dt_cd in ('LDPPOEFD','LDPPFEFD','LODBEWM','ODBEWM') ) and
7564: (p_per_in_ler_id <> l_pil_id or l_ler_typ_cd = 'SCHEDDU' or
7565: (p_per_in_ler_id = l_pil_id and l_ler_typ_cd not in ('IREC','GSP','COMP','ABS'))) then
7566: -- Bug 2847110
7567: hr_utility.set_location('c_new_cvg_strt_dt,srav : '||l_new_enrt_cvg_strt_dt,199);
7568: open c_new_cvg_strt_dt(l_pen.pl_typ_id, l_pen.ptip_id,l_pen.pl_id,
7569: l_enrt_cvg_strt_dt );
7570: fetch c_new_cvg_strt_dt into l_new_enrt_cvg_strt_dt;
7571: close c_new_cvg_strt_dt;
7590: --
7591: end if;
7592: --
7593: if g_debug then
7594: hr_utility.set_location('c_new_cvg_strt_dt'||l_new_enrt_cvg_strt_dt,199);
7595: hr_utility.set_location('l_enrt_cvg_end_dt'||l_enrt_cvg_end_dt,199);
7596: end if;
7597: --
7598: -- Change Rate Dates to 1 Prior
7591: end if;
7592: --
7593: if g_debug then
7594: hr_utility.set_location('c_new_cvg_strt_dt'||l_new_enrt_cvg_strt_dt,199);
7595: hr_utility.set_location('l_enrt_cvg_end_dt'||l_enrt_cvg_end_dt,199);
7596: end if;
7597: --
7598: -- Change Rate Dates to 1 Prior
7599: If (substr(nvl(l_rt_end_dt_cd, 'X'), 1, 1) = 'W' or
7603: -- Bug: 4642299 Added 'LODBEWM','ODBEWM' too for 1 Prior logic.
7604: then
7605: --
7606: if g_debug then
7607: hr_utility.set_location(' pen api rt strt dt ' || l_rt_strt_dt, 299 );
7608: hr_utility.set_location(' pen api rt end dt ' || l_rt_end_dt, 299 );
7609: end if;
7610: --bug#3260564
7611: if l_new_enrt_cvg_strt_dt is not null then
7604: then
7605: --
7606: if g_debug then
7607: hr_utility.set_location(' pen api rt strt dt ' || l_rt_strt_dt, 299 );
7608: hr_utility.set_location(' pen api rt end dt ' || l_rt_end_dt, 299 );
7609: end if;
7610: --bug#3260564
7611: if l_new_enrt_cvg_strt_dt is not null then
7612: l_rt_end_dt := nvl(l_rt_strt_dt - 1,l_rt_end_dt) ;
7651: fetch c_check_carry_fwd_enrt into l_check_carry_fwd_enrt;
7652: close c_check_carry_fwd_enrt;
7653:
7654: if l_check_carry_fwd_enrt is null then
7655: hr_utility.set_location('Void Rate procedure is called',3455);
7656: void_rate (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
7657: p_business_group_id => p_business_group_id,
7658: p_person_id => l_pen.person_id,
7659: p_per_in_ler_id => p_per_in_ler_id,
7663: If substr(nvl(l_rt_end_dt_cd, 'X'), 1, 1) = 'W' then
7664: open c_get_old_prv;
7665: fetch c_get_old_prv into l_get_old_prv;
7666: if c_get_old_prv%found then
7667: hr_utility.set_location('l_get_old_prv.input_value_id,srav : '||l_get_old_prv.input_value_id,3455);
7668: hr_utility.set_location('l_get_old_prv.prtt_rt_val_id :'||l_get_old_prv.prtt_rt_val_id,3455);
7669: hr_utility.set_location('p_prtt_enrt_rslt_id :'||p_prtt_enrt_rslt_id,3455);
7670: hr_utility.set_location('l_rt_end_dt :'||l_rt_end_dt,3455);
7671:
7664: open c_get_old_prv;
7665: fetch c_get_old_prv into l_get_old_prv;
7666: if c_get_old_prv%found then
7667: hr_utility.set_location('l_get_old_prv.input_value_id,srav : '||l_get_old_prv.input_value_id,3455);
7668: hr_utility.set_location('l_get_old_prv.prtt_rt_val_id :'||l_get_old_prv.prtt_rt_val_id,3455);
7669: hr_utility.set_location('p_prtt_enrt_rslt_id :'||p_prtt_enrt_rslt_id,3455);
7670: hr_utility.set_location('l_rt_end_dt :'||l_rt_end_dt,3455);
7671:
7672: ben_prtt_rt_val_api.update_prtt_rt_val
7665: fetch c_get_old_prv into l_get_old_prv;
7666: if c_get_old_prv%found then
7667: hr_utility.set_location('l_get_old_prv.input_value_id,srav : '||l_get_old_prv.input_value_id,3455);
7668: hr_utility.set_location('l_get_old_prv.prtt_rt_val_id :'||l_get_old_prv.prtt_rt_val_id,3455);
7669: hr_utility.set_location('p_prtt_enrt_rslt_id :'||p_prtt_enrt_rslt_id,3455);
7670: hr_utility.set_location('l_rt_end_dt :'||l_rt_end_dt,3455);
7671:
7672: ben_prtt_rt_val_api.update_prtt_rt_val
7673: (P_VALIDATE => FALSE
7666: if c_get_old_prv%found then
7667: hr_utility.set_location('l_get_old_prv.input_value_id,srav : '||l_get_old_prv.input_value_id,3455);
7668: hr_utility.set_location('l_get_old_prv.prtt_rt_val_id :'||l_get_old_prv.prtt_rt_val_id,3455);
7669: hr_utility.set_location('p_prtt_enrt_rslt_id :'||p_prtt_enrt_rslt_id,3455);
7670: hr_utility.set_location('l_rt_end_dt :'||l_rt_end_dt,3455);
7671:
7672: ben_prtt_rt_val_api.update_prtt_rt_val
7673: (P_VALIDATE => FALSE
7674: ,P_PRTT_RT_VAL_ID => l_get_old_prv.prtt_rt_val_id
7680: ,p_business_group_id => p_business_group_id
7681: ,P_OBJECT_VERSION_NUMBER => l_get_old_prv.object_version_number
7682: ,P_EFFECTIVE_DATE => p_effective_date
7683: );
7684: hr_utility.set_location('P_RT_END_DT,srav :'||hr_api.g_eot,3455);
7685: end if;
7686: close c_get_old_prv;
7687: hr_utility.set_location('P_RT_END_DT,srav,not fund :'||hr_api.g_eot,3455);
7688: hr_utility.set_location('p_effective_start_date :'||p_effective_start_date,3455);
7683: );
7684: hr_utility.set_location('P_RT_END_DT,srav :'||hr_api.g_eot,3455);
7685: end if;
7686: close c_get_old_prv;
7687: hr_utility.set_location('P_RT_END_DT,srav,not fund :'||hr_api.g_eot,3455);
7688: hr_utility.set_location('p_effective_start_date :'||p_effective_start_date,3455);
7689: hr_utility.set_location('p_effective_date :'||p_effective_date,3455);
7690: end if;
7691: -----Bug 6925893
7684: hr_utility.set_location('P_RT_END_DT,srav :'||hr_api.g_eot,3455);
7685: end if;
7686: close c_get_old_prv;
7687: hr_utility.set_location('P_RT_END_DT,srav,not fund :'||hr_api.g_eot,3455);
7688: hr_utility.set_location('p_effective_start_date :'||p_effective_start_date,3455);
7689: hr_utility.set_location('p_effective_date :'||p_effective_date,3455);
7690: end if;
7691: -----Bug 6925893
7692: end if;
7685: end if;
7686: close c_get_old_prv;
7687: hr_utility.set_location('P_RT_END_DT,srav,not fund :'||hr_api.g_eot,3455);
7688: hr_utility.set_location('p_effective_start_date :'||p_effective_start_date,3455);
7689: hr_utility.set_location('p_effective_date :'||p_effective_date,3455);
7690: end if;
7691: -----Bug 6925893
7692: end if;
7693: end if;
7692: end if;
7693: end if;
7694: --End Bug 5499809
7695:
7696: hr_utility.set_location('P_RT_END_DT,srav,not fund1 :'||hr_api.g_eot,3455);
7697: --
7698: l_step := 35;
7699: if g_debug then
7700: hr_utility.set_location(l_proc,3456);
7696: hr_utility.set_location('P_RT_END_DT,srav,not fund1 :'||hr_api.g_eot,3455);
7697: --
7698: l_step := 35;
7699: if g_debug then
7700: hr_utility.set_location(l_proc,3456);
7701: end if;
7702: -- rate is having non recurring
7703: if g_debug then
7704: hr_utility.set_location(l_deenrol_dt,3459);
7700: hr_utility.set_location(l_proc,3456);
7701: end if;
7702: -- rate is having non recurring
7703: if g_debug then
7704: hr_utility.set_location(l_deenrol_dt,3459);
7705: end if;
7706: for l_prv in c_prv3 (p_per_in_ler_id) loop
7707: --check whether rate is non-recurring
7708: open c_abr(l_prv.acty_base_rt_id);
7716: else
7717: exit;
7718: end if;
7719: if g_debug then
7720: hr_utility.set_location('delete prtt',3459);
7721: end if;
7722: update ben_enrt_rt
7723: set prtt_rt_val_id = null
7724: where enrt_rt_id = l_prv.enrt_rt_id;
7733: );
7734: end loop;
7735: For l_prv in c_prv2 loop
7736: if g_debug then
7737: hr_utility.set_location(l_proc,3457);
7738: end if;
7739: l_prv_count := l_prv_count + 1;
7740: if (nvl(l_prv.per_in_ler_id, -1) <> nvl(p_per_in_ler_id,-1)) and
7741: l_rt_end_dt < l_prv.rt_strt_dt then
7756: ,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
7757: ,P_EFFECTIVE_DATE => p_effective_date
7758: );
7759: if g_debug then
7760: hr_utility.set_location(l_proc,3458);
7761: end if;
7762: end loop;
7763: --
7764: -- Adjust overlapping rates . Bug 5391554
7763: --
7764: -- Adjust overlapping rates . Bug 5391554
7765: --
7766: -- basu
7767: hr_utility.set_location('p_per_in_ler_id '||p_per_in_ler_id,44333);
7768: --
7769: open c_enrt_in_curr_le;
7770: fetch c_enrt_in_curr_le into l_enrt_in_curr_le;
7771: --
7770: fetch c_enrt_in_curr_le into l_enrt_in_curr_le;
7771: --
7772: if c_enrt_in_curr_le%found then
7773: --
7774: hr_utility.set_location('l_enrt_in_curr_le '||l_enrt_in_curr_le,44333);
7775: --
7776: if l_enrt_in_curr_le = 'Y' then
7777: -- basu
7778: open c_get_pgm_extra_info(l_pen.pgm_id);
7781: if l_adjust = 'Y' then
7782: for l_prv5 in c_prv5(l_rt_end_dt) loop
7783: --
7784: if g_debug then
7785: hr_utility.set_location('Adjusting rate '||l_rt_end_dt,111);
7786: end if;
7787: ben_prtt_rt_val_api.update_prtt_rt_val
7788: (P_VALIDATE => FALSE
7789: ,P_PRTT_RT_VAL_ID => l_prv5.prtt_rt_val_id
7810: --
7811: for l_pen5 in c_pen5(l_enrt_cvg_end_dt) loop
7812: --
7813: if g_debug then
7814: hr_utility.set_location('Adjusting coverage for '||l_enrt_cvg_end_dt,44333);
7815: end if;
7816: ben_prtt_enrt_result_api.update_prtt_enrt_result
7817: (p_validate => FALSE,
7818: p_prtt_enrt_rslt_id => l_pen5.prtt_enrt_rslt_id,
7841: -- date needs to be updated
7842: -- Bug 2739965
7843: if l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' then
7844: if g_debug then
7845: hr_utility.set_location('ben mngle mode',111);
7846: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
7847: end if;
7848: For l_prv4 in c_prv4 (l_rt_end_dt) loop
7849: if g_debug then
7842: -- Bug 2739965
7843: if l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' then
7844: if g_debug then
7845: hr_utility.set_location('ben mngle mode',111);
7846: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
7847: end if;
7848: For l_prv4 in c_prv4 (l_rt_end_dt) loop
7849: if g_debug then
7850: hr_utility.set_location(l_proc,3459);
7846: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
7847: end if;
7848: For l_prv4 in c_prv4 (l_rt_end_dt) loop
7849: if g_debug then
7850: hr_utility.set_location(l_proc,3459);
7851: end if;
7852: -- 2739965 In case of unrestricted enrollment, this happens only when there are future rates
7853: -- and they got deleted as part of c_prv2 process above. Lets us throw this error
7854: -- here warning the user about the deletion of the future rate.
7882: ,P_OBJECT_VERSION_NUMBER => l_prv4.object_version_number
7883: ,P_EFFECTIVE_DATE => p_effective_date
7884: );
7885: if g_debug then
7886: hr_utility.set_location(l_proc,3460);
7887: end if;
7888: --
7889: end if; /* End Bug 5739530 */
7890: --
7891: end loop;
7892: --
7893: end if;
7894: if g_debug then
7895: hr_utility.set_location(l_proc,3459);
7896: end if;
7897: --
7898: -- 3574168: Remove PCP records
7899: -- Set End-date to coverage-end-date.
7901: l_pcp_effective_date := NVL(l_enrt_cvg_end_dt+1, p_effective_date);
7902: --
7903: for l_pcp in c_pcp(l_pcp_effective_date) loop
7904: --
7905: hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
7906: hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
7907: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
7908: hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
7909: -- Since, deletion automatically sets end-date to 1 day less than effective-date,
7902: --
7903: for l_pcp in c_pcp(l_pcp_effective_date) loop
7904: --
7905: hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
7906: hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
7907: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
7908: hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
7909: -- Since, deletion automatically sets end-date to 1 day less than effective-date,
7910: -- call the delete-api with effective_date = cvg_thru_date+1.
7903: for l_pcp in c_pcp(l_pcp_effective_date) loop
7904: --
7905: hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
7906: hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
7907: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
7908: hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
7909: -- Since, deletion automatically sets end-date to 1 day less than effective-date,
7910: -- call the delete-api with effective_date = cvg_thru_date+1.
7911: --
7904: --
7905: hr_utility.set_location('DELETE prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
7906: hr_utility.set_location('PCP ESD: EED '|| l_pcp.effective_start_date ||': '||l_pcp.effective_end_date, 15);
7907: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_date, 15);
7908: hr_utility.set_location('DATETRACK_MODE '|| hr_api.g_delete, 15);
7909: -- Since, deletion automatically sets end-date to 1 day less than effective-date,
7910: -- call the delete-api with effective_date = cvg_thru_date+1.
7911: --
7912: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
7927: for l_pcp_future in c_pcp_future(l_pcp_effective_date) loop
7928: --
7929: l_pcp_effective_start_date := l_pcp_future.effective_start_date;
7930: --
7931: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
7932: hr_utility.set_location('PCP ESD: EED '|| l_pcp_future.effective_start_date ||': '||l_pcp_future.effective_end_date, 15);
7933: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
7934: --
7935: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
7928: --
7929: l_pcp_effective_start_date := l_pcp_future.effective_start_date;
7930: --
7931: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
7932: hr_utility.set_location('PCP ESD: EED '|| l_pcp_future.effective_start_date ||': '||l_pcp_future.effective_end_date, 15);
7933: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
7934: --
7935: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
7936: (P_VALIDATE => FALSE
7929: l_pcp_effective_start_date := l_pcp_future.effective_start_date;
7930: --
7931: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
7932: hr_utility.set_location('PCP ESD: EED '|| l_pcp_future.effective_start_date ||': '||l_pcp_future.effective_end_date, 15);
7933: hr_utility.set_location('Effective Date to delete '|| l_pcp_effective_start_date, 15);
7934: --
7935: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
7936: (P_VALIDATE => FALSE
7937: ,P_PRMRY_CARE_PRVDR_ID => l_pcp_future.prmry_care_prvdr_id
8012: );
8013: --
8014: l_step := 120;
8015: --
8016: hr_utility.set_location(' Before get_ben_pen_upd_dt_mode '||p_datetrack_mode,123);
8017: get_ben_pen_upd_dt_mode
8018: (p_effective_date => p_effective_date
8019: ,p_base_key_value => p_prtt_enrt_rslt_id
8020: ,P_desired_datetrack_mode => p_datetrack_mode
8020: ,P_desired_datetrack_mode => p_datetrack_mode
8021: ,P_datetrack_allow => l_datetrack_mode
8022: ,p_ler_typ_cd => l_ler_typ_cd -- Bug 2739965
8023: );
8024: hr_utility.set_location(' After get_ben_pen_upd_dt_mode '||l_datetrack_mode,123);
8025: --
8026: l_step := 125;
8027: --
8028: if g_debug then
8025: --
8026: l_step := 125;
8027: --
8028: if g_debug then
8029: hr_utility.set_location('enrt_cvg_end_dt='||l_enrt_cvg_end_dt,19);
8030: end if;
8031: --
8032: --CFW. If deleting a suspended result, remove interim pen id
8033: -- below code is commenetd as part of 5347887.
8072: END IF;
8073: end if;
8074:
8075: if g_debug then
8076: hr_utility.set_location('prtt stat cd'||l_prtt_enrt_rslt_Stat_cd,20);
8077: hr_utility.set_location('l_ler_typ_cd'||l_ler_typ_cd,20);
8078: end if;
8079:
8080: --End Bug 5499809
8073: end if;
8074:
8075: if g_debug then
8076: hr_utility.set_location('prtt stat cd'||l_prtt_enrt_rslt_Stat_cd,20);
8077: hr_utility.set_location('l_ler_typ_cd'||l_ler_typ_cd,20);
8078: end if;
8079:
8080: --End Bug 5499809
8081:
8150: ,p_pil_id => l_pil_id
8151: );
8152: l_step := 135;
8153: if g_debug then
8154: hr_utility.set_location('p_datetrack_mode='||p_datetrack_mode,20);
8155: end if;
8156: if p_source is null or p_source = 'benelinf' then
8157: -- Bug 2537720
8158: -- This is not allowing to update the enrollment on the same date for
8169: else
8170: l_eff_dt := p_effective_date;
8171: end if;
8172: if g_debug then
8173: hr_utility.set_location('per in ler ='|| p_per_in_ler_id ||' old ' || l_pil_id,21);
8174: hr_utility.set_location('future dated cvg ='||l_pen.enrt_cvg_strt_dt,21);
8175: end if;
8176: --- bug 2546259 the if condition added to treat the future dated Coverage
8177: --- if future dated cvg exisit dont zap the result, if the current LE
8170: l_eff_dt := p_effective_date;
8171: end if;
8172: if g_debug then
8173: hr_utility.set_location('per in ler ='|| p_per_in_ler_id ||' old ' || l_pil_id,21);
8174: hr_utility.set_location('future dated cvg ='||l_pen.enrt_cvg_strt_dt,21);
8175: end if;
8176: --- bug 2546259 the if condition added to treat the future dated Coverage
8177: --- if future dated cvg exisit dont zap the result, if the current LE
8178: --- Backedout then the future dated result hase to be restored
8181: and p_per_in_ler_id <> l_pil_id /* and l_pen.SSPNDD_FLAG = 'N' */ then
8182: -- Voiding future dated coverage started in previous LE
8183: --
8184: if g_debug then
8185: hr_utility.set_location('future dated cvg ='||l_pen.enrt_cvg_strt_dt,21);
8186: end if;
8187: If substr(nvl(l_cvg_end_dt_cd, 'X'), 1, 1) = 'W' or
8188: substr(nvl(l_cvg_end_dt_cd, 'X'), 1, 2) = 'LW' then
8189: --Bug 2847110
8209: --
8210: End if;
8211: --
8212: if g_debug then
8213: hr_utility.set_location('c_new_cvg_strt_dt'||l_new_enrt_cvg_strt_dt,199);
8214: hr_utility.set_location('l_enrt_cvg_end_dt'||l_enrt_cvg_end_dt,199);
8215: end if;
8216: -- ikasire: Added the cases starting with 'LW'
8217: -- fixed typo - changed from l_cvg_end_dt_cd to l_rt_end_dt_cd in
8210: End if;
8211: --
8212: if g_debug then
8213: hr_utility.set_location('c_new_cvg_strt_dt'||l_new_enrt_cvg_strt_dt,199);
8214: hr_utility.set_location('l_enrt_cvg_end_dt'||l_enrt_cvg_end_dt,199);
8215: end if;
8216: -- ikasire: Added the cases starting with 'LW'
8217: -- fixed typo - changed from l_cvg_end_dt_cd to l_rt_end_dt_cd in
8218: -- the if condition
8220: substr(nvl(l_rt_end_dt_cd, 'X'), 1, 2) = 'LW'
8221: then
8222: --
8223: if g_debug then
8224: hr_utility.set_location(' pen api rt strt dt ' || l_rt_strt_dt, 299 );
8225: hr_utility.set_location(' pen api rt end dt ' || l_rt_end_dt, 299 );
8226: end if;
8227: l_rt_end_dt := nvl(l_rt_strt_dt - 1,l_rt_end_dt) ;
8228: End if;
8221: then
8222: --
8223: if g_debug then
8224: hr_utility.set_location(' pen api rt strt dt ' || l_rt_strt_dt, 299 );
8225: hr_utility.set_location(' pen api rt end dt ' || l_rt_end_dt, 299 );
8226: end if;
8227: l_rt_end_dt := nvl(l_rt_strt_dt - 1,l_rt_end_dt) ;
8228: End if;
8229: --
8244: -- Update rt_end_dt in prtt_rate_val table
8245: --
8246: l_step := 35;
8247: if g_debug then
8248: hr_utility.set_location(l_proc,3456);
8249: end if;
8250: -- rate is having non recurring
8251: for l_prv in c_prv3 (p_per_in_ler_id) loop
8252: --check whether rate is non-recurring
8260: else
8261: exit;
8262: end if;
8263: if g_debug then
8264: hr_utility.set_location('delete prtt',3459);
8265: end if;
8266: update ben_enrt_rt
8267: set prtt_rt_val_id = null
8268: where enrt_rt_id = l_prv.enrt_rt_id;
8277: );
8278: end loop;
8279: For l_prv in c_prv2 loop
8280: if g_debug then
8281: hr_utility.set_location(l_proc,3457);
8282: end if;
8283: l_prv_count := l_prv_count + 1;
8284: ben_prtt_rt_val_api.update_prtt_rt_val
8285: (P_VALIDATE => FALSE
8296: ,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
8297: ,P_EFFECTIVE_DATE => p_effective_date
8298: );
8299: if g_debug then
8300: hr_utility.set_location(l_proc,3458);
8301: end if;
8302: end loop;
8303: --
8304: -- Adjust overlapping rates . Bug 5391554
8303: --
8304: -- Adjust overlapping rates . Bug 5391554
8305: --
8306: -- basu
8307: hr_utility.set_location('p_per_in_ler_id '||p_per_in_ler_id,44333);
8308: --
8309: open c_enrt_in_curr_le;
8310: fetch c_enrt_in_curr_le into l_enrt_in_curr_le;
8311: --
8308: --
8309: open c_enrt_in_curr_le;
8310: fetch c_enrt_in_curr_le into l_enrt_in_curr_le;
8311: --
8312: hr_utility.set_location('l_enrt_in_curr_le '||l_enrt_in_curr_le,44333);
8313: --
8314: if c_enrt_in_curr_le%found then
8315: if l_enrt_in_curr_le = 'Y' then
8316: -- basu
8320: if l_adjust = 'Y' then
8321: for l_prv5 in c_prv5(l_rt_end_dt) loop
8322: --
8323: if g_debug then
8324: hr_utility.set_location('Adjusting rate '||l_rt_end_dt,111);
8325: end if;
8326: ben_prtt_rt_val_api.update_prtt_rt_val
8327: (P_VALIDATE => FALSE
8328: ,P_PRTT_RT_VAL_ID => l_prv5.prtt_rt_val_id
8349: --
8350: for l_pen5 in c_pen5(l_enrt_cvg_end_dt) loop
8351: --
8352: if g_debug then
8353: hr_utility.set_location('Adjusting coverage for '||l_enrt_cvg_end_dt,44333);
8354: end if;
8355: ben_prtt_enrt_result_api.update_prtt_enrt_result
8356: (p_validate => FALSE,
8357: p_prtt_enrt_rslt_id => l_pen5.prtt_enrt_rslt_id,
8381: -- date needs to be updated
8382: -- Bug 2739965
8383: if l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' then
8384: if g_debug then
8385: hr_utility.set_location('ben mngle mode',111);
8386: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
8387: end if;
8388: --
8389: For l_prv4 in c_prv4 (l_rt_end_dt) loop
8382: -- Bug 2739965
8383: if l_benmngle_parm_rec.mode_cd = 'S' or l_ler_typ_cd = 'SCHEDDU' then
8384: if g_debug then
8385: hr_utility.set_location('ben mngle mode',111);
8386: hr_utility.set_location('rate end date'||l_rt_end_dt,111);
8387: end if;
8388: --
8389: For l_prv4 in c_prv4 (l_rt_end_dt) loop
8390: -- 2739965 In case of unrestricted enrollment, this happens only when there are future rates
8418: );
8419: end loop;
8420: end if;
8421: if g_debug then
8422: hr_utility.set_location(l_proc,3459);
8423: end if;
8424: --
8425: -- Get dependent coverage End date. If Participant coverage end date
8426: -- is greater than dependent coverage end date, then use participant.
8488: );
8489: --
8490: l_step := 125;
8491: if g_debug then
8492: hr_utility.set_location('enrt_cvg_end_dt='||l_enrt_cvg_end_dt,19);
8493: end if;
8494: ben_prtt_enrt_result_api.update_prtt_enrt_result
8495: (p_validate => FALSE
8496: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
8544: p_source = 'benuneai' then
8545: l_datetrack_mode := p_datetrack_mode;
8546: end if;
8547: if g_debug then
8548: hr_utility.set_location('l_datetrack_mode='||l_datetrack_mode,20);
8549: end if;
8550: l_step := 140;
8551: --
8552: -- Added call the below code for 3797391 to get all dates properly
8551: --
8552: -- Added call the below code for 3797391 to get all dates properly
8553: --
8554: if g_debug then
8555: hr_utility.set_location('calc_dpnt_cvg_dt ', 100);
8556: end if;
8557: --
8558: calc_dpnt_cvg_dt
8559: (p_calc_end_dt => TRUE
8588: ,p_datetrack_mode => l_datetrack_mode
8589: ,p_rslt_delete_flag => TRUE
8590: );
8591:
8592: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8593: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
8594: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
8595: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8596: hr_utility.set_location('p_source ='||p_source,20);
8589: ,p_rslt_delete_flag => TRUE
8590: );
8591:
8592: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8593: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
8594: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
8595: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8596: hr_utility.set_location('p_source ='||p_source,20);
8597: --
8590: );
8591:
8592: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8593: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
8594: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
8595: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8596: hr_utility.set_location('p_source ='||p_source,20);
8597: --
8598: l_step := 150;
8591:
8592: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8593: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
8594: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
8595: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8596: hr_utility.set_location('p_source ='||p_source,20);
8597: --
8598: l_step := 150;
8599: --
8592: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8593: hr_utility.set_location('l_pen.per_in_ler_id ='||l_pen.per_in_ler_id,20);
8594: hr_utility.set_location('l_dpnt_cvg_thru_dt ='||to_char(l_dpnt_cvg_thru_dt),20);
8595: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8596: hr_utility.set_location('p_source ='||p_source,20);
8597: --
8598: l_step := 150;
8599: --
8600: unhook_dpnt
8668: -- Bug 4136432
8669: for l_prv in c_prvdel
8670: loop
8671: --
8672: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8673: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8674: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
8675: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
8676: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
8669: for l_prv in c_prvdel
8670: loop
8671: --
8672: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8673: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8674: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
8675: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
8676: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
8677: --
8670: loop
8671: --
8672: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8673: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8674: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
8675: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
8676: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
8677: --
8678: -- Bug 5626835 : For Enter Value At Enrollment cases, we need to just BCKDT PRV, so that
8671: --
8672: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8673: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8674: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
8675: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
8676: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
8677: --
8678: -- Bug 5626835 : For Enter Value At Enrollment cases, we need to just BCKDT PRV, so that
8679: -- during reinstatement of corresponding PEN, we can get the actual PRV
8672: hr_utility.set_location('p_prtt_enrt_rslt_id ='||p_prtt_enrt_rslt_id,20);
8673: hr_utility.set_location('l_eff_dt ='||to_char(l_eff_dt),20);
8674: hr_utility.set_location('l_prv.prtt_rt_val_id ='||l_prv.prtt_rt_val_id,20);
8675: hr_utility.set_location('l_pen.person_id ='|| l_pen.person_id,20);
8676: hr_utility.set_location('l_prv.enrt_rt_id ='||l_prv.enrt_rt_id,20);
8677: --
8678: -- Bug 5626835 : For Enter Value At Enrollment cases, we need to just BCKDT PRV, so that
8679: -- during reinstatement of corresponding PEN, we can get the actual PRV
8680: -- and hence the previously selected RT_VAL
8918: -- if the datetrack mode is other than zap we need to call
8919: -- update_prtt_enrt_result procedure
8920: --
8921: if p_source = 'benuneai' or l_datetrack_mode <> hr_api.g_zap then
8922: hr_utility.set_location('called from benuneai',99);
8923: --
8924: -- 4663971
8925: if l_pen_ovn.effective_start_date = l_eff_dt
8926: or l_datetrack_mode='CORRECTION' then
8986: --
8987: end if;
8988: --bug # 3086161
8989: if l_corr_pil_id is not null then
8990: hr_utility.set_location(' correcting the resulst for ' || l_corr_pil_id , 999 );
8991: open c_pen_obj_no (p_per_in_ler_id ,p_prtt_enrt_rslt_id ) ;
8992: fetch c_pen_obj_no into l_object_version_number ;
8993: if c_pen_obj_no%found then
8994: hr_utility.set_location(' correcting the result ovn ' || l_object_version_number , 999 );
8990: hr_utility.set_location(' correcting the resulst for ' || l_corr_pil_id , 999 );
8991: open c_pen_obj_no (p_per_in_ler_id ,p_prtt_enrt_rslt_id ) ;
8992: fetch c_pen_obj_no into l_object_version_number ;
8993: if c_pen_obj_no%found then
8994: hr_utility.set_location(' correcting the result ovn ' || l_object_version_number , 999 );
8995: ben_prtt_enrt_result_api.update_prtt_enrt_result
8996: (p_validate => FALSE
8997: ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
8998: ,p_effective_start_date => p_effective_start_date
9076: --
9077: end if;
9078: --
9079: if g_debug then
9080: hr_utility.set_location(l_proc, 60);
9081: end if;
9082: --
9083: -- When in validation only mode raise the Validate_Enabled exception
9084: --
9086: raise hr_api.validate_enabled;
9087: end if;
9088: --
9089: if g_debug then
9090: hr_utility.set_location(' Leaving:'||l_proc, 70);
9091: end if;
9092: --
9093: Exception
9094: --
9104: -- (Any key or derived arguments must be set to null
9105: -- when validation only mode is being used.)
9106: --
9107: if g_debug then
9108: hr_utility.set_location(' Leaving:'||l_proc, 80);
9109: end if;
9110: --
9111: when l_fnd_message_exception then
9112: ROLLBACK TO delete_enrollment;
9207: l_per_in_ler_id number := p_per_in_ler_id;
9208: --
9209: begin
9210: --
9211: g_debug := hr_utility.debug_enabled;
9212: if g_debug then
9213: l_proc := g_package||'unhook_bnf';
9214: hr_utility.set_location('Entering'||l_proc, 5);
9215: end if;
9210: --
9211: g_debug := hr_utility.debug_enabled;
9212: if g_debug then
9213: l_proc := g_package||'unhook_bnf';
9214: hr_utility.set_location('Entering'||l_proc, 5);
9215: end if;
9216: --
9217: l_step := 10;
9218: --
9271: l_datetrack_mode := hr_api.g_correction;
9272: --
9273: end if;
9274: if g_debug then
9275: hr_utility.set_location('dt_mode='||l_datetrack_mode,11);
9276: end if;
9277: --
9278: l_step := 20;
9279: --
9287: l_per_in_ler_id := orec.per_in_ler_id;
9288: end if;
9289: --
9290: if g_debug then
9291: hr_utility.set_location('calling update mode' || l_datetrack_mode , 192 );
9292: end if;
9293: ben_PLAN_BENEFICIARY_api.update_PLAN_BENEFICIARY
9294: (p_validate => FALSE
9295: ,p_business_group_id => p_business_group_id
9336: --
9337: else
9338: --
9339: if g_debug then
9340: hr_utility.set_location('calling delete ' || l_datetrack_mode , 192 );
9341: end if;
9342: ben_plan_beneficiary_api.delete_plan_beneficiary
9343: (p_validate => p_validate
9344: ,p_pl_bnf_id => orec.pl_bnf_id
9358: End if;
9359: end if;
9360: End loop;
9361: if g_debug then
9362: hr_utility.set_location('Exiting'||l_proc, 30);
9363: end if;
9364:
9365: exception
9366: --
9474: l_pcp_effective_date date;
9475: --
9476: begin
9477: --
9478: g_debug := hr_utility.debug_enabled;
9479: if g_debug then
9480: l_proc := g_package||'unhook_dpnt';
9481: hr_utility.set_location('Entering'||l_proc, 5);
9482: hr_utility.set_location('p_effective_date '||to_char(p_effective_date), 5);
9477: --
9478: g_debug := hr_utility.debug_enabled;
9479: if g_debug then
9480: l_proc := g_package||'unhook_dpnt';
9481: hr_utility.set_location('Entering'||l_proc, 5);
9482: hr_utility.set_location('p_effective_date '||to_char(p_effective_date), 5);
9483: end if;
9484: --
9485: l_step := 10;
9478: g_debug := hr_utility.debug_enabled;
9479: if g_debug then
9480: l_proc := g_package||'unhook_dpnt';
9481: hr_utility.set_location('Entering'||l_proc, 5);
9482: hr_utility.set_location('p_effective_date '||to_char(p_effective_date), 5);
9483: end if;
9484: --
9485: l_step := 10;
9486: --
9490: -- This was added to zap future dated rows
9491: -- because result delete fails if children
9492: -- are not ended.
9493: --
9494: hr_utility.set_location('orec.effective_start_date '||to_char(orec.effective_start_date), 5);
9495: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode , 5);
9496: hr_utility.set_location('orec.cvg_strt_dt '||to_char(orec.cvg_strt_dt), 5);
9497: hr_utility.set_location('orec.cvg_thru_dt '||to_char(orec.cvg_thru_dt), 5);
9498:
9491: -- because result delete fails if children
9492: -- are not ended.
9493: --
9494: hr_utility.set_location('orec.effective_start_date '||to_char(orec.effective_start_date), 5);
9495: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode , 5);
9496: hr_utility.set_location('orec.cvg_strt_dt '||to_char(orec.cvg_strt_dt), 5);
9497: hr_utility.set_location('orec.cvg_thru_dt '||to_char(orec.cvg_thru_dt), 5);
9498:
9499: if l_last=-1 or
9492: -- are not ended.
9493: --
9494: hr_utility.set_location('orec.effective_start_date '||to_char(orec.effective_start_date), 5);
9495: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode , 5);
9496: hr_utility.set_location('orec.cvg_strt_dt '||to_char(orec.cvg_strt_dt), 5);
9497: hr_utility.set_location('orec.cvg_thru_dt '||to_char(orec.cvg_thru_dt), 5);
9498:
9499: if l_last=-1 or
9500: l_last<>orec.elig_cvrd_dpnt_id then
9493: --
9494: hr_utility.set_location('orec.effective_start_date '||to_char(orec.effective_start_date), 5);
9495: hr_utility.set_location('p_datetrack_mode '||p_datetrack_mode , 5);
9496: hr_utility.set_location('orec.cvg_strt_dt '||to_char(orec.cvg_strt_dt), 5);
9497: hr_utility.set_location('orec.cvg_thru_dt '||to_char(orec.cvg_thru_dt), 5);
9498:
9499: if l_last=-1 or
9500: l_last<>orec.elig_cvrd_dpnt_id then
9501: l_last:=orec.elig_cvrd_dpnt_id;
9549: --
9550:
9551: end if;
9552: if g_debug then
9553: hr_utility.set_location('dt_mode='||l_datetrack_mode,11);
9554: end if;
9555: --
9556: l_step := 20;
9557: --
9602: l_datetrack_mode := hr_api.g_zap;
9603: Else
9604: l_datetrack_mode := hr_api.g_delete;
9605: if g_debug then
9606: hr_utility.set_location('in dt_mode='||l_datetrack_mode,11);
9607: end if;
9608: End if;
9609: --
9610: -- Start of fix for INTERNAL bug 4924
9609: --
9610: -- Start of fix for INTERNAL bug 4924
9611: --
9612: if g_debug then
9613: hr_utility.set_location('dt_mode='||l_datetrack_mode,11);
9614: end if;
9615: if l_datetrack_mode = hr_api.g_delete and
9616: p_effective_date = orec.effective_end_date then
9617: --
9626: -- Set End-date to coverage-end-date.
9627: --
9628: for l_pcp in c_pcp(orec.elig_cvrd_dpnt_id) loop
9629: --
9630: hr_utility.set_location('Delete prmry_care_prvdr_id '|| l_pcp.prmry_care_prvdr_id, 15);
9631: --
9632: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
9633: (P_VALIDATE => FALSE
9634: ,P_PRMRY_CARE_PRVDR_ID => l_pcp.prmry_care_prvdr_id
9645: -- Get future PCP records if any and zap - delete all of them.
9646: --
9647: for l_pcp_future in c_pcp_future(orec.elig_cvrd_dpnt_id) loop
9648: --
9649: hr_utility.set_location('ZAP prmry_care_prvdr_id '|| l_pcp_future.prmry_care_prvdr_id, 15);
9650: --
9651: l_pcp_effective_date := l_pcp_future.effective_start_date;
9652: --
9653: ben_prmry_care_prvdr_api.delete_prmry_care_prvdr
9686: End if;
9687: end if;
9688: End loop;
9689: if g_debug then
9690: hr_utility.set_location('Exiting'||l_proc, 30);
9691: end if;
9692: exception
9693: --
9694: when others then
9718: l_validation_end_date date;
9719: --
9720: begin
9721: --
9722: g_debug := hr_utility.debug_enabled;
9723: if g_debug then
9724: l_proc := g_package||'lck';
9725: hr_utility.set_location('Entering:'|| l_proc, 10);
9726: end if;
9721: --
9722: g_debug := hr_utility.debug_enabled;
9723: if g_debug then
9724: l_proc := g_package||'lck';
9725: hr_utility.set_location('Entering:'|| l_proc, 10);
9726: end if;
9727: --
9728: ben_pen_shd.lck
9729: (
9735: ,p_datetrack_mode => p_datetrack_mode
9736: );
9737: --
9738: if g_debug then
9739: hr_utility.set_location(' Leaving:'||l_proc, 70);
9740: end if;
9741: --
9742: end lck;
9743:
9841:
9842: begin
9843:
9844: if g_debug then
9845: hr_utility.set_location(' Entering '||l_proc, 70);
9846: end if;
9847:
9848: if p_pgm_id is not NULL then
9849: --
9847:
9848: if p_pgm_id is not NULL then
9849: --
9850: if g_debug then
9851: hr_utility.set_location('Check for min/max across ptip', 77);
9852: end if;
9853: --
9854: for l_acrs_ptip in c_acrs_ptip loop
9855: --
9853: --
9854: for l_acrs_ptip in c_acrs_ptip loop
9855: --
9856: if g_debug then
9857: hr_utility.set_location('Check for min/max across ptip', 78);
9858: end if;
9859: open c_prtt_total_bnft_amt(l_acrs_ptip.acrs_ptip_cvg_id);
9860: fetch c_prtt_total_bnft_amt into l_total_amt;
9861: close c_prtt_total_bnft_amt;
9859: open c_prtt_total_bnft_amt(l_acrs_ptip.acrs_ptip_cvg_id);
9860: fetch c_prtt_total_bnft_amt into l_total_amt;
9861: close c_prtt_total_bnft_amt;
9862: if g_debug then
9863: hr_utility.set_location('total='||l_total_amt||' mn='||
9864: l_acrs_ptip.mn_cvg_alwd_amt||' mx='||l_acrs_ptip.mx_cvg_alwd_amt,20);
9865: end if;
9866: --
9867: -- bug 3123698
9924: --
9925: end if;
9926: --
9927: if g_debug then
9928: hr_utility.set_location('total='||l_total_amt||' mn='||
9929: l_acrs_ptip.mn_cvg_alwd_amt||' mx='||l_acrs_ptip.mx_cvg_alwd_amt,20);
9930: end if;
9931: --
9932: end if; --p_suspended_enrt_check_flag
9935: --
9936: end if;
9937:
9938: if g_debug then
9939: hr_utility.set_location(' Leaving '||l_proc, 70);
9940: end if;
9941:
9942:
9943: end chk_coverage_across_plan_types;
10356: l_ler_typ_cd ben_ler_f.typ_cd%type;
10357: --
10358: begin
10359: --
10360: g_debug := hr_utility.debug_enabled;
10361: if g_debug then
10362: l_proc := g_package||'multi_rows_edit';
10363: hr_utility.set_location('Entering:'|| l_proc, 10);
10364: end if;
10359: --
10360: g_debug := hr_utility.debug_enabled;
10361: if g_debug then
10362: l_proc := g_package||'multi_rows_edit';
10363: hr_utility.set_location('Entering:'|| l_proc, 10);
10364: end if;
10365: --
10366: --Bug 4709601 we dont need to call multi_rows_edit in this case
10367: --
10365: --
10366: --Bug 4709601 we dont need to call multi_rows_edit in this case
10367: --
10368: if ben_sspndd_enrollment.g_cfw_flag = 'Y' then
10369: hr_utility.set_location('Leaving- called from CFW action items',15);
10370: return;
10371: end if;
10372: --
10373: if fnd_global.conc_request_id in (0,-1) then
10411: --
10412: For i in 1..l_cnt loop
10413:
10414:
10415: hr_utility.set_location('result id :'|| l_rec(i).prtt_enrt_rslt_id , 10);
10416:
10417:
10418: if l_rec(i).erlst_deenrt_dt is not null and
10419: l_rec(i).erlst_deenrt_dt>p_effective_date then
10527: end if;
10528: End loop;
10529: --
10530: if g_debug then
10531: hr_utility.set_location('Del rslts no lngr elig for', 30);
10532: end if;
10533: --
10534: -- Take care of enrollments no longer eligible for
10535: --
10546: end if ;
10547: --
10548: if g_debug then
10549:
10550: hr_utility.set_location('Del rslt in Pgm no lngr elig for:' ||
10551: to_char(p_pgm_id)||' rslt:'||
10552: to_char(rslt.prtt_enrt_rslt_id), 35);
10553: end if;
10554: delete_enrollment
10574: --
10575: l_pl_typ_id := typ.pl_typ_id;
10576: --
10577: if g_debug then
10578: hr_utility.set_location('Del rslt no lngr elig for for pl type '||
10579: to_char(l_pl_typ_id), 37);
10580: end if;
10581: --
10582: For rslt In no_lngr_elig_pnip_c Loop
10677: -- Bug 6656136
10678: end if; -- p_called_frm_ss
10679: --
10680: if g_debug then
10681: hr_utility.set_location(' Leaving:'||l_proc, 70);
10682: end if;
10683: --
10684: end multi_rows_edit;
10685: --
10724: l_proc varchar2(72); -- := g_package||'update_person_type_usages';
10725: --
10726: Begin
10727:
10728: g_debug := hr_utility.debug_enabled;
10729: if g_debug then
10730: l_proc := g_package||'update_person_type_usages';
10731: hr_utility.set_location('Entering:'||l_proc, 5);
10732: end if;
10727:
10728: g_debug := hr_utility.debug_enabled;
10729: if g_debug then
10730: l_proc := g_package||'update_person_type_usages';
10731: hr_utility.set_location('Entering:'||l_proc, 5);
10732: end if;
10733: --
10734: --
10735: if (g_enrollment_change) then
10745:
10746: g_enrollment_change := FALSE;
10747: --
10748: if g_debug then
10749: hr_utility.set_location(' Leaving:'||l_proc, 10);
10750: end if;
10751: --
10752: End update_person_type_usages;
10753:
10778: begin
10779: fnd_msg_pub.initialize;
10780:
10781: if g_debug then
10782: hr_utility.set_location('Entering:'||l_proc, 5);
10783: end if;
10784:
10785: delete_enrollment
10786: (p_validate => p_validate
10798: ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
10799: ,p_mode => p_mode);
10800:
10801: if g_debug then
10802: hr_utility.set_location('Leaving:'||l_proc, 10);
10803: end if;
10804:
10805: exception
10806: --
10804:
10805: exception
10806: --
10807: when app_exception.application_exception then --Bug 4387247
10808: hr_utility.set_location ('Application Error in delete_enrollment_w.', 88);
10809: fnd_msg_pub.add;
10810: when others then
10811: if g_debug then
10812: hr_utility.set_location('Exception:'||l_proc, 100);
10808: hr_utility.set_location ('Application Error in delete_enrollment_w.', 88);
10809: fnd_msg_pub.add;
10810: when others then
10811: if g_debug then
10812: hr_utility.set_location('Exception:'||l_proc, 100);
10813: end if;
10814: hr_utility.set_location ('Other Error in delete_enrollment_w : '|| sqlerrm , 89);
10815: --Bug 4387247
10816: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
10810: when others then
10811: if g_debug then
10812: hr_utility.set_location('Exception:'||l_proc, 100);
10813: end if;
10814: hr_utility.set_location ('Other Error in delete_enrollment_w : '|| sqlerrm , 89);
10815: --Bug 4387247
10816: fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
10817: fnd_message.set_token('2',substr(sqlerrm,1,200));
10818: fnd_msg_pub.add;