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_COMPETENCE

Source


1 package body hrdpp_CREATE_COMPETENCE as
2 /*
3  * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:00
4  * Generated for API: HR_COMPETENCES_API.CREATE_COMPETENCE
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_LANGUAGE_CODE in varchar2 default null
84 ,P_DESCRIPTION in varchar2 default null
85 ,P_COMPETENCE_ALIAS in varchar2 default null
86 ,P_DATE_FROM in date
87 ,P_DATE_TO in date default null
88 ,P_BEHAVIOURAL_INDICATOR in varchar2 default null
89 ,P_CERTIFICATION_REQUIRED in varchar2 default null
90 ,P_EVALUATION_METHOD in varchar2 default null
91 ,P_RENEWAL_PERIOD_FREQUENCY in number default null
92 ,P_RENEWAL_PERIOD_UNITS 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_SEGMENT1 in varchar2 default null
115 ,P_SEGMENT2 in varchar2 default null
116 ,P_SEGMENT3 in varchar2 default null
117 ,P_SEGMENT4 in varchar2 default null
118 ,P_SEGMENT5 in varchar2 default null
119 ,P_SEGMENT6 in varchar2 default null
120 ,P_SEGMENT7 in varchar2 default null
121 ,P_SEGMENT8 in varchar2 default null
122 ,P_SEGMENT9 in varchar2 default null
123 ,P_SEGMENT10 in varchar2 default null
124 ,P_SEGMENT11 in varchar2 default null
125 ,P_SEGMENT12 in varchar2 default null
126 ,P_SEGMENT13 in varchar2 default null
127 ,P_SEGMENT14 in varchar2 default null
128 ,P_SEGMENT15 in varchar2 default null
129 ,P_SEGMENT16 in varchar2 default null
130 ,P_SEGMENT17 in varchar2 default null
131 ,P_SEGMENT18 in varchar2 default null
132 ,P_SEGMENT19 in varchar2 default null
133 ,P_SEGMENT20 in varchar2 default null
134 ,P_SEGMENT21 in varchar2 default null
135 ,P_SEGMENT22 in varchar2 default null
136 ,P_SEGMENT23 in varchar2 default null
137 ,P_SEGMENT24 in varchar2 default null
138 ,P_SEGMENT25 in varchar2 default null
139 ,P_SEGMENT26 in varchar2 default null
140 ,P_SEGMENT27 in varchar2 default null
141 ,P_SEGMENT28 in varchar2 default null
142 ,P_SEGMENT29 in varchar2 default null
143 ,P_SEGMENT30 in varchar2 default null
144 ,P_CONCAT_SEGMENTS in varchar2 default null
145 ,P_COMPETENCE_DEFINITION_ID in number
146 ,P_COMPETENCE_CLUSTER in varchar2 default null
147 ,P_UNIT_STANDARD_ID in varchar2 default null
148 ,P_CREDIT_TYPE in varchar2 default null
149 ,P_CREDITS in number default null
150 ,P_LEVEL_TYPE in varchar2 default null
151 ,P_LEVEL_NUMBER in number default null
152 ,P_FIELD in varchar2 default null
153 ,P_SUB_FIELD in varchar2 default null
154 ,P_PROVIDER in varchar2 default null
155 ,P_QA_ORGANIZATION in varchar2 default null
156 ,P_INFORMATION_CATEGORY in varchar2 default null
157 ,P_INFORMATION1 in varchar2 default null
158 ,P_INFORMATION2 in varchar2 default null
159 ,P_INFORMATION3 in varchar2 default null
160 ,P_INFORMATION4 in varchar2 default null
161 ,P_INFORMATION5 in varchar2 default null
162 ,P_INFORMATION6 in varchar2 default null
163 ,P_INFORMATION7 in varchar2 default null
164 ,P_INFORMATION8 in varchar2 default null
165 ,P_INFORMATION9 in varchar2 default null
166 ,P_INFORMATION10 in varchar2 default null
167 ,P_INFORMATION11 in varchar2 default null
168 ,P_INFORMATION12 in varchar2 default null
169 ,P_INFORMATION13 in varchar2 default null
170 ,P_INFORMATION14 in varchar2 default null
171 ,P_INFORMATION15 in varchar2 default null
172 ,P_INFORMATION16 in varchar2 default null
173 ,P_INFORMATION17 in varchar2 default null
174 ,P_INFORMATION18 in varchar2 default null
175 ,P_INFORMATION19 in varchar2 default null
176 ,P_INFORMATION20 in varchar2 default null
177 ,P_RATING_SCALE_NAME in varchar2 default null) is
178 blid number := p_data_pump_batch_line_id;
179 begin
180 if blid is not null then
181 delete from hr_pump_batch_lines where batch_line_id = blid;
182 delete from hr_pump_batch_exceptions
183 where source_type = 'BATCH_LINE' and source_id = blid;
184 end if;
185 insert into hr_pump_batch_lines
186 (batch_id
187 ,batch_line_id
188 ,business_group_name
189 ,api_module_id
190 ,line_status
191 ,user_sequence
192 ,link_value
193 ,pval001
194 ,pval002
195 ,pval003
196 ,pval004
197 ,pval005
198 ,pval006
199 ,pval007
200 ,pval008
201 ,pval009
202 ,pval010
203 ,pval011
204 ,pval012
205 ,pval013
206 ,pval014
207 ,pval015
208 ,pval016
209 ,pval017
210 ,pval018
211 ,pval019
212 ,pval020
213 ,pval021
214 ,pval022
215 ,pval023
216 ,pval024
217 ,pval025
218 ,pval026
219 ,pval027
220 ,pval028
221 ,pval029
222 ,pval030
223 ,pval031
224 ,pval032
225 ,pval033
226 ,pval034
227 ,pval035
228 ,pval036
229 ,pval037
230 ,pval038
231 ,pval039
232 ,pval040
233 ,pval041
234 ,pval042
235 ,pval043
236 ,pval044
237 ,pval045
238 ,pval046
239 ,pval047
240 ,pval048
241 ,pval049
242 ,pval050
243 ,pval051
244 ,pval052
245 ,pval053
246 ,pval054
247 ,pval055
248 ,pval056
249 ,pval057
250 ,pval058
251 ,pval059
252 ,pval060
253 ,pval061
254 ,pval062
255 ,pval063
256 ,pval065
257 ,pval068
258 ,pval069
259 ,pval070
260 ,pval071
261 ,pval072
262 ,pval073
263 ,pval074
264 ,pval075
265 ,pval076
266 ,pval077
267 ,pval078
268 ,pval079
269 ,pval080
270 ,pval081
271 ,pval082
272 ,pval083
273 ,pval084
274 ,pval085
275 ,pval086
276 ,pval087
277 ,pval088
278 ,pval089
279 ,pval090
280 ,pval091
281 ,pval092
282 ,pval093
283 ,pval094
284 ,pval095
285 ,pval096
286 ,pval097
287 ,pval098
288 ,pval099)
289 values
290 (p_batch_id
291 ,nvl(blid,hr_pump_batch_lines_s.nextval)
292 ,p_data_pump_business_grp_name
293 ,1173
294 ,'U'
295 ,p_user_sequence
296 ,p_link_value
297 ,dc(P_EFFECTIVE_DATE)
298 ,P_LANGUAGE_CODE
299 ,P_DESCRIPTION
300 ,P_COMPETENCE_ALIAS
301 ,dc(P_DATE_FROM)
302 ,dc(P_DATE_TO)
303 ,P_BEHAVIOURAL_INDICATOR
304 ,P_CERTIFICATION_REQUIRED
305 ,P_EVALUATION_METHOD
306 ,P_RENEWAL_PERIOD_FREQUENCY
307 ,P_RENEWAL_PERIOD_UNITS
308 ,P_ATTRIBUTE_CATEGORY
309 ,P_ATTRIBUTE1
310 ,P_ATTRIBUTE2
311 ,P_ATTRIBUTE3
312 ,P_ATTRIBUTE4
313 ,P_ATTRIBUTE5
314 ,P_ATTRIBUTE6
315 ,P_ATTRIBUTE7
316 ,P_ATTRIBUTE8
317 ,P_ATTRIBUTE9
318 ,P_ATTRIBUTE10
319 ,P_ATTRIBUTE11
320 ,P_ATTRIBUTE12
321 ,P_ATTRIBUTE13
322 ,P_ATTRIBUTE14
323 ,P_ATTRIBUTE15
324 ,P_ATTRIBUTE16
325 ,P_ATTRIBUTE17
326 ,P_ATTRIBUTE18
327 ,P_ATTRIBUTE19
328 ,P_ATTRIBUTE20
329 ,P_SEGMENT1
330 ,P_SEGMENT2
331 ,P_SEGMENT3
332 ,P_SEGMENT4
333 ,P_SEGMENT5
334 ,P_SEGMENT6
335 ,P_SEGMENT7
336 ,P_SEGMENT8
337 ,P_SEGMENT9
338 ,P_SEGMENT10
339 ,P_SEGMENT11
340 ,P_SEGMENT12
341 ,P_SEGMENT13
342 ,P_SEGMENT14
343 ,P_SEGMENT15
344 ,P_SEGMENT16
345 ,P_SEGMENT17
346 ,P_SEGMENT18
347 ,P_SEGMENT19
348 ,P_SEGMENT20
349 ,P_SEGMENT21
350 ,P_SEGMENT22
351 ,P_SEGMENT23
352 ,P_SEGMENT24
353 ,P_SEGMENT25
354 ,P_SEGMENT26
355 ,P_SEGMENT27
356 ,P_SEGMENT28
357 ,P_SEGMENT29
358 ,P_SEGMENT30
359 ,P_CONCAT_SEGMENTS
360 ,P_COMPETENCE_DEFINITION_ID
361 ,P_COMPETENCE_CLUSTER
362 ,P_UNIT_STANDARD_ID
363 ,P_CREDIT_TYPE
364 ,P_CREDITS
365 ,P_LEVEL_TYPE
366 ,P_LEVEL_NUMBER
367 ,P_FIELD
368 ,P_SUB_FIELD
369 ,P_PROVIDER
370 ,P_QA_ORGANIZATION
371 ,P_INFORMATION_CATEGORY
372 ,P_INFORMATION1
373 ,P_INFORMATION2
374 ,P_INFORMATION3
375 ,P_INFORMATION4
376 ,P_INFORMATION5
377 ,P_INFORMATION6
378 ,P_INFORMATION7
379 ,P_INFORMATION8
380 ,P_INFORMATION9
381 ,P_INFORMATION10
382 ,P_INFORMATION11
383 ,P_INFORMATION12
384 ,P_INFORMATION13
385 ,P_INFORMATION14
386 ,P_INFORMATION15
387 ,P_INFORMATION16
388 ,P_INFORMATION17
389 ,P_INFORMATION18
390 ,P_INFORMATION19
391 ,P_INFORMATION20
392 ,P_RATING_SCALE_NAME);
393 end insert_batch_lines;
394 --
395 procedure call
396 (p_business_group_id in number,
397 p_batch_line_id     in number) is
398 cursor cr is
399 select l.rowid myrowid,
400 decode(l.pval001,cn,dn,d(l.pval001)) p1,
401 decode(l.pval002,cn,vn,vn,vn,l.pval002) p2,
402 l.pval002 d2,
403 decode(l.pval003,cn,vn,vn,vn,l.pval003) p3,
404 l.pval003 d3,
405 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
406 l.pval004 d4,
407 decode(l.pval005,cn,dn,d(l.pval005)) p5,
408 decode(l.pval006,cn,dn,vn,dn,d(l.pval006)) p6,
409 l.pval006 d6,
410 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
411 l.pval007 d7,
412 decode(l.pval008,cn,vn,vn,vn,
413  hr_pump_get.gl(l.pval008,'YES_NO',d(l.pval001),l.pval002)) p8,
414 l.pval008 d8,
415 decode(l.pval009,cn,vn,vn,vn,
416  hr_pump_get.gl(l.pval009,'COMPETENCE_EVAL_TYPE',d(l.pval001),l.pval002)) p9,
417 l.pval009 d9,
418 decode(l.pval010,cn,nn,vn,nn,n(l.pval010)) p10,
419 l.pval010 d10,
420 decode(l.pval011,cn,vn,vn,vn,
421  hr_pump_get.gl(l.pval011,'FREQUENCY',d(l.pval001),l.pval002)) p11,
422 l.pval011 d11,
423 decode(l.pval012,cn,vn,vn,vn,l.pval012) p12,
424 l.pval012 d12,
425 decode(l.pval013,cn,vn,vn,vn,l.pval013) p13,
426 l.pval013 d13,
427 decode(l.pval014,cn,vn,vn,vn,l.pval014) p14,
428 l.pval014 d14,
429 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
430 l.pval015 d15,
431 decode(l.pval016,cn,vn,vn,vn,l.pval016) p16,
432 l.pval016 d16,
433 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
434 l.pval017 d17,
435 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
436 l.pval018 d18,
437 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
438 l.pval019 d19,
439 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
440 l.pval020 d20,
441 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
442 l.pval021 d21,
443 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
444 l.pval022 d22,
445 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
446 l.pval023 d23,
447 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
448 l.pval024 d24,
449 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
450 l.pval025 d25,
451 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
452 l.pval026 d26,
453 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
454 l.pval027 d27,
455 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
456 l.pval028 d28,
457 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
458 l.pval029 d29,
459 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
460 l.pval030 d30,
461 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
462 l.pval031 d31,
463 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
464 l.pval032 d32,
465 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
466 l.pval033 d33,
467 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
468 l.pval034 d34,
469 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
470 l.pval035 d35,
471 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
472 l.pval036 d36,
473 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
474 l.pval037 d37,
475 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
476 l.pval038 d38,
477 decode(l.pval039,cn,vn,vn,vn,l.pval039) p39,
478 l.pval039 d39,
479 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
480 l.pval040 d40,
481 decode(l.pval041,cn,vn,vn,vn,l.pval041) p41,
482 l.pval041 d41,
483 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
484 l.pval042 d42,
485 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
486 l.pval043 d43,
490 l.pval045 d45,
487 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
488 l.pval044 d44,
489 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
491 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
492 l.pval046 d46,
493 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
494 l.pval047 d47,
495 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
496 l.pval048 d48,
497 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
498 l.pval049 d49,
499 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
500 l.pval050 d50,
501 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
502 l.pval051 d51,
503 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
504 l.pval052 d52,
505 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
506 l.pval053 d53,
507 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
508 l.pval054 d54,
509 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
510 l.pval055 d55,
511 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
512 l.pval056 d56,
513 decode(l.pval057,cn,vn,vn,vn,l.pval057) p57,
514 l.pval057 d57,
515 decode(l.pval058,cn,vn,vn,vn,l.pval058) p58,
516 l.pval058 d58,
517 decode(l.pval059,cn,vn,vn,vn,l.pval059) p59,
518 l.pval059 d59,
519 decode(l.pval060,cn,vn,vn,vn,l.pval060) p60,
520 l.pval060 d60,
521 decode(l.pval061,cn,vn,vn,vn,l.pval061) p61,
522 l.pval061 d61,
523 decode(l.pval062,cn,vn,vn,vn,l.pval062) p62,
524 l.pval062 d62,
525 decode(l.pval063,cn,vn,vn,vn,l.pval063) p63,
526 l.pval063 d63,
527 l.pval064 p64,
528 decode(l.pval065,cn,nn,n(l.pval065)) p65,
529 l.pval066 p66,
530 l.pval067 p67,
531 decode(l.pval068,cn,vn,vn,vn,l.pval068) p68,
532 l.pval068 d68,
533 decode(l.pval069,cn,vn,vn,vn,l.pval069) p69,
534 l.pval069 d69,
535 decode(l.pval070,cn,vn,vn,vn,
536  hr_pump_get.gl(l.pval070,'PER_QUAL_FWK_CREDIT_TYPE',d(l.pval001),l.pval002)) p70,
537 l.pval070 d70,
538 decode(l.pval071,cn,nn,vn,nn,n(l.pval071)) p71,
539 l.pval071 d71,
540 decode(l.pval072,cn,vn,vn,vn,
541  hr_pump_get.gl(l.pval072,'PER_QUAL_FWK_LEVEL_TYPE',d(l.pval001),l.pval002)) p72,
542 l.pval072 d72,
543 decode(l.pval073,cn,nn,vn,nn,
544  hr_pump_get.gl(l.pval073,'PER_QUAL_FWK_LEVEL',d(l.pval001),l.pval002)) p73,
545 l.pval073 d73,
546 decode(l.pval074,cn,vn,vn,vn,
547  hr_pump_get.gl(l.pval074,'PER_QUAL_FWK_FIELD',d(l.pval001),l.pval002)) p74,
548 l.pval074 d74,
549 decode(l.pval075,cn,vn,vn,vn,
550  hr_pump_get.gl(l.pval075,'PER_QUAL_FWK_SUB_FIELD',d(l.pval001),l.pval002)) p75,
551 l.pval075 d75,
552 decode(l.pval076,cn,vn,vn,vn,
553  hr_pump_get.gl(l.pval076,'PER_QUAL_FWK_PROVIDER',d(l.pval001),l.pval002)) p76,
554 l.pval076 d76,
555 decode(l.pval077,cn,vn,vn,vn,
556  hr_pump_get.gl(l.pval077,'PER_QUAL_FWK_QA_ORG',d(l.pval001),l.pval002)) p77,
557 l.pval077 d77,
558 decode(l.pval078,cn,vn,vn,vn,l.pval078) p78,
559 l.pval078 d78,
560 decode(l.pval079,cn,vn,vn,vn,l.pval079) p79,
561 l.pval079 d79,
562 decode(l.pval080,cn,vn,vn,vn,l.pval080) p80,
563 l.pval080 d80,
564 decode(l.pval081,cn,vn,vn,vn,l.pval081) p81,
565 l.pval081 d81,
566 decode(l.pval082,cn,vn,vn,vn,l.pval082) p82,
567 l.pval082 d82,
568 decode(l.pval083,cn,vn,vn,vn,l.pval083) p83,
569 l.pval083 d83,
570 decode(l.pval084,cn,vn,vn,vn,l.pval084) p84,
571 l.pval084 d84,
572 decode(l.pval085,cn,vn,vn,vn,l.pval085) p85,
573 l.pval085 d85,
574 decode(l.pval086,cn,vn,vn,vn,l.pval086) p86,
575 l.pval086 d86,
576 decode(l.pval087,cn,vn,vn,vn,l.pval087) p87,
577 l.pval087 d87,
578 decode(l.pval088,cn,vn,vn,vn,l.pval088) p88,
579 l.pval088 d88,
580 decode(l.pval089,cn,vn,vn,vn,l.pval089) p89,
581 l.pval089 d89,
582 decode(l.pval090,cn,vn,vn,vn,l.pval090) p90,
583 l.pval090 d90,
584 decode(l.pval091,cn,vn,vn,vn,l.pval091) p91,
585 l.pval091 d91,
586 decode(l.pval092,cn,vn,vn,vn,l.pval092) p92,
587 l.pval092 d92,
588 decode(l.pval093,cn,vn,vn,vn,l.pval093) p93,
589 l.pval093 d93,
590 decode(l.pval094,cn,vn,vn,vn,l.pval094) p94,
591 l.pval094 d94,
592 decode(l.pval095,cn,vn,vn,vn,l.pval095) p95,
593 l.pval095 d95,
594 decode(l.pval096,cn,vn,vn,vn,l.pval096) p96,
595 l.pval096 d96,
596 decode(l.pval097,cn,vn,vn,vn,l.pval097) p97,
597 l.pval097 d97,
598 decode(l.pval098,cn,vn,vn,vn,l.pval098) p98,
599 l.pval098 d98,
600 decode(l.pval099,cn,vn,vn,vn,l.pval099) p99,
601 l.pval099 d99
602 from hr_pump_batch_lines l
603 where l.batch_line_id = p_batch_line_id;
604 --
605 c cr%rowtype;
606 l_validate boolean := false;
607 L_RATING_SCALE_ID number;
608 --
609 begin
610 hr_data_pump.entry('call');
611 open cr;
612 fetch cr into c;
613 if cr%notfound then
614 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
615 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
616 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
617 hr_utility.set_message_token('VALUE',p_batch_line_id);
618 hr_utility.raise_error;
619 end if;
620 --
621 L_RATING_SCALE_ID := 
622 PER_COMPETENCE_DATA_PUMP.get_rating_scale_id
623 (P_DATA_PUMP_ALWAYS_CALL => null
624 ,P_RATING_SCALE_NAME => c.p99
625 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
626 --
627 hr_data_pump.api_trc_on;
628 HR_COMPETENCES_API.CREATE_COMPETENCE
629 (p_validate => l_validate
630 ,P_EFFECTIVE_DATE => c.p1
631 ,P_LANGUAGE_CODE => c.p2
632 ,p_business_group_id => p_business_group_id
633 ,P_DESCRIPTION => c.p3
634 ,P_COMPETENCE_ALIAS => c.p4
635 ,P_DATE_FROM => c.p5
636 ,P_DATE_TO => c.p6
637 ,P_BEHAVIOURAL_INDICATOR => c.p7
638 ,P_CERTIFICATION_REQUIRED => c.p8
639 ,P_EVALUATION_METHOD => c.p9
640 ,P_RENEWAL_PERIOD_FREQUENCY => c.p10
641 ,P_RENEWAL_PERIOD_UNITS => c.p11
642 ,P_RATING_SCALE_ID => L_RATING_SCALE_ID
643 ,P_ATTRIBUTE_CATEGORY => c.p12
644 ,P_ATTRIBUTE1 => c.p13
645 ,P_ATTRIBUTE2 => c.p14
649 ,P_ATTRIBUTE6 => c.p18
646 ,P_ATTRIBUTE3 => c.p15
647 ,P_ATTRIBUTE4 => c.p16
648 ,P_ATTRIBUTE5 => c.p17
650 ,P_ATTRIBUTE7 => c.p19
651 ,P_ATTRIBUTE8 => c.p20
652 ,P_ATTRIBUTE9 => c.p21
653 ,P_ATTRIBUTE10 => c.p22
654 ,P_ATTRIBUTE11 => c.p23
655 ,P_ATTRIBUTE12 => c.p24
656 ,P_ATTRIBUTE13 => c.p25
657 ,P_ATTRIBUTE14 => c.p26
658 ,P_ATTRIBUTE15 => c.p27
659 ,P_ATTRIBUTE16 => c.p28
660 ,P_ATTRIBUTE17 => c.p29
661 ,P_ATTRIBUTE18 => c.p30
662 ,P_ATTRIBUTE19 => c.p31
663 ,P_ATTRIBUTE20 => c.p32
664 ,P_SEGMENT1 => c.p33
665 ,P_SEGMENT2 => c.p34
666 ,P_SEGMENT3 => c.p35
667 ,P_SEGMENT4 => c.p36
668 ,P_SEGMENT5 => c.p37
669 ,P_SEGMENT6 => c.p38
670 ,P_SEGMENT7 => c.p39
671 ,P_SEGMENT8 => c.p40
672 ,P_SEGMENT9 => c.p41
673 ,P_SEGMENT10 => c.p42
674 ,P_SEGMENT11 => c.p43
675 ,P_SEGMENT12 => c.p44
676 ,P_SEGMENT13 => c.p45
677 ,P_SEGMENT14 => c.p46
678 ,P_SEGMENT15 => c.p47
679 ,P_SEGMENT16 => c.p48
680 ,P_SEGMENT17 => c.p49
681 ,P_SEGMENT18 => c.p50
682 ,P_SEGMENT19 => c.p51
683 ,P_SEGMENT20 => c.p52
684 ,P_SEGMENT21 => c.p53
685 ,P_SEGMENT22 => c.p54
686 ,P_SEGMENT23 => c.p55
687 ,P_SEGMENT24 => c.p56
688 ,P_SEGMENT25 => c.p57
689 ,P_SEGMENT26 => c.p58
690 ,P_SEGMENT27 => c.p59
691 ,P_SEGMENT28 => c.p60
692 ,P_SEGMENT29 => c.p61
693 ,P_SEGMENT30 => c.p62
694 ,P_CONCAT_SEGMENTS => c.p63
698 ,P_NAME => c.p67
695 ,P_COMPETENCE_ID => c.p64
696 ,P_COMPETENCE_DEFINITION_ID => c.p65
697 ,P_OBJECT_VERSION_NUMBER => c.p66
699 ,P_COMPETENCE_CLUSTER => c.p68
700 ,P_UNIT_STANDARD_ID => c.p69
701 ,P_CREDIT_TYPE => c.p70
702 ,P_CREDITS => c.p71
703 ,P_LEVEL_TYPE => c.p72
704 ,P_LEVEL_NUMBER => c.p73
705 ,P_FIELD => c.p74
706 ,P_SUB_FIELD => c.p75
707 ,P_PROVIDER => c.p76
708 ,P_QA_ORGANIZATION => c.p77
709 ,P_INFORMATION_CATEGORY => c.p78
710 ,P_INFORMATION1 => c.p79
711 ,P_INFORMATION2 => c.p80
712 ,P_INFORMATION3 => c.p81
713 ,P_INFORMATION4 => c.p82
714 ,P_INFORMATION5 => c.p83
715 ,P_INFORMATION6 => c.p84
716 ,P_INFORMATION7 => c.p85
717 ,P_INFORMATION8 => c.p86
718 ,P_INFORMATION9 => c.p87
719 ,P_INFORMATION10 => c.p88
720 ,P_INFORMATION11 => c.p89
721 ,P_INFORMATION12 => c.p90
722 ,P_INFORMATION13 => c.p91
723 ,P_INFORMATION14 => c.p92
724 ,P_INFORMATION15 => c.p93
725 ,P_INFORMATION16 => c.p94
726 ,P_INFORMATION17 => c.p95
727 ,P_INFORMATION18 => c.p96
728 ,P_INFORMATION19 => c.p97
729 ,P_INFORMATION20 => c.p98);
730 hr_data_pump.api_trc_off;
731 
732 --
733 update hr_pump_batch_lines l set
734 l.pval064 = decode(c.p64,null,cn,c.p64),
735 l.pval065 = decode(c.p65,null,cn,c.p65),
736 l.pval066 = decode(c.p66,null,cn,c.p66),
737 l.pval067 = decode(c.p67,null,cn,c.p67)
738 where l.rowid = c.myrowid;
739 --
740 close cr;
741 --
742 hr_data_pump.exit('call');
743 exception
744  when hr_multi_message.error_message_exist then
745    if cr%isopen then
746     close cr;
747    end if;
748    hr_pump_utils.set_multi_msg_error_flag(true);
749  when others then
750  if cr%isopen then
751   close cr;
752  end if;
753  raise;
754 end call;
755 end hrdpp_CREATE_COMPETENCE;