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.GHR_BENEFITS_EIT dependencies on BEN_PGM_F

Line 17: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,

13: p_temps_total_cost varchar2,
14: p_temp_appt varchar2 default 'N')
15: IS
16: -- Cursor to get Program
17: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
18: c_effective_date ben_pgm_f.effective_start_date%type) is
19: SELECT pgm.pgm_id
20: FROM ben_pgm_f pgm
21: WHERE pgm.name = c_prog_name

Line 18: c_effective_date ben_pgm_f.effective_start_date%type) is

14: p_temp_appt varchar2 default 'N')
15: IS
16: -- Cursor to get Program
17: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
18: c_effective_date ben_pgm_f.effective_start_date%type) is
19: SELECT pgm.pgm_id
20: FROM ben_pgm_f pgm
21: WHERE pgm.name = c_prog_name
22: AND pgm.business_group_id = c_business_group_id

Line 20: FROM ben_pgm_f pgm

16: -- Cursor to get Program
17: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
18: c_effective_date ben_pgm_f.effective_start_date%type) is
19: SELECT pgm.pgm_id
20: FROM ben_pgm_f pgm
21: WHERE pgm.name = c_prog_name
22: AND pgm.business_group_id = c_business_group_id
23: AND c_effective_date between effective_start_date and effective_end_date;
24:

Line 26: c_effective_date ben_pgm_f.effective_start_date%type) is

22: AND pgm.business_group_id = c_business_group_id
23: AND c_effective_date between effective_start_date and effective_end_date;
24:
25: CURSOR c_emp_in_ben(c_person_id ben_prtt_enrt_rslt_f.person_id%type, c_pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
26: c_effective_date ben_pgm_f.effective_start_date%type) is
27: SELECT 1
28: FROM ben_prtt_enrt_rslt_f
29: WHERE person_id = c_person_id
30: AND pgm_id = c_pgm_id

Line 35: CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,

31: AND prtt_enrt_rslt_stat_cd IS NULL
32: AND c_effective_date between effective_start_date and effective_end_date;
33:
34: --Cursor to get the Plan Type Id for the given Business_group_id
35: CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
36: c_effective_date ben_pgm_f.effective_start_date%type) is
37: SELECT plt.pl_typ_id
38: FROM ben_pl_typ_f plt
39: WHERE plt.name = c_plan_type -- 'Savings Plan'

Line 36: c_effective_date ben_pgm_f.effective_start_date%type) is

32: AND c_effective_date between effective_start_date and effective_end_date;
33:
34: --Cursor to get the Plan Type Id for the given Business_group_id
35: CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
36: c_effective_date ben_pgm_f.effective_start_date%type) is
37: SELECT plt.pl_typ_id
38: FROM ben_pl_typ_f plt
39: WHERE plt.name = c_plan_type -- 'Savings Plan'
40: AND plt.business_group_id = c_business_group_id

Line 44: c_effective_date ben_pgm_f.effective_start_date%type) is

40: AND plt.business_group_id = c_business_group_id
41: AND c_effective_date between effective_start_date and effective_end_date;
42:
43: CURSOR get_ptip_id(c_plan_type_id ben_ptip_f.pl_typ_id%type, c_pgm_id ben_ptip_f.pgm_id%type,
44: c_effective_date ben_pgm_f.effective_start_date%type) is
45: SELECT ptip_id
46: FROM ben_ptip_f
47: WHERE pl_typ_id = c_plan_type_id
48: AND pgm_id = c_pgm_id

Line 51: CURSOR get_pl_id(c_health_plan ben_pl_f.short_code%type, c_business_group_id ben_pgm_f.business_group_id%type,

47: WHERE pl_typ_id = c_plan_type_id
48: AND pgm_id = c_pgm_id
49: AND c_effective_date between effective_start_date and effective_end_date;
50:
51: CURSOR get_pl_id(c_health_plan ben_pl_f.short_code%type, c_business_group_id ben_pgm_f.business_group_id%type,
52: c_effective_date ben_pgm_f.effective_start_date%type) is
53: SELECT pln.pl_id pl_id
54: FROM ben_pl_f pln
55: WHERE pln.short_code = c_health_plan

Line 52: c_effective_date ben_pgm_f.effective_start_date%type) is

