[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* This package is used to update the enrollment result to indicate
* it to be suspended and assign a interiem coverage if it's required
* and available.
* History
* Date Who Version What?
* ----------- ---------- ------- -----
* 27 May 1998 maagrawa 110.0 Created.
* 17 Jun 1998 maagrawa 110.1 elctbl_chc api changes.
* 19 Jun 1998 maagrawa 110.2 Header line changes.
* 24 Jun 1998 maagrawa 110.3 Added call to multi_row_edit.
* 07 Jul 1998 jmohapat 110.4 Added batch who col to call of
* ben_elig_per_elc_chc_api.update..
* ,ben_prtt_enrt_result_api.update.
* 22 Jul 1998 maagrawa 110.5 p_rslt_object_version_number argument added.
* 22 Sep 1998 bbulusu 110.6 removed p_enrt_mthd_cd from
* p_suspend_enrollment
* 30 Oct 1998 Hdang 115.7 Change (Un)suspend process logic Remove
* misc. procedures/Functions
* 30 Oct 1998 Hdang 115.8 add per_in_ler_id as one of input parameter.
* 19 Nov 1998 Hdang 115.9 unsuspended options if plan is saving plan.
* 10 Feb 1999 Hdang 115.10 Add logic to handle unsuspend enrollment.
* 19 Feb 1999 Hdang 115.11 Add logic to handle interim coverage.
* 03 Mar 1999 jcarpent 115.12 Removed dbms_output.put_lines
* 04 Mar 1999 jcarpent 115.13 Unsuspend handles old Element entries.
* 22 Mar 1999 jcarpent 115.15 Removed pen join in c_prv cursor
* 03 May 1999 jcarpent 115.16 Added check for prtt_rt_val_stat_cd is null
* 03 May 1999 shdas 115.17 Added contexts to rule calls.
* 03 May 1999 jcarpent 115.18 Added support for unsspnd_enrt_cd
* 03 May 1999 shdas 115.21 Added jurisdiction_cd
* 06 May 1999 jcarpent 115.22 check status of per_in_ler <> 'VOIDD'
* 07 May 1999 lmcdonal 115.23 Check status of prtt_enrt_rslt, and added
* per_in_ler_stat <> 'BCKDT'.
* 08 May 1999 jcarpent 115.24 Check ('VOIDD', 'BCKDT') for pil stat cd
* 11 May 1999 jcarpent 115.25 Fixed unsspnd_enrt_cd of 'UEECSD' to
* handle null l_rec_rt_strt_dt.
* 19 May 1999 jcarpent 115.26 Changed c_current_enrt cursor to union
* to pick up only choice for current pil
* or no choice at all.
* 19 May 1999 jcarpent 115.27 Added c_new_ovn cursor to get updated ovn.
* Changed determine interim result cursor
* to only get old enrollments.
* Process_interim was passing epe.ovn as
* the pen.ovn into ben_election_info.
* 09 Jul 1999 jcarpent 115.28 Added checks for backed out nocopy pil
* 20-JUL-1999 Gperry 115.29 genutils -> benutils package rename.
* 12-Aug-1999 lmcdonal 115.30 Call get_ben_pen_upd_dt_mode before calling
* update_prtt_enrt_rslt.
* 19-Aug-1999 lmcdonal 115.31 Add call to premium_warning.Made p_person_id
* required in update_sspndd_flag.
* 07-Sep-1999 tguy 115.32 fixed call to pay_mag_util
* 09-Sep-1999 maagrawa 115.33 Backport to 115.29. Made fix to calculate
* dpnt_cvg_strt_dt when the code is not null
* 09-Sep-1999 maagrawa 115.34 Leapfrog to 115.32 and applied changes in
* 115.33
* 14-Sep-1999 shdas 115.35 changed election_information to add bnft_amt
* 08-oct-1999 jcarpent 115.36 Added ed to call to create_enrollment_ele
* 10-oct-1999 pbodla 115.37 Added ed to call to reopen_closed_enrollment
* 26-Oct-1999 maagrawa 115.38 Fixed c_choice_info cursor in
* unsuspend_enrt to get choice for the same
* per_in_ler_id as the result.
* 05-Nov-1999 jcarpent 115.39 Fixed interim cursors to be less
* restrictive, use election_information globs
* 12-Nov-1999 lmcdonal 115.41 Better debugging message
* 19-Nov-1999 pbodla 115.42 Added p_elig_per_elctbl_chc_id as parameter to
* get_dflt_to_asn_pndg_ctfn_cd
* 03-Jan-2000 lmcdonal 115.43 When update_prtt_rt_val is called, update the
* per_in_ler_id too. This is used in a check in
* the election_rate_information proc to decide
* if the rt_strt_dt should be recalulated.
* Bug 1121022
* 06-Jan-2000 maagrawa 115.44 Update rate start date and dpnt cvg strt dt
* while unsuspending only if enrt cvg strt dt
* is to be updated.
* Pass person_id when calling
* update_prtt_rt_val api. (Bug 1096737)
* 13-Jan-2000 lmcdonal 115.45 When calling election_info to create a new
* result, pass in the correct enrt_mthd_cd.
* Bug 1147606.
* 24-Jan-2000 maagrawa 115.46 Pass per_in_ler_id when calling
* process_post_results (Bug 1148445)
* 05-Feb-2000 maagrawa 115.47 Fixed interim coverage logic in
* procedure determine_interim (1172233).
*
* 10-FEB-2000 shdas 115.48 call determine_date.main only if
* dpnt_cvg_end_dt_cd is not null.
* 18-Feb-2000 jcarpent 115.49 changed the c_choice_info cursor to use
* the comp object to join instead of the
* result_id since won't be the same.
* 18-Feb-2000 bbulusu 115.49 Added join to plan to determine if
* bnf designation is optional
* 28-Feb-2000 maagrawa 115.50 Pass p_source to delete_enrollment.
*
* 28-Feb-2000 pbodla 115.51 Bug: 4279 : passed p_prtt_rt_val_id to
* reopen. To get the correct element entry
* 24-Mar-2000 lmcdonal 115.52 better debugging messages.
* Bug 1247109 - was sometimes using the wrong
* result id for interim coverage.
* 28-Mar-2000 shdas 115.53 delete ledger row if result is suspended.
* 30-Mar-2000 lmcdonal 115.54 Bug 1252084 - When cvg restriction is Opt,
* we still may have a bnft row hanging off
* the opt. Fetch it: c_next_lower_oipl_epe,
* c_min_oipl_epe
* 13-Apr-2000 pbodla 115.55 - Bug 5052 - unsuspend_enrollment :
* moved the interim update or delete to
* the beginning of procedure. First
* update or delete the interim and then
* do the un suspend of original enrollment.
* 17-Apr-2000 maagrawa 115.56 - Bug 5098. Check the ctfn_rqd_flag for the
* benefit record also, even if the rstrn
* is "Option Restriction applies".
* 22-May-2000 lmcdonal 115.57 Bug 1249901 - when cvg is entered at enrt
* and interim is 'min', give them the min
* amt from same bfnt row.
* 23-May-2000 lmcdonal 115.58 Fix v57 fix so that other interims with
* 'min' codes work.
* 24-May-2000 shdas 115.59 bug 5234- interim cvg end date is set based on
* datetrack mode.
* 19-Jul-2000 rchase 115.61 bug 5353 - iterim cvg not selecting previous
* cvg if previous cvg exists in same pl or pl_typ
* 19-Jul-2000 rchase 115.62 bug 5181 - backed out nocopy cvg included in selection
* for current.
* 04-Aug-2000 jcarpent 115.63 bug 5353 - Bug was wrong. Should use
* code not hardcode to 'SM'
* Also bug 5427. Was not checking interim
* codes stored on plip.
* 28-Aug-2000 jcarpent 115.64 bug 1386626. Recalc imputed income for
* suspend/unsuspend enrollments.
*
* 06-Sep-2000 rchase 115.65 fix for bug#1394066. Set the interim coverage
* date to suspended - 1 if coverage start
* dates are the same.
* 23-Oct-2000 pbodla 115.66 fix for bug#1471135 : Added code to reset
* the enrt_cvg_strt_dt after the cursor
* csr_prtt_enrt_rslt is opened second time.
* 14-Nov-2000 rchase 115.67 Bug 1477284. Also look for unsuspend
* enrt_cd at ptip level.
* 09-Jan-2001 mhoyes 115.68 - Added new out nocopy parameter to call
* create_enrollment_element.
* 27-Feb-2001 kmahendr 115.69 - Bug#1649847 - changed value of parameter from
* p_per_in_ler_id to l_per_in_ler_id to call
* ben_determine_date.rate_and_coverage_dates
* as null was passed
* 29-Mar-2001 maagrawa 115.70 When the enrollment is unsuspended, update
* or create the element entries for rates
* which are active for this result and
* life event.
* 02-Apr-2001 kmahendr 115.71 - Bug#1617825 - when the enrollment is unsuspended
* call create_debit_ledger_entries to write ledger
* entries into pool
* 27-aug-2001 tilak 115.72 bug:1949361 jurisdiction code is
derived inside benutils.formula.
* 02-nov-2001 pbodla 115.73 bug:2088231 Called accumulate pools after
* unsuspend enrollment.
* 23-Jan-2002 ikasire 115.74 bug:2185509 when unsuspended, we are calling
* the ben_provider_pools.remove_bnft_prvdd_ldgr
* to remove the ledger entries of the interim
* coverage
* 30-Jan-2002 ikasire 115.75 Bug2191886 fixed the error where effective
* date is used for life event occured date in
* unsuspend enrollment.
* 28-Jan-2002 hnarayan 115.76 Bug 1826902 when rule is attached to interim
* coverage, the default to assign pending code
* returned by the rule is captured as Varchar2
* and returned as number. Fixed.
* 13-Mar-2002 pbodla 115.77 p_cmncd_rt, and p_ann_rt values passed to
* create_enrollment_element : Based on
* ele_entry_val_cd communicated and annual
* rate values are used in EE creation.
* 29-Mar-2002 ikasire 115.78 Bug 1998648 Interim issues related with
* default code are fixed. See bug for more
* details.
*
* 10-Apr-2002 ikasire 115.79 Bug 1886183 fixed the enter value at enrollment
* cases for the determine_interim procedure.
* 20-Apr-2002 ikasire 115.80 Bug 1886183 fixed the cursor which determines the
* current enrollment in determine_interim process
* 08-Jun-2002 pabodla 115.81 Do not select the contingent worker
* assignment when assignment data is
* fetched.
* 02-Jul-2002 pabodla 115.82 Bug 2396628: at the time of unsuspending a plan
* check whether interim is there or not
* if interim is already deleted then bypass
* deletion logic.
* 07-Jul-2002 ikasire 115.82 Bug 2502633 - Interim Enhancements
* See Bug for more details.
* 13-Aug-2002 hnarayan 115.84 Bug 2330694 - Premium handling for interim
* coverage. See bug for details
* 03-Sep-2002 ikasire 115.85 Bug 2538015 changes to unsuspend enrollment
* procedure to use the unsuspend code for
* ending the interim coverage, rates and
* starting the unsuspeded enrollment coverage and
* rates.
* Also fixed the interim code xxx,xxx;New, Next Lower
* 1.update epe with the pen id
* 2.update enb to removed the interim pen id
* and make sure we have the right pen id on
* the unsuspended pen
* 26-Jun-2003 ikasire 115.93 c_pea is getting called multiple times due
* to date trackupdate of the pen row.
* 23-Jul-2003 ikasire 115.94 Bug 3042379 fixes for indefinate loop when
* multiple certifications are required or
* dependent or beneficiary designation in
* combination with benefit restrictions.
* 18-Aug-2003 ikasire 115.95 Bug 3095291 cleaned the c_current_enrt
* cursor and removed the union and out joined
* to epe.
* 30-Oct-2003 kmahendr 115.96 Bug#3202455 - added a cursor c_previous_status
* in unsuspend_enrollment procedure.
* 25-Nov-2003 ikasire 115.97 Bug 3278908 Modified the c_ppe cursor get the
* correct record
* 14-Jan-2004 ikasire 115.98 Bug fix 3202455 introduced another regression
* Since l_previous_no_sspn is not initialized
* nevel goes into IF clause and always goes into
* else clause. This will make the system not to
* use unsuspend code.
* 21-Jan-2004 mmudigon 115.99 Bug 3317017. CWB Changes
* 16-Feb-2004 ikasire 115.100 Bug 3441027 compute the date track mode while
* unsuspending the result
* 08-Jun-2004 kmahendr 115.101 Bug#3659657 - added code to suspend_enroll procedure
* to handle correction of date received for certification
* 22-Jun-2004 kmahendr 115.102 Bug#3692450 - rate start date not changedif the
* unsuspend code is UEECSD.
* 30-Jun-2004 tjesumic 115.103 bug 3666347 fixed by reverting 115.84 2330694 fix
* 02-Aug-2004 kmahendr 115.104 Bug#3794162 - added a parameter - p_per_in_ler_id
* to determine_interim and modified cursors by joing
* per_in_ler_id
* 04-Aug-2004 kmahendr 115.105 Bug#3794162 - modified cursor c_interim
* 23-Aug-2004 mmudigon 115.106 CFW. Added p_act_item_flag to
* suspend_enrollment.
* 2534391 :NEED TO LEAVE ACTION ITEMS
* 26-aug-2004 nhunur 115.107 gscc compliance
* 05-sep-2004 ikasire 115.108 FIDOML Override Enhancements
* 07-Sep-2004 mmudigon 115.109 CFW. Changes to suspend_enrollment
* 09-Sep-2004 mmudigon 115.110 CFW. p_act_item_flag no longer needed
* 03-Nov-2004 ikasire 115.111 Bug 3977951 fix
* 13-Nov-2004 kmahendr 115.112 Bug#4009443-modified cursor c_current_enrt
* 16-Nov-2004 kmahendr 115.113 Bug#4009443-modified cursor c_current_enrt
* 30-dec-2004 nhunur 115.114 4031733 - No need to open cursor c_state.
* 07-Jan-2005 ikasire 115.115 Bug 4064635. Need to carry forward suspended and
* interim enrollment
* 11-Jan-2005 ikasire 115.116 CF Interim Suspended BUG 4064635
* 18-Jan-2005 ikasire 115.117 CF Interim Suspended BUG 4064635- unsuspend epe
* for electable is 'N'
* 02-Feb-2005 ikasire 115.118 CF Interim Suspended BUG 4064635
* 10-Feb-2005 kmahendr 115.119 Bug#4172569 - suspend flag is checked
* 16-Feb-2005 kmahendr 115.120 Bug#4186343 - cursors in determine_interim modified to
* look for optional certification
* 07-Mar-2005 ikasire 115.121 Bug#4223840 Second part of Interim code is not
* evaluated right in determining the interim code
* when you save the enrollments multiple times.
* 18-Mar-2005 ikasire 115.122 Bug 4247213 Performance changes
* 24-Mar-2005 abparekh 115.123 Bug 4256836 : While determining interim coverage
* 115.124 select electable choice that falls under the program
* for which enrollment is suspended. This it to avoid
* suspended and interim falling into different programs
* 05-Apr-2005 abparekh 115.125 Bug 4141269 pass p_input_value_id and p_element_type_id
* as null to ben_element_entry.create_enrollment_element
* 14-Apr-2005 ikasire 115.126 Added new parameter to manage_enrt_bnft call
* 20-Jun-2005 mmudigon 115.127 Bug 4352871. Added logic to delete element
* entries when pen is suspended.
* 29-Jun-2005 ikasire 115.128 Bug 4422667 getting into loop issue
* 17-Aug-2005 ikasire 115.129 Bug 4547332 fix changes to c_current_enrt
* 19-Aug-2005 ikasire 115.130 Bug 4563223 to filter program in the cursor
* 26-Aug-2005 ikasire 115.131 Bug 4558512 for completion date
* search string p_cmpltd_dt
* 01-Sep-2005 ikasire 115.132 Bug 4577581 we need to pass p_per_in_ler_id to
* multi row edit call from susps and unsusp
* 13-Sep-2005 ikasire 115.133 Bug 4463267 fix several interim cursors
* 15 Sep 2005 ikasire 115.134 Bug 4450214 Added cfw condition bases on
* g_cfw_flag and modified the cfw cursor to
* to function as per the changed process in
* election_information.
* 22 Sep 2005 ikasire 115.135 Bug 4622534 for carrforward dependents from
* default rule
* 27 Sep 2005 mmudigon 115.136 Bug 4622534 continued. Added join on
* person_id in cursor c_cf_suspended
* 05 Dec 2005 bmanyam 115.137 4775743: If Dpnt Cvg starts after the PEN Cvg Strt,
* then use Dpnt Cvg Strt, as the Start date
* for restoring results.
* 07 Mar 2006 ikasired 115.140 Interim - Default to Assign Pending Action
* Rule Enhacenments.
* 07 Mar 2006 ikasired 115.141 Interim - Rule more changes
* 12 Apr 2006 ikasired 115.142 fix for regression from 115.120 version
* Flat Range see Bug 5158595
* 12 Apr 2006 ikasired 115.143 fix for flat rante bug 5158471
* 19 Apr 2006 gsehgal 115.144 bug:5148514. change the message when interim amount is
* equal to benefit amount.
* 27 Apr 2006 nhunur 115.145 bug:5135117. interim amount should be less than
* benefit amount.
* 16 May 2006 swjain 115.146 Bug 5225780 - Updated procedure validate_interim_rule
* to pick valid epe records
* 17 May 2006 swjain 115.147 Bug 5225780 - Updated the message number
* 18-May-2006 abparekh 115.149 Bug 5231894 - While un-suspending, update rate start date
* only if its earlier than date of un-suspension
* 11-Jul-2006 ssarkar 115.150 Bug 5381200 - modified c_bnft,c_dflt_bnft of proc determine_interim
* 26-Jul-2006 rtagarra 115.151 Bug 5402317 - modified cursor c1 to check as per lf_evt_ocrd_dt.
* 01-Aug-2006 abparekh 115.152 Bug 5415757 - Commented cursor c_rt clause that prevented carry
* forward of rates for interim PEN.
* 30-Aug-2006 rtagarra 115.153 Bug 5491212 - Changed the cursor Csr_prtt_enrt_rslt.
* 12-Oct-2006 ikasired 115.154 Bug 5596918 fix for 'SAME' part in carryforward. bnft amt null issue
* 12-Oct-2006 ikasired 115.154 Bug 5596907 fix for carryforward to use right bnft record
* 10-nov-2006 ssarkar 115.155 Bug 5653168 - resetting g_interim_flag for any exception in process_interim
* 18-May-2007 swjain 115.156 Bug 6054988 - In procedure unsuspend_enrollment, call multi_rows_edit only
if any elections made in the current pil
* 22-jun-2007 nhunur 115.157 perf changes
* 24-Aug-2007 gsehgal 115.158 bug 6337803 added global variable g_sspnded_rslt_id to store the pen id of
enrollment going to suspend at the time of processing the interim
* 12-Nov-2007 sshetty 115.156.11516.2 Bug 6597329 Added per_in_ler id
* check for c_prv_sspnd to fix
* the purge issue
* 22-Feb-2008 rtagarra 115.157 Bug 6840074
* 23-Jun-2008 sallumwa 115.161 Bug 7195598 - Fixed cursor c_cur_bnft to fetch benefit records
* even if order number is 1,which inturn is used to
* calculate interim rates.
* 18-sep-2008 sallumwa 115.162 Bug 7262435 : Fixed cursor c_cur_bnft to fetch correct benefit
* records which inturn is used to
* calculate interim rates for EVAT and falt range cases.
=========================================================================================*/
g_package varchar2(80) := 'ben_sspndd_enrollment';
select epe.*
from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id(+) /* Bug 5225780 */
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or
pil.per_in_ler_stat_cd is null);
select enb.*
from ben_enrt_bnft enb
where enb.enrt_bnft_id = v_enrt_bnft_id ;
select enb.*
from ben_enrt_bnft enb
where -- enb.enrt_bnft_id = v_enrt_bnft_id
enb.val = p_interim_bnft_amt
and enb.elig_per_elctbl_chc_id = v_epe_id ;
select enb.*
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and enb.MX_WO_CTFN_FLAG = 'N' ;
select enb.*
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and enb.MX_WO_CTFN_FLAG = 'Y' ;
select ecc.rqd_flag,
ecc.enrt_ctfn_typ_cd,
ecc.SUSP_IF_CTFN_NOT_PRVD_FLAG,
ecc.ctfn_determine_cd
from ben_elctbl_chc_ctfn ecc
where ecc.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ecc.enrt_bnft_id is null
and ecc.business_group_id = p_business_group_id ;
select count(*)
from ben_enrt_bnft
where elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ordr_num >= 0 ; --This will exclude the records being create for SAME case
select pen.*
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
and pen.per_in_ler_id = v_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd IS NULL
and pen.effective_end_date = hr_api.g_eot ;
select region_2
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and p_effective_date between
asg.effective_start_date and asg.effective_end_date
and asg.business_group_id=p_business_group_id;
select epe.business_group_id,
epe.pgm_id,
epe.pl_id,
epe.pl_typ_id,
epe.oipl_id,
pil.ler_id
from ben_elig_per_elctbl_chc epe,ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and pil.per_in_ler_id = epe.per_in_ler_id;
select 1 order_no
,DFLT_TO_ASN_PNDG_CTFN_CD
,DFLT_TO_ASN_PNDG_CTFN_RL
,null BNFT_OR_OPTION_RSTRCTN_CD
From ben_ler_bnft_rstrn_f
Where pl_id = p_pl_id
and ler_id = p_ler_id
and l_get_lf_evt_ocrd_dt between --Bug#5402317
effective_start_date and effective_end_date
and business_group_id=p_business_group_id
and DFLT_TO_ASN_PNDG_CTFN_CD is not NULL
Union
select 2 order_no
,plip.DFLT_TO_ASN_PNDG_CTFN_CD
,plip.DFLT_TO_ASN_PNDG_CTFN_RL
,plip.BNFT_OR_OPTION_RSTRCTN_CD
From ben_plip_f plip, ben_prtt_enrt_rslt_f pen
Where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.pgm_id = plip.pgm_id
and pen.pl_id = plip.pl_id
and pen.prtt_enrt_rslt_stat_cd is null
and l_get_lf_evt_ocrd_dt between --Bug#5402317
pen.effective_start_date and pen.effective_end_date
and l_get_lf_evt_ocrd_dt between
plip.effective_start_date and plip.effective_end_date
and pen.business_group_id=p_business_group_id
-- and DFLT_TO_ASN_PNDG_CTFN_CD is not NULL
Union
select 3 order_no
,DFLT_TO_ASN_PNDG_CTFN_CD
,DFLT_TO_ASN_PNDG_CTFN_RL
,BNFT_OR_OPTION_RSTRCTN_CD
From ben_pl_f
Where pl_id = p_pl_id
and l_get_lf_evt_ocrd_dt between --Bug#5402317
effective_start_date and effective_end_date
and business_group_id=p_business_group_id
-- and DFLT_TO_ASN_PNDG_CTFN_CD is not NULL
order by 1
;
Select assignment_id,organization_id
From per_all_assignments_f
Where person_id = p_person_id
and assignment_type <> 'C'
And business_group_id=p_business_group_id
and p_effective_date between
effective_start_date and effective_end_date
and primary_flag = 'Y'
;
is select oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = l_epe.oipl_id
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date
and oipl.business_group_id=p_business_group_id;
select per.pl_id,
per.pl_typ_id,
epe.elig_per_elctbl_chc_id,
-- RCHASE Bug#5353 added
per.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f per,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
pil.per_in_ler_stat_cd='STRTD' and
per.person_id=p_person_id and
per.business_group_id=p_business_group_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
p_effective_date-1 between
per.effective_start_date and per.effective_end_date and
-- RCHASE Bug#5181 check against life event occrd date -1
-- instead of effective date
-- p_effective_date <= per.enrt_cvg_thru_dt and
pil.lf_evt_ocrd_dt-1 <= per.enrt_cvg_thru_dt and
-- per.enrt_cvg_strt_dt < p_effective_date and
per.enrt_cvg_strt_dt <= pil.lf_evt_ocrd_dt-1 and
-- RCHASE Bug#5181 don't check my per_in_ler
per.per_in_ler_id <> pil.per_in_ler_id and
per.pl_typ_id=p_pl_typ_id and
per.pl_id = nvl(v_pl_id, per.pl_id) and
nvl(per.oipl_id,-1) = nvl(v_oipl_id,nvl(per.oipl_id,-1)) and
epe.prtt_enrt_rslt_id=per.prtt_enrt_rslt_id and
epe.business_group_id=per.business_group_id
-- epe.per_in_ler_id=pil.per_in_ler_id Bug 1886183 This doesnot work for benefit amt changes
union
select per.pl_id,
per.pl_typ_id,
to_number(null) elig_per_elctbl_chc_id,
-- RCHASE Bug#5353
per.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f per,
ben_per_in_ler pinl
where per.per_in_ler_id=pinl.per_in_ler_id and -- Bug 2595113
per.person_id=p_person_id and
per.business_group_id=p_business_group_id and
per.sspndd_flag='N' and
per.prtt_enrt_rslt_stat_cd is null and
p_effective_date-1 between
per.effective_start_date and per.effective_end_date and
-- RCHASE Bug#5181 check against life event occrd date -1
-- instead of effective date
-- p_effective_date <= per.enrt_cvg_thru_dt and
-- per.enrt_cvg_strt_dt < p_effective_date and
pinl.lf_evt_ocrd_dt-1 <= per.enrt_cvg_thru_dt and
per.enrt_cvg_strt_dt <= pinl.lf_evt_ocrd_dt-1 and
-- RCHASE Bug#5181 don't check my per_in_ler
-- per.per_in_ler_id <> pinl.per_in_ler_id and -- Bug 2595113
per.pl_typ_id=p_pl_typ_id and
per.pl_id = nvl(v_pl_id, per.pl_id) and
nvl(per.oipl_id,-1) = nvl(v_oipl_id,nvl(per.oipl_id,-1)) and
not exists (
select null
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
pil.per_in_ler_stat_cd='STRTD' and
epe.prtt_enrt_rslt_id=per.prtt_enrt_rslt_id and
epe.business_group_id=per.business_group_id and
epe.per_in_ler_id=pil.per_in_ler_id)
;
[the data will change once the delete enrollment is called]
Ended OldOipl
NewPIL
OldPEN
CTD FILLED
EED EOT
New NewOipl
NewPIL
NewPEN
CTD EOT
EED EOT
Case 3: Replace the enrollment with a new Plan Option
OldOipl OldOipl
OldPIL OldPIL [Important]
OldPEN OldPEN
CTD EOT
EED EOT
[the data will change once the delete enrollment is called]
Ended OldOipl
NewPIL
OldPEN
CTD FILLED
EED EOT
New NewOipl
NewPIL
NewPEN
CTD EOT
EED EOT
Case 4: delete the current enrollment and enroll in a new one later
OldOipl OldOipl
OldPIL NewPIL
OldPEN OldPEN
CTD FILLED
EED EOT
[the data will change once the delete enrollment is called]
Ended OldOipl
NewPIL
OldPEN
CTD FILLED
EED EOT
New NewOipl
NewPIL
NewPEN
CTD EOT
EED EOT
*/
--BUG 4547332 rewriten sql
--Check the above cases before changing any logic
--
cursor c_current_enrt(v_pl_id number, v_oipl_id number ) is
select per.pl_id,
per.pl_typ_id,
per.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f per,
ben_per_in_ler pil
where pil.business_group_id = p_business_group_id and
pil.person_id = p_person_id and
pil.per_in_ler_id = p_per_in_ler_id and
per.person_id = pil.person_id and
per.business_group_id = p_business_group_id and
per.sspndd_flag = 'N' and
per.prtt_enrt_rslt_stat_cd is null and
per.effective_end_date = hr_api.g_eot and
per.enrt_cvg_strt_dt < per.effective_end_date and
(
( p_per_in_ler_id = per.per_in_ler_id and
( /* Case 4 */
per.enrt_cvg_thru_dt <> hr_api.g_eot or
( /* Case 1,2*/
( exists (select 'x' from ben_prtt_enrt_rslt_f pen3
where pen3.prtt_enrt_rslt_id = per.prtt_enrt_rslt_id and
pen3.prtt_enrt_rslt_stat_cd is null and
pen3.sspndd_flag = 'N' and
pen3.effective_end_date < per.effective_start_date and
pen3.enrt_cvg_thru_dt = hr_api.g_eot and
pen3.per_in_ler_id <> per.per_in_ler_id
)
) and
per.enrt_cvg_thru_dt = hr_api.g_eot
)
)
)
or
( /* Case 3 */
per.enrt_cvg_thru_dt = to_date('31-12-4712','dd-mm-yyyy') and
per.per_in_ler_id <> p_per_in_ler_id
)
) and
per.pl_typ_id = p_pl_typ_id and
(( per.pgm_id = p_pgm_id) or
(p_pgm_id is null)
) and -- BUG 4563223
((per.pl_id = v_pl_id) or
(v_pl_id is null)) and
((per.oipl_id = v_oipl_id) or
(v_oipl_id is null))
;
select epe.elig_per_elctbl_chc_id,
enb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_pl_f pl,
ben_enrt_bnft enb
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_typ_id =p_pl_typ_id and
epe.business_group_id=p_business_group_id and
epe.elctbl_flag='Y' and
epe.dflt_flag='Y' and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
-- epe.ctfn_rqd_flag='N' and
epe.pl_id = pl.pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
enb.elig_per_elctbl_chc_id(+)=epe.elig_per_elctbl_chc_id and
nvl(enb.dflt_flag,'Y') = 'Y' and
nvl(enb.ctfn_rqd_flag,'N') ='N' and
--bug#4186343
not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
order by epe.plip_ordr_num,epe.oipl_ordr_num
;
select epe.elig_per_elctbl_chc_id,
enb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_pl_f pl,
ben_enrt_bnft enb
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_id =p_pl_id and
epe.business_group_id=p_business_group_id and
epe.elctbl_flag='Y' and
epe.dflt_flag='Y' and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
-- epe.ctfn_rqd_flag='N' and
epe.pl_id = pl.pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
enb.elig_per_elctbl_chc_id(+)=epe.elig_per_elctbl_chc_id and
nvl(enb.dflt_flag,'Y') = 'Y' and
nvl(enb.ctfn_rqd_flag,'N') ='N' and
--bug#4186343
not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
order by epe.plip_ordr_num,epe.oipl_ordr_num
;
select epe.elig_per_elctbl_chc_id,
enb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_pl_f pl,
ben_enrt_bnft enb
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_typ_id =p_pl_typ_id and
epe.business_group_id=p_business_group_id and
-- epe.elctbl_flag='Y' and
epe.dflt_flag='Y' and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
epe.ctfn_rqd_flag='N' and
epe.pl_id = pl.pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
enb.elig_per_elctbl_chc_id(+)=epe.elig_per_elctbl_chc_id and
((nvl(enb.dflt_flag,'Y') = 'Y' and
nvl(enb.ctfn_rqd_flag,'N') ='N')
or ( nvl(enb.mx_wo_ctfn_flag,'Y') = 'Y' and nvl(enb.ordr_num,0)=0 ) )
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
-- If the default one is enter value at enrollment then ctfn is Y
order by epe.plip_ordr_num,epe.oipl_ordr_num,nvl(enb.ordr_num,1)
;
select epe.elig_per_elctbl_chc_id,
enb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_pl_f pl,
ben_enrt_bnft enb
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_id =p_pl_id and
epe.business_group_id=p_business_group_id and
-- epe.elctbl_flag='Y' and
epe.dflt_flag='Y' and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
epe.ctfn_rqd_flag='N' and
epe.pl_id = pl.pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
enb.elig_per_elctbl_chc_id (+) = epe.elig_per_elctbl_chc_id and
((nvl(enb.dflt_flag,'Y') = 'Y' and nvl(enb.ctfn_rqd_flag,'N') ='N')
or ( nvl(enb.mx_wo_ctfn_flag,'Y') = 'Y' and nvl(enb.ordr_num,0)=0 ) )
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
-- If the default one is enter value at enrollment then ctfn is Y
order by epe.oipl_ordr_num,enb.ordr_num
;
select epe.elig_per_elctbl_chc_id, eb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_pl_f pl,
ben_enrt_bnft eb
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_id =p_pl_id and
epe.elctbl_flag='Y' and
epe.business_group_id=p_business_group_id and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
-- epe.ctfn_rqd_flag='N' and
epe.pl_id = pl.pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
epe.elig_per_elctbl_chc_id = eb.elig_per_elctbl_chc_id(+) and
nvl(eb.ctfn_rqd_flag,'N') = 'N' and
nvl(eb.ordr_num,1) > 0 and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
epe.oipl_ordr_num is not null and
epe.oipl_ordr_num< p_oipl_ordr_num
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
order by epe.oipl_ordr_num ;
select epe.elig_per_elctbl_chc_id,
enb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb,
ben_pl_f pl
where pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_typ_id =p_pl_typ_id and
-- epe.elctbl_flag='Y' and
epe.business_group_id=p_business_group_id and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
epe.pl_id = pl.pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
epe.ctfn_rqd_flag='N' and
epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id (+) and
nvl(enb.ctfn_rqd_flag,'N') = 'N' and
nvl(enb.ordr_num,1) > 0 and
epe.plip_ordr_num is not null and
epe.plip_ordr_num<= p_plip_ordr_num
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
order by epe.plip_ordr_num,epe.oipl_ordr_num ;
select epe.elig_per_elctbl_chc_id,
eb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_pl_f pl,
ben_enrt_bnft eb
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_id =p_pl_id and
epe.elctbl_flag='Y' and
epe.business_group_id=p_business_group_id and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
-- epe.ctfn_rqd_flag='N' and
epe.pl_id = pl.pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
epe.elig_per_elctbl_chc_id = eb.elig_per_elctbl_chc_id(+) and
nvl(eb.ctfn_rqd_flag,'N') = 'N' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
epe.oipl_ordr_num < p_oipl_ordr_num
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
order by epe.oipl_ordr_num desc
;
select epe.elig_per_elctbl_chc_id,
eb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft eb,
ben_pl_f pl
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_typ_id =p_pl_typ_id and
epe.elctbl_flag='Y' and
epe.business_group_id=p_business_group_id and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
epe.pl_id = pl.pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
-- epe.ctfn_rqd_flag='N' and
epe.elig_per_elctbl_chc_id = eb.elig_per_elctbl_chc_id (+) and
nvl(eb.ctfn_rqd_flag,'N') = 'N' and -- Bug 2677315 changed the (+) to nvl
(epe.plip_ordr_num <= p_plip_ordr_num and
(epe.plip_ordr_num <> p_plip_ordr_num or
(epe.oipl_ordr_num is null or epe.oipl_ordr_num < p_oipl_ordr_num ))) -- changed to < from <=
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
order by epe.plip_ordr_num desc, epe.oipl_ordr_num desc
;
select epe.elig_per_elctbl_chc_id,
enb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb,
ben_pl_f pl
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.pl_id =p_pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(epe.oipl_id,-1)= nvl( p_oipl_id,-1) and
epe.elctbl_flag='Y' and
epe.business_group_id=p_business_group_id and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
-- epe.ctfn_rqd_flag='N' and
epe.pl_id = pl.pl_id and
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
enb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
enb.business_group_id=p_business_group_id and
enb.ctfn_rqd_flag = 'N' and
enb.ordr_num < p_bnft_ordr_num
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y'
and enrt_bnft_id = enb.enrt_bnft_id ) --BUG 5158595
order by enb.ordr_num
;
select epe.elig_per_elctbl_chc_id,
eb.enrt_bnft_id
from ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft eb,
ben_pl_f pl
where
pil.business_group_id=p_business_group_id and
pil.person_id=p_person_id and
--pil.per_in_ler_stat_cd='STRTD' and
pil.per_in_ler_id = p_per_in_ler_id and
epe.per_in_ler_id=pil.per_in_ler_id and
epe.elctbl_flag='Y' and
epe.pl_id =p_pl_id and
( epe.pgm_id = p_pgm_id or
p_pgm_id is null ) and /* Bug 4256836 */
nvl(epe.oipl_id,-1) = nvl(p_oipl_id,-1) and
epe.business_group_id=p_business_group_id and
nvl(epe.dpnt_dsgn_cd,'O')='O' and
-- epe.ctfn_rqd_flag='N' and
eb.ordr_num < p_bnft_ordr_num and
eb.ordr_num > 0 and
epe.pl_id = pl.pl_id and
nvl(pl.bnf_dsgn_cd, 'O') = 'O' and
p_effective_date between
pl.effective_start_date and pl.effective_end_date and
eb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
eb.business_group_id=p_business_group_id and
eb.ctfn_rqd_flag='N'
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and enrt_bnft_id = eb.enrt_bnft_id
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
order by eb.ordr_num desc ;
select nvl(enb2.mn_val, 0) min_bnft_amt,
nvl(enb2.dflt_val, 0) dflt_bnft_amt, -- Bug 1886183
enb1.enrt_bnft_id
from ben_enrt_bnft enb1,
ben_enrt_bnft enb2
where enb1.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and enb1.cvg_mlt_cd = 'FLFX'
and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
and enb2.entr_val_at_enrt_flag = 'Y'
and enb1.entr_val_at_enrt_flag = 'N'; -- Bug 1886183 changed to 'N'
select enb1.val bnft_amt,
enb1.enrt_bnft_id
from ben_enrt_bnft enb1,
ben_enrt_bnft enb2,
ben_pl_f pl,
ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
--to make it consistent with other
--interim cursors.
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and nvl(epe.dpnt_dsgn_cd,'O')='O'
-- and epe.ctfn_rqd_flag='N'
and epe.pl_id = pl.pl_id
and p_effective_date between
pl.effective_start_date and pl.effective_end_date
and nvl(pl.bnf_dsgn_cd, 'O') ='O'
and enb1.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and enb1.cvg_mlt_cd = 'FLFX'
and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
and enb2.entr_val_at_enrt_flag = 'Y'
and enb1.entr_val_at_enrt_flag = 'N'
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and enrt_bnft_id = enb1.enrt_bnft_id -- 5381200
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y' )
and enb1.ordr_num = 0 ;
select enb1.val bnft_amt,
enb1.enrt_bnft_id
from ben_enrt_bnft enb1,
ben_enrt_bnft enb2,
ben_pl_f pl,
ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
--to make it consistent with other
--interim cursors.
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and nvl(epe.dpnt_dsgn_cd,'O')='O'
-- and epe.ctfn_rqd_flag='N'
and epe.pl_id = pl.pl_id
and p_effective_date between
pl.effective_start_date and pl.effective_end_date
and nvl(pl.bnf_dsgn_cd, 'O') ='O'
and enb1.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and enb1.cvg_mlt_cd = 'FLFX'
and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
and enb2.entr_val_at_enrt_flag = 'Y'
and enb1.entr_val_at_enrt_flag = 'N'
and enb1.ordr_num = 0
and enb2.dflt_flag = 'Y'
and not exists ( select 'Y'
from ben_elctbl_chc_ctfn
where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and enrt_bnft_id = enb1.enrt_bnft_id -- 5381200
and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y') ;
select enb.val bnft_amt,
enb.enrt_bnft_id
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
----Bug : 7195598
and enb.ordr_num in (-1,1)
---Bug 7195598
---Bug 7262435
order by enb.ordr_num
---Bug 7262435
;
select epe_new.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe_current,
ben_elig_per_elctbl_chc epe_new,
ben_per_in_ler pil_new
where epe_current.elig_per_elctbl_chc_id = c_current_epe_id
and epe_current.pl_id = epe_new.pl_id
and nvl(epe_current.oipl_id,-1) = nvl(epe_new.oipl_id,-1)
and epe_new.comp_lvl_cd not in ( 'PLANFC' , 'PLANIMP')
and epe_new.crntly_enrd_flag = 'Y'
and pil_new.per_in_ler_id = epe_new.per_in_ler_id
and pil_new.per_in_ler_stat_cd='STRTD' ;
select epe_new.elig_per_elctbl_chc_id
from ben_prtt_enrt_rslt_f pen_current,
ben_elig_per_elctbl_chc epe_new,
ben_per_in_ler pil_new
where pen_current.prtt_enrt_rslt_id = c_current_pen_id
and pen_current.pl_id = epe_new.pl_id
and nvl(pen_current.pgm_id,-1) = nvl(epe_new.pgm_id,-1)
and nvl(pen_current.oipl_id,-1) = nvl(epe_new.oipl_id,-1)
and epe_new.comp_lvl_cd not in ( 'PLANFC' , 'PLANIMP')
and epe_new.crntly_enrd_flag = 'Y'
and pil_new.per_in_ler_id = epe_new.per_in_ler_id
and pil_new.person_id = p_person_id
--and pil_new.per_in_ler_stat_cd='STRTD' ;
updated. Now we are deleting the suspended enrollment and recreating
new result.
cursor c_cf_suspended(c_prtt_enrt_rslt_id number,
c_per_in_ler_id number,
c_elig_per_elctbl_chc_id number) is
select currently_susp.prtt_enrt_rslt_id,
currently_susp.rplcs_sspndd_rslt_id
from ben_prtt_enrt_rslt_f susp,
ben_prtt_enrt_rslt_f currently_susp
where susp.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and susp.per_in_ler_id = c_per_in_ler_id
-- and susp.sspndd_flag = 'N' --This can be changed once elinf is fixed
and susp.effective_end_date = hr_api.g_eot
and susp.enrt_cvg_thru_dt = hr_api.g_eot
and susp.prtt_enrt_rslt_id = currently_susp.prtt_enrt_rslt_id
and currently_susp.effective_end_date+1 = susp.effective_start_date
and currently_susp.enrt_cvg_thru_dt = hr_api.g_eot
and currently_susp.per_in_ler_id <> c_per_in_ler_id
and currently_susp.sspndd_flag = 'Y' ;
select currently_susp.prtt_enrt_rslt_id,
currently_susp.rplcs_sspndd_rslt_id
from ben_prtt_enrt_rslt_f susp,
ben_prtt_enrt_rslt_f currently_susp
where susp.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and susp.per_in_ler_id = c_per_in_ler_id
-- and susp.sspndd_flag = 'N' --This can be changed once elinf is fixed
and susp.effective_end_date = hr_api.g_eot
and susp.enrt_cvg_thru_dt = hr_api.g_eot
and currently_susp.prtt_enrt_rslt_stat_cd IS NULL
and susp.person_id = currently_susp.person_id
and (currently_susp.pl_id = susp.pl_id AND
(p_pgm_id IS NULL or currently_susp.pgm_id = susp.pgm_id) AND
(p_oipl_id IS NULL or currently_susp.oipl_id = susp.oipl_id))
and currently_susp.effective_end_date = hr_api.g_eot
and currently_susp.enrt_cvg_thru_dt = hr_api.g_eot
and currently_susp.per_in_ler_id <> c_per_in_ler_id
and currently_susp.sspndd_flag = 'Y' ;
select new_epe.elig_per_elctbl_chc_id,
interim.bnft_ordr_num ordr_num,
interim.bnft_amt
from ben_prtt_enrt_rslt_f interim,
ben_elig_per_elctbl_chc new_epe
where interim.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and interim.per_in_ler_id <> c_per_in_ler_id
and interim.effective_end_date = hr_api.g_eot
and interim.enrt_cvg_thru_dt = hr_api.g_eot
and new_epe.per_in_ler_id = c_per_in_ler_id
and new_epe.pl_id = interim.pl_id
and nvl(new_epe.pgm_id,-1) = nvl(interim.pgm_id,-1)
and nvl(new_epe.oipl_id,-1)= nvl(interim.oipl_id,-1)
and interim.prtt_enrt_rslt_stat_cd is null ;
select enb.enrt_bnft_id,
enb.val bnft_amt
from ben_enrt_bnft enb
where enb.ordr_num = c_ordr_num
--enb.val = c_bnft_amt
and enb.entr_val_at_enrt_flag = 'N'
and enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id ;
select enb.enrt_bnft_id,
enb.val bnft_amt
from ben_enrt_bnft enb
where --enb.ordr_num = c_ordr_num
enb.val = c_bnft_amt
and enb.entr_val_at_enrt_flag = 'N'
and enb.mx_wo_ctfn_flag = 'Y'
and enb.ordr_num = -1
and enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id ;
select lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id;
select epe.ELIG_PER_ELCTBL_CHC_ID,
epe.pgm_id,
epe.pl_id,
epe.oipl_id,
pen.pgm_id pen_pgm_id,
pen.pl_id pen_pl_id,
pen.oipl_id pen_oipl_id,
pen.enrt_cvg_thru_dt,
pen.object_version_number
From ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen
Where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id
and pen.business_group_id(+)=p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date(+) and pen.effective_end_date(+)
;
select ecr.enrt_rt_id
,ecr.dflt_val
,ecr.ANN_DFLT_VAL
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
-- and ecr.prtt_rt_val_id is null -- Bug 5415757 - This clause prevented carry forward of rates for interim PEN
union
select ecr.enrt_rt_id
,ecr.dflt_val
,ecr.ANN_DFLT_VAL
from ben_enrt_rt ecr
,ben_enrt_bnft enb
where enb.enrt_bnft_id = ecr.enrt_bnft_id
and ecr.business_group_id = p_business_group_id
and enb.business_group_id = p_business_group_id
and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and ecr.entr_val_at_enrt_flag = 'Y'
-- and ecr.prtt_rt_val_id is null -- Bug 5415757 - This clause prevented carry forward of rates for interim PEN
;
select enrt_bnft_id, val
from ben_enrt_bnft
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and business_group_id=p_business_group_id
-- and dflt_flag = 'Y'
and (enrt_bnft_id=p_enrt_bnft_id
or (p_enrt_bnft_id is null and
dflt_flag='Y'))
;
,p_datetrack_mode => hr_api.g_update
,p_suspend_flag => l_suspend_flag
,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
,P_PRTT_RT_VAL_ID1 => l_dump_num
,P_PRTT_RT_VAL_ID2 => l_dump_num
,P_PRTT_RT_VAL_ID3 => l_dump_num
,P_PRTT_RT_VAL_ID4 => l_dump_num
,P_PRTT_RT_VAL_ID5 => l_dump_num
,P_PRTT_RT_VAL_ID6 => l_dump_num
,P_PRTT_RT_VAL_ID7 => l_dump_num
,P_PRTT_RT_VAL_ID8 => l_dump_num
,P_PRTT_RT_VAL_ID9 => l_dump_num
,P_PRTT_RT_VAL_ID10 => l_dump_num
,P_OBJECT_VERSION_NUMBER => l_epe.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,P_DPNT_ACTN_WARNING => l_dump_boolean
,P_BNF_ACTN_WARNING => l_dump_boolean
,P_CTFN_ACTN_WARNING => l_dump_boolean
);
Procedure update_sspndd_flag
(p_prtt_enrt_rslt_id in number,
p_effective_date in date,
p_business_group_id in number,
p_sspndd_flag in varchar2,
p_RPLCS_SSPNDD_RSLT_ID in number,
p_object_version_number in out nocopy number,
p_datetrack_mode in varchar2,
p_ENRT_PL_OPT_FLAG in varchar2 default 'N',
p_enrt_cvg_strt_dt in date default hr_api.g_date,
p_enrt_cvg_thru_dt in date default hr_api.g_date,
p_pgm_id in number default NULL,
p_pl_id in number default NULL,
p_person_id in number
) is
Cursor csr_pen is
Select rplcs_sspndd_rslt_id
,prtt_enrt_rslt_id
,effective_start_date
,effective_end_date
,enrt_cvg_strt_dt
,enrt_cvg_thru_dt
,object_version_number
From ben_prtt_enrt_rslt_f pen
Where pen.business_group_id = p_business_group_id
And pen.person_id = p_person_id
And nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
And pen.pl_id = p_pl_id
And p_effective_date between
pen.effective_start_date and nvl(pen.effective_end_date,hr_api.g_eot)
And pen.sspndd_flag = 'Y'
and pen.prtt_enrt_rslt_stat_cd is null
And pen.oipl_id is not NULL
;
l_proc varchar2(80) := g_package || '.update_sspndd_flag';
l_last_place := 'Calling update_prtt_enrt_result';
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => p_business_group_id,
p_sspndd_flag => p_sspndd_flag,
p_RPLCS_SSPNDD_RSLT_ID => p_rplcs_sspndd_rslt_id,
p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt,
p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt,
p_object_version_number => p_object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => l_datetrack_mode,
p_multi_row_validate => FALSE,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => sysdate);
l_last_place := 'Calling update_prtt_enrt_rslt options...';
l_last_place := 'Calling update_prtt_enrt_rslt case 1.2';
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => l_rec.prtt_enrt_rslt_id,
p_effective_start_date => l_rec.effective_start_date,
p_effective_end_date => l_rec.effective_end_date,
p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt,
p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt,
p_business_group_id => p_business_group_id,
p_sspndd_flag => p_sspndd_flag,
p_RPLCS_SSPNDD_RSLT_ID => l_rec.rplcs_sspndd_rslt_id,
p_object_version_number => l_rec.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => l_datetrack_mode,
p_multi_row_validate => FALSE,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => sysdate);
End update_sspndd_flag;
select pen.rplcs_sspndd_rslt_id
,pen.prtt_enrt_rslt_id
,pen.person_id
,pen.pgm_id
,pen.sspndd_flag
,pen.enrt_mthd_cd
,pen.enrt_cvg_strt_dt
,pen.effective_start_date
,pen.effective_end_date
,epe.prtt_enrt_rslt_id chc_prtt_enrt_rslt_id
,epe.elig_per_elctbl_chc_id
,pen.pl_id
,pen.oipl_id
,pen.pl_typ_id
,pen.ler_id
,pen.per_in_ler_id
,pen.oipl_ordr_num
,pen.pl_ordr_num
,pen.bnft_amt
,pen.plip_ordr_num /*ENH*/
,pen.bnft_ordr_num /*ENH*/
,epe.dpnt_dsgn_cd
From ben_prtt_enrt_rslt_f pen
,ben_elig_per_elctbl_chc epe
,ben_per_in_ler pil
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.business_group_id= p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.business_group_id = epe.business_group_id (+)
and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
and pen.per_in_ler_id = epe.per_in_ler_id (+)
and pil.per_in_ler_id=pen.per_in_ler_id --Bug#5491212
and pil.business_group_id=pen.business_group_id --Bug#5491212
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select enb.enrt_bnft_id
from ben_enrt_bnft enb
where enb.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
and enb.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
and enb.business_group_id=p_business_group_id
union -- To get this when the enb is not update with the pen_id
-- This happens in the flex enrollment if the certifications is called
-- from flex routine /*ENH*/
select enb.enrt_bnft_id
from ben_enrt_bnft enb
where
enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and enb.ordr_num = p_bnft_ordr_num ;
select pen.object_version_number
From ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.business_group_id= p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
;
select *
From ben_elig_per_elctbl_chc
Where elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
;
select prv.prtt_rt_val_id
,prv.object_version_number
,prv.rt_strt_dt
,prv.rt_end_dt
,prv.rt_val
,prv.acty_base_rt_id
,prv.acty_ref_perd_cd
,abr.input_value_id
,abr.element_type_id
,prv.element_entry_value_id
,pev.effective_end_date
,pee.element_link_id
from ben_prtt_rt_val prv,
ben_acty_base_rt_f abr,
pay_element_entry_values_f pev,
pay_element_entries_f pee
where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and prv.rt_end_dt=hr_api.g_eot
and prv.business_group_id = p_business_group_id
and prv.prtt_rt_val_stat_cd is null
and abr.acty_base_rt_id=prv.acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date
and pev.element_entry_value_id = prv.element_entry_value_id
and prv.rt_strt_dt between
pev.effective_start_date and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and prv.rt_strt_dt between
pee.effective_start_date and pee.effective_end_date
;
select 'x' from ben_pl_f pl
where pl.pl_id = p_pl_id
and pl.SUBJ_TO_IMPTD_INCM_TYP_CD is not null
and p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select
pea.prtt_enrt_actn_id
,pea.actn_typ_id
,pea.rqd_flag
,pea.business_group_id
,pea.object_version_number pea_object_version_number
,pen.object_version_number pen_object_version_number
--START OHSU
,pea.effective_start_date pea_effective_date
--END OHSU
from ben_prtt_enrt_actn_f pea,
ben_prtt_enrt_rslt_f pen
where
pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
--START OHSU
and p_effective_date between pen.effective_start_date and
pen.effective_end_date
--END OHSU
and pen.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
and pea.rqd_flag = 'Y'
and pen.prtt_enrt_rslt_stat_cd is null;
select pen.RPLCS_SSPNDD_RSLT_ID
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.sspndd_flag = 'Y'
and pen.per_in_ler_id = p_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and not exists
(select null
from ben_prtt_enrt_rslt_f pen3
where pen3.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
and pen3.prtt_enrt_rslt_stat_cd is null
and pen3.effective_start_date < pen.effective_start_date
and pen3.per_in_ler_id <> pen.per_in_ler_id)
and exists (select null from ben_prtt_enrt_rslt_f pen2
where pen2.prtt_enrt_rslt_id = pen.RPLCS_SSPNDD_RSLT_ID
and pen2.prtt_enrt_rslt_stat_cd is null
and pen2.per_in_ler_id = p_per_in_ler_id
and pen2.enrt_cvg_thru_dt <> hr_api.g_eot
and pen2.effective_end_date = hr_api.g_eot);
select pen.effective_start_date,
pen.effective_end_date,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = (select pen2.effective_start_date - 1
from ben_prtt_enrt_rslt_f pen2
where pen2.enrt_cvg_thru_dt <> hr_api.g_eot
and pen2.effective_end_date = hr_api.g_eot
and pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen2.prtt_enrt_rslt_stat_cd is null);
select prv.rowid,
prv.prtt_rt_val_id,
prv.object_version_number,
prv.acty_base_rt_id,
prv.rt_strt_dt,
prv.rt_end_dt,
prv.rt_val,
prv.ann_rt_val,
prv.acty_ref_perd_cd
from ben_prtt_rt_val prv
where prv.prtt_rt_val_stat_cd is null
and prv.prtt_enrt_rslt_id = p_pen_id
and prv.per_in_ler_id = cp_per_in_ler_id
and prv.rt_strt_dt = -- for Unrestricted
(select max(prv1.rt_strt_dt)
from ben_prtt_rt_val prv1
where prv1.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and prv1.per_in_ler_id = prv.per_in_ler_id
and prv1.prtt_rt_val_stat_cd is null
and prv1.acty_base_rt_id = prv.acty_base_rt_id);
select object_version_number
from ben_prtt_rt_val
where rowid = p_rowid;
select prv.prtt_rt_val_id,
prv.object_version_number,
prv.rt_end_dt,
prv.rt_strt_dt,
prv.per_in_ler_id,
prv.prtt_enrt_rslt_id,
pil.person_id
from ben_prtt_rt_val prv,
ben_per_in_ler pil
where prv.per_in_ler_id = p_per_in_ler_id
and prv.per_in_ler_id = pil.per_in_ler_id
and prv.prtt_rt_val_stat_cd is null
and prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.rt_end_dt <> hr_api.g_eot
and prv.rt_strt_dt = (select max(rt_strt_dt)
from ben_prtt_rt_val
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and rt_end_dt <> hr_api.g_eot
and prtt_rt_val_stat_cd is null)
;
l_last_place := 'Calling update_sspndd_flag to update sspndd flag';
update_sspndd_flag
(p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
,p_sspndd_flag => 'Y'
,p_RPLCS_SSPNDD_RSLT_ID => l_rplcs_sspndd_rslt_id
,p_object_version_number => p_object_version_number
,p_datetrack_mode => l_datetrack_mode
,p_person_id => l_pen.person_id
);
,p_dt_delete_mode => null
,p_acty_base_rt_id => l_prv_sspndd.acty_base_rt_id
,p_amt => l_prv_sspndd.rt_val
);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => false
,p_business_group_id => p_business_group_id
,p_prtt_rt_val_id => l_prv_sspndd.prtt_rt_val_id
,p_element_entry_value_id => null
,p_object_version_number => l_prv_sspndd.object_version_number
,p_effective_date => l_prv_sspndd.rt_strt_dt
);
ben_prtt_enrt_result_api.delete_prtt_enrt_result
(p_validate => false,
p_prtt_enrt_rslt_id => l_rplcs_sspndd_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_enrt_rslt.object_version_number,
p_effective_date => l_enrt_rslt.effective_end_date,
p_datetrack_mode => hr_api.g_future_change,
p_multi_row_validate => FALSE);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_validate => FALSE
,p_prtt_rt_val_id => l_prv.prtt_rt_val_id
,p_object_version_number => l_prv.object_version_number
,p_rt_end_dt => hr_api.g_eot
,p_prtt_rt_val_stat_cd => null
,p_ended_per_in_ler_id => null
,p_person_id => l_prv.person_id
,p_business_group_id => p_business_group_id
,p_effective_date => p_effective_date);
hr_utility.set_location('Before Entering ben_prtt_enrt_actn_api.update_prtt_enrt_actn ' ,10);
ben_prtt_enrt_actn_api.update_prtt_enrt_actn
( p_prtt_enrt_actn_id => l_pea.prtt_enrt_actn_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_rslt_object_version_number => l_pea.pen_object_version_number
,p_actn_typ_id => l_pea.actn_typ_id
,p_rqd_flag => 'N'
--START OHSU
-- ,p_effective_date => p_effective_date
,p_effective_date => l_pea.pea_effective_date
--END OHSU
,p_post_rslt_flag => p_post_rslt_flag
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_pea_effective_start_date
,p_effective_end_date => l_pea_effective_end_date
,p_object_version_number => l_pea.pea_object_version_number
,p_datetrack_mode => hr_api.g_correction
);
hr_utility.set_location('After ben_prtt_enrt_actn_api.update_prtt_enrt_actn ',20);
l_update_dates boolean := false;
Select pen.rplcs_sspndd_rslt_id
,pen.prtt_enrt_rslt_id
,pen.per_in_ler_id
,pen.enrt_cvg_strt_dt
,pen.enrt_cvg_thru_dt
,pen.person_id
,pen.pgm_id
,pen.sspndd_flag
,pen.effective_start_date
,pen.effective_end_date
,pen.enrt_mthd_cd
,pen.object_version_number
,pen.pl_id
,pen.oipl_id
,pen.ptip_id
,pln.ENRT_PL_OPT_FLAG
,pen.business_group_id
,'USEEFD' calc_cvg_strt_dt_cd
,'USE1BSEFD' calc_cvg_end_dt_cd
From ben_prtt_enrt_rslt_f pen
,ben_pl_f pln
Where pen.prtt_enrt_rslt_id = c_rslt_id
And pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
And p_effective_date between
pen.effective_start_date and
pen.effective_end_date
And pen.pl_id = pln.pl_id
And pen.business_group_id = pln.business_group_id
And p_effective_date between
pln.effective_start_date and
pln.effective_end_date
;
select 'x' from ben_pl_f pl
where pl.pl_id = p_pl_id
and pl.SUBJ_TO_IMPTD_INCM_TYP_CD is not null
and p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select epe.elig_per_elctbl_chc_id,
pel.lee_rsn_id,
pel.enrt_perd_id,
--START Bug 2958032
epe.prtt_enrt_rslt_id,
epe.object_version_number
--END Bug 2958032
from ben_prtt_enrt_rslt_f pen,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where pen.prtt_enrt_rslt_id=c_prtt_enrt_rslt_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.person_id=pil.person_id
and epe.pl_id=pen.pl_id
and nvl(epe.oipl_id,-1)=nvl(pen.oipl_id,-1)
and nvl(epe.pgm_id,-1)=nvl(pen.pgm_id,-1)
and epe.per_in_ler_id = l_per_in_ler_id
and epe.business_group_id=p_business_group_id
-- and epe.elctbl_flag = 'Y' -- Bug 2958032 CF BUG 4064635
and pel.pil_elctbl_chc_popl_id=epe.pil_elctbl_chc_popl_id
and pel.business_group_id=p_business_group_id
and pil.per_in_ler_id=epe.per_in_ler_id
and pil.business_group_id=epe.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and pen.prtt_enrt_rslt_stat_cd is null
;
select ecr.*
from ben_enrt_rt ecr
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and acty_base_rt_id = p_acty_base_rt_id;
select prv.*
,abr.input_value_id
,abr.element_type_id
from ben_prtt_rt_val prv,
ben_acty_base_rt_f abr
where prv.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and prv.per_in_ler_id = l_per_in_ler_id
and prv.rt_strt_dt <= prv.rt_end_dt
-- and prv.rt_end_dt=hr_api.g_eot
and prv.business_group_id = p_business_group_id
and prv.prtt_rt_val_stat_cd is null
and abr.acty_base_rt_id=prv.acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date
;
select ppe.prtt_prem_id,
ppe.object_version_number
from ben_prtt_prem_f ppe,
ben_actl_prem_f apr
where ppe.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and ppe.per_in_ler_id = l_per_in_ler_id
and ppe.business_group_id = p_business_group_id
and ppe.actl_prem_id = apr.actl_prem_id
and apr.business_group_id = p_business_group_id
and l_ppe_effective_date between -- p_effective_date between ----3278908
apr.effective_start_date and apr.effective_end_date
--Added for 3278908
and l_ppe_effective_date between
ppe.effective_start_date and ppe.effective_end_date
;
select pev.effective_end_date
from pay_element_entry_values_f pev,
pay_element_entries_f pee
where pev.element_entry_value_id = p_element_entry_value_id
and p_rt_strt_dt between
pev.effective_start_date and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and p_rt_strt_dt between
pee.effective_start_date and pee.effective_end_date
;
select ecd.elig_cvrd_dpnt_id
,ecd.effective_start_date
,ecd.effective_end_date
,ecd.cvg_strt_dt
,ecd.cvg_thru_dt
,ecd.object_version_number
From ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
Where ecd.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and ecd.cvg_strt_dt is not null
and ecd.cvg_thru_dt = hr_api.g_eot
and ecd.business_group_id = p_business_group_id
and p_effective_date between
ecd.effective_start_date and ecd.effective_end_date
and pil.per_in_ler_id=ecd.per_in_ler_id
and pil.business_group_id=ecd.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
select pil.lf_evt_ocrd_dt,
pil.ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id=l_per_in_ler_id and
pil.business_group_id=p_business_group_id;
select nvl(lbr.unsspnd_enrt_cd,
nvl(pl.unsspnd_enrt_cd, plip.unsspnd_enrt_cd))
from ben_ler_bnft_rstrn_f lbr,
ben_pl_f pl,
ben_plip_f plip
where
pl.pl_id=l_pen.pl_id
and plip.pl_id(+)=pl.pl_id
and nvl(plip.pgm_id,l_pen.pgm_id)=l_pen.pgm_id
and pl.business_group_id = p_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between pl.effective_start_date
and pl.effective_end_date
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between plip.effective_start_date(+)
and plip.effective_end_date(+)
-- get ler_bnft_rstrn_f if exists
and lbr.pl_id(+)=pl.pl_id
and lbr.ler_id(+)=l_ler_id
and lbr.business_group_id(+) = p_business_group_id
and nvl(l_lf_evt_ocrd_dt,p_effective_date)
between lbr.effective_start_date(+)
and lbr.effective_end_date(+)
;
select decr_bnft_prvdr_pool_id
from ben_enrt_rt
where prtt_rt_val_id = p_prtt_rt_val_id;
select enb.enrt_bnft_id,
enb.object_version_number
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and enb.prtt_enrt_rslt_id is not null
and enb.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id ;
select enb.enrt_bnft_id,
enb.object_version_number
from ben_prtt_enrt_rslt_f pen,
ben_enrt_bnft enb
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.bnft_ordr_num = enb.ordr_num
and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and nvl(enb.prtt_enrt_rslt_id,p_prtt_enrt_rslt_id) <> p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null;
select null
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.sspndd_flag = 'N'
and pen.per_in_ler_id <> l_per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null;
SELECT 'x'
FROM ben_pil_elctbl_chc_popl popl, ben_prtt_enrt_rslt_f pen
WHERE popl.per_in_ler_id = l_per_in_ler_id
AND popl.elcns_made_dt IS NULL
AND popl.dflt_asnd_dt IS NULL
AND popl.pgm_id = pen.pgm_id
AND pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND pen.per_in_ler_id = popl.per_in_ler_id
AND prtt_enrt_rslt_stat_cd IS NULL;
l_last_place := 'Calling Delete Enrollment';
ben_prtt_enrt_result_api.delete_enrollment
(P_VALIDATE => FALSE
,P_PRTT_ENRT_RSLT_ID => l_interim.prtt_enrt_rslt_id
,p_per_in_ler_id => p_per_in_ler_id
,P_BUSINESS_GROUP_ID => p_business_group_id
,P_EFFECTIVE_START_DATE => l_interim.effective_start_date
,P_EFFECTIVE_END_DATE => l_interim.effective_end_date
,P_OBJECT_VERSION_NUMBER => l_interim.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
,P_DATETRACK_MODE => hr_api.g_delete
,P_MULTI_ROW_VALIDATE => FALSE
,p_source => 'bensuenr'
);
l_last_place := 'Calling update_prtt_rt_val';
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,p_person_id => l_interim.person_id
,P_RT_END_DT => l_cvg_thru_dt -- 999 l_rec_rt_end_dt
,p_business_group_id => p_business_group_id
,p_per_in_ler_id => l_per_in_ler_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
l_datetrack_mode := hr_api.g_update;
l_last_place := 'Calling update_elig_cvrd_dpnt';
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
(p_validate => FALSE
,p_business_group_id => p_business_group_id
,p_elig_cvrd_dpnt_id => l_dpnt.elig_cvrd_dpnt_id
,p_effective_start_date => l_dpnt.effective_start_date
,p_effective_end_date => l_dpnt.effective_end_date
,p_cvg_thru_dt => l_cvg_thru_dt -- 999 l_dpnt_cvg_end_dt
,p_object_version_number => l_dpnt.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_actn => FALSE);
l_datetrack_mode := hr_api.g_update;
Update_sspndd_flag
(p_prtt_enrt_rslt_id => l_interim.prtt_enrt_rslt_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_sspndd_flag => 'N'
,p_RPLCS_SSPNDD_RSLT_ID => NULL
,p_enrt_cvg_thru_dt => l_cvg_thru_dt
,p_object_version_number => l_interim.object_version_number
,p_datetrack_mode => l_datetrack_mode
,p_ENRT_PL_OPT_FLAG => 'N'
,p_pgm_id => l_interim.pgm_id
,p_pl_id => l_interim.pl_id
,p_person_id => l_interim.person_id
);
,p_datetrack_mode => hr_api.g_delete
);
l_update_dates := true;
l_last_place := 'Calling update_prtt_rt_val';
l_update_dates then
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_prv.prtt_rt_val_id
,p_person_id => l_pen.person_id
,P_RT_STRT_DT => l_rec_rt_strt_dt
,p_business_group_id => p_business_group_id
,p_per_in_ler_id => l_per_in_ler_id
,P_OBJECT_VERSION_NUMBER => l_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
l_last_place := 'Calling update_sspndd_flag';
if l_update_dates then
--
-- As the below cursor Csr_prtt_enrt_rslt replaces the computed
-- cvrg_strt_dt save it to reset it.
--
l_enrt_cvg_strt_dt := l_pen.enrt_cvg_strt_dt;
l_datetrack_mode := hr_api.g_update;
if l_update_dates and not l_previous_no_sspn then
--
-- As the above cursor Csr_prtt_enrt_rslt replaces the computed
-- enrt_cvrg_strt_dt reset it with computed value.
--
l_pen.enrt_cvg_strt_dt := l_enrt_cvg_strt_dt;
Update_sspndd_flag
(p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_sspndd_flag => 'N'
,p_RPLCS_SSPNDD_RSLT_ID => NULL
,p_object_version_number => l_pen.object_version_number
,p_datetrack_mode => l_datetrack_mode
,p_ENRT_PL_OPT_FLAG => l_pen.ENRT_PL_OPT_FLAG
,p_enrt_cvg_strt_dt => l_pen.enrt_cvg_strt_dt
,p_pgm_id => l_pen.pgm_id
,p_pl_id => l_pen.pl_id
,p_person_id => l_pen.person_id
);
ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
(p_validate => FALSE
,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_object_version_number => l_epe_object_version_number
,p_effective_date => p_effective_date
,p_request_id => fnd_global.conc_request_id
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
);
ben_enrt_bnft_api.update_enrt_bnft
(p_enrt_bnft_id => l_enb.enrt_bnft_id
,p_effective_date => p_effective_date
,p_object_version_number => l_enb.object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => NULL
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_request_id => fnd_global.conc_request_id
,p_program_update_date => sysdate
);
if l_dpnt.cvg_strt_dt <> l_pen.enrt_cvg_strt_dt and l_update_dates then
--
If (p_effective_date = l_dpnt.effective_start_date) then
l_datetrack_mode := hr_api.g_correction;
l_datetrack_mode := hr_api.g_update;
l_last_place := 'Calling update_elig_cvrd_dpnt';
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
(p_validate => FALSE
,p_business_group_id => p_business_group_id
,p_elig_cvrd_dpnt_id => l_dpnt.elig_cvrd_dpnt_id
,p_effective_start_date => l_dpnt.effective_start_date
,p_effective_end_date => l_dpnt.effective_end_date
,p_cvg_strt_dt => l_dpnt_cvg_strt_dt -- l_pen.enrt_cvg_strt_dt -- 999 l_dpnt_cvg_strt_dt
,p_object_version_number => l_dpnt.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_actn => FALSE
);