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_UPDATE_AU_PERSON

Source


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