eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_PRTT_ENRT_RESULT_API dependencies on BEN_PRTT_ENRT_RSLT_F

Line 1479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;

1475: --
1476: -- Declare cursors and local variables
1477: --
1478: l_chg_evt_cd hr_lookups.lookup_code%type;
1479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
1480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
1481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
1482: l_effective_date date;
1483: l_proc varchar2(72); -- := g_package||'create_enrollment';

Line 1480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;

1476: -- Declare cursors and local variables
1477: --
1478: l_chg_evt_cd hr_lookups.lookup_code%type;
1479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
1480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
1481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
1482: l_effective_date date;
1483: l_proc varchar2(72); -- := g_package||'create_enrollment';
1484: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;

Line 1481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;

1477: --
1478: l_chg_evt_cd hr_lookups.lookup_code%type;
1479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
1480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
1481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
1482: l_effective_date date;
1483: l_proc varchar2(72); -- := g_package||'create_enrollment';
1484: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
1485: l_return_to_exist_cvg_flag varchar2(30) := 'N';

Line 1484: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;

1480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
1481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
1482: l_effective_date date;
1483: l_proc varchar2(72); -- := g_package||'create_enrollment';
1484: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
1485: l_return_to_exist_cvg_flag varchar2(30) := 'N';
1486: l_datetrack_mode varchar2(30);
1487: l_step integer;
1488: l_crntly_enrd_flag varchar2(30) := 'N';

Line 1518: l_global_pen_rec ben_prtt_enrt_rslt_f%rowtype;

1514: l_rt_end_dt_rl number;
1515: /* End of Changes for WWBUG: 1646442 : added declarations */
1516: --
1517: l_global_epe_rec ben_global_enrt.g_global_epe_rec_type;
1518: l_global_pen_rec ben_prtt_enrt_rslt_f%rowtype;
1519: l_pl_rec ben_cobj_cache.g_pl_inst_row;
1520: l_pgm_rec ben_cobj_cache.g_pgm_inst_row;
1521: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
1522: --

Line 1528: from ben_prtt_enrt_rslt_f pen

1524: select pen.prtt_enrt_rslt_id,
1525: pen.pl_id,
1526: pen.oipl_id,
1527: pen.pl_typ_id
1528: from ben_prtt_enrt_rslt_f pen
1529: where pen.person_id=p_person_id and
1530: pen.business_group_id =p_business_group_id and
1531: pen.prtt_enrt_rslt_stat_cd is null and
1532: pen.sspndd_flag='N' and

Line 1553: from ben_prtt_enrt_rslt_f

1549: ,pl_typ_id
1550: ,pl_id
1551: ,oipl_id
1552: ,object_version_number
1553: from ben_prtt_enrt_rslt_f
1554: where prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1555: and p_effective_date between
1556: effective_start_date and effective_end_date;
1557: --

Line 1623: ,ben_prtt_enrt_rslt_f pen

1619: --
1620: cursor c_check_cvrd_in_plan is
1621: select null
1622: from ben_elig_cvrd_dpnt_f pdp
1623: ,ben_prtt_enrt_rslt_f pen
1624: ,ben_pgm_f pgm
1625: where pdp.dpnt_person_id = p_person_id
1626: and pdp.business_group_id = p_business_group_id
1627: and p_effective_date

Line 2479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;

2475: ) is
2476: --
2477: -- Declare cursors and local variables
2478: --
2479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
2480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
2481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2482: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2483: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;

Line 2480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;

2476: --
2477: -- Declare cursors and local variables
2478: --
2479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
2480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
2481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2482: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2483: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2484: begin

Line 2481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;

2477: -- Declare cursors and local variables
2478: --
2479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
2480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
2481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2482: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2483: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2484: begin
2485: g_debug := hr_utility.debug_enabled;

Line 2483: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;

2479: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
2480: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
2481: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
2482: l_proc varchar2(72) ; -- := g_package||'create_PRTT_ENRT_RESULT';
2483: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
2484: begin
2485: g_debug := hr_utility.debug_enabled;
2486: if g_debug then
2487: l_proc := g_package||'create_PRTT_ENRT_RESULT';

Line 2905: l_global_pen_rec ben_prtt_enrt_rslt_f%rowtype;

