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_BACK_OUT_LIFE_EVENT dependencies on BEN_PER_IN_LER

Line 218: ben_per_in_ler to get l_person_id

214: for not NULL l_person_id prior to
215: opening cursor c_prv_of_previous_
216: pil. Also modified c_prv_of_
217: previous_pil to include join to
218: ben_per_in_ler to get l_person_id
219: based on this cursor rather than
220: c_ben_prtt_rt_val.
221: 03-JAN-01 tilak 115.53 - Bug 1182293 reimburement reqist table added
222: 09-feb-01 ikasire 115.54 Bug 1627373 and 1584238 to fix the

Line 447: ben_per_in_ler pil,

443: cursor get_contacts_pils(cv_person_id in number,
444: cv_csd_by_ptnl_ler_for_per_id in number) is
445: select pil.*
446: from per_contact_relationships pcr,
447: ben_per_in_ler pil,
448: ben_PTNL_LER_FOR_PER ppl
449: where --pcr.contact_person_id = cv_person_id
450: pcr.person_id = cv_person_id -- Bug 6620291
451: and pcr.business_group_id = p_business_group_id

Line 472: ben_per_in_ler pil,

468: cursor get_fut_dtd_cntct_pils(cv_person_id in number
469: ) is
470: select pil.*
471: from per_contact_relationships pcr,
472: ben_per_in_ler pil,
473: ben_PTNL_LER_FOR_PER ppl
474: where pcr.contact_person_id = cv_person_id
475: and pcr.business_group_id = p_business_group_id
476: and pil.lf_evt_ocrd_dt > (Select pil1.lf_evt_ocrd_dt

Line 477: from ben_per_in_ler pil1

473: ben_PTNL_LER_FOR_PER ppl
474: where pcr.contact_person_id = cv_person_id
475: and pcr.business_group_id = p_business_group_id
476: and pil.lf_evt_ocrd_dt > (Select pil1.lf_evt_ocrd_dt
477: from ben_per_in_ler pil1
478: where pil1.per_in_ler_id= p_per_in_ler_id)
479: and pcr.personal_flag = 'Y'
480: and pil.person_id = pcr.person_id
481: and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')

Line 602: procedure unprocess_ptnl_ler(p_per_in_ler in out nocopy BEN_PER_IN_LER%ROWTYPE

598: --
599: -- ----------------------------------------------------------------------------
600: -- |------------------------< unprocess_ptnl_ler >-----------------------------|
601: -- ----------------------------------------------------------------------------
602: procedure unprocess_ptnl_ler(p_per_in_ler in out nocopy BEN_PER_IN_LER%ROWTYPE
603: ,p_bckt_stat_cd in varchar2
604: ,p_effective_date in date) is
605: --
606: l_proc varchar2(72) := g_package||'unprocess_ptnl_ler';

Line 1155: from ben_per_in_ler pil

1151: pil.ptnl_ler_for_per_id,
1152: pil.person_id,
1153: pil.lf_evt_ocrd_dt,
1154: pil.ler_id
1155: from ben_per_in_ler pil
1156: where pil.per_in_ler_id = p_per_in_ler_id;
1157:
1158:
1159:

Line 1162: from ben_per_in_ler pil

1158:
1159:
1160: cursor c_get_all_per_in_ler is
1161: select *
1162: from ben_per_in_ler pil
1163: where pil.per_in_ler_id = p_per_in_ler_id and
1164: pil.business_group_id=p_business_group_id
1165: ;
1166:

Line 1178: ben_per_in_ler pil

1174: --
1175: cursor c_ler_typ is
1176: select ler.typ_cd
1177: from ben_ler_f ler,
1178: ben_per_in_ler pil
1179: where pil.per_in_ler_id = p_per_in_ler_id
1180: and ler.ler_id = pil.ler_id
1181: /*and ler.business_group_id = p_business_group_id*/ --CWBGLOBAL
1182: and pil.lf_evt_ocrd_Dt between

Line 1200: from ben_per_in_ler

1196: -- 2982606
1197: cursor c_prev_pil_id (p_person_id number, p_lf_evt_ocrd_dt date) is
1198: select per_in_ler_id ,
1199: lf_evt_ocrd_dt
1200: from ben_per_in_ler
1201: where per_in_ler_id <> p_per_in_ler_id
1202: and per_in_ler_stat_cd = 'PROCD'
1203: and person_id = p_person_id
1204: and lf_evt_ocrd_dt <= p_lf_evt_ocrd_dt

Line 1283: -- BEN_PER_IN_LER (PIL)

1279: --
1280: -- Tables that will be deleted from include and must be deleted from in
1281: -- a bottom to top fashion :
1282: --
1283: -- BEN_PER_IN_LER (PIL)
1284: -- BEN_PRTT_ENRT_RSLT_F (PEN)
1285: -- BEN_PL_BNF_F (PBN) (join to pil to check status)
1286: -- BEN_PRTT_ENRT_ACTN_F (PEA) (join to pil to check status)
1287: -- BEN_BNFT_PRVDD_LDGR_F (BPL)(join to pil to check status)

Line 2055: ben_per_in_ler pil

2051: --
2052: cursor c_get_past_pil (p_per_in_ler_id number) is
2053: select max(pea.per_in_ler_id)
2054: from ben_prtt_enrt_actn_f pea,
2055: ben_per_in_ler pil
2056: where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2057: and pea.per_in_ler_id <> p_per_in_ler_id
2058: and pea.business_group_id = p_business_group_id
2059: and pea.per_in_ler_id = pil.per_in_ler_id

Line 2426: -- BEN_PER_IN_LER

2422: --
2423: -- START ----> DELETE ROUTINE FOR BEN_ELIG_PER_OPT_F
2424: --
2425: -- Source Table
2426: -- BEN_PER_IN_LER
2427: --
2428: cursor c_ben_ELIG_PER_OPT_f is
2429: select epo.ELIG_PER_OPT_id,
2430: epo.object_version_number

Line 2432: ben_per_in_ler pil

2428: cursor c_ben_ELIG_PER_OPT_f is
2429: select epo.ELIG_PER_OPT_id,
2430: epo.object_version_number
2431: from ben_ELIG_PER_OPT_f epo,
2432: ben_per_in_ler pil
2433: where pil.per_in_ler_id = p_per_in_ler_id
2434: and pil.business_group_id = p_business_group_id
2435: and epo.per_in_ler_id = pil.per_in_ler_id
2436: and epo.business_group_id = pil.business_group_id

Line 2480: from ben_per_in_ler

2476: */
2477: /*
2478: --RCHASE 5364
2479: and epo.effective_end_date <= (select lf_evt_ocrd_dt
2480: from ben_per_in_ler
2481: where per_in_ler_id = p_per_in_ler_id); */
2482: --
2483: cursor c_epo_max_esd_of_past_pil(v_ELIG_PER_OPT_id in number) is
2484: select epo.effective_end_date, epo.object_version_number

Line 2486: ben_per_in_ler pil

2482: --
2483: cursor c_epo_max_esd_of_past_pil(v_ELIG_PER_OPT_id in number) is
2484: select epo.effective_end_date, epo.object_version_number
2485: from ben_ELIG_PER_OPT_f epo,
2486: ben_per_in_ler pil
2487: where epo.ELIG_PER_OPT_id = v_ELIG_PER_OPT_id
2488: and nvl(epo.per_in_ler_id , -1) <> p_per_in_ler_id
2489: and epo.business_group_id = p_business_group_id
2490: and epo.per_in_ler_id = pil.per_in_ler_id

Line 2513: -- BEN_PER_IN_LER

2509: --
2510: -- START ----> DELETE ROUTINE FOR BEN_ELIG_PER_F
2511: --
2512: -- Source Table
2513: -- BEN_PER_IN_LER
2514: --
2515: cursor c_ben_elig_per_f is
2516: select pep.elig_per_id,
2517: pep.object_version_number

Line 2519: ben_per_in_ler pil

2515: cursor c_ben_elig_per_f is
2516: select pep.elig_per_id,
2517: pep.object_version_number
2518: from ben_elig_per_f pep,
2519: ben_per_in_ler pil
2520: where pil.per_in_ler_id = p_per_in_ler_id
2521: and pil.business_group_id = p_business_group_id
2522: and pep.per_in_ler_id = pil.per_in_ler_id
2523: and pep.business_group_id = pil.business_group_id

Line 2553: ben_per_in_ler pil

2549: */
2550: cursor c_pep_max_esd_of_past_pil(v_elig_per_id in number) is
2551: select pep.effective_end_date, pep.object_version_number
2552: from ben_elig_per_f pep,
2553: ben_per_in_ler pil
2554: where pep.elig_per_id = v_elig_per_id
2555: and ((pep.per_in_ler_id <> p_per_in_ler_id
2556: and pep.per_in_ler_id is not null)
2557: or

Line 2593: -- BEN_PER_IN_LER

2589: -- END ----> DELETE ROUTINE FOR BEN_ELIG_PER_F
2590: -- START ----> DELETE ROUTINE FOR BEN_PRTT_PREM_F
2591: --
2592: -- Source Table
2593: -- BEN_PER_IN_LER
2594: --
2595: ----7133998
2596: cursor c_ben_prtt_prem_f_corr is
2597: select ppe.*

Line 2599: ben_per_in_ler pil,

2595: ----7133998
2596: cursor c_ben_prtt_prem_f_corr is
2597: select ppe.*
2598: from ben_prtt_prem_f ppe,
2599: ben_per_in_ler pil,
2600: BEN_LE_CLSN_N_RSTR bkup
2601: where pil.per_in_ler_id = p_per_in_ler_id
2602: and pil.business_group_id = p_business_group_id
2603: and ppe.per_in_ler_id = pil.per_in_ler_id

Line 2631: ben_per_in_ler pil

2627: cursor c_ben_prtt_prem_f is
2628: select ppe.prtt_prem_id,
2629: ppe.object_version_number
2630: from ben_prtt_prem_f ppe,
2631: ben_per_in_ler pil
2632: where pil.per_in_ler_id = p_per_in_ler_id
2633: and pil.business_group_id = p_business_group_id
2634: and ppe.per_in_ler_id = pil.per_in_ler_id
2635: and ppe.business_group_id = pil.business_group_id

Line 2666: ben_per_in_ler pil

2662: */
2663: cursor c_ppe_max_esd_of_past_pil(v_prtt_prem_id in number) is
2664: select prm.effective_end_date, prm.object_version_number
2665: from ben_prtt_prem_f prm,
2666: ben_per_in_ler pil
2667: where prm.prtt_prem_id = v_prtt_prem_id
2668: and nvl(prm.per_in_ler_id , -1) <> p_per_in_ler_id
2669: and prm.business_group_id = p_business_group_id
2670: and prm.per_in_ler_id = pil.per_in_ler_id

Line 2690: -- BEN_PER_IN_LER

2686: --
2687: -- START ----> DELETE ROUTINE FOR BEN_ELIG_CVRD_DPNT_F
2688: --
2689: -- Source Table
2690: -- BEN_PER_IN_LER
2691: --
2692: -- Bug 5222 : Removed the effective date check.
2693: -- See comments at c_ben_prtt_enrt_rslt_f cursor.
2694: --

Line 2701: ben_per_in_ler pil

2697: pdp.object_version_number,
2698: -- bug 5668052
2699: pdp.effective_start_date
2700: from ben_elig_cvrd_dpnt_f pdp,
2701: ben_per_in_ler pil
2702: where pil.per_in_ler_id = p_per_in_ler_id
2703: and pil.business_group_id = p_business_group_id
2704: and pdp.per_in_ler_id = pil.per_in_ler_id
2705: and pdp.business_group_id = pil.business_group_id

Line 2729: ben_per_in_ler pil

2725: */
2726: cursor c_pdp_max_esd_of_past_pil(v_elig_cvrd_dpnt_id in number) is
2727: select pdp.effective_end_date, pdp.object_version_number
2728: from ben_elig_cvrd_dpnt_f pdp,
2729: ben_per_in_ler pil
2730: where pdp.elig_cvrd_dpnt_id = v_elig_cvrd_dpnt_id
2731: and nvl(pdp.per_in_ler_id , -1) <> p_per_in_ler_id
2732: and pdp.business_group_id = p_business_group_id
2733: and pdp.per_in_ler_id = pil.per_in_ler_id

Line 2755: -- BEN_PER_IN_LER

2751: --
2752: -- START ----> DELETE ROUTINE FOR BEN_PL_BNF_F
2753: --
2754: -- Source Table
2755: -- BEN_PER_IN_LER
2756: --
2757: cursor c_ben_pl_bnf_f is
2758: select pbn.pl_bnf_id,
2759: pbn.object_version_number,

Line 2763: ben_per_in_ler pil

2759: pbn.object_version_number,
2760: -- bug 5668052
2761: pbn.effective_start_date
2762: from ben_pl_bnf_f pbn,
2763: ben_per_in_ler pil
2764: where pil.per_in_ler_id = p_per_in_ler_id
2765: and pil.business_group_id = p_business_group_id
2766: and pbn.per_in_ler_id = pil.per_in_ler_id
2767: and pbn.business_group_id = pil.business_group_id

Line 2802: ben_per_in_ler pil

2798: -- 5895645 : typo fix
2799: cursor c_pbn_max_esd_of_past_pil(v_pl_bnf_id in number) is
2800: select pbn.effective_end_date, pbn.object_version_number
2801: from ben_pl_bnf_f pbn,
2802: ben_per_in_ler pil
2803: where pbn.pl_bnf_id = v_pl_bnf_id
2804: and nvl(pbn.per_in_ler_id , -1) <> p_per_in_ler_id
2805: and pbn.business_group_id = p_business_group_id
2806: and pbn.per_in_ler_id = pil.per_in_ler_id

Line 2828: -- BEN_PER_IN_LER

2824: --
2825: -- START ----> DELETE ROUTINE FOR BEN_PRMRY_CARE_PRVDR_F
2826: --
2827: -- Source Table
2828: -- BEN_PER_IN_LER
2829: -- Drive Routes
2830: -- BEN_PRTT_ENRT_RSLT_F
2831: --
2832: --

Line 2839: ben_per_in_ler pil

2835: ppr.effective_start_date,
2836: ppr.object_version_number
2837: from ben_prmry_care_prvdr_f ppr,
2838: ben_prtt_enrt_rslt_f pen,
2839: ben_per_in_ler pil
2840: where pil.per_in_ler_id = p_per_in_ler_id
2841: and pil.business_group_id = p_business_group_id
2842: and pen.per_in_ler_id = pil.per_in_ler_id
2843: and pen.business_group_id = pil.business_group_id

Line 2870: -- BEN_PER_IN_LER

2866: --
2867: -- START ----> DELETE ROUTINE FOR BEN_PRTT_RT_VAL
2868: --
2869: -- Source Table
2870: -- BEN_PER_IN_LER
2871: --
2872: cursor c_ben_prtt_rt_val is
2873: select prv.prtt_rt_val_id,
2874: prv.object_version_number,

Line 2882: ben_per_in_ler pil

2878: prv.prtt_enrt_rslt_id,
2879: prv.pk_id,
2880: prv.pk_id_table_name
2881: from ben_prtt_rt_val prv,
2882: ben_per_in_ler pil
2883: where pil.per_in_ler_id = p_per_in_ler_id
2884: and pil.business_group_id = p_business_group_id
2885: and prv.prtt_rt_val_stat_cd is null
2886: and prv.per_in_ler_id = pil.per_in_ler_id

Line 2904: ben_per_in_ler pil

2900: prv.prtt_enrt_rslt_id,
2901: prv.acty_base_rt_id,
2902: pil.person_id
2903: from ben_prtt_rt_val prv,
2904: ben_per_in_ler pil
2905: where prv.ended_per_in_ler_id = pil.per_in_ler_id
2906: and prv.ended_per_in_ler_id = p_per_in_ler_id
2907: and nvl(prv.prtt_rt_val_stat_cd,'BCKDT') ='BCKDT'
2908: and prv.business_group_id = p_business_group_id

Line 2987: ben_per_in_ler pil

2983: /* BUG 3507554 Performance Changes
2984: cursor c_multiple_rate is
2985: select 'Y'
2986: from ben_prtt_rt_val prv,
2987: ben_per_in_ler pil
2988: where pil.per_in_ler_id = p_per_in_ler_id
2989: and pil.business_group_id = p_business_group_id
2990: and prv.prtt_rt_val_stat_cd is null
2991: and prv.per_in_ler_id = pil.per_in_ler_id

Line 3053: -- BEN_PER_IN_LER

3049: --
3050: -- START ----> DELETE ROUTINE FOR BEN_PRTT_ENRT_RSLT_F
3051: --
3052: -- Source Table
3053: -- BEN_PER_IN_LER
3054: --
3055: cursor c_ben_prtt_enrt_rslt_f is
3056: select pen.prtt_enrt_rslt_id,
3057: pen.object_version_number,

Line 3068: ben_per_in_ler pil

3064: pen.effective_start_date,
3065: pen.per_in_ler_id
3066: ,pil.lf_evt_ocrd_dt
3067: from ben_prtt_enrt_rslt_f pen,
3068: ben_per_in_ler pil
3069: where pil.per_in_ler_id = p_per_in_ler_id
3070: and pil.business_group_id = p_business_group_id
3071: and pen.per_in_ler_id = pil.per_in_ler_id
3072: and pen.business_group_id = pil.business_group_id

Line 3233: ben_per_in_ler pil

3229: -- on order by clause.
3230: cursor c_pen_max_esd_of_past_pil(v_prtt_enrt_rslt_id in number) is
3231: select pen.effective_end_date,pen.object_version_number, pen.per_in_ler_id
3232: from ben_prtt_enrt_rslt_f pen,
3233: ben_per_in_ler pil
3234: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
3235: and nvl(pen.per_in_ler_id , -1) <> p_per_in_ler_id
3236: and pen.business_group_id = p_business_group_id
3237: and pen.per_in_ler_id = pil.per_in_ler_id

Line 3284: ben_per_in_ler pil

3280: bpl.effective_end_date,
3281: bpl.effective_start_date,
3282: bpl.per_in_ler_id
3283: from ben_bnft_prvdd_ldgr_f bpl,
3284: ben_per_in_ler pil
3285: where pil.per_in_ler_id = p_per_in_ler_id
3286: and pil.business_group_id = p_business_group_id
3287: and bpl.per_in_ler_id = pil.per_in_ler_id
3288: and bpl.business_group_id = pil.business_group_id

Line 3333: ben_per_in_ler pil

3329: -- Bug#2646851 - cursor modified with order by clause and max function removed
3330: cursor c_bpl_max_esd_of_past_pil(v_bnft_prvdd_ldgr_id in number) is
3331: select bpl.effective_end_date, bpl.object_version_number
3332: from ben_bnft_prvdd_ldgr_f bpl,
3333: ben_per_in_ler pil
3334: where bpl.bnft_prvdd_ldgr_id = v_bnft_prvdd_ldgr_id
3335: and nvl(bpl.per_in_ler_id , -1) <> p_per_in_ler_id
3336: and bpl.business_group_id = p_business_group_id
3337: and bpl.per_in_ler_id = pil.per_in_ler_id

Line 3343: -- -- Source Table -- BEN_PER_IN_LER --

3339: order by bpl.effective_end_date desc;
3340: -- END ---> DELETE ROUTINE FOR BEN_BNFT_PRVDD_LDGR_F
3341:
3342: -- START ----> DELETE ROUTINE FOR BEN_PIL_ELCTBL_CHC_POPL
3343: -- -- Source Table -- BEN_PER_IN_LER --
3344: cursor c_ben_pil_elctbl_chc_popl is
3345: select pel.pil_elctbl_chc_popl_id,
3346: pel.object_version_number
3347: from ben_pil_elctbl_chc_popl pel,

Line 3348: ben_per_in_ler pil

3344: cursor c_ben_pil_elctbl_chc_popl is
3345: select pel.pil_elctbl_chc_popl_id,
3346: pel.object_version_number
3347: from ben_pil_elctbl_chc_popl pel,
3348: ben_per_in_ler pil
3349: where pil.per_in_ler_id = p_per_in_ler_id
3350: and pil.business_group_id = p_business_group_id
3351: and pel.per_in_ler_id = pil.per_in_ler_id
3352: and pel.business_group_id = pil.business_group_id;

Line 3380: ,ben_per_in_ler pil

3376: ,prc.effective_start_date
3377: ,pil.person_id
3378: from ben_prtt_reimbmt_rqst_f prc
3379: ,ben_prtt_rt_val prv
3380: ,ben_per_in_ler pil
3381: where prv.per_in_ler_id = p_per_in_ler_id
3382: and prv.PRTT_REIMBMT_RQST_id = prc.PRTT_REIMBMT_RQST_id
3383: and pil.per_in_ler_id = p_per_in_ler_id
3384: -- hnarayan -- bug 2223214

Line 3435: ben_per_in_ler pil

3431: cursor c_pen_sus is
3432: select pen.prtt_enrt_rslt_id,
3433: pen.per_in_ler_id
3434: from ben_prtt_enrt_rslt_f pen,
3435: ben_per_in_ler pil
3436: where pen.sspndd_flag = 'Y'
3437: and pen.per_in_ler_id <> pil.per_in_ler_id
3438: and pil.per_in_ler_id = p_per_in_ler_id
3439: and pen.person_id = pil.person_id