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_CN_EMPLOYEE

Source


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