[Home] [Help]
128: 23 Jul 98 thayden 110.21 Added Inelg rsn cd.
129: 23 Oct 98 gperry 115.11 Added in extra call to
130: ben_newly_ineligible to handle
131: plans as well as programs.
132: 26 Oct 98 gperry 115.12 Corrected ben_elig_per_f so
133: pgm_id is populated if pl_id has
134: a program parent.
135: 23 Nov 98 gperry 115.13 Supports new columns for
136: ben_elig_per_f and
132: 26 Oct 98 gperry 115.12 Corrected ben_elig_per_f so
133: pgm_id is populated if pl_id has
134: a program parent.
135: 23 Nov 98 gperry 115.13 Supports new columns for
136: ben_elig_per_f and
137: ben_elig_per_opt_f
138: 23 Nov 98 gperry 115.14 Added inelg_rsn_cd.
139: 02 Jan 99 gperry 115.15 Made trk inelig per flag work.
140: 18 Jan 99 G Perry 115.16 LED V ED
368: 16-Feb-07 rtagarra 115.126 ICM Changes.
369: 12-jun-07 rtagarra 115.127 ICM: Bug 6038232. Also incorporated changes of Bug 6000303 : Defer Deenrollment ENH
370: on 04-Dec-07 from branchline.
371: 23-Jan-2008 sallumwa 115.128 Bug 6601884 : For still ineligible records, when ineligibility is to be tracked,
372: the records should be updated with correct datetrack mode for both ben_elig_per_f
373: and ben_elig_per_opt_f.
374: 12-Jan-2009 krupani 120.17.12010000.1 Forward ported fix of 11i bug 8542643
375: 31-Oct-09 velvanop 120.17.12010000.2 Bug 9081414: Fwd port of Bug 9020962 : If future eligibility record exists for the previous per_in_ler_id, then insert the future eligiblity record
376: in backup table ben_le_clsn_n_rstr
379: 30-Aug-10 velvanop 120.17.12010000.5 Bug 10030441; If a Life Event is processed after the waiting period completion date
380: for a plan, then set the l_wait_perd_cmpltn_dt to null. Since participant has satisfied the
381: waiting period, there is no need to determine the participation start date code after the l_wait_perd_cmpltn_dt
382: 19-Sep-11 usaraswa 120.17.12010000.6 Bug 12964414: If the unrestricted Le is pocessed in a future date and if we run a concurrent request of mode = 'S'
383: on present date then prtn_strt_dt of the eligibility record created in ben_elig_per_f and
384: ben_elig_per_opt_f should be the date on which concurrent request is run.
385: 14-dec-11 usaraswa 120.17.12010000.7 Bug 12732878: If future eligibility record exists for the previous per_in_ler_id for oipl and oiplip comp object, then insert
386: the future eligiblity record in to backup table ben_le_clsn_n_rstr
387: --------------------------------------------------------------------------------
1132: --
1133: /*PLEASE NOTE THE FOLLOWING:
1134:
1135: 1. Option eligibility is written to ben_elig_per_opt_f
1136: 2. Plan and Program eligibility is written to ben_elig_per_f
1137: 3. When a person is found eligible or ineligible for a pgm, pl or oipl,
1138: a check for previous eligibility/ineligibility will determine
1139: whether a new row will be inserted or an existing row will be
1140: updated.
1156: l_oiplipepo_row ben_derive_part_and_rate_facts.g_cache_structure;
1157: l_oiplippep_row ben_derive_part_and_rate_facts.g_cache_structure;
1158: l_pep_row ben_derive_part_and_rate_facts.g_cache_structure;
1159: --
1160: l_new_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1161: l_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1162: l_prev_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1163: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
1164: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1157: l_oiplippep_row ben_derive_part_and_rate_facts.g_cache_structure;
1158: l_pep_row ben_derive_part_and_rate_facts.g_cache_structure;
1159: --
1160: l_new_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1161: l_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1162: l_prev_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1163: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
1164: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1165: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1158: l_pep_row ben_derive_part_and_rate_facts.g_cache_structure;
1159: --
1160: l_new_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1161: l_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1162: l_prev_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1163: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
1164: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1165: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1166: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
1159: --
1160: l_new_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1161: l_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1162: l_prev_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1163: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
1164: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1165: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1166: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
1167: l_t_object_version_number ben_elig_per_opt_f.object_version_number%type;
1160: l_new_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1161: l_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1162: l_prev_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1163: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
1164: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1165: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1166: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
1167: l_t_object_version_number ben_elig_per_opt_f.object_version_number%type;
1168: l_p_object_version_number ben_elig_per_f.object_version_number%type;
1161: l_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1162: l_prev_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1163: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
1164: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1165: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1166: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
1167: l_t_object_version_number ben_elig_per_opt_f.object_version_number%type;
1168: l_p_object_version_number ben_elig_per_f.object_version_number%type;
1169: l_t_effective_dt date;
1164: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1165: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1166: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
1167: l_t_object_version_number ben_elig_per_opt_f.object_version_number%type;
1168: l_p_object_version_number ben_elig_per_f.object_version_number%type;
1169: l_t_effective_dt date;
1170: l_prev_elig boolean := FALSE;
1171: l_prtn_eff_strt_dt date;
1172: l_prtn_eff_end_dt date;
1225: pep.prtn_strt_dt,
1226: pep.prtn_end_dt,
1227: pep.per_in_ler_id,
1228: pep.object_version_number
1229: from ben_elig_per_f pep,
1230: ben_per_in_ler pil
1231: where pep.person_id = c_person_id
1232: and nvl(pep.pgm_id,-1) = c_pgm_id
1233: and nvl(pep.pl_id,-1) = c_pl_id
1268: pep.prtn_strt_dt,
1269: pep.prtn_end_dt
1270: from ben_elig_per_opt_f epo,
1271: ben_per_in_ler pil,
1272: ben_elig_per_f pep
1273: where pep.person_id = c_person_id
1274: and pep.pl_id = c_pl_id
1275: and epo.opt_id = c_opt_id
1276: and pep.elig_per_id = epo.elig_per_id
1304: epo.effective_start_date,
1305: epo.object_version_number
1306: from ben_elig_per_opt_f epo,
1307: ben_per_in_ler pil,
1308: ben_elig_per_f pep
1309: where pep.person_id = c_person_id
1310: and pep.pgm_id = c_pgm_id
1311: and pep.plip_id = c_plip_id
1312: and epo.opt_id = c_opt_id
1332: ,c_add_one in number
1333: )
1334: is
1335: select pep.prtn_strt_dt
1336: from ben_elig_per_f pep,
1337: ben_per_in_ler pil
1338: where pep.person_id = p_person_id
1339: and nvl(pep.pgm_id,-1) =
1340: nvl(p_pgm_id,nvl(l_envpgm_id,-1))
1380: )
1381: is
1382: select epo.prtn_strt_dt
1383: from ben_elig_per_opt_f epo,
1384: ben_elig_per_f pep,
1385: ben_per_in_ler pil
1386: where pep.person_id = p_person_id
1387: and pep.pl_id = l_envpl_id
1388: and nvl(pep.pgm_id,-1) = nvl(l_envpgm_id,-1)
1480: order by pil.lf_evt_ocrd_dt desc;
1481:
1482: /* Cursor to get the eligibility record */
1483: cursor c_elig_rec(c_elig_per_id number,c_per_in_ler_id number) is
1484: select * from ben_elig_per_f
1485: where elig_per_id = c_elig_per_id
1486: and per_in_ler_id = c_per_in_ler_id;
1487:
1488: l_prev_pil_id number;
1496: c_pgm_id number,
1497: c_ptip_id number,
1498: c_plip_id number,
1499: c_effective_date date) is
1500: select * from ben_elig_per_f pep
1501: where nvl(pep.pgm_id,-1) = nvl(c_pgm_id,-1)
1502: and nvl(pep.pl_id,-1) = nvl(c_pl_id,-1)
1503: and nvl(pep.plip_id,-1) = nvl(c_plip_id ,-1)
1504: and nvl(pep.ptip_id,-1) = nvl(c_ptip_id,-1)
2241: life event.
2242: */
2243: if l_first_elig or l_oipl_rec.opt_id is not null then
2244: --
2245: --Get elig id from ben_elig_per_f for the plan
2246: --
2247: if g_debug then
2248: hr_utility.set_location('open c_get_elig_id ben_determine_eligibility2.check_prev_elig', 10);
2249: end if;
2322:
2323: --Bug 12964414: If the unrestricted Le is pocessed in a future date and
2324: -- if we run a concurrent request of mode = 'S' on present date
2325: -- then prtn_strt_dt of the eligibility record created in
2326: -- ben_elig_per_f and ben_elig_per_opt_f should be the date on
2327: -- which concurrent request is run.
2328:
2329: if ben_manage_life_events.g_modified_mode in ('S') then
2330: if l_prtn_eff_strt_dt > l_elig_per_prtn_strt_dt then
3548:
3549: elsif l_first_inelig then --n
3550: --
3551: hr_utility.set_location ('first inelig',121);
3552: -- Get elig id from ben_elig_per_f for the plan
3553: --
3554: ben_pep_cache1.get_currplnpep_dets
3555: (p_comp_obj_tree_row => p_comp_obj_tree_row
3556: ,p_person_id => p_person_id
4526: end if;
4527:
4528: dt_api.find_dt_upd_modes
4529: (p_effective_date => l_effective_dt,
4530: p_base_table_name => 'BEN_ELIG_PER_F',
4531: p_base_key_column => 'elig_per_id',
4532: p_base_key_value => l_elig_per_id,
4533: p_correction => l_correction,
4534: p_update => l_update,
4542: if l_update_override then
4543: l_datetrack_mode := hr_api.g_update_override;
4544:
4545: if g_debug then
4546: hr_utility.set_location('ben_elig_per_f l_datetrack_mode '|| l_datetrack_mode, 777);
4547: end if;
4548:
4549: elsif l_update then
4550: l_datetrack_mode := hr_api.g_update;
4549: elsif l_update then
4550: l_datetrack_mode := hr_api.g_update;
4551:
4552: if g_debug then
4553: hr_utility.set_location('ben_elig_per_f l_datetrack_mode '|| l_datetrack_mode, 777);
4554: end if;
4555:
4556: else
4557: l_datetrack_mode := hr_api.g_correction;
4556: else
4557: l_datetrack_mode := hr_api.g_correction;
4558:
4559: if g_debug then
4560: hr_utility.set_location('ben_elig_per_f l_datetrack_mode '|| l_datetrack_mode, 777);
4561: end if;
4562:
4563: end if;
4564:
4912:
4913: --Bug 12964414: If the unrestricted Le is pocessed in a future date and
4914: -- if we run a concurrent request of mode = 'S' on present date
4915: -- then prtn_strt_dt of the eligibility record created in
4916: -- ben_elig_per_f and ben_elig_per_opt_f should be the date on
4917: -- which concurrent request is run.
4918: --
4919: if ben_manage_life_events.g_modified_mode in ('S') then
4920: if l_prtn_eff_strt_dt > p_effective_date then
5182: -- Bugs : 1412882, part of bug 1412951
5183: --
5184: p_effective_date => l_effective_dt,
5185: --
5186: p_base_table_name => 'BEN_ELIG_PER_F',
5187: p_base_key_column => 'elig_per_id',
5188: p_base_key_value => l_elig_per_id,
5189: p_correction => l_correction,
5190: p_update => l_update,
5540: -- Bugs : 1412882, part of bug 1412951
5541: --
5542: p_effective_date => l_effective_dt,
5543: --
5544: p_base_table_name => 'BEN_ELIG_PER_F',
5545: p_base_key_column => 'elig_per_id',
5546: p_base_key_value => l_elig_per_id,
5547: p_correction => l_correction,
5548: p_update => l_update,
5885: -- Bugs : 1412882, part of bug 1412951
5886: --
5887: p_effective_date => l_effective_dt,
5888: --
5889: p_base_table_name => 'BEN_ELIG_PER_F',
5890: p_base_key_column => 'elig_per_id',
5891: p_base_key_value => l_elig_per_id,
5892: p_correction => l_correction,
5893: p_update => l_update,