48: AND pgm_id = c_pgm_id
49: AND c_effective_date between effective_start_date and effective_end_date;
50:
51: CURSOR get_pl_id(c_health_plan ben_pl_f.short_code%type, c_business_group_id ben_pgm_f.business_group_id%type,
52: c_effective_date ben_pgm_f.effective_start_date%type) is
53: SELECT pln.pl_id pl_id
54: FROM ben_pl_f pln
55: WHERE pln.short_code = c_health_plan
56: AND pln.business_group_id = c_business_group_id

Line 61: CURSOR get_opt_id(c_option_code ben_opt_f.short_code%type, c_business_group_id ben_pgm_f.business_group_id%type,

57: AND c_effective_date between effective_start_date and effective_end_date
58: AND pl_stat_cd = 'A';
59:
60: --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
61: CURSOR get_opt_id(c_option_code ben_opt_f.short_code%type, c_business_group_id ben_pgm_f.business_group_id%type,
62: c_effective_date ben_pgm_f.effective_start_date%type) is
63: SELECT opt_id
64: FROM ben_opt_f opt
65: WHERE opt.short_code = c_option_code

Line 62: c_effective_date ben_pgm_f.effective_start_date%type) is

58: AND pl_stat_cd = 'A';
59:
60: --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
61: CURSOR get_opt_id(c_option_code ben_opt_f.short_code%type, c_business_group_id ben_pgm_f.business_group_id%type,
62: c_effective_date ben_pgm_f.effective_start_date%type) is
63: SELECT opt_id
64: FROM ben_opt_f opt
65: WHERE opt.short_code = c_option_code
66: AND opt.business_group_id = c_business_group_id

Line 70: CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type, c_pgm_id ben_plip_f.pgm_id%type, c_business_group_id ben_pgm_f.business_group_id%type,

66: AND opt.business_group_id = c_business_group_id
67: AND c_effective_date between effective_start_date and effective_end_date;
68:
69: --Cursor to get the plan in Program Id for the given Pl_id
70: CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type, c_pgm_id ben_plip_f.pgm_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
71: c_effective_date ben_pgm_f.effective_start_date%type) is
72: SELECT plip.plip_id
73: FROM ben_plip_f plip
74: WHERE plip.pl_id = c_plan_id

Line 71: c_effective_date ben_pgm_f.effective_start_date%type) is

67: AND c_effective_date between effective_start_date and effective_end_date;
68:
69: --Cursor to get the plan in Program Id for the given Pl_id
70: CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type, c_pgm_id ben_plip_f.pgm_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
71: c_effective_date ben_pgm_f.effective_start_date%type) is
72: SELECT plip.plip_id
73: FROM ben_plip_f plip
74: WHERE plip.pl_id = c_plan_id
75: AND plip.pgm_id = c_pgm_id

Line 81: CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type, c_opt_id ben_opt_f.opt_id%type, c_business_group_id ben_pgm_f.business_group_id%type,

77: AND c_effective_date between effective_start_date and effective_end_date;
78:
79: -- Cursor to get the option in plan Id
80:
81: CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type, c_opt_id ben_opt_f.opt_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
82: c_effective_date ben_pgm_f.effective_start_date%type) is
83: SELECT oipl_id
84: FROM ben_oipl_f
85: WHERE pl_id = c_pl_id

Line 82: c_effective_date ben_pgm_f.effective_start_date%type) is

78:
79: -- Cursor to get the option in plan Id
80:
81: CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type, c_opt_id ben_opt_f.opt_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
82: c_effective_date ben_pgm_f.effective_start_date%type) is
83: SELECT oipl_id
84: FROM ben_oipl_f
85: WHERE pl_id = c_pl_id
86: AND opt_id = c_opt_id

Line 90: Cursor get_ler_id(c_life_event ben_ler_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,

86: AND opt_id = c_opt_id
87: AND business_group_id = c_business_group_id
88: AND c_effective_date between effective_start_date and effective_end_date;
89:
90: Cursor get_ler_id(c_life_event ben_ler_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
91: c_effective_date ben_pgm_f.effective_start_date%type) is
92: select ler.ler_id
93: from ben_ler_f ler
94: where ler.business_group_id = c_business_group_id

Line 91: c_effective_date ben_pgm_f.effective_start_date%type) is

87: AND business_group_id = c_business_group_id
88: AND c_effective_date between effective_start_date and effective_end_date;
89:
90: Cursor get_ler_id(c_life_event ben_ler_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
91: c_effective_date ben_pgm_f.effective_start_date%type) is
92: select ler.ler_id
93: from ben_ler_f ler
94: where ler.business_group_id = c_business_group_id
95: and ler.name = c_life_event

