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_MX_PERSON

Source


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