[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: --------------------------------------------------------------------------------
375: */
376: --
377: -- -----------------------------------------------------------------------------
1118: -- but we do not want to track in-eligibility.
1119: --
1120: /*PLEASE NOTE THE FOLLOWING:
1121:
1122: 1. Option eligibility is written to ben_elig_per_opt_f
1123: 2. Plan and Program eligibility is written to ben_elig_per_f
1124: 3. When a person is found eligible or ineligible for a pgm, pl or oipl,
1125: a check for previous eligibility/ineligibility will determine
1126: whether a new row will be inserted or an existing row will be
1149: l_prev_per_in_ler_id ben_elig_per_f.per_in_ler_id%type;
1150: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
1151: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1152: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1153: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
1154: l_t_object_version_number ben_elig_per_opt_f.object_version_number%type;
1155: l_p_object_version_number ben_elig_per_f.object_version_number%type;
1156: l_t_effective_dt date;
1157: l_prev_elig boolean := FALSE;
1150: l_elig_per_id ben_elig_per_f.elig_per_id%TYPE;
1151: l_elig_per_elig_flag ben_elig_per_f.elig_flag%TYPE;
1152: l_object_version_number ben_elig_per_f.object_version_number%TYPE;
1153: l_object_version_number_opt ben_elig_per_opt_f.object_version_number%type;
1154: l_t_object_version_number ben_elig_per_opt_f.object_version_number%type;
1155: l_p_object_version_number ben_elig_per_f.object_version_number%type;
1156: l_t_effective_dt date;
1157: l_prev_elig boolean := FALSE;
1158: l_prtn_eff_strt_dt date;
1163: l_prev_prtn_end_dt date;
1164: l_effective_start_date date;
1165: l_effective_end_date date;
1166: l_datetrack_mode varchar2(100);
1167: l_elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%type;
1168: l_elig_per_oiplip_id ben_elig_per_opt_f.elig_per_opt_id%type;
1169: l_effective_start_date_opt date;
1170: l_effective_end_date_opt date;
1171: l_opt_elig_flag ben_elig_per_opt_f.elig_flag%type;
1164: l_effective_start_date date;
1165: l_effective_end_date date;
1166: l_datetrack_mode varchar2(100);
1167: l_elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%type;
1168: l_elig_per_oiplip_id ben_elig_per_opt_f.elig_per_opt_id%type;
1169: l_effective_start_date_opt date;
1170: l_effective_end_date_opt date;
1171: l_opt_elig_flag ben_elig_per_opt_f.elig_flag%type;
1172: l_opt_id ben_opt_f.opt_id%type;
1167: l_elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%type;
1168: l_elig_per_oiplip_id ben_elig_per_opt_f.elig_per_opt_id%type;
1169: l_effective_start_date_opt date;
1170: l_effective_end_date_opt date;
1171: l_opt_elig_flag ben_elig_per_opt_f.elig_flag%type;
1172: l_opt_id ben_opt_f.opt_id%type;
1173: l_pl_id ben_pl_f.pl_id%type;
1174: l_start_or_end varchar2(1);
1175: l_end_dt_plus_one date;
1253: pep.elig_per_id,
1254: epo.per_in_ler_id,
1255: pep.prtn_strt_dt,
1256: pep.prtn_end_dt
1257: from ben_elig_per_opt_f epo,
1258: ben_per_in_ler pil,
1259: ben_elig_per_f pep
1260: where pep.person_id = c_person_id
1261: and pep.pl_id = c_pl_id
1289: select epo.elig_per_opt_id,
1290: epo.elig_per_id,
1291: epo.effective_start_date,
1292: epo.object_version_number
1293: from ben_elig_per_opt_f epo,
1294: ben_per_in_ler pil,
1295: ben_elig_per_f pep
1296: where pep.person_id = c_person_id
1297: and pep.pgm_id = c_pgm_id
1366: ,c_add_one in number
1367: )
1368: is
1369: select epo.prtn_strt_dt
1370: from ben_elig_per_opt_f epo,
1371: ben_elig_per_f pep,
1372: ben_per_in_ler pil
1373: where pep.person_id = p_person_id
1374: and pep.pl_id = l_envpl_id
1881: -- record should be inserted in the backup table
1882:
1883: dt_api.find_dt_upd_modes
1884: (p_effective_date => l_effective_dt,
1885: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
1886: p_base_key_column => 'elig_per_opt_id',
1887: p_base_key_value => l_elig_per_opt_id,
1888: p_correction => l_correction,
1889: p_update => l_update,
2443: -- Bugs : 1412882, part of bug 1412951
2444: --
2445: p_effective_date => l_effective_dt,
2446: --
2447: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
2448: p_base_key_column => 'elig_per_opt_id',
2449: p_base_key_value => l_elig_per_opt_id,
2450: p_correction => l_correction,
2451: p_update => l_update,
2748: -- Bugs : 1412882, part of bug 1412951
2749: --
2750: p_effective_date => l_effective_dt,
2751: --
2752: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
2753: p_base_key_column => 'elig_per_opt_id',
2754: p_base_key_value => l_elig_per_opt_id,
2755: p_correction => l_correction,
2756: p_update => l_update,
3031: -- Bugs : 1412882, part of bug 1412951
3032: --
3033: p_effective_date => l_effective_dt,
3034: --
3035: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
3036: p_base_key_column => 'elig_per_opt_id',
3037: p_base_key_value => l_elig_per_opt_id,
3038: p_correction => l_correction,
3039: p_update => l_update,
3708: -- Bugs : 1412882, part of bug 1412951
3709: --
3710: p_effective_date => l_effective_dt,
3711: --
3712: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
3713: p_base_key_column => 'elig_per_opt_id',
3714: p_base_key_value => l_prev_oiplip_elig_check.elig_per_opt_id,
3715: p_correction => l_correction,
3716: p_update => l_update,