2901: --
2902: l_pl_rec ben_cobj_cache.g_pl_inst_row;
2903: l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
2904: --
2905: l_global_pen_rec ben_prtt_enrt_rslt_f%rowtype;
2906:
2907:
2908: cursor c_interim_enrt is
2909: select pen.rplcs_sspndd_rslt_id

Line 2910: from ben_prtt_enrt_rslt_f pen

2906:
2907:
2908: cursor c_interim_enrt is
2909: select pen.rplcs_sspndd_rslt_id
2910: from ben_prtt_enrt_rslt_f pen
2911: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
2912: pen.business_group_id = p_business_group_id and
2913: p_effective_date between
2914: pen.effective_start_date and pen.effective_end_date;

Line 3126: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;

3122: l_rt_end_dt_rl number;
3123: /* End of Changes for WWBUG: 1646442 */
3124: --
3125: l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
3126: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
3127: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
3128: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
3129: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
3130: l_enrt_pl_opt_flag ben_pl_f.enrt_pl_opt_flag%TYPE;

Line 3127: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;

3123: /* End of Changes for WWBUG: 1646442 */
3124: --
3125: l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
3126: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
3127: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
3128: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
3129: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
3130: l_enrt_pl_opt_flag ben_pl_f.enrt_pl_opt_flag%TYPE;
3131: l_prvdd_val ben_bnft_prvdd_ldgr_f.prvdd_Val%type ;

Line 3128: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;

3124: --
3125: l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
3126: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
3127: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
3128: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
3129: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
3130: l_enrt_pl_opt_flag ben_pl_f.enrt_pl_opt_flag%TYPE;
3131: l_prvdd_val ben_bnft_prvdd_ldgr_f.prvdd_Val%type ;
3132: l_chg boolean := FALSE;

Line 3129: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;

3125: l_proc varchar2(72); -- := g_package||'update_ENROLLMENT';
3126: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
3127: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
3128: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
3129: l_prev_bnft_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
3130: l_enrt_pl_opt_flag ben_pl_f.enrt_pl_opt_flag%TYPE;
3131: l_prvdd_val ben_bnft_prvdd_ldgr_f.prvdd_Val%type ;
3132: l_chg boolean := FALSE;
3133: l_step number(9);

Line 3503: p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',

