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_MX_CONTACT

Source


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