1 PACKAGE BODY igs_uc_mv_imp_degsubj AS
2 /* $Header: IGSUC36B.pls 120.1 2006/02/08 19:56:25 anwest noship $*/
3
4 /*===============================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA |
6 | All rights reserved. |
7 +===============================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL spec for package: IGS_UC_MV_IMP_DEGSUBJ |
11 | |
12 | NOTES |
13 | This is a part of Concurrent Requeset Set which updates the main table |
14 | with the Degree Subjects data for GTTR uploaded into the temporary table |
15 | from flat file by the 1st (SQL Ldr part of this concurrent request set. |
16 | |
17 | HISTORY |
18 | Who When What |
19 | rgangara 05-Nov-2002 Create Version as part of Small systems support|
20 | Enhancement (UCFD02). Bug 2643048 |
21 | pmarada 13-Jun-2003 Removed the igs_uc_refcodetype table references |
22 | and removed the hercules profiles references as |
23 | per the UCFD203-Multiple cycles, bug 2669208 |
24 | anwest 18-Jan-06 Bug# 4950285 R12 Disable OSS Mandate |
25 *==============================================================================*/
26
27
28 PROCEDURE Imp_Degsubject_codes (
29 errbuf OUT NOCOPY VARCHAR2,
30 retcode OUT NOCOPY NUMBER
31 ) IS
32 /*
33 || Created By : rgangara
34 || Created On : 05-Nov-2002
35 || Purpose : This is main Procedure which will transfer the Degree Subjects (GTTR) data
36 || from the temporary table to the UCAS Reference codes table.
37 || Known limitations, enhancements or remarks :
38 || Change History :
39 || Who When What
40 || anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
41 || (reverse chronological order - newest change first)
42 */
43
44 -- Declare all the local variables, cursors
45
46 -- Cursor to pick records from temp table to be processed.
47 CURSOR Degsubj_proc_cur IS
48 SELECT deg_subj_code,
49 code_text
50 FROM igs_uc_mv_degsb_int;
51
52 -- Cursor to check for existence of 'DS' in Ref code types table.
53 CURSOR refcode_types_cur IS
54 SELECT lookup_code
55 FROM igs_lookup_values
56 WHERE lookup_type = 'IGS_UC_CODE_TYPES'
57 AND lookup_code = 'DS';
58
59 -- Cursor to check whether the CODE already exists in ref codes table.
60 CURSOR refcodes_cur (p_code igs_uc_ref_codes.code%TYPE) IS
61 SELECT rowid, code
62 FROM igs_uc_ref_codes
63 WHERE code_type = 'DS'
64 AND code = p_code;
65
66 refcode_types_rec refcode_types_cur%ROWTYPE;
67 refcodes_rec refcodes_cur%ROWTYPE;
68 l_rowid VARCHAR2(26) := NULL;
69 l_success_cnt NUMBER := 0;
70
71 BEGIN
72
73 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
74 IGS_GE_GEN_003.SET_ORG_ID;
75
76 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
77 retcode := 0;
78
79 -- check for 'DS' lookup code type exists in lookups or not.
80 OPEN refcode_types_cur;
81 FETCH refcode_types_cur INTO refcode_types_rec;
82 IF refcode_types_cur%NOTFOUND THEN
83 FND_MESSAGE.SET_NAME('IGS', 'IGS_UC_REFCODETYP_NOT_EXISTS');
84 FND_MESSAGE.SET_TOKEN('CODE_TYPE','DS');
85 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
86 RETCODE := 2;
87 RETURN;
88 END IF;
89 CLOSE refcode_types_cur;
90
91 -- Insert Degree Subjects data into Ref Codes table.
92 FOR degsubj_proc_rec IN degsubj_proc_cur
93 LOOP
94 -- check to see whether the code is NULL. If NULL then ignore such records.
95 IF degsubj_proc_rec.deg_subj_code is NOT NULL THEN
96
97 -- Check to see whether the current Code already exists. If not found then insert
98 OPEN refcodes_cur(degsubj_proc_rec.deg_subj_code);
99 FETCH refcodes_cur INTO refcodes_rec;
100 IF refcodes_cur%NOTFOUND THEN
101 l_rowid := NULL;
102
103 --Call the TBH of the Reference codes table to insert a new Degree Subject.
104 igs_uc_ref_codes_pkg.Insert_Row (
105 X_ROWID => l_rowid,
106 X_CODE_TYPE => 'DS',
107 X_CODE => degsubj_proc_rec.deg_subj_code,
108 X_CODE_TEXT => degsubj_proc_rec.code_text,
109 X_IMPORTED => 'Y',
110 X_MODE => 'R'
111 );
112 l_success_cnt := l_success_cnt + 1;
113
114 ELSE
115 l_rowid := refcodes_rec.rowid;
116 --Call the TBH of the Reference codes table to update a new Degree Subject.
117 igs_uc_ref_codes_pkg.Update_Row (
118 X_ROWID => l_rowid,
119 X_CODE_TYPE => 'DS',
120 X_CODE => degsubj_proc_rec.deg_subj_code,
121 X_CODE_TEXT => degsubj_proc_rec.code_text,
122 X_IMPORTED => 'Y',
123 X_MODE => 'R'
124 );
125 l_success_cnt := l_success_cnt + 1;
126
127 END IF;
128
129 CLOSE refcodes_cur;
130 END IF;
131
132 END LOOP;
133
134 -- Print Number of records successfully transferred
135 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
136 FND_MESSAGE.SET_NAME('IGS', 'IGS_UC_MV_LOAD_SUCCESS');
137 FND_MESSAGE.SET_TOKEN('CNT', l_success_cnt);
138 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
139
140 -- commit the data;
141 COMMIT;
142 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
143
144 EXCEPTION
145 WHEN app_exception.record_lock_exception THEN
146 ROLLBACK;
147 retcode := 2;
148 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
149 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
150
151 WHEN OTHERS THEN
152 ROLLBACK;
153 RETCODE := 2;
154 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
155 FND_MESSAGE.SET_TOKEN('NAME','IGS_UC_MV_IMP_DEGSUBJ.IMP_DEGSUBJECT_CODES - '||SQLERRM);
156 ERRBUF := FND_MESSAGE.GET;
157 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
158 END Imp_Degsubject_codes;
159 END igs_uc_mv_imp_degsubj;
[Home] [Help]