3499: -- Check dt mode is valid
3500: --
3501: dt_api.find_dt_upd_modes
3502: (p_effective_date => p_effective_date,
3503: p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
3504: p_base_key_column => 'prtt_enrt_rslt_id',
3505: p_base_key_value => p_prtt_enrt_rslt_id,
3506: p_correction => l_correction,
3507: p_update => l_update,

Line 4287: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;

4283: --
4284: -- Declare cursors and local variables
4285: --
4286: l_proc varchar2(72) ; -- := g_package||'update_PRTT_ENRT_RESULT';
4287: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4288: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4289: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4290:
4291: --

Line 4288: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;

4284: -- Declare cursors and local variables
4285: --
4286: l_proc varchar2(72) ; -- := g_package||'update_PRTT_ENRT_RESULT';
4287: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4288: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4289: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4290:
4291: --
4292: cursor c_old_rslt is

Line 4289: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;

4285: --
4286: l_proc varchar2(72) ; -- := g_package||'update_PRTT_ENRT_RESULT';
4287: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4288: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4289: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4290:
4291: --
4292: cursor c_old_rslt is
4293: select *

Line 4294: from ben_prtt_enrt_rslt_f

4290:
4291: --
4292: cursor c_old_rslt is
4293: select *
4294: from ben_prtt_enrt_rslt_f
4295: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4296: and prtt_enrt_rslt_stat_cd is null
4297: and p_effective_date between effective_start_date and effective_end_date ;
4298: l_old_rslt c_old_rslt%rowtype ;

Line 4318: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'

4314: where bkup_tbl_id = c_pen_id
4315: and ((per_in_ler_id = c_pil_id
4316: and per_in_ler_ended_id = c_pil_end_id)
4317: or per_in_ler_ended_id = c_pil_id) -- 7197868
4318: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
4319: union
4320: select 'x'
4321: from BEN_LE_CLSN_N_RSTR
4322: where bkup_tbl_id = c_pen_id

Line 4324: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'

4320: select 'x'
4321: from BEN_LE_CLSN_N_RSTR
4322: where bkup_tbl_id = c_pen_id
4323: and per_in_ler_id = c_pil_id
4324: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'
4325: and c_eff_strt_dt = EFFECTIVE_START_DATE
4326: and c_eff_end_dt = EFFECTIVE_END_DATE
4327: and c_cvg_strt_dt = ENRT_CVG_STRT_DT
4328: and c_cvg_thru_dt = ENRT_CVG_THRU_DT

Line 4497: 'BEN_PRTT_ENRT_RSLT_F_CORR',

4493: PLIP_ORDR_NUM,
4494: PTIP_ORDR_NUM,
4495: OIPL_ORDR_NUM)
4496: values (
4497: 'BEN_PRTT_ENRT_RSLT_F_CORR',
4498: l_old_rslt.COMP_LVL_CD,
4499: l_old_rslt.PEN_ATTRIBUTE16,
4500: l_old_rslt.PEN_ATTRIBUTE17,
4501: l_old_rslt.PEN_ATTRIBUTE18,

Line 4916: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;

4912: --
4913: -- Declare cursors and local variables
4914: --
4915: l_proc varchar2(72) ; -- := g_package||'delete_PRTT_ENRT_RESULT';
4916: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4917: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4918: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4919: --
4920: begin

Line 4917: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;

4913: -- Declare cursors and local variables
4914: --
4915: l_proc varchar2(72) ; -- := g_package||'delete_PRTT_ENRT_RESULT';
4916: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4917: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4918: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4919: --
4920: begin
4921: --

Line 4918: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;

4914: --
4915: l_proc varchar2(72) ; -- := g_package||'delete_PRTT_ENRT_RESULT';
4916: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
4917: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
4918: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
4919: --
4920: begin
4921: --
4922: g_debug := hr_utility.debug_enabled;

Line 5413: From ben_prtt_enrt_rslt_f pen

5409: ,p_pil_id out nocopy number
5410: ) is
5411: Cursor c1 is
5412: Select min(effective_start_date),pen.per_in_ler_id
5413: From ben_prtt_enrt_rslt_f pen
5414: Where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5415: And pen.business_group_id = p_business_group_id
5416: and pen.prtt_enrt_rslt_stat_cd is null
5417: Group by pen.per_in_ler_id

Line 5473: from ben_prtt_enrt_rslt_f pen

5469: ,epe.elig_per_elctbl_chc_id
5470: ,epe.per_in_ler_id
5471: ,epe.object_version_number epe_ovn
5472: ,enb.enrt_bnft_id
5473: from ben_prtt_enrt_rslt_f pen
5474: ,ben_elig_per_elctbl_chc epe
5475: ,ben_per_in_ler pil
5476: ,ben_enrt_bnft enb
5477: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id

Line 5506: from ben_prtt_enrt_rslt_f pen

5502: ,to_number(null) elig_per_elctbl_chc_id
5503: ,to_number(null) per_in_ler_id
5504: ,to_number(null) epe_ovn
5505: ,to_number(null) enrt_bnft_id
5506: from ben_prtt_enrt_rslt_f pen
5507: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5508: and p_effective_date between pen.effective_start_date
5509: and pen.effective_end_date
5510: ;

Line 5618: from ben_prtt_enrt_rslt_f pen,

5614: cursor c_unrestricted_future (p_per_in_ler_id number,
5615: p_prtt_enrt_rslt_id number,
5616: p_effective_start_date date) is
5617: select null
5618: from ben_prtt_enrt_rslt_f pen,
5619: ben_per_in_ler pil,
5620: ben_ler_f ler
5621: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5622: and pen.effective_start_date > p_effective_start_date

Line 6050: from ben_prtt_enrt_rslt_f pen

6046: ,oipl.opt_id
6047: ,pl.name pl_name
6048: ,opt.name opt_name
6049: ,pl.imptd_incm_calc_cd
6050: from ben_prtt_enrt_rslt_f pen
6051: ,ben_elig_per_elctbl_chc epe
6052: ,ben_per_in_ler pil
6053: ,ben_enrt_bnft enb
6054: ,ben_oipl_f oipl

Line 6203: from ben_prtt_enrt_rslt_f pen

