[Home] [Help]
95:
96: l_count number := 0;
97: l_proc varchar2(100):= g_package||'write';
98: begin
99: hr_utility.set_location('Entering: '||l_proc,10);
100:
101: l_count := p_score_tab.count +1;
102: p_score_tab(l_count).eligy_prfl_id := p_eligy_prfl_id;
103: p_score_tab(l_count).crit_tab_short_name := p_tab_short_name;
104: p_score_tab(l_count).crit_tab_pk_id := p_pk_id;
105: p_score_tab(l_count).computed_score := p_computed_score;
106: p_score_tab(l_count).benefit_action_id := benutils.g_benefit_action_id;
107:
108: hr_utility.set_location('Leaving: '||l_proc,10);
109:
110: end write;
111:
112: procedure write(p_profile_score_tab in out nocopy scoreTab,
113: p_crit_score_tab in scoreTab) is
114: l_proc varchar2(100):= g_package||'write';
115: begin
116:
117: hr_utility.set_location('Entering: '||l_proc,10);
118: if p_crit_score_tab.count > 0
119: then
120: for i in 1..p_crit_score_tab.count
121: loop
121: loop
122: p_profile_score_tab(p_profile_score_tab.count+1) := p_crit_score_tab(i);
123: end loop;
124: end if;
125: hr_utility.set_location('Leaving: '||l_proc,10);
126:
127: end write;
128:
129: function is_ok (p_val number
251: -- records.
252: if p_plip_id is not null then
253: open c_hrs_wkd_plip;
254: fetch c_hrs_wkd_plip into l_rt_hrs_wkd_val;
255: hr_utility.set_location ('oiplip '||to_char(l_rt_hrs_wkd_val),01);
256: close c_hrs_wkd_plip;
257: end if;
258:
259: -- If there is no oiplip, check for oipl
259: -- If there is no oiplip, check for oipl
260: if l_rt_hrs_wkd_val is null then
261: open c_hrs_wkd_opt;
262: fetch c_hrs_wkd_opt into l_rt_hrs_wkd_val;
263: hr_utility.set_location ('oipl '||to_char(l_rt_hrs_wkd_val),01);
264: close c_hrs_wkd_opt;
265: end if;
266: else
267: -- just look for pl elig per record.
266: else
267: -- just look for pl elig per record.
268: open c_hrs_wkd;
269: fetch c_hrs_wkd into l_rt_hrs_wkd_val;
270: hr_utility.set_location ('pl '||to_char(l_rt_hrs_wkd_val),01);
271: close c_hrs_wkd;
272: end if;
273:
274: return l_rt_hrs_wkd_val;
350: -- records.
351: if p_plip_id is not null then
352: open c_pct_ft_plip;
353: fetch c_pct_ft_plip into l_per_pct_ft_val;
354: hr_utility.set_location ('oiplip '||to_char(l_per_pct_ft_val),01);
355: close c_pct_ft_plip;
356: end if;
357:
358: -- If there is no oiplip, check for oipl
358: -- If there is no oiplip, check for oipl
359: if l_per_pct_ft_val is null then
360: open c_pct_ft_opt;
361: fetch c_pct_ft_opt into l_per_pct_ft_val;
362: hr_utility.set_location ('oipl '||to_char(l_per_pct_ft_val),01);
363: close c_pct_ft_opt;
364: end if;
365: else
366: -- just look for pl elig per record.
365: else
366: -- just look for pl elig per record.
367: open c_pct_ft;
368: fetch c_pct_ft into l_per_pct_ft_val;
369: hr_utility.set_location ('pl '||to_char(l_per_pct_ft_val),01);
370: close c_pct_ft;
371: end if;
372: --
373: return l_per_pct_ft_val;
415: l_score_tab scoreTab;
416: --
417: begin
418: --
419: hr_utility.set_location('Entering: '||l_proc,10);
420: --
421: -- Getting eligibility profile length of service by eligibility profile
422: --
423: ben_elp_cache.elpels_getcacdets
457: l_prtn_ovridn_flag := l_epo_row.prtn_ovridn_flag;
458: l_prtn_ovridn_thru_dt := l_epo_row.prtn_ovridn_thru_dt;
459: l_per_los := l_epo_row.rt_los_val;
460: else
461: hr_utility.set_location('Plan not in Program',10);
462: l_prtn_ovridn_flag := p_currepe_row.prtn_ovridn_flag;
463: l_prtn_ovridn_thru_dt := p_currepe_row.prtn_ovridn_thru_dt;
464: l_per_los := p_currepe_row.rt_los_val;
465: end if;
496: ,l_inst_dets(l_insttorrw_num).mn_los_num
497: ,l_inst_dets(l_insttorrw_num).no_mx_los_num_apls_flag
498: ,l_inst_dets(l_insttorrw_num).no_mn_los_num_apls_flag
499: );
500: hr_utility.set_location('l_per_los '||l_per_los,20);
501: hr_utility.set_location('ACE Score = ' || l_inst_dets(l_insttorrw_num).criteria_score, 20);
502: hr_utility.set_location('ACE Weight = ' || l_inst_dets(l_insttorrw_num).criteria_weight, 20);
503: 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);
504: --
497: ,l_inst_dets(l_insttorrw_num).no_mx_los_num_apls_flag
498: ,l_inst_dets(l_insttorrw_num).no_mn_los_num_apls_flag
499: );
500: hr_utility.set_location('l_per_los '||l_per_los,20);
501: hr_utility.set_location('ACE Score = ' || l_inst_dets(l_insttorrw_num).criteria_score, 20);
502: hr_utility.set_location('ACE Weight = ' || l_inst_dets(l_insttorrw_num).criteria_weight, 20);
503: 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);
504: --
505: if l_per_los is null then
498: ,l_inst_dets(l_insttorrw_num).no_mn_los_num_apls_flag
499: );
500: hr_utility.set_location('l_per_los '||l_per_los,20);
501: hr_utility.set_location('ACE Score = ' || l_inst_dets(l_insttorrw_num).criteria_score, 20);
502: hr_utility.set_location('ACE Weight = ' || l_inst_dets(l_insttorrw_num).criteria_weight, 20);
503: 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);
504: --
505: if l_per_los is null then
506: --
499: );
500: hr_utility.set_location('l_per_los '||l_per_los,20);
501: hr_utility.set_location('ACE Score = ' || l_inst_dets(l_insttorrw_num).criteria_score, 20);
502: hr_utility.set_location('ACE Weight = ' || l_inst_dets(l_insttorrw_num).criteria_weight, 20);
503: 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);
504: --
505: if l_per_los is null then
506: --
507: l_ok := false;
563: --
564: l_score_tab.delete;
565: g_inelg_rsn_cd := 'LOS';
566: fnd_message.set_name('BEN','BEN_91669_LOS_PRFL_FAIL');
567: hr_utility.set_location('Criteria Failed: '||l_proc,20);
568: raise g_criteria_failed;
569: --
570: end if;
571: end if;
571: end if;
572: --
573: if p_score_compute_mode
574: then
575: hr_utility.set_location('count '||l_score_tab.count,20);
576: write(p_profile_score_tab,l_score_tab);
577: end if;
578: hr_utility.set_location('Leaving: '||l_proc,20);
579: --
574: then
575: hr_utility.set_location('count '||l_score_tab.count,20);
576: write(p_profile_score_tab,l_score_tab);
577: end if;
578: hr_utility.set_location('Leaving: '||l_proc,20);
579: --
580: end check_los_elig;
581: --
582: -- ----------------------------------------------------
622: l_score_tab scoreTab;
623: --
624: begin
625: --
626: hr_utility.set_location('Entering :'||l_proc, 10);
627: --
628: -- Getting eligibility profile age details by eligibility profile
629: --
630: ben_elp_cache.elpeap_getcacdets
633: ,p_eligy_prfl_id => p_eligy_prfl_id
634: ,p_inst_set => l_inst_dets
635: ,p_inst_count => l_inst_count);
636: --
637: hr_utility.set_location('Age :'||l_per_age, 10);
638: if l_inst_count > 0 then
639:
640: if p_eval_typ <> 'E' then
641: --
665: l_prtn_ovridn_flag := l_epo_row.prtn_ovridn_flag;
666: l_prtn_ovridn_thru_dt := l_epo_row.prtn_ovridn_thru_dt;
667: l_per_age := l_epo_row.rt_age_val;
668: else
669: hr_utility.set_location('Plan not in Program',10);
670: l_prtn_ovridn_flag := p_currepe_row.prtn_ovridn_flag;
671: l_prtn_ovridn_thru_dt := p_currepe_row.prtn_ovridn_thru_dt;
672: l_per_age := p_currepe_row.rt_age_val;
673: end if;
767: not l_ok then
768: --
769: g_inelg_rsn_cd := 'AGE';
770: fnd_message.set_name('BEN','BEN_91670_AGE_PRFL_FAIL');
771: hr_utility.set_location('Criteria Failed: '||l_proc,20);
772: raise g_criteria_failed;
773: --
774: end if;
775: end if;
775: end if;
776: --
777: if p_score_compute_mode
778: then
779: hr_utility.set_location('count '||l_score_tab.count,20);
780: write(p_profile_score_tab,l_score_tab);
781: end if;
782: hr_utility.set_location('Leaving :'||l_proc, 20);
783: --
778: then
779: hr_utility.set_location('count '||l_score_tab.count,20);
780: write(p_profile_score_tab,l_score_tab);
781: end if;
782: hr_utility.set_location('Leaving :'||l_proc, 20);
783: --
784: end check_age_elig;
785: --
786: -- --------------------------------------------------------------------
834: l_score_tab scoreTab;
835: --
836: begin
837: --
838: hr_utility.set_location('Entering :'||l_proc,10);
839: --
840: -- Getting eligibility profile age/los combination by eligibility profile
841: --
842: ben_elp_cache.elpecp_getcacdets
876: l_prtn_ovridn_flag := l_epo_row.prtn_ovridn_flag;
877: l_prtn_ovridn_thru_dt := l_epo_row.prtn_ovridn_thru_dt;
878: l_per_cmbn_age_los := l_epo_row.rt_cmbn_age_n_los_val;
879: else
880: hr_utility.set_location('Plan not in Program',10);
881: l_prtn_ovridn_flag := p_currepe_row.prtn_ovridn_flag;
882: l_prtn_ovridn_thru_dt := p_currepe_row.prtn_ovridn_thru_dt;
883: l_per_cmbn_age_los := p_currepe_row.rt_cmbn_age_n_los_val;
884: end if;
938: ,l_inst_dets(l_insttorrw_num).cmbnd_max_val
939: ,l_inst_dets(l_insttorrw_num).cmbnd_min_val
940: );
941: --
942: hr_utility.set_location('TOTAL ='||l_per_cmbn_age_los,10);
943: hr_utility.set_location('MIN ='||l_inst_dets(l_insttorrw_num).cmbnd_min_val,10);
944: hr_utility.set_location('MAX ='||l_inst_dets(l_insttorrw_num).cmbnd_max_val,10);
945: if l_per_cmbn_age_los is null then
946: --
939: ,l_inst_dets(l_insttorrw_num).cmbnd_min_val
940: );
941: --
942: hr_utility.set_location('TOTAL ='||l_per_cmbn_age_los,10);
943: hr_utility.set_location('MIN ='||l_inst_dets(l_insttorrw_num).cmbnd_min_val,10);
944: hr_utility.set_location('MAX ='||l_inst_dets(l_insttorrw_num).cmbnd_max_val,10);
945: if l_per_cmbn_age_los is null then
946: --
947: l_ok := false;
940: );
941: --
942: hr_utility.set_location('TOTAL ='||l_per_cmbn_age_los,10);
943: hr_utility.set_location('MIN ='||l_inst_dets(l_insttorrw_num).cmbnd_min_val,10);
944: hr_utility.set_location('MAX ='||l_inst_dets(l_insttorrw_num).cmbnd_max_val,10);
945: if l_per_cmbn_age_los is null then
946: --
947: l_ok := false;
948: --
949: end if;
950: --
951: if not l_ok then
952: --
953: hr_utility.set_location('Failed cmbn',10);
954: --
955: elsif l_ok then
956: --
957: -- Getting length of service factor
978: end if;
979: --
980: if not l_ok then
981: --
982: hr_utility.set_location('Failed los',10);
983: --
984: elsif l_ok then
985: --
986: -- Check if age passes
1062: then
1063: if l_rows_found and
1064: not l_ok then
1065: --
1066: hr_utility.set_location('Failed Elig ',10);
1067: g_inelg_rsn_cd := 'AGL';
1068: fnd_message.set_name('BEN','BEN_91671_AGE_LOS_PRFL_FAIL');
1069: raise g_criteria_failed;
1070: --
1072: end if;
1073: --
1074: if p_score_compute_mode
1075: then
1076: hr_utility.set_location('count '||l_score_tab.count,20);
1077: write(p_profile_score_tab,l_score_tab);
1078: end if;
1079: hr_utility.set_location('Leaving :'||l_proc,20);
1080: --
1075: then
1076: hr_utility.set_location('count '||l_score_tab.count,20);
1077: write(p_profile_score_tab,l_score_tab);
1078: end if;
1079: hr_utility.set_location('Leaving :'||l_proc,20);
1080: --
1081: end check_age_los_elig;
1082: --
1083: -- -----------------------------------------------------------
1116: l_score_tab scoreTab;
1117: --
1118: begin
1119: --
1120: hr_utility.set_location('Entering: '||l_proc, 10);
1121: --
1122: -- Getting eligibility profile compensation level by eligibility profile
1123: --
1124: ben_elp_cache.elpecl_getcacdets
1220: not l_ok then
1221: --
1222: g_inelg_rsn_cd := 'CMP';
1223: fnd_message.set_name('BEN','BEN_91672_COMP_LVL_PRFL_FAIL');
1224: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1225: raise g_criteria_failed;
1226: --
1227: end if;
1228: end if;
1226: --
1227: end if;
1228: end if;
1229: --
1230: hr_utility.set_location('Leaving :'||l_proc,20);
1231: --
1232: if p_score_compute_mode
1233: then
1234: hr_utility.set_location('count '||l_score_tab.count,20);
1230: hr_utility.set_location('Leaving :'||l_proc,20);
1231: --
1232: if p_score_compute_mode
1233: then
1234: hr_utility.set_location('count '||l_score_tab.count,20);
1235: write(p_profile_score_tab,l_score_tab);
1236: end if;
1237: end check_comp_level_rl_elig;
1238: --
1264: l_score_tab scoreTab;
1265: --
1266: begin
1267: --
1268: hr_utility.set_location('Entering: '||l_proc, 10);
1269: --
1270: -- Getting eligibility profile compensation level by eligibility profile
1271: --
1272: ben_elp_cache.elpecl_getcacdets
1370: not l_ok then
1371: --
1372: g_inelg_rsn_cd := 'CMP';
1373: fnd_message.set_name('BEN','BEN_91672_COMP_LVL_PRFL_FAIL');
1374: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1375: raise g_criteria_failed;
1376: --
1377: end if;
1378: end if;
1376: --
1377: end if;
1378: end if;
1379: --
1380: hr_utility.set_location('Leaving :'||l_proc,20);
1381: --
1382: if p_score_compute_mode
1383: then
1384: hr_utility.set_location('count '||l_score_tab.count,20);
1380: hr_utility.set_location('Leaving :'||l_proc,20);
1381: --
1382: if p_score_compute_mode
1383: then
1384: hr_utility.set_location('count '||l_score_tab.count,20);
1385: write(p_profile_score_tab,l_score_tab);
1386: end if;
1387: end check_comp_level_elig;
1388: --
1415: l_score_tab scoreTab;
1416: --
1417: begin
1418: --
1419: hr_utility.set_location('Entering :'||l_proc,10);
1420: --
1421: -- Check if we need to test this business rule at all
1422: --
1423: hr_utility.set_location('Elig Flag :'||p_elig_flag,10);
1419: hr_utility.set_location('Entering :'||l_proc,10);
1420: --
1421: -- Check if we need to test this business rule at all
1422: --
1423: hr_utility.set_location('Elig Flag :'||p_elig_flag,10);
1424: hr_utility.set_location('Once r Cntug Cd :'||p_once_r_cntug_cd,10);
1425: --
1426: if p_elig_flag = 'Y' and
1427: nvl(p_once_r_cntug_cd,'-1') = 'ONCE' then
1420: --
1421: -- Check if we need to test this business rule at all
1422: --
1423: hr_utility.set_location('Elig Flag :'||p_elig_flag,10);
1424: hr_utility.set_location('Once r Cntug Cd :'||p_once_r_cntug_cd,10);
1425: --
1426: if p_elig_flag = 'Y' and
1427: nvl(p_once_r_cntug_cd,'-1') = 'ONCE' then
1428: --
1439: ,p_eligy_prfl_id => p_eligy_prfl_id
1440: ,p_inst_set => l_inst_dets
1441: ,p_inst_count => l_inst_count);
1442: --
1443: hr_utility.set_location('Inst Count :'||l_inst_count,10);
1444: --
1445: if l_inst_count > 0 then
1446: --
1447:
1535: not l_ok then
1536: --
1537: g_inelg_rsn_cd := 'HRS';
1538: fnd_message.set_name('BEN','BEN_91673_HRS_WKD_PRFL_FAIL');
1539: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1540: raise g_criteria_failed;
1541: --
1542: end if;
1543: end if;
1541: --
1542: end if;
1543: end if;
1544: --
1545: hr_utility.set_location('Leaving :'||l_proc,20);
1546: --
1547: if p_score_compute_mode
1548: then
1549: hr_utility.set_location('count '||l_score_tab.count,20);
1545: hr_utility.set_location('Leaving :'||l_proc,20);
1546: --
1547: if p_score_compute_mode
1548: then
1549: hr_utility.set_location('count '||l_score_tab.count,20);
1550: write(p_profile_score_tab,l_score_tab);
1551: end if;
1552: end check_hrs_wkd_ben_bal;
1553: --
1579: l_score_tab scoreTab;
1580: --
1581: begin
1582: --
1583: hr_utility.set_location('Entering :'||l_proc,10);
1584: --
1585: if p_elig_flag = 'Y' and
1586: nvl(p_once_r_cntug_cd,'-1') = 'ONCE' then
1587: --
1690: not l_ok then
1691: --
1692: g_inelg_rsn_cd := 'HRS';
1693: fnd_message.set_name('BEN','BEN_91673_HRS_WKD_PRFL_FAIL');
1694: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1695: raise g_criteria_failed;
1696: --
1697: end if;
1698: end if;
1698: end if;
1699: --
1700: if p_score_compute_mode
1701: then
1702: hr_utility.set_location('count '||l_score_tab.count,20);
1703: write(p_profile_score_tab,l_score_tab);
1704: end if;
1705: hr_utility.set_location('Leaving :'||l_proc,20);
1706: --
1701: then
1702: hr_utility.set_location('count '||l_score_tab.count,20);
1703: write(p_profile_score_tab,l_score_tab);
1704: end if;
1705: hr_utility.set_location('Leaving :'||l_proc,20);
1706: --
1707: end check_hrs_wkd_rl_balance;
1708: --
1709: -- --------------------------------------------------------------------
1734: l_score_tab scoreTab;
1735: --
1736: begin
1737: --
1738: hr_utility.set_location('Entering :'||l_proc,10);
1739: --
1740: if p_elig_flag = 'Y' and
1741: nvl(p_once_r_cntug_cd,'-1') = 'ONCE' then
1742: --
1844: not l_ok then
1845: --
1846: g_inelg_rsn_cd := 'HRS';
1847: fnd_message.set_name('BEN','BEN_91673_HRS_WKD_PRFL_FAIL');
1848: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1849: raise g_criteria_failed;
1850: --
1851: end if;
1852: end if;
1852: end if;
1853: --
1854: if p_score_compute_mode
1855: then
1856: hr_utility.set_location('count '||l_score_tab.count,20);
1857: write(p_profile_score_tab,l_score_tab);
1858: end if;
1859: hr_utility.set_location('Leaving :'||l_proc,20);
1860: --
1855: then
1856: hr_utility.set_location('count '||l_score_tab.count,20);
1857: write(p_profile_score_tab,l_score_tab);
1858: end if;
1859: hr_utility.set_location('Leaving :'||l_proc,20);
1860: --
1861: end check_hrs_wkd_balance;
1862: --
1863: -- -----------------------------------------------------------------
1887: l_score_tab scoreTab;
1888: --
1889: begin
1890: --
1891: hr_utility.set_location('Entering :'||l_proc,10);
1892: --
1893: -- Getting eligibility profile full time by eligibility profile
1894: --
1895: ben_elp_cache.elpepf_getcacdets
1985: not l_ok then
1986: --
1987: g_inelg_rsn_cd := 'PFT';
1988: fnd_message.set_name('BEN','BEN_91674_PCT_FT_PRFL_FAIL');
1989: hr_utility.set_location('Criteria Failed: '||l_proc,20);
1990: raise g_criteria_failed;
1991: --
1992: end if;
1993: end if;
1993: end if;
1994: --
1995: if p_score_compute_mode
1996: then
1997: hr_utility.set_location('count '||l_score_tab.count,20);
1998: write(p_profile_score_tab,l_score_tab);
1999: end if;
2000: hr_utility.set_location('Leaving :'||l_proc,20);
2001: --
1996: then
1997: hr_utility.set_location('count '||l_score_tab.count,20);
1998: write(p_profile_score_tab,l_score_tab);
1999: end if;
2000: hr_utility.set_location('Leaving :'||l_proc,20);
2001: --
2002: end check_pct_fltm_elig;
2003: --
2004: -- ------------------------------------------------------
2022: l_score_tab scoreTab;
2023: --
2024: begin
2025: --
2026: hr_utility.set_location('Entering : '||l_proc,10);
2027: --
2028: -- Getting eligibility profile grade by eligibility profile
2029: --
2030: ben_elp_cache.elpegr_getcacdets
2038: if l_inst_count > 0 then
2039: --
2040: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
2041: --
2042: hr_utility.set_location('GRADE'||p_grade_id,10);
2043: hr_utility.set_location('COMPARE GRADE'||l_inst_dets(l_insttorrw_num).grade_id,10);
2044: l_ok := nvl((nvl(p_grade_id,-1) = l_inst_dets(l_insttorrw_num).grade_id),FALSE);
2045: --if l_ok is null then
2046: -- l_ok:=false;
2039: --
2040: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
2041: --
2042: hr_utility.set_location('GRADE'||p_grade_id,10);
2043: hr_utility.set_location('COMPARE GRADE'||l_inst_dets(l_insttorrw_num).grade_id,10);
2044: l_ok := nvl((nvl(p_grade_id,-1) = l_inst_dets(l_insttorrw_num).grade_id),FALSE);
2045: --if l_ok is null then
2046: -- l_ok:=false;
2047: --end if;
2098: not l_ok then
2099: --
2100: g_inelg_rsn_cd := 'GRD';
2101: fnd_message.set_name('BEN','BEN_91675_GRD_PRFL_FAIL');
2102: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2103: raise g_criteria_failed;
2104: --
2105: end if;
2106: end if;
2106: end if;
2107: --
2108: if p_score_compute_mode
2109: then
2110: hr_utility.set_location('count '||l_score_tab.count,20);
2111: write(p_profile_score_tab,l_score_tab);
2112: end if;
2113: hr_utility.set_location('Leaving: '||l_proc,20);
2114: --
2109: then
2110: hr_utility.set_location('count '||l_score_tab.count,20);
2111: write(p_profile_score_tab,l_score_tab);
2112: end if;
2113: hr_utility.set_location('Leaving: '||l_proc,20);
2114: --
2115: end check_grade_elig;
2116: --
2117: -- ------------------------------------------------------
2135: l_score_tab scoreTab;
2136: --
2137: begin
2138: --
2139: hr_utility.set_location('Entering : '||l_proc,10);
2140: --
2141: -- Getting eligibility profile sex by eligibility profile
2142: --
2143: ben_elp_cache.elpegn_getcacdets
2151: if l_inst_count > 0 then
2152: --
2153: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
2154: --
2155: hr_utility.set_location('SEX'||p_sex,10);
2156: l_ok := nvl((nvl(p_sex,'zz') = l_inst_dets(l_insttorrw_num).sex),FALSE);
2157: --
2158: if l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2159: if p_score_compute_mode then
2206: not l_ok then
2207: --
2208: g_inelg_rsn_cd := 'GND';
2209: fnd_message.set_name('BEN','BEN_91675_GND_PRFL_FAIL');
2210: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2211: raise g_criteria_failed;
2212: --
2213: end if;
2214: end if;
2214: end if;
2215: --
2216: if p_score_compute_mode
2217: then
2218: hr_utility.set_location('count '||l_score_tab.count,20);
2219: write(p_profile_score_tab,l_score_tab);
2220: end if;
2221: hr_utility.set_location('Leaving: '||l_proc,20);
2222: --
2217: then
2218: hr_utility.set_location('count '||l_score_tab.count,20);
2219: write(p_profile_score_tab,l_score_tab);
2220: end if;
2221: hr_utility.set_location('Leaving: '||l_proc,20);
2222: --
2223: end check_gender_elig;
2224: --
2225: -- ------------------------------------------------------
2267: l_score_tab scoreTab;
2268: --
2269: begin
2270: --
2271: hr_utility.set_location('Entering : '||l_proc,10);
2272: --
2273: -- Getting eligibility profile rule by eligibility profile
2274: --
2275:
2283: if l_inst_count > 0 then
2284: --find the profile value for formul and/Or # 2508757\
2285: l_ben_elgy_prfl_rl_Cond := fnd_profile.value('BEN_ELGY_PRFL_RL_COND');
2286: --
2287: hr_utility.set_location('assignment_id='||p_assignment_id,1963);
2288: hr_utility.set_location('l_ben_elgy_prfl_rl_Cond='||l_ben_elgy_prfl_rl_Cond,1963);
2289: --
2290: if p_oipl_id is not null then
2291: --
2284: --find the profile value for formul and/Or # 2508757\
2285: l_ben_elgy_prfl_rl_Cond := fnd_profile.value('BEN_ELGY_PRFL_RL_COND');
2286: --
2287: hr_utility.set_location('assignment_id='||p_assignment_id,1963);
2288: hr_utility.set_location('l_ben_elgy_prfl_rl_Cond='||l_ben_elgy_prfl_rl_Cond,1963);
2289: --
2290: if p_oipl_id is not null then
2291: --
2292: l_oipl_rec := ben_cobj_cache.g_oipl_currow;
2312: then
2313: --
2314: g_inelg_rsn_cd := 'ERL';
2315: fnd_message.set_name('BEN','BEN_92445_ERL_PRFL_FAIL');
2316: hr_utility.set_location('Criteria Failed: '||l_proc,19);
2317: raise g_criteria_failed;
2318: --
2319: end if;
2320: --
2383: end;
2384: --
2385: end loop;
2386: --if the condition is AND and fails go out 2508757
2387: hr_utility.set_location('l_eligible='||l_eligible,1963);
2388: if nvl(l_ben_elgy_prfl_rl_Cond,'A') = 'A' then
2389:
2390: if l_eligible <> 'Y' then
2391: --
2390: if l_eligible <> 'Y' then
2391: --
2392: g_inelg_rsn_cd := 'ERL';
2393: fnd_message.set_name('BEN','BEN_92445_ERL_PRFL_FAIL');
2394: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2395: raise g_criteria_failed;
2396: End if ;
2397: Else ---- if the condition is OR and pass then exit
2398: if l_eligible = 'Y' then
2417: --if the condition is OR and and exit the loop with 'N' throw error
2418: if l_crit_passed is null
2419: then
2420: if nvl(l_ben_elgy_prfl_rl_Cond,'A') = 'O' and l_eligible <> 'Y' then
2421: hr_utility.set_location('erroring on OR and N ',1963);
2422: g_inelg_rsn_cd := 'ERL';
2423: fnd_message.set_name('BEN','BEN_92445_ERL_PRFL_FAIL');
2424: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2425: raise g_criteria_failed;
2420: if nvl(l_ben_elgy_prfl_rl_Cond,'A') = 'O' and l_eligible <> 'Y' then
2421: hr_utility.set_location('erroring on OR and N ',1963);
2422: g_inelg_rsn_cd := 'ERL';
2423: fnd_message.set_name('BEN','BEN_92445_ERL_PRFL_FAIL');
2424: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2425: raise g_criteria_failed;
2426: end if ;
2427: end if ;
2428:
2429: end if;
2430: --
2431: if p_score_compute_mode --999
2432: then
2433: hr_utility.set_location('count '||l_score_tab.count,20);
2434: write(p_profile_score_tab,l_score_tab);
2435: end if;
2436: hr_utility.set_location('Leaving: '||l_proc,20);
2437: --
2432: then
2433: hr_utility.set_location('count '||l_score_tab.count,20);
2434: write(p_profile_score_tab,l_score_tab);
2435: end if;
2436: hr_utility.set_location('Leaving: '||l_proc,20);
2437: --
2438: end check_rule_elig;
2439: -- --------------------------------------------------
2440: -- This procedure determines elibility based on job.
2456: l_score_tab scoreTab;
2457: --
2458: begin
2459: --
2460: hr_utility.set_location('Entering : '||l_proc,10);
2461: --
2462: -- Getting eligibility profile job by eligibility profile
2463: --
2464: ben_elp_cache.elpejp_getcacdets
2529: not l_ok then
2530: --
2531: g_inelg_rsn_cd := 'JOB';
2532: fnd_message.set_name('BEN','BEN_91676_JOB_PRFL_FAIL');
2533: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2534: raise g_criteria_failed;
2535: --
2536: end if;
2537: end if;
2537: end if;
2538: --
2539: if p_score_compute_mode
2540: then
2541: hr_utility.set_location('count '||l_score_tab.count,20);
2542: write(p_profile_score_tab,l_score_tab);
2543: end if;
2544: hr_utility.set_location('Leaving: '||l_proc,20);
2545: --
2540: then
2541: hr_utility.set_location('count '||l_score_tab.count,20);
2542: write(p_profile_score_tab,l_score_tab);
2543: end if;
2544: hr_utility.set_location('Leaving: '||l_proc,20);
2545: --
2546: end check_job_elig;
2547: --
2548: -- -----------------------------------------------------------
2569: l_score_tab scoreTab;
2570: --
2571: begin
2572: --
2573: hr_utility.set_location('Entering :'||l_proc,10);
2574: --
2575: ben_elp_cache.elpept_getcacdets
2576: (p_effective_date => p_effective_date
2577: ,p_business_group_id => p_business_group_id
2659: not l_ok then
2660: --
2661: g_inelg_rsn_cd := 'PTP';
2662: fnd_message.set_name('BEN','BEN_91677_PER_TYPE_PRFL_FAIL');
2663: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2664: raise g_criteria_failed;
2665: --
2666: end if;
2667: end if;
2667: end if;
2668: --
2669: if p_score_compute_mode
2670: then
2671: hr_utility.set_location('count '||l_score_tab.count,20);
2672: write(p_profile_score_tab,l_score_tab);
2673: end if;
2674: hr_utility.set_location('Leaving: '||l_proc,20);
2675: --
2670: then
2671: hr_utility.set_location('count '||l_score_tab.count,20);
2672: write(p_profile_score_tab,l_score_tab);
2673: end if;
2674: hr_utility.set_location('Leaving: '||l_proc,20);
2675: --
2676: end check_per_typ_elig;
2677: -- ----------------------------------------------------------
2678: -- This procedure determines eligibility based on pay basis.
2694: l_score_tab scoreTab;
2695: --
2696: begin
2697: --
2698: hr_utility.set_location('Entering : '||l_proc,10);
2699: --
2700: -- Getting eligibility profile pay basis by eligibility profile
2701: --
2702: ben_elp_cache.elpepb_getcacdets
2715: --if l_ok is null then
2716: -- l_ok:=false;
2717: --end if;
2718: if l_ok then
2719: hr_utility.set_location('ok',10);
2720: else
2721: hr_utility.set_location('not ok',10);
2722: end if;
2723: hr_utility.set_location('pay_basis_id='||p_pay_basis_id,10);
2717: --end if;
2718: if l_ok then
2719: hr_utility.set_location('ok',10);
2720: else
2721: hr_utility.set_location('not ok',10);
2722: end if;
2723: hr_utility.set_location('pay_basis_id='||p_pay_basis_id,10);
2724: --
2725: if l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2719: hr_utility.set_location('ok',10);
2720: else
2721: hr_utility.set_location('not ok',10);
2722: end if;
2723: hr_utility.set_location('pay_basis_id='||p_pay_basis_id,10);
2724: --
2725: if l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2726: hr_utility.set_location(l_proc,10);
2727: --
2722: end if;
2723: hr_utility.set_location('pay_basis_id='||p_pay_basis_id,10);
2724: --
2725: if l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2726: hr_utility.set_location(l_proc,10);
2727: --
2728: if p_score_compute_mode then
2729: if l_crit_passed is null then
2730: l_crit_passed := true;
2739: exit;
2740: end if;
2741: --
2742: elsif l_ok and l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
2743: hr_utility.set_location(l_proc,20);
2744: --
2745: l_rows_found := true;
2746: l_ok := false;
2747: exit;
2746: l_ok := false;
2747: exit;
2748: --
2749: elsif (not l_ok) and l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
2750: hr_utility.set_location(l_proc,30);
2751: --
2752: l_rows_found := true;
2753: l_ok := true;
2754: if p_score_compute_mode then
2761: end if;
2762: -- exit;
2763: --
2764: elsif l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
2765: hr_utility.set_location(l_proc,40);
2766: --
2767: l_rows_found := true;
2768: --
2769: end if;
2766: --
2767: l_rows_found := true;
2768: --
2769: end if;
2770: hr_utility.set_location(l_proc,50);
2771: --
2772: end loop;
2773: hr_utility.set_location(l_proc,60);
2774: --
2769: end if;
2770: hr_utility.set_location(l_proc,50);
2771: --
2772: end loop;
2773: hr_utility.set_location(l_proc,60);
2774: --
2775: end if;
2776: --
2777: hr_utility.set_location(l_proc,70);
2773: hr_utility.set_location(l_proc,60);
2774: --
2775: end if;
2776: --
2777: hr_utility.set_location(l_proc,70);
2778: if l_ok then
2779: hr_utility.set_location('ok',10);
2780: else
2781: hr_utility.set_location('not ok',10);
2775: end if;
2776: --
2777: hr_utility.set_location(l_proc,70);
2778: if l_ok then
2779: hr_utility.set_location('ok',10);
2780: else
2781: hr_utility.set_location('not ok',10);
2782: end if;
2783: if l_rows_found then
2777: hr_utility.set_location(l_proc,70);
2778: if l_ok then
2779: hr_utility.set_location('ok',10);
2780: else
2781: hr_utility.set_location('not ok',10);
2782: end if;
2783: if l_rows_found then
2784: hr_utility.set_location('found',10);
2785: else
2780: else
2781: hr_utility.set_location('not ok',10);
2782: end if;
2783: if l_rows_found then
2784: hr_utility.set_location('found',10);
2785: else
2786: hr_utility.set_location('not found',10);
2787: end if;
2788: if l_rows_found is null then
2782: end if;
2783: if l_rows_found then
2784: hr_utility.set_location('found',10);
2785: else
2786: hr_utility.set_location('not found',10);
2787: end if;
2788: if l_rows_found is null then
2789: hr_utility.set_location('found is null',10);
2790: end if;
2785: else
2786: hr_utility.set_location('not found',10);
2787: end if;
2788: if l_rows_found is null then
2789: hr_utility.set_location('found is null',10);
2790: end if;
2791: if l_ok is null then
2792: hr_utility.set_location('ok is null',10);
2793: end if;
2788: if l_rows_found is null then
2789: hr_utility.set_location('found is null',10);
2790: end if;
2791: if l_ok is null then
2792: hr_utility.set_location('ok is null',10);
2793: end if;
2794:
2795: if l_crit_passed is null
2796: then
2796: then
2797: if l_rows_found and
2798: not l_ok then
2799: --
2800: hr_utility.set_location(l_proc,80);
2801: g_inelg_rsn_cd := 'PBS';
2802: fnd_message.set_name('BEN','BEN_91678_PY_BSS_PRFL_FAIL');
2803: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2804: raise g_criteria_failed;
2799: --
2800: hr_utility.set_location(l_proc,80);
2801: g_inelg_rsn_cd := 'PBS';
2802: fnd_message.set_name('BEN','BEN_91678_PY_BSS_PRFL_FAIL');
2803: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2804: raise g_criteria_failed;
2805: --
2806: end if;
2807: end if;
2807: end if;
2808: --
2809: if p_score_compute_mode
2810: then
2811: hr_utility.set_location('count '||l_score_tab.count,20);
2812: write(p_profile_score_tab,l_score_tab);
2813: end if;
2814: hr_utility.set_location('Leaving: '||l_proc,20);
2815: --
2810: then
2811: hr_utility.set_location('count '||l_score_tab.count,20);
2812: write(p_profile_score_tab,l_score_tab);
2813: end if;
2814: hr_utility.set_location('Leaving: '||l_proc,20);
2815: --
2816: end check_py_bss_elig;
2817: -- --------------------------------------------------------
2818: -- This procedure determines eligibility based on payroll.
2834: l_score_tab scoreTab;
2835: --
2836: begin
2837: --
2838: hr_utility.set_location('Entering : '||l_proc,10);
2839: hr_utility.set_location('payroll id : '||p_payroll_id,10);
2840: --
2841: -- Getting eligibility profile payroll by eligibility profile
2842: --
2835: --
2836: begin
2837: --
2838: hr_utility.set_location('Entering : '||l_proc,10);
2839: hr_utility.set_location('payroll id : '||p_payroll_id,10);
2840: --
2841: -- Getting eligibility profile payroll by eligibility profile
2842: --
2843: ben_elp_cache.elpepy_getcacdets
2847: ,p_inst_set => l_inst_dets
2848: ,p_inst_count => l_inst_count
2849: );
2850: --
2851: hr_utility.set_location('Inst Count : '||l_inst_count,10);
2852: if l_inst_count > 0 then
2853: --
2854: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
2855: --
2910: not l_ok then
2911: --
2912: g_inelg_rsn_cd := 'PYR';
2913: fnd_message.set_name('BEN','BEN_91679_PYRL_PRFL_FAIL');
2914: hr_utility.set_location('Criteria Failed: '||l_proc,20);
2915: raise g_criteria_failed;
2916: --
2917: end if;
2918: end if;
2918: end if;
2919: --
2920: if p_score_compute_mode
2921: then
2922: hr_utility.set_location('count '||l_score_tab.count,20);
2923: write(p_profile_score_tab,l_score_tab);
2924: end if;
2925: hr_utility.set_location('Leaving: '||l_proc,20);
2926: --
2921: then
2922: hr_utility.set_location('count '||l_score_tab.count,20);
2923: write(p_profile_score_tab,l_score_tab);
2924: end if;
2925: hr_utility.set_location('Leaving: '||l_proc,20);
2926: --
2927: end check_pyrl_elig;
2928: -- ---------------------------------------------------------------
2929: -- This procedure determines eligibility based on benefits group.
2945: l_score_tab scoreTab;
2946: --
2947: begin
2948: --
2949: hr_utility.set_location('Entering : '||l_proc,10);
2950: --
2951: -- Getting eligibility profile benefits group by eligibility profile
2952: --
2953: ben_elp_cache.elpebn_getcacdets
3020: not l_ok then
3021: --
3022: g_inelg_rsn_cd := 'BGR';
3023: fnd_message.set_name('BEN','BEN_91680_BENFTS_GRP_PRFL_FAIL');
3024: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3025: raise g_criteria_failed;
3026: --
3027: end if;
3028: end if;
3028: end if;
3029: --
3030: if p_score_compute_mode
3031: then
3032: hr_utility.set_location('count '||l_score_tab.count,20);
3033: write(p_profile_score_tab,l_score_tab);
3034: end if;
3035: hr_utility.set_location('Leaving: '||l_proc,20);
3036: --
3031: then
3032: hr_utility.set_location('count '||l_score_tab.count,20);
3033: write(p_profile_score_tab,l_score_tab);
3034: end if;
3035: hr_utility.set_location('Leaving: '||l_proc,20);
3036: --
3037: end check_benefits_grp_elig;
3038: -- --------------------------------------------------------------
3039: -- This procedure determines eligibility based on work location.
3055: l_score_tab scoreTab;
3056: --
3057: begin
3058: --
3059: hr_utility.set_location('Entering : '||l_proc,10);
3060: --
3061: -- Getting eligibility profile location by eligibility profile
3062: --
3063: ben_elp_cache.elpewl_getcacdets
3128: not l_ok then
3129: --
3130: g_inelg_rsn_cd := 'LOC';
3131: fnd_message.set_name('BEN','BEN_91681_WK_LOC_PRFL_FAIL');
3132: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3133: raise g_criteria_failed;
3134: --
3135: end if;
3136: end if;
3136: end if;
3137: --
3138: if p_score_compute_mode
3139: then
3140: hr_utility.set_location('count '||l_score_tab.count,20);
3141: write(p_profile_score_tab,l_score_tab);
3142: end if;
3143: hr_utility.set_location('Leaving: '||l_proc,20);
3144: --
3139: then
3140: hr_utility.set_location('count '||l_score_tab.count,20);
3141: write(p_profile_score_tab,l_score_tab);
3142: end if;
3143: hr_utility.set_location('Leaving: '||l_proc,20);
3144: --
3145: end check_wk_location_elig;
3146: -- -------------------------------------------------------------
3147: -- This procedure determines eligibility based on people group.
3204: l_ppl_grp_rec c_ppl_grp%ROWTYPE ;
3205: --
3206: begin
3207: --
3208: hr_utility.set_location('Entering : '||l_proc,10);
3209: --
3210: -- Getting eligibility profile organization by eligibility profile
3211: --
3212: ben_elp_cache.elpepg_getcacdets
3560: not l_ok then
3561: --
3562: g_inelg_rsn_cd := 'PEO';
3563: fnd_message.set_name('BEN','BEN_92224_PEO_GROUP_PRFL_FAIL');
3564: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3565: raise g_criteria_failed;
3566: --
3567: end if;
3568: end if;
3568: end if;
3569: --
3570: if p_score_compute_mode
3571: then
3572: hr_utility.set_location('count '||l_score_tab.count,20);
3573: write(p_profile_score_tab,l_score_tab);
3574: end if;
3575: hr_utility.set_location('Leaving: '||l_proc,20);
3576: --
3571: then
3572: hr_utility.set_location('count '||l_score_tab.count,20);
3573: write(p_profile_score_tab,l_score_tab);
3574: end if;
3575: hr_utility.set_location('Leaving: '||l_proc,20);
3576: --
3577: end check_people_group_elig;
3578: -- ---------------------------------------------------------
3579: -- This procedure determines eligibility based on org unit.
3595: l_score_tab scoreTab;
3596: --
3597: begin
3598: --
3599: hr_utility.set_location('Entering : '||l_proc,10);
3600: --
3601: -- Getting eligibility profile organization by eligibility profile
3602: --
3603: ben_elp_cache.elpeou_getcacdets
3668: not l_ok then
3669: --
3670: g_inelg_rsn_cd := 'ORG';
3671: fnd_message.set_name('BEN','BEN_91682_ORG_UNIT_PRFL_FAIL');
3672: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3673: raise g_criteria_failed;
3674: --
3675: end if;
3676: end if;
3676: end if;
3677: --
3678: if p_score_compute_mode
3679: then
3680: hr_utility.set_location('count '||l_score_tab.count,20);
3681: write(p_profile_score_tab,l_score_tab);
3682: end if;
3683: hr_utility.set_location('Leaving: '||l_proc,20);
3684: --
3679: then
3680: hr_utility.set_location('count '||l_score_tab.count,20);
3681: write(p_profile_score_tab,l_score_tab);
3682: end if;
3683: hr_utility.set_location('Leaving: '||l_proc,20);
3684: --
3685: end check_org_unit_elig;
3686: -- --------------------------------------------------------------
3687: -- This procedure determines eligibility based on pay frequency.
3703: l_score_tab scoreTab;
3704: --
3705: begin
3706: --
3707: hr_utility.set_location('Entering : '||l_proc,10);
3708: --
3709: -- Getting eligibility profile pay frequency by eligibility profile
3710: --
3711: ben_elp_cache.elpehs_getcacdets
3777: not l_ok then
3778: --
3779: g_inelg_rsn_cd := 'PFQ';
3780: fnd_message.set_name('BEN','BEN_91683_PY_FREQ_PRFL_FAIL');
3781: hr_utility.set_location('Criteria Failed: '||l_proc,20);
3782: raise g_criteria_failed;
3783: --
3784: end if;
3785: end if;
3785: end if;
3786: --
3787: if p_score_compute_mode
3788: then
3789: hr_utility.set_location('count '||l_score_tab.count,20);
3790: write(p_profile_score_tab,l_score_tab);
3791: end if;
3792: hr_utility.set_location('Leaving: '||l_proc,20);
3793: --
3788: then
3789: hr_utility.set_location('count '||l_score_tab.count,20);
3790: write(p_profile_score_tab,l_score_tab);
3791: end if;
3792: hr_utility.set_location('Leaving: '||l_proc,20);
3793: --
3794: end check_py_freq_elig;
3795: -- ---------------------------------------------------------------
3796: -- This procedure determines eligibility based on service area.
3851: and p_effective_date between zip.effective_start_date
3852: and zip.effective_end_date;
3853: begin
3854: --
3855: hr_utility.set_location('Entering : '||l_proc,10);
3856: --
3857: if p_postal_code is null then
3858: ben_person_object.get_object(p_person_id => p_person_id,
3859: p_rec => l_pad_rec);
3897: nvl(l_criteria_score, l_criteria_weight));
3898: else
3899: exit;
3900: end if;
3901: hr_utility.set_location(' l_rows_found := TRUE ' ,99);
3902: --
3903: ELSIF (NOT l_rows_found AND l_excld_flag = 'Y' ) then
3904: --
3905: l_rows_found := TRUE;
3909: 'ESA',
3910: l_pk_id,
3911: nvl(l_criteria_score, l_criteria_weight));
3912: end if;
3913: hr_utility.set_location(' l_rows_found := TRUE ' ,99);
3914: --
3915: ELSIF ( l_rows_found AND l_excld_flag = 'Y' ) then
3916: --
3917: g_inelg_rsn_cd := 'SVC';
3927: if l_crit_passed is null
3928: then
3929: if NOT l_rows_found then
3930: --
3931: hr_utility.set_location(' g_profile_failed ',99);
3932: RAISE g_criteria_failed ;
3933: --
3934: End if;
3935: End if;
3935: End if;
3936: --
3937: if p_score_compute_mode
3938: then
3939: hr_utility.set_location('count '||l_score_tab.count,20);
3940: write(p_profile_score_tab,l_score_tab);
3941: end if;
3942: hr_utility.set_location('Leaving: '||l_proc,20);
3943: --
3938: then
3939: hr_utility.set_location('count '||l_score_tab.count,20);
3940: write(p_profile_score_tab,l_score_tab);
3941: end if;
3942: hr_utility.set_location('Leaving: '||l_proc,20);
3943: --
3944: end check_service_area_elig;
3945: --
3946: -- ---------------------------------------------------------------
3993: and p_effective_date between zip.effective_start_date
3994: and zip.effective_end_date;
3995: begin
3996: --
3997: hr_utility.set_location('Entering : '||l_proc,10);
3998: --
3999: -- Getting eligibility profile zip code range by eligibility profile
4000: --
4001: --
4023: loop
4024: fetch get_zip_ranges into l_from_value,l_to_value;
4025: exit when get_zip_ranges%NOTFOUND;
4026: --
4027: hr_utility.set_location('person zip '||l_pad_rec.postal_code ,2219.3);
4028: hr_utility.set_location('from zip '||l_from_value ,2219.3);
4029: hr_utility.set_location('to zip '||l_to_value ,2219.3);
4030: --
4031: l_rows_found := true;
4024: fetch get_zip_ranges into l_from_value,l_to_value;
4025: exit when get_zip_ranges%NOTFOUND;
4026: --
4027: hr_utility.set_location('person zip '||l_pad_rec.postal_code ,2219.3);
4028: hr_utility.set_location('from zip '||l_from_value ,2219.3);
4029: hr_utility.set_location('to zip '||l_to_value ,2219.3);
4030: --
4031: l_rows_found := true;
4032: exit;
4025: exit when get_zip_ranges%NOTFOUND;
4026: --
4027: hr_utility.set_location('person zip '||l_pad_rec.postal_code ,2219.3);
4028: hr_utility.set_location('from zip '||l_from_value ,2219.3);
4029: hr_utility.set_location('to zip '||l_to_value ,2219.3);
4030: --
4031: l_rows_found := true;
4032: exit;
4033: end loop zip_loop;
4083: end if;
4084: end if;
4085: if p_score_compute_mode
4086: then
4087: hr_utility.set_location('count '||l_score_tab.count,20);
4088: write(p_profile_score_tab,l_score_tab);
4089: end if;
4090: hr_utility.set_location('Leaving: '||l_proc,20);
4091: --
4086: then
4087: hr_utility.set_location('count '||l_score_tab.count,20);
4088: write(p_profile_score_tab,l_score_tab);
4089: end if;
4090: hr_utility.set_location('Leaving: '||l_proc,20);
4091: --
4092: end check_zip_code_rng_elig;
4093: -- ---------------------------------------------------------
4094: -- This procedure determines elig based on scheduled hours.
4157: and frequency = p_frequency ;
4158: --
4159: begin
4160: --
4161: hr_utility.set_location('Entering : '||l_proc,10);
4162: --
4163:
4164: -- Getting eligibility profile scheduled hours by eligibility profile
4165: --
4205: l_min_hours := null;
4206: l_max_hours := null;
4207: l_freq_cd := null;
4208: --
4209: hr_utility.set_location('criteria #' || l_insttorrw_num,20);
4210: --
4211: if l_inst_dets(l_insttorrw_num).schedd_hrs_rl is not null then
4212: l_output := benutils.formula
4213: (p_formula_id => l_inst_dets(l_insttorrw_num).schedd_hrs_rl
4300: l_max_hours := l_inst_dets(l_insttorrw_num).max_hrs_num;
4301: l_freq_cd := l_inst_dets(l_insttorrw_num).freq_cd;
4302: end if;
4303: --
4304: hr_utility.set_location('l_min_hours' || l_min_hours,30);
4305: hr_utility.set_location('l_max_hours' || l_max_hours,30);
4306: hr_utility.set_location('l_freq_cd ' || l_freq_cd ,30);
4307: --
4308: -- Use the determination code given in the criteria in ELPRO to arrive
4301: l_freq_cd := l_inst_dets(l_insttorrw_num).freq_cd;
4302: end if;
4303: --
4304: hr_utility.set_location('l_min_hours' || l_min_hours,30);
4305: hr_utility.set_location('l_max_hours' || l_max_hours,30);
4306: hr_utility.set_location('l_freq_cd ' || l_freq_cd ,30);
4307: --
4308: -- Use the determination code given in the criteria in ELPRO to arrive
4309: -- at the effective date to be used for retrieving Normal Hours of the person.
4302: end if;
4303: --
4304: hr_utility.set_location('l_min_hours' || l_min_hours,30);
4305: hr_utility.set_location('l_max_hours' || l_max_hours,30);
4306: hr_utility.set_location('l_freq_cd ' || l_freq_cd ,30);
4307: --
4308: -- Use the determination code given in the criteria in ELPRO to arrive
4309: -- at the effective date to be used for retrieving Normal Hours of the person.
4310: --
4338: l_use_all_asnts_elig_flag := 'Y';
4339: --
4340: end if;
4341: --
4342: hr_utility.set_location('asg eff. date ' || l_sched_effective_date ,40);
4343: hr_utility.set_location('l_use_all_asnts_elig_flag ' || l_use_all_asnts_elig_flag ,40);
4344: --
4345: open c_normal_hours (l_freq_cd,l_use_all_asnts_elig_flag) ;
4346: fetch c_normal_hours into l_normal_hours ;
4339: --
4340: end if;
4341: --
4342: hr_utility.set_location('asg eff. date ' || l_sched_effective_date ,40);
4343: hr_utility.set_location('l_use_all_asnts_elig_flag ' || l_use_all_asnts_elig_flag ,40);
4344: --
4345: open c_normal_hours (l_freq_cd,l_use_all_asnts_elig_flag) ;
4346: fetch c_normal_hours into l_normal_hours ;
4347: if c_normal_hours%found then
4345: open c_normal_hours (l_freq_cd,l_use_all_asnts_elig_flag) ;
4346: fetch c_normal_hours into l_normal_hours ;
4347: if c_normal_hours%found then
4348: --
4349: hr_utility.set_location('l_normal_hours ' || l_normal_hours ,50);
4350: close c_normal_hours ;
4351: --
4352: -- Before comparing the normal hours with the range , we have to perform
4353: -- the rounding
4453: not l_ok then
4454: --
4455: g_inelg_rsn_cd := 'SHR';
4456: fnd_message.set_name('BEN','BEN_91685_SCHED_HRS_PRFL_FAIL');
4457: hr_utility.set_location('Criteria Failed: '||l_proc,60);
4458: raise g_criteria_failed;
4459: --
4460: end if;
4461: end if;
4461: end if;
4462: --
4463: if p_score_compute_mode
4464: then
4465: hr_utility.set_location('count '||l_score_tab.count,20);
4466: write(p_profile_score_tab,l_score_tab);
4467: end if;
4468: hr_utility.set_location('Leaving: '||l_proc,70);
4469: --
4464: then
4465: hr_utility.set_location('count '||l_score_tab.count,20);
4466: write(p_profile_score_tab,l_score_tab);
4467: end if;
4468: hr_utility.set_location('Leaving: '||l_proc,70);
4469: --
4470: end check_sched_hrs_elig;
4471: -- --------------------------------------------------
4472: -- This procedure det elig based on bargaining unit.
4488: l_score_tab scoreTab;
4489: --
4490: begin
4491: --
4492: hr_utility.set_location('Entering : '||l_proc,10);
4493: --
4494: -- Getting eligibility profile bargaining unit by eligibility profile
4495: --
4496: ben_elp_cache.elpebu_getcacdets
4561: not l_ok then
4562: --
4563: g_inelg_rsn_cd := 'BRG';
4564: fnd_message.set_name('BEN','BEN_91686_BRGNG_UNIT_PRFL_FAIL');
4565: hr_utility.set_location('Criteria Failed: '||l_proc,20);
4566: raise g_criteria_failed;
4567: --
4568: end if;
4569: end if;
4569: end if;
4570: --
4571: if p_score_compute_mode
4572: then
4573: hr_utility.set_location('count '||l_score_tab.count,20);
4574: write(p_profile_score_tab,l_score_tab);
4575: end if;
4576: hr_utility.set_location('Leaving: '||l_proc,20);
4577: --
4572: then
4573: hr_utility.set_location('count '||l_score_tab.count,20);
4574: write(p_profile_score_tab,l_score_tab);
4575: end if;
4576: hr_utility.set_location('Leaving: '||l_proc,20);
4577: --
4578: end check_brgng_unit_elig;
4579: -- ----------------------------------------------------------
4580: -- This procedure det elig based on labour union membership.
4596: l_score_tab scoreTab;
4597: --
4598: begin
4599: --
4600: hr_utility.set_location('Entering : '||l_proc,10);
4601: --
4602: -- Getting eligibility profile labour union membership by eligibility profile
4603: --
4604: ben_elp_cache.elpelu_getcacdets
4669: not l_ok then
4670: --
4671: g_inelg_rsn_cd := 'LBR';
4672: fnd_message.set_name('BEN','BEN_91687_LBR_MMBR_PRFL_FAIL');
4673: hr_utility.set_location('Criteria Failed: '||l_proc,20);
4674: raise g_criteria_failed;
4675: --
4676: end if;
4677: end if;
4677: end if;
4678: --
4679: if p_score_compute_mode
4680: then
4681: hr_utility.set_location('count '||l_score_tab.count,20);
4682: write(p_profile_score_tab,l_score_tab);
4683: end if;
4684: hr_utility.set_location('Leaving: '||l_proc,20);
4685: --
4680: then
4681: hr_utility.set_location('count '||l_score_tab.count,20);
4682: write(p_profile_score_tab,l_score_tab);
4683: end if;
4684: hr_utility.set_location('Leaving: '||l_proc,20);
4685: --
4686: end check_lbr_union_elig;
4687: -- --------------------------------------------------
4688: -- This procedure det elig based on employee status.
4706: l_score_tab scoreTab;
4707: --
4708: begin
4709: --
4710: hr_utility.set_location('Entering : '||l_proc,10);
4711: --
4712: -- Getting eligibility profile assignment status type by eligibility profile
4713: --
4714: ben_elp_cache.elpees_getcacdets
4780: not l_ok then
4781: --
4782: g_inelg_rsn_cd := 'STA';
4783: fnd_message.set_name('BEN','BEN_91688_EE_STAT_PRFL_FAIL');
4784: hr_utility.set_location('Criteria Failed: '||l_proc,20);
4785: raise g_criteria_failed;
4786: --
4787: end if;
4788: end if;
4788: end if;
4789: --
4790: if p_score_compute_mode
4791: then
4792: hr_utility.set_location('count '||l_score_tab.count,20);
4793: write(p_profile_score_tab,l_score_tab);
4794: end if;
4795: hr_utility.set_location('Leaving: '||l_proc,20);
4796: --
4791: then
4792: hr_utility.set_location('count '||l_score_tab.count,20);
4793: write(p_profile_score_tab,l_score_tab);
4794: end if;
4795: hr_utility.set_location('Leaving: '||l_proc,20);
4796: --
4797: end check_ee_stat_elig;
4798: -- ---------------------------------------------------
4799: -- This procedure det elig based on leave of absence.
4838: and abs.business_group_id = p_business_group_id;
4839: --
4840: begin
4841: --
4842: hr_utility.set_location('Entering : '||l_proc,10);
4843: --
4844: -- Getting eligibility profile leave of absence reason by eligibility profile
4845: --
4846: ben_elp_cache.elpelr_getcacdets
5030: not l_ok then
5031: --
5032: g_inelg_rsn_cd := 'LOA';
5033: fnd_message.set_name('BEN','BEN_91689_PER_LOA_PRFL_FAIL');
5034: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5035: raise g_criteria_failed;
5036: --
5037: end if;
5038: end if;
5038: end if;
5039: --
5040: if p_score_compute_mode
5041: then
5042: hr_utility.set_location('count '||l_score_tab.count,20);
5043: write(p_profile_score_tab,l_score_tab);
5044: end if;
5045: hr_utility.set_location('Leaving: '||l_proc,20);
5046: --
5041: then
5042: hr_utility.set_location('count '||l_score_tab.count,20);
5043: write(p_profile_score_tab,l_score_tab);
5044: end if;
5045: hr_utility.set_location('Leaving: '||l_proc,20);
5046: --
5047: end check_loa_rsn_elig;
5048: -- -----------------------------------------------
5049: -- This procedure det elig based on legal entity.
5065: l_score_tab scoreTab;
5066: --
5067: begin
5068: --
5069: hr_utility.set_location('Entering : '||l_proc,10);
5070: --
5071: -- Getting eligibility profile legal entity by eligibility profile
5072: --
5073: ben_elp_cache.elpeln_getcacdets
5076: ,p_eligy_prfl_id => p_eligy_prfl_id
5077: ,p_inst_set => l_inst_dets
5078: ,p_inst_count => l_inst_count);
5079: --
5080: hr_utility.set_location('Got Profiles : '||l_proc,10);
5081: --
5082: if l_inst_count > 0 then
5083: --
5084: hr_utility.set_location('In Loop : '||l_proc,10);
5080: hr_utility.set_location('Got Profiles : '||l_proc,10);
5081: --
5082: if l_inst_count > 0 then
5083: --
5084: hr_utility.set_location('In Loop : '||l_proc,10);
5085: --
5086: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
5087: --
5088: l_ok := nvl((l_inst_dets(l_insttorrw_num).name = nvl(p_gre_name,'-1')),FALSE);
5142: not l_ok then
5143: --
5144: g_inelg_rsn_cd := 'LGL';
5145: fnd_message.set_name('BEN','BEN_91690_LGL_ENTY_PRFL_FAIL');
5146: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5147: raise g_criteria_failed;
5148: --
5149: end if;
5150: end if;
5150: end if;
5151: --
5152: if p_score_compute_mode
5153: then
5154: hr_utility.set_location('count '||l_score_tab.count,20);
5155: write(p_profile_score_tab,l_score_tab);
5156: end if;
5157: hr_utility.set_location('Copying gre cd : ',10);
5158: --
5153: then
5154: hr_utility.set_location('count '||l_score_tab.count,20);
5155: write(p_profile_score_tab,l_score_tab);
5156: end if;
5157: hr_utility.set_location('Copying gre cd : ',10);
5158: --
5159: hr_utility.set_location('Leaving : '||l_proc,10);
5160: --
5161: end check_lgl_enty_elig;
5155: write(p_profile_score_tab,l_score_tab);
5156: end if;
5157: hr_utility.set_location('Copying gre cd : ',10);
5158: --
5159: hr_utility.set_location('Leaving : '||l_proc,10);
5160: --
5161: end check_lgl_enty_elig;
5162: -- ----------------------------------------------------------------
5163: -- This procedure det elig based on participation in another plan.
5201: ;
5202:
5203: begin
5204: --
5205: hr_utility.set_location('Entering : '||l_proc,10);
5206: --
5207: -- Getting eligibility profile other plan by eligibility profile
5208: --
5209: ben_elp_cache.elpepp_getcacdets
5254: not l_ok then
5255: --
5256: g_inelg_rsn_cd := 'PLN';
5257: fnd_message.set_name('BEN','BEN_91691_PRTN_PL_PRFL_FAIL');
5258: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5259: raise g_criteria_failed;
5260: --
5261: end if;
5262: --
5259: raise g_criteria_failed;
5260: --
5261: end if;
5262: --
5263: hr_utility.set_location('Leaving: '||l_proc,20);
5264: --
5265: end check_prtt_in_anthr_pl_elig;
5266: -- ---------------------------------------------------------------
5267: -- This procedure det elig based on full time/part time category.
5283: l_score_tab scoreTab;
5284: --
5285: begin
5286: --
5287: hr_utility.set_location('Entering : '||l_proc,10);
5288: --
5289: -- Getting eligibility profile full/part time by eligibility profile
5290: --
5291: ben_elp_cache.elpefp_getcacdets
5359: -- There were non-excludes and we didn't match any
5360: --
5361: g_inelg_rsn_cd := 'FPT';
5362: fnd_message.set_name('BEN','BEN_91692_FL_PT_PRFL_FAIL');
5363: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5364: raise g_criteria_failed;
5365: --
5366: end if;
5367: end if;
5367: end if;
5368: --
5369: if p_score_compute_mode
5370: then
5371: hr_utility.set_location('count '||l_score_tab.count,20);
5372: write(p_profile_score_tab,l_score_tab);
5373: end if;
5374: hr_utility.set_location('Leaving: '||l_proc,20);
5375: --
5370: then
5371: hr_utility.set_location('count '||l_score_tab.count,20);
5372: write(p_profile_score_tab,l_score_tab);
5373: end if;
5374: hr_utility.set_location('Leaving: '||l_proc,20);
5375: --
5376: end check_fl_tm_pt_elig;
5377: -- ---------------------------------------------------------------
5378: -- This procedure det elig based on persons benefit balance.
5405: l_score_tab scoreTab;
5406: --
5407: begin
5408: --
5409: hr_utility.set_location('Entering: '||l_proc, 10);
5410: --
5411: -- Getting eligibility profile compensation level by eligibility profile
5412: --
5413: ben_elp_cache.elpecl_getcacdets
5518: not l_ok then
5519: --
5520: g_inelg_rsn_cd := 'CMP';
5521: fnd_message.set_name('BEN','BEN_91672_COMP_LVL_PRFL_FAIL');
5522: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5523: raise g_criteria_failed;
5524: --
5525: end if;
5526: end if;
5526: end if;
5527: --
5528: if p_score_compute_mode
5529: then
5530: hr_utility.set_location('count '||l_score_tab.count,20);
5531: write(p_profile_score_tab,l_score_tab);
5532: end if;
5533: hr_utility.set_location('Leaving :'||l_proc,20);
5534: --
5529: then
5530: hr_utility.set_location('count '||l_score_tab.count,20);
5531: write(p_profile_score_tab,l_score_tab);
5532: end if;
5533: hr_utility.set_location('Leaving :'||l_proc,20);
5534: --
5535: end check_person_ben_bal;
5536: --
5537: procedure check_asnt_set
5555: l_score_tab scoreTab;
5556: --
5557: begin
5558: --
5559: hr_utility.set_location('Entering: '||l_proc, 10);
5560: --
5561: -- Getting eligibility profile compensation level by eligibility profile
5562: --
5563: ben_elp_cache.elpean_getcacdets
5628: fnd_message.raise_error;
5629: --
5630: end;
5631: --
5632: hr_utility.set_location('Include Flag '||l_include_flag,10);
5633: --
5634: l_ok := nvl((l_include_flag = 'Y'),FALSE);
5635: --if l_ok is null then
5636: -- l_ok:=false;
5688: not l_ok then
5689: --
5690: g_inelg_rsn_cd := 'ASS';
5691: fnd_message.set_name('BEN','BEN_92459_ASS_SET_PRFL_FAIL');
5692: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5693: raise g_criteria_failed;
5694: --
5695: end if;
5696: end if;
5696: end if;
5697: --
5698: if p_score_compute_mode
5699: then
5700: hr_utility.set_location('count '||l_score_tab.count,20);
5701: write(p_profile_score_tab,l_score_tab);
5702: end if;
5703: hr_utility.set_location('Leaving :'||l_proc,20);
5704: --
5699: then
5700: hr_utility.set_location('count '||l_score_tab.count,20);
5701: write(p_profile_score_tab,l_score_tab);
5702: end if;
5703: hr_utility.set_location('Leaving :'||l_proc,20);
5704: --
5705: end check_asnt_set;
5706: ------------------------------------------------------------------------
5707: -- ---------------------------------------------------------------
5735: l_score_tab scoreTab;
5736: --
5737: begin
5738: --
5739: hr_utility.set_location('Entering: '||l_proc, 10);
5740: --
5741: -- Getting eligibility profile compensation level by eligibility profile
5742: --
5743: ben_elp_cache.elpecl_getcacdets
5844: not l_ok then
5845: --
5846: g_inelg_rsn_cd := 'CMP';
5847: fnd_message.set_name('BEN','BEN_91672_COMP_LVL_PRFL_FAIL');
5848: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5849: raise g_criteria_failed;
5850: --
5851: end if;
5852: end if;
5852: end if;
5853: --
5854: if p_score_compute_mode
5855: then
5856: hr_utility.set_location('count '||l_score_tab.count,20);
5857: write(p_profile_score_tab,l_score_tab);
5858: end if;
5859: hr_utility.set_location('Leaving :'||l_proc,20);
5860: --
5855: then
5856: hr_utility.set_location('count '||l_score_tab.count,20);
5857: write(p_profile_score_tab,l_score_tab);
5858: end if;
5859: hr_utility.set_location('Leaving :'||l_proc,20);
5860: --
5861: end check_person_balance;
5862: --------------------------------------------------------------------
5863: --
5879: l_rec per_all_people_f%rowtype;
5880: --
5881: begin
5882: --
5883: hr_utility.set_location('Entering: '||l_proc, 10);
5884: --
5885: -- Getting eligibility profile compensation level by eligibility profile
5886: --
5887: ben_elp_cache.elpeno_getcacdets
5919: not l_ok then
5920: --
5921: g_inelg_rsn_cd := 'ENO';
5922: fnd_message.set_name('BEN','BEN_92227_ENO_ELIG_PRFL_FAIL');
5923: hr_utility.set_location('Criteria Failed: '||l_proc,20);
5924: raise g_criteria_failed;
5925: --
5926: end if;
5927: --
5924: raise g_criteria_failed;
5925: --
5926: end if;
5927: --
5928: hr_utility.set_location('Leaving :'||l_proc,20);
5929: --
5930: end check_elig_no_othr_cvg_prte;
5931: --
5932: -- --------------------------------------------------------------------------
5952: l_crit_passed boolean;
5953: l_score_tab scoreTab;
5954: begin
5955: --
5956: hr_utility.set_location('Entering: '||l_proc, 10);
5957: --
5958: -- Getting eligibility profile compensation level by eligibility profile
5959: --
5960: ben_elp_cache.elpelv_getcacdets
6036: not l_ok then
6037: --
6038: g_inelg_rsn_cd := 'ELV';
6039: fnd_message.set_name('BEN','BEN_92228_ELV_ELIG_PRFL_FAIL');
6040: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6041: raise g_criteria_failed;
6042: --
6043: end if;
6044: end if;
6044: end if;
6045: --
6046: if p_score_compute_mode
6047: then
6048: hr_utility.set_location('count '||l_score_tab.count,20);
6049: write(p_profile_score_tab,l_score_tab);
6050: end if;
6051: hr_utility.set_location('Leaving :'||l_proc,20);
6052: --
6047: then
6048: hr_utility.set_location('count '||l_score_tab.count,20);
6049: write(p_profile_score_tab,l_score_tab);
6050: end if;
6051: hr_utility.set_location('Leaving :'||l_proc,20);
6052: --
6053: end check_elig_lvg_rsn_prte;
6054: --
6055: -- --------------------------------------------------------------------------
6070: l_rec per_all_people_f%rowtype;
6071: --
6072: begin
6073: --
6074: hr_utility.set_location('Entering: '||l_proc, 10);
6075: --
6076: -- Getting eligibility profile compensation level by eligibility profile
6077: --
6078: ben_elp_cache.elpeom_getcacdets
6112: not l_ok then
6113: --
6114: g_inelg_rsn_cd := 'EOM';
6115: fnd_message.set_name('BEN','BEN_92229_EOM_ELIG_PRFL_FAIL');
6116: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6117: raise g_criteria_failed;
6118: --
6119: end if;
6120: --
6117: raise g_criteria_failed;
6118: --
6119: end if;
6120: --
6121: hr_utility.set_location('Leaving :'||l_proc,20);
6122: --
6123: end check_elig_optd_mdcr_prte;
6124: --
6125: -- --------------------------------------------------------------------------
6144: l_dummy varchar2(1);
6145: --
6146: begin
6147: --
6148: hr_utility.set_location('Entering: '||l_proc, 10);
6149: --
6150: -- Getting eligibility profile enrld in another plan by eligibility profile
6151: --
6152: ben_elp_cache.elpeep_getcacdets
6228: not l_ok then
6229: --
6230: g_inelg_rsn_cd := 'EEP';
6231: fnd_message.set_name('BEN','BEN_92230_EEP_ELIG_PRFL_FAIL');
6232: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6233: raise g_criteria_failed;
6234: --
6235: end if;
6236: --
6233: raise g_criteria_failed;
6234: --
6235: end if;
6236: --
6237: hr_utility.set_location('Leaving :'||l_proc,20);
6238: --
6239: end check_elig_enrld_anthr_pl;
6240: --
6241: -- --------------------------------------------------------------------------
6272: and pen.prtt_enrt_rslt_stat_cd is null;
6273: --
6274: begin
6275: --
6276: hr_utility.set_location('Entering: '||l_proc, 10);
6277: --
6278: -- Getting eligibility profile compensation level by eligibility profile
6279: --
6280: ben_elp_cache.elpeei_getcacdets
6358: not l_ok then
6359: --
6360: g_inelg_rsn_cd := 'EEI';
6361: fnd_message.set_name('BEN','BEN_92231_EEI_ELIG_PRFL_FAIL');
6362: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6363: raise g_criteria_failed;
6364: --
6365: end if;
6366: --
6363: raise g_criteria_failed;
6364: --
6365: end if;
6366: --
6367: hr_utility.set_location('Leaving :'||l_proc,20);
6368: --
6369: end check_elig_enrld_anthr_oipl;
6370: --
6371: -- --------------------------------------------------------------------------
6393: l_dummy varchar2(1);
6394: --
6395: begin
6396: --
6397: hr_utility.set_location('Entering: '||l_proc, 10);
6398: hr_utility.set_location('prfl_id: '||p_eligy_prfl_id, 10);
6399: --
6400: -- Getting eligibility profile compensation level by eligibility profile
6401: --
6394: --
6395: begin
6396: --
6397: hr_utility.set_location('Entering: '||l_proc, 10);
6398: hr_utility.set_location('prfl_id: '||p_eligy_prfl_id, 10);
6399: --
6400: -- Getting eligibility profile compensation level by eligibility profile
6401: --
6402: ben_elp_cache.elpeeg_getcacdets
6482: not l_ok then
6483: --
6484: g_inelg_rsn_cd := 'EEG';
6485: fnd_message.set_name('BEN','BEN_92232_EEG_ELIG_PRFL_FAIL');
6486: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6487: raise g_criteria_failed;
6488: --
6489: end if;
6490: --
6487: raise g_criteria_failed;
6488: --
6489: end if;
6490: --
6491: hr_utility.set_location('Leaving :'||l_proc,20);
6492: --
6493: end check_elig_enrld_anthr_pgm;
6494: --
6495: -- --------------------------------------------------------------------------
6532: and pdp.effective_end_date = hr_api.g_eot;
6533: --
6534: begin
6535: --
6536: hr_utility.set_location('Entering: '||l_proc, 10);
6537: --
6538: -- Getting eligibility profile compensation level by eligibility profile
6539: --
6540: ben_elp_cache.elpedp_getcacdets
6601: not l_ok then
6602: --
6603: g_inelg_rsn_cd := 'EDP';
6604: fnd_message.set_name('BEN','BEN_92233_EDP_ELIG_PRFL_FAIL');
6605: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6606: raise g_criteria_failed;
6607: --
6608: end if;
6609: --
6606: raise g_criteria_failed;
6607: --
6608: end if;
6609: --
6610: hr_utility.set_location('Leaving :'||l_proc,20);
6611: --
6612: end check_elig_dpnt_cvrd_othr_pl;
6613: --
6614: -- --------------------------------------------------------------------------
6650: and pen.prtt_enrt_rslt_stat_cd is null;
6651: --
6652: begin
6653: --
6654: hr_utility.set_location('Entering: '||l_proc, 10);
6655: --
6656: -- Getting eligibility profile compensation level by eligibility profile
6657: --
6658: ben_elp_cache.elpeai_getcacdets
6721: not l_ok then
6722: --
6723: g_inelg_rsn_cd := 'EAI';
6724: fnd_message.set_name('BEN','BEN_92420_EAI_ELIG_PRFL_FAIL');
6725: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6726: raise g_criteria_failed;
6727: --
6728: end if;
6729: --
6726: raise g_criteria_failed;
6727: --
6728: end if;
6729: --
6730: hr_utility.set_location('Leaving :'||l_proc,20);
6731: --
6732: end check_elig_enrld_anthr_plip;
6733: --
6734: -- --------------------------------------------------------------------------
6779: and pdp.effective_end_date = hr_api.g_eot;
6780: --
6781: begin
6782: --
6783: hr_utility.set_location('Entering: '||l_proc, 10);
6784: --
6785: -- Getting eligibility profile compensation level by eligibility profile
6786: --
6787: ben_elp_cache.elpedi_getcacdets
6850: not l_ok then
6851: --
6852: g_inelg_rsn_cd := 'EDI';
6853: fnd_message.set_name('BEN','BEN_92421_EDI_ELIG_PRFL_FAIL');
6854: hr_utility.set_location('Criteria Failed: '||l_proc,20);
6855: raise g_criteria_failed;
6856: --
6857: end if;
6858: --
6855: raise g_criteria_failed;
6856: --
6857: end if;
6858: --
6859: hr_utility.set_location('Leaving :'||l_proc,20);
6860: --
6861: end check_elig_dpnt_cvrd_othr_plip;
6862: --
6863: -- --------------------------------------------------------------------------
6906:
6907: --
6908: begin
6909: --
6910: hr_utility.set_location('Entering: '||l_proc, 10);
6911: hr_utility.set_location('l_fonm_cvg_strt_dt: '||l_fonm_cvg_strt_dt, 10);
6912: --
6913: -- Getting eligibility profile compensation level by eligibility profile
6914: --
6907: --
6908: begin
6909: --
6910: hr_utility.set_location('Entering: '||l_proc, 10);
6911: hr_utility.set_location('l_fonm_cvg_strt_dt: '||l_fonm_cvg_strt_dt, 10);
6912: --
6913: -- Getting eligibility profile compensation level by eligibility profile
6914: --
6915: ben_elp_cache.elpeet_getcacdets
6918: p_eligy_prfl_id => p_eligy_prfl_id,
6919: p_inst_set => l_inst_dets,
6920: p_inst_count => l_inst_count);
6921: --
6922: hr_utility.set_location('l_inst_count: '||l_inst_count, 10);
6923: if l_inst_count > 0 then
6924: --
6925: -- Operation
6926: -- =========
6932: -- then no problem.
6933: -- 5) If person eligible for any of the plans and exclude flag = 'N'
6934: -- then fail criteria.
6935: --
6936: hr_utility.set_location('Getting profiles',10);
6937: <
6938: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
6939: --
6940: l_rows_found := true;
6938: for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
6939: --
6940: l_rows_found := true;
6941: --
6942: hr_utility.set_location('Getting results' || ben_manage_life_events.g_cache_person_prtn.last ,10);
6943:
6944: <
6945: for l_count in nvl(ben_manage_life_events.g_cache_person_prtn.first,0)..
6946: nvl(ben_manage_life_events.g_cache_person_prtn.last,-1) loop
6957: p_fonm_cvg_strt_dt => ben_manage_life_events.g_fonm_cvg_strt_dt,
6958: p_fonm_rt_strt_dt => ben_manage_life_events.g_fonm_rt_strt_dt,
6959: p_returned_date => l_date_to_use);
6960: --
6961: hr_utility.set_location( 'l_date_to_use ' || l_date_to_use , 99 ) ;
6962:
6963: -- 4204020 l_date_to_use := NVL(l_fonm_cvg_strt_dt,l_date_to_use);
6964: hr_utility.set_location( 'l_date_to_use ' || l_date_to_use , 99 ) ;
6965: --
6960: --
6961: hr_utility.set_location( 'l_date_to_use ' || l_date_to_use , 99 ) ;
6962:
6963: -- 4204020 l_date_to_use := NVL(l_fonm_cvg_strt_dt,l_date_to_use);
6964: hr_utility.set_location( 'l_date_to_use ' || l_date_to_use , 99 ) ;
6965: --
6966: -- If only check plans that are subject to COBRA.
6967: --
6968: l_continue := true;
6966: -- If only check plans that are subject to COBRA.
6967: --
6968: l_continue := true;
6969: --
6970: hr_utility.set_location('Getting cobra plans',10);
6971: --
6972: if l_inst_dets(l_insttorrw_num).only_pls_subj_cobra_flag = 'Y' then
6973: --
6974: open c1(ben_manage_life_events.g_cache_person_prtn(l_count).pl_id,
6977: fetch c1 into l_dummy;
6978: --
6979: if c1%notfound then
6980: --
6981: hr_utility.set_location('Cobra plans not found',10);
6982: l_continue := false;
6983: --
6984: end if;
6985: --
6988: end if;
6989: --
6990: if l_continue then
6991: --
6992: hr_utility.set_location('Cobra plans found',10);
6993: --
6994: if (l_date_to_use
6995: between ben_manage_life_events.
6996: g_cache_person_prtn(l_count).enrt_cvg_strt_dt
6997: and ben_manage_life_events.
6998: g_cache_person_prtn(l_count).enrt_cvg_thru_dt) then
6999: --
7000: l_found_ptip := true;
7001: hr_utility.set_location('cobra plans found',10);
7002: --
7003: if l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
7004: --
7005: hr_utility.set_location('Exclude flags = N Cobra plans found',10);
7001: hr_utility.set_location('cobra plans found',10);
7002: --
7003: if l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
7004: --
7005: hr_utility.set_location('Exclude flags = N Cobra plans found',10);
7006: l_ok := true;
7007: exit prfl;
7008: --
7009: end if;
7009: end if;
7010: --
7011: if l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
7012: --
7013: hr_utility.set_location('Exclude flags = Y Cobra plans found',10);
7014: l_ok := false;
7015: exit prfl;
7016: --
7017: end if;
7040: not l_ok then
7041: --
7042: g_inelg_rsn_cd := 'EET';
7043: fnd_message.set_name('BEN','BEN_92422_EET_ELIG_PRFL_FAIL');
7044: hr_utility.set_location('Criteria Failed: '||l_proc,20);
7045: raise g_criteria_failed;
7046: --
7047: end if;
7048: --
7045: raise g_criteria_failed;
7046: --
7047: end if;
7048: --
7049: hr_utility.set_location('Leaving :'||l_proc,20);
7050: --
7051: end check_elig_enrld_anthr_ptip;
7052: --
7053: -- --------------------------------------------------------------------------
7110: and reg.effective_end_date;
7111: --
7112: begin
7113: --
7114: hr_utility.set_location('Entering: '||l_proc, 10);
7115: --
7116: -- Getting eligibility profile compensation level by eligibility profile
7117: --
7118: ben_elp_cache.elpedt_getcacdets
7197: not l_ok then
7198: --
7199: g_inelg_rsn_cd := 'EDT';
7200: fnd_message.set_name('BEN','BEN_92423_EDT_ELIG_PRFL_FAIL');
7201: hr_utility.set_location('Criteria Failed: '||l_proc,20);
7202: raise g_criteria_failed;
7203: --
7204: end if;
7205: --
7202: raise g_criteria_failed;
7203: --
7204: end if;
7205: --
7206: hr_utility.set_location('Leaving :'||l_proc,20);
7207: --
7208: end check_elig_dpnt_cvrd_othr_ptip;
7209: --
7210: -- --------------------------------------------------------------------------
7246: and pdp.effective_end_date = hr_api.g_eot;
7247: --
7248: begin
7249: --
7250: hr_utility.set_location('Entering: '||l_proc, 10);
7251: --
7252: -- Getting eligibility profile compensation level by eligibility profile
7253: --
7254: ben_elp_cache.elpedg_getcacdets
7315: not l_ok then
7316: --
7317: g_inelg_rsn_cd := 'EDG';
7318: fnd_message.set_name('BEN','BEN_92424_EDG_ELIG_PRFL_FAIL');
7319: hr_utility.set_location('Criteria Failed: '||l_proc,20);
7320: raise g_criteria_failed;
7321: --
7322: end if;
7323: --
7320: raise g_criteria_failed;
7321: --
7322: end if;
7323: --
7324: hr_utility.set_location('Leaving :'||l_proc,20);
7325: --
7326: end check_elig_dpnt_cvrd_othr_pgm;
7327: --
7328: -- --------------------------------------------------------------------------
7375:
7376: --
7377: begin
7378: --
7379: hr_utility.set_location('Entering: '||l_proc, 10);
7380: --
7381: -- Getting eligibility profile compensation level by eligibility profile
7382: --
7383: ben_elp_cache.elpecq_getcacdets
7424: not l_ok then
7425: --
7426: g_inelg_rsn_cd := 'ECQ';
7427: fnd_message.set_name('BEN','BEN_92425_ECQ_ELIG_PRFL_FAIL');
7428: hr_utility.set_location('Criteria Failed: '||l_proc,20);
7429: raise g_criteria_failed;
7430: --
7431: end if;
7432: --
7429: raise g_criteria_failed;
7430: --
7431: end if;
7432: --
7433: hr_utility.set_location('Leaving :'||l_proc,20);
7434: --
7435: end check_elig_cbr_quald_bnf;
7436: -- ----------------------------------------------------------------------------
7437: -- Disabled
7455: l_score_tab scoreTab;
7456: --
7457: begin
7458: --
7459: hr_utility.set_location('Entering : '||l_proc,10);
7460: --
7461: ben_elp_cache.elpeds_getdets
7462: (p_effective_date => p_effective_date
7463: ,p_eligy_prfl_id => p_eligy_prfl_id
7476: l_ok := nvl((nvl(p_dsbld_cd,'xxxx') = l_inst_set(l_ele_num).v230_val),FALSE);
7477: --
7478: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
7479: --
7480: hr_utility.set_location('Exclude Flag = N, Disabled Code = ' || p_dsbld_cd || ' found ', 20 );
7481: if p_score_compute_mode then
7482: if l_crit_passed is null then
7483: l_crit_passed := true;
7484: end if;
7493: end if;
7494: --
7495: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
7496: --
7497: hr_utility.set_location('Exclude Flag = Y, Disabled Code = ' || p_dsbld_cd || ' found ', 20 );
7498: l_ok := false;
7499: exit;
7500: --
7501: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
7529: then
7530: --
7531: g_inelg_rsn_cd := 'DSB';
7532: fnd_message.set_name('BEN','BEN_93080_DSBLD_PRFL_FAIL');
7533: hr_utility.set_location('Criteria Failed: '||l_proc,40);
7534: raise g_criteria_failed;
7535: --
7536: end if;
7537: end if;
7537: end if;
7538: --
7539: if p_score_compute_mode
7540: then
7541: hr_utility.set_location('count '||l_score_tab.count,20);
7542: write(p_profile_score_tab,l_score_tab);
7543: end if;
7544: hr_utility.set_location('Leaving: '||l_proc,50);
7545: --
7540: then
7541: hr_utility.set_location('count '||l_score_tab.count,20);
7542: write(p_profile_score_tab,l_score_tab);
7543: end if;
7544: hr_utility.set_location('Leaving: '||l_proc,50);
7545: --
7546: end check_dsbld_elig;
7547: --
7548: -- ----------------------------------------------------------------------------
7567: l_score_tab scoreTab;
7568: --
7569: begin
7570: --
7571: hr_utility.set_location('Entering : '||l_proc,10);
7572: --
7573: ben_elp_cache.elpetu_getdets
7574: (p_effective_date => p_effective_date
7575: ,p_eligy_prfl_id => p_eligy_prfl_id
7588: l_ok := nvl((nvl(p_tbco_use_flag,'xxxx') = l_inst_set(l_ele_num).v230_val),FALSE);
7589: --
7590: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
7591: --
7592: hr_utility.set_location('Exclude Flag = N, Tobacco Use = ' || p_tbco_use_flag || ' found ', 20 );
7593: if p_score_compute_mode then
7594: if l_crit_passed is null then
7595: l_crit_passed := true;
7596: end if;
7605: end if;
7606: --
7607: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
7608: --
7609: hr_utility.set_location('Exclude Flag = Y, Tobacco Use = ' || p_tbco_use_flag || ' found ', 20 );
7610: l_ok := false;
7611: exit;
7612: --
7613: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
7641: then
7642: --
7643: g_inelg_rsn_cd := 'ETU';
7644: fnd_message.set_name('BEN','BEN_93081_TBCO_USE_PRFL_FAIL');
7645: hr_utility.set_location('Criteria Failed: '||l_proc,40);
7646: raise g_criteria_failed;
7647: --
7648: end if;
7649: end if;
7649: end if;
7650: --
7651: if p_score_compute_mode
7652: then
7653: hr_utility.set_location('count '||l_score_tab.count,20);
7654: write(p_profile_score_tab,l_score_tab);
7655: end if;
7656: hr_utility.set_location('Leaving: '||l_proc,50);
7657: --
7652: then
7653: hr_utility.set_location('count '||l_score_tab.count,20);
7654: write(p_profile_score_tab,l_score_tab);
7655: end if;
7656: hr_utility.set_location('Leaving: '||l_proc,50);
7657: --
7658: end check_tbco_use_elig;
7659: --
7660: -- ----------------------------------------------------------------------------
7707: and pen.effective_end_date = l_eot;
7708: --
7709: begin
7710: --
7711: hr_utility.set_location('Entering: '||l_proc, 10);
7712: --
7713: ben_elp_cache.elpetc_getdets
7714: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
7715: ,p_eligy_prfl_id => p_eligy_prfl_id
7739: if nvl(l_ttl_cvg_vol,0)
7740: between nvl(l_inst_set(l_ele_num).num_val,0)
7741: and nvl(l_inst_set(l_ele_num).num_val1 - 1 , 999999999999999) -- 2431619
7742: then
7743: hr_utility.set_location(l_proc, 20);
7744: l_ok := true;
7745: exit;
7746: --
7747: elsif nvl(l_ttl_cvg_vol,0) = nvl(l_inst_set(l_ele_num).num_val1, 999999999999999) then -- bug 2431619
7752: p_business_group_id ) ;
7753: fetch c_pen_found into l_dummy;
7754: if c_pen_found%found then
7755: --
7756: hr_utility.set_location(l_proc, 30);
7757: close c_pen_found;
7758: l_ok := true;
7759: exit;
7760: --
7759: exit;
7760: --
7761: else
7762: --
7763: hr_utility.set_location(l_proc, 40);
7764: close c_pen_found;
7765: l_ok := false;
7766: --
7767: end if;
7767: end if;
7768: -- -- end 2431619
7769: else
7770: --
7771: hr_utility.set_location(l_proc, 50);
7772: l_ok := false;
7773: --
7774: end if;
7775: --
7784: then
7785: --
7786: g_inelg_rsn_cd := 'ETC';
7787: fnd_message.set_name('BEN','BEN_93082_TTLCVGVOL_PRFL_FAIL');
7788: hr_utility.set_location('Criteria Failed: '||l_proc,60);
7789: raise g_criteria_failed;
7790: --
7791: end if;
7792: --
7789: raise g_criteria_failed;
7790: --
7791: end if;
7792: --
7793: hr_utility.set_location('Leaving: '||l_proc,50);
7794: --
7795: end check_ttl_cvg_vol_elig;
7796: --
7797: -- ----------------------------------------------------------------------------
7844: and pen.effective_end_date = l_eot;
7845: --
7846: begin
7847: --
7848: hr_utility.set_location('Entering: '||l_proc, 10);
7849: --
7850: ben_elp_cache.elpetp_getdets
7851: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
7852: ,p_eligy_prfl_id => p_eligy_prfl_id
7871: for i in l_inst_set.first .. l_inst_set.last loop
7872: --
7873: l_rows_found := true;
7874: --
7875: hr_utility.set_location('l_ttl_prtt ' || l_ttl_prtt || ' num_val1 ' || l_inst_set(l_ele_num).num_val1, -729);
7876: if nvl(l_ttl_prtt,0)
7877: between nvl(l_inst_set(l_ele_num).num_val,0)
7878: and nvl(l_inst_set(l_ele_num).num_val1 - 1 , 999999999999999) -- bug 2431619
7879: then
7876: if nvl(l_ttl_prtt,0)
7877: between nvl(l_inst_set(l_ele_num).num_val,0)
7878: and nvl(l_inst_set(l_ele_num).num_val1 - 1 , 999999999999999) -- bug 2431619
7879: then
7880: hr_utility.set_location(l_proc, 20);
7881: l_ok := true;
7882: exit;
7883: --
7884: elsif nvl(l_ttl_prtt,0) = nvl(l_inst_set(l_ele_num).num_val1, 999999999999999) then -- bug 2431619
7889: p_business_group_id ) ;
7890: fetch c_pen_found into l_dummy;
7891: if c_pen_found%found then
7892: --
7893: hr_utility.set_location(l_proc, 30);
7894: close c_pen_found;
7895: l_ok := true;
7896: exit;
7897: --
7896: exit;
7897: --
7898: else
7899: --
7900: hr_utility.set_location(l_proc, 40);
7901: close c_pen_found;
7902: l_ok := false;
7903: --
7904: end if;
7904: end if;
7905: -- -- end 2431619
7906: else
7907: --
7908: hr_utility.set_location(l_proc, 50);
7909: l_ok := false;
7910: --
7911: end if;
7912: --
7921: then
7922: --
7923: g_inelg_rsn_cd := 'ETP';
7924: fnd_message.set_name('BEN','BEN_93083_TTL_PRTT_PRFL_FAIL');
7925: hr_utility.set_location('Criteria Failed: '||l_proc,60);
7926: raise g_criteria_failed;
7927: --
7928: end if;
7929: --
7926: raise g_criteria_failed;
7927: --
7928: end if;
7929: --
7930: hr_utility.set_location('Leaving: '||l_proc,50);
7931: --
7932: end check_ttl_prtt_elig;
7933: --
7934: -- ----------------------------------------------------------------------------
7969: and pen.effective_end_date = l_eot;
7970: --
7971: begin
7972: --
7973: hr_utility.set_location('Entering: '||l_proc, 10);
7974: --
7975: ben_elp_cache.elpeop_getdets
7976: (p_effective_date => p_effective_date
7977: ,p_eligy_prfl_id => p_eligy_prfl_id
8004: --
8005: --
8006: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
8007: --
8008: hr_utility.set_location('Exclude Flag = N, Participation in Plan id = ' || l_inst_set(l_ele_num).num_val || ' found ', 20 );
8009: exit;
8010: --
8011: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8012: --
8009: exit;
8010: --
8011: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8012: --
8013: hr_utility.set_location('Exclude Flag = Y, Participation in Plan id = ' || l_inst_set(l_ele_num).num_val || ' found ', 20 );
8014: l_ok := false;
8015: exit;
8016: --
8017: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
8031: then
8032: --
8033: g_inelg_rsn_cd := 'EOP';
8034: fnd_message.set_name('BEN','BEN_93084_ANTHR_PL_PRFL_FAIL');
8035: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8036: raise g_criteria_failed;
8037: --
8038: end if;
8039: --
8036: raise g_criteria_failed;
8037: --
8038: end if;
8039: --
8040: hr_utility.set_location('Leaving: '||l_proc,50);
8041: --
8042: end check_anthr_pl_elig;
8043: --
8044: -- ----------------------------------------------------------------------------
8087:
8088: --
8089: begin
8090: --
8091: hr_utility.set_location('Entering: '||l_proc, 10);
8092: --
8093: ben_elp_cache.elpehc_getdets
8094: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
8095: ,p_eligy_prfl_id => p_eligy_prfl_id
8123: --
8124: --
8125: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
8126: --
8127: hr_utility.set_location('Exclude Flag = N, Health Coverage in OIPL id = ' || l_inst_set(l_ele_num).num_val1 || ' found ', 20 );
8128: exit;
8129: --
8130: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8131: --
8128: exit;
8129: --
8130: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8131: --
8132: hr_utility.set_location('Exclude Flag = Y, Health Coverage in OIPL id = ' || l_inst_set(l_ele_num).num_val1 || ' found ', 20 );
8133: l_ok := false;
8134: exit;
8135: --
8136: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
8150: then
8151: --
8152: g_inelg_rsn_cd := 'EHC';
8153: fnd_message.set_name('BEN','BEN_93085_HLTH_CVG_PRFL_FAIL');
8154: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8155: raise g_criteria_failed;
8156: --
8157: end if;
8158: --
8155: raise g_criteria_failed;
8156: --
8157: end if;
8158: --
8159: hr_utility.set_location('Leaving: '||l_proc,50);
8160: --
8161: end check_hlth_cvg_elig;
8162: --
8163: -- ----------------------------------------------------------------------------
8203: and nvl(cmp.effective_date_to, cp_effective_date) ;
8204: --
8205: begin
8206: --
8207: hr_utility.set_location('Entering: '||l_proc, 10);
8208: --
8209: ben_elp_cache.elpecy_getdets
8210: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
8211: ,p_eligy_prfl_id => p_eligy_prfl_id
8248: --
8249: --
8250: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
8251: --
8252: 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 );
8253: if p_score_compute_mode then
8254: if l_crit_passed is null then
8255: l_crit_passed := true;
8256: end if;
8265: end if;
8266: --
8267: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8268: --
8269: 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 );
8270: l_ok := false;
8271: exit;
8272: --
8273: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
8297: then
8298: --
8299: g_inelg_rsn_cd := 'ECY';
8300: fnd_message.set_name('BEN','BEN_93086_COMPTNCY_PRFL_FAIL');
8301: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8302: raise g_criteria_failed;
8303: --
8304: end if;
8305: end if;
8305: end if;
8306: --
8307: if p_score_compute_mode
8308: then
8309: hr_utility.set_location('count '||l_score_tab.count,20);
8310: write(p_profile_score_tab,l_score_tab);
8311: end if;
8312: hr_utility.set_location('Leaving: '||l_proc,50);
8313: --
8308: then
8309: hr_utility.set_location('count '||l_score_tab.count,20);
8310: write(p_profile_score_tab,l_score_tab);
8311: end if;
8312: hr_utility.set_location('Leaving: '||l_proc,50);
8313: --
8314: end check_comptncy_elig;
8315: --
8316: -- ----------------------------------------------------------------------------
8390: ORDER BY ppr.review_date desc;
8391: --
8392: begin
8393: --
8394: hr_utility.set_location('Entering: '||l_proc, 10);
8395: --
8396: if p_perf_rtng_cd is null then
8397: ben_elp_cache.elpepr_getdets
8398: (p_effective_date => p_effective_date
8495: end if;
8496: --
8497: if l_ok and l_inst_set(l_ele_num).excld_flag = 'N' then
8498: --
8499: 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 );
8500: if p_score_compute_mode then
8501: if l_crit_passed is null then
8502: l_crit_passed := true;
8503: end if;
8512: end if;
8513: --
8514: elsif l_ok and l_inst_set(l_ele_num).excld_flag = 'Y' then
8515: --
8516: 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 );
8517: l_ok := false;
8518: exit;
8519: --
8520: elsif (not l_ok) and l_inst_set(l_ele_num).excld_flag = 'Y' then
8543: then
8544: --
8545: g_inelg_rsn_cd := 'ERG';
8546: fnd_message.set_name('BEN','BEN_93108_PERF_RTNG_PRFL_FAIL');
8547: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8548: raise g_criteria_failed;
8549: --
8550: end if;
8551: end if;
8551: end if;
8552: --
8553: if p_score_compute_mode
8554: then
8555: hr_utility.set_location('count '||l_score_tab.count,20);
8556: write(p_profile_score_tab,l_score_tab);
8557: end if;
8558: hr_utility.set_location('Leaving: '||l_proc,50);
8559: --
8554: then
8555: hr_utility.set_location('count '||l_score_tab.count,20);
8556: write(p_profile_score_tab,l_score_tab);
8557: end if;
8558: hr_utility.set_location('Leaving: '||l_proc,50);
8559: --
8560: end check_perf_rtng_elig;
8561: --
8562: -- --------------------------------------------------
8673: end;
8674: --
8675: begin
8676: --
8677: hr_utility.set_location('Entering: '||l_proc, 10);
8678: --
8679: ben_elp_cache.elpeqg_getdets
8680: (p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
8681: ,p_eligy_prfl_id => p_eligy_prfl_id
8754: ,p_max => nvl(l_max_val,0)
8755: ,p_code => l_inst_set(l_ele_num).v230_val
8756: ,p_min_qualifier => l_min_qualifier
8757: ,p_max_qualifier => l_max_qualifier );
8758: hr_utility.set_location('ACE l_min_qualifier = ' || l_min_qualifier, 9999);
8759: hr_utility.set_location('ACE l_max_qualifier = ' || l_max_qualifier, 9999);
8760:
8761: --
8762: if l_inst_set(l_ele_num).v230_val = 'ABV' then
8755: ,p_code => l_inst_set(l_ele_num).v230_val
8756: ,p_min_qualifier => l_min_qualifier
8757: ,p_max_qualifier => l_max_qualifier );
8758: hr_utility.set_location('ACE l_min_qualifier = ' || l_min_qualifier, 9999);
8759: hr_utility.set_location('ACE l_max_qualifier = ' || l_max_qualifier, 9999);
8760:
8761: --
8762: if l_inst_set(l_ele_num).v230_val = 'ABV' then
8763: l_in_quartile := l_ann_sal > nvl(l_max_val,0);
8820: then
8821: --
8822: g_inelg_rsn_cd := 'EQG';
8823: fnd_message.set_name('BEN','BEN_93107_QUA_IN_GR_PRFL_FAIL');
8824: hr_utility.set_location('Criteria Failed: '||l_proc,40);
8825: raise g_criteria_failed;
8826: --
8827: end if;
8828: end if;
8828: end if;
8829: --
8830: if p_score_compute_mode
8831: then
8832: hr_utility.set_location('count '||l_score_tab.count,20);
8833: write(p_profile_score_tab,l_score_tab);
8834: end if;
8835: hr_utility.set_location('Leaving: '||l_proc,50);
8836: --
8831: then
8832: hr_utility.set_location('count '||l_score_tab.count,20);
8833: write(p_profile_score_tab,l_score_tab);
8834: end if;
8835: hr_utility.set_location('Leaving: '||l_proc,50);
8836: --
8837: end check_qua_in_gr_elig;
8838: --
8839: -- Public Function
9121: l_proc varchar2(30) := 'assign_overriden_values';
9122:
9123: begin
9124:
9125: hr_utility.set_location('Entering :'||l_proc,10);
9126:
9127: if not (pqh_popl_criteria_ovrrd.g_criteria_count>0) then
9128: hr_utility.set_location('No overridden values',10);
9129: hr_utility.set_location('Leaving :'||l_proc,10);
9124:
9125: hr_utility.set_location('Entering :'||l_proc,10);
9126:
9127: if not (pqh_popl_criteria_ovrrd.g_criteria_count>0) then
9128: hr_utility.set_location('No overridden values',10);
9129: hr_utility.set_location('Leaving :'||l_proc,10);
9130: return;
9131: end if;
9132:
9125: hr_utility.set_location('Entering :'||l_proc,10);
9126:
9127: if not (pqh_popl_criteria_ovrrd.g_criteria_count>0) then
9128: hr_utility.set_location('No overridden values',10);
9129: hr_utility.set_location('Leaving :'||l_proc,10);
9130: return;
9131: end if;
9132:
9133: hr_utility.set_location('Overridden count '||pqh_popl_criteria_ovrrd.g_criteria_override_val.count,10);
9129: hr_utility.set_location('Leaving :'||l_proc,10);
9130: return;
9131: end if;
9132:
9133: hr_utility.set_location('Overridden count '||pqh_popl_criteria_ovrrd.g_criteria_override_val.count,10);
9134:
9135: for i in 1..pqh_popl_criteria_ovrrd.g_criteria_override_val.count
9136: loop
9137: l_crit_ovrrd_val_rec := pqh_popl_criteria_ovrrd.g_criteria_override_val(i);
9134:
9135: for i in 1..pqh_popl_criteria_ovrrd.g_criteria_override_val.count
9136: loop
9137: l_crit_ovrrd_val_rec := pqh_popl_criteria_ovrrd.g_criteria_override_val(i);
9138: hr_utility.set_location('Short code '||l_crit_ovrrd_val_rec.criteria_short_code,10);
9139: if l_crit_ovrrd_val_rec.criteria_short_code is null then
9140: exit;
9141: end if;
9142:
9222: fetch check_gen_criteria into l_dummy;
9223: if check_gen_criteria%found then
9224: l_crit_ovrrd_val_tab(l_crit_ovrrd_val_tab.count+1) := l_crit_ovrrd_val_rec;
9225: else
9226: hr_utility.set_location('Unknown criteria '||l_crit_ovrrd_val_rec.criteria_short_code,10);
9227: end if;
9228: close check_gen_criteria;
9229:
9230: end if;
9230: end if;
9231:
9232: end loop;
9233:
9234: hr_utility.set_location('Leaving :'||l_proc,10);
9235:
9236: end assign_overriden_values;
9237:
9238: begin
9236: end assign_overriden_values;
9237:
9238: begin
9239: --
9240: hr_utility.set_location('Entering :'||l_proc,10);
9241: --
9242: -- intialize the global
9243: --
9244: l_fonm_cvg_strt_dt := null ;
9257: --
9258: end if;
9259: --
9260: end if;
9261: hr_utility.set_location('l_fonm_cvg_strt_dt :'||l_fonm_cvg_strt_dt,10);
9262: l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
9263: -- Start of Bug 3520054
9264: if p_plip_id is not null then
9265: --
9262: l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
9263: -- Start of Bug 3520054
9264: if p_plip_id is not null then
9265: --
9266: hr_utility.set_location('PLIP_ID '||p_plip_id,11);
9267: --
9268: ben_comp_object.get_object(p_plip_id => p_plip_id
9269: ,p_rec => l_plip_rec);
9270: --
9269: ,p_rec => l_plip_rec);
9270: --
9271: elsif p_ptip_id is not null then
9272: --
9273: hr_utility.set_location('PTIP_ID '||p_ptip_id,12);
9274: --
9275: ben_comp_object.get_object(p_ptip_id => p_ptip_id
9276: ,p_rec => l_ptip_rec);
9277: --
9277: --
9278: -- End of Bug 3520054
9279: elsif p_pl_id is not null then
9280: --
9281: hr_utility.set_location('PL_ID '||p_pl_id,14);
9282: --
9283: ben_comp_object.get_object(p_pl_id => p_pl_id
9284: ,p_rec => l_pl_rec);
9285: --
9284: ,p_rec => l_pl_rec);
9285: --
9286: elsif p_oipl_id is not null then
9287: --
9288: hr_utility.set_location('OIPL_ID '||p_oipl_id,16);
9289: --
9290: ben_comp_object.get_object(p_oipl_id => p_oipl_id
9291: ,p_rec => l_oipl_rec);
9292:
9342: l_eligprof_dets.extend(p_eligprof_tab.count);
9343: for i in 1..p_eligprof_tab.count
9344: loop
9345:
9346: hr_utility.set_location('prfl count'||p_eligprof_tab.count,99);
9347: hr_utility.set_location('prfl id'||p_eligprof_tab(i).eligy_prfl_id,99);
9348: open c_elgy_prfl(p_eligprof_tab(i).eligy_prfl_id,
9349: nvl(l_fonm_cvg_strt_dt,l_effective_date));
9350: fetch c_elgy_prfl into l_eligprof_dets(i);
9343: for i in 1..p_eligprof_tab.count
9344: loop
9345:
9346: hr_utility.set_location('prfl count'||p_eligprof_tab.count,99);
9347: hr_utility.set_location('prfl id'||p_eligprof_tab(i).eligy_prfl_id,99);
9348: open c_elgy_prfl(p_eligprof_tab(i).eligy_prfl_id,
9349: nvl(l_fonm_cvg_strt_dt,l_effective_date));
9350: fetch c_elgy_prfl into l_eligprof_dets(i);
9351: close c_elgy_prfl;
9356: end loop;
9357:
9358: end if;
9359:
9360: hr_utility.set_location(l_proc||' After Cache call ',46);
9361: --
9362: -- Filter out non CAGR profiles in collective agreement mode
9363: --
9364: -- Get the environment details
9391: fnd_message.set_name('BEN','BEN_92844_NOCAGRELP_NOT_ELIG');
9392: return false;
9393: --
9394: end if;
9395: hr_utility.set_location('l_cagrelig_cnt = '|| l_cagrelig_cnt,1687);
9396: --
9397: l_eligprof_dets.delete;
9398: l_eligprof_dets := l_tmpelp_dets;
9399: --
9419: --
9420: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9421: between l_per_rec.effective_start_date and l_per_rec.effective_end_date then
9422:
9423: hr_utility.set_location('clearing cache'||nvl(l_fonm_cvg_strt_dt,l_effective_date) ,10);
9424: hr_utility.set_location('cache start'||l_per_rec.effective_start_date ,10);
9425: hr_utility.set_location('cache end'||l_per_rec.effective_end_date ,10);
9426:
9427: ben_use_cvg_rt_date.fonm_clear_down_cache;
9420: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9421: between l_per_rec.effective_start_date and l_per_rec.effective_end_date then
9422:
9423: hr_utility.set_location('clearing cache'||nvl(l_fonm_cvg_strt_dt,l_effective_date) ,10);
9424: hr_utility.set_location('cache start'||l_per_rec.effective_start_date ,10);
9425: hr_utility.set_location('cache end'||l_per_rec.effective_end_date ,10);
9426:
9427: ben_use_cvg_rt_date.fonm_clear_down_cache;
9428: --
9421: between l_per_rec.effective_start_date and l_per_rec.effective_end_date then
9422:
9423: hr_utility.set_location('clearing cache'||nvl(l_fonm_cvg_strt_dt,l_effective_date) ,10);
9424: hr_utility.set_location('cache start'||l_per_rec.effective_start_date ,10);
9425: hr_utility.set_location('cache end'||l_per_rec.effective_end_date ,10);
9426:
9427: ben_use_cvg_rt_date.fonm_clear_down_cache;
9428: --
9429: ben_person_object.get_object(p_person_id => p_person_id,
9431: --
9432: ben_person_object.get_object(p_person_id => p_person_id,
9433: p_rec => l_per_rec);
9434:
9435: hr_utility.set_location('nw cache start'||l_per_rec.effective_start_date ,10);
9436: hr_utility.set_location('nw cache end'||l_per_rec.effective_end_date ,10);
9437: else
9438:
9439: -- Bug 6399423
9432: ben_person_object.get_object(p_person_id => p_person_id,
9433: p_rec => l_per_rec);
9434:
9435: hr_utility.set_location('nw cache start'||l_per_rec.effective_start_date ,10);
9436: hr_utility.set_location('nw cache end'||l_per_rec.effective_end_date ,10);
9437: else
9438:
9439: -- Bug 6399423
9440: /* ben_person_object.get_object(p_person_id => p_person_id,
9444: ben_person_object.get_object(p_person_id => p_person_id,
9445: p_rec => l_ass_rec1);
9446: -- Bug 6399423
9447:
9448: hr_utility.set_location('cache asg start'||l_ass_rec1.effective_start_date ,10);
9449: hr_utility.set_location('cache asg end'||l_ass_rec1.effective_end_date ,10);
9450:
9451: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9452: between l_ass_rec1.effective_start_date and l_ass_rec1.effective_end_date then
9445: p_rec => l_ass_rec1);
9446: -- Bug 6399423
9447:
9448: hr_utility.set_location('cache asg start'||l_ass_rec1.effective_start_date ,10);
9449: hr_utility.set_location('cache asg end'||l_ass_rec1.effective_end_date ,10);
9450:
9451: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9452: between l_ass_rec1.effective_start_date and l_ass_rec1.effective_end_date then
9453:
9450:
9451: if not nvl(l_fonm_cvg_strt_dt,l_effective_date)
9452: between l_ass_rec1.effective_start_date and l_ass_rec1.effective_end_date then
9453:
9454: hr_utility.set_location('clearing asg cache'||nvl(l_fonm_cvg_strt_dt,l_effective_date) ,10);
9455: ben_use_cvg_rt_date.fonm_clear_down_cache;
9456:
9457: end if ;
9458: end if ;
9469: end if;
9470: end loop;
9471: --
9472: if g_score_compute_mode then
9473: hr_utility.set_location('in score compute mode',10);
9474: end if;
9475: if g_trk_scr_for_inelg_flag then
9476: hr_utility.set_location('track score for inelig flag on',10);
9477: end if;
9472: if g_score_compute_mode then
9473: hr_utility.set_location('in score compute mode',10);
9474: end if;
9475: if g_trk_scr_for_inelg_flag then
9476: hr_utility.set_location('track score for inelig flag on',10);
9477: end if;
9478:
9479: g_per_eligible := null;
9480: --
9483: l_found_profile := 'Y';
9484: --
9485: -- if eligibility profiles do exists for program, plan or option:
9486: --
9487: hr_utility.set_location('ELigibility Profile ID = '||l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,10);
9488: hr_utility.set_location('Number of profiles = '||l_eligprof_dets.count,10);
9489: begin
9490: if g_score_compute_mode then
9491: if g_per_eligible is not null then --per already found elig/inelig
9484: --
9485: -- if eligibility profiles do exists for program, plan or option:
9486: --
9487: hr_utility.set_location('ELigibility Profile ID = '||l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,10);
9488: hr_utility.set_location('Number of profiles = '||l_eligprof_dets.count,10);
9489: begin
9490: if g_score_compute_mode then
9491: if g_per_eligible is not null then --per already found elig/inelig
9492: if nvl(l_eligprof_dets(l_elptorrw_num).compute_score_flag,'N')='N' then
9489: begin
9490: if g_score_compute_mode then
9491: if g_per_eligible is not null then --per already found elig/inelig
9492: if nvl(l_eligprof_dets(l_elptorrw_num).compute_score_flag,'N')='N' then
9493: hr_utility.set_location('skipping prof '||
9494: l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,45);
9495: raise g_skip_profile;
9496: end if;
9497: end if;
9498: end if;
9499: --
9500: l_profile_score_tab.delete;
9501: l_prof_score_compute := (l_eligprof_dets(l_elptorrw_num).compute_score_flag = 'Y');
9502: hr_utility.set_location(l_proc||' St ELP loop ',46);
9503: --
9504: g_inelg_rsn_cd := null;
9505: --
9506: if l_eligprof_dets(l_elptorrw_num).elig_per_typ_flag = 'Y' then
9569: elsif l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd = 'ANY' then
9570: --
9571: -- First assignment only
9572: --
9573: hr_utility.set_location('Getting all assignments',10);
9574: -- 4958846 ssarkar for irec
9575: ben_env_object.get(p_rec => l_env_rec);
9576: benutils.get_batch_parameters(p_benefit_action_id => l_env_rec.benefit_action_id
9577: ,p_rec => l_benmngle_parm_rec);
9575: ben_env_object.get(p_rec => l_env_rec);
9576: benutils.get_batch_parameters(p_benefit_action_id => l_env_rec.benefit_action_id
9577: ,p_rec => l_benmngle_parm_rec);
9578:
9579: hr_utility.set_location('l_benmngle_parm_rec.mode_cd :' || l_benmngle_parm_rec.mode_cd,99);
9580:
9581: if l_benmngle_parm_rec.mode_cd = 'I' then
9582:
9583:
9584: l_appass_rec.delete;
9585:
9586: l_appass_rec(1) := ben_manage_life_events.g_irec_ass_rec ;
9587:
9588: hr_utility.set_location(' p_assignment_id :' || l_appass_rec(1).assignment_id,99);
9589: hr_utility.set_location(' location_id :' || l_appass_rec(1).location_id,99);
9590: hr_utility.set_location(' p_effective_date :' || p_effective_date,99);
9591:
9592:
9585:
9586: l_appass_rec(1) := ben_manage_life_events.g_irec_ass_rec ;
9587:
9588: hr_utility.set_location(' p_assignment_id :' || l_appass_rec(1).assignment_id,99);
9589: hr_utility.set_location(' location_id :' || l_appass_rec(1).location_id,99);
9590: hr_utility.set_location(' p_effective_date :' || p_effective_date,99);
9591:
9592:
9593: else
9586: l_appass_rec(1) := ben_manage_life_events.g_irec_ass_rec ;
9587:
9588: hr_utility.set_location(' p_assignment_id :' || l_appass_rec(1).assignment_id,99);
9589: hr_utility.set_location(' location_id :' || l_appass_rec(1).location_id,99);
9590: hr_utility.set_location(' p_effective_date :' || p_effective_date,99);
9591:
9592:
9593: else
9594: ben_person_object.get_allass_object(p_person_id => p_person_id,
9600: -- Initialize first record so that one time test works
9601: --
9602: l_appass_rec(1).person_id := p_person_id;
9603: --
9604: hr_utility.set_location('NO RECS',10);
9605: --
9606: end if;
9607: --
9608: l_loop_count := l_appass_rec.count;
9607: --
9608: l_loop_count := l_appass_rec.count;
9609: --
9610: l_ass_rec := l_appass_rec(1);
9611: hr_utility.set_location('NUMRECS'||l_appass_rec.count,10);
9612: hr_utility.set_location('GRADE'||l_appass_rec(1).grade_id,10);
9613: elsif l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd = 'ETB' then
9614: --
9615: -- Employee then Benefits assignment only
9608: l_loop_count := l_appass_rec.count;
9609: --
9610: l_ass_rec := l_appass_rec(1);
9611: hr_utility.set_location('NUMRECS'||l_appass_rec.count,10);
9612: hr_utility.set_location('GRADE'||l_appass_rec(1).grade_id,10);
9613: elsif l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd = 'ETB' then
9614: --
9615: -- Employee then Benefits assignment only
9616: --
9695: /*Start Enh: 13478736 - Learner Access Eligibility Enhancement*/
9696: elsif l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd in ('CWAO','EAAO','ECWA') then
9697: if p_assignment_id is null then
9698: fnd_message.set_name('BEN','BEN_93193_ASGN_TYPE_PRFL_FAIL');
9699: hr_utility.set_location('Criteria Failed: Assignment Type',67);
9700: raise g_criteria_failed;
9701: end if;
9702: hr_utility.set_location('asmt_to_use_cd '||l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd,67);
9703: --hr_utility.set_location('p_person_id '||p_person_id,671);
9698: fnd_message.set_name('BEN','BEN_93193_ASGN_TYPE_PRFL_FAIL');
9699: hr_utility.set_location('Criteria Failed: Assignment Type',67);
9700: raise g_criteria_failed;
9701: end if;
9702: hr_utility.set_location('asmt_to_use_cd '||l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd,67);
9703: --hr_utility.set_location('p_person_id '||p_person_id,671);
9704: --hr_utility.set_location('p_assignment_id '||p_assignment_id,671);
9705: ben_person_object.get_assgn_object( p_person_id => p_person_id,
9706: p_assignment_id => p_assignment_id,
9699: hr_utility.set_location('Criteria Failed: Assignment Type',67);
9700: raise g_criteria_failed;
9701: end if;
9702: hr_utility.set_location('asmt_to_use_cd '||l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd,67);
9703: --hr_utility.set_location('p_person_id '||p_person_id,671);
9704: --hr_utility.set_location('p_assignment_id '||p_assignment_id,671);
9705: ben_person_object.get_assgn_object( p_person_id => p_person_id,
9706: p_assignment_id => p_assignment_id,
9707: p_rec => l_ass_rec);
9700: raise g_criteria_failed;
9701: end if;
9702: hr_utility.set_location('asmt_to_use_cd '||l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd,67);
9703: --hr_utility.set_location('p_person_id '||p_person_id,671);
9704: --hr_utility.set_location('p_assignment_id '||p_assignment_id,671);
9705: ben_person_object.get_assgn_object( p_person_id => p_person_id,
9706: p_assignment_id => p_assignment_id,
9707: p_rec => l_ass_rec);
9708: hr_utility.set_location('p_person_id '||l_ass_rec.person_id,672);
9704: --hr_utility.set_location('p_assignment_id '||p_assignment_id,671);
9705: ben_person_object.get_assgn_object( p_person_id => p_person_id,
9706: p_assignment_id => p_assignment_id,
9707: p_rec => l_ass_rec);
9708: hr_utility.set_location('p_person_id '||l_ass_rec.person_id,672);
9709: hr_utility.set_location('p_assignment_id '||l_ass_rec.assignment_id,672);
9710: /*End Enh: 13478736*/
9711: end if;
9712: --
9705: ben_person_object.get_assgn_object( p_person_id => p_person_id,
9706: p_assignment_id => p_assignment_id,
9707: p_rec => l_ass_rec);
9708: hr_utility.set_location('p_person_id '||l_ass_rec.person_id,672);
9709: hr_utility.set_location('p_assignment_id '||l_ass_rec.assignment_id,672);
9710: /*End Enh: 13478736*/
9711: end if;
9712: --
9713: -- bug fix 2431776 - 27-AUG-2002 - hnarayan
9726: --
9727: -- At this stage the assignment record would have been retreived
9728: -- as per the assignment type code given in the ELPRO for this person
9729: --
9730: hr_utility.set_location(l_proc || ' Start Check Asst Type' , 10) ;
9731: --
9732: if (l_eligprof_dets(l_elptorrw_num).asmt_to_use_cd <> 'ANY' and
9733: l_ass_rec.assignment_id is null) then
9734: --
9733: l_ass_rec.assignment_id is null) then
9734: --
9735: g_inelg_rsn_cd := 'AST';
9736: fnd_message.set_name('BEN','BEN_93193_ASGN_TYPE_PRFL_FAIL');
9737: hr_utility.set_location('Criteria Failed: Assignment Type',20);
9738: raise g_criteria_failed;
9739: --
9740: end if;
9741: --
9738: raise g_criteria_failed;
9739: --
9740: end if;
9741: --
9742: hr_utility.set_location(l_proc || ' End Check Asst Type' , 10) ;
9743: --
9744: -- end fix 2431776 - 27-AUG-2002 - hnarayan
9745: --
9746: -- if assignment_id is passed in, make sure eligibility profile's
9770: if not l_asg_found then
9771: --
9772: g_inelg_rsn_cd := 'AST';
9773: fnd_message.set_name('BEN','BEN_93193_ASGN_TYPE_PRFL_FAIL');
9774: hr_utility.set_location('Criteria Failed: Assignment Type',20);
9775: raise g_criteria_failed;
9776: --
9777: end if;
9778: end if;
9777: end if;
9778: end if;
9779: end if; --p_person_id not null
9780:
9781: hr_utility.set_location(l_proc||' Asg ELPs ',46);
9782: for l_count in 1..l_loop_count loop
9783: --
9784: begin
9785: --
9793: end if;
9794: --
9795: assign_overriden_values(l_per_rec,l_ass_rec);
9796: --
9797: hr_utility.set_location(' here ',999);
9798: if l_eligprof_dets(l_elptorrw_num).elig_ee_stat_flag = 'Y' then
9799: check_ee_stat_elig
9800: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
9801: eligy_prfl_id,
10003: p_abs_attd_reason_id => l_absence_attendance_reason_id,
10004: p_effective_date => nvl(l_fonm_cvg_strt_dt,l_effective_date));
10005: end if;
10006: --
10007: hr_utility.set_location(' here 2 ',999);
10008: if l_ass_rec.soft_coding_keyflex_id is not null then
10009: --
10010: ben_person_object.get_object
10011: (p_soft_coding_keyflex_id => l_ass_rec.soft_coding_keyflex_id,
10057:
10058: end if ;
10059:
10060: if l_eligprof_dets(l_elptorrw_num).eligy_prfl_rl_flag = 'Y' then
10061: hr_utility.set_location(l_proc||' Chk Rule',48);
10062:
10063: -- Bug# 2424041 pl_typ_id context is not being passed to the formula
10064: if p_pgm_id is not null then
10065: -- pl_typ_id is not available at pgm level
10110: --
10111: exception
10112: --
10113: when g_skip_profile then
10114: hr_utility.set_location('skip 1',20);
10115: raise g_skip_profile;
10116: when g_criteria_failed then
10117: --
10118: -- Handle case where we want an error if we are dealing with
10141: end if ;
10142: --
10143: end if;
10144: --
10145: hr_utility.set_location(l_proc||' Crit Failed ',48);
10146: --
10147: -- Bug 8685338 -- Person should error out instead
10148: -- of failing the criteria.
10149: --
10150: /* when others then
10151: --
10152: -- Catch any unhandled exceptions
10153: --
10154: hr_utility.set_location(l_proc||' Crit Failed ',49);
10155: return false; */
10156: --
10157: end;
10158: --
10160: --
10161: -- Check these criteria only if the person is an employee who is
10162: -- not terminated:
10163: --
10164: hr_utility.set_location(' here 3 ',999);
10165: if p_person_id is not null and
10166: l_typ_rec(1).system_person_type = 'EMP' and
10167: nvl(p_asg_status,hr_api.g_varchar2) <> 'TERM_ASSIGN' then
10168:
10165: if p_person_id is not null and
10166: l_typ_rec(1).system_person_type = 'EMP' and
10167: nvl(p_asg_status,hr_api.g_varchar2) <> 'TERM_ASSIGN' then
10168:
10169: hr_utility.set_location(l_proc||' Not TERM ASSIGN',48);
10170:
10171: if l_eligprof_dets(l_elptorrw_num).elig_pct_fl_tm_flag = 'Y' then
10172: --
10173: -- for Vapros, get the values from elig_per and elig_per_opt ??
10211: end if;
10212: end if;
10213: --
10214: --
10215: hr_utility.set_location(' here 4 ',999);
10216: if l_eligprof_dets(l_elptorrw_num).elig_los_flag = 'Y' then
10217: check_los_elig
10218: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10219: eligy_prfl_id,
10259: p_plip_id => p_plip_id,
10260: p_opt_id => p_opt_id);
10261: end if;
10262: --
10263: hr_utility.set_location(l_proc||' check_comp_level_elig',50);
10264: if l_eligprof_dets(l_elptorrw_num).elig_comp_lvl_flag = 'Y' then
10265: check_comp_level_elig
10266: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10267: eligy_prfl_id,
10319: p_plip_id => p_plip_id,
10320: p_opt_id => p_opt_id);
10321: end if;
10322: --
10323: hr_utility.set_location(l_proc||' check_person_balance',52);
10324: if l_eligprof_dets(l_elptorrw_num).elig_comp_lvl_flag = 'Y' then
10325: check_person_balance
10326: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10327: eligy_prfl_id,
10342: p_opt_id => p_opt_id);
10343: end if;
10344: --
10345: if l_eligprof_dets(l_elptorrw_num).elig_hrs_wkd_flag = 'Y' then
10346: hr_utility.set_location(l_proc||' check_hrs_wkd_ben_bal',54);
10347:
10348: if p_comp_obj_mode and
10349: p_eval_typ <> 'E' then
10350: --
10407: p_per_hrs_wkd => l_hrs_wkd_val);
10408: --
10409: end if;
10410: --
10411: hr_utility.set_location(l_proc||' check_age_elig',56);
10412: if l_eligprof_dets(l_elptorrw_num).elig_age_flag = 'Y' then
10413: check_age_elig
10414: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10415: eligy_prfl_id,
10431: p_plip_id => p_plip_id,
10432: p_opt_id => p_opt_id);
10433: end if;
10434: --
10435: hr_utility.set_location(l_proc||' Zip Code',48);
10436: if l_eligprof_dets(l_elptorrw_num).elig_pstl_cd_flag = 'Y' then
10437: check_zip_code_rng_elig
10438: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10439: eligy_prfl_id,
10443: p_profile_score_tab => l_profile_score_tab,
10444: p_postal_code => l_postal_code,
10445: p_effective_date => nvl(l_fonm_cvg_strt_dt,l_effective_date));
10446: end if;
10447: hr_utility.set_location(l_proc||' check_service_a',60);
10448: --
10449: if l_eligprof_dets(l_elptorrw_num).elig_svc_area_flag = 'Y' then
10450: check_service_area_elig
10451: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10457: p_postal_code => l_postal_code,
10458: p_effective_date => nvl(l_fonm_cvg_strt_dt,l_effective_date));
10459: end if;
10460: --
10461: hr_utility.set_location(l_proc||' check_benefits_grp_elig',62);
10462: if l_eligprof_dets(l_elptorrw_num).elig_benfts_grp_flag = 'Y' then
10463: check_benefits_grp_elig
10464: (p_eligy_prfl_id => l_eligprof_dets(l_elptorrw_num).
10465: eligy_prfl_id,
10628:
10629: /* Bug 13809302: Evaluate the Eligibility Profiles attached to the Communication and Life Event.
10630: If the p_source is 'C' or 'L', evaluate the eligibility profiles attached under Related coverages tab.*/
10631:
10632: hr_utility.set_location(l_proc||' cobra',64);
10633: if p_par_pgm_id is not null or
10634: p_eval_typ <> 'E' or p_source in ('C','L') then
10635: --
10636: -- We are dealing with a program hierarchy
10784: p_effective_date => p_effective_date);
10785: end if;
10786: --
10787: end if;
10788: hr_utility.set_location(l_proc||' Done cobra',66);
10789: hr_utility.set_location(l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,99);
10790: --
10791: ben_cagr_check_eligibility.check_cagr_elig_profiles
10792: (p_eligprof_dets => l_eligprof_dets(l_elptorrw_num)
10785: end if;
10786: --
10787: end if;
10788: hr_utility.set_location(l_proc||' Done cobra',66);
10789: hr_utility.set_location(l_eligprof_dets(l_elptorrw_num).eligy_prfl_id,99);
10790: --
10791: ben_cagr_check_eligibility.check_cagr_elig_profiles
10792: (p_eligprof_dets => l_eligprof_dets(l_elptorrw_num)
10793: ,p_effective_date => nvl(l_fonm_cvg_strt_dt,p_effective_date)
10836: end if;
10837: exception
10838: --
10839: when g_skip_profile then
10840: hr_utility.set_location('skip 2',20);
10841: null;
10842: when g_criteria_failed then
10843: --
10844: -- when one of the criteria of the profile fail, they raise
10870: end if;
10871: --
10872: end;
10873: --
10874: hr_utility.set_location(l_proc||' End ELP loop ',68);
10875: end loop; -- elig_prfls
10876: --
10877: end if; -- inst_count
10878: --
10876: --
10877: end if; -- inst_count
10878: --
10879: --
10880: hr_utility.set_location(l_proc||' After profile loop',70);
10881: --
10882: -- If we are here, either:
10883: -- there were no profiles or
10884: -- all mandatory profiles passed
10927: end if;
10928: return false;
10929: end if;
10930: --
10931: hr_utility.set_location('Leaving: '||l_proc,99);
10932: --
10933: return true;
10934: --
10935: end eligible;