[Home] [Help]
84:
85: l_count number := 0;
86: l_proc varchar2(100):= g_package||'write';
87: begin
88: hr_utility.set_location('Entering: '||l_proc,10);
89:
90: l_count := p_score_tab.count +1;
91: p_score_tab(l_count).eligy_prfl_id := p_eligy_prfl_id;
92: p_score_tab(l_count).crit_tab_short_name := p_tab_short_name;
93: p_score_tab(l_count).crit_tab_pk_id := p_pk_id;
94: p_score_tab(l_count).computed_score := p_computed_score;
95: p_score_tab(l_count).benefit_action_id := benutils.g_benefit_action_id;
96:
97: hr_utility.set_location('Leaving: '||l_proc,10);
98:
99: end write;
100:
101: procedure write(p_profile_score_tab in out nocopy scoreTab,
102: p_crit_score_tab in scoreTab) is
103: l_proc varchar2(100):= g_package||'write';
104: begin
105:
106: hr_utility.set_location('Entering: '||l_proc,10);
107: if p_crit_score_tab.count > 0
108: then
109: for i in 1..p_crit_score_tab.count
110: loop
110: loop
111: p_profile_score_tab(p_profile_score_tab.count+1) := p_crit_score_tab(i);
112: end loop;
113: end if;
114: hr_utility.set_location('Leaving: '||l_proc,10);
115:
116: end write;
117:
118: function is_ok (p_val number
240: -- records.
241: if p_plip_id is not null then
242: open c_hrs_wkd_plip;
243: fetch c_hrs_wkd_plip into l_rt_hrs_wkd_val;
244: hr_utility.set_location ('oiplip '||to_char(l_rt_hrs_wkd_val),01);
245: close c_hrs_wkd_plip;
246: end if;
247:
248: -- If there is no oiplip, check for oipl
248: -- If there is no oiplip, check for oipl
249: if l_rt_hrs_wkd_val is null then
250: open c_hrs_wkd_opt;
251: fetch c_hrs_wkd_opt into l_rt_hrs_wkd_val;
252: hr_utility.set_location ('oipl '||to_char(l_rt_hrs_wkd_val),01);
253: close c_hrs_wkd_opt;
254: end if;
255: else
256: -- just look for pl elig per record.
255: else
256: -- just look for pl elig per record.
257: open c_hrs_wkd;
258: fetch c_hrs_wkd into l_rt_hrs_wkd_val;
259: hr_utility.set_location ('pl '||to_char(l_rt_hrs_wkd_val),01);
260: close c_hrs_wkd;
261: end if;
262:
263: return l_rt_hrs_wkd_val;
339: -- records.
340: if p_plip_id is not null then
341: open c_pct_ft_plip;
342: fetch c_pct_ft_plip into l_per_pct_ft_val;
343: hr_utility.set_location ('oiplip '||to_char(l_per_pct_ft_val),01);
344: close c_pct_ft_plip;
345: end if;
346:
347: -- If there is no oiplip, check for oipl
347: -- If there is no oiplip, check for oipl
348: if l_per_pct_ft_val is null then
349: open c_pct_ft_opt;
350: fetch c_pct_ft_opt into l_per_pct_ft_val;
351: hr_utility.set_location ('oipl '||to_char(l_per_pct_ft_val),01);
352: close c_pct_ft_opt;
353: end if;
354: else
355: -- just look for pl elig per record.
354: else
355: -- just look for pl elig per record.
356: open c_pct_ft;
357: fetch c_pct_ft into l_per_pct_ft_val;
358: hr_utility.set_location ('pl '||to_char(l_per_pct_ft_val),01);
359: close c_pct_ft;
360: end if;
361: --
362: return l_per_pct_ft_val;
404: l_score_tab scoreTab;
405: --
406: begin
407: --
408: hr_utility.set_location('Entering: '||l_proc,10);
409: --
410: -- Getting eligibility profile length of service by eligibility profile
411: --
412: ben_elp_cache.elpels_getcacdets
446: l_prtn_ovridn_flag := l_epo_row.prtn_ovridn_flag;
447: l_prtn_ovridn_thru_dt := l_epo_row.prtn_ovridn_thru_dt;
448: l_per_los := l_epo_row.rt_los_val;
449: else
450: hr_utility.set_location('Plan not in Program',10);
451: l_prtn_ovridn_flag := p_currepe_row.prtn_ovridn_flag;
452: l_prtn_ovridn_thru_dt := p_currepe_row.prtn_ovridn_thru_dt;
453: l_per_los := p_currepe_row.rt_los_val;
454: end if;
485: ,l_inst_dets(l_insttorrw_num).mn_los_num
486: ,l_inst_dets(l_insttorrw_num).no_mx_los_num_apls_flag
487: ,l_inst_dets(l_insttorrw_num).no_mn_los_num_apls_flag
488: );
489: hr_utility.set_location('l_per_los '||l_per_los,20);
490: hr_utility.set_location('ACE Score = ' || l_inst_dets(l_insttorrw_num).criteria_score, 20);
491: hr_utility.set_location('ACE Weight = ' || l_inst_dets(l_insttorrw_num).criteria_weight, 20);
492: hr_utility.set_location('ACE Value = ' || to_char(( l_per_los * nvl(l_inst_dets(l_insttorrw_num).criteria_weight, 0) ) + nvl(l_inst_dets(l_insttorrw_num).criteria_score, 0)), 20);
493: --
486: ,l_inst_dets(l_insttorrw_num).no_mx_los_num_apls_flag
487: ,l_inst_dets(l_insttorrw_num).no_mn_los_num_apls_flag
488: );
489: hr_utility.set_location('l_per_los '||l_per_los,20);
490: hr_utility.set_location('ACE Score = ' || l_inst_dets(l_insttorrw_num).criteria_score, 20);
491: hr_utility.set_location('ACE Weight = ' || l_inst_dets(l_insttorrw_num).criteria_weight, 20);
492: hr_utility.set_location('ACE Value = ' || to_char(( l_per_los * nvl(l_inst_dets(l_insttorrw_num).criteria_weight, 0) ) + nvl(l_inst_dets(l_insttorrw_num).criteria_score, 0)), 20);
493: --
494: if l_per_los is null then
487: ,l_inst_dets(l_insttorrw_num).no_mn_los_num_apls_flag
488: );
489: hr_utility.set_location('l_per_los '||l_per_los,20);
490: hr_utility.set_location('ACE Score = ' || l_inst_dets(l_insttorrw_num).criteria_score, 20);
491: hr_utility.set_location('ACE Weight = ' || l_inst_dets(l_insttorrw_num).criteria_weight, 20);
492: hr_utility.set_location('ACE Value = ' || to_char(( l_per_los * nvl(l_inst_dets(l_insttorrw_num).criteria_weight, 0) ) + nvl(l_inst_dets(l_insttorrw_num).criteria_score, 0)), 20);
493: --
494: if l_per_los is null then
495: --
488: );
489: hr_utility.set_location('l_per_los '||l_per_los,20);
490: hr_utility.set_location('ACE Score = ' || l_inst_dets(l_insttorrw_num).criteria_score, 20);
491: hr_utility.set_location('ACE Weight = ' || l_inst_dets(l_insttorrw_num).criteria_weight, 20);
492: hr_utility.set_location('ACE Value = ' || to_char(( l_per_los * nvl(l_inst_dets(l_insttorrw_num).criteria_weight, 0) ) + nvl(l_inst_dets(l_insttorrw_num).criteria_score, 0)), 20);
493: --
494: if l_per_los is null then
495: --
496: l_ok := false;
552: --
553: l_score_tab.delete;
554: g_inelg_rsn_cd := 'LOS';
555: fnd_message.set_name('BEN','BEN_91669_LOS_PRFL_FAIL');
556: hr_utility.set_location('Criteria Failed: '||l_proc,20);
557: raise g_criteria_failed;
558: --
559: end if;
560: end if;
560: end if;
561: --
562: if p_score_compute_mode
563: then
564: hr_utility.set_location('count '||l_score_tab.count,20);
565: write(p_profile_score_tab,l_score_tab);
566: end if;
567: hr_utility.set_location('Leaving: '||l_proc,20);
568: --
563: then
564: hr_utility.set_location('count '||l_score_tab.count,20);
565: write(p_profile_score_tab,l_score_tab);
566: end if;
567: hr_utility.set_location('Leaving: '||l_proc,20);
568: --
569: end check_los_elig;
570: --
571: -- ----------------------------------------------------
611: l_score_tab scoreTab;
612: --
613: begin
614: --
615: hr_utility.set_location('Entering :'||l_proc, 10);
616: --
617: -- Getting eligibility profile age details by eligibility profile
618: --
619: ben_elp_cache.elpeap_getcacdets
622: ,p_eligy_prfl_id => p_eligy_prfl_id
623: ,p_inst_set => l_inst_dets
624: ,p_inst_count => l_inst_count);
625: --
626: hr_utility.set_location('Age :'||l_per_age, 10);
627: if l_inst_count > 0 then
628:
629: if p_eval_typ <> 'E' then
630: --
654: l_prtn_ovridn_flag := l_epo_row.prtn_ovridn_flag;
655: l_prtn_ovridn_thru_dt := l_epo_row.prtn_ovridn_thru_dt;
656: l_per_age := l_epo_row.rt_age_val;
657: else
658: hr_utility.set_location('Plan not in Program',10);
659: l_prtn_ovridn_flag := p_currepe_row.prtn_ovridn_flag;
660: l_prtn_ovridn_thru_dt := p_currepe_row.prtn_ovridn_thru_dt;
661: l_per_age := p_currepe_row.rt_age_val;
662: end if;
756: not l_ok then
757: --
758: g_inelg_rsn_cd := 'AGE';
759: fnd_message.set_name('BEN','BEN_91670_AGE_PRFL_FAIL');
760: hr_utility.set_location('Criteria Failed: '||l_proc,20);
761: raise g_criteria_failed;
762: --
763: end if;
764: end if;
764: end if;
765: --
766: if p_score_compute_mode
767: then
768: hr_utility.set_location('count '||l_score_tab.count,20);
769: write(p_profile_score_tab,l_score_tab);
770: end if;
771: hr_utility.set_location('Leaving :'||l_proc, 20);
772: --
767: then
768: hr_utility.set_location('count '||l_score_tab.count,20);
769: write(p_profile_score_tab,l_score_tab);
770: end if;
771: hr_utility.set_location('Leaving :'||l_proc, 20);
772: --
773: end check_age_elig;
774: --
775: -- --------------------------------------------------------------------
823: l_score_tab scoreTab;
824: --
825: begin
826: --
827: hr_utility.set_location('Entering :'||l_proc,10);
828: --
829: -- Getting eligibility profile age/los combination by eligibility profile
830: --
831: ben_elp_cache.elpecp_getcacdets
865: l_prtn_ovridn_flag := l_epo_row.prtn_ovridn_flag;
866: l_prtn_ovridn_thru_dt := l_epo_row.prtn_ovridn_thru_dt;
867: l_per_cmbn_age_los := l_epo_row.rt_cmbn_age_n_los_val;
868: else
869: hr_utility.set_location('Plan not in Program',10);
870: l_prtn_ovridn_flag := p_currepe_row.prtn_ovridn_flag;
871: l_prtn_ovridn_thru_dt := p_currepe_row.prtn_ovridn_thru_dt;
872: l_per_cmbn_age_los := p_currepe_row.rt_cmbn_age_n_los_val;
873: end if;
927: ,l_inst_dets(l_insttorrw_num).cmbnd_max_val
928: ,l_inst_dets(l_insttorrw_num).cmbnd_min_val
929: );
930: --
931: hr_utility.set_location('TOTAL ='||l_per_cmbn_age_los,10);
932: hr_utility.set_location('MIN ='||l_inst_dets(l_insttorrw_num).cmbnd_min_val,10);
933: hr_utility.set_location('MAX ='||l_inst_dets(l_insttorrw_num).cmbnd_max_val,10);
934: if l_per_cmbn_age_los is null then
935: --
928: ,l_inst_dets(l_insttorrw_num).cmbnd_min_val
929: );
930: --
931: hr_utility.set_location('TOTAL ='||l_per_cmbn_age_los,10);
932: hr_utility.set_location('MIN ='||l_inst_dets(l_insttorrw_num).cmbnd_min_val,10);
933: hr_utility.set_location('MAX ='||l_inst_dets(l_insttorrw_num).cmbnd_max_val,10);
934: if l_per_cmbn_age_los is null then
935: --
936: l_ok := false;
929: );
930: --
931: hr_utility.set_location('TOTAL ='||l_per_cmbn_age_los,10);
932: hr_utility.set_location('MIN ='||l_inst_dets(l_insttorrw_num).cmbnd_min_val,10);
933: hr_utility.set_location('MAX ='||l_inst_dets(l_insttorrw_num).cmbnd_max_val,10);
934: if l_per_cmbn_age_los is null then
935: --
936: l_ok := false;
937: --
938: end if;
939: --
940: if not l_ok then
941: --
942: hr_utility.set_location('Failed cmbn',10);
943: --
944: elsif l_ok then
945: --
946: -- Getting length of service factor
967: end if;
968: --
969: if not l_ok then
970: --
971: hr_utility.set_location('Failed los',10);
972: --
973: elsif l_ok then
974: --
975: -- Check if age passes
1051: then
1052: if l_rows_found and
1053: not l_ok then
1054: --
1055: hr_utility.set_location('Failed Elig ',10);
1056: g_inelg_rsn_cd := 'AGL';
1057: fnd_message.set_name('BEN','BEN_91671_AGE_LOS_PRFL_FAIL');
1058: raise g_criteria_failed;
1059: --
1061: end if;
1062: --
1063: if p_score_compute_mode
1064: then
1065: hr_utility.set_location('count '||l_score_tab.count,20);
1066: write(p_profile_score_tab,l_score_tab);
1067: end if;
1068: hr_utility.set_location('Leaving :'||l_proc,20);
1069: --
1064: then
1065: hr_utility.set_location('count '||l_score_tab.count,20);
1066: write(p_profile_score_tab,l_score_tab);
1067: end if;
1068: hr_utility.set_location('Leaving :'||l_proc,20);
1069: --
1070: end check_age_los_elig;
1071: --
1072: -- -----------------------------------------------------------
1105: l_score_tab scoreTab;
1106: --
1107: begin
1108: --
1109: hr_utility.set_location('Entering: '||l_proc, 10);
1110: --
1111: -- Getting eligibility profile compensation level by eligibility profile
1112: --
1113: ben_elp_cache.elpecl_getcacdets
1209: not l_ok then
1210: --
1211: g_inelg_rsn_cd := 'CMP';
1212: fnd_message.set_name('BEN','BEN_91672_COMP_LVL_PRFL_FAIL');
1213: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1214: raise g_criteria_failed;
1215: --
1216: end if;
1217: end if;
1215: --
1216: end if;
1217: end if;
1218: --
1219: hr_utility.set_location('Leaving :'||l_proc,20);
1220: --
1221: if p_score_compute_mode
1222: then
1223: hr_utility.set_location('count '||l_score_tab.count,20);
1219: hr_utility.set_location('Leaving :'||l_proc,20);
1220: --
1221: if p_score_compute_mode
1222: then
1223: hr_utility.set_location('count '||l_score_tab.count,20);
1224: write(p_profile_score_tab,l_score_tab);
1225: end if;
1226: end check_comp_level_rl_elig;
1227: --
1253: l_score_tab scoreTab;
1254: --
1255: begin
1256: --
1257: hr_utility.set_location('Entering: '||l_proc, 10);
1258: --
1259: -- Getting eligibility profile compensation level by eligibility profile
1260: --
1261: ben_elp_cache.elpecl_getcacdets
1359: not l_ok then
1360: --
1361: g_inelg_rsn_cd := 'CMP';
1362: fnd_message.set_name('BEN','BEN_91672_COMP_LVL_PRFL_FAIL');
1363: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1364: raise g_criteria_failed;
1365: --
1366: end if;
1367: end if;
1365: --
1366: end if;
1367: end if;
1368: --
1369: hr_utility.set_location('Leaving :'||l_proc,20);
1370: --
1371: if p_score_compute_mode
1372: then
1373: hr_utility.set_location('count '||l_score_tab.count,20);
1369: hr_utility.set_location('Leaving :'||l_proc,20);
1370: --
1371: if p_score_compute_mode
1372: then
1373: hr_utility.set_location('count '||l_score_tab.count,20);
1374: write(p_profile_score_tab,l_score_tab);
1375: end if;
1376: end check_comp_level_elig;
1377: --
1404: l_score_tab scoreTab;
1405: --
1406: begin
1407: --
1408: hr_utility.set_location('Entering :'||l_proc,10);
1409: --
1410: -- Check if we need to test this business rule at all
1411: --
1412: hr_utility.set_location('Elig Flag :'||p_elig_flag,10);
1408: hr_utility.set_location('Entering :'||l_proc,10);
1409: --
1410: -- Check if we need to test this business rule at all
1411: --
1412: hr_utility.set_location('Elig Flag :'||p_elig_flag,10);
1413: hr_utility.set_location('Once r Cntug Cd :'||p_once_r_cntug_cd,10);
1414: --
1415: if p_elig_flag = 'Y' and
1416: nvl(p_once_r_cntug_cd,'-1') = 'ONCE' then
1409: --
1410: -- Check if we need to test this business rule at all
1411: --
1412: hr_utility.set_location('Elig Flag :'||p_elig_flag,10);
1413: hr_utility.set_location('Once r Cntug Cd :'||p_once_r_cntug_cd,10);
1414: --
1415: if p_elig_flag = 'Y' and
1416: nvl(p_once_r_cntug_cd,'-1') = 'ONCE' then
1417: --
1428: ,p_eligy_prfl_id => p_eligy_prfl_id
1429: ,p_inst_set => l_inst_dets
1430: ,p_inst_count => l_inst_count);
1431: --
1432: hr_utility.set_location('Inst Count :'||l_inst_count,10);
1433: --
1434: if l_inst_count > 0 then
1435: --
1436:
1524: not l_ok then
1525: --
1526: g_inelg_rsn_cd := 'HRS';
1527: fnd_message.set_name('BEN','BEN_91673_HRS_WKD_PRFL_FAIL');
1528: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1529: raise g_criteria_failed;
1530: --
1531: end if;
1532: end if;
1530: --
1531: end if;
1532: end if;
1533: --
1534: hr_utility.set_location('Leaving :'||l_proc,20);
1535: --
1536: if p_score_compute_mode
1537: then
1538: hr_utility.set_location('count '||l_score_tab.count,20);
1534: hr_utility.set_location('Leaving :'||l_proc,20);
1535: --
1536: if p_score_compute_mode
1537: then
1538: hr_utility.set_location('count '||l_score_tab.count,20);
1539: write(p_profile_score_tab,l_score_tab);
1540: end if;
1541: end check_hrs_wkd_ben_bal;
1542: --
1568: l_score_tab scoreTab;
1569: --
1570: begin
1571: --
1572: hr_utility.set_location('Entering :'||l_proc,10);
1573: --
1574: if p_elig_flag = 'Y' and
1575: nvl(p_once_r_cntug_cd,'-1') = 'ONCE' then
1576: --
1679: not l_ok then
1680: --
1681: g_inelg_rsn_cd := 'HRS';
1682: fnd_message.set_name('BEN','BEN_91673_HRS_WKD_PRFL_FAIL');
1683: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1684: raise g_criteria_failed;
1685: --
1686: end if;
1687: end if;
1687: end if;
1688: --
1689: if p_score_compute_mode
1690: then
1691: hr_utility.set_location('count '||l_score_tab.count,20);
1692: write(p_profile_score_tab,l_score_tab);
1693: end if;
1694: hr_utility.set_location('Leaving :'||l_proc,20);
1695: --
1690: then
1691: hr_utility.set_location('count '||l_score_tab.count,20);
1692: write(p_profile_score_tab,l_score_tab);
1693: end if;
1694: hr_utility.set_location('Leaving :'||l_proc,20);
1695: --
1696: end check_hrs_wkd_rl_balance;
1697: --
1698: -- --------------------------------------------------------------------
1723: l_score_tab scoreTab;
1724: --
1725: begin
1726: --
1727: hr_utility.set_location('Entering :'||l_proc,10);
1728: --
1729: if p_elig_flag = 'Y' and
1730: nvl(p_once_r_cntug_cd,'-1') = 'ONCE' then
1731: --
1833: not l_ok then
1834: --
1835: g_inelg_rsn_cd := 'HRS';
1836: fnd_message.set_name('BEN','BEN_91673_HRS_WKD_PRFL_FAIL');
1837: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1838: raise g_criteria_failed;
1839: --
1840: end if;
1841: end if;
1841: end if;
1842: --
1843: if p_score_compute_mode
1844: then
1845: hr_utility.set_location('count '||l_score_tab.count,20);
1846: write(p_profile_score_tab,l_score_tab);
1847: end if;
1848: hr_utility.set_location('Leaving :'||l_proc,20);
1849: --
1844: then
1845: hr_utility.set_location('count '||l_score_tab.count,20);
1846: write(p_profile_score_tab,l_score_tab);
1847: end if;
1848: hr_utility.set_location('Leaving :'||l_proc,20);
1849: --
1850: end check_hrs_wkd_balance;
1851: --
1852: -- -----------------------------------------------------------------
1876: l_score_tab scoreTab;
1877: --
1878: begin
1879: --
1880: hr_utility.set_location('Entering :'||l_proc,10);
1881: --
1882: -- Getting eligibility profile full time by eligibility profile
1883: --
1884: ben_elp_cache.elpepf_getcacdets
1974: not l_ok then
1975: --
1976: g_inelg_rsn_cd := 'PFT';
1977: fnd_message.set_name('BEN','BEN_91674_PCT_FT_PRFL_FAIL');
1978: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1979: raise g_criteria_failed;
1980: --
1981: end if;
1982: end if;
1982: end if;
1983: --
1984: if p_score_compute_mode
1985: then
1986: hr_utility.set_location('count '||l_score_tab.count,20);
1987: write(p_profile_score_tab,l_score_tab);
1988: end if;
1989: hr_utility.set_location('Leaving :'||l_proc,20);
1990: --
1985: then
1986: hr_utility.set_location('count '||l_score_tab.count,20);
1987: write(p_profile_score_tab,l_score_tab);
1988: end if;
1989: hr_utility.set_location('Leaving :'||l_proc,20);
1990: --
1991: end check_pct_fltm_elig;
1992: --
1993: -- ------------------------------------------------------
2011: l_score_tab scoreTab;
2012: --
2013: begin
2014: --
2015: hr_utility.set_location('Entering : '||l_proc,10);
2016: --
2017: -- Getting eligibility profile grade by eligibility profile
2018: --
2019: ben_elp_cache.elpegr_getcacdets
2027: if l_inst_count > 0 then
2028: --
2029: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
2030: --
2031: hr_utility.set_location('GRADE'||p_grade_id,10);
2032: hr_utility.set_location('COMPARE GRADE'||l_inst_dets(l_insttorrw_num).grade_id,10);
2033: l_ok := nvl((nvl(p_grade_id,-1) = l_inst_dets(l_insttorrw_num).grade_id),FALSE);
2034: --if l_ok is null then
2035: -- l_ok:=false;
2028: --
2029: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
2030: --
2031: hr_utility.set_location('GRADE'||p_grade_id,10);
2032: hr_utility.set_location('COMPARE GRADE'||l_inst_dets(l_insttorrw_num).grade_id,10);
2033: l_ok := nvl((nvl(p_grade_id,-1) = l_inst_dets(l_insttorrw_num).grade_id),FALSE);
2034: --if l_ok is null then
2035: -- l_ok:=false;
2036: --end if;
2087: not l_ok then
2088: --
2089: g_inelg_rsn_cd := 'GRD';
2090: fnd_message.set_name('BEN','BEN_91675_GRD_PRFL_FAIL');
2091: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2092: raise g_criteria_failed;
2093: --
2094: end if;
2095: end if;
2095: end if;
2096: --
2097: if p_score_compute_mode
2098: then
2099: hr_utility.set_location('count '||l_score_tab.count,20);
2100: write(p_profile_score_tab,l_score_tab);
2101: end if;
2102: hr_utility.set_location('Leaving: '||l_proc,20);
2103: --
2098: then
2099: hr_utility.set_location('count '||l_score_tab.count,20);
2100: write(p_profile_score_tab,l_score_tab);
2101: end if;
2102: hr_utility.set_location('Leaving: '||l_proc,20);
2103: --
2104: end check_grade_elig;
2105: --
2106: -- ------------------------------------------------------
2124: l_score_tab scoreTab;
2125: --
2126: begin
2127: --
2128: hr_utility.set_location('Entering : '||l_proc,10);
2129: --
2130: -- Getting eligibility profile sex by eligibility profile
2131: --
2132: ben_elp_cache.elpegn_getcacdets
2140: if l_inst_count > 0 then
2141: --
2142: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
2143: --
2144: hr_utility.set_location('SEX'||p_sex,10);
2145: l_ok := nvl((nvl(p_sex,'zz') = l_inst_dets(l_insttorrw_num).sex),FALSE);
2146: --
2147: if l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2148: if p_score_compute_mode then
2195: not l_ok then
2196: --
2197: g_inelg_rsn_cd := 'GND';
2198: fnd_message.set_name('BEN','BEN_91675_GND_PRFL_FAIL');
2199: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2200: raise g_criteria_failed;
2201: --
2202: end if;
2203: end if;
2203: end if;
2204: --
2205: if p_score_compute_mode
2206: then
2207: hr_utility.set_location('count '||l_score_tab.count,20);
2208: write(p_profile_score_tab,l_score_tab);
2209: end if;
2210: hr_utility.set_location('Leaving: '||l_proc,20);
2211: --
2206: then
2207: hr_utility.set_location('count '||l_score_tab.count,20);
2208: write(p_profile_score_tab,l_score_tab);
2209: end if;
2210: hr_utility.set_location('Leaving: '||l_proc,20);
2211: --
2212: end check_gender_elig;
2213: --
2214: -- ------------------------------------------------------
2256: l_score_tab scoreTab;
2257: --
2258: begin
2259: --
2260: hr_utility.set_location('Entering : '||l_proc,10);
2261: --
2262: -- Getting eligibility profile rule by eligibility profile
2263: --
2264:
2272: if l_inst_count > 0 then
2273: --find the profile value for formul and/Or # 2508757\
2274: l_ben_elgy_prfl_rl_Cond := fnd_profile.value('BEN_ELGY_PRFL_RL_COND');
2275: --
2276: hr_utility.set_location('assignment_id='||p_assignment_id,1963);
2277: hr_utility.set_location('l_ben_elgy_prfl_rl_Cond='||l_ben_elgy_prfl_rl_Cond,1963);
2278: --
2279: if p_oipl_id is not null then
2280: --
2273: --find the profile value for formul and/Or # 2508757\
2274: l_ben_elgy_prfl_rl_Cond := fnd_profile.value('BEN_ELGY_PRFL_RL_COND');
2275: --
2276: hr_utility.set_location('assignment_id='||p_assignment_id,1963);
2277: hr_utility.set_location('l_ben_elgy_prfl_rl_Cond='||l_ben_elgy_prfl_rl_Cond,1963);
2278: --
2279: if p_oipl_id is not null then
2280: --
2281: l_oipl_rec := ben_cobj_cache.g_oipl_currow;
2301: then
2302: --
2303: g_inelg_rsn_cd := 'ERL';
2304: fnd_message.set_name('BEN','BEN_92445_ERL_PRFL_FAIL');
2305: hr_utility.set_location('Criteria Failed: '||l_proc,19);
2306: raise g_criteria_failed;
2307: --
2308: end if;
2309: --
2372: end;
2373: --
2374: end loop;
2375: --if the condition is AND and fails go out 2508757
2376: hr_utility.set_location('l_eligible='||l_eligible,1963);
2377: if nvl(l_ben_elgy_prfl_rl_Cond,'A') = 'A' then
2378:
2379: if l_eligible <> 'Y' then
2380: --
2379: if l_eligible <> 'Y' then
2380: --
2381: g_inelg_rsn_cd := 'ERL';
2382: fnd_message.set_name('BEN','BEN_92445_ERL_PRFL_FAIL');
2383: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2384: raise g_criteria_failed;
2385: End if ;
2386: Else ---- if the condition is OR and pass then exit
2387: if l_eligible = 'Y' then
2406: --if the condition is OR and and exit the loop with 'N' throw error
2407: if l_crit_passed is null
2408: then
2409: if nvl(l_ben_elgy_prfl_rl_Cond,'A') = 'O' and l_eligible <> 'Y' then
2410: hr_utility.set_location('erroring on OR and N ',1963);
2411: g_inelg_rsn_cd := 'ERL';
2412: fnd_message.set_name('BEN','BEN_92445_ERL_PRFL_FAIL');
2413: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2414: raise g_criteria_failed;
2409: if nvl(l_ben_elgy_prfl_rl_Cond,'A') = 'O' and l_eligible <> 'Y' then
2410: hr_utility.set_location('erroring on OR and N ',1963);
2411: g_inelg_rsn_cd := 'ERL';
2412: fnd_message.set_name('BEN','BEN_92445_ERL_PRFL_FAIL');
2413: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2414: raise g_criteria_failed;
2415: end if ;
2416: end if ;
2417:
2418: end if;
2419: --
2420: if p_score_compute_mode --999
2421: then
2422: hr_utility.set_location('count '||l_score_tab.count,20);
2423: write(p_profile_score_tab,l_score_tab);
2424: end if;
2425: hr_utility.set_location('Leaving: '||l_proc,20);
2426: --
2421: then
2422: hr_utility.set_location('count '||l_score_tab.count,20);
2423: write(p_profile_score_tab,l_score_tab);
2424: end if;
2425: hr_utility.set_location('Leaving: '||l_proc,20);
2426: --
2427: end check_rule_elig;
2428: -- --------------------------------------------------
2429: -- This procedure determines elibility based on job.
2445: l_score_tab scoreTab;
2446: --
2447: begin
2448: --
2449: hr_utility.set_location('Entering : '||l_proc,10);
2450: --
2451: -- Getting eligibility profile job by eligibility profile
2452: --
2453: ben_elp_cache.elpejp_getcacdets
2518: not l_ok then
2519: --
2520: g_inelg_rsn_cd := 'JOB';
2521: fnd_message.set_name('BEN','BEN_91676_JOB_PRFL_FAIL');
2522: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2523: raise g_criteria_failed;
2524: --
2525: end if;
2526: end if;
2526: end if;
2527: --
2528: if p_score_compute_mode
2529: then
2530: hr_utility.set_location('count '||l_score_tab.count,20);
2531: write(p_profile_score_tab,l_score_tab);
2532: end if;
2533: hr_utility.set_location('Leaving: '||l_proc,20);
2534: --
2529: then
2530: hr_utility.set_location('count '||l_score_tab.count,20);
2531: write(p_profile_score_tab,l_score_tab);
2532: end if;
2533: hr_utility.set_location('Leaving: '||l_proc,20);
2534: --
2535: end check_job_elig;
2536: --
2537: -- -----------------------------------------------------------
2558: l_score_tab scoreTab;
2559: --
2560: begin
2561: --
2562: hr_utility.set_location('Entering :'||l_proc,10);
2563: --
2564: ben_elp_cache.elpept_getcacdets
2565: (p_effective_date => p_effective_date
2566: ,p_business_group_id => p_business_group_id
2648: not l_ok then
2649: --
2650: g_inelg_rsn_cd := 'PTP';
2651: fnd_message.set_name('BEN','BEN_91677_PER_TYPE_PRFL_FAIL');
2652: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2653: raise g_criteria_failed;
2654: --
2655: end if;
2656: end if;
2656: end if;
2657: --
2658: if p_score_compute_mode
2659: then
2660: hr_utility.set_location('count '||l_score_tab.count,20);
2661: write(p_profile_score_tab,l_score_tab);
2662: end if;
2663: hr_utility.set_location('Leaving: '||l_proc,20);
2664: --
2659: then
2660: hr_utility.set_location('count '||l_score_tab.count,20);
2661: write(p_profile_score_tab,l_score_tab);
2662: end if;
2663: hr_utility.set_location('Leaving: '||l_proc,20);
2664: --
2665: end check_per_typ_elig;
2666: -- ----------------------------------------------------------
2667: -- This procedure determines eligibility based on pay basis.
2683: l_score_tab scoreTab;
2684: --
2685: begin
2686: --
2687: hr_utility.set_location('Entering : '||l_proc,10);
2688: --
2689: -- Getting eligibility profile pay basis by eligibility profile
2690: --
2691: ben_elp_cache.elpepb_getcacdets
2704: --if l_ok is null then
2705: -- l_ok:=false;
2706: --end if;
2707: if l_ok then
2708: hr_utility.set_location('ok',10);
2709: else
2710: hr_utility.set_location('not ok',10);
2711: end if;
2712: hr_utility.set_location('pay_basis_id='||p_pay_basis_id,10);
2706: --end if;
2707: if l_ok then
2708: hr_utility.set_location('ok',10);
2709: else
2710: hr_utility.set_location('not ok',10);
2711: end if;
2712: hr_utility.set_location('pay_basis_id='||p_pay_basis_id,10);
2713: --
2714: if l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2708: hr_utility.set_location('ok',10);
2709: else
2710: hr_utility.set_location('not ok',10);
2711: end if;
2712: hr_utility.set_location('pay_basis_id='||p_pay_basis_id,10);
2713: --
2714: if l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2715: hr_utility.set_location(l_proc,10);
2716: --
2711: end if;
2712: hr_utility.set_location('pay_basis_id='||p_pay_basis_id,10);
2713: --
2714: if l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2715: hr_utility.set_location(l_proc,10);
2716: --
2717: if p_score_compute_mode then
2718: if l_crit_passed is null then
2719: l_crit_passed := true;
2728: exit;
2729: end if;
2730: --
2731: elsif l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
2732: hr_utility.set_location(l_proc,20);
2733: --
2734: l_rows_found := true;
2735: l_ok := false;
2736: exit;
2735: l_ok := false;
2736: exit;
2737: --
2738: elsif (not l_ok) and l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
2739: hr_utility.set_location(l_proc,30);
2740: --
2741: l_rows_found := true;
2742: l_ok := true;
2743: if p_score_compute_mode then
2750: end if;
2751: -- exit;
2752: --
2753: elsif l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2754: hr_utility.set_location(l_proc,40);
2755: --
2756: l_rows_found := true;
2757: --
2758: end if;
2755: --
2756: l_rows_found := true;
2757: --
2758: end if;
2759: hr_utility.set_location(l_proc,50);
2760: --
2761: end loop;
2762: hr_utility.set_location(l_proc,60);
2763: --
2758: end if;
2759: hr_utility.set_location(l_proc,50);
2760: --
2761: end loop;
2762: hr_utility.set_location(l_proc,60);
2763: --
2764: end if;
2765: --
2766: hr_utility.set_location(l_proc,70);
2762: hr_utility.set_location(l_proc,60);
2763: --
2764: end if;
2765: --
2766: hr_utility.set_location(l_proc,70);
2767: if l_ok then
2768: hr_utility.set_location('ok',10);
2769: else
2770: hr_utility.set_location('not ok',10);
2764: end if;
2765: --
2766: hr_utility.set_location(l_proc,70);
2767: if l_ok then
2768: hr_utility.set_location('ok',10);
2769: else
2770: hr_utility.set_location('not ok',10);
2771: end if;
2772: if l_rows_found then
2766: hr_utility.set_location(l_proc,70);
2767: if l_ok then
2768: hr_utility.set_location('ok',10);
2769: else
2770: hr_utility.set_location('not ok',10);
2771: end if;
2772: if l_rows_found then
2773: hr_utility.set_location('found',10);
2774: else
2769: else
2770: hr_utility.set_location('not ok',10);
2771: end if;
2772: if l_rows_found then
2773: hr_utility.set_location('found',10);
2774: else
2775: hr_utility.set_location('not found',10);
2776: end if;
2777: if l_rows_found is null then
2771: end if;
2772: if l_rows_found then
2773: hr_utility.set_location('found',10);
2774: else
2775: hr_utility.set_location('not found',10);
2776: end if;
2777: if l_rows_found is null then
2778: hr_utility.set_location('found is null',10);
2779: end if;
2774: else
2775: hr_utility.set_location('not found',10);
2776: end if;
2777: if l_rows_found is null then
2778: hr_utility.set_location('found is null',10);
2779: end if;
2780: if l_ok is null then
2781: hr_utility.set_location('ok is null',10);
2782: end if;
2777: if l_rows_found is null then
2778: hr_utility.set_location('found is null',10);
2779: end if;
2780: if l_ok is null then
2781: hr_utility.set_location('ok is null',10);
2782: end if;
2783:
2784: if l_crit_passed is null
2785: then
2785: then
2786: if l_rows_found and
2787: not l_ok then
2788: --
2789: hr_utility.set_location(l_proc,80);
2790: g_inelg_rsn_cd := 'PBS';
2791: fnd_message.set_name('BEN','BEN_91678_PY_BSS_PRFL_FAIL');
2792: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2793: raise g_criteria_failed;
2788: --
2789: hr_utility.set_location(l_proc,80);
2790: g_inelg_rsn_cd := 'PBS';
2791: fnd_message.set_name('BEN','BEN_91678_PY_BSS_PRFL_FAIL');
2792: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2793: raise g_criteria_failed;
2794: --
2795: end if;
2796: end if;
2796: end if;
2797: --
2798: if p_score_compute_mode
2799: then
2800: hr_utility.set_location('count '||l_score_tab.count,20);
2801: write(p_profile_score_tab,l_score_tab);
2802: end if;
2803: hr_utility.set_location('Leaving: '||l_proc,20);
2804: --
2799: then
2800: hr_utility.set_location('count '||l_score_tab.count,20);
2801: write(p_profile_score_tab,l_score_tab);
2802: end if;
2803: hr_utility.set_location('Leaving: '||l_proc,20);
2804: --
2805: end check_py_bss_elig;
2806: -- --------------------------------------------------------
2807: -- This procedure determines eligibility based on payroll.
2823: l_score_tab scoreTab;
2824: --
2825: begin
2826: --
2827: hr_utility.set_location('Entering : '||l_proc,10);
2828: hr_utility.set_location('payroll id : '||p_payroll_id,10);
2829: --
2830: -- Getting eligibility profile payroll by eligibility profile
2831: --
2824: --
2825: begin
2826: --
2827: hr_utility.set_location('Entering : '||l_proc,10);
2828: hr_utility.set_location('payroll id : '||p_payroll_id,10);
2829: --
2830: -- Getting eligibility profile payroll by eligibility profile
2831: --
2832: ben_elp_cache.elpepy_getcacdets
2836: ,p_inst_set => l_inst_dets
2837: ,p_inst_count => l_inst_count
2838: );
2839: --
2840: hr_utility.set_location('Inst Count : '||l_inst_count,10);
2841: if l_inst_count > 0 then
2842: --
2843: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
2844: --
2899: not l_ok then
2900: --
2901: g_inelg_rsn_cd := 'PYR';
2902: fnd_message.set_name('BEN','BEN_91679_PYRL_PRFL_FAIL');
2903: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2904: raise g_criteria_failed;
2905: --
2906: end if;
2907: end if;
2907: end if;
2908: --
2909: if p_score_compute_mode
2910: then
2911: hr_utility.set_location('count '||l_score_tab.count,20);
2912: write(p_profile_score_tab,l_score_tab);
2913: end if;
2914: hr_utility.set_location('Leaving: '||l_proc,20);
2915: --
2910: then
2911: hr_utility.set_location('count '||l_score_tab.count,20);
2912: write(p_profile_score_tab,l_score_tab);
2913: end if;
2914: hr_utility.set_location('Leaving: '||l_proc,20);
2915: --
2916: end check_pyrl_elig;
2917: -- ---------------------------------------------------------------
2918: -- This procedure determines eligibility based on benefits group.
2934: l_score_tab scoreTab;
2935: --
2936: begin
2937: --
2938: hr_utility.set_location('Entering : '||l_proc,10);
2939: --
2940: -- Getting eligibility profile benefits group by eligibility profile
2941: --
2942: ben_elp_cache.elpebn_getcacdets
3009: not l_ok then
3010: --
3011: g_inelg_rsn_cd := 'BGR';
3012: fnd_message.set_name('BEN','BEN_91680_BENFTS_GRP_PRFL_FAIL');
3013: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3014: raise g_criteria_failed;
3015: --
3016: end if;
3017: end if;
3017: end if;
3018: --
3019: if p_score_compute_mode
3020: then
3021: hr_utility.set_location('count '||l_score_tab.count,20);
3022: write(p_profile_score_tab,l_score_tab);
3023: end if;
3024: hr_utility.set_location('Leaving: '||l_proc,20);
3025: --
3020: then
3021: hr_utility.set_location('count '||l_score_tab.count,20);
3022: write(p_profile_score_tab,l_score_tab);
3023: end if;
3024: hr_utility.set_location('Leaving: '||l_proc,20);
3025: --
3026: end check_benefits_grp_elig;
3027: -- --------------------------------------------------------------
3028: -- This procedure determines eligibility based on work location.
3044: l_score_tab scoreTab;
3045: --
3046: begin
3047: --
3048: hr_utility.set_location('Entering : '||l_proc,10);
3049: --
3050: -- Getting eligibility profile location by eligibility profile
3051: --
3052: ben_elp_cache.elpewl_getcacdets
3117: not l_ok then
3118: --
3119: g_inelg_rsn_cd := 'LOC';
3120: fnd_message.set_name('BEN','BEN_91681_WK_LOC_PRFL_FAIL');
3121: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3122: raise g_criteria_failed;
3123: --
3124: end if;
3125: end if;
3125: end if;
3126: --
3127: if p_score_compute_mode
3128: then
3129: hr_utility.set_location('count '||l_score_tab.count,20);
3130: write(p_profile_score_tab,l_score_tab);
3131: end if;
3132: hr_utility.set_location('Leaving: '||l_proc,20);
3133: --
3128: then
3129: hr_utility.set_location('count '||l_score_tab.count,20);
3130: write(p_profile_score_tab,l_score_tab);
3131: end if;
3132: hr_utility.set_location('Leaving: '||l_proc,20);
3133: --
3134: end check_wk_location_elig;
3135: -- -------------------------------------------------------------
3136: -- This procedure determines eligibility based on people group.
3193: l_ppl_grp_rec c_ppl_grp%ROWTYPE ;
3194: --
3195: begin
3196: --
3197: hr_utility.set_location('Entering : '||l_proc,10);
3198: --
3199: -- Getting eligibility profile organization by eligibility profile
3200: --
3201: ben_elp_cache.elpepg_getcacdets
3549: not l_ok then
3550: --
3551: g_inelg_rsn_cd := 'PEO';
3552: fnd_message.set_name('BEN','BEN_92224_PEO_GROUP_PRFL_FAIL');
3553: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3554: raise g_criteria_failed;
3555: --
3556: end if;
3557: end if;
3557: end if;
3558: --
3559: if p_score_compute_mode
3560: then
3561: hr_utility.set_location('count '||l_score_tab.count,20);
3562: write(p_profile_score_tab,l_score_tab);
3563: end if;
3564: hr_utility.set_location('Leaving: '||l_proc,20);
3565: --
3560: then
3561: hr_utility.set_location('count '||l_score_tab.count,20);
3562: write(p_profile_score_tab,l_score_tab);
3563: end if;
3564: hr_utility.set_location('Leaving: '||l_proc,20);
3565: --
3566: end check_people_group_elig;
3567: -- ---------------------------------------------------------
3568: -- This procedure determines eligibility based on org unit.
3584: l_score_tab scoreTab;
3585: --
3586: begin
3587: --
3588: hr_utility.set_location('Entering : '||l_proc,10);
3589: --
3590: -- Getting eligibility profile organization by eligibility profile
3591: --
3592: ben_elp_cache.elpeou_getcacdets
3657: not l_ok then
3658: --
3659: g_inelg_rsn_cd := 'ORG';
3660: fnd_message.set_name('BEN','BEN_91682_ORG_UNIT_PRFL_FAIL');
3661: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3662: raise g_criteria_failed;
3663: --
3664: end if;
3665: end if;
3665: end if;
3666: --
3667: if p_score_compute_mode
3668: then
3669: hr_utility.set_location('count '||l_score_tab.count,20);
3670: write(p_profile_score_tab,l_score_tab);
3671: end if;
3672: hr_utility.set_location('Leaving: '||l_proc,20);
3673: --
3668: then
3669: hr_utility.set_location('count '||l_score_tab.count,20);
3670: write(p_profile_score_tab,l_score_tab);
3671: end if;
3672: hr_utility.set_location('Leaving: '||l_proc,20);
3673: --
3674: end check_org_unit_elig;
3675: -- --------------------------------------------------------------
3676: -- This procedure determines eligibility based on pay frequency.
3692: l_score_tab scoreTab;
3693: --
3694: begin
3695: --
3696: hr_utility.set_location('Entering : '||l_proc,10);
3697: --
3698: -- Getting eligibility profile pay frequency by eligibility profile
3699: --
3700: ben_elp_cache.elpehs_getcacdets
3766: not l_ok then
3767: --
3768: g_inelg_rsn_cd := 'PFQ';
3769: fnd_message.set_name('BEN','BEN_91683_PY_FREQ_PRFL_FAIL');
3770: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3771: raise g_criteria_failed;
3772: --
3773: end if;
3774: end if;
3774: end if;
3775: --
3776: if p_score_compute_mode
3777: then
3778: hr_utility.set_location('count '||l_score_tab.count,20);
3779: write(p_profile_score_tab,l_score_tab);
3780: end if;
3781: hr_utility.set_location('Leaving: '||l_proc,20);
3782: --
3777: then
3778: hr_utility.set_location('count '||l_score_tab.count,20);
3779: write(p_profile_score_tab,l_score_tab);
3780: end if;
3781: hr_utility.set_location('Leaving: '||l_proc,20);
3782: --
3783: end check_py_freq_elig;
3784: -- ---------------------------------------------------------------
3785: -- This procedure determines eligibility based on service area.
3840: and p_effective_date between zip.effective_start_date
3841: and zip.effective_end_date;
3842: begin
3843: --
3844: hr_utility.set_location('Entering : '||l_proc,10);
3845: --
3846: if p_postal_code is null then
3847: ben_person_object.get_object(p_person_id => p_person_id,
3848: p_rec => l_pad_rec);
3886: nvl(l_criteria_score, l_criteria_weight));
3887: else
3888: exit;
3889: end if;
3890: hr_utility.set_location(' l_rows_found := TRUE ' ,99);
3891: --
3892: ELSIF (NOT l_rows_found AND l_excld_flag = 'Y' ) then
3893: --
3894: l_rows_found := TRUE;
3898: 'ESA',
3899: l_pk_id,
3900: nvl(l_criteria_score, l_criteria_weight));
3901: end if;
3902: hr_utility.set_location(' l_rows_found := TRUE ' ,99);
3903: --
3904: ELSIF ( l_rows_found AND l_excld_flag = 'Y' ) then
3905: --
3906: g_inelg_rsn_cd := 'SVC';
3916: if l_crit_passed is null
3917: then
3918: if NOT l_rows_found then
3919: --
3920: hr_utility.set_location(' g_profile_failed ',99);
3921: RAISE g_criteria_failed ;
3922: --
3923: End if;
3924: End if;
3924: End if;
3925: --
3926: if p_score_compute_mode
3927: then
3928: hr_utility.set_location('count '||l_score_tab.count,20);
3929: write(p_profile_score_tab,l_score_tab);
3930: end if;
3931: hr_utility.set_location('Leaving: '||l_proc,20);
3932: --
3927: then
3928: hr_utility.set_location('count '||l_score_tab.count,20);
3929: write(p_profile_score_tab,l_score_tab);
3930: end if;
3931: hr_utility.set_location('Leaving: '||l_proc,20);
3932: --
3933: end check_service_area_elig;
3934: --
3935: -- ---------------------------------------------------------------
3982: and p_effective_date between zip.effective_start_date
3983: and zip.effective_end_date;
3984: begin
3985: --
3986: hr_utility.set_location('Entering : '||l_proc,10);
3987: --
3988: -- Getting eligibility profile zip code range by eligibility profile
3989: --
3990: --
4012: loop
4013: fetch get_zip_ranges into l_from_value,l_to_value;
4014: exit when get_zip_ranges%NOTFOUND;
4015: --
4016: hr_utility.set_location('person zip '||l_pad_rec.postal_code ,2219.3);
4017: hr_utility.set_location('from zip '||l_from_value ,2219.3);
4018: hr_utility.set_location('to zip '||l_to_value ,2219.3);
4019: --
4020: l_rows_found := true;
4013: fetch get_zip_ranges into l_from_value,l_to_value;
4014: exit when get_zip_ranges%NOTFOUND;
4015: --
4016: hr_utility.set_location('person zip '||l_pad_rec.postal_code ,2219.3);
4017: hr_utility.set_location('from zip '||l_from_value ,2219.3);
4018: hr_utility.set_location('to zip '||l_to_value ,2219.3);
4019: --
4020: l_rows_found := true;
4021: exit;
4014: exit when get_zip_ranges%NOTFOUND;
4015: --
4016: hr_utility.set_location('person zip '||l_pad_rec.postal_code ,2219.3);
4017: hr_utility.set_location('from zip '||l_from_value ,2219.3);
4018: hr_utility.set_location('to zip '||l_to_value ,2219.3);
4019: --
4020: l_rows_found := true;
4021: exit;
4022: end loop zip_loop;
4072: end if;
4073: end if;
4074: if p_score_compute_mode
4075: then
4076: hr_utility.set_location('count '||l_score_tab.count,20);
4077: write(p_profile_score_tab,l_score_tab);
4078: end if;
4079: hr_utility.set_location('Leaving: '||l_proc,20);
4080: --
4075: then
4076: hr_utility.set_location('count '||l_score_tab.count,20);
4077: write(p_profile_score_tab,l_score_tab);
4078: end if;
4079: hr_utility.set_location('Leaving: '||l_proc,20);
4080: --
4081: end check_zip_code_rng_elig;
4082: -- ---------------------------------------------------------
4083: -- This procedure determines elig based on scheduled hours.
4146: and frequency = p_frequency ;
4147: --
4148: begin
4149: --
4150: hr_utility.set_location('Entering : '||l_proc,10);
4151: --
4152:
4153: -- Getting eligibility profile scheduled hours by eligibility profile
4154: --
4194: l_min_hours := null;
4195: l_max_hours := null;
4196: l_freq_cd := null;
4197: --
4198: hr_utility.set_location('criteria #' || l_insttorrw_num,20);
4199: --
4200: if l_inst_dets(l_insttorrw_num).schedd_hrs_rl is not null then
4201: l_output := benutils.formula
4202: (p_formula_id => l_inst_dets(l_insttorrw_num).schedd_hrs_rl
4289: l_max_hours := l_inst_dets(l_insttorrw_num).max_hrs_num;
4290: l_freq_cd := l_inst_dets(l_insttorrw_num).freq_cd;
4291: end if;
4292: --
4293: hr_utility.set_location('l_min_hours' || l_min_hours,30);
4294: hr_utility.set_location('l_max_hours' || l_max_hours,30);
4295: hr_utility.set_location('l_freq_cd ' || l_freq_cd ,30);
4296: --
4297: -- Use the determination code given in the criteria in ELPRO to arrive
4290: l_freq_cd := l_inst_dets(l_insttorrw_num).freq_cd;
4291: end if;
4292: --
4293: hr_utility.set_location('l_min_hours' || l_min_hours,30);
4294: hr_utility.set_location('l_max_hours' || l_max_hours,30);
4295: hr_utility.set_location('l_freq_cd ' || l_freq_cd ,30);
4296: --
4297: -- Use the determination code given in the criteria in ELPRO to arrive
4298: -- at the effective date to be used for retrieving Normal Hours of the person.
4291: end if;
4292: --
4293: hr_utility.set_location('l_min_hours' || l_min_hours,30);
4294: hr_utility.set_location('l_max_hours' || l_max_hours,30);
4295: hr_utility.set_location('l_freq_cd ' || l_freq_cd ,30);
4296: --
4297: -- Use the determination code given in the criteria in ELPRO to arrive
4298: -- at the effective date to be used for retrieving Normal Hours of the person.
4299: --
4327: l_use_all_asnts_elig_flag := 'Y';
4328: --
4329: end if;
4330: --
4331: hr_utility.set_location('asg eff. date ' || l_sched_effective_date ,40);
4332: hr_utility.set_location('l_use_all_asnts_elig_flag ' || l_use_all_asnts_elig_flag ,40);
4333: --
4334: open c_normal_hours (l_freq_cd,l_use_all_asnts_elig_flag) ;
4335: fetch c_normal_hours into l_normal_hours ;
4328: --
4329: end if;
4330: --
4331: hr_utility.set_location('asg eff. date ' || l_sched_effective_date ,40);
4332: hr_utility.set_location('l_use_all_asnts_elig_flag ' || l_use_all_asnts_elig_flag ,40);
4333: --
4334: open c_normal_hours (l_freq_cd,l_use_all_asnts_elig_flag) ;
4335: fetch c_normal_hours into l_normal_hours ;
4336: if c_normal_hours%found then
4334: open c_normal_hours (l_freq_cd,l_use_all_asnts_elig_flag) ;
4335: fetch c_normal_hours into l_normal_hours ;
4336: if c_normal_hours%found then
4337: --
4338: hr_utility.set_location('l_normal_hours ' || l_normal_hours ,50);
4339: close c_normal_hours ;
4340: --
4341: -- Before comparing the normal hours with the range , we have to perform
4342: -- the rounding
4442: not l_ok then
4443: --
4444: g_inelg_rsn_cd := 'SHR';
4445: fnd_message.set_name('BEN','BEN_91685_SCHED_HRS_PRFL_FAIL');
4446: hr_utility.set_location('Criteria Failed: '||l_proc,60);
4447: raise g_criteria_failed;
4448: --
4449: end if;
4450: end if;
4450: end if;
4451: --
4452: if p_score_compute_mode
4453: then
4454: hr_utility.set_location('count '||l_score_tab.count,20);
4455: write(p_profile_score_tab,l_score_tab);
4456: end if;
4457: hr_utility.set_location('Leaving: '||l_proc,70);
4458: --
4453: then
4454: hr_utility.set_location('count '||l_score_tab.count,20);
4455: write(p_profile_score_tab,l_score_tab);
4456: end if;
4457: hr_utility.set_location('Leaving: '||l_proc,70);
4458: --
4459: end check_sched_hrs_elig;
4460: -- --------------------------------------------------
4461: -- This procedure det elig based on bargaining unit.
4477: l_score_tab scoreTab;
4478: --
4479: begin
4480: --
4481: hr_utility.set_location('Entering : '||l_proc,10);
4482: --
4483: -- Getting eligibility profile bargaining unit by eligibility profile
4484: --
4485: ben_elp_cache.elpebu_getcacdets
4550: not l_ok then
4551: --
4552: g_inelg_rsn_cd := 'BRG';
4553: fnd_message.set_name('BEN','BEN_91686_BRGNG_UNIT_PRFL_FAIL');
4554: hr_utility.set_location('Criteria Failed: '||l_proc,20);
4555: raise g_criteria_failed;
4556: --
4557: end if;
4558: end if;
4558: end if;
4559: --
4560: if p_score_compute_mode
4561: then
4562: hr_utility.set_location('count '||l_score_tab.count,20);
4563: write(p_profile_score_tab,l_score_tab);
4564: end if;
4565: hr_utility.set_location('Leaving: '||l_proc,20);
4566: --
4561: then
4562: hr_utility.set_location('count '||l_score_tab.count,20);
4563: write(p_profile_score_tab,l_score_tab);
4564: end if;
4565: hr_utility.set_location('Leaving: '||l_proc,20);
4566: --
4567: end check_brgng_unit_elig;
4568: -- ----------------------------------------------------------
4569: -- This procedure det elig based on labour union membership.
4585: l_score_tab scoreTab;
4586: --
4587: begin
4588: --
4589: hr_utility.set_location('Entering : '||l_proc,10);
4590: --
4591: -- Getting eligibility profile labour union membership by eligibility profile
4592: --
4593: ben_elp_cache.elpelu_getcacdets
4658: not l_ok then
4659: --
4660: g_inelg_rsn_cd := 'LBR';
4661: fnd_message.set_name('BEN','BEN_91687_LBR_MMBR_PRFL_FAIL');
4662: hr_utility.set_location('Criteria Failed: '||l_proc,20);
4663: raise g_criteria_failed;
4664: --
4665: end if;
4666: end if;
4666: end if;
4667: --
4668: if p_score_compute_mode
4669: then
4670: hr_utility.set_location('count '||l_score_tab.count,20);
4671: write(p_profile_score_tab,l_score_tab);
4672: end if;
4673: hr_utility.set_location('Leaving: '||l_proc,20);
4674: --
4669: then
4670: hr_utility.set_location('count '||l_score_tab.count,20);
4671: write(p_profile_score_tab,l_score_tab);
4672: end if;
4673: hr_utility.set_location('Leaving: '||l_proc,20);
4674: --
4675: end check_lbr_union_elig;
4676: -- --------------------------------------------------
4677: -- This procedure det elig based on employee status.
4695: l_score_tab scoreTab;
4696: --
4697: begin
4698: --
4699: hr_utility.set_location('Entering : '||l_proc,10);
4700: --
4701: -- Getting eligibility profile assignment status type by eligibility profile
4702: --
4703: ben_elp_cache.elpees_getcacdets
4769: not l_ok then
4770: --
4771: g_inelg_rsn_cd := 'STA';
4772: fnd_message.set_name('BEN','BEN_91688_EE_STAT_PRFL_FAIL');
4773: hr_utility.set_location('Criteria Failed: '||l_proc,20);
4774: raise g_criteria_failed;
4775: --
4776: end if;
4777: end if;
4777: end if;
4778: --
4779: if p_score_compute_mode
4780: then
4781: hr_utility.set_location('count '||l_score_tab.count,20);
4782: write(p_profile_score_tab,l_score_tab);
4783: end if;
4784: hr_utility.set_location('Leaving: '||l_proc,20);
4785: --
4780: then
4781: hr_utility.set_location('count '||l_score_tab.count,20);
4782: write(p_profile_score_tab,l_score_tab);
4783: end if;
4784: hr_utility.set_location('Leaving: '||l_proc,20);
4785: --
4786: end check_ee_stat_elig;
4787: -- ---------------------------------------------------
4788: -- This procedure det elig based on leave of absence.
4827: and abs.business_group_id = p_business_group_id;
4828: --
4829: begin
4830: --
4831: hr_utility.set_location('Entering : '||l_proc,10);
4832: --
4833: -- Getting eligibility profile leave of absence reason by eligibility profile
4834: --
4835: ben_elp_cache.elpelr_getcacdets
5019: not l_ok then
5020: --
5021: g_inelg_rsn_cd := 'LOA';
5022: fnd_message.set_name('BEN','BEN_91689_PER_LOA_PRFL_FAIL');
5023: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5024: raise g_criteria_failed;
5025: --
5026: end if;
5027: end if;
5027: end if;
5028: --
5029: if p_score_compute_mode
5030: then
5031: hr_utility.set_location('count '||l_score_tab.count,20);
5032: write(p_profile_score_tab,l_score_tab);
5033: end if;
5034: hr_utility.set_location('Leaving: '||l_proc,20);
5035: --
5030: then
5031: hr_utility.set_location('count '||l_score_tab.count,20);
5032: write(p_profile_score_tab,l_score_tab);
5033: end if;
5034: hr_utility.set_location('Leaving: '||l_proc,20);
5035: --
5036: end check_loa_rsn_elig;
5037: -- -----------------------------------------------
5038: -- This procedure det elig based on legal entity.
5054: l_score_tab scoreTab;
5055: --
5056: begin
5057: --
5058: hr_utility.set_location('Entering : '||l_proc,10);
5059: --
5060: -- Getting eligibility profile legal entity by eligibility profile
5061: --
5062: ben_elp_cache.elpeln_getcacdets
5065: ,p_eligy_prfl_id => p_eligy_prfl_id
5066: ,p_inst_set => l_inst_dets
5067: ,p_inst_count => l_inst_count);
5068: --
5069: hr_utility.set_location('Got Profiles : '||l_proc,10);
5070: --
5071: if l_inst_count > 0 then
5072: --
5073: hr_utility.set_location('In Loop : '||l_proc,10);
5069: hr_utility.set_location('Got Profiles : '||l_proc,10);
5070: --
5071: if l_inst_count > 0 then
5072: --
5073: hr_utility.set_location('In Loop : '||l_proc,10);
5074: --
5075: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
5076: --
5077: l_ok := nvl((l_inst_dets(l_insttorrw_num).name = nvl(p_gre_name,'-1')),FALSE);
5131: not l_ok then
5132: --
5133: g_inelg_rsn_cd := 'LGL';
5134: fnd_message.set_name('BEN','BEN_91690_LGL_ENTY_PRFL_FAIL');
5135: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5136: raise g_criteria_failed;
5137: --
5138: end if;
5139: end if;
5139: end if;
5140: --
5141: if p_score_compute_mode
5142: then
5143: hr_utility.set_location('count '||l_score_tab.count,20);
5144: write(p_profile_score_tab,l_score_tab);
5145: end if;
5146: hr_utility.set_location('Copying gre cd : ',10);
5147: --
5142: then
5143: hr_utility.set_location('count '||l_score_tab.count,20);
5144: write(p_profile_score_tab,l_score_tab);
5145: end if;
5146: hr_utility.set_location('Copying gre cd : ',10);
5147: --
5148: hr_utility.set_location('Leaving : '||l_proc,10);
5149: --
5150: end check_lgl_enty_elig;
5144: write(p_profile_score_tab,l_score_tab);
5145: end if;
5146: hr_utility.set_location('Copying gre cd : ',10);
5147: --
5148: hr_utility.set_location('Leaving : '||l_proc,10);
5149: --
5150: end check_lgl_enty_elig;
5151: -- ----------------------------------------------------------------
5152: -- This procedure det elig based on participation in another plan.
5189: ;
5190:
5191: begin
5192: --
5193: hr_utility.set_location('Entering : '||l_proc,10);
5194: --
5195: -- Getting eligibility profile other plan by eligibility profile
5196: --
5197: ben_elp_cache.elpepp_getcacdets
5242: not l_ok then
5243: --
5244: g_inelg_rsn_cd := 'PLN';
5245: fnd_message.set_name('BEN','BEN_91691_PRTN_PL_PRFL_FAIL');
5246: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5247: raise g_criteria_failed;
5248: --
5249: end if;
5250: --
5247: raise g_criteria_failed;
5248: --
5249: end if;
5250: --
5251: hr_utility.set_location('Leaving: '||l_proc,20);
5252: --
5253: end check_prtt_in_anthr_pl_elig;
5254: -- ---------------------------------------------------------------
5255: -- This procedure det elig based on full time/part time category.
5271: l_score_tab scoreTab;
5272: --
5273: begin
5274: --
5275: hr_utility.set_location('Entering : '||l_proc,10);
5276: --
5277: -- Getting eligibility profile full/part time by eligibility profile
5278: --
5279: ben_elp_cache.elpefp_getcacdets
5347: -- There were non-excludes and we didn't match any
5348: --
5349: g_inelg_rsn_cd := 'FPT';
5350: fnd_message.set_name('BEN','BEN_91692_FL_PT_PRFL_FAIL');
5351: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5352: raise g_criteria_failed;
5353: --
5354: end if;
5355: end if;
5355: end if;
5356: --
5357: if p_score_compute_mode
5358: then
5359: hr_utility.set_location('count '||l_score_tab.count,20);
5360: write(p_profile_score_tab,l_score_tab);
5361: end if;
5362: hr_utility.set_location('Leaving: '||l_proc,20);
5363: --
5358: then
5359: hr_utility.set_location('count '||l_score_tab.count,20);
5360: write(p_profile_score_tab,l_score_tab);
5361: end if;
5362: hr_utility.set_location('Leaving: '||l_proc,20);
5363: --
5364: end check_fl_tm_pt_elig;
5365: -- ---------------------------------------------------------------
5366: -- This procedure det elig based on persons benefit balance.
5393: l_score_tab scoreTab;
5394: --
5395: begin
5396: --
5397: hr_utility.set_location('Entering: '||l_proc, 10);
5398: --
5399: -- Getting eligibility profile compensation level by eligibility profile
5400: --
5401: ben_elp_cache.elpecl_getcacdets
5506: not l_ok then
5507: --
5508: g_inelg_rsn_cd := 'CMP';
5509: fnd_message.set_name('BEN','BEN_91672_COMP_LVL_PRFL_FAIL');
5510: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5511: raise g_criteria_failed;
5512: --
5513: end if;
5514: end if;
5514: end if;
5515: --
5516: if p_score_compute_mode
5517: then
5518: hr_utility.set_location('count '||l_score_tab.count,20);
5519: write(p_profile_score_tab,l_score_tab);
5520: end if;
5521: hr_utility.set_location('Leaving :'||l_proc,20);
5522: --
5517: then
5518: hr_utility.set_location('count '||l_score_tab.count,20);
5519: write(p_profile_score_tab,l_score_tab);
5520: end if;
5521: hr_utility.set_location('Leaving :'||l_proc,20);
5522: --
5523: end check_person_ben_bal;
5524: --
5525: procedure check_asnt_set
5543: l_score_tab scoreTab;
5544: --
5545: begin
5546: --
5547: hr_utility.set_location('Entering: '||l_proc, 10);
5548: --
5549: -- Getting eligibility profile compensation level by eligibility profile
5550: --
5551: ben_elp_cache.elpean_getcacdets
5616: fnd_message.raise_error;
5617: --
5618: end;
5619: --
5620: hr_utility.set_location('Include Flag '||l_include_flag,10);
5621: --
5622: l_ok := nvl((l_include_flag = 'Y'),FALSE);
5623: --if l_ok is null then
5624: -- l_ok:=false;
5676: not l_ok then
5677: --
5678: g_inelg_rsn_cd := 'ASS';
5679: fnd_message.set_name('BEN','BEN_92459_ASS_SET_PRFL_FAIL');
5680: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5681: raise g_criteria_failed;
5682: --
5683: end if;
5684: end if;
5684: end if;
5685: --
5686: if p_score_compute_mode
5687: then
5688: hr_utility.set_location('count '||l_score_tab.count,20);
5689: write(p_profile_score_tab,l_score_tab);
5690: end if;
5691: hr_utility.set_location('Leaving :'||l_proc,20);
5692: --
5687: then
5688: hr_utility.set_location('count '||l_score_tab.count,20);
5689: write(p_profile_score_tab,l_score_tab);
5690: end if;
5691: hr_utility.set_location('Leaving :'||l_proc,20);
5692: --
5693: end check_asnt_set;
5694: ------------------------------------------------------------------------
5695: -- ---------------------------------------------------------------
5723: l_score_tab scoreTab;
5724: --
5725: begin
5726: --
5727: hr_utility.set_location('Entering: '||l_proc, 10);
5728: --
5729: -- Getting eligibility profile compensation level by eligibility profile
5730: --
5731: ben_elp_cache.elpecl_getcacdets
5832: not l_ok then
5833: --
5834: g_inelg_rsn_cd := 'CMP';
5835: fnd_message.set_name('BEN','BEN_91672_COMP_LVL_PRFL_FAIL');
5836: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5837: raise g_criteria_failed;
5838: --
5839: end if;
5840: end if;
5840: end if;
5841: --
5842: if p_score_compute_mode
5843: then
5844: hr_utility.set_location('count '||l_score_tab.count,20);
5845: write(p_profile_score_tab,l_score_tab);
5846: end if;
5847: hr_utility.set_location('Leaving :'||l_proc,20);
5848: --
5843: then
5844: hr_utility.set_location('count '||l_score_tab.count,20);
5845: write(p_profile_score_tab,l_score_tab);
5846: end if;
5847: hr_utility.set_location('Leaving :'||l_proc,20);
5848: --
5849: end check_person_balance;
5850: --------------------------------------------------------------------
5851: --
5867: l_rec per_all_people_f%rowtype;
5868: --
5869: begin
5870: --
5871: hr_utility.set_location('Entering: '||l_proc, 10);
5872: --
5873: -- Getting eligibility profile compensation level by eligibility profile
5874: --
5875: ben_elp_cache.elpeno_getcacdets
5907: not l_ok then
5908: --
5909: g_inelg_rsn_cd := 'ENO';
5910: fnd_message.set_name('BEN','BEN_92227_ENO_ELIG_PRFL_FAIL');
5911: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5912: raise g_criteria_failed;
5913: --
5914: end if;
5915: --
5912: raise g_criteria_failed;
5913: --
5914: end if;
5915: --
5916: hr_utility.set_location('Leaving :'||l_proc,20);
5917: --
5918: end check_elig_no_othr_cvg_prte;
5919: --
5920: -- --------------------------------------------------------------------------
5940: l_crit_passed boolean;
5941: l_score_tab scoreTab;
5942: begin
5943: --
5944: hr_utility.set_location('Entering: '||l_proc, 10);
5945: --
5946: -- Getting eligibility profile compensation level by eligibility profile
5947: --
5948: ben_elp_cache.elpelv_getcacdets
6024: not l_ok then
6025: --
6026: g_inelg_rsn_cd := 'ELV';
6027: fnd_message.set_name('BEN','BEN_92228_ELV_ELIG_PRFL_FAIL');
6028: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6029: raise g_criteria_failed;
6030: --
6031: end if;
6032: end if;
6032: end if;
6033: --
6034: if p_score_compute_mode
6035: then
6036: hr_utility.set_location('count '||l_score_tab.count,20);
6037: write(p_profile_score_tab,l_score_tab);
6038: end if;
6039: hr_utility.set_location('Leaving :'||l_proc,20);
6040: --
6035: then
6036: hr_utility.set_location('count '||l_score_tab.count,20);
6037: write(p_profile_score_tab,l_score_tab);
6038: end if;
6039: hr_utility.set_location('Leaving :'||l_proc,20);
6040: --
6041: end check_elig_lvg_rsn_prte;
6042: --
6043: -- --------------------------------------------------------------------------
6058: l_rec per_all_people_f%rowtype;
6059: --
6060: begin
6061: --
6062: hr_utility.set_location('Entering: '||l_proc, 10);
6063: --
6064: -- Getting eligibility profile compensation level by eligibility profile
6065: --
6066: ben_elp_cache.elpeom_getcacdets
6100: not l_ok then
6101: --
6102: g_inelg_rsn_cd := 'EOM';
6103: fnd_message.set_name('BEN','BEN_92229_EOM_ELIG_PRFL_FAIL');
6104: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6105: raise g_criteria_failed;
6106: --
6107: end if;
6108: --
6105: raise g_criteria_failed;
6106: --
6107: end if;
6108: --
6109: hr_utility.set_location('Leaving :'||l_proc,20);
6110: --
6111: end check_elig_optd_mdcr_prte;
6112: --
6113: -- --------------------------------------------------------------------------
6132: l_dummy varchar2(1);
6133: --
6134: begin
6135: --
6136: hr_utility.set_location('Entering: '||l_proc, 10);
6137: --
6138: -- Getting eligibility profile enrld in another plan by eligibility profile
6139: --
6140: ben_elp_cache.elpeep_getcacdets
6216: not l_ok then
6217: --
6218: g_inelg_rsn_cd := 'EEP';
6219: fnd_message.set_name('BEN','BEN_92230_EEP_ELIG_PRFL_FAIL');
6220: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6221: raise g_criteria_failed;
6222: --
6223: end if;
6224: --
6221: raise g_criteria_failed;
6222: --
6223: end if;
6224: --
6225: hr_utility.set_location('Leaving :'||l_proc,20);
6226: --
6227: end check_elig_enrld_anthr_pl;
6228: --
6229: -- --------------------------------------------------------------------------
6260: and pen.prtt_enrt_rslt_stat_cd is null;
6261: --
6262: begin
6263: --
6264: hr_utility.set_location('Entering: '||l_proc, 10);
6265: --
6266: -- Getting eligibility profile compensation level by eligibility profile
6267: --
6268: ben_elp_cache.elpeei_getcacdets
6346: not l_ok then
6347: --
6348: g_inelg_rsn_cd := 'EEI';
6349: fnd_message.set_name('BEN','BEN_92231_EEI_ELIG_PRFL_FAIL');
6350: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6351: raise g_criteria_failed;
6352: --
6353: end if;
6354: --
6351: raise g_criteria_failed;
6352: --
6353: end if;
6354: --
6355: hr_utility.set_location('Leaving :'||l_proc,20);
6356: --
6357: end check_elig_enrld_anthr_oipl;
6358: --
6359: -- --------------------------------------------------------------------------
6381: l_dummy varchar2(1);
6382: --
6383: begin
6384: --
6385: hr_utility.set_location('Entering: '||l_proc, 10);
6386: hr_utility.set_location('prfl_id: '||p_eligy_prfl_id, 10);
6387: --
6388: -- Getting eligibility profile compensation level by eligibility profile
6389: --
6382: --
6383: begin
6384: --
6385: hr_utility.set_location('Entering: '||l_proc, 10);
6386: hr_utility.set_location('prfl_id: '||p_eligy_prfl_id, 10);
6387: --
6388: -- Getting eligibility profile compensation level by eligibility profile
6389: --
6390: ben_elp_cache.elpeeg_getcacdets
6470: not l_ok then
6471: --
6472: g_inelg_rsn_cd := 'EEG';
6473: fnd_message.set_name('BEN','BEN_92232_EEG_ELIG_PRFL_FAIL');
6474: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6475: raise g_criteria_failed;
6476: --
6477: end if;
6478: --
6475: raise g_criteria_failed;
6476: --
6477: end if;
6478: --
6479: hr_utility.set_location('Leaving :'||l_proc,20);
6480: --
6481: end check_elig_enrld_anthr_pgm;
6482: --
6483: -- --------------------------------------------------------------------------
6520: and pdp.effective_end_date = hr_api.g_eot;
6521: --
6522: begin
6523: --
6524: hr_utility.set_location('Entering: '||l_proc, 10);
6525: --
6526: -- Getting eligibility profile compensation level by eligibility profile
6527: --
6528: ben_elp_cache.elpedp_getcacdets
6589: not l_ok then
6590: --
6591: g_inelg_rsn_cd := 'EDP';
6592: fnd_message.set_name('BEN','BEN_92233_EDP_ELIG_PRFL_FAIL');
6593: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6594: raise g_criteria_failed;
6595: --
6596: end if;
6597: --
6594: raise g_criteria_failed;
6595: --
6596: end if;
6597: --
6598: hr_utility.set_location('Leaving :'||l_proc,20);
6599: --
6600: end check_elig_dpnt_cvrd_othr_pl;
6601: --
6602: -- --------------------------------------------------------------------------
6638: and pen.prtt_enrt_rslt_stat_cd is null;
6639: --
6640: begin
6641: --
6642: hr_utility.set_location('Entering: '||l_proc, 10);
6643: --
6644: -- Getting eligibility profile compensation level by eligibility profile
6645: --
6646: ben_elp_cache.elpeai_getcacdets
6709: not l_ok then
6710: --
6711: g_inelg_rsn_cd := 'EAI';
6712: fnd_message.set_name('BEN','BEN_92420_EAI_ELIG_PRFL_FAIL');
6713: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6714: raise g_criteria_failed;
6715: --
6716: end if;
6717: --
6714: raise g_criteria_failed;
6715: --
6716: end if;
6717: --
6718: hr_utility.set_location('Leaving :'||l_proc,20);
6719: --
6720: end check_elig_enrld_anthr_plip;
6721: --
6722: -- --------------------------------------------------------------------------
6767: and pdp.effective_end_date = hr_api.g_eot;
6768: --
6769: begin
6770: --
6771: hr_utility.set_location('Entering: '||l_proc, 10);
6772: --
6773: -- Getting eligibility profile compensation level by eligibility profile
6774: --
6775: ben_elp_cache.elpedi_getcacdets
6838: not l_ok then
6839: --
6840: g_inelg_rsn_cd := 'EDI';
6841: fnd_message.set_name('BEN','BEN_92421_EDI_ELIG_PRFL_FAIL');
6842: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6843: raise g_criteria_failed;
6844: --
6845: end if;
6846: --
6843: raise g_criteria_failed;
6844: --
6845: end if;
6846: --
6847: hr_utility.set_location('Leaving :'||l_proc,20);
6848: --
6849: end check_elig_dpnt_cvrd_othr_plip;
6850: --
6851: -- --------------------------------------------------------------------------
6894:
6895: --
6896: begin
6897: --
6898: hr_utility.set_location('Entering: '||l_proc, 10);
6899: hr_utility.set_location('l_fonm_cvg_strt_dt: '||l_fonm_cvg_strt_dt, 10);
6900: --
6901: -- Getting eligibility profile compensation level by eligibility profile
6902: --
6895: --
6896: begin
6897: --
6898: hr_utility.set_location('Entering: '||l_proc, 10);
6899: hr_utility.set_location('l_fonm_cvg_strt_dt: '||l_fonm_cvg_strt_dt, 10);
6900: --
6901: -- Getting eligibility profile compensation level by eligibility profile
6902: --
6903: ben_elp_cache.elpeet_getcacdets
6906: p_eligy_prfl_id => p_eligy_prfl_id,
6907: p_inst_set => l_inst_dets,
6908: p_inst_count => l_inst_count);
6909: --
6910: hr_utility.set_location('l_inst_count: '||l_inst_count, 10);
6911: if l_inst_count > 0 then
6912: --
6913: -- Operation
6914: -- =========
6920: -- then no problem.
6921: -- 5) If person eligible for any of the plans and exclude flag = 'N'
6922: -- then fail criteria.
6923: --
6924: hr_utility.set_location('Getting profiles',10);
6925: <
6926: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
6927: --
6928: l_rows_found := true;
6926: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
6927: --
6928: l_rows_found := true;
6929: --
6930: hr_utility.set_location('Getting results' || ben_manage_life_events.g_cache_person_prtn.last ,10);
6931:
6932: <
6933: for l_count in nvl(ben_manage_life_events.g_cache_person_prtn.first,0)..
6934: nvl(ben_manage_life_events.g_cache_person_prtn.last,-1) loop
6945: p_fonm_cvg_strt_dt => ben_manage_life_events.g_fonm_cvg_strt_dt,
6946: p_fonm_rt_strt_dt => ben_manage_life_events.g_fonm_rt_strt_dt,
6947: p_returned_date => l_date_to_use);
6948: --
6949: hr_utility.set_location( 'l_date_to_use ' || l_date_to_use , 99 ) ;
6950:
6951: -- 4204020 l_date_to_use := NVL(l_fonm_cvg_strt_dt,l_date_to_use);
6952: hr_utility.set_location( 'l_date_to_use ' || l_date_to_use , 99 ) ;
6953: --
6948: --
6949: hr_utility.set_location( 'l_date_to_use ' || l_date_to_use , 99 ) ;
6950:
6951: -- 4204020 l_date_to_use := NVL(l_fonm_cvg_strt_dt,l_date_to_use);
6952: hr_utility.set_location( 'l_date_to_use ' || l_date_to_use , 99 ) ;
6953: --
6954: -- If only check plans that are subject to COBRA.
6955: --
6956: l_continue := true;
6954: -- If only check plans that are subject to COBRA.
6955: --
6956: l_continue := true;
6957: --
6958: hr_utility.set_location('Getting cobra plans',10);
6959: --
6960: if l_inst_dets(l_insttorrw_num).only_pls_subj_cobra_flag = 'Y' then
6961: --
6962: open c1(ben_manage_life_events.g_cache_person_prtn(l_count).pl_id,
6965: fetch c1 into l_dummy;
6966: --
6967: if c1%notfound then
6968: --
6969: hr_utility.set_location('Cobra plans not found',10);
6970: l_continue := false;
6971: --
6972: end if;
6973: --
6976: end if;
6977: --
6978: if l_continue then
6979: --
6980: hr_utility.set_location('Cobra plans found',10);
6981: --
6982: if (l_date_to_use
6983: between ben_manage_life_events.
6984: g_cache_person_prtn(l_count).enrt_cvg_strt_dt
6985: and ben_manage_life_events.
6986: g_cache_person_prtn(l_count).enrt_cvg_thru_dt) then
6987: --
6988: l_found_ptip := true;
6989: hr_utility.set_location('cobra plans found',10);
6990: --
6991: if l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
6992: --
6993: hr_utility.set_location('Exclude flags = N Cobra plans found',10);
6989: hr_utility.set_location('cobra plans found',10);
6990: --
6991: if l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
6992: --
6993: hr_utility.set_location('Exclude flags = N Cobra plans found',10);
6994: l_ok := true;
6995: exit prfl;
6996: --
6997: end if;
6997: end if;
6998: --
6999: if l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
7000: --
7001: hr_utility.set_location('Exclude flags = Y Cobra plans found',10);
7002: l_ok := false;
7003: exit prfl;
7004: --
7005: end if;
7028: not l_ok then
7029: --
7030: g_inelg_rsn_cd := 'EET';
7031: fnd_message.set_name('BEN','BEN_92422_EET_ELIG_PRFL_FAIL');
7032: hr_utility.set_location('Criteria Failed: '||l_proc,20);
7033: raise g_criteria_failed;
7034: --
7035: end if;
7036: --
7033: raise g_criteria_failed;
7034: --
7035: end if;
7036: --
7037: hr_utility.set_location('Leaving :'||l_proc,20);
7038: --
7039: end check_elig_enrld_anthr_ptip;
7040: --
7041: -- --------------------------------------------------------------------------
7098: and reg.effective_end_date;
7099: --
7100: begin
7101: --
7102: hr_utility.set_location('Entering: '||l_proc, 10);
7103: --
7104: -- Getting eligibility profile compensation level by eligibility profile
7105: --
7106: ben_elp_cache.elpedt_getcacdets
7185: not l_ok then
7186: --
7187: g_inelg_rsn_cd := 'EDT';
7188: fnd_message.set_name('BEN','BEN_92423_EDT_ELIG_PRFL_FAIL');
7189: hr_utility.set_location('Criteria Failed: '||l_proc,20);
7190: raise g_criteria_failed;
7191: --
7192: end if;
7193: --
7190: raise g_criteria_failed;
7191: --
7192: end if;
7193: --
7194: hr_utility.set_location('Leaving :'||l_proc,20);
7195: --
7196: end check_elig_dpnt_cvrd_othr_ptip;
7197: --
7198: -- --------------------------------------------------------------------------
7234: and pdp.effective_end_date = hr_api.g_eot;
7235: --
7236: begin
7237: --
7238: hr_utility.set_location('Entering: '||l_proc, 10);
7239: --
7240: -- Getting eligibility profile compensation level by eligibility profile
7241: --
7242: ben_elp_cache.elpedg_getcacdets
7303: not l_ok then
7304: --
7305: g_inelg_rsn_cd := 'EDG';
7306: fnd_message.set_name('BEN','BEN_92424_EDG_ELIG_PRFL_FAIL');
7307: hr_utility.set_location('Criteria Failed: '||l_proc,20);
7308: raise g_criteria_failed;
7309: --
7310: end if;
7311: --
7308: raise g_criteria_failed;
7309: --
7310: end if;
7311: --
7312: hr_utility.set_location('Leaving :'||l_proc,20);
7313: --
7314: end check_elig_dpnt_cvrd_othr_pgm;
7315: --
7316: -- --------------------------------------------------------------------------
7358: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
7359: --
7360: begin
7361: --
7362: hr_utility.set_location('Entering: '||l_proc, 10);
7363: --
7364: -- Getting eligibility profile compensation level by eligibility profile
7365: --
7366: ben_elp_cache.elpecq_getcacdets
7406: not l_ok then
7407: --
7408: g_inelg_rsn_cd := 'ECQ';
7409: fnd_message.set_name('BEN','BEN_92425_ECQ_ELIG_PRFL_FAIL');
7410: hr_utility.set_location('Criteria Failed: '||l_proc,20);
7411: raise g_criteria_failed;
7412: --
7413: end if;
7414: --
7411: raise g_criteria_failed;
7412: --
7413: end if;
7414: --
7415: hr_utility.set_location('Leaving :'||l_proc,20);
7416: --
7417: end check_elig_cbr_quald_bnf;
7418: -- ----------------------------------------------------------------------------
7419: -- Disabled
7437: l_score_tab scoreTab;
7438: --
7439: begin
7440: --
7441: hr_utility.set_location('Entering : '||l_proc,10);
7442: --
7443: ben_elp_cache.elpeds_getdets
7444: (p_effective_date => p_effective_date
7445: ,p_eligy_prfl_id => p_eligy_prfl_id
7458: l_ok := nvl((nvl(p_dsbld_cd,'xxxx') = l_inst_set(l_ele_num).v230_val),FALSE);
7459: --
7460: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
7461: --
7462: hr_utility.set_location('Exclude Flag = N, Disabled Code = ' || p_dsbld_cd || ' found ', 20 );
7463: if p_score_compute_mode then
7464: if l_crit_passed is null then
7465: l_crit_passed := true;
7466: end if;
7475: end if;
7476: --
7477: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
7478: --
7479: hr_utility.set_location('Exclude Flag = Y, Disabled Code = ' || p_dsbld_cd || ' found ', 20 );
7480: l_ok := false;
7481: exit;
7482: --
7483: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
7511: then
7512: --
7513: g_inelg_rsn_cd := 'DSB';
7514: fnd_message.set_name('BEN','BEN_93080_DSBLD_PRFL_FAIL');
7515: hr_utility.set_location('Criteria Failed: '||l_proc,40);
7516: raise g_criteria_failed;
7517: --
7518: end if;
7519: end if;
7519: end if;
7520: --
7521: if p_score_compute_mode
7522: then
7523: hr_utility.set_location('count '||l_score_tab.count,20);
7524: write(p_profile_score_tab,l_score_tab);
7525: end if;
7526: hr_utility.set_location('Leaving: '||l_proc,50);
7527: --
7522: then
7523: hr_utility.set_location('count '||l_score_tab.count,20);
7524: write(p_profile_score_tab,l_score_tab);
7525: end if;
7526: hr_utility.set_location('Leaving: '||l_proc,50);
7527: --
7528: end check_dsbld_elig;
7529: --
7530: -- ----------------------------------------------------------------------------
7549: l_score_tab scoreTab;
7550: --
7551: begin
7552: --
7553: hr_utility.set_location('Entering : '||l_proc,10);
7554: --
7555: ben_elp_cache.elpetu_getdets
7556: (p_effective_date => p_effective_date
7557: ,p_eligy_prfl_id => p_eligy_prfl_id
7570: l_ok := nvl((nvl(p_tbco_use_flag,'xxxx') = l_inst_set(l_ele_num).v230_val),FALSE);
7571: --
7572: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
7573: --
7574: hr_utility.set_location('Exclude Flag = N, Tobacco Use = ' || p_tbco_use_flag || ' found ', 20 );
7575: if p_score_compute_mode then
7576: if l_crit_passed is null then
7577: l_crit_passed := true;
7578: end if;
7587: end if;
7588: --
7589: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
7590: --
7591: hr_utility.set_location('Exclude Flag = Y, Tobacco Use = ' || p_tbco_use_flag || ' found ', 20 );
7592: l_ok := false;
7593: exit;
7594: --
7595: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
7623: then
7624: --
7625: g_inelg_rsn_cd := 'ETU';
7626: fnd_message.set_name('BEN','BEN_93081_TBCO_USE_PRFL_FAIL');
7627: hr_utility.set_location('Criteria Failed: '||l_proc,40);
7628: raise g_criteria_failed;
7629: --
7630: end if;
7631: end if;
7631: end if;
7632: --
7633: if p_score_compute_mode
7634: then
7635: hr_utility.set_location('count '||l_score_tab.count,20);
7636: write(p_profile_score_tab,l_score_tab);
7637: end if;
7638: hr_utility.set_location('Leaving: '||l_proc,50);
7639: --
7634: then
7635: hr_utility.set_location('count '||l_score_tab.count,20);
7636: write(p_profile_score_tab,l_score_tab);
7637: end if;
7638: hr_utility.set_location('Leaving: '||l_proc,50);
7639: --
7640: end check_tbco_use_elig;
7641: --
7642: -- ----------------------------------------------------------------------------
7689: and pen.effective_end_date = l_eot;
7690: --
7691: begin
7692: --
7693: hr_utility.set_location('Entering: '||l_proc, 10);
7694: --
7695: ben_elp_cache.elpetc_getdets
7696: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
7697: ,p_eligy_prfl_id => p_eligy_prfl_id
7721: if nvl(l_ttl_cvg_vol,0)
7722: between nvl(l_inst_set(l_ele_num).num_val,0)
7723: and nvl(l_inst_set(l_ele_num).num_val1 - 1 , 999999999999999) -- 2431619
7724: then
7725: hr_utility.set_location(l_proc, 20);
7726: l_ok := true;
7727: exit;
7728: --
7729: elsif nvl(l_ttl_cvg_vol,0) = nvl(l_inst_set(l_ele_num).num_val1, 999999999999999) then -- bug 2431619
7734: p_business_group_id ) ;
7735: fetch c_pen_found into l_dummy;
7736: if c_pen_found%found then
7737: --
7738: hr_utility.set_location(l_proc, 30);
7739: close c_pen_found;
7740: l_ok := true;
7741: exit;
7742: --
7741: exit;
7742: --
7743: else
7744: --
7745: hr_utility.set_location(l_proc, 40);
7746: close c_pen_found;
7747: l_ok := false;
7748: --
7749: end if;
7749: end if;
7750: -- -- end 2431619
7751: else
7752: --
7753: hr_utility.set_location(l_proc, 50);
7754: l_ok := false;
7755: --
7756: end if;
7757: --
7766: then
7767: --
7768: g_inelg_rsn_cd := 'ETC';
7769: fnd_message.set_name('BEN','BEN_93082_TTLCVGVOL_PRFL_FAIL');
7770: hr_utility.set_location('Criteria Failed: '||l_proc,60);
7771: raise g_criteria_failed;
7772: --
7773: end if;
7774: --
7771: raise g_criteria_failed;
7772: --
7773: end if;
7774: --
7775: hr_utility.set_location('Leaving: '||l_proc,50);
7776: --
7777: end check_ttl_cvg_vol_elig;
7778: --
7779: -- ----------------------------------------------------------------------------
7826: and pen.effective_end_date = l_eot;
7827: --
7828: begin
7829: --
7830: hr_utility.set_location('Entering: '||l_proc, 10);
7831: --
7832: ben_elp_cache.elpetp_getdets
7833: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
7834: ,p_eligy_prfl_id => p_eligy_prfl_id
7853: for i in l_inst_set.first .. l_inst_set.last loop
7854: --
7855: l_rows_found := true;
7856: --
7857: hr_utility.set_location('l_ttl_prtt ' || l_ttl_prtt || ' num_val1 ' || l_inst_set(l_ele_num).num_val1, -729);
7858: if nvl(l_ttl_prtt,0)
7859: between nvl(l_inst_set(l_ele_num).num_val,0)
7860: and nvl(l_inst_set(l_ele_num).num_val1 - 1 , 999999999999999) -- bug 2431619
7861: then
7858: if nvl(l_ttl_prtt,0)
7859: between nvl(l_inst_set(l_ele_num).num_val,0)
7860: and nvl(l_inst_set(l_ele_num).num_val1 - 1 , 999999999999999) -- bug 2431619
7861: then
7862: hr_utility.set_location(l_proc, 20);
7863: l_ok := true;
7864: exit;
7865: --
7866: elsif nvl(l_ttl_prtt,0) = nvl(l_inst_set(l_ele_num).num_val1, 999999999999999) then -- bug 2431619
7871: p_business_group_id ) ;
7872: fetch c_pen_found into l_dummy;
7873: if c_pen_found%found then
7874: --
7875: hr_utility.set_location(l_proc, 30);
7876: close c_pen_found;
7877: l_ok := true;
7878: exit;
7879: --
7878: exit;
7879: --
7880: else
7881: --
7882: hr_utility.set_location(l_proc, 40);
7883: close c_pen_found;
7884: l_ok := false;
7885: --
7886: end if;
7886: end if;
7887: -- -- end 2431619
7888: else
7889: --
7890: hr_utility.set_location(l_proc, 50);
7891: l_ok := false;
7892: --
7893: end if;
7894: --
7903: then
7904: --
7905: g_inelg_rsn_cd := 'ETP';
7906: fnd_message.set_name('BEN','BEN_93083_TTL_PRTT_PRFL_FAIL');
7907: hr_utility.set_location('Criteria Failed: '||l_proc,60);
7908: raise g_criteria_failed;
7909: --
7910: end if;
7911: --
7908: raise g_criteria_failed;
7909: --
7910: end if;
7911: --
7912: hr_utility.set_location('Leaving: '||l_proc,50);
7913: --
7914: end check_ttl_prtt_elig;
7915: --
7916: -- ----------------------------------------------------------------------------
7951: and pen.effective_end_date = l_eot;
7952: --
7953: begin
7954: --
7955: hr_utility.set_location('Entering: '||l_proc, 10);
7956: --
7957: ben_elp_cache.elpeop_getdets
7958: (p_effective_date => p_effective_date
7959: ,p_eligy_prfl_id => p_eligy_prfl_id
7986: --
7987: --
7988: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
7989: --
7990: hr_utility.set_location('Exclude Flag = N, Participation in Plan id = ' || l_inst_set(l_ele_num).num_val || ' found ', 20 );
7991: exit;
7992: --
7993: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
7994: --
7991: exit;
7992: --
7993: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
7994: --
7995: hr_utility.set_location('Exclude Flag = Y, Participation in Plan id = ' || l_inst_set(l_ele_num).num_val || ' found ', 20 );
7996: l_ok := false;
7997: exit;
7998: --
7999: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
8013: then
8014: --
8015: g_inelg_rsn_cd := 'EOP';
8016: fnd_message.set_name('BEN','BEN_93084_ANTHR_PL_PRFL_FAIL');
8017: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8018: raise g_criteria_failed;
8019: --
8020: end if;
8021: --
8018: raise g_criteria_failed;
8019: --
8020: end if;
8021: --
8022: hr_utility.set_location('Leaving: '||l_proc,50);
8023: --
8024: end check_anthr_pl_elig;
8025: --
8026: -- ----------------------------------------------------------------------------
8069:
8070: --
8071: begin
8072: --
8073: hr_utility.set_location('Entering: '||l_proc, 10);
8074: --
8075: ben_elp_cache.elpehc_getdets
8076: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
8077: ,p_eligy_prfl_id => p_eligy_prfl_id
8105: --
8106: --
8107: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
8108: --
8109: hr_utility.set_location('Exclude Flag = N, Health Coverage in OIPL id = ' || l_inst_set(l_ele_num).num_val1 || ' found ', 20 );
8110: exit;
8111: --
8112: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8113: --
8110: exit;
8111: --
8112: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8113: --
8114: hr_utility.set_location('Exclude Flag = Y, Health Coverage in OIPL id = ' || l_inst_set(l_ele_num).num_val1 || ' found ', 20 );
8115: l_ok := false;
8116: exit;
8117: --
8118: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
8132: then
8133: --
8134: g_inelg_rsn_cd := 'EHC';
8135: fnd_message.set_name('BEN','BEN_93085_HLTH_CVG_PRFL_FAIL');
8136: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8137: raise g_criteria_failed;
8138: --
8139: end if;
8140: --
8137: raise g_criteria_failed;
8138: --
8139: end if;
8140: --
8141: hr_utility.set_location('Leaving: '||l_proc,50);
8142: --
8143: end check_hlth_cvg_elig;
8144: --
8145: -- ----------------------------------------------------------------------------
8185: and nvl(cmp.effective_date_to, cp_effective_date) ;
8186: --
8187: begin
8188: --
8189: hr_utility.set_location('Entering: '||l_proc, 10);
8190: --
8191: ben_elp_cache.elpecy_getdets
8192: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
8193: ,p_eligy_prfl_id => p_eligy_prfl_id
8230: --
8231: --
8232: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
8233: --
8234: hr_utility.set_location('Exclude Flag = N, Competence ' || l_inst_set(l_ele_num).num_val || ' and Rating level ' || l_inst_set(l_ele_num).num_val1 || ' found ', 20 );
8235: if p_score_compute_mode then
8236: if l_crit_passed is null then
8237: l_crit_passed := true;
8238: end if;
8247: end if;
8248: --
8249: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8250: --
8251: hr_utility.set_location('Exclude Flag = Y, Competence ' || l_inst_set(l_ele_num).num_val || ' and Rating level ' || l_inst_set(l_ele_num).num_val1 || ' found ', 20 );
8252: l_ok := false;
8253: exit;
8254: --
8255: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
8279: then
8280: --
8281: g_inelg_rsn_cd := 'ECY';
8282: fnd_message.set_name('BEN','BEN_93086_COMPTNCY_PRFL_FAIL');
8283: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8284: raise g_criteria_failed;
8285: --
8286: end if;
8287: end if;
8287: end if;
8288: --
8289: if p_score_compute_mode
8290: then
8291: hr_utility.set_location('count '||l_score_tab.count,20);
8292: write(p_profile_score_tab,l_score_tab);
8293: end if;
8294: hr_utility.set_location('Leaving: '||l_proc,50);
8295: --
8290: then
8291: hr_utility.set_location('count '||l_score_tab.count,20);
8292: write(p_profile_score_tab,l_score_tab);
8293: end if;
8294: hr_utility.set_location('Leaving: '||l_proc,50);
8295: --
8296: end check_comptncy_elig;
8297: --
8298: -- ----------------------------------------------------------------------------
8372: ORDER BY ppr.review_date desc;
8373: --
8374: begin
8375: --
8376: hr_utility.set_location('Entering: '||l_proc, 10);
8377: --
8378: if p_perf_rtng_cd is null then
8379: ben_elp_cache.elpepr_getdets
8380: (p_effective_date => p_effective_date
8477: end if;
8478: --
8479: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
8480: --
8481: hr_utility.set_location('Exclude Flag = N, Performance Rating ' || l_inst_set(l_ele_num).num_val || ' and Rating level ' || l_inst_set(l_ele_num).num_val1 || ' found ', 20 );
8482: if p_score_compute_mode then
8483: if l_crit_passed is null then
8484: l_crit_passed := true;
8485: end if;
8494: end if;
8495: --
8496: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8497: --
8498: hr_utility.set_location('Exclude Flag = Y, Performance Rating ' || l_inst_set(l_ele_num).num_val || ' and Rating level ' || l_inst_set(l_ele_num).num_val1 || ' found ', 20 );
8499: l_ok := false;
8500: exit;
8501: --
8502: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
8525: then
8526: --
8527: g_inelg_rsn_cd := 'ERG';
8528: fnd_message.set_name('BEN','BEN_93108_PERF_RTNG_PRFL_FAIL');
8529: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8530: raise g_criteria_failed;
8531: --
8532: end if;
8533: end if;
8533: end if;
8534: --
8535: if p_score_compute_mode
8536: then
8537: hr_utility.set_location('count '||l_score_tab.count,20);
8538: write(p_profile_score_tab,l_score_tab);
8539: end if;
8540: hr_utility.set_location('Leaving: '||l_proc,50);
8541: --
8536: then
8537: hr_utility.set_location('count '||l_score_tab.count,20);
8538: write(p_profile_score_tab,l_score_tab);
8539: end if;
8540: hr_utility.set_location('Leaving: '||l_proc,50);
8541: --
8542: end check_perf_rtng_elig;
8543: --
8544: -- --------------------------------------------------
8655: end;
8656: --
8657: begin
8658: --
8659: hr_utility.set_location('Entering: '||l_proc, 10);
8660: --
8661: ben_elp_cache.elpeqg_getdets
8662: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
8663: ,p_eligy_prfl_id => p_eligy_prfl_id
8736: ,p_max => nvl(l_max_val,0)
8737: ,p_code => l_inst_set(l_ele_num).v230_val
8738: ,p_min_qualifier => l_min_qualifier
8739: ,p_max_qualifier => l_max_qualifier );
8740: hr_utility.set_location('ACE l_min_qualifier = ' || l_min_qualifier, 9999);
8741: hr_utility.set_location('ACE l_max_qualifier = ' || l_max_qualifier, 9999);
8742:
8743: --
8744: if l_inst_set(l_ele_num).v230_val = 'ABV' then
8737: ,p_code => l_inst_set(l_ele_num).v230_val
8738: ,p_min_qualifier => l_min_qualifier
8739: ,p_max_qualifier => l_max_qualifier );
8740: hr_utility.set_location('ACE l_min_qualifier = ' || l_min_qualifier, 9999);
8741: hr_utility.set_location('ACE l_max_qualifier = ' || l_max_qualifier, 9999);
8742:
8743: --
8744: if l_inst_set(l_ele_num).v230_val = 'ABV' then
8745: l_in_quartile := l_ann_sal > nvl(l_max_val,0);
8802: then
8803: --
8804: g_inelg_rsn_cd := 'EQG';
8805: fnd_message.set_name('BEN','BEN_93107_QUA_IN_GR_PRFL_FAIL');
8806: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8807: raise g_criteria_failed;
8808: --
8809: end if;
8810: end if;
8810: end if;
8811: --
8812: if p_score_compute_mode
8813: then
8814: hr_utility.set_location('count '||l_score_tab.count,20);
8815: write(p_profile_score_tab,l_score_tab);
8816: end if;
8817: hr_utility.set_location('Leaving: '||l_proc,50);
8818: --
8813: then
8814: hr_utility.set_location('count '||l_score_tab.count,20);
8815: write(p_profile_score_tab,l_score_tab);
8816: end if;
8817: hr_utility.set_location('Leaving: '||l_proc,50);
8818: --
8819: end check_qua_in_gr_elig;
8820: --
8821: -- Public Function
9102: l_proc varchar2(30) := 'assign_overriden_values';
9103:
9104: begin
9105:
9106: hr_utility.set_location('Entering :'||l_proc,10);
9107:
9108: if not (pqh_popl_criteria_ovrrd.g_criteria_count>0) then
9109: hr_utility.set_location('No overridden values',10);
9110: hr_utility.set_location('Leaving :'||l_proc,10);
9105:
9106: hr_utility.set_location('Entering :'||l_proc,10);
9107:
9108: if not (pqh_popl_criteria_ovrrd.g_criteria_count>0) then
9109: hr_utility.set_location('No overridden values',10);
9110: hr_utility.set_location('Leaving :'||l_proc,10);
9111: return;
9112: end if;
9113:
9106: hr_utility.set_location('Entering :'||l_proc,10);
9107:
9108: if not (pqh_popl_criteria_ovrrd.g_criteria_count>0) then
9109: hr_utility.set_location('No overridden values',10);
9110: hr_utility.set_location('Leaving :'||l_proc,10);
9111: return;
9112: end if;
9113:
9114: hr_utility.set_location('Overridden count '||pqh_popl_criteria_ovrrd.g_criteria_override_val.count,10);
9110: hr_utility.set_location('Leaving :'||l_proc,10);
9111: return;
9112: end if;
9113:
9114: hr_utility.set_location('Overridden count '||pqh_popl_criteria_ovrrd.g_criteria_override_val.count,10);
9115:
9116: for i in 1..pqh_popl_criteria_ovrrd.g_criteria_override_val.count
9117: loop
9118: l_crit_ovrrd_val_rec := pqh_popl_criteria_ovrrd.g_criteria_override_val(i);
9115:
9116: for i in 1..pqh_popl_criteria_ovrrd.g_criteria_override_val.count
9117: loop
9118: l_crit_ovrrd_val_rec := pqh_popl_criteria_ovrrd.g_criteria_override_val(i);
9119: hr_utility.set_location('Short code '||l_crit_ovrrd_val_rec.criteria_short_code,10);
9120: if l_crit_ovrrd_val_rec.criteria_short_code is null then
9121: exit;
9122: end if;
9123:
9203: fetch check_gen_criteria into l_dummy;
9204: if check_gen_criteria%found then
9205: l_crit_ovrrd_val_tab(l_crit_ovrrd_val_tab.count+1) := l_crit_ovrrd_val_rec;
9206: else
9207: hr_utility.set_location('Unknown criteria '||l_crit_ovrrd_val_rec.criteria_short_code,10);
9208: end if;
9209: close check_gen_criteria;
9210:
9211: end if;
9211: end if;
9212:
9213: end loop;
9214:
9215: hr_utility.set_location('Leaving :'||l_proc,10);
9216:
9217: end assign_overriden_values;
9218:
9219: begin
9217: end assign_overriden_values;
9218:
9219: begin
9220: --
9221: hr_utility.set_location('Entering :'||l_proc,10);
9222: --
9223: -- intialize the global
9224: --
9225: l_fonm_cvg_strt_dt := null ;
9238: --
9239: end if;
9240: --
9241: end if;
9242: hr_utility.set_location('l_fonm_cvg_strt_dt :'||l_fonm_cvg_strt_dt,10);
9243: l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
9244: -- Start of Bug 3520054
9245: if p_plip_id is not null then
9246: --
9243: l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
9244: -- Start of Bug 3520054
9245: if p_plip_id is not null then
9246: --
9247: hr_utility.set_location('PLIP_ID '||p_plip_id,11);
9248: --
9249: ben_comp_object.get_object(p_plip_id => p_plip_id
9250: ,p_rec => l_plip_rec);
9251: --
9250: ,p_rec => l_plip_rec);
9251: --
9252: elsif p_ptip_id is not null then
9253: --
9254: hr_utility.set_location('PTIP_ID '||p_ptip_id,12);
9255: --
9256: ben_comp_object.get_object(p_ptip_id => p_ptip_id
9257: ,p_rec => l_ptip_rec);
9258: --
9258: --
9259: -- End of Bug 3520054
9260: elsif p_pl_id is not null then
9261: --
9262: hr_utility.set_location('PL_ID '||p_pl_id,14);
9263: --
9264: ben_comp_object.get_object(p_pl_id => p_pl_id
9265: ,p_rec => l_pl_rec);
9266: --
9265: ,p_rec => l_pl_rec);
9266: --
9267: elsif p_oipl_id is not null then
9268: --
9269: hr_utility.set_location('OIPL_ID '||p_oipl_id,16);
9270: --
9271: ben_comp_object.get_object(p_oipl_id => p_oipl_id
9272: ,p_rec => l_oipl_rec);
9273:
9323: l_eligprof_dets.extend(p_eligprof_tab.count);
9324: for i in 1..p_eligprof_tab.count
9325: loop
9326:
9327: hr_utility.set_location('prfl count'||p_eligprof_tab.count,99);
9328: hr_utility.set_location('prfl id'||p_eligprof_tab(i).eligy_prfl_id,99);
9329: open c_elgy_prfl(p_eligprof_tab(i).eligy_prfl_id,
9330: nvl(l_fonm_cvg_strt_dt,l_effective_date));
9331: fetch c_elgy_prfl into l_eligprof_dets(i);
9324: for i in 1..p_eligprof_tab.count
9325: loop
9326:
9327: hr_utility.set_location('prfl count'||p_eligprof_tab.count,99);
9328: hr_utility.set_location('prfl id'||p_eligprof_tab(i).eligy_prfl_id,99);
9329: open c_elgy_prfl(p_eligprof_tab(i).eligy_prfl_id,
9330: nvl(l_fonm_cvg_strt_dt,l_effective_date));
9331: fetch c_elgy_prfl into l_eligprof_dets(i);
9332: close c_elgy_prfl;
9337: end loop;
9338:
9339: end if;
9340:
9341: hr_utility.set_location(l_proc||' After Cache call ',46);
9342: --
9343: -- Filter out non CAGR profiles in collective agreement mode
9344: --
9345: -- Get the environment details
9372: fnd_message.set_name('BEN','BEN_92844_NOCAGRELP_NOT_ELIG');
9373: return false;
9374: --
9375: end if;
9376: hr_utility.set_location('l_cagrelig_cnt = '|| l_cagrelig_cnt,1687);
9377: --
9378: l_eligprof_dets.delete;
9379: l_eligprof_dets := l_tmpelp_dets;
9380: --
9400: --
9401: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9402: between l_per_rec.effective_start_date and l_per_rec.effective_end_date then
9403:
9404: hr_utility.set_location('clearing cache'||nvl(l_fonm_cvg_strt_dt,l_effective_date) ,10);
9405: hr_utility.set_location('cache start'||l_per_rec.effective_start_date ,10);
9406: hr_utility.set_location('cache end'||l_per_rec.effective_end_date ,10);
9407:
9408: ben_use_cvg_rt_date.fonm_clear_down_cache;
9401: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9402: between l_per_rec.effective_start_date and l_per_rec.effective_end_date then
9403:
9404: hr_utility.set_location('clearing cache'||nvl(l_fonm_cvg_strt_dt,l_effective_date) ,10);
9405: hr_utility.set_location('cache start'||l_per_rec.effective_start_date ,10);
9406: hr_utility.set_location('cache end'||l_per_rec.effective_end_date ,10);
9407:
9408: ben_use_cvg_rt_date.fonm_clear_down_cache;
9409: --
9402: between l_per_rec.effective_start_date and l_per_rec.effective_end_date then
9403:
9404: hr_utility.set_location('clearing cache'||nvl(l_fonm_cvg_strt_dt,l_effective_date) ,10);
9405: hr_utility.set_location('cache start'||l_per_rec.effective_start_date ,10);
9406: hr_utility.set_location('cache end'||l_per_rec.effective_end_date ,10);
9407:
9408: ben_use_cvg_rt_date.fonm_clear_down_cache;
9409: --
9410: ben_person_object.get_object(p_person_id => p_person_id,
9412: --
9413: ben_person_object.get_object(p_person_id => p_person_id,
9414: p_rec => l_per_rec);
9415:
9416: hr_utility.set_location('nw cache start'||l_per_rec.effective_start_date ,10);
9417: hr_utility.set_location('nw cache end'||l_per_rec.effective_end_date ,10);
9418: else
9419:
9420: -- Bug 6399423
9413: ben_person_object.get_object(p_person_id => p_person_id,
9414: p_rec => l_per_rec);
9415:
9416: hr_utility.set_location('nw cache start'||l_per_rec.effective_start_date ,10);
9417: hr_utility.set_location('nw cache end'||l_per_rec.effective_end_date ,10);
9418: else
9419:
9420: -- Bug 6399423
9421: /* ben_person_object.get_object(p_person_id => p_person_id,
9425: ben_person_object.get_object(p_person_id => p_person_id,
9426: p_rec => l_ass_rec1);
9427: -- Bug 6399423
9428:
9429: hr_utility.set_location('cache asg start'||l_ass_rec1.effective_start_date ,10);
9430: hr_utility.set_location('cache asg end'||l_ass_rec1.effective_end_date ,10);
9431:
9432: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9433: between l_ass_rec1.effective_start_date and l_ass_rec1.effective_end_date then
9426: p_rec => l_ass_rec1);
9427: -- Bug 6399423
9428:
9429: hr_utility.set_location('cache asg start'||l_ass_rec1.effective_start_date ,10);
9430: hr_utility.set_location('cache asg end'||l_ass_rec1.effective_end_date ,10);
9431:
9432: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9433: between l_ass_rec1.effective_start_date and l_ass_rec1.effective_end_date then
9434:
9431:
9432: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9433: between l_ass_rec1.effective_start_date and l_ass_rec1.effective_end_date then
9434:
9435: hr_utility.set_location('clearing asg cache'||nvl(l_fonm_cvg_strt_dt,l_effective_date) ,10);
9436: ben_use_cvg_rt_date.fonm_clear_down_cache;
9437:
9438: end if ;
9439: end if ;
9450: end if;
9451: end loop;
9452: --
9453: if g_score_compute_mode then
9454: hr_utility.set_location('in score compute mode',10);
9455: end if;
9456: if g_trk_scr_for_inelg_flag then
9457: hr_utility.set_location('track score for inelig flag on',10);
9458: end if;
9453: if g_score_compute_mode then
9454: hr_utility.set_location('in score compute mode',10);
9455: end if;
9456: if g_trk_scr_for_inelg_flag then
9457: hr_utility.set_location('track score for inelig flag on',10);
9458: end if;
9459:
9460: g_per_eligible := null;
9461: --
9464: l_found_profile := 'Y';
9465: --
9466: -- if eligibility profiles do exists for program, plan or option:
9467: --
9468: hr_utility.set_location('ELigibility Profile ID = '||l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,10);
9469: hr_utility.set_location('Number of profiles = '||l_eligprof_dets.count,10);
9470: begin
9471: if g_score_compute_mode then
9472: if g_per_eligible is not null then --per already found elig/inelig
9465: --
9466: -- if eligibility profiles do exists for program, plan or option:
9467: --
9468: hr_utility.set_location('ELigibility Profile ID = '||l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,10);
9469: hr_utility.set_location('Number of profiles = '||l_eligprof_dets.count,10);
9470: begin
9471: if g_score_compute_mode then
9472: if g_per_eligible is not null then --per already found elig/inelig
9473: if nvl(l_eligprof_dets(l_elptorrw_num).compute_score_flag,'N')='N' then
9470: begin
9471: if g_score_compute_mode then
9472: if g_per_eligible is not null then --per already found elig/inelig
9473: if nvl(l_eligprof_dets(l_elptorrw_num).compute_score_flag,'N')='N' then
9474: hr_utility.set_location('skipping prof '||
9475: l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,45);
9476: raise g_skip_profile;
9477: end if;
9478: end if;
9479: end if;
9480: --
9481: l_profile_score_tab.delete;
9482: l_prof_score_compute := (l_eligprof_dets(l_elptorrw_num).compute_score_flag = 'Y');
9483: hr_utility.set_location(l_proc||' St ELP loop ',46);
9484: --
9485: g_inelg_rsn_cd := null;
9486: --
9487: if l_eligprof_dets(l_elptorrw_num).elig_per_typ_flag = 'Y' then
9550: elsif l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd = 'ANY' then
9551: --
9552: -- First assignment only
9553: --
9554: hr_utility.set_location('Getting all assignments',10);
9555: -- 4958846 ssarkar for irec
9556: ben_env_object.get(p_rec => l_env_rec);
9557: benutils.get_batch_parameters(p_benefit_action_id => l_env_rec.benefit_action_id
9558: ,p_rec => l_benmngle_parm_rec);
9556: ben_env_object.get(p_rec => l_env_rec);
9557: benutils.get_batch_parameters(p_benefit_action_id => l_env_rec.benefit_action_id
9558: ,p_rec => l_benmngle_parm_rec);
9559:
9560: hr_utility.set_location('l_benmngle_parm_rec.mode_cd :' || l_benmngle_parm_rec.mode_cd,99);
9561:
9562: if l_benmngle_parm_rec.mode_cd = 'I' then
9563:
9564:
9565: l_appass_rec.delete;
9566:
9567: l_appass_rec(1) := ben_manage_life_events.g_irec_ass_rec ;
9568:
9569: hr_utility.set_location(' p_assignment_id :' || l_appass_rec(1).assignment_id,99);
9570: hr_utility.set_location(' location_id :' || l_appass_rec(1).location_id,99);
9571: hr_utility.set_location(' p_effective_date :' || p_effective_date,99);
9572:
9573:
9566:
9567: l_appass_rec(1) := ben_manage_life_events.g_irec_ass_rec ;
9568:
9569: hr_utility.set_location(' p_assignment_id :' || l_appass_rec(1).assignment_id,99);
9570: hr_utility.set_location(' location_id :' || l_appass_rec(1).location_id,99);
9571: hr_utility.set_location(' p_effective_date :' || p_effective_date,99);
9572:
9573:
9574: else
9567: l_appass_rec(1) := ben_manage_life_events.g_irec_ass_rec ;
9568:
9569: hr_utility.set_location(' p_assignment_id :' || l_appass_rec(1).assignment_id,99);
9570: hr_utility.set_location(' location_id :' || l_appass_rec(1).location_id,99);
9571: hr_utility.set_location(' p_effective_date :' || p_effective_date,99);
9572:
9573:
9574: else
9575: ben_person_object.get_allass_object(p_person_id => p_person_id,
9581: -- Initialize first record so that one time test works
9582: --
9583: l_appass_rec(1).person_id := p_person_id;
9584: --
9585: hr_utility.set_location('NO RECS',10);
9586: --
9587: end if;
9588: --
9589: l_loop_count := l_appass_rec.count;
9588: --
9589: l_loop_count := l_appass_rec.count;
9590: --
9591: l_ass_rec := l_appass_rec(1);
9592: hr_utility.set_location('NUMRECS'||l_appass_rec.count,10);
9593: hr_utility.set_location('GRADE'||l_appass_rec(1).grade_id,10);
9594: elsif l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd = 'ETB' then
9595: --
9596: -- Employee then Benefits assignment only
9589: l_loop_count := l_appass_rec.count;
9590: --
9591: l_ass_rec := l_appass_rec(1);
9592: hr_utility.set_location('NUMRECS'||l_appass_rec.count,10);
9593: hr_utility.set_location('GRADE'||l_appass_rec(1).grade_id,10);
9594: elsif l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd = 'ETB' then
9595: --
9596: -- Employee then Benefits assignment only
9597: --
9691: --
9692: -- At this stage the assignment record would have been retreived
9693: -- as per the assignment type code given in the ELPRO for this person
9694: --
9695: hr_utility.set_location(l_proc || ' Start Check Asst Type' , 10) ;
9696: --
9697: if (l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd <> 'ANY' and
9698: l_ass_rec.assignment_id is null) then
9699: --
9698: l_ass_rec.assignment_id is null) then
9699: --
9700: g_inelg_rsn_cd := 'AST';
9701: fnd_message.set_name('BEN','BEN_93193_ASGN_TYPE_PRFL_FAIL');
9702: hr_utility.set_location('Criteria Failed: Assignment Type',20);
9703: raise g_criteria_failed;
9704: --
9705: end if;
9706: --
9703: raise g_criteria_failed;
9704: --
9705: end if;
9706: --
9707: hr_utility.set_location(l_proc || ' End Check Asst Type' , 10) ;
9708: --
9709: -- end fix 2431776 - 27-AUG-2002 - hnarayan
9710: --
9711: -- if assignment_id is passed in, make sure eligibility profile's
9730: if not l_asg_found then
9731: --
9732: g_inelg_rsn_cd := 'AST';
9733: fnd_message.set_name('BEN','BEN_93193_ASGN_TYPE_PRFL_FAIL');
9734: hr_utility.set_location('Criteria Failed: Assignment Type',20);
9735: raise g_criteria_failed;
9736: --
9737: end if;
9738: end if;
9737: end if;
9738: end if;
9739: end if; --p_person_id not null
9740:
9741: hr_utility.set_location(l_proc||' Asg ELPs ',46);
9742: for l_count in 1..l_loop_count loop
9743: --
9744: begin
9745: --
9753: end if;
9754: --
9755: assign_overriden_values(l_per_rec,l_ass_rec);
9756: --
9757: hr_utility.set_location(' here ',999);
9758: if l_eligprof_dets(l_elptorrw_num).elig_ee_stat_flag = 'Y' then
9759: check_ee_stat_elig
9760: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
9761: eligy_prfl_id,
9963: p_abs_attd_reason_id => l_absence_attendance_reason_id,
9964: p_effective_date => nvl(l_fonm_cvg_strt_dt,l_effective_date));
9965: end if;
9966: --
9967: hr_utility.set_location(' here 2 ',999);
9968: if l_ass_rec.soft_coding_keyflex_id is not null then
9969: --
9970: ben_person_object.get_object
9971: (p_soft_coding_keyflex_id => l_ass_rec.soft_coding_keyflex_id,
10017:
10018: end if ;
10019:
10020: if l_eligprof_dets(l_elptorrw_num).eligy_prfl_rl_flag = 'Y' then
10021: hr_utility.set_location(l_proc||' Chk Rule',48);
10022:
10023: -- Bug# 2424041 pl_typ_id context is not being passed to the formula
10024: if p_pgm_id is not null then
10025: -- pl_typ_id is not available at pgm level
10070: --
10071: exception
10072: --
10073: when g_skip_profile then
10074: hr_utility.set_location('skip 1',20);
10075: raise g_skip_profile;
10076: when g_criteria_failed then
10077: --
10078: -- Handle case where we want an error if we are dealing with
10101: end if ;
10102: --
10103: end if;
10104: --
10105: hr_utility.set_location(l_proc||' Crit Failed ',48);
10106: when others then
10107: --
10108: -- Catch any unhandled exceptions
10109: --
10106: when others then
10107: --
10108: -- Catch any unhandled exceptions
10109: --
10110: hr_utility.set_location(l_proc||' Crit Failed ',49);
10111: return false;
10112: --
10113: end;
10114: --
10116: --
10117: -- Check these criteria only if the person is an employee who is
10118: -- not terminated:
10119: --
10120: hr_utility.set_location(' here 3 ',999);
10121: if p_person_id is not null and
10122: l_typ_rec(1).system_person_type = 'EMP' and
10123: nvl(p_asg_status,hr_api.g_varchar2) <> 'TERM_ASSIGN' then
10124:
10121: if p_person_id is not null and
10122: l_typ_rec(1).system_person_type = 'EMP' and
10123: nvl(p_asg_status,hr_api.g_varchar2) <> 'TERM_ASSIGN' then
10124:
10125: hr_utility.set_location(l_proc||' Not TERM ASSIGN',48);
10126:
10127: if l_eligprof_dets(l_elptorrw_num).elig_pct_fl_tm_flag = 'Y' then
10128: --
10129: -- for Vapros, get the values from elig_per and elig_per_opt ??
10167: end if;
10168: end if;
10169: --
10170: --
10171: hr_utility.set_location(' here 4 ',999);
10172: if l_eligprof_dets(l_elptorrw_num).elig_los_flag = 'Y' then
10173: check_los_elig
10174: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10175: eligy_prfl_id,
10215: p_plip_id => p_plip_id,
10216: p_opt_id => p_opt_id);
10217: end if;
10218: --
10219: hr_utility.set_location(l_proc||' check_comp_level_elig',50);
10220: if l_eligprof_dets(l_elptorrw_num).elig_comp_lvl_flag = 'Y' then
10221: check_comp_level_elig
10222: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10223: eligy_prfl_id,
10275: p_plip_id => p_plip_id,
10276: p_opt_id => p_opt_id);
10277: end if;
10278: --
10279: hr_utility.set_location(l_proc||' check_person_balance',52);
10280: if l_eligprof_dets(l_elptorrw_num).elig_comp_lvl_flag = 'Y' then
10281: check_person_balance
10282: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10283: eligy_prfl_id,
10298: p_opt_id => p_opt_id);
10299: end if;
10300: --
10301: if l_eligprof_dets(l_elptorrw_num).elig_hrs_wkd_flag = 'Y' then
10302: hr_utility.set_location(l_proc||' check_hrs_wkd_ben_bal',54);
10303:
10304: if p_comp_obj_mode and
10305: p_eval_typ <> 'E' then
10306: --
10363: p_per_hrs_wkd => l_hrs_wkd_val);
10364: --
10365: end if;
10366: --
10367: hr_utility.set_location(l_proc||' check_age_elig',56);
10368: if l_eligprof_dets(l_elptorrw_num).elig_age_flag = 'Y' then
10369: check_age_elig
10370: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10371: eligy_prfl_id,
10387: p_plip_id => p_plip_id,
10388: p_opt_id => p_opt_id);
10389: end if;
10390: --
10391: hr_utility.set_location(l_proc||' Zip Code',48);
10392: if l_eligprof_dets(l_elptorrw_num).elig_pstl_cd_flag = 'Y' then
10393: check_zip_code_rng_elig
10394: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10395: eligy_prfl_id,
10399: p_profile_score_tab => l_profile_score_tab,
10400: p_postal_code => l_postal_code,
10401: p_effective_date => nvl(l_fonm_cvg_strt_dt,l_effective_date));
10402: end if;
10403: hr_utility.set_location(l_proc||' check_service_a',60);
10404: --
10405: if l_eligprof_dets(l_elptorrw_num).elig_svc_area_flag = 'Y' then
10406: check_service_area_elig
10407: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10413: p_postal_code => l_postal_code,
10414: p_effective_date => nvl(l_fonm_cvg_strt_dt,l_effective_date));
10415: end if;
10416: --
10417: hr_utility.set_location(l_proc||' check_benefits_grp_elig',62);
10418: if l_eligprof_dets(l_elptorrw_num).elig_benfts_grp_flag = 'Y' then
10419: check_benefits_grp_elig
10420: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10421: eligy_prfl_id,
10581: p_effective_date => nvl(l_fonm_cvg_strt_dt,l_effective_date));
10582: end if;
10583: --
10584:
10585: hr_utility.set_location(l_proc||' cobra',64);
10586: if p_par_pgm_id is not null or
10587: p_eval_typ <> 'E' then
10588: --
10589: -- We are dealing with a program hierarchy
10737: p_effective_date => p_effective_date);
10738: end if;
10739: --
10740: end if;
10741: hr_utility.set_location(l_proc||' Done cobra',66);
10742: hr_utility.set_location(l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,99);
10743: --
10744: ben_cagr_check_eligibility.check_cagr_elig_profiles
10745: (p_eligprof_dets => l_eligprof_dets(l_elptorrw_num)
10738: end if;
10739: --
10740: end if;
10741: hr_utility.set_location(l_proc||' Done cobra',66);
10742: hr_utility.set_location(l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,99);
10743: --
10744: ben_cagr_check_eligibility.check_cagr_elig_profiles
10745: (p_eligprof_dets => l_eligprof_dets(l_elptorrw_num)
10746: ,p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
10789: end if;
10790: exception
10791: --
10792: when g_skip_profile then
10793: hr_utility.set_location('skip 2',20);
10794: null;
10795: when g_criteria_failed then
10796: --
10797: -- when one of the criteria of the profile fail, they raise
10823: end if;
10824: --
10825: end;
10826: --
10827: hr_utility.set_location(l_proc||' End ELP loop ',68);
10828: end loop; -- elig_prfls
10829: --
10830: end if; -- inst_count
10831: --
10829: --
10830: end if; -- inst_count
10831: --
10832: --
10833: hr_utility.set_location(l_proc||' After profile loop',70);
10834: --
10835: -- If we are here, either:
10836: -- there were no profiles or
10837: -- all mandatory profiles passed
10880: end if;
10881: return false;
10882: end if;
10883: --
10884: hr_utility.set_location('Leaving: '||l_proc,99);
10885: --
10886: return true;
10887: --
10888: end eligible;