6199: -- overlapped coverage adj
6200: -- basu
6201: Cursor c_enrt_in_curr_le is
6202: select 'Y'
6203: from ben_prtt_enrt_rslt_f pen
6204: where pen.per_in_ler_id = p_per_in_ler_id
6205: and pen.effective_end_date = hr_api.g_eot
6206: and pen.enrt_cvg_thru_dt = hr_api.g_eot
6207: and pen.prtt_enrt_rslt_stat_cd is null;

Line 6216: from ben_prtt_enrt_rslt_f pen

6212: select pen.prtt_enrt_rslt_id
6213: ,pen.enrt_cvg_strt_dt
6214: ,pen.per_in_ler_id
6215: ,pen.object_version_number
6216: from ben_prtt_enrt_rslt_f pen
6217: ,ben_ptip_f ptip
6218: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id -- 245535
6219: and pen.per_in_ler_id <> p_per_in_ler_id -- basu
6220: and pen.enrt_cvg_thru_dt > p_cvg_end_dt -- '20-jan-2008'

Line 6378: from ben_prtt_enrt_rslt_f pen

6374: --level - otherwise if multiple plans enrolled in same plan type leads to bug
6375: cursor c_new_cvg_strt_dt(l_pl_typ_id number, l_ptip_id number, l_pl_id number,
6376: p_enrt_cvg_strt_dt date ) is
6377: select max(enrt_cvg_strt_dt)
6378: from ben_prtt_enrt_rslt_f pen
6379: where pen.per_in_ler_id = nvl(p_per_in_ler_id, -1) -- is this OK?
6380: and ((pen.pgm_id is null and pen.pl_typ_id is not null
6381: and pen.pl_typ_id = nvl(l_pl_typ_id, -1))
6382: or (pen.ptip_id is not null and pen.ptip_id = nvl(l_ptip_id, -1))

Line 6399: from ben_prtt_enrt_rslt_f pen

6395: ;
6396: --
6397: cursor c_intm_other_rslt(p_per_in_ler_id number) is
6398: select 'Y'
6399: from ben_prtt_enrt_rslt_f pen
6400: where pen.rplcs_sspndd_rslt_id = l_pen.rplcs_sspndd_rslt_id
6401: and pen.business_group_id = p_business_group_id
6402: and pen.sspndd_flag = 'Y'
6403: and pen.enrt_cvg_thru_dt = hr_api.g_eot

Line 6412: from ben_prtt_enrt_rslt_f pen, ben_le_clsn_n_rstr cls

6408: -- Bug 6165501 : Added union clause if its a Correction case, and interim has already
6409: -- been end-dated by the new pil, then no need to call delete_enrollment for the interim
6410: UNION
6411: select 'Y'
6412: from ben_prtt_enrt_rslt_f pen, ben_le_clsn_n_rstr cls
6413: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6414: and pen.prtt_enrt_rslt_stat_cd is null
6415: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
6416: and pen.per_in_ler_id = p_per_in_ler_id

Line 6418: and cls.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR'

6414: and pen.prtt_enrt_rslt_stat_cd is null
6415: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
6416: and pen.per_in_ler_id = p_per_in_ler_id
6417: and cls.bkup_tbl_id = pen.prtt_enrt_rslt_id
6418: and cls.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR'
6419: and cls.per_in_ler_ended_id = p_per_in_ler_id;
6420: -- End Bug 6165501
6421:
6422: --

Line 6428: from ben_prtt_enrt_rslt_f pen

6424: select pen.prtt_enrt_rslt_id,
6425: pen.object_version_number,
6426: pen.effective_start_date,
6427: pen.effective_end_date
6428: from ben_prtt_enrt_rslt_f pen
6429: where pen.prtt_enrt_rslt_id = l_pen.rplcs_sspndd_rslt_id
6430: and pen.business_group_id = p_business_group_id
6431: and pen.prtt_enrt_rslt_stat_cd is null
6432: and p_effective_date between

Line 6443: from ben_prtt_enrt_rslt_f pen

6439: p_per_in_ler_id number ,
6440: p_effective_date date
6441: ) is
6442: select 'x'
6443: from ben_prtt_enrt_rslt_f pen
6444: where pen.person_id = p_person_id
6445: and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
6446: and nvl(p_pl_id ,-1) = nvl(pen.pl_id,-1)
6447: and nvl(p_oipl_id,-1) = nvl(pen.oipl_id, -1)

