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_US_PERSON

Source


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