[Home] [Help]
PACKAGE BODY: APPS.BEN_ICD_ELEMENT_ENTRY_PKG
Source
1 PACKAGE BODY BEN_ICD_ELEMENT_ENTRY_PKG AS
2 /* $Header: beicdeleent.pkb 120.12 2008/04/17 10:18:20 schowdhu noship $ */
3
4 -- Global variables
5 g_package constant varchar2(80):='ben_icd_element_entry_pkg.';
6
7
8
9 function get_datetrack_mode
10 (p_element_entry_id in number
11 ,p_effective_start_date in date
12 ,p_effective_date in date
13 ,p_datetrack_mode in varchar2) return varchar2 is
14
15 cursor dt_row_exists is select 'Y' from pay_element_entries_f a, pay_element_entries_f b
16 where a.element_entry_id = p_element_entry_id
17 and a.effective_start_date = p_effective_start_Date
18 and b.element_entry_id = a.element_entry_id
19 and b.effective_start_date = a.effective_end_Date+1;
20 next_row_exists varchar2(1):='N';
21 l_datetrack_mode varchar2(30);
22 begin
23
24 open dt_row_exists;
25 fetch dt_row_exists into next_row_exists;
26 close dt_row_exists;
27
28 if(next_row_exists is null) then
29 next_row_exists := 'N';
30 end if ;
31
32 if(hr_api.g_update = p_datetrack_mode) then
33 if( p_effective_date < p_effective_start_date)then
34 -- throw an error.
35 --'Please check the effective date as the changes can not become effective before the record started.'
36 null;
37 elsif(p_effective_date = p_effective_start_date)then
38 l_datetrack_mode:= hr_api.g_correction;
39 elsif(p_effective_date > p_effective_start_date and 'Y' = next_row_exists) then
40 l_datetrack_mode:=hr_api.g_update_change_insert;
41 else
42 l_datetrack_mode:= hr_api.g_update;
43 end if;
44 elsif(hr_api.g_delete = p_datetrack_mode) then
45 if( p_effective_date < p_effective_start_date)then
46 null;
47 -- throw an error.
48 --'Please check the end date as the changes can not end before the record started.'
49 elsif(p_effective_date = p_effective_start_date) then
50 l_datetrack_mode:= hr_api.g_zap;
51 elsif(p_effective_date > p_effective_start_date and 'Y' = next_row_exists) then
52 l_datetrack_mode:= hr_api.g_future_change;
53 else
54 l_datetrack_mode:= hr_api.g_delete;
55 end if;
56 else
57 -- this is insert dml_operation and should not happen.
58 l_datetrack_mode:= p_datetrack_mode;
59 end if;
60 return l_datetrack_mode;
61 end get_datetrack_mode;
62
63 PROCEDURE ICD_UPDATE_ELEMENT_ENTRY
64 (p_validate in number default 0
65 ,p_datetrack_update_mode in varchar2
66 ,p_effective_date in date
67 ,p_business_group_id in number
68 ,p_element_entry_id in number
69 ,p_object_version_number in number
70 ,p_cost_allocation_keyflex_id in number
71 ,p_attribute_category in varchar2
72 ,p_attribute1 in varchar2
73 ,p_attribute2 in varchar2
74 ,p_attribute3 in varchar2
75 ,p_attribute4 in varchar2
76 ,p_attribute5 in varchar2
77 ,p_attribute6 in varchar2
78 ,p_attribute7 in varchar2
79 ,p_attribute8 in varchar2
80 ,p_attribute9 in varchar2
81 ,p_attribute10 in varchar2
82 ,p_attribute11 in varchar2
83 ,p_attribute12 in varchar2
84 ,p_attribute13 in varchar2
85 ,p_attribute14 in varchar2
86 ,p_attribute15 in varchar2
87 ,p_attribute16 in varchar2
88 ,p_attribute17 in varchar2
89 ,p_attribute18 in varchar2
90 ,p_attribute19 in varchar2
91 ,p_attribute20 in varchar2
92 ,p_input_value_id1 in number
93 ,p_input_value_id2 in number
94 ,p_input_value_id3 in number
95 ,p_input_value_id4 in number
96 ,p_input_value_id5 in number
97 ,p_input_value_id6 in number
98 ,p_input_value_id7 in number
99 ,p_input_value_id8 in number
100 ,p_input_value_id9 in number
101 ,p_input_value_id10 in number
102 ,p_input_value_id11 in number
103 ,p_input_value_id12 in number
104 ,p_input_value_id13 in number
105 ,p_input_value_id14 in number
106 ,p_input_value_id15 in number
107 ,p_entry_value1 in varchar2
108 ,p_entry_value2 in varchar2
109 ,p_entry_value3 in varchar2
110 ,p_entry_value4 in varchar2
111 ,p_entry_value5 in varchar2
112 ,p_entry_value6 in varchar2
113 ,p_entry_value7 in varchar2
114 ,p_entry_value8 in varchar2
115 ,p_entry_value9 in varchar2
116 ,p_entry_value10 in varchar2
117 ,p_entry_value11 in varchar2
118 ,p_entry_value12 in varchar2
119 ,p_entry_value13 in varchar2
120 ,p_entry_value14 in varchar2
121 ,p_entry_value15 in varchar2
122 ,p_entry_information_category in varchar2
123 ,p_entry_information1 in varchar2
124 ,p_entry_information2 in varchar2
125 ,p_entry_information3 in varchar2
126 ,p_entry_information4 in varchar2
127 ,p_entry_information5 in varchar2
128 ,p_entry_information6 in varchar2
129 ,p_entry_information7 in varchar2
130 ,p_entry_information8 in varchar2
131 ,p_entry_information9 in varchar2
132 ,p_entry_information10 in varchar2
133 ,p_entry_information11 in varchar2
134 ,p_entry_information12 in varchar2
135 ,p_entry_information13 in varchar2
136 ,p_entry_information14 in varchar2
137 ,p_entry_information15 in varchar2
138 ,p_entry_information16 in varchar2
139 ,p_entry_information17 in varchar2
140 ,p_entry_information18 in varchar2
141 ,p_entry_information19 in varchar2
142 ,p_entry_information20 in varchar2
143 ,p_entry_information21 in varchar2
144 ,p_entry_information22 in varchar2
145 ,p_entry_information23 in varchar2
146 ,p_entry_information24 in varchar2
147 ,p_entry_information25 in varchar2
148 ,p_entry_information26 in varchar2
149 ,p_entry_information27 in varchar2
150 ,p_entry_information28 in varchar2
151 ,p_entry_information29 in varchar2
152 ,p_entry_information30 in varchar2
153 ,p_effective_start_date in date
154 ,p_effective_end_date in date
155 -- ,p_icd_effective_date in date
156 -- ,p_warning out nocopy number
157 ) is
158
159 cursor c_input_values is
160 select * from
161 pay_input_values_f
162 where element_type_id = (select element_type_id from pay_element_entries_f where
163 element_entry_id = p_element_entry_id and p_effective_date between effective_start_date and effective_end_date)
164 and p_effective_date between effective_start_date and effective_end_date
165 order by input_value_id asc;
166
167 l_effective_start_date date;
168 l_effective_end_date date;
169 l_update_warning boolean;
170 l_validate boolean;
171 l_object_version_number number;
172
173 l_entry_value1 varchar2(60);
174 l_entry_value2 varchar2(60);
175 l_entry_value3 varchar2(60);
176 l_entry_value4 varchar2(60);
177 l_entry_value5 varchar2(60);
178 l_entry_value6 varchar2(60);
179 l_entry_value7 varchar2(60);
180 l_entry_value8 varchar2(60);
181 l_entry_value9 varchar2(60);
182 l_entry_value10 varchar2(60);
183 l_entry_value11 varchar2(60);
184 l_entry_value12 varchar2(60);
185 l_entry_value13 varchar2(60);
186 l_entry_value14 varchar2(60);
187 l_entry_value15 varchar2(60);
188 l_app_name varchar2(50);
189 l_msg_name varchar2(30);
190 --changes for ICD number formatting issue
191 icx_numeric varchar2(20);
192
193 begin
194 fnd_msg_pub.initialize;
195 l_object_version_number := P_OBJECT_VERSION_NUMBER;
196 l_validate := false;
197 if p_validate = 1 then
198 l_validate := true;
199 end if;
200
201 icx_numeric := fnd_profile.value('ICX_NUMERIC_CHARACTERS');
202
203 for l_input_value in c_input_values loop
204 if(p_input_value_id1 = l_input_value.input_value_id ) then
205 -- the format coming in is already canonical but the api is expecting a user format
206 if( 'D' = l_input_value.uom) then
207 l_entry_value1 := fnd_date.date_to_displaydate(to_date(p_entry_value1,'YYYY/MM/DD HH24:MI:SS'));
208 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
209 l_entry_value1 := replace(p_entry_value1,'.',',');
210 else
211 l_entry_value1 := p_entry_value1;
212 end if ;
213 elsif(p_input_value_id2 = l_input_value.input_value_id ) then
214 if( 'D' = l_input_value.uom) then
215 l_entry_value2 := fnd_date.date_to_displaydate(to_date(p_entry_value2,'YYYY/MM/DD HH24:MI:SS'));
216 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
217 l_entry_value2 := replace(p_entry_value2,'.',',');
218 else
219 l_entry_value2 := p_entry_value2;
220 end if ;
221
222 elsif(p_input_value_id3 = l_input_value.input_value_id) then
223 if( 'D' = l_input_value.uom) then
224 l_entry_value3 := fnd_date.date_to_displaydate(to_date(p_entry_value3,'YYYY/MM/DD HH24:MI:SS'));
225 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
226 l_entry_value3 := replace(p_entry_value3,'.',',');
227 else
228 l_entry_value3 := p_entry_value3;
229 end if ;
230
231 elsif(p_input_value_id4 = l_input_value.input_value_id) then
232 if( 'D' = l_input_value.uom) then
233 l_entry_value4 := fnd_date.date_to_displaydate(to_date(p_entry_value4,'YYYY/MM/DD HH24:MI:SS'));
234 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
235 l_entry_value4 := replace(p_entry_value4,'.',',');
236 else
237 l_entry_value4 := p_entry_value4;
238 end if ;
239
240 elsif(p_input_value_id5 = l_input_value.input_value_id) then
241 if( 'D' = l_input_value.uom) then
242 l_entry_value5 := fnd_date.date_to_displaydate(to_date(p_entry_value5,'YYYY/MM/DD HH24:MI:SS'));
243 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
244 l_entry_value5 := replace(p_entry_value5,'.',',');
245 else
246 l_entry_value5 := p_entry_value5;
247 end if ;
248
249 elsif(p_input_value_id6 = l_input_value.input_value_id) then
250 if( 'D' = l_input_value.uom) then
251 l_entry_value6 := fnd_date.date_to_displaydate(to_date(p_entry_value6,'YYYY/MM/DD HH24:MI:SS'));
252 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
253 l_entry_value6 := replace(p_entry_value6,'.',',');
254 else
255 l_entry_value6 := p_entry_value6;
256 end if ;
257
258 elsif(p_input_value_id7 = l_input_value.input_value_id ) then
259 if( 'D' = l_input_value.uom) then
260 l_entry_value7 := fnd_date.date_to_displaydate(to_date(p_entry_value7,'YYYY/MM/DD HH24:MI:SS'));
261 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
262 l_entry_value7 := replace(p_entry_value7,'.',',');
263 else
264 l_entry_value7 := p_entry_value7;
265 end if ;
266
267 elsif(p_input_value_id8 = l_input_value.input_value_id) then
268 if( 'D' = l_input_value.uom) then
269 l_entry_value8 := fnd_date.date_to_displaydate(to_date(p_entry_value8,'YYYY/MM/DD HH24:MI:SS'));
270 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
271 l_entry_value8 := replace(p_entry_value8,'.',',');
272 else
273 l_entry_value8 := p_entry_value8;
274 end if ;
275
276 elsif(p_input_value_id9 = l_input_value.input_value_id) then
277 if( 'D' = l_input_value.uom) then
278 l_entry_value9 := fnd_date.date_to_displaydate(to_date(p_entry_value9,'YYYY/MM/DD HH24:MI:SS'));
279 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
280 l_entry_value9 := replace(p_entry_value9,'.',',');
281 else
282 l_entry_value9 := p_entry_value9;
283 end if ;
284 elsif(p_input_value_id10 = l_input_value.input_value_id) then
285 if( 'D' = l_input_value.uom) then
286 l_entry_value10 := fnd_date.date_to_displaydate(to_date(p_entry_value10,'YYYY/MM/DD HH24:MI:SS'));
287 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
288 l_entry_value10 := replace(p_entry_value10,'.',',');
289 else
290 l_entry_value10 := p_entry_value10;
291 end if ;
292 elsif(p_input_value_id11 = l_input_value.input_value_id) then
293 if( 'D' = l_input_value.uom) then
294 l_entry_value11 := fnd_date.date_to_displaydate(to_date(p_entry_value11,'YYYY/MM/DD HH24:MI:SS'));
295 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
296 l_entry_value11 := replace(p_entry_value11,'.',',');
297 else
298 l_entry_value11 := p_entry_value11;
299 end if ;
300 elsif(p_input_value_id12 = l_input_value.input_value_id) then
301 if( 'D' = l_input_value.uom) then
302 l_entry_value12 := fnd_date.date_to_displaydate(to_date(p_entry_value12,'YYYY/MM/DD HH24:MI:SS'));
303 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
304 l_entry_value12 := replace(p_entry_value12,'.',',');
305 else
306 l_entry_value12 := p_entry_value12;
307 end if ;
308 elsif(p_input_value_id13 = l_input_value.input_value_id) then
309 if( 'D' = l_input_value.uom) then
310 l_entry_value13 := fnd_date.date_to_displaydate(to_date(p_entry_value13,'YYYY/MM/DD HH24:MI:SS'));
311 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
312 l_entry_value13 := replace(p_entry_value13,'.',',');
313 else
314 l_entry_value13 := p_entry_value13;
315 end if ;
316 elsif(p_input_value_id14 = l_input_value.input_value_id ) then
317 if( 'D' = l_input_value.uom) then
318 l_entry_value14 := fnd_date.date_to_displaydate(to_date(p_entry_value14,'YYYY/MM/DD HH24:MI:SS'));
319 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
320 l_entry_value14 := replace(p_entry_value14,'.',',');
321 else
322 l_entry_value14 := p_entry_value14;
323 end if ;
324
325 elsif(p_input_value_id15 = l_input_value.input_value_id) then
326 if( 'D' = l_input_value.uom) then
327 l_entry_value15 := fnd_date.date_to_displaydate(to_date(p_entry_value15,'YYYY/MM/DD HH24:MI:SS'));
328 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
329 l_entry_value15 := replace(p_entry_value15,'.',',');
330 else
331 l_entry_value15 := p_entry_value15;
332 end if ;
333 end if;
334 end loop;
335 pay_element_entry_api.update_element_entry
336 (p_validate => l_validate
337 ,p_datetrack_update_mode => get_datetrack_mode(p_element_entry_id,p_effective_start_date,p_effective_date,p_datetrack_update_mode)
338 ,p_effective_date => p_effective_date
339 ,p_business_group_id => p_business_group_id
340 ,p_element_entry_id => p_element_entry_id
341 ,p_object_version_number => l_object_version_number
342 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
343 ,p_attribute_category => p_attribute_category
344 ,p_attribute1 => p_attribute1
345 ,p_attribute2 => p_attribute2
346 ,p_attribute3 => p_attribute3
347 ,p_attribute4 => p_attribute4
348 ,p_attribute5 => p_attribute5
349 ,p_attribute6 => p_attribute6
350 ,p_attribute7 => p_attribute7
351 ,p_attribute8 => p_attribute8
352 ,p_attribute9 => p_attribute9
353 ,p_attribute10 => p_attribute10
354 ,p_attribute11 => p_attribute11
355 ,p_attribute12 => p_attribute12
356 ,p_attribute13 => p_attribute13
357 ,p_attribute14 => p_attribute14
358 ,p_attribute15 => p_attribute15
359 ,p_attribute16 => p_attribute16
360 ,p_attribute17 => p_attribute17
361 ,p_attribute18 => p_attribute18
362 ,p_attribute19 => p_attribute19
363 ,p_attribute20 => p_attribute20
364 ,p_input_value_id1 => p_input_value_id1
365 ,p_input_value_id2 => p_input_value_id2
366 ,p_input_value_id3 => p_input_value_id3
367 ,p_input_value_id4 => p_input_value_id4
368 ,p_input_value_id5 => p_input_value_id5
369 ,p_input_value_id6 => p_input_value_id6
370 ,p_input_value_id7 => p_input_value_id7
371 ,p_input_value_id8 => p_input_value_id8
372 ,p_input_value_id9 => p_input_value_id9
373 ,p_input_value_id10 => p_input_value_id10
374 ,p_input_value_id11 => p_input_value_id11
375 ,p_input_value_id12 => p_input_value_id12
376 ,p_input_value_id13 => p_input_value_id13
377 ,p_input_value_id14 => p_input_value_id14
378 ,p_input_value_id15 => p_input_value_id15
379 ,p_entry_value1 => l_entry_value1
380 ,p_entry_value2 => l_entry_value2
381 ,p_entry_value3 => l_entry_value3
382 ,p_entry_value4 => l_entry_value4
383 ,p_entry_value5 => l_entry_value5
384 ,p_entry_value6 => l_entry_value6
385 ,p_entry_value7 => l_entry_value7
386 ,p_entry_value8 => l_entry_value8
387 ,p_entry_value9 => l_entry_value9
388 ,p_entry_value10 => l_entry_value10
389 ,p_entry_value11 => l_entry_value11
390 ,p_entry_value12 => l_entry_value12
391 ,p_entry_value13 => l_entry_value13
392 ,p_entry_value14 => l_entry_value14
393 ,p_entry_value15 => l_entry_value15
394 ,p_entry_information_category => p_entry_information_category
395 ,p_entry_information1 => p_entry_information1
396 ,p_entry_information2 => p_entry_information2
397 ,p_entry_information3 => p_entry_information3
398 ,p_entry_information4 => p_entry_information4
399 ,p_entry_information5 => p_entry_information5
400 ,p_entry_information6 => p_entry_information6
401 ,p_entry_information7 => p_entry_information7
402 ,p_entry_information8 => p_entry_information8
403 ,p_entry_information9 => p_entry_information9
404 ,p_entry_information10 => p_entry_information10
405 ,p_entry_information11 => p_entry_information11
406 ,p_entry_information12 => p_entry_information12
407 ,p_entry_information13 => p_entry_information13
408 ,p_entry_information14 => p_entry_information14
409 ,p_entry_information15 => p_entry_information15
410 ,p_entry_information16 => p_entry_information16
411 ,p_entry_information17 => p_entry_information17
412 ,p_entry_information18 => p_entry_information18
413 ,p_entry_information19 => p_entry_information19
414 ,p_entry_information20 => p_entry_information20
415 ,p_entry_information21 => p_entry_information21
416 ,p_entry_information22 => p_entry_information22
417 ,p_entry_information23 => p_entry_information23
418 ,p_entry_information24 => p_entry_information24
419 ,p_entry_information25 => p_entry_information25
420 ,p_entry_information26 => p_entry_information26
421 ,p_entry_information27 => p_entry_information27
422 ,p_entry_information28 => p_entry_information28
423 ,p_entry_information29 => p_entry_information29
424 ,p_entry_information30 => p_entry_information30
425 ,p_effective_start_date => l_effective_start_date
426 ,p_effective_end_date => l_effective_end_date
427 ,p_update_warning => l_update_warning
428 );
429
430 exception
431
432 when app_exception.application_exception then
433 if(p_validate = 1) then
434 fnd_msg_pub.add;
435 /*
436 fnd_message.parse_encoded(fnd_message.get_encoded,l_app_name,l_msg_name);
437 if('HR_34927_ELE_ENTRY_VSET_INVLD'= l_msg_name) then
438 fnd_message.set_name('PER', 'HR_34927_ELE_ENTRY_VSET_INVLD');
439 fnd_msg_pub.add;
440 else
441 fnd_message.set_name(l_app_name, l_msg_name);
442 fnd_msg_pub.add;
443 end if;
444 */
445 else
446 raise;
447 end if ;
448
449 when OTHERS THEN
450 raise;
451
452 end ICD_UPDATE_ELEMENT_ENTRY;
453
454
455 PROCEDURE ICD_CREATE_ELEMENT_ENTRY
456 (p_validate in number default 0
457 ,p_effective_date in date
458 ,p_business_group_id in number
459 ,p_assignment_id in number
460 ,p_element_link_id in number
461 ,p_entry_type in varchar2
462 ,p_cost_allocation_keyflex_id in number
463 ,p_attribute_category in varchar2
464 ,p_attribute1 in varchar2
465 ,p_attribute2 in varchar2
466 ,p_attribute3 in varchar2
467 ,p_attribute4 in varchar2
468 ,p_attribute5 in varchar2
469 ,p_attribute6 in varchar2
470 ,p_attribute7 in varchar2
471 ,p_attribute8 in varchar2
472 ,p_attribute9 in varchar2
473 ,p_attribute10 in varchar2
474 ,p_attribute11 in varchar2
475 ,p_attribute12 in varchar2
476 ,p_attribute13 in varchar2
477 ,p_attribute14 in varchar2
478 ,p_attribute15 in varchar2
479 ,p_attribute16 in varchar2
480 ,p_attribute17 in varchar2
481 ,p_attribute18 in varchar2
482 ,p_attribute19 in varchar2
483 ,p_attribute20 in varchar2
484 ,p_input_value_id1 in number
485 ,p_input_value_id2 in number
486 ,p_input_value_id3 in number
487 ,p_input_value_id4 in number
488 ,p_input_value_id5 in number
489 ,p_input_value_id6 in number
490 ,p_input_value_id7 in number
491 ,p_input_value_id8 in number
492 ,p_input_value_id9 in number
493 ,p_input_value_id10 in number
494 ,p_input_value_id11 in number
495 ,p_input_value_id12 in number
496 ,p_input_value_id13 in number
497 ,p_input_value_id14 in number
498 ,p_input_value_id15 in number
499 ,p_entry_value1 in varchar2
500 ,p_entry_value2 in varchar2
501 ,p_entry_value3 in varchar2
502 ,p_entry_value4 in varchar2
503 ,p_entry_value5 in varchar2
504 ,p_entry_value6 in varchar2
505 ,p_entry_value7 in varchar2
506 ,p_entry_value8 in varchar2
507 ,p_entry_value9 in varchar2
508 ,p_entry_value10 in varchar2
509 ,p_entry_value11 in varchar2
510 ,p_entry_value12 in varchar2
511 ,p_entry_value13 in varchar2
512 ,p_entry_value14 in varchar2
513 ,p_entry_value15 in varchar2
514 ,p_entry_information_category in varchar2
515 ,p_entry_information1 in varchar2
516 ,p_entry_information2 in varchar2
517 ,p_entry_information3 in varchar2
518 ,p_entry_information4 in varchar2
519 ,p_entry_information5 in varchar2
520 ,p_entry_information6 in varchar2
521 ,p_entry_information7 in varchar2
522 ,p_entry_information8 in varchar2
523 ,p_entry_information9 in varchar2
524 ,p_entry_information10 in varchar2
525 ,p_entry_information11 in varchar2
526 ,p_entry_information12 in varchar2
527 ,p_entry_information13 in varchar2
528 ,p_entry_information14 in varchar2
529 ,p_entry_information15 in varchar2
530 ,p_entry_information16 in varchar2
531 ,p_entry_information17 in varchar2
532 ,p_entry_information18 in varchar2
533 ,p_entry_information19 in varchar2
534 ,p_entry_information20 in varchar2
535 ,p_entry_information21 in varchar2
536 ,p_entry_information22 in varchar2
537 ,p_entry_information23 in varchar2
538 ,p_entry_information24 in varchar2
539 ,p_entry_information25 in varchar2
540 ,p_entry_information26 in varchar2
541 ,p_entry_information27 in varchar2
542 ,p_entry_information28 in varchar2
543 ,p_entry_information29 in varchar2
544 ,p_entry_information30 in varchar2
545 ,p_effective_start_date in date
546 ,p_effective_end_date in date
547 -- ,p_icd_effective_date in date
548 -- ,p_warning out nocopy number
549 ) is
550
551 cursor c_input_values is
552 select * from
553 pay_input_values_f
554 where element_type_id = (select element_type_id from pay_element_links_f where
555 element_link_id = p_element_link_id and p_effective_date between effective_start_date and effective_end_date)
556 and p_effective_date between effective_start_date and effective_end_date
557 order by input_value_id asc;
558
559 l_effective_start_date date;
560 l_effective_end_date date;
561 l_create_warning boolean;
562 l_object_version_number number;
563 l_element_entry_id number;
564 l_validate boolean;
565 l_entry_value1 varchar2(60);
566 l_entry_value2 varchar2(60);
567 l_entry_value3 varchar2(60);
568 l_entry_value4 varchar2(60);
569 l_entry_value5 varchar2(60);
570 l_entry_value6 varchar2(60);
571 l_entry_value7 varchar2(60);
572 l_entry_value8 varchar2(60);
573 l_entry_value9 varchar2(60);
574 l_entry_value10 varchar2(60);
575 l_entry_value11 varchar2(60);
576 l_entry_value12 varchar2(60);
577 l_entry_value13 varchar2(60);
578 l_entry_value14 varchar2(60);
579 l_entry_value15 varchar2(60);
580 l_delete_warning boolean;
581 --changes for ICD number formatting issue
582 icx_numeric varchar2(20);
583
584 l_app_name varchar2(50);
585 l_msg_name varchar2(30);
586
587 begin
588 fnd_msg_pub.initialize;
589 l_validate := false;
590 if p_validate = 1 then
591 l_validate := true;
592 end if;
593
594 icx_numeric := fnd_profile.value('ICX_NUMERIC_CHARACTERS');
595
596 savepoint create_entry;
597 for l_input_value in c_input_values loop
598 if(p_input_value_id1 = l_input_value.input_value_id ) then
599 -- the format coming in is already canonical but the api is expecting a user format
600 if( 'D' = l_input_value.uom) then
601 l_entry_value1 := fnd_date.date_to_displaydate(to_date(p_entry_value1,'YYYY/MM/DD HH24:MI:SS'));
602 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
603 l_entry_value1 := replace(p_entry_value1,'.',',');
604 else
605 l_entry_value1 := p_entry_value1;
606 end if ;
607 elsif(p_input_value_id2 = l_input_value.input_value_id ) then
608 if( 'D' = l_input_value.uom) then
609 l_entry_value2 := fnd_date.date_to_displaydate(to_date(p_entry_value2,'YYYY/MM/DD HH24:MI:SS'));
610 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
611 l_entry_value2 := replace(p_entry_value2,'.',',');
612 else
613 l_entry_value2 := p_entry_value2;
614 end if ;
615
616 elsif(p_input_value_id3 = l_input_value.input_value_id) then
617 if( 'D' = l_input_value.uom) then
618 l_entry_value3 := fnd_date.date_to_displaydate(to_date(p_entry_value3,'YYYY/MM/DD HH24:MI:SS'));
619 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
620 l_entry_value3 := replace(p_entry_value3,'.',',');
621 else
622 l_entry_value3 := p_entry_value3;
623 end if ;
624
625 elsif(p_input_value_id4 = l_input_value.input_value_id) then
626 if( 'D' = l_input_value.uom) then
627 l_entry_value4 := fnd_date.date_to_displaydate(to_date(p_entry_value4,'YYYY/MM/DD HH24:MI:SS'));
628 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
629 l_entry_value4 := replace(p_entry_value4,'.',',');
630 else
631 l_entry_value4 := p_entry_value4;
632 end if ;
633
634 elsif(p_input_value_id5 = l_input_value.input_value_id) then
635 if( 'D' = l_input_value.uom) then
636 l_entry_value5 := fnd_date.date_to_displaydate(to_date(p_entry_value5,'YYYY/MM/DD HH24:MI:SS'));
637 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
638 l_entry_value5 := replace(p_entry_value5,'.',',');
639 else
640 l_entry_value5 := p_entry_value5;
641 end if ;
642
643 elsif(p_input_value_id6 = l_input_value.input_value_id) then
644 if( 'D' = l_input_value.uom) then
645 l_entry_value6 := fnd_date.date_to_displaydate(to_date(p_entry_value6,'YYYY/MM/DD HH24:MI:SS'));
646 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
647 l_entry_value6 := replace(p_entry_value6,'.',',');
648 else
649 l_entry_value6 := p_entry_value6;
650 end if ;
651
652 elsif(p_input_value_id7 = l_input_value.input_value_id ) then
653 if( 'D' = l_input_value.uom) then
654 l_entry_value7 := fnd_date.date_to_displaydate(to_date(p_entry_value7,'YYYY/MM/DD HH24:MI:SS'));
655 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
656 l_entry_value7 := replace(p_entry_value7,'.',',');
657 else
658 l_entry_value7 := p_entry_value7;
659 end if ;
660
661 elsif(p_input_value_id8 = l_input_value.input_value_id) then
662 if( 'D' = l_input_value.uom) then
663 l_entry_value8 := fnd_date.date_to_displaydate(to_date(p_entry_value8,'YYYY/MM/DD HH24:MI:SS'));
664 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
665 l_entry_value8 := replace(p_entry_value8,'.',',');
666 else
667 l_entry_value8 := p_entry_value8;
668 end if ;
669
670 elsif(p_input_value_id9 = l_input_value.input_value_id) then
671 if( 'D' = l_input_value.uom) then
672 l_entry_value9 := fnd_date.date_to_displaydate(to_date(p_entry_value9,'YYYY/MM/DD HH24:MI:SS'));
673 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
674 l_entry_value9 := replace(p_entry_value9,'.',',');
675 else
676 l_entry_value9 := p_entry_value9;
677 end if ;
678 elsif(p_input_value_id10 = l_input_value.input_value_id) then
679 if( 'D' = l_input_value.uom) then
680 l_entry_value10 := fnd_date.date_to_displaydate(to_date(p_entry_value10,'YYYY/MM/DD HH24:MI:SS'));
681 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
682 l_entry_value10 := replace(p_entry_value10,'.',',');
683 else
684 l_entry_value10 := p_entry_value10;
685 end if ;
686 elsif(p_input_value_id11 = l_input_value.input_value_id) then
687 if( 'D' = l_input_value.uom) then
688 l_entry_value11 := fnd_date.date_to_displaydate(to_date(p_entry_value11,'YYYY/MM/DD HH24:MI:SS'));
689 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
690 l_entry_value11 := replace(p_entry_value11,'.',',');
691 else
692 l_entry_value11 := p_entry_value11;
693 end if ;
694 elsif(p_input_value_id12 = l_input_value.input_value_id) then
695 if( 'D' = l_input_value.uom) then
696 l_entry_value12 := fnd_date.date_to_displaydate(to_date(p_entry_value12,'YYYY/MM/DD HH24:MI:SS'));
697 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
698 l_entry_value12 := replace(p_entry_value12,'.',',');
699 else
700 l_entry_value12 := p_entry_value12;
701 end if ;
702 elsif(p_input_value_id13 = l_input_value.input_value_id) then
703 if( 'D' = l_input_value.uom) then
704 l_entry_value13 := fnd_date.date_to_displaydate(to_date(p_entry_value13,'YYYY/MM/DD HH24:MI:SS'));
705 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
706 l_entry_value13 := replace(p_entry_value13,'.',',');
707 else
708 l_entry_value13 := p_entry_value13;
709 end if ;
710 elsif(p_input_value_id14 = l_input_value.input_value_id ) then
711 if( 'D' = l_input_value.uom) then
712 l_entry_value14 := fnd_date.date_to_displaydate(to_date(p_entry_value14,'YYYY/MM/DD HH24:MI:SS'));
713 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
714 l_entry_value14 := replace(p_entry_value14,'.',',');
715 else
716 l_entry_value14 := p_entry_value14;
717 end if ;
718
719 elsif(p_input_value_id15 = l_input_value.input_value_id) then
720 if( 'D' = l_input_value.uom) then
721 l_entry_value15 := fnd_date.date_to_displaydate(to_date(p_entry_value15,'YYYY/MM/DD HH24:MI:SS'));
722 elsif ('M' = l_input_value.uom and icx_numeric <> '.,')then
723 l_entry_value15 := replace(p_entry_value15,'.',',');
724 else
725 l_entry_value15 := p_entry_value15;
726 end if ;
727 end if;
728 end loop;
729 pay_element_entry_api.create_element_entry
730 (p_validate => false
731 ,p_effective_date => p_effective_date
732 ,p_business_group_id => p_business_group_id
733 ,p_assignment_id => p_assignment_id
734 ,p_element_link_id => p_element_link_id
735 ,p_entry_type => p_entry_type
736 ,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
737 ,p_attribute_category => p_attribute_category
738 ,p_attribute1 => p_attribute1
739 ,p_attribute2 => p_attribute2
740 ,p_attribute3 => p_attribute3
741 ,p_attribute4 => p_attribute4
742 ,p_attribute5 => p_attribute5
743 ,p_attribute6 => p_attribute6
744 ,p_attribute7 => p_attribute7
745 ,p_attribute8 => p_attribute8
746 ,p_attribute9 => p_attribute9
747 ,p_attribute10 => p_attribute10
748 ,p_attribute11 => p_attribute11
749 ,p_attribute12 => p_attribute12
750 ,p_attribute13 => p_attribute13
751 ,p_attribute14 => p_attribute14
752 ,p_attribute15 => p_attribute15
753 ,p_attribute16 => p_attribute16
754 ,p_attribute17 => p_attribute17
755 ,p_attribute18 => p_attribute18
756 ,p_attribute19 => p_attribute19
757 ,p_attribute20 => p_attribute20
758 ,p_input_value_id1 => p_input_value_id1
759 ,p_input_value_id2 => p_input_value_id2
760 ,p_input_value_id3 => p_input_value_id3
761 ,p_input_value_id4 => p_input_value_id4
762 ,p_input_value_id5 => p_input_value_id5
763 ,p_input_value_id6 => p_input_value_id6
764 ,p_input_value_id7 => p_input_value_id7
765 ,p_input_value_id8 => p_input_value_id8
766 ,p_input_value_id9 => p_input_value_id9
767 ,p_input_value_id10 => p_input_value_id10
768 ,p_input_value_id11 => p_input_value_id11
769 ,p_input_value_id12 => p_input_value_id12
770 ,p_input_value_id13 => p_input_value_id13
771 ,p_input_value_id14 => p_input_value_id14
772 ,p_input_value_id15 => p_input_value_id15
773 ,p_entry_value1 => l_entry_value1
774 ,p_entry_value2 => l_entry_value2
775 ,p_entry_value3 => l_entry_value3
776 ,p_entry_value4 => l_entry_value4
777 ,p_entry_value5 => l_entry_value5
778 ,p_entry_value6 => l_entry_value6
779 ,p_entry_value7 => l_entry_value7
780 ,p_entry_value8 => l_entry_value8
781 ,p_entry_value9 => l_entry_value9
782 ,p_entry_value10 => l_entry_value10
783 ,p_entry_value11 => l_entry_value11
784 ,p_entry_value12 => l_entry_value12
785 ,p_entry_value13 => l_entry_value13
786 ,p_entry_value14 => l_entry_value14
787 ,p_entry_value15 => l_entry_value15
788 ,p_entry_information_category => p_entry_information_category
789 ,p_entry_information1 => p_entry_information1
790 ,p_entry_information2 => p_entry_information2
791 ,p_entry_information3 => p_entry_information3
792 ,p_entry_information4 => p_entry_information4
793 ,p_entry_information5 => p_entry_information5
794 ,p_entry_information6 => p_entry_information6
795 ,p_entry_information7 => p_entry_information7
796 ,p_entry_information8 => p_entry_information8
797 ,p_entry_information9 => p_entry_information9
798 ,p_entry_information10 => p_entry_information10
799 ,p_entry_information11 => p_entry_information11
800 ,p_entry_information12 => p_entry_information12
801 ,p_entry_information13 => p_entry_information13
802 ,p_entry_information14 => p_entry_information14
803 ,p_entry_information15 => p_entry_information15
804 ,p_entry_information16 => p_entry_information16
805 ,p_entry_information17 => p_entry_information17
806 ,p_entry_information18 => p_entry_information18
807 ,p_entry_information19 => p_entry_information19
808 ,p_entry_information20 => p_entry_information20
809 ,p_entry_information21 => p_entry_information21
810 ,p_entry_information22 => p_entry_information22
811 ,p_entry_information23 => p_entry_information23
812 ,p_entry_information24 => p_entry_information24
813 ,p_entry_information25 => p_entry_information25
814 ,p_entry_information26 => p_entry_information26
815 ,p_entry_information27 => p_entry_information27
816 ,p_entry_information28 => p_entry_information28
817 ,p_entry_information29 => p_entry_information29
818 ,p_entry_information30 => p_entry_information30
819 ,p_effective_start_date => l_effective_start_date
820 ,p_effective_end_date => l_effective_end_date
821 ,p_element_entry_id => l_element_entry_id
822 ,p_object_version_number => l_object_version_number
823 ,p_create_warning => l_create_warning
824 );
825
826 -- try to end date the entry if it was entered on the page.
827 if(p_effective_end_date is not null and p_effective_end_date <> hr_api.g_eot) then
828 -- we need to call the delete for this record.
829 -- if this is a non recurring entry then the end date might be after the effective_end_date
830 -- of the record, we need not delete in that case.
831 if( p_effective_end_date < l_effective_end_date) then
832 pay_element_entry_api.delete_element_entry
833 (p_validate => false
834 ,p_datetrack_delete_mode => hr_api.g_delete
835 ,p_effective_date => p_effective_end_date
836 ,p_element_entry_id => l_element_entry_id
837 ,p_object_version_number => l_object_version_number
838 ,p_effective_start_date => l_effective_start_date
839 ,p_effective_end_date => l_effective_end_date
840 ,p_delete_warning => l_delete_warning
841 );
842 end if;
843 end if;
844
845 if(l_validate) then
846 rollback to create_entry;
847 end if;
848 exception
849
850 when app_exception.application_exception then
851 rollback to create_entry;
852 if(p_validate = 1) then
853 fnd_msg_pub.add;
854 /*
855 -- this is when we look for the multiple entries allowed exception
856 fnd_message.parse_encoded(fnd_message.get_encoded,l_app_name,l_msg_name);
857 if('HR_7455_PLK_ELE_ENTRY_EXISTS'= l_msg_name) then
858 fnd_message.set_name('BEN', 'BEN_ICD_ELE_EXISTS');
859 fnd_msg_pub.add;
860 elsif('HR_34927_ELE_ENTRY_VSET_INVLD'= l_msg_name) then
861 fnd_message.set_name('PER', 'HR_34927_ELE_ENTRY_VSET_INVLD');
862 fnd_msg_pub.add;
863 else
864 fnd_message.set_name(l_app_name, l_msg_name);
865 fnd_msg_pub.add;
866 end if;
867 */
868 else
869 -- this is when its being called from the final process api after approval
870 raise;
871 end if ;
872 when OTHERS THEN
873 rollback to create_entry;
874 raise;
875
876 end ICD_CREATE_ELEMENT_ENTRY;
877
878 PROCEDURE ICD_DELETE_ELEMENT_ENTRY(
879 p_validate in number default 0
880 ,p_datetrack_delete_mode in varchar2
881 ,p_effective_date in date
882 ,p_element_entry_id in number
883 ,p_object_version_number in number
884 ,p_effective_start_date in date
885 ,p_effective_end_date in date
886 -- ,p_icd_effective_date in date
887 -- ,p_warning out nocopy number
888 ) is
889 l_object_version_number number;
890 l_effective_start_date date;
891 l_effective_end_date date;
892 l_delete_warning boolean;
893 l_validate boolean;
894 l_app_name varchar2(50);
895 l_msg_name varchar2(30);
896
897 begin
898 fnd_msg_pub.initialize;
899 l_object_version_number := p_object_version_number;
900 l_validate := false;
901 if p_validate = 1 then
902 l_validate := true;
903 end if;
904
905 pay_element_entry_api.delete_element_entry
906 (p_validate => l_validate
907 ,p_datetrack_delete_mode => get_datetrack_mode(p_element_entry_id,p_effective_start_date,p_effective_date,p_datetrack_delete_mode)
908 ,p_effective_date => p_effective_date
909 ,p_element_entry_id => p_element_entry_id
910 ,p_object_version_number => l_object_version_number
911 ,p_effective_start_date => l_effective_start_date
912 ,p_effective_end_date => l_effective_end_date
913 ,p_delete_warning => l_delete_warning
914 );
915
916 exception
917
918 when app_exception.application_exception then
919 if(p_validate = 1) then
920 fnd_msg_pub.add;
921 /*
922 fnd_message.parse_encoded(fnd_message.get_encoded,l_app_name,l_msg_name);
923 if('HR_33000_ENTRY_CANT_PURGE'= l_msg_name) then
924 fnd_message.set_name('PER', 'HR_33000_ENTRY_CANT_PURGE');
925 fnd_msg_pub.add;
926 else
927 fnd_message.set_name(l_app_name, l_msg_name);
928 fnd_msg_pub.add;
929 end if;
930 */
931 else
932 raise;
933 end if ;
934
935 when others then
936 raise;
937 end ICD_DELETE_ELEMENT_ENTRY;
938
939 -----------------------------Get Hr Transaction Api----------------------
940
941 procedure GET_HR_TRANSACTION_ID
942 (
943 p_item_type in varchar2
944 ,p_item_key in varchar2
945 ,p_activity_id in number
946 ,p_login_person_id in number
947 ,p_person_id in number
948 ,p_transaction_id out nocopy number
949 ,p_transaction_step_id out nocopy number
950 ) is
951 --
952 cursor c_txn_step(p_transaction_id number) is
953 select hats.transaction_step_id
954 from hr_api_transaction_steps hats
955 where hats.transaction_id = p_transaction_id
956 and hats.api_name = upper(g_package || 'process_api')
957 order by hats.transaction_step_id;
958 --
959 l_transaction_id number := null;
960 l_transaction_step_id number := null;
961 l_result varchar2(100);
962 l_trans_obj_vers_num number;
963 l_processing_order number := 1;
964 --
965 begin
966 l_transaction_id := hr_transaction_ss.get_transaction_id
967 (p_item_type => p_item_type
968 ,p_item_key => p_item_key);
969
970 if l_transaction_id is null then
971 hr_transaction_ss.start_transaction
972 (itemtype => p_item_type
973 ,itemkey => p_item_key
974 ,actid => p_activity_id
975 ,funmode => 'RUN'
976 ,p_login_person_id => p_login_person_id
977 ,result => l_result);
978
979 l_transaction_id := hr_transaction_ss.get_transaction_id
980 (p_item_type => p_item_type
981 ,p_item_key => p_item_key);
982 end if;
983
984 --
985 -- get the transaction_step_id
986 --
987 Open c_txn_step(l_transaction_id);
988 Fetch c_txn_step into l_transaction_step_id;
989 Close c_txn_step;
990
991 -- if it is not available, create it.
992 if l_transaction_step_id is null then
993 --
994 hr_transaction_api.create_transaction_step
995 (p_validate => false
996 ,p_creator_person_id => p_login_person_id
997 ,p_transaction_id => l_transaction_id
998 ,p_api_name => upper(g_package || 'process_api')
999 ,p_item_type => p_item_type
1000 ,p_item_key => p_item_key
1001 ,p_activity_id => p_activity_id
1002 ,p_transaction_step_id => l_transaction_step_id
1003 ,p_object_version_number => l_trans_obj_vers_num);
1004 --
1005 -- insert a row in the transaction values table.
1006 hr_transaction_api.set_varchar2_value
1007 (p_transaction_step_id => l_transaction_step_id
1008 ,p_person_id => p_login_person_id
1009 ,p_name => 'P_REVIEW_PROC_CALL'
1010 ,p_value => 'BenAdvancedCompensation');
1011
1012 hr_transaction_api.set_varchar2_value
1013 (p_transaction_step_id => l_transaction_step_id
1014 ,p_person_id => p_login_person_id
1015 ,p_name => 'P_REVIEW_ACTID'
1016 ,p_value => to_char(p_activity_id));
1017
1018
1019 end if;
1020 p_transaction_id := l_transaction_id;
1021 p_transaction_step_id := l_transaction_step_id;
1022
1023 end GET_HR_TRANSACTION_ID;
1024
1025 procedure process_transaction_row
1026 (p_transaction_row in ben_icd_transaction%rowtype
1027 ,p_validate in number
1028 ,p_icd_effective_date in date)
1029 is
1030 l_warning number;
1031 effectiveDate Date ;
1032 begin
1033
1034 IF 'UPDATE' = p_transaction_row.dml_operation then
1035 if(p_transaction_row.effective_date is null )then
1036 if(p_transaction_row.effective_start_date > trunc(sysdate) ) then
1037 effectiveDate := p_transaction_row.effective_start_date;
1038 else
1039 effectiveDate := trunc(sysdate);
1040 end if;
1041 else
1042 effectiveDate := p_transaction_row.effective_date;
1043 end if;
1044 ICD_UPDATE_ELEMENT_ENTRY
1045 (p_validate => p_validate
1046 ,p_datetrack_update_mode => p_transaction_row.datetrack_mode
1047 ,p_effective_date => effectiveDate
1048 ,p_business_group_id => p_transaction_row.business_group_id
1049 ,p_element_entry_id => p_transaction_row.element_entry_id
1050 ,p_object_version_number => p_transaction_row.e_object_version_number
1051 ,p_cost_allocation_keyflex_id => p_transaction_row.cost_allocation_keyflex_id
1052 ,p_attribute_category => p_transaction_row.attribute_category
1053 ,p_attribute1 => p_transaction_row.attribute1
1054 ,p_attribute2 => p_transaction_row.attribute2
1055 ,p_attribute3 => p_transaction_row.attribute3
1056 ,p_attribute4 => p_transaction_row.attribute4
1057 ,p_attribute5 => p_transaction_row.attribute5
1058 ,p_attribute6 => p_transaction_row.attribute6
1059 ,p_attribute7 => p_transaction_row.attribute7
1060 ,p_attribute8 => p_transaction_row.attribute8
1061 ,p_attribute9 => p_transaction_row.attribute9
1062 ,p_attribute10 => p_transaction_row.attribute10
1063 ,p_attribute11 => p_transaction_row.attribute11
1064 ,p_attribute12 => p_transaction_row.attribute12
1065 ,p_attribute13 => p_transaction_row.attribute13
1066 ,p_attribute14 => p_transaction_row.attribute14
1067 ,p_attribute15 => p_transaction_row.attribute15
1068 ,p_attribute16 => p_transaction_row.attribute16
1069 ,p_attribute17 => p_transaction_row.attribute17
1070 ,p_attribute18 => p_transaction_row.attribute18
1071 ,p_attribute19 => p_transaction_row.attribute19
1072 ,p_attribute20 => p_transaction_row.attribute20
1073 ,p_input_value_id1 => p_transaction_row.input_value_id1
1074 ,p_input_value_id2 => p_transaction_row.input_value_id2
1075 ,p_input_value_id3 => p_transaction_row.input_value_id3
1076 ,p_input_value_id4 => p_transaction_row.input_value_id4
1077 ,p_input_value_id5 => p_transaction_row.input_value_id5
1078 ,p_input_value_id6 => p_transaction_row.input_value_id6
1079 ,p_input_value_id7 => p_transaction_row.input_value_id7
1080 ,p_input_value_id8 => p_transaction_row.input_value_id8
1081 ,p_input_value_id9 => p_transaction_row.input_value_id9
1082 ,p_input_value_id10 => p_transaction_row.input_value_id10
1083 ,p_input_value_id11 => p_transaction_row.input_value_id11
1084 ,p_input_value_id12 => p_transaction_row.input_value_id12
1085 ,p_input_value_id13 => p_transaction_row.input_value_id13
1086 ,p_input_value_id14 => p_transaction_row.input_value_id14
1087 ,p_input_value_id15 => p_transaction_row.input_value_id15
1088 ,p_entry_value1 => p_transaction_row.input_value1
1089 ,p_entry_value2 => p_transaction_row.input_value2
1090 ,p_entry_value3 => p_transaction_row.input_value3
1091 ,p_entry_value4 => p_transaction_row.input_value4
1092 ,p_entry_value5 => p_transaction_row.input_value5
1093 ,p_entry_value6 => p_transaction_row.input_value6
1094 ,p_entry_value7 => p_transaction_row.input_value7
1095 ,p_entry_value8 => p_transaction_row.input_value8
1096 ,p_entry_value9 => p_transaction_row.input_value9
1097 ,p_entry_value10 => p_transaction_row.input_value10
1098 ,p_entry_value11 => p_transaction_row.input_value11
1099 ,p_entry_value12 => p_transaction_row.input_value12
1100 ,p_entry_value13 => p_transaction_row.input_value13
1101 ,p_entry_value14 => p_transaction_row.input_value14
1102 ,p_entry_value15 => p_transaction_row.input_value15
1103 ,p_entry_information_category => p_transaction_row.entry_information_category
1104 ,p_entry_information1 => p_transaction_row.entry_information1
1105 ,p_entry_information2 => p_transaction_row.entry_information2
1106 ,p_entry_information3 => p_transaction_row.entry_information3
1107 ,p_entry_information4 => p_transaction_row.entry_information4
1108 ,p_entry_information5 => p_transaction_row.entry_information5
1109 ,p_entry_information6 => p_transaction_row.entry_information6
1110 ,p_entry_information7 => p_transaction_row.entry_information7
1111 ,p_entry_information8 => p_transaction_row.entry_information8
1112 ,p_entry_information9 => p_transaction_row.entry_information9
1113 ,p_entry_information10 => p_transaction_row.entry_information10
1114 ,p_entry_information11 => p_transaction_row.entry_information11
1115 ,p_entry_information12 => p_transaction_row.entry_information12
1116 ,p_entry_information13 => p_transaction_row.entry_information13
1117 ,p_entry_information14 => p_transaction_row.entry_information14
1118 ,p_entry_information15 => p_transaction_row.entry_information15
1119 ,p_entry_information16 => p_transaction_row.entry_information16
1120 ,p_entry_information17 => p_transaction_row.entry_information17
1121 ,p_entry_information18 => p_transaction_row.entry_information18
1122 ,p_entry_information19 => p_transaction_row.entry_information19
1123 ,p_entry_information20 => p_transaction_row.entry_information20
1124 ,p_entry_information21 => p_transaction_row.entry_information21
1125 ,p_entry_information22 => p_transaction_row.entry_information22
1126 ,p_entry_information23 => p_transaction_row.entry_information23
1127 ,p_entry_information24 => p_transaction_row.entry_information24
1128 ,p_entry_information25 => p_transaction_row.entry_information25
1129 ,p_entry_information26 => p_transaction_row.entry_information26
1130 ,p_entry_information27 => p_transaction_row.entry_information27
1131 ,p_entry_information28 => p_transaction_row.entry_information28
1132 ,p_entry_information29 => p_transaction_row.entry_information29
1133 ,p_entry_information30 => p_transaction_row.entry_information30
1134 ,p_effective_start_date => p_transaction_row.effective_start_Date
1135 ,p_effective_end_date => p_transaction_row.effective_end_date
1136 --,p_icd_effective_date => p_icd_effective_date
1137 --,p_warning => l_warning
1138 );
1139 elsif ('INSERT' = p_transaction_row.dml_operation) then
1140 ICD_CREATE_ELEMENT_ENTRY
1141 (p_validate => p_validate
1142 ,p_effective_date => nvl(p_transaction_row.effective_date,TRUNC(SYSDATE))
1143 ,p_business_group_id => p_transaction_row.business_group_id
1144 ,p_assignment_id => p_transaction_row.assignment_id
1145 ,p_element_link_id => p_transaction_row.element_link_id
1146 ,p_entry_type => 'E'
1147 ,p_cost_allocation_keyflex_id => p_transaction_row.cost_allocation_keyflex_id
1148 ,p_attribute_category => p_transaction_row.attribute_category
1149 ,p_attribute1 => p_transaction_row.attribute1
1150 ,p_attribute2 => p_transaction_row.attribute2
1151 ,p_attribute3 => p_transaction_row.attribute3
1152 ,p_attribute4 => p_transaction_row.attribute4
1153 ,p_attribute5 => p_transaction_row.attribute5
1154 ,p_attribute6 => p_transaction_row.attribute6
1155 ,p_attribute7 => p_transaction_row.attribute7
1156 ,p_attribute8 => p_transaction_row.attribute8
1157 ,p_attribute9 => p_transaction_row.attribute9
1158 ,p_attribute10 => p_transaction_row.attribute10
1159 ,p_attribute11 => p_transaction_row.attribute11
1160 ,p_attribute12 => p_transaction_row.attribute12
1161 ,p_attribute13 => p_transaction_row.attribute13
1162 ,p_attribute14 => p_transaction_row.attribute14
1163 ,p_attribute15 => p_transaction_row.attribute15
1164 ,p_attribute16 => p_transaction_row.attribute16
1165 ,p_attribute17 => p_transaction_row.attribute17
1166 ,p_attribute18 => p_transaction_row.attribute18
1167 ,p_attribute19 => p_transaction_row.attribute19
1168 ,p_attribute20 => p_transaction_row.attribute20
1169 ,p_input_value_id1 => p_transaction_row.input_value_id1
1170 ,p_input_value_id2 => p_transaction_row.input_value_id2
1171 ,p_input_value_id3 => p_transaction_row.input_value_id3
1172 ,p_input_value_id4 => p_transaction_row.input_value_id4
1173 ,p_input_value_id5 => p_transaction_row.input_value_id5
1174 ,p_input_value_id6 => p_transaction_row.input_value_id6
1175 ,p_input_value_id7 => p_transaction_row.input_value_id7
1176 ,p_input_value_id8 => p_transaction_row.input_value_id8
1177 ,p_input_value_id9 => p_transaction_row.input_value_id9
1178 ,p_input_value_id10 => p_transaction_row.input_value_id10
1179 ,p_input_value_id11 => p_transaction_row.input_value_id11
1180 ,p_input_value_id12 => p_transaction_row.input_value_id12
1181 ,p_input_value_id13 => p_transaction_row.input_value_id13
1182 ,p_input_value_id14 => p_transaction_row.input_value_id14
1183 ,p_input_value_id15 => p_transaction_row.input_value_id15
1184 ,p_entry_value1 => p_transaction_row.input_value1
1185 ,p_entry_value2 => p_transaction_row.input_value2
1186 ,p_entry_value3 => p_transaction_row.input_value3
1187 ,p_entry_value4 => p_transaction_row.input_value4
1188 ,p_entry_value5 => p_transaction_row.input_value5
1189 ,p_entry_value6 => p_transaction_row.input_value6
1190 ,p_entry_value7 => p_transaction_row.input_value7
1191 ,p_entry_value8 => p_transaction_row.input_value8
1192 ,p_entry_value9 => p_transaction_row.input_value9
1193 ,p_entry_value10 => p_transaction_row.input_value10
1194 ,p_entry_value11 => p_transaction_row.input_value11
1195 ,p_entry_value12 => p_transaction_row.input_value12
1196 ,p_entry_value13 => p_transaction_row.input_value13
1197 ,p_entry_value14 => p_transaction_row.input_value14
1198 ,p_entry_value15 => p_transaction_row.input_value15
1199 ,p_entry_information_category => p_transaction_row.entry_information_category
1200 ,p_entry_information1 => p_transaction_row.entry_information1
1201 ,p_entry_information2 => p_transaction_row.entry_information2
1202 ,p_entry_information3 => p_transaction_row.entry_information3
1203 ,p_entry_information4 => p_transaction_row.entry_information4
1204 ,p_entry_information5 => p_transaction_row.entry_information5
1205 ,p_entry_information6 => p_transaction_row.entry_information6
1206 ,p_entry_information7 => p_transaction_row.entry_information7
1207 ,p_entry_information8 => p_transaction_row.entry_information8
1208 ,p_entry_information9 => p_transaction_row.entry_information9
1209 ,p_entry_information10 => p_transaction_row.entry_information10
1210 ,p_entry_information11 => p_transaction_row.entry_information11
1211 ,p_entry_information12 => p_transaction_row.entry_information12
1212 ,p_entry_information13 => p_transaction_row.entry_information13
1213 ,p_entry_information14 => p_transaction_row.entry_information14
1214 ,p_entry_information15 => p_transaction_row.entry_information15
1215 ,p_entry_information16 => p_transaction_row.entry_information16
1216 ,p_entry_information17 => p_transaction_row.entry_information17
1217 ,p_entry_information18 => p_transaction_row.entry_information18
1218 ,p_entry_information19 => p_transaction_row.entry_information19
1219 ,p_entry_information20 => p_transaction_row.entry_information20
1220 ,p_entry_information21 => p_transaction_row.entry_information21
1221 ,p_entry_information22 => p_transaction_row.entry_information22
1222 ,p_entry_information23 => p_transaction_row.entry_information23
1223 ,p_entry_information24 => p_transaction_row.entry_information24
1224 ,p_entry_information25 => p_transaction_row.entry_information25
1225 ,p_entry_information26 => p_transaction_row.entry_information26
1226 ,p_entry_information27 => p_transaction_row.entry_information27
1227 ,p_entry_information28 => p_transaction_row.entry_information28
1228 ,p_entry_information29 => p_transaction_row.entry_information29
1229 ,p_entry_information30 => p_transaction_row.entry_information30
1230 ,p_effective_start_date => p_transaction_row.effective_start_Date
1231 ,p_effective_end_date => p_transaction_row.effective_end_date
1232 -- ,p_icd_effective_date => p_icd_effective_date
1233 -- ,p_warning => l_warning
1234 );
1235 elsif ('DELETE' = p_transaction_row.dml_operation) then
1236 ICD_DELETE_ELEMENT_ENTRY(
1237 p_validate => p_validate
1238 ,p_datetrack_delete_mode => p_transaction_row.datetrack_mode
1239 ,p_effective_date => p_transaction_row.effective_date
1240 ,p_element_entry_id => p_transaction_row.element_entry_id
1241 ,p_object_version_number => p_transaction_row.e_object_version_number
1242 ,p_effective_start_date => p_transaction_row.effective_start_Date
1243 ,p_effective_end_date => p_transaction_row.effective_end_date
1244 -- ,p_icd_effective_date => p_icd_effective_date
1245 -- ,p_warning => l_warning
1246 );
1247 end if;
1248 end process_transaction_row;
1249
1250 procedure create_person_action_items
1251 (p_person_id in number
1252 ,p_assignment_id in number
1253 ,p_pl_id in number
1254 ,p_icd_transaction_id in number
1255 ,p_effective_date in date
1256 ,p_mandatory_action_item out nocopy varchar2
1257 ) is
1258 cursor c_action_items is
1259 select actn.actn_typ_id,popl.pl_id,popl.mandatory,popl.actn_typ_due_dt_cd
1260 from
1261 ben_popl_actn_typ_f popl,ben_actn_typ_tl actn
1262 where popl.pl_id = p_pl_id
1263 and p_effective_Date between popl.effective_start_Date and popl.effective_end_date
1264 and actn.language = userenv('LANG')
1265 and popl.actn_typ_id = actn.actn_typ_id
1266 and not exists
1267 (select 'Y'
1268 from ben_person_action_items per
1269 where per.person_id = p_person_id
1270 and per.complete_date is not null
1271 and per.actn_typ_id = actn.actn_typ_id
1272 and popl.once_or_always <> 'ALW'
1273 and ((popl.once_or_always = 'ONCE') or
1274 (popl.once_or_always = 'PLAN' and per.pl_id = popl.pl_id)));
1275 l_returned_date date;
1276 begin
1277 p_mandatory_action_item := 'N';
1278 for l_action_items in c_action_items loop
1279 l_returned_date:=null;
1280 if(l_action_items.actn_typ_due_dt_cd is not null) then
1281 ben_determine_date.main
1282 (p_date_cd=>l_action_items.actn_typ_due_dt_cd
1283 ,p_person_id=>p_person_id
1284 ,p_effective_date=>p_effective_date
1285 ,p_returned_date=>l_returned_date);
1286 end if;
1287
1288 insert into ben_person_action_items
1289 (PERSON_ACTION_ITEM_ID
1290 ,ACTN_TYP_ID
1291 ,TRANSACTION_TYPE
1292 ,PERSON_ID
1293 ,ASSIGNMENT_ID
1294 ,EFFECTIVE_DATE
1295 ,PL_ID
1296 ,STATUS
1297 ,DUE_DATE
1298 ,OBJECT_VERSION_NUMBER
1299 ,COMPLETE_DATE
1300 ,VOID_DATE
1301 ,TRANSACTION_ID
1302 )
1303 values
1304 (ben_person_action_items_s.nextval
1305 ,l_action_items.actn_typ_id
1306 ,'ICD'
1307 ,p_person_id
1308 ,p_assignment_id
1309 ,p_effective_date
1310 ,p_pl_id
1311 ,'OPEN'
1312 ,l_returned_date
1313 ,1
1314 ,null
1315 ,null
1316 ,p_icd_transaction_id
1317 );
1318
1319 if('N' = p_mandatory_action_item) then
1320 p_mandatory_action_item:= l_action_items.mandatory;
1321 end if ;
1322 end loop;
1323
1324 end create_person_action_items;
1325
1326
1327 --This procedure will delete the row from ben_icd_transaction table
1328 procedure delete_transaction_row(p_icd_transaction_id number)
1329 is
1330 begin
1331 delete from ben_icd_transaction where icd_transaction_id = p_icd_transaction_id ;
1332 end delete_transaction_row;
1333
1334 procedure suspend_enrollment(p_icd_transaction_id number)
1335 is
1336 begin
1337 update ben_icd_transaction
1338 set status = 'SP'
1339 where icd_transaction_id = p_icd_transaction_id;
1340 end suspend_enrollment;
1341
1342
1343
1344 -----------------------------Process API----------------------
1345 Procedure PROCESS_API(
1346 p_validate in boolean default false,
1347 p_transaction_step_id in number,
1348 p_effective_date in varchar2 default null) is
1349 --
1350 cursor c_icd_transaction is
1351 select *
1352 from ben_icd_transaction
1353 where transaction_id = (select transaction_id from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id)
1354 order by dml_operation;
1355
1356 l_proc varchar2(61) := 'process_api' ;
1357 l_validate number;
1358 l_effective_date date;
1359 l_mandatory_action_item varchar2(1);
1360 --
1361 Begin
1362 hr_utility.set_location('Entering '||l_proc,10);
1363 if(p_validate) then
1364 l_validate := 1;
1365 else
1366 l_validate := 0;
1367 end if;
1368
1369 l_effective_date := to_date(nvl(p_effective_date,to_char(sysdate,'YYYY/MM/DD')), 'YYYY/MM/DD');
1370
1371 for l_transaction_row in c_icd_transaction loop
1372
1373 -- create the action items for this award. and if it is mandatory then suspend it.
1374 -- For action items the effective Date will remain same as passed data.
1375 -- for element entries effective date will change based on rate codes and other factors.
1376 --create action items for create and update but not for delete
1377 if(l_transaction_row.dml_operation <> 'DELETE') then
1378 create_person_action_items
1379 (p_person_id => l_transaction_row.person_id
1380 ,p_assignment_id => l_transaction_row.assignment_id
1381 ,p_pl_id => l_transaction_row.pl_id
1382 ,p_icd_transaction_id =>l_transaction_row.icd_transaction_id
1383 ,p_effective_date => l_effective_date
1384 ,p_mandatory_action_item =>l_mandatory_action_item
1385 );
1386
1387 if ('Y'= l_mandatory_action_item) then
1388 suspend_enrollment(l_transaction_row.icd_transaction_id);
1389 else
1390 process_transaction_row(l_transaction_row,l_validate,l_effective_date);
1391 end if;
1392 else
1393 process_transaction_row(l_transaction_row,l_validate,l_effective_date);
1394 end if;
1395 end loop;
1396
1397 end process_api;
1398
1399
1400
1401 procedure unsuspend_enrollment(p_icd_transaction_id in number, p_effective_Date in date)
1402 is
1403 cursor c_transaction_row is select * from ben_icd_transaction where
1404 icd_transaction_id = p_icd_transaction_id
1405 order by dml_operation,element_entry_id,effective_start_date desc;
1406 l_validate number := 0;
1407 begin
1408 -- call relevant apis here.
1409 for l_transaction_row in c_transaction_row loop
1410 process_transaction_row(l_transaction_row,l_validate,p_effective_date);
1411 delete_transaction_row(l_transaction_row.icd_transaction_id );
1412 end loop ;
1413 end unsuspend_enrollment;
1414
1415 END BEN_ICD_ELEMENT_ENTRY_PKG;