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_COMPETENCE

Source


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