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_SUPPLEMENTARY_ROL

Source


1 package body hrdpp_UPDATE_SUPPLEMENTARY_ROL as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2009/03/30 04:03:11
4  * Generated for API: PER_SUPPLEMENTARY_ROLE_API.UPDATE_SUPPLEMENTARY_ROLE
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_START_DATE in date default null
84 ,I_START_DATE in varchar2 default 'N'
85 ,P_END_DATE in date default null
86 ,I_END_DATE in varchar2 default 'N'
87 ,P_CONFIDENTIAL_DATE in date default null
88 ,I_CONFIDENTIAL_DATE in varchar2 default 'N'
89 ,P_EMP_RIGHTS_FLAG in varchar2 default null
90 ,P_END_OF_RIGHTS_DATE in date default null
91 ,I_END_OF_RIGHTS_DATE in varchar2 default 'N'
92 ,P_PRIMARY_CONTACT_FLAG in varchar2 default null
93 ,P_ATTRIBUTE_CATEGORY in varchar2 default null
94 ,P_ATTRIBUTE1 in varchar2 default null
95 ,P_ATTRIBUTE2 in varchar2 default null
96 ,P_ATTRIBUTE3 in varchar2 default null
97 ,P_ATTRIBUTE4 in varchar2 default null
98 ,P_ATTRIBUTE5 in varchar2 default null
99 ,P_ATTRIBUTE6 in varchar2 default null
100 ,P_ATTRIBUTE7 in varchar2 default null
101 ,P_ATTRIBUTE8 in varchar2 default null
102 ,P_ATTRIBUTE9 in varchar2 default null
103 ,P_ATTRIBUTE10 in varchar2 default null
104 ,P_ATTRIBUTE11 in varchar2 default null
105 ,P_ATTRIBUTE12 in varchar2 default null
106 ,P_ATTRIBUTE13 in varchar2 default null
107 ,P_ATTRIBUTE14 in varchar2 default null
108 ,P_ATTRIBUTE15 in varchar2 default null
109 ,P_ATTRIBUTE16 in varchar2 default null
110 ,P_ATTRIBUTE17 in varchar2 default null
111 ,P_ATTRIBUTE18 in varchar2 default null
112 ,P_ATTRIBUTE19 in varchar2 default null
113 ,P_ATTRIBUTE20 in varchar2 default null
114 ,P_ROLE_INFORMATION_CATEGORY in varchar2 default null
115 ,P_ROLE_INFORMATION1 in varchar2 default null
116 ,P_ROLE_INFORMATION2 in varchar2 default null
117 ,P_ROLE_INFORMATION3 in varchar2 default null
118 ,P_ROLE_INFORMATION4 in varchar2 default null
119 ,P_ROLE_INFORMATION5 in varchar2 default null
120 ,P_ROLE_INFORMATION6 in varchar2 default null
121 ,P_ROLE_INFORMATION7 in varchar2 default null
122 ,P_ROLE_INFORMATION8 in varchar2 default null
123 ,P_ROLE_INFORMATION9 in varchar2 default null
124 ,P_ROLE_INFORMATION10 in varchar2 default null
125 ,P_ROLE_INFORMATION11 in varchar2 default null
126 ,P_ROLE_INFORMATION12 in varchar2 default null
127 ,P_ROLE_INFORMATION13 in varchar2 default null
128 ,P_ROLE_INFORMATION14 in varchar2 default null
129 ,P_ROLE_INFORMATION15 in varchar2 default null
130 ,P_ROLE_INFORMATION16 in varchar2 default null
131 ,P_ROLE_INFORMATION17 in varchar2 default null
132 ,P_ROLE_INFORMATION18 in varchar2 default null
133 ,P_ROLE_INFORMATION19 in varchar2 default null
134 ,P_ROLE_INFORMATION20 in varchar2 default null
135 ,P_OLD_END_DATE in date default null
136 ,I_OLD_END_DATE in varchar2 default 'N'
137 ,P_ROLE_USER_KEY in varchar2) is
138 blid number := p_data_pump_batch_line_id;
139 begin
140 if blid is not null then
141 delete from hr_pump_batch_lines where batch_line_id = blid;
142 delete from hr_pump_batch_exceptions
143 where source_type = 'BATCH_LINE' and source_id = blid;
144 end if;
145 insert into hr_pump_batch_lines
146 (batch_id
147 ,batch_line_id
148 ,business_group_name
149 ,api_module_id
150 ,line_status
151 ,user_sequence
152 ,link_value
153 ,pval001
154 ,pval002
155 ,pval003
156 ,pval004
157 ,pval005
158 ,pval006
159 ,pval007
160 ,pval008
161 ,pval009
162 ,pval010
163 ,pval011
164 ,pval012
165 ,pval013
166 ,pval014
167 ,pval015
168 ,pval016
169 ,pval017
170 ,pval018
171 ,pval019
172 ,pval020
173 ,pval021
174 ,pval022
175 ,pval023
176 ,pval024
177 ,pval025
178 ,pval026
179 ,pval027
180 ,pval028
181 ,pval029
182 ,pval030
183 ,pval031
184 ,pval032
185 ,pval033
186 ,pval034
187 ,pval035
188 ,pval036
189 ,pval037
190 ,pval038
191 ,pval039
192 ,pval040
193 ,pval041
194 ,pval042
195 ,pval043
196 ,pval044
197 ,pval045
198 ,pval046
199 ,pval047
200 ,pval048
201 ,pval049
202 ,pval050
203 ,pval051)
204 values
205 (p_batch_id
206 ,nvl(blid,hr_pump_batch_lines_s.nextval)
207 ,p_data_pump_business_grp_name
208 ,1738
209 ,'U'
210 ,p_user_sequence
211 ,p_link_value
212 ,dc(P_EFFECTIVE_DATE)
213 ,dd(P_START_DATE,I_START_DATE)
214 ,dd(P_END_DATE,I_END_DATE)
215 ,dd(P_CONFIDENTIAL_DATE,I_CONFIDENTIAL_DATE)
216 ,P_EMP_RIGHTS_FLAG
217 ,dd(P_END_OF_RIGHTS_DATE,I_END_OF_RIGHTS_DATE)
218 ,P_PRIMARY_CONTACT_FLAG
219 ,P_ATTRIBUTE_CATEGORY
220 ,P_ATTRIBUTE1
221 ,P_ATTRIBUTE2
222 ,P_ATTRIBUTE3
223 ,P_ATTRIBUTE4
224 ,P_ATTRIBUTE5
225 ,P_ATTRIBUTE6
226 ,P_ATTRIBUTE7
227 ,P_ATTRIBUTE8
228 ,P_ATTRIBUTE9
229 ,P_ATTRIBUTE10
230 ,P_ATTRIBUTE11
231 ,P_ATTRIBUTE12
232 ,P_ATTRIBUTE13
233 ,P_ATTRIBUTE14
234 ,P_ATTRIBUTE15
235 ,P_ATTRIBUTE16
236 ,P_ATTRIBUTE17
237 ,P_ATTRIBUTE18
238 ,P_ATTRIBUTE19
239 ,P_ATTRIBUTE20
240 ,P_ROLE_INFORMATION_CATEGORY
241 ,P_ROLE_INFORMATION1
242 ,P_ROLE_INFORMATION2
243 ,P_ROLE_INFORMATION3
244 ,P_ROLE_INFORMATION4
245 ,P_ROLE_INFORMATION5
246 ,P_ROLE_INFORMATION6
247 ,P_ROLE_INFORMATION7
248 ,P_ROLE_INFORMATION8
249 ,P_ROLE_INFORMATION9
250 ,P_ROLE_INFORMATION10
251 ,P_ROLE_INFORMATION11
252 ,P_ROLE_INFORMATION12
253 ,P_ROLE_INFORMATION13
254 ,P_ROLE_INFORMATION14
255 ,P_ROLE_INFORMATION15
256 ,P_ROLE_INFORMATION16
257 ,P_ROLE_INFORMATION17
258 ,P_ROLE_INFORMATION18
259 ,P_ROLE_INFORMATION19
260 ,P_ROLE_INFORMATION20
261 ,dd(P_OLD_END_DATE,I_OLD_END_DATE)
262 ,P_ROLE_USER_KEY);
263 end insert_batch_lines;
264 --
265 procedure call
266 (p_business_group_id in number,
267 p_batch_line_id     in number) is
268 cursor cr is
269 select l.rowid myrowid,
270 decode(l.pval001,cn,dn,d(l.pval001)) p1,
271 decode(l.pval002,cn,dn,vn,dh,d(l.pval002)) p2,
272 l.pval002 d2,
273 decode(l.pval003,cn,dn,vn,dh,d(l.pval003)) p3,
274 l.pval003 d3,
275 decode(l.pval004,cn,dn,vn,dh,d(l.pval004)) p4,
276 l.pval004 d4,
277 decode(l.pval005,cn,vn,vn,vh,
278  hr_pump_get.gl(l.pval005,'YES_NO',d(l.pval001),vn)) p5,
279 l.pval005 d5,
280 decode(l.pval006,cn,dn,vn,dh,d(l.pval006)) p6,
281 l.pval006 d6,
282 decode(l.pval007,cn,vn,vn,vh,
283  hr_pump_get.gl(l.pval007,'YES_NO',d(l.pval001),vn)) 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,dn,vn,dh,d(l.pval050)) p50,
370 l.pval050 d50,
371 decode(l.pval051,cn,vn,l.pval051) p51
372 from hr_pump_batch_lines l
373 where l.batch_line_id = p_batch_line_id;
374 --
375 c cr%rowtype;
376 l_validate boolean := false;
377 L_ROLE_ID number;
378 L_OBJECT_VERSION_NUMBER number;
379 --
380 begin
381 hr_data_pump.entry('call');
382 open cr;
383 fetch cr into c;
384 if cr%notfound then
385 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
386 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
387 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
388 hr_utility.set_message_token('VALUE',p_batch_line_id);
389 hr_utility.raise_error;
390 end if;
391 --
392 if c.p51 is null then
393 L_ROLE_ID:=nn;
394 else
395 L_ROLE_ID := 
396 hr_pump_get.get_role_id
397 (P_ROLE_USER_KEY => c.p51);
398 end if;
399 --
400 if c.p51 is null then
401 L_OBJECT_VERSION_NUMBER:=nn;
402 else
403 L_OBJECT_VERSION_NUMBER := 
404 hr_pump_get.GET_ROL_OVN
405 (P_ROLE_USER_KEY => c.p51);
406 end if;
407 --
408 hr_data_pump.api_trc_on;
409 PER_SUPPLEMENTARY_ROLE_API.UPDATE_SUPPLEMENTARY_ROLE
410 (p_validate => l_validate
411 ,P_EFFECTIVE_DATE => c.p1
412 ,P_ROLE_ID => L_ROLE_ID
413 ,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER
414 ,P_START_DATE => c.p2
415 ,P_END_DATE => c.p3
416 ,P_CONFIDENTIAL_DATE => c.p4
417 ,P_EMP_RIGHTS_FLAG => c.p5
418 ,P_END_OF_RIGHTS_DATE => c.p6
419 ,P_PRIMARY_CONTACT_FLAG => c.p7
420 ,P_ATTRIBUTE_CATEGORY => c.p8
421 ,P_ATTRIBUTE1 => c.p9
422 ,P_ATTRIBUTE2 => c.p10
423 ,P_ATTRIBUTE3 => c.p11
424 ,P_ATTRIBUTE4 => c.p12
425 ,P_ATTRIBUTE5 => c.p13
426 ,P_ATTRIBUTE6 => c.p14
427 ,P_ATTRIBUTE7 => c.p15
428 ,P_ATTRIBUTE8 => c.p16
429 ,P_ATTRIBUTE9 => c.p17
430 ,P_ATTRIBUTE10 => c.p18
431 ,P_ATTRIBUTE11 => c.p19
432 ,P_ATTRIBUTE12 => c.p20
433 ,P_ATTRIBUTE13 => c.p21
434 ,P_ATTRIBUTE14 => c.p22
435 ,P_ATTRIBUTE15 => c.p23
436 ,P_ATTRIBUTE16 => c.p24
437 ,P_ATTRIBUTE17 => c.p25
438 ,P_ATTRIBUTE18 => c.p26
439 ,P_ATTRIBUTE19 => c.p27
440 ,P_ATTRIBUTE20 => c.p28
441 ,P_ROLE_INFORMATION_CATEGORY => c.p29
442 ,P_ROLE_INFORMATION1 => c.p30
443 ,P_ROLE_INFORMATION2 => c.p31
444 ,P_ROLE_INFORMATION3 => c.p32
445 ,P_ROLE_INFORMATION4 => c.p33
446 ,P_ROLE_INFORMATION5 => c.p34
447 ,P_ROLE_INFORMATION6 => c.p35
448 ,P_ROLE_INFORMATION7 => c.p36
449 ,P_ROLE_INFORMATION8 => c.p37
450 ,P_ROLE_INFORMATION9 => c.p38
451 ,P_ROLE_INFORMATION10 => c.p39
452 ,P_ROLE_INFORMATION11 => c.p40
453 ,P_ROLE_INFORMATION12 => c.p41
454 ,P_ROLE_INFORMATION13 => c.p42
455 ,P_ROLE_INFORMATION14 => c.p43
456 ,P_ROLE_INFORMATION15 => c.p44
457 ,P_ROLE_INFORMATION16 => c.p45
458 ,P_ROLE_INFORMATION17 => c.p46
459 ,P_ROLE_INFORMATION18 => c.p47
460 ,P_ROLE_INFORMATION19 => c.p48
461 ,P_ROLE_INFORMATION20 => c.p49
462 ,P_OLD_END_DATE => c.p50);
463 hr_data_pump.api_trc_off;
464 
465 --
466 
467 --
468 close cr;
469 --
470 hr_data_pump.exit('call');
471 exception
472  when hr_multi_message.error_message_exist then
473    if cr%isopen then
474     close cr;
475    end if;
476    hr_pump_utils.set_multi_msg_error_flag(true);
477  when others then
478  if cr%isopen then
479   close cr;
480  end if;
481  raise;
482 end call;
483 end hrdpp_UPDATE_SUPPLEMENTARY_ROL;