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_IN_PERSON

Source


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