[Home] [Help]
PACKAGE BODY: APPS.HRDPP_CREATE_GB_EMPLOYEE
Source
1 package body hrdpp_create_gb_employee as
2 /*
3 * Generated by hr_pump_meta_mapper at: 2007/01/04 02:01:16
4 * Generated for API: hr_employee_api.create_gb_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_NI_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_DIRECTOR in varchar2 default null
134 ,P_PENSIONER in varchar2 default null
135 ,P_WORK_PERMIT_NUMBER in varchar2 default null
136 ,P_ADDL_PENSION_YEARS in varchar2 default null
137 ,P_ADDL_PENSION_MONTHS in varchar2 default null
138 ,P_ADDL_PENSION_DAYS in varchar2 default null
139 ,P_NI_MULTIPLE_ASG 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 ,1372
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
325 ,P_EXPENSE_CHECK_SEND_TO_ADDRES
326 ,P_FIRST_NAME
327 ,P_KNOWN_AS
328 ,P_MARITAL_STATUS
329 ,P_MIDDLE_NAMES
330 ,P_NATIONALITY
331 ,P_NI_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_DIRECTOR
369 ,P_PENSIONER
370 ,P_WORK_PERMIT_NUMBER
371 ,P_ADDL_PENSION_YEARS
372 ,P_ADDL_PENSION_MONTHS
373 ,P_ADDL_PENSION_DAYS
374 ,P_NI_MULTIPLE_ASG
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
429 select l.rowid myrowid,
426 (p_business_group_id in number,
427 p_batch_line_id in number) is
428 cursor cr is
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,
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,
507 l.pval038 d38,
508 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
512 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
509 l.pval039 d39,
510 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
511 l.pval040 d40,
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,'ETH_TYPE',dn,l.pval114)) p51,
534 l.pval051 d51,
535 decode(l.pval052,cn,vn,vn,'N',
536 hr_pump_get.gl(l.pval052,'YES_NO',dn,l.pval114)) p52,
537 l.pval052 d52,
538 decode(l.pval053,cn,vn,vn,'N',
539 hr_pump_get.gl(l.pval053,'YES_NO',dn,l.pval114)) p53,
540 l.pval053 d53,
541 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
542 l.pval054 d54,
543 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
544 l.pval055 d55,
545 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
546 l.pval056 d56,
547 decode(l.pval057,cn,vn,vn,vn,l.pval057) p57,
548 l.pval057 d57,
549 decode(l.pval058,cn,vn,vn,'N',
550 hr_pump_get.gl(l.pval058,'YES_NO',dn,l.pval114)) p58,
551 l.pval058 d58,
552 decode(l.pval059,cn,dn,vn,dn,d(l.pval059)) p59,
553 l.pval059 d59,
554 decode(l.pval060,cn,vn,vn,vn,
555 hr_pump_get.gl(l.pval060,'YES_NO',dn,l.pval114)) p60,
556 l.pval060 d60,
557 decode(l.pval061,cn,dn,vn,dn,d(l.pval061)) p61,
558 l.pval061 d61,
559 decode(l.pval062,cn,vn,vn,vn,
560 hr_pump_get.gl(l.pval062,'BLOOD_TYPE',dn,l.pval114)) p62,
561 l.pval062 d62,
562 decode(l.pval063,cn,vn,vn,vn,l.pval063) p63,
563 l.pval063 d63,
564 decode(l.pval064,cn,nn,vn,nn,n(l.pval064)) p64,
565 l.pval064 d64,
566 decode(l.pval065,cn,vn,vn,vn,l.pval065) p65,
567 l.pval065 d65,
568 decode(l.pval066,cn,vn,vn,vn,l.pval066) p66,
569 l.pval066 d66,
570 decode(l.pval067,cn,vn,vn,vn,l.pval067) p67,
571 l.pval067 d67,
572 decode(l.pval068,cn,dn,vn,dn,d(l.pval068)) p68,
573 l.pval068 d68,
574 decode(l.pval069,cn,vn,vn,vn,l.pval069) p69,
575 l.pval069 d69,
576 decode(l.pval070,cn,vn,vn,vn,l.pval070) p70,
577 l.pval070 d70,
578 decode(l.pval071,cn,vn,vn,vn,
579 hr_pump_get.gl(l.pval071,'YES_NO',dn,l.pval114)) p71,
580 l.pval071 d71,
581 decode(l.pval072,cn,vn,vn,vn,l.pval072) p72,
582 l.pval072 d72,
583 decode(l.pval073,cn,vn,vn,vn,l.pval073) p73,
584 l.pval073 d73,
585 decode(l.pval074,cn,dn,vn,dn,d(l.pval074)) p74,
586 l.pval074 d74,
587 decode(l.pval075,cn,vn,vn,vn,
588 hr_pump_get.gl(l.pval075,'YES_NO',dn,l.pval114)) p75,
589 l.pval075 d75,
590 decode(l.pval076,cn,dn,vn,dn,d(l.pval076)) p76,
591 l.pval076 d76,
592 decode(l.pval077,cn,vn,vn,vn,
596 hr_pump_get.gl(l.pval078,'STUDENT_STATUS',dn,l.pval114)) p78,
593 hr_pump_get.gl(l.pval077,'YES_NO',dn,l.pval114)) p77,
594 l.pval077 d77,
595 decode(l.pval078,cn,vn,vn,vn,
597 l.pval078 d78,
598 decode(l.pval079,cn,vn,vn,vn,
599 hr_pump_get.gl(l.pval079,'WORK_SCHEDULE',dn,l.pval114)) p79,
600 l.pval079 d79,
601 decode(l.pval080,cn,vn,vn,vn,l.pval080) p80,
602 l.pval080 d80,
603 decode(l.pval081,cn,dn,vn,dn,d(l.pval081)) p81,
604 l.pval081 d81,
605 decode(l.pval082,cn,vn,vn,vn,l.pval082) p82,
606 l.pval082 d82,
607 decode(l.pval083,cn,vn,vn,'N',
608 hr_pump_get.gl(l.pval083,'YES_NO',dn,l.pval114)) p83,
609 l.pval083 d83,
610 decode(l.pval084,cn,vn,vn,vn,l.pval084) p84,
611 l.pval084 d84,
612 decode(l.pval085,cn,vn,vn,vn,l.pval085) p85,
613 l.pval085 d85,
614 decode(l.pval086,cn,vn,vn,vn,l.pval086) p86,
615 l.pval086 d86,
616 decode(l.pval087,cn,vn,vn,vn,l.pval087) p87,
617 l.pval087 d87,
618 decode(l.pval088,cn,dn,vn,dn,d(l.pval088)) p88,
619 l.pval088 d88,
620 decode(l.pval089,cn,dn,vn,dn,d(l.pval089)) p89,
621 l.pval089 d89,
622 decode(l.pval090,cn,vn,vn,vn,
623 hr_pump_get.gl(l.pval090,'TOBACCO_USER',dn,l.pval114)) p90,
624 l.pval090 d90,
625 decode(l.pval091,cn,dn,vn,dn,d(l.pval091)) p91,
626 l.pval091 d91,
627 decode(l.pval092,cn,vn,vn,'N',
628 hr_pump_get.gl(l.pval092,'YES_NO',dn,l.pval114)) p92,
629 l.pval092 d92,
630 decode(l.pval093,cn,dn,vn,dn,d(l.pval093)) p93,
631 l.pval093 d93,
632 decode(l.pval094,cn,dn,vn,dn,d(l.pval094)) p94,
633 l.pval094 d94,
634 decode(l.pval095,cn,vn,vn,vn,l.pval095) p95,
635 l.pval095 d95,
636 decode(l.pval096,cn,vn,vn,vn,l.pval096) p96,
637 l.pval096 d96,
638 decode(l.pval097,cn,vn,vn,vn,l.pval097) p97,
639 l.pval097 d97,
640 decode(l.pval098,cn,vn,vn,vn,l.pval098) p98,
641 l.pval098 d98,
642 decode(l.pval099,cn,nn,vn,nn,n(l.pval099)) p99,
643 l.pval099 d99,
644 l.pval100 p100,
645 l.pval101 p101,
646 l.pval102 p102,
647 l.pval103 p103,
648 decode(l.pval104,cn,dn,d(l.pval104)) p104,
649 decode(l.pval105,cn,dn,d(l.pval105)) p105,
650 l.pval106 p106,
651 l.pval107 p107,
652 l.pval108 p108,
653 l.pval109 p109,
654 l.pval110 p110,
655 l.pval111 p111,
656 l.pval112 p112,
657 decode(l.pval113,cn,vn,vn,vn,l.pval113) p113,
658 l.pval113 d113,
659 decode(l.pval114,cn,vn,vn,vn,l.pval114) p114,
660 l.pval114 d114,
661 decode(l.pval115,cn,vn,vn,vn,l.pval115) p115,
662 l.pval115 d115,
663 decode(l.pval116,cn,vn,vn,vn,l.pval116) p116,
664 l.pval116 d116,
665 decode(l.pval117,cn,vn,vn,vn,l.pval117) p117,
666 l.pval117 d117
667 from hr_pump_batch_lines l
668 where l.batch_line_id = p_batch_line_id;
669 --
670 c cr%rowtype;
671 l_validate boolean := false;
672 L_PERSON_ID number;
673 L_ASSIGNMENT_ID number;
674 L_NAME_COMBINATION_WARNING boolean;
675 L_ASSIGN_PAYROLL_WARNING boolean;
676 L_ORIG_HIRE_WARNING boolean;
677 L_PERSON_TYPE_ID number;
678 L_VENDOR_ID number;
679 L_CORRESPONDENCE_LANGUAGE varchar2(2000);
680 L_BENEFIT_GROUP_ID number;
681 --
682 begin
683 hr_data_pump.entry('call');
684 open cr;
685 fetch cr into c;
686 if cr%notfound then
687 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
688 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
689 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
690 hr_utility.set_message_token('VALUE',p_batch_line_id);
691 hr_utility.raise_error;
692 end if;
693 --
694 if c.p113 is null or
695 c.p114 is null then
696 L_PERSON_TYPE_ID:=nn;
697 else
698 L_PERSON_TYPE_ID :=
699 hr_pump_get.get_person_type_id
700 (P_USER_PERSON_TYPE => c.p113
701 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
702 ,P_LANGUAGE_CODE => c.p114);
703 end if;
704 --
705 if c.p115 is null then
706 L_VENDOR_ID:=nn;
707 else
708 L_VENDOR_ID :=
709 hr_pump_get.get_vendor_id
710 (P_VENDOR_NAME => c.p115);
711 end if;
712 --
713 if c.p116 is null then
714 L_CORRESPONDENCE_LANGUAGE:=vn;
715 else
716 L_CORRESPONDENCE_LANGUAGE :=
717 hr_pump_get.GET_CORRESPONDENCE_LANGUAGE
718 (P_CORRESPONDENCE_LANGUAGE => c.p116);
719 end if;
720 --
721 if c.p117 is null then
722 L_BENEFIT_GROUP_ID:=nn;
723 else
724 L_BENEFIT_GROUP_ID :=
725 hr_pump_get.get_benefit_group_id
726 (P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
727 ,P_BENEFIT_GROUP => c.p117);
728 end if;
729 --
730 hr_data_pump.api_trc_on;
731 hr_employee_api.create_gb_employee
732 (p_validate => l_validate
733 ,P_HIRE_DATE => c.p1
734 ,p_business_group_id => p_business_group_id
735 ,P_LAST_NAME => c.p2
736 ,P_SEX => c.p3
737 ,P_PERSON_TYPE_ID => L_PERSON_TYPE_ID
738 ,P_COMMENTS => c.p4
739 ,P_DATE_EMPLOYEE_DATA_VERIFIED => c.p5
740 ,P_DATE_OF_BIRTH => c.p6
741 ,P_EMAIL_ADDRESS => c.p7
742 ,P_EMPLOYEE_NUMBER => c.p8
743 ,P_EXPENSE_CHECK_SEND_TO_ADDRES => c.p9
744 ,P_FIRST_NAME => c.p10
745 ,P_KNOWN_AS => c.p11
746 ,P_MARITAL_STATUS => c.p12
747 ,P_MIDDLE_NAMES => c.p13
748 ,P_NATIONALITY => c.p14
749 ,P_NI_NUMBER => c.p15
750 ,P_PREVIOUS_LAST_NAME => c.p16
751 ,P_REGISTERED_DISABLED_FLAG => c.p17
752 ,P_TITLE => c.p18
753 ,P_VENDOR_ID => L_VENDOR_ID
754 ,P_WORK_TELEPHONE => c.p19
755 ,P_ATTRIBUTE_CATEGORY => c.p20
756 ,P_ATTRIBUTE1 => c.p21
757 ,P_ATTRIBUTE2 => c.p22
758 ,P_ATTRIBUTE3 => c.p23
759 ,P_ATTRIBUTE4 => c.p24
760 ,P_ATTRIBUTE5 => c.p25
761 ,P_ATTRIBUTE6 => c.p26
762 ,P_ATTRIBUTE7 => c.p27
763 ,P_ATTRIBUTE8 => c.p28
764 ,P_ATTRIBUTE9 => c.p29
765 ,P_ATTRIBUTE10 => c.p30
766 ,P_ATTRIBUTE11 => c.p31
767 ,P_ATTRIBUTE12 => c.p32
771 ,P_ATTRIBUTE16 => c.p36
768 ,P_ATTRIBUTE13 => c.p33
769 ,P_ATTRIBUTE14 => c.p34
770 ,P_ATTRIBUTE15 => c.p35
772 ,P_ATTRIBUTE17 => c.p37
773 ,P_ATTRIBUTE18 => c.p38
774 ,P_ATTRIBUTE19 => c.p39
775 ,P_ATTRIBUTE20 => c.p40
776 ,P_ATTRIBUTE21 => c.p41
777 ,P_ATTRIBUTE22 => c.p42
778 ,P_ATTRIBUTE23 => c.p43
779 ,P_ATTRIBUTE24 => c.p44
780 ,P_ATTRIBUTE25 => c.p45
781 ,P_ATTRIBUTE26 => c.p46
782 ,P_ATTRIBUTE27 => c.p47
783 ,P_ATTRIBUTE28 => c.p48
784 ,P_ATTRIBUTE29 => c.p49
785 ,P_ATTRIBUTE30 => c.p50
786 ,P_ETHNIC_ORIGIN => c.p51
787 ,P_DIRECTOR => c.p52
788 ,P_PENSIONER => c.p53
789 ,P_WORK_PERMIT_NUMBER => c.p54
790 ,P_ADDL_PENSION_YEARS => c.p55
791 ,P_ADDL_PENSION_MONTHS => c.p56
792 ,P_ADDL_PENSION_DAYS => c.p57
793 ,P_NI_MULTIPLE_ASG => c.p58
794 ,P_DATE_OF_DEATH => c.p59
795 ,P_BACKGROUND_CHECK_STATUS => c.p60
796 ,P_BACKGROUND_DATE_CHECK => c.p61
797 ,P_BLOOD_TYPE => c.p62
798 ,P_CORRESPONDENCE_LANGUAGE => L_CORRESPONDENCE_LANGUAGE
799 ,P_FAST_PATH_EMPLOYEE => c.p63
800 ,P_FTE_CAPACITY => c.p64
801 ,P_HONORS => c.p65
802 ,P_INTERNAL_LOCATION => c.p66
803 ,P_LAST_MEDICAL_TEST_BY => c.p67
804 ,P_LAST_MEDICAL_TEST_DATE => c.p68
805 ,P_MAILSTOP => c.p69
806 ,P_OFFICE_NUMBER => c.p70
807 ,P_ON_MILITARY_SERVICE => c.p71
808 ,P_PRE_NAME_ADJUNCT => c.p72
809 ,P_REHIRE_RECOMMENDATION => c.p73
810 ,P_PROJECTED_START_DATE => c.p74
811 ,P_RESUME_EXISTS => c.p75
812 ,P_RESUME_LAST_UPDATED => c.p76
813 ,P_SECOND_PASSPORT_EXISTS => c.p77
814 ,P_STUDENT_STATUS => c.p78
815 ,P_WORK_SCHEDULE => c.p79
816 ,P_SUFFIX => c.p80
817 ,P_BENEFIT_GROUP_ID => L_BENEFIT_GROUP_ID
818 ,P_RECEIPT_OF_DEATH_CERT_DATE => c.p81
819 ,P_COORD_BEN_MED_PLN_NO => c.p82
820 ,P_COORD_BEN_NO_CVG_FLAG => c.p83
821 ,P_COORD_BEN_MED_EXT_ER => c.p84
822 ,P_COORD_BEN_MED_PL_NAME => c.p85
823 ,P_COORD_BEN_MED_INSR_CRR_NAME => c.p86
824 ,P_COORD_BEN_MED_INSR_CRR_IDENT => c.p87
825 ,P_COORD_BEN_MED_CVG_STRT_DT => c.p88
826 ,P_COORD_BEN_MED_CVG_END_DT => c.p89
827 ,P_USES_TOBACCO_FLAG => c.p90
828 ,P_DPDNT_ADOPTION_DATE => c.p91
829 ,P_DPDNT_VLNTRY_SVCE_FLAG => c.p92
830 ,P_ORIGINAL_DATE_OF_HIRE => c.p93
831 ,P_ADJUSTED_SVC_DATE => c.p94
832 ,P_TOWN_OF_BIRTH => c.p95
833 ,P_REGION_OF_BIRTH => c.p96
834 ,P_COUNTRY_OF_BIRTH => c.p97
835 ,P_GLOBAL_PERSON_ID => c.p98
836 ,P_PARTY_ID => c.p99
837 ,P_PERSON_ID => L_PERSON_ID
838 ,P_ASSIGNMENT_ID => L_ASSIGNMENT_ID
839 ,P_PER_OBJECT_VERSION_NUMBER => c.p102
840 ,P_ASG_OBJECT_VERSION_NUMBER => c.p103
841 ,P_PER_EFFECTIVE_START_DATE => c.p104
842 ,P_PER_EFFECTIVE_END_DATE => c.p105
843 ,P_FULL_NAME => c.p106
844 ,P_PER_COMMENT_ID => c.p107
845 ,P_ASSIGNMENT_SEQUENCE => c.p108
846 ,P_ASSIGNMENT_NUMBER => c.p109
847 ,P_NAME_COMBINATION_WARNING => L_NAME_COMBINATION_WARNING
848 ,P_ASSIGN_PAYROLL_WARNING => L_ASSIGN_PAYROLL_WARNING
849 ,P_ORIG_HIRE_WARNING => L_ORIG_HIRE_WARNING);
850 hr_data_pump.api_trc_off;
851 --
852 iuk(p_batch_line_id,c.p100,L_PERSON_ID);
853 --
854 iuk(p_batch_line_id,c.p101,L_ASSIGNMENT_ID);
855 --
856 if L_NAME_COMBINATION_WARNING then
857 c.p110 := 'TRUE';
858 else
862 if L_ASSIGN_PAYROLL_WARNING then
859 c.p110 := 'FALSE';
860 end if;
861 --
863 c.p111 := 'TRUE';
864 else
865 c.p111 := 'FALSE';
866 end if;
867 --
868 if L_ORIG_HIRE_WARNING then
869 c.p112 := 'TRUE';
870 else
871 c.p112 := 'FALSE';
872 end if;
873 --
874 update hr_pump_batch_lines l set
875 l.pval008 = decode(c.p8,null,cn,c.p8),
876 l.pval100 = decode(c.p100,null,cn,c.p100),
877 l.pval101 = decode(c.p101,null,cn,c.p101),
878 l.pval102 = decode(c.p102,null,cn,c.p102),
879 l.pval103 = decode(c.p103,null,cn,c.p103),
880 l.pval104 = decode(c.p104,null,cn,dc(c.p104)),
881 l.pval105 = decode(c.p105,null,cn,dc(c.p105)),
882 l.pval106 = decode(c.p106,null,cn,c.p106),
883 l.pval107 = decode(c.p107,null,cn,c.p107),
884 l.pval108 = decode(c.p108,null,cn,c.p108),
885 l.pval109 = decode(c.p109,null,cn,c.p109),
886 l.pval110 = decode(c.p110,null,cn,c.p110),
887 l.pval111 = decode(c.p111,null,cn,c.p111),
888 l.pval112 = decode(c.p112,null,cn,c.p112)
889 where l.rowid = c.myrowid;
890 --
891 close cr;
892 --
893 hr_data_pump.exit('call');
894 exception
895 when hr_multi_message.error_message_exist then
896 if cr%isopen then
897 close cr;
898 end if;
899 hr_pump_utils.set_multi_msg_error_flag(true);
900 when others then
901 if cr%isopen then
902 close cr;
903 end if;
904 raise;
905 end call;
906 end hrdpp_create_gb_employee;