[Home] [Help]
PACKAGE: APPS.BEN_ICM_LIFE_EVENTS
Source
1 PACKAGE ben_icm_life_events AUTHID CURRENT_USER AS
2 /* $Header: benicmle.pkh 120.2 2007/04/09 10:41:49 rtagarra noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Determine Rates.
12
13 Purpose:
14 This process determines rates for either elctable choices or coverages, and
15 writes them to the ben_enrt_rt table. This process can only run in benmngle.
16
17 History:
18 Date Who Version What?
19 ---- --- ------- -----
20 10-Feb-07 rtagarra 120.0 Created.
21 28-Feb-07 rtagarra 120.1 GSCC Error.
22 */
23 --------------------------------------------------------------------------------
24 --
25 TYPE g_cache_pep_objects_rec IS RECORD (
26 elig_per_id ben_elig_per_f.elig_per_id%TYPE,
27 effective_start_date ben_elig_per_f.effective_start_date%TYPE,
28 effective_end_date ben_elig_per_f.effective_end_date%TYPE,
29 business_group_id ben_elig_per_f.business_group_id%TYPE,
30 pl_id ben_elig_per_f.pl_id%TYPE,
31 plip_id ben_elig_per_f.plip_id%TYPE,
32 ptip_id ben_elig_per_f.ptip_id%TYPE,
33 pgm_id ben_elig_per_f.pgm_id%TYPE,
34 ler_id ben_elig_per_f.ler_id%TYPE,
35 person_id ben_elig_per_f.person_id%TYPE,
36 per_in_ler_id ben_elig_per_f.per_in_ler_id%TYPE,
37 dpnt_othr_pl_cvrd_rl_flag ben_elig_per_f.dpnt_othr_pl_cvrd_rl_flag%TYPE,
38 pl_key_ee_flag ben_elig_per_f.pl_key_ee_flag%TYPE,
39 pl_hghly_compd_flag ben_elig_per_f.pl_hghly_compd_flag%TYPE,
40 prtn_ovridn_flag ben_elig_per_f.prtn_ovridn_flag%TYPE,
41 prtn_ovridn_thru_dt ben_elig_per_f.prtn_ovridn_thru_dt%TYPE,
42 no_mx_prtn_ovrid_thru_flag ben_elig_per_f.no_mx_prtn_ovrid_thru_flag%TYPE,
43 prtn_strt_dt ben_elig_per_f.prtn_strt_dt%TYPE,
44 dstr_rstcn_flag ben_elig_per_f.dstr_rstcn_flag%TYPE,
45 pl_wvd_flag ben_elig_per_f.pl_wvd_flag%TYPE,
46 wait_perd_cmpltn_dt ben_elig_per_f.wait_perd_cmpltn_dt%TYPE,
47 wait_perd_strt_dt ben_elig_per_f.wait_perd_strt_dt%TYPE,
48 elig_flag ben_elig_per_f.elig_flag%TYPE,
49 comp_ref_amt ben_elig_per_f.comp_ref_amt%TYPE,
50 cmbn_age_n_los_val ben_elig_per_f.cmbn_age_n_los_val%TYPE,
51 comp_ref_uom ben_elig_per_f.comp_ref_uom%TYPE,
52 age_val ben_elig_per_f.age_val%TYPE,
53 age_uom ben_elig_per_f.age_uom%TYPE,
54 los_val ben_elig_per_f.los_val%TYPE,
55 los_uom ben_elig_per_f.los_uom%TYPE,
56 hrs_wkd_val ben_elig_per_f.hrs_wkd_val%TYPE,
57 hrs_wkd_bndry_perd_cd ben_elig_per_f.hrs_wkd_bndry_perd_cd%TYPE,
58 pct_fl_tm_val ben_elig_per_f.pct_fl_tm_val%TYPE,
59 frz_los_flag ben_elig_per_f.frz_los_flag%TYPE,
60 frz_age_flag ben_elig_per_f.frz_age_flag%TYPE,
61 frz_cmp_lvl_flag ben_elig_per_f.frz_cmp_lvl_flag%TYPE,
62 frz_pct_fl_tm_flag ben_elig_per_f.frz_pct_fl_tm_flag%TYPE,
63 frz_hrs_wkd_flag ben_elig_per_f.frz_hrs_wkd_flag%TYPE,
64 frz_comb_age_and_los_flag ben_elig_per_f.frz_comb_age_and_los_flag%TYPE,
65 rt_comp_ref_amt ben_elig_per_f.rt_comp_ref_amt%TYPE,
66 rt_cmbn_age_n_los_val ben_elig_per_f.rt_cmbn_age_n_los_val%TYPE,
67 rt_comp_ref_uom ben_elig_per_f.rt_comp_ref_uom%TYPE,
68 rt_age_val ben_elig_per_f.rt_age_val%TYPE,
69 rt_age_uom ben_elig_per_f.rt_age_uom%TYPE,
70 rt_los_val ben_elig_per_f.rt_los_val%TYPE,
71 rt_los_uom ben_elig_per_f.rt_los_uom%TYPE,
72 rt_hrs_wkd_val ben_elig_per_f.rt_hrs_wkd_val%TYPE,
73 rt_hrs_wkd_bndry_perd_cd ben_elig_per_f.rt_hrs_wkd_bndry_perd_cd%TYPE,
74 rt_pct_fl_tm_val ben_elig_per_f.rt_pct_fl_tm_val%TYPE,
75 rt_frz_los_flag ben_elig_per_f.rt_frz_los_flag%TYPE,
76 rt_frz_age_flag ben_elig_per_f.rt_frz_age_flag%TYPE,
77 rt_frz_cmp_lvl_flag ben_elig_per_f.rt_frz_cmp_lvl_flag%TYPE,
78 rt_frz_pct_fl_tm_flag ben_elig_per_f.rt_frz_pct_fl_tm_flag%TYPE,
79 rt_frz_hrs_wkd_flag ben_elig_per_f.rt_frz_hrs_wkd_flag%TYPE,
80 rt_frz_comb_age_and_los_flag ben_elig_per_f.rt_frz_comb_age_and_los_flag%TYPE,
81 once_r_cntug_cd ben_elig_per_f.once_r_cntug_cd%TYPE,
82 pl_ordr_num ben_elig_per_f.pl_ordr_num%TYPE,
83 plip_ordr_num ben_elig_per_f.plip_ordr_num%TYPE,
84 ptip_ordr_num ben_elig_per_f.ptip_ordr_num%TYPE,
85 object_version_number ben_elig_per_f.object_version_number%TYPE,
86 p_effective_date DATE,
87 program_application_id ben_elig_per_f.program_application_id%TYPE,
88 prtn_end_dt ben_elig_per_f.prtn_end_dt%TYPE,
89 program_id ben_elig_per_f.program_id%TYPE,
90 request_id ben_elig_per_f.request_id%TYPE,
91 program_update_date ben_elig_per_f.program_update_date%TYPE,
92 p_datetrack_mode VARCHAR2 (100),
93 p_newly_elig BOOLEAN,
94 p_newly_inelig BOOLEAN,
95 p_first_elig BOOLEAN,
96 p_first_inelig BOOLEAN,
97 p_still_elig BOOLEAN,
98 p_still_inelig BOOLEAN
99 );
100
101 TYPE g_cache_pep_objects_rec_tab IS TABLE OF g_cache_pep_objects_rec
102 INDEX BY BINARY_INTEGER;
103
104 g_cache_pep_object g_cache_pep_objects_rec_tab;
105
106 --
107 TYPE g_cache_epo_objects_rec IS RECORD (
108 elig_per_id ben_elig_per_opt_f.elig_per_id%TYPE,
109 elig_per_opt_id ben_elig_per_opt_f.elig_per_opt_id%TYPE,
110 effective_start_date ben_elig_per_opt_f.effective_start_date%TYPE,
111 effective_end_date ben_elig_per_opt_f.effective_end_date%TYPE,
112 business_group_id ben_elig_per_opt_f.business_group_id%TYPE,
113 opt_id ben_elig_per_opt_f.opt_id%TYPE,
114 per_in_ler_id ben_elig_per_opt_f.per_in_ler_id%TYPE,
115 wait_perd_cmpltn_date ben_elig_per_opt_f.wait_perd_cmpltn_date%TYPE,
116 wait_perd_strt_dt ben_elig_per_opt_f.wait_perd_strt_dt%TYPE,
117 prtn_ovridn_flag ben_elig_per_opt_f.prtn_ovridn_flag%TYPE,
118 oipl_ordr_num ben_elig_per_opt_f.oipl_ordr_num%TYPE,
119 prtn_ovridn_thru_dt ben_elig_per_opt_f.prtn_ovridn_thru_dt%TYPE,
120 no_mx_prtn_ovrid_thru_flag ben_elig_per_opt_f.no_mx_prtn_ovrid_thru_flag%TYPE,
121 prtn_strt_dt ben_elig_per_opt_f.prtn_strt_dt%TYPE,
122 prtn_end_dt ben_elig_per_opt_f.prtn_end_dt%TYPE,
123 wait_perd_cmpltn_dt ben_elig_per_opt_f.wait_perd_cmpltn_dt%TYPE,
124 elig_flag ben_elig_per_opt_f.elig_flag%TYPE,
125 comp_ref_amt ben_elig_per_opt_f.comp_ref_amt%TYPE,
126 cmbn_age_n_los_val ben_elig_per_opt_f.cmbn_age_n_los_val%TYPE,
127 comp_ref_uom ben_elig_per_opt_f.comp_ref_uom%TYPE,
128 age_val ben_elig_per_opt_f.age_val%TYPE,
129 age_uom ben_elig_per_opt_f.age_uom%TYPE,
130 los_val ben_elig_per_opt_f.los_val%TYPE,
131 los_uom ben_elig_per_opt_f.los_uom%TYPE,
132 hrs_wkd_val ben_elig_per_opt_f.hrs_wkd_val%TYPE,
133 hrs_wkd_bndry_perd_cd ben_elig_per_opt_f.hrs_wkd_bndry_perd_cd%TYPE,
134 pct_fl_tm_val ben_elig_per_opt_f.pct_fl_tm_val%TYPE,
135 frz_los_flag ben_elig_per_opt_f.frz_los_flag%TYPE,
136 frz_age_flag ben_elig_per_opt_f.frz_age_flag%TYPE,
137 frz_cmp_lvl_flag ben_elig_per_opt_f.frz_cmp_lvl_flag%TYPE,
138 frz_pct_fl_tm_flag ben_elig_per_opt_f.frz_pct_fl_tm_flag%TYPE,
139 frz_hrs_wkd_flag ben_elig_per_opt_f.frz_hrs_wkd_flag%TYPE,
140 frz_comb_age_and_los_flag ben_elig_per_opt_f.frz_comb_age_and_los_flag%TYPE,
141 rt_comp_ref_amt ben_elig_per_opt_f.rt_comp_ref_amt%TYPE,
142 rt_cmbn_age_n_los_val ben_elig_per_opt_f.rt_cmbn_age_n_los_val%TYPE,
143 rt_comp_ref_uom ben_elig_per_opt_f.rt_comp_ref_uom%TYPE,
144 rt_age_val ben_elig_per_opt_f.rt_age_val%TYPE,
145 rt_age_uom ben_elig_per_opt_f.rt_age_uom%TYPE,
146 rt_los_val ben_elig_per_opt_f.rt_los_val%TYPE,
147 rt_los_uom ben_elig_per_opt_f.rt_los_uom%TYPE,
148 rt_hrs_wkd_val ben_elig_per_opt_f.rt_hrs_wkd_val%TYPE,
149 rt_hrs_wkd_bndry_perd_cd ben_elig_per_opt_f.rt_hrs_wkd_bndry_perd_cd%TYPE,
150 rt_pct_fl_tm_val ben_elig_per_opt_f.rt_pct_fl_tm_val%TYPE,
151 rt_frz_los_flag ben_elig_per_opt_f.rt_frz_los_flag%TYPE,
152 rt_frz_age_flag ben_elig_per_opt_f.rt_frz_age_flag%TYPE,
153 rt_frz_cmp_lvl_flag ben_elig_per_opt_f.rt_frz_cmp_lvl_flag%TYPE,
154 rt_frz_pct_fl_tm_flag ben_elig_per_opt_f.rt_frz_pct_fl_tm_flag%TYPE,
155 rt_frz_hrs_wkd_flag ben_elig_per_opt_f.rt_frz_hrs_wkd_flag%TYPE,
156 rt_frz_comb_age_and_los_flag ben_elig_per_opt_f.rt_frz_comb_age_and_los_flag%TYPE,
157 once_r_cntug_cd ben_elig_per_opt_f.once_r_cntug_cd%TYPE,
158 object_version_number ben_elig_per_opt_f.object_version_number%TYPE,
159 p_effective_date DATE,
160 p_datetrack_mode VARCHAR2 (100),
161 program_application_id ben_elig_per_opt_f.program_application_id%TYPE,
162 program_id ben_elig_per_opt_f.program_id%TYPE,
163 request_id ben_elig_per_opt_f.request_id%TYPE,
164 program_update_date ben_elig_per_opt_f.program_update_date%TYPE,
165 inelg_rsn_cd ben_elig_per_opt_f.inelg_rsn_cd%TYPE,
166 p_newly_elig BOOLEAN,
167 p_newly_inelig BOOLEAN,
168 p_first_elig BOOLEAN,
169 p_first_inelig BOOLEAN,
170 p_still_elig BOOLEAN,
171 p_still_inelig BOOLEAN,
172 p_pl_id NUMBER
173 );
174
175 --
176 TYPE g_cache_epo_objects_rec_tab IS TABLE OF g_cache_epo_objects_rec
177 INDEX BY BINARY_INTEGER;
178
179 g_cache_epo_object g_cache_epo_objects_rec_tab;
180
181 --
182 TYPE icd_chc_rates_rec IS RECORD (
183 icd_chc_rate_id NUMBER,
184 person_id NUMBER,
185 business_group_id NUMBER,
186 effective_date DATE,
187 acty_base_rt_id NUMBER,
188 pl_id NUMBER,
189 pl_typ_id NUMBER,
190 oipl_id NUMBER,
191 opt_id NUMBER,
192 pl_ordr_num NUMBER,
193 oipl_ordr_num NUMBER,
194 nnmntry_uom VARCHAR (100),
195 rt_strt_dt_cd VARCHAR (100),
196 rt_strt_dt DATE,
197 rt_strt_dt_rl NUMBER,
198 rt_end_dt_cd VARCHAR (100),
199 rt_end_dt DATE,
200 rt_end_dt_rl NUMBER,
201 bnf_rqd_yn VARCHAR (100),
202 input_value_id1 NUMBER,
203 input_value1 VARCHAR (100),
204 input_value_id2 NUMBER,
205 input_value2 VARCHAR (100),
206 input_value_id3 NUMBER,
207 input_value3 VARCHAR (100),
208 input_value_id4 NUMBER,
209 input_value4 VARCHAR (100),
210 input_value_id5 NUMBER,
211 input_value5 VARCHAR (100),
212 input_value_id6 NUMBER,
213 input_value6 VARCHAR (100),
214 input_value_id7 NUMBER,
215 input_value7 VARCHAR (100),
216 input_value_id8 NUMBER,
217 input_value8 VARCHAR (100),
218 input_value_id9 NUMBER,
219 input_value9 VARCHAR (100),
220 input_value_id10 NUMBER,
221 input_value10 VARCHAR (100),
222 input_value_id11 NUMBER,
223 input_value11 VARCHAR (100),
224 input_value_id12 NUMBER,
225 input_value12 VARCHAR (100),
226 input_value_id13 NUMBER,
227 input_value13 VARCHAR (100),
228 input_value_id14 NUMBER,
229 input_value14 VARCHAR (100),
230 input_value_id15 NUMBER,
231 input_value15 VARCHAR (100),
232 element_type_id NUMBER,
233 element_link_id NUMBER,
234 object_version_number NUMBER,
235 last_update_date DATE,
236 last_updated_by NUMBER,
237 creation_date DATE,
238 created_by NUMBER,
239 cost_allocation_keyflex_id NUMBER,
240 l_assignment_id per_all_assignments_f.assignment_id%TYPE,
241 l_level varchar2(30)
242 );
243
244 --
245 TYPE icd_chc_rates_tab IS TABLE OF icd_chc_rates_rec
246 INDEX BY BINARY_INTEGER;
247
248 --
249 PROCEDURE p_manage_icm_life_events (
250 p_person_id IN NUMBER,
251 p_effective_date IN DATE,
252 p_business_group_id IN NUMBER,
253 p_lf_evt_ocrd_dt IN DATE DEFAULT NULL
254 );
255 --
256 END ben_icm_life_events;