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_APPLICANT

Source


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