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_EMPLOYEE

Source


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