[Home] [Help]
PACKAGE BODY: APPS.HRDPP_CREATE_APPLICANT
Source
1 package body hrdpp_CREATE_APPLICANT as
2 /*
3 * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:47
4 * Generated for API: HR_APPLICANT_API.CREATE_APPLICANT
5 */
6 --
7 dh constant date := hr_api.g_date;
8 nh constant number := hr_api.g_number;
9 vh constant varchar2(64) := hr_api.g_varchar2;
10 c_sot constant date := to_date('01010001','DDMMYYYY');
11 cn constant varchar2(32) := '<NULL>';
12 dn constant date := null;
13 nn constant number := null;
14 vn constant varchar2(1) := null;
15 --
16 function dc(p in date) return varchar2 is
17 begin
18 if p<c_sot then
19 if p<>trunc(p) then
20 return to_char(p,'SYYYY/MM/DD HH24:MI:SS');
21 end if;
22 return to_char(p,'SYYYY/MM/DD');
23 elsif p<>trunc(p) then
24 return to_char(p,'YYYY/MM/DD HH24:MI:SS');
25 end if;
26 return to_char(p,'YYYY/MM/DD');
27 end dc;
28 function d(p in varchar2) return date is
29 begin
30 if length(p)=10 then
31 return to_date(p,'YYYY/MM/DD');
32 elsif length(p)=19 then
33 return to_date(p,'YYYY/MM/DD HH24:MI:SS');
34 elsif length(p)=11 then
35 return to_date(p,'SYYYY/MM/DD');
36 elsif length(p)=20 then
37 return to_date(p,'SYYYY/MM/DD HH24:MI:SS');
38 end if;
39 -- Try default format as last resort.
40 return to_date(p,'YYYY/MM/DD');
41 end d;
42 function n(p in varchar2) return number is
43 begin
44 return to_number(p);
45 end n;
46 function dd(p in date,i in varchar2)
47 return varchar2 is
48 begin
49 if upper(i) = 'N' then return dc(p);
50 else return cn; end if;
51 end dd;
52 function nd(p in number,i in varchar2)
53 return varchar2 is
54 begin
55 if upper(i) = 'N' then return to_char(p);
56 else return cn; end if;
57 end nd;
58 --
59 procedure iuk
60 (p_batch_line_id in number,
61 p_user_key_value in varchar2,
62 p_unique_key_id in number)
63 is
64 begin
65 hr_data_pump.entry('ins_user_key');
66 insert into hr_pump_batch_line_user_keys
67 (user_key_id, batch_line_id,user_key_value,unique_key_id)
68 values
69 (hr_pump_batch_line_user_keys_s.nextval,
70 p_batch_line_id,
71 p_user_key_value,
72 p_unique_key_id);
73 hr_data_pump.exit('ins_user_key');
74 end iuk;
75 --
76 procedure insert_batch_lines
77 (p_batch_id in number
78 ,p_data_pump_batch_line_id in number default null
79 ,p_data_pump_business_grp_name in varchar2 default null
80 ,p_user_sequence in number default null
81 ,p_link_value in number default null
82 ,P_DATE_RECEIVED in date
83 ,P_LAST_NAME in varchar2
84 ,P_APPLICANT_NUMBER in varchar2
85 ,P_PER_COMMENTS in varchar2 default null
86 ,P_DATE_EMPLOYEE_DATA_VERIFIED in date default null
87 ,P_DATE_OF_BIRTH in date default null
88 ,P_EMAIL_ADDRESS in varchar2 default null
89 ,P_EXPENSE_CHECK_SEND_TO_ADDRES in varchar2 default null
90 ,P_FIRST_NAME in varchar2 default null
91 ,P_KNOWN_AS in varchar2 default null
92 ,P_MARITAL_STATUS in varchar2 default null
93 ,P_MIDDLE_NAMES in varchar2 default null
94 ,P_NATIONALITY in varchar2 default null
95 ,P_NATIONAL_IDENTIFIER in varchar2 default null
96 ,P_PREVIOUS_LAST_NAME in varchar2 default null
97 ,P_REGISTERED_DISABLED_FLAG in varchar2 default null
98 ,P_SEX in varchar2 default null
99 ,P_TITLE in varchar2 default null
100 ,P_WORK_TELEPHONE in varchar2 default null
101 ,P_ATTRIBUTE_CATEGORY in varchar2 default null
102 ,P_ATTRIBUTE1 in varchar2 default null
103 ,P_ATTRIBUTE2 in varchar2 default null
104 ,P_ATTRIBUTE3 in varchar2 default null
105 ,P_ATTRIBUTE4 in varchar2 default null
106 ,P_ATTRIBUTE5 in varchar2 default null
107 ,P_ATTRIBUTE6 in varchar2 default null
108 ,P_ATTRIBUTE7 in varchar2 default null
109 ,P_ATTRIBUTE8 in varchar2 default null
110 ,P_ATTRIBUTE9 in varchar2 default null
111 ,P_ATTRIBUTE10 in varchar2 default null
112 ,P_ATTRIBUTE11 in varchar2 default null
113 ,P_ATTRIBUTE12 in varchar2 default null
114 ,P_ATTRIBUTE13 in varchar2 default null
115 ,P_ATTRIBUTE14 in varchar2 default null
116 ,P_ATTRIBUTE15 in varchar2 default null
117 ,P_ATTRIBUTE16 in varchar2 default null
118 ,P_ATTRIBUTE17 in varchar2 default null
119 ,P_ATTRIBUTE18 in varchar2 default null
120 ,P_ATTRIBUTE19 in varchar2 default null
121 ,P_ATTRIBUTE20 in varchar2 default null
122 ,P_ATTRIBUTE21 in varchar2 default null
123 ,P_ATTRIBUTE22 in varchar2 default null
124 ,P_ATTRIBUTE23 in varchar2 default null
125 ,P_ATTRIBUTE24 in varchar2 default null
126 ,P_ATTRIBUTE25 in varchar2 default null
127 ,P_ATTRIBUTE26 in varchar2 default null
128 ,P_ATTRIBUTE27 in varchar2 default null
129 ,P_ATTRIBUTE28 in varchar2 default null
130 ,P_ATTRIBUTE29 in varchar2 default null
131 ,P_ATTRIBUTE30 in varchar2 default null
132 ,P_PER_INFORMATION_CATEGORY in varchar2 default null
133 ,P_PER_INFORMATION1 in varchar2 default null
134 ,P_PER_INFORMATION2 in varchar2 default null
135 ,P_PER_INFORMATION3 in varchar2 default null
136 ,P_PER_INFORMATION4 in varchar2 default null
137 ,P_PER_INFORMATION5 in varchar2 default null
138 ,P_PER_INFORMATION6 in varchar2 default null
139 ,P_PER_INFORMATION7 in varchar2 default null
140 ,P_PER_INFORMATION8 in varchar2 default null
141 ,P_PER_INFORMATION9 in varchar2 default null
142 ,P_PER_INFORMATION10 in varchar2 default null
143 ,P_PER_INFORMATION11 in varchar2 default null
144 ,P_PER_INFORMATION12 in varchar2 default null
145 ,P_PER_INFORMATION13 in varchar2 default null
146 ,P_PER_INFORMATION14 in varchar2 default null
147 ,P_PER_INFORMATION15 in varchar2 default null
148 ,P_PER_INFORMATION16 in varchar2 default null
149 ,P_PER_INFORMATION17 in varchar2 default null
150 ,P_PER_INFORMATION18 in varchar2 default null
151 ,P_PER_INFORMATION19 in varchar2 default null
152 ,P_PER_INFORMATION20 in varchar2 default null
153 ,P_PER_INFORMATION21 in varchar2 default null
154 ,P_PER_INFORMATION22 in varchar2 default null
155 ,P_PER_INFORMATION23 in varchar2 default null
156 ,P_PER_INFORMATION24 in varchar2 default null
157 ,P_PER_INFORMATION25 in varchar2 default null
158 ,P_PER_INFORMATION26 in varchar2 default null
159 ,P_PER_INFORMATION27 in varchar2 default null
160 ,P_PER_INFORMATION28 in varchar2 default null
161 ,P_PER_INFORMATION29 in varchar2 default null
162 ,P_PER_INFORMATION30 in varchar2 default null
163 ,P_BACKGROUND_CHECK_STATUS in varchar2 default null
164 ,P_BACKGROUND_DATE_CHECK in date default null
165 ,P_FTE_CAPACITY in number default null
166 ,P_HOLD_APPLICANT_DATE_UNTIL in date default null
167 ,P_HONORS in varchar2 default null
168 ,P_MAILSTOP in varchar2 default null
169 ,P_OFFICE_NUMBER in varchar2 default null
170 ,P_ON_MILITARY_SERVICE in varchar2 default null
171 ,P_PRE_NAME_ADJUNCT in varchar2 default null
172 ,P_PROJECTED_START_DATE in date default null
173 ,P_RESUME_EXISTS in varchar2 default null
174 ,P_RESUME_LAST_UPDATED in date default null
175 ,P_STUDENT_STATUS in varchar2 default null
176 ,P_WORK_SCHEDULE in varchar2 default null
177 ,P_SUFFIX in varchar2 default null
178 ,P_DATE_OF_DEATH in date default null
179 ,P_RECEIPT_OF_DEATH_CERT_DATE in date default null
180 ,P_COORD_BEN_MED_PLN_NO in varchar2 default null
181 ,P_COORD_BEN_NO_CVG_FLAG in varchar2 default null
182 ,P_USES_TOBACCO_FLAG in varchar2 default null
183 ,P_DPDNT_ADOPTION_DATE in date default null
184 ,P_DPDNT_VLNTRY_SVCE_FLAG in varchar2 default null
185 ,P_ORIGINAL_DATE_OF_HIRE in date default null
186 ,P_TOWN_OF_BIRTH in varchar2 default null
187 ,P_REGION_OF_BIRTH in varchar2 default null
188 ,P_COUNTRY_OF_BIRTH in varchar2 default null
189 ,P_GLOBAL_PERSON_ID in varchar2 default null
190 ,P_PARTY_ID in number default null
191 ,P_PERSON_USER_KEY in varchar2
192 ,P_ASSIGNMENT_USER_KEY in varchar2
193 ,P_APPLICATION_USER_KEY in varchar2
194 ,P_USER_PERSON_TYPE in varchar2 default null
195 ,P_LANGUAGE_CODE in varchar2 default null
196 ,P_CORRESPONDENCE_LANGUAGE in varchar2 default null
197 ,P_BENEFIT_GROUP in varchar2 default null
198 ,P_VACANCY_USER_KEY in varchar2 default null) is
199 blid number := p_data_pump_batch_line_id;
200 begin
201 if blid is not null then
202 delete from hr_pump_batch_lines where batch_line_id = blid;
203 delete from hr_pump_batch_exceptions
204 where source_type = 'BATCH_LINE' and source_id = blid;
205 end if;
206 insert into hr_pump_batch_lines
207 (batch_id
208 ,batch_line_id
209 ,business_group_name
210 ,api_module_id
211 ,line_status
212 ,user_sequence
213 ,link_value
214 ,pval001
215 ,pval002
216 ,pval003
217 ,pval004
218 ,pval005
219 ,pval006
220 ,pval007
221 ,pval008
222 ,pval009
223 ,pval010
224 ,pval011
225 ,pval012
226 ,pval013
227 ,pval014
228 ,pval015
229 ,pval016
230 ,pval017
231 ,pval018
232 ,pval019
233 ,pval020
234 ,pval021
235 ,pval022
236 ,pval023
237 ,pval024
238 ,pval025
239 ,pval026
240 ,pval027
241 ,pval028
242 ,pval029
243 ,pval030
244 ,pval031
245 ,pval032
246 ,pval033
247 ,pval034
248 ,pval035
249 ,pval036
250 ,pval037
251 ,pval038
252 ,pval039
253 ,pval040
254 ,pval041
255 ,pval042
256 ,pval043
257 ,pval044
258 ,pval045
259 ,pval046
260 ,pval047
261 ,pval048
262 ,pval049
263 ,pval050
264 ,pval051
265 ,pval052
266 ,pval053
267 ,pval054
268 ,pval055
269 ,pval056
270 ,pval057
271 ,pval058
272 ,pval059
273 ,pval060
274 ,pval061
275 ,pval062
276 ,pval063
277 ,pval064
278 ,pval065
279 ,pval066
280 ,pval067
281 ,pval068
282 ,pval069
283 ,pval070
284 ,pval071
285 ,pval072
286 ,pval073
287 ,pval074
288 ,pval075
289 ,pval076
290 ,pval077
291 ,pval078
292 ,pval079
293 ,pval080
294 ,pval081
295 ,pval082
296 ,pval083
297 ,pval084
298 ,pval085
299 ,pval086
300 ,pval087
301 ,pval088
302 ,pval089
303 ,pval090
304 ,pval091
305 ,pval092
306 ,pval093
307 ,pval094
308 ,pval095
309 ,pval096
310 ,pval097
311 ,pval098
312 ,pval099
313 ,pval100
314 ,pval101
315 ,pval102
316 ,pval103
317 ,pval104
318 ,pval105
319 ,pval106
320 ,pval107
321 ,pval108
322 ,pval109
323 ,pval110
324 ,pval111
325 ,pval112
326 ,pval123
327 ,pval124
328 ,pval125
329 ,pval126
330 ,pval127)
331 values
332 (p_batch_id
333 ,nvl(blid,hr_pump_batch_lines_s.nextval)
334 ,p_data_pump_business_grp_name
335 ,1339
336 ,'U'
337 ,p_user_sequence
338 ,p_link_value
339 ,dc(P_DATE_RECEIVED)
340 ,P_LAST_NAME
341 ,P_APPLICANT_NUMBER
342 ,P_PER_COMMENTS
343 ,dc(P_DATE_EMPLOYEE_DATA_VERIFIED)
344 ,dc(P_DATE_OF_BIRTH)
345 ,P_EMAIL_ADDRESS
346 ,P_EXPENSE_CHECK_SEND_TO_ADDRES
347 ,P_FIRST_NAME
348 ,P_KNOWN_AS
349 ,P_MARITAL_STATUS
350 ,P_MIDDLE_NAMES
351 ,P_NATIONALITY
352 ,P_NATIONAL_IDENTIFIER
353 ,P_PREVIOUS_LAST_NAME
354 ,P_REGISTERED_DISABLED_FLAG
355 ,P_SEX
356 ,P_TITLE
357 ,P_WORK_TELEPHONE
358 ,P_ATTRIBUTE_CATEGORY
359 ,P_ATTRIBUTE1
360 ,P_ATTRIBUTE2
361 ,P_ATTRIBUTE3
362 ,P_ATTRIBUTE4
363 ,P_ATTRIBUTE5
364 ,P_ATTRIBUTE6
365 ,P_ATTRIBUTE7
366 ,P_ATTRIBUTE8
367 ,P_ATTRIBUTE9
368 ,P_ATTRIBUTE10
369 ,P_ATTRIBUTE11
370 ,P_ATTRIBUTE12
371 ,P_ATTRIBUTE13
372 ,P_ATTRIBUTE14
373 ,P_ATTRIBUTE15
374 ,P_ATTRIBUTE16
375 ,P_ATTRIBUTE17
376 ,P_ATTRIBUTE18
377 ,P_ATTRIBUTE19
378 ,P_ATTRIBUTE20
379 ,P_ATTRIBUTE21
380 ,P_ATTRIBUTE22
381 ,P_ATTRIBUTE23
382 ,P_ATTRIBUTE24
383 ,P_ATTRIBUTE25
384 ,P_ATTRIBUTE26
385 ,P_ATTRIBUTE27
386 ,P_ATTRIBUTE28
387 ,P_ATTRIBUTE29
388 ,P_ATTRIBUTE30
389 ,P_PER_INFORMATION_CATEGORY
390 ,P_PER_INFORMATION1
391 ,P_PER_INFORMATION2
392 ,P_PER_INFORMATION3
393 ,P_PER_INFORMATION4
394 ,P_PER_INFORMATION5
395 ,P_PER_INFORMATION6
396 ,P_PER_INFORMATION7
400 ,P_PER_INFORMATION11
397 ,P_PER_INFORMATION8
398 ,P_PER_INFORMATION9
399 ,P_PER_INFORMATION10
401 ,P_PER_INFORMATION12
402 ,P_PER_INFORMATION13
403 ,P_PER_INFORMATION14
404 ,P_PER_INFORMATION15
405 ,P_PER_INFORMATION16
406 ,P_PER_INFORMATION17
407 ,P_PER_INFORMATION18
408 ,P_PER_INFORMATION19
409 ,P_PER_INFORMATION20
410 ,P_PER_INFORMATION21
411 ,P_PER_INFORMATION22
412 ,P_PER_INFORMATION23
413 ,P_PER_INFORMATION24
414 ,P_PER_INFORMATION25
415 ,P_PER_INFORMATION26
416 ,P_PER_INFORMATION27
417 ,P_PER_INFORMATION28
418 ,P_PER_INFORMATION29
419 ,P_PER_INFORMATION30
420 ,P_BACKGROUND_CHECK_STATUS
421 ,dc(P_BACKGROUND_DATE_CHECK)
422 ,P_FTE_CAPACITY
423 ,dc(P_HOLD_APPLICANT_DATE_UNTIL)
424 ,P_HONORS
425 ,P_MAILSTOP
426 ,P_OFFICE_NUMBER
427 ,P_ON_MILITARY_SERVICE
428 ,P_PRE_NAME_ADJUNCT
429 ,dc(P_PROJECTED_START_DATE)
430 ,P_RESUME_EXISTS
431 ,dc(P_RESUME_LAST_UPDATED)
432 ,P_STUDENT_STATUS
433 ,P_WORK_SCHEDULE
434 ,P_SUFFIX
435 ,dc(P_DATE_OF_DEATH)
436 ,dc(P_RECEIPT_OF_DEATH_CERT_DATE)
437 ,P_COORD_BEN_MED_PLN_NO
438 ,P_COORD_BEN_NO_CVG_FLAG
439 ,P_USES_TOBACCO_FLAG
440 ,dc(P_DPDNT_ADOPTION_DATE)
441 ,P_DPDNT_VLNTRY_SVCE_FLAG
442 ,dc(P_ORIGINAL_DATE_OF_HIRE)
443 ,P_TOWN_OF_BIRTH
444 ,P_REGION_OF_BIRTH
445 ,P_COUNTRY_OF_BIRTH
446 ,P_GLOBAL_PERSON_ID
447 ,P_PARTY_ID
448 ,P_PERSON_USER_KEY
449 ,P_ASSIGNMENT_USER_KEY
450 ,P_APPLICATION_USER_KEY
451 ,P_USER_PERSON_TYPE
452 ,P_LANGUAGE_CODE
453 ,P_CORRESPONDENCE_LANGUAGE
454 ,P_BENEFIT_GROUP
455 ,P_VACANCY_USER_KEY);
456 end insert_batch_lines;
457 --
458 procedure call
459 (p_business_group_id in number,
460 p_batch_line_id in number) is
461 cursor cr is
462 select l.rowid myrowid,
463 decode(l.pval001,cn,dn,d(l.pval001)) p1,
464 decode(l.pval002,cn,vn,l.pval002) p2,
465 decode(l.pval003,cn,vn,l.pval003) p3,
466 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
467 l.pval004 d4,
468 decode(l.pval005,cn,dn,vn,dn,d(l.pval005)) p5,
469 l.pval005 d5,
470 decode(l.pval006,cn,dn,vn,dn,d(l.pval006)) p6,
471 l.pval006 d6,
472 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
473 l.pval007 d7,
474 decode(l.pval008,cn,vn,vn,vn,
475 hr_pump_get.gl(l.pval008,'HOME_OFFICE',d(l.pval001),l.pval124)) p8,
476 l.pval008 d8,
477 decode(l.pval009,cn,vn,vn,vn,l.pval009) p9,
478 l.pval009 d9,
479 decode(l.pval010,cn,vn,vn,vn,l.pval010) p10,
480 l.pval010 d10,
481 decode(l.pval011,cn,vn,vn,vn,
482 hr_pump_get.gl(l.pval011,'MAR_STATUS',d(l.pval001),l.pval124)) p11,
483 l.pval011 d11,
484 decode(l.pval012,cn,vn,vn,vn,l.pval012) p12,
485 l.pval012 d12,
486 decode(l.pval013,cn,vn,vn,vn,
487 hr_pump_get.gl(l.pval013,'NATIONALITY',d(l.pval001),l.pval124)) p13,
488 l.pval013 d13,
489 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
490 l.pval014 d14,
491 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
492 l.pval015 d15,
493 decode(l.pval016,cn,vn,vn,vn,
494 hr_pump_get.gl(l.pval016,'REGISTERED_DISABLED',d(l.pval001),l.pval124)) p16,
495 l.pval016 d16,
496 decode(l.pval017,cn,vn,vn,vn,
497 hr_pump_get.gl(l.pval017,'SEX',d(l.pval001),l.pval124)) p17,
498 l.pval017 d17,
499 decode(l.pval018,cn,vn,vn,vn,
500 hr_pump_get.gl(l.pval018,'TITLE',d(l.pval001),l.pval124)) p18,
501 l.pval018 d18,
502 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
503 l.pval019 d19,
504 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
505 l.pval020 d20,
506 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
507 l.pval021 d21,
508 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
509 l.pval022 d22,
510 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
511 l.pval023 d23,
512 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
513 l.pval024 d24,
514 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
515 l.pval025 d25,
516 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
517 l.pval026 d26,
518 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
519 l.pval027 d27,
520 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
521 l.pval028 d28,
522 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
523 l.pval029 d29,
524 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
525 l.pval030 d30,
526 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
527 l.pval031 d31,
528 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
529 l.pval032 d32,
530 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
531 l.pval033 d33,
532 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
533 l.pval034 d34,
534 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
535 l.pval035 d35,
536 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
537 l.pval036 d36,
538 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
539 l.pval037 d37,
540 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
541 l.pval038 d38,
542 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
543 l.pval039 d39,
544 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
545 l.pval040 d40,
546 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
547 l.pval041 d41,
548 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
549 l.pval042 d42,
550 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
551 l.pval043 d43,
552 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
553 l.pval044 d44,
554 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
555 l.pval045 d45,
556 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
557 l.pval046 d46,
558 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
559 l.pval047 d47,
560 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
561 l.pval048 d48,
562 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
563 l.pval049 d49,
564 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
565 l.pval050 d50,
566 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
567 l.pval051 d51,
571 l.pval053 d53,
568 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
569 l.pval052 d52,
570 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
572 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
573 l.pval054 d54,
574 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
575 l.pval055 d55,
576 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
577 l.pval056 d56,
578 decode(l.pval057,cn,vn,vn,vn,l.pval057) p57,
579 l.pval057 d57,
580 decode(l.pval058,cn,vn,vn,vn,l.pval058) p58,
581 l.pval058 d58,
582 decode(l.pval059,cn,vn,vn,vn,l.pval059) p59,
583 l.pval059 d59,
584 decode(l.pval060,cn,vn,vn,vn,l.pval060) p60,
585 l.pval060 d60,
586 decode(l.pval061,cn,vn,vn,vn,l.pval061) p61,
587 l.pval061 d61,
588 decode(l.pval062,cn,vn,vn,vn,l.pval062) p62,
589 l.pval062 d62,
590 decode(l.pval063,cn,vn,vn,vn,l.pval063) p63,
591 l.pval063 d63,
592 decode(l.pval064,cn,vn,vn,vn,l.pval064) p64,
593 l.pval064 d64,
594 decode(l.pval065,cn,vn,vn,vn,l.pval065) p65,
595 l.pval065 d65,
596 decode(l.pval066,cn,vn,vn,vn,l.pval066) p66,
597 l.pval066 d66,
598 decode(l.pval067,cn,vn,vn,vn,l.pval067) p67,
599 l.pval067 d67,
600 decode(l.pval068,cn,vn,vn,vn,l.pval068) p68,
601 l.pval068 d68,
602 decode(l.pval069,cn,vn,vn,vn,l.pval069) p69,
603 l.pval069 d69,
604 decode(l.pval070,cn,vn,vn,vn,l.pval070) p70,
605 l.pval070 d70,
606 decode(l.pval071,cn,vn,vn,vn,l.pval071) p71,
607 l.pval071 d71,
608 decode(l.pval072,cn,vn,vn,vn,l.pval072) p72,
609 l.pval072 d72,
610 decode(l.pval073,cn,vn,vn,vn,l.pval073) p73,
611 l.pval073 d73,
612 decode(l.pval074,cn,vn,vn,vn,l.pval074) p74,
613 l.pval074 d74,
614 decode(l.pval075,cn,vn,vn,vn,l.pval075) p75,
615 l.pval075 d75,
616 decode(l.pval076,cn,vn,vn,vn,l.pval076) p76,
617 l.pval076 d76,
618 decode(l.pval077,cn,vn,vn,vn,l.pval077) p77,
619 l.pval077 d77,
620 decode(l.pval078,cn,vn,vn,vn,l.pval078) p78,
621 l.pval078 d78,
622 decode(l.pval079,cn,vn,vn,vn,l.pval079) p79,
623 l.pval079 d79,
624 decode(l.pval080,cn,vn,vn,vn,l.pval080) p80,
625 l.pval080 d80,
626 decode(l.pval081,cn,vn,vn,vn,l.pval081) p81,
627 l.pval081 d81,
628 decode(l.pval082,cn,vn,vn,vn,
629 hr_pump_get.gl(l.pval082,'YES_NO',d(l.pval001),l.pval124)) p82,
630 l.pval082 d82,
631 decode(l.pval083,cn,dn,vn,dn,d(l.pval083)) p83,
632 l.pval083 d83,
633 decode(l.pval084,cn,nn,vn,nn,n(l.pval084)) p84,
634 l.pval084 d84,
635 decode(l.pval085,cn,dn,vn,dn,d(l.pval085)) p85,
636 l.pval085 d85,
637 decode(l.pval086,cn,vn,vn,vn,l.pval086) p86,
638 l.pval086 d86,
639 decode(l.pval087,cn,vn,vn,vn,l.pval087) p87,
640 l.pval087 d87,
641 decode(l.pval088,cn,vn,vn,vn,l.pval088) p88,
642 l.pval088 d88,
643 decode(l.pval089,cn,vn,vn,vn,l.pval089) p89,
644 l.pval089 d89,
645 decode(l.pval090,cn,vn,vn,vn,l.pval090) p90,
646 l.pval090 d90,
647 decode(l.pval091,cn,dn,vn,dn,d(l.pval091)) p91,
648 l.pval091 d91,
649 decode(l.pval092,cn,vn,vn,vn,
650 hr_pump_get.gl(l.pval092,'YES_NO',d(l.pval001),l.pval124)) p92,
651 l.pval092 d92,
652 decode(l.pval093,cn,dn,vn,dn,d(l.pval093)) p93,
653 l.pval093 d93,
654 decode(l.pval094,cn,vn,vn,vn,l.pval094) p94,
655 l.pval094 d94,
656 decode(l.pval095,cn,vn,vn,vn,
657 hr_pump_get.gl(l.pval095,'WORK_SCHEDULE',d(l.pval001),l.pval124)) p95,
658 l.pval095 d95,
659 decode(l.pval096,cn,vn,vn,vn,l.pval096) p96,
660 l.pval096 d96,
661 decode(l.pval097,cn,dn,vn,dn,d(l.pval097)) p97,
662 l.pval097 d97,
663 decode(l.pval098,cn,dn,vn,dn,d(l.pval098)) p98,
664 l.pval098 d98,
665 decode(l.pval099,cn,vn,vn,vn,l.pval099) p99,
666 l.pval099 d99,
667 decode(l.pval100,cn,vn,vn,vn,l.pval100) p100,
668 l.pval100 d100,
669 decode(l.pval101,cn,vn,vn,vn,l.pval101) p101,
670 l.pval101 d101,
671 decode(l.pval102,cn,dn,vn,dn,d(l.pval102)) p102,
672 l.pval102 d102,
673 decode(l.pval103,cn,vn,vn,vn,l.pval103) p103,
674 l.pval103 d103,
675 decode(l.pval104,cn,dn,vn,dn,d(l.pval104)) p104,
676 l.pval104 d104,
677 decode(l.pval105,cn,vn,vn,vn,l.pval105) p105,
678 l.pval105 d105,
679 decode(l.pval106,cn,vn,vn,vn,l.pval106) p106,
680 l.pval106 d106,
681 decode(l.pval107,cn,vn,vn,vn,l.pval107) p107,
682 l.pval107 d107,
683 decode(l.pval108,cn,vn,vn,vn,l.pval108) p108,
684 l.pval108 d108,
685 decode(l.pval109,cn,nn,vn,nn,n(l.pval109)) p109,
686 l.pval109 d109,
687 l.pval110 p110,
688 l.pval111 p111,
689 l.pval112 p112,
690 l.pval113 p113,
691 l.pval114 p114,
692 l.pval115 p115,
693 decode(l.pval116,cn,dn,d(l.pval116)) p116,
694 decode(l.pval117,cn,dn,d(l.pval117)) p117,
695 l.pval118 p118,
696 l.pval119 p119,
697 l.pval120 p120,
698 l.pval121 p121,
699 l.pval122 p122,
703 l.pval124 d124,
700 decode(l.pval123,cn,vn,vn,vn,l.pval123) p123,
701 l.pval123 d123,
702 decode(l.pval124,cn,vn,vn,vn,l.pval124) p124,
704 decode(l.pval125,cn,vn,vn,vn,l.pval125) p125,
705 l.pval125 d125,
706 decode(l.pval126,cn,vn,vn,vn,l.pval126) p126,
707 l.pval126 d126,
708 decode(l.pval127,cn,vn,vn,vn,l.pval127) p127,
709 l.pval127 d127
710 from hr_pump_batch_lines l
711 where l.batch_line_id = p_batch_line_id;
712 --
713 c cr%rowtype;
714 l_validate boolean := false;
715 L_PERSON_ID number;
716 L_ASSIGNMENT_ID number;
717 L_APPLICATION_ID number;
718 L_NAME_COMBINATION_WARNING boolean;
719 L_ORIG_HIRE_WARNING boolean;
720 L_PERSON_TYPE_ID number;
721 L_CORRESPONDENCE_LANGUAGE varchar2(2000);
722 L_BENEFIT_GROUP_ID number;
723 L_VACANCY_ID number;
724 --
725 begin
726 hr_data_pump.entry('call');
727 open cr;
728 fetch cr into c;
729 if cr%notfound then
730 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
731 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
732 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
733 hr_utility.set_message_token('VALUE',p_batch_line_id);
734 hr_utility.raise_error;
735 end if;
736 --
737 if c.p123 is null or
738 c.p124 is null then
739 L_PERSON_TYPE_ID:=nn;
740 else
741 L_PERSON_TYPE_ID :=
742 hr_pump_get.get_person_type_id
743 (P_USER_PERSON_TYPE => c.p123
744 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
745 ,P_LANGUAGE_CODE => c.p124);
746 end if;
747 --
748 if c.p125 is null then
749 L_CORRESPONDENCE_LANGUAGE:=vn;
750 else
751 L_CORRESPONDENCE_LANGUAGE :=
752 hr_pump_get.GET_CORRESPONDENCE_LANGUAGE
753 (P_CORRESPONDENCE_LANGUAGE => c.p125);
754 end if;
755 --
756 if c.p126 is null then
757 L_BENEFIT_GROUP_ID:=nn;
758 else
759 L_BENEFIT_GROUP_ID :=
760 hr_pump_get.get_benefit_group_id
761 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
762 ,P_BENEFIT_GROUP => c.p126);
763 end if;
764 --
765 if c.p127 is null then
766 L_VACANCY_ID:=nn;
767 else
768 L_VACANCY_ID :=
769 hr_pump_get.get_vacancy_id
770 (P_VACANCY_USER_KEY => c.p127);
771 end if;
772 --
773 hr_data_pump.api_trc_on;
774 HR_APPLICANT_API.CREATE_APPLICANT
775 (p_validate => l_validate
776 ,P_DATE_RECEIVED => c.p1
777 ,p_business_group_id => p_business_group_id
778 ,P_LAST_NAME => c.p2
779 ,P_PERSON_TYPE_ID => L_PERSON_TYPE_ID
780 ,P_APPLICANT_NUMBER => c.p3
781 ,P_PER_COMMENTS => c.p4
782 ,P_DATE_EMPLOYEE_DATA_VERIFIED => c.p5
783 ,P_DATE_OF_BIRTH => c.p6
784 ,P_EMAIL_ADDRESS => c.p7
785 ,P_EXPENSE_CHECK_SEND_TO_ADDRES => c.p8
786 ,P_FIRST_NAME => c.p9
787 ,P_KNOWN_AS => c.p10
788 ,P_MARITAL_STATUS => c.p11
789 ,P_MIDDLE_NAMES => c.p12
790 ,P_NATIONALITY => c.p13
791 ,P_NATIONAL_IDENTIFIER => c.p14
792 ,P_PREVIOUS_LAST_NAME => c.p15
793 ,P_REGISTERED_DISABLED_FLAG => c.p16
794 ,P_SEX => c.p17
795 ,P_TITLE => c.p18
796 ,P_WORK_TELEPHONE => c.p19
797 ,P_ATTRIBUTE_CATEGORY => c.p20
798 ,P_ATTRIBUTE1 => c.p21
799 ,P_ATTRIBUTE2 => c.p22
800 ,P_ATTRIBUTE3 => c.p23
801 ,P_ATTRIBUTE4 => c.p24
802 ,P_ATTRIBUTE5 => c.p25
803 ,P_ATTRIBUTE6 => c.p26
804 ,P_ATTRIBUTE7 => c.p27
805 ,P_ATTRIBUTE8 => c.p28
806 ,P_ATTRIBUTE9 => c.p29
807 ,P_ATTRIBUTE10 => c.p30
808 ,P_ATTRIBUTE11 => c.p31
809 ,P_ATTRIBUTE12 => c.p32
810 ,P_ATTRIBUTE13 => c.p33
811 ,P_ATTRIBUTE14 => c.p34
812 ,P_ATTRIBUTE15 => c.p35
813 ,P_ATTRIBUTE16 => c.p36
814 ,P_ATTRIBUTE17 => c.p37
815 ,P_ATTRIBUTE18 => c.p38
816 ,P_ATTRIBUTE19 => c.p39
817 ,P_ATTRIBUTE20 => c.p40
818 ,P_ATTRIBUTE21 => c.p41
819 ,P_ATTRIBUTE22 => c.p42
820 ,P_ATTRIBUTE23 => c.p43
821 ,P_ATTRIBUTE24 => c.p44
822 ,P_ATTRIBUTE25 => c.p45
823 ,P_ATTRIBUTE26 => c.p46
824 ,P_ATTRIBUTE27 => c.p47
825 ,P_ATTRIBUTE28 => c.p48
826 ,P_ATTRIBUTE29 => c.p49
827 ,P_ATTRIBUTE30 => c.p50
828 ,P_PER_INFORMATION_CATEGORY => c.p51
829 ,P_PER_INFORMATION1 => c.p52
830 ,P_PER_INFORMATION2 => c.p53
831 ,P_PER_INFORMATION3 => c.p54
832 ,P_PER_INFORMATION4 => c.p55
833 ,P_PER_INFORMATION5 => c.p56
834 ,P_PER_INFORMATION6 => c.p57
835 ,P_PER_INFORMATION7 => c.p58
836 ,P_PER_INFORMATION8 => c.p59
837 ,P_PER_INFORMATION9 => c.p60
838 ,P_PER_INFORMATION10 => c.p61
839 ,P_PER_INFORMATION11 => c.p62
840 ,P_PER_INFORMATION12 => c.p63
841 ,P_PER_INFORMATION13 => c.p64
842 ,P_PER_INFORMATION14 => c.p65
843 ,P_PER_INFORMATION15 => c.p66
844 ,P_PER_INFORMATION16 => c.p67
845 ,P_PER_INFORMATION17 => c.p68
846 ,P_PER_INFORMATION18 => c.p69
847 ,P_PER_INFORMATION19 => c.p70
848 ,P_PER_INFORMATION20 => c.p71
849 ,P_PER_INFORMATION21 => c.p72
850 ,P_PER_INFORMATION22 => c.p73
851 ,P_PER_INFORMATION23 => c.p74
852 ,P_PER_INFORMATION24 => c.p75
853 ,P_PER_INFORMATION25 => c.p76
854 ,P_PER_INFORMATION26 => c.p77
855 ,P_PER_INFORMATION27 => c.p78
856 ,P_PER_INFORMATION28 => c.p79
857 ,P_PER_INFORMATION29 => c.p80
858 ,P_PER_INFORMATION30 => c.p81
859 ,P_BACKGROUND_CHECK_STATUS => c.p82
860 ,P_BACKGROUND_DATE_CHECK => c.p83
861 ,P_CORRESPONDENCE_LANGUAGE => L_CORRESPONDENCE_LANGUAGE
862 ,P_FTE_CAPACITY => c.p84
863 ,P_HOLD_APPLICANT_DATE_UNTIL => c.p85
864 ,P_HONORS => c.p86
865 ,P_MAILSTOP => c.p87
866 ,P_OFFICE_NUMBER => c.p88
867 ,P_ON_MILITARY_SERVICE => c.p89
868 ,P_PRE_NAME_ADJUNCT => c.p90
869 ,P_PROJECTED_START_DATE => c.p91
870 ,P_RESUME_EXISTS => c.p92
874 ,P_SUFFIX => c.p96
871 ,P_RESUME_LAST_UPDATED => c.p93
872 ,P_STUDENT_STATUS => c.p94
873 ,P_WORK_SCHEDULE => c.p95
875 ,P_DATE_OF_DEATH => c.p97
876 ,P_BENEFIT_GROUP_ID => L_BENEFIT_GROUP_ID
877 ,P_RECEIPT_OF_DEATH_CERT_DATE => c.p98
878 ,P_COORD_BEN_MED_PLN_NO => c.p99
879 ,P_COORD_BEN_NO_CVG_FLAG => c.p100
880 ,P_USES_TOBACCO_FLAG => c.p101
881 ,P_DPDNT_ADOPTION_DATE => c.p102
882 ,P_DPDNT_VLNTRY_SVCE_FLAG => c.p103
883 ,P_ORIGINAL_DATE_OF_HIRE => c.p104
884 ,P_TOWN_OF_BIRTH => c.p105
885 ,P_REGION_OF_BIRTH => c.p106
886 ,P_COUNTRY_OF_BIRTH => c.p107
887 ,P_GLOBAL_PERSON_ID => c.p108
888 ,P_PARTY_ID => c.p109
889 ,P_VACANCY_ID => L_VACANCY_ID
890 ,P_PERSON_ID => L_PERSON_ID
891 ,P_ASSIGNMENT_ID => L_ASSIGNMENT_ID
892 ,P_APPLICATION_ID => L_APPLICATION_ID
893 ,P_PER_OBJECT_VERSION_NUMBER => c.p113
894 ,P_ASG_OBJECT_VERSION_NUMBER => c.p114
895 ,P_APL_OBJECT_VERSION_NUMBER => c.p115
896 ,P_PER_EFFECTIVE_START_DATE => c.p116
897 ,P_PER_EFFECTIVE_END_DATE => c.p117
898 ,P_FULL_NAME => c.p118
899 ,P_PER_COMMENT_ID => c.p119
900 ,P_ASSIGNMENT_SEQUENCE => c.p120
901 ,P_NAME_COMBINATION_WARNING => L_NAME_COMBINATION_WARNING
902 ,P_ORIG_HIRE_WARNING => L_ORIG_HIRE_WARNING);
903 hr_data_pump.api_trc_off;
904 --
905 iuk(p_batch_line_id,c.p110,L_PERSON_ID);
906 --
907 iuk(p_batch_line_id,c.p111,L_ASSIGNMENT_ID);
908 --
909 iuk(p_batch_line_id,c.p112,L_APPLICATION_ID);
910 --
911 if L_NAME_COMBINATION_WARNING then
912 c.p121 := 'TRUE';
913 else
914 c.p121 := 'FALSE';
915 end if;
916 --
917 if L_ORIG_HIRE_WARNING then
918 c.p122 := 'TRUE';
919 else
920 c.p122 := 'FALSE';
921 end if;
922 --
923 update hr_pump_batch_lines l set
924 l.pval003 = decode(c.p3,null,cn,c.p3),
925 l.pval110 = decode(c.p110,null,cn,c.p110),
926 l.pval111 = decode(c.p111,null,cn,c.p111),
927 l.pval112 = decode(c.p112,null,cn,c.p112),
928 l.pval113 = decode(c.p113,null,cn,c.p113),
929 l.pval114 = decode(c.p114,null,cn,c.p114),
930 l.pval115 = decode(c.p115,null,cn,c.p115),
931 l.pval116 = decode(c.p116,null,cn,dc(c.p116)),
932 l.pval117 = decode(c.p117,null,cn,dc(c.p117)),
933 l.pval118 = decode(c.p118,null,cn,c.p118),
934 l.pval119 = decode(c.p119,null,cn,c.p119),
935 l.pval120 = decode(c.p120,null,cn,c.p120),
936 l.pval121 = decode(c.p121,null,cn,c.p121),
937 l.pval122 = decode(c.p122,null,cn,c.p122)
938 where l.rowid = c.myrowid;
939 --
940 close cr;
941 --
942 hr_data_pump.exit('call');
943 exception
944 when hr_multi_message.error_message_exist then
945 if cr%isopen then
946 close cr;
947 end if;
948 hr_pump_utils.set_multi_msg_error_flag(true);
949 when others then
950 if cr%isopen then
951 close cr;
952 end if;
953 raise;
954 end call;
955 end hrdpp_CREATE_APPLICANT;