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_SUPPLEMENTARY_ROL

Source


1 package body hrdpp_CREATE_SUPPLEMENTARY_ROL as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2009/03/30 04:03:08
4  * Generated for API: PER_SUPPLEMENTARY_ROLE_API.CREATE_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
84 ,P_END_DATE in date default null
85 ,P_CONFIDENTIAL_DATE in date default null
86 ,P_EMP_RIGHTS_FLAG in varchar2 default null
87 ,P_END_OF_RIGHTS_DATE in date default null
88 ,P_PRIMARY_CONTACT_FLAG in varchar2 default null
89 ,P_ATTRIBUTE_CATEGORY in varchar2 default null
90 ,P_ATTRIBUTE1 in varchar2 default null
91 ,P_ATTRIBUTE2 in varchar2 default null
92 ,P_ATTRIBUTE3 in varchar2 default null
93 ,P_ATTRIBUTE4 in varchar2 default null
94 ,P_ATTRIBUTE5 in varchar2 default null
95 ,P_ATTRIBUTE6 in varchar2 default null
96 ,P_ATTRIBUTE7 in varchar2 default null
97 ,P_ATTRIBUTE8 in varchar2 default null
98 ,P_ATTRIBUTE9 in varchar2 default null
99 ,P_ATTRIBUTE10 in varchar2 default null
100 ,P_ATTRIBUTE11 in varchar2 default null
101 ,P_ATTRIBUTE12 in varchar2 default null
102 ,P_ATTRIBUTE13 in varchar2 default null
103 ,P_ATTRIBUTE14 in varchar2 default null
104 ,P_ATTRIBUTE15 in varchar2 default null
105 ,P_ATTRIBUTE16 in varchar2 default null
106 ,P_ATTRIBUTE17 in varchar2 default null
107 ,P_ATTRIBUTE18 in varchar2 default null
108 ,P_ATTRIBUTE19 in varchar2 default null
109 ,P_ATTRIBUTE20 in varchar2 default null
110 ,P_ROLE_INFORMATION_CATEGORY in varchar2 default null
111 ,P_ROLE_INFORMATION1 in varchar2 default null
112 ,P_ROLE_INFORMATION2 in varchar2 default null
113 ,P_ROLE_INFORMATION3 in varchar2 default null
114 ,P_ROLE_INFORMATION4 in varchar2 default null
115 ,P_ROLE_INFORMATION5 in varchar2 default null
116 ,P_ROLE_INFORMATION6 in varchar2 default null
117 ,P_ROLE_INFORMATION7 in varchar2 default null
118 ,P_ROLE_INFORMATION8 in varchar2 default null
119 ,P_ROLE_INFORMATION9 in varchar2 default null
120 ,P_ROLE_INFORMATION10 in varchar2 default null
121 ,P_ROLE_INFORMATION11 in varchar2 default null
122 ,P_ROLE_INFORMATION12 in varchar2 default null
123 ,P_ROLE_INFORMATION13 in varchar2 default null
124 ,P_ROLE_INFORMATION14 in varchar2 default null
125 ,P_ROLE_INFORMATION15 in varchar2 default null
126 ,P_ROLE_INFORMATION16 in varchar2 default null
127 ,P_ROLE_INFORMATION17 in varchar2 default null
128 ,P_ROLE_INFORMATION18 in varchar2 default null
129 ,P_ROLE_INFORMATION19 in varchar2 default null
130 ,P_ROLE_INFORMATION20 in varchar2 default null
131 ,P_ROLE_USER_KEY in varchar2
132 ,P_JOB_NAME in varchar2
133 ,P_JOB_GROUP_USER_KEY in varchar2
134 ,P_PERSON_USER_KEY in varchar2
135 ,P_ORGANIZATION_NAME in varchar2 default null
136 ,P_LANGUAGE_CODE in varchar2 default null) 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 ,pval052
203 ,pval053
204 ,pval054
205 ,pval055
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 ,1737
212 ,'U'
213 ,p_user_sequence
214 ,p_link_value
215 ,dc(P_EFFECTIVE_DATE)
216 ,dc(P_START_DATE)
217 ,dc(P_END_DATE)
218 ,dc(P_CONFIDENTIAL_DATE)
219 ,P_EMP_RIGHTS_FLAG
220 ,dc(P_END_OF_RIGHTS_DATE)
221 ,P_PRIMARY_CONTACT_FLAG
222 ,P_ATTRIBUTE_CATEGORY
223 ,P_ATTRIBUTE1
224 ,P_ATTRIBUTE2
225 ,P_ATTRIBUTE3
226 ,P_ATTRIBUTE4
227 ,P_ATTRIBUTE5
228 ,P_ATTRIBUTE6
229 ,P_ATTRIBUTE7
230 ,P_ATTRIBUTE8
231 ,P_ATTRIBUTE9
232 ,P_ATTRIBUTE10
233 ,P_ATTRIBUTE11
234 ,P_ATTRIBUTE12
235 ,P_ATTRIBUTE13
236 ,P_ATTRIBUTE14
237 ,P_ATTRIBUTE15
238 ,P_ATTRIBUTE16
239 ,P_ATTRIBUTE17
240 ,P_ATTRIBUTE18
241 ,P_ATTRIBUTE19
242 ,P_ATTRIBUTE20
243 ,P_ROLE_INFORMATION_CATEGORY
244 ,P_ROLE_INFORMATION1
245 ,P_ROLE_INFORMATION2
246 ,P_ROLE_INFORMATION3
247 ,P_ROLE_INFORMATION4
248 ,P_ROLE_INFORMATION5
249 ,P_ROLE_INFORMATION6
250 ,P_ROLE_INFORMATION7
251 ,P_ROLE_INFORMATION8
252 ,P_ROLE_INFORMATION9
253 ,P_ROLE_INFORMATION10
254 ,P_ROLE_INFORMATION11
255 ,P_ROLE_INFORMATION12
256 ,P_ROLE_INFORMATION13
257 ,P_ROLE_INFORMATION14
258 ,P_ROLE_INFORMATION15
259 ,P_ROLE_INFORMATION16
260 ,P_ROLE_INFORMATION17
261 ,P_ROLE_INFORMATION18
262 ,P_ROLE_INFORMATION19
263 ,P_ROLE_INFORMATION20
264 ,P_ROLE_USER_KEY
265 ,P_JOB_NAME
266 ,P_JOB_GROUP_USER_KEY
267 ,P_PERSON_USER_KEY
268 ,P_ORGANIZATION_NAME
269 ,P_LANGUAGE_CODE);
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,dn,d(l.pval001)) p1,
278 decode(l.pval002,cn,dn,d(l.pval002)) p2,
279 decode(l.pval003,cn,dn,vn,dn,d(l.pval003)) p3,
280 l.pval003 d3,
281 decode(l.pval004,cn,dn,vn,dn,d(l.pval004)) p4,
282 l.pval004 d4,
283 decode(l.pval005,cn,vn,vn,vn,
284  hr_pump_get.gl(l.pval005,'YES_NO',d(l.pval001),l.pval056)) p5,
285 l.pval005 d5,
286 decode(l.pval006,cn,dn,vn,dn,d(l.pval006)) p6,
287 l.pval006 d6,
288 decode(l.pval007,cn,vn,vn,vn,
289  hr_pump_get.gl(l.pval007,'YES_NO',d(l.pval001),l.pval056)) p7,
290 l.pval007 d7,
291 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
292 l.pval008 d8,
293 decode(l.pval009,cn,vn,vn,vn,l.pval009) p9,
294 l.pval009 d9,
295 decode(l.pval010,cn,vn,vn,vn,l.pval010) p10,
296 l.pval010 d10,
297 decode(l.pval011,cn,vn,vn,vn,l.pval011) p11,
298 l.pval011 d11,
299 decode(l.pval012,cn,vn,vn,vn,l.pval012) p12,
300 l.pval012 d12,
301 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
302 l.pval013 d13,
303 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
304 l.pval014 d14,
305 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
306 l.pval015 d15,
307 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
308 l.pval016 d16,
309 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
310 l.pval017 d17,
311 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
312 l.pval018 d18,
313 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
314 l.pval019 d19,
315 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
316 l.pval020 d20,
317 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
318 l.pval021 d21,
319 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
320 l.pval022 d22,
321 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
322 l.pval023 d23,
323 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
324 l.pval024 d24,
325 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
326 l.pval025 d25,
327 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
328 l.pval026 d26,
329 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
330 l.pval027 d27,
331 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
332 l.pval028 d28,
333 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
334 l.pval029 d29,
335 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
336 l.pval030 d30,
337 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
338 l.pval031 d31,
339 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
340 l.pval032 d32,
341 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
342 l.pval033 d33,
343 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
344 l.pval034 d34,
345 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
346 l.pval035 d35,
347 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
348 l.pval036 d36,
349 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
350 l.pval037 d37,
351 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
352 l.pval038 d38,
353 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
354 l.pval039 d39,
355 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
356 l.pval040 d40,
357 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
358 l.pval041 d41,
359 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
360 l.pval042 d42,
361 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
362 l.pval043 d43,
363 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
364 l.pval044 d44,
365 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
366 l.pval045 d45,
367 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
368 l.pval046 d46,
369 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
370 l.pval047 d47,
371 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
372 l.pval048 d48,
373 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
374 l.pval049 d49,
375 l.pval050 p50,
376 l.pval051 p51,
377 decode(l.pval052,cn,vn,l.pval052) p52,
378 decode(l.pval053,cn,vn,l.pval053) p53,
379 decode(l.pval054,cn,vn,l.pval054) p54,
380 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
381 l.pval055 d55,
382 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
383 l.pval056 d56
384 from hr_pump_batch_lines l
385 where l.batch_line_id = p_batch_line_id;
386 --
387 c cr%rowtype;
388 l_validate boolean := false;
389 L_ROLE_ID number;
390 L_JOB_ID number;
391 L_JOB_GROUP_ID number;
392 L_PERSON_ID number;
393 L_ORGANIZATION_ID number;
394 --
395 begin
396 hr_data_pump.entry('call');
397 open cr;
398 fetch cr into c;
399 if cr%notfound then
400 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
401 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
402 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
403 hr_utility.set_message_token('VALUE',p_batch_line_id);
404 hr_utility.raise_error;
405 end if;
406 --
407 if c.p52 is null or
408 c.p1 is null then
409 L_JOB_ID:=nn;
410 else
411 L_JOB_ID := 
412 hr_pump_get.get_job_id
413 (P_JOB_NAME => c.p52
414 ,P_EFFECTIVE_DATE => c.p1
415 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
416 end if;
417 --
418 if c.p53 is null then
419 L_JOB_GROUP_ID:=nn;
420 else
421 L_JOB_GROUP_ID := 
422 hr_pump_get.get_job_group_id
423 (P_JOB_GROUP_USER_KEY => c.p53);
424 end if;
425 --
426 if c.p54 is null then
427 L_PERSON_ID:=nn;
428 else
429 L_PERSON_ID := 
430 hr_pump_get.get_person_id
431 (P_PERSON_USER_KEY => c.p54);
432 end if;
433 --
434 if c.p55 is null or
435 c.p1 is null or
436 c.p56 is null then
437 L_ORGANIZATION_ID:=nn;
438 else
439 L_ORGANIZATION_ID := 
440 hr_pump_get.get_organization_id
441 (P_ORGANIZATION_NAME => c.p55
442 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
443 ,P_EFFECTIVE_DATE => c.p1
444 ,P_LANGUAGE_CODE => c.p56);
445 end if;
446 --
447 hr_data_pump.api_trc_on;
448 PER_SUPPLEMENTARY_ROLE_API.CREATE_SUPPLEMENTARY_ROLE
449 (p_validate => l_validate
450 ,P_EFFECTIVE_DATE => c.p1
451 ,P_JOB_ID => L_JOB_ID
452 ,P_JOB_GROUP_ID => L_JOB_GROUP_ID
453 ,P_PERSON_ID => L_PERSON_ID
454 ,P_ORGANIZATION_ID => L_ORGANIZATION_ID
455 ,P_START_DATE => c.p2
456 ,P_END_DATE => c.p3
457 ,P_CONFIDENTIAL_DATE => c.p4
458 ,P_EMP_RIGHTS_FLAG => c.p5
459 ,P_END_OF_RIGHTS_DATE => c.p6
460 ,P_PRIMARY_CONTACT_FLAG => c.p7
461 ,P_ATTRIBUTE_CATEGORY => c.p8
462 ,P_ATTRIBUTE1 => c.p9
463 ,P_ATTRIBUTE2 => c.p10
464 ,P_ATTRIBUTE3 => c.p11
465 ,P_ATTRIBUTE4 => c.p12
466 ,P_ATTRIBUTE5 => c.p13
467 ,P_ATTRIBUTE6 => c.p14
468 ,P_ATTRIBUTE7 => c.p15
469 ,P_ATTRIBUTE8 => c.p16
470 ,P_ATTRIBUTE9 => c.p17
471 ,P_ATTRIBUTE10 => c.p18
472 ,P_ATTRIBUTE11 => c.p19
473 ,P_ATTRIBUTE12 => c.p20
474 ,P_ATTRIBUTE13 => c.p21
475 ,P_ATTRIBUTE14 => c.p22
476 ,P_ATTRIBUTE15 => c.p23
477 ,P_ATTRIBUTE16 => c.p24
478 ,P_ATTRIBUTE17 => c.p25
479 ,P_ATTRIBUTE18 => c.p26
480 ,P_ATTRIBUTE19 => c.p27
481 ,P_ATTRIBUTE20 => c.p28
482 ,P_ROLE_INFORMATION_CATEGORY => c.p29
483 ,P_ROLE_INFORMATION1 => c.p30
484 ,P_ROLE_INFORMATION2 => c.p31
485 ,P_ROLE_INFORMATION3 => c.p32
486 ,P_ROLE_INFORMATION4 => c.p33
487 ,P_ROLE_INFORMATION5 => c.p34
488 ,P_ROLE_INFORMATION6 => c.p35
489 ,P_ROLE_INFORMATION7 => c.p36
490 ,P_ROLE_INFORMATION8 => c.p37
491 ,P_ROLE_INFORMATION9 => c.p38
492 ,P_ROLE_INFORMATION10 => c.p39
493 ,P_ROLE_INFORMATION11 => c.p40
494 ,P_ROLE_INFORMATION12 => c.p41
495 ,P_ROLE_INFORMATION13 => c.p42
496 ,P_ROLE_INFORMATION14 => c.p43
497 ,P_ROLE_INFORMATION15 => c.p44
498 ,P_ROLE_INFORMATION16 => c.p45
499 ,P_ROLE_INFORMATION17 => c.p46
500 ,P_ROLE_INFORMATION18 => c.p47
501 ,P_ROLE_INFORMATION19 => c.p48
502 ,P_ROLE_INFORMATION20 => c.p49
503 ,P_ROLE_ID => L_ROLE_ID
504 ,P_OBJECT_VERSION_NUMBER => c.p51);
505 hr_data_pump.api_trc_off;
506 --
507 iuk(p_batch_line_id,c.p50,L_ROLE_ID);
508 --
509 update hr_pump_batch_lines l set
510 l.pval050 = decode(c.p50,null,cn,c.p50),
511 l.pval051 = decode(c.p51,null,cn,c.p51)
512 where l.rowid = c.myrowid;
513 --
514 close cr;
515 --
516 hr_data_pump.exit('call');
517 exception
518  when hr_multi_message.error_message_exist then
519    if cr%isopen then
520     close cr;
521    end if;
522    hr_pump_utils.set_multi_msg_error_flag(true);
523  when others then
524  if cr%isopen then
525   close cr;
526  end if;
527  raise;
528 end call;
529 end hrdpp_CREATE_SUPPLEMENTARY_ROL;