Line 6464: from ben_prtt_enrt_rslt_f pen

6460: p_per_in_ler_id number ,
6461: p_effective_date date
6462: ) is
6463: select 'x'
6464: from ben_prtt_enrt_rslt_f pen
6465: where pen.person_id = p_person_id
6466: and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
6467: and p_pl_id = pen.pl_id
6468: and (p_per_in_ler_id <> pen.per_in_ler_id

Line 6483: from ben_prtt_enrt_rslt_f pen

6479: p_per_in_ler_id number ,
6480: p_effective_date date
6481: ) is
6482: select 'x'
6483: from ben_prtt_enrt_rslt_f pen
6484: where pen.person_id = p_person_id
6485: and p_pgm_id = pen.pgm_id
6486: and p_ptip_id = pen.ptip_id
6487: and (p_per_in_ler_id <> pen.per_in_ler_id

Line 6506: from ben_prtt_enrt_rslt_f pen

6502: l_per_in_ler c_per_in_ler%rowtype;
6503: --
6504: cursor c_pen_ovn (p_prtt_enrt_rslt_id number, p_effective_date date ) is
6505: select pen.object_version_number,pen.effective_start_date --4663971
6506: from ben_prtt_enrt_rslt_f pen
6507: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6508: and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
6509: l_pen_ovn c_pen_ovn%rowtype;
6510: --

Line 6559: from ben_prtt_enrt_rslt_f pen

6555: p_oipl_id number ,
6556: p_per_in_ler_id number
6557: ) is
6558: select prtt_enrt_rslt_id
6559: from ben_prtt_enrt_rslt_f pen
6560: where pen.person_id = p_person_id
6561: and nvl(p_pgm_id,-1) = nvl(pen.pgm_id,-1)
6562: and nvl(p_pl_id ,-1) = nvl(pen.pl_id,-1)
6563: and nvl(p_oipl_id,-1) = nvl(pen.oipl_id, -1)

Line 6575: ben_prtt_enrt_rslt_f pen

6571: -- Bug 2689915
6572: cursor c_ler is
6573: select typ_cd
6574: from ben_ler_f ler,
6575: ben_prtt_enrt_rslt_f pen
6576: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6577: and pen.ler_id = ler.ler_id ;
6578: --
6579: cursor c_old_corr_pen (c_pil_id number

Line 6594: from ben_prtt_enrt_rslt_f pen

6590:
6591: cursor c_pen_obj_no (c_pil_id number
6592: ,c_pen_id number ) is
6593: select object_version_number
6594: from ben_prtt_enrt_rslt_f pen
6595: where pen.prtt_enrt_rslt_id = c_pen_id
6596: and pen.per_in_ler_id = c_pil_id
6597: and p_effective_date between
6598: pen.effective_start_date and pen.effective_end_date ;

Line 6606: ben_prtt_enrt_rslt_f pen

6602: cursor c_crntly_enrd_flag(p_elig_pe_elctbl_chc_id number) is
6603: select epe.crntly_enrd_flag,ler.typ_cd
6604: from ben_elig_per_elctbl_chc epe,
6605: ben_ler_f ler,
6606: ben_prtt_enrt_rslt_f pen
6607: where epe.per_in_ler_id=p_per_in_ler_id
6608: and epe.elig_per_elctbl_chc_id=p_elig_pe_elctbl_chc_id
6609: and epe.prtt_enrt_rslt_id=pen.prtt_enrt_rslt_id
6610: and pen.per_in_ler_id=p_per_in_ler_id

Line 6621: from ben_prtt_enrt_rslt_f pen,

6617: --Added for Bug#5018328
6618:
6619: cursor c_crntly_enrd_flag_unres(p_elig_pe_elctbl_chc_id number) is
6620: select ler.typ_cd
6621: from ben_prtt_enrt_rslt_f pen,
6622: ben_elig_per_elctbl_chc epe,
6623: ben_ler_f ler
6624: where epe.elig_per_elctbl_chc_id=p_elig_pe_elctbl_chc_id
6625: and epe.per_in_ler_id=p_per_in_ler_id

Line 6640: from ben_prtt_enrt_rslt_f pen

6636: p_per_in_ler_id number,
6637: p_enrt_cvg_strt_dt date,
6638: p_effective_start_date date) is
6639: select 'Y'
6640: from ben_prtt_enrt_rslt_f pen
6641: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6642: and pen.prtt_enrt_rslt_stat_cd is null
6643: and pen.enrt_cvg_thru_dt = to_date('4712/12/31','rrrr/mm/dd')
6644: and pen.per_in_ler_id <> p_per_in_ler_id