Line 98: CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,

94: where ler.business_group_id = c_business_group_id
95: and ler.name = c_life_event
96: and c_effective_date between effective_start_date and effective_end_date;
97:
98: CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
99: c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
100: c_ptip_id ben_ptip_f.ptip_id%type, c_oipl_id ben_oipl_f.oipl_id%type,
101: c_ler_id ben_ler_f.ler_id%type, c_person_id per_all_people_f.person_id%type) IS
102: SELECT elig_per_elctbl_chc_id,

Line 118: Cursor get_elig_chc_id(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,

114: AND pil.ler_id = c_ler_id
115: AND pil.person_id = c_person_id
116: AND PER_IN_LER_STAT_CD NOT IN ('BCKDT','PROCD');
117:
118: Cursor get_elig_chc_id(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
119: c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
120: c_ptip_id ben_ptip_f.ptip_id%type,
121: c_ler_id ben_ler_f.ler_id%type, c_person_id per_all_people_f.person_id%type) is
122: select elig_per_elctbl_chc_id,

Line 147: l_pgm_id ben_pgm_f.pgm_id%type;

143: l_warning boolean;
144: l_business_group_id per_all_people_f.business_group_id%type;
145: l_pl_code ben_pl_f.short_code%type ;
146: l_opt_code ben_opt_f.short_code%type;
147: l_pgm_id ben_pgm_f.pgm_id%type;
148: l_err_msg varchar2(2000);
149: l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
150: l_ptip_id ben_ptip_f.ptip_id%type;
151: l_pl_id ben_pl_f.pl_id%type;

Line 616: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,

612: )
613:
614: IS
615: -- Cursor to get Program
616: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
617: c_effective_date ben_pgm_f.effective_start_date%type) is
618: SELECT pgm.pgm_id
619: FROM ben_pgm_f pgm
620: WHERE pgm.name = c_prog_name

Line 617: c_effective_date ben_pgm_f.effective_start_date%type) is

613:
614: IS
615: -- Cursor to get Program
616: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
617: c_effective_date ben_pgm_f.effective_start_date%type) is
618: SELECT pgm.pgm_id
619: FROM ben_pgm_f pgm
620: WHERE pgm.name = c_prog_name
621: AND pgm.business_group_id = c_business_group_id

Line 619: FROM ben_pgm_f pgm

615: -- Cursor to get Program
616: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
617: c_effective_date ben_pgm_f.effective_start_date%type) is
618: SELECT pgm.pgm_id
619: FROM ben_pgm_f pgm
620: WHERE pgm.name = c_prog_name
621: AND pgm.business_group_id = c_business_group_id
622: AND c_effective_date between effective_start_date and effective_end_date;
623:

Line 625: c_effective_date ben_pgm_f.effective_start_date%type) is

621: AND pgm.business_group_id = c_business_group_id
622: AND c_effective_date between effective_start_date and effective_end_date;
623:
624: CURSOR c_emp_in_ben(c_person_id ben_prtt_enrt_rslt_f.person_id%type, c_pgm_id ben_prtt_enrt_rslt_f.pgm_id%type,
625: c_effective_date ben_pgm_f.effective_start_date%type) is
626: SELECT 1
627: FROM ben_prtt_enrt_rslt_f
628: WHERE person_id = c_person_id
629: AND pgm_id = c_pgm_id

Line 633: CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,

629: AND pgm_id = c_pgm_id
630: AND c_effective_date between effective_start_date and effective_end_date;
631:
632: --Cursor to get the Plan Type Id for the given Business_group_id
633: CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
634: c_effective_date ben_pgm_f.effective_start_date%type) is
635: SELECT plt.pl_typ_id
636: FROM ben_pl_typ_f plt
637: WHERE plt.name = c_plan_type -- 'Savings Plan'

Line 634: c_effective_date ben_pgm_f.effective_start_date%type) is

630: AND c_effective_date between effective_start_date and effective_end_date;
631:
632: --Cursor to get the Plan Type Id for the given Business_group_id
633: CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
634: c_effective_date ben_pgm_f.effective_start_date%type) is
635: SELECT plt.pl_typ_id
636: FROM ben_pl_typ_f plt
637: WHERE plt.name = c_plan_type -- 'Savings Plan'
638: AND plt.business_group_id = c_business_group_id

