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_PERSON_EXTRA_INFO

Source


1 package body hrdpp_CREATE_PERSON_EXTRA_INFO as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2009/03/30 04:03:07
4  * Generated for API: HR_PERSON_EXTRA_INFO_API.CREATE_PERSON_EXTRA_INFO
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_INFORMATION_TYPE in varchar2
83 ,P_PEI_ATTRIBUTE_CATEGORY in varchar2 default null
84 ,P_PEI_ATTRIBUTE1 in varchar2 default null
85 ,P_PEI_ATTRIBUTE2 in varchar2 default null
86 ,P_PEI_ATTRIBUTE3 in varchar2 default null
87 ,P_PEI_ATTRIBUTE4 in varchar2 default null
88 ,P_PEI_ATTRIBUTE5 in varchar2 default null
89 ,P_PEI_ATTRIBUTE6 in varchar2 default null
90 ,P_PEI_ATTRIBUTE7 in varchar2 default null
91 ,P_PEI_ATTRIBUTE8 in varchar2 default null
92 ,P_PEI_ATTRIBUTE9 in varchar2 default null
93 ,P_PEI_ATTRIBUTE10 in varchar2 default null
94 ,P_PEI_ATTRIBUTE11 in varchar2 default null
95 ,P_PEI_ATTRIBUTE12 in varchar2 default null
96 ,P_PEI_ATTRIBUTE13 in varchar2 default null
97 ,P_PEI_ATTRIBUTE14 in varchar2 default null
98 ,P_PEI_ATTRIBUTE15 in varchar2 default null
99 ,P_PEI_ATTRIBUTE16 in varchar2 default null
100 ,P_PEI_ATTRIBUTE17 in varchar2 default null
101 ,P_PEI_ATTRIBUTE18 in varchar2 default null
102 ,P_PEI_ATTRIBUTE19 in varchar2 default null
103 ,P_PEI_ATTRIBUTE20 in varchar2 default null
104 ,P_PEI_INFORMATION_CATEGORY in varchar2 default null
105 ,P_PEI_INFORMATION1 in varchar2 default null
106 ,P_PEI_INFORMATION2 in varchar2 default null
107 ,P_PEI_INFORMATION3 in varchar2 default null
108 ,P_PEI_INFORMATION4 in varchar2 default null
109 ,P_PEI_INFORMATION5 in varchar2 default null
110 ,P_PEI_INFORMATION6 in varchar2 default null
111 ,P_PEI_INFORMATION7 in varchar2 default null
112 ,P_PEI_INFORMATION8 in varchar2 default null
113 ,P_PEI_INFORMATION9 in varchar2 default null
114 ,P_PEI_INFORMATION10 in varchar2 default null
115 ,P_PEI_INFORMATION11 in varchar2 default null
116 ,P_PEI_INFORMATION12 in varchar2 default null
117 ,P_PEI_INFORMATION13 in varchar2 default null
118 ,P_PEI_INFORMATION14 in varchar2 default null
119 ,P_PEI_INFORMATION15 in varchar2 default null
120 ,P_PEI_INFORMATION16 in varchar2 default null
121 ,P_PEI_INFORMATION17 in varchar2 default null
122 ,P_PEI_INFORMATION18 in varchar2 default null
123 ,P_PEI_INFORMATION19 in varchar2 default null
124 ,P_PEI_INFORMATION20 in varchar2 default null
125 ,P_PEI_INFORMATION21 in varchar2 default null
126 ,P_PEI_INFORMATION22 in varchar2 default null
127 ,P_PEI_INFORMATION23 in varchar2 default null
128 ,P_PEI_INFORMATION24 in varchar2 default null
129 ,P_PEI_INFORMATION25 in varchar2 default null
130 ,P_PEI_INFORMATION26 in varchar2 default null
131 ,P_PEI_INFORMATION27 in varchar2 default null
132 ,P_PEI_INFORMATION28 in varchar2 default null
133 ,P_PEI_INFORMATION29 in varchar2 default null
134 ,P_PEI_INFORMATION30 in varchar2 default null
135 ,P_PERSON_EXTRA_INFO_USER_KEY in varchar2
136 ,P_PERSON_USER_KEY in varchar2) is
137 blid number := p_data_pump_batch_line_id;
138 begin
139 if blid is not null then
140 delete from hr_pump_batch_lines where batch_line_id = blid;
141 delete from hr_pump_batch_exceptions
142 where source_type = 'BATCH_LINE' and source_id = blid;
143 end if;
144 insert into hr_pump_batch_lines
145 (batch_id
146 ,batch_line_id
147 ,business_group_name
148 ,api_module_id
149 ,line_status
150 ,user_sequence
151 ,link_value
152 ,pval001
153 ,pval002
154 ,pval003
155 ,pval004
156 ,pval005
157 ,pval006
158 ,pval007
159 ,pval008
160 ,pval009
161 ,pval010
162 ,pval011
163 ,pval012
164 ,pval013
165 ,pval014
166 ,pval015
167 ,pval016
168 ,pval017
169 ,pval018
170 ,pval019
171 ,pval020
172 ,pval021
173 ,pval022
174 ,pval023
175 ,pval024
176 ,pval025
177 ,pval026
178 ,pval027
179 ,pval028
180 ,pval029
181 ,pval030
182 ,pval031
183 ,pval032
184 ,pval033
185 ,pval034
186 ,pval035
187 ,pval036
188 ,pval037
189 ,pval038
190 ,pval039
191 ,pval040
192 ,pval041
193 ,pval042
194 ,pval043
195 ,pval044
196 ,pval045
197 ,pval046
198 ,pval047
199 ,pval048
200 ,pval049
201 ,pval050
202 ,pval051
203 ,pval052
204 ,pval053
205 ,pval054
206 ,pval056)
207 values
208 (p_batch_id
209 ,nvl(blid,hr_pump_batch_lines_s.nextval)
210 ,p_data_pump_business_grp_name
211 ,1226
212 ,'U'
213 ,p_user_sequence
214 ,p_link_value
215 ,P_INFORMATION_TYPE
216 ,P_PEI_ATTRIBUTE_CATEGORY
217 ,P_PEI_ATTRIBUTE1
218 ,P_PEI_ATTRIBUTE2
219 ,P_PEI_ATTRIBUTE3
220 ,P_PEI_ATTRIBUTE4
221 ,P_PEI_ATTRIBUTE5
222 ,P_PEI_ATTRIBUTE6
223 ,P_PEI_ATTRIBUTE7
224 ,P_PEI_ATTRIBUTE8
225 ,P_PEI_ATTRIBUTE9
226 ,P_PEI_ATTRIBUTE10
227 ,P_PEI_ATTRIBUTE11
228 ,P_PEI_ATTRIBUTE12
229 ,P_PEI_ATTRIBUTE13
230 ,P_PEI_ATTRIBUTE14
231 ,P_PEI_ATTRIBUTE15
232 ,P_PEI_ATTRIBUTE16
233 ,P_PEI_ATTRIBUTE17
234 ,P_PEI_ATTRIBUTE18
235 ,P_PEI_ATTRIBUTE19
236 ,P_PEI_ATTRIBUTE20
237 ,P_PEI_INFORMATION_CATEGORY
238 ,P_PEI_INFORMATION1
239 ,P_PEI_INFORMATION2
240 ,P_PEI_INFORMATION3
241 ,P_PEI_INFORMATION4
242 ,P_PEI_INFORMATION5
243 ,P_PEI_INFORMATION6
244 ,P_PEI_INFORMATION7
245 ,P_PEI_INFORMATION8
246 ,P_PEI_INFORMATION9
247 ,P_PEI_INFORMATION10
248 ,P_PEI_INFORMATION11
249 ,P_PEI_INFORMATION12
250 ,P_PEI_INFORMATION13
251 ,P_PEI_INFORMATION14
252 ,P_PEI_INFORMATION15
253 ,P_PEI_INFORMATION16
254 ,P_PEI_INFORMATION17
255 ,P_PEI_INFORMATION18
256 ,P_PEI_INFORMATION19
257 ,P_PEI_INFORMATION20
258 ,P_PEI_INFORMATION21
259 ,P_PEI_INFORMATION22
260 ,P_PEI_INFORMATION23
261 ,P_PEI_INFORMATION24
262 ,P_PEI_INFORMATION25
263 ,P_PEI_INFORMATION26
264 ,P_PEI_INFORMATION27
265 ,P_PEI_INFORMATION28
266 ,P_PEI_INFORMATION29
267 ,P_PEI_INFORMATION30
268 ,P_PERSON_EXTRA_INFO_USER_KEY
269 ,P_PERSON_USER_KEY);
270 end insert_batch_lines;
271 --
272 procedure call
273 (p_business_group_id in number,
274 p_batch_line_id     in number) is
275 cursor cr is
276 select l.rowid myrowid,
277 decode(l.pval001,cn,vn,l.pval001) p1,
278 decode(l.pval002,cn,vn,vn,vn,l.pval002) p2,
279 l.pval002 d2,
280 decode(l.pval003,cn,vn,vn,vn,l.pval003) p3,
281 l.pval003 d3,
282 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
283 l.pval004 d4,
284 decode(l.pval005,cn,vn,vn,vn,l.pval005) p5,
285 l.pval005 d5,
286 decode(l.pval006,cn,vn,vn,vn,l.pval006) p6,
287 l.pval006 d6,
288 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
289 l.pval007 d7,
290 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
291 l.pval008 d8,
292 decode(l.pval009,cn,vn,vn,vn,l.pval009) p9,
293 l.pval009 d9,
294 decode(l.pval010,cn,vn,vn,vn,l.pval010) p10,
295 l.pval010 d10,
296 decode(l.pval011,cn,vn,vn,vn,l.pval011) p11,
297 l.pval011 d11,
298 decode(l.pval012,cn,vn,vn,vn,l.pval012) p12,
299 l.pval012 d12,
300 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
301 l.pval013 d13,
302 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
303 l.pval014 d14,
304 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
305 l.pval015 d15,
306 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
307 l.pval016 d16,
308 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
309 l.pval017 d17,
310 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
311 l.pval018 d18,
312 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
313 l.pval019 d19,
314 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
315 l.pval020 d20,
316 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
317 l.pval021 d21,
318 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
319 l.pval022 d22,
320 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
321 l.pval023 d23,
322 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
323 l.pval024 d24,
324 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
325 l.pval025 d25,
326 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
327 l.pval026 d26,
328 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
329 l.pval027 d27,
330 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
331 l.pval028 d28,
332 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
333 l.pval029 d29,
334 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
335 l.pval030 d30,
336 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
337 l.pval031 d31,
338 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
339 l.pval032 d32,
340 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
341 l.pval033 d33,
342 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
343 l.pval034 d34,
344 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
345 l.pval035 d35,
346 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
347 l.pval036 d36,
348 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
349 l.pval037 d37,
350 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
351 l.pval038 d38,
352 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
353 l.pval039 d39,
354 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
355 l.pval040 d40,
356 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
357 l.pval041 d41,
358 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
359 l.pval042 d42,
360 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
361 l.pval043 d43,
362 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
363 l.pval044 d44,
364 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
365 l.pval045 d45,
366 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
367 l.pval046 d46,
368 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
369 l.pval047 d47,
370 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
371 l.pval048 d48,
372 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
373 l.pval049 d49,
374 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
375 l.pval050 d50,
376 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
377 l.pval051 d51,
378 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
379 l.pval052 d52,
380 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
381 l.pval053 d53,
382 l.pval054 p54,
383 l.pval055 p55,
384 decode(l.pval056,cn,vn,l.pval056) p56
385 from hr_pump_batch_lines l
386 where l.batch_line_id = p_batch_line_id;
387 --
388 c cr%rowtype;
389 l_validate boolean := false;
390 L_PERSON_EXTRA_INFO_ID number;
391 L_PERSON_ID number;
392 --
393 begin
394 hr_data_pump.entry('call');
395 open cr;
396 fetch cr into c;
397 if cr%notfound then
398 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
399 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
400 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
401 hr_utility.set_message_token('VALUE',p_batch_line_id);
402 hr_utility.raise_error;
403 end if;
404 --
405 if c.p56 is null then
406 L_PERSON_ID:=nn;
407 else
408 L_PERSON_ID := 
409 hr_pump_get.get_person_id
410 (P_PERSON_USER_KEY => c.p56);
411 end if;
412 --
413 hr_data_pump.api_trc_on;
414 HR_PERSON_EXTRA_INFO_API.CREATE_PERSON_EXTRA_INFO
415 (p_validate => l_validate
416 ,P_PERSON_ID => L_PERSON_ID
417 ,P_INFORMATION_TYPE => c.p1
418 ,P_PEI_ATTRIBUTE_CATEGORY => c.p2
419 ,P_PEI_ATTRIBUTE1 => c.p3
420 ,P_PEI_ATTRIBUTE2 => c.p4
421 ,P_PEI_ATTRIBUTE3 => c.p5
422 ,P_PEI_ATTRIBUTE4 => c.p6
423 ,P_PEI_ATTRIBUTE5 => c.p7
424 ,P_PEI_ATTRIBUTE6 => c.p8
425 ,P_PEI_ATTRIBUTE7 => c.p9
426 ,P_PEI_ATTRIBUTE8 => c.p10
427 ,P_PEI_ATTRIBUTE9 => c.p11
428 ,P_PEI_ATTRIBUTE10 => c.p12
429 ,P_PEI_ATTRIBUTE11 => c.p13
430 ,P_PEI_ATTRIBUTE12 => c.p14
431 ,P_PEI_ATTRIBUTE13 => c.p15
432 ,P_PEI_ATTRIBUTE14 => c.p16
433 ,P_PEI_ATTRIBUTE15 => c.p17
434 ,P_PEI_ATTRIBUTE16 => c.p18
435 ,P_PEI_ATTRIBUTE17 => c.p19
436 ,P_PEI_ATTRIBUTE18 => c.p20
437 ,P_PEI_ATTRIBUTE19 => c.p21
438 ,P_PEI_ATTRIBUTE20 => c.p22
439 ,P_PEI_INFORMATION_CATEGORY => c.p23
440 ,P_PEI_INFORMATION1 => c.p24
441 ,P_PEI_INFORMATION2 => c.p25
442 ,P_PEI_INFORMATION3 => c.p26
443 ,P_PEI_INFORMATION4 => c.p27
444 ,P_PEI_INFORMATION5 => c.p28
445 ,P_PEI_INFORMATION6 => c.p29
446 ,P_PEI_INFORMATION7 => c.p30
447 ,P_PEI_INFORMATION8 => c.p31
448 ,P_PEI_INFORMATION9 => c.p32
449 ,P_PEI_INFORMATION10 => c.p33
450 ,P_PEI_INFORMATION11 => c.p34
451 ,P_PEI_INFORMATION12 => c.p35
452 ,P_PEI_INFORMATION13 => c.p36
453 ,P_PEI_INFORMATION14 => c.p37
454 ,P_PEI_INFORMATION15 => c.p38
455 ,P_PEI_INFORMATION16 => c.p39
456 ,P_PEI_INFORMATION17 => c.p40
457 ,P_PEI_INFORMATION18 => c.p41
458 ,P_PEI_INFORMATION19 => c.p42
459 ,P_PEI_INFORMATION20 => c.p43
460 ,P_PEI_INFORMATION21 => c.p44
461 ,P_PEI_INFORMATION22 => c.p45
462 ,P_PEI_INFORMATION23 => c.p46
463 ,P_PEI_INFORMATION24 => c.p47
464 ,P_PEI_INFORMATION25 => c.p48
465 ,P_PEI_INFORMATION26 => c.p49
466 ,P_PEI_INFORMATION27 => c.p50
467 ,P_PEI_INFORMATION28 => c.p51
468 ,P_PEI_INFORMATION29 => c.p52
469 ,P_PEI_INFORMATION30 => c.p53
470 ,P_PERSON_EXTRA_INFO_ID => L_PERSON_EXTRA_INFO_ID
471 ,P_OBJECT_VERSION_NUMBER => c.p55);
472 hr_data_pump.api_trc_off;
473 --
474 iuk(p_batch_line_id,c.p54,L_PERSON_EXTRA_INFO_ID);
475 --
476 update hr_pump_batch_lines l set
477 l.pval054 = decode(c.p54,null,cn,c.p54),
478 l.pval055 = decode(c.p55,null,cn,c.p55)
479 where l.rowid = c.myrowid;
480 --
481 close cr;
482 --
483 hr_data_pump.exit('call');
484 exception
485  when hr_multi_message.error_message_exist then
486    if cr%isopen then
487     close cr;
488    end if;
489    hr_pump_utils.set_multi_msg_error_flag(true);
490  when others then
491  if cr%isopen then
492   close cr;
493  end if;
494  raise;
495 end call;
496 end hrdpp_CREATE_PERSON_EXTRA_INFO;