[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_MV_IMP_INST
Source
1 PACKAGE BODY igs_uc_mv_imp_inst AS
2 /* $Header: IGSUC35B.pls 120.1 2006/02/08 19:56:06 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_INST |
11 | |
12 | NOTES |
13 | This is a part of Concurrent Requeset Set which updates the Insittutions |
14 | table with the Institutions data uploaded into the temporary table |
15 | from flat file by the 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. Enh Bug# 2643048 |
21 | pmarada 13-jun-2003 Obsoleted the datetimestamp column references from the |
22 | object, removed the profile references as per UCFD203 |
23 | Multiple cycle build, bug 2669208 |
24 | anwest 18-Jan-06 Bug# 4950285 R12 Disable OSS Mandate |
25 *==============================================================================*/
26
27
28 PROCEDURE Import_Inst_codes (
29 errbuf OUT NOCOPY VARCHAR2,
30 retcode OUT NOCOPY NUMBER ,
31 p_system_code IN VARCHAR2
32 ) IS
33 /*
34 || Created By : rgangara
35 || Created On : 05-Nov-2002
36 || Purpose : This is main Procedure which will transfer the Institutions data
37 || from the temporary table to the UCAS Institutions table.
38 || Known limitations, enhancements or remarks :
39 || Change History :
40 || Who When What
41 || anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
42 || (reverse chronological order - newest change first)
43 */
44
45 -- Declare all the local variables, cursors
46
47 -- Cursor to pick records from temp table to be processed.
48 CURSOR inst_int_cur IS
49 SELECT inst,
50 inst_code,
51 inst_name
52 FROM igs_uc_mv_inst_int;
53
54 -- Cursor to check whether the CODE already exists in Institutions table.
55 CURSOR instcodes_cur (p_inst igs_uc_com_inst.inst%TYPE) IS
56 SELECT rowid ,
57 inst ,
58 inst_code ,
59 inst_name ,
60 ucas ,
61 gttr ,
62 swas ,
63 nmas ,
64 imported
65 FROM igs_uc_com_inst
66 WHERE inst = p_inst;
67
68
69 instcodes_rec instcodes_cur%ROWTYPE;
70 l_rowid VARCHAR2(26) := NULL;
71 l_success_cnt NUMBER := 0;
72 l_gttr VARCHAR2(1);
73 l_ucas VARCHAR2(1);
74 l_nmas VARCHAR2(1);
75 l_swas VARCHAR2(1);
76
77 BEGIN
78
79 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
80 IGS_GE_GEN_003.SET_ORG_ID;
81
82 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
83 retcode := 0;
84
85 -- Check If profile Country Code is set to GB. If not, then close form.
86 IF NOT igs_uc_utils.is_ucas_hesa_enabled THEN
87 fnd_message.set_name('IGS','IGS_UC_HE_NOT_ENABLED');
88 fnd_file.put_line( fnd_file.log, fnd_message.get());
89 retcode := 2;
90 RETURN;
91 END IF;
92
93 -- Insert/Update Institutions data into UCAS Institutions table IGS_UC_COM_INST.
94 FOR inst_int_rec IN inst_int_cur
95 LOOP
96
97
98 -- check to see whether the code is NULL. If NULL then ignore such records.
99 IF inst_int_rec.inst is NOT NULL THEN
100 -- Check to see whether the current Code already exists. If not found then insert
101 OPEN instcodes_cur(inst_int_rec.inst);
102 FETCH instcodes_cur INTO instcodes_rec;
103 IF instcodes_cur%NOTFOUND THEN
104 l_rowid := NULL;
105 -- Initializing flags
106 l_gttr := 'N';
107 l_ucas := 'N';
108 l_nmas := 'N';
109 l_swas := 'N';
110
111 IF p_system_code = 'U' THEN
112 l_ucas := 'Y';
113 ELSIF p_system_code = 'G' THEN
114 l_gttr := 'Y';
115 ELSIF p_system_code = 'N' THEN
116 l_nmas := 'Y';
117 ELSIF p_system_code = 'S' THEN
118 l_swas := 'Y';
119 END IF;
120
121 fnd_message.set_name('IGS', 'IGS_UC_MV_INST_IMP');
122 fnd_message.set_token('IMPORT', 'INSERTING ');
123 fnd_message.set_token('INST', inst_int_rec.inst);
124 fnd_file.put_line( fnd_file.log, fnd_message.get());
125
126 --Call the TBH of the Reference codes table to insert a new Degree Subject.
127 igs_uc_com_inst_pkg.Insert_Row (
128 x_rowid => l_rowid,
129 x_inst => inst_int_rec.inst,
130 x_inst_code => inst_int_rec.inst_code,
131 x_inst_name => inst_int_rec.inst_name,
132 x_ucas => l_ucas,
133 x_gttr => l_gttr,
134 x_swas => l_swas,
135 x_nmas => l_nmas,
136 x_imported => 'Y',
137 x_mode => 'R'
138 );
139
140 l_success_cnt := l_success_cnt + 1;
141
142 ELSE
143 l_rowid := instcodes_rec.rowid;
144 -- Initializing flags with the current/existing values
145 l_ucas := instcodes_rec.ucas;
146 l_gttr := instcodes_rec.gttr;
147 l_nmas := instcodes_rec.nmas;
148 l_swas := instcodes_rec.swas;
149
150 -- updating the appropriate system flag as per the input parmaeter
151 IF p_system_code = 'U' THEN
152 l_ucas := 'Y';
153 ELSIF p_system_code = 'G' THEN
154 l_gttr := 'Y';
155 ELSIF p_system_code = 'N' THEN
156 l_nmas := 'Y';
157 ELSIF p_system_code = 'S' THEN
158 l_swas := 'Y';
159 END IF;
160
161 fnd_message.set_name('IGS', 'IGS_UC_MV_INST_IMP');
162 fnd_message.set_token('IMPORT', 'UPDATING ');
163 fnd_message.set_token('INST', instcodes_rec.inst);
164 fnd_file.put_line( fnd_file.log, fnd_message.get());
165
166 --Call the TBH of the Reference codes table to update a new Degree Subject.
167 igs_uc_com_inst_pkg.Update_Row (
168 x_rowid => l_rowid,
169 x_inst => instcodes_rec.inst,
170 x_inst_code => inst_int_rec.inst_code,
171 x_inst_name => inst_int_rec.inst_name,
172 x_ucas => l_ucas,
173 x_gttr => l_gttr,
174 x_swas => l_swas,
175 x_nmas => l_nmas,
176 x_imported => 'Y',
177 x_mode => 'R'
178 );
179
180 l_success_cnt := l_success_cnt + 1;
181
182 END IF;
183
184 CLOSE instcodes_cur;
185 END IF;
186
187 END LOOP;
188
189 -- Print Number of records successfully transferred
190 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
191 FND_MESSAGE.SET_NAME('IGS', 'IGS_UC_MV_LOAD_SUCCESS');
192 FND_MESSAGE.SET_TOKEN('CNT', l_success_cnt);
193 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET());
194
195 -- commit the data;
196 COMMIT;
197 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
198
199 EXCEPTION
200 WHEN app_exception.record_lock_exception THEN
201 ROLLBACK;
202 retcode := 2;
203 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
204 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
205
206 WHEN OTHERS THEN
207 ROLLBACK;
208 RETCODE := 2;
209 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
210 FND_MESSAGE.SET_TOKEN('NAME','IGS_UC_MV_IMP_DEGSUBJ.IMP_DEGSUBJECT_CODES - '||SQLERRM);
211 ERRBUF := FND_MESSAGE.GET;
212 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
213
214 END Import_Inst_codes;
215 END igs_uc_mv_imp_inst;