[Home] [Help]
PACKAGE BODY: APPS.HR_PROCESS_CEI_SS
Source
1 package body hr_process_cei_ss as
2 /* $Header: hrceiwrs.pkb 120.0 2005/05/30 23:10 appldev noship $ */
3
4 function get_row_status
5 (
6 p_contact_extra_info_id in number,
7 p_effective_date in date
8 ) return varchar2 is
9 --
10 l_row_status varchar2(20);
11 l_dummy varchar2(1);
12 l_effective_end_date date;
13 --
14 begin
15 --
16 l_dummy := 'N';
17 --
18 begin
19 --
20 select
21 effective_end_date
22 into
23 l_effective_end_date
24 from
25 per_contact_extra_info_f
26 where
27 contact_extra_info_id = p_contact_extra_info_id
28 and
29 p_effective_date
30 between
31 effective_start_date
32 and
33 effective_end_date
34 and
35 effective_end_date <> to_date('31/12/4712', 'DD/MM/YYYY');
36 --
37 begin
38 --
39 select
40 'Y'
41 into
42 l_dummy
43 from
44 per_contact_extra_info_f
45 where
46 contact_extra_info_id = p_contact_extra_info_id
47 and
48 l_effective_end_date + 1
49 between
50 effective_start_date
51 and
52 effective_end_date;
53 --
54 l_row_status := 'FUTURE_CHANGE_ROW';
55 --
56 exception
57 when no_data_found then
58 l_row_status := 'FUTURE_DELETE_ROW';
59 end;
60 --
61 exception
62 when no_data_found then
63 l_row_status := 'DB_ROW';
64 end;
65 --
66 return l_row_status;
67 --
68 end get_row_status;
69
70 procedure set_transaction_step
71 (
72 p_item_type in varchar2,
73 p_item_key in varchar2,
74 p_activity_id in number,
75 p_login_person_id in number,
76 p_action in varchar2, -- 'INSERT' or 'UPDATE' or 'DELETE'
77 p_effective_date in date default null,
78 p_date_track_option in varchar2 default null,
79 p_contact_extra_info_id in number default null,
80 p_contact_relationship_id in number default null,
81 p_information_type in varchar2 default null,
82 p_object_version_number in number default null,
83 p_information_category in varchar2 default null,
84 p_information1 in varchar2 default null,
85 p_information2 in varchar2 default null,
86 p_information3 in varchar2 default null,
87 p_information4 in varchar2 default null,
88 p_information5 in varchar2 default null,
89 p_information6 in varchar2 default null,
90 p_information7 in varchar2 default null,
91 p_information8 in varchar2 default null,
92 p_information9 in varchar2 default null,
93 p_information10 in varchar2 default null,
94 p_information11 in varchar2 default null,
95 p_information12 in varchar2 default null,
96 p_information13 in varchar2 default null,
97 p_information14 in varchar2 default null,
98 p_information15 in varchar2 default null,
99 p_information16 in varchar2 default null,
100 p_information17 in varchar2 default null,
101 p_information18 in varchar2 default null,
102 p_information19 in varchar2 default null,
103 p_information20 in varchar2 default null,
104 p_information21 in varchar2 default null,
105 p_information22 in varchar2 default null,
106 p_information23 in varchar2 default null,
107 p_information24 in varchar2 default null,
108 p_information25 in varchar2 default null,
109 p_information26 in varchar2 default null,
110 p_information27 in varchar2 default null,
111 p_information28 in varchar2 default null,
112 p_information29 in varchar2 default null,
113 p_information30 in varchar2 default null,
114 p_attribute_category in varchar2 default null,
115 p_attribute1 in varchar2 default null,
116 p_attribute2 in varchar2 default null,
117 p_attribute3 in varchar2 default null,
118 p_attribute4 in varchar2 default null,
119 p_attribute5 in varchar2 default null,
120 p_attribute6 in varchar2 default null,
121 p_attribute7 in varchar2 default null,
122 p_attribute8 in varchar2 default null,
123 p_attribute9 in varchar2 default null,
124 p_attribute10 in varchar2 default null,
125 p_attribute11 in varchar2 default null,
126 p_attribute12 in varchar2 default null,
127 p_attribute13 in varchar2 default null,
128 p_attribute14 in varchar2 default null,
129 p_attribute15 in varchar2 default null,
130 p_attribute16 in varchar2 default null,
131 p_attribute17 in varchar2 default null,
132 p_attribute18 in varchar2 default null,
133 p_attribute19 in varchar2 default null,
134 p_attribute20 in varchar2 default null
135 ) is
136
137 l_date_format varchar2(10);
138 l_api_name varchar2(100);
139
140 l_transaction_table hr_transaction_ss.transaction_table;
141 l_count number := 0;
142
143 l_transaction_step_id number;
144 l_review_item_name varchar2(50);
145
146 begin
147
148 l_date_format := hr_transaction_ss.g_date_format;
149 l_api_name := 'HR_PROCESS_CEI_SS.PROCESS_API';
150
151 l_review_item_name :=
152 wf_engine.GetActivityAttrText
153 (
154 itemtype => p_item_type,
155 itemkey => p_item_key,
156 actid => p_activity_id,
157 aname => 'HR_REVIEW_REGION_ITEM'
158 );
159
160 l_count := l_count + 1;
161 l_transaction_table(l_count).param_name := 'P_REVIEW_PROC_CALL';
162 l_transaction_table(l_count).param_value := l_review_item_name;
163 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
164
165 l_count := l_count + 1;
166 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
167 l_transaction_table(l_count).param_value := p_activity_id;
168 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
169 /*
170 l_count := l_count + 1;
171 l_transaction_table(l_count).param_name := 'P_VALIDATE';
172 l_transaction_table(l_count).param_value := 0;
173 l_transaction_table(l_count).param_data_type := 'NUMBER';
174 */
175 l_count := l_count + 1;
176 l_transaction_table(l_count).param_name := 'P_ACTION';
177 l_transaction_table(l_count).param_value := p_action;
178 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
179
180 l_count := l_count + 1;
181 l_transaction_table(l_count).param_name := 'P_EFFECTIVE_DATE';
182 l_transaction_table(l_count).param_value := to_char(p_effective_date, l_date_format);
183 l_transaction_table(l_count).param_data_type := 'DATE';
184
185 l_count := l_count + 1;
186 l_transaction_table(l_count).param_name := 'P_DATE_TRACK_OPTION';
187 l_transaction_table(l_count).param_value := p_date_track_option;
188 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
189
190 l_count := l_count + 1;
191 l_transaction_table(l_count).param_name := 'P_EXTRA_INFO_ID';
192 l_transaction_table(l_count).param_value := p_contact_extra_info_id;
193 l_transaction_table(l_count).param_data_type := 'NUMBER';
194
195 l_count := l_count + 1;
196 l_transaction_table(l_count).param_name := 'P_CONTACT_RELATIONSHIP_ID';
197 l_transaction_table(l_count).param_value := p_contact_relationship_id;
198 l_transaction_table(l_count).param_data_type := 'NUMBER';
199
200 l_count := l_count + 1;
201 l_transaction_table(l_count).param_name := 'P_INFORMATION_TYPE';
202 l_transaction_table(l_count).param_value := p_information_type;
203 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
204
205 l_count := l_count + 1;
206 l_transaction_table(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
207 l_transaction_table(l_count).param_value := p_object_version_number;
208 l_transaction_table(l_count).param_data_type := 'NUMBER';
209
210 l_count := l_count + 1;
211 l_transaction_table(l_count).param_name := 'P_INFORMATION_CATEGORY';
212 l_transaction_table(l_count).param_value := p_information_category;
213 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
214
215 l_count := l_count + 1;
216 l_transaction_table(l_count).param_name := 'P_INFORMATION1';
217 l_transaction_table(l_count).param_value := p_information1;
218 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
219
220 l_count := l_count + 1;
221 l_transaction_table(l_count).param_name := 'P_INFORMATION2';
222 l_transaction_table(l_count).param_value := p_information2;
223 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
224
225 l_count := l_count + 1;
226 l_transaction_table(l_count).param_name := 'P_INFORMATION3';
227 l_transaction_table(l_count).param_value := p_information3;
228 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
229
230 l_count := l_count + 1;
231 l_transaction_table(l_count).param_name := 'P_INFORMATION4';
232 l_transaction_table(l_count).param_value := p_information4;
233 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
234
235 l_count := l_count + 1;
236 l_transaction_table(l_count).param_name := 'P_INFORMATION5';
237 l_transaction_table(l_count).param_value := p_information5;
238 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
239
240 l_count := l_count + 1;
241 l_transaction_table(l_count).param_name := 'P_INFORMATION6';
242 l_transaction_table(l_count).param_value := p_information6;
243 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
244
245 l_count := l_count + 1;
246 l_transaction_table(l_count).param_name := 'P_INFORMATION7';
247 l_transaction_table(l_count).param_value := p_information7;
248 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
249
250 l_count := l_count + 1;
251 l_transaction_table(l_count).param_name := 'P_INFORMATION8';
252 l_transaction_table(l_count).param_value := p_information8;
253 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
254
255 l_count := l_count + 1;
256 l_transaction_table(l_count).param_name := 'P_INFORMATION9';
257 l_transaction_table(l_count).param_value := p_information9;
258 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
259
260 l_count := l_count + 1;
261 l_transaction_table(l_count).param_name := 'P_INFORMATION10';
262 l_transaction_table(l_count).param_value := p_information10;
263 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
264
265 l_count := l_count + 1;
266 l_transaction_table(l_count).param_name := 'P_INFORMATION11';
267 l_transaction_table(l_count).param_value := p_information11;
268 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
269
270 l_count := l_count + 1;
271 l_transaction_table(l_count).param_name := 'P_INFORMATION12';
272 l_transaction_table(l_count).param_value := p_information12;
273 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
274
275 l_count := l_count + 1;
276 l_transaction_table(l_count).param_name := 'P_INFORMATION13';
277 l_transaction_table(l_count).param_value := p_information13;
278 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
279
280 l_count := l_count + 1;
281 l_transaction_table(l_count).param_name := 'P_INFORMATION14';
282 l_transaction_table(l_count).param_value := p_information14;
283 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
284
285 l_count := l_count + 1;
286 l_transaction_table(l_count).param_name := 'P_INFORMATION15';
287 l_transaction_table(l_count).param_value := p_information15;
288 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
289
290 l_count := l_count + 1;
291 l_transaction_table(l_count).param_name := 'P_INFORMATION16';
292 l_transaction_table(l_count).param_value := p_information16;
293 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
294
295 l_count := l_count + 1;
296 l_transaction_table(l_count).param_name := 'P_INFORMATION17';
297 l_transaction_table(l_count).param_value := p_information17;
298 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
299
300 l_count := l_count + 1;
301 l_transaction_table(l_count).param_name := 'P_INFORMATION18';
302 l_transaction_table(l_count).param_value := p_information18;
303 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
304
305 l_count := l_count + 1;
306 l_transaction_table(l_count).param_name := 'P_INFORMATION19';
307 l_transaction_table(l_count).param_value := p_information19;
308 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
309
310 l_count := l_count + 1;
311 l_transaction_table(l_count).param_name := 'P_INFORMATION20';
312 l_transaction_table(l_count).param_value := p_information20;
313 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
314
315 l_count := l_count + 1;
316 l_transaction_table(l_count).param_name := 'P_INFORMATION21';
317 l_transaction_table(l_count).param_value := p_information21;
318 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
319
320 l_count := l_count + 1;
321 l_transaction_table(l_count).param_name := 'P_INFORMATION22';
322 l_transaction_table(l_count).param_value := p_information22;
323 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
324
325 l_count := l_count + 1;
326 l_transaction_table(l_count).param_name := 'P_INFORMATION23';
327 l_transaction_table(l_count).param_value := p_information23;
328 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
329
330 l_count := l_count + 1;
331 l_transaction_table(l_count).param_name := 'P_INFORMATION24';
332 l_transaction_table(l_count).param_value := p_information24;
333 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
334
335 l_count := l_count + 1;
336 l_transaction_table(l_count).param_name := 'P_INFORMATION25';
337 l_transaction_table(l_count).param_value := p_information25;
338 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
339
340 l_count := l_count + 1;
341 l_transaction_table(l_count).param_name := 'P_INFORMATION26';
342 l_transaction_table(l_count).param_value := p_information26;
343 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
344
345 l_count := l_count + 1;
346 l_transaction_table(l_count).param_name := 'P_INFORMATION27';
347 l_transaction_table(l_count).param_value := p_information27;
348 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
349
350 l_count := l_count + 1;
351 l_transaction_table(l_count).param_name := 'P_INFORMATION28';
352 l_transaction_table(l_count).param_value := p_information28;
353 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
354
355 l_count := l_count + 1;
356 l_transaction_table(l_count).param_name := 'P_INFORMATION29';
357 l_transaction_table(l_count).param_value := p_information29;
358 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
359
360 l_count := l_count + 1;
361 l_transaction_table(l_count).param_name := 'P_INFORMATION30';
362 l_transaction_table(l_count).param_value := p_information30;
363 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
364
365 l_count := l_count + 1;
366 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE_CATEGORY';
367 l_transaction_table(l_count).param_value := p_attribute_category;
368 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
369
370 l_count := l_count + 1;
371 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE1';
372 l_transaction_table(l_count).param_value := p_attribute1;
373 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
374
375 l_count := l_count + 1;
376 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE2';
377 l_transaction_table(l_count).param_value := p_attribute2;
378 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
379
380 l_count := l_count + 1;
381 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE3';
382 l_transaction_table(l_count).param_value := p_attribute3;
383 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
384
385 l_count := l_count + 1;
386 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE4';
387 l_transaction_table(l_count).param_value := p_attribute4;
388 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
389
390 l_count := l_count + 1;
391 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE5';
392 l_transaction_table(l_count).param_value := p_attribute5;
393 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
394
395 l_count := l_count + 1;
396 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE6';
397 l_transaction_table(l_count).param_value := p_attribute6;
398 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
399
400 l_count := l_count + 1;
401 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE7';
402 l_transaction_table(l_count).param_value := p_attribute7;
403 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
404
405 l_count := l_count + 1;
406 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE8';
407 l_transaction_table(l_count).param_value := p_attribute8;
408 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
409
410 l_count := l_count + 1;
411 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE9';
412 l_transaction_table(l_count).param_value := p_attribute9;
413 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
414
415 l_count := l_count + 1;
416 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE10';
417 l_transaction_table(l_count).param_value := p_attribute10;
418 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
419
420 l_count := l_count + 1;
421 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE11';
422 l_transaction_table(l_count).param_value := p_attribute11;
423 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
424
425 l_count := l_count + 1;
426 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE12';
427 l_transaction_table(l_count).param_value := p_attribute12;
428 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
429
430 l_count := l_count + 1;
431 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE13';
432 l_transaction_table(l_count).param_value := p_attribute13;
433 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
434
435 l_count := l_count + 1;
436 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE14';
437 l_transaction_table(l_count).param_value := p_attribute14;
438 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
439
440 l_count := l_count + 1;
441 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE15';
442 l_transaction_table(l_count).param_value := p_attribute15;
443 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
444
445 l_count := l_count + 1;
446 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE16';
447 l_transaction_table(l_count).param_value := p_attribute16;
448 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
449
450 l_count := l_count + 1;
451 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE17';
452 l_transaction_table(l_count).param_value := p_attribute17;
453 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
454
455 l_count := l_count + 1;
456 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE18';
457 l_transaction_table(l_count).param_value := p_attribute18;
458 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
459
460 l_count := l_count + 1;
461 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE19';
462 l_transaction_table(l_count).param_value := p_attribute19;
463 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
464
465 l_count := l_count + 1;
466 l_transaction_table(l_count).param_name := 'P_ATTRIBUTE20';
467 l_transaction_table(l_count).param_value := p_attribute20;
468 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
469
470 hr_transaction_ss.save_transaction_step
471 (
472 p_item_type => p_item_type,
473 p_item_key => p_item_key,
474 p_actid => p_activity_id,
475 p_login_person_id => p_login_person_id,
476 p_transaction_step_id => l_transaction_step_id,
477 p_api_name => l_api_name,
478 p_transaction_data => l_transaction_table
479 );
480
481 end set_transaction_step;
482
483 /*
484 -- ----------------------------------------------------------------------------
485 -- |-----------------------< del_transaction_data >---------------------------|
486 -- Wrapper Package for API hr_process_sit_ss.
487 --
488 -- Description:
489 -- This Function dels the transaction data for the given item type, item key
490 -- and activity id.
491 -- ----------------------------------------------------------------------------
492 procedure del_transaction_data
493 (
494 p_item_type in varchar2,
495 p_item_key in varchar2,
496 p_activity_id in varchar2,
497 p_login_person_id in varchar2,
498 ) is
499
500 begin
501
502 hr_transaction_ss.delete_transaction_steps
503 (
504 p_item_type => p_item_type,
505 p_item_key => p_item_key,
506 p_actid => p_activity_id,
507 p_login_person_id => p_login_person_id
508 );
509
510 end del_transaction_data;
511 */
512
513 procedure process_api
514 (
515 p_validate in boolean default false,
516 p_transaction_step_id in number default null,
517 p_effective_date in varchar2 default null
518 ) is
519
520 -- for return values from out parameters
521 l_contact_extra_info_id number;
522 l_object_version_number number;
523 l_effective_start_date date;
524 l_effective_end_date date;
525
526 l_action varchar2(30);
527 l_effective_date date;
528 l_date_track_option varchar2(30);
529
530 l_tran_tab hr_transaction_ss.transaction_data;
531 l_tran_rec per_contact_extra_info_f%rowtype;
532
533 i number;
534
535 begin
536
537 -- get taransaction data
538 hr_transaction_ss.get_transaction_data
539 (
540 p_transaction_step_id => p_transaction_step_id,
541 p_transaction_data => l_tran_tab
542 );
543
544 i := l_tran_tab.name.first;
545
546 loop
547 exit when not(l_tran_tab.name.exists(i));
548 if l_tran_tab.name(i) in ('P_REVIEW_PROC_CALL', 'P_REVIEW_ACTID') then
549 null;
550 elsif l_tran_tab.name(i) = 'P_ACTION' then
551 l_action := l_tran_tab.varchar2_value(i);
552 elsif l_tran_tab.name(i) = 'P_EFFECTIVE_DATE' then
553 l_effective_date := l_tran_tab.date_value(i);
554 elsif l_tran_tab.name(i) = 'P_DATE_TRACK_OPTION' then
555 l_date_track_option := l_tran_tab.varchar2_value(i);
556 elsif l_tran_tab.name(i) = 'P_EXTRA_INFO_ID' then
557 l_tran_rec.contact_extra_info_id := l_tran_tab.number_value(i);
558 elsif l_tran_tab.name(i) = 'P_CONTACT_RELATIONSHIP_ID' then
559 l_tran_rec.contact_relationship_id := l_tran_tab.number_value(i);
560 elsif l_tran_tab.name(i) = 'P_INFORMATION_TYPE' then
561 l_tran_rec.information_type := l_tran_tab.varchar2_value(i);
562 elsif l_tran_tab.name(i) = 'P_OBJECT_VERSION_NUMBER' then
563 l_tran_rec.object_version_number := l_tran_tab.number_value(i);
564 elsif l_tran_tab.name(i) = 'P_INFORMATION_CATEGORY' then
565 l_tran_rec.cei_information_category := l_tran_tab.varchar2_value(i);
566 elsif l_tran_tab.name(i) = 'P_INFORMATION1' then
567 l_tran_rec.cei_information1 := l_tran_tab.varchar2_value(i);
568 elsif l_tran_tab.name(i) = 'P_INFORMATION2' then
569 l_tran_rec.cei_information2 := l_tran_tab.varchar2_value(i);
570 elsif l_tran_tab.name(i) = 'P_INFORMATION3' then
571 l_tran_rec.cei_information3 := l_tran_tab.varchar2_value(i);
572 elsif l_tran_tab.name(i) = 'P_INFORMATION4' then
573 l_tran_rec.cei_information4 := l_tran_tab.varchar2_value(i);
574 elsif l_tran_tab.name(i) = 'P_INFORMATION5' then
575 l_tran_rec.cei_information5 := l_tran_tab.varchar2_value(i);
576 elsif l_tran_tab.name(i) = 'P_INFORMATION6' then
577 l_tran_rec.cei_information6 := l_tran_tab.varchar2_value(i);
578 elsif l_tran_tab.name(i) = 'P_INFORMATION7' then
579 l_tran_rec.cei_information7 := l_tran_tab.varchar2_value(i);
580 elsif l_tran_tab.name(i) = 'P_INFORMATION8' then
581 l_tran_rec.cei_information8 := l_tran_tab.varchar2_value(i);
582 elsif l_tran_tab.name(i) = 'P_INFORMATION9' then
583 l_tran_rec.cei_information9 := l_tran_tab.varchar2_value(i);
584 elsif l_tran_tab.name(i) = 'P_INFORMATION10' then
585 l_tran_rec.cei_information10 := l_tran_tab.varchar2_value(i);
586 elsif l_tran_tab.name(i) = 'P_INFORMATION11' then
587 l_tran_rec.cei_information11 := l_tran_tab.varchar2_value(i);
588 elsif l_tran_tab.name(i) = 'P_INFORMATION12' then
589 l_tran_rec.cei_information12 := l_tran_tab.varchar2_value(i);
590 elsif l_tran_tab.name(i) = 'P_INFORMATION13' then
591 l_tran_rec.cei_information13 := l_tran_tab.varchar2_value(i);
592 elsif l_tran_tab.name(i) = 'P_INFORMATION14' then
593 l_tran_rec.cei_information14 := l_tran_tab.varchar2_value(i);
594 elsif l_tran_tab.name(i) = 'P_INFORMATION15' then
595 l_tran_rec.cei_information15 := l_tran_tab.varchar2_value(i);
596 elsif l_tran_tab.name(i) = 'P_INFORMATION16' then
597 l_tran_rec.cei_information16 := l_tran_tab.varchar2_value(i);
598 elsif l_tran_tab.name(i) = 'P_INFORMATION17' then
599 l_tran_rec.cei_information17 := l_tran_tab.varchar2_value(i);
600 elsif l_tran_tab.name(i) = 'P_INFORMATION18' then
601 l_tran_rec.cei_information18 := l_tran_tab.varchar2_value(i);
602 elsif l_tran_tab.name(i) = 'P_INFORMATION19' then
603 l_tran_rec.cei_information19 := l_tran_tab.varchar2_value(i);
604 elsif l_tran_tab.name(i) = 'P_INFORMATION20' then
605 l_tran_rec.cei_information20 := l_tran_tab.varchar2_value(i);
606 elsif l_tran_tab.name(i) = 'P_INFORMATION21' then
607 l_tran_rec.cei_information21 := l_tran_tab.varchar2_value(i);
608 elsif l_tran_tab.name(i) = 'P_INFORMATION22' then
609 l_tran_rec.cei_information22 := l_tran_tab.varchar2_value(i);
610 elsif l_tran_tab.name(i) = 'P_INFORMATION23' then
611 l_tran_rec.cei_information23 := l_tran_tab.varchar2_value(i);
612 elsif l_tran_tab.name(i) = 'P_INFORMATION24' then
613 l_tran_rec.cei_information24 := l_tran_tab.varchar2_value(i);
614 elsif l_tran_tab.name(i) = 'P_INFORMATION25' then
615 l_tran_rec.cei_information25 := l_tran_tab.varchar2_value(i);
616 elsif l_tran_tab.name(i) = 'P_INFORMATION26' then
617 l_tran_rec.cei_information26 := l_tran_tab.varchar2_value(i);
618 elsif l_tran_tab.name(i) = 'P_INFORMATION27' then
619 l_tran_rec.cei_information27 := l_tran_tab.varchar2_value(i);
620 elsif l_tran_tab.name(i) = 'P_INFORMATION28' then
621 l_tran_rec.cei_information28 := l_tran_tab.varchar2_value(i);
622 elsif l_tran_tab.name(i) = 'P_INFORMATION29' then
623 l_tran_rec.cei_information29 := l_tran_tab.varchar2_value(i);
624 elsif l_tran_tab.name(i) = 'P_INFORMATION30' then
625 l_tran_rec.cei_information30 := l_tran_tab.varchar2_value(i);
626 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE_CATEGORY' then
627 l_tran_rec.cei_attribute_category := l_tran_tab.varchar2_value(i);
628 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE1' then
629 l_tran_rec.cei_attribute1 := l_tran_tab.varchar2_value(i);
630 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE2' then
631 l_tran_rec.cei_attribute2 := l_tran_tab.varchar2_value(i);
632 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE3' then
633 l_tran_rec.cei_attribute3 := l_tran_tab.varchar2_value(i);
634 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE4' then
635 l_tran_rec.cei_attribute4 := l_tran_tab.varchar2_value(i);
636 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE5' then
637 l_tran_rec.cei_attribute5 := l_tran_tab.varchar2_value(i);
638 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE6' then
639 l_tran_rec.cei_attribute6 := l_tran_tab.varchar2_value(i);
640 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE7' then
641 l_tran_rec.cei_attribute7 := l_tran_tab.varchar2_value(i);
642 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE8' then
643 l_tran_rec.cei_attribute8 := l_tran_tab.varchar2_value(i);
644 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE9' then
645 l_tran_rec.cei_attribute9 := l_tran_tab.varchar2_value(i);
646 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE10' then
647 l_tran_rec.cei_attribute10 := l_tran_tab.varchar2_value(i);
648 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE11' then
649 l_tran_rec.cei_attribute11 := l_tran_tab.varchar2_value(i);
650 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE12' then
651 l_tran_rec.cei_attribute12 := l_tran_tab.varchar2_value(i);
652 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE13' then
653 l_tran_rec.cei_attribute13 := l_tran_tab.varchar2_value(i);
654 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE14' then
655 l_tran_rec.cei_attribute14 := l_tran_tab.varchar2_value(i);
656 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE15' then
657 l_tran_rec.cei_attribute15 := l_tran_tab.varchar2_value(i);
658 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE16' then
659 l_tran_rec.cei_attribute16 := l_tran_tab.varchar2_value(i);
660 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE17' then
661 l_tran_rec.cei_attribute17 := l_tran_tab.varchar2_value(i);
662 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE18' then
663 l_tran_rec.cei_attribute18 := l_tran_tab.varchar2_value(i);
664 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE19' then
665 l_tran_rec.cei_attribute19 := l_tran_tab.varchar2_value(i);
666 elsif l_tran_tab.name(i) = 'P_ATTRIBUTE20' then
667 l_tran_rec.cei_attribute20 := l_tran_tab.varchar2_value(i);
668 end if;
669 i := i + 1;
670 end loop;
671
672 hr_util_misc_web.insert_session_row(l_effective_date);
673
674 if l_action = 'INSERT' then
675
676 hr_contact_extra_info_api.create_contact_extra_info
677 (
678 p_validate => p_validate,
679 p_effective_date => l_effective_date,
680 p_contact_relationship_id => l_tran_rec.contact_relationship_id,
681 p_information_type => l_tran_rec.information_type,
682 p_cei_information_category => l_tran_rec.cei_information_category,
683 p_cei_information1 => l_tran_rec.cei_information1,
684 p_cei_information2 => l_tran_rec.cei_information2,
685 p_cei_information3 => l_tran_rec.cei_information3,
686 p_cei_information4 => l_tran_rec.cei_information4,
687 p_cei_information5 => l_tran_rec.cei_information5,
688 p_cei_information6 => l_tran_rec.cei_information6,
689 p_cei_information7 => l_tran_rec.cei_information7,
690 p_cei_information8 => l_tran_rec.cei_information8,
691 p_cei_information9 => l_tran_rec.cei_information9,
692 p_cei_information10 => l_tran_rec.cei_information10,
693 p_cei_information11 => l_tran_rec.cei_information11,
694 p_cei_information12 => l_tran_rec.cei_information12,
695 p_cei_information13 => l_tran_rec.cei_information13,
696 p_cei_information14 => l_tran_rec.cei_information14,
697 p_cei_information15 => l_tran_rec.cei_information15,
698 p_cei_information16 => l_tran_rec.cei_information16,
699 p_cei_information17 => l_tran_rec.cei_information17,
700 p_cei_information18 => l_tran_rec.cei_information18,
701 p_cei_information19 => l_tran_rec.cei_information19,
702 p_cei_information20 => l_tran_rec.cei_information20,
703 p_cei_information21 => l_tran_rec.cei_information21,
704 p_cei_information22 => l_tran_rec.cei_information22,
705 p_cei_information23 => l_tran_rec.cei_information23,
706 p_cei_information24 => l_tran_rec.cei_information24,
707 p_cei_information25 => l_tran_rec.cei_information25,
708 p_cei_information26 => l_tran_rec.cei_information26,
709 p_cei_information27 => l_tran_rec.cei_information27,
710 p_cei_information28 => l_tran_rec.cei_information28,
711 p_cei_information29 => l_tran_rec.cei_information29,
712 p_cei_information30 => l_tran_rec.cei_information30,
713 p_cei_attribute_category => l_tran_rec.cei_attribute_category,
714 p_cei_attribute1 => l_tran_rec.cei_attribute1,
715 p_cei_attribute2 => l_tran_rec.cei_attribute2,
716 p_cei_attribute3 => l_tran_rec.cei_attribute3,
717 p_cei_attribute4 => l_tran_rec.cei_attribute4,
718 p_cei_attribute5 => l_tran_rec.cei_attribute5,
719 p_cei_attribute6 => l_tran_rec.cei_attribute6,
720 p_cei_attribute7 => l_tran_rec.cei_attribute7,
721 p_cei_attribute8 => l_tran_rec.cei_attribute8,
722 p_cei_attribute9 => l_tran_rec.cei_attribute9,
723 p_cei_attribute10 => l_tran_rec.cei_attribute10,
724 p_cei_attribute11 => l_tran_rec.cei_attribute11,
725 p_cei_attribute12 => l_tran_rec.cei_attribute12,
726 p_cei_attribute13 => l_tran_rec.cei_attribute13,
727 p_cei_attribute14 => l_tran_rec.cei_attribute14,
728 p_cei_attribute15 => l_tran_rec.cei_attribute15,
729 p_cei_attribute16 => l_tran_rec.cei_attribute16,
730 p_cei_attribute17 => l_tran_rec.cei_attribute17,
731 p_cei_attribute18 => l_tran_rec.cei_attribute18,
732 p_cei_attribute19 => l_tran_rec.cei_attribute19,
733 p_cei_attribute20 => l_tran_rec.cei_attribute20,
734 p_contact_extra_info_id => l_contact_extra_info_id,
735 p_object_version_number => l_object_version_number,
736 p_effective_start_date => l_effective_start_date,
737 p_effective_end_date => l_effective_end_date
738 );
739
740 elsif l_action = 'UPDATE' then
741
742 hr_contact_extra_info_api.update_contact_extra_info
743 (
744 p_validate => p_validate,
745 p_effective_date => l_effective_date,
746 p_datetrack_update_mode => l_date_track_option,
747 p_contact_extra_info_id => l_tran_rec.contact_extra_info_id,
748 p_contact_relationship_id => l_tran_rec.contact_relationship_id,
749 p_information_type => l_tran_rec.information_type,
750 p_object_version_number => l_tran_rec.object_version_number,
751 p_cei_information_category => l_tran_rec.cei_information_category,
752 p_cei_information1 => l_tran_rec.cei_information1,
753 p_cei_information2 => l_tran_rec.cei_information2,
754 p_cei_information3 => l_tran_rec.cei_information3,
755 p_cei_information4 => l_tran_rec.cei_information4,
756 p_cei_information5 => l_tran_rec.cei_information5,
757 p_cei_information6 => l_tran_rec.cei_information6,
758 p_cei_information7 => l_tran_rec.cei_information7,
759 p_cei_information8 => l_tran_rec.cei_information8,
760 p_cei_information9 => l_tran_rec.cei_information9,
761 p_cei_information10 => l_tran_rec.cei_information10,
762 p_cei_information11 => l_tran_rec.cei_information11,
763 p_cei_information12 => l_tran_rec.cei_information12,
764 p_cei_information13 => l_tran_rec.cei_information13,
765 p_cei_information14 => l_tran_rec.cei_information14,
766 p_cei_information15 => l_tran_rec.cei_information15,
767 p_cei_information16 => l_tran_rec.cei_information16,
768 p_cei_information17 => l_tran_rec.cei_information17,
769 p_cei_information18 => l_tran_rec.cei_information18,
770 p_cei_information19 => l_tran_rec.cei_information19,
771 p_cei_information20 => l_tran_rec.cei_information20,
772 p_cei_information21 => l_tran_rec.cei_information21,
773 p_cei_information22 => l_tran_rec.cei_information22,
774 p_cei_information23 => l_tran_rec.cei_information23,
775 p_cei_information24 => l_tran_rec.cei_information24,
776 p_cei_information25 => l_tran_rec.cei_information25,
777 p_cei_information26 => l_tran_rec.cei_information26,
778 p_cei_information27 => l_tran_rec.cei_information27,
779 p_cei_information28 => l_tran_rec.cei_information28,
780 p_cei_information29 => l_tran_rec.cei_information29,
781 p_cei_information30 => l_tran_rec.cei_information30,
782 p_cei_attribute_category => l_tran_rec.cei_attribute_category,
783 p_cei_attribute1 => l_tran_rec.cei_attribute1,
784 p_cei_attribute2 => l_tran_rec.cei_attribute2,
785 p_cei_attribute3 => l_tran_rec.cei_attribute3,
786 p_cei_attribute4 => l_tran_rec.cei_attribute4,
787 p_cei_attribute5 => l_tran_rec.cei_attribute5,
788 p_cei_attribute6 => l_tran_rec.cei_attribute6,
789 p_cei_attribute7 => l_tran_rec.cei_attribute7,
790 p_cei_attribute8 => l_tran_rec.cei_attribute8,
791 p_cei_attribute9 => l_tran_rec.cei_attribute9,
792 p_cei_attribute10 => l_tran_rec.cei_attribute10,
793 p_cei_attribute11 => l_tran_rec.cei_attribute11,
794 p_cei_attribute12 => l_tran_rec.cei_attribute12,
795 p_cei_attribute13 => l_tran_rec.cei_attribute13,
796 p_cei_attribute14 => l_tran_rec.cei_attribute14,
797 p_cei_attribute15 => l_tran_rec.cei_attribute15,
798 p_cei_attribute16 => l_tran_rec.cei_attribute16,
799 p_cei_attribute17 => l_tran_rec.cei_attribute17,
800 p_cei_attribute18 => l_tran_rec.cei_attribute18,
801 p_cei_attribute19 => l_tran_rec.cei_attribute19,
802 p_cei_attribute20 => l_tran_rec.cei_attribute20,
803 p_effective_start_date => l_effective_start_date,
804 p_effective_end_date => l_effective_end_date
805 );
806
807 elsif l_action = 'DELETE' then
808
809 hr_contact_extra_info_api.delete_contact_extra_info
810 (
811 p_validate => p_validate,
812 p_effective_date => l_effective_date,
813 p_datetrack_delete_mode => l_date_track_option,
814 p_contact_extra_info_id => l_tran_rec.contact_extra_info_id,
815 p_object_version_number => l_tran_rec.object_version_number,
816 p_effective_start_date => l_effective_start_date,
817 p_effective_end_date => l_effective_end_date
818 );
819
820 end if;
821
822 end process_api;
823
824 end hr_process_cei_ss;