[Home] [Help]
2: /* $Header: bendetdt.pkb 120.30.12010000.2 2008/12/19 13:10:32 sallumwa 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;
1100: l_mode l_env.mode_cd%TYPE; -- 6823087 (CWB Requirement for 'ENTRBL')
1101: --
1102: begin
1103:
1104: -- hr_utility.trace_on (null, 'ORACLE');
1105:
1106: g_debug := hr_utility.debug_enabled;
1107: --
1108: -- Commented out for performance
1102: begin
1103:
1104: -- hr_utility.trace_on (null, 'ORACLE');
1105:
1106: g_debug := hr_utility.debug_enabled;
1107: --
1108: -- Commented out for performance
1109: --
1110: --
1107: --
1108: -- Commented out for performance
1109: --
1110: --
1111: -- hr_utility.set_location('Entering :'|| l_proc,10);
1112: -- comment these out until we need to debug a code
1113: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1114: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1115: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1109: --
1110: --
1111: -- hr_utility.set_location('Entering :'|| l_proc,10);
1112: -- comment these out until we need to debug a code
1113: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1114: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1115: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1116: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1110: --
1111: -- hr_utility.set_location('Entering :'|| l_proc,10);
1112: -- comment these out until we need to debug a code
1113: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1114: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1115: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1116: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1111: -- hr_utility.set_location('Entering :'|| l_proc,10);
1112: -- comment these out until we need to debug a code
1113: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1114: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1115: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1116: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1112: -- comment these out until we need to debug a code
1113: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1114: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1115: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1116: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1113: -- hr_utility.set_location('p_date_cd : '||p_date_cd ,15);
1114: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1115: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1116: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1121: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1114: -- hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id ,15);
1115: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1116: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1121: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1122: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1115: -- hr_utility.set_location('p_person_id : '||p_person_id ,15);
1116: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1121: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1122: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1123: -- hr_utility.set_location('person : '||p_person_id ,665);
1116: -- hr_utility.set_location('p_pgm_id : '||p_pgm_id ,15);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1121: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1122: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1123: -- hr_utility.set_location('person : '||p_person_id ,665);
1124: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1117: -- hr_utility.set_location('p_pl_id : '||p_pl_id ,15);
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1121: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1122: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1123: -- hr_utility.set_location('person : '||p_person_id ,665);
1124: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1125: --
1118: -- hr_utility.set_location('p_oipl_id : '||p_oipl_id ,15);
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1121: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1122: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1123: -- hr_utility.set_location('person : '||p_person_id ,665);
1124: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1125: --
1126: --- Fonm2 Determine the fonm and effective date
1119: -- hr_utility.set_location('p_elig_per_elctbl_chc_id: '||p_elig_per_elctbl_chc_id ,15);
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1121: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1122: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1123: -- hr_utility.set_location('person : '||p_person_id ,665);
1124: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1125: --
1126: --- Fonm2 Determine the fonm and effective date
1127: l_fonm_rt_cvg_strt_dt := nvl(p_fonm_cvg_strt_dt,p_fonm_rt_strt_dt) ;
1120: -- hr_utility.set_location('p_effective_date : '||p_effective_date ,15);
1121: -- hr_utility.set_location('p_lf_evt_ocrd_dt : '||p_lf_evt_ocrd_dt ,15);
1122: -- hr_utility.set_location('p_start_date : '||p_start_date ,15);
1123: -- hr_utility.set_location('person : '||p_person_id ,665);
1124: -- hr_utility.set_location('parent : '||p_parent_person_id ,665);
1125: --
1126: --- Fonm2 Determine the fonm and effective date
1127: l_fonm_rt_cvg_strt_dt := nvl(p_fonm_cvg_strt_dt,p_fonm_rt_strt_dt) ;
1128: l_lf_evt_ocrd_dt := nvl(l_fonm_rt_cvg_strt_dt, p_lf_evt_ocrd_dt ) ;
1137: -- procedures will have to provide these values: per_in_ler_id,
1138: -- person_id, pl_id, oipl_id, pgm_id.
1139: --
1140: if g_debug then
1141: hr_utility.set_location('open c_per_elig_elctbl_chc',10);
1142: end if;
1143:
1144: open c_per_elig_elctbl_chc;
1145:
1154: l_business_group_id,
1155: l_enrt_cvg_strt_dt;
1156:
1157: if g_debug then
1158: hr_utility.set_location('l_enrt_cvg_strt_dt from c_per_elig_elctbl_chc'||l_enrt_cvg_strt_dt,19);
1159: end if;
1160:
1161: if c_per_elig_elctbl_chc%notfound then
1162: close c_per_elig_elctbl_chc;
1160:
1161: if c_per_elig_elctbl_chc%notfound then
1162: close c_per_elig_elctbl_chc;
1163: if g_debug then
1164: hr_utility.set_location('BEN_91529_CANNOT_FIND_ELEC_CHC',40);
1165: end if;
1166: fnd_message.set_name('BEN','BEN_91529_CANNOT_FIND_ELEC_CHC');
1167: fnd_message.set_token('L_PROC',l_proc);
1168: fnd_message.set_token('ELIG_PER_ELCTBL_CHC_ID',p_elig_per_elctbl_chc_id);
1207: fetch c_pay_id into l_payroll_id ;
1208: if c_pay_id%notfound then
1209: l_person_id := p_parent_person_id ;
1210: if g_debug then
1211: hr_utility.set_location('for chold prill not found ' ,665);
1212: end if;
1213: end if ;
1214: close c_pay_id ;
1215: end if ;
1214: close c_pay_id ;
1215: end if ;
1216:
1217: if g_debug then
1218: hr_utility.set_location('person : '||l_person_id ,665);
1219: end if;
1220: /*
1221:
1222: -- Check for cache mode. If so then get the per in ler details
1233: --
1234: */
1235:
1236: if g_debug then
1237: hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt,19);
1238: end if;
1239: if g_debug then
1240: hr_utility.set_location('p_date_cd :'||p_date_cd,19);
1241: end if;
1236: if g_debug then
1237: hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt,19);
1238: end if;
1239: if g_debug then
1240: hr_utility.set_location('p_date_cd :'||p_date_cd,19);
1241: end if;
1242:
1243: -- AED - Event Date
1244:
1244:
1245: if p_date_cd in ( 'AED' , 'NUMDOE') then
1246: --
1247: if g_debug then
1248: hr_utility.set_location('Entering AED',10);
1249: end if;
1250: --
1251: if l_lf_evt_ocrd_dt is null then
1252: --
1262: --
1263: elsif p_date_cd = 'WAED' then
1264: --
1265: if g_debug then
1266: hr_utility.set_location('Entering AED',10);
1267: end if;
1268: --
1269: if l_lf_evt_ocrd_dt is null then
1270: --
1301: p_date_cd = '15DAFDEP' or p_date_cd = '20DAFDEP' or
1302: p_date_cd = '25DAFDEP' or p_date_cd = '30DAFDEP' then
1303: --
1304: if g_debug then
1305: hr_utility.set_location('Entering '||p_date_cd,10);
1306: end if;
1307: --
1308: open c_cm_enrt_perd_strt_dt;
1309: fetch c_cm_enrt_perd_strt_dt into l_cm_date;
1317: else
1318: --
1319: close c_cm_enrt_perd_strt_dt;
1320: if g_debug then
1321: hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',40);
1322: end if;
1323: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
1324: fnd_message.set_token('DATE_CODE',p_date_cd);
1325: fnd_message.set_token('L_PROC',l_proc);
1403: or p_date_cd = 'EEELDPPADI' or p_date_cd = 'EEELDMADI' or p_date_cd = 'EEDI'
1404: or p_date_cd = 'OCED' or p_date_cd = 'PECED' then
1405: --
1406: if g_debug then
1407: hr_utility.set_location('Entering '||p_date_cd,23);
1408: end if;
1409: --
1410: if l_lf_evt_ocrd_dt is null then
1411: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
1417: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
1418: l_enrt_cvg_strt_dt,
1419: l_enrt_eff_strt_date;
1420: if g_debug then
1421: hr_utility.set_location('l_enrt_cvg_end_dt cursor returns ', 19);
1422: end if;
1423: close c_prtt_enrt_rslt_dts;
1424: else
1425: l_enrt_cvg_end_dt := p_start_date;
1452:
1453: if c_pay_period_for_date%notfound and l_enrt_eff_strt_date is not null then
1454: close c_pay_period_for_date;
1455: if g_debug then
1456: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',15);
1457: end if;
1458: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1459: fnd_message.set_token('DATE_CODE',p_date_cd);
1460: fnd_message.set_token('L_PROC',l_proc);
1480: -- End of Pay Period (On or) After
1481: if l_enrt_cvg_end_dt <> hr_api.g_eot then
1482: --
1483: if g_debug then
1484: hr_utility.set_location(' Step 2 ' ,19);
1485: end if;
1486: open c_pay_period_for_date(l_enrt_cvg_end_dt);
1487: fetch c_pay_period_for_date into
1488: l_start_date,
1490:
1491: if c_pay_period_for_date%notfound and l_enrt_eff_strt_date is not null then
1492: close c_pay_period_for_date;
1493: if g_debug then
1494: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',15);
1495: end if;
1496: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1497: fnd_message.set_token('DATE_CODE',p_date_cd);
1498: fnd_message.set_token('L_PROC',l_proc);
1504: close c_pay_period_for_date;
1505:
1506: else
1507: if g_debug then
1508: hr_utility.set_location('End of Time set 1 ' , 19 );
1509: end if;
1510: l_end_date := l_enrt_cvg_end_dt ;
1511: end if ;
1512:
1517: if p_date_cd = 'LDPPFEE' then
1518: -- End of Pay Period After
1519: -- retrieve last day of next pay period
1520: if g_debug then
1521: hr_utility.set_location(' Step 3 ' ,19);
1522: end if;
1523: open c_pay_period_for_date(l_end_date+1);
1524: fetch c_pay_period_for_date into
1525: l_start_date,
1528: if c_pay_period_for_date%notfound and
1529: l_enrt_eff_strt_date is not null then
1530: close c_pay_period_for_date;
1531: if g_debug then
1532: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',25);
1533: end if;
1534: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1535: fnd_message.set_token('DATE_CODE',p_date_cd);
1536: fnd_message.set_token('L_PROC',l_proc);
1544: end if ;
1545: --
1546: else
1547: if g_debug then
1548: hr_utility.set_location('End of Time set 2' , 19 );
1549: end if;
1550: l_end_date := l_enrt_cvg_end_dt ;
1551: end if;
1552:
1624: or p_date_cd = 'LESFDPPAD' or p_date_cd = 'LESFDMAD' or p_date_cd = 'FDPPELD'
1625: or p_date_cd = 'FDSMFES' or p_date_cd = 'FDSMCFES' or p_date_cd = 'LESFDSMAD'
1626: then
1627: if g_debug then
1628: hr_utility.set_location('Entering '||p_date_cd,17);
1629: end if;
1630: if g_debug then
1631: hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt , 17);
1632: end if;
1627: if g_debug then
1628: hr_utility.set_location('Entering '||p_date_cd,17);
1629: end if;
1630: if g_debug then
1631: hr_utility.set_location('l_lf_evt_ocrd_dt :'||l_lf_evt_ocrd_dt , 17);
1632: end if;
1633:
1634: if l_lf_evt_ocrd_dt is null then
1635: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
1633:
1634: if l_lf_evt_ocrd_dt is null then
1635: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
1636: if g_debug then
1637: hr_utility.set_location('In the get_event_date '||l_lf_evt_ocrd_dt,17);
1638: end if ;
1639: end if;
1640:
1641: if g_debug then
1638: end if ;
1639: end if;
1640:
1641: if g_debug then
1642: hr_utility.set_location('before cursor l_enrt_cvg_strt_dt:'||l_enrt_cvg_strt_dt,18);
1643: end if;
1644:
1645: open c_prtt_enrt_rslt_dts;
1646: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
1662: end if;
1663: --
1664: close c_prtt_enrt_rslt_dts;
1665: if g_debug then
1666: hr_utility.set_location('l_enrt_cvg_end_dt :'||l_enrt_cvg_end_dt , 18);
1667: end if;
1668: if g_debug then
1669: hr_utility.set_location('l_enrt_cvg_strt_dt:'||l_enrt_cvg_strt_dt,18);
1670: end if;
1665: if g_debug then
1666: hr_utility.set_location('l_enrt_cvg_end_dt :'||l_enrt_cvg_end_dt , 18);
1667: end if;
1668: if g_debug then
1669: hr_utility.set_location('l_enrt_cvg_strt_dt:'||l_enrt_cvg_strt_dt,18);
1670: end if;
1671: if g_debug then
1672: hr_utility.set_location('l_enrt_eff_strt_date:'||l_enrt_eff_strt_date,18);
1673: end if;
1668: if g_debug then
1669: hr_utility.set_location('l_enrt_cvg_strt_dt:'||l_enrt_cvg_strt_dt,18);
1670: end if;
1671: if g_debug then
1672: hr_utility.set_location('l_enrt_eff_strt_date:'||l_enrt_eff_strt_date,18);
1673: end if;
1674: --
1675: --
1676: if p_date_cd = 'LEMES' or p_date_cd = 'LFPPEMES' or p_date_cd = 'LFMEMES'
1721: then
1722: --
1723: p_returned_date := l_enrt_cvg_strt_dt ;
1724: if g_debug then
1725: hr_utility.set_location('Case 1',15);
1726: end if;
1727: --
1728: elsif to_char(l_enrt_cvg_strt_dt, 'DD') > 15 then
1729: --
1728: elsif to_char(l_enrt_cvg_strt_dt, 'DD') > 15 then
1729: --
1730: p_returned_date := round(l_enrt_cvg_strt_dt,'Month') ;
1731: if g_debug then
1732: hr_utility.set_location('Case 2',15);
1733: end if;
1734: --
1735: else
1736: --
1735: else
1736: --
1737: p_returned_date := round(l_enrt_cvg_strt_dt,'Month') + 15 ;
1738: if g_debug then
1739: hr_utility.set_location('Case 3'||l_enrt_cvg_strt_dt,15);
1740: end if;
1741: if g_debug then
1742: hr_utility.set_location('Case 3'||p_returned_date,15);
1743: end if;
1738: if g_debug then
1739: hr_utility.set_location('Case 3'||l_enrt_cvg_strt_dt,15);
1740: end if;
1741: if g_debug then
1742: hr_utility.set_location('Case 3'||p_returned_date,15);
1743: end if;
1744:
1745: --
1746: end if;
1761:
1762: if c_pay_period_for_date%notfound and
1763: l_enrt_cvg_strt_dt is not null then
1764: if g_debug then
1765: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',80);
1766: end if;
1767: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1768: fnd_message.set_token('DATE_CODE',p_date_cd);
1769: fnd_message.set_token('L_PROC',l_proc);
1782: and p_returned_date <> l_start_date)
1783: or p_date_cd = 'LFPPEMES' or p_date_cd = 'FDPPFES'
1784: or p_date_cd = 'LESFDPPAD' then
1785: if g_debug then
1786: hr_utility.set_location('LFPPEMES First of Pay Period After',20);
1787: end if;
1788: -- First of Pay Period After
1789: if p_date_cd = 'LESFDPPAD' then
1790: -- need to get pay period after designation, not enrt strt.
1793: open c_next_pay_period(p_returned_date);
1794: fetch c_next_pay_period into l_next_pay_period;
1795:
1796: if g_debug then
1797: hr_utility.set_location('l_next_pay_period.start_date'||l_next_pay_period.start_date,20);
1798: end if;
1799: if c_next_pay_period%notfound and
1800: l_enrt_cvg_strt_dt is not null then
1801: close c_next_pay_period;
1799: if c_next_pay_period%notfound and
1800: l_enrt_cvg_strt_dt is not null then
1801: close c_next_pay_period;
1802: if g_debug then
1803: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',40);
1804: end if;
1805: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1806: fnd_message.set_token('DATE_CODE',p_date_cd);
1807: fnd_message.set_token('L_PROC',l_proc);
1818: p_returned_date := l_enrt_eff_strt_date;
1819: else
1820: p_returned_date := l_next_pay_period.start_date;
1821: if g_debug then
1822: hr_utility.set_location('Else case :'||l_next_pay_period.start_date,20);
1823: end if;
1824: end if;
1825:
1826: elsif p_date_cd = 'LFMEMES' or p_date_cd = 'FDMCFES' or p_date_cd = 'FDMFES'
1863: --
1864: open c_next_pay_period(p_effective_date);
1865: fetch c_next_pay_period into l_next_pay_period;
1866: if g_debug then
1867: hr_utility.set_location('l_next_pay_period.start_date'||l_next_pay_period.start_date,20);
1868: end if;
1869: if c_next_pay_period%notfound then
1870: close c_next_pay_period;
1871: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
1886: -- ** FDPPOED - First of Pay Period On or After Effective Date
1887: elsif p_date_cd = 'FDPPOED' then
1888: -- Bug:4268494: Changed the logic. Get the current pay-period start/end dates
1889: -- If start_date <> effective_date, fetch next pay period start/end dates. Return start_date.
1890: hr_utility.set_location('Evaluate FDPPOED',10);
1891: l_event_date := NVL(l_fonm_rt_cvg_strt_dt,p_effective_date);
1892: --
1893: open c_pay_period;
1894: fetch c_pay_period into l_pay_period;
1904: close c_pay_period ;
1905: --
1906: if l_event_date = l_pay_period.start_date then
1907: p_returned_date := l_pay_period.start_date;
1908: hr_utility.set_location('l_pay_period.start_date '||l_pay_period.start_date,10);
1909: else
1910: --
1911: open c_next_pay_period(NVL(l_event_date,p_effective_date));
1912: fetch c_next_pay_period into l_next_pay_period;
1921: fnd_message.raise_error;
1922: end if;
1923: close c_next_pay_period;
1924: p_returned_date := l_next_pay_period.start_date;
1925: hr_utility.set_location('l_pay_period.start_date '|| l_next_pay_period.start_date,20);
1926: --
1927: end if;
1928: --
1929: --
1930: -- ** LDPPOEFD - 1 Prior or End of Pay Period On or After Effective Date
1931: elsif p_date_cd = 'LDPPOEFD' THEN
1932: -- Bug:4268494: Changed the logic. Get the current pay-period start/end dates
1933: -- If end_date <> effective_date, fetch next pay period start/end dates. Return end_date.
1934: hr_utility.set_location('Evaluate LDPPOEFD',10);
1935: l_event_date := NVL(l_fonm_rt_cvg_strt_dt,p_effective_date);
1936: --
1937: open c_pay_period;
1938: fetch c_pay_period into l_pay_period;
1948: close c_pay_period ;
1949: --
1950: if l_event_date = l_pay_period.end_date then
1951: p_returned_date := l_pay_period.end_date;
1952: hr_utility.set_location('l_pay_period.end_date '|| l_pay_period.end_date,20);
1953: else
1954: --
1955: open c_next_pay_period(NVL(l_event_date,p_effective_date));
1956: fetch c_next_pay_period into l_next_pay_period;
1965: fnd_message.raise_error;
1966: end if;
1967: close c_next_pay_period;
1968: p_returned_date := l_next_pay_period.end_date;
1969: hr_utility.set_location('l_pay_period.end_date '|| l_next_pay_period.end_date,20);
1970: --
1971: end if;
1972: --
1973: -- AFDCSMFDFFNSM First of next Semi month,if from day is first,
2113: --
2114: elsif p_date_cd = 'LWALDCSM' then
2115: -- --
2116: if g_debug then
2117: hr_utility.set_location('Entering LWALDCSM',10);
2118: end if;
2119: --
2120: if l_lf_evt_ocrd_dt is null then
2121: --
2154:
2155: elsif p_date_cd = 'WALDCSM' then
2156: -- --
2157: if g_debug then
2158: hr_utility.set_location('Entering WALDCSM',10);
2159: end if;
2160: --
2161: if l_lf_evt_ocrd_dt is null then
2162: --
2174: p_returned_date := trunc(l_event_date,'Month') + 14 ;
2175: end if;
2176: --
2177: if g_debug then
2178: hr_utility.set_location('p_returned_date '||p_returned_date , 19 );
2179: end if;
2180: --
2181: --
2182: -- EEELDNSMADI -- Earlier Participant Enrollment End or Next Semi Month End
2183: -- for dependent coverage end date
2184: elsif p_date_cd = 'EEELDNSMADI' then
2185: --
2186: if g_debug then
2187: hr_utility.set_location('Entering EEELDNSMADI',10);
2188: end if;
2189: --
2190: --
2191: -- p_effective_date -- Designated Date
2224: elsif p_date_cd = 'ALDPSM' then
2225: --
2226: --
2227: if g_debug then
2228: hr_utility.set_location('Entering ALDPSM',10);
2229: end if;
2230: --
2231: if l_lf_evt_ocrd_dt is null then
2232: --
2251: elsif p_date_cd = 'LALDPSM' then
2252: --
2253: --
2254: if g_debug then
2255: hr_utility.set_location('Entering ALDPSM',10);
2256: end if;
2257: --
2258: if l_lf_evt_ocrd_dt is null then
2259: --
2298: --
2299: elsif p_date_cd = 'ALDCSM' then
2300: --
2301: if g_debug then
2302: hr_utility.set_location('Entering ALDCSM',10);
2303: end if;
2304: --
2305: if l_lf_evt_ocrd_dt is null then
2306: --
2322: --
2323: end if;
2324: --
2325: if g_debug then
2326: hr_utility.set_location('p_returned_date '||p_returned_date , 199) ;
2327: end if;
2328: --
2329: -- LALDCSM End of Semi-month later event or Notified.
2330: --
2330: --
2331: elsif p_date_cd = 'LALDCSM' then
2332: --
2333: if g_debug then
2334: hr_utility.set_location('Entering LALDCSM',10);
2335: end if;
2336: --
2337: if l_lf_evt_ocrd_dt is null then
2338: --
2375: --
2376: elsif p_date_cd = 'AFDFSM' then
2377: --
2378: if g_debug then
2379: hr_utility.set_location('Entering AFDFSM',10);
2380: end if;
2381: --
2382: if l_lf_evt_ocrd_dt is null then
2383: --
2399: --
2400: elsif p_date_cd = 'AFDCSM' then
2401: --
2402: if g_debug then
2403: hr_utility.set_location('Entering AFDCSM',10);
2404: end if;
2405: --
2406: if l_lf_evt_ocrd_dt is null then
2407: --
2454: or p_date_cd = 'FDSMFC' or p_date_cd = 'FDSMCFC'
2455: then
2456:
2457: if g_debug then
2458: hr_utility.set_location('Entering '||p_date_cd,14);
2459: end if;
2460:
2461: l_lf_evt_ocrd_dt := nvl(l_fonm_rt_cvg_strt_dt, p_effective_date) ; -- completion date.
2462:
2469: --
2470: elsif p_date_cd = 'FDSMFC' then
2471: -- First of Semi Month after Completion
2472: if g_debug then
2473: hr_utility.set_location('Entering FDSMFC',10);
2474: end if;
2475: --
2476: /*
2477: if to_char(l_lf_evt_ocrd_dt, 'DD') > 15 then
2488: --
2489: elsif p_date_cd = 'FDSMCFC' then
2490: -- First of Semi Month on or after Completion
2491: if g_debug then
2492: hr_utility.set_location('Entering FDSMCFC',10);
2493: end if;
2494: --
2495: /*
2496: if to_number(to_char(l_lf_evt_ocrd_dt, 'DD')) in ( 1, 16 ) then
2570: if c_pay_period_for_date%notfound and
2571: p_effective_date is not null then
2572: close c_pay_period_for_date;
2573: if g_debug then
2574: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',45);
2575: end if;
2576: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
2577: fnd_message.set_token('DATE_CODE',p_date_cd);
2578: fnd_message.set_token('L_PROC',l_proc);
2598: if c_next_pay_period%notfound and
2599: p_effective_date is not null then
2600: close c_next_pay_period;
2601: if g_debug then
2602: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',55);
2603: end if;
2604: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
2605: fnd_message.set_token('DATE_CODE',p_date_cd);
2606: fnd_message.set_token('L_PROC',l_proc);
2621: --
2622: elsif p_date_cd = 'AFDELY' then
2623: --
2624: if g_debug then
2625: hr_utility.set_location('Entering AFDELY',10);
2626: end if;
2627: --
2628: if l_lf_evt_ocrd_dt is null then
2629: --
2653: close c_oipl_popl_lim_yr;
2654: --
2655: else
2656: if g_debug then
2657: hr_utility.set_location('BEN_92489_CANNOT_CALC_DATE',55);
2658: end if;
2659: fnd_message.set_name('BEN','BEN_92489_CANNOT_CALC_DATE');
2660: fnd_message.set_token('DATE_CODE',p_date_cd);
2661: fnd_message.set_token('L_PROC',l_proc);
2669: --
2670: elsif p_date_cd = 'ALDELMY' then
2671: --
2672: if g_debug then
2673: hr_utility.set_location('Entering ALDELMY',10);
2674: end if;
2675: --
2676: if l_lf_evt_ocrd_dt is null then
2677: --
2700: fetch c_oipl_popl_lim_yr into l_start_date, l_end_date;
2701: close c_oipl_popl_lim_yr;
2702: --
2703: else
2704: -- hr_utility.set_location('BEN_92489_CANNOT_CALC_DATE',55);
2705: fnd_message.set_name('BEN','BEN_92489_CANNOT_CALC_DATE');
2706: fnd_message.set_token('DATE_CODE',p_date_cd);
2707: fnd_message.set_token('L_PROC',l_proc);
2708: fnd_message.raise_error;
2714: -- FDODD - Within 5 Days After Due
2715: --
2716: elsif p_date_cd = 'FDODD' then
2717: --
2718: -- hr_utility.set_location('Entering FDODD',10);
2719: --
2720: p_returned_date:= p_effective_date + 5;
2721: --
2722: -- FRTYFV - Within 45 Days After Due
2722: -- FRTYFV - Within 45 Days After Due
2723: --
2724: elsif p_date_cd = 'FRTYFV' then
2725: --
2726: -- hr_utility.set_location('Entering FRTYFV',10);
2727: --
2728: p_returned_date:= p_effective_date + 45;
2729: ---
2730: -- TDODD - Within 10 Days after Due
2730: -- TDODD - Within 10 Days after Due
2731: --
2732: elsif p_date_cd = 'TDODD' then
2733: --
2734: -- hr_utility.set_location('Entering TDODD',10);
2735: --
2736: p_returned_date:= p_effective_date + 10;
2737: --
2738: -- THRTY - Within 30 Days after Due
2738: -- THRTY - Within 30 Days after Due
2739: --
2740: elsif p_date_cd = 'THRTY' then
2741: --
2742: -- hr_utility.set_location('Entering THRTY',10);
2743: --
2744: p_returned_date:= p_effective_date + 30;
2745: --
2746: -- THRTYONE - Within 31 Days after Due
2746: -- THRTYONE - Within 31 Days after Due
2747: --
2748: elsif p_date_cd = 'THRTYONE' then
2749: --
2750: -- hr_utility.set_location('Entering THRTYONE',10);
2751: --
2752: p_returned_date:= p_effective_date + 31;
2753: --
2754: -- DO - Date Occurred
2754: -- DO - Date Occurred
2755: --
2756: elsif p_date_cd = 'DO' then
2757: --
2758: -- hr_utility.set_location('Entering DO',10);
2759:
2760: p_returned_date:= p_effective_date;
2761: --
2762: -- DR - Date Recorded or Notified
2762: -- DR - Date Recorded or Notified
2763: --
2764: elsif p_date_cd in ('DR','ODNPE') then
2765: --
2766: -- hr_utility.set_location('Entering DR',10);
2767:
2768: p_returned_date:= sysdate;
2769: --
2770: -- LOR - Later Occurred Date or Recorded Date.
2770: -- LOR - Later Occurred Date or Recorded Date.
2771: --
2772: elsif p_date_cd = 'LOR' then
2773: --
2774: -- hr_utility.set_location('Entering LOR',10);
2775: --
2776: if sysdate > nvl(l_lf_evt_ocrd_dt,hr_api.g_sot) then
2777: p_returned_date := sysdate;
2778: else
2785: --
2786: --
2787: elsif p_date_cd = 'DAO' or p_date_cd = 'ODAED' then
2788: --
2789: -- hr_utility.set_location('Entering DAO',10);
2790: -- hr_utility.set_location('Entering ODAED',10);
2791: --
2792: --p_returned_date := p_effective_date + 1;
2793: if l_lf_evt_ocrd_dt is null then
2786: --
2787: elsif p_date_cd = 'DAO' or p_date_cd = 'ODAED' then
2788: --
2789: -- hr_utility.set_location('Entering DAO',10);
2790: -- hr_utility.set_location('Entering ODAED',10);
2791: --
2792: --p_returned_date := p_effective_date + 1;
2793: if l_lf_evt_ocrd_dt is null then
2794: --
2807: -- or Recorded Date.
2808: --
2809: elsif p_date_cd = 'LDAOR' then
2810: --
2811: -- hr_utility.set_location('Entering LDAOR',10);
2812: --
2813: if sysdate > (nvl(l_lf_evt_ocrd_dt,hr_api.g_sot) + 1) then
2814: p_returned_date := sysdate;
2815: else
2819: -- ALDECLY - End of Calendar Year
2820: --
2821: elsif p_date_cd = 'ALDECLY' then
2822: --
2823: -- hr_utility.set_location('Entering ALDECLY',10);
2824: --
2825: if l_lf_evt_ocrd_dt is null then
2826: --
2827: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2839: -- FFDFED - 45 Days After Event
2840: --
2841: elsif p_date_cd = 'FFDFED' then
2842: --
2843: -- hr_utility.set_location('Entering FFDFED',10);
2844: --
2845: if l_lf_evt_ocrd_dt is null then
2846: --
2847: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2857: -- NDFED - 90 Days After Event
2858: --
2859: elsif p_date_cd = 'NDFED' then
2860: --
2861: -- hr_utility.set_location('Entering NDFED',10);
2862: --
2863: if l_lf_evt_ocrd_dt is null then
2864: --
2865: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2875: -- LFFDFED - 45 Days After Later Event or Notified
2876: --
2877: elsif p_date_cd = 'LFFDFED' then
2878: --
2879: -- hr_utility.set_location('Entering LFFDFED',10);
2880: --
2881: if l_lf_evt_ocrd_dt is null then
2882: --
2883: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2905: -- LNDFED - 90 Days After Later Event or Notified
2906: --
2907: elsif p_date_cd = 'LNDFED' then
2908: --
2909: -- hr_utility.set_location('Entering LNDFED',10);
2910: --
2911: if l_lf_evt_ocrd_dt is null then
2912: --
2913: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2935: -- LTDFED - 30 Days After Later Event or Notified
2936: --
2937: elsif p_date_cd = 'LTDFED' then
2938: --
2939: -- hr_utility.set_location('Entering LTDFED',10);
2940: --
2941: if l_lf_evt_ocrd_dt is null then
2942: --
2943: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2965: -- ALDPM - End of Previous Month
2966: --
2967: elsif p_date_cd = 'ALDPM' then
2968:
2969: -- hr_utility.set_location('Entering ALDPM',10);
2970:
2971: if l_lf_evt_ocrd_dt is null then
2972:
2973: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
2985: -- LALDPM - End of Previous Month Later Event or Notified
2986:
2987: elsif p_date_cd = 'LALDPM' then
2988:
2989: -- hr_utility.set_location('Entering LALDPM',10);
2990:
2991: if l_lf_evt_ocrd_dt is null then
2992:
2993: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3014: -- ALDPPP - End of Previous Pay period
3015:
3016: elsif p_date_cd = 'ALDPPP' then
3017: --
3018: -- hr_utility.set_location('Entering ALDPPP',10);
3019:
3020: if l_lf_evt_ocrd_dt is null then
3021:
3022: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3032:
3033: if c_pay_period%notfound then
3034:
3035: close c_pay_period;
3036: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',65);
3037: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3038: fnd_message.set_token('DATE_CODE',p_date_cd);
3039: fnd_message.set_token('L_PROC',l_proc);
3040: fnd_message.set_token('PERSON_ID',l_person_id);
3051: -- LALDPPP - End of Previous Pay period Later Event or Notified
3052:
3053: elsif p_date_cd = 'LALDPPP' then
3054: --
3055: -- hr_utility.set_location('Entering LALDPPP',10);
3056:
3057: if l_lf_evt_ocrd_dt is null then
3058:
3059: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3079:
3080: if c_pay_period%notfound then
3081:
3082: close c_pay_period;
3083: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',70);
3084: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3085: fnd_message.set_token('DATE_CODE',p_date_cd);
3086: fnd_message.set_token('L_PROC',l_proc);
3087: fnd_message.set_token('PERSON_ID',l_person_id);
3100: -- Later Event or Notified
3101:
3102: elsif p_date_cd = 'LALDPPPY' then
3103:
3104: -- hr_utility.set_location('Entering LALDPPPY',10);
3105:
3106: if l_lf_evt_ocrd_dt is null then
3107:
3108: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3169: or p_date_cd = 'AFDCPPQ' or p_date_cd = 'LAFDFPPQ' or p_date_cd = 'ALDPPPQ'
3170: or p_date_cd = 'LALDPPPQ' or p_date_cd = 'LALDCPPQ' or p_date_cd = 'LFDPPQCF'
3171: then
3172:
3173: -- hr_utility.set_location('Entering '||p_date_cd,12);
3174:
3175: if l_lf_evt_ocrd_dt is null then
3176: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3177: end if;
3276:
3277: -- SDBED - Sixty days Before Event Date
3278: elsif p_date_cd = 'SDBED' then
3279:
3280: -- hr_utility.set_location('Entering SDBED',10);
3281:
3282: if l_lf_evt_ocrd_dt is null then
3283:
3284: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3324: or p_date_cd = 'LAFDFPPSY' or p_date_cd = 'AFDFPPSY' or p_date_cd = 'ALDCPPSY'
3325: or p_date_cd = 'LALDCPPSY' or p_date_cd = 'ALDPPPSY' or p_date_cd = 'LALDPPPSY'
3326: then
3327:
3328: -- hr_utility.set_location('Entering '||p_date_cd,22);
3329:
3330: if l_lf_evt_ocrd_dt is null then
3331: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3332: end if;
3402:
3403: -- ODCED - Dependent Coverage End
3404: elsif p_date_cd = 'ODCED' then
3405:
3406: -- hr_utility.set_location('Entering ODCED',10);
3407:
3408: if l_lf_evt_ocrd_dt is null then
3409:
3410: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3423: -- ODCSD - Dependent Coverage End
3424:
3425: elsif p_date_cd = 'ODCSD' then
3426:
3427: -- hr_utility.set_location('Entering ODCSD',10);
3428:
3429: if l_lf_evt_ocrd_dt is null then
3430:
3431: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3443: -- AFDECY - First of Calendar year
3444:
3445: elsif p_date_cd = 'AFDECY' then
3446:
3447: -- hr_utility.set_location('Entering AFDECY',10);
3448:
3449: if l_lf_evt_ocrd_dt is null then
3450:
3451: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3457: -- 30DANED - 30 Days After Notice to Enroll
3458:
3459: elsif p_date_cd = '30DANED' then
3460:
3461: -- hr_utility.set_location('Entering 30DANED',10);
3462:
3463: l_recorded_date := get_recorded_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3464:
3465: p_returned_date := l_recorded_date + 30;
3470: --Case DOB 08/01/1935 , Event 08/03/2000 , return date should be 09/01/2000
3471:
3472: elsif p_date_cd = 'AFDCMFDFFNM' then
3473:
3474: -- hr_utility.set_location('Entering AFDCMFDFFNM',10);
3475:
3476: if l_lf_evt_ocrd_dt is null then
3477:
3478: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3519: -- DOD - Date of Determination
3520: --
3521: elsif p_date_cd = 'DOD' then
3522: --
3523: -- hr_utility.set_location('Entering DOD',10);
3524: --
3525: if l_lf_evt_ocrd_dt is null then
3526: --
3527: p_returned_date:= p_effective_date;
3536: -- DODM1 - Date of Determination Minus 1 day
3537: --
3538: elsif p_date_cd = 'DODM1' then
3539: --
3540: -- hr_utility.set_location('Entering DODM1',10);
3541: --
3542: if l_lf_evt_ocrd_dt is null then
3543: --
3544: p_returned_date:= p_effective_date - 1;
3554: -- Concurrent with or Following
3555: --
3556: elsif p_date_cd = 'FDLMEPPYCF' then
3557: --
3558: -- hr_utility.set_location('Entering FDLMEPPYCF',10);
3559: --
3560: if l_lf_evt_ocrd_dt is null then
3561: --
3562: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3599: -- ALDPPPY - Last Day of previous Program or Plan Year
3600: --
3601: elsif p_date_cd = 'ALDPPPY' then
3602: --
3603: -- hr_utility.set_location('Entering ALDPPPY',10);
3604: --
3605: if l_lf_evt_ocrd_dt is null then
3606: --
3607: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3625: -- ALDLPPEPPY - Last Day Last Pay period of Event Program or plan Year
3626: --
3627: elsif p_date_cd = 'ALDLPPEPPY' then
3628: --
3629: -- hr_utility.set_location('Entering ALDLPPEPPY',10);
3630: --
3631: if l_lf_evt_ocrd_dt is null then
3632:
3633: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3651: l_start_date,
3652: l_end_date;
3653: --
3654: if c_pay_period_for_date%notfound then
3655: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',95);
3656: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3657: fnd_message.set_token('DATE_CODE',p_date_cd);
3658: fnd_message.set_token('L_PROC',l_proc);
3659: fnd_message.set_token('PERSON_ID',l_person_id);
3670: -- FDLPPEPPY - First Day Last Pay period of Event Program or plan Year
3671: --
3672: elsif p_date_cd = 'FDLPPEPPY' then
3673: --
3674: -- hr_utility.set_location('Entering FDLPPEPPY',10);
3675: --
3676: if l_lf_evt_ocrd_dt is null then
3677: --
3678: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3695: l_start_date,
3696: l_end_date;
3697: --
3698: if c_pay_period_for_date%notfound then
3699: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',100);
3700: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3701: fnd_message.set_token('DATE_CODE',p_date_cd);
3702: fnd_message.set_token('L_PROC',l_proc);
3703: fnd_message.set_token('PERSON_ID',l_person_id);
3714: -- FDLPPEPPYCF - First Day Last Pay period in Year on or after event
3715: --
3716: elsif p_date_cd = 'FDLPPEPPYCF' then
3717: --
3718: -- hr_utility.set_location('Entering FDLPPEPPYCF',10);
3719: --
3720: if l_lf_evt_ocrd_dt is null then
3721: --
3722: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3739: l_start_date,
3740: l_end_date;
3741: --
3742: if c_pay_period_for_date%notfound then
3743: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',105);
3744: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
3745: fnd_message.set_token('DATE_CODE',p_date_cd);
3746: fnd_message.set_token('L_PROC',l_proc);
3747: fnd_message.set_token('PERSON_ID',l_person_id);
3757: -- ODBED - One day before Event Date
3758: --
3759: elsif p_date_cd = 'ODBED' then
3760: --
3761: -- hr_utility.set_location('Entering ODBED WODBED OR WEM',10);
3762: --
3763: if l_lf_evt_ocrd_dt is null then
3764: --
3765: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3776: --
3777: elsif p_date_cd = 'WEM' then
3778: --
3779: if g_debug then
3780: hr_utility.set_location('Entering WEM',10);
3781: end if;
3782: --
3783: if l_lf_evt_ocrd_dt is null then
3784: --
3796: --
3797: elsif p_date_cd = 'WODBED' then
3798: --
3799: if g_debug then
3800: hr_utility.set_location('Entering WODBED',10);
3801: end if;
3802: --
3803: --
3804: if l_lf_evt_ocrd_dt is null then
3816: -- AFDCM - First day of Current Month
3817: --
3818: elsif p_date_cd = 'AFDCM' then
3819: --
3820: -- hr_utility.set_location('Entering AFDCM',10);
3821: --
3822: if l_lf_evt_ocrd_dt is null then
3823: --
3824: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3838: --
3839: elsif p_date_cd = 'ALDCM' then
3840: --
3841: if g_debug then
3842: hr_utility.set_location('Entering ALDCM',10);
3843: end if;
3844: --
3845: if l_lf_evt_ocrd_dt is null then
3846: --
3858: --
3859: elsif p_date_cd = 'WALDCM' then
3860: --
3861: if g_debug then
3862: hr_utility.set_location('Entering WALDCM',10);
3863: end if;
3864: --
3865: if l_lf_evt_ocrd_dt is null then
3866: --
3878: -- ALDFM - Last day of Following Month
3879: --
3880: elsif p_date_cd = 'ALDFM' then
3881: --
3882: -- hr_utility.set_location('Entering ALDFM',10);
3883: --
3884: if l_lf_evt_ocrd_dt is null then
3885: --
3886: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3893: -- AEOT - As of End of Time
3894: --
3895: elsif p_date_cd = 'AEOT' then
3896: --
3897: -- hr_utility.set_location('Entering AEOT',10);
3898: --
3899: p_returned_date := to_date('31-12-4712','DD-MM-YYYY');
3900: --
3901: -- A30DFPSD - As of 30 days from the participation start date
3901: -- A30DFPSD - As of 30 days from the participation start date
3902: --
3903: elsif p_date_cd = 'A30DFPSD' then
3904: --
3905: -- hr_utility.set_location('Entering A30DFPSD',10);
3906: --
3907: p_returned_date := p_start_date+30;
3908: --
3909: -- A45DFPSD - As of 45 days from the participation start date
3909: -- A45DFPSD - As of 45 days from the participation start date
3910: --
3911: elsif p_date_cd = 'A45DFPSD' then
3912: --
3913: -- hr_utility.set_location('Entering A45DFPSD',10);
3914: --
3915: p_returned_date := p_start_date+45;
3916: --
3917: -- A60DFPSD - As of 60 days from the participation start date
3917: -- A60DFPSD - As of 60 days from the participation start date
3918: --
3919: elsif p_date_cd = 'A60DFPSD' then
3920: --
3921: -- hr_utility.set_location('Entering A60DFPSD',10);
3922: --
3923: p_returned_date := p_start_date+60;
3924: --
3925: -- A12MFPSD - As of 12 months from the participation start date
3925: -- A12MFPSD - As of 12 months from the participation start date
3926: --
3927: elsif p_date_cd = 'A12MFPSD' then
3928: --
3929: -- hr_utility.set_location('Entering A12MFPSD',10);
3930: --
3931: p_returned_date := add_months(p_start_date,12);
3932: --
3933: -- A18MFPSD - As of 18 months from the participation start date
3933: -- A18MFPSD - As of 18 months from the participation start date
3934: --
3935: elsif p_date_cd = 'A18MFPSD' then
3936: --
3937: -- hr_utility.set_location('Entering A18MFPSD',10);
3938: --
3939: p_returned_date := add_months(p_start_date,18);
3940: --
3941: -- A1MFPSD - As of 1 months from the participation start date
3941: -- A1MFPSD - As of 1 months from the participation start date
3942: --
3943: elsif p_date_cd = 'A1MFPSD' then
3944: --
3945: -- hr_utility.set_location('Entering A1MFPSD',10);
3946: --
3947: p_returned_date := add_months(p_start_date,1);
3948: --
3949: -- A29MFPSD - As of 29 months from the participation start date
3949: -- A29MFPSD - As of 29 months from the participation start date
3950: --
3951: elsif p_date_cd = 'A29MFPSD' then
3952: --
3953: -- hr_utility.set_location('Entering A29MFPSD',10);
3954: --
3955: p_returned_date := add_months(p_start_date,29);
3956: --
3957: -- A36MFPSD - As of 36 months from the participation start date
3957: -- A36MFPSD - As of 36 months from the participation start date
3958: --
3959: elsif p_date_cd = 'A36MFPSD' then
3960: --
3961: -- hr_utility.set_location('Entering A36MFPSD',10);
3962: --
3963: p_returned_date := add_months(p_start_date,36);
3964: --
3965: -- As of First Day of Following Month
3965: -- As of First Day of Following Month
3966: --
3967: elsif p_date_cd = 'AFDFM' then
3968: --
3969: -- hr_utility.set_location('Entering AFDFM',10);
3970: --
3971: if l_lf_evt_ocrd_dt is null then
3972: --
3973: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
3983: -- As of First Day of Following Month after 15 days
3984: --
3985: elsif p_date_cd = 'AFDFM15' then
3986: --
3987: -- hr_utility.set_location('Entering AFDFM15',10);
3988: --
3989: if l_lf_evt_ocrd_dt is null then
3990: --
3991: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4001: -- ALDCPPY - Last day of current program or plan year
4002: --
4003: elsif p_date_cd = 'ALDCPPY' then
4004: --
4005: -- hr_utility.set_location('Entering ALDCPPY',10);
4006: --
4007: if l_lf_evt_ocrd_dt is null then
4008: --
4009: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4028: --
4029: elsif p_date_cd = 'WALDCPPY' then
4030: --
4031: if g_debug then
4032: hr_utility.set_location('Entering ALDCPPY',10);
4033: end if;
4034: --
4035: --
4036: if l_lf_evt_ocrd_dt is null then
4056: -- FDLMPPY - First Day Last Month of current Program or Pl Year
4057: --
4058: elsif p_date_cd = 'FDLMPPY' then
4059: --
4060: -- hr_utility.set_location('Entering FDLMPPY',10);
4061: --
4062: if l_lf_evt_ocrd_dt is null then
4063: --
4064: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4082: -- AFDCPPY - First day of current program or plan year
4083: --
4084: elsif p_date_cd = 'AFDCPPY' then
4085: --
4086: -- hr_utility.set_location('Entering AFDCPPY',10);
4087: --
4088: if l_lf_evt_ocrd_dt is null then
4089: --
4090: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4108: -- AFDFPPY - First day of following program or plan year
4109: --
4110: elsif p_date_cd = 'AFDFPPY' then
4111: --
4112: -- hr_utility.set_location('Entering AFDFPPY',10);
4113: --
4114: if l_lf_evt_ocrd_dt is null then
4115: --
4116: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4134: -- AFDCPP - First day of current pay period
4135: --
4136: elsif p_date_cd = 'AFDCPP' then
4137: --
4138: -- hr_utility.set_location('Entering AFDCPP',10);
4139: --
4140: if l_lf_evt_ocrd_dt is null then
4141: --
4142: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4148: --
4149: if c_pay_period%notfound then
4150: --
4151: close c_pay_period;
4152: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',110);
4153: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4154: fnd_message.set_token('DATE_CODE',p_date_cd);
4155: fnd_message.set_token('L_PROC',l_proc);
4156: fnd_message.set_token('PERSON_ID',l_person_id);
4168: --
4169: elsif p_date_cd = 'ALDCPP' then
4170: --
4171: if g_debug then
4172: hr_utility.set_location('Entering ALDCPP',10);
4173: end if;
4174: --
4175: if l_lf_evt_ocrd_dt is null then
4176: --
4182: fetch c_pay_period into l_pay_period;
4183: --
4184: if c_pay_period%notfound then
4185: close c_pay_period;
4186: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',115);
4187: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4188: fnd_message.set_token('DATE_CODE',p_date_cd);
4189: fnd_message.set_token('L_PROC',l_proc);
4190: fnd_message.set_token('PERSON_ID',l_person_id);
4199: --
4200: elsif p_date_cd = 'WALDCPP' then
4201: --
4202: if g_debug then
4203: hr_utility.set_location('Entering WALDCPP',10);
4204: end if;
4205: --
4206: --
4207: if l_lf_evt_ocrd_dt is null then
4215: --
4216: if c_pay_period%notfound then
4217: close c_pay_period;
4218: if g_debug then
4219: hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',115);
4220: end if;
4221: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4222: fnd_message.set_token('DATE_CODE',p_date_cd);
4223: fnd_message.set_token('L_PROC',l_proc);
4234: -- AFDFPP - First day of following pay period
4235: --
4236: elsif p_date_cd = 'AFDFPP' then
4237: --
4238: -- hr_utility.set_location('Entering AFDFPP',10);
4239: --
4240: if l_lf_evt_ocrd_dt is null then
4241: --
4242: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4247: fetch c_next_pay_period into l_next_pay_period;
4248: --
4249: if c_next_pay_period%notfound then
4250: close c_next_pay_period;
4251: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',120);
4252: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4253: fnd_message.set_token('DATE_CODE',p_date_cd);
4254: fnd_message.set_token('PERSON_ID',l_person_id);
4255: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4264: --
4265: elsif p_date_cd = 'APOCT1' then
4266: -- tilak
4267: --
4268: -- hr_utility.set_location('Entering APOCT1',10);
4269: --
4270: if l_lf_evt_ocrd_dt is null then
4271: --
4272: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4289: -- Set to first of month of October
4290: --
4291: p_returned_date := last_day(l_date)+1;
4292: if g_debug then
4293: hr_utility.set_location(' APOCT1 date : ' || p_returned_date , 450) ;
4294: end if;
4295: -- tilak
4296:
4297: -- OMFED - One Month Afl_event_date p_date_cd = 'OMFED' then
4297: -- OMFED - One Month Afl_event_date p_date_cd = 'OMFED' then
4298: --
4299: elsif p_date_cd = 'OMFED' then
4300: --
4301: -- hr_utility.set_location('Entering OMFED',10);
4302: --
4303: if l_lf_evt_ocrd_dt is null then
4304: --
4305: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4315: -- TMFED - Two Months After Event Date
4316: --
4317: elsif p_date_cd = 'TMFED' then
4318: --
4319: -- hr_utility.set_location('Entering TMFED',10);
4320: --
4321: if l_lf_evt_ocrd_dt is null then
4322: --
4323: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4333: -- 30DFLED - Thirty days from Life Event Date
4334: --
4335: elsif p_date_cd = '30DFLED' then
4336: --
4337: -- hr_utility.set_location('Entering 30DFLED',10);
4338: --
4339: if l_lf_evt_ocrd_dt is null then
4340: --
4341: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4351: -- TDBED - Thirty days before event date
4352: --
4353: elsif p_date_cd = 'TDBED' then
4354: --
4355: -- hr_utility.set_location('Entering TDBED',10);
4356: --
4357: if l_lf_evt_ocrd_dt is null then
4358: --
4359: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4369: -- LTDBED - Later: Thirty days before event date or notified
4370: --
4371: elsif p_date_cd = 'LTDBED' then
4372: --
4373: -- hr_utility.set_location('Entering LTDBED',10);
4374: --
4375: if l_lf_evt_ocrd_dt is null then
4376: --
4377: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4407: -- SDFED - Sixty days After Event Date
4408: --
4409: elsif p_date_cd = 'SDFED' then
4410: --
4411: -- hr_utility.set_location('Entering SDFED',10);
4412: --
4413: if l_lf_evt_ocrd_dt is null then
4414: --
4415: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4427: -- LSDBED - Later: Sixty days before Event Date
4428: --
4429: elsif p_date_cd = 'LSDBED' then
4430: --
4431: -- hr_utility.set_location('Entering LSDBED',10);
4432: --
4433: if l_lf_evt_ocrd_dt is null then
4434: --
4435: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4465: -- TODFED - Thirty-one days After Event Date
4466: --
4467: elsif p_date_cd = 'TODFED' then
4468: --
4469: -- hr_utility.set_location('Entering TODFED',10);
4470: --
4471: if l_lf_evt_ocrd_dt is null then
4472: --
4473: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4483: -- LEPPPED - Later of Enrollment Period End Date and Processing End Date.
4484: --
4485: elsif p_date_cd = 'LEPPPED' then
4486: --
4487: -- hr_utility.set_location('Entering LEPPPED',10);
4488: --
4489: open c_pil_popl;
4490: fetch c_pil_popl into l_enrt_end_dt, l_procg_end_dt;
4491: --
4490: fetch c_pil_popl into l_enrt_end_dt, l_procg_end_dt;
4491: --
4492: if c_pil_popl%notfound then
4493: close c_pil_popl;
4494: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',81);
4495: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4496: fnd_message.set_token('DATE_CODE',p_date_cd);
4497: fnd_message.set_token('L_PROC',l_proc);
4498: fnd_message.raise_error;
4509: elsif p_date_cd = '5DBEEPD' then
4510: --
4511: -- 5DBEEPD - 5 Days before the end of the enrollment period end date
4512: --
4513: -- hr_utility.set_location('Entering 5DBEEPD',10);
4514: --
4515: open c_cm_enrt_perd_end_dt;
4516: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4517: if c_cm_enrt_perd_end_dt%notfound then
4515: open c_cm_enrt_perd_end_dt;
4516: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4517: if c_cm_enrt_perd_end_dt%notfound then
4518: close c_cm_enrt_perd_end_dt;
4519: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',86);
4520: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4521: fnd_message.set_token('DATE_CODE',p_date_cd);
4522: fnd_message.set_token('L_PROC',l_proc);
4523: fnd_message.raise_error;
4531: elsif p_date_cd = '10DBEEPD' then
4532: --
4533: -- 10DBEEPD - 10 Days before the end of the enrollment period end date
4534: --
4535: -- hr_utility.set_location('Entering 10DBEEPD',10);
4536: --
4537: open c_cm_enrt_perd_end_dt;
4538: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4539: if c_cm_enrt_perd_end_dt%notfound then
4537: open c_cm_enrt_perd_end_dt;
4538: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4539: if c_cm_enrt_perd_end_dt%notfound then
4540: close c_cm_enrt_perd_end_dt;
4541: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',87);
4542: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4543: fnd_message.set_token('DATE_CODE',p_date_cd);
4544: fnd_message.set_token('L_PROC',l_proc);
4545: fnd_message.raise_error;
4552: elsif p_date_cd = '20DBEEPD' then
4553: --
4554: -- 20DBEEPD - 20 Days before the end of the enrollment period end date
4555: --
4556: -- hr_utility.set_location('Entering 20DBEEPD',10);
4557: --
4558: open c_cm_enrt_perd_end_dt;
4559: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4560: if c_cm_enrt_perd_end_dt%notfound then
4558: open c_cm_enrt_perd_end_dt;
4559: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4560: if c_cm_enrt_perd_end_dt%notfound then
4561: close c_cm_enrt_perd_end_dt;
4562: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',89);
4563: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4564: fnd_message.set_token('DATE_CODE',p_date_cd);
4565: fnd_message.set_token('L_PROC',l_proc);
4566: fnd_message.raise_error;
4573: elsif p_date_cd = '25DBEEPD' then
4574: --
4575: -- 25DBEEPD - 25 Days before the end of the enrollment period end date
4576: --
4577: -- hr_utility.set_location('Entering 25DBEEPD',10);
4578: --
4579: open c_cm_enrt_perd_end_dt;
4580: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4581: if c_cm_enrt_perd_end_dt%notfound then
4579: open c_cm_enrt_perd_end_dt;
4580: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4581: if c_cm_enrt_perd_end_dt%notfound then
4582: close c_cm_enrt_perd_end_dt;
4583: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',90);
4584: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4585: fnd_message.set_token('DATE_CODE',p_date_cd);
4586: fnd_message.set_token('L_PROC',l_proc);
4587: fnd_message.raise_error;
4594: elsif p_date_cd = '30DBEEPD' then
4595: --
4596: -- 30DBEEPD - 30 Days before the end of the enrollment period end date
4597: --
4598: -- hr_utility.set_location('Entering 30DBEEPD',10);
4599: --
4600: open c_cm_enrt_perd_end_dt;
4601: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4602: if c_cm_enrt_perd_end_dt%notfound then
4600: open c_cm_enrt_perd_end_dt;
4601: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4602: if c_cm_enrt_perd_end_dt%notfound then
4603: close c_cm_enrt_perd_end_dt;
4604: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',91);
4605: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4606: fnd_message.set_token('DATE_CODE',p_date_cd);
4607: fnd_message.set_token('L_PROC',l_proc);
4608: fnd_message.raise_error;
4616: elsif p_date_cd = '10DBDAD' then
4617: --
4618: -- 10DBDAD - 10 Days before the default applied date
4619: --
4620: -- hr_utility.set_location('Entering 10DBDAD',10);
4621: --
4622: open c_cm_dflt_asnd_dt;
4623: fetch c_cm_dflt_asnd_dt into l_cm_date;
4624: if c_cm_dflt_asnd_dt%notfound then
4622: open c_cm_dflt_asnd_dt;
4623: fetch c_cm_dflt_asnd_dt into l_cm_date;
4624: if c_cm_dflt_asnd_dt%notfound then
4625: close c_cm_dflt_asnd_dt;
4626: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',92);
4627: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4628: fnd_message.set_token('DATE_CODE',p_date_cd);
4629: fnd_message.set_token('L_PROC',l_proc);
4630: fnd_message.raise_error;
4637: elsif p_date_cd = '14DBEPD' then
4638: --
4639: -- 14DBEPD - 14 Days before the Eligible to Participate Date
4640: --
4641: -- hr_utility.set_location('Entering 14DBEPD',10);
4642: --
4643: open c_cm_elig_prtn_strt_dt;
4644: fetch c_cm_elig_prtn_strt_dt into l_cm_date;
4645: if c_cm_elig_prtn_strt_dt%notfound then
4643: open c_cm_elig_prtn_strt_dt;
4644: fetch c_cm_elig_prtn_strt_dt into l_cm_date;
4645: if c_cm_elig_prtn_strt_dt%notfound then
4646: close c_cm_elig_prtn_strt_dt;
4647: -- hr_utility.set_location('BEN_91386_FIRST_INELIG',81);
4648: fnd_message.set_name('BEN', 'BEN_91386_FIRST_INELIG');
4649: fnd_message.set_token('DATE_CODE',p_date_cd);
4650: fnd_message.set_token('PROC',l_proc);
4651: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4664: open c_cm_elig_prtn_end_dt;
4665: fetch c_cm_elig_prtn_end_dt into l_cm_date;
4666: if c_cm_elig_prtn_end_dt%notfound then
4667: close c_cm_elig_prtn_end_dt;
4668: -- hr_utility.set_location('BEN_91386_FIRST_INELIG',93);
4669: fnd_message.set_name('BEN', 'BEN_91386_FIRST_INELIG');
4670: fnd_message.set_token('DATE_CODE',p_date_cd);
4671: fnd_message.set_token('PROC',l_proc);
4672: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
4681: elsif p_date_cd = '15DBEEPD' then
4682: --
4683: -- 15DBEEPD - 15 Days before the end of the enrollment period end date
4684: --
4685: -- hr_utility.set_location('Entering 15DBEEPD',10);
4686: --
4687: open c_cm_enrt_perd_end_dt;
4688: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4689: if c_cm_enrt_perd_end_dt%notfound then
4687: open c_cm_enrt_perd_end_dt;
4688: fetch c_cm_enrt_perd_end_dt into l_cm_date;
4689: if c_cm_enrt_perd_end_dt%notfound then
4690: close c_cm_enrt_perd_end_dt;
4691: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',88);
4692: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4693: fnd_message.set_token('DATE_CODE',p_date_cd);
4694: fnd_message.set_token('L_PROC',l_proc);
4695: fnd_message.raise_error;
4701: elsif p_date_cd = 'OAED' then
4702: --
4703: -- OAED - On the Automatic Enrollment Date.
4704: --
4705: -- hr_utility.set_location('Entering OAED',10);
4706: --
4707: open c_cm_auto_asnd_dt;
4708: fetch c_cm_auto_asnd_dt into l_cm_date;
4709: if c_cm_auto_asnd_dt%notfound then
4707: open c_cm_auto_asnd_dt;
4708: fetch c_cm_auto_asnd_dt into l_cm_date;
4709: if c_cm_auto_asnd_dt%notfound then
4710: close c_cm_auto_asnd_dt;
4711: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',98);
4712: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
4713: fnd_message.set_token('DATE_CODE',p_date_cd);
4714: fnd_message.set_token('L_PROC',l_proc);
4715: fnd_message.raise_error;
4722: elsif p_date_cd = 'OCSD' or p_date_cd = 'PECSD' then
4723: --
4724: -- OCSD - On the Coverage Start Date.
4725: --
4726: -- hr_utility.set_location('Entering OCSD',10);
4727: --
4728: open c_prtt_enrt_rslt_dts;
4729: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
4730: l_enrt_cvg_strt_dt,
4733: --
4734: l_enrt_cvg_strt_dt := p_start_date ;
4735: --
4736: if g_debug then
4737: hr_utility.set_location('OCSD Not found ', 19);
4738: end if;
4739: --
4740: end if;
4741: --
4747: -- LFDMCF - First of Month on or After Later of Event or Notified
4748: --
4749: elsif p_date_cd = 'LFDMCF' then
4750: --
4751: -- hr_utility.set_location('Entering LFDMCF',10);
4752: --
4753: if l_lf_evt_ocrd_dt is null then
4754: --
4755: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4788: -- LFDPPCF - First of Pay Period on or After Later of Event or Notified
4789: --
4790: elsif p_date_cd = 'LFDPPCF' then
4791: --
4792: -- hr_utility.set_location('Entering LFDPPCF',10);
4793: --
4794: if l_lf_evt_ocrd_dt is null then
4795: --
4796: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4809: --
4810: if c_pay_period_for_date%notfound then
4811: --
4812: close c_pay_period_for_date;
4813: -- hr_utility.set_location('BEN_92380_CANNOT_CALC_LFDPPCF',96);
4814: -- hr_utility.set_location('l_lf_evt_ocrd_dt'||to_char(l_lf_evt_ocrd_dt),96);
4815: fnd_message.set_name('BEN','BEN_92380_CANNOT_CALC_LFDPPCF');
4816: fnd_message.set_token('L_PROC',l_proc);
4817: fnd_message.set_token('PERSON_ID',l_person_id);
4810: if c_pay_period_for_date%notfound then
4811: --
4812: close c_pay_period_for_date;
4813: -- hr_utility.set_location('BEN_92380_CANNOT_CALC_LFDPPCF',96);
4814: -- hr_utility.set_location('l_lf_evt_ocrd_dt'||to_char(l_lf_evt_ocrd_dt),96);
4815: fnd_message.set_name('BEN','BEN_92380_CANNOT_CALC_LFDPPCF');
4816: fnd_message.set_token('L_PROC',l_proc);
4817: fnd_message.set_token('PERSON_ID',l_person_id);
4818: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4834: fetch c_next_pay_period into l_next_pay_period;
4835: --
4836: if c_next_pay_period%notfound then
4837: close c_next_pay_period;
4838: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',125);
4839: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4840: fnd_message.set_token('DATE_CODE',p_date_cd);
4841: fnd_message.set_token('PERSON_ID',l_person_id);
4842: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4859: --
4860: if c_pay_period_for_date%notfound then
4861: --
4862: close c_pay_period_for_date;
4863: -- hr_utility.set_location('BEN_92380_CANNOT_CALC_LFDPPCF',97);
4864: -- hr_utility.set_location('l_recorded_date'||to_char(l_recorded_date),97);
4865: fnd_message.set_name('BEN','BEN_92380_CANNOT_CALC_LFDPPCF');
4866: fnd_message.set_token('L_PROC',l_proc);
4867: fnd_message.set_token('PERSON_ID',l_person_id);
4860: if c_pay_period_for_date%notfound then
4861: --
4862: close c_pay_period_for_date;
4863: -- hr_utility.set_location('BEN_92380_CANNOT_CALC_LFDPPCF',97);
4864: -- hr_utility.set_location('l_recorded_date'||to_char(l_recorded_date),97);
4865: fnd_message.set_name('BEN','BEN_92380_CANNOT_CALC_LFDPPCF');
4866: fnd_message.set_token('L_PROC',l_proc);
4867: fnd_message.set_token('PERSON_ID',l_person_id);
4868: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4886: fetch c_next_pay_period into l_next_pay_period;
4887: --
4888: if c_next_pay_period%notfound then
4889: close c_next_pay_period;
4890: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',130);
4891: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
4892: fnd_message.set_token('DATE_CODE',p_date_cd);
4893: fnd_message.set_token('PERSON_ID',l_person_id);
4894: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
4907: -- LELD - Latest of Elections, Event or Notified
4908: --
4909: elsif p_date_cd = 'LELD' then
4910: --
4911: -- hr_utility.set_location('Entering LELD',10);
4912: --
4913: if l_lf_evt_ocrd_dt is null then
4914: --
4915: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4956: -- LELDED - Later of Elections or Event
4957: --
4958: elsif p_date_cd = 'LELDED' then
4959: --
4960: -- hr_utility.set_location('Entering LELDED',10);
4961: --
4962: if l_lf_evt_ocrd_dt is null then
4963: --
4964: l_lf_evt_ocrd_dt:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
4996: -- should return null if result with current pil
4997: -- is not found, i.e. in benmngle. gets date at
4998: -- time of enrollment.
4999: --
5000: -- hr_utility.set_location('Entering ODEWM',10);
5001: --
5002: open c_prtt_enrt_rslt_dts;
5003: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
5004: l_enrt_cvg_strt_dt,
5020: -- should return null if result with current pil
5021: -- is not found, i.e. in benmngle. gets date at
5022: -- time of enrollment.
5023: --
5024: hr_utility.set_location('Entering AFDELD',10);
5025: --
5026: open c_prtt_enrt_rslt_dts;
5027: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
5028: l_enrt_cvg_strt_dt,
5028: l_enrt_cvg_strt_dt,
5029: l_enrt_eff_strt_date;
5030: --
5031: close c_prtt_enrt_rslt_dts;
5032: hr_utility.set_location('Entering AFDELD ' || l_enrt_eff_strt_date ,10);
5033: --
5034: -- when the first time cvg calcualted there may not be a result
5035: if l_enrt_eff_strt_date is null then
5036: l_enrt_eff_strt_date :=p_effective_date;
5036: l_enrt_eff_strt_date :=p_effective_date;
5037: end if;
5038: p_returned_date := last_day(l_enrt_eff_strt_date) + 1 ;
5039:
5040: hr_utility.set_location('AFDELD ' || p_returned_date ,10);
5041: --
5042: --
5043: -- FDMELD First of Month on or After Elections Made
5044: elsif p_date_cd = 'FDMELD' then
5046: -- should return null if result with current pil
5047: -- is not found, i.e. in benmngle. gets date at
5048: -- time of enrollment.
5049: --
5050: hr_utility.set_location('Entering FDMELD',10);
5051: --
5052: open c_prtt_enrt_rslt_dts;
5053: fetch c_prtt_enrt_rslt_dts into l_enrt_cvg_end_dt,
5054: l_enrt_cvg_strt_dt,
5065: elsif p_date_cd = 'ODD' then
5066: --
5067: -- ODD - On the De-enrollment Date.
5068: --
5069: -- hr_utility.set_location('Entering ODD',10);
5070: --
5071: p_returned_date := p_effective_date;
5072: --
5073: elsif p_date_cd = 'ODAD' then
5073: elsif p_date_cd = 'ODAD' then
5074: --
5075: -- ODAD - On the Default Applied Date.
5076: --
5077: -- hr_utility.set_location('Entering ODAD',10);
5078: --
5079: open c_cm_dflt_asnd_dt;
5080: fetch c_cm_dflt_asnd_dt into l_cm_date;
5081: if c_cm_dflt_asnd_dt%notfound then
5079: open c_cm_dflt_asnd_dt;
5080: fetch c_cm_dflt_asnd_dt into l_cm_date;
5081: if c_cm_dflt_asnd_dt%notfound then
5082: close c_cm_dflt_asnd_dt;
5083: -- hr_utility.set_location('BEN_91942_PEL_NOT_FOUND',96);
5084: fnd_message.set_name('BEN', 'BEN_91942_PEL_NOT_FOUND');
5085: fnd_message.set_token('DATE_CODE',p_date_cd);
5086: fnd_message.set_token('L_PROC',l_proc);
5087: fnd_message.raise_error;
5093: elsif p_date_cd = 'OEPD' then
5094: --
5095: -- OEPD - On the Eligible to Participate Date.
5096: --
5097: -- hr_utility.set_location('Entering OEPD',10);
5098: --
5099: open c_cm_elig_prtn_strt_dt;
5100: fetch c_cm_elig_prtn_strt_dt into l_cm_date;
5101: if c_cm_elig_prtn_strt_dt%notfound then
5099: open c_cm_elig_prtn_strt_dt;
5100: fetch c_cm_elig_prtn_strt_dt into l_cm_date;
5101: if c_cm_elig_prtn_strt_dt%notfound then
5102: close c_cm_elig_prtn_strt_dt;
5103: -- hr_utility.set_location('BEN_92381_ELIG_PER_NOT_FOUND',96);
5104: fnd_message.set_name('BEN', 'BEN_92381_ELIG_PER_NOT_FOUND');
5105: fnd_message.set_token('PROC',l_proc);
5106: fnd_message.set_token('DATE_CODE',p_date_cd);
5107: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
5116: elsif p_date_cd = 'OIPD' then
5117: --
5118: -- OIPD - On the Ineligible to Participate Date.
5119: --
5120: -- hr_utility.set_location('Entering OIPD',10);
5121: --
5122: open c_cm_elig_prtn_end_dt;
5123: fetch c_cm_elig_prtn_end_dt into l_cm_date;
5124: if c_cm_elig_prtn_end_dt%notfound then
5122: open c_cm_elig_prtn_end_dt;
5123: fetch c_cm_elig_prtn_end_dt into l_cm_date;
5124: if c_cm_elig_prtn_end_dt%notfound then
5125: close c_cm_elig_prtn_end_dt;
5126: -- hr_utility.set_location('BEN_92381_ELIG_PER_NOT_FOUND',98);
5127: fnd_message.set_name('BEN', 'BEN_92381_ELIG_PER_NOT_FOUND');
5128: fnd_message.set_token('PROC',l_proc);
5129: fnd_message.set_token('DATE_CODE',p_date_cd);
5130: fnd_message.set_token('PERSON_ID',to_char(p_person_id));
5139: elsif p_date_cd = 'OLDEP' then
5140: --
5141: -- OLDEP - On the last day of the enrollment period.
5142: --
5143: -- hr_utility.set_location('Entering OLDEP',10);
5144: --
5145: open c_cm_enrt_perd_end_dt;
5146: fetch c_cm_enrt_perd_end_dt into l_cm_date;
5147: if c_cm_enrt_perd_end_dt%notfound then
5172: else -- OED
5173: --
5174: -- OED - On the effective date.
5175: --
5176: -- hr_utility.set_location('Entering OED',10);
5177: --
5178: p_returned_date := p_effective_date;
5179: --
5180: -- ODBEFFD - One day before the effective date.
5185: --6823087
5186:
5187: elsif p_date_cd = 'ODBEFFD' then
5188: --
5189: -- hr_utility.set_location('Entering ODBEFFD',10);
5190: --
5191: p_returned_date := p_effective_date-1;
5192: --
5193: --Bug 6212793
5202: -- FDPPYCF - First Day of Program or Plan Year Concurrent with or Following
5203: --
5204: elsif p_date_cd = 'FDPPYCF' then
5205: --
5206: -- hr_utility.set_location('Entering FDPPYCF',10);
5207: --
5208: if l_lf_evt_ocrd_dt is null then
5209: --
5210: l_lf_evt_ocrd_dt := get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5246: -- FDMCF - First Day of Month concurrent with or following
5247: --
5248: elsif p_date_cd = 'FDMCF' then
5249: --
5250: -- hr_utility.set_location('Entering FDMCF',10);
5251: --
5252: if l_lf_evt_ocrd_dt is null then
5253: --
5254: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5275: -- FDPPCF - First day of pay period concurrent with or following
5276: --
5277: elsif p_date_cd = 'FDPPCF' then
5278: --
5279: -- hr_utility.set_location('Entering FDPPCF',10);
5280: --
5281: if l_lf_evt_ocrd_dt is null then
5282: --
5283: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5291: open c_pay_period;
5292: fetch c_pay_period into l_pay_period;
5293: if c_pay_period%notfound then
5294: close c_pay_period;
5295: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',135);
5296: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
5297: fnd_message.set_token('DATE_CODE',p_date_cd);
5298: fnd_message.set_token('PERSON_ID',l_person_id);
5299: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5313: fetch c_next_pay_period into l_next_pay_period;
5314: --
5315: if c_next_pay_period%notfound then
5316: close c_next_pay_period;
5317: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',140);
5318: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
5319: fnd_message.set_token('DATE_CODE',p_date_cd);
5320: fnd_message.set_token('PERSON_ID',l_person_id);
5321: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5332: -- RL - Rule
5333: --
5334: elsif p_date_cd = 'RL' then
5335: --
5336: -- hr_utility.set_location('Entering RL',10);
5337: --
5338: open c_asg;
5339: fetch c_asg into l_asg; --if notfound, don't care, will pass null to formula
5340: close c_asg;
5369: close c_pl_typ;
5370: end if;
5371:
5372: if g_debug then
5373: hr_utility.set_location ('ler_id '||to_char(l_ler_id),70);
5374: end if;
5375: if l_per_in_ler_id is not null then
5376: open c_ler;
5377: fetch c_ler into l_ler_id;
5378: close c_ler;
5379: end if;
5380:
5381: if g_debug then
5382: hr_utility.set_location ('Organization_id '||l_asg.organization_id,10);
5383: end if;
5384: if g_debug then
5385: hr_utility.set_location ('assignment_id '||l_asg.assignment_id,15);
5386: end if;
5381: if g_debug then
5382: hr_utility.set_location ('Organization_id '||l_asg.organization_id,10);
5383: end if;
5384: if g_debug then
5385: hr_utility.set_location ('assignment_id '||l_asg.assignment_id,15);
5386: end if;
5387: if g_debug then
5388: hr_utility.set_location ('Business_group_id '||p_business_group_id,20);
5389: end if;
5384: if g_debug then
5385: hr_utility.set_location ('assignment_id '||l_asg.assignment_id,15);
5386: end if;
5387: if g_debug then
5388: hr_utility.set_location ('Business_group_id '||p_business_group_id,20);
5389: end if;
5390: if g_debug then
5391: hr_utility.set_location ('pgm_id '||l_pgm_id,30);
5392: end if;
5387: if g_debug then
5388: hr_utility.set_location ('Business_group_id '||p_business_group_id,20);
5389: end if;
5390: if g_debug then
5391: hr_utility.set_location ('pgm_id '||l_pgm_id,30);
5392: end if;
5393: if g_debug then
5394: hr_utility.set_location ('pl_id '||l_pl_id,40);
5395: end if;
5390: if g_debug then
5391: hr_utility.set_location ('pgm_id '||l_pgm_id,30);
5392: end if;
5393: if g_debug then
5394: hr_utility.set_location ('pl_id '||l_pl_id,40);
5395: end if;
5396: if g_debug then
5397: hr_utility.set_location ('pl_typ_id '||l_pl_typ_id,50);
5398: end if;
5393: if g_debug then
5394: hr_utility.set_location ('pl_id '||l_pl_id,40);
5395: end if;
5396: if g_debug then
5397: hr_utility.set_location ('pl_typ_id '||l_pl_typ_id,50);
5398: end if;
5399: if g_debug then
5400: hr_utility.set_location ('opt_id '||l_opt.opt_id,60);
5401: end if;
5396: if g_debug then
5397: hr_utility.set_location ('pl_typ_id '||l_pl_typ_id,50);
5398: end if;
5399: if g_debug then
5400: hr_utility.set_location ('opt_id '||l_opt.opt_id,60);
5401: end if;
5402: if g_debug then
5403: hr_utility.set_location ('ler_id '||l_ler_id,70);
5404: end if;
5399: if g_debug then
5400: hr_utility.set_location ('opt_id '||l_opt.opt_id,60);
5401: end if;
5402: if g_debug then
5403: hr_utility.set_location ('ler_id '||l_ler_id,70);
5404: end if;
5405: if g_debug then
5406: hr_utility.set_location ('p_acty_base_rt_id '||p_acty_base_rt_id,50);
5407: end if;
5402: if g_debug then
5403: hr_utility.set_location ('ler_id '||l_ler_id,70);
5404: end if;
5405: if g_debug then
5406: hr_utility.set_location ('p_acty_base_rt_id '||p_acty_base_rt_id,50);
5407: end if;
5408: if g_debug then
5409: hr_utility.set_location ('p_bnfts_bal_id '||p_bnfts_bal_id,60);
5410: end if;
5405: if g_debug then
5406: hr_utility.set_location ('p_acty_base_rt_id '||p_acty_base_rt_id,50);
5407: end if;
5408: if g_debug then
5409: hr_utility.set_location ('p_bnfts_bal_id '||p_bnfts_bal_id,60);
5410: end if;
5411: if g_debug then
5412: hr_utility.set_location ('jurisdiction_code '||l_jurisdiction_code,70);
5413: end if;
5408: if g_debug then
5409: hr_utility.set_location ('p_bnfts_bal_id '||p_bnfts_bal_id,60);
5410: end if;
5411: if g_debug then
5412: hr_utility.set_location ('jurisdiction_code '||l_jurisdiction_code,70);
5413: end if;
5414:
5415: -- for all other codes LE date is calcualted when the LE date is null
5416: -- this update does that for formula too
5426: -- Call formula initialise routine
5427: -- Added param1 and param1_value parameters to fix bug 1531647
5428:
5429: if g_debug then
5430: hr_utility.set_location ('p_formula_id '||p_formula_id,1689);
5431: end if;
5432:
5433: l_outputs := benutils.formula
5434: (p_formula_id => p_formula_id
5459:
5460: p_returned_date := fnd_date.canonical_to_date(l_outputs(l_outputs.first).value);
5461:
5462: if g_debug then
5463: hr_utility.set_location ('p_returned_date='||p_returned_date,1689);
5464: end if;
5465: --
5466: --
5467: l_ben_disp_ff_warn_msg := get_profile_ff_warn_val();
5492: --
5493: -- LRD - Later of Recorded Date and Event Date
5494: --
5495: elsif p_date_cd in ( 'LRD' , 'NUMDOEN') then
5496: -- hr_utility.set_location('Entering LRD',10);
5497:
5498: if l_lf_evt_ocrd_dt is null then
5499: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5500: else
5524: -- LODBED - One day before later of Recorded Date and Event Date
5525: --
5526: elsif p_date_cd in ('LODBED','LWEM') then
5527: --
5528: -- hr_utility.set_location('Entering LODBED OR LWEM',10);
5529: --
5530: if l_lf_evt_ocrd_dt is null then
5531: --
5532: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5556: -- LALDCM - End of Month Later Event or Notified
5557: --
5558: elsif p_date_cd = 'LALDCM' then
5559: --
5560: -- hr_utility.set_location('Entering LALDCM',10);
5561: --
5562: if l_lf_evt_ocrd_dt is null then
5563: --
5564: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5580: -- LALDFM - End of Month After Later Event or Notified
5581: --
5582: elsif p_date_cd = 'LALDFM' then
5583: --
5584: -- hr_utility.set_location('Entering LALDFM',10);
5585: --
5586: if l_lf_evt_ocrd_dt is null then
5587: --
5588: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5603: -- LAFDFM - Later: First Day of Following Month
5604: --
5605: elsif p_date_cd = 'LAFDFM' then
5606: --
5607: -- hr_utility.set_location('Entering LAFDFM',10);
5608: --
5609: if l_lf_evt_ocrd_dt is null then
5610: --
5611: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5626: -- LALDCPPY - Later: Last day of current program or plan year
5627: --
5628: elsif p_date_cd = 'LALDCPPY' then
5629: --
5630: -- hr_utility.set_location('Entering LALDCPPY',10);
5631: --
5632: if l_lf_evt_ocrd_dt is null then
5633: --
5634: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5663: -- LAFDLMEPPY - First of Last Month in Year Later Event or Notified
5664: --
5665: elsif p_date_cd = 'LAFDLMEPPY' then
5666: --
5667: -- hr_utility.set_location('Entering LAFDLMEPPY',10);
5668: --
5669: if l_lf_evt_ocrd_dt is null then
5670: --
5671: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5701: -- LALDLPPEPPY - End of Last Pay Period of Event Year Later Event or Notified
5702: --
5703: elsif p_date_cd = 'LALDLPPEPPY' then
5704: --
5705: -- hr_utility.set_location('Entering LALDLPPEPPY',10);
5706: --
5707: if l_lf_evt_ocrd_dt is null then
5708: --
5709: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5729: --
5730: if c_pay_period_for_date%notfound then
5731: --
5732: close c_pay_period_for_date;
5733: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',145);
5734: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
5735: fnd_message.set_token('DATE_CODE',p_date_cd);
5736: fnd_message.set_token('PERSON_ID',l_person_id);
5737: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5749: --
5750: elsif p_date_cd = 'WALDLPPEPPY' then
5751: --
5752: if g_debug then
5753: hr_utility.set_location('Entering WALDLPPEPPY',10);
5754: end if;
5755: --
5756: if l_lf_evt_ocrd_dt is null then
5757: --
5770: --
5771: if c_pre_pay_period%notfound then
5772: --
5773: close c_pre_pay_period;
5774: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',150);
5775: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
5776: fnd_message.set_token('DATE_CODE',p_date_cd);
5777: fnd_message.set_token('PERSON_ID',l_person_id);
5778: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
5790: -- LODBEWM - 1 Prior New Rate Start or 1 Before Later Event or Notified
5791: --
5792: elsif p_date_cd = 'LODBEWM' then
5793: --
5794: -- hr_utility.set_location('Entering LODBEWM',10);
5795: --
5796: open c_prtt_rt_val_dts;
5797: --
5798: /*
5833: fetch c_enrt_rt_val_dt into l_end_date;
5834: if c_enrt_rt_val_dt%found then
5835: --
5836: if g_debug then
5837: hr_utility.set_location('l_end_date - 1',1999);
5838: end if;
5839: p_returned_date := l_end_date - 1;
5840: --
5841: else
5842: --
5843: if l_lf_evt_ocrd_dt is null then
5844: --
5845: if g_debug then
5846: hr_utility.set_location('l_lf_evt_ocrd_dt is null',1999);
5847: end if;
5848: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5849: --
5850: end if;
5869: --
5870: elsif p_date_cd = 'ODBEWM' then
5871: --
5872: if g_debug then
5873: hr_utility.set_location('Entering ODBEWM',10);
5874: end if;
5875: --
5876: open c_prtt_rt_val_dts;
5877: --
5881: */
5882: fetch c_prtt_rt_val_dts into l_dummy ;
5883: --
5884: if g_debug then
5885: hr_utility.set_location('l_start_date'||l_start_date , 1999);
5886: end if;
5887: if g_debug then
5888: hr_utility.set_location('l_end_date'||l_end_date , 1999);
5889: end if;
5884: if g_debug then
5885: hr_utility.set_location('l_start_date'||l_start_date , 1999);
5886: end if;
5887: if g_debug then
5888: hr_utility.set_location('l_end_date'||l_end_date , 1999);
5889: end if;
5890:
5891: if c_prtt_rt_val_dts%notfound then
5892: --
5892: --
5893: if l_lf_evt_ocrd_dt is null then
5894: --
5895: if g_debug then
5896: hr_utility.set_location('l_lf_evt_ocrd_dt is null',1999);
5897: end if;
5898: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5899: --
5900: end if;
5899: --
5900: end if;
5901: --
5902: if g_debug then
5903: hr_utility.set_location('l_event_date - 1',1999);
5904: end if;
5905: p_returned_date := l_event_date - 1;
5906: --
5907: else
5913: --
5914: if c_enrt_rt_val_dt%found then
5915: --
5916: if g_debug then
5917: hr_utility.set_location('l_end_date - 1',1999);
5918: end if;
5919: p_returned_date := l_end_date - 1;
5920: --
5921: else
5922:
5923: if l_lf_evt_ocrd_dt is null then
5924: --
5925: if g_debug then
5926: hr_utility.set_location('p_lf_evt_ocrd_dt is null',1999);
5927: end if;
5928: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5929: --
5930: end if;
5928: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
5929: --
5930: end if;
5931: if g_debug then
5932: hr_utility.set_location('l_lf_evt_ocrd_dt else'||l_event_date , 1999);
5933: end if;
5934: p_returned_date := l_event_date - 1;
5935: --
5936: end if;
5952: --
5953: elsif p_date_cd = 'LWODBED' then
5954: --
5955: if g_debug then
5956: hr_utility.set_location('Entering LWODBED',10);
5957: end if;
5958: --
5959: if l_lf_evt_ocrd_dt is null then
5960: --
5987: --
5988: elsif p_date_cd = 'LWALDLPPEPPY' then
5989: --
5990: if g_debug then
5991: hr_utility.set_location('Entering LWALDLPPEPPY',10);
5992: end if;
5993: --
5994: if l_lf_evt_ocrd_dt is null then
5995: --
6023: l_start_date,
6024: l_end_date;
6025: --
6026: if c_pay_period_for_date%notfound then
6027: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',160);
6028: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
6029: fnd_message.set_token('DATE_CODE',p_date_cd);
6030: fnd_message.set_token('PERSON_ID',l_person_id);
6031: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
6058: l_start_date,
6059: l_end_date;
6060: --
6061: if c_pay_period_for_date%notfound then
6062: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',165);
6063: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
6064: fnd_message.set_token('DATE_CODE',p_date_cd);
6065: fnd_message.set_token('PERSON_ID',l_person_id);
6066: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
6080: --
6081: elsif p_date_cd = 'LWALDCPPY' then
6082: --
6083: if g_debug then
6084: hr_utility.set_location('Entering LWALDCPPY',10);
6085: end if;
6086: --
6087: if l_lf_evt_ocrd_dt is null then
6088: --
6137: --
6138: elsif p_date_cd = 'LWALDCPP' then
6139: --
6140: if g_debug then
6141: hr_utility.set_location('Entering LWALDCPP',10);
6142: end if;
6143: --
6144: if l_lf_evt_ocrd_dt is null then
6145: --
6180: --
6181: elsif p_date_cd = 'LWALDCM' then
6182: --
6183: if g_debug then
6184: hr_utility.set_location('Entering LWALDCM',10);
6185: end if;
6186: --
6187: if l_lf_evt_ocrd_dt is null then
6188: --
6210: -- LFDPPYCF - First of Year On or After Later of Event or Notified
6211: --
6212: elsif p_date_cd = 'LFDPPYCF' then
6213: --
6214: -- hr_utility.set_location('Entering LFDPPYCF',10);
6215: --
6216: if l_lf_evt_ocrd_dt is null then
6217: --
6218: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6267: -- LAFDCPPY - Later: First day of current program or plan year
6268: --
6269: elsif p_date_cd = 'LAFDCPPY' then
6270: --
6271: -- hr_utility.set_location('Entering LAFDCPPY',10);
6272: --
6273: if l_lf_evt_ocrd_dt is null then
6274: --
6275: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6304: -- LAFDFPPY - Later: First day of following program or plan year
6305: --
6306: elsif p_date_cd = 'LAFDFPPY' then
6307: --
6308: -- hr_utility.set_location('Entering LAFDFPPY',10);
6309: --
6310: if l_lf_evt_ocrd_dt is null then
6311: --
6312: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6341: -- LALDCPP - Later: Last day of current pay period
6342: --
6343: elsif p_date_cd = 'LALDCPP' then
6344: --
6345: -- hr_utility.set_location('Entering LALDCPP',10);
6346: --
6347: if l_lf_evt_ocrd_dt is null then
6348: --
6349: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6365: open c_pay_period;
6366: fetch c_pay_period into l_pay_period;
6367: if c_pay_period%notfound then
6368: close c_pay_period;
6369: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',170);
6370: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
6371: fnd_message.set_token('DATE_CODE',p_date_cd);
6372: fnd_message.set_token('PERSON_ID',l_person_id);
6373: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
6381: -- LAFDFPP - Later: First day of following pay period
6382: --
6383: elsif p_date_cd = 'LAFDFPP' then
6384: --
6385: -- hr_utility.set_location('Entering LAFDFPP',10);
6386: --
6387: if l_lf_evt_ocrd_dt is null then
6388: --
6389: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6406: fetch c_next_pay_period into l_next_pay_period;
6407: --
6408: if c_next_pay_period%notfound then
6409: close c_next_pay_period;
6410: -- hr_utility.set_location('BEN_91477_PAY_PERIOD_MISSING',170);
6411: fnd_message.set_name('BEN','BEN_91477_PAY_PERIOD_MISSING');
6412: fnd_message.set_token('DATE_CODE',p_date_cd);
6413: fnd_message.set_token('PERSON_ID',l_person_id);
6414: fnd_message.set_token('BUSINESS_GROUP_ID',l_business_group_id);
6421: -- LTODFED - Thirty-one days After Later of Recorded Date and Event Date
6422: --
6423: elsif p_date_cd = 'LTODFED' then
6424: --
6425: -- hr_utility.set_location('Entering LTODFED',10);
6426: --
6427: if l_lf_evt_ocrd_dt is null then
6428: --
6429: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6444: -- LSDFED - Sixty days After Later of Recorded Date and Event Date
6445: --
6446: elsif p_date_cd = 'LSDFED' then
6447: --
6448: -- hr_utility.set_location('Entering LSDFED',10);
6449: --
6450: if l_lf_evt_ocrd_dt is null then
6451: --
6452: l_event_date:= get_event_date(p_cache_mode,l_per_in_ler_id,p_effective_date);
6472: elsif p_date_cd in ( '30DAHRD','31DAHRD','60DAHRD','61DAHRD') then
6473:
6474: -- get the latest hire date, if fails error
6475: if g_debug then
6476: hr_utility.set_location(' Hire date ' , 99 );
6477: end if;
6478: open c_pps ;
6479: fetch c_pps into l_hire_date ;
6480: close c_pps ;
6478: open c_pps ;
6479: fetch c_pps into l_hire_date ;
6480: close c_pps ;
6481: if g_debug then
6482: hr_utility.set_location(' Hire date ' || l_hire_date, 99 );
6483: end if;
6484: if l_hire_date is null then
6485: fnd_message.set_name('BEN','BEN_92489_CANNOT_CALC_DATE');
6486: fnd_message.set_token('DATE_CODE',p_date_cd);
6487: fnd_message.set_token('L_PROC',l_proc);
6488: fnd_message.raise_error;
6489: end if ;
6490: if g_debug then
6491: hr_utility.set_location(' Hire date ' || l_hire_date, 99 );
6492: end if;
6493:
6494: if p_date_cd = '30DAHRD' then
6495: p_returned_date := l_hire_date + 30 ;
6500: elsif p_date_cd = '61DAHRD' then
6501: p_returned_date := l_hire_date + 61 ;
6502: end if ;
6503: if g_debug then
6504: hr_utility.set_location(' p_date_cd ' || p_returned_date, 99 );
6505: end if;
6506:
6507: elsif p_date_cd in ('AFDELY','ALDELMY','FDM','FDMCFC','FDMFC',
6508: 'FDPP','FDPPQCFC','FDPPQFC','FDPPSYCFC','FDPPSYFC', 'FDPPYCFC','FDPPYFC','FDQ','FFDFED','FFDFEPS',
6510: 'NDFEPS','WALDCPPQ','WALDCPPSY','FDCY','FFDFEPS',
6511: 'FDLY','FDQ','FDSY', 'LFSEMES'
6512: ) then
6513:
6514: -- hr_utility.set_location('Future Date Code: '||p_date_cd , 222);
6515: fnd_message.set_name('BEN','FUTURE_DATE_CD_DO_NOT_USE');
6516: fnd_message.set_token('DATE_CODE',p_date_cd);
6517: fnd_message.raise_error;
6518:
6518:
6519: elsif p_date_cd in ('FDPPFCDE','FDPPFCDEL') then
6520: --
6521: if g_debug then
6522: hr_utility.set_location(' Step 2 ' ,181);
6523: end if;
6524: --
6525: if p_date_cd = 'FDPPFCDE' then
6526: --
6529: end if;
6530: l_start_date := l_lf_evt_ocrd_dt;
6531: else
6532: --
6533: hr_utility.set_location('p effective date '||p_effective_date,182);
6534: l_start_date := p_effective_date;
6535: end if;
6536: --
6537: open c_pay_period_for_check(l_start_date, 'E');
6573:
6574: elsif p_date_cd in ('WAPPCDE','WAPPDEL') then
6575: --
6576: if g_debug then
6577: hr_utility.set_location(' Step 2 ' ,181);
6578: end if;
6579: --
6580: if p_date_cd = 'WAPPCDE' then
6581: --
6613: close c_pay_period_for_check_end ;
6614: p_returned_date := l_end_date;
6615: else
6616:
6617: hr_utility.set_location('Unknown Date Code: '||p_date_cd , 222);
6618: fnd_message.set_name('BEN','BEN_91534_BEN_UNKNOWN_DATE_CD');
6619: fnd_message.set_token('DATE_CODE',p_date_cd);
6620: fnd_message.set_token('L_PROC',l_proc);
6621: fnd_message.raise_error;
6622:
6623: end if;
6624: --
6625: if g_debug then
6626: hr_utility.set_location('p_returned_date: '||p_returned_date,15);
6627: end if;
6628: p_returned_date := trunc(p_returned_date);
6629: --
6630: if g_debug then
6627: end if;
6628: p_returned_date := trunc(p_returned_date);
6629: --
6630: if g_debug then
6631: hr_utility.set_location('sent dt code ' || p_date_cd ,596);
6632: end if;
6633: if g_debug then
6634: hr_utility.set_location('sent_dt p_returned_date: '||p_returned_date,15);
6635: end if;
6630: if g_debug then
6631: hr_utility.set_location('sent dt code ' || p_date_cd ,596);
6632: end if;
6633: if g_debug then
6634: hr_utility.set_location('sent_dt p_returned_date: '||p_returned_date,15);
6635: end if;
6636: if g_debug then
6637: hr_utility.set_location('Leaving : '||l_proc , 20);
6638: end if;
6633: if g_debug then
6634: hr_utility.set_location('sent_dt p_returned_date: '||p_returned_date,15);
6635: end if;
6636: if g_debug then
6637: hr_utility.set_location('Leaving : '||l_proc , 20);
6638: end if;
6639: exception -- nocopy changes
6640: --
6641: when others then
7312:
7313:
7314: begin
7315: --
7316: g_debug := hr_utility.debug_enabled;
7317: if g_debug then
7318: hr_utility.set_location ('Entering '||l_proc,10);
7319: end if;
7320: /*
7314: begin
7315: --
7316: g_debug := hr_utility.debug_enabled;
7317: if g_debug then
7318: hr_utility.set_location ('Entering '||l_proc,10);
7319: end if;
7320: /*
7321: if p_which_dates_cd = 'R' then
7322: --hr_utility.set_location('p_cache_mode '|| p_cache_mode , 1687);
7318: hr_utility.set_location ('Entering '||l_proc,10);
7319: end if;
7320: /*
7321: if p_which_dates_cd = 'R' then
7322: --hr_utility.set_location('p_cache_mode '|| p_cache_mode , 1687);
7323: if g_debug then
7324: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 1687);
7325: end if;
7326: if g_debug then
7320: /*
7321: if p_which_dates_cd = 'R' then
7322: --hr_utility.set_location('p_cache_mode '|| p_cache_mode , 1687);
7323: if g_debug then
7324: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 1687);
7325: end if;
7326: if g_debug then
7327: hr_utility.set_location('p_person_id '|| p_person_id , 1687);
7328: end if;
7323: if g_debug then
7324: hr_utility.set_location('p_per_in_ler_id '|| p_per_in_ler_id , 1687);
7325: end if;
7326: if g_debug then
7327: hr_utility.set_location('p_person_id '|| p_person_id , 1687);
7328: end if;
7329: if g_debug then
7330: hr_utility.set_location('p_pgm_id '|| p_pgm_id , 1687);
7331: end if;
7326: if g_debug then
7327: hr_utility.set_location('p_person_id '|| p_person_id , 1687);
7328: end if;
7329: if g_debug then
7330: hr_utility.set_location('p_pgm_id '|| p_pgm_id , 1687);
7331: end if;
7332: if g_debug then
7333: hr_utility.set_location('p_pl_id '|| p_pl_id , 1687);
7334: end if;
7329: if g_debug then
7330: hr_utility.set_location('p_pgm_id '|| p_pgm_id , 1687);
7331: end if;
7332: if g_debug then
7333: hr_utility.set_location('p_pl_id '|| p_pl_id , 1687);
7334: end if;
7335: if g_debug then
7336: hr_utility.set_location('p_oipl_id '|| p_oipl_id , 1687);
7337: end if;
7332: if g_debug then
7333: hr_utility.set_location('p_pl_id '|| p_pl_id , 1687);
7334: end if;
7335: if g_debug then
7336: hr_utility.set_location('p_oipl_id '|| p_oipl_id , 1687);
7337: end if;
7338: if g_debug then
7339: hr_utility.set_location('p_par_ptip_id '|| p_par_ptip_id , 1687);
7340: end if;
7335: if g_debug then
7336: hr_utility.set_location('p_oipl_id '|| p_oipl_id , 1687);
7337: end if;
7338: if g_debug then
7339: hr_utility.set_location('p_par_ptip_id '|| p_par_ptip_id , 1687);
7340: end if;
7341: if g_debug then
7342: hr_utility.set_location('p_lee_rsn_id '|| p_lee_rsn_id , 1687);
7343: end if;
7338: if g_debug then
7339: hr_utility.set_location('p_par_ptip_id '|| p_par_ptip_id , 1687);
7340: end if;
7341: if g_debug then
7342: hr_utility.set_location('p_lee_rsn_id '|| p_lee_rsn_id , 1687);
7343: end if;
7344: if g_debug then
7345: hr_utility.set_location('p_enrt_perd_id '|| p_enrt_perd_id , 1687);
7346: end if;
7341: if g_debug then
7342: hr_utility.set_location('p_lee_rsn_id '|| p_lee_rsn_id , 1687);
7343: end if;
7344: if g_debug then
7345: hr_utility.set_location('p_enrt_perd_id '|| p_enrt_perd_id , 1687);
7346: end if;
7347: if g_debug then
7348: hr_utility.set_location('p_enrt_perd_for_pl_id '|| p_enrt_perd_for_pl_id , 1687);
7349: end if;
7344: if g_debug then
7345: hr_utility.set_location('p_enrt_perd_id '|| p_enrt_perd_id , 1687);
7346: end if;
7347: if g_debug then
7348: hr_utility.set_location('p_enrt_perd_for_pl_id '|| p_enrt_perd_for_pl_id , 1687);
7349: end if;
7350: if g_debug then
7351: hr_utility.set_location('p_which_dates_cd '|| p_which_dates_cd , 1687);
7352: end if;
7347: if g_debug then
7348: hr_utility.set_location('p_enrt_perd_for_pl_id '|| p_enrt_perd_for_pl_id , 1687);
7349: end if;
7350: if g_debug then
7351: hr_utility.set_location('p_which_dates_cd '|| p_which_dates_cd , 1687);
7352: end if;
7353: if g_debug then
7354: hr_utility.set_location('p_date_mandatory_flag '|| p_date_mandatory_flag , 1687);
7355: end if;
7350: if g_debug then
7351: hr_utility.set_location('p_which_dates_cd '|| p_which_dates_cd , 1687);
7352: end if;
7353: if g_debug then
7354: hr_utility.set_location('p_date_mandatory_flag '|| p_date_mandatory_flag , 1687);
7355: end if;
7356: if g_debug then
7357: hr_utility.set_location('p_compute_dates_flag '|| p_compute_dates_flag , 1687);
7358: end if;
7353: if g_debug then
7354: hr_utility.set_location('p_date_mandatory_flag '|| p_date_mandatory_flag , 1687);
7355: end if;
7356: if g_debug then
7357: hr_utility.set_location('p_compute_dates_flag '|| p_compute_dates_flag , 1687);
7358: end if;
7359: if g_debug then
7360: hr_utility.set_location('p_elig_per_elctbl_chc_id '|| p_elig_per_elctbl_chc_id , 1687);
7361: end if;
7356: if g_debug then
7357: hr_utility.set_location('p_compute_dates_flag '|| p_compute_dates_flag , 1687);
7358: end if;
7359: if g_debug then
7360: hr_utility.set_location('p_elig_per_elctbl_chc_id '|| p_elig_per_elctbl_chc_id , 1687);
7361: end if;
7362: if g_debug then
7363: hr_utility.set_location('p_acty_base_rt_id '|| p_acty_base_rt_id , 1687);
7364: end if;
7359: if g_debug then
7360: hr_utility.set_location('p_elig_per_elctbl_chc_id '|| p_elig_per_elctbl_chc_id , 1687);
7361: end if;
7362: if g_debug then
7363: hr_utility.set_location('p_acty_base_rt_id '|| p_acty_base_rt_id , 1687);
7364: end if;
7365: if g_debug then
7366: hr_utility.set_location('p_business_group_id '|| p_business_group_id , 1687);
7367: end if;
7362: if g_debug then
7363: hr_utility.set_location('p_acty_base_rt_id '|| p_acty_base_rt_id , 1687);
7364: end if;
7365: if g_debug then
7366: hr_utility.set_location('p_business_group_id '|| p_business_group_id , 1687);
7367: end if;
7368: if g_debug then
7369: hr_utility.set_location('p_start_date '|| p_start_date , 1687);
7370: end if;
7365: if g_debug then
7366: hr_utility.set_location('p_business_group_id '|| p_business_group_id , 1687);
7367: end if;
7368: if g_debug then
7369: hr_utility.set_location('p_start_date '|| p_start_date , 1687);
7370: end if;
7371: if g_debug then
7372: hr_utility.set_location('p_end_date '|| p_end_date , 1687);
7373: end if;
7368: if g_debug then
7369: hr_utility.set_location('p_start_date '|| p_start_date , 1687);
7370: end if;
7371: if g_debug then
7372: hr_utility.set_location('p_end_date '|| p_end_date , 1687);
7373: end if;
7374: if g_debug then
7375: hr_utility.set_location('p_effective_date '|| p_effective_date , 1687);
7376: end if;
7371: if g_debug then
7372: hr_utility.set_location('p_end_date '|| p_end_date , 1687);
7373: end if;
7374: if g_debug then
7375: hr_utility.set_location('p_effective_date '|| p_effective_date , 1687);
7376: end if;
7377: if g_debug then
7378: hr_utility.set_location('p_lf_evt_ocrd_dt '|| p_lf_evt_ocrd_dt , 1687);
7379: end if;
7374: if g_debug then
7375: hr_utility.set_location('p_effective_date '|| p_effective_date , 1687);
7376: end if;
7377: if g_debug then
7378: hr_utility.set_location('p_lf_evt_ocrd_dt '|| p_lf_evt_ocrd_dt , 1687);
7379: end if;
7380: end if;
7381: */
7382: --
7383:
7384:
7385: l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
7386:
7387: hr_utility.set_location('l_effective_date ' || l_effective_date, 2080);
7388: --
7389: -- Get the necessary info to start processing
7390: --
7391: if p_cache_mode
7405: --
7406: elsif p_elig_per_elctbl_chc_id is not null then
7407: --
7408: if g_debug then
7409: hr_utility.set_location(l_proc , 20);
7410: end if;
7411: --
7412: -- get from elig_per_elctbl_chc
7413: -- Note: Don't need all the args just the ones for direct use by
7425: l_fonm_cvg_strt_dt
7426: ;
7427: --
7428: if g_debug then
7429: hr_utility.set_location('l_oipl_id '||l_oipl_id,19);
7430: end if;
7431: if c_epe_info%notfound then
7432: close c_epe_info;
7433: fnd_message.set_name('BEN','BEN_91457_ELCTBL_CHC_NOT_FOUND');
7436: fnd_message.set_token('PROC',l_proc);
7437: fnd_message.raise_error;
7438: end if;
7439: if g_debug then
7440: hr_utility.set_location(l_proc , 40);
7441: end if;
7442:
7443: close c_epe_info;
7444: else
7442:
7443: close c_epe_info;
7444: else
7445: if g_debug then
7446: hr_utility.set_location(l_proc , 50);
7447: end if;
7448: --
7449: -- use args
7450: --
7458: --
7459: end if;
7460: --
7461: if g_debug then
7462: hr_utility.set_location(l_proc , 60);
7463: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7464: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7465: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7466: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7459: end if;
7460: --
7461: if g_debug then
7462: hr_utility.set_location(l_proc , 60);
7463: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7464: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7465: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7466: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7467: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7460: --
7461: if g_debug then
7462: hr_utility.set_location(l_proc , 60);
7463: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7464: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7465: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7466: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7467: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7468: hr_utility.set_location('plan ' || p_pl_id , 60);
7461: if g_debug then
7462: hr_utility.set_location(l_proc , 60);
7463: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7464: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7465: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7466: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7467: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7468: hr_utility.set_location('plan ' || p_pl_id , 60);
7469:
7462: hr_utility.set_location(l_proc , 60);
7463: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7464: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7465: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7466: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7467: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7468: hr_utility.set_location('plan ' || p_pl_id , 60);
7469:
7470: end if;
7463: hr_utility.set_location('FONM FLAG ' || ben_manage_life_events.fonm , 60);
7464: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7465: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7466: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7467: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7468: hr_utility.set_location('plan ' || p_pl_id , 60);
7469:
7470: end if;
7471:
7464: hr_utility.set_location('FONM gc date ' || ben_manage_life_events.g_fonm_cvg_strt_dt , 60);
7465: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
7466: hr_utility.set_location('FONM epe date ' || l_fonm_cvg_strt_dt , 60);
7467: hr_utility.set_location('which date code ' || p_which_dates_cd , 60);
7468: hr_utility.set_location('plan ' || p_pl_id , 60);
7469:
7470: end if;
7471:
7472:
7479: --
7480: -- Get the ptip id for the pl and pgm
7481: --
7482: if l_ptip_id is null then
7483: -- hr_utility.set_location(' c_gptip_id: '||l_proc , 140);
7484: open c_gptip_id
7485: (c_pl_id => l_pl_id
7486: ,c_pgm_id => l_pgm_id
7487: ,c_effective_date => l_effective_date
7488: );
7489: fetch c_gptip_id into l_ptip_id;
7490: close c_gptip_id;
7491: if g_debug then
7492: hr_utility.set_location(' Dn c_gptip_id: '||l_proc , 140);
7493: end if;
7494: else
7495: l_ptip_id := p_par_ptip_id;
7496: end if;
7498: -- Get the plip id for the pl and pgm
7499: --
7500: if l_plip_id is null then
7501: if g_debug then
7502: hr_utility.set_location(' c_gplip_id: '||l_proc , 140);
7503: end if;
7504: open c_gplip_id
7505: (c_pl_id => l_pl_id
7506: ,c_pgm_id => l_pgm_id
7508: );
7509: fetch c_gplip_id into l_plip_id;
7510: close c_gplip_id;
7511: if g_debug then
7512: hr_utility.set_location(' Dn c_gplip_id: '||l_proc , 140);
7513: end if;
7514: else
7515: l_plip_id := p_par_plip_id;
7516: end if;
7516: end if;
7517: --
7518: if l_enrt_perd_id is not null then
7519: if g_debug then
7520: hr_utility.set_location(' Op c_SCFP: '||l_proc , 140);
7521: end if;
7522: --
7523: -- Check if the enrt perd for pl id is passed in
7524: --
7549: ,c_per_in_ler_id => l_per_in_ler_id -- bug 4356591
7550: );
7551: fetch c_sched_for_plip into l_rec;
7552: if g_debug then
7553: hr_utility.set_location(' Dn Fet c_SCFP: '||l_proc , 140);
7554: hr_utility.set_location(' l_rec.enrt_cvg_strt_dt_cd : '|| l_rec.enrt_cvg_strt_dt_cd , 140);
7555: hr_utility.set_location(' l_rec.enrt_cvg_end_dt_cd : '|| l_rec.enrt_cvg_end_dt_cd , 140);
7556: end if;
7557: l_found:=c_sched_for_plip%found;
7550: );
7551: fetch c_sched_for_plip into l_rec;
7552: if g_debug then
7553: hr_utility.set_location(' Dn Fet c_SCFP: '||l_proc , 140);
7554: hr_utility.set_location(' l_rec.enrt_cvg_strt_dt_cd : '|| l_rec.enrt_cvg_strt_dt_cd , 140);
7555: hr_utility.set_location(' l_rec.enrt_cvg_end_dt_cd : '|| l_rec.enrt_cvg_end_dt_cd , 140);
7556: end if;
7557: l_found:=c_sched_for_plip%found;
7558: else
7551: fetch c_sched_for_plip into l_rec;
7552: if g_debug then
7553: hr_utility.set_location(' Dn Fet c_SCFP: '||l_proc , 140);
7554: hr_utility.set_location(' l_rec.enrt_cvg_strt_dt_cd : '|| l_rec.enrt_cvg_strt_dt_cd , 140);
7555: hr_utility.set_location(' l_rec.enrt_cvg_end_dt_cd : '|| l_rec.enrt_cvg_end_dt_cd , 140);
7556: end if;
7557: l_found:=c_sched_for_plip%found;
7558: else
7559: --
7560: -- do life event plip processing
7561: -- also used for unrestricted plip
7562: --
7563: if g_debug then
7564: hr_utility.set_location(' Op c_glee_epfp_id: '||l_proc , 150);
7565: end if;
7566: --
7567: if l_enrt_perd_for_pl_id is null then
7568: --
7580: --
7581: end if;
7582: --
7583: if g_debug then
7584: hr_utility.set_location(' Dn c_glee_epfp_id: '||l_proc , 150);
7585: end if;
7586: --
7587: -- Fetch the first row from the union cache
7588: --
7592: and p_pl_row.pl_id is not null
7593: then
7594:
7595: if g_debug then
7596: hr_utility.set_location(' Building the union cache ', 1687);
7597: end if;
7598: --
7599: -- Build up union set
7600: --
7622: end if;
7623: close c_gepp_dets;
7624: --
7625: if g_debug then
7626: hr_utility.set_location(' l_lee_rsn_id: '||l_lee_rsn_id, 1687);
7627: end if;
7628:
7629: open c_gleersn_dets
7630: (c_lee_rsn_id => l_lee_rsn_id
7754: --
7755: end if;
7756: --
7757: if g_debug then
7758: hr_utility.set_location(' Done Building the union cache l_unionele_num = '||l_unionele_num, 1687);
7759: end if;
7760: else
7761: --
7762: open c_life_for_plip
7769: ,c_epfp_id => l_enrt_perd_for_pl_id
7770: ,c_per_in_ler_id => l_per_in_ler_id
7771: );
7772: if g_debug then
7773: hr_utility.set_location('Fetching rt start date cd' , 1687);
7774: end if;
7775: fetch c_life_for_plip into l_rec;
7776: if g_debug then
7777: hr_utility.set_location(' Dn Fet c_LFP: '||l_proc , 150);
7773: hr_utility.set_location('Fetching rt start date cd' , 1687);
7774: end if;
7775: fetch c_life_for_plip into l_rec;
7776: if g_debug then
7777: hr_utility.set_location(' Dn Fet c_LFP: '||l_proc , 150);
7778: end if;
7779: l_found:=c_life_for_plip%found;
7780: --
7781: end if;
7783: end if;
7784: else
7785: if l_enrt_perd_id is not null then
7786: if g_debug then
7787: hr_utility.set_location(l_proc , 190);
7788: end if;
7789: -- do scheduled pl_nip processing
7790: open c_sched_for_pl_nip;
7791: fetch c_sched_for_pl_nip into l_rec;
7791: fetch c_sched_for_pl_nip into l_rec;
7792: l_found:=c_sched_for_pl_nip%found;
7793: else
7794: if g_debug then
7795: hr_utility.set_location(l_proc , 200);
7796: end if;
7797: -- do life event pl_nip processing
7798: -- also used for unrestricted pl_nip
7799: if g_debug then
7796: end if;
7797: -- do life event pl_nip processing
7798: -- also used for unrestricted pl_nip
7799: if g_debug then
7800: hr_utility.set_location(' Doing c_life_for_pl_nip', 1687);
7801: end if;
7802: open c_life_for_pl_nip;
7803: fetch c_life_for_pl_nip into l_rec;
7804: l_found:=c_life_for_pl_nip%found;
7805: end if;
7806: end if;
7807: loop
7808: if g_debug then
7809: hr_utility.set_location(l_proc , 240);
7810: end if;
7811: exit when l_found = FALSE;
7812: --
7813: -- process rates
7812: --
7813: -- process rates
7814: --
7815: if g_debug then
7816: hr_utility.set_location(l_proc , 250);
7817: end if;
7818: if p_which_dates_cd in ('R','B') then
7819: if l_rt_strt_dt_cd is null and
7820: l_rec.rt_strt_dt_cd is not null then
7818: if p_which_dates_cd in ('R','B') then
7819: if l_rt_strt_dt_cd is null and
7820: l_rec.rt_strt_dt_cd is not null then
7821: if g_debug then
7822: hr_utility.set_location(l_proc , 270);
7823: end if;
7824: l_rt_strt_dt_cd:=l_rec.rt_strt_dt_cd;
7825: l_rt_strt_dt_rl:=l_rec.rt_strt_dt_rl;
7826: end if;
7826: end if;
7827: if l_rt_end_dt_cd is null and
7828: l_rec.rt_end_dt_cd is not null then
7829: if g_debug then
7830: hr_utility.set_location(l_proc , 290);
7831: end if;
7832: l_rt_end_dt_cd:=l_rec.rt_end_dt_cd;
7833: l_rt_end_dt_rl:=l_rec.rt_end_dt_rl;
7834: end if;
7834: end if;
7835: end if;
7836: --
7837: if g_debug then
7838: hr_utility.set_location(l_proc , 310);
7839: end if;
7840: --
7841: -- process coverage
7842: --
7843: if p_which_dates_cd in ('C','B') then
7844: if l_enrt_cvg_strt_dt_cd is null and
7845: l_rec.enrt_cvg_strt_dt_cd is not null then
7846: if g_debug then
7847: hr_utility.set_location(l_proc , 330);
7848: end if;
7849: l_enrt_cvg_strt_dt_cd:=l_rec.enrt_cvg_strt_dt_cd;
7850: l_enrt_cvg_strt_dt_rl:=l_rec.enrt_cvg_strt_dt_rl;
7851: end if;
7851: end if;
7852: if l_enrt_cvg_end_dt_cd is null and
7853: l_rec.enrt_cvg_end_dt_cd is not null then
7854: if g_debug then
7855: hr_utility.set_location(l_proc , 350);
7856: end if;
7857: l_enrt_cvg_end_dt_cd:=l_rec.enrt_cvg_end_dt_cd;
7858: l_enrt_cvg_end_dt_rl:=l_rec.enrt_cvg_end_dt_rl;
7859: end if;
7859: end if;
7860: end if;
7861: --
7862: if g_debug then
7863: hr_utility.set_location(l_proc , 360);
7864: end if;
7865: --
7866: -- determine if done
7867: --
7876: l_enrt_cvg_end_dt_cd is not null and
7877: l_rt_strt_dt_cd is not null and
7878: l_rt_end_dt_cd is not null)) then
7879: if g_debug then
7880: hr_utility.set_location(l_proc , 380);
7881: end if;
7882: l_found:=FALSE;
7883: l_done:=TRUE;
7884: else
7882: l_found:=FALSE;
7883: l_done:=TRUE;
7884: else
7885: if g_debug then
7886: hr_utility.set_location(l_proc , 390);
7887: end if;
7888: --
7889: -- if not done then get the next row
7890: --
7890: --
7891: if l_pgm_id is not null then
7892: if l_enrt_perd_id is not null then
7893: if g_debug then
7894: hr_utility.set_location(l_proc , 410);
7895: end if;
7896: -- do scheduled plip processing
7897: fetch c_sched_for_plip into l_rec;
7898: l_found:=c_sched_for_plip%found;
7899: else
7900: -- do life event plip processing
7901: -- also used for unrestricted pl_nip
7902: if g_debug then
7903: hr_utility.set_location(' Fet c_LFPLIP: '||l_proc , 420);
7904: end if;
7905: if p_pgm_row.pgm_id is not null
7906: and p_ptip_row.ptip_id is not null
7907: and p_plip_row.plip_id is not null
7938: end if;
7939: else
7940: if l_enrt_perd_id is not null then
7941: if g_debug then
7942: hr_utility.set_location(l_proc , 460);
7943: end if;
7944: -- do scheduled pl_nip processing
7945: fetch c_sched_for_pl_nip into l_rec;
7946: l_found:=c_sched_for_pl_nip%found;
7945: fetch c_sched_for_pl_nip into l_rec;
7946: l_found:=c_sched_for_pl_nip%found;
7947: else
7948: if g_debug then
7949: hr_utility.set_location(l_proc , 470);
7950: end if;
7951: -- do life event pl_nip processing
7952: -- also used for unrestricted pl_nip
7953: fetch c_life_for_pl_nip into l_rec;
7955: end if;
7956: end if;
7957: end if;
7958: if g_debug then
7959: hr_utility.set_location(' End loop: '||l_proc , 420);
7960: end if;
7961: end loop;
7962: --
7963: -- close cursors
7964: --
7965: if l_pgm_id is not null then
7966: if l_enrt_perd_id is not null then
7967: if g_debug then
7968: hr_utility.set_location(l_proc , 860);
7969: end if;
7970: -- do scheduled plip processing
7971: close c_sched_for_plip;
7972: else
7970: -- do scheduled plip processing
7971: close c_sched_for_plip;
7972: else
7973: if g_debug then
7974: hr_utility.set_location(l_proc , 870);
7975: end if;
7976: -- do life event plip processing
7977: -- also used for unrestricted plip
7978: if p_pgm_row.pgm_id is not null
7992: end if;
7993: else
7994: if l_enrt_perd_id is not null then
7995: if g_debug then
7996: hr_utility.set_location(l_proc , 900);
7997: end if;
7998: -- do scheduled pl_nip processing
7999: close c_sched_for_pl_nip;
8000: else
7998: -- do scheduled pl_nip processing
7999: close c_sched_for_pl_nip;
8000: else
8001: if g_debug then
8002: hr_utility.set_location(l_proc , 910);
8003: end if;
8004: -- do life event pl_nip processing
8005: -- also used for unrestricted pl_nip
8006: close c_life_for_pl_nip;
8033: -- must be done
8034: --
8035: if l_done=FALSE then
8036: if g_debug then
8037: hr_utility.set_location(l_proc , 530);
8038: end if;
8039:
8040: -- Bug No 3965571 : All ids are replaced by their names in the error message's tokens.
8041:
8041:
8042: if p_which_dates_cd in ('R','B') and
8043: l_rt_strt_dt_cd is null then
8044: if g_debug then
8045: hr_utility.set_location('BEN_91455_RT_STRT_DT_NOT_FOUND' , 540);
8046: end if;
8047: fnd_message.set_name('BEN','BEN_91455_RT_STRT_DT_NOT_FOUND');
8048: fnd_message.set_token('PLAN_ID',l_pln_name);
8049: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8052: fnd_message.raise_error;
8053: elsif p_which_dates_cd in ('R','B') and
8054: l_rt_end_dt_cd is null then
8055: if g_debug then
8056: hr_utility.set_location('BEN_91703_NOT_DET_RATE_END_DT' , 550);
8057: end if;
8058: fnd_message.set_name('BEN','BEN_91703_NOT_DET_RATE_END_DT');
8059: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8060: fnd_message.set_token('PGM_ID',l_pgm_name);
8063: fnd_message.raise_error;
8064: elsif p_which_dates_cd in ('C','B') and
8065: l_enrt_cvg_strt_dt_cd is null then
8066: if g_debug then
8067: hr_utility.set_location('BEN_91453_CVG_STRT_DT_NOT_FOUN' , 560);
8068: end if;
8069: fnd_message.set_name('BEN','BEN_91453_CVG_STRT_DT_NOT_FOUN');
8070: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8071: fnd_message.set_token('PGM_ID',l_pgm_name);
8074: fnd_message.raise_error;
8075: elsif p_which_dates_cd in ('C','B') and
8076: l_enrt_cvg_end_dt_cd is null then
8077: if g_debug then
8078: hr_utility.set_location(l_proc , 570);
8079: end if;
8080: fnd_message.set_name('BEN','BEN_91702_NOT_DET_CVG_END_DT');
8081: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8082: fnd_message.set_token('PGM_ID',l_pgm_name);
8091: if p_compute_dates_flag='Y' then
8092: --
8093: if (l_enrt_cvg_strt_dt_cd is not NULL) then
8094: if g_debug then
8095: hr_utility.set_location(' ECSDCD DETDT_MN '||l_proc , 630);
8096: end if;
8097: main
8098: (p_cache_mode => p_cache_mode
8099: ,p_date_cd => l_enrt_cvg_strt_dt_cd
8110: ,p_oipl_id => l_oipl_id
8111: ,p_acty_base_rt_id => p_acty_base_rt_id
8112: ,p_start_date => p_start_date);
8113: if g_debug then
8114: hr_utility.set_location(' DN ECSDCD DETDT_MN '||l_proc , 630);
8115: end if;
8116: --
8117: end if;
8118: --
8125: 'LWEM','LWODBED','WAED','WALDCM' ,
8126: 'WALDCPP','WALDCPPY','WALDLPPEPPY',
8127: 'WEM','WODBED' )
8128: then
8129: hr_utility.set_location('pasing start date '||l_enrt_cvg_strt_dt , 199);
8130: l_pass_cvg_strt_dt := l_enrt_cvg_strt_dt ;
8131: else
8132: l_pass_cvg_strt_dt := p_start_date ;
8133: end if;
8134: */
8135: l_pass_cvg_strt_dt := p_start_date ;
8136: --
8137: if (l_enrt_cvg_end_dt_cd is not NULL) then
8138: hr_utility.set_location(' ECEDCD DETDT_MN '||l_proc , 610);
8139: main
8140: (p_cache_mode => p_cache_mode
8141: ,p_date_cd => l_enrt_cvg_end_dt_cd
8142: ,p_formula_id => l_enrt_cvg_end_dt_rl
8152: ,p_acty_base_rt_id => p_acty_base_rt_id
8153: ,p_start_date => l_pass_cvg_strt_dt
8154: ,p_returned_date => l_enrt_cvg_end_dt
8155: );
8156: -- hr_utility.set_location(' Dn ECEDCD DETDT_MN '||l_proc , 610);
8157: --
8158: end if;
8159: --
8160:
8160:
8161: -- rate start date calcualted before the end date
8162: if l_rt_strt_dt_cd is not NULL then
8163: if g_debug then
8164: hr_utility.set_location(' SDC DETDT_MN '||l_proc , 670);
8165: end if;
8166: --
8167: -- Passing Enrollment coverage
8168: --
8193: ,p_acty_base_rt_id => p_acty_base_rt_id
8194: ,p_start_date => l_pass_cvg_strt_dt );
8195:
8196: if g_debug then
8197: hr_utility.set_location(' rate start date = '||l_rt_strt_dt , 1687);
8198: end if;
8199: if g_debug then
8200: hr_utility.set_location(' Dn SDC DETDT_MN '||l_proc , 670);
8201: end if;
8196: if g_debug then
8197: hr_utility.set_location(' rate start date = '||l_rt_strt_dt , 1687);
8198: end if;
8199: if g_debug then
8200: hr_utility.set_location(' Dn SDC DETDT_MN '||l_proc , 670);
8201: end if;
8202: --- assign the fonm rat from the rate so the end date rule can use the fonm value
8203: if ben_manage_life_events.fonm = 'Y' or l_fonm_cvg_strt_dt is not null then
8204: if ben_manage_life_events.g_fonm_rt_strt_dt is null or
8204: if ben_manage_life_events.g_fonm_rt_strt_dt is null or
8205: ben_manage_life_events.g_fonm_rt_strt_dt <> l_rt_strt_dt then
8206: ben_manage_life_events.g_fonm_rt_strt_dt := l_rt_strt_dt ;
8207: end if ;
8208: hr_utility.set_location('FONM gr date ' || ben_manage_life_events.g_fonm_rt_strt_dt , 60);
8209: end if ;
8210:
8211: end if;
8212:
8230: l_pass_cvg_end_dt := p_end_date;
8231: end if;
8232: --
8233: if g_debug then
8234: hr_utility.set_location(' EDC DETDT_MN '||l_proc , 650);
8235: end if;
8236: main
8237: (p_cache_mode => p_cache_mode
8238: ,p_date_cd => l_rt_end_dt_cd
8249: ,p_oipl_id => l_oipl_id
8250: ,p_acty_base_rt_id => p_acty_base_rt_id
8251: ,p_start_date => l_pass_cvg_end_dt);
8252: if g_debug then
8253: hr_utility.set_location(' Dn EDC DETDT_MN '||l_proc , 650);
8254: end if;
8255:
8256: end if;
8257: --
8271: --
8272: if l_rt_end_dt is null
8273: then
8274: if g_debug then
8275: hr_utility.set_location('Special condition for LODBEWM ODBEWM ', 199);
8276: end if;
8277: l_rt_end_dt := l_rt_strt_dt - 1 ;
8278: if g_debug then
8279: hr_utility.set_location('l_rt_end_dt '||l_rt_end_dt , 199) ;
8275: hr_utility.set_location('Special condition for LODBEWM ODBEWM ', 199);
8276: end if;
8277: l_rt_end_dt := l_rt_strt_dt - 1 ;
8278: if g_debug then
8279: hr_utility.set_location('l_rt_end_dt '||l_rt_end_dt , 199) ;
8280: end if;
8281: end if;
8282: --
8283: end if;
8291: then
8292: --
8293: l_rt_strt_dt := l_enrt_cvg_strt_dt ;
8294: if g_debug then
8295: hr_utility.set_location('Case 1',15);
8296: end if;
8297: --
8298: elsif to_char(l_enrt_cvg_strt_dt, 'DD') > 15 then
8299: --
8298: elsif to_char(l_enrt_cvg_strt_dt, 'DD') > 15 then
8299: --
8300: l_rt_strt_dt := round(l_enrt_cvg_strt_dt,'Month') ;
8301: if g_debug then
8302: hr_utility.set_location('Case 2',15);
8303: end if;
8304: --
8305: else
8306: --
8305: else
8306: --
8307: l_rt_strt_dt := round(l_enrt_cvg_strt_dt,'Month') + 15 ;
8308: if g_debug then
8309: hr_utility.set_location('Case 3'||l_enrt_cvg_strt_dt,15);
8310: end if;
8311:
8312: --
8313: end if;
8322: if l_rt_strt_dt is not null and
8323: (not do_date_at_enrollment(l_rt_strt_dt_cd)) and
8324: p_date_mandatory_flag='N' then
8325: if g_debug then
8326: hr_utility.set_location(l_proc , 700);
8327: end if;
8328: l_rt_strt_dt_cd:=null;
8329: l_rt_strt_dt_rl:=null;
8330: end if;
8332: if l_rt_end_dt is not null and
8333: (not do_date_at_enrollment(l_rt_end_dt_cd)) and
8334: p_date_mandatory_flag='N' then
8335: if g_debug then
8336: hr_utility.set_location(l_proc , 720);
8337: end if;
8338: l_rt_end_dt_cd:=null;
8339: l_rt_end_dt_rl:=null;
8340: end if;
8342: if l_enrt_cvg_strt_dt is not null and
8343: (not do_date_at_enrollment(l_enrt_cvg_strt_dt_cd)) and
8344: p_date_mandatory_flag='N' then
8345: if g_debug then
8346: hr_utility.set_location(l_proc , 740);
8347: end if;
8348: l_enrt_cvg_strt_dt_cd:=null;
8349: l_enrt_cvg_strt_dt_rl:=null;
8350: end if;
8352: if l_enrt_cvg_end_dt is not null and
8353: (not do_date_at_enrollment(l_enrt_cvg_end_dt_cd)) and
8354: p_date_mandatory_flag ='N' then
8355: if g_debug then
8356: hr_utility.set_location(l_proc , 760);
8357: end if;
8358: l_enrt_cvg_end_dt_cd:=null;
8359: l_enrt_cvg_end_dt_rl:=null;
8360: end if;
8366:
8367: if p_date_mandatory_flag='Y' and
8368: p_compute_dates_flag='Y' then
8369: if g_debug then
8370: hr_utility.set_location(l_proc , 780);
8371: end if;
8372:
8373: -- Bug No 3965571 : All ids are replaced by their names in the error message's tokens.
8374:
8374:
8375: if p_which_dates_cd in ('R','B') and
8376: l_rt_strt_dt is null then
8377: if g_debug then
8378: hr_utility.set_location('BEN_91455_RT_STRT_DT_NOT_FOUND',37);
8379: end if;
8380: fnd_message.set_name('BEN','BEN_91455_RT_STRT_DT_NOT_FOUND');
8381: fnd_message.set_token('PLAN_ID',l_pln_name);
8382: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8385: fnd_message.raise_error;
8386: elsif p_which_dates_cd in ('R','B') and
8387: l_rt_end_dt is null then
8388: if g_debug then
8389: hr_utility.set_location('BEN_91703_NOT_DET_RATE_END_DT',37);
8390: end if;
8391: fnd_message.set_name('BEN','BEN_91703_NOT_DET_RATE_END_DT');
8392: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8393: fnd_message.set_token('PGM_ID',l_pgm_name);
8396: fnd_message.raise_error;
8397: elsif p_which_dates_cd in ('C','B') and
8398: l_enrt_cvg_strt_dt is null then
8399: if g_debug then
8400: hr_utility.set_location('BEN_91453_CVG_STRT_DT_NOT_FOUN',37);
8401: end if;
8402: fnd_message.set_name('BEN','BEN_91453_CVG_STRT_DT_NOT_FOUN');
8403: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8404: fnd_message.set_token('PGM_ID',l_pgm_name);
8407: fnd_message.raise_error;
8408: elsif p_which_dates_cd in ('C','B') and
8409: l_enrt_cvg_end_dt is null then
8410: if g_debug then
8411: hr_utility.set_location('BEN_91702_NOT_DET_CVG_END_DT',37);
8412: end if;
8413: fnd_message.set_name('BEN','BEN_91702_NOT_DET_CVG_END_DT');
8414: fnd_message.set_token('PERSON_ID',to_char(l_person_id));
8415: fnd_message.set_token('PGM_ID',l_pgm_name);
8434: p_enrt_cvg_end_dt:=l_enrt_cvg_end_dt;
8435: p_enrt_cvg_end_dt_cd:=l_enrt_cvg_end_dt_cd;
8436: p_enrt_cvg_end_dt_rl:=l_enrt_cvg_end_dt_rl;
8437: --
8438: -- hr_utility.set_location(' Leaving:'||l_proc, 930);
8439: --
8440:
8441: exception -- nocopy changes
8442: --