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