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_US_APPLICANT

Source


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