Line 642: c_effective_date ben_pgm_f.effective_start_date%type) is

638: AND plt.business_group_id = c_business_group_id
639: AND c_effective_date between effective_start_date and effective_end_date;
640:
641: CURSOR get_ptip_id(c_plan_type_id ben_ptip_f.pl_typ_id%type, c_pgm_id ben_ptip_f.pgm_id%type,
642: c_effective_date ben_pgm_f.effective_start_date%type) is
643: SELECT ptip_id
644: FROM ben_ptip_f
645: WHERE pl_typ_id = c_plan_type_id
646: AND pgm_id = c_pgm_id

Line 649: CURSOR get_pl_id(c_pl_name ben_pl_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,

645: WHERE pl_typ_id = c_plan_type_id
646: AND pgm_id = c_pgm_id
647: AND c_effective_date between effective_start_date and effective_end_date;
648:
649: CURSOR get_pl_id(c_pl_name ben_pl_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
650: c_effective_date ben_pgm_f.effective_start_date%type) is
651: SELECT pln.pl_id pl_id
652: FROM ben_pl_f pln
653: WHERE pln.name = c_pl_name

Line 650: c_effective_date ben_pgm_f.effective_start_date%type) is

646: AND pgm_id = c_pgm_id
647: AND c_effective_date between effective_start_date and effective_end_date;
648:
649: CURSOR get_pl_id(c_pl_name ben_pl_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
650: c_effective_date ben_pgm_f.effective_start_date%type) is
651: SELECT pln.pl_id pl_id
652: FROM ben_pl_f pln
653: WHERE pln.name = c_pl_name
654: AND pln.business_group_id = c_business_group_id

Line 658: CURSOR get_opt_id(c_opt_name ben_opt_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,

654: AND pln.business_group_id = c_business_group_id
655: AND c_effective_date between effective_start_date and effective_end_date;
656:
657: --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
658: CURSOR get_opt_id(c_opt_name ben_opt_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
659: c_effective_date ben_pgm_f.effective_start_date%type) is
660: SELECT opt_id
661: FROM ben_opt_f opt
662: WHERE opt.name = c_opt_name

Line 659: c_effective_date ben_pgm_f.effective_start_date%type) is

655: AND c_effective_date between effective_start_date and effective_end_date;
656:
657: --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
658: CURSOR get_opt_id(c_opt_name ben_opt_f.name%type, c_business_group_id ben_pgm_f.business_group_id%type,
659: c_effective_date ben_pgm_f.effective_start_date%type) is
660: SELECT opt_id
661: FROM ben_opt_f opt
662: WHERE opt.name = c_opt_name
663: AND opt.business_group_id = c_business_group_id

Line 667: CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type, c_pgm_id ben_plip_f.pgm_id%type, c_business_group_id ben_pgm_f.business_group_id%type,

663: AND opt.business_group_id = c_business_group_id
664: AND c_effective_date between effective_start_date and effective_end_date;
665:
666: --Cursor to get the plan in Program Id for the given Pl_id
667: CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type, c_pgm_id ben_plip_f.pgm_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
668: c_effective_date ben_pgm_f.effective_start_date%type) is
669: SELECT plip.plip_id
670: FROM ben_plip_f plip
671: WHERE plip.pl_id = c_plan_id

Line 668: c_effective_date ben_pgm_f.effective_start_date%type) is

664: AND c_effective_date between effective_start_date and effective_end_date;
665:
666: --Cursor to get the plan in Program Id for the given Pl_id
667: CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type, c_pgm_id ben_plip_f.pgm_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
668: c_effective_date ben_pgm_f.effective_start_date%type) is
669: SELECT plip.plip_id
670: FROM ben_plip_f plip
671: WHERE plip.pl_id = c_plan_id
672: AND plip.pgm_id = c_pgm_id

Line 678: CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type, c_opt_id ben_opt_f.opt_id%type, c_business_group_id ben_pgm_f.business_group_id%type,

674: AND c_effective_date between effective_start_date and effective_end_date;
675:
676: -- Cursor to get the option in plan Id
677:
678: CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type, c_opt_id ben_opt_f.opt_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
679: c_effective_date ben_pgm_f.effective_start_date%type) is
680: SELECT oipl_id
681: FROM ben_oipl_f
682: WHERE pl_id = c_pl_id