Line 6747: l_other_pen_rec ben_prtt_enrt_rslt_f%rowtype;

6743: l_rqd_perd_enrt_nenrt_uom varchar2(30);
6744: l_rqd_perd_enrt_nenrt_val number;
6745: l_rqd_perd_enrt_nenrt_rl number;
6746: l_level varchar2(30);
6747: l_other_pen_rec ben_prtt_enrt_rslt_f%rowtype;
6748: l_intm_other_rslt varchar2(30) := 'N';
6749: l_new_enrollment varchar2(1) := 'N' ;
6750: l_object_version_number number ;
6751: --

Line 9017: and bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR';

9013: update ben_le_clsn_n_rstr
9014: set effective_start_date = p_effective_start_date
9015: where per_in_ler_id = l_corr_pil_id
9016: and bkup_tbl_id = p_prtt_enrt_rslt_id
9017: and bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR';
9018: --
9019: -- added till here for bug 6963660 */
9020: close c_pen_obj_no ;
9021: end if ;

Line 9768: from ben_prtt_enrt_rslt_f prt,

9764: and business_group_id = p_business_group_id;
9765: --
9766: cursor c_prtt_total_bnft_amt(p_acrs_ptip_cvg_id number) is
9767: select sum(prt.bnft_amt)
9768: from ben_prtt_enrt_rslt_f prt,
9769: ben_ptip_f ptp
9770: where prt.pgm_id = p_pgm_id
9771: and prt.person_id = p_person_id
9772: and prt.sspndd_flag = 'N' -- # 3626176

Line 9792: from ben_prtt_enrt_rslt_f prt,

