[Home] [Help]
PACKAGE BODY: APPS.PER_QUALIFICATION_TYPES_PKG
Source
1 package body per_qualification_types_pkg as
2 /* $Header: pequatyp.pkb 120.2 2006/06/27 12:24:26 bshukla noship $*/
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 insert into PER_QUALIFICATION_TYPES_TL
284 (
285 QUALIFICATION_TYPE_ID,
286 NAME,
287 LAST_UPDATE_DATE,
288 LAST_UPDATED_BY,
289 LAST_UPDATE_LOGIN,
290 CREATED_BY,
291 CREATION_DATE,
292 LANGUAGE,
293 SOURCE_LANG
294 ) select
295 X_QUALIFICATION_TYPE_ID,
296 X_USER_NAME,
297 X_LAST_UPDATE_DATE,
298 X_LAST_UPDATED_BY,
299 X_LAST_UPDATE_LOGIN,
300 X_CREATED_BY,
301 X_CREATION_DATE,
302 L.LANGUAGE_CODE,
303 userenv('LANG')
304 from FND_LANGUAGES L
305 where L.INSTALLED_FLAG in ('I', 'B')
306 and not exists
307 (select NULL
308 from PER_QUALIFICATION_TYPES_TL T
309 where T.QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID
310 AND T.LANGUAGE = L.LANGUAGE_CODE);
311
312 open c;
313 fetch c into X_ROWID;
314 if (c%notfound) then
315 close c;
316 raise no_data_found;
317 end if;
318 close c;
319
320 end INSERT_ROW;
321
322
323 procedure update_row(
324 X_QUALIFICATION_TYPE_ID IN NUMBER,
325 X_NAME IN VARCHAR2,
326 X_CATEGORY IN VARCHAR2,
327 X_RANK in number,
328 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
329 X_ATTRIBUTE1 IN VARCHAR2,
330 X_ATTRIBUTE2 IN VARCHAR2,
331 X_ATTRIBUTE3 IN VARCHAR2,
332 X_ATTRIBUTE4 IN VARCHAR2,
333 X_ATTRIBUTE5 IN VARCHAR2,
334 X_ATTRIBUTE6 IN VARCHAR2,
335 X_ATTRIBUTE7 IN VARCHAR2,
336 X_ATTRIBUTE8 IN VARCHAR2,
337 X_ATTRIBUTE9 IN VARCHAR2,
338 X_ATTRIBUTE10 IN VARCHAR2,
339 X_ATTRIBUTE11 IN VARCHAR2,
340 X_ATTRIBUTE12 IN VARCHAR2,
341 X_ATTRIBUTE13 IN VARCHAR2,
342 X_ATTRIBUTE14 IN VARCHAR2,
343 X_ATTRIBUTE15 IN VARCHAR2,
344 X_ATTRIBUTE16 IN VARCHAR2,
345 X_ATTRIBUTE17 IN VARCHAR2,
346 X_ATTRIBUTE18 IN VARCHAR2,
347 X_ATTRIBUTE19 IN VARCHAR2,
348 X_ATTRIBUTE20 IN VARCHAR2,
349 X_INFORMATION1 IN VARCHAR2,
350 X_INFORMATION2 IN VARCHAR2,
351 X_INFORMATION3 IN VARCHAR2,
352 X_INFORMATION4 IN VARCHAR2,
353 X_INFORMATION5 IN VARCHAR2,
354 X_INFORMATION6 IN VARCHAR2,
355 X_INFORMATION7 IN VARCHAR2,
356 X_INFORMATION8 IN VARCHAR2,
357 X_INFORMATION9 IN VARCHAR2,
358 X_INFORMATION10 IN VARCHAR2,
359 X_INFORMATION11 IN VARCHAR2,
360 X_INFORMATION12 IN VARCHAR2,
361 X_INFORMATION13 IN VARCHAR2,
362 X_INFORMATION14 IN VARCHAR2,
363 X_INFORMATION15 IN VARCHAR2,
364 X_INFORMATION16 IN VARCHAR2,
365 X_INFORMATION17 IN VARCHAR2,
366 X_INFORMATION18 IN VARCHAR2,
367 X_INFORMATION19 IN VARCHAR2,
368 X_INFORMATION20 IN VARCHAR2,
369 X_INFORMATION21 IN VARCHAR2,
370 X_INFORMATION22 IN VARCHAR2,
371 X_INFORMATION23 IN VARCHAR2,
372 X_INFORMATION24 IN VARCHAR2,
373 X_INFORMATION25 IN VARCHAR2,
374 X_INFORMATION26 IN VARCHAR2,
375 X_INFORMATION27 IN VARCHAR2,
376 X_INFORMATION28 IN VARCHAR2,
377 X_INFORMATION29 IN VARCHAR2,
378 X_INFORMATION30 IN VARCHAR2,
379 X_INFORMATION_CATEGORY IN VARCHAR2,
380 X_QUAL_FRAMEWORK_ID in number,
381 X_QUALIFICATION_TYPE in varchar2,
382 X_CREDIT_TYPE in varchar2,
383 X_CREDITS in number,
384 X_LEVEL_TYPE in varchar2,
385 X_LEVEL_NUMBER in varchar2,
386 X_FIELD IN VARCHAR2,
387 X_SUB_FIELD IN VARCHAR2,
388 X_PROVIDER IN VARCHAR2,
389 X_QA_ORGANIZATION IN VARCHAR2,
390 X_OBJECT_VERSION_NUMBER IN VARCHAR2,
391 X_USER_NAME IN VARCHAR2,
392 X_CREATION_DATE in DATE,
393 X_CREATED_BY in NUMBER,
394 X_LAST_UPDATE_DATE in DATE,
395 X_LAST_UPDATED_BY in NUMBER,
396 X_LAST_UPDATE_LOGIN in NUMBER
397 ) is
398
399
400 begin
401
402 update per_qualification_types
403 set NAME = X_NAME,
404 CATEGORY = X_CATEGORY,
405 RANK = X_RANK,
406 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
407 ATTRIBUTE1 = X_ATTRIBUTE1,
408 ATTRIBUTE2 = X_ATTRIBUTE2,
409 ATTRIBUTE3 = X_ATTRIBUTE3,
410 ATTRIBUTE4 = X_ATTRIBUTE4,
411 ATTRIBUTE5 = X_ATTRIBUTE5,
412 ATTRIBUTE6 = X_ATTRIBUTE6,
413 ATTRIBUTE7 = X_ATTRIBUTE7,
414 ATTRIBUTE8 = X_ATTRIBUTE8,
415 ATTRIBUTE9 = X_ATTRIBUTE9,
416 ATTRIBUTE10 = X_ATTRIBUTE10,
417 ATTRIBUTE11 = X_ATTRIBUTE11,
418 ATTRIBUTE12 = X_ATTRIBUTE12,
419 ATTRIBUTE13 = X_ATTRIBUTE13,
420 ATTRIBUTE14 = X_ATTRIBUTE14,
421 ATTRIBUTE15 = X_ATTRIBUTE15,
422 ATTRIBUTE16 = X_ATTRIBUTE16,
423 ATTRIBUTE17 = X_ATTRIBUTE17,
424 ATTRIBUTE18 = X_ATTRIBUTE18,
425 ATTRIBUTE19 = X_ATTRIBUTE19,
426 ATTRIBUTE20 = X_ATTRIBUTE20,
427 INFORMATION1 = X_INFORMATION1,
428 INFORMATION2 = X_INFORMATION2,
429 INFORMATION3 = X_INFORMATION3,
430 INFORMATION4 = X_INFORMATION4,
431 INFORMATION5 = X_INFORMATION5,
432 INFORMATION6 = X_INFORMATION6,
433 INFORMATION7 = X_INFORMATION7,
434 INFORMATION8 = X_INFORMATION8,
435 INFORMATION9 = X_INFORMATION9,
436 INFORMATION10 = X_INFORMATION10,
437 INFORMATION11 = X_INFORMATION11,
438 INFORMATION12 = X_INFORMATION12,
439 INFORMATION13 = X_INFORMATION13,
440 INFORMATION14 = X_INFORMATION14,
441 INFORMATION15 = X_INFORMATION15,
442 INFORMATION16 = X_INFORMATION16,
443 INFORMATION17 = X_INFORMATION17,
444 INFORMATION18 = X_INFORMATION18,
445 INFORMATION19 = X_INFORMATION19,
446 INFORMATION20 = X_INFORMATION20,
447 INFORMATION21 = X_INFORMATION21,
448 INFORMATION22 = X_INFORMATION22,
449 INFORMATION23 = X_INFORMATION23,
450 INFORMATION24 = X_INFORMATION24,
451 INFORMATION25 = X_INFORMATION25,
452 INFORMATION26 = X_INFORMATION26,
453 INFORMATION27 = X_INFORMATION27,
454 INFORMATION28 = X_INFORMATION28,
455 INFORMATION29 = X_INFORMATION29,
456 INFORMATION30 = X_INFORMATION30,
457 INFORMATION_CATEGORY = X_INFORMATION_CATEGORY,
458 QUAL_FRAMEWORK_ID = X_QUAL_FRAMEWORK_ID,
459 QUALIFICATION_TYPE = X_QUALIFICATION_TYPE,
460 CREDIT_TYPE = X_CREDIT_TYPE,
461 CREDITS = X_CREDITS,
462 LEVEL_TYPE = X_LEVEL_TYPE,
463 LEVEL_NUMBER = X_LEVEL_NUMBER,
464 FIELD = X_FIELD,
465 SUB_FIELD = X_SUB_FIELD,
466 PROVIDER = X_PROVIDER,
467 QA_ORGANIZATION = X_QA_ORGANIZATION,
468 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
469 CREATION_DATE = X_CREATION_DATE,
470 CREATED_BY = X_CREATED_BY,
471 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
472 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
473 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
474 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID;
475
476 if (sql%notfound) then
477 raise no_data_found;
478 end if;
479
480 update per_qualification_types_tl
481 set NAME = X_USER_NAME,
482 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
483 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
484 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
485 SOURCE_LANG = userenv('LANG')
486 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID
487 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
488
489 if (sql%notfound) then
490 raise no_data_found;
491 end if;
492
493 end update_row;
494
495 procedure load_row(X_NAME IN VARCHAR2,
496 X_CATEGORY IN VARCHAR2,
497 X_RANK in number,
498 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
499 X_ATTRIBUTE1 IN VARCHAR2,
500 X_ATTRIBUTE2 IN VARCHAR2,
501 X_ATTRIBUTE3 IN VARCHAR2,
502 X_ATTRIBUTE4 IN VARCHAR2,
503 X_ATTRIBUTE5 IN VARCHAR2,
504 X_ATTRIBUTE6 IN VARCHAR2,
505 X_ATTRIBUTE7 IN VARCHAR2,
506 X_ATTRIBUTE8 IN VARCHAR2,
507 X_ATTRIBUTE9 IN VARCHAR2,
508 X_ATTRIBUTE10 IN VARCHAR2,
509 X_ATTRIBUTE11 IN VARCHAR2,
510 X_ATTRIBUTE12 IN VARCHAR2,
511 X_ATTRIBUTE13 IN VARCHAR2,
512 X_ATTRIBUTE14 IN VARCHAR2,
513 X_ATTRIBUTE15 IN VARCHAR2,
514 X_ATTRIBUTE16 IN VARCHAR2,
515 X_ATTRIBUTE17 IN VARCHAR2,
516 X_ATTRIBUTE18 IN VARCHAR2,
517 X_ATTRIBUTE19 IN VARCHAR2,
518 X_ATTRIBUTE20 IN VARCHAR2,
519 X_INFORMATION1 IN VARCHAR2,
520 X_INFORMATION2 IN VARCHAR2,
521 X_INFORMATION3 IN VARCHAR2,
522 X_INFORMATION4 IN VARCHAR2,
523 X_INFORMATION5 IN VARCHAR2,
524 X_INFORMATION6 IN VARCHAR2,
525 X_INFORMATION7 IN VARCHAR2,
526 X_INFORMATION8 IN VARCHAR2,
527 X_INFORMATION9 IN VARCHAR2,
528 X_INFORMATION10 IN VARCHAR2,
529 X_INFORMATION11 IN VARCHAR2,
530 X_INFORMATION12 IN VARCHAR2,
531 X_INFORMATION13 IN VARCHAR2,
532 X_INFORMATION14 IN VARCHAR2,
533 X_INFORMATION15 IN VARCHAR2,
537 X_INFORMATION19 IN VARCHAR2,
534 X_INFORMATION16 IN VARCHAR2,
535 X_INFORMATION17 IN VARCHAR2,
536 X_INFORMATION18 IN VARCHAR2,
538 X_INFORMATION20 IN VARCHAR2,
539 X_INFORMATION21 IN VARCHAR2,
540 X_INFORMATION22 IN VARCHAR2,
541 X_INFORMATION23 IN VARCHAR2,
542 X_INFORMATION24 IN VARCHAR2,
543 X_INFORMATION25 IN VARCHAR2,
544 X_INFORMATION26 IN VARCHAR2,
545 X_INFORMATION27 IN VARCHAR2,
546 X_INFORMATION28 IN VARCHAR2,
547 X_INFORMATION29 IN VARCHAR2,
548 X_INFORMATION30 IN VARCHAR2,
549 X_INFORMATION_CATEGORY IN VARCHAR2,
550 X_QUAL_FRAMEWORK_ID in number,
551 X_QUALIFICATION_TYPE in varchar2,
552 X_CREDIT_TYPE in varchar2,
553 X_CREDITS in number,
554 X_LEVEL_TYPE in varchar2,
555 X_LEVEL_NUMBER in varchar2,
556 X_FIELD IN VARCHAR2,
557 X_SUB_FIELD IN VARCHAR2,
558 X_PROVIDER IN VARCHAR2,
559 X_QA_ORGANIZATION IN VARCHAR2,
560 X_OBJECT_VERSION_NUMBER IN VARCHAR2,
561 X_USER_NAME IN VARCHAR2,
562 X_OWNER in VARCHAR2,
563 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
564 X_CUSTOM_MODE IN VARCHAR2 default null) IS
565
566 X_ROWID ROWID;
567 X_QUALIFICATION_TYPE_ID NUMBER;
568 X_CREATION_DATE DATE :=sysdate;
569 X_CREATED_BY NUMBER;
570 -- X_LAST_UPDATE_DATE DATE;
571 X_LAST_UPDATED_BY NUMBER;
572 X_LAST_UPDATE_LOGIN NUMBER;
573 f_luby number; -- entity owner in file
574 f_ludate date; -- entity update date in file
575 db_luby number; -- entity owner in db
576 db_ludate date; -- entity update date in db
577
578 begin
579
580 if X_OWNER = 'SEED' then
581 X_CREATED_BY := 1;
582 else
583 X_CREATED_BY := 0;
584 end if;
585
586 key_to_id( X_NAME,X_QUALIFICATION_TYPE_ID);
587
588 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
589 /* OWNER_TO_WHO (
590 X_OWNER,
591 X_CREATION_DATE,
592 X_CREATED_BY,
593 X_LAST_UPDATE_DATE,
594 X_LAST_UPDATED_BY,
595 X_LAST_UPDATE_LOGIN
596 ); */
597
598 begin
599 f_luby := fnd_load_util.owner_id(X_OWNER);
600 -- Translate char last_update_date to date
601 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
602 select LAST_UPDATED_BY, LAST_UPDATE_DATE
603 into db_luby, db_ludate
604 from PER_QUALIFICATION_TYPES
605 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID;
606
607 -- Test for customization and version
608 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
609 db_ludate, X_CUSTOM_MODE)) then
610
611
612 UPDATE_ROW (
613 X_QUALIFICATION_TYPE_ID,
614 X_NAME,
615 X_CATEGORY,
616 X_RANK,
617 X_ATTRIBUTE_CATEGORY,
618 X_ATTRIBUTE1,
619 X_ATTRIBUTE2,
620 X_ATTRIBUTE3,
621 X_ATTRIBUTE4,
622 X_ATTRIBUTE5,
623 X_ATTRIBUTE6,
624 X_ATTRIBUTE7,
625 X_ATTRIBUTE8,
626 X_ATTRIBUTE9,
627 X_ATTRIBUTE10,
628 X_ATTRIBUTE11,
629 X_ATTRIBUTE12,
630 X_ATTRIBUTE13,
631 X_ATTRIBUTE14,
632 X_ATTRIBUTE15,
633 X_ATTRIBUTE16,
634 X_ATTRIBUTE17,
635 X_ATTRIBUTE18,
636 X_ATTRIBUTE19,
637 X_ATTRIBUTE20,
638 X_INFORMATION1,
639 X_INFORMATION2,
640 X_INFORMATION3,
641 X_INFORMATION4,
642 X_INFORMATION5,
643 X_INFORMATION6,
644 X_INFORMATION7,
645 X_INFORMATION8,
646 X_INFORMATION9,
647 X_INFORMATION10,
648 X_INFORMATION11,
649 X_INFORMATION12,
650 X_INFORMATION13,
651 X_INFORMATION14,
652 X_INFORMATION15,
653 X_INFORMATION16,
654 X_INFORMATION17,
655 X_INFORMATION18,
656 X_INFORMATION19,
657 X_INFORMATION20,
658 X_INFORMATION21,
659 X_INFORMATION22,
660 X_INFORMATION23,
661 X_INFORMATION24,
662 X_INFORMATION25,
663 X_INFORMATION26,
664 X_INFORMATION27,
665 X_INFORMATION28,
666 X_INFORMATION29,
667 X_INFORMATION30,
668 X_INFORMATION_CATEGORY,
669 X_QUAL_FRAMEWORK_ID,
670 X_QUALIFICATION_TYPE,
671 X_CREDIT_TYPE,
672 X_CREDITS,
673 X_LEVEL_TYPE,
674 X_LEVEL_NUMBER,
675 X_FIELD,
676 X_SUB_FIELD,
677 X_PROVIDER,
678 X_QA_ORGANIZATION,
679 X_OBJECT_VERSION_NUMBER,
680 X_USER_NAME,
681 X_CREATION_DATE,
682 X_CREATED_BY,
683 f_ludate,
684 f_luby,
685 0 );
686 end if;
687 exception
688 when no_data_found then
689 INSERT_ROW( X_ROWID,
690 X_QUALIFICATION_TYPE_ID,
691 X_NAME,
692 X_CATEGORY,
693 X_RANK,
694 X_ATTRIBUTE_CATEGORY,
695 X_ATTRIBUTE1,
696 X_ATTRIBUTE2,
697 X_ATTRIBUTE3,
698 X_ATTRIBUTE4,
699 X_ATTRIBUTE5,
700 X_ATTRIBUTE6,
701 X_ATTRIBUTE7,
702 X_ATTRIBUTE8,
703 X_ATTRIBUTE9,
704 X_ATTRIBUTE10,
705 X_ATTRIBUTE11,
706 X_ATTRIBUTE12,
707 X_ATTRIBUTE13,
708 X_ATTRIBUTE14,
709 X_ATTRIBUTE15,
710 X_ATTRIBUTE16,
711 X_ATTRIBUTE17,
712 X_ATTRIBUTE18,
713 X_ATTRIBUTE19,
714 X_ATTRIBUTE20,
715 X_INFORMATION1,
716 X_INFORMATION2,
717 X_INFORMATION3,
718 X_INFORMATION4,
719 X_INFORMATION5,
720 X_INFORMATION6,
721 X_INFORMATION7,
722 X_INFORMATION8,
723 X_INFORMATION9,
727 X_INFORMATION13,
724 X_INFORMATION10,
725 X_INFORMATION11,
726 X_INFORMATION12,
728 X_INFORMATION14,
729 X_INFORMATION15,
730 X_INFORMATION16,
731 X_INFORMATION17,
732 X_INFORMATION18,
733 X_INFORMATION19,
734 X_INFORMATION20,
735 X_INFORMATION21,
736 X_INFORMATION22,
737 X_INFORMATION23,
738 X_INFORMATION24,
739 X_INFORMATION25,
740 X_INFORMATION26,
741 X_INFORMATION27,
742 X_INFORMATION28,
743 X_INFORMATION29,
744 X_INFORMATION30,
745 X_INFORMATION_CATEGORY,
746 X_QUAL_FRAMEWORK_ID,
747 X_QUALIFICATION_TYPE,
748 X_CREDIT_TYPE,
749 X_CREDITS,
750 X_LEVEL_TYPE,
751 X_LEVEL_NUMBER,
752 X_FIELD,
753 X_SUB_FIELD,
754 X_PROVIDER,
755 X_QA_ORGANIZATION,
756 X_OBJECT_VERSION_NUMBER,
757 X_USER_NAME,
758 X_CREATION_DATE,
759 X_CREATED_BY,
760 f_ludate,
761 f_luby,
762 0 );
763 end;
764 end LOAD_ROW;
765
766 procedure TRANSLATE_ROW (
767 X_NAME IN VARCHAR2,
768 X_USER_NAME in varchar2,
769 X_OWNER in VARCHAR2,
770 X_LAST_UPDATE_DATE IN VARCHAR2 default sysdate,
771 X_CUSTOM_MODE IN VARCHAR2 default null
772 ) IS
773
774 X_QUALIFICATION_TYPE_ID NUMBER;
775 X_CREATION_DATE DATE;
776 X_CREATED_BY NUMBER;
777 -- X_LAST_UPDATE_DATE DATE;
778 X_LAST_UPDATED_BY NUMBER;
779 X_LAST_UPDATE_LOGIN NUMBER;
780 f_luby number; -- entity owner in file
781 f_ludate date; -- entity update date in file
782 db_luby number; -- entity owner in db
783 db_ludate date; -- entity update date in db
784
785 begin
786
787 key_to_id( X_USER_NAME,X_QUALIFICATION_TYPE_ID);
788 -- Commenting this as X_LAST_UPDATE_DATE is now an Input parameter.
789 /*
790 OWNER_TO_WHO (
791 X_OWNER,
792 X_CREATION_DATE,
793 X_CREATED_BY,
794 X_LAST_UPDATE_DATE,
795 X_LAST_UPDATED_BY,
796 X_LAST_UPDATE_LOGIN
797 );*/
798
799 -- Translate owner to file_last_updated_by
800 f_luby := fnd_load_util.owner_id(x_owner);
801
802 -- Translate char last_update_date to date
803 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
804 select LAST_UPDATED_BY, LAST_UPDATE_DATE
805 into db_luby, db_ludate
806 from PER_QUALIFICATION_TYPES_TL
807 where QUALIFICATION_TYPE_ID = TO_NUMBER(X_QUALIFICATION_TYPE_ID)
808 and LANGUAGE=userenv('LANG');
809
810 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
811 db_ludate,X_CUSTOM_MODE)) then
812
813 update per_qualification_types_tl
814 set NAME = X_USER_NAME,
815 LAST_UPDATE_DATE = db_ludate,
816 LAST_UPDATED_BY = db_luby,
817 LAST_UPDATE_LOGIN = 0,
818 SOURCE_LANG = userenv('LANG')
819 where QUALIFICATION_TYPE_ID = X_QUALIFICATION_TYPE_ID
820 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
821 end if;
822 end TRANSLATE_ROW;
823
824 procedure delete_row(
825 X_QUALIFICATION_TYPE_ID IN NUMBER
826 ) is
827 begin
828
829 delete from PER_QUALIFICATION_TYPES_TL
830 where qualification_type_id =X_QUALIFICATION_TYPE_ID;
831
832 end delete_row;
833
834 procedure ADD_LANGUAGE
835 is
836 begin
837 -- process PER_QUALIFICATION_TYPES_TL table
838 delete from PER_QUALIFICATION_TYPES_TL T
839 where not exists
840 (select NULL
841 from PER_QUALIFICATION_TYPES B
842 where B.QUALIFICATION_TYPE_ID = T.QUALIFICATION_TYPE_ID
843 );
844
845 update PER_QUALIFICATION_TYPES_TL T set (
846 NAME
847 ) = (select
848 B.NAME
849 from PER_QUALIFICATION_TYPES_TL B
850 where B.QUALIFICATION_TYPE_ID = T.QUALIFICATION_TYPE_ID
851 and B.LANGUAGE = T.SOURCE_LANG)
852 where (
853 T.QUALIFICATION_TYPE_ID,
854 T.LANGUAGE
855 ) in (select
856 SUBT.QUALIFICATION_TYPE_ID,
857 SUBT.LANGUAGE
858 from PER_QUALIFICATION_TYPES_TL SUBB, PER_QUALIFICATION_TYPES_TL SUBT
859 where SUBB.QUALIFICATION_TYPE_ID = SUBT.QUALIFICATION_TYPE_ID
860 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
861 and (SUBB.NAME <> SUBT.NAME
862 ));
863
864 insert into PER_QUALIFICATION_TYPES_TL (
865 QUALIFICATION_TYPE_ID,
866 NAME,
867 LAST_UPDATE_DATE,
868 LAST_UPDATED_BY,
869 LAST_UPDATE_LOGIN,
870 CREATED_BY,
871 CREATION_DATE,
872 LANGUAGE,
873 SOURCE_LANG
874 ) select
875 B.QUALIFICATION_TYPE_ID,
876 B.NAME,
877 B.LAST_UPDATE_DATE,
878 B.LAST_UPDATED_BY,
879 B.LAST_UPDATE_LOGIN,
880 B.CREATED_BY,
881 B.CREATION_DATE,
882 L.LANGUAGE_CODE,
883 B.SOURCE_LANG
884 from PER_QUALIFICATION_TYPES_TL B, FND_LANGUAGES L
885 where L.INSTALLED_FLAG in ('I', 'B')
886 and B.LANGUAGE = userenv('LANG')
887 and not exists
888 (select NULL
889 from PER_QUALIFICATION_TYPES_TL T
890 where T.QUALIFICATION_TYPE_ID = B.QUALIFICATION_TYPE_ID
891 and T.LANGUAGE = L.LANGUAGE_CODE);
892
893 end ADD_LANGUAGE;
894
895 End per_qualification_types_pkg;