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_GB_APPLICANT

Source


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