9788: --here.
9789: --
9790: cursor c_prtt_total_bnft_amt_sspndd(p_acrs_ptip_cvg_id number) is
9791: select sum(prt.bnft_amt)
9792: from ben_prtt_enrt_rslt_f prt,
9793: ben_ptip_f ptp
9794: where prt.pgm_id = p_pgm_id
9795: and prt.person_id = p_person_id
9796: and ( prt.sspndd_flag = 'Y' or (

Line 9799: from ben_prtt_enrt_rslt_f prt1

9795: and prt.person_id = p_person_id
9796: and ( prt.sspndd_flag = 'Y' or (
9797: prt.sspndd_flag = 'N' and not exists (
9798: select 'x'
9799: from ben_prtt_enrt_rslt_f prt1
9800: where prt1.person_id = p_person_id
9801: and prt1.pgm_id = p_pgm_id
9802: and PRT1.rplcs_sspndd_rslt_id = prt.prtt_enrt_rslt_id
9803: and prt1.per_in_ler_id = prt.per_in_ler_id

Line 9996: From ben_prtt_enrt_rslt_f r,

9992: ,r.person_id
9993: ,oipl.opt_id
9994: ,r.ptip_id
9995: ,r.enrt_mthd_cd --Bug 2200139
9996: From ben_prtt_enrt_rslt_f r,
9997: ben_oipl_f oipl,
9998: ben_elig_per_elctbl_chc c
9999: Where c.per_in_ler_id = p_per_in_ler_id
10000: and r.prtt_enrt_rslt_id = c.prtt_enrt_rslt_id

Line 10038: From ben_prtt_enrt_rslt_f r,

10034: ,r.person_id
10035: ,oipl.opt_id
10036: ,r.ptip_id
10037: ,r.enrt_mthd_cd
10038: From ben_prtt_enrt_rslt_f r,
10039: ben_oipl_f oipl
10040: Where r.per_in_ler_id <> p_per_in_ler_id
10041: and r.person_id = p_person_id
10042: and nvl(r.pgm_id,-1) = p_pgm_id

Line 10098: From ben_prtt_enrt_rslt_f r

10094: ,r.person_id
10095: ,oipl.opt_id
10096: ,r.ptip_id
10097: ,r.enrt_mthd_cd
10098: From ben_prtt_enrt_rslt_f r
10099: ,ben_elig_per_elctbl_chc c
10100: ,ben_oipl_f oipl
10101: ,ben_pil_elctbl_chc_popl pel
10102: Where c.per_in_ler_id = p_per_in_ler_id

Line 10153: l_other_pen_rec ben_prtt_enrt_rslt_f%rowtype;

10149: l_effective_start_date date;
10150: l_effective_end_date date;
10151: l_pl_typ_id number;
10152: l_total_amt number;
10153: l_other_pen_rec ben_prtt_enrt_rslt_f%rowtype;
10154: l_rqd_perd_enrt_nenrt_uom varchar2(30);
10155: l_rqd_perd_enrt_nenrt_val number;
10156: l_rqd_perd_enrt_nenrt_rl number;
10157: l_level varchar2(30);

Line 10167: from ben_prtt_enrt_rslt_f pen

10163: cursor no_lngr_elig_pgm_c is
10164: select pen.prtt_enrt_rslt_id,
10165: pen.object_version_number,
10166: pen.effective_start_date
10167: from ben_prtt_enrt_rslt_f pen
10168: where pen.per_in_ler_id = p_per_in_ler_id
10169: and pen.pgm_id = p_pgm_id
10170: and pen.prtt_enrt_rslt_stat_cd is null
10171: and pen.no_lngr_elig_flag = 'Y'

Line 10195: from ben_prtt_enrt_rslt_f pen

10191: cursor no_lngr_elig_pnip_c is
10192: select pen.prtt_enrt_rslt_id,
10193: pen.object_version_number,
10194: pen.effective_start_date
10195: from ben_prtt_enrt_rslt_f pen
10196: where pen.pgm_id is null
10197: and pl_typ_id = l_pl_typ_id
10198: and pen.per_in_ler_id = p_per_in_ler_id
10199: and pen.prtt_enrt_rslt_stat_cd is null

Line 10236: from ben_prtt_enrt_rslt_f prt,

10232: -- end 3123698
10233: --
10234: cursor c_prtt_total_bnft_amt(p_acrs_ptip_cvg_id number) is
10235: select sum(prt.bnft_amt)
10236: from ben_prtt_enrt_rslt_f prt,
10237: ben_ptip_f ptp
10238: where prt.pgm_id = p_pgm_id
10239: and prt.person_id = p_person_id
10240: and prt.sspndd_flag = 'N' -- # 3626176

Line 10260: from ben_prtt_enrt_rslt_f prt,

10256: --here.
10257: --
10258: cursor c_prtt_total_bnft_amt_sspndd(p_acrs_ptip_cvg_id number) is
10259: select sum(prt.bnft_amt)
10260: from ben_prtt_enrt_rslt_f prt,
10261: ben_ptip_f ptp
10262: where prt.pgm_id = p_pgm_id
10263: and prt.person_id = p_person_id
10264: and ( prt.sspndd_flag = 'Y' or (

Line 10267: from ben_prtt_enrt_rslt_f prt1

10263: and prt.person_id = p_person_id
10264: and ( prt.sspndd_flag = 'Y' or (
10265: prt.sspndd_flag = 'N' and not exists (
10266: select 'x'
10267: from ben_prtt_enrt_rslt_f prt1
10268: where prt1.person_id = p_person_id
10269: and prt1.pgm_id = p_pgm_id
10270: and PRT1.rplcs_sspndd_rslt_id = prt.prtt_enrt_rslt_id
10271: and prt1.per_in_ler_id = prt.per_in_ler_id

Line 10307: From ben_prtt_enrt_rslt_f r,

10303: ,c.dpnt_cvg_strt_dt_rl
10304: ,pil.ler_id
10305: ,c.alws_dpnt_dsgn_flag
10306: ,pl.bnf_dsgn_cd
10307: From ben_prtt_enrt_rslt_f r,
10308: ben_elig_per_elctbl_chc c,
10309: ben_per_in_ler pil,
10310: ben_pl_f pl
10311: Where r.pl_typ_id = p_pl_typ_id