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