[Home] [Help]
PACKAGE BODY: APPS.HRDPP_CREATE_COMPETENCE_ELEMEN
Source
1 package body hrdpp_CREATE_COMPETENCE_ELEMEN as
2 /*
3 * Generated by hr_pump_meta_mapper at: 2007/01/03 23:01:08
4 * Generated for API: HR_COMPETENCE_ELEMENT_API.CREATE_COMPETENCE_ELEMENT
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_TYPE in varchar2
83 ,P_MANDATORY in varchar2 default null
84 ,P_EFFECTIVE_DATE_FROM in date default null
85 ,P_EFFECTIVE_DATE_TO in date default null
86 ,P_GROUP_COMPETENCE_TYPE in varchar2 default null
87 ,P_COMPETENCE_TYPE in varchar2 default null
88 ,P_NORMAL_ELAPSE_DURATION in number default null
89 ,P_NORMAL_ELAPSE_DURATION_UNIT in varchar2 default null
90 ,P_SEQUENCE_NUMBER in number default null
91 ,P_SOURCE_OF_PROFICIENCY_LEVEL in varchar2 default null
92 ,P_LINE_SCORE in number default null
93 ,P_CERTIFICATION_DATE in date default null
94 ,P_CERTIFICATION_METHOD in varchar2 default null
95 ,P_NEXT_CERTIFICATION_DATE in date default null
96 ,P_COMMENTS in varchar2 default null
97 ,P_ATTRIBUTE_CATEGORY in varchar2 default null
98 ,P_ATTRIBUTE1 in varchar2 default null
99 ,P_ATTRIBUTE2 in varchar2 default null
100 ,P_ATTRIBUTE3 in varchar2 default null
101 ,P_ATTRIBUTE4 in varchar2 default null
102 ,P_ATTRIBUTE5 in varchar2 default null
103 ,P_ATTRIBUTE6 in varchar2 default null
104 ,P_ATTRIBUTE7 in varchar2 default null
105 ,P_ATTRIBUTE8 in varchar2 default null
106 ,P_ATTRIBUTE9 in varchar2 default null
107 ,P_ATTRIBUTE10 in varchar2 default null
108 ,P_ATTRIBUTE11 in varchar2 default null
109 ,P_ATTRIBUTE12 in varchar2 default null
110 ,P_ATTRIBUTE13 in varchar2 default null
111 ,P_ATTRIBUTE14 in varchar2 default null
112 ,P_ATTRIBUTE15 in varchar2 default null
113 ,P_ATTRIBUTE16 in varchar2 default null
114 ,P_ATTRIBUTE17 in varchar2 default null
115 ,P_ATTRIBUTE18 in varchar2 default null
116 ,P_ATTRIBUTE19 in varchar2 default null
117 ,P_ATTRIBUTE20 in varchar2 default null
118 ,P_EFFECTIVE_DATE in date
119 ,P_OBJECT_NAME in varchar2 default null
120 ,P_PARTY_ID in number default null
121 ,P_UNIT_STANDARD_TYPE in varchar2 default null
122 ,P_STATUS in varchar2 default null
123 ,P_INFORMATION_CATEGORY in varchar2 default null
124 ,P_INFORMATION1 in varchar2 default null
125 ,P_INFORMATION2 in varchar2 default null
126 ,P_INFORMATION3 in varchar2 default null
127 ,P_INFORMATION4 in varchar2 default null
128 ,P_INFORMATION5 in varchar2 default null
129 ,P_INFORMATION6 in varchar2 default null
130 ,P_INFORMATION7 in varchar2 default null
131 ,P_INFORMATION8 in varchar2 default null
132 ,P_INFORMATION9 in varchar2 default null
133 ,P_INFORMATION10 in varchar2 default null
134 ,P_INFORMATION11 in varchar2 default null
135 ,P_INFORMATION12 in varchar2 default null
136 ,P_INFORMATION13 in varchar2 default null
137 ,P_INFORMATION14 in varchar2 default null
138 ,P_INFORMATION15 in varchar2 default null
139 ,P_INFORMATION16 in varchar2 default null
140 ,P_INFORMATION17 in varchar2 default null
141 ,P_INFORMATION18 in varchar2 default null
142 ,P_INFORMATION19 in varchar2 default null
143 ,P_INFORMATION20 in varchar2 default null
144 ,P_ACHIEVED_DATE in date default null
145 ,P_APPR_LINE_SCORE in number default null
146 ,P_COMPETENCE_NAME in varchar2 default null
147 ,P_RATING_SCALE_NAME in varchar2 default null
148 ,P_RATING_LEVEL_NAME in varchar2 default null
149 ,P_JOB_NAME in varchar2 default null
150 ,P_ORGANIZATION_NAME in varchar2 default null
151 ,P_LANGUAGE_CODE in varchar2 default null
152 ,P_QUALIFICATION_TYPE_NAME in varchar2 default null) is
153 blid number := p_data_pump_batch_line_id;
154 begin
155 if blid is not null then
156 delete from hr_pump_batch_lines where batch_line_id = blid;
157 delete from hr_pump_batch_exceptions
158 where source_type = 'BATCH_LINE' and source_id = blid;
159 end if;
160 insert into hr_pump_batch_lines
161 (batch_id
162 ,batch_line_id
163 ,business_group_name
164 ,api_module_id
165 ,line_status
166 ,user_sequence
167 ,link_value
168 ,pval003
169 ,pval004
170 ,pval005
171 ,pval006
172 ,pval007
173 ,pval008
174 ,pval009
175 ,pval010
176 ,pval011
177 ,pval012
178 ,pval013
179 ,pval014
180 ,pval015
181 ,pval016
182 ,pval017
183 ,pval018
184 ,pval019
185 ,pval020
186 ,pval021
187 ,pval022
188 ,pval023
189 ,pval024
190 ,pval025
191 ,pval026
192 ,pval027
193 ,pval028
194 ,pval029
195 ,pval030
196 ,pval031
197 ,pval032
198 ,pval033
199 ,pval034
200 ,pval035
201 ,pval036
202 ,pval037
203 ,pval038
204 ,pval039
205 ,pval040
206 ,pval041
207 ,pval042
208 ,pval043
209 ,pval044
210 ,pval045
211 ,pval046
212 ,pval047
213 ,pval048
214 ,pval049
215 ,pval050
216 ,pval051
217 ,pval052
218 ,pval053
219 ,pval054
220 ,pval055
221 ,pval056
222 ,pval057
223 ,pval058
224 ,pval059
225 ,pval060
226 ,pval061
227 ,pval062
228 ,pval063
229 ,pval064
230 ,pval065
231 ,pval066
232 ,pval067
233 ,pval068
234 ,pval069
235 ,pval070
236 ,pval071
237 ,pval072
238 ,pval073)
239 values
240 (p_batch_id
241 ,nvl(blid,hr_pump_batch_lines_s.nextval)
242 ,p_data_pump_business_grp_name
243 ,1172
244 ,'U'
245 ,p_user_sequence
246 ,p_link_value
247 ,P_TYPE
248 ,P_MANDATORY
249 ,dc(P_EFFECTIVE_DATE_FROM)
250 ,dc(P_EFFECTIVE_DATE_TO)
251 ,P_GROUP_COMPETENCE_TYPE
252 ,P_COMPETENCE_TYPE
253 ,P_NORMAL_ELAPSE_DURATION
254 ,P_NORMAL_ELAPSE_DURATION_UNIT
255 ,P_SEQUENCE_NUMBER
256 ,P_SOURCE_OF_PROFICIENCY_LEVEL
257 ,P_LINE_SCORE
258 ,dc(P_CERTIFICATION_DATE)
259 ,P_CERTIFICATION_METHOD
260 ,dc(P_NEXT_CERTIFICATION_DATE)
261 ,P_COMMENTS
262 ,P_ATTRIBUTE_CATEGORY
263 ,P_ATTRIBUTE1
264 ,P_ATTRIBUTE2
265 ,P_ATTRIBUTE3
266 ,P_ATTRIBUTE4
267 ,P_ATTRIBUTE5
268 ,P_ATTRIBUTE6
269 ,P_ATTRIBUTE7
270 ,P_ATTRIBUTE8
271 ,P_ATTRIBUTE9
272 ,P_ATTRIBUTE10
273 ,P_ATTRIBUTE11
274 ,P_ATTRIBUTE12
275 ,P_ATTRIBUTE13
276 ,P_ATTRIBUTE14
277 ,P_ATTRIBUTE15
278 ,P_ATTRIBUTE16
279 ,P_ATTRIBUTE17
280 ,P_ATTRIBUTE18
281 ,P_ATTRIBUTE19
282 ,P_ATTRIBUTE20
283 ,dc(P_EFFECTIVE_DATE)
284 ,P_OBJECT_NAME
285 ,P_PARTY_ID
286 ,P_UNIT_STANDARD_TYPE
287 ,P_STATUS
288 ,P_INFORMATION_CATEGORY
289 ,P_INFORMATION1
290 ,P_INFORMATION2
291 ,P_INFORMATION3
292 ,P_INFORMATION4
293 ,P_INFORMATION5
294 ,P_INFORMATION6
295 ,P_INFORMATION7
296 ,P_INFORMATION8
297 ,P_INFORMATION9
298 ,P_INFORMATION10
299 ,P_INFORMATION11
300 ,P_INFORMATION12
301 ,P_INFORMATION13
302 ,P_INFORMATION14
303 ,P_INFORMATION15
304 ,P_INFORMATION16
305 ,P_INFORMATION17
306 ,P_INFORMATION18
307 ,P_INFORMATION19
308 ,P_INFORMATION20
309 ,dc(P_ACHIEVED_DATE)
310 ,P_APPR_LINE_SCORE
311 ,P_COMPETENCE_NAME
312 ,P_RATING_SCALE_NAME
313 ,P_RATING_LEVEL_NAME
314 ,P_JOB_NAME
315 ,P_ORGANIZATION_NAME
316 ,P_LANGUAGE_CODE
317 ,P_QUALIFICATION_TYPE_NAME);
318 end insert_batch_lines;
319 --
320 procedure call
321 (p_business_group_id in number,
322 p_batch_line_id in number) is
323 cursor cr is
324 select l.rowid myrowid,
325 l.pval001 p1,
326 l.pval002 p2,
327 decode(l.pval003,cn,vn,l.pval003) p3,
328 decode(l.pval004,cn,vn,vn,vn,l.pval004) p4,
329 l.pval004 d4,
330 decode(l.pval005,cn,dn,vn,dn,d(l.pval005)) p5,
331 l.pval005 d5,
332 decode(l.pval006,cn,dn,vn,dn,d(l.pval006)) p6,
333 l.pval006 d6,
334 decode(l.pval007,cn,vn,vn,vn,l.pval007) p7,
335 l.pval007 d7,
336 decode(l.pval008,cn,vn,vn,vn,l.pval008) p8,
337 l.pval008 d8,
338 decode(l.pval009,cn,nn,vn,nn,n(l.pval009)) p9,
339 l.pval009 d9,
340 decode(l.pval010,cn,vn,vn,vn,l.pval010) p10,
341 l.pval010 d10,
342 decode(l.pval011,cn,nn,vn,nn,n(l.pval011)) p11,
343 l.pval011 d11,
344 decode(l.pval012,cn,vn,vn,vn,l.pval012) p12,
345 l.pval012 d12,
346 decode(l.pval013,cn,nn,vn,nn,n(l.pval013)) p13,
347 l.pval013 d13,
348 decode(l.pval014,cn,dn,vn,dn,d(l.pval014)) p14,
349 l.pval014 d14,
350 decode(l.pval015,cn,vn,vn,vn,l.pval015) p15,
351 l.pval015 d15,
352 decode(l.pval016,cn,dn,vn,dn,d(l.pval016)) p16,
353 l.pval016 d16,
354 decode(l.pval017,cn,vn,vn,vn,l.pval017) p17,
355 l.pval017 d17,
356 decode(l.pval018,cn,vn,vn,vn,l.pval018) p18,
357 l.pval018 d18,
358 decode(l.pval019,cn,vn,vn,vn,l.pval019) p19,
359 l.pval019 d19,
360 decode(l.pval020,cn,vn,vn,vn,l.pval020) p20,
361 l.pval020 d20,
362 decode(l.pval021,cn,vn,vn,vn,l.pval021) p21,
363 l.pval021 d21,
364 decode(l.pval022,cn,vn,vn,vn,l.pval022) p22,
365 l.pval022 d22,
366 decode(l.pval023,cn,vn,vn,vn,l.pval023) p23,
367 l.pval023 d23,
368 decode(l.pval024,cn,vn,vn,vn,l.pval024) p24,
369 l.pval024 d24,
370 decode(l.pval025,cn,vn,vn,vn,l.pval025) p25,
371 l.pval025 d25,
372 decode(l.pval026,cn,vn,vn,vn,l.pval026) p26,
373 l.pval026 d26,
374 decode(l.pval027,cn,vn,vn,vn,l.pval027) p27,
375 l.pval027 d27,
376 decode(l.pval028,cn,vn,vn,vn,l.pval028) p28,
377 l.pval028 d28,
378 decode(l.pval029,cn,vn,vn,vn,l.pval029) p29,
379 l.pval029 d29,
380 decode(l.pval030,cn,vn,vn,vn,l.pval030) p30,
381 l.pval030 d30,
382 decode(l.pval031,cn,vn,vn,vn,l.pval031) p31,
383 l.pval031 d31,
384 decode(l.pval032,cn,vn,vn,vn,l.pval032) p32,
385 l.pval032 d32,
386 decode(l.pval033,cn,vn,vn,vn,l.pval033) p33,
387 l.pval033 d33,
388 decode(l.pval034,cn,vn,vn,vn,l.pval034) p34,
389 l.pval034 d34,
390 decode(l.pval035,cn,vn,vn,vn,l.pval035) p35,
391 l.pval035 d35,
392 decode(l.pval036,cn,vn,vn,vn,l.pval036) p36,
393 l.pval036 d36,
394 decode(l.pval037,cn,vn,vn,vn,l.pval037) p37,
395 l.pval037 d37,
396 decode(l.pval038,cn,vn,vn,vn,l.pval038) p38,
397 l.pval038 d38,
398 decode(l.pval039,cn,dn,d(l.pval039)) p39,
399 decode(l.pval040,cn,vn,vn,vn,l.pval040) p40,
400 l.pval040 d40,
401 decode(l.pval041,cn,nn,vn,nn,n(l.pval041)) p41,
402 l.pval041 d41,
403 decode(l.pval042,cn,vn,vn,vn,l.pval042) p42,
404 l.pval042 d42,
405 decode(l.pval043,cn,vn,vn,vn,l.pval043) p43,
406 l.pval043 d43,
407 decode(l.pval044,cn,vn,vn,vn,l.pval044) p44,
408 l.pval044 d44,
409 decode(l.pval045,cn,vn,vn,vn,l.pval045) p45,
410 l.pval045 d45,
411 decode(l.pval046,cn,vn,vn,vn,l.pval046) p46,
412 l.pval046 d46,
413 decode(l.pval047,cn,vn,vn,vn,l.pval047) p47,
414 l.pval047 d47,
415 decode(l.pval048,cn,vn,vn,vn,l.pval048) p48,
416 l.pval048 d48,
417 decode(l.pval049,cn,vn,vn,vn,l.pval049) p49,
418 l.pval049 d49,
419 decode(l.pval050,cn,vn,vn,vn,l.pval050) p50,
420 l.pval050 d50,
421 decode(l.pval051,cn,vn,vn,vn,l.pval051) p51,
422 l.pval051 d51,
423 decode(l.pval052,cn,vn,vn,vn,l.pval052) p52,
424 l.pval052 d52,
425 decode(l.pval053,cn,vn,vn,vn,l.pval053) p53,
426 l.pval053 d53,
427 decode(l.pval054,cn,vn,vn,vn,l.pval054) p54,
428 l.pval054 d54,
429 decode(l.pval055,cn,vn,vn,vn,l.pval055) p55,
430 l.pval055 d55,
431 decode(l.pval056,cn,vn,vn,vn,l.pval056) p56,
432 l.pval056 d56,
433 decode(l.pval057,cn,vn,vn,vn,l.pval057) p57,
434 l.pval057 d57,
435 decode(l.pval058,cn,vn,vn,vn,l.pval058) p58,
436 l.pval058 d58,
437 decode(l.pval059,cn,vn,vn,vn,l.pval059) p59,
438 l.pval059 d59,
439 decode(l.pval060,cn,vn,vn,vn,l.pval060) p60,
440 l.pval060 d60,
441 decode(l.pval061,cn,vn,vn,vn,l.pval061) p61,
442 l.pval061 d61,
443 decode(l.pval062,cn,vn,vn,vn,l.pval062) p62,
444 l.pval062 d62,
445 decode(l.pval063,cn,vn,vn,vn,l.pval063) p63,
446 l.pval063 d63,
447 decode(l.pval064,cn,vn,vn,vn,l.pval064) p64,
448 l.pval064 d64,
449 decode(l.pval065,cn,dn,vn,dn,d(l.pval065)) p65,
450 l.pval065 d65,
451 decode(l.pval066,cn,nn,vn,nn,n(l.pval066)) p66,
452 l.pval066 d66,
453 decode(l.pval067,cn,vn,vn,vn,l.pval067) p67,
454 l.pval067 d67,
455 decode(l.pval068,cn,vn,vn,vn,l.pval068) p68,
456 l.pval068 d68,
457 decode(l.pval069,cn,vn,vn,vn,l.pval069) p69,
458 l.pval069 d69,
459 decode(l.pval070,cn,vn,vn,vn,l.pval070) p70,
460 l.pval070 d70,
461 decode(l.pval071,cn,vn,vn,vn,l.pval071) p71,
462 l.pval071 d71,
463 decode(l.pval072,cn,vn,vn,vn,l.pval072) p72,
464 l.pval072 d72,
465 decode(l.pval073,cn,vn,vn,vn,l.pval073) p73,
466 l.pval073 d73
467 from hr_pump_batch_lines l
468 where l.batch_line_id = p_batch_line_id;
469 --
470 c cr%rowtype;
471 l_validate boolean := false;
472 L_ENTERPRISE_ID number;
473 L_COMPETENCE_ID number;
474 L_PROFICIENCY_LEVEL_ID number;
475 L_HIGH_PROFICIENCY_LEVEL_ID number;
476 L_WEIGHTING_LEVEL_ID number;
477 L_RATING_LEVEL_ID number;
478 L_PERSON_ID number;
479 L_JOB_ID number;
480 L_VALID_GRADE_ID number;
481 L_POSITION_ID number;
482 L_ORGANIZATION_ID number;
483 L_PARENT_COMPETENCE_ELEMENT_ID number;
484 L_ACTIVITY_VERSION_ID number;
485 L_ASSESSMENT_ID number;
486 L_ASSESSMENT_TYPE_ID number;
487 L_OBJECT_ID number;
488 L_QUALIFICATION_TYPE_ID number;
489 --
490 begin
491 hr_data_pump.entry('call');
492 open cr;
493 fetch cr into c;
494 if cr%notfound then
495 hr_utility.set_message(800,'HR_50326_DP_NO_ROW');
496 hr_utility.set_message_token('TABLE','HR_PUMP_BATCH_LINES');
497 hr_utility.set_message_token('COLUMN','P_BATCH_LINE_ID');
498 hr_utility.set_message_token('VALUE',p_batch_line_id);
499 hr_utility.raise_error;
500 end if;
501 --
502 L_ENTERPRISE_ID :=
503 PER_COMPETENCE_DATA_PUMP.get_enterprise_id;
504 --
505 L_COMPETENCE_ID :=
506 PER_COMPETENCE_DATA_PUMP.get_competence_id
507 (P_DATA_PUMP_ALWAYS_CALL => null
508 ,P_COMPETENCE_NAME => c.p67
509 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
510 --
511 L_PROFICIENCY_LEVEL_ID :=
512 PER_COMPETENCE_DATA_PUMP.get_proficiency_level_id;
513 --
514 L_HIGH_PROFICIENCY_LEVEL_ID :=
515 PER_COMPETENCE_DATA_PUMP.get_high_proficiency_level_id;
516 --
517 L_WEIGHTING_LEVEL_ID :=
521 PER_COMPETENCE_DATA_PUMP.get_rating_level_id
518 PER_COMPETENCE_DATA_PUMP.get_weighting_level_id;
519 --
520 L_RATING_LEVEL_ID :=
522 (P_DATA_PUMP_ALWAYS_CALL => null
523 ,P_RATING_SCALE_NAME => c.p68
524 ,P_COMPETENCE_NAME => c.p67
525 ,P_RATING_LEVEL_NAME => c.p69
526 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
527 --
528 L_PERSON_ID :=
529 PER_COMPETENCE_DATA_PUMP.get_person_id;
530 --
531 if c.p70 is null or
532 c.p39 is null then
533 L_JOB_ID:=nn;
534 else
535 L_JOB_ID :=
536 hr_pump_get.get_job_id
537 (P_JOB_NAME => c.p70
538 ,P_EFFECTIVE_DATE => c.p39
539 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID);
540 end if;
541 --
542 L_VALID_GRADE_ID :=
543 PER_COMPETENCE_DATA_PUMP.get_valid_grade_id;
544 --
545 L_POSITION_ID :=
546 PER_COMPETENCE_DATA_PUMP.get_position_id;
547 --
548 if c.p71 is null or
549 c.p39 is null or
550 c.p72 is null then
551 L_ORGANIZATION_ID:=nn;
552 else
553 L_ORGANIZATION_ID :=
554 hr_pump_get.get_organization_id
555 (P_ORGANIZATION_NAME => c.p71
556 ,P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID
557 ,P_EFFECTIVE_DATE => c.p39
558 ,P_LANGUAGE_CODE => c.p72);
559 end if;
560 --
561 L_PARENT_COMPETENCE_ELEMENT_ID :=
562 PER_COMPETENCE_DATA_PUMP.GET_PARENT_COMP_ELEMENT_ID;
563 --
564 L_ACTIVITY_VERSION_ID :=
565 PER_COMPETENCE_DATA_PUMP.get_activity_version_id;
566 --
567 L_ASSESSMENT_ID :=
568 PER_COMPETENCE_DATA_PUMP.get_assessment_id;
569 --
570 L_ASSESSMENT_TYPE_ID :=
571 PER_COMPETENCE_DATA_PUMP.get_assessment_type_id;
572 --
573 L_OBJECT_ID :=
574 PER_COMPETENCE_DATA_PUMP.get_object_id;
575 --
576 L_QUALIFICATION_TYPE_ID :=
577 PER_COMPETENCE_DATA_PUMP.get_qualification_type_id
578 (P_DATA_PUMP_ALWAYS_CALL => null
579 ,P_QUALIFICATION_TYPE_NAME => c.p73);
580 --
581 hr_data_pump.api_trc_on;
582 HR_COMPETENCE_ELEMENT_API.CREATE_COMPETENCE_ELEMENT
583 (p_validate => l_validate
584 ,P_COMPETENCE_ELEMENT_ID => c.p1
585 ,P_OBJECT_VERSION_NUMBER => c.p2
586 ,P_TYPE => c.p3
587 ,p_business_group_id => p_business_group_id
588 ,P_ENTERPRISE_ID => L_ENTERPRISE_ID
589 ,P_COMPETENCE_ID => L_COMPETENCE_ID
590 ,P_PROFICIENCY_LEVEL_ID => L_PROFICIENCY_LEVEL_ID
591 ,P_HIGH_PROFICIENCY_LEVEL_ID => L_HIGH_PROFICIENCY_LEVEL_ID
592 ,P_WEIGHTING_LEVEL_ID => L_WEIGHTING_LEVEL_ID
593 ,P_RATING_LEVEL_ID => L_RATING_LEVEL_ID
594 ,P_PERSON_ID => L_PERSON_ID
595 ,P_JOB_ID => L_JOB_ID
596 ,P_VALID_GRADE_ID => L_VALID_GRADE_ID
597 ,P_POSITION_ID => L_POSITION_ID
598 ,P_ORGANIZATION_ID => L_ORGANIZATION_ID
599 ,P_PARENT_COMPETENCE_ELEMENT_ID => L_PARENT_COMPETENCE_ELEMENT_ID
600 ,P_ACTIVITY_VERSION_ID => L_ACTIVITY_VERSION_ID
601 ,P_ASSESSMENT_ID => L_ASSESSMENT_ID
602 ,P_ASSESSMENT_TYPE_ID => L_ASSESSMENT_TYPE_ID
603 ,P_MANDATORY => c.p4
604 ,P_EFFECTIVE_DATE_FROM => c.p5
605 ,P_EFFECTIVE_DATE_TO => c.p6
606 ,P_GROUP_COMPETENCE_TYPE => c.p7
607 ,P_COMPETENCE_TYPE => c.p8
608 ,P_NORMAL_ELAPSE_DURATION => c.p9
609 ,P_NORMAL_ELAPSE_DURATION_UNIT => c.p10
610 ,P_SEQUENCE_NUMBER => c.p11
611 ,P_SOURCE_OF_PROFICIENCY_LEVEL => c.p12
612 ,P_LINE_SCORE => c.p13
613 ,P_CERTIFICATION_DATE => c.p14
614 ,P_CERTIFICATION_METHOD => c.p15
615 ,P_NEXT_CERTIFICATION_DATE => c.p16
616 ,P_COMMENTS => c.p17
617 ,P_ATTRIBUTE_CATEGORY => c.p18
618 ,P_ATTRIBUTE1 => c.p19
619 ,P_ATTRIBUTE2 => c.p20
620 ,P_ATTRIBUTE3 => c.p21
621 ,P_ATTRIBUTE4 => c.p22
622 ,P_ATTRIBUTE5 => c.p23
623 ,P_ATTRIBUTE6 => c.p24
624 ,P_ATTRIBUTE7 => c.p25
625 ,P_ATTRIBUTE8 => c.p26
626 ,P_ATTRIBUTE9 => c.p27
627 ,P_ATTRIBUTE10 => c.p28
628 ,P_ATTRIBUTE11 => c.p29
629 ,P_ATTRIBUTE12 => c.p30
630 ,P_ATTRIBUTE13 => c.p31
631 ,P_ATTRIBUTE14 => c.p32
632 ,P_ATTRIBUTE15 => c.p33
633 ,P_ATTRIBUTE16 => c.p34
634 ,P_ATTRIBUTE17 => c.p35
635 ,P_ATTRIBUTE18 => c.p36
636 ,P_ATTRIBUTE19 => c.p37
637 ,P_ATTRIBUTE20 => c.p38
638 ,P_EFFECTIVE_DATE => c.p39
639 ,P_OBJECT_ID => L_OBJECT_ID
640 ,P_OBJECT_NAME => c.p40
641 ,P_PARTY_ID => c.p41
642 ,P_QUALIFICATION_TYPE_ID => L_QUALIFICATION_TYPE_ID
643 ,P_UNIT_STANDARD_TYPE => c.p42
644 ,P_STATUS => c.p43
645 ,P_INFORMATION_CATEGORY => c.p44
646 ,P_INFORMATION1 => c.p45
647 ,P_INFORMATION2 => c.p46
648 ,P_INFORMATION3 => c.p47
649 ,P_INFORMATION4 => c.p48
650 ,P_INFORMATION5 => c.p49
651 ,P_INFORMATION6 => c.p50
652 ,P_INFORMATION7 => c.p51
653 ,P_INFORMATION8 => c.p52
654 ,P_INFORMATION9 => c.p53
655 ,P_INFORMATION10 => c.p54
656 ,P_INFORMATION11 => c.p55
657 ,P_INFORMATION12 => c.p56
658 ,P_INFORMATION13 => c.p57
659 ,P_INFORMATION14 => c.p58
660 ,P_INFORMATION15 => c.p59
661 ,P_INFORMATION16 => c.p60
662 ,P_INFORMATION17 => c.p61
663 ,P_INFORMATION18 => c.p62
664 ,P_INFORMATION19 => c.p63
665 ,P_INFORMATION20 => c.p64
666 ,P_ACHIEVED_DATE => c.p65
667 ,P_APPR_LINE_SCORE => c.p66);
668 hr_data_pump.api_trc_off;
669
670 --
671 update hr_pump_batch_lines l set
672 l.pval001 = decode(c.p1,null,cn,c.p1),
673 l.pval002 = decode(c.p2,null,cn,c.p2)
674 where l.rowid = c.myrowid;
675 --
676 close cr;
677 --
678 hr_data_pump.exit('call');
679 exception
680 when hr_multi_message.error_message_exist then
681 if cr%isopen then
682 close cr;
683 end if;
684 hr_pump_utils.set_multi_msg_error_flag(true);
685 when others then
686 if cr%isopen then
687 close cr;
688 end if;
689 raise;
690 end call;
691 end hrdpp_CREATE_COMPETENCE_ELEMEN;