Line 679: c_effective_date ben_pgm_f.effective_start_date%type) is

675:
676: -- Cursor to get the option in plan Id
677:
678: CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type, c_opt_id ben_opt_f.opt_id%type, c_business_group_id ben_pgm_f.business_group_id%type,
679: c_effective_date ben_pgm_f.effective_start_date%type) is
680: SELECT oipl_id
681: FROM ben_oipl_f
682: WHERE pl_id = c_pl_id
683: AND opt_id = c_opt_id

Line 687: CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,

683: AND opt_id = c_opt_id
684: AND business_group_id = c_business_group_id
685: AND c_effective_date between effective_start_date and effective_end_date;
686:
687: CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
688: c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
689: c_ptip_id ben_ptip_f.ptip_id%type, c_oipl_id ben_oipl_f.oipl_id%type,
690: c_person_id per_all_people_f.person_id%type) IS
691: SELECT elig_per_elctbl_chc_id,

Line 719: l_pgm_id ben_pgm_f.pgm_id%type;

715: l_effective_date date;
716: l_business_group_id per_all_people_f.business_group_id%type;
717: l_pl_code ben_pl_f.short_code%type ;
718: l_opt_name ben_opt_f.name%type;
719: l_pgm_id ben_pgm_f.pgm_id%type;
720: l_err_msg varchar2(2000);
721: l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
722: l_ptip_id ben_ptip_f.ptip_id%type;
723: l_pl_id ben_pl_f.pl_id%type;

Line 888: /*(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,

884: END IF;
885:
886: hr_utility.set_location('Option in plan ID ' || l_oipl_id,1234);
887:
888: /*(c_pgm_id ben_pgm_f.pgm_id%type, c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
889: c_pl_id ben_pl_f.pl_id%type, c_plip_id ben_plip_f.plip_id%type,
890: c_ptip_id ben_ptip_f.ptip_id%type, c_oipl_id ben_oipl_f.oipl_id%type,
891: c_person_id per_all_people_f.person_id%type) */
892: hr_utility.set_location('p_manage_life_events done' ,1234);

Line 975: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type,

971: p_opt_code ben_opt_f.short_code%type,
972: p_assignment_id per_all_assignments_f.assignment_id%type) IS
973:
974: -- Cursor to get Program
975: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type,
976: c_business_group_id ben_pgm_f.business_group_id%type,
977: c_effective_date ben_pgm_f.effective_start_date%type) is
978: SELECT pgm.pgm_id
979: FROM ben_pgm_f pgm

Line 976: c_business_group_id ben_pgm_f.business_group_id%type,

972: p_assignment_id per_all_assignments_f.assignment_id%type) IS
973:
974: -- Cursor to get Program
975: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type,
976: c_business_group_id ben_pgm_f.business_group_id%type,
977: c_effective_date ben_pgm_f.effective_start_date%type) is
978: SELECT pgm.pgm_id
979: FROM ben_pgm_f pgm
980: WHERE pgm.name = c_prog_name

Line 977: c_effective_date ben_pgm_f.effective_start_date%type) is

973:
974: -- Cursor to get Program
975: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type,
976: c_business_group_id ben_pgm_f.business_group_id%type,
977: c_effective_date ben_pgm_f.effective_start_date%type) is
978: SELECT pgm.pgm_id
979: FROM ben_pgm_f pgm
980: WHERE pgm.name = c_prog_name
981: AND pgm.business_group_id = c_business_group_id

Line 979: FROM ben_pgm_f pgm

975: CURSOR c_get_pgm_id(c_prog_name ben_pgm_f.name%type,
976: c_business_group_id ben_pgm_f.business_group_id%type,
977: c_effective_date ben_pgm_f.effective_start_date%type) is
978: SELECT pgm.pgm_id
979: FROM ben_pgm_f pgm
980: WHERE pgm.name = c_prog_name
981: AND pgm.business_group_id = c_business_group_id
982: AND c_effective_date between effective_start_date and effective_end_date;
983:

Line 986: c_business_group_id ben_pgm_f.business_group_id%type,

982: AND c_effective_date between effective_start_date and effective_end_date;
983:
984: --Cursor to get the Plan Type Id for the given Business_group_id
985: CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type,
986: c_business_group_id ben_pgm_f.business_group_id%type,
987: c_effective_date ben_pgm_f.effective_start_date%type) is
988: SELECT plt.pl_typ_id
989: FROM ben_pl_typ_f plt
990: WHERE plt.name = c_plan_type

