eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HRDPP_CREATE_AU_APPLICANT

Source


1 package body hrdpp_CREATE_AU_APPLICANT as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2009/03/30 04:03:29
4  * Generated for API: HR_AU_APPLICANT_API.CREATE_AU_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_SEX in varchar2 default null
85 ,P_APPLICANT_NUMBER in varchar2
86 ,P_COMMENTS in varchar2 default null
87 ,P_DATE_EMPLOYEE_DATA_VERIFIED in date default null
88 ,P_DATE_OF_BIRTH in date default null
89 ,P_EMAIL_ADDRESS in varchar2 default null
90 ,P_EXPENSE_CHECK_SEND_TO_ADDRES in varchar2 default null
91 ,P_FIRST_NAME in varchar2 default null
92 ,P_KNOWN_AS in varchar2 default null
93 ,P_MARITAL_STATUS in varchar2 default null
94 ,P_MIDDLE_NAMES in varchar2 default null
95 ,P_NATIONALITY in varchar2 default null
96 ,P_NATIONAL_IDENTIFIER in varchar2 default null
97 ,P_PREVIOUS_LAST_NAME in varchar2 default null
98 ,P_REGISTERED_DISABLED_FLAG 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_COUNTRY_OF_BIRTH in varchar2 default null
133 ,P_BACKGROUND_CHECK_STATUS in varchar2 default null
134 ,P_BACKGROUND_DATE_CHECK in date default null
135 ,P_FTE_CAPACITY in number default null
136 ,P_HOLD_APPLICANT_DATE_UNTIL in date default null
137 ,P_HONORS in varchar2 default null
138 ,P_MAILSTOP in varchar2 default null
139 ,P_OFFICE_NUMBER in varchar2 default null
140 ,P_ON_MILITARY_SERVICE in varchar2 default null
141 ,P_PRE_NAME_ADJUNCT in varchar2 default null
142 ,P_PROJECTED_START_DATE in date default null
143 ,P_RESUME_EXISTS in varchar2 default null
144 ,P_RESUME_LAST_UPDATED in date default null
145 ,P_STUDENT_STATUS in varchar2 default null
146 ,P_WORK_SCHEDULE in varchar2 default null
147 ,P_SUFFIX in varchar2 default null
148 ,P_DATE_OF_DEATH in date default null
149 ,P_RECEIPT_OF_DEATH_CERT_DATE in date default null
150 ,P_COORD_BEN_MED_PLN_NO in varchar2 default null
151 ,P_COORD_BEN_NO_CVG_FLAG in varchar2 default null
152 ,P_USES_TOBACCO_FLAG in varchar2 default null
153 ,P_DPDNT_ADOPTION_DATE in date default null
154 ,P_DPDNT_VLNTRY_SVCE_FLAG in varchar2 default null
155 ,P_ORIGINAL_DATE_OF_HIRE in date default null
156 ,P_PERSON_USER_KEY in varchar2
157 ,P_ASSIGNMENT_USER_KEY in varchar2
158 ,P_APPLICATION_USER_KEY in varchar2
159 ,P_USER_PERSON_TYPE in varchar2 default null
160 ,P_LANGUAGE_CODE in varchar2 default null
161 ,P_CORRESPONDENCE_LANGUAGE in varchar2 default null
162 ,P_BENEFIT_GROUP in varchar2 default null) is
163 blid number := p_data_pump_batch_line_id;
164 begin
165 if blid is not null then
166 delete from hr_pump_batch_lines where batch_line_id = blid;
167 delete from hr_pump_batch_exceptions
168 where source_type = 'BATCH_LINE' and source_id = blid;
169 end if;
170 insert into hr_pump_batch_lines
171 (batch_id
172 ,batch_line_id
173 ,business_group_name
174 ,api_module_id
175 ,line_status
176 ,user_sequence
177 ,link_value
178 ,pval001
179 ,pval002
180 ,pval003
181 ,pval004
182 ,pval005
183 ,pval006
184 ,pval007
185 ,pval008
186 ,pval009
187 ,pval010
188 ,pval011
189 ,pval012
190 ,pval013
191 ,pval014
192 ,pval015
193 ,pval016
194 ,pval017
195 ,pval018
196 ,pval019
197 ,pval020
198 ,pval021
199 ,pval022
200 ,pval023
201 ,pval024
202 ,pval025
203 ,pval026
204 ,pval027
205 ,pval028
206 ,pval029
207 ,pval030
208 ,pval031
209 ,pval032
210 ,pval033
211 ,pval034
212 ,pval035
213 ,pval036
214 ,pval037
215 ,pval038
216 ,pval039
217 ,pval040
218 ,pval041
219 ,pval042
220 ,pval043
221 ,pval044
222 ,pval045
223 ,pval046
224 ,pval047
225 ,pval048
226 ,pval049
227 ,pval050
228 ,pval051
229 ,pval052
230 ,pval053
231 ,pval054
232 ,pval055
233 ,pval056
234 ,pval057
235 ,pval058
236 ,pval059
237 ,pval060
238 ,pval061
239 ,pval062
240 ,pval063
241 ,pval064
242 ,pval065
243 ,pval066
244 ,pval067
245 ,pval068
246 ,pval069
247 ,pval070
248 ,pval071
249 ,pval072
250 ,pval073
251 ,pval074
252 ,pval075
253 ,pval076
254 ,pval077
255 ,pval088
256 ,pval089
257 ,pval090
258 ,pval091)
259 values
260 (p_batch_id
261 ,nvl(blid,hr_pump_batch_lines_s.nextval)
262 ,p_data_pump_business_grp_name
263 ,2165
264 ,'U'
265 ,p_user_sequence
266 ,p_link_value
267 ,dc(P_DATE_RECEIVED)
268 ,P_LAST_NAME
269 ,P_SEX
270 ,P_APPLICANT_NUMBER
271 ,P_COMMENTS
272 ,dc(P_DATE_EMPLOYEE_DATA_VERIFIED)
273 ,dc(P_DATE_OF_BIRTH)
274 ,P_EMAIL_ADDRESS
275 ,P_EXPENSE_CHECK_SEND_TO_ADDRES
276 ,P_FIRST_NAME
277 ,P_KNOWN_AS
278 ,P_MARITAL_STATUS
279 ,P_MIDDLE_NAMES
280 ,P_NATIONALITY
281 ,P_NATIONAL_IDENTIFIER
282 ,P_PREVIOUS_LAST_NAME
283 ,P_REGISTERED_DISABLED_FLAG
284 ,P_TITLE
285 ,P_WORK_TELEPHONE
286 ,P_ATTRIBUTE_CATEGORY
287 ,P_ATTRIBUTE1
288 ,P_ATTRIBUTE2
289 ,P_ATTRIBUTE3
290 ,P_ATTRIBUTE4
291 ,P_ATTRIBUTE5
292 ,P_ATTRIBUTE6
293 ,P_ATTRIBUTE7
294 ,P_ATTRIBUTE8
295 ,P_ATTRIBUTE9
296 ,P_ATTRIBUTE10
297 ,P_ATTRIBUTE11
298 ,P_ATTRIBUTE12
299 ,P_ATTRIBUTE13
300 ,P_ATTRIBUTE14
301 ,P_ATTRIBUTE15
302 ,P_ATTRIBUTE16
303 ,P_ATTRIBUTE17
304 ,P_ATTRIBUTE18
305 ,P_ATTRIBUTE19
306 ,P_ATTRIBUTE20
307 ,P_ATTRIBUTE21
308 ,P_ATTRIBUTE22
309 ,P_ATTRIBUTE23
310 ,P_ATTRIBUTE24
311 ,P_ATTRIBUTE25
312 ,P_ATTRIBUTE26
313 ,P_ATTRIBUTE27
314 ,P_ATTRIBUTE28
315 ,P_ATTRIBUTE29
316 ,P_ATTRIBUTE30
317 ,P_COUNTRY_OF_BIRTH
318 ,P_BACKGROUND_CHECK_STATUS
319 ,dc(P_BACKGROUND_DATE_CHECK)
320 ,P_FTE_CAPACITY
321 ,dc(P_HOLD_APPLICANT_DATE_UNTIL)
322 ,P_HONORS
323 ,P_MAILSTOP
324 ,P_OFFICE_NUMBER
325 ,P_ON_MILITARY_SERVICE
326 ,P_PRE_NAME_ADJUNCT
327 ,dc(P_PROJECTED_START_DATE)
328 ,P_RESUME_EXISTS
329 ,dc(P_RESUME_LAST_UPDATED)
330 ,P_STUDENT_STATUS
331 ,P_WORK_SCHEDULE
332 ,P_SUFFIX
333 ,dc(P_DATE_OF_DEATH)
334 ,dc(P_RECEIPT_OF_DEATH_CERT_DATE)
335 ,P_COORD_BEN_MED_PLN_NO
336 ,P_COORD_BEN_NO_CVG_FLAG
337 ,P_USES_TOBACCO_FLAG
338 ,dc(P_DPDNT_ADOPTION_DATE)
339 ,P_DPDNT_VLNTRY_SVCE_FLAG
340 ,dc(P_ORIGINAL_DATE_OF_HIRE)
341 ,P_PERSON_USER_KEY
342 ,P_ASSIGNMENT_USER_KEY
343 ,P_APPLICATION_USER_KEY
344 ,P_USER_PERSON_TYPE
345 ,P_LANGUAGE_CODE
346 ,P_CORRESPONDENCE_LANGUAGE
347 ,P_BENEFIT_GROUP);
348 end insert_batch_lines;
349 --
350 procedure call
351 (p_business_group_id in number,
352 p_batch_line_id     in number) is
353 cursor cr is
354 select l.rowid myrowid,
355 decode(l.pval001,cn,dn,d(l.pval001)) p1,
356 decode(l.pval002,cn,vn,l.pval002) p2,
357 decode(l.pval003,cn,vn,vn,vn,
358  hr_pump_get.gl(l.pval003,'SEX',d(l.pval001),l.pval089)) p3,
359 l.pval003 d3,
360 decode(l.pval004,cn,vn,l.pval004) p4,
361 decode(l.pval005,cn,vn,vn,vn,l.pval005) p5,
362 l.pval005 d5,
363 decode(l.pval006,cn,dn,vn,dn,d(l.pval006)) p6,
364 l.pval006 d6,
365 decode(l.pval007,cn,dn,vn,dn,d(l.pval007)) p7,
366 l.pval007 d7,
367 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
368 l.pval008 d8,
369 decode(l.pval009,cn,vn,vn,vn,
370  hr_pump_get.gl(l.pval009,'HOME_OFFICE',d(l.pval001),l.pval089)) p9,
371 l.pval009 d9,
372 decode(l.pval010,cn,vn,vn,vn,l.pval010) p10,
373 l.pval010 d10,
374 decode(l.pval011,cn,vn,vn,vn,l.pval011) p11,
375 l.pval011 d11,
376 decode(l.pval012,cn,vn,vn,vn,
377  hr_pump_get.gl(l.pval012,'MAR_STATUS',d(l.pval001),l.pval089)) p12,
378 l.pval012 d12,
379 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
380 l.pval013 d13,
381 decode(l.pval014,cn,vn,vn,vn,
382  hr_pump_get.gl(l.pval014,'NATIONALITY',d(l.pval001),l.pval089)) p14,
383 l.pval014 d14,
384 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
385 l.pval015 d15,
386 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
387 l.pval016 d16,
388 decode(l.pval017,cn,vn,vn,vn,
389  hr_pump_get.gl(l.pval017,'YES_NO',d(l.pval001),l.pval089)) p17,
390 l.pval017 d17,
391 decode(l.pval018,cn,vn,vn,vn,
392  hr_pump_get.gl(l.pval018,'TITLE',d(l.pval001),l.pval089)) p18,
393 l.pval018 d18,
394 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
395 l.pval019 d19,
396 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
397 l.pval020 d20,
398 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
399 l.pval021 d21,
400 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
401 l.pval022 d22,
402 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
403 l.pval023 d23,
404 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
405 l.pval024 d24,
406 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
407 l.pval025 d25,
408 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
409 l.pval026 d26,
410 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
411 l.pval027 d27,
412 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
413 l.pval028 d28,
414 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
415 l.pval029 d29,
416 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
417 l.pval030 d30,
418 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
419 l.pval031 d31,
420 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
421 l.pval032 d32,
422 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
423 l.pval033 d33,
424 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
425 l.pval034 d34,
426 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
427 l.pval035 d35,
428 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
429 l.pval036 d36,
430 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
431 l.pval037 d37,
432 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
433 l.pval038 d38,
434 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
435 l.pval039 d39,
436 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
437 l.pval040 d40,
438 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
439 l.pval041 d41,
443 l.pval043 d43,
440 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
441 l.pval042 d42,
442 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
444 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
445 l.pval044 d44,
446 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
447 l.pval045 d45,
448 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
449 l.pval046 d46,
450 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
451 l.pval047 d47,
452 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
453 l.pval048 d48,
454 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
455 l.pval049 d49,
456 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
457 l.pval050 d50,
458 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
459 l.pval051 d51,
460 decode(l.pval052,cn,vn,vn,vn,
461  hr_pump_get.gl(l.pval052,'YES_NO',d(l.pval001),l.pval089)) p52,
462 l.pval052 d52,
463 decode(l.pval053,cn,dn,vn,dn,d(l.pval053)) p53,
464 l.pval053 d53,
465 decode(l.pval054,cn,nn,vn,nn,n(l.pval054)) p54,
466 l.pval054 d54,
467 decode(l.pval055,cn,dn,vn,dn,d(l.pval055)) p55,
468 l.pval055 d55,
469 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
470 l.pval056 d56,
471 decode(l.pval057,cn,vn,vn,vn,l.pval057) p57,
472 l.pval057 d57,
473 decode(l.pval058,cn,vn,vn,vn,l.pval058) p58,
474 l.pval058 d58,
475 decode(l.pval059,cn,vn,vn,vn,l.pval059) p59,
476 l.pval059 d59,
477 decode(l.pval060,cn,vn,vn,vn,l.pval060) p60,
478 l.pval060 d60,
479 decode(l.pval061,cn,dn,vn,dn,d(l.pval061)) p61,
480 l.pval061 d61,
481 decode(l.pval062,cn,vn,vn,vn,
482  hr_pump_get.gl(l.pval062,'YES_NO',d(l.pval001),l.pval089)) p62,
483 l.pval062 d62,
484 decode(l.pval063,cn,dn,vn,dn,d(l.pval063)) p63,
485 l.pval063 d63,
486 decode(l.pval064,cn,vn,vn,vn,l.pval064) p64,
487 l.pval064 d64,
488 decode(l.pval065,cn,vn,vn,vn,
489  hr_pump_get.gl(l.pval065,'WORK_SCHEDULE',d(l.pval001),l.pval089)) p65,
490 l.pval065 d65,
491 decode(l.pval066,cn,vn,vn,vn,l.pval066) p66,
492 l.pval066 d66,
493 decode(l.pval067,cn,dn,vn,dn,d(l.pval067)) p67,
494 l.pval067 d67,
495 decode(l.pval068,cn,dn,vn,dn,d(l.pval068)) p68,
496 l.pval068 d68,
497 decode(l.pval069,cn,vn,vn,vn,l.pval069) p69,
498 l.pval069 d69,
499 decode(l.pval070,cn,vn,vn,vn,l.pval070) p70,
500 l.pval070 d70,
501 decode(l.pval071,cn,vn,vn,vn,l.pval071) p71,
502 l.pval071 d71,
503 decode(l.pval072,cn,dn,vn,dn,d(l.pval072)) p72,
504 l.pval072 d72,
505 decode(l.pval073,cn,vn,vn,vn,l.pval073) p73,
506 l.pval073 d73,
507 decode(l.pval074,cn,dn,vn,dn,d(l.pval074)) p74,
508 l.pval074 d74,
509 l.pval075 p75,
510 l.pval076 p76,
511 l.pval077 p77,
512 l.pval078 p78,
513 l.pval079 p79,
514 l.pval080 p80,
515 decode(l.pval081,cn,dn,d(l.pval081)) p81,
516 decode(l.pval082,cn,dn,d(l.pval082)) p82,
517 l.pval083 p83,
518 l.pval084 p84,
519 l.pval085 p85,
520 l.pval086 p86,
521 l.pval087 p87,
522 decode(l.pval088,cn,vn,vn,vn,l.pval088) p88,
523 l.pval088 d88,
524 decode(l.pval089,cn,vn,vn,vn,l.pval089) p89,
525 l.pval089 d89,
526 decode(l.pval090,cn,vn,vn,vn,l.pval090) p90,
527 l.pval090 d90,
528 decode(l.pval091,cn,vn,vn,vn,l.pval091) p91,
529 l.pval091 d91
530 from hr_pump_batch_lines l
531 where l.batch_line_id = p_batch_line_id;
532 --
533 c cr%rowtype;
534 l_validate boolean := false;
535 L_PERSON_ID number;
536 L_ASSIGNMENT_ID number;
537 L_APPLICATION_ID number;
538 L_NAME_COMBINATION_WARNING boolean;
539 L_ORIG_HIRE_WARNING boolean;
540 L_PERSON_TYPE_ID number;
541 L_CORRESPONDENCE_LANGUAGE varchar2(2000);
542 L_BENEFIT_GROUP_ID number;
543 --
544 begin
545 hr_data_pump.entry('call');
546 open cr;
547 fetch cr into c;
548 if cr%notfound then
549 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
550 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
551 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
552 hr_utility.set_message_token('VALUE',p_batch_line_id);
553 hr_utility.raise_error;
554 end if;
555 --
556 if c.p88 is null or
557 c.p89 is null then
558 L_PERSON_TYPE_ID:=nn;
559 else
560 L_PERSON_TYPE_ID := 
561 hr_pump_get.get_person_type_id
562 (P_USER_PERSON_TYPE => c.p88
563 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
564 ,P_LANGUAGE_CODE => c.p89);
565 end if;
566 --
567 if c.p90 is null then
568 L_CORRESPONDENCE_LANGUAGE:=vn;
569 else
570 L_CORRESPONDENCE_LANGUAGE := 
571 hr_pump_get.GET_CORRESPONDENCE_LANGUAGE
572 (P_CORRESPONDENCE_LANGUAGE => c.p90);
573 end if;
574 --
575 if c.p91 is null then
576 L_BENEFIT_GROUP_ID:=nn;
577 else
578 L_BENEFIT_GROUP_ID := 
579 hr_pump_get.get_benefit_group_id
580 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
581 ,P_BENEFIT_GROUP => c.p91);
582 end if;
583 --
584 hr_data_pump.api_trc_on;
585 HR_AU_APPLICANT_API.CREATE_AU_APPLICANT
586 (p_validate => l_validate
587 ,P_DATE_RECEIVED => c.p1
588 ,p_business_group_id => p_business_group_id
589 ,P_LAST_NAME => c.p2
590 ,P_SEX => c.p3
591 ,P_PERSON_TYPE_ID => L_PERSON_TYPE_ID
592 ,P_APPLICANT_NUMBER => c.p4
593 ,P_COMMENTS => c.p5
594 ,P_DATE_EMPLOYEE_DATA_VERIFIED => c.p6
595 ,P_DATE_OF_BIRTH => c.p7
596 ,P_EMAIL_ADDRESS => c.p8
597 ,P_EXPENSE_CHECK_SEND_TO_ADDRES => c.p9
598 ,P_FIRST_NAME => c.p10
599 ,P_KNOWN_AS => c.p11
600 ,P_MARITAL_STATUS => c.p12
601 ,P_MIDDLE_NAMES => c.p13
602 ,P_NATIONALITY => c.p14
603 ,P_NATIONAL_IDENTIFIER => c.p15
604 ,P_PREVIOUS_LAST_NAME => c.p16
605 ,P_REGISTERED_DISABLED_FLAG => c.p17
609 ,P_ATTRIBUTE1 => c.p21
606 ,P_TITLE => c.p18
607 ,P_WORK_TELEPHONE => c.p19
608 ,P_ATTRIBUTE_CATEGORY => c.p20
610 ,P_ATTRIBUTE2 => c.p22
611 ,P_ATTRIBUTE3 => c.p23
612 ,P_ATTRIBUTE4 => c.p24
613 ,P_ATTRIBUTE5 => c.p25
614 ,P_ATTRIBUTE6 => c.p26
615 ,P_ATTRIBUTE7 => c.p27
616 ,P_ATTRIBUTE8 => c.p28
617 ,P_ATTRIBUTE9 => c.p29
618 ,P_ATTRIBUTE10 => c.p30
619 ,P_ATTRIBUTE11 => c.p31
620 ,P_ATTRIBUTE12 => c.p32
621 ,P_ATTRIBUTE13 => c.p33
622 ,P_ATTRIBUTE14 => c.p34
623 ,P_ATTRIBUTE15 => c.p35
624 ,P_ATTRIBUTE16 => c.p36
625 ,P_ATTRIBUTE17 => c.p37
626 ,P_ATTRIBUTE18 => c.p38
627 ,P_ATTRIBUTE19 => c.p39
628 ,P_ATTRIBUTE20 => c.p40
629 ,P_ATTRIBUTE21 => c.p41
630 ,P_ATTRIBUTE22 => c.p42
631 ,P_ATTRIBUTE23 => c.p43
632 ,P_ATTRIBUTE24 => c.p44
633 ,P_ATTRIBUTE25 => c.p45
634 ,P_ATTRIBUTE26 => c.p46
635 ,P_ATTRIBUTE27 => c.p47
636 ,P_ATTRIBUTE28 => c.p48
637 ,P_ATTRIBUTE29 => c.p49
638 ,P_ATTRIBUTE30 => c.p50
639 ,P_COUNTRY_OF_BIRTH => c.p51
640 ,P_BACKGROUND_CHECK_STATUS => c.p52
641 ,P_BACKGROUND_DATE_CHECK => c.p53
642 ,P_CORRESPONDENCE_LANGUAGE => L_CORRESPONDENCE_LANGUAGE
643 ,P_FTE_CAPACITY => c.p54
644 ,P_HOLD_APPLICANT_DATE_UNTIL => c.p55
645 ,P_HONORS => c.p56
646 ,P_MAILSTOP => c.p57
647 ,P_OFFICE_NUMBER => c.p58
648 ,P_ON_MILITARY_SERVICE => c.p59
649 ,P_PRE_NAME_ADJUNCT => c.p60
650 ,P_PROJECTED_START_DATE => c.p61
651 ,P_RESUME_EXISTS => c.p62
652 ,P_RESUME_LAST_UPDATED => c.p63
653 ,P_STUDENT_STATUS => c.p64
654 ,P_WORK_SCHEDULE => c.p65
655 ,P_SUFFIX => c.p66
656 ,P_DATE_OF_DEATH => c.p67
657 ,P_BENEFIT_GROUP_ID => L_BENEFIT_GROUP_ID
658 ,P_RECEIPT_OF_DEATH_CERT_DATE => c.p68
659 ,P_COORD_BEN_MED_PLN_NO => c.p69
660 ,P_COORD_BEN_NO_CVG_FLAG => c.p70
661 ,P_USES_TOBACCO_FLAG => c.p71
662 ,P_DPDNT_ADOPTION_DATE => c.p72
663 ,P_DPDNT_VLNTRY_SVCE_FLAG => c.p73
664 ,P_ORIGINAL_DATE_OF_HIRE => c.p74
665 ,P_PERSON_ID => L_PERSON_ID
666 ,P_ASSIGNMENT_ID => L_ASSIGNMENT_ID
667 ,P_APPLICATION_ID => L_APPLICATION_ID
668 ,P_PER_OBJECT_VERSION_NUMBER => c.p78
669 ,P_ASG_OBJECT_VERSION_NUMBER => c.p79
670 ,P_APL_OBJECT_VERSION_NUMBER => c.p80
671 ,P_PER_EFFECTIVE_START_DATE => c.p81
672 ,P_PER_EFFECTIVE_END_DATE => c.p82
673 ,P_FULL_NAME => c.p83
674 ,P_PER_COMMENT_ID => c.p84
675 ,P_ASSIGNMENT_SEQUENCE => c.p85
676 ,P_NAME_COMBINATION_WARNING => L_NAME_COMBINATION_WARNING
677 ,P_ORIG_HIRE_WARNING => L_ORIG_HIRE_WARNING);
678 hr_data_pump.api_trc_off;
679 --
680 iuk(p_batch_line_id,c.p75,L_PERSON_ID);
681 --
682 iuk(p_batch_line_id,c.p76,L_ASSIGNMENT_ID);
683 --
684 iuk(p_batch_line_id,c.p77,L_APPLICATION_ID);
685 --
686 if L_NAME_COMBINATION_WARNING then
687 c.p86 := 'TRUE';
688 else
689 c.p86 := 'FALSE';
690 end if;
691 --
692 if L_ORIG_HIRE_WARNING then
693 c.p87 := 'TRUE';
694 else
695 c.p87 := 'FALSE';
696 end if;
697 --
698 update hr_pump_batch_lines l set
699 l.pval004 = decode(c.p4,null,cn,c.p4),
700 l.pval075 = decode(c.p75,null,cn,c.p75),
701 l.pval076 = decode(c.p76,null,cn,c.p76),
702 l.pval077 = decode(c.p77,null,cn,c.p77),
703 l.pval078 = decode(c.p78,null,cn,c.p78),
704 l.pval079 = decode(c.p79,null,cn,c.p79),
705 l.pval080 = decode(c.p80,null,cn,c.p80),
706 l.pval081 = decode(c.p81,null,cn,dc(c.p81)),
707 l.pval082 = decode(c.p82,null,cn,dc(c.p82)),
708 l.pval083 = decode(c.p83,null,cn,c.p83),
709 l.pval084 = decode(c.p84,null,cn,c.p84),
710 l.pval085 = decode(c.p85,null,cn,c.p85),
711 l.pval086 = decode(c.p86,null,cn,c.p86),
712 l.pval087 = decode(c.p87,null,cn,c.p87)
713 where l.rowid = c.myrowid;
714 --
715 close cr;
716 --
717 hr_data_pump.exit('call');
718 exception
719  when hr_multi_message.error_message_exist then
720    if cr%isopen then
721     close cr;
722    end if;
723    hr_pump_utils.set_multi_msg_error_flag(true);
724  when others then
725  if cr%isopen then
726   close cr;
727  end if;
728  raise;
729 end call;
730 end hrdpp_CREATE_AU_APPLICANT;