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_MX_EMPLOYEE

Source


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