[Home] [Help]
PACKAGE BODY: APPS.PER_QUALIFICATION_TYPES_PKG
Source
1 package body per_qualification_types_pkg as
2 /* $Header: pequatyp.pkb 120.3 2011/04/28 10:11:15 sidsaxen ship $*/
3
4 procedure OWNER_TO_WHO (
5 X_OWNER in VARCHAR2,
6 X_CREATION_DATE out nocopy DATE,
7 X_CREATED_BY out nocopy NUMBER,
8 X_LAST_UPDATE_DATE out nocopy DATE,
9 X_LAST_UPDATED_BY out nocopy NUMBER,
10 X_LAST_UPDATE_LOGIN out nocopy NUMBER
11 ) is
12 begin
13 if X_OWNER = 'SEED' then
14 X_CREATED_BY := 1;
15 X_LAST_UPDATED_BY := 1;
16 else
17 X_CREATED_BY := 0;
18 X_LAST_UPDATED_BY := 0;
19 end if;
20 X_CREATION_DATE := sysdate;
21 X_LAST_UPDATE_DATE := sysdate;
22 X_LAST_UPDATE_LOGIN := 0;
23 end OWNER_TO_WHO;
24
25 procedure KEY_TO_ID (
26 X_NAME IN VARCHAR2,
27 X_QUALIFICATION_TYPE_ID OUT NOCOPY NUMBER
28 ) IS
29 Cursor csr_qualification_type is
30 select QUALIFICATION_TYPE_ID
31 from per_qualification_types
32 where name = X_NAME;
33 Cursor csr_sequence is
34 select per_qualification_types_s.nextval
35 from dual;
36 begin
37 open csr_qualification_type;
38 fetch csr_qualification_type into X_QUALIFICATION_TYPE_ID;
39 if csr_qualification_type%notfound then
40 open csr_sequence;
41 fetch csr_sequence into X_QUALIFICATION_TYPE_ID;
42 close csr_sequence;
43 end if;
44 close csr_qualification_type;
45 end KEY_TO_ID;
46
47 procedure insert_row(
48 X_ROWID in out nocopy VARCHAR2,
49 X_QUALIFICATION_TYPE_ID IN NUMBER,
50 X_NAME IN VARCHAR2,
51 X_CATEGORY IN VARCHAR2,
52 X_RANK in number,
53 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
54 X_ATTRIBUTE1 IN VARCHAR2,
55 X_ATTRIBUTE2 IN VARCHAR2,
56 X_ATTRIBUTE3 IN VARCHAR2,
57 X_ATTRIBUTE4 IN VARCHAR2,
58 X_ATTRIBUTE5 IN VARCHAR2,
59 X_ATTRIBUTE6 IN VARCHAR2,
60 X_ATTRIBUTE7 IN VARCHAR2,
61 X_ATTRIBUTE8 IN VARCHAR2,
62 X_ATTRIBUTE9 IN VARCHAR2,
63 X_ATTRIBUTE10 IN VARCHAR2,
64 X_ATTRIBUTE11 IN VARCHAR2,
65 X_ATTRIBUTE12 IN VARCHAR2,
66 X_ATTRIBUTE13 IN VARCHAR2,
67 X_ATTRIBUTE14 IN VARCHAR2,
68 X_ATTRIBUTE15 IN VARCHAR2,
69 X_ATTRIBUTE16 IN VARCHAR2,
70 X_ATTRIBUTE17 IN VARCHAR2,
71 X_ATTRIBUTE18 IN VARCHAR2,
72 X_ATTRIBUTE19 IN VARCHAR2,
73 X_ATTRIBUTE20 IN VARCHAR2,
74 X_INFORMATION1 IN VARCHAR2,
75 X_INFORMATION2 IN VARCHAR2,
76 X_INFORMATION3 IN VARCHAR2,
77 X_INFORMATION4 IN VARCHAR2,
78 X_INFORMATION5 IN VARCHAR2,
79 X_INFORMATION6 IN VARCHAR2,
80 X_INFORMATION7 IN VARCHAR2,
81 X_INFORMATION8 IN VARCHAR2,
82 X_INFORMATION9 IN VARCHAR2,
83 X_INFORMATION10 IN VARCHAR2,
84 X_INFORMATION11 IN VARCHAR2,
85 X_INFORMATION12 IN VARCHAR2,
86 X_INFORMATION13 IN VARCHAR2,
87 X_INFORMATION14 IN VARCHAR2,
88 X_INFORMATION15 IN VARCHAR2,
89 X_INFORMATION16 IN VARCHAR2,
90 X_INFORMATION17 IN VARCHAR2,
91 X_INFORMATION18 IN VARCHAR2,
92 X_INFORMATION19 IN VARCHAR2,
93 X_INFORMATION20 IN VARCHAR2,
94 X_INFORMATION21 IN VARCHAR2,
95 X_INFORMATION22 IN VARCHAR2,
96 X_INFORMATION23 IN VARCHAR2,
97 X_INFORMATION24 IN VARCHAR2,
98 X_INFORMATION25 IN VARCHAR2,
99 X_INFORMATION26 IN VARCHAR2,
100 X_INFORMATION27 IN VARCHAR2,
101 X_INFORMATION28 IN VARCHAR2,
102 X_INFORMATION29 IN VARCHAR2,
103 X_INFORMATION30 IN VARCHAR2,
104 X_INFORMATION_CATEGORY IN VARCHAR2,
105 X_QUAL_FRAMEWORK_ID in number,
106 X_QUALIFICATION_TYPE in varchar2,
107 X_CREDIT_TYPE in varchar2,
108 X_CREDITS in number,
109 X_LEVEL_TYPE in varchar2,
110 X_LEVEL_NUMBER in varchar2,
111 X_FIELD IN VARCHAR2,
112 X_SUB_FIELD IN VARCHAR2,
113 X_PROVIDER IN VARCHAR2,
114 X_QA_ORGANIZATION IN VARCHAR2,
115 X_OBJECT_VERSION_NUMBER IN VARCHAR2,
116 X_USER_NAME IN VARCHAR2,
117 X_CREATION_DATE in DATE,
118 X_CREATED_BY in NUMBER,
119 X_LAST_UPDATE_DATE in DATE,
120 X_LAST_UPDATED_BY in NUMBER,
121 X_LAST_UPDATE_LOGIN in NUMBER
122 ) is
123
124 Cursor C is
125 select rowid
126 from PER_QUALIFICATION_TYPES
127 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID;
128
129
130 begin
131
132 insert into PER_QUALIFICATION_TYPES
133 (
134 QUALIFICATION_TYPE_ID,
135 NAME,
136 CATEGORY,
137 RANK,
138 ATTRIBUTE_CATEGORY,
139 ATTRIBUTE1,
140 ATTRIBUTE2,
141 ATTRIBUTE3,
142 ATTRIBUTE4,
143 ATTRIBUTE5,
144 ATTRIBUTE6,
145 ATTRIBUTE7,
146 ATTRIBUTE8,
147 ATTRIBUTE9,
148 ATTRIBUTE10,
149 ATTRIBUTE11,
150 ATTRIBUTE12,
151 ATTRIBUTE13,
152 ATTRIBUTE14,
153 ATTRIBUTE15,
154 ATTRIBUTE16,
155 ATTRIBUTE17,
156 ATTRIBUTE18,
157 ATTRIBUTE19,
158 ATTRIBUTE20,
159 INFORMATION1,
160 INFORMATION2,
161 INFORMATION3,
162 INFORMATION4,
163 INFORMATION5,
164 INFORMATION6,
165 INFORMATION7,
166 INFORMATION8,
167 INFORMATION9,
168 INFORMATION10,
169 INFORMATION11,
170 INFORMATION12,
171 INFORMATION13,
172 INFORMATION14,
173 INFORMATION15,
174 INFORMATION16,
175 INFORMATION17,
176 INFORMATION18,
177 INFORMATION19,
178 INFORMATION20,
179 INFORMATION21,
180 INFORMATION22,
181 INFORMATION23,
182 INFORMATION24,
183 INFORMATION25,
184 INFORMATION26,
185 INFORMATION27,
186 INFORMATION28,
187 INFORMATION29,
188 INFORMATION30,
189 INFORMATION_CATEGORY,
190 QUAL_FRAMEWORK_ID,
191 QUALIFICATION_TYPE,
192 CREDIT_TYPE,
193 CREDITS,
194 LEVEL_TYPE,
195 LEVEL_NUMBER,
196 FIELD,
197 SUB_FIELD,
198 PROVIDER,
199 QA_ORGANIZATION,
200 OBJECT_VERSION_NUMBER,
201 CREATION_DATE,
202 CREATED_BY,
203 LAST_UPDATE_DATE,
204 LAST_UPDATED_BY,
205 LAST_UPDATE_LOGIN
206 )
207 values
208 (
209 X_QUALIFICATION_TYPE_ID,
210 X_NAME,
211 X_CATEGORY,
212 X_RANK,
213 X_ATTRIBUTE_CATEGORY,
214 X_ATTRIBUTE1,
215 X_ATTRIBUTE2,
216 X_ATTRIBUTE3,
217 X_ATTRIBUTE4,
218 X_ATTRIBUTE5,
219 X_ATTRIBUTE6,
220 X_ATTRIBUTE7,
221 X_ATTRIBUTE8,
222 X_ATTRIBUTE9,
223 X_ATTRIBUTE10,
224 X_ATTRIBUTE11,
225 X_ATTRIBUTE12,
226 X_ATTRIBUTE13,
227 X_ATTRIBUTE14,
228 X_ATTRIBUTE15,
229 X_ATTRIBUTE16,
230 X_ATTRIBUTE17,
231 X_ATTRIBUTE18,
232 X_ATTRIBUTE19,
233 X_ATTRIBUTE20,
234 X_INFORMATION1,
235 X_INFORMATION2,
236 X_INFORMATION3,
237 X_INFORMATION4,
238 X_INFORMATION5,
239 X_INFORMATION6,
240 X_INFORMATION7,
241 X_INFORMATION8,
242 X_INFORMATION9,
243 X_INFORMATION10,
244 X_INFORMATION11,
245 X_INFORMATION12,
246 X_INFORMATION13,
247 X_INFORMATION14,
248 X_INFORMATION15,
249 X_INFORMATION16,
250 X_INFORMATION17,
251 X_INFORMATION18,
252 X_INFORMATION19,
253 X_INFORMATION20,
254 X_INFORMATION21,
255 X_INFORMATION22,
256 X_INFORMATION23,
257 X_INFORMATION24,
258 X_INFORMATION25,
259 X_INFORMATION26,
260 X_INFORMATION27,
261 X_INFORMATION28,
262 X_INFORMATION29,
263 X_INFORMATION30,
264 X_INFORMATION_CATEGORY,
265 X_QUAL_FRAMEWORK_ID,
266 X_QUALIFICATION_TYPE,
267 X_CREDIT_TYPE,
268 X_CREDITS,
269 X_LEVEL_TYPE,
270 X_LEVEL_NUMBER,
271 X_FIELD,
272 X_SUB_FIELD,
273 X_PROVIDER,
274 X_QA_ORGANIZATION,
275 X_OBJECT_VERSION_NUMBER,
276 X_CREATION_DATE,
277 X_CREATED_BY,
278 X_LAST_UPDATE_DATE,
279 X_LAST_UPDATED_BY,
280 X_LAST_UPDATE_LOGIN
281 );
282
283 --
284 -- Added the following code as a part of Zero Downtime Patching Project.
285 -- Code Starts Here.
286 --
287 per_ric_pkg.Chk_integrity (
288 p_entity_name=>'PER_QUALIFICATION_TYPES_TL',
289 p_ref_entity=>'PER_QUALIFICATION_TYPES',
290 p_ref_column_name=>'QUALIFICATION_TYPE_ID',
291 p_ref_col_value_number=>X_QUALIFICATION_TYPE_ID,
292 p_ref_col_value_varchar=>NULL,
293 p_ref_col_value_date=>NULL,
294 p_ref_type=>'INS');
295 --
296 -- Code Ends Here
297 --
298
299 insert into PER_QUALIFICATION_TYPES_TL
300 (
301 QUALIFICATION_TYPE_ID,
302 NAME,
303 LAST_UPDATE_DATE,
304 LAST_UPDATED_BY,
305 LAST_UPDATE_LOGIN,
306 CREATED_BY,
307 CREATION_DATE,
308 LANGUAGE,
309 SOURCE_LANG
310 ) select
311 X_QUALIFICATION_TYPE_ID,
312 X_USER_NAME,
313 X_LAST_UPDATE_DATE,
314 X_LAST_UPDATED_BY,
315 X_LAST_UPDATE_LOGIN,
316 X_CREATED_BY,
317 X_CREATION_DATE,
318 L.LANGUAGE_CODE,
319 userenv('LANG')
320 from FND_LANGUAGES L
321 where L.INSTALLED_FLAG in ('I', 'B')
322 and not exists
323 (select NULL
324 from PER_QUALIFICATION_TYPES_TL T
325 where T.QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID
326 AND T.LANGUAGE = L.LANGUAGE_CODE);
327
328 open c;
329 fetch c into X_ROWID;
330 if (c%notfound) then
331 close c;
332 raise no_data_found;
333 end if;
334 close c;
335
336 end INSERT_ROW;
337
338
339 procedure update_row(
340 X_QUALIFICATION_TYPE_ID IN NUMBER,
341 X_NAME IN VARCHAR2,
342 X_CATEGORY IN VARCHAR2,
343 X_RANK in number,
344 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
345 X_ATTRIBUTE1 IN VARCHAR2,
346 X_ATTRIBUTE2 IN VARCHAR2,
347 X_ATTRIBUTE3 IN VARCHAR2,
348 X_ATTRIBUTE4 IN VARCHAR2,
349 X_ATTRIBUTE5 IN VARCHAR2,
350 X_ATTRIBUTE6 IN VARCHAR2,
351 X_ATTRIBUTE7 IN VARCHAR2,
352 X_ATTRIBUTE8 IN VARCHAR2,
353 X_ATTRIBUTE9 IN VARCHAR2,
354 X_ATTRIBUTE10 IN VARCHAR2,
355 X_ATTRIBUTE11 IN VARCHAR2,
356 X_ATTRIBUTE12 IN VARCHAR2,
357 X_ATTRIBUTE13 IN VARCHAR2,
358 X_ATTRIBUTE14 IN VARCHAR2,
359 X_ATTRIBUTE15 IN VARCHAR2,
360 X_ATTRIBUTE16 IN VARCHAR2,
361 X_ATTRIBUTE17 IN VARCHAR2,
362 X_ATTRIBUTE18 IN VARCHAR2,
363 X_ATTRIBUTE19 IN VARCHAR2,
364 X_ATTRIBUTE20 IN VARCHAR2,
365 X_INFORMATION1 IN VARCHAR2,
366 X_INFORMATION2 IN VARCHAR2,
367 X_INFORMATION3 IN VARCHAR2,
368 X_INFORMATION4 IN VARCHAR2,
369 X_INFORMATION5 IN VARCHAR2,
370 X_INFORMATION6 IN VARCHAR2,
371 X_INFORMATION7 IN VARCHAR2,
372 X_INFORMATION8 IN VARCHAR2,
373 X_INFORMATION9 IN VARCHAR2,
374 X_INFORMATION10 IN VARCHAR2,
375 X_INFORMATION11 IN VARCHAR2,
376 X_INFORMATION12 IN VARCHAR2,
377 X_INFORMATION13 IN VARCHAR2,
378 X_INFORMATION14 IN VARCHAR2,
379 X_INFORMATION15 IN VARCHAR2,
380 X_INFORMATION16 IN VARCHAR2,
381 X_INFORMATION17 IN VARCHAR2,
382 X_INFORMATION18 IN VARCHAR2,
383 X_INFORMATION19 IN VARCHAR2,
384 X_INFORMATION20 IN VARCHAR2,
385 X_INFORMATION21 IN VARCHAR2,
386 X_INFORMATION22 IN VARCHAR2,
387 X_INFORMATION23 IN VARCHAR2,
388 X_INFORMATION24 IN VARCHAR2,
389 X_INFORMATION25 IN VARCHAR2,
390 X_INFORMATION26 IN VARCHAR2,
391 X_INFORMATION27 IN VARCHAR2,
392 X_INFORMATION28 IN VARCHAR2,
393 X_INFORMATION29 IN VARCHAR2,
394 X_INFORMATION30 IN VARCHAR2,
395 X_INFORMATION_CATEGORY IN VARCHAR2,
396 X_QUAL_FRAMEWORK_ID in number,
397 X_QUALIFICATION_TYPE in varchar2,
398 X_CREDIT_TYPE in varchar2,
399 X_CREDITS in number,
400 X_LEVEL_TYPE in varchar2,
401 X_LEVEL_NUMBER in varchar2,
402 X_FIELD IN VARCHAR2,
403 X_SUB_FIELD IN VARCHAR2,
404 X_PROVIDER IN VARCHAR2,
405 X_QA_ORGANIZATION IN VARCHAR2,
406 X_OBJECT_VERSION_NUMBER IN VARCHAR2,
407 X_USER_NAME IN VARCHAR2,
408 X_CREATION_DATE in DATE,
409 X_CREATED_BY in NUMBER,
410 X_LAST_UPDATE_DATE in DATE,
411 X_LAST_UPDATED_BY in NUMBER,
412 X_LAST_UPDATE_LOGIN in NUMBER
413 ) is
414
415
416 begin
417
418 update per_qualification_types
419 set NAME = X_NAME,
420 CATEGORY = X_CATEGORY,
421 RANK = X_RANK,
422 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
426 ATTRIBUTE4 = X_ATTRIBUTE4,
423 ATTRIBUTE1 = X_ATTRIBUTE1,
424 ATTRIBUTE2 = X_ATTRIBUTE2,
425 ATTRIBUTE3 = X_ATTRIBUTE3,
427 ATTRIBUTE5 = X_ATTRIBUTE5,
428 ATTRIBUTE6 = X_ATTRIBUTE6,
429 ATTRIBUTE7 = X_ATTRIBUTE7,
430 ATTRIBUTE8 = X_ATTRIBUTE8,
431 ATTRIBUTE9 = X_ATTRIBUTE9,
432 ATTRIBUTE10 = X_ATTRIBUTE10,
433 ATTRIBUTE11 = X_ATTRIBUTE11,
434 ATTRIBUTE12 = X_ATTRIBUTE12,
435 ATTRIBUTE13 = X_ATTRIBUTE13,
436 ATTRIBUTE14 = X_ATTRIBUTE14,
437 ATTRIBUTE15 = X_ATTRIBUTE15,
438 ATTRIBUTE16 = X_ATTRIBUTE16,
439 ATTRIBUTE17 = X_ATTRIBUTE17,
440 ATTRIBUTE18 = X_ATTRIBUTE18,
441 ATTRIBUTE19 = X_ATTRIBUTE19,
442 ATTRIBUTE20 = X_ATTRIBUTE20,
443 INFORMATION1 = X_INFORMATION1,
444 INFORMATION2 = X_INFORMATION2,
445 INFORMATION3 = X_INFORMATION3,
446 INFORMATION4 = X_INFORMATION4,
447 INFORMATION5 = X_INFORMATION5,
448 INFORMATION6 = X_INFORMATION6,
449 INFORMATION7 = X_INFORMATION7,
450 INFORMATION8 = X_INFORMATION8,
451 INFORMATION9 = X_INFORMATION9,
452 INFORMATION10 = X_INFORMATION10,
453 INFORMATION11 = X_INFORMATION11,
454 INFORMATION12 = X_INFORMATION12,
455 INFORMATION13 = X_INFORMATION13,
456 INFORMATION14 = X_INFORMATION14,
457 INFORMATION15 = X_INFORMATION15,
458 INFORMATION16 = X_INFORMATION16,
459 INFORMATION17 = X_INFORMATION17,
460 INFORMATION18 = X_INFORMATION18,
461 INFORMATION19 = X_INFORMATION19,
462 INFORMATION20 = X_INFORMATION20,
463 INFORMATION21 = X_INFORMATION21,
464 INFORMATION22 = X_INFORMATION22,
465 INFORMATION23 = X_INFORMATION23,
466 INFORMATION24 = X_INFORMATION24,
467 INFORMATION25 = X_INFORMATION25,
468 INFORMATION26 = X_INFORMATION26,
469 INFORMATION27 = X_INFORMATION27,
470 INFORMATION28 = X_INFORMATION28,
471 INFORMATION29 = X_INFORMATION29,
472 INFORMATION30 = X_INFORMATION30,
473 INFORMATION_CATEGORY = X_INFORMATION_CATEGORY,
474 QUAL_FRAMEWORK_ID = X_QUAL_FRAMEWORK_ID,
475 QUALIFICATION_TYPE = X_QUALIFICATION_TYPE,
476 CREDIT_TYPE = X_CREDIT_TYPE,
477 CREDITS = X_CREDITS,
478 LEVEL_TYPE = X_LEVEL_TYPE,
479 LEVEL_NUMBER = X_LEVEL_NUMBER,
480 FIELD = X_FIELD,
481 SUB_FIELD = X_SUB_FIELD,
482 PROVIDER = X_PROVIDER,
483 QA_ORGANIZATION = X_QA_ORGANIZATION,
484 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
485 CREATION_DATE = X_CREATION_DATE,
486 CREATED_BY = X_CREATED_BY,
487 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
488 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
489 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
490 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID;
491
492 if (sql%notfound) then
493 raise no_data_found;
494 end if;
495
496 update per_qualification_types_tl
497 set NAME = X_USER_NAME,
498 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
499 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
500 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
501 SOURCE_LANG = userenv('LANG')
502 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID
503 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
504
505 if (sql%notfound) then
506 raise no_data_found;
507 end if;
508
509 end update_row;
510
511 procedure load_row(X_NAME IN VARCHAR2,
512 X_CATEGORY IN VARCHAR2,
513 X_RANK in number,
514 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
515 X_ATTRIBUTE1 IN VARCHAR2,
516 X_ATTRIBUTE2 IN VARCHAR2,
517 X_ATTRIBUTE3 IN VARCHAR2,
518 X_ATTRIBUTE4 IN VARCHAR2,
519 X_ATTRIBUTE5 IN VARCHAR2,
520 X_ATTRIBUTE6 IN VARCHAR2,
521 X_ATTRIBUTE7 IN VARCHAR2,
522 X_ATTRIBUTE8 IN VARCHAR2,
523 X_ATTRIBUTE9 IN VARCHAR2,
524 X_ATTRIBUTE10 IN VARCHAR2,
525 X_ATTRIBUTE11 IN VARCHAR2,
526 X_ATTRIBUTE12 IN VARCHAR2,
527 X_ATTRIBUTE13 IN VARCHAR2,
528 X_ATTRIBUTE14 IN VARCHAR2,
529 X_ATTRIBUTE15 IN VARCHAR2,
530 X_ATTRIBUTE16 IN VARCHAR2,
531 X_ATTRIBUTE17 IN VARCHAR2,
532 X_ATTRIBUTE18 IN VARCHAR2,
533 X_ATTRIBUTE19 IN VARCHAR2,
534 X_ATTRIBUTE20 IN VARCHAR2,
535 X_INFORMATION1 IN VARCHAR2,
536 X_INFORMATION2 IN VARCHAR2,
537 X_INFORMATION3 IN VARCHAR2,
538 X_INFORMATION4 IN VARCHAR2,
539 X_INFORMATION5 IN VARCHAR2,
540 X_INFORMATION6 IN VARCHAR2,
541 X_INFORMATION7 IN VARCHAR2,
542 X_INFORMATION8 IN VARCHAR2,
543 X_INFORMATION9 IN VARCHAR2,
544 X_INFORMATION10 IN VARCHAR2,
545 X_INFORMATION11 IN VARCHAR2,
546 X_INFORMATION12 IN VARCHAR2,
547 X_INFORMATION13 IN VARCHAR2,
548 X_INFORMATION14 IN VARCHAR2,
549 X_INFORMATION15 IN VARCHAR2,
550 X_INFORMATION16 IN VARCHAR2,
551 X_INFORMATION17 IN VARCHAR2,
552 X_INFORMATION18 IN VARCHAR2,
553 X_INFORMATION19 IN VARCHAR2,
554 X_INFORMATION20 IN VARCHAR2,
555 X_INFORMATION21 IN VARCHAR2,
556 X_INFORMATION22 IN VARCHAR2,
557 X_INFORMATION23 IN VARCHAR2,
558 X_INFORMATION24 IN VARCHAR2,
559 X_INFORMATION25 IN VARCHAR2,
560 X_INFORMATION26 IN VARCHAR2,
564 X_INFORMATION30 IN VARCHAR2,
561 X_INFORMATION27 IN VARCHAR2,
562 X_INFORMATION28 IN VARCHAR2,
563 X_INFORMATION29 IN VARCHAR2,
565 X_INFORMATION_CATEGORY IN VARCHAR2,
566 X_QUAL_FRAMEWORK_ID in number,
567 X_QUALIFICATION_TYPE in varchar2,
568 X_CREDIT_TYPE in varchar2,
569 X_CREDITS in number,
570 X_LEVEL_TYPE in varchar2,
571 X_LEVEL_NUMBER in varchar2,
572 X_FIELD IN VARCHAR2,
573 X_SUB_FIELD IN VARCHAR2,
574 X_PROVIDER IN VARCHAR2,
575 X_QA_ORGANIZATION IN VARCHAR2,
576 X_OBJECT_VERSION_NUMBER IN VARCHAR2,
577 X_USER_NAME IN VARCHAR2,
578 X_OWNER in VARCHAR2,
579 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
580 X_CUSTOM_MODE IN VARCHAR2 default null) IS
581
582 X_ROWID ROWID;
583 X_QUALIFICATION_TYPE_ID NUMBER;
584 X_CREATION_DATE DATE :=sysdate;
585 X_CREATED_BY NUMBER;
586 -- X_LAST_UPDATE_DATE DATE;
587 X_LAST_UPDATED_BY NUMBER;
588 X_LAST_UPDATE_LOGIN NUMBER;
589 f_luby number; -- entity owner in file
590 f_ludate date; -- entity update date in file
591 db_luby number; -- entity owner in db
592 db_ludate date; -- entity update date in db
593
594 begin
595
596 if X_OWNER = 'SEED' then
597 X_CREATED_BY := 1;
598 else
599 X_CREATED_BY := 0;
600 end if;
601
602 key_to_id( X_NAME,X_QUALIFICATION_TYPE_ID);
603
604 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
605 /* OWNER_TO_WHO (
606 X_OWNER,
607 X_CREATION_DATE,
608 X_CREATED_BY,
609 X_LAST_UPDATE_DATE,
610 X_LAST_UPDATED_BY,
611 X_LAST_UPDATE_LOGIN
612 ); */
613
614 begin
615 f_luby := fnd_load_util.owner_id(X_OWNER);
616 -- Translate char last_update_date to date
617 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
618 select LAST_UPDATED_BY, LAST_UPDATE_DATE
619 into db_luby, db_ludate
620 from PER_QUALIFICATION_TYPES
621 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID;
622
623 -- Test for customization and version
624 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
625 db_ludate, X_CUSTOM_MODE)) then
626
627
628 UPDATE_ROW (
629 X_QUALIFICATION_TYPE_ID,
630 X_NAME,
631 X_CATEGORY,
632 X_RANK,
633 X_ATTRIBUTE_CATEGORY,
634 X_ATTRIBUTE1,
635 X_ATTRIBUTE2,
636 X_ATTRIBUTE3,
637 X_ATTRIBUTE4,
638 X_ATTRIBUTE5,
639 X_ATTRIBUTE6,
640 X_ATTRIBUTE7,
641 X_ATTRIBUTE8,
642 X_ATTRIBUTE9,
643 X_ATTRIBUTE10,
644 X_ATTRIBUTE11,
645 X_ATTRIBUTE12,
646 X_ATTRIBUTE13,
647 X_ATTRIBUTE14,
648 X_ATTRIBUTE15,
649 X_ATTRIBUTE16,
650 X_ATTRIBUTE17,
651 X_ATTRIBUTE18,
652 X_ATTRIBUTE19,
653 X_ATTRIBUTE20,
654 X_INFORMATION1,
655 X_INFORMATION2,
656 X_INFORMATION3,
657 X_INFORMATION4,
658 X_INFORMATION5,
659 X_INFORMATION6,
660 X_INFORMATION7,
661 X_INFORMATION8,
662 X_INFORMATION9,
663 X_INFORMATION10,
664 X_INFORMATION11,
665 X_INFORMATION12,
666 X_INFORMATION13,
667 X_INFORMATION14,
668 X_INFORMATION15,
669 X_INFORMATION16,
670 X_INFORMATION17,
671 X_INFORMATION18,
672 X_INFORMATION19,
673 X_INFORMATION20,
674 X_INFORMATION21,
675 X_INFORMATION22,
676 X_INFORMATION23,
677 X_INFORMATION24,
678 X_INFORMATION25,
679 X_INFORMATION26,
680 X_INFORMATION27,
681 X_INFORMATION28,
682 X_INFORMATION29,
683 X_INFORMATION30,
684 X_INFORMATION_CATEGORY,
685 X_QUAL_FRAMEWORK_ID,
686 X_QUALIFICATION_TYPE,
687 X_CREDIT_TYPE,
688 X_CREDITS,
689 X_LEVEL_TYPE,
690 X_LEVEL_NUMBER,
691 X_FIELD,
692 X_SUB_FIELD,
693 X_PROVIDER,
694 X_QA_ORGANIZATION,
695 X_OBJECT_VERSION_NUMBER,
696 X_USER_NAME,
697 X_CREATION_DATE,
698 X_CREATED_BY,
699 f_ludate,
700 f_luby,
701 0 );
702 end if;
703 exception
704 when no_data_found then
705 INSERT_ROW( X_ROWID,
706 X_QUALIFICATION_TYPE_ID,
707 X_NAME,
708 X_CATEGORY,
709 X_RANK,
710 X_ATTRIBUTE_CATEGORY,
711 X_ATTRIBUTE1,
712 X_ATTRIBUTE2,
713 X_ATTRIBUTE3,
714 X_ATTRIBUTE4,
715 X_ATTRIBUTE5,
716 X_ATTRIBUTE6,
717 X_ATTRIBUTE7,
718 X_ATTRIBUTE8,
719 X_ATTRIBUTE9,
720 X_ATTRIBUTE10,
721 X_ATTRIBUTE11,
722 X_ATTRIBUTE12,
723 X_ATTRIBUTE13,
724 X_ATTRIBUTE14,
725 X_ATTRIBUTE15,
726 X_ATTRIBUTE16,
727 X_ATTRIBUTE17,
728 X_ATTRIBUTE18,
729 X_ATTRIBUTE19,
730 X_ATTRIBUTE20,
731 X_INFORMATION1,
732 X_INFORMATION2,
733 X_INFORMATION3,
734 X_INFORMATION4,
735 X_INFORMATION5,
736 X_INFORMATION6,
737 X_INFORMATION7,
738 X_INFORMATION8,
739 X_INFORMATION9,
740 X_INFORMATION10,
741 X_INFORMATION11,
742 X_INFORMATION12,
743 X_INFORMATION13,
744 X_INFORMATION14,
745 X_INFORMATION15,
746 X_INFORMATION16,
747 X_INFORMATION17,
748 X_INFORMATION18,
749 X_INFORMATION19,
750 X_INFORMATION20,
751 X_INFORMATION21,
752 X_INFORMATION22,
756 X_INFORMATION26,
753 X_INFORMATION23,
754 X_INFORMATION24,
755 X_INFORMATION25,
757 X_INFORMATION27,
758 X_INFORMATION28,
759 X_INFORMATION29,
760 X_INFORMATION30,
761 X_INFORMATION_CATEGORY,
762 X_QUAL_FRAMEWORK_ID,
763 X_QUALIFICATION_TYPE,
764 X_CREDIT_TYPE,
765 X_CREDITS,
766 X_LEVEL_TYPE,
767 X_LEVEL_NUMBER,
768 X_FIELD,
769 X_SUB_FIELD,
770 X_PROVIDER,
771 X_QA_ORGANIZATION,
772 X_OBJECT_VERSION_NUMBER,
773 X_USER_NAME,
774 X_CREATION_DATE,
775 X_CREATED_BY,
776 f_ludate,
777 f_luby,
778 0 );
779 end;
780 end LOAD_ROW;
781
782 procedure TRANSLATE_ROW (
783 X_NAME IN VARCHAR2,
784 X_USER_NAME in varchar2,
785 X_OWNER in VARCHAR2,
786 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
787 X_CUSTOM_MODE IN VARCHAR2 default null
788 ) IS
789
790 X_QUALIFICATION_TYPE_ID NUMBER;
791 X_CREATION_DATE DATE;
792 X_CREATED_BY NUMBER;
793 -- X_LAST_UPDATE_DATE DATE;
794 X_LAST_UPDATED_BY NUMBER;
795 X_LAST_UPDATE_LOGIN NUMBER;
796 f_luby number; -- entity owner in file
797 f_ludate date; -- entity update date in file
798 db_luby number; -- entity owner in db
799 db_ludate date; -- entity update date in db
800
801 begin
802
803 key_to_id( X_USER_NAME,X_QUALIFICATION_TYPE_ID);
804 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
805 /*
806 OWNER_TO_WHO (
807 X_OWNER,
808 X_CREATION_DATE,
809 X_CREATED_BY,
810 X_LAST_UPDATE_DATE,
811 X_LAST_UPDATED_BY,
812 X_LAST_UPDATE_LOGIN
813 );*/
814
815 -- Translate owner to file_last_updated_by
816 f_luby := fnd_load_util.owner_id(x_owner);
817
818 -- Translate char last_update_date to date
819 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
820 select LAST_UPDATED_BY, LAST_UPDATE_DATE
821 into db_luby, db_ludate
822 from PER_QUALIFICATION_TYPES_TL
823 where QUALIFICATION_TYPE_ID = TO_NUMBER(X_QUALIFICATION_TYPE_ID)
824 and LANGUAGE=userenv('LANG');
825
826 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
827 db_ludate,X_CUSTOM_MODE)) then
828
829 update per_qualification_types_tl
830 set NAME = X_USER_NAME,
831 LAST_UPDATE_DATE = db_ludate,
832 LAST_UPDATED_BY = db_luby,
833 LAST_UPDATE_LOGIN = 0,
834 SOURCE_LANG = userenv('LANG')
835 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID
836 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
837 end if;
838 end TRANSLATE_ROW;
839
840 procedure delete_row(
841 X_QUALIFICATION_TYPE_ID IN NUMBER
842 ) is
843 begin
844
845 delete from PER_QUALIFICATION_TYPES_TL
846 where qualification_type_id =X_QUALIFICATION_TYPE_ID;
847
848 end delete_row;
849
850 procedure ADD_LANGUAGE
851 is
852 begin
853 -- process PER_QUALIFICATION_TYPES_TL table
854 delete from PER_QUALIFICATION_TYPES_TL T
855 where not exists
856 (select NULL
857 from PER_QUALIFICATION_TYPES B
858 where B.QUALIFICATION_TYPE_ID = T.QUALIFICATION_TYPE_ID
859 );
860
861 update PER_QUALIFICATION_TYPES_TL T set (
862 NAME
863 ) = (select
864 B.NAME
865 from PER_QUALIFICATION_TYPES_TL B
866 where B.QUALIFICATION_TYPE_ID = T.QUALIFICATION_TYPE_ID
867 and B.LANGUAGE = T.SOURCE_LANG)
868 where (
869 T.QUALIFICATION_TYPE_ID,
870 T.LANGUAGE
871 ) in (select
872 SUBT.QUALIFICATION_TYPE_ID,
873 SUBT.LANGUAGE
874 from PER_QUALIFICATION_TYPES_TL SUBB, PER_QUALIFICATION_TYPES_TL SUBT
875 where SUBB.QUALIFICATION_TYPE_ID = SUBT.QUALIFICATION_TYPE_ID
876 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
877 and (SUBB.NAME <> SUBT.NAME
878 ));
879
880 insert into PER_QUALIFICATION_TYPES_TL (
881 QUALIFICATION_TYPE_ID,
882 NAME,
883 LAST_UPDATE_DATE,
884 LAST_UPDATED_BY,
885 LAST_UPDATE_LOGIN,
886 CREATED_BY,
887 CREATION_DATE,
888 LANGUAGE,
889 SOURCE_LANG
890 ) select
891 B.QUALIFICATION_TYPE_ID,
892 B.NAME,
893 B.LAST_UPDATE_DATE,
894 B.LAST_UPDATED_BY,
895 B.LAST_UPDATE_LOGIN,
896 B.CREATED_BY,
897 B.CREATION_DATE,
898 L.LANGUAGE_CODE,
899 B.SOURCE_LANG
900 from PER_QUALIFICATION_TYPES_TL B, FND_LANGUAGES L
901 where L.INSTALLED_FLAG in ('I', 'B')
902 and B.LANGUAGE = userenv('LANG')
903 and not exists
904 (select NULL
905 from PER_QUALIFICATION_TYPES_TL T
906 where T.QUALIFICATION_TYPE_ID = B.QUALIFICATION_TYPE_ID
907 and T.LANGUAGE = L.LANGUAGE_CODE);
908
909 end ADD_LANGUAGE;
910
911 End per_qualification_types_pkg;