Line 987: c_effective_date ben_pgm_f.effective_start_date%type) is

983:
984: --Cursor to get the Plan Type Id for the given Business_group_id
985: CURSOR c_get_pl_typ_id(c_plan_type ben_pl_typ_f.name%type,
986: c_business_group_id ben_pgm_f.business_group_id%type,
987: c_effective_date ben_pgm_f.effective_start_date%type) is
988: SELECT plt.pl_typ_id
989: FROM ben_pl_typ_f plt
990: WHERE plt.name = c_plan_type
991: AND plt.business_group_id = c_business_group_id

Line 996: c_effective_date ben_pgm_f.effective_start_date%type) is

992: AND c_effective_date between effective_start_date and effective_end_date;
993:
994: CURSOR get_ptip_id(c_plan_type_id ben_ptip_f.pl_typ_id%type,
995: c_pgm_id ben_ptip_f.pgm_id%type,
996: c_effective_date ben_pgm_f.effective_start_date%type) is
997: SELECT ptip_id
998: FROM ben_ptip_f
999: WHERE pl_typ_id = c_plan_type_id
1000: AND pgm_id = c_pgm_id

Line 1004: c_business_group_id ben_pgm_f.business_group_id%type,

1000: AND pgm_id = c_pgm_id
1001: AND c_effective_date between effective_start_date and effective_end_date;
1002:
1003: CURSOR get_pl_id(c_health_plan ben_pl_f.short_code%type,
1004: c_business_group_id ben_pgm_f.business_group_id%type,
1005: c_effective_date ben_pgm_f.effective_start_date%type) is
1006: SELECT pln.pl_id pl_id
1007: FROM ben_pl_f pln
1008: WHERE pln.short_code = c_health_plan

Line 1005: c_effective_date ben_pgm_f.effective_start_date%type) is

1001: AND c_effective_date between effective_start_date and effective_end_date;
1002:
1003: CURSOR get_pl_id(c_health_plan ben_pl_f.short_code%type,
1004: c_business_group_id ben_pgm_f.business_group_id%type,
1005: c_effective_date ben_pgm_f.effective_start_date%type) is
1006: SELECT pln.pl_id pl_id
1007: FROM ben_pl_f pln
1008: WHERE pln.short_code = c_health_plan
1009: AND pln.business_group_id = c_business_group_id

Line 1015: c_business_group_id ben_pgm_f.business_group_id%type,

1011: AND pl_stat_cd = 'A';
1012:
1013: --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
1014: CURSOR get_opt_id(c_option_code ben_opt_f.short_code%type,
1015: c_business_group_id ben_pgm_f.business_group_id%type,
1016: c_effective_date ben_pgm_f.effective_start_date%type) is
1017: SELECT opt_id
1018: FROM ben_opt_f opt
1019: WHERE opt.short_code = c_option_code

Line 1016: c_effective_date ben_pgm_f.effective_start_date%type) is

1012:
1013: --Cursor to get the opt_id for the EE's Enrollment Screen Entry Value
1014: CURSOR get_opt_id(c_option_code ben_opt_f.short_code%type,
1015: c_business_group_id ben_pgm_f.business_group_id%type,
1016: c_effective_date ben_pgm_f.effective_start_date%type) is
1017: SELECT opt_id
1018: FROM ben_opt_f opt
1019: WHERE opt.short_code = c_option_code
1020: AND opt.business_group_id = c_business_group_id

Line 1026: c_business_group_id ben_pgm_f.business_group_id%type,

1022:
1023: --Cursor to get the plan in Program Id for the given Pl_id
1024: CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type,
1025: c_pgm_id ben_plip_f.pgm_id%type,
1026: c_business_group_id ben_pgm_f.business_group_id%type,
1027: c_effective_date ben_pgm_f.effective_start_date%type) is
1028: SELECT plip.plip_id
1029: FROM ben_plip_f plip
1030: WHERE plip.pl_id = c_plan_id

Line 1027: c_effective_date ben_pgm_f.effective_start_date%type) is

