[Home] [Help]
184: 28-JUL-00 rchase 115.48 - Bug#5364 continuation
185: Update the c_epo_max_esd_of_past_pil
186: cursor in the delete_routine
187: to fetch the appropriate
188: dates for deleting ben_elig_per_opt_f
189: rows
190: 03-AUG-00 pbodla 115.49 - Removed the effective date usage
191: from the cursors -
192: c_ben_ELIG_PER_OPT_f,
188: dates for deleting ben_elig_per_opt_f
189: rows
190: 03-AUG-00 pbodla 115.49 - Removed the effective date usage
191: from the cursors -
192: c_ben_ELIG_PER_OPT_f,
193: c_epo_max_esd_of_past_pil,
194: c_ben_elig_per_f,
195: c_ben_prtt_prem_f,
196: c_ben_pl_bnf_f,
203: not EOT.
204: Affect p_routines:
205: BEN_ELIG_CVRD_DPNT_F
206: BEN_ELIG_PER_F
207: BEN_ELIG_PER_OPT_F
208: BEN_PRTT_PREM_F
209: BEN_PL_BNF_F
210: BEN_PRTT_ENRT_RSLT_F
211: 14-Sep-2000 jcarpent 115.51 Bug 1269016. added bolfe
1002: cursor c_restore_epo( p_pil_id number ) IS
1003: select *
1004: from ben_le_clsn_n_rstr elig
1005: where elig.per_in_ler_ended_id = p_pil_id
1006: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT' ;
1007: --
1008: cursor c_epo(p_elig_per_opt_id number,p_effective_date date) IS
1009: select object_version_number
1010: from ben_elig_per_opt_f epo
1006: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT' ;
1007: --
1008: cursor c_epo(p_elig_per_opt_id number,p_effective_date date) IS
1009: select object_version_number
1010: from ben_elig_per_opt_f epo
1011: where epo.elig_per_opt_id = p_elig_per_opt_id
1012: and p_effective_date between epo.effective_start_date
1013: and epo.effective_end_date ;
1014: --
1033: --
1034: --Get the Datetrack Mode
1035: dt_api.find_dt_upd_modes
1036: (p_effective_date => l_effective_date,
1037: p_base_table_name => 'BEN_ELIG_PER_OPT_F',
1038: p_base_key_column => 'elig_per_opt_id',
1039: p_base_key_value => l_rec.BKUP_TBL_ID,
1040: p_correction => l_correction,
1041: p_update => l_update,
1125: --Now Delete the records from ben_le_clsn_n_rstr
1126: --
1127: delete from ben_le_clsn_n_rstr elig
1128: where elig.per_in_ler_ended_id = p_per_in_ler_id
1129: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT';
1130: --
1131: hr_utility.set_location ('Leaving '||l_proc,10);
1132: --
1133: END restore_prev_epo ;
1294: -- BEN_ENRT_RT (ECR) (cascade delete in epe api, but epe not deleted)
1295: -- BEN_ENRT_CVG_N_RT_CTFN (ECC) (cascade delete in epe api, but epe not deleted)
1296: -- BEN_ELIG_CVRD_DPNT_F (PDP)
1297: -- BEN_ELIG_PER_F (PEP)
1298: -- BEN_ELIG_PER_OPT_F (EPO)
1299: -- BEN_CVRD_DPNT_CTFN_PRVDD_F (CCP)
1300: -- BEN_PER_CM_F (PCM) (join to pil to check status)
1301: -- BEN_PER_CM_USG_F (PCU) (join to pil to check status)
1302: -- BEN_PER_CM_TRGR_F (PCR) (join to pil to check status)
1471:
1472: --CWBGLOBAL : No need to backout in case of CWB backout
1473: if l_ler_typ not in ('COMP') then
1474: -- mark elig_per opts's 'backed out'
1475: delete_routine(p_routine => 'BEN_ELIG_PER_OPT_F',
1476: p_per_in_ler_id => p_per_in_ler_id,
1477: p_business_group_id => p_business_group_id,
1478: p_copy_only => p_copy_only,
1479: p_effective_date => p_effective_date);
1571: hr_utility.set_location ('prv_per_in_ler_id' || l_prv_per_in_ler_id, 199 );
1572: hr_utility.set_location ('l_lf_evt_ocrd_dt' || l_prv_lf_evt_ocrd_dt, 199 );
1573: --
1574: --IK START
1575: -- Get all the BEN_ELIG_PER_F_CORRECTION and BEN_ELIG_PER_OPT_F_CORRECTION
1576: -- records from BEN_LE_CLSN_N_RSTR table for the per_in_ler being backed out
1577: -- and update the records with PEP and EPO records with the restored date
1578: -- in correction mode.
1579: -- After API call to Corrrect the PEP and EPO records delete the data from
2419: --
2420: l_package varchar2(80) := g_package||'.delete_routine';
2421: --
2422: --
2423: -- START ----> DELETE ROUTINE FOR BEN_ELIG_PER_OPT_F
2424: --
2425: -- Source Table
2426: -- BEN_PER_IN_LER
2427: --
2424: --
2425: -- Source Table
2426: -- BEN_PER_IN_LER
2427: --
2428: cursor c_ben_ELIG_PER_OPT_f is
2429: select epo.ELIG_PER_OPT_id,
2430: epo.object_version_number
2431: from ben_ELIG_PER_OPT_f epo,
2432: ben_per_in_ler pil
2427: --
2428: cursor c_ben_ELIG_PER_OPT_f is
2429: select epo.ELIG_PER_OPT_id,
2430: epo.object_version_number
2431: from ben_ELIG_PER_OPT_f epo,
2432: ben_per_in_ler pil
2433: where pil.per_in_ler_id = p_per_in_ler_id
2434: and pil.business_group_id = p_business_group_id
2435: and epo.per_in_ler_id = pil.per_in_ler_id
2453: -- RCHASE 5364 - Update cursor to fetch appropriate rows
2454: -- Previous cursor definition
2455: --cursor c_epo_max_esd_of_past_pil(v_ELIG_PER_OPT_id in number) is
2456: -- select max(effective_end_date), max(object_version_number)
2457: -- from ben_ELIG_PER_OPT_f
2458: -- where ELIG_PER_OPT_id = v_ELIG_PER_OPT_id
2459: -- and nvl(per_in_ler_id , -1) <> p_per_in_ler_id
2460: -- and business_group_id = p_business_group_id;
2461: -- and effective_end_date < p_effective_date;
2462: -- New cursor definition
2463: /*
2464: cursor c_epo_max_esd_of_past_pil(v_ELIG_PER_OPT_id in number) is
2465: select max(epo.effective_end_date), max(epo.object_version_number)
2466: from ben_ELIG_PER_OPT_f epo
2467: where epo.ELIG_PER_OPT_id = v_ELIG_PER_OPT_id
2468: --RCHASE 5364
2469: and nvl(per_in_ler_id , -1) <> p_per_in_ler_id
2470: and epo.business_group_id = p_business_group_id;
2481: where per_in_ler_id = p_per_in_ler_id); */
2482: --
2483: cursor c_epo_max_esd_of_past_pil(v_ELIG_PER_OPT_id in number) is
2484: select epo.effective_end_date, epo.object_version_number
2485: from ben_ELIG_PER_OPT_f epo,
2486: ben_per_in_ler pil
2487: where epo.ELIG_PER_OPT_id = v_ELIG_PER_OPT_id
2488: and nvl(epo.per_in_ler_id , -1) <> p_per_in_ler_id
2489: and epo.business_group_id = p_business_group_id
2496: --
2497: cursor c_deleted_epo(v_elig_per_opt_id in number,
2498: v_effective_date in date) is
2499: select *
2500: from ben_elig_per_opt_f
2501: where elig_per_opt_id = v_elig_per_opt_id
2502: and nvl(per_in_ler_id , -1) = p_per_in_ler_id
2503: and business_group_id = p_business_group_id
2504: and effective_end_date > v_effective_date;
2504: and effective_end_date > v_effective_date;
2505:
2506:
2507: --
2508: -- END ----> DELETE ROUTINE FOR BEN_ELIG_PER_OPT_F
2509: --
2510: -- START ----> DELETE ROUTINE FOR BEN_ELIG_PER_F
2511: --
2512: -- Source Table
4178: --
4179:
4180: close c_ben_elig_per_f;
4181: --
4182: elsif p_routine = 'BEN_ELIG_PER_OPT_F'
4183: then
4184: -- we want to 'un-end' any records that were ENDED
4185: -- due to the per-in-ler. The 'future-change' date track mode will do that
4186: -- for us.
4189:
4190: l_prev_pk_id := -1; -- like null
4191:
4192: --
4193: open c_ben_elig_per_opt_f;
4194: --
4195: loop
4196: --
4197: fetch c_ben_elig_per_opt_f into l_pk_id,
4193: open c_ben_elig_per_opt_f;
4194: --
4195: loop
4196: --
4197: fetch c_ben_elig_per_opt_f into l_pk_id,
4198: l_object_version_number;
4199: exit when c_ben_elig_per_opt_f%notfound;
4200: --
4201: -- Get the maximum of effective end date for the dependents
4195: loop
4196: --
4197: fetch c_ben_elig_per_opt_f into l_pk_id,
4198: l_object_version_number;
4199: exit when c_ben_elig_per_opt_f%notfound;
4200: --
4201: -- Get the maximum of effective end date for the dependents
4202: -- with past per_in_ler 's.
4203: --
4323: ONCE_R_CNTUG_CD,
4324: OIPL_ORDR_NUM,
4325: PRTN_END_DT )
4326: values (
4327: 'BEN_ELIG_PER_OPT_F',
4328: l_deleted_epo_rec.INELG_RSN_CD,
4329: l_deleted_epo_rec.PER_IN_LER_ID,
4330: l_deleted_epo_rec.AGE_UOM,
4331: l_deleted_epo_rec.LOS_UOM,
4451: --
4452: l_prev_pk_id := -1;
4453: --
4454:
4455: close c_ben_elig_per_opt_f;
4456: --
4457: elsif p_routine = 'BEN_PRTT_PREM_F' then
4458: -------------Bug 7133998
4459: for l_prtt_prem_corr in c_ben_prtt_prem_f_corr loop
6611: close c_ben_elig_per_f;
6612: --
6613: end if;
6614: --
6615: if c_ben_elig_per_opt_f%isopen then
6616: --
6617: close c_ben_elig_per_opt_f;
6618: --
6619: end if;
6613: end if;
6614: --
6615: if c_ben_elig_per_opt_f%isopen then
6616: --
6617: close c_ben_elig_per_opt_f;
6618: --
6619: end if;
6620: --
6621: if c_ben_prtt_prem_f%isopen then