[Home] [Help]
PACKAGE BODY: APPS.BEN_PROCESS_COMPENSATION_W
Source
1 package body ben_process_compensation_w AS
2 /* $Header: bencmpws.pkb 120.1 2005/07/18 14:54:28 maagrawa noship $*/
3
4 -- Global variables
5 g_package constant varchar2(80):='ben_process_compensation_w.';
6
7 g_data_error exception;
8
9 type t_tx_name is table of varchar2(30) index by binary_integer;
10 type t_tx_char is table of varchar2(2000) index by binary_integer;
11 type t_tx_num is table of number index by binary_integer;
12 type t_tx_date is table of date index by binary_integer;
13 type t_tx_type is table of varchar2(30) index by binary_integer;
14
15
16 procedure get_transaction_step_info
17 (p_item_type in varchar2
18 ,p_item_key in varchar2
19 ,p_activity_id in number
20 ,p_transaction_step_id out nocopy hr_util_web.g_varchar2_tab_type
21 ,p_rows out nocopy number) is
22 --
23 cursor csr_hats is
24 select hats.transaction_step_id
25 from hr_api_transaction_steps hats
26 where hats.item_type = p_item_type
27 and hats.item_key = p_item_key
28 and hats.activity_id = p_activity_id
29 and hats.api_name = upper(g_package || 'process_api')
30 order by hats.transaction_step_id;
31 --
32 l_index number;
33 l_data csr_hats%rowtype;
34 --
35 begin
36 l_index := 0;
37 open csr_hats;
38 loop
39 fetch csr_hats into l_data;
40 exit when csr_hats%notfound;
41 p_transaction_step_id(l_index) := to_char(l_data.transaction_step_id);
42 l_index := l_index + 1;
43 end loop;
44 close csr_hats;
45 p_rows := l_index;
46 end get_transaction_step_info;
47
48 function get_transaction_step_id
49 (p_item_type in varchar2
50 ,p_item_key in varchar2
51 ,p_activity_id in number
52 ,p_elig_per_elctbl_chc_id in number) return number is
53 --
54 l_transaction_step_ids hr_util_web.g_varchar2_tab_type;
55 l_no_of_rows number := 0;
56 l_transaction_step_id number := null;
57 l_elig_per_elctbl_chc_id number;
58 --
59 begin
60 --
61 if p_elig_per_elctbl_chc_id is null then
62 return l_transaction_step_id;
63 end if;
64 --
65 -- ------------------------------------------------------------------
66 -- Check if there are any transaction rec already saved for the current
67 -- transaction. This is used for re-display the Update page when a user
68 -- clicks the Back button on the Review page to go back to the Update page
69 -- to make further changes or to correct errors.
70 -------------------------------------------------------------------------
71 --
72 get_transaction_step_info
73 (p_item_type => p_item_type
74 ,p_item_key => p_item_key
75 ,p_activity_id => p_activity_id
76 ,p_transaction_step_id => l_transaction_step_ids
77 ,p_rows => l_no_of_rows);
78 --
79 if l_no_of_rows > 0 then
80 null;
81 else
82 return l_transaction_step_id;
83 end if;
84 --
85 for i in 1..l_no_of_rows loop
86 l_elig_per_elctbl_chc_id
87 := hr_transaction_api.get_number_value
88 (p_transaction_step_id => l_transaction_step_ids(i-1)
89 ,p_name => 'P_ELIG_PER_ELCTBL_CHC_ID');
90 if p_elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id then
91 l_transaction_step_id := l_transaction_step_ids(i-1);
92 exit;
93 end if;
94 end loop;
95 --
96 return l_transaction_step_id;
97 --
98 end get_transaction_step_id;
99 --
100 procedure get_comp_data_from_tt
101 (p_transaction_step_id in number
102 ,p_column_names in varchar2
103 ,p_column_values out nocopy varchar2) is
104 --
105 l_start_point number := 1;
106 l_end_point number;
107 l_length number := length(p_column_names);
108 l_first boolean := true;
109 l_last boolean := false;
110 l_datatype varchar2(30);
111 l_number_value number;
112 l_varchar2_value varchar2(2000);
113 l_date_value date;
114 l_column_name varchar2(30);
115 l_column_value varchar2(2000);
116 --
117 begin
118 --
119 p_column_values := null;
120 --
121 if p_transaction_step_id is null then
122 return;
123 end if;
124 --
125 if l_length = 0 then
126 return;
127 end if;
128 --
129 loop
130 --
131 l_end_point := instr(p_column_names, g_column_delimiter, l_start_point);
132 --
133 if l_end_point = 0 then
134 l_last := true;
135 l_end_point := l_length+1;
136 end if;
137 --
138 l_column_name := substr(p_column_names,
139 l_start_point,
140 l_end_point-l_start_point);
141 hr_transaction_api.get_value
142 (p_transaction_step_id => p_transaction_step_id
143 ,p_name => l_column_name
144 ,p_datatype => l_datatype
145 ,p_varchar2_value => l_varchar2_value
146 ,p_number_value => l_number_value
147 ,p_date_value => l_date_value);
148 --
149 if l_datatype = 'VARCHAR2' then
150 l_column_value := l_varchar2_value;
151 elsif l_datatype = 'NUMBER' then
152 l_column_value := to_char(l_number_value);
153 elsif l_datatype = 'DATE' then
154 l_column_value := to_char(l_date_value,hr_transaction_ss.g_date_format);
155 end if;
156 --
157 if l_first then
158 p_column_values := l_column_value;
159 l_first := false;
160 else
161 p_column_values := p_column_values ||g_column_delimiter||l_column_value;
162 end if;
163 --
164 l_start_point := l_end_point + length(g_column_delimiter);
165 --
166 if l_last then
167 exit;
168 end if;
169 --
170 end loop;
171 --
172 end get_comp_data_from_tt;
173 --
174 procedure get_comp_data_from_tt
175 (p_item_type in varchar2
176 ,p_item_key in varchar2
177 ,p_elig_per_elctbl_chc_id in number
178 ,p_column_names in varchar2
179 ,p_column_values out nocopy varchar2) is
180 begin
181 --
182 get_comp_data_from_tt
183 (p_transaction_step_id =>
184 get_transaction_step_id
185 (p_item_type => p_item_type
186 ,p_item_key => p_item_key
187 ,p_activity_id => null
188 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id)
189 ,p_column_names => p_column_names
190 ,p_column_values => p_column_values);
191 --
192 end get_comp_data_from_tt;
193 --
194 -- ---------------------------------------------------------------------------
195 -- ---------------------- < get_comp_data_from_tt> -------------------------
196 -- ---------------------------------------------------------------------------
197 -- Purpose: This procedure will get transaction data which are pending for
198 -- approval in workflow for a transaction step id.
199 -- This is the procedure which does the actual work.
200 -- ---------------------------------------------------------------------------
201 procedure get_comp_data_from_tt
202 (p_transaction_step_id in number
203 ,p_elig_per_elctbl_chc_id out nocopy number
204 ,p_prtt_enrt_rslt_id out nocopy number
205 ,p_person_id out nocopy number
206 ,p_per_in_ler_id out nocopy number
207 ,p_pgm_id out nocopy number
208 ,p_pl_id out nocopy number
209 ,p_effective_date out nocopy date
210 ,p_enrt_bnft_id out nocopy number
211 ,p_bnft_amt out nocopy number
212 ,p_enrt_rt_id out nocopy number
213 ,p_prtt_rt_val_id out nocopy number
214 ,p_rt_val out nocopy number
215 ,p_datetrack_mode out nocopy varchar2
216 ,p_effective_start_date out nocopy date
217 ,p_object_version_number out nocopy number
218 ,p_business_group_id out nocopy number
219 ,p_enrt_cvg_strt_dt out nocopy date
220 ,p_enrt_cvg_thru_dt out nocopy date
221 ,p_justification out nocopy varchar2
222 ,p_pl_name out nocopy varchar2
223 ,p_frequency_meaning out nocopy varchar2
224 ,p_frequency_cd out nocopy varchar2
225 ,p_entr_rt_at_enrt_flag out nocopy varchar2
226 ,p_entr_bnft_at_enrt_flag out nocopy varchar2
227 ,p_rt_nnmntry_uom out nocopy varchar2
228 ,p_bnft_nnmntry_uom out nocopy varchar2
229 ,p_rt_uom out nocopy varchar2
230 ,p_bnft_uom out nocopy varchar2
231 ,p_rt_mn_val out nocopy number
232 ,p_rt_mx_val out nocopy number
233 ,p_bnft_mn_val out nocopy number
234 ,p_bnft_mx_val out nocopy number
235 ,p_enrt_cvg_strt_dt_cd out nocopy varchar2
236 ,p_acty_ref_perd_cd out nocopy varchar2
237 ,p_currency_cd out nocopy varchar2
238 ,p_limit_enrt_rt_id out nocopy number
239 ,p_limit_prtt_rt_val_id out nocopy number
240 ,p_limit_rt_val out nocopy number
241 ,p_limit_entr_rt_at_enrt_flag out nocopy varchar2
242 ,p_pl_typ_id out nocopy number
243 ,p_ler_id out nocopy number
244 ,p_limit_dsply_on_enrt_flag out nocopy varchar2
245 ,p_currency_symbol out nocopy varchar2
246 ,p_rt_strt_dt out nocopy date
247 ,p_rt_end_dt out nocopy date
248 ,p_rt_strt_dt_cd out nocopy varchar2
249 ,p_rt_end_dt_cd out nocopy varchar2
250 ,p_rslt_bnft_amt out nocopy number
251 ,p_rtval_rt_end_dt out nocopy date
252 ,p_rtval_rt_val out nocopy number
253 ,p_rtval_limit_rt_val out nocopy number
254 ,p_bnft_typ_meaning out nocopy varchar2
255 ,p_ctfn_names out nocopy varchar2
256 ,p_rt_update_mode out nocopy varchar2
257 ,p_rtval_rt_strt_dt out nocopy date
258 ,p_nip_pl_uom out nocopy varchar2) is
259 --
260 cursor c_txn_values is
261 select txn.name
262 ,txn.varchar2_value
263 ,txn.number_value
264 ,txn.date_value
265 from hr_api_transaction_values txn
266 where txn.transaction_step_id = p_transaction_step_id
267 and (txn.varchar2_value is not null or
268 txn.number_value is not null or
269 txn.date_value is not null);
270 --
271 begin
272 --
273 if p_transaction_step_id is null then
274 return;
275 end if;
276 --
277 for l_txn_values in c_txn_values loop
278 --
279 if l_txn_values.name = 'P_ELIG_PER_ELCTBL_CHC_ID' then
280 p_elig_per_elctbl_chc_id := l_txn_values.number_value;
281 elsif l_txn_values.name = 'P_PRTT_ENRT_RSLT_ID' then
282 p_prtt_enrt_rslt_id := l_txn_values.number_value;
283 elsif l_txn_values.name = 'P_PERSON_ID' then
284 p_person_id := l_txn_values.number_value;
285 elsif l_txn_values.name = 'P_PER_IN_LER_ID' then
286 p_per_in_ler_id := l_txn_values.number_value;
287 elsif l_txn_values.name = 'P_PGM_ID' then
288 p_pgm_id := l_txn_values.number_value;
289 elsif l_txn_values.name = 'P_PL_ID' then
290 p_pl_id := l_txn_values.number_value;
291 elsif l_txn_values.name = 'P_EFFECTIVE_DATE' then
292 p_effective_date := l_txn_values.date_value;
293 elsif l_txn_values.name = 'P_ENRT_BNFT_ID' then
294 p_enrt_bnft_id := l_txn_values.number_value;
295 elsif l_txn_values.name = 'P_BNFT_AMT' then
296 p_bnft_amt := l_txn_values.number_value;
297 elsif l_txn_values.name = 'P_ENRT_RT_ID' then
298 p_enrt_rt_id := l_txn_values.number_value;
299 elsif l_txn_values.name = 'P_PRTT_RT_VAL_ID' then
300 p_prtt_rt_val_id := l_txn_values.number_value;
301 elsif l_txn_values.name = 'P_RT_VAL' then
302 p_rt_val := l_txn_values.number_value;
303 elsif l_txn_values.name = 'P_DATETRACK_MODE' then
304 p_datetrack_mode := l_txn_values.varchar2_value;
305 elsif l_txn_values.name = 'P_EFFECTIVE_START_DATE' then
306 p_effective_start_date := l_txn_values.date_value;
307 elsif l_txn_values.name = 'P_OBJECT_VERSION_NUMBER' then
308 p_object_version_number := l_txn_values.number_value;
309 elsif l_txn_values.name = 'P_BUSINESS_GROUP_ID' then
310 p_business_group_id := l_txn_values.number_value;
311 elsif l_txn_values.name = 'P_ENRT_CVG_STRT_DT' then
312 p_enrt_cvg_strt_dt := l_txn_values.date_value;
313 elsif l_txn_values.name = 'P_ENRT_CVG_THRU_DT' then
314 p_enrt_cvg_thru_dt := l_txn_values.date_value;
315 elsif l_txn_values.name = 'P_JUSTIFICATION' then
316 p_justification := l_txn_values.varchar2_value;
317 elsif l_txn_values.name = 'P_PL_NAME' then
318 p_pl_name := l_txn_values.varchar2_value;
319 elsif l_txn_values.name = 'P_FREQUENCY_MEANING' then
320 p_frequency_meaning := l_txn_values.varchar2_value;
321 elsif l_txn_values.name = 'P_FREQUENCY_CD' then
322 p_frequency_cd := l_txn_values.varchar2_value;
323 elsif l_txn_values.name = 'P_ENTR_RT_AT_ENRT_FLAG' then
324 p_entr_rt_at_enrt_flag := l_txn_values.varchar2_value;
325 elsif l_txn_values.name = 'P_ENTR_BNFT_AT_ENRT_FLAG' then
326 p_entr_bnft_at_enrt_flag := l_txn_values.varchar2_value;
327 elsif l_txn_values.name = 'P_RT_NNMNTRY_UOM' then
328 p_rt_nnmntry_uom := l_txn_values.varchar2_value;
329 elsif l_txn_values.name = 'P_BNFT_NNMNTRY_UOM' then
330 p_bnft_nnmntry_uom := l_txn_values.varchar2_value;
331 elsif l_txn_values.name = 'P_RT_UOM' then
332 p_rt_uom := l_txn_values.varchar2_value;
333 elsif l_txn_values.name = 'P_BNFT_UOM' then
334 p_bnft_uom := l_txn_values.varchar2_value;
335 elsif l_txn_values.name = 'P_RT_MN_VAL' then
336 p_rt_mn_val := l_txn_values.number_value;
337 elsif l_txn_values.name = 'P_RT_MX_VAL' then
338 p_rt_mx_val := l_txn_values.number_value;
339 elsif l_txn_values.name = 'P_BNFT_MN_VAL' then
340 p_bnft_mn_val := l_txn_values.number_value;
341 elsif l_txn_values.name = 'P_BNFT_MX_VAL' then
342 p_bnft_mx_val := l_txn_values.number_value;
343 elsif l_txn_values.name = 'P_ENRT_CVG_STRT_DT_CD' then
344 p_enrt_cvg_strt_dt_cd := l_txn_values.varchar2_value;
345 elsif l_txn_values.name = 'P_ACTY_REF_PERD_CD' then
346 p_acty_ref_perd_cd := l_txn_values.varchar2_value;
347 elsif l_txn_values.name = 'P_CURRENCY_CD' then
348 p_currency_cd := l_txn_values.varchar2_value;
349 elsif l_txn_values.name = 'P_LIMIT_ENRT_RT_ID' then
350 p_limit_enrt_rt_id := l_txn_values.number_value;
351 elsif l_txn_values.name = 'P_LIMIT_PRTT_RT_VAL_ID' then
352 p_limit_prtt_rt_val_id := l_txn_values.number_value;
353 elsif l_txn_values.name = 'P_LIMIT_RT_VAL' then
354 p_limit_rt_val := l_txn_values.number_value;
355 elsif l_txn_values.name = 'P_LIMIT_ENTR_RT_AT_ENRT_FLAG' then
356 p_limit_entr_rt_at_enrt_flag := l_txn_values.varchar2_value;
357 elsif l_txn_values.name = 'P_PL_TYP_ID' then
358 p_pl_typ_id := l_txn_values.number_value;
359 elsif l_txn_values.name = 'P_LER_ID' then
360 p_ler_id := l_txn_values.number_value;
361 elsif l_txn_values.name = 'P_LIMIT_DSPLY_ON_ENRT_FLAG' then
362 p_limit_dsply_on_enrt_flag := l_txn_values.varchar2_value;
363 elsif l_txn_values.name = 'P_CURRENCY_SYMBOL' then
364 p_currency_symbol := l_txn_values.varchar2_value;
365 elsif l_txn_values.name = 'P_RT_STRT_DT_CD' then
366 p_rt_strt_dt_cd := l_txn_values.varchar2_value;
367 elsif l_txn_values.name = 'P_RT_END_DT_CD' then
368 p_rt_end_dt_cd := l_txn_values.varchar2_value;
369 elsif l_txn_values.name = 'P_RT_STRT_DT' then
370 p_rt_strt_dt := l_txn_values.date_value;
371 elsif l_txn_values.name = 'P_RT_END_DT' then
372 p_rt_end_dt := l_txn_values.date_value;
373 elsif l_txn_values.name = 'P_RSLT_BNFT_AMT' then
374 p_rslt_bnft_amt := l_txn_values.number_value;
375 elsif l_txn_values.name = 'P_RTVAL_RT_END_DT' then
376 p_rtval_rt_end_dt := l_txn_values.date_value;
377 elsif l_txn_values.name = 'P_RTVAL_RT_VAL' then
378 p_rtval_rt_val := l_txn_values.number_value;
379 elsif l_txn_values.name = 'P_RTVAL_LIMIT_RT_VAL' then
380 p_rtval_limit_rt_val := l_txn_values.number_value;
381 elsif l_txn_values.name = 'P_BNFT_TYP_MEANING' then
382 p_bnft_typ_meaning := l_txn_values.varchar2_value;
383 elsif l_txn_values.name = 'P_CTFN_NAMES' then
384 p_ctfn_names := l_txn_values.varchar2_value;
385 elsif l_txn_values.name = 'P_RT_UPDATE_MODE' then
386 p_rt_update_mode := l_txn_values.varchar2_value;
387 elsif l_txn_values.name = 'P_RTVAL_RT_STRT_DT' then
388 p_rtval_rt_strt_dt := l_txn_values.date_value;
389 elsif l_txn_values.name = 'P_NIP_PL_UOM' then
390 p_nip_pl_uom := l_txn_values.varchar2_value;
391 end if;
392
393 --
394 end loop;
395 --
396 end get_comp_data_from_tt;
397 --
398 procedure clear_enroll_caches
399 is
400 begin
401 --
402 ben_letrg_cache.clear_down_cache;
403 ben_batch_dt_api.clear_down_cache;
404 ben_cobj_cache.clear_down_cache;
405 ben_element_entry.clear_down_cache;
406 --
407 end clear_enroll_caches;
408 --
409 procedure election_information_w
410 (p_elig_per_elctbl_chc_id in number
411 ,p_prtt_enrt_rslt_id in number
412 ,p_effective_date in date
413 ,p_person_id in number
414 ,p_enrt_bnft_id in number
415 ,p_bnft_amt in number
416 ,p_enrt_rt_id in number
417 ,p_prtt_rt_val_id in number
418 ,p_rt_val in number
419 ,p_datetrack_mode in varchar2
420 ,p_effective_start_date in date
421 ,p_object_version_number in number
422 ,p_business_group_id in number
423 ,p_enrt_cvg_strt_dt in date
424 ,p_enrt_cvg_thru_dt in date
425 ,p_rt_strt_dt in date
426 ,p_rt_end_dt in date
427 ,p_rt_strt_dt_cd in varchar2
428 ,p_limit_enrt_rt_id in number
429 ,p_limit_prtt_rt_val_id in number
430 ,p_limit_rt_val in number
431 ,p_rt_update_mode in varchar2
432 ,p_api_error out nocopy boolean) is
433 --
434 l_return_status varchar2(30);
435 begin
436 --
437 p_api_error := false;
438 --
439 ben_election_information.election_information_w
440 (p_validate => 'N'
441 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
442 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
443 ,p_effective_date => p_effective_date
444 ,p_person_id => p_person_id
445 ,p_enrt_mthd_cd => 'E'
446 ,p_enrt_bnft_id => p_enrt_bnft_id
447 ,p_bnft_val => p_bnft_amt
448 ,p_enrt_rt_id => p_enrt_rt_id
449 ,p_prtt_rt_val_id => p_prtt_rt_val_id
450 ,p_rt_val => p_rt_val
451 ,p_ann_rt_val => null
452 ,p_rt_strt_dt1 => p_rt_strt_dt
453 ,p_rt_end_dt1 => p_rt_end_dt
454 ,p_rt_strt_dt_cd1 => p_rt_strt_dt_cd
455 ,p_enrt_rt_id2 => p_limit_enrt_rt_id
456 ,p_prtt_rt_val_id2 => p_limit_prtt_rt_val_id
457 ,p_rt_val2 => p_limit_rt_val
458 ,p_ann_rt_val2 => null
459 ,p_prtt_rt_val_id3 => null
460 ,p_prtt_rt_val_id4 => null
461 ,p_datetrack_mode => p_datetrack_mode
462 ,p_suspend_flag => 'N'
463 ,p_effective_start_date => p_effective_start_date
464 ,p_object_version_number => p_object_version_number
465 ,p_business_group_id => p_business_group_id
466 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
467 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
468 ,p_rt_update_mode => p_rt_update_mode
469 ,p_return_status => l_return_status);
470 --
471 if l_return_status = 'E' then
472 p_api_error := true;
473 end if;
474 --
475 end election_information_w;
476 --
477 --
478 procedure validate_current_comp_details
479 (p_elig_per_elctbl_chc_id in number
480 ,p_prtt_enrt_rslt_id in number
481 ,p_person_id in number
482 ,p_per_in_ler_id in number
483 ,p_pgm_id in number
484 ,p_pl_id in number
485 ,p_effective_date in date
486 ,p_enrt_bnft_id in number
487 ,p_bnft_amt in number
488 ,p_enrt_rt_id in number
489 ,p_prtt_rt_val_id in number
490 ,p_rt_val in number
491 ,p_datetrack_mode in varchar2
492 ,p_effective_start_date in date
493 ,p_object_version_number in number
494 ,p_business_group_id in number
495 ,p_enrt_cvg_strt_dt in date
496 ,p_enrt_cvg_thru_dt in date
497 ,p_rt_strt_dt in date
498 ,p_rt_end_dt in date
499 ,p_rt_strt_dt_cd in varchar2
500 ,p_limit_enrt_rt_id in number
501 ,p_limit_prtt_rt_val_id in number
502 ,p_limit_rt_val in number
503 ,p_rt_update_mode in varchar2
504 ,p_api_error out nocopy boolean) is
505 --
506 begin
507 --
508 election_information_w
509 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
510 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
511 ,p_effective_date => p_effective_date
512 ,p_person_id => p_person_id
513 ,p_enrt_bnft_id => p_enrt_bnft_id
514 ,p_bnft_amt => p_bnft_amt
515 ,p_enrt_rt_id => p_enrt_rt_id
516 ,p_prtt_rt_val_id => p_prtt_rt_val_id
517 ,p_rt_val => p_rt_val
518 ,p_datetrack_mode => p_datetrack_mode
519 ,p_effective_start_date => p_effective_start_date
520 ,p_object_version_number => p_object_version_number
521 ,p_business_group_id => p_business_group_id
522 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
523 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
524 ,p_rt_strt_dt => p_rt_strt_dt
525 ,p_rt_end_dt => p_rt_end_dt
526 ,p_rt_strt_dt_cd => p_rt_strt_dt_cd
527 ,p_limit_enrt_rt_id => p_limit_enrt_rt_id
528 ,p_limit_prtt_rt_val_id => p_limit_prtt_rt_val_id
529 ,p_limit_rt_val => p_limit_rt_val
530 ,p_rt_update_mode => p_rt_update_mode
531 ,p_api_error => p_api_error
532 );
533 --
534 if not p_api_error then
535 --
536 ben_proc_common_enrt_rslt.process_post_enrt_calls_w
537 (p_validate => 'N'
538 ,p_person_id => p_person_id
539 ,p_per_in_ler_id => p_per_in_ler_id
540 ,p_pgm_id => p_pgm_id
541 ,p_pl_id => p_pl_id
542 ,p_flx_cr_flag => 'N'
543 ,p_enrt_mthd_cd => 'E'
544 ,p_proc_cd => 'WEBENRT'
545 ,p_cls_enrt_flag => 'N'
546 ,p_business_group_id => p_business_group_id
547 ,p_effective_date => p_effective_date);
548 --
549 end if;
550 --
551 end validate_current_comp_details;
552 --
553 -- ---------------------------------------------------------------------------
554 -- ---------------------- < validate_comp_details> ---------------------------
555 -- ---------------------------------------------------------------------------
556 -- Purpose: This procedure will perform field validation and then call the api.
557 -- This procedure is invoked from Update Basic Details page.
558 -- ---------------------------------------------------------------------------
559 procedure validate_comp_details
560 (p_item_type in varchar2
561 ,p_item_key in varchar2
562 ,p_activity_id in number
563 ,p_elig_per_elctbl_chc_id in number
564 ,p_prtt_enrt_rslt_id in number
565 ,p_person_id in number
566 ,p_per_in_ler_id in number
567 ,p_pgm_id in number
568 ,p_pl_id in number
569 ,p_effective_date in date
570 ,p_enrt_bnft_id in number
571 ,p_bnft_amt in number
572 ,p_enrt_rt_id in number
573 ,p_prtt_rt_val_id in number
574 ,p_rt_val in number
575 ,p_datetrack_mode in varchar2
576 ,p_effective_start_date in date
577 ,p_object_version_number in number
578 ,p_business_group_id in number
579 ,p_enrt_cvg_strt_dt in date
580 ,p_enrt_cvg_thru_dt in date
581 ,p_rt_strt_dt in date
582 ,p_rt_end_dt in date
583 ,p_rt_strt_dt_cd in varchar2
584 ,p_limit_enrt_rt_id in number
585 ,p_limit_prtt_rt_val_id in number
586 ,p_limit_rt_val in number
587 ,p_rt_update_mode in varchar2
588 ,p_api_error out nocopy boolean) is
589 --
590 l_elig_per_elctbl_chc_id number;
591 l_prtt_enrt_rslt_id number;
592 l_person_id number;
593 l_per_in_ler_id number;
594 l_pgm_id number;
595 l_pl_id number;
596 l_effective_date date;
597 l_enrt_bnft_id number;
598 l_bnft_amt number;
599 l_enrt_rt_id number;
600 l_prtt_rt_val_id number;
601 l_rt_val number;
602 l_datetrack_mode varchar2(30);
603 l_effective_start_date date;
604 l_object_version_number number;
605 l_business_group_id number;
606 l_enrt_cvg_strt_dt date;
607 l_enrt_cvg_thru_dt date;
608 l_justification varchar2(2000);
609 l_pl_name varchar2(481);
610 l_frequency_meaning varchar2(80);
611 l_frequency_cd varchar2(30);
612 l_entr_rt_at_enrt_flag varchar2(30);
613 l_entr_bnft_at_enrt_flag varchar2(30);
614 l_rt_nnmntry_uom varchar2(30);
615 l_bnft_nnmntry_uom varchar2(30);
616 l_rt_uom varchar2(80);
617 l_bnft_uom varchar2(80);
618 l_rt_mn_val number;
619 l_rt_mx_val number;
620 l_bnft_mn_val number;
621 l_bnft_mx_val number;
622 l_enrt_cvg_strt_dt_cd varchar2(30);
623 l_acty_ref_perd_cd varchar2(80);
624 l_currency_cd varchar2(80);
625 l_limit_enrt_rt_id number;
626 l_limit_prtt_rt_val_id number;
627 l_limit_rt_val number;
628 l_limit_entr_rt_at_enrt_flag varchar2(30);
629 l_pl_typ_id number;
630 l_ler_id number;
631 l_limit_dsply_on_enrt_flag varchar2(30);
632 l_currency_symbol varchar2(30);
633 l_rt_strt_dt date;
634 l_rt_end_dt date;
635 l_rt_strt_dt_cd varchar2(30);
636 l_rt_end_dt_cd varchar2(30);
637 l_rslt_bnft_amt number;
638 l_rtval_rt_end_dt date;
639 l_rtval_rt_val number;
640 l_rtval_limit_rt_val number;
641 l_bnft_typ_meaning varchar2(80);
642 l_ctfn_names varchar2(2000);
643 l_rt_update_mode varchar2(30);
644 l_rtval_rt_strt_dt date;
645 l_nip_pl_uom varchar2(30);
646 --
647 l_transaction_step_ids hr_util_web.g_varchar2_tab_type;
648 l_no_of_rows number := 0;
649 --
650 begin
651 --
652 savepoint validate_comp_details;
653 --
654 p_api_error := false;
655 --
656 get_transaction_step_info
657 (p_item_type => p_item_type
658 ,p_item_key => p_item_key
659 ,p_activity_id => p_activity_id
660 ,p_transaction_step_id => l_transaction_step_ids
661 ,p_rows => l_no_of_rows);
662 --
663 for i in 0..(l_no_of_rows-1) loop
664 get_comp_data_from_tt(
665 p_transaction_step_id => l_transaction_step_ids(i)
666 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
667 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
668 ,p_person_id => l_person_id
669 ,p_per_in_ler_id => l_per_in_ler_id
670 ,p_pgm_id => l_pgm_id
671 ,p_pl_id => l_pl_id
672 ,p_effective_date => l_effective_date
673 ,p_enrt_bnft_id => l_enrt_bnft_id
674 ,p_bnft_amt => l_bnft_amt
675 ,p_enrt_rt_id => l_enrt_rt_id
676 ,p_prtt_rt_val_id => l_prtt_rt_val_id
677 ,p_rt_val => l_rt_val
678 ,p_datetrack_mode => l_datetrack_mode
679 ,p_effective_start_date => l_effective_start_date
680 ,p_object_version_number => l_object_version_number
681 ,p_business_group_id => l_business_group_id
682 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
683 ,p_enrt_cvg_thru_dt => l_enrt_cvg_thru_dt
684 ,p_justification => l_justification
685 ,p_pl_name => l_pl_name
686 ,p_frequency_meaning => l_frequency_meaning
687 ,p_frequency_cd => l_frequency_cd
688 ,p_entr_rt_at_enrt_flag => l_entr_rt_at_enrt_flag
689 ,p_entr_bnft_at_enrt_flag => l_entr_bnft_at_enrt_flag
690 ,p_rt_nnmntry_uom => l_rt_nnmntry_uom
691 ,p_bnft_nnmntry_uom => l_bnft_nnmntry_uom
692 ,p_rt_uom => l_rt_uom
693 ,p_bnft_uom => l_bnft_uom
694 ,p_rt_mn_val => l_rt_mn_val
695 ,p_rt_mx_val => l_rt_mx_val
696 ,p_bnft_mn_val => l_bnft_mn_val
697 ,p_bnft_mx_val => l_bnft_mx_val
698 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd
699 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
700 ,p_currency_cd => l_currency_cd
701 ,p_limit_enrt_rt_id => l_limit_enrt_rt_id
702 ,p_limit_prtt_rt_val_id => l_limit_prtt_rt_val_id
703 ,p_limit_rt_val => l_limit_rt_val
704 ,p_limit_entr_rt_at_enrt_flag => l_limit_entr_rt_at_enrt_flag
705 ,p_pl_typ_id => l_pl_typ_id
706 ,p_ler_id => l_ler_id
707 ,p_limit_dsply_on_enrt_flag => l_limit_dsply_on_enrt_flag
708 ,p_currency_symbol => l_currency_symbol
709 ,p_rt_strt_dt => l_rt_strt_dt
710 ,p_rt_end_dt => l_rt_end_dt
711 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
712 ,p_rt_end_dt_cd => l_rt_end_dt_cd
713 ,p_rslt_bnft_amt => l_rslt_bnft_amt
714 ,p_rtval_rt_end_dt => l_rtval_rt_end_dt
715 ,p_rtval_rt_val => l_rtval_rt_val
716 ,p_rtval_limit_rt_val => l_rtval_limit_rt_val
717 ,p_bnft_typ_meaning => l_bnft_typ_meaning
718 ,p_ctfn_names => l_ctfn_names
719 ,p_rt_update_mode => l_rt_update_mode
720 ,p_rtval_rt_strt_dt => l_rtval_rt_strt_dt
721 ,p_nip_pl_uom => l_nip_pl_uom);
722 --
723 if l_elig_per_elctbl_chc_id <> p_elig_per_elctbl_chc_id then
724 election_information_w
725 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
726 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
727 ,p_effective_date => l_effective_date
728 ,p_person_id => p_person_id
729 ,p_enrt_bnft_id => l_enrt_bnft_id
730 ,p_bnft_amt => l_bnft_amt
731 ,p_enrt_rt_id => l_enrt_rt_id
732 ,p_prtt_rt_val_id => l_prtt_rt_val_id
733 ,p_rt_val => l_rt_val
734 ,p_datetrack_mode => l_datetrack_mode
735 ,p_effective_start_date => l_effective_start_date
736 ,p_object_version_number => l_object_version_number
737 ,p_business_group_id => l_business_group_id
738 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
739 ,p_enrt_cvg_thru_dt => l_enrt_cvg_thru_dt
740 ,p_rt_strt_dt => l_rt_strt_dt
741 ,p_rt_end_dt => l_rt_end_dt
742 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
743 ,p_limit_enrt_rt_id => l_limit_enrt_rt_id
744 ,p_limit_prtt_rt_val_id => l_limit_prtt_rt_val_id
745 ,p_limit_rt_val => l_limit_rt_val
746 ,p_rt_update_mode => l_rt_update_mode
747 ,p_api_error => p_api_error);
748 --
749 end if;
750
751 if p_api_error then
752 exit;
753 end if;
754 --
755 end loop;
756 --
757 if not p_api_error then
758 --
759 validate_current_comp_details
760 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
761 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
762 ,p_person_id => p_person_id
763 ,p_per_in_ler_id => p_per_in_ler_id
764 ,p_pgm_id => p_pgm_id
765 ,p_pl_id => p_pl_id
766 ,p_effective_date => p_effective_date
767 ,p_enrt_bnft_id => p_enrt_bnft_id
768 ,p_bnft_amt => p_bnft_amt
769 ,p_enrt_rt_id => p_enrt_rt_id
770 ,p_prtt_rt_val_id => p_prtt_rt_val_id
771 ,p_rt_val => p_rt_val
772 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
773 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
774 ,p_rt_strt_dt => p_rt_strt_dt
775 ,p_rt_end_dt => p_rt_end_dt
776 ,p_rt_strt_dt_cd => p_rt_strt_dt_cd
777 ,p_datetrack_mode => p_datetrack_mode
778 ,p_effective_start_date => p_effective_start_date
779 ,p_object_version_number => p_object_version_number
780 ,p_business_group_id => p_business_group_id
781 ,p_limit_enrt_rt_id => p_limit_enrt_rt_id
782 ,p_limit_prtt_rt_val_id => p_limit_prtt_rt_val_id
783 ,p_limit_rt_val => p_limit_rt_val
784 ,p_rt_update_mode => p_rt_update_mode
785 ,p_api_error => p_api_error);
786 --
787 end if;
788 --
789 rollback to validate_comp_details;
790 --
791 end validate_comp_details;
792 --
793 --
794 -- ---------------------------------------------------------------------------
795 -- ----------------------- < get_comp_data_from_tt> --------------------------
796 -- ---------------------------------------------------------------------------
797 -- Purpose: This procedure will get transaction data which are saved earlier
798 -- in the current transaction. This is invoked when a user click BACK
799 -- button to go back from the Review page to Update page to correct
800 -- typos or make further changes. Hence, we need to use the item_type
801 -- item_key passed in to retrieve the transaction record.
802 -- This is an overloaded version.
803 -- ---------------------------------------------------------------------------
804 procedure get_comp_data_from_tt
805 (p_item_type in varchar2
806 ,p_item_key in varchar2
807 ,p_activity_id in number
808 ,p_elig_per_elctbl_chc_id in out nocopy number
809 ,p_prtt_enrt_rslt_id out nocopy number
810 ,p_person_id out nocopy number
811 ,p_per_in_ler_id out nocopy number
812 ,p_pgm_id out nocopy number
813 ,p_pl_id out nocopy number
814 ,p_effective_date out nocopy date
815 ,p_enrt_bnft_id out nocopy number
816 ,p_bnft_amt out nocopy number
817 ,p_enrt_rt_id out nocopy number
818 ,p_prtt_rt_val_id out nocopy number
819 ,p_rt_val out nocopy number
820 ,p_datetrack_mode out nocopy varchar2
821 ,p_effective_start_date out nocopy date
822 ,p_object_version_number out nocopy number
823 ,p_business_group_id out nocopy number
824 ,p_enrt_cvg_strt_dt out nocopy date
825 ,p_enrt_cvg_thru_dt out nocopy date
826 ,p_justification out nocopy varchar2
827 ,p_pl_name out nocopy varchar2
828 ,p_frequency_meaning out nocopy varchar2
829 ,p_frequency_cd out nocopy varchar2
830 ,p_entr_rt_at_enrt_flag out nocopy varchar2
831 ,p_entr_bnft_at_enrt_flag out nocopy varchar2
832 ,p_rt_nnmntry_uom out nocopy varchar2
833 ,p_bnft_nnmntry_uom out nocopy varchar2
834 ,p_rt_uom out nocopy varchar2
835 ,p_bnft_uom out nocopy varchar2
836 ,p_rt_mn_val out nocopy number
837 ,p_rt_mx_val out nocopy number
838 ,p_bnft_mn_val out nocopy number
839 ,p_bnft_mx_val out nocopy number
840 ,p_enrt_cvg_strt_dt_cd out nocopy varchar2
841 ,p_acty_ref_perd_cd out nocopy varchar2
842 ,p_currency_cd out nocopy varchar2
843 ,p_limit_enrt_rt_id out nocopy number
844 ,p_limit_prtt_rt_val_id out nocopy number
845 ,p_limit_rt_val out nocopy number
846 ,p_limit_entr_rt_at_enrt_flag out nocopy varchar2
847 ,p_pl_typ_id out nocopy number
848 ,p_ler_id out nocopy number
849 ,p_limit_dsply_on_enrt_flag out nocopy varchar2
850 ,p_currency_symbol out nocopy varchar2
851 ,p_rt_strt_dt out nocopy date
852 ,p_rt_end_dt out nocopy date
853 ,p_rt_strt_dt_cd out nocopy varchar2
854 ,p_rt_end_dt_cd out nocopy varchar2
855 ,p_rslt_bnft_amt out nocopy number
856 ,p_rtval_rt_end_dt out nocopy date
857 ,p_rtval_rt_val out nocopy number
858 ,p_rtval_limit_rt_val out nocopy number
859 ,p_bnft_typ_meaning out nocopy varchar2
860 ,p_ctfn_names out nocopy varchar2
861 ,p_rt_update_mode out nocopy varchar2
862 ,p_rtval_rt_strt_dt out nocopy date
863 ,p_nip_pl_uom out nocopy varchar2) is
864 --
865 l_transaction_step_id number := null;
866 --
867 begin
868 --
869 -- ------------------------------------------------------------------
870 -- Check if there are any transaction rec already saved for the current
871 -- choice record in the current workflow instance.
872 -----------------------------------------------------------------------------
873 --
874 l_transaction_step_id := get_transaction_step_id
875 (p_item_type => p_item_type
876 ,p_item_key => p_item_key
877 ,p_activity_id => p_activity_id
878 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id);
879 --
880 -- -------------------------------------------------------------------
881 -- There are some changes made earlier in the transaction.
882 -- Retrieve the data and return to caller.
883 -- -------------------------------------------------------------------
884 --
885 -- Now get the transaction data for the given step
886 --
887 get_comp_data_from_tt(
888 p_transaction_step_id => l_transaction_step_id
889 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
890 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
891 ,p_person_id => p_person_id
892 ,p_per_in_ler_id => p_per_in_ler_id
893 ,p_pgm_id => p_pgm_id
894 ,p_pl_id => p_pl_id
895 ,p_effective_date => p_effective_date
896 ,p_enrt_bnft_id => p_enrt_bnft_id
897 ,p_bnft_amt => p_bnft_amt
898 ,p_enrt_rt_id => p_enrt_rt_id
899 ,p_prtt_rt_val_id => p_prtt_rt_val_id
900 ,p_rt_val => p_rt_val
901 ,p_datetrack_mode => p_datetrack_mode
902 ,p_effective_start_date => p_effective_start_date
903 ,p_object_version_number => p_object_version_number
904 ,p_business_group_id => p_business_group_id
905 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
906 ,p_enrt_cvg_thru_dt => p_enrt_cvg_thru_dt
907 ,p_justification => p_justification
908 ,p_pl_name => p_pl_name
909 ,p_frequency_meaning => p_frequency_meaning
910 ,p_frequency_cd => p_frequency_cd
911 ,p_entr_rt_at_enrt_flag => p_entr_rt_at_enrt_flag
912 ,p_entr_bnft_at_enrt_flag => p_entr_bnft_at_enrt_flag
913 ,p_rt_nnmntry_uom => p_rt_nnmntry_uom
914 ,p_bnft_nnmntry_uom => p_bnft_nnmntry_uom
915 ,p_rt_uom => p_rt_uom
916 ,p_bnft_uom => p_bnft_uom
917 ,p_rt_mn_val => p_rt_mn_val
918 ,p_rt_mx_val => p_rt_mx_val
919 ,p_bnft_mn_val => p_bnft_mn_val
920 ,p_bnft_mx_val => p_bnft_mx_val
921 ,p_enrt_cvg_strt_dt_cd => p_enrt_cvg_strt_dt_cd
922 ,p_acty_ref_perd_cd => p_acty_ref_perd_cd
923 ,p_currency_cd => p_currency_cd
924 ,p_limit_enrt_rt_id => p_limit_enrt_rt_id
925 ,p_limit_prtt_rt_val_id => p_limit_prtt_rt_val_id
926 ,p_limit_rt_val => p_limit_rt_val
927 ,p_limit_entr_rt_at_enrt_flag => p_limit_entr_rt_at_enrt_flag
928 ,p_pl_typ_id => p_pl_typ_id
929 ,p_ler_id => p_ler_id
930 ,p_limit_dsply_on_enrt_flag => p_limit_dsply_on_enrt_flag
931 ,p_currency_symbol => p_currency_symbol
932 ,p_rt_strt_dt => p_rt_strt_dt
933 ,p_rt_end_dt => p_rt_end_dt
934 ,p_rt_strt_dt_cd => p_rt_strt_dt_cd
935 ,p_rt_end_dt_cd => p_rt_end_dt_cd
936 ,p_rslt_bnft_amt => p_rslt_bnft_amt
937 ,p_rtval_rt_end_dt => p_rtval_rt_end_dt
938 ,p_rtval_rt_val => p_rtval_rt_val
939 ,p_rtval_limit_rt_val => p_rtval_limit_rt_val
940 ,p_bnft_typ_meaning => p_bnft_typ_meaning
941 ,p_ctfn_names => p_ctfn_names
942 ,p_rt_update_mode => p_rt_update_mode
943 ,p_rtval_rt_strt_dt => p_rtval_rt_strt_dt
944 ,p_nip_pl_uom => p_nip_pl_uom);
945 --
946 end get_comp_data_from_tt;
947 --
948 -- ---------------------------------------------------------------------------
949 -- ------------------------- < update_compensation > -------------------------
950 -- ---------------------------------------------------------------------------
951 -- Purpose: This procedure will perform validations when a user presses Next
952 -- on Update Plan Details entry page or on the Review page.
953 -- Either case, the data will be saved to the transaction table.
954 -- If this procedure is invoked from Review page, it will first check
955 -- that if a transaction already exists. If it does, it will update
956 -- the current transaction record.
957 -- NOTE: The p_validate_mode cannot be in boolean because this
958 -- procedure will be called from Java which has a different
959 -- boolean value from pl/sql.
960 -- ---------------------------------------------------------------------------
961 procedure update_compensation
962 (p_item_type in varchar2
963 ,p_item_key in varchar2
964 ,p_actid in number
965 ,p_login_person_id in number
966 ,p_process_section_name in varchar2
967 ,p_review_page_region_code in varchar2
968 ,p_elig_per_elctbl_chc_id in number
969 ,p_prtt_enrt_rslt_id in number
970 ,p_person_id in number
971 ,p_per_in_ler_id in number
972 ,p_pgm_id in number
973 ,p_pl_id in number
974 ,p_effective_date in date
975 ,p_enrt_bnft_id in number
976 ,p_bnft_amt in number
977 ,p_enrt_rt_id in number
978 ,p_prtt_rt_val_id in number
979 ,p_rt_val in number
980 ,p_datetrack_mode in varchar2
981 ,p_effective_start_date in date
982 ,p_object_version_number in number
983 ,p_business_group_id in number
984 ,p_enrt_cvg_strt_dt in date
985 ,p_enrt_cvg_thru_dt in date
986 ,p_justification in varchar2
987 ,p_pl_name in varchar2
988 ,p_frequency_meaning in varchar2
989 ,p_frequency_cd in varchar2
990 ,p_entr_rt_at_enrt_flag in varchar2
991 ,p_entr_bnft_at_enrt_flag in varchar2
992 ,p_rt_nnmntry_uom in varchar2
993 ,p_bnft_nnmntry_uom in varchar2
994 ,p_rt_uom in varchar2
995 ,p_bnft_uom in varchar2
996 ,p_rt_mn_val in number
997 ,p_rt_mx_val in number
998 ,p_bnft_mn_val in number
999 ,p_bnft_mx_val in number
1000 ,p_enrt_cvg_strt_dt_cd in varchar2
1001 ,p_acty_ref_perd_cd in varchar2
1002 ,p_currency_cd in varchar2
1003 ,p_limit_enrt_rt_id in number
1004 ,p_limit_prtt_rt_val_id in number
1005 ,p_limit_rt_val in number
1006 ,p_limit_entr_rt_at_enrt_flag in varchar2
1007 ,p_pl_typ_id in number
1008 ,p_ler_id in number
1009 ,p_limit_dsply_on_enrt_flag in varchar2
1010 ,p_currency_symbol in varchar2
1011 ,p_rt_strt_dt in date
1012 ,p_rt_end_dt in date
1013 ,p_rt_strt_dt_cd in varchar2
1014 ,p_rt_end_dt_cd in varchar2
1015 ,p_rslt_bnft_amt in number
1016 ,p_rtval_rt_end_dt in date
1017 ,p_rtval_rt_val in number
1018 ,p_rtval_limit_rt_val in number
1019 ,p_bnft_typ_meaning in varchar2
1020 ,p_ctfn_names in varchar2
1021 ,p_rt_update_mode in varchar2
1022 ,p_rtval_rt_strt_dt in date
1023 ,p_save_mode in varchar2 default null
1024 ,p_nip_pl_uom in varchar2) is
1025 --
1026 l_tx_name t_tx_name;
1027 l_tx_char t_tx_char;
1028 l_tx_num t_tx_num;
1029 l_tx_date t_tx_date;
1030 l_tx_type t_tx_type;
1031
1032 l_api_error boolean;
1033 l_transaction_id number := null;
1034 l_transaction_step_id number := null;
1035 l_result varchar2(100);
1036 l_trans_obj_vers_num number;
1037 l_enrt_cvg_thru_dt date := null;
1038 l_rt_end_dt date := null;
1039 l_count number := 1;
1040 l_update_mode boolean := true;
1041 --
1042 cursor c_step_id is
1043 select stp.transaction_step_id
1044 from hr_api_transactions trn,
1045 hr_api_transaction_steps stp,
1046 hr_api_transaction_values vlv
1047 where trn.selected_person_id = p_person_id
1048 and trn.transaction_id = stp.transaction_id
1049 and stp.api_name = upper(g_package || 'process_api')
1050 and stp.transaction_step_id <> l_transaction_step_id
1051 and stp.transaction_step_id = vlv.transaction_step_id
1052 and vlv.name = 'P_ELIG_PER_ELCTBL_CHC_ID'
1053 and vlv.number_value = p_elig_per_elctbl_chc_id;
1054 --
1055 begin
1056 --
1057 -- Clear enrollment caching
1058 --
1059 ben_process_compensation_w.clear_enroll_caches;
1060 --
1061 if p_enrt_cvg_thru_dt <> hr_api.g_eot then
1062 l_enrt_cvg_thru_dt := p_enrt_cvg_thru_dt;
1063 end if;
1064 if p_rt_end_dt <> hr_api.g_eot then
1065 l_rt_end_dt := p_rt_end_dt;
1066 end if;
1067 --
1068 if p_save_mode is null or
1069 p_save_mode <> 'SAVE_FOR_LATER' then
1070 --
1071 validate_comp_details
1072 (p_item_type => p_item_type
1073 ,p_item_key => p_item_key
1074 ,p_activity_id => p_actid
1075 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id
1076 ,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
1077 ,p_person_id => p_person_id
1078 ,p_per_in_ler_id => p_per_in_ler_id
1079 ,p_pgm_id => p_pgm_id
1080 ,p_pl_id => p_pl_id
1081 ,p_effective_date => p_effective_date
1082 ,p_enrt_bnft_id => p_enrt_bnft_id
1083 ,p_bnft_amt => p_bnft_amt
1084 ,p_enrt_rt_id => p_enrt_rt_id
1085 ,p_prtt_rt_val_id => p_prtt_rt_val_id
1086 ,p_rt_val => p_rt_val
1087 ,p_enrt_cvg_strt_dt => p_enrt_cvg_strt_dt
1088 ,p_enrt_cvg_thru_dt => l_enrt_cvg_thru_dt
1089 ,p_rt_strt_dt => p_rt_strt_dt
1090 ,p_rt_end_dt => l_rt_end_dt
1091 ,p_rt_strt_dt_cd => p_rt_strt_dt_cd
1092 ,p_datetrack_mode => p_datetrack_mode
1093 ,p_effective_start_date => p_effective_start_date
1094 ,p_object_version_number => p_object_version_number
1095 ,p_business_group_id => p_business_group_id
1096 ,p_limit_enrt_rt_id => p_limit_enrt_rt_id
1097 ,p_limit_prtt_rt_val_id => p_limit_prtt_rt_val_id
1098 ,p_limit_rt_val => p_limit_rt_val
1099 ,p_rt_update_mode => p_rt_update_mode
1100 ,p_api_error => l_api_error);
1101 --
1102 end if;
1103 --
1104 if l_api_error then
1105 raise g_data_error;
1106 end if;
1107 --
1108 -------------------------------------------------------------------------------
1109 -- We use the p_actid passed in because only in the Update page will it call
1110 -- this update_person procedure.
1111 --
1112 -- Now save the data to transaction table. When coming from Update Comp
1113 -- Details first time, a transaction step won't exit. We'll save to
1114 -- transaction table. Then displays the Review page. A user can press back to
1115 -- go back to Update Comp Details and enters some more changes or correct typo
1116 -- errors. At this point, a transaction step already exists.
1117 -- Before saving to the transaction table, we need to see if a transaction step
1118 -- already exists or not. This could happen when a user enters data to Update
1119 -- Comp Details --> Next --> Review Page --> Back to Update Comp Details to
1120 -- correct wrong entry or to make further changes --> Next --> Review Page.
1121 -- Use the activity_id to check if a transaction step already
1122 -- exists.
1123 -------------------------------------------------------------------------------
1124 --
1125 l_transaction_id := hr_transaction_ss.get_transaction_id
1126 (p_item_type => p_item_type
1127 ,p_item_key => p_item_key);
1128 --
1129 if l_transaction_id is null then
1130 hr_transaction_ss.start_transaction
1131 (itemtype => p_item_type
1132 ,itemkey => p_item_key
1133 ,actid => p_actid
1134 ,funmode => 'RUN'
1135 ,p_login_person_id => p_login_person_id
1136 ,result => l_result);
1137 --
1138 l_transaction_id := hr_transaction_ss.get_transaction_id
1139 (p_item_type => p_item_type
1140 ,p_item_key => p_item_key);
1141 end if;
1142 --
1143 l_transaction_step_id := get_transaction_step_id
1144 (p_item_type => p_item_type
1145 ,p_item_key => p_item_key
1146 ,p_activity_id => p_actid
1147 ,p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id);
1148 --
1149 if l_transaction_step_id is null then
1150 hr_transaction_api.create_transaction_step
1151 (p_validate => false
1152 ,p_creator_person_id => p_login_person_id
1153 ,p_transaction_id => l_transaction_id
1154 ,p_api_name => upper(g_package || 'process_api')
1155 ,p_item_type => p_item_type
1156 ,p_item_key => p_item_key
1157 ,p_activity_id => p_actid
1158 ,p_transaction_step_id => l_transaction_step_id
1159 ,p_object_version_number => l_trans_obj_vers_num);
1160 l_update_mode := false;
1161 end if;
1162 --
1163 l_tx_name(l_count) := 'P_PROCESS_SECTION_NAME';
1164 l_tx_char(l_count) := p_process_section_name;
1165 l_tx_num(l_count) := null;
1166 l_tx_date(l_count) := null;
1167 l_tx_type(l_count) := 'VARCHAR2';
1168 --
1169 l_count := l_count + 1;
1170 l_tx_name(l_count) := 'P_REVIEW_PROC_CALL';
1171 l_tx_char(l_count) := p_review_page_region_code;
1172 l_tx_num(l_count) := null;
1173 l_tx_date(l_count) := null;
1174 l_tx_type(l_count) := 'VARCHAR2';
1175 --
1176 l_count := l_count + 1;
1177 l_tx_name(l_count) := 'P_REVIEW_ACTID';
1178 l_tx_char(l_count) := p_actid;
1179 l_tx_num(l_count) := null;
1180 l_tx_date(l_count) := null;
1181 l_tx_type(l_count) := 'VARCHAR2';
1182 --
1183 l_count := l_count + 1;
1184 l_tx_name(l_count) := 'P_ELIG_PER_ELCTBL_CHC_ID';
1185 l_tx_char(l_count) := null;
1186 l_tx_num(l_count) := p_elig_per_elctbl_chc_id;
1187 l_tx_date(l_count) := null;
1188 l_tx_type(l_count) := 'NUMBER';
1189 --
1190 l_count := l_count + 1;
1191 l_tx_name(l_count) := 'P_PRTT_ENRT_RSLT_ID';
1192 l_tx_char(l_count) := null;
1193 l_tx_num(l_count) := p_prtt_enrt_rslt_id;
1194 l_tx_date(l_count) := null;
1195 l_tx_type(l_count) := 'NUMBER';
1196 --
1197 l_count := l_count + 1;
1198 l_tx_name(l_count) := 'P_PERSON_ID';
1199 l_tx_char(l_count) := null;
1200 l_tx_num(l_count) := p_person_id;
1201 l_tx_date(l_count) := null;
1202 l_tx_type(l_count) := 'NUMBER';
1203 --
1204 l_count := l_count + 1;
1205 l_tx_name(l_count) := 'P_PER_IN_LER_ID';
1206 l_tx_char(l_count) := null;
1207 l_tx_num(l_count) := p_per_in_ler_id;
1208 l_tx_date(l_count) := null;
1209 l_tx_type(l_count) := 'NUMBER';
1210 --
1211 l_count := l_count + 1;
1212 l_tx_name(l_count) := 'P_PGM_ID';
1213 l_tx_char(l_count) := null;
1214 l_tx_num(l_count) := p_pgm_id;
1215 l_tx_date(l_count) := null;
1216 l_tx_type(l_count) := 'NUMBER';
1217 --
1218 l_count := l_count + 1;
1219 l_tx_name(l_count) := 'P_PL_ID';
1220 l_tx_char(l_count) := null;
1221 l_tx_num(l_count) := p_pl_id;
1222 l_tx_date(l_count) := null;
1223 l_tx_type(l_count) := 'NUMBER';
1224 --
1225 l_count := l_count + 1;
1226 l_tx_name(l_count) := 'P_EFFECTIVE_DATE';
1227 l_tx_char(l_count) := null;
1228 l_tx_num(l_count) := null;
1229 l_tx_date(l_count) := p_effective_date;
1230 l_tx_type(l_count) := 'DATE';
1231 --
1232 l_count := l_count + 1;
1233 l_tx_name(l_count) := 'P_ENRT_BNFT_ID';
1234 l_tx_char(l_count) := null;
1235 l_tx_num(l_count) := p_enrt_bnft_id;
1236 l_tx_date(l_count) := null;
1237 l_tx_type(l_count) := 'NUMBER';
1238 --
1239 l_count := l_count + 1;
1240 l_tx_name(l_count) := 'P_BNFT_AMT';
1241 l_tx_char(l_count) := null;
1242 l_tx_num(l_count) := p_bnft_amt;
1243 l_tx_date(l_count) := null;
1244 l_tx_type(l_count) := 'NUMBER';
1245 --
1246 l_count := l_count + 1;
1247 l_tx_name(l_count) := 'P_ENRT_RT_ID';
1248 l_tx_char(l_count) := null;
1249 l_tx_num(l_count) := p_enrt_rt_id;
1250 l_tx_date(l_count) := null;
1251 l_tx_type(l_count) := 'NUMBER';
1252 --
1253 l_count := l_count + 1;
1254 l_tx_name(l_count) := 'P_PRTT_RT_VAL_ID';
1255 l_tx_char(l_count) := null;
1256 l_tx_num(l_count) := p_prtt_rt_val_id;
1257 l_tx_date(l_count) := null;
1258 l_tx_type(l_count) := 'NUMBER';
1259 --
1260 l_count := l_count + 1;
1261 l_tx_name(l_count) := 'P_RT_VAL';
1262 l_tx_char(l_count) := null;
1263 l_tx_num(l_count) := p_rt_val;
1264 l_tx_date(l_count) := null;
1265 l_tx_type(l_count) := 'NUMBER';
1266 --
1267 l_count := l_count + 1;
1268 l_tx_name(l_count) := 'P_DATETRACK_MODE';
1269 l_tx_char(l_count) := p_datetrack_mode;
1270 l_tx_num(l_count) := null;
1271 l_tx_date(l_count) := null;
1272 l_tx_type(l_count) := 'VARCHAR2';
1273 --
1274 l_count := l_count + 1;
1275 l_tx_name(l_count) := 'P_EFFECTIVE_START_DATE';
1276 l_tx_char(l_count) := null;
1277 l_tx_num(l_count) := null;
1278 l_tx_date(l_count) := p_effective_start_date;
1279 l_tx_type(l_count) := 'DATE';
1280 --
1281 l_count := l_count + 1;
1282 l_tx_name(l_count) := 'P_OBJECT_VERSION_NUMBER';
1283 l_tx_char(l_count) := null;
1284 l_tx_num(l_count) := p_object_version_number;
1285 l_tx_date(l_count) := null;
1286 l_tx_type(l_count) := 'NUMBER';
1287 --
1288 l_count := l_count + 1;
1289 l_tx_name(l_count) := 'P_BUSINESS_GROUP_ID';
1290 l_tx_char(l_count) := null;
1291 l_tx_num(l_count) := p_business_group_id;
1292 l_tx_date(l_count) := null;
1293 l_tx_type(l_count) := 'NUMBER';
1294 --
1295 l_count := l_count + 1;
1296 l_tx_name(l_count) := 'P_ENRT_CVG_STRT_DT';
1297 l_tx_char(l_count) := null;
1298 l_tx_num(l_count) := null;
1299 l_tx_date(l_count) := p_enrt_cvg_strt_dt;
1300 l_tx_type(l_count) := 'DATE';
1301 --
1302 l_count := l_count + 1;
1303 l_tx_name(l_count) := 'P_ENRT_CVG_THRU_DT';
1304 l_tx_char(l_count) := null;
1305 l_tx_num(l_count) := null;
1306 l_tx_date(l_count) := l_enrt_cvg_thru_dt;
1307 l_tx_type(l_count) := 'DATE';
1308 --
1309 l_count := l_count + 1;
1310 l_tx_name(l_count) := 'P_JUSTIFICATION';
1311 l_tx_char(l_count) := p_justification;
1312 l_tx_num(l_count) := null;
1313 l_tx_date(l_count) := null;
1314 l_tx_type(l_count) := 'VARCHAR2';
1315 --
1316 l_count := l_count + 1;
1317 l_tx_name(l_count) := 'P_PL_NAME';
1318 l_tx_char(l_count) := p_pl_name;
1319 l_tx_num(l_count) := null;
1320 l_tx_date(l_count) := null;
1321 l_tx_type(l_count) := 'VARCHAR2';
1322 --
1323 l_count := l_count + 1;
1324 l_tx_name(l_count) := 'P_FREQUENCY_CD';
1325 l_tx_char(l_count) := p_frequency_cd;
1326 l_tx_num(l_count) := null;
1327 l_tx_date(l_count) := null;
1328 l_tx_type(l_count) := 'VARCHAR2';
1329 --
1330 l_count := l_count + 1;
1331 l_tx_name(l_count) := 'P_FREQUENCY_MEANING';
1332 l_tx_char(l_count) := p_frequency_meaning;
1333 l_tx_num(l_count) := null;
1334 l_tx_date(l_count) := null;
1335 l_tx_type(l_count) := 'VARCHAR2';
1336 --
1337 l_count := l_count + 1;
1338 l_tx_name(l_count) := 'P_ENTR_RT_AT_ENRT_FLAG';
1339 l_tx_char(l_count) := p_entr_rt_at_enrt_flag;
1340 l_tx_num(l_count) := null;
1341 l_tx_date(l_count) := null;
1342 l_tx_type(l_count) := 'VARCHAR2';
1343 --
1344 l_count := l_count + 1;
1345 l_tx_name(l_count) := 'P_ENTR_BNFT_AT_ENRT_FLAG';
1346 l_tx_char(l_count) := p_entr_bnft_at_enrt_flag;
1347 l_tx_num(l_count) := null;
1348 l_tx_date(l_count) := null;
1349 l_tx_type(l_count) := 'VARCHAR2';
1350 --
1351 l_count := l_count + 1;
1352 l_tx_name(l_count) := 'P_RT_NNMNTRY_UOM';
1353 l_tx_char(l_count) := p_rt_nnmntry_uom;
1354 l_tx_num(l_count) := null;
1355 l_tx_date(l_count) := null;
1356 l_tx_type(l_count) := 'VARCHAR2';
1357 --
1358 l_count := l_count + 1;
1359 l_tx_name(l_count) := 'P_BNFT_NNMNTRY_UOM';
1360 l_tx_char(l_count) := p_bnft_nnmntry_uom;
1361 l_tx_num(l_count) := null;
1362 l_tx_date(l_count) := null;
1363 l_tx_type(l_count) := 'VARCHAR2';
1364 --
1365 l_count := l_count + 1;
1366 l_tx_name(l_count) := 'P_RT_UOM';
1367 l_tx_char(l_count) := p_rt_uom;
1368 l_tx_num(l_count) := null;
1369 l_tx_date(l_count) := null;
1370 l_tx_type(l_count) := 'VARCHAR2';
1371 --
1372 l_count := l_count + 1;
1373 l_tx_name(l_count) := 'P_BNFT_UOM';
1374 l_tx_char(l_count) := p_bnft_uom;
1375 l_tx_num(l_count) := null;
1376 l_tx_date(l_count) := null;
1377 l_tx_type(l_count) := 'VARCHAR2';
1378 --
1379 l_count := l_count + 1;
1380 l_tx_name(l_count) := 'P_RT_MN_VAL';
1381 l_tx_char(l_count) := null;
1382 l_tx_num(l_count) := p_rt_mn_val;
1383 l_tx_date(l_count) := null;
1384 l_tx_type(l_count) := 'NUMBER';
1385 --
1386 l_count := l_count + 1;
1387 l_tx_name(l_count) := 'P_RT_MX_VAL';
1388 l_tx_char(l_count) := null;
1389 l_tx_num(l_count) := p_rt_mx_val;
1390 l_tx_date(l_count) := null;
1391 l_tx_type(l_count) := 'NUMBER';
1392 --
1393 l_count := l_count + 1;
1394 l_tx_name(l_count) := 'P_BNFT_MN_VAL';
1395 l_tx_char(l_count) := null;
1396 l_tx_num(l_count) := p_bnft_mn_val;
1397 l_tx_date(l_count) := null;
1398 l_tx_type(l_count) := 'NUMBER';
1399 --
1400 l_count := l_count + 1;
1401 l_tx_name(l_count) := 'P_BNFT_MX_VAL';
1402 l_tx_char(l_count) := null;
1403 l_tx_num(l_count) := p_bnft_mx_val;
1404 l_tx_date(l_count) := null;
1405 l_tx_type(l_count) := 'NUMBER';
1406 --
1407 l_count := l_count + 1;
1408 l_tx_name(l_count) := 'P_ENRT_CVG_STRT_DT_CD';
1409 l_tx_char(l_count) := p_enrt_cvg_strt_dt_cd;
1410 l_tx_num(l_count) := null;
1411 l_tx_date(l_count) := null;
1412 l_tx_type(l_count) := 'VARCHAR2';
1413 --
1414 l_count := l_count + 1;
1415 l_tx_name(l_count) := 'P_ACTY_REF_PERD_CD';
1416 l_tx_char(l_count) := p_acty_ref_perd_cd;
1417 l_tx_num(l_count) := null;
1418 l_tx_date(l_count) := null;
1419 l_tx_type(l_count) := 'VARCHAR2';
1420 --
1421 l_count := l_count + 1;
1422 l_tx_name(l_count) := 'P_CURRENCY_CD';
1423 l_tx_char(l_count) := p_currency_cd;
1424 l_tx_num(l_count) := null;
1425 l_tx_date(l_count) := null;
1426 l_tx_type(l_count) := 'VARCHAR2';
1427 --
1428 l_count := l_count + 1;
1429 l_tx_name(l_count) := 'P_LIMIT_ENRT_RT_ID';
1430 l_tx_char(l_count) := null;
1431 l_tx_num(l_count) := p_limit_enrt_rt_id;
1432 l_tx_date(l_count) := null;
1433 l_tx_type(l_count) := 'NUMBER';
1434 --
1435 l_count := l_count + 1;
1436 l_tx_name(l_count) := 'P_LIMIT_PRTT_RT_VAL_ID';
1437 l_tx_char(l_count) := null;
1438 l_tx_num(l_count) := p_limit_prtt_rt_val_id;
1439 l_tx_date(l_count) := null;
1440 l_tx_type(l_count) := 'NUMBER';
1441 --
1442 l_count := l_count + 1;
1443 l_tx_name(l_count) := 'P_LIMIT_RT_VAL';
1444 l_tx_char(l_count) := null;
1445 l_tx_num(l_count) := p_limit_rt_val;
1446 l_tx_date(l_count) := null;
1447 l_tx_type(l_count) := 'NUMBER';
1448 --
1449 l_count := l_count + 1;
1450 l_tx_name(l_count) := 'P_LIMIT_ENTR_RT_AT_ENRT_FLAG';
1451 l_tx_char(l_count) := p_limit_entr_rt_at_enrt_flag;
1452 l_tx_num(l_count) := null;
1453 l_tx_date(l_count) := null;
1454 l_tx_type(l_count) := 'VARCHAR2';
1455 --
1456 l_count := l_count + 1;
1457 l_tx_name(l_count) := 'P_PL_TYP_ID';
1458 l_tx_char(l_count) := null;
1459 l_tx_num(l_count) := p_pl_typ_id;
1460 l_tx_date(l_count) := null;
1461 l_tx_type(l_count) := 'NUMBER';
1462 --
1463 l_count := l_count + 1;
1464 l_tx_name(l_count) := 'P_LER_ID';
1465 l_tx_char(l_count) := null;
1466 l_tx_num(l_count) := p_ler_id;
1467 l_tx_date(l_count) := null;
1468 l_tx_type(l_count) := 'NUMBER';
1469 --
1470 l_count := l_count + 1;
1471 l_tx_name(l_count) := 'P_LIMIT_DSPLY_ON_ENRT_FLAG';
1472 l_tx_char(l_count) := p_limit_dsply_on_enrt_flag;
1473 l_tx_num(l_count) := null;
1474 l_tx_date(l_count) := null;
1475 l_tx_type(l_count) := 'VARCHAR2';
1476 --
1477 l_count := l_count + 1;
1478 l_tx_name(l_count) := 'P_CURRENCY_SYMBOL';
1479 l_tx_char(l_count) := p_currency_symbol;
1480 l_tx_num(l_count) := null;
1481 l_tx_date(l_count) := null;
1482 l_tx_type(l_count) := 'VARCHAR2';
1483 --
1484 l_count := l_count + 1;
1485 l_tx_name(l_count) := 'P_RT_STRT_DT';
1486 l_tx_char(l_count) := null;
1487 l_tx_num(l_count) := null;
1488 l_tx_date(l_count) := p_rt_strt_dt;
1489 l_tx_type(l_count) := 'DATE';
1490 --
1491 l_count := l_count + 1;
1492 l_tx_name(l_count) := 'P_RT_END_DT';
1493 l_tx_char(l_count) := null;
1494 l_tx_num(l_count) := null;
1495 l_tx_date(l_count) := l_rt_end_dt;
1496 l_tx_type(l_count) := 'DATE';
1497 --
1498 l_count := l_count + 1;
1499 l_tx_name(l_count) := 'P_RT_STRT_DT_CD';
1500 l_tx_char(l_count) := p_rt_strt_dt_cd;
1501 l_tx_num(l_count) := null;
1502 l_tx_date(l_count) := null;
1503 l_tx_type(l_count) := 'VARCHAR2';
1504 --
1505 l_count := l_count + 1;
1506 l_tx_name(l_count) := 'P_RT_END_DT_CD';
1507 l_tx_char(l_count) := p_rt_end_dt_cd;
1508 l_tx_num(l_count) := null;
1509 l_tx_date(l_count) := null;
1510 l_tx_type(l_count) := 'VARCHAR2';
1511 --
1512 l_count := l_count + 1;
1513 l_tx_name(l_count) := 'P_RSLT_BNFT_AMT';
1514 l_tx_char(l_count) := null;
1515 l_tx_num(l_count) := p_rslt_bnft_amt;
1516 l_tx_date(l_count) := null;
1517 l_tx_type(l_count) := 'NUMBER';
1518 --
1519 l_count := l_count + 1;
1520 l_tx_name(l_count) := 'P_RTVAL_RT_END_DT';
1521 l_tx_char(l_count) := null;
1522 l_tx_num(l_count) := null;
1523 l_tx_date(l_count) := p_rtval_rt_end_dt;
1524 l_tx_type(l_count) := 'DATE';
1525 --
1526 l_count := l_count + 1;
1527 l_tx_name(l_count) := 'P_RTVAL_RT_VAL';
1528 l_tx_char(l_count) := null;
1529 l_tx_num(l_count) := p_rtval_rt_val;
1530 l_tx_date(l_count) := null;
1531 l_tx_type(l_count) := 'NUMBER';
1532 --
1533 l_count := l_count + 1;
1534 l_tx_name(l_count) := 'P_RTVAL_LIMIT_RT_VAL';
1535 l_tx_char(l_count) := null;
1536 l_tx_num(l_count) := p_rtval_limit_rt_val;
1537 l_tx_date(l_count) := null;
1538 l_tx_type(l_count) := 'NUMBER';
1539 --
1540 l_count := l_count + 1;
1541 l_tx_name(l_count) := 'P_BNFT_TYP_MEANING';
1542 l_tx_char(l_count) := p_bnft_typ_meaning;
1543 l_tx_num(l_count) := null;
1544 l_tx_date(l_count) := null;
1545 l_tx_type(l_count) := 'VARCHAR2';
1546 --
1547 l_count := l_count + 1;
1548 l_tx_name(l_count) := 'P_CTFN_NAMES';
1549 l_tx_char(l_count) := p_ctfn_names;
1550 l_tx_num(l_count) := null;
1551 l_tx_date(l_count) := null;
1552 l_tx_type(l_count) := 'VARCHAR2';
1553 --
1554 l_count := l_count + 1;
1555 l_tx_name(l_count) := 'P_RT_UPDATE_MODE';
1556 l_tx_char(l_count) := p_rt_update_mode;
1557 l_tx_num(l_count) := null;
1558 l_tx_date(l_count) := null;
1559 l_tx_type(l_count) := 'VARCHAR2';
1560 --
1561 l_count := l_count + 1;
1562 l_tx_name(l_count) := 'P_RTVAL_RT_STRT_DT';
1563 l_tx_char(l_count) := null;
1564 l_tx_num(l_count) := null;
1565 l_tx_date(l_count) := p_rtval_rt_strt_dt;
1566 l_tx_type(l_count) := 'DATE';
1567 --
1568 l_count := l_count + 1;
1569 l_tx_name(l_count) := 'P_NIP_PL_UOM';
1570 l_tx_char(l_count) := p_nip_pl_uom;
1571 l_tx_num(l_count) := null;
1572 l_tx_date(l_count) := null;
1573 l_tx_type(l_count) := 'VARCHAR2';
1574 --
1575
1576
1577
1578 if l_update_mode then
1579 forall i in 1..l_count
1580 update hr_api_transaction_values
1581 set
1582 varchar2_value = l_tx_char(i),
1583 number_value = l_tx_num(i),
1584 date_value = l_tx_date(i)
1585 where transaction_step_id = l_transaction_step_id
1586 and name = l_tx_name(i);
1587 else
1588 forall i in 1..l_count
1589 insert into hr_api_transaction_values
1590 ( transaction_value_id,
1591 transaction_step_id,
1592 datatype,
1593 name,
1594 varchar2_value,
1595 number_value,
1596 date_value,
1597 original_varchar2_value,
1598 original_number_value,
1599 original_date_value)
1600 Values
1601 ( hr_api_transaction_values_s.nextval,
1602 l_transaction_step_id,
1603 l_tx_type(i),
1604 l_tx_name(i),
1605 l_tx_char(i),
1606 l_tx_num(i),
1607 l_tx_date(i),
1608 l_tx_char(i),
1609 l_tx_num(i),
1610 l_tx_date(i));
1611 end if;
1612 --
1613 -- delete pending steps for the same choice.
1614 --
1615 if p_elig_per_elctbl_chc_id is not null then
1616 --
1617 for l_step in c_step_id loop
1618 --
1619 delete hr_api_transaction_values vlv
1620 where vlv.transaction_step_id = l_step.transaction_step_id;
1621 --
1622 delete hr_api_transaction_steps step
1623 where step.transaction_step_id = l_step.transaction_step_id;
1624 --
1625 end loop;
1626 --
1627 end if;
1628 --
1629 exception
1630 when g_data_error then
1631 null;
1632
1633 end update_compensation;
1634 --
1635 -- ---------------------------------------------------------------------------
1636 -- ----------------------------- < process_api > -----------------------------
1637 -- ---------------------------------------------------------------------------
1638 -- Purpose: This procedure will be invoked in workflow notification
1639 -- when an approver approves all the changes. This procedure
1640 -- will call the api to update to the database with p_validate
1641 -- equal to false.
1642 -- ---------------------------------------------------------------------------
1643 procedure process_api
1644 (p_validate IN BOOLEAN DEFAULT FALSE
1645 ,p_transaction_step_id IN NUMBER
1646 ,p_effective_date in varchar2 default null) is
1647 --
1648 l_elig_per_elctbl_chc_id number;
1649 l_prtt_enrt_rslt_id number;
1650 l_person_id number;
1651 l_per_in_ler_id number;
1652 l_pgm_id number;
1653 l_pl_id number;
1654 l_effective_date date;
1655 l_enrt_bnft_id number;
1656 l_bnft_amt number;
1657 l_enrt_rt_id number;
1658 l_prtt_rt_val_id number;
1659 l_rt_val number;
1660 l_datetrack_mode varchar2(30);
1661 l_effective_start_date date;
1662 l_object_version_number number;
1663 l_business_group_id number;
1664 l_enrt_cvg_strt_dt date;
1665 l_enrt_cvg_thru_dt date;
1666 l_justification varchar2(2000);
1667 l_pl_name varchar2(481);
1668 l_frequency_meaning varchar2(80);
1669 l_frequency_cd varchar2(30);
1670 l_entr_rt_at_enrt_flag varchar2(30);
1671 l_entr_bnft_at_enrt_flag varchar2(30);
1672 l_rt_nnmntry_uom varchar2(30);
1673 l_bnft_nnmntry_uom varchar2(30);
1674 l_rt_uom varchar2(80);
1675 l_bnft_uom varchar2(80);
1676 l_rt_mn_val number;
1677 l_rt_mx_val number;
1678 l_bnft_mn_val number;
1679 l_bnft_mx_val number;
1680 l_enrt_cvg_strt_dt_cd varchar2(30);
1681 l_acty_ref_perd_cd varchar2(80);
1682 l_currency_cd varchar2(80);
1683 l_limit_enrt_rt_id number;
1684 l_limit_prtt_rt_val_id number;
1685 l_limit_rt_val number;
1686 l_limit_entr_rt_at_enrt_flag varchar2(30);
1687 l_pl_typ_id number;
1688 l_ler_id number;
1689 l_limit_dsply_on_enrt_flag varchar2(30);
1690 l_currency_symbol varchar2(30);
1691 l_rt_strt_dt date;
1692 l_rt_end_dt date;
1693 l_rt_strt_dt_cd varchar2(30);
1694 l_rt_end_dt_cd varchar2(30);
1695 l_rslt_bnft_amt number;
1696 l_rtval_rt_end_dt date;
1697 l_rtval_rt_val number;
1698 l_rtval_limit_rt_val number;
1699 l_bnft_typ_meaning varchar2(80);
1700 l_ctfn_names varchar2(2000);
1701 l_rt_update_mode varchar2(30);
1702 l_rtval_rt_strt_dt date;
1703 l_nip_pl_uom varchar2(30);
1704 --
1705 l_api_error boolean;
1706 --
1707 begin
1708 --
1709 -- Clear enrollment caching
1710 --
1711 ben_process_compensation_w.clear_enroll_caches;
1712 --
1713 get_comp_data_from_tt(
1714 p_transaction_step_id => p_transaction_step_id
1715 ,p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
1716 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1717 ,p_person_id => l_person_id
1718 ,p_per_in_ler_id => l_per_in_ler_id
1719 ,p_pgm_id => l_pgm_id
1720 ,p_pl_id => l_pl_id
1721 ,p_effective_date => l_effective_date
1722 ,p_enrt_bnft_id => l_enrt_bnft_id
1723 ,p_bnft_amt => l_bnft_amt
1724 ,p_enrt_rt_id => l_enrt_rt_id
1725 ,p_prtt_rt_val_id => l_prtt_rt_val_id
1726 ,p_rt_val => l_rt_val
1727 ,p_datetrack_mode => l_datetrack_mode
1728 ,p_effective_start_date => l_effective_start_date
1729 ,p_object_version_number => l_object_version_number
1730 ,p_business_group_id => l_business_group_id
1731 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
1732 ,p_enrt_cvg_thru_dt => l_enrt_cvg_thru_dt
1733 ,p_justification => l_justification
1734 ,p_pl_name => l_pl_name
1735 ,p_frequency_meaning => l_frequency_meaning
1736 ,p_frequency_cd => l_frequency_cd
1737 ,p_entr_rt_at_enrt_flag => l_entr_rt_at_enrt_flag
1738 ,p_entr_bnft_at_enrt_flag => l_entr_bnft_at_enrt_flag
1739 ,p_rt_nnmntry_uom => l_rt_nnmntry_uom
1740 ,p_bnft_nnmntry_uom => l_bnft_nnmntry_uom
1741 ,p_rt_uom => l_rt_uom
1742 ,p_bnft_uom => l_bnft_uom
1743 ,p_rt_mn_val => l_rt_mn_val
1744 ,p_rt_mx_val => l_rt_mx_val
1745 ,p_bnft_mn_val => l_bnft_mn_val
1746 ,p_bnft_mx_val => l_bnft_mx_val
1747 ,p_enrt_cvg_strt_dt_cd => l_enrt_cvg_strt_dt_cd
1748 ,p_acty_ref_perd_cd => l_acty_ref_perd_cd
1749 ,p_currency_cd => l_currency_cd
1750 ,p_limit_enrt_rt_id => l_limit_enrt_rt_id
1751 ,p_limit_prtt_rt_val_id => l_limit_prtt_rt_val_id
1752 ,p_limit_rt_val => l_limit_rt_val
1753 ,p_limit_entr_rt_at_enrt_flag => l_limit_entr_rt_at_enrt_flag
1754 ,p_pl_typ_id => l_pl_typ_id
1755 ,p_ler_id => l_ler_id
1756 ,p_limit_dsply_on_enrt_flag => l_limit_dsply_on_enrt_flag
1757 ,p_currency_symbol => l_currency_symbol
1758 ,p_rt_strt_dt => l_rt_strt_dt
1759 ,p_rt_end_dt => l_rt_end_dt
1760 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
1761 ,p_rt_end_dt_cd => l_rt_end_dt_cd
1762 ,p_rslt_bnft_amt => l_rslt_bnft_amt
1763 ,p_rtval_rt_end_dt => l_rtval_rt_end_dt
1764 ,p_rtval_rt_val => l_rtval_rt_val
1765 ,p_rtval_limit_rt_val => l_rtval_limit_rt_val
1766 ,p_bnft_typ_meaning => l_bnft_typ_meaning
1767 ,p_ctfn_names => l_ctfn_names
1768 ,p_rt_update_mode => l_rt_update_mode
1769 ,p_rtval_rt_strt_dt => l_rtval_rt_strt_dt
1770 ,p_nip_pl_uom => l_nip_pl_uom
1771 );
1772 --
1773 validate_current_comp_details
1774 (p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
1775 ,p_prtt_enrt_rslt_id => l_prtt_enrt_rslt_id
1776 ,p_person_id => l_person_id
1777 ,p_per_in_ler_id => l_per_in_ler_id
1778 ,p_pgm_id => l_pgm_id
1779 ,p_pl_id => l_pl_id
1780 ,p_effective_date => trunc(sysdate) -- l_effective_date
1781 ,p_enrt_bnft_id => l_enrt_bnft_id
1782 ,p_bnft_amt => l_bnft_amt
1783 ,p_enrt_rt_id => l_enrt_rt_id
1784 ,p_prtt_rt_val_id => l_prtt_rt_val_id
1785 ,p_rt_val => l_rt_val
1786 ,p_enrt_cvg_strt_dt => l_enrt_cvg_strt_dt
1787 ,p_enrt_cvg_thru_dt => l_enrt_cvg_thru_dt
1788 ,p_rt_strt_dt => l_rt_strt_dt
1789 ,p_rt_end_dt => l_rt_end_dt
1790 ,p_rt_strt_dt_cd => l_rt_strt_dt_cd
1791 ,p_datetrack_mode => l_datetrack_mode
1792 ,p_effective_start_date => l_effective_start_date
1793 ,p_object_version_number => l_object_version_number
1794 ,p_business_group_id => l_business_group_id
1795 ,p_limit_enrt_rt_id => l_limit_enrt_rt_id
1796 ,p_limit_prtt_rt_val_id => l_limit_prtt_rt_val_id
1797 ,p_limit_rt_val => l_limit_rt_val
1798 ,p_rt_update_mode => l_rt_update_mode
1799 ,p_api_error => l_api_error);
1800 --
1801 end process_api;
1802 --
1803 procedure back_from_review(p_item_type in varchar2,
1804 p_item_key in varchar2,
1805 p_act_id in number,
1806 funmode in varchar2,
1807 result out nocopy varchar2) is
1808 --
1809 l_choice_status varchar2(30) := null;
1810 --
1811 begin
1812 --
1813 if funmode = 'RUN' then
1814 --
1815 l_choice_status := wf_engine.GetItemAttrText
1816 (itemtype => p_item_type
1817 ,itemkey => p_item_key
1818 ,aname => 'COMP_CHOICE_STATUS');
1819 --
1820 if l_choice_status = 'NS' then
1821 result := 'COMPLETE:Y';
1822 else
1823 result := 'COMPLETE:N';
1824 end if;
1825 --
1826 end if;
1827 --
1828 end back_from_review;
1829 --
1830 --
1831 procedure update_object_version
1832 (p_transaction_step_id in number
1833 ,p_login_person_id in number) is
1834 begin
1835 --
1836 -- No Nothing.
1837 -- Procedure created beacuse SS HR needs it.
1838 --
1839 null;
1840 --
1841 end update_object_version;
1842 --
1843 --
1844 end ben_process_compensation_w;
1845 --
1846 --