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_GB_PERSON

Source


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