[Home] [Help]
1130: 13-Sep-11 usaraswa 120.28.12010000.13 Bug 12715758 : In Process_comp_objects while calling ben_comp_obj_filter.filter_comp_objects sending
1131: p_effective_date instead of nvl(l_rec.lf_evt_ocrd_dt,p_effective_date).
1132: 14-Dec-11 usaraswa 120.28.12010000.14 Bug 12732878 : Added procedure check_for_first_eligible to check whether the comp objects present in the global variables
1133: ben_determine_eligibility2.g_cache_first_inelig_object,ben_determine_eligibility2.g_cache_first_elig_object
1134: are having any future records are there in ben_elig_per_f and ben_elig_per_opt_f tables.If the future records are present
1135: then inserting the future records in to back up table and deleting the future records.
1136: called the procedure check_for_first_eligible in process_comp_objects.
1137: 10-Oct-12 velvanop 120.28.12010000.15 Bug 14484394: Set g_max_errors_allowed for the Participation Process: Compensation Workbench Conc Program
1138: 11-Dec-12 velvanop 120.28.12010000.16 Bug 14484281
1162: -------------------------------------------------------------------------------
1163: --Bug 12732878 ud changes start
1164: --Added procedure check_for_first_eligible to check whether the comp objects present in the global variables
1165: --ben_determine_eligibility2.g_cache_first_inelig_object,ben_determine_eligibility2.g_cache_first_elig_object
1166: --are having any future records in ben_elig_per_f and ben_elig_per_opt_f tables.If the future records are
1167: --present then inserting the future records in to back up table and deleting the future records.
1168:
1169: procedure check_for_first_eligible(
1170: p_per_in_ler_id in number
1186:
1187: --
1188:
1189: cursor c_elig_rec(c_elig_per_id number,c_per_in_ler_id number) is
1190: select * from ben_elig_per_f
1191: where elig_per_id = c_elig_per_id
1192: and per_in_ler_id = c_per_in_ler_id;
1193:
1194: --
1198: c_pl_id number,
1199: c_ptip_id number,
1200: c_plip_id number,
1201: c_effective_date date) is
1202: select * from ben_elig_per_f pep
1203: where nvl(pep.pgm_id,-1) = nvl(c_pgm_id,-1)
1204: and nvl(pep.pl_id,-1) = nvl(c_pl_id,-1)
1205: and nvl(pep.plip_id,-1) = nvl(c_plip_id ,-1)
1206: and nvl(pep.ptip_id,-1) = nvl(c_ptip_id,-1)
1215:
1216: --
1217: cursor c_get_end_date_version(c_elig_per_id number,c_effective_date date) is
1218: select epf.effective_end_date,epf.object_version_number
1219: from ben_elig_per_f epf
1220: where epf.elig_per_id = c_elig_per_id
1221: and c_effective_date between epf.effective_start_date
1222: and epf.effective_end_date;
1223: -- and c_effective_date > epf.effective_start_date
1361: ,p_elig_per_opt_id => NULL
1362: ,p_effective_date => l_fut_elig_per_rec.effective_start_date
1363: );
1364:
1365: delete from ben_elig_per_f
1366: where elig_per_id = l_fut_elig_per_rec.elig_per_id;
1367: hr_utility.set_location('elig_per_id record deleted',10);
1368:
1369: end if; --c
1517: ,p_elig_per_opt_id => NULL
1518: ,p_effective_date => l_fut_elig_per_rec.effective_start_date
1519: );
1520:
1521: delete from ben_elig_per_f
1522: where elig_per_id = l_fut_elig_per_rec.elig_per_id;
1523: hr_utility.set_location('elig_per_id record deleted',10);
1524: end if;
1525: end if; --c
2418: l_oiplip_id number := p_comp_obj_tree_row.oiplip_id;
2419: l_plip_id number := p_comp_obj_tree_row.plip_id;
2420: l_ptip_id number := p_comp_obj_tree_row.ptip_id;
2421:
2422: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
2423: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
2424: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
2425: l_effective_start_date date;
2426: l_effective_end_date date;
2419: l_plip_id number := p_comp_obj_tree_row.plip_id;
2420: l_ptip_id number := p_comp_obj_tree_row.ptip_id;
2421:
2422: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
2423: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
2424: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
2425: l_effective_start_date date;
2426: l_effective_end_date date;
2427: l_datetrack_mode varchar2(100);
2448: pep.prtn_strt_dt,
2449: pep.prtn_end_dt,
2450: pep.per_in_ler_id,
2451: pep.object_version_number
2452: from ben_elig_per_f pep,
2453: ben_per_in_ler pil
2454: where pep.person_id = c_person_id
2455: and nvl(pep.pgm_id,-1) = c_pgm_id
2456: and nvl(pep.pl_id,-1) = c_pl_id
2483: pep.prtn_strt_dt strt_dt,
2484: pep.prtn_end_dt end_dt
2485: from ben_elig_per_opt_f epo,
2486: ben_per_in_ler pil,
2487: ben_elig_per_f pep
2488: where pep.person_id = c_person_id
2489: and pep.pl_id = c_pl_id
2490: and epo.opt_id = c_opt_id
2491: and pep.elig_per_id = epo.elig_per_id
2502: or pil.per_in_ler_stat_cd is null);
2503: --
2504: cursor c_epe (p_pgm_id number) is
2505: select null
2506: from ben_elig_per_f
2507: where person_id = p_person_id
2508: and p_effective_date between effective_start_date and
2509: effective_end_date
2510: and per_in_ler_id is null
2511: and pgm_id = p_pgm_id;
2512: --
2513: cursor c_epe2 (p_pl_id number) is
2514: select null
2515: from ben_elig_per_f
2516: where person_id = p_person_id
2517: and p_effective_date between effective_start_date and
2518: effective_end_date
2519: and per_in_ler_id is null
2527: and p_effective_date between epo.effective_start_date
2528: and epo.effective_end_date
2529: and epo.elig_per_id in
2530: (select elig_per_id
2531: from ben_elig_per_f
2532: where person_id = p_person_id
2533: and p_effective_date between effective_start_date and
2534: effective_end_date
2535: and per_in_ler_id = p_per_in_ler_id
2542: and p_effective_date between epo.effective_start_date
2543: and epo.effective_end_date
2544: and epo.elig_per_id in
2545: (select elig_per_id
2546: from ben_elig_per_f
2547: where person_id = p_person_id
2548: and p_effective_date between effective_start_date and
2549: effective_end_date
2550: and per_in_ler_id = p_per_in_ler_id
2574: open c_epe (l_envpgm_id);
2575: fetch c_epe into l_dummy;
2576: if c_epe%found then
2577: --
2578: update ben_elig_per_f pep set per_in_ler_id = p_per_in_ler_id
2579: where pep.person_id = p_person_id
2580: and pep.per_in_ler_id is null
2581: and pep.pgm_id = l_envpgm_id
2582: and p_effective_date between pep.effective_start_date
2595: update ben_elig_per_opt_f epo
2596: set per_in_ler_id = p_per_in_ler_id
2597: where epo.per_in_ler_id is null
2598: and epo.elig_per_id in
2599: (select elig_per_id from ben_elig_per_f pep
2600: where pep.pgm_id = l_envpgm_id
2601: and pep.per_in_ler_id = p_per_in_ler_id
2602: and p_effective_date between pep.effective_start_date
2603: and pep.effective_end_date)
2614: open c_epe2(l_envpl_id);
2615: fetch c_epe2 into l_dummy;
2616: if c_epe2%found then
2617: --
2618: update ben_elig_per_f pep set per_in_ler_id = p_per_in_ler_id
2619: where pep.person_id = p_person_id
2620: and pep.per_in_ler_id is null
2621: and pep.pgm_id is null
2622: and pep.pl_id = l_envpl_id
2637: update ben_elig_per_opt_f epo
2638: set per_in_ler_id = p_per_in_ler_id
2639: where epo.per_in_ler_id is null
2640: and epo.elig_per_id in
2641: (select elig_per_id from ben_elig_per_f pep
2642: where pep.pgm_id is null
2643: and pep.pl_id = l_envpl_id
2644: and pep.per_in_ler_id = p_per_in_ler_id
2645: and p_effective_date between pep.effective_start_date
10251: cursor c_get_ptip_elig(p_ptip_id number
10252: ,p_per_in_ler_id number
10253: ) is
10254: select pep.elig_flag
10255: from ben_elig_per_f pep
10256: where pep.per_in_ler_id = p_per_in_ler_id
10257: and pep.ptip_id = p_ptip_id
10258: and pep.business_group_id = p_business_group_id
10259: and p_effective_date between pep.effective_start_date
11673: --
11674: -------------------------------------------------------------------------------
11675: --Bug 12732878 ud changes start
11676: --if program level comp object is ineligible then inserting the comp object to ben_determine_eligibility2.g_cache_first_inelig_object
11677: --for checking any future records are there in ben_elig_per_f and ben_elig_per_opt_f tables.
11678: hr_utility.set_location('In the l_not_eligible if',20);
11679: if l_comp_obj_tree(l_treeele_num).pgm_id is not null then
11680: hr_utility.set_location('l_comp_obj_tree(l_treeele_num).pgm_id'||l_comp_obj_tree(l_treeele_num).pgm_id,10);
11681: ben_determine_eligibility2.g_cache_first_inelig_object.extend;
12123: --------------------------------------------------------------------------
12124: --Bug 12732878 ud changes procedure start
12125: --Calling procedure check_for_first_eligible to check whether the comp objects present in the global variables
12126: --ben_determine_eligibility2.g_cache_first_inelig_object,ben_determine_eligibility2.g_cache_first_elig_object
12127: --are having any future records in ben_elig_per_f and ben_elig_opt_f tables.If the future records are present
12128: -- then inserting the future records in to back up table and deleting the future records.
12129:
12130: check_for_first_eligible(p_per_in_ler_id => l_pil_row.per_in_ler_id
12131: ,p_person_id => p_person_id