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_APPLICANT

Source


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