[Home] [Help]
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
141: 25 Jan 99 G Perry 115.17 Fixed first time inelig case
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
377: 16-Aug-10 krupani 120.17.12010000.4 Bug 10029253 - 12.1 forward port of 9789915 : In still-ineligible case,
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: --------------------------------------------------------------------------------
388: */
1131: -- but we do not want to track in-eligibility.
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
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;
1170: l_prev_elig boolean := FALSE;
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;
1170: l_prev_elig boolean := FALSE;
1171: l_prtn_eff_strt_dt date;
1176: l_prev_prtn_end_dt date;
1177: l_effective_start_date date;
1178: l_effective_end_date date;
1179: l_datetrack_mode varchar2(100);
1180: l_elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%type;
1181: l_elig_per_oiplip_id ben_elig_per_opt_f.elig_per_opt_id%type;
1182: l_effective_start_date_opt date;
1183: l_effective_end_date_opt date;
1184: l_opt_elig_flag ben_elig_per_opt_f.elig_flag%type;
1177: l_effective_start_date date;
1178: l_effective_end_date date;
1179: l_datetrack_mode varchar2(100);
1180: l_elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%type;
1181: l_elig_per_oiplip_id ben_elig_per_opt_f.elig_per_opt_id%type;
1182: l_effective_start_date_opt date;
1183: l_effective_end_date_opt date;
1184: l_opt_elig_flag ben_elig_per_opt_f.elig_flag%type;
1185: l_opt_id ben_opt_f.opt_id%type;
1180: l_elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%type;
1181: l_elig_per_oiplip_id ben_elig_per_opt_f.elig_per_opt_id%type;
1182: l_effective_start_date_opt date;
1183: l_effective_end_date_opt date;
1184: l_opt_elig_flag ben_elig_per_opt_f.elig_flag%type;
1185: l_opt_id ben_opt_f.opt_id%type;
1186: l_pl_id ben_pl_f.pl_id%type;
1187: l_start_or_end varchar2(1);
1188: l_end_dt_plus_one date;
1266: pep.elig_per_id,
1267: epo.per_in_ler_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
1302: select epo.elig_per_opt_id,
1303: 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
1379: ,c_add_one in number
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
1511: --Bug 12732878 changes start
1512:
1513: cursor c_elig_opt_rec(c_elig_per_opt_id number,c_per_in_ler_id number)
1514: is
1515: select * from ben_elig_per_opt_f
1516: where elig_per_opt_id = c_elig_per_opt_id
1517: and per_in_ler_id = c_per_in_ler_id;
1518:
1519: l_elig_per_opt_rec c_elig_opt_rec%rowtype;
1522: cursor c_elig_per_opt_id(c_per_in_ler_id number,
1523: c_elig_per_opt_id number,
1524: c_effective_date date)
1525: is
1526: select * from ben_elig_per_opt_f epo
1527: where epo.elig_per_opt_id = c_elig_per_opt_id
1528: and epo.per_in_ler_id = c_per_in_ler_id
1529: and c_effective_date < epo.effective_start_date;
1530:
1975: -- record should be inserted in the backup table
1976:
1977: dt_api.find_dt_upd_modes
1978: (p_effective_date => l_effective_dt,
1979: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
1980: p_base_key_column => 'elig_per_opt_id',
1981: p_base_key_value => l_elig_per_opt_id,
1982: p_correction => l_correction,
1983: p_update => l_update,
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
2553: -- Bugs : 1412882, part of bug 1412951
2554: --
2555: p_effective_date => l_effective_dt,
2556: --
2557: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
2558: p_base_key_column => 'elig_per_opt_id',
2559: p_base_key_value => l_elig_per_opt_id,
2560: p_correction => l_correction,
2561: p_update => l_update,
2625: END IF;
2626: -------------------------------------------------------------------------------
2627: --Bug 12732878 changes
2628: --If the oipl comp object is still eligible then checking whether for the same comp object
2629: --Future records are present in ben_elig_per_opt_f tables.If present inserting them into
2630: --backup table.
2631:
2632: IF (l_datetrack_mode = hr_api.g_update_override) -- AND
2633: -- l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
2914: -- Bugs : 1412882, part of bug 1412951
2915: --
2916: p_effective_date => l_effective_dt,
2917: --
2918: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
2919: p_base_key_column => 'elig_per_opt_id',
2920: p_base_key_value => l_elig_per_opt_id,
2921: p_correction => l_correction,
2922: p_update => l_update,
2987: --
2988: -------------------------------------------------------------------------------
2989: --Bug 12732878 changes
2990: --If the oipl comp object is Newly eligible then checking whether for the same comp object
2991: --Future records are present in ben_elig_per_opt_f tables.If present inserting them into
2992: --backup table.
2993:
2994: IF (l_datetrack_mode = hr_api.g_update_override) -- AND
2995: -- l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
3252: -- Bugs : 1412882, part of bug 1412951
3253: --
3254: p_effective_date => l_effective_dt,
3255: --
3256: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
3257: p_base_key_column => 'elig_per_opt_id',
3258: p_base_key_value => l_elig_per_opt_id,
3259: p_correction => l_correction,
3260: p_update => l_update,
3291: --
3292: -------------------------------------------------------------------------------
3293: --Bug 12732878 changes
3294: --If the oipl comp object is newly ineligible then checking whether for the same comp object
3295: --Future records are present in ben_elig_per_opt_f tables.If present inserting them into
3296: --backup table.
3297:
3298: IF (l_datetrack_mode = hr_api.g_update_override) -- AND
3299: -- l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
3984: -- Bugs : 1412882, part of bug 1412951
3985: --
3986: p_effective_date => l_effective_dt,
3987: --
3988: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
3989: p_base_key_column => 'elig_per_opt_id',
3990: p_base_key_value => l_prev_oiplip_elig_check.elig_per_opt_id,
3991: p_correction => l_correction,
3992: p_update => l_update,
4024: --
4025: ---------------------------------------------------------------------------------
4026: --Bug 12732878 changes
4027: --If the oiplip comp object is Newly eligible/Still eligible then checking whether the same comp object
4028: --Future records are present in ben_elig_per_opt_f tables.If present inserting them into
4029: --backup table.
4030:
4031: IF (l_datetrack_mode = hr_api.g_update_override) -- AND
4032: -- l_per_in_ler_id <> NVL(l_prev_per_in_ler_id,-1) THEN
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