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