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_CREATE_IN_CONTACT

Source


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