[Home] [Help]
2: /* $Header: bendetdt.pkb 120.36.12020000.3 2012/10/31 08:19:19 amnaraya ship $ */
3: -- --------------------------------------------------------------------
4: -- Get the plan year during the life event or effective date.
5: -- --------------------------------------------------------------------
6: g_debug boolean := hr_utility.debug_enabled;
7: --
8: --
9: -- ----------------------------------------------------------------------------
10: -- |----------------------< get_profile_ff_warn_val >-------------------------|
107: close c_oipl_popl_yr;
108:
109: else
110: if g_debug then
111: hr_utility.set_location('BEN_92489_CANNOT_CALC_DATE',55);
112: end if;
113:
114: fnd_message.set_name('BEN','BEN_92489_CANNOT_CALC_DATE');
115: fnd_message.set_token('DATE_CODE',p_date_cd);
208: l_dummy number;
209: l_national_identifier per_all_people_f.national_identifier%type;
210:
211: begin
212: hr_utility.set_location('Entering validate_rule_cd_date',1000);
213:
214: open c_formula_type_id_name;
215: fetch c_formula_type_id_name into l_formula_type_id,l_formula_type_name;
216: close c_formula_type_id_name;
358: close c_mode; -- Bug fix 5057685. Moved here
359: --
360: end if;
361: end if;
362: hr_utility.set_location('Leaving validate_rule_cd_date',8888);
363: end validate_rule_cd_date;
364:
365: -- --------------------------------------------------------------------
366: -- Get the plan year that starts after the life event or effective date.
427: fetch c_oipl_next_popl_yr into p_start_date,p_end_date;
428: close c_oipl_next_popl_yr;
429: else
430: if g_debug then
431: hr_utility.set_location('BEN_92489_CANNOT_CALC_DATE',55);
432: end if;
433: fnd_message.set_name('BEN','BEN_92489_CANNOT_CALC_DATE');
434: fnd_message.set_token('DATE_CODE',p_date_cd);
435: fnd_message.set_token('L_PROC',l_proc);
480: fetch c_per_in_ler into l_event_date;
481: if c_per_in_ler%notfound then
482: close c_per_in_ler;
483: if g_debug then
484: hr_utility.set_location('BEN_91530_CANNOT_FIND_AED_DATE',40);
485: end if;
486: fnd_message.set_name('BEN','BEN_91530_CANNOT_FIND_AED_DATE');
487: fnd_message.raise_error;
488: end if;
558: fetch c_per_in_ler into l_recorded_date;
559: if c_per_in_ler%notfound then
560: close c_per_in_ler;
561: if g_debug then
562: hr_utility.set_location('BEN_92391_CANT_FIND_RCRD_DATE',40);
563: end if;
564: fnd_message.set_name('BEN','BEN_92391_CANT_FIND_RCRD_DATE');
565: fnd_message.raise_error;
566: end if;
1148: l_assignment_id number;
1149: -----------Bug 8394662
1150: begin
1151:
1152: -- hr_utility.trace_on (null, 'ORACLE');
1153:
1154: g_debug := hr_utility.debug_enabled;
1155: --
1156: -- Commented out for performance
1150: begin
1151:
1152: -- hr_utility.trace_on (null, 'ORACLE');
1153:
1154: g_debug := hr_utility.debug_enabled;
1155: --
1156: -- Commented out for performance
1157: --
1158: --
1155: --
1156: -- Commented out for performance
1157: --
1158: --
1159: -- hr_utility.set_location('Entering :'|| l_proc,10);
1160: -- comment these out until we need to debug a code
1161: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1162: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1163: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1157: --
1158: --
1159: -- hr_utility.set_location('Entering :'|| l_proc,10);
1160: -- comment these out until we need to debug a code
1161: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1162: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1163: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1164: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1158: --
1159: -- hr_utility.set_location('Entering :'|| l_proc,10);
1160: -- comment these out until we need to debug a code
1161: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1162: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1163: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1164: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1159: -- hr_utility.set_location('Entering :'|| l_proc,10);
1160: -- comment these out until we need to debug a code
1161: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1162: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1163: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1164: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1160: -- comment these out until we need to debug a code
1161: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1162: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1163: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1164: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1161: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1162: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1163: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1164: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1169: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1162: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1163: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1164: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1169: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1170: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1163: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1164: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1169: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1170: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1171: -- hr_utility.set_location('person : '||p_person_id ,665);
1164: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1169: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1170: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1171: -- hr_utility.set_location('person : '||p_person_id ,665);
1172: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1165: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1169: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1170: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1171: -- hr_utility.set_location('person : '||p_person_id ,665);
1172: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1173: --
1166: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1169: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1170: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1171: -- hr_utility.set_location('person : '||p_person_id ,665);
1172: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1173: --
1174: --- Fonm2 Determine the fonm and effective date
1167: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1169: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1170: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1171: -- hr_utility.set_location('person : '||p_person_id ,665);
1172: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1173: --
1174: --- Fonm2 Determine the fonm and effective date
1175: l_fonm_rt_cvg_strt_dt := nvl(p_fonm_cvg_strt_dt,p_fonm_rt_strt_dt) ;
1168: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1169: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1170: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1171: -- hr_utility.set_location('person : '||p_person_id ,665);
1172: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1173: --
1174: --- Fonm2 Determine the fonm and effective date
1175: l_fonm_rt_cvg_strt_dt := nvl(p_fonm_cvg_strt_dt,p_fonm_rt_strt_dt) ;
1176: l_lf_evt_ocrd_dt := nvl(l_fonm_rt_cvg_strt_dt, p_lf_evt_ocrd_dt ) ;
1185: -- procedures will have to provide these values: per_in_ler_id,
1186: -- person_id, pl_id, oipl_id, pgm_id.
1187: --
1188: if g_debug then
1189: hr_utility.set_location('open c_per_elig_elctbl_chc',10);
1190: end if;
1191:
1192: open c_per_elig_elctbl_chc;
1193:
1202: l_business_group_id,
1203: l_enrt_cvg_strt_dt;
1204:
1205: if g_debug then
1206: hr_utility.set_location('l_enrt_cvg_strt_dt from c_per_elig_elctbl_chc'||l_enrt_cvg_strt_dt,19);
1207: end if;
1208:
1209: if c_per_elig_elctbl_chc%notfound then
1210: close c_per_elig_elctbl_chc;
1208:
1209: if c_per_elig_elctbl_chc%notfound then
1210: close c_per_elig_elctbl_chc;
1211: if g_debug then
1212: hr_utility.set_location('BEN_91529_CANNOT_FIND_ELEC_CHC',40);
1213: end if;
1214: fnd_message.set_name('BEN','BEN_91529_CANNOT_FIND_ELEC_CHC');
1215: fnd_message.set_token('L_PROC',l_proc);
1216: fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',p_elig_per_elctbl_chc_id);
1250: ,p_organization_id => l_organization_id
1251: ,p_payroll_id => l_payroll_id1
1252: ,p_assignment_id => l_assignment_id);
1253:
1254: hr_utility.set_location('p_acty_base_rt_id : '||p_acty_base_rt_id,10);
1255: hr_utility.set_location('l_payroll_id1 : '||l_payroll_id1,10);
1256: hr_utility.set_location('l_assignment_id : '||l_assignment_id,10);
1257: end if;
1258: ------------Bug 8394662
1251: ,p_payroll_id => l_payroll_id1
1252: ,p_assignment_id => l_assignment_id);
1253:
1254: hr_utility.set_location('p_acty_base_rt_id : '||p_acty_base_rt_id,10);
1255: hr_utility.set_location('l_payroll_id1 : '||l_payroll_id1,10);
1256: hr_utility.set_location('l_assignment_id : '||l_assignment_id,10);
1257: end if;
1258: ------------Bug 8394662
1259: --
1252: ,p_assignment_id => l_assignment_id);
1253:
1254: hr_utility.set_location('p_acty_base_rt_id : '||p_acty_base_rt_id,10);
1255: hr_utility.set_location('l_payroll_id1 : '||l_payroll_id1,10);
1256: hr_utility.set_location('l_assignment_id : '||l_assignment_id,10);
1257: end if;
1258: ------------Bug 8394662
1259: --
1260: -- when the date determinne from pay period for the contact
1272: fetch c_pay_id into l_payroll_id ;
1273: if c_pay_id%notfound then
1274: l_person_id := p_parent_person_id ;
1275: if g_debug then
1276: hr_utility.set_location('for chold prill not found ' ,665);
1277: end if;
1278: end if ;
1279: close c_pay_id ;
1280: end if ;
1279: close c_pay_id ;
1280: end if ;
1281:
1282: if g_debug then
1283: hr_utility.set_location('person : '||l_person_id ,665);
1284: end if;
1285: /*
1286:
1287: -- Check for cache mode. If so then get the per in ler details
1298: --
1299: */
1300:
1301: if g_debug then
1302: hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt,19);
1303: end if;
1304: if g_debug then
1305: hr_utility.set_location('p_date_cd :'||p_date_cd,19);
1306: end if;
1301: if g_debug then
1302: hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt,19);
1303: end if;
1304: if g_debug then
1305: hr_utility.set_location('p_date_cd :'||p_date_cd,19);
1306: end if;
1307:
1308: -- AED - Event Date
1309:
1309:
1310: if p_date_cd in ( 'AED' , 'NUMDOE') then
1311: --
1312: if g_debug then
1313: hr_utility.set_location('Entering AED',10);
1314: end if;
1315: --
1316: if l_lf_evt_ocrd_dt is null then
1317: --
1327: --
1328: elsif p_date_cd = 'WAED' then
1329: --
1330: if g_debug then
1331: hr_utility.set_location('Entering AED',10);
1332: end if;
1333: --
1334: if l_lf_evt_ocrd_dt is null then
1335: --
1366: p_date_cd = '15DAFDEP' or p_date_cd = '20DAFDEP' or
1367: p_date_cd = '25DAFDEP' or p_date_cd = '30DAFDEP' then
1368: --
1369: if g_debug then
1370: hr_utility.set_location('Entering '||p_date_cd,10);
1371: end if;
1372: --
1373: open c_cm_enrt_perd_strt_dt;
1374: fetch c_cm_enrt_perd_strt_dt into l_cm_date;
1382: else
1383: --
1384: close c_cm_enrt_perd_strt_dt;
1385: if g_debug then
1386: hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',40);
1387: end if;
1388: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
1389: fnd_message.set_token('DATE_CODE',p_date_cd);
1390: fnd_message.set_token('L_PROC',l_proc);
1468: or p_date_cd = 'EEELDPPADI' or p_date_cd = 'EEELDMADI' or p_date_cd = 'EEDI'
1469: or p_date_cd = 'OCED' or p_date_cd = 'PECED' then
1470: --
1471: if g_debug then
1472: hr_utility.set_location('Entering '||p_date_cd,23);
1473: end if;
1474: --
1475: if l_lf_evt_ocrd_dt is null then
1476: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
1482: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
1483: l_enrt_cvg_strt_dt,
1484: l_enrt_eff_strt_date;
1485: if g_debug then
1486: hr_utility.set_location('l_enrt_cvg_end_dt cursor returns ', 19);
1487: end if;
1488: close c_prtt_enrt_rslt_dts;
1489: else
1490: l_enrt_cvg_end_dt := p_start_date;
1517:
1518: if c_pay_period_for_date%notfound and l_enrt_eff_strt_date is not null then
1519: close c_pay_period_for_date;
1520: if g_debug then
1521: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',15);
1522: end if;
1523: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1524: fnd_message.set_token('DATE_CODE',p_date_cd);
1525: fnd_message.set_token('L_PROC',l_proc);
1545: -- End of Pay Period (On or) After
1546: if l_enrt_cvg_end_dt <> hr_api.g_eot then
1547: --
1548: if g_debug then
1549: hr_utility.set_location(' Step 2 ' ,19);
1550: end if;
1551: open c_pay_period_for_date(l_enrt_cvg_end_dt);
1552: fetch c_pay_period_for_date into
1553: l_start_date,
1555:
1556: if c_pay_period_for_date%notfound and l_enrt_eff_strt_date is not null then
1557: close c_pay_period_for_date;
1558: if g_debug then
1559: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',15);
1560: end if;
1561: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1562: fnd_message.set_token('DATE_CODE',p_date_cd);
1563: fnd_message.set_token('L_PROC',l_proc);
1569: close c_pay_period_for_date;
1570:
1571: else
1572: if g_debug then
1573: hr_utility.set_location('End of Time set 1 ' , 19 );
1574: end if;
1575: l_end_date := l_enrt_cvg_end_dt ;
1576: end if ;
1577:
1582: if p_date_cd = 'LDPPFEE' then
1583: -- End of Pay Period After
1584: -- retrieve last day of next pay period
1585: if g_debug then
1586: hr_utility.set_location(' Step 3 ' ,19);
1587: end if;
1588: open c_pay_period_for_date(l_end_date+1);
1589: fetch c_pay_period_for_date into
1590: l_start_date,
1593: if c_pay_period_for_date%notfound and
1594: l_enrt_eff_strt_date is not null then
1595: close c_pay_period_for_date;
1596: if g_debug then
1597: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',25);
1598: end if;
1599: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1600: fnd_message.set_token('DATE_CODE',p_date_cd);
1601: fnd_message.set_token('L_PROC',l_proc);
1609: end if ;
1610: --
1611: else
1612: if g_debug then
1613: hr_utility.set_location('End of Time set 2' , 19 );
1614: end if;
1615: l_end_date := l_enrt_cvg_end_dt ;
1616: end if;
1617:
1689: or p_date_cd = 'LESFDPPAD' or p_date_cd = 'LESFDMAD' or p_date_cd = 'FDPPELD'
1690: or p_date_cd = 'FDSMFES' or p_date_cd = 'FDSMCFES' or p_date_cd = 'LESFDSMAD'
1691: then
1692: if g_debug then
1693: hr_utility.set_location('Entering '||p_date_cd,17);
1694: end if;
1695: if g_debug then
1696: hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt , 17);
1697: end if;
1692: if g_debug then
1693: hr_utility.set_location('Entering '||p_date_cd,17);
1694: end if;
1695: if g_debug then
1696: hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt , 17);
1697: end if;
1698:
1699: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL
1700: and p_date_cd in ('LEMES','LFMEMES','LFPPEMES','LFYEMES') ) then
1697: end if;
1698:
1699: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL
1700: and p_date_cd in ('LEMES','LFMEMES','LFPPEMES','LFYEMES') ) then
1701: hr_utility.set_location('l_lf_evt_ocrd_dt bckdt:'||ben_manage_life_events.g_bckdt_per_in_ler_id, 17);
1702: open c_rt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
1703: fetch c_rt_dts_bckdt into l_rt_strt_dt1,
1704: l_rt_end_dt1;
1705: close c_rt_dts_bckdt;
1706:
1707: if l_rt_strt_dt1 is null then
1708: l_rt_strt_dt1 :=p_effective_date;
1709: end if;
1710: hr_utility.set_location('l_rt_strt_dt1: '||l_rt_strt_dt1, 17);
1711: p_returned_date := l_rt_strt_dt1;
1712: return;
1713: end if;
1714:
1714:
1715: if l_lf_evt_ocrd_dt is null then
1716: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
1717: if g_debug then
1718: hr_utility.set_location('In the get_event_date '||l_lf_evt_ocrd_dt,17);
1719: end if ;
1720: end if;
1721:
1722: if g_debug then
1719: end if ;
1720: end if;
1721:
1722: if g_debug then
1723: hr_utility.set_location('before cursor l_enrt_cvg_strt_dt:'||l_enrt_cvg_strt_dt,18);
1724: end if;
1725:
1726: open c_prtt_enrt_rslt_dts;
1727: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
1743: end if;
1744: --
1745: close c_prtt_enrt_rslt_dts;
1746: if g_debug then
1747: hr_utility.set_location('l_enrt_cvg_end_dt :'||l_enrt_cvg_end_dt , 18);
1748: end if;
1749: if g_debug then
1750: hr_utility.set_location('l_enrt_cvg_strt_dt:'||l_enrt_cvg_strt_dt,18);
1751: end if;
1746: if g_debug then
1747: hr_utility.set_location('l_enrt_cvg_end_dt :'||l_enrt_cvg_end_dt , 18);
1748: end if;
1749: if g_debug then
1750: hr_utility.set_location('l_enrt_cvg_strt_dt:'||l_enrt_cvg_strt_dt,18);
1751: end if;
1752: if g_debug then
1753: hr_utility.set_location('l_enrt_eff_strt_date:'||l_enrt_eff_strt_date,18);
1754: end if;
1749: if g_debug then
1750: hr_utility.set_location('l_enrt_cvg_strt_dt:'||l_enrt_cvg_strt_dt,18);
1751: end if;
1752: if g_debug then
1753: hr_utility.set_location('l_enrt_eff_strt_date:'||l_enrt_eff_strt_date,18);
1754: end if;
1755: --
1756: --
1757: if p_date_cd = 'LEMES' or p_date_cd = 'LFPPEMES' or p_date_cd = 'LFMEMES'
1802: then
1803: --
1804: p_returned_date := l_enrt_cvg_strt_dt ;
1805: if g_debug then
1806: hr_utility.set_location('Case 1',15);
1807: end if;
1808: --
1809: elsif to_char(l_enrt_cvg_strt_dt, 'DD') > 15 then
1810: --
1809: elsif to_char(l_enrt_cvg_strt_dt, 'DD') > 15 then
1810: --
1811: p_returned_date := round(l_enrt_cvg_strt_dt,'Month') ;
1812: if g_debug then
1813: hr_utility.set_location('Case 2',15);
1814: end if;
1815: --
1816: else
1817: --
1816: else
1817: --
1818: p_returned_date := round(l_enrt_cvg_strt_dt,'Month') + 15 ;
1819: if g_debug then
1820: hr_utility.set_location('Case 3'||l_enrt_cvg_strt_dt,15);
1821: end if;
1822: if g_debug then
1823: hr_utility.set_location('Case 3'||p_returned_date,15);
1824: end if;
1819: if g_debug then
1820: hr_utility.set_location('Case 3'||l_enrt_cvg_strt_dt,15);
1821: end if;
1822: if g_debug then
1823: hr_utility.set_location('Case 3'||p_returned_date,15);
1824: end if;
1825:
1826: --
1827: end if;
1832: -- First of Pay Period On or After Enrollment
1833: --
1834: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL
1835: and p_date_cd ='FDPPELD') then
1836: hr_utility.set_location('Entering FDPPELD',11);
1837: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
1838: fetch c_prtt_enrt_rslt_dts_bckdt into l_enrt_cvg_end_dt,
1839: l_enrt_cvg_strt_dt,
1840: l_enrt_eff_strt_date;
1859:
1860: if c_pay_period_for_date%notfound and
1861: l_enrt_cvg_strt_dt is not null then
1862: if g_debug then
1863: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',80);
1864: end if;
1865: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1866: fnd_message.set_token('DATE_CODE',p_date_cd);
1867: fnd_message.set_token('L_PROC',l_proc);
1880: and p_returned_date <> l_start_date)
1881: or p_date_cd = 'LFPPEMES' or p_date_cd = 'FDPPFES'
1882: or p_date_cd = 'LESFDPPAD' then
1883: if g_debug then
1884: hr_utility.set_location('LFPPEMES First of Pay Period After',20);
1885: end if;
1886: -- First of Pay Period After
1887: if p_date_cd = 'LESFDPPAD' then
1888: -- need to get pay period after designation, not enrt strt.
1891: open c_next_pay_period(p_returned_date);
1892: fetch c_next_pay_period into l_next_pay_period;
1893:
1894: if g_debug then
1895: hr_utility.set_location('l_next_pay_period.start_date'||l_next_pay_period.start_date,20);
1896: end if;
1897: if c_next_pay_period%notfound and
1898: l_enrt_cvg_strt_dt is not null then
1899: close c_next_pay_period;
1897: if c_next_pay_period%notfound and
1898: l_enrt_cvg_strt_dt is not null then
1899: close c_next_pay_period;
1900: if g_debug then
1901: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',40);
1902: end if;
1903: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1904: fnd_message.set_token('DATE_CODE',p_date_cd);
1905: fnd_message.set_token('L_PROC',l_proc);
1916: p_returned_date := l_enrt_eff_strt_date;
1917: else
1918: p_returned_date := l_next_pay_period.start_date;
1919: if g_debug then
1920: hr_utility.set_location('Else case :'||l_next_pay_period.start_date,20);
1921: end if;
1922: end if;
1923:
1924: elsif p_date_cd = 'LFMEMES' or p_date_cd = 'FDMCFES' or p_date_cd = 'FDMFES'
1961: --
1962: open c_next_pay_period(p_effective_date);
1963: fetch c_next_pay_period into l_next_pay_period;
1964: if g_debug then
1965: hr_utility.set_location('l_next_pay_period.start_date'||l_next_pay_period.start_date,20);
1966: end if;
1967: if c_next_pay_period%notfound then
1968: close c_next_pay_period;
1969: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1984: -- ** FDPPOED - First of Pay Period On or After Effective Date
1985: elsif p_date_cd = 'FDPPOED' then
1986: -- Bug:4268494: Changed the logic. Get the current pay-period start/end dates
1987: -- If start_date <> effective_date, fetch next pay period start/end dates. Return start_date.
1988: hr_utility.set_location('Evaluate FDPPOED',10);
1989: l_event_date := NVL(l_fonm_rt_cvg_strt_dt,p_effective_date);
1990: --
1991: open c_pay_period;
1992: fetch c_pay_period into l_pay_period;
2002: close c_pay_period ;
2003: --
2004: if l_event_date = l_pay_period.start_date then
2005: p_returned_date := l_pay_period.start_date;
2006: hr_utility.set_location('l_pay_period.start_date '||l_pay_period.start_date,10);
2007: else
2008: --
2009: open c_next_pay_period(NVL(l_event_date,p_effective_date));
2010: fetch c_next_pay_period into l_next_pay_period;
2019: fnd_message.raise_error;
2020: end if;
2021: close c_next_pay_period;
2022: p_returned_date := l_next_pay_period.start_date;
2023: hr_utility.set_location('l_pay_period.start_date '|| l_next_pay_period.start_date,20);
2024: --
2025: end if;
2026: --
2027: --
2028: -- ** LDPPOEFD - 1 Prior or End of Pay Period On or After Effective Date
2029: elsif p_date_cd = 'LDPPOEFD' THEN
2030: -- Bug:4268494: Changed the logic. Get the current pay-period start/end dates
2031: -- If end_date <> effective_date, fetch next pay period start/end dates. Return end_date.
2032: hr_utility.set_location('Evaluate LDPPOEFD',10);
2033: l_event_date := NVL(l_fonm_rt_cvg_strt_dt,p_effective_date);
2034: --
2035: open c_pay_period;
2036: fetch c_pay_period into l_pay_period;
2046: close c_pay_period ;
2047: --
2048: if l_event_date = l_pay_period.end_date then
2049: p_returned_date := l_pay_period.end_date;
2050: hr_utility.set_location('l_pay_period.end_date '|| l_pay_period.end_date,20);
2051: else
2052: --
2053: open c_next_pay_period(NVL(l_event_date,p_effective_date));
2054: fetch c_next_pay_period into l_next_pay_period;
2063: fnd_message.raise_error;
2064: end if;
2065: close c_next_pay_period;
2066: p_returned_date := l_next_pay_period.end_date;
2067: hr_utility.set_location('l_pay_period.end_date '|| l_next_pay_period.end_date,20);
2068: --
2069: end if;
2070: --
2071: -- AFDCSMFDFFNSM First of next Semi month,if from day is first,
2211: --
2212: elsif p_date_cd = 'LWALDCSM' then
2213: -- --
2214: if g_debug then
2215: hr_utility.set_location('Entering LWALDCSM',10);
2216: end if;
2217: --
2218: if l_lf_evt_ocrd_dt is null then
2219: --
2252:
2253: elsif p_date_cd = 'WALDCSM' then
2254: -- --
2255: if g_debug then
2256: hr_utility.set_location('Entering WALDCSM',10);
2257: end if;
2258: --
2259: if l_lf_evt_ocrd_dt is null then
2260: --
2272: p_returned_date := trunc(l_event_date,'Month') + 14 ;
2273: end if;
2274: --
2275: if g_debug then
2276: hr_utility.set_location('p_returned_date '||p_returned_date , 19 );
2277: end if;
2278: --
2279: --
2280: -- EEELDNSMADI -- Earlier Participant Enrollment End or Next Semi Month End
2281: -- for dependent coverage end date
2282: elsif p_date_cd = 'EEELDNSMADI' then
2283: --
2284: if g_debug then
2285: hr_utility.set_location('Entering EEELDNSMADI',10);
2286: end if;
2287: --
2288: --
2289: -- p_effective_date -- Designated Date
2322: elsif p_date_cd = 'ALDPSM' then
2323: --
2324: --
2325: if g_debug then
2326: hr_utility.set_location('Entering ALDPSM',10);
2327: end if;
2328: --
2329: if l_lf_evt_ocrd_dt is null then
2330: --
2349: elsif p_date_cd = 'LALDPSM' then
2350: --
2351: --
2352: if g_debug then
2353: hr_utility.set_location('Entering ALDPSM',10);
2354: end if;
2355: --
2356: if l_lf_evt_ocrd_dt is null then
2357: --
2396: --
2397: elsif p_date_cd = 'ALDCSM' then
2398: --
2399: if g_debug then
2400: hr_utility.set_location('Entering ALDCSM',10);
2401: end if;
2402: --
2403: if l_lf_evt_ocrd_dt is null then
2404: --
2420: --
2421: end if;
2422: --
2423: if g_debug then
2424: hr_utility.set_location('p_returned_date '||p_returned_date , 199) ;
2425: end if;
2426: --
2427: -- LALDCSM End of Semi-month later event or Notified.
2428: --
2428: --
2429: elsif p_date_cd = 'LALDCSM' then
2430: --
2431: if g_debug then
2432: hr_utility.set_location('Entering LALDCSM',10);
2433: end if;
2434: --
2435: if l_lf_evt_ocrd_dt is null then
2436: --
2473: --
2474: elsif p_date_cd = 'AFDFSM' then
2475: --
2476: if g_debug then
2477: hr_utility.set_location('Entering AFDFSM',10);
2478: end if;
2479: --
2480: if l_lf_evt_ocrd_dt is null then
2481: --
2497: --
2498: elsif p_date_cd = 'AFDCSM' then
2499: --
2500: if g_debug then
2501: hr_utility.set_location('Entering AFDCSM',10);
2502: end if;
2503: --
2504: if l_lf_evt_ocrd_dt is null then
2505: --
2552: or p_date_cd = 'FDSMFC' or p_date_cd = 'FDSMCFC'
2553: then
2554:
2555: if g_debug then
2556: hr_utility.set_location('Entering '||p_date_cd,14);
2557: end if;
2558:
2559: l_lf_evt_ocrd_dt := nvl(l_fonm_rt_cvg_strt_dt, p_effective_date) ; -- completion date.
2560:
2567: --
2568: elsif p_date_cd = 'FDSMFC' then
2569: -- First of Semi Month after Completion
2570: if g_debug then
2571: hr_utility.set_location('Entering FDSMFC',10);
2572: end if;
2573: --
2574: /*
2575: if to_char(l_lf_evt_ocrd_dt, 'DD') > 15 then
2586: --
2587: elsif p_date_cd = 'FDSMCFC' then
2588: -- First of Semi Month on or after Completion
2589: if g_debug then
2590: hr_utility.set_location('Entering FDSMCFC',10);
2591: end if;
2592: --
2593: /*
2594: if to_number(to_char(l_lf_evt_ocrd_dt, 'DD')) in ( 1, 16 ) then
2668: if c_pay_period_for_date%notfound and
2669: p_effective_date is not null then
2670: close c_pay_period_for_date;
2671: if g_debug then
2672: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',45);
2673: end if;
2674: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
2675: fnd_message.set_token('DATE_CODE',p_date_cd);
2676: fnd_message.set_token('L_PROC',l_proc);
2696: if c_next_pay_period%notfound and
2697: p_effective_date is not null then
2698: close c_next_pay_period;
2699: if g_debug then
2700: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',55);
2701: end if;
2702: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
2703: fnd_message.set_token('DATE_CODE',p_date_cd);
2704: fnd_message.set_token('L_PROC',l_proc);
2719: --
2720: elsif p_date_cd = 'AFDELY' then
2721: --
2722: if g_debug then
2723: hr_utility.set_location('Entering AFDELY',10);
2724: end if;
2725: --
2726: if l_lf_evt_ocrd_dt is null then
2727: --
2751: close c_oipl_popl_lim_yr;
2752: --
2753: else
2754: if g_debug then
2755: hr_utility.set_location('BEN_92489_CANNOT_CALC_DATE',55);
2756: end if;
2757: fnd_message.set_name('BEN','BEN_92489_CANNOT_CALC_DATE');
2758: fnd_message.set_token('DATE_CODE',p_date_cd);
2759: fnd_message.set_token('L_PROC',l_proc);
2767: --
2768: elsif p_date_cd = 'ALDELMY' then
2769: --
2770: if g_debug then
2771: hr_utility.set_location('Entering ALDELMY',10);
2772: end if;
2773: --
2774: if l_lf_evt_ocrd_dt is null then
2775: --
2798: fetch c_oipl_popl_lim_yr into l_start_date, l_end_date;
2799: close c_oipl_popl_lim_yr;
2800: --
2801: else
2802: -- hr_utility.set_location('BEN_92489_CANNOT_CALC_DATE',55);
2803: fnd_message.set_name('BEN','BEN_92489_CANNOT_CALC_DATE');
2804: fnd_message.set_token('DATE_CODE',p_date_cd);
2805: fnd_message.set_token('L_PROC',l_proc);
2806: fnd_message.raise_error;
2812: -- FDODD - Within 5 Days After Due
2813: --
2814: elsif p_date_cd = 'FDODD' then
2815: --
2816: -- hr_utility.set_location('Entering FDODD',10);
2817: --
2818: p_returned_date:= p_effective_date + 5;
2819: --
2820: -- FRTYFV - Within 45 Days After Due
2820: -- FRTYFV - Within 45 Days After Due
2821: --
2822: elsif p_date_cd = 'FRTYFV' then
2823: --
2824: -- hr_utility.set_location('Entering FRTYFV',10);
2825: --
2826: p_returned_date:= p_effective_date + 45;
2827: ---
2828: -- TDODD - Within 10 Days after Due
2828: -- TDODD - Within 10 Days after Due
2829: --
2830: elsif p_date_cd = 'TDODD' then
2831: --
2832: -- hr_utility.set_location('Entering TDODD',10);
2833: --
2834: p_returned_date:= p_effective_date + 10;
2835: --
2836: -- THRTY - Within 30 Days after Due
2836: -- THRTY - Within 30 Days after Due
2837: --
2838: elsif p_date_cd = 'THRTY' then
2839: --
2840: -- hr_utility.set_location('Entering THRTY',10);
2841: --
2842: p_returned_date:= p_effective_date + 30;
2843: --
2844: -- THRTYONE - Within 31 Days after Due
2844: -- THRTYONE - Within 31 Days after Due
2845: --
2846: elsif p_date_cd = 'THRTYONE' then
2847: --
2848: -- hr_utility.set_location('Entering THRTYONE',10);
2849: --
2850: p_returned_date:= p_effective_date + 31;
2851: --
2852: -- DO - Date Occurred
2852: -- DO - Date Occurred
2853: --
2854: elsif p_date_cd = 'DO' then
2855: --
2856: -- hr_utility.set_location('Entering DO',10);
2857:
2858: p_returned_date:= p_effective_date;
2859: --
2860: -- DR - Date Recorded or Notified
2860: -- DR - Date Recorded or Notified
2861: --
2862: elsif p_date_cd in ('DR','ODNPE') then
2863: --
2864: -- hr_utility.set_location('Entering DR',10);
2865:
2866: p_returned_date:= sysdate;
2867: --
2868: -- LOR - Later Occurred Date or Recorded Date.
2868: -- LOR - Later Occurred Date or Recorded Date.
2869: --
2870: elsif p_date_cd = 'LOR' then
2871: --
2872: -- hr_utility.set_location('Entering LOR',10);
2873: --
2874: if sysdate > nvl(l_lf_evt_ocrd_dt,hr_api.g_sot) then
2875: p_returned_date := sysdate;
2876: else
2883: --
2884: --
2885: elsif p_date_cd = 'DAO' or p_date_cd = 'ODAED' then
2886: --
2887: -- hr_utility.set_location('Entering DAO',10);
2888: -- hr_utility.set_location('Entering ODAED',10);
2889: --
2890: --p_returned_date := p_effective_date + 1;
2891: if l_lf_evt_ocrd_dt is null then
2884: --
2885: elsif p_date_cd = 'DAO' or p_date_cd = 'ODAED' then
2886: --
2887: -- hr_utility.set_location('Entering DAO',10);
2888: -- hr_utility.set_location('Entering ODAED',10);
2889: --
2890: --p_returned_date := p_effective_date + 1;
2891: if l_lf_evt_ocrd_dt is null then
2892: --
2905: -- or Recorded Date.
2906: --
2907: elsif p_date_cd = 'LDAOR' then
2908: --
2909: -- hr_utility.set_location('Entering LDAOR',10);
2910: --
2911: if sysdate > (nvl(l_lf_evt_ocrd_dt,hr_api.g_sot) + 1) then
2912: p_returned_date := sysdate;
2913: else
2917: -- ALDECLY - End of Calendar Year
2918: --
2919: elsif p_date_cd = 'ALDECLY' then
2920: --
2921: -- hr_utility.set_location('Entering ALDECLY',10);
2922: --
2923: if l_lf_evt_ocrd_dt is null then
2924: --
2925: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2937: -- FFDFED - 45 Days After Event
2938: --
2939: elsif p_date_cd = 'FFDFED' then
2940: --
2941: -- hr_utility.set_location('Entering FFDFED',10);
2942: --
2943: if l_lf_evt_ocrd_dt is null then
2944: --
2945: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2955: -- NDFED - 90 Days After Event
2956: --
2957: elsif p_date_cd = 'NDFED' then
2958: --
2959: -- hr_utility.set_location('Entering NDFED',10);
2960: --
2961: if l_lf_evt_ocrd_dt is null then
2962: --
2963: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2973: -- LFFDFED - 45 Days After Later Event or Notified
2974: --
2975: elsif p_date_cd = 'LFFDFED' then
2976: --
2977: -- hr_utility.set_location('Entering LFFDFED',10);
2978: --
2979: if l_lf_evt_ocrd_dt is null then
2980: --
2981: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3003: -- LNDFED - 90 Days After Later Event or Notified
3004: --
3005: elsif p_date_cd = 'LNDFED' then
3006: --
3007: -- hr_utility.set_location('Entering LNDFED',10);
3008: --
3009: if l_lf_evt_ocrd_dt is null then
3010: --
3011: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3033: -- LTDFED - 30 Days After Later Event or Notified
3034: --
3035: elsif p_date_cd = 'LTDFED' then
3036: --
3037: -- hr_utility.set_location('Entering LTDFED',10);
3038: --
3039: if l_lf_evt_ocrd_dt is null then
3040: --
3041: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3063: -- ALDPM - End of Previous Month
3064: --
3065: elsif p_date_cd = 'ALDPM' then
3066:
3067: -- hr_utility.set_location('Entering ALDPM',10);
3068:
3069: if l_lf_evt_ocrd_dt is null then
3070:
3071: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3083: -- LALDPM - End of Previous Month Later Event or Notified
3084:
3085: elsif p_date_cd = 'LALDPM' then
3086:
3087: -- hr_utility.set_location('Entering LALDPM',10);
3088:
3089: if l_lf_evt_ocrd_dt is null then
3090:
3091: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3112: -- ALDPPP - End of Previous Pay period
3113:
3114: elsif p_date_cd = 'ALDPPP' then
3115: --
3116: -- hr_utility.set_location('Entering ALDPPP',10);
3117:
3118: if l_lf_evt_ocrd_dt is null then
3119:
3120: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3130:
3131: if c_pay_period%notfound then
3132:
3133: close c_pay_period;
3134: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',65);
3135: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3136: fnd_message.set_token('DATE_CODE',p_date_cd);
3137: fnd_message.set_token('L_PROC',l_proc);
3138: fnd_message.set_token('PERSON_ID',l_person_id);
3149: -- LALDPPP - End of Previous Pay period Later Event or Notified
3150:
3151: elsif p_date_cd = 'LALDPPP' then
3152: --
3153: -- hr_utility.set_location('Entering LALDPPP',10);
3154:
3155: if l_lf_evt_ocrd_dt is null then
3156:
3157: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3177:
3178: if c_pay_period%notfound then
3179:
3180: close c_pay_period;
3181: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',70);
3182: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3183: fnd_message.set_token('DATE_CODE',p_date_cd);
3184: fnd_message.set_token('L_PROC',l_proc);
3185: fnd_message.set_token('PERSON_ID',l_person_id);
3198: -- Later Event or Notified
3199:
3200: elsif p_date_cd = 'LALDPPPY' then
3201:
3202: -- hr_utility.set_location('Entering LALDPPPY',10);
3203:
3204: if l_lf_evt_ocrd_dt is null then
3205:
3206: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3267: or p_date_cd = 'AFDCPPQ' or p_date_cd = 'LAFDFPPQ' or p_date_cd = 'ALDPPPQ'
3268: or p_date_cd = 'LALDPPPQ' or p_date_cd = 'LALDCPPQ' or p_date_cd = 'LFDPPQCF'
3269: then
3270:
3271: -- hr_utility.set_location('Entering '||p_date_cd,12);
3272:
3273: if l_lf_evt_ocrd_dt is null then
3274: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3275: end if;
3374:
3375: -- SDBED - Sixty days Before Event Date
3376: elsif p_date_cd = 'SDBED' then
3377:
3378: -- hr_utility.set_location('Entering SDBED',10);
3379:
3380: if l_lf_evt_ocrd_dt is null then
3381:
3382: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3422: or p_date_cd = 'LAFDFPPSY' or p_date_cd = 'AFDFPPSY' or p_date_cd = 'ALDCPPSY'
3423: or p_date_cd = 'LALDCPPSY' or p_date_cd = 'ALDPPPSY' or p_date_cd = 'LALDPPPSY'
3424: then
3425:
3426: -- hr_utility.set_location('Entering '||p_date_cd,22);
3427:
3428: if l_lf_evt_ocrd_dt is null then
3429: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3430: end if;
3500:
3501: -- ODCED - Dependent Coverage End
3502: elsif p_date_cd = 'ODCED' then
3503:
3504: -- hr_utility.set_location('Entering ODCED',10);
3505:
3506: if l_lf_evt_ocrd_dt is null then
3507:
3508: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3521: -- ODCSD - Dependent Coverage End
3522:
3523: elsif p_date_cd = 'ODCSD' then
3524:
3525: -- hr_utility.set_location('Entering ODCSD',10);
3526:
3527: if l_lf_evt_ocrd_dt is null then
3528:
3529: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3541: -- AFDECY - First of Calendar year
3542:
3543: elsif p_date_cd = 'AFDECY' then
3544:
3545: -- hr_utility.set_location('Entering AFDECY',10);
3546:
3547: if l_lf_evt_ocrd_dt is null then
3548:
3549: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3555: -- 30DANED - 30 Days After Notice to Enroll
3556:
3557: elsif p_date_cd = '30DANED' then
3558:
3559: -- hr_utility.set_location('Entering 30DANED',10);
3560:
3561: l_recorded_date := get_recorded_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3562:
3563: p_returned_date := l_recorded_date + 30;
3568: --Case DOB 08/01/1935 , Event 08/03/2000 , return date should be 09/01/2000
3569:
3570: elsif p_date_cd = 'AFDCMFDFFNM' then
3571:
3572: -- hr_utility.set_location('Entering AFDCMFDFFNM',10);
3573: /*Bug 13695553: l_event_date has to be set irrespective of the l_lf_evt_ocrd_dt is null or
3574: not null
3575: if l_lf_evt_ocrd_dt is null then*/
3576:
3617: -- DOD - Date of Determination
3618: --
3619: elsif p_date_cd = 'DOD' then
3620: --
3621: -- hr_utility.set_location('Entering DOD',10);
3622: --
3623: if l_lf_evt_ocrd_dt is null then
3624: --
3625: p_returned_date:= p_effective_date;
3634: -- DODM1 - Date of Determination Minus 1 day
3635: --
3636: elsif p_date_cd = 'DODM1' then
3637: --
3638: -- hr_utility.set_location('Entering DODM1',10);
3639: --
3640: if l_lf_evt_ocrd_dt is null then
3641: --
3642: p_returned_date:= p_effective_date - 1;
3652: -- Concurrent with or Following
3653: --
3654: elsif p_date_cd = 'FDLMEPPYCF' then
3655: --
3656: -- hr_utility.set_location('Entering FDLMEPPYCF',10);
3657: --
3658: if l_lf_evt_ocrd_dt is null then
3659: --
3660: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3697: -- ALDPPPY - Last Day of previous Program or Plan Year
3698: --
3699: elsif p_date_cd = 'ALDPPPY' then
3700: --
3701: -- hr_utility.set_location('Entering ALDPPPY',10);
3702: --
3703: if l_lf_evt_ocrd_dt is null then
3704: --
3705: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3723: -- ALDLPPEPPY - Last Day Last Pay period of Event Program or plan Year
3724: --
3725: elsif p_date_cd = 'ALDLPPEPPY' then
3726: --
3727: -- hr_utility.set_location('Entering ALDLPPEPPY',10);
3728: --
3729: if l_lf_evt_ocrd_dt is null then
3730:
3731: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3749: l_start_date,
3750: l_end_date;
3751: --
3752: if c_pay_period_for_date%notfound then
3753: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',95);
3754: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3755: fnd_message.set_token('DATE_CODE',p_date_cd);
3756: fnd_message.set_token('L_PROC',l_proc);
3757: fnd_message.set_token('PERSON_ID',l_person_id);
3768: -- FDLPPEPPY - First Day Last Pay period of Event Program or plan Year
3769: --
3770: elsif p_date_cd = 'FDLPPEPPY' then
3771: --
3772: -- hr_utility.set_location('Entering FDLPPEPPY',10);
3773: --
3774: if l_lf_evt_ocrd_dt is null then
3775: --
3776: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3793: l_start_date,
3794: l_end_date;
3795: --
3796: if c_pay_period_for_date%notfound then
3797: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',100);
3798: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3799: fnd_message.set_token('DATE_CODE',p_date_cd);
3800: fnd_message.set_token('L_PROC',l_proc);
3801: fnd_message.set_token('PERSON_ID',l_person_id);
3812: -- FDLPPEPPYCF - First Day Last Pay period in Year on or after event
3813: --
3814: elsif p_date_cd = 'FDLPPEPPYCF' then
3815: --
3816: -- hr_utility.set_location('Entering FDLPPEPPYCF',10);
3817: --
3818: if l_lf_evt_ocrd_dt is null then
3819: --
3820: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3837: l_start_date,
3838: l_end_date;
3839: --
3840: if c_pay_period_for_date%notfound then
3841: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',105);
3842: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3843: fnd_message.set_token('DATE_CODE',p_date_cd);
3844: fnd_message.set_token('L_PROC',l_proc);
3845: fnd_message.set_token('PERSON_ID',l_person_id);
3855: -- ODBED - One day before Event Date
3856: --
3857: elsif p_date_cd = 'ODBED' then
3858: --
3859: -- hr_utility.set_location('Entering ODBED WODBED OR WEM',10);
3860: --
3861: if l_lf_evt_ocrd_dt is null then
3862: --
3863: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3874: --
3875: elsif p_date_cd = 'WEM' then
3876: --
3877: if g_debug then
3878: hr_utility.set_location('Entering WEM',10);
3879: end if;
3880: --
3881: if l_lf_evt_ocrd_dt is null then
3882: --
3894: --
3895: elsif p_date_cd = 'WODBED' then
3896: --
3897: if g_debug then
3898: hr_utility.set_location('Entering WODBED',10);
3899: end if;
3900: --
3901: --
3902: if l_lf_evt_ocrd_dt is null then
3914: -- AFDCM - First day of Current Month
3915: --
3916: elsif p_date_cd = 'AFDCM' then
3917: --
3918: -- hr_utility.set_location('Entering AFDCM',10);
3919: --
3920: if l_lf_evt_ocrd_dt is null then
3921: --
3922: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3936: --
3937: elsif p_date_cd = 'ALDCM' then
3938: --
3939: if g_debug then
3940: hr_utility.set_location('Entering ALDCM',10);
3941: end if;
3942: --
3943: if l_lf_evt_ocrd_dt is null then
3944: --
3956: --
3957: elsif p_date_cd = 'WALDCM' then
3958: --
3959: if g_debug then
3960: hr_utility.set_location('Entering WALDCM',10);
3961: end if;
3962: --
3963: if l_lf_evt_ocrd_dt is null then
3964: --
3976: -- ALDFM - Last day of Following Month
3977: --
3978: elsif p_date_cd = 'ALDFM' then
3979: --
3980: -- hr_utility.set_location('Entering ALDFM',10);
3981: --
3982: if l_lf_evt_ocrd_dt is null then
3983: --
3984: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3991: -- AEOT - As of End of Time
3992: --
3993: elsif p_date_cd = 'AEOT' then
3994: --
3995: -- hr_utility.set_location('Entering AEOT',10);
3996: --
3997: p_returned_date := to_date('31-12-4712','DD-MM-YYYY');
3998: --
3999: -- A30DFPSD - As of 30 days from the participation start date
3999: -- A30DFPSD - As of 30 days from the participation start date
4000: --
4001: elsif p_date_cd = 'A30DFPSD' then
4002: --
4003: -- hr_utility.set_location('Entering A30DFPSD',10);
4004: --
4005: p_returned_date := p_start_date+30;
4006: --
4007: -- A45DFPSD - As of 45 days from the participation start date
4007: -- A45DFPSD - As of 45 days from the participation start date
4008: --
4009: elsif p_date_cd = 'A45DFPSD' then
4010: --
4011: -- hr_utility.set_location('Entering A45DFPSD',10);
4012: --
4013: p_returned_date := p_start_date+45;
4014: --
4015: -- A60DFPSD - As of 60 days from the participation start date
4015: -- A60DFPSD - As of 60 days from the participation start date
4016: --
4017: elsif p_date_cd = 'A60DFPSD' then
4018: --
4019: -- hr_utility.set_location('Entering A60DFPSD',10);
4020: --
4021: p_returned_date := p_start_date+60;
4022: --
4023: -- A12MFPSD - As of 12 months from the participation start date
4023: -- A12MFPSD - As of 12 months from the participation start date
4024: --
4025: elsif p_date_cd = 'A12MFPSD' then
4026: --
4027: -- hr_utility.set_location('Entering A12MFPSD',10);
4028: --
4029: p_returned_date := add_months(p_start_date,12);
4030: --
4031: -- A18MFPSD - As of 18 months from the participation start date
4031: -- A18MFPSD - As of 18 months from the participation start date
4032: --
4033: elsif p_date_cd = 'A18MFPSD' then
4034: --
4035: -- hr_utility.set_location('Entering A18MFPSD',10);
4036: --
4037: p_returned_date := add_months(p_start_date,18);
4038: --
4039: -- A1MFPSD - As of 1 months from the participation start date
4039: -- A1MFPSD - As of 1 months from the participation start date
4040: --
4041: elsif p_date_cd = 'A1MFPSD' then
4042: --
4043: -- hr_utility.set_location('Entering A1MFPSD',10);
4044: --
4045: p_returned_date := add_months(p_start_date,1);
4046: --
4047: -- A29MFPSD - As of 29 months from the participation start date
4047: -- A29MFPSD - As of 29 months from the participation start date
4048: --
4049: elsif p_date_cd = 'A29MFPSD' then
4050: --
4051: -- hr_utility.set_location('Entering A29MFPSD',10);
4052: --
4053: p_returned_date := add_months(p_start_date,29);
4054: --
4055: -- A36MFPSD - As of 36 months from the participation start date
4055: -- A36MFPSD - As of 36 months from the participation start date
4056: --
4057: elsif p_date_cd = 'A36MFPSD' then
4058: --
4059: -- hr_utility.set_location('Entering A36MFPSD',10);
4060: --
4061: p_returned_date := add_months(p_start_date,36);
4062: --
4063: -- As of First Day of Following Month
4063: -- As of First Day of Following Month
4064: --
4065: elsif p_date_cd = 'AFDFM' then
4066: --
4067: -- hr_utility.set_location('Entering AFDFM',10);
4068: --
4069: if l_lf_evt_ocrd_dt is null then
4070: --
4071: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4081: -- As of First Day of Following Month after 15 days
4082: --
4083: elsif p_date_cd = 'AFDFM15' then
4084: --
4085: -- hr_utility.set_location('Entering AFDFM15',10);
4086: --
4087: if l_lf_evt_ocrd_dt is null then
4088: --
4089: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4099: -- ALDCPPY - Last day of current program or plan year
4100: --
4101: elsif p_date_cd = 'ALDCPPY' then
4102: --
4103: -- hr_utility.set_location('Entering ALDCPPY',10);
4104: --
4105: if l_lf_evt_ocrd_dt is null then
4106: --
4107: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4126: --
4127: elsif p_date_cd = 'WALDCPPY' then
4128: --
4129: if g_debug then
4130: hr_utility.set_location('Entering ALDCPPY',10);
4131: end if;
4132: --
4133: --
4134: if l_lf_evt_ocrd_dt is null then
4154: -- FDLMPPY - First Day Last Month of current Program or Pl Year
4155: --
4156: elsif p_date_cd = 'FDLMPPY' then
4157: --
4158: -- hr_utility.set_location('Entering FDLMPPY',10);
4159: --
4160: if l_lf_evt_ocrd_dt is null then
4161: --
4162: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4180: -- AFDCPPY - First day of current program or plan year
4181: --
4182: elsif p_date_cd = 'AFDCPPY' then
4183: --
4184: -- hr_utility.set_location('Entering AFDCPPY',10);
4185: --
4186: if l_lf_evt_ocrd_dt is null then
4187: --
4188: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4206: -- AFDFPPY - First day of following program or plan year
4207: --
4208: elsif p_date_cd = 'AFDFPPY' then
4209: --
4210: -- hr_utility.set_location('Entering AFDFPPY',10);
4211: --
4212: if l_lf_evt_ocrd_dt is null then
4213: --
4214: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4232: -- AFDCPP - First day of current pay period
4233: --
4234: elsif p_date_cd = 'AFDCPP' then
4235: --
4236: -- hr_utility.set_location('Entering AFDCPP',10);
4237: --
4238: if l_lf_evt_ocrd_dt is null then
4239: --
4240: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4246: --
4247: if c_pay_period%notfound then
4248: --
4249: close c_pay_period;
4250: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',110);
4251: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4252: fnd_message.set_token('DATE_CODE',p_date_cd);
4253: fnd_message.set_token('L_PROC',l_proc);
4254: fnd_message.set_token('PERSON_ID',l_person_id);
4266: --
4267: elsif p_date_cd = 'ALDCPP' then
4268: --
4269: if g_debug then
4270: hr_utility.set_location('Entering ALDCPP',10);
4271: end if;
4272: --
4273: if l_lf_evt_ocrd_dt is null then
4274: --
4280: fetch c_pay_period into l_pay_period;
4281: --
4282: if c_pay_period%notfound then
4283: close c_pay_period;
4284: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',115);
4285: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4286: fnd_message.set_token('DATE_CODE',p_date_cd);
4287: fnd_message.set_token('L_PROC',l_proc);
4288: fnd_message.set_token('PERSON_ID',l_person_id);
4297: --
4298: elsif p_date_cd = 'WALDCPP' then
4299: --
4300: if g_debug then
4301: hr_utility.set_location('Entering WALDCPP',10);
4302: end if;
4303: --
4304: --
4305: if l_lf_evt_ocrd_dt is null then
4313: --
4314: if c_pay_period%notfound then
4315: close c_pay_period;
4316: if g_debug then
4317: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',115);
4318: end if;
4319: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4320: fnd_message.set_token('DATE_CODE',p_date_cd);
4321: fnd_message.set_token('L_PROC',l_proc);
4332: -- AFDFPP - First day of following pay period
4333: --
4334: elsif p_date_cd = 'AFDFPP' then
4335: --
4336: -- hr_utility.set_location('Entering AFDFPP',10);
4337: --
4338: if l_lf_evt_ocrd_dt is null then
4339: --
4340: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4345: fetch c_next_pay_period into l_next_pay_period;
4346: --
4347: if c_next_pay_period%notfound then
4348: close c_next_pay_period;
4349: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',120);
4350: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4351: fnd_message.set_token('DATE_CODE',p_date_cd);
4352: fnd_message.set_token('PERSON_ID',l_person_id);
4353: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4362: --
4363: elsif p_date_cd = 'APOCT1' then
4364: -- tilak
4365: --
4366: -- hr_utility.set_location('Entering APOCT1',10);
4367: --
4368: if l_lf_evt_ocrd_dt is null then
4369: --
4370: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4387: -- Set to first of month of October
4388: --
4389: p_returned_date := last_day(l_date)+1;
4390: if g_debug then
4391: hr_utility.set_location(' APOCT1 date : ' || p_returned_date , 450) ;
4392: end if;
4393: -- tilak
4394:
4395: -- OMFED - One Month Afl_event_date p_date_cd = 'OMFED' then
4395: -- OMFED - One Month Afl_event_date p_date_cd = 'OMFED' then
4396: --
4397: elsif p_date_cd = 'OMFED' then
4398: --
4399: -- hr_utility.set_location('Entering OMFED',10);
4400: --
4401: if l_lf_evt_ocrd_dt is null then
4402: --
4403: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4413: -- TMFED - Two Months After Event Date
4414: --
4415: elsif p_date_cd = 'TMFED' then
4416: --
4417: -- hr_utility.set_location('Entering TMFED',10);
4418: --
4419: if l_lf_evt_ocrd_dt is null then
4420: --
4421: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4431: -- 30DFLED - Thirty days from Life Event Date
4432: --
4433: elsif p_date_cd = '30DFLED' then
4434: --
4435: -- hr_utility.set_location('Entering 30DFLED',10);
4436: --
4437: if l_lf_evt_ocrd_dt is null then
4438: --
4439: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4449: -- TDBED - Thirty days before event date
4450: --
4451: elsif p_date_cd = 'TDBED' then
4452: --
4453: -- hr_utility.set_location('Entering TDBED',10);
4454: --
4455: if l_lf_evt_ocrd_dt is null then
4456: --
4457: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4467: -- LTDBED - Later: Thirty days before event date or notified
4468: --
4469: elsif p_date_cd = 'LTDBED' then
4470: --
4471: -- hr_utility.set_location('Entering LTDBED',10);
4472: --
4473: if l_lf_evt_ocrd_dt is null then
4474: --
4475: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4505: -- SDFED - Sixty days After Event Date
4506: --
4507: elsif p_date_cd = 'SDFED' then
4508: --
4509: -- hr_utility.set_location('Entering SDFED',10);
4510: --
4511: if l_lf_evt_ocrd_dt is null then
4512: --
4513: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4525: -- LSDBED - Later: Sixty days before Event Date
4526: --
4527: elsif p_date_cd = 'LSDBED' then
4528: --
4529: -- hr_utility.set_location('Entering LSDBED',10);
4530: --
4531: if l_lf_evt_ocrd_dt is null then
4532: --
4533: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4563: -- TODFED - Thirty-one days After Event Date
4564: --
4565: elsif p_date_cd = 'TODFED' then
4566: --
4567: -- hr_utility.set_location('Entering TODFED',10);
4568: --
4569: if l_lf_evt_ocrd_dt is null then
4570: --
4571: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4581: -- LEPPPED - Later of Enrollment Period End Date and Processing End Date.
4582: --
4583: elsif p_date_cd = 'LEPPPED' then
4584: --
4585: -- hr_utility.set_location('Entering LEPPPED',10);
4586: --
4587: open c_pil_popl;
4588: fetch c_pil_popl into l_enrt_end_dt, l_procg_end_dt;
4589: --
4588: fetch c_pil_popl into l_enrt_end_dt, l_procg_end_dt;
4589: --
4590: if c_pil_popl%notfound then
4591: close c_pil_popl;
4592: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',81);
4593: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4594: fnd_message.set_token('DATE_CODE',p_date_cd);
4595: fnd_message.set_token('L_PROC',l_proc);
4596: fnd_message.raise_error;
4607: elsif p_date_cd = '5DBEEPD' then
4608: --
4609: -- 5DBEEPD - 5 Days before the end of the enrollment period end date
4610: --
4611: -- hr_utility.set_location('Entering 5DBEEPD',10);
4612: --
4613: open c_cm_enrt_perd_end_dt;
4614: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4615: if c_cm_enrt_perd_end_dt%notfound then
4613: open c_cm_enrt_perd_end_dt;
4614: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4615: if c_cm_enrt_perd_end_dt%notfound then
4616: close c_cm_enrt_perd_end_dt;
4617: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',86);
4618: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4619: fnd_message.set_token('DATE_CODE',p_date_cd);
4620: fnd_message.set_token('L_PROC',l_proc);
4621: fnd_message.raise_error;
4629: elsif p_date_cd = '10DBEEPD' then
4630: --
4631: -- 10DBEEPD - 10 Days before the end of the enrollment period end date
4632: --
4633: -- hr_utility.set_location('Entering 10DBEEPD',10);
4634: --
4635: open c_cm_enrt_perd_end_dt;
4636: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4637: if c_cm_enrt_perd_end_dt%notfound then
4635: open c_cm_enrt_perd_end_dt;
4636: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4637: if c_cm_enrt_perd_end_dt%notfound then
4638: close c_cm_enrt_perd_end_dt;
4639: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',87);
4640: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4641: fnd_message.set_token('DATE_CODE',p_date_cd);
4642: fnd_message.set_token('L_PROC',l_proc);
4643: fnd_message.raise_error;
4650: elsif p_date_cd = '20DBEEPD' then
4651: --
4652: -- 20DBEEPD - 20 Days before the end of the enrollment period end date
4653: --
4654: -- hr_utility.set_location('Entering 20DBEEPD',10);
4655: --
4656: open c_cm_enrt_perd_end_dt;
4657: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4658: if c_cm_enrt_perd_end_dt%notfound then
4656: open c_cm_enrt_perd_end_dt;
4657: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4658: if c_cm_enrt_perd_end_dt%notfound then
4659: close c_cm_enrt_perd_end_dt;
4660: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',89);
4661: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4662: fnd_message.set_token('DATE_CODE',p_date_cd);
4663: fnd_message.set_token('L_PROC',l_proc);
4664: fnd_message.raise_error;
4671: elsif p_date_cd = '25DBEEPD' then
4672: --
4673: -- 25DBEEPD - 25 Days before the end of the enrollment period end date
4674: --
4675: -- hr_utility.set_location('Entering 25DBEEPD',10);
4676: --
4677: open c_cm_enrt_perd_end_dt;
4678: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4679: if c_cm_enrt_perd_end_dt%notfound then
4677: open c_cm_enrt_perd_end_dt;
4678: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4679: if c_cm_enrt_perd_end_dt%notfound then
4680: close c_cm_enrt_perd_end_dt;
4681: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',90);
4682: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4683: fnd_message.set_token('DATE_CODE',p_date_cd);
4684: fnd_message.set_token('L_PROC',l_proc);
4685: fnd_message.raise_error;
4692: elsif p_date_cd = '30DBEEPD' then
4693: --
4694: -- 30DBEEPD - 30 Days before the end of the enrollment period end date
4695: --
4696: -- hr_utility.set_location('Entering 30DBEEPD',10);
4697: --
4698: open c_cm_enrt_perd_end_dt;
4699: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4700: if c_cm_enrt_perd_end_dt%notfound then
4698: open c_cm_enrt_perd_end_dt;
4699: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4700: if c_cm_enrt_perd_end_dt%notfound then
4701: close c_cm_enrt_perd_end_dt;
4702: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',91);
4703: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4704: fnd_message.set_token('DATE_CODE',p_date_cd);
4705: fnd_message.set_token('L_PROC',l_proc);
4706: fnd_message.raise_error;
4714: elsif p_date_cd = '10DBDAD' then
4715: --
4716: -- 10DBDAD - 10 Days before the default applied date
4717: --
4718: -- hr_utility.set_location('Entering 10DBDAD',10);
4719: --
4720: open c_cm_dflt_asnd_dt;
4721: fetch c_cm_dflt_asnd_dt into l_cm_date;
4722: if c_cm_dflt_asnd_dt%notfound then
4720: open c_cm_dflt_asnd_dt;
4721: fetch c_cm_dflt_asnd_dt into l_cm_date;
4722: if c_cm_dflt_asnd_dt%notfound then
4723: close c_cm_dflt_asnd_dt;
4724: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',92);
4725: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4726: fnd_message.set_token('DATE_CODE',p_date_cd);
4727: fnd_message.set_token('L_PROC',l_proc);
4728: fnd_message.raise_error;
4735: elsif p_date_cd = '14DBEPD' then
4736: --
4737: -- 14DBEPD - 14 Days before the Eligible to Participate Date
4738: --
4739: -- hr_utility.set_location('Entering 14DBEPD',10);
4740: --
4741: open c_cm_elig_prtn_strt_dt;
4742: fetch c_cm_elig_prtn_strt_dt into l_cm_date;
4743: if c_cm_elig_prtn_strt_dt%notfound then
4741: open c_cm_elig_prtn_strt_dt;
4742: fetch c_cm_elig_prtn_strt_dt into l_cm_date;
4743: if c_cm_elig_prtn_strt_dt%notfound then
4744: close c_cm_elig_prtn_strt_dt;
4745: -- hr_utility.set_location('BEN_91386_FIRST_INELIG',81);
4746: fnd_message.set_name('BEN', 'BEN_91386_FIRST_INELIG');
4747: fnd_message.set_token('DATE_CODE',p_date_cd);
4748: fnd_message.set_token('PROC',l_proc);
4749: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4762: open c_cm_elig_prtn_end_dt;
4763: fetch c_cm_elig_prtn_end_dt into l_cm_date;
4764: if c_cm_elig_prtn_end_dt%notfound then
4765: close c_cm_elig_prtn_end_dt;
4766: -- hr_utility.set_location('BEN_91386_FIRST_INELIG',93);
4767: fnd_message.set_name('BEN', 'BEN_91386_FIRST_INELIG');
4768: fnd_message.set_token('DATE_CODE',p_date_cd);
4769: fnd_message.set_token('PROC',l_proc);
4770: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4779: elsif p_date_cd = '15DBEEPD' then
4780: --
4781: -- 15DBEEPD - 15 Days before the end of the enrollment period end date
4782: --
4783: -- hr_utility.set_location('Entering 15DBEEPD',10);
4784: --
4785: open c_cm_enrt_perd_end_dt;
4786: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4787: if c_cm_enrt_perd_end_dt%notfound then
4785: open c_cm_enrt_perd_end_dt;
4786: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4787: if c_cm_enrt_perd_end_dt%notfound then
4788: close c_cm_enrt_perd_end_dt;
4789: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',88);
4790: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4791: fnd_message.set_token('DATE_CODE',p_date_cd);
4792: fnd_message.set_token('L_PROC',l_proc);
4793: fnd_message.raise_error;
4799: elsif p_date_cd = 'OAED' then
4800: --
4801: -- OAED - On the Automatic Enrollment Date.
4802: --
4803: -- hr_utility.set_location('Entering OAED',10);
4804: --
4805: open c_cm_auto_asnd_dt;
4806: fetch c_cm_auto_asnd_dt into l_cm_date;
4807: if c_cm_auto_asnd_dt%notfound then
4805: open c_cm_auto_asnd_dt;
4806: fetch c_cm_auto_asnd_dt into l_cm_date;
4807: if c_cm_auto_asnd_dt%notfound then
4808: close c_cm_auto_asnd_dt;
4809: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',98);
4810: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4811: fnd_message.set_token('DATE_CODE',p_date_cd);
4812: fnd_message.set_token('L_PROC',l_proc);
4813: fnd_message.raise_error;
4820: elsif p_date_cd = 'OCSD' or p_date_cd = 'PECSD' then
4821: --
4822: -- OCSD - On the Coverage Start Date.
4823: --
4824: -- hr_utility.set_location('Entering OCSD',10);
4825: --
4826: open c_prtt_enrt_rslt_dts;
4827: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
4828: l_enrt_cvg_strt_dt,
4831: --
4832: l_enrt_cvg_strt_dt := p_start_date ;
4833: --
4834: if g_debug then
4835: hr_utility.set_location('OCSD Not found ', 19);
4836: end if;
4837: --
4838: end if;
4839: --
4845: -- LFDMCF - First of Month on or After Later of Event or Notified
4846: --
4847: elsif p_date_cd = 'LFDMCF' then
4848: --
4849: -- hr_utility.set_location('Entering LFDMCF',10);
4850: --
4851: if l_lf_evt_ocrd_dt is null then
4852: --
4853: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4886: -- LFDPPCF - First of Pay Period on or After Later of Event or Notified
4887: --
4888: elsif p_date_cd = 'LFDPPCF' then
4889: --
4890: -- hr_utility.set_location('Entering LFDPPCF',10);
4891: --
4892: if l_lf_evt_ocrd_dt is null then
4893: --
4894: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4907: --
4908: if c_pay_period_for_date%notfound then
4909: --
4910: close c_pay_period_for_date;
4911: -- hr_utility.set_location('BEN_92380_CANNOT_CALC_LFDPPCF',96);
4912: -- hr_utility.set_location('l_lf_evt_ocrd_dt'||to_char(l_lf_evt_ocrd_dt),96);
4913: fnd_message.set_name('BEN','BEN_92380_CANNOT_CALC_LFDPPCF');
4914: fnd_message.set_token('L_PROC',l_proc);
4915: fnd_message.set_token('PERSON_ID',l_person_id);
4908: if c_pay_period_for_date%notfound then
4909: --
4910: close c_pay_period_for_date;
4911: -- hr_utility.set_location('BEN_92380_CANNOT_CALC_LFDPPCF',96);
4912: -- hr_utility.set_location('l_lf_evt_ocrd_dt'||to_char(l_lf_evt_ocrd_dt),96);
4913: fnd_message.set_name('BEN','BEN_92380_CANNOT_CALC_LFDPPCF');
4914: fnd_message.set_token('L_PROC',l_proc);
4915: fnd_message.set_token('PERSON_ID',l_person_id);
4916: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4932: fetch c_next_pay_period into l_next_pay_period;
4933: --
4934: if c_next_pay_period%notfound then
4935: close c_next_pay_period;
4936: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',125);
4937: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4938: fnd_message.set_token('DATE_CODE',p_date_cd);
4939: fnd_message.set_token('PERSON_ID',l_person_id);
4940: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4957: --
4958: if c_pay_period_for_date%notfound then
4959: --
4960: close c_pay_period_for_date;
4961: -- hr_utility.set_location('BEN_92380_CANNOT_CALC_LFDPPCF',97);
4962: -- hr_utility.set_location('l_recorded_date'||to_char(l_recorded_date),97);
4963: fnd_message.set_name('BEN','BEN_92380_CANNOT_CALC_LFDPPCF');
4964: fnd_message.set_token('L_PROC',l_proc);
4965: fnd_message.set_token('PERSON_ID',l_person_id);
4958: if c_pay_period_for_date%notfound then
4959: --
4960: close c_pay_period_for_date;
4961: -- hr_utility.set_location('BEN_92380_CANNOT_CALC_LFDPPCF',97);
4962: -- hr_utility.set_location('l_recorded_date'||to_char(l_recorded_date),97);
4963: fnd_message.set_name('BEN','BEN_92380_CANNOT_CALC_LFDPPCF');
4964: fnd_message.set_token('L_PROC',l_proc);
4965: fnd_message.set_token('PERSON_ID',l_person_id);
4966: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4984: fetch c_next_pay_period into l_next_pay_period;
4985: --
4986: if c_next_pay_period%notfound then
4987: close c_next_pay_period;
4988: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',130);
4989: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4990: fnd_message.set_token('DATE_CODE',p_date_cd);
4991: fnd_message.set_token('PERSON_ID',l_person_id);
4992: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5005: -- LELD - Latest of Elections, Event or Notified
5006: --
5007: elsif p_date_cd = 'LELD' then
5008: --
5009: -- hr_utility.set_location('Entering LELD',10);
5010: --
5011: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5012: hr_utility.set_location('Entering LELD',11);
5013: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5008: --
5009: -- hr_utility.set_location('Entering LELD',10);
5010: --
5011: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5012: hr_utility.set_location('Entering LELD',11);
5013: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5014: fetch c_prtt_enrt_rslt_dts_bckdt into l_enrt_cvg_end_dt,
5015: l_enrt_cvg_strt_dt,
5016: l_enrt_eff_strt_date;
5070: -- LELDED - Later of Elections or Event
5071: --
5072: elsif p_date_cd = 'LELDED' then
5073: --
5074: -- hr_utility.set_location('Entering LELDED',10);
5075: --
5076: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5077: hr_utility.set_location('Entering LELDED',11);
5078: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5073: --
5074: -- hr_utility.set_location('Entering LELDED',10);
5075: --
5076: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5077: hr_utility.set_location('Entering LELDED',11);
5078: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5079: fetch c_prtt_enrt_rslt_dts_bckdt into l_enrt_cvg_end_dt,
5080: l_enrt_cvg_strt_dt,
5081: l_enrt_eff_strt_date;
5125: -- should return null if result with current pil
5126: -- is not found, i.e. in benmngle. gets date at
5127: -- time of enrollment.
5128: --
5129: -- hr_utility.set_location('Entering ODEWM',10);
5130: --
5131: /*Added for Bug 12351605. get the old coverage from backed out pen result. Added if condition */
5132: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5133: hr_utility.set_location('Entering ODEWM',11);
5129: -- hr_utility.set_location('Entering ODEWM',10);
5130: --
5131: /*Added for Bug 12351605. get the old coverage from backed out pen result. Added if condition */
5132: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5133: hr_utility.set_location('Entering ODEWM',11);
5134: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5135: fetch c_prtt_enrt_rslt_dts_bckdt into l_enrt_cvg_end_dt,
5136: l_enrt_cvg_strt_dt,
5137: l_enrt_eff_strt_date;
5168: -- should return null if result with current pil
5169: -- is not found, i.e. in benmngle. gets date at
5170: -- time of enrollment.
5171: --
5172: hr_utility.set_location('Entering AFDELD',10);
5173: --
5174: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5175: hr_utility.set_location('Entering AFDELD',11);
5176: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5171: --
5172: hr_utility.set_location('Entering AFDELD',10);
5173: --
5174: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5175: hr_utility.set_location('Entering AFDELD',11);
5176: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5177: fetch c_prtt_enrt_rslt_dts_bckdt into l_enrt_cvg_end_dt,
5178: l_enrt_cvg_strt_dt,
5179: l_enrt_eff_strt_date;
5191: l_enrt_eff_strt_date;
5192: --
5193: close c_prtt_enrt_rslt_dts;
5194:
5195: hr_utility.set_location('Entering AFDELD ' || l_enrt_eff_strt_date ,10);
5196: --
5197: -- when the first time cvg calcualted there may not be a result
5198: if l_enrt_eff_strt_date is null then
5199: l_enrt_eff_strt_date :=p_effective_date;
5199: l_enrt_eff_strt_date :=p_effective_date;
5200: end if;
5201: p_returned_date := last_day(l_enrt_eff_strt_date) + 1 ;
5202: end if;
5203: hr_utility.set_location('AFDELD ' || p_returned_date ,10);
5204: --
5205: --
5206: -- FDMELD First of Month on or After Elections Made
5207: elsif p_date_cd = 'FDMELD' then
5209: -- should return null if result with current pil
5210: -- is not found, i.e. in benmngle. gets date at
5211: -- time of enrollment.
5212: --
5213: hr_utility.set_location('Entering FDMELD',10);
5214: --
5215: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5216: hr_utility.set_location('Entering FDMELD',11);
5217: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5212: --
5213: hr_utility.set_location('Entering FDMELD',10);
5214: --
5215: if(ben_manage_life_events.g_bckdt_per_in_ler_id is not NULL) then
5216: hr_utility.set_location('Entering FDMELD',11);
5217: open c_prtt_enrt_rslt_dts_bckdt(ben_manage_life_events.g_bckdt_per_in_ler_id);
5218: fetch c_prtt_enrt_rslt_dts_bckdt into l_enrt_cvg_end_dt,
5219: l_enrt_cvg_strt_dt,
5220: l_enrt_eff_strt_date;
5243: elsif p_date_cd = 'ODD' then
5244: --
5245: -- ODD - On the De-enrollment Date.
5246: --
5247: -- hr_utility.set_location('Entering ODD',10);
5248: --
5249: p_returned_date := p_effective_date;
5250: --
5251: elsif p_date_cd = 'ODAD' then
5251: elsif p_date_cd = 'ODAD' then
5252: --
5253: -- ODAD - On the Default Applied Date.
5254: --
5255: -- hr_utility.set_location('Entering ODAD',10);
5256: --
5257: open c_cm_dflt_asnd_dt;
5258: fetch c_cm_dflt_asnd_dt into l_cm_date;
5259: if c_cm_dflt_asnd_dt%notfound then
5257: open c_cm_dflt_asnd_dt;
5258: fetch c_cm_dflt_asnd_dt into l_cm_date;
5259: if c_cm_dflt_asnd_dt%notfound then
5260: close c_cm_dflt_asnd_dt;
5261: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',96);
5262: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
5263: fnd_message.set_token('DATE_CODE',p_date_cd);
5264: fnd_message.set_token('L_PROC',l_proc);
5265: fnd_message.raise_error;
5271: elsif p_date_cd = 'OEPD' then
5272: --
5273: -- OEPD - On the Eligible to Participate Date.
5274: --
5275: -- hr_utility.set_location('Entering OEPD',10);
5276: --
5277: open c_cm_elig_prtn_strt_dt;
5278: fetch c_cm_elig_prtn_strt_dt into l_cm_date;
5279: if c_cm_elig_prtn_strt_dt%notfound then
5277: open c_cm_elig_prtn_strt_dt;
5278: fetch c_cm_elig_prtn_strt_dt into l_cm_date;
5279: if c_cm_elig_prtn_strt_dt%notfound then
5280: close c_cm_elig_prtn_strt_dt;
5281: -- hr_utility.set_location('BEN_92381_ELIG_PER_NOT_FOUND',96);
5282: fnd_message.set_name('BEN', 'BEN_92381_ELIG_PER_NOT_FOUND');
5283: fnd_message.set_token('PROC',l_proc);
5284: fnd_message.set_token('DATE_CODE',p_date_cd);
5285: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
5294: elsif p_date_cd = 'OIPD' then
5295: --
5296: -- OIPD - On the Ineligible to Participate Date.
5297: --
5298: -- hr_utility.set_location('Entering OIPD',10);
5299: --
5300: open c_cm_elig_prtn_end_dt;
5301: fetch c_cm_elig_prtn_end_dt into l_cm_date;
5302: if c_cm_elig_prtn_end_dt%notfound then
5300: open c_cm_elig_prtn_end_dt;
5301: fetch c_cm_elig_prtn_end_dt into l_cm_date;
5302: if c_cm_elig_prtn_end_dt%notfound then
5303: close c_cm_elig_prtn_end_dt;
5304: -- hr_utility.set_location('BEN_92381_ELIG_PER_NOT_FOUND',98);
5305: fnd_message.set_name('BEN', 'BEN_92381_ELIG_PER_NOT_FOUND');
5306: fnd_message.set_token('PROC',l_proc);
5307: fnd_message.set_token('DATE_CODE',p_date_cd);
5308: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
5317: elsif p_date_cd = 'OLDEP' then
5318: --
5319: -- OLDEP - On the last day of the enrollment period.
5320: --
5321: -- hr_utility.set_location('Entering OLDEP',10);
5322: --
5323: open c_cm_enrt_perd_end_dt;
5324: fetch c_cm_enrt_perd_end_dt into l_cm_date;
5325: if c_cm_enrt_perd_end_dt%notfound then
5350: else -- OED
5351: --
5352: -- OED - On the effective date.
5353: --
5354: -- hr_utility.set_location('Entering OED',10);
5355: --
5356: p_returned_date := p_effective_date;
5357: --
5358: -- ODBEFFD - One day before the effective date.
5363: --6823087
5364:
5365: elsif p_date_cd = 'ODBEFFD' then
5366: --
5367: -- hr_utility.set_location('Entering ODBEFFD',10);
5368: --
5369: p_returned_date := p_effective_date-1;
5370: --
5371: --Bug 6212793
5380: -- FDPPYCF - First Day of Program or Plan Year Concurrent with or Following
5381: --
5382: elsif p_date_cd = 'FDPPYCF' then
5383: --
5384: -- hr_utility.set_location('Entering FDPPYCF',10);
5385: --
5386: if l_lf_evt_ocrd_dt is null then
5387: --
5388: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5424: -- FDMCF - First Day of Month concurrent with or following
5425: --
5426: elsif p_date_cd = 'FDMCF' then
5427: --
5428: -- hr_utility.set_location('Entering FDMCF',10);
5429: --
5430: if l_lf_evt_ocrd_dt is null then
5431: --
5432: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5453: -- FDPPCF - First day of pay period concurrent with or following
5454: --
5455: elsif p_date_cd = 'FDPPCF' then
5456: --
5457: -- hr_utility.set_location('Entering FDPPCF',10);
5458: --
5459: if l_lf_evt_ocrd_dt is null then
5460: --
5461: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5470: open c_pay_period;
5471: fetch c_pay_period into l_pay_period;
5472: if c_pay_period%notfound then
5473: close c_pay_period;
5474: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',135);
5475: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
5476: fnd_message.set_token('DATE_CODE',p_date_cd);
5477: fnd_message.set_token('PERSON_ID',l_person_id);
5478: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5492: fetch c_next_pay_period into l_next_pay_period;
5493: --
5494: if c_next_pay_period%notfound then
5495: close c_next_pay_period;
5496: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',140);
5497: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
5498: fnd_message.set_token('DATE_CODE',p_date_cd);
5499: fnd_message.set_token('PERSON_ID',l_person_id);
5500: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5511: -- RL - Rule
5512: --
5513: elsif p_date_cd = 'RL' then
5514: --
5515: -- hr_utility.set_location('Entering RL',10);
5516: --
5517: open c_asg;
5518: fetch c_asg into l_asg; --if notfound, don't care, will pass null to formula
5519: close c_asg;
5548: close c_pl_typ;
5549: end if;
5550:
5551: if g_debug then
5552: hr_utility.set_location ('ler_id '||to_char(l_ler_id),70);
5553: end if;
5554: if l_per_in_ler_id is not null then
5555: open c_ler;
5556: fetch c_ler into l_ler_id;
5557: close c_ler;
5558: end if;
5559:
5560: if g_debug then
5561: hr_utility.set_location ('Organization_id '||l_asg.organization_id,10);
5562: end if;
5563: if g_debug then
5564: hr_utility.set_location ('assignment_id '||l_asg.assignment_id,15);
5565: end if;
5560: if g_debug then
5561: hr_utility.set_location ('Organization_id '||l_asg.organization_id,10);
5562: end if;
5563: if g_debug then
5564: hr_utility.set_location ('assignment_id '||l_asg.assignment_id,15);
5565: end if;
5566: if g_debug then
5567: hr_utility.set_location ('Business_group_id '||p_business_group_id,20);
5568: end if;
5563: if g_debug then
5564: hr_utility.set_location ('assignment_id '||l_asg.assignment_id,15);
5565: end if;
5566: if g_debug then
5567: hr_utility.set_location ('Business_group_id '||p_business_group_id,20);
5568: end if;
5569: if g_debug then
5570: hr_utility.set_location ('pgm_id '||l_pgm_id,30);
5571: end if;
5566: if g_debug then
5567: hr_utility.set_location ('Business_group_id '||p_business_group_id,20);
5568: end if;
5569: if g_debug then
5570: hr_utility.set_location ('pgm_id '||l_pgm_id,30);
5571: end if;
5572: if g_debug then
5573: hr_utility.set_location ('pl_id '||l_pl_id,40);
5574: end if;
5569: if g_debug then
5570: hr_utility.set_location ('pgm_id '||l_pgm_id,30);
5571: end if;
5572: if g_debug then
5573: hr_utility.set_location ('pl_id '||l_pl_id,40);
5574: end if;
5575: if g_debug then
5576: hr_utility.set_location ('pl_typ_id '||l_pl_typ_id,50);
5577: end if;
5572: if g_debug then
5573: hr_utility.set_location ('pl_id '||l_pl_id,40);
5574: end if;
5575: if g_debug then
5576: hr_utility.set_location ('pl_typ_id '||l_pl_typ_id,50);
5577: end if;
5578: if g_debug then
5579: hr_utility.set_location ('opt_id '||l_opt.opt_id,60);
5580: end if;
5575: if g_debug then
5576: hr_utility.set_location ('pl_typ_id '||l_pl_typ_id,50);
5577: end if;
5578: if g_debug then
5579: hr_utility.set_location ('opt_id '||l_opt.opt_id,60);
5580: end if;
5581: if g_debug then
5582: hr_utility.set_location ('ler_id '||l_ler_id,70);
5583: end if;
5578: if g_debug then
5579: hr_utility.set_location ('opt_id '||l_opt.opt_id,60);
5580: end if;
5581: if g_debug then
5582: hr_utility.set_location ('ler_id '||l_ler_id,70);
5583: end if;
5584: if g_debug then
5585: hr_utility.set_location ('p_acty_base_rt_id '||p_acty_base_rt_id,50);
5586: end if;
5581: if g_debug then
5582: hr_utility.set_location ('ler_id '||l_ler_id,70);
5583: end if;
5584: if g_debug then
5585: hr_utility.set_location ('p_acty_base_rt_id '||p_acty_base_rt_id,50);
5586: end if;
5587: if g_debug then
5588: hr_utility.set_location ('p_bnfts_bal_id '||p_bnfts_bal_id,60);
5589: end if;
5584: if g_debug then
5585: hr_utility.set_location ('p_acty_base_rt_id '||p_acty_base_rt_id,50);
5586: end if;
5587: if g_debug then
5588: hr_utility.set_location ('p_bnfts_bal_id '||p_bnfts_bal_id,60);
5589: end if;
5590: if g_debug then
5591: hr_utility.set_location ('jurisdiction_code '||l_jurisdiction_code,70);
5592: end if;
5587: if g_debug then
5588: hr_utility.set_location ('p_bnfts_bal_id '||p_bnfts_bal_id,60);
5589: end if;
5590: if g_debug then
5591: hr_utility.set_location ('jurisdiction_code '||l_jurisdiction_code,70);
5592: end if;
5593:
5594: -- for all other codes LE date is calcualted when the LE date is null
5595: -- this update does that for formula too
5605: -- Call formula initialise routine
5606: -- Added param1 and param1_value parameters to fix bug 1531647
5607:
5608: if g_debug then
5609: hr_utility.set_location ('p_formula_id '||p_formula_id,1689);
5610: end if;
5611:
5612: l_outputs := benutils.formula
5613: (p_formula_id => p_formula_id
5638:
5639: p_returned_date := fnd_date.canonical_to_date(l_outputs(l_outputs.first).value);
5640:
5641: if g_debug then
5642: hr_utility.set_location ('p_returned_date='||p_returned_date,1689);
5643: end if;
5644: --
5645: --
5646: l_ben_disp_ff_warn_msg := get_profile_ff_warn_val();
5671: --
5672: -- LRD - Later of Recorded Date and Event Date
5673: --
5674: elsif p_date_cd in ( 'LRD' , 'NUMDOEN') then
5675: -- hr_utility.set_location('Entering LRD',10);
5676:
5677: if l_lf_evt_ocrd_dt is null then
5678: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5679: else
5703: -- LODBED - One day before later of Recorded Date and Event Date
5704: --
5705: elsif p_date_cd in ('LODBED','LWEM') then
5706: --
5707: -- hr_utility.set_location('Entering LODBED OR LWEM',10);
5708: --
5709: if l_lf_evt_ocrd_dt is null then
5710: --
5711: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5735: -- LALDCM - End of Month Later Event or Notified
5736: --
5737: elsif p_date_cd = 'LALDCM' then
5738: --
5739: -- hr_utility.set_location('Entering LALDCM',10);
5740: --
5741: if l_lf_evt_ocrd_dt is null then
5742: --
5743: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5759: -- LALDFM - End of Month After Later Event or Notified
5760: --
5761: elsif p_date_cd = 'LALDFM' then
5762: --
5763: -- hr_utility.set_location('Entering LALDFM',10);
5764: --
5765: if l_lf_evt_ocrd_dt is null then
5766: --
5767: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5782: -- LAFDFM - Later: First Day of Following Month
5783: --
5784: elsif p_date_cd = 'LAFDFM' then
5785: --
5786: -- hr_utility.set_location('Entering LAFDFM',10);
5787: --
5788: if l_lf_evt_ocrd_dt is null then
5789: --
5790: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5805: -- LALDCPPY - Later: Last day of current program or plan year
5806: --
5807: elsif p_date_cd = 'LALDCPPY' then
5808: --
5809: -- hr_utility.set_location('Entering LALDCPPY',10);
5810: --
5811: if l_lf_evt_ocrd_dt is null then
5812: --
5813: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5842: -- LAFDLMEPPY - First of Last Month in Year Later Event or Notified
5843: --
5844: elsif p_date_cd = 'LAFDLMEPPY' then
5845: --
5846: -- hr_utility.set_location('Entering LAFDLMEPPY',10);
5847: --
5848: if l_lf_evt_ocrd_dt is null then
5849: --
5850: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5880: -- LALDLPPEPPY - End of Last Pay Period of Event Year Later Event or Notified
5881: --
5882: elsif p_date_cd = 'LALDLPPEPPY' then
5883: --
5884: -- hr_utility.set_location('Entering LALDLPPEPPY',10);
5885: --
5886: if l_lf_evt_ocrd_dt is null then
5887: --
5888: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5908: --
5909: if c_pay_period_for_date%notfound then
5910: --
5911: close c_pay_period_for_date;
5912: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',145);
5913: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
5914: fnd_message.set_token('DATE_CODE',p_date_cd);
5915: fnd_message.set_token('PERSON_ID',l_person_id);
5916: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5928: --
5929: elsif p_date_cd = 'WALDLPPEPPY' then
5930: --
5931: if g_debug then
5932: hr_utility.set_location('Entering WALDLPPEPPY',10);
5933: end if;
5934: --
5935: if l_lf_evt_ocrd_dt is null then
5936: --
5949: --
5950: if c_pre_pay_period%notfound then
5951: --
5952: close c_pre_pay_period;
5953: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',150);
5954: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
5955: fnd_message.set_token('DATE_CODE',p_date_cd);
5956: fnd_message.set_token('PERSON_ID',l_person_id);
5957: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5969: -- LODBEWM - 1 Prior New Rate Start or 1 Before Later Event or Notified
5970: --
5971: elsif p_date_cd = 'LODBEWM' then
5972: --
5973: -- hr_utility.set_location('Entering LODBEWM',10);
5974: --
5975: open c_prtt_rt_val_dts;
5976: --
5977: /*
6012: fetch c_enrt_rt_val_dt into l_end_date;
6013: if c_enrt_rt_val_dt%found then
6014: --
6015: if g_debug then
6016: hr_utility.set_location('l_end_date - 1',1999);
6017: end if;
6018: p_returned_date := l_end_date - 1;
6019: --
6020: else
6021: --
6022: if l_lf_evt_ocrd_dt is null then
6023: --
6024: if g_debug then
6025: hr_utility.set_location('l_lf_evt_ocrd_dt is null',1999);
6026: end if;
6027: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6028: --
6029: end if;
6048: --
6049: elsif p_date_cd = 'ODBEWM' then
6050: --
6051: if g_debug then
6052: hr_utility.set_location('Entering ODBEWM',10);
6053: end if;
6054: --
6055: open c_prtt_rt_val_dts;
6056: --
6060: */
6061: fetch c_prtt_rt_val_dts into l_dummy ;
6062: --
6063: if g_debug then
6064: hr_utility.set_location('l_start_date'||l_start_date , 1999);
6065: end if;
6066: if g_debug then
6067: hr_utility.set_location('l_end_date'||l_end_date , 1999);
6068: end if;
6063: if g_debug then
6064: hr_utility.set_location('l_start_date'||l_start_date , 1999);
6065: end if;
6066: if g_debug then
6067: hr_utility.set_location('l_end_date'||l_end_date , 1999);
6068: end if;
6069:
6070: if c_prtt_rt_val_dts%notfound then
6071: --
6071: --
6072: if l_lf_evt_ocrd_dt is null then
6073: --
6074: if g_debug then
6075: hr_utility.set_location('l_lf_evt_ocrd_dt is null',1999);
6076: end if;
6077: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6078: --
6079: end if;
6078: --
6079: end if;
6080: --
6081: if g_debug then
6082: hr_utility.set_location('l_event_date - 1',1999);
6083: end if;
6084: p_returned_date := l_event_date - 1;
6085: --
6086: else
6092: --
6093: if c_enrt_rt_val_dt%found then
6094: --
6095: if g_debug then
6096: hr_utility.set_location('l_end_date - 1',1999);
6097: end if;
6098: p_returned_date := l_end_date - 1;
6099: --
6100: else
6101:
6102: if l_lf_evt_ocrd_dt is null then
6103: --
6104: if g_debug then
6105: hr_utility.set_location('p_lf_evt_ocrd_dt is null',1999);
6106: end if;
6107: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6108: --
6109: end if;
6107: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6108: --
6109: end if;
6110: if g_debug then
6111: hr_utility.set_location('l_lf_evt_ocrd_dt else'||l_event_date , 1999);
6112: end if;
6113: p_returned_date := l_event_date - 1;
6114: --
6115: end if;
6131: --
6132: elsif p_date_cd = 'LWODBED' then
6133: --
6134: if g_debug then
6135: hr_utility.set_location('Entering LWODBED',10);
6136: end if;
6137: --
6138: if l_lf_evt_ocrd_dt is null then
6139: --
6166: --
6167: elsif p_date_cd = 'LWALDLPPEPPY' then
6168: --
6169: if g_debug then
6170: hr_utility.set_location('Entering LWALDLPPEPPY',10);
6171: end if;
6172: --
6173: if l_lf_evt_ocrd_dt is null then
6174: --
6202: l_start_date,
6203: l_end_date;
6204: --
6205: if c_pay_period_for_date%notfound then
6206: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',160);
6207: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
6208: fnd_message.set_token('DATE_CODE',p_date_cd);
6209: fnd_message.set_token('PERSON_ID',l_person_id);
6210: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
6237: l_start_date,
6238: l_end_date;
6239: --
6240: if c_pay_period_for_date%notfound then
6241: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',165);
6242: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
6243: fnd_message.set_token('DATE_CODE',p_date_cd);
6244: fnd_message.set_token('PERSON_ID',l_person_id);
6245: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
6259: --
6260: elsif p_date_cd = 'LWALDCPPY' then
6261: --
6262: if g_debug then
6263: hr_utility.set_location('Entering LWALDCPPY',10);
6264: end if;
6265: --
6266: if l_lf_evt_ocrd_dt is null then
6267: --
6316: --
6317: elsif p_date_cd = 'LWALDCPP' then
6318: --
6319: if g_debug then
6320: hr_utility.set_location('Entering LWALDCPP',10);
6321: end if;
6322: --
6323: if l_lf_evt_ocrd_dt is null then
6324: --
6359: --
6360: elsif p_date_cd = 'LWALDCM' then
6361: --
6362: if g_debug then
6363: hr_utility.set_location('Entering LWALDCM',10);
6364: end if;
6365: --
6366: if l_lf_evt_ocrd_dt is null then
6367: --
6389: -- LFDPPYCF - First of Year On or After Later of Event or Notified
6390: --
6391: elsif p_date_cd = 'LFDPPYCF' then
6392: --
6393: -- hr_utility.set_location('Entering LFDPPYCF',10);
6394: --
6395: if l_lf_evt_ocrd_dt is null then
6396: --
6397: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6446: -- LAFDCPPY - Later: First day of current program or plan year
6447: --
6448: elsif p_date_cd = 'LAFDCPPY' then
6449: --
6450: -- hr_utility.set_location('Entering LAFDCPPY',10);
6451: --
6452: if l_lf_evt_ocrd_dt is null then
6453: --
6454: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6483: -- LAFDFPPY - Later: First day of following program or plan year
6484: --
6485: elsif p_date_cd = 'LAFDFPPY' then
6486: --
6487: -- hr_utility.set_location('Entering LAFDFPPY',10);
6488: --
6489: if l_lf_evt_ocrd_dt is null then
6490: --
6491: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6520: -- LALDCPP - Later: Last day of current pay period
6521: --
6522: elsif p_date_cd = 'LALDCPP' then
6523: --
6524: -- hr_utility.set_location('Entering LALDCPP',10);
6525: --
6526: if l_lf_evt_ocrd_dt is null then
6527: --
6528: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6544: open c_pay_period;
6545: fetch c_pay_period into l_pay_period;
6546: if c_pay_period%notfound then
6547: close c_pay_period;
6548: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',170);
6549: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
6550: fnd_message.set_token('DATE_CODE',p_date_cd);
6551: fnd_message.set_token('PERSON_ID',l_person_id);
6552: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
6560: -- LAFDFPP - Later: First day of following pay period
6561: --
6562: elsif p_date_cd = 'LAFDFPP' then
6563: --
6564: -- hr_utility.set_location('Entering LAFDFPP',10);
6565: --
6566: if l_lf_evt_ocrd_dt is null then
6567: --
6568: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6585: fetch c_next_pay_period into l_next_pay_period;
6586: --
6587: if c_next_pay_period%notfound then
6588: close c_next_pay_period;
6589: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',170);
6590: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
6591: fnd_message.set_token('DATE_CODE',p_date_cd);
6592: fnd_message.set_token('PERSON_ID',l_person_id);
6593: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
6600: -- LTODFED - Thirty-one days After Later of Recorded Date and Event Date
6601: --
6602: elsif p_date_cd = 'LTODFED' then
6603: --
6604: -- hr_utility.set_location('Entering LTODFED',10);
6605: --
6606: if l_lf_evt_ocrd_dt is null then
6607: --
6608: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6623: -- LSDFED - Sixty days After Later of Recorded Date and Event Date
6624: --
6625: elsif p_date_cd = 'LSDFED' then
6626: --
6627: -- hr_utility.set_location('Entering LSDFED',10);
6628: --
6629: if l_lf_evt_ocrd_dt is null then
6630: --
6631: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6651: elsif p_date_cd in ( '30DAHRD','31DAHRD','60DAHRD','61DAHRD') then
6652:
6653: -- get the latest hire date, if fails error
6654: if g_debug then
6655: hr_utility.set_location(' Hire date ' , 99 );
6656: end if;
6657: open c_pps ;
6658: fetch c_pps into l_hire_date ;
6659: close c_pps ;
6657: open c_pps ;
6658: fetch c_pps into l_hire_date ;
6659: close c_pps ;
6660: if g_debug then
6661: hr_utility.set_location(' Hire date ' || l_hire_date, 99 );
6662: end if;
6663: if l_hire_date is null then
6664: fnd_message.set_name('BEN','BEN_92489_CANNOT_CALC_DATE');
6665: fnd_message.set_token('DATE_CODE',p_date_cd);
6666: fnd_message.set_token('L_PROC',l_proc);
6667: fnd_message.raise_error;
6668: end if ;
6669: if g_debug then
6670: hr_utility.set_location(' Hire date ' || l_hire_date, 99 );
6671: end if;
6672:
6673: if p_date_cd = '30DAHRD' then
6674: p_returned_date := l_hire_date + 30 ;
6679: elsif p_date_cd = '61DAHRD' then
6680: p_returned_date := l_hire_date + 61 ;
6681: end if ;
6682: if g_debug then
6683: hr_utility.set_location(' p_date_cd ' || p_returned_date, 99 );
6684: end if;
6685:
6686: elsif p_date_cd in ('AFDELY','ALDELMY','FDM','FDMCFC','FDMFC',
6687: 'FDPP','FDPPQCFC','FDPPQFC','FDPPSYCFC','FDPPSYFC', 'FDPPYCFC','FDPPYFC','FDQ','FFDFED','FFDFEPS',
6689: 'NDFEPS','WALDCPPQ','WALDCPPSY','FDCY','FFDFEPS',
6690: 'FDLY','FDQ','FDSY', 'LFSEMES'
6691: ) then
6692:
6693: -- hr_utility.set_location('Future Date Code: '||p_date_cd , 222);
6694: fnd_message.set_name('BEN','FUTURE_DATE_CD_DO_NOT_USE');
6695: fnd_message.set_token('DATE_CODE',p_date_cd);
6696: fnd_message.raise_error;
6697:
6697:
6698: elsif p_date_cd in ('FDPPFCDE','FDPPFCDEL') then
6699: --
6700: if g_debug then
6701: hr_utility.set_location(' Step 2 ' ,181);
6702: end if;
6703: --
6704: if p_date_cd = 'FDPPFCDE' then
6705: --
6708: end if;
6709: l_start_date := l_lf_evt_ocrd_dt;
6710: else
6711: --
6712: hr_utility.set_location('p effective date '||p_effective_date,182);
6713: l_start_date := p_effective_date;
6714: end if;
6715: --
6716: open c_pay_period_for_check(l_start_date, 'E');
6752:
6753: elsif p_date_cd in ('WAPPCDE','WAPPDEL') then
6754: --
6755: if g_debug then
6756: hr_utility.set_location(' Step 2 ' ,181);
6757: end if;
6758: --
6759: if p_date_cd = 'WAPPCDE' then
6760: --
6792: close c_pay_period_for_check_end ;
6793: p_returned_date := l_end_date;
6794: else
6795:
6796: hr_utility.set_location('Unknown Date Code: '||p_date_cd , 222);
6797: fnd_message.set_name('BEN','BEN_91534_BEN_UNKNOWN_DATE_CD');
6798: fnd_message.set_token('DATE_CODE',p_date_cd);
6799: fnd_message.set_token('L_PROC',l_proc);
6800: fnd_message.raise_error;
6801:
6802: end if;
6803: --
6804: if g_debug then
6805: hr_utility.set_location('p_returned_date: '||p_returned_date,15);
6806: end if;
6807: p_returned_date := trunc(p_returned_date);
6808: --
6809: if g_debug then
6806: end if;
6807: p_returned_date := trunc(p_returned_date);
6808: --
6809: if g_debug then
6810: hr_utility.set_location('sent dt code ' || p_date_cd ,596);
6811: end if;
6812: if g_debug then
6813: hr_utility.set_location('sent_dt p_returned_date: '||p_returned_date,15);
6814: end if;
6809: if g_debug then
6810: hr_utility.set_location('sent dt code ' || p_date_cd ,596);
6811: end if;
6812: if g_debug then
6813: hr_utility.set_location('sent_dt p_returned_date: '||p_returned_date,15);
6814: end if;
6815: if g_debug then
6816: hr_utility.set_location('Leaving : '||l_proc , 20);
6817: end if;
6812: if g_debug then
6813: hr_utility.set_location('sent_dt p_returned_date: '||p_returned_date,15);
6814: end if;
6815: if g_debug then
6816: hr_utility.set_location('Leaving : '||l_proc , 20);
6817: end if;
6818: exception -- nocopy changes
6819: --
6820: when others then
7513:
7514:
7515: begin
7516: --
7517: g_debug := hr_utility.debug_enabled;
7518: if g_debug then
7519: hr_utility.set_location ('Entering '||l_proc,10);
7520: end if;
7521: /*
7515: begin
7516: --
7517: g_debug := hr_utility.debug_enabled;
7518: if g_debug then
7519: hr_utility.set_location ('Entering '||l_proc,10);
7520: end if;
7521: /*
7522: if p_which_dates_cd = 'R' then
7523: --hr_utility.set_location('p_cache_mode '|| p_cache_mode , 1687);
7519: hr_utility.set_location ('Entering '||l_proc,10);
7520: end if;
7521: /*
7522: if p_which_dates_cd = 'R' then
7523: --hr_utility.set_location('p_cache_mode '|| p_cache_mode , 1687);
7524: if g_debug then
7525: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 1687);
7526: end if;
7527: if g_debug then
7521: /*
7522: if p_which_dates_cd = 'R' then
7523: --hr_utility.set_location('p_cache_mode '|| p_cache_mode , 1687);
7524: if g_debug then
7525: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 1687);
7526: end if;
7527: if g_debug then
7528: hr_utility.set_location('p_person_id '|| p_person_id , 1687);
7529: end if;
7524: if g_debug then
7525: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 1687);
7526: end if;
7527: if g_debug then
7528: hr_utility.set_location('p_person_id '|| p_person_id , 1687);
7529: end if;
7530: if g_debug then
7531: hr_utility.set_location('p_pgm_id '|| p_pgm_id , 1687);
7532: end if;
7527: if g_debug then
7528: hr_utility.set_location('p_person_id '|| p_person_id , 1687);
7529: end if;
7530: if g_debug then
7531: hr_utility.set_location('p_pgm_id '|| p_pgm_id , 1687);
7532: end if;
7533: if g_debug then
7534: hr_utility.set_location('p_pl_id '|| p_pl_id , 1687);
7535: end if;
7530: if g_debug then
7531: hr_utility.set_location('p_pgm_id '|| p_pgm_id , 1687);
7532: end if;
7533: if g_debug then
7534: hr_utility.set_location('p_pl_id '|| p_pl_id , 1687);
7535: end if;
7536: if g_debug then
7537: hr_utility.set_location('p_oipl_id '|| p_oipl_id , 1687);
7538: end if;
7533: if g_debug then
7534: hr_utility.set_location('p_pl_id '|| p_pl_id , 1687);
7535: end if;
7536: if g_debug then
7537: hr_utility.set_location('p_oipl_id '|| p_oipl_id , 1687);
7538: end if;
7539: if g_debug then
7540: hr_utility.set_location('p_par_ptip_id '|| p_par_ptip_id , 1687);
7541: end if;
7536: if g_debug then
7537: hr_utility.set_location('p_oipl_id '|| p_oipl_id , 1687);
7538: end if;
7539: if g_debug then
7540: hr_utility.set_location('p_par_ptip_id '|| p_par_ptip_id , 1687);
7541: end if;
7542: if g_debug then
7543: hr_utility.set_location('p_lee_rsn_id '|| p_lee_rsn_id , 1687);
7544: end if;
7539: if g_debug then
7540: hr_utility.set_location('p_par_ptip_id '|| p_par_ptip_id , 1687);
7541: end if;
7542: if g_debug then
7543: hr_utility.set_location('p_lee_rsn_id '|| p_lee_rsn_id , 1687);
7544: end if;
7545: if g_debug then
7546: hr_utility.set_location('p_enrt_perd_id '|| p_enrt_perd_id , 1687);
7547: end if;
7542: if g_debug then
7543: hr_utility.set_location('p_lee_rsn_id '|| p_lee_rsn_id , 1687);
7544: end if;
7545: if g_debug then
7546: hr_utility.set_location('p_enrt_perd_id '|| p_enrt_perd_id , 1687);
7547: end if;
7548: if g_debug then
7549: hr_utility.set_location('p_enrt_perd_for_pl_id '|| p_enrt_perd_for_pl_id , 1687);
7550: end if;
7545: if g_debug then
7546: hr_utility.set_location('p_enrt_perd_id '|| p_enrt_perd_id , 1687);
7547: end if;
7548: if g_debug then
7549: hr_utility.set_location('p_enrt_perd_for_pl_id '|| p_enrt_perd_for_pl_id , 1687);
7550: end if;
7551: if g_debug then
7552: hr_utility.set_location('p_which_dates_cd '|| p_which_dates_cd , 1687);
7553: end if;
7548: if g_debug then
7549: hr_utility.set_location('p_enrt_perd_for_pl_id '|| p_enrt_perd_for_pl_id , 1687);
7550: end if;
7551: if g_debug then
7552: hr_utility.set_location('p_which_dates_cd '|| p_which_dates_cd , 1687);
7553: end if;
7554: if g_debug then
7555: hr_utility.set_location('p_date_mandatory_flag '|| p_date_mandatory_flag , 1687);
7556: end if;
7551: if g_debug then
7552: hr_utility.set_location('p_which_dates_cd '|| p_which_dates_cd , 1687);
7553: end if;
7554: if g_debug then
7555: hr_utility.set_location('p_date_mandatory_flag '|| p_date_mandatory_flag , 1687);
7556: end if;
7557: if g_debug then
7558: hr_utility.set_location('p_compute_dates_flag '|| p_compute_dates_flag , 1687);
7559: end if;
7554: if g_debug then
7555: hr_utility.set_location('p_date_mandatory_flag '|| p_date_mandatory_flag , 1687);
7556: end if;
7557: if g_debug then
7558: hr_utility.set_location('p_compute_dates_flag '|| p_compute_dates_flag , 1687);
7559: end if;
7560: if g_debug then
7561: hr_utility.set_location('p_elig_per_elctbl_chc_id '|| p_elig_per_elctbl_chc_id , 1687);
7562: end if;
7557: if g_debug then
7558: hr_utility.set_location('p_compute_dates_flag '|| p_compute_dates_flag , 1687);
7559: end if;
7560: if g_debug then
7561: hr_utility.set_location('p_elig_per_elctbl_chc_id '|| p_elig_per_elctbl_chc_id , 1687);
7562: end if;
7563: if g_debug then
7564: hr_utility.set_location('p_acty_base_rt_id '|| p_acty_base_rt_id , 1687);
7565: end if;
7560: if g_debug then
7561: hr_utility.set_location('p_elig_per_elctbl_chc_id '|| p_elig_per_elctbl_chc_id , 1687);
7562: end if;
7563: if g_debug then
7564: hr_utility.set_location('p_acty_base_rt_id '|| p_acty_base_rt_id , 1687);
7565: end if;
7566: if g_debug then
7567: hr_utility.set_location('p_business_group_id '|| p_business_group_id , 1687);
7568: end if;
7563: if g_debug then
7564: hr_utility.set_location('p_acty_base_rt_id '|| p_acty_base_rt_id , 1687);
7565: end if;
7566: if g_debug then
7567: hr_utility.set_location('p_business_group_id '|| p_business_group_id , 1687);
7568: end if;
7569: if g_debug then
7570: hr_utility.set_location('p_start_date '|| p_start_date , 1687);
7571: end if;
7566: if g_debug then
7567: hr_utility.set_location('p_business_group_id '|| p_business_group_id , 1687);
7568: end if;
7569: if g_debug then
7570: hr_utility.set_location('p_start_date '|| p_start_date , 1687);
7571: end if;
7572: if g_debug then
7573: hr_utility.set_location('p_end_date '|| p_end_date , 1687);
7574: end if;
7569: if g_debug then
7570: hr_utility.set_location('p_start_date '|| p_start_date , 1687);
7571: end if;
7572: if g_debug then
7573: hr_utility.set_location('p_end_date '|| p_end_date , 1687);
7574: end if;
7575: if g_debug then
7576: hr_utility.set_location('p_effective_date '|| p_effective_date , 1687);
7577: end if;
7572: if g_debug then
7573: hr_utility.set_location('p_end_date '|| p_end_date , 1687);
7574: end if;
7575: if g_debug then
7576: hr_utility.set_location('p_effective_date '|| p_effective_date , 1687);
7577: end if;
7578: if g_debug then
7579: hr_utility.set_location('p_lf_evt_ocrd_dt '|| p_lf_evt_ocrd_dt , 1687);
7580: end if;
7575: if g_debug then
7576: hr_utility.set_location('p_effective_date '|| p_effective_date , 1687);
7577: end if;
7578: if g_debug then
7579: hr_utility.set_location('p_lf_evt_ocrd_dt '|| p_lf_evt_ocrd_dt , 1687);
7580: end if;
7581: end if;
7582: */
7583: --
7584:
7585:
7586: l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
7587:
7588: hr_utility.set_location('l_effective_date ' || l_effective_date, 2080);
7589: --
7590: -- Get the necessary info to start processing
7591: --
7592: if p_cache_mode
7606: --
7607: elsif p_elig_per_elctbl_chc_id is not null then
7608: --
7609: if g_debug then
7610: hr_utility.set_location(l_proc , 20);
7611: end if;
7612: --
7613: -- get from elig_per_elctbl_chc
7614: -- Note: Don't need all the args just the ones for direct use by
7626: l_fonm_cvg_strt_dt
7627: ;
7628: --
7629: if g_debug then
7630: hr_utility.set_location('l_oipl_id '||l_oipl_id,19);
7631: end if;
7632: if c_epe_info%notfound then
7633: close c_epe_info;
7634: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
7637: fnd_message.set_token('PROC',l_proc);
7638: fnd_message.raise_error;
7639: end if;
7640: if g_debug then
7641: hr_utility.set_location(l_proc , 40);
7642: end if;
7643:
7644: close c_epe_info;
7645: else
7643:
7644: close c_epe_info;
7645: else
7646: if g_debug then
7647: hr_utility.set_location(l_proc , 50);
7648: end if;
7649: --
7650: -- use args
7651: --
7659: --
7660: end if;
7661: --
7662: if g_debug then
7663: hr_utility.set_location(l_proc , 60);
7664: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7665: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7666: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7667: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7660: end if;
7661: --
7662: if g_debug then
7663: hr_utility.set_location(l_proc , 60);
7664: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7665: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7666: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7667: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7668: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7661: --
7662: if g_debug then
7663: hr_utility.set_location(l_proc , 60);
7664: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7665: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7666: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7667: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7668: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7669: hr_utility.set_location('plan ' || p_pl_id , 60);
7662: if g_debug then
7663: hr_utility.set_location(l_proc , 60);
7664: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7665: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7666: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7667: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7668: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7669: hr_utility.set_location('plan ' || p_pl_id , 60);
7670:
7663: hr_utility.set_location(l_proc , 60);
7664: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7665: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7666: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7667: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7668: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7669: hr_utility.set_location('plan ' || p_pl_id , 60);
7670:
7671: end if;
7664: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7665: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7666: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7667: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7668: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7669: hr_utility.set_location('plan ' || p_pl_id , 60);
7670:
7671: end if;
7672:
7665: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7666: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7667: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7668: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7669: hr_utility.set_location('plan ' || p_pl_id , 60);
7670:
7671: end if;
7672:
7673:
7680: --
7681: -- Get the ptip id for the pl and pgm
7682: --
7683: if l_ptip_id is null then
7684: -- hr_utility.set_location(' c_gptip_id: '||l_proc , 140);
7685: open c_gptip_id
7686: (c_pl_id => l_pl_id
7687: ,c_pgm_id => l_pgm_id
7688: ,c_effective_date => l_effective_date
7689: );
7690: fetch c_gptip_id into l_ptip_id;
7691: close c_gptip_id;
7692: if g_debug then
7693: hr_utility.set_location(' Dn c_gptip_id: '||l_proc , 140);
7694: end if;
7695: else
7696: l_ptip_id := p_par_ptip_id;
7697: end if;
7699: -- Get the plip id for the pl and pgm
7700: --
7701: if l_plip_id is null then
7702: if g_debug then
7703: hr_utility.set_location(' c_gplip_id: '||l_proc , 140);
7704: end if;
7705: open c_gplip_id
7706: (c_pl_id => l_pl_id
7707: ,c_pgm_id => l_pgm_id
7709: );
7710: fetch c_gplip_id into l_plip_id;
7711: close c_gplip_id;
7712: if g_debug then
7713: hr_utility.set_location(' Dn c_gplip_id: '||l_proc , 140);
7714: end if;
7715: else
7716: l_plip_id := p_par_plip_id;
7717: end if;
7717: end if;
7718: --
7719: if l_enrt_perd_id is not null then
7720: if g_debug then
7721: hr_utility.set_location(' Op c_SCFP: '||l_proc , 140);
7722: end if;
7723: --
7724: -- Check if the enrt perd for pl id is passed in
7725: --
7750: ,c_per_in_ler_id => l_per_in_ler_id -- bug 4356591
7751: );
7752: fetch c_sched_for_plip into l_rec;
7753: if g_debug then
7754: hr_utility.set_location(' Dn Fet c_SCFP: '||l_proc , 140);
7755: hr_utility.set_location(' l_rec.enrt_cvg_strt_dt_cd : '|| l_rec.enrt_cvg_strt_dt_cd , 140);
7756: hr_utility.set_location(' l_rec.enrt_cvg_end_dt_cd : '|| l_rec.enrt_cvg_end_dt_cd , 140);
7757: end if;
7758: l_found:=c_sched_for_plip%found;
7751: );
7752: fetch c_sched_for_plip into l_rec;
7753: if g_debug then
7754: hr_utility.set_location(' Dn Fet c_SCFP: '||l_proc , 140);
7755: hr_utility.set_location(' l_rec.enrt_cvg_strt_dt_cd : '|| l_rec.enrt_cvg_strt_dt_cd , 140);
7756: hr_utility.set_location(' l_rec.enrt_cvg_end_dt_cd : '|| l_rec.enrt_cvg_end_dt_cd , 140);
7757: end if;
7758: l_found:=c_sched_for_plip%found;
7759: else
7752: fetch c_sched_for_plip into l_rec;
7753: if g_debug then
7754: hr_utility.set_location(' Dn Fet c_SCFP: '||l_proc , 140);
7755: hr_utility.set_location(' l_rec.enrt_cvg_strt_dt_cd : '|| l_rec.enrt_cvg_strt_dt_cd , 140);
7756: hr_utility.set_location(' l_rec.enrt_cvg_end_dt_cd : '|| l_rec.enrt_cvg_end_dt_cd , 140);
7757: end if;
7758: l_found:=c_sched_for_plip%found;
7759: else
7760: --
7761: -- do life event plip processing
7762: -- also used for unrestricted plip
7763: --
7764: if g_debug then
7765: hr_utility.set_location(' Op c_glee_epfp_id: '||l_proc , 150);
7766: end if;
7767: --
7768: if l_enrt_perd_for_pl_id is null then
7769: --
7781: --
7782: end if;
7783: --
7784: if g_debug then
7785: hr_utility.set_location(' Dn c_glee_epfp_id: '||l_proc , 150);
7786: end if;
7787: --
7788: -- Fetch the first row from the union cache
7789: --
7793: and p_pl_row.pl_id is not null
7794: then
7795:
7796: if g_debug then
7797: hr_utility.set_location(' Building the union cache ', 1687);
7798: end if;
7799: --
7800: -- Build up union set
7801: --
7823: end if;
7824: close c_gepp_dets;
7825: --
7826: if g_debug then
7827: hr_utility.set_location(' l_lee_rsn_id: '||l_lee_rsn_id, 1687);
7828: end if;
7829:
7830: open c_gleersn_dets
7831: (c_lee_rsn_id => l_lee_rsn_id
7955: --
7956: end if;
7957: --
7958: if g_debug then
7959: hr_utility.set_location(' Done Building the union cache l_unionele_num = '||l_unionele_num, 1687);
7960: end if;
7961: else
7962: --
7963: open c_life_for_plip
7970: ,c_epfp_id => l_enrt_perd_for_pl_id
7971: ,c_per_in_ler_id => l_per_in_ler_id
7972: );
7973: if g_debug then
7974: hr_utility.set_location('Fetching rt start date cd' , 1687);
7975: end if;
7976: fetch c_life_for_plip into l_rec;
7977: if g_debug then
7978: hr_utility.set_location(' Dn Fet c_LFP: '||l_proc , 150);
7974: hr_utility.set_location('Fetching rt start date cd' , 1687);
7975: end if;
7976: fetch c_life_for_plip into l_rec;
7977: if g_debug then
7978: hr_utility.set_location(' Dn Fet c_LFP: '||l_proc , 150);
7979: end if;
7980: l_found:=c_life_for_plip%found;
7981: --
7982: end if;
7984: end if;
7985: else
7986: if l_enrt_perd_id is not null then
7987: if g_debug then
7988: hr_utility.set_location(l_proc , 190);
7989: end if;
7990: -- do scheduled pl_nip processing
7991: open c_sched_for_pl_nip;
7992: fetch c_sched_for_pl_nip into l_rec;
7992: fetch c_sched_for_pl_nip into l_rec;
7993: l_found:=c_sched_for_pl_nip%found;
7994: else
7995: if g_debug then
7996: hr_utility.set_location(l_proc , 200);
7997: end if;
7998: -- do life event pl_nip processing
7999: -- also used for unrestricted pl_nip
8000: if g_debug then
7997: end if;
7998: -- do life event pl_nip processing
7999: -- also used for unrestricted pl_nip
8000: if g_debug then
8001: hr_utility.set_location(' Doing c_life_for_pl_nip', 1687);
8002: end if;
8003: open c_life_for_pl_nip;
8004: fetch c_life_for_pl_nip into l_rec;
8005: l_found:=c_life_for_pl_nip%found;
8006: end if;
8007: end if;
8008: loop
8009: if g_debug then
8010: hr_utility.set_location(l_proc , 240);
8011: end if;
8012: exit when l_found = FALSE;
8013: --
8014: -- process rates
8013: --
8014: -- process rates
8015: --
8016: if g_debug then
8017: hr_utility.set_location(l_proc , 250);
8018: end if;
8019: if p_which_dates_cd in ('R','B') then
8020: if l_rt_strt_dt_cd is null and
8021: l_rec.rt_strt_dt_cd is not null then
8019: if p_which_dates_cd in ('R','B') then
8020: if l_rt_strt_dt_cd is null and
8021: l_rec.rt_strt_dt_cd is not null then
8022: if g_debug then
8023: hr_utility.set_location(l_proc , 270);
8024: end if;
8025: l_rt_strt_dt_cd:=l_rec.rt_strt_dt_cd;
8026: l_rt_strt_dt_rl:=l_rec.rt_strt_dt_rl;
8027: end if;
8027: end if;
8028: if l_rt_end_dt_cd is null and
8029: l_rec.rt_end_dt_cd is not null then
8030: if g_debug then
8031: hr_utility.set_location(l_proc , 290);
8032: end if;
8033: l_rt_end_dt_cd:=l_rec.rt_end_dt_cd;
8034: l_rt_end_dt_rl:=l_rec.rt_end_dt_rl;
8035: end if;
8035: end if;
8036: end if;
8037: --
8038: if g_debug then
8039: hr_utility.set_location(l_proc , 310);
8040: end if;
8041: --
8042: -- process coverage
8043: --
8044: if p_which_dates_cd in ('C','B') then
8045: if l_enrt_cvg_strt_dt_cd is null and
8046: l_rec.enrt_cvg_strt_dt_cd is not null then
8047: if g_debug then
8048: hr_utility.set_location(l_proc , 330);
8049: end if;
8050: l_enrt_cvg_strt_dt_cd:=l_rec.enrt_cvg_strt_dt_cd;
8051: l_enrt_cvg_strt_dt_rl:=l_rec.enrt_cvg_strt_dt_rl;
8052: end if;
8052: end if;
8053: if l_enrt_cvg_end_dt_cd is null and
8054: l_rec.enrt_cvg_end_dt_cd is not null then
8055: if g_debug then
8056: hr_utility.set_location(l_proc , 350);
8057: end if;
8058: l_enrt_cvg_end_dt_cd:=l_rec.enrt_cvg_end_dt_cd;
8059: l_enrt_cvg_end_dt_rl:=l_rec.enrt_cvg_end_dt_rl;
8060: end if;
8060: end if;
8061: end if;
8062: --
8063: if g_debug then
8064: hr_utility.set_location(l_proc , 360);
8065: end if;
8066: --
8067: -- determine if done
8068: --
8077: l_enrt_cvg_end_dt_cd is not null and
8078: l_rt_strt_dt_cd is not null and
8079: l_rt_end_dt_cd is not null)) then
8080: if g_debug then
8081: hr_utility.set_location(l_proc , 380);
8082: end if;
8083: l_found:=FALSE;
8084: l_done:=TRUE;
8085: else
8083: l_found:=FALSE;
8084: l_done:=TRUE;
8085: else
8086: if g_debug then
8087: hr_utility.set_location(l_proc , 390);
8088: end if;
8089: --
8090: -- if not done then get the next row
8091: --
8091: --
8092: if l_pgm_id is not null then
8093: if l_enrt_perd_id is not null then
8094: if g_debug then
8095: hr_utility.set_location(l_proc , 410);
8096: end if;
8097: -- do scheduled plip processing
8098: fetch c_sched_for_plip into l_rec;
8099: l_found:=c_sched_for_plip%found;
8100: else
8101: -- do life event plip processing
8102: -- also used for unrestricted pl_nip
8103: if g_debug then
8104: hr_utility.set_location(' Fet c_LFPLIP: '||l_proc , 420);
8105: end if;
8106: if p_pgm_row.pgm_id is not null
8107: and p_ptip_row.ptip_id is not null
8108: and p_plip_row.plip_id is not null
8139: end if;
8140: else
8141: if l_enrt_perd_id is not null then
8142: if g_debug then
8143: hr_utility.set_location(l_proc , 460);
8144: end if;
8145: -- do scheduled pl_nip processing
8146: fetch c_sched_for_pl_nip into l_rec;
8147: l_found:=c_sched_for_pl_nip%found;
8146: fetch c_sched_for_pl_nip into l_rec;
8147: l_found:=c_sched_for_pl_nip%found;
8148: else
8149: if g_debug then
8150: hr_utility.set_location(l_proc , 470);
8151: end if;
8152: -- do life event pl_nip processing
8153: -- also used for unrestricted pl_nip
8154: fetch c_life_for_pl_nip into l_rec;
8156: end if;
8157: end if;
8158: end if;
8159: if g_debug then
8160: hr_utility.set_location(' End loop: '||l_proc , 420);
8161: end if;
8162: end loop;
8163: --
8164: -- close cursors
8165: --
8166: if l_pgm_id is not null then
8167: if l_enrt_perd_id is not null then
8168: if g_debug then
8169: hr_utility.set_location(l_proc , 860);
8170: end if;
8171: -- do scheduled plip processing
8172: close c_sched_for_plip;
8173: else
8171: -- do scheduled plip processing
8172: close c_sched_for_plip;
8173: else
8174: if g_debug then
8175: hr_utility.set_location(l_proc , 870);
8176: end if;
8177: -- do life event plip processing
8178: -- also used for unrestricted plip
8179: if p_pgm_row.pgm_id is not null
8193: end if;
8194: else
8195: if l_enrt_perd_id is not null then
8196: if g_debug then
8197: hr_utility.set_location(l_proc , 900);
8198: end if;
8199: -- do scheduled pl_nip processing
8200: close c_sched_for_pl_nip;
8201: else
8199: -- do scheduled pl_nip processing
8200: close c_sched_for_pl_nip;
8201: else
8202: if g_debug then
8203: hr_utility.set_location(l_proc , 910);
8204: end if;
8205: -- do life event pl_nip processing
8206: -- also used for unrestricted pl_nip
8207: close c_life_for_pl_nip;
8234: -- must be done
8235: --
8236: if l_done=FALSE then
8237: if g_debug then
8238: hr_utility.set_location(l_proc , 530);
8239: end if;
8240:
8241: -- Bug No 3965571 : All ids are replaced by their names in the error message's tokens.
8242:
8242:
8243: if p_which_dates_cd in ('R','B') and
8244: l_rt_strt_dt_cd is null then
8245: if g_debug then
8246: hr_utility.set_location('BEN_91455_RT_STRT_DT_NOT_FOUND' , 540);
8247: end if;
8248: fnd_message.set_name('BEN','BEN_91455_RT_STRT_DT_NOT_FOUND');
8249: fnd_message.set_token('PLAN_ID',l_pln_name);
8250: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8253: fnd_message.raise_error;
8254: elsif p_which_dates_cd in ('R','B') and
8255: l_rt_end_dt_cd is null then
8256: if g_debug then
8257: hr_utility.set_location('BEN_91703_NOT_DET_RATE_END_DT' , 550);
8258: end if;
8259: fnd_message.set_name('BEN','BEN_91703_NOT_DET_RATE_END_DT');
8260: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8261: fnd_message.set_token('PGM_ID',l_pgm_name);
8264: fnd_message.raise_error;
8265: elsif p_which_dates_cd in ('C','B') and
8266: l_enrt_cvg_strt_dt_cd is null then
8267: if g_debug then
8268: hr_utility.set_location('BEN_91453_CVG_STRT_DT_NOT_FOUN' , 560);
8269: end if;
8270: fnd_message.set_name('BEN','BEN_91453_CVG_STRT_DT_NOT_FOUN');
8271: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8272: fnd_message.set_token('PGM_ID',l_pgm_name);
8275: fnd_message.raise_error;
8276: elsif p_which_dates_cd in ('C','B') and
8277: l_enrt_cvg_end_dt_cd is null then
8278: if g_debug then
8279: hr_utility.set_location(l_proc , 570);
8280: end if;
8281: fnd_message.set_name('BEN','BEN_91702_NOT_DET_CVG_END_DT');
8282: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8283: fnd_message.set_token('PGM_ID',l_pgm_name);
8292: if p_compute_dates_flag='Y' then
8293: --
8294: if (l_enrt_cvg_strt_dt_cd is not NULL) then
8295: if g_debug then
8296: hr_utility.set_location(' ECSDCD DETDT_MN '||l_proc , 630);
8297: end if;
8298: main
8299: (p_cache_mode => p_cache_mode
8300: ,p_date_cd => l_enrt_cvg_strt_dt_cd
8311: ,p_oipl_id => l_oipl_id
8312: ,p_acty_base_rt_id => p_acty_base_rt_id
8313: ,p_start_date => p_start_date);
8314: if g_debug then
8315: hr_utility.set_location(' DN ECSDCD DETDT_MN '||l_proc , 630);
8316: end if;
8317: --
8318: end if;
8319: --
8326: 'LWEM','LWODBED','WAED','WALDCM' ,
8327: 'WALDCPP','WALDCPPY','WALDLPPEPPY',
8328: 'WEM','WODBED' )
8329: then
8330: hr_utility.set_location('pasing start date '||l_enrt_cvg_strt_dt , 199);
8331: l_pass_cvg_strt_dt := l_enrt_cvg_strt_dt ;
8332: else
8333: l_pass_cvg_strt_dt := p_start_date ;
8334: end if;
8335: */
8336: l_pass_cvg_strt_dt := p_start_date ;
8337: --
8338: if (l_enrt_cvg_end_dt_cd is not NULL) then
8339: hr_utility.set_location(' ECEDCD DETDT_MN '||l_proc , 610);
8340: main
8341: (p_cache_mode => p_cache_mode
8342: ,p_date_cd => l_enrt_cvg_end_dt_cd
8343: ,p_formula_id => l_enrt_cvg_end_dt_rl
8353: ,p_acty_base_rt_id => p_acty_base_rt_id
8354: ,p_start_date => l_pass_cvg_strt_dt
8355: ,p_returned_date => l_enrt_cvg_end_dt
8356: );
8357: -- hr_utility.set_location(' Dn ECEDCD DETDT_MN '||l_proc , 610);
8358: --
8359: end if;
8360: --
8361:
8361:
8362: -- rate start date calcualted before the end date
8363: if l_rt_strt_dt_cd is not NULL then
8364: if g_debug then
8365: hr_utility.set_location(' SDC DETDT_MN '||l_proc , 670);
8366: end if;
8367: --
8368: -- Passing Enrollment coverage
8369: --
8394: ,p_acty_base_rt_id => p_acty_base_rt_id
8395: ,p_start_date => l_pass_cvg_strt_dt );
8396:
8397: if g_debug then
8398: hr_utility.set_location(' rate start date = '||l_rt_strt_dt , 1687);
8399: end if;
8400: if g_debug then
8401: hr_utility.set_location(' Dn SDC DETDT_MN '||l_proc , 670);
8402: end if;
8397: if g_debug then
8398: hr_utility.set_location(' rate start date = '||l_rt_strt_dt , 1687);
8399: end if;
8400: if g_debug then
8401: hr_utility.set_location(' Dn SDC DETDT_MN '||l_proc , 670);
8402: end if;
8403: --- assign the fonm rat from the rate so the end date rule can use the fonm value
8404: if ben_manage_life_events.fonm = 'Y' or l_fonm_cvg_strt_dt is not null then
8405: if ben_manage_life_events.g_fonm_rt_strt_dt is null or
8405: if ben_manage_life_events.g_fonm_rt_strt_dt is null or
8406: ben_manage_life_events.g_fonm_rt_strt_dt <> l_rt_strt_dt then
8407: ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt ;
8408: end if ;
8409: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
8410: end if ;
8411:
8412: end if;
8413:
8431: l_pass_cvg_end_dt := p_end_date;
8432: end if;
8433: --
8434: if g_debug then
8435: hr_utility.set_location(' EDC DETDT_MN '||l_proc , 650);
8436: end if;
8437: main
8438: (p_cache_mode => p_cache_mode
8439: ,p_date_cd => l_rt_end_dt_cd
8450: ,p_oipl_id => l_oipl_id
8451: ,p_acty_base_rt_id => p_acty_base_rt_id
8452: ,p_start_date => l_pass_cvg_end_dt);
8453: if g_debug then
8454: hr_utility.set_location(' Dn EDC DETDT_MN '||l_proc , 650);
8455: end if;
8456:
8457: end if;
8458: --
8472: --
8473: if l_rt_end_dt is null
8474: then
8475: if g_debug then
8476: hr_utility.set_location('Special condition for LODBEWM ODBEWM ', 199);
8477: end if;
8478: l_rt_end_dt := l_rt_strt_dt - 1 ;
8479: if g_debug then
8480: hr_utility.set_location('l_rt_end_dt '||l_rt_end_dt , 199) ;
8476: hr_utility.set_location('Special condition for LODBEWM ODBEWM ', 199);
8477: end if;
8478: l_rt_end_dt := l_rt_strt_dt - 1 ;
8479: if g_debug then
8480: hr_utility.set_location('l_rt_end_dt '||l_rt_end_dt , 199) ;
8481: end if;
8482: end if;
8483: --
8484: end if;
8503: then
8504: --
8505: l_rt_strt_dt := l_enrt_cvg_strt_dt ;
8506: if g_debug then
8507: hr_utility.set_location('Case 1',15);
8508: end if;
8509: --
8510: elsif to_char(l_enrt_cvg_strt_dt, 'DD') > 15 then
8511: --
8510: elsif to_char(l_enrt_cvg_strt_dt, 'DD') > 15 then
8511: --
8512: l_rt_strt_dt := round(l_enrt_cvg_strt_dt,'Month') ;
8513: if g_debug then
8514: hr_utility.set_location('Case 2',15);
8515: end if;
8516: --
8517: else
8518: --
8517: else
8518: --
8519: l_rt_strt_dt := round(l_enrt_cvg_strt_dt,'Month') + 15 ;
8520: if g_debug then
8521: hr_utility.set_location('Case 3'||l_enrt_cvg_strt_dt,15);
8522: end if;
8523:
8524: --
8525: end if;
8534: if l_rt_strt_dt is not null and
8535: (not do_date_at_enrollment(l_rt_strt_dt_cd)) and
8536: p_date_mandatory_flag='N' then
8537: if g_debug then
8538: hr_utility.set_location(l_proc , 700);
8539: end if;
8540: l_rt_strt_dt_cd:=null;
8541: l_rt_strt_dt_rl:=null;
8542: end if;
8544: if l_rt_end_dt is not null and
8545: (not do_date_at_enrollment(l_rt_end_dt_cd)) and
8546: p_date_mandatory_flag='N' then
8547: if g_debug then
8548: hr_utility.set_location(l_proc , 720);
8549: end if;
8550: l_rt_end_dt_cd:=null;
8551: l_rt_end_dt_rl:=null;
8552: end if;
8554: if l_enrt_cvg_strt_dt is not null and
8555: (not do_date_at_enrollment(l_enrt_cvg_strt_dt_cd)) and
8556: p_date_mandatory_flag='N' then
8557: if g_debug then
8558: hr_utility.set_location(l_proc , 740);
8559: end if;
8560: l_enrt_cvg_strt_dt_cd:=null;
8561: l_enrt_cvg_strt_dt_rl:=null;
8562: end if;
8564: if l_enrt_cvg_end_dt is not null and
8565: (not do_date_at_enrollment(l_enrt_cvg_end_dt_cd)) and
8566: p_date_mandatory_flag ='N' then
8567: if g_debug then
8568: hr_utility.set_location(l_proc , 760);
8569: end if;
8570: l_enrt_cvg_end_dt_cd:=null;
8571: l_enrt_cvg_end_dt_rl:=null;
8572: end if;
8578:
8579: if p_date_mandatory_flag='Y' and
8580: p_compute_dates_flag='Y' then
8581: if g_debug then
8582: hr_utility.set_location(l_proc , 780);
8583: end if;
8584:
8585: -- Bug No 3965571 : All ids are replaced by their names in the error message's tokens.
8586:
8586:
8587: if p_which_dates_cd in ('R','B') and
8588: l_rt_strt_dt is null then
8589: if g_debug then
8590: hr_utility.set_location('BEN_91455_RT_STRT_DT_NOT_FOUND',37);
8591: end if;
8592: fnd_message.set_name('BEN','BEN_91455_RT_STRT_DT_NOT_FOUND');
8593: fnd_message.set_token('PLAN_ID',l_pln_name);
8594: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8597: fnd_message.raise_error;
8598: elsif p_which_dates_cd in ('R','B') and
8599: l_rt_end_dt is null then
8600: if g_debug then
8601: hr_utility.set_location('BEN_91703_NOT_DET_RATE_END_DT',37);
8602: end if;
8603: fnd_message.set_name('BEN','BEN_91703_NOT_DET_RATE_END_DT');
8604: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8605: fnd_message.set_token('PGM_ID',l_pgm_name);
8608: fnd_message.raise_error;
8609: elsif p_which_dates_cd in ('C','B') and
8610: l_enrt_cvg_strt_dt is null then
8611: if g_debug then
8612: hr_utility.set_location('BEN_91453_CVG_STRT_DT_NOT_FOUN',37);
8613: end if;
8614: fnd_message.set_name('BEN','BEN_91453_CVG_STRT_DT_NOT_FOUN');
8615: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8616: fnd_message.set_token('PGM_ID',l_pgm_name);
8619: fnd_message.raise_error;
8620: elsif p_which_dates_cd in ('C','B') and
8621: l_enrt_cvg_end_dt is null then
8622: if g_debug then
8623: hr_utility.set_location('BEN_91702_NOT_DET_CVG_END_DT',37);
8624: end if;
8625: fnd_message.set_name('BEN','BEN_91702_NOT_DET_CVG_END_DT');
8626: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8627: fnd_message.set_token('PGM_ID',l_pgm_name);
8646: p_enrt_cvg_end_dt:=l_enrt_cvg_end_dt;
8647: p_enrt_cvg_end_dt_cd:=l_enrt_cvg_end_dt_cd;
8648: p_enrt_cvg_end_dt_rl:=l_enrt_cvg_end_dt_rl;
8649: --
8650: -- hr_utility.set_location(' Leaving:'||l_proc, 930);
8651: --
8652:
8653: exception -- nocopy changes
8654: --