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_PERSON_EXTRA_INFO

Source


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