[Home] [Help]
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,
197: c_ben_prmry_care_prvdr_f
198: 31-AUG-00 jcarpent 115.50 OraBug# 4988. Added logic to
202: l_effective_date passed is
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
719: cursor c_restore_pep( p_pil_id number ) IS
720: select *
721: from ben_le_clsn_n_rstr elig
722: where elig.per_in_ler_ended_id = p_pil_id
723: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
724: --
725: cursor c_pep(p_elig_per_id number,p_effective_date date) IS
726: select object_version_number
727: from ben_elig_per_f pep
723: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
724: --
725: cursor c_pep(p_elig_per_id number,p_effective_date date) IS
726: select object_version_number
727: from ben_elig_per_f pep
728: where pep.elig_per_id = p_elig_per_id
729: and p_effective_date between pep.effective_start_date
730: and pep.effective_end_date ;
731: --
749: IF c_pep%FOUND THEN
750: --Get the Datetrack Mode
751: dt_api.find_dt_upd_modes
752: (p_effective_date => l_effective_date,
753: p_base_table_name => 'BEN_ELIG_PER_F',
754: p_base_key_column => 'elig_per_id',
755: p_base_key_value => l_rec.BKUP_TBL_ID,
756: p_correction => l_correction,
757: p_update => l_update,
840: --Now Delete the records from ben_le_clsn_n_rstr
841: --
842: delete from ben_le_clsn_n_rstr elig
843: where elig.per_in_ler_ended_id = p_per_in_ler_id
844: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
845: --
846: hr_utility.set_location ('Leaving '||l_proc,10);
847: --
848: end restore_prev_pep ;
1293: -- BEN_ENRT_BNFT (ENB) (cascade delete in epe api, but epe not deleted)
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)
1478: p_copy_only => p_copy_only,
1479: p_effective_date => p_effective_date);
1480:
1481: -- mark elig_per's 'backed out'
1482: delete_routine(p_routine => 'BEN_ELIG_PER_F',
1483: p_per_in_ler_id => p_per_in_ler_id,
1484: p_business_group_id => p_business_group_id,
1485: p_copy_only => p_copy_only,
1486: 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
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
2513: -- BEN_PER_IN_LER
2514: --
2511: --
2512: -- Source Table
2513: -- BEN_PER_IN_LER
2514: --
2515: cursor c_ben_elig_per_f is
2516: select pep.elig_per_id,
2517: pep.object_version_number
2518: from ben_elig_per_f pep,
2519: ben_per_in_ler pil
2514: --
2515: cursor c_ben_elig_per_f is
2516: select pep.elig_per_id,
2517: pep.object_version_number
2518: from ben_elig_per_f pep,
2519: ben_per_in_ler pil
2520: where pil.per_in_ler_id = p_per_in_ler_id
2521: and pil.business_group_id = p_business_group_id
2522: and pep.per_in_ler_id = pil.per_in_ler_id
2540: --
2541: /*
2542: cursor c_pep_max_esd_of_past_pil(v_elig_per_id in number) is
2543: select max(effective_end_date), max(object_version_number)
2544: from ben_elig_per_f
2545: where elig_per_id = v_elig_per_id
2546: and nvl(per_in_ler_id , -1) <> p_per_in_ler_id
2547: and business_group_id = p_business_group_id;
2548: -- and effective_end_date < p_effective_date;
2548: -- and effective_end_date < p_effective_date;
2549: */
2550: cursor c_pep_max_esd_of_past_pil(v_elig_per_id in number) is
2551: select pep.effective_end_date, pep.object_version_number
2552: from ben_elig_per_f pep,
2553: ben_per_in_ler pil
2554: where pep.elig_per_id = v_elig_per_id
2555: and ((pep.per_in_ler_id <> p_per_in_ler_id
2556: and pep.per_in_ler_id is not null)
2579: --
2580: cursor c_deleted_pep(v_elig_per_id in number,
2581: v_effective_date in date) is
2582: select *
2583: from ben_elig_per_f
2584: where elig_per_id = v_elig_per_id
2585: and nvl(per_in_ler_id , -1) = p_per_in_ler_id
2586: and business_group_id = p_business_group_id
2587: and effective_end_date > v_effective_date;
2585: and nvl(per_in_ler_id , -1) = p_per_in_ler_id
2586: and business_group_id = p_business_group_id
2587: and effective_end_date > v_effective_date;
2588: --
2589: -- END ----> DELETE ROUTINE FOR BEN_ELIG_PER_F
2590: -- START ----> DELETE ROUTINE FOR BEN_PRTT_PREM_F
2591: --
2592: -- Source Table
2593: -- BEN_PER_IN_LER
3873: --
3874:
3875: close c_ben_elig_cvrd_dpnt_f;
3876: --
3877: elsif p_routine = 'BEN_ELIG_PER_F' then
3878:
3879: -- For elig_per, we want to 'un-end' any records that were ENDED
3880: -- due to the per-in-ler. The 'future-change' date track mode will do that
3881: -- for us.
3884:
3885: l_prev_pk_id := -1; -- like null
3886:
3887: --
3888: open c_ben_elig_per_f;
3889: --
3890: loop
3891: --
3892: fetch c_ben_elig_per_f into l_pk_id,
3888: open c_ben_elig_per_f;
3889: --
3890: loop
3891: --
3892: fetch c_ben_elig_per_f into l_pk_id,
3893: l_object_version_number;
3894: exit when c_ben_elig_per_f%notfound;
3895: --
3896: -- Get the maximum of effective end date
3890: loop
3891: --
3892: fetch c_ben_elig_per_f into l_pk_id,
3893: l_object_version_number;
3894: exit when c_ben_elig_per_f%notfound;
3895: --
3896: -- Get the maximum of effective end date
3897: -- with past per_in_ler 's.
3898: --
4033: PL_ORDR_NUM,
4034: PLIP_ORDR_NUM,
4035: PTIP_ORDR_NUM )
4036: values (
4037: 'BEN_ELIG_PER_F',
4038: l_deleted_pep_rec.PLIP_ID,
4039: l_deleted_pep_rec.PTIP_ID,
4040: l_deleted_pep_rec.WAIT_PERD_CMPLTN_DT,
4041: l_deleted_pep_rec.PER_IN_LER_ID,
4176: --
4177: l_prev_pk_id := -1;
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
6605: close c_ben_elig_cvrd_dpnt_f;
6606: --
6607: end if;
6608: --
6609: if c_ben_elig_per_f%isopen then
6610: --
6611: close c_ben_elig_per_f;
6612: --
6613: end if;
6607: end if;
6608: --
6609: if c_ben_elig_per_f%isopen then
6610: --
6611: close c_ben_elig_per_f;
6612: --
6613: end if;
6614: --
6615: if c_ben_elig_per_opt_f%isopen then