1023: --Cursor to get the plan in Program Id for the given Pl_id
1024: CURSOR get_plip_id(c_plan_id ben_plip_f.pl_id%type,
1025: c_pgm_id ben_plip_f.pgm_id%type,
1026: c_business_group_id ben_pgm_f.business_group_id%type,
1027: c_effective_date ben_pgm_f.effective_start_date%type) is
1028: SELECT plip.plip_id
1029: FROM ben_plip_f plip
1030: WHERE plip.pl_id = c_plan_id
1031: AND plip.pgm_id = c_pgm_id

Line 1039: c_business_group_id ben_pgm_f.business_group_id%type,

1035: -- Cursor to get the option in plan Id
1036:
1037: CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type,
1038: c_opt_id ben_opt_f.opt_id%type,
1039: c_business_group_id ben_pgm_f.business_group_id%type,
1040: c_effective_date ben_pgm_f.effective_start_date%type) is
1041: SELECT oipl_id
1042: FROM ben_oipl_f
1043: WHERE pl_id = c_pl_id

Line 1040: c_effective_date ben_pgm_f.effective_start_date%type) is

1036:
1037: CURSOR get_oipl_id(c_pl_id ben_pl_f.pl_id%type,
1038: c_opt_id ben_opt_f.opt_id%type,
1039: c_business_group_id ben_pgm_f.business_group_id%type,
1040: c_effective_date ben_pgm_f.effective_start_date%type) is
1041: SELECT oipl_id
1042: FROM ben_oipl_f
1043: WHERE pl_id = c_pl_id
1044: AND opt_id = c_opt_id

Line 1049: c_business_group_id ben_pgm_f.business_group_id%type,

1045: AND business_group_id = c_business_group_id
1046: AND c_effective_date between effective_start_date and effective_end_date;
1047:
1048: CURSOR get_ler_id(c_life_event ben_ler_f.name%type,
1049: c_business_group_id ben_pgm_f.business_group_id%type,
1050: c_effective_date ben_pgm_f.effective_start_date%type) is
1051: SELECT ler.ler_id
1052: FROM ben_ler_f ler
1053: WHERE ler.business_group_id = c_business_group_id

Line 1050: c_effective_date ben_pgm_f.effective_start_date%type) is

1046: AND c_effective_date between effective_start_date and effective_end_date;
1047:
1048: CURSOR get_ler_id(c_life_event ben_ler_f.name%type,
1049: c_business_group_id ben_pgm_f.business_group_id%type,
1050: c_effective_date ben_pgm_f.effective_start_date%type) is
1051: SELECT ler.ler_id
1052: FROM ben_ler_f ler
1053: WHERE ler.business_group_id = c_business_group_id
1054: AND ler.name = c_life_event

Line 1057: CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type,

1053: WHERE ler.business_group_id = c_business_group_id
1054: AND ler.name = c_life_event
1055: AND c_effective_date between effective_start_date and effective_end_date;
1056:
1057: CURSOR get_elig_chc_id_opt(c_pgm_id ben_pgm_f.pgm_id%type,
1058: c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
1059: c_pl_id ben_pl_f.pl_id%type,
1060: c_plip_id ben_plip_f.plip_id%type,
1061: c_ptip_id ben_ptip_f.ptip_id%type,

Line 1079: CURSOR get_elig_chc_id(c_pgm_id ben_pgm_f.pgm_id%type,

1075: AND pil.per_in_ler_id = chc.per_in_ler_id
1076: AND pil.ler_id = c_ler_id
1077: AND pil.person_id = c_person_id;
1078:
1079: CURSOR get_elig_chc_id(c_pgm_id ben_pgm_f.pgm_id%type,
1080: c_pl_typ_id ben_pl_typ_f.pl_typ_id%type,
1081: c_pl_id ben_pl_f.pl_id%type,
1082: c_plip_id ben_plip_f.plip_id%type,
1083: c_ptip_id ben_ptip_f.ptip_id%type,

Line 1137: l_pgm_id ben_pgm_f.pgm_id%type;

1133: l_warning boolean;
1134: l_business_group_id per_all_people_f.business_group_id%type;
1135: l_pl_code ben_pl_f.short_code%type ;
1136: l_opt_code ben_opt_f.short_code%type;
1137: l_pgm_id ben_pgm_f.pgm_id%type;
1138: l_err_msg varchar2(2000);
1139: l_pl_typ_id ben_pl_typ_f.pl_typ_id%type;
1140: l_ptip_id ben_ptip_f.ptip_id%type;
1141: l_pl_id ben_pl_f.pl_id%type;