[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_CONFIG_CYCLE
Source
1 PACKAGE BODY igs_uc_config_cycle AS
2 /* $Header: IGSUC41B.pls 120.7 2006/08/21 06:14:56 jbaber ship $ */
3
4 g_synonym_fail BOOLEAN := FALSE;
5
6
7 PROCEDURE log_msg (p_name VARCHAR2, p_mode VARCHAR2) IS
8 /*************************************************************
9 Created By : DSRIDHAR
10 Date Created On : 05-JUN-2003
11 Purpose : Procedure to log messages for Synonyms Created or Dropped
12 based on the parametres
13
14 Know limitations, enhancements or remarks
15 Change History
16 Who When What
17 (reverse chronological order - newest change first)
18 dsridhar 15-JUL-2003 Tokens added for messages,
19 All messages used on synoyms and objects sdded here.
20 dsridhar 16-JUL-2003 Added cursor cur_uc_defaults_data and changed updation
21 of package igs_uc_defaults_pkg from cur_uc_defaults to
22 cur_uc_defaults_data
23 jbaber 20-JUN-2006 Added new messages for mode CD and CF
24 ***************************************************************/
25
26 BEGIN
27
28 -- Synonym Created if p_mode = 'C'
29 IF p_mode = 'C' THEN
30 -- Logging the message
31 fnd_message.set_name('IGS', 'IGS_UC_CREATE_SYNONYMS');
32 fnd_message.set_token('SYN_NAME', p_name);
33 fnd_file.put_line(fnd_file.log, fnd_message.get);
34 -- Synonym Dummy Created if p_mode = 'CD'
35 ELSIF p_mode = 'CD' THEN
36 -- Logging the message
37 fnd_message.set_name('IGS', 'IGS_UC_CREATE_DUMMY_SYNONYMS');
38 fnd_message.set_token('SYN_NAME', p_name);
39 fnd_file.put_line(fnd_file.log, fnd_message.get);
40 -- Synonym Failed to create if p_mode = 'CF'
41 ELSIF p_mode = 'CF' THEN
42 -- Logging the message
43 fnd_message.set_name('IGS', 'IGS_UC_CREATE_SYNONYMS_FAIL');
44 fnd_message.set_token('SYN_NAME', p_name);
45 fnd_file.put_line(fnd_file.log, fnd_message.get);
46 -- Synonym Dropped if p_mode = 'D'
47 ELSIF p_mode = 'D' THEN
48 -- Logging the message
49 fnd_message.set_name('IGS', 'IGS_UC_DROP_SYNONYMS');
50 fnd_message.set_token('SYN_NAME', p_name);
51 fnd_file.put_line(fnd_file.log, fnd_message.get);
52 ELSIF p_mode = 'O' THEN
53 -- Logging the message
54 fnd_message.set_name('IGS', 'IGS_UC_COMP_OBJECT');
55 fnd_message.set_token('OBJ_NAME', p_name);
56 fnd_file.put_line(fnd_file.log, fnd_message.get);
57 ELSIF p_mode = 'I' THEN
58 fnd_message.set_name('IGS','IGS_UC_INV_OBJECT');
59 fnd_message.set_token('OBJ_NAME', p_name);
60 fnd_file.put_line(fnd_file.log, fnd_message.get);
61
62 END IF;
63
64 END log_msg;
65
66
67 PROCEDURE create_synonym (p_mode IN VARCHAR,
68 p_synonym IN VARCHAR2,
69 p_object IN VARCHAR2,
70 p_dblink_name IN VARCHAR2,
71 p_dummy IN VARCHAR2) IS
72 /*************************************************************
73 Created By : jbaber
74 Date Created On : 20-Jun-2006
75 Purpose : Creates synonyms. Gracefully handles invalid synonmys
76 by pointing to dummy views.
77
78 Know limitations, enhancements or remarks
79 Change History
80 Who When What
81 (reverse chronological order - newest change first)
82 ***************************************************************/
83
84 sql_stmt VARCHAR2(32767);
85
86 BEGIN
87
88 -- Create synonym
89 IF p_mode = 'C' THEN
90
91 -- Create synonymn to point to UCAS
92 apps_ddl.apps_ddl('CREATE SYNONYM ' || p_synonym || ' FOR ' || p_object || '@' || p_dblink_name);
93 log_msg(p_synonym, 'C');
94
95 -- Try to access synonymn
96 BEGIN
97
98 sql_stmt := 'SELECT ''x'' FROM ' || p_synonym || ' WHERE 1 = 2 ';
99 EXECUTE IMMEDIATE(sql_stmt);
100
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 -- IF fail then log a message and create DUMMY synonym
105 log_msg(p_synonym , 'CF');
106 apps_ddl.apps_ddl('CREATE OR REPLACE SYNONYM ' || p_synonym || ' FOR ' || p_dummy);
107 log_msg(p_synonym, 'CD');
108
109 -- set fail flag
110 g_synonym_fail := TRUE;
111
112 END;
113
114 -- Create Dummy Synonym
115 ELSE
116 apps_ddl.apps_ddl('CREATE SYNONYM ' || p_synonym || ' FOR ' || p_dummy);
117 log_msg(p_synonym, 'CD');
118
119 END IF;
120
121 END create_synonym;
122
123
124 PROCEDURE conf_system_for_ucas_cycle( errbuf OUT NOCOPY VARCHAR2,
125 retcode OUT NOCOPY NUMBER,
126 p_target_cycle IN NUMBER,
127 p_dblink_name IN VARCHAR2
128 ) IS
129 /*************************************************************
130 Created By : DSRIDHAR
131 Date Created On : 05-JUN-2003
132 Purpose : Created w.r.t. UCFD203 - UCAS Multiple Cycles - Build, Bug No: 2669208.
133 Configure System for UCAS Cylce - This process would provide for switching
134 the system between the supported cycles via a standard Concurrent Manager
135 Request. This process would drop and create synonyms either pointing to the
136 Hercules views over the Database link or to the local dummy views to support
137 compilation of code referencing these synonyms.
138
139 Know limitations, enhancements or remarks
140 Change History
141 Who When What
142 (reverse chronological order - newest change first)
143 dsridhar 15-Jul-03 Tokens added for messages,
144 New views IGS_UC_IVFORMQUALS_2004_V AND IGS_UC_IVREFERENCE_2004_V added for
145 compilation, IGS_UC_FORMQUALS_2004 added
146 pmarada 16-Jul-03 Added the code to compile igs_uc_gen_001 package,as per UCFD203
147 dsridhar 16-Jul-03 Bug No: 3083664. Package IGS_UC_PROC_COM_INST_DATA included for compilation
148 smaddali 27-Aug-03 Modified to point cvcontrol , cvrefamendments synonyms always to dblink , bug#3116897 |
149 jchakrab 27-Jul-04 Modified for UCFD308 - UCAS - 2005 Regulatory Changes
150 jbaber 07-Jul-05 Modified for UC315 - UCAS Support 2006
151 anwest 18-JAN-06 Bug# 4950285 R12 Disable OSS Mandate
152 jchin 20-jan-06 Bug 4950293 R12 Perf improvements
153 ***************************************************************/
154
155 -- Get the cycle information from defaults
156 CURSOR cur_cyc_info IS
157 SELECT MAX(current_cycle) current_cycle , MAX(configured_cycle) configured_cycle
158 FROM igs_uc_defaults ;
159
160 -- Cursor to get the data from IGS_UC_DEFAULTS
161 CURSOR cur_uc_defaults_data IS
162 SELECT iuc.rowid, iuc.*
163 FROM igs_uc_defaults iuc
164 WHERE system_code <> 'S';
165
166 -- Cursor to get the UCAS_INTERFACE for the 'target cycle'
167 CURSOR cur_ucas_interface (cp_ucas_cycle igs_uc_cyc_defaults.ucas_cycle%TYPE,
168 cp_system_code igs_uc_cyc_defaults.system_code%TYPE) IS
169 SELECT ucas_interface
170 FROM IGS_UC_CYC_DEFAULTS
171 WHERE system_code = cp_system_code
172 AND ucas_cycle = cp_ucas_cycle;
173
174 -- Cursor to get the SYNONYMS
175 -- smaddali added new synonym igs_uc_u_ivstatement_2004 for bug#33098810
176 -- modified the user to add filtering based on pseudo column USER for bug# 3431844
177 -- jchin - bug 4950293
178 CURSOR cur_synonyms IS
179 SELECT synonym_name object_name
180 FROM user_synonyms
181 WHERE synonym_name IN (
182 'IGS_UC_U_CVCONTROL_2003',
183 'IGS_UC_U_CVCOURSE_2003',
184 'IGS_UC_U_CVEBLSUBJECT_2003',
185 'IGS_UC_U_CVINSTITUTION_2003',
186 'IGS_UC_U_CVJNTADMISSIONS_2003',
187 'IGS_UC_U_CVNAME_2003',
188 'IGS_UC_U_CVREFAMENDMENTS_2003',
189 'IGS_UC_U_CVREFAPR_2003',
190 'IGS_UC_U_CVREFAWARDBODY_2003',
191 'IGS_UC_U_CVREFDIS_2003',
192 'IGS_UC_U_CVREFERROR_2003',
193 'IGS_UC_U_CVREFESTGROUP_2003',
194 'IGS_UC_U_CVREFETHNIC_2003',
195 'IGS_UC_U_CVREFEXAM_2003',
196 'IGS_UC_U_CVREFFEE_2003',
197 'IGS_UC_U_CVREFKEYWORD_2003',
198 'IGS_UC_U_CVREFOEQ_2003',
199 'IGS_UC_U_CVREFOFFERABBREV_2003',
200 'IGS_UC_U_CVREFOFFERSUBJ_2003',
201 'IGS_UC_U_CVREFPOCC_2003',
202 'IGS_UC_U_CVREFPRE2000POCC_2003',
203 'IGS_UC_U_CVREFRESCAT_2003',
204 'IGS_UC_U_CVREFSCHOOLTYPE_2003',
205 'IGS_UC_U_CVREFSOCIALCLASS_2003',
206 'IGS_UC_U_CVREFSOCIOECON_2003',
207 'IGS_UC_U_CVREFSTATUS_2003',
208 'IGS_UC_U_CVREFSUBJ_2003',
209 'IGS_UC_U_CVREFTARIFF_2003',
210 'IGS_UC_U_CVREFUCASGROUP_2003',
211 'IGS_UC_U_CVSCHOOLCONTACT_2003',
212 'IGS_UC_U_CVSCHOOL_2003',
213 'IGS_UC_U_UVCONTACT_2003',
214 'IGS_UC_U_UVCONTGRP_2003',
215 'IGS_UC_U_UVCOURSEKEYWORD_2003',
216 'IGS_UC_U_UVCOURSEVACS_2003',
217 'IGS_UC_U_UVCOURSEVACOPS_2003',
218 'IGS_UC_U_UVCOURSE_2003',
219 'IGS_UC_U_UVINSTITUTION_2003',
220 'IGS_UC_U_UVINSTITUTION_2004',
221 'IGS_UC_U_UVOFFERABBREV_2003',
222 'IGS_UC_U_IVOFFER_2003',
223 'IGS_UC_U_IVQUALIFICATION_2003',
224 'IGS_UC_U_IVSTARA_2003',
225 'IGS_UC_U_IVSTARC_2003',
226 'IGS_UC_U_IVSTARH_2003',
227 'IGS_UC_U_IVSTARK_2003',
228 'IGS_UC_U_IVSTARN_2003',
229 'IGS_UC_U_IVSTARPQR_2003',
230 'IGS_UC_U_IVSTARW_2003',
231 'IGS_UC_U_IVSTARX_2003',
232 'IGS_UC_U_IVSTARZ1_2003',
233 'IGS_UC_U_IVSTARZ2_2003',
234 'IGS_UC_U_IVSTATEMENT_2003',
235 'IGS_UC_U_TRANIN_2003',
236 'IGS_UC_U_IVFORMQUALS_2004',
237 'IGS_UC_U_IVREFERENCE_2004',
238 'IGS_UC_U_IVSTARJ_2004',
239 'IGS_UC_U_IVSTARW_2004',
240 'IGS_UC_U_TRANIN_2004',
241 'IGS_UC_U_IVSTATEMENT_2004',
242 'IGS_UC_U_IVREFERENCE_2006',
243 'IGS_UC_U_IVSTARA_2006',
244 'IGS_UC_U_IVFORMQUALS_2006',
245 'IGS_UC_U_IVSTARA_2007',
246 'IGS_UC_U_IVSTARN_2007',
247 'IGS_UC_U_IVSTARK_2007',
248 'IGS_UC_U_CVREFCOUNTRY_2007',
249 'IGS_UC_U_CVREFNATIONALITY_2007',
250 'IGS_UC_U_CVREFAMENDMENTS_2007',
251 -- Small Systems Synonyms
252 'IGS_UC_G_CVGNAME_2006',
253 'IGS_UC_N_CVNNAME_2006',
254 'IGS_UC_G_IVGOFFER_2006',
255 'IGS_UC_N_IVNOFFER_2006',
256 'IGS_UC_G_IVGSTARA_2006',
257 'IGS_UC_N_IVNSTARA_2006',
258 'IGS_UC_N_IVNSTARC_2006',
259 'IGS_UC_G_IVGSTARG_2006',
260 'IGS_UC_G_IVGSTARH_2006',
261 'IGS_UC_N_IVNSTARH_2006',
262 'IGS_UC_G_IVGSTARK_2006',
263 'IGS_UC_N_IVNSTARK_2006',
264 'IGS_UC_G_IVGSTARN_2006',
265 'IGS_UC_N_IVNSTARN_2006',
266 'IGS_UC_G_IVGSTARW_2006',
267 'IGS_UC_N_IVNSTARW_2006',
268 'IGS_UC_G_IVGSTARX_2006',
269 'IGS_UC_N_IVNSTARX_2006',
270 'IGS_UC_N_IVNSTARZ1_2006',
271 'IGS_UC_G_IVGSTATEMENT_2006',
272 'IGS_UC_N_IVNSTATEMENT_2006',
273 'IGS_UC_G_IVGREFERENCE_2006',
274 'IGS_UC_N_IVNREFERENCE_2006',
275 'IGS_UC_G_CVGREFAMENDMENTS_2006',
276 'IGS_UC_G_CVGREFDEGREESUBJ_2006',
277 'IGS_UC_G_IVGSTARA_2007',
278 'IGS_UC_N_IVNSTARA_2007',
279 'IGS_UC_G_IVGSTARN_2007',
280 'IGS_UC_N_IVNSTARN_2007',
281 'IGS_UC_G_IVGSTARK_2007',
282 'IGS_UC_N_IVNSTARK_2007',
283 'IGS_UC_G_IVGSTARW_2007',
284 'IGS_UC_N_IVNSTARW_2007',
285 'IGS_UC_G_CVGCOURSE_2007',
286 'IGS_UC_N_CVNCOURSE_2007'
287 );
288
289 l_cyc_info_rec cur_cyc_info%ROWTYPE;
290 l_configured_cycle igs_uc_defaults.configured_cycle%TYPE;
291 l_current_cycle igs_uc_defaults.current_cycle%TYPE;
292 l_ucas_interface igs_uc_cyc_defaults.ucas_interface%TYPE;
293 l_gttr_interface igs_uc_cyc_defaults.ucas_interface%TYPE;
294 l_nmas_interface igs_uc_cyc_defaults.ucas_interface%TYPE;
295
296 BEGIN
297
298 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
299 IGS_GE_GEN_003.SET_ORG_ID;
300
301 -- Get the configured and current cycle information and exit process if not found
302 l_cyc_info_rec := NULL ;
303 OPEN cur_cyc_info ;
304 FETCH cur_cyc_info INTO l_cyc_info_rec ;
305 CLOSE cur_cyc_info ;
306 IF l_cyc_info_rec.configured_cycle IS NULL OR l_cyc_info_rec.current_cycle IS NULL THEN
307 fnd_message.set_name('IGS','IGS_UC_CYCLE_NOT_FOUND');
308 errbuf := fnd_message.get;
309 fnd_file.put_line(fnd_file.log, errbuf);
310 retcode := 2 ;
311 RETURN ;
312 END IF;
313
314 l_configured_cycle := l_cyc_info_rec.configured_cycle ;
315 l_current_cycle := l_cyc_info_rec.current_cycle ;
316
317 -- Checking if the configured and target cycle are same
318 IF l_configured_cycle = p_target_cycle THEN
319 fnd_message.set_name('IGS','IGS_UC_CONF_TARG_SAME');
320 fnd_file.put_line(fnd_file.log, fnd_message.get);
321 END IF;
322
323 -- Fetching 'UCAS_INTERFACE'
324 OPEN cur_ucas_interface (p_target_cycle, 'U');
325 FETCH cur_ucas_interface INTO l_ucas_interface;
326 CLOSE cur_ucas_interface;
327
328 -- Drop all synonyms
329 FOR rec_cur_synonyms IN cur_synonyms LOOP
330 BEGIN
331
332 apps_ddl.apps_ddl('DROP SYNONYM ' || rec_cur_synonyms.object_name);
333 log_msg(rec_cur_synonyms.object_name, 'D');
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 retcode := 2 ;
338 log_msg(rec_cur_synonyms.object_name, 'I');
339 END;
340 END LOOP;
341
342 IF p_target_cycle < 2006 THEN
343 create_synonym('C', 'IGS_UC_U_CVCONTROL_2003', 'CVCONTROL', p_dblink_name, 'IGS_UC_CVCONTROL_2003');
344 ELSE
345 create_synonym('D', 'IGS_UC_U_CVCONTROL_2003', 'CVCONTROL', p_dblink_name, 'IGS_UC_CVCONTROL_2003');
346 END IF;
347
348 IF p_target_cycle < 2007 THEN
349 create_synonym('C', 'IGS_UC_U_CVREFAMENDMENTS_2003', 'CVREFAMENDMENTS', p_dblink_name, 'IGS_UC_CVREFAMENDMENTS_2003');
350 create_synonym('D', 'IGS_UC_U_CVREFAMENDMENTS_2007', 'CVREFAMENDMENTS', p_dblink_name, 'IGS_UC_CVREFAMENDMENTS_2007');
351 ELSE
352 create_synonym('D', 'IGS_UC_U_CVREFAMENDMENTS_2003', 'CVREFAMENDMENTS', p_dblink_name, 'IGS_UC_CVREFAMENDMENTS_2003');
353 create_synonym('C', 'IGS_UC_U_CVREFAMENDMENTS_2007', 'CVREFAMENDMENTS', p_dblink_name, 'IGS_UC_CVREFAMENDMENTS_2007');
354 END IF;
355
356
357 -- For all synonyms pertaining to REFERENCE VIEWS for target cycle
358 IF p_target_cycle = l_current_cycle THEN
359
360 -- Check the UCAS Inteface Profile value is whether Hercules or Marvin.
361 IF (l_ucas_interface = 'H') THEN
362
363 -- Create Synonyms to Hercules for the views 'uvCourseVacancies',
364 -- 'uvCourseVacOptions', 'uvOfferAbbrev'
365 IF p_target_cycle < 2006 THEN
366 create_synonym('C', 'IGS_UC_U_UVCOURSEVACS_2003', 'UVCOURSEVACANCIES', p_dblink_name, 'IGS_UC_UVCOURSEVACANCIES_2003');
367 create_synonym('C', 'IGS_UC_U_UVCOURSEVACOPS_2003', 'UVCOURSEVACOPTIONS', p_dblink_name, 'IGS_UC_UVCOURSEVACOPTIONS_2003');
368
369 ELSE
370 create_synonym('D', 'IGS_UC_U_UVCOURSEVACS_2003', 'UVCOURSEVACANCIES', p_dblink_name, 'IGS_UC_UVCOURSEVACANCIES_2003');
371 create_synonym('D', 'IGS_UC_U_UVCOURSEVACOPS_2003', 'UVCOURSEVACOPTIONS', p_dblink_name, 'IGS_UC_UVCOURSEVACOPTIONS_2003');
372
373 END IF;
374
375 create_synonym('C', 'IGS_UC_U_UVOFFERABBREV_2003', 'UVOFFERABBREV', p_dblink_name, 'IGS_UC_UVOFFERABBREV_2003');
376
377 ELSE
378 -- Create Synonyms to DUMMY VIEW for the views 'uvCourseVacancies',
379 -- 'uvCourseVacOptions', 'uvOfferAbbrev'
380 create_synonym('D', 'IGS_UC_U_UVCOURSEVACS_2003', 'UVCOURSEVACANCIES', p_dblink_name, 'IGS_UC_UVCOURSEVACANCIES_2003');
381 create_synonym('D', 'IGS_UC_U_UVCOURSEVACOPS_2003', 'UVCOURSEVACOPTIONS', p_dblink_name, 'IGS_UC_UVCOURSEVACOPTIONS_2003');
382 create_synonym('D', 'IGS_UC_U_UVOFFERABBREV_2003', 'UVOFFERABBREV', p_dblink_name, 'IGS_UC_UVOFFERABBREV_2003');
383
384 END IF;
385
386
387 -- Create Synonym to HERCULES for views OTHER THAN
388 -- uvCourseVacs, uvCourseVacOptions, uvOfferAbbrev (already done)
389 -- uvContact, uvContactGroups, cvRefPre2000Pocc, cvRefUCASGroup, uvInstitution, cvRefNationality, cvRefCountry (dependent on year)
390 create_synonym('C', 'IGS_UC_U_CVCOURSE_2003', 'CVCOURSE', p_dblink_name, 'IGS_UC_CVCOURSE_2003');
391 create_synonym('C', 'IGS_UC_U_CVEBLSUBJECT_2003', 'CVEBLSUBJECT', p_dblink_name, 'IGS_UC_CVEBLSUBJECT_2003');
392 create_synonym('C', 'IGS_UC_U_CVINSTITUTION_2003', 'CVINSTITUTION', p_dblink_name, 'IGS_UC_CVINSTITUTION_2003');
393 create_synonym('C', 'IGS_UC_U_CVJNTADMISSIONS_2003', 'CVJOINTADMISSIONS', p_dblink_name, 'IGS_UC_CVJOINTADMISSIONS_2003');
394 create_synonym('C', 'IGS_UC_U_CVNAME_2003', 'CVNAME', p_dblink_name, 'IGS_UC_CVNAME_2003');
395 create_synonym('C', 'IGS_UC_U_CVREFAPR_2003', 'CVREFAPR', p_dblink_name, 'IGS_UC_CVREFAPR_2003');
396 create_synonym('C', 'IGS_UC_U_CVREFAWARDBODY_2003', 'CVREFAWARDBODY', p_dblink_name, 'IGS_UC_CVREFAWARDBODY_2003');
397 create_synonym('C', 'IGS_UC_U_CVREFDIS_2003', 'CVREFDIS', p_dblink_name, 'IGS_UC_CVREFDIS_2003');
398 create_synonym('C', 'IGS_UC_U_CVREFERROR_2003', 'CVREFERROR', p_dblink_name, 'IGS_UC_CVREFERROR_2003');
399 create_synonym('C', 'IGS_UC_U_CVREFESTGROUP_2003', 'CVREFESTGROUP', p_dblink_name, 'IGS_UC_CVREFESTGROUP_2003');
400 create_synonym('C', 'IGS_UC_U_CVREFETHNIC_2003', 'CVREFETHNIC', p_dblink_name, 'IGS_UC_CVREFETHNIC_2003');
401 create_synonym('C', 'IGS_UC_U_CVREFEXAM_2003', 'CVREFEXAM', p_dblink_name, 'IGS_UC_CVREFEXAM_2003');
402 create_synonym('C', 'IGS_UC_U_CVREFFEE_2003', 'CVREFFEE', p_dblink_name, 'IGS_UC_CVREFFEE_2003');
403 create_synonym('C', 'IGS_UC_U_CVREFKEYWORD_2003', 'CVREFKEYWORD', p_dblink_name, 'IGS_UC_CVREFKEYWORD_2003');
404 create_synonym('C', 'IGS_UC_U_CVREFOEQ_2003', 'CVREFOEQ', p_dblink_name, 'IGS_UC_CVREFOEQ_2003');
405 create_synonym('C', 'IGS_UC_U_CVREFOFFERABBREV_2003', 'CVREFOFFERABBREV', p_dblink_name, 'IGS_UC_CVREFOFFERABBREV_2003');
406 create_synonym('C', 'IGS_UC_U_CVREFOFFERSUBJ_2003', 'CVREFOFFERSUBJ', p_dblink_name, 'IGS_UC_CVREFOFFERSUBJ_2003');
407 create_synonym('C', 'IGS_UC_U_CVREFPOCC_2003', 'CVREFPOCC', p_dblink_name, 'IGS_UC_CVREFPOCC_2003');
408 create_synonym('C', 'IGS_UC_U_CVREFRESCAT_2003', 'CVREFRESCAT', p_dblink_name, 'IGS_UC_CVREFRESCAT_2003');
409 create_synonym('C', 'IGS_UC_U_CVREFSCHOOLTYPE_2003', 'CVREFSCHOOLTYPE', p_dblink_name, 'IGS_UC_CVREFSCHOOLTYPE_2003');
410 create_synonym('C', 'IGS_UC_U_CVREFSOCIALCLASS_2003', 'CVREFSOCIALCLASS', p_dblink_name, 'IGS_UC_CVREFSOCIALCLASS_2003');
411 create_synonym('C', 'IGS_UC_U_CVREFSOCIOECON_2003', 'CVREFSOCIOECONOMIC', p_dblink_name, 'IGS_UC_CVREFSOCIOECONOMIC_2003');
412 create_synonym('C', 'IGS_UC_U_CVREFSTATUS_2003', 'CVREFSTATUS', p_dblink_name, 'IGS_UC_CVREFSTATUS_2003');
413 create_synonym('C', 'IGS_UC_U_CVREFSUBJ_2003', 'CVREFSUBJ', p_dblink_name, 'IGS_UC_CVREFSUBJ_2003');
414 create_synonym('C', 'IGS_UC_U_CVREFTARIFF_2003', 'CVREFTARIFF', p_dblink_name, 'IGS_UC_CVREFTARIFF_2003');
415 create_synonym('C', 'IGS_UC_U_CVSCHOOLCONTACT_2003', 'CVSCHOOLCONTACT', p_dblink_name, 'IGS_UC_CVSCHOOLCONTACT_2003');
416 create_synonym('C', 'IGS_UC_U_CVSCHOOL_2003', 'CVSCHOOL', p_dblink_name, 'IGS_UC_CVSCHOOL_2003');
417 create_synonym('C', 'IGS_UC_U_UVCOURSEKEYWORD_2003', 'UVCOURSEKEYWORD', p_dblink_name, 'IGS_UC_UVCOURSEKEYWORD_2003');
418 create_synonym('C', 'IGS_UC_U_UVCOURSE_2003', 'UVCOURSE', p_dblink_name, 'IGS_UC_UVCOURSE_2003');
419
420 IF p_target_cycle = 2003 THEN
421
422 -- Point following synonyms to HERCULES
423 create_synonym('C', 'IGS_UC_U_UVINSTITUTION_2003', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2003');
424 create_synonym('C', 'IGS_UC_U_CVREFPRE2000POCC_2003', 'CVREFPRE2000POCC', p_dblink_name, 'IGS_UC_CVREFPRE2000POCC_2003');
425 create_synonym('C', 'IGS_UC_U_CVREFUCASGROUP_2003', 'CVREFUCASGROUP', p_dblink_name, 'IGS_UC_CVREFUCASGROUP_2003');
426 create_synonym('C', 'IGS_UC_U_UVCONTACT_2003', 'UVCONTACT', p_dblink_name, 'IGS_UC_UVCONTACT_2003');
427 create_synonym('C', 'IGS_UC_U_UVCONTGRP_2003', 'UVCONTGRP', p_dblink_name, 'IGS_UC_UVCONTGRP_2003');
428
429 -- Point following synonyms to DUMMY
430 create_synonym('D', 'IGS_UC_U_UVINSTITUTION_2004', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2004');
431 create_synonym('D', 'IGS_UC_U_CVREFCOUNTRY_2007', 'CVREFCOUNTRY', p_dblink_name, 'IGS_UC_CVREFCOUNTRY_2007');
432 create_synonym('D', 'IGS_UC_U_CVREFNATIONALITY_2007', 'CVREFNATIONALITY', p_dblink_name, 'IGS_UC_CVREFNATIONALITY_2007');
433
434 ELSIF p_target_cycle = 2004 OR p_target_cycle = 2005 THEN
435
436 -- Point following synonyms to HERCULES
437 create_synonym('C', 'IGS_UC_U_UVINSTITUTION_2004', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2004');
438 create_synonym('C', 'IGS_UC_U_CVREFPRE2000POCC_2003', 'CVREFPRE2000POCC', p_dblink_name, 'IGS_UC_CVREFPRE2000POCC_2003');
439 create_synonym('C', 'IGS_UC_U_CVREFUCASGROUP_2003', 'CVREFUCASGROUP', p_dblink_name, 'IGS_UC_CVREFUCASGROUP_2003');
440 create_synonym('C', 'IGS_UC_U_UVCONTACT_2003', 'UVCONTACT', p_dblink_name, 'IGS_UC_UVCONTACT_2003');
441 create_synonym('C', 'IGS_UC_U_UVCONTGRP_2003', 'UVCONTGRP', p_dblink_name, 'IGS_UC_UVCONTGRP_2003');
442
443 -- Point following synonyms to DUMMY
444 create_synonym('D', 'IGS_UC_U_UVINSTITUTION_2003', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2003');
445 create_synonym('D', 'IGS_UC_U_CVREFCOUNTRY_2007', 'CVREFCOUNTRY', p_dblink_name, 'IGS_UC_CVREFCOUNTRY_2007');
446 create_synonym('D', 'IGS_UC_U_CVREFNATIONALITY_2007', 'CVREFNATIONALITY', p_dblink_name, 'IGS_UC_CVREFNATIONALITY_2007');
447
448 ELSIF p_target_cycle = 2006 THEN
449
450 -- Point following synonyms to HERCULES
451 create_synonym('C', 'IGS_UC_U_UVINSTITUTION_2004', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2004');
452
453 -- Point following synonyms to DUMMY
454 create_synonym('D', 'IGS_UC_U_UVINSTITUTION_2003', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2003');
455 create_synonym('D', 'IGS_UC_U_CVREFPRE2000POCC_2003', 'CVREFPRE2000POCC', p_dblink_name, 'IGS_UC_CVREFPRE2000POCC_2003');
456 create_synonym('D', 'IGS_UC_U_CVREFUCASGROUP_2003', 'CVREFUCASGROUP', p_dblink_name, 'IGS_UC_CVREFUCASGROUP_2003');
457 create_synonym('D', 'IGS_UC_U_UVCONTACT_2003', 'UVCONTACT', p_dblink_name, 'IGS_UC_UVCONTACT_2003');
458 create_synonym('D', 'IGS_UC_U_UVCONTGRP_2003', 'UVCONTGRP', p_dblink_name, 'IGS_UC_UVCONTGRP_2003');
459 create_synonym('D', 'IGS_UC_U_CVREFCOUNTRY_2007', 'CVREFCOUNTRY', p_dblink_name, 'IGS_UC_CVREFCOUNTRY_2007');
460 create_synonym('D', 'IGS_UC_U_CVREFNATIONALITY_2007', 'CVREFNATIONALITY', p_dblink_name, 'IGS_UC_CVREFNATIONALITY_2007');
461
462 ELSIF p_target_cycle = 2007 THEN
463 -- Point following synonyms to HERCULES
464 create_synonym('C', 'IGS_UC_U_CVREFCOUNTRY_2007', 'CVREFCOUNTRY', p_dblink_name, 'IGS_UC_CVREFCOUNTRY_2007');
465 create_synonym('C', 'IGS_UC_U_CVREFNATIONALITY_2007', 'CVREFNATIONALITY', p_dblink_name, 'IGS_UC_CVREFNATIONALITY_2007');
466 create_synonym('C', 'IGS_UC_U_UVINSTITUTION_2004', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2004');
467
468 -- Point following synonyms to DUMMY
469 create_synonym('D', 'IGS_UC_U_UVINSTITUTION_2003', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2003');
470 create_synonym('D', 'IGS_UC_U_CVREFPRE2000POCC_2003', 'CVREFPRE2000POCC', p_dblink_name, 'IGS_UC_CVREFPRE2000POCC_2003');
471 create_synonym('D', 'IGS_UC_U_CVREFUCASGROUP_2003', 'CVREFUCASGROUP', p_dblink_name, 'IGS_UC_CVREFUCASGROUP_2003');
472 create_synonym('D', 'IGS_UC_U_UVCONTACT_2003', 'UVCONTACT', p_dblink_name, 'IGS_UC_UVCONTACT_2003');
473 create_synonym('D', 'IGS_UC_U_UVCONTGRP_2003', 'UVCONTGRP', p_dblink_name, 'IGS_UC_UVCONTGRP_2003');
474
475 END IF;
476
477 -- Create Synonyms to dummy views
478 ELSE
479
480 create_synonym('D', 'IGS_UC_U_CVCOURSE_2003', 'CVCOURSE', p_dblink_name, 'IGS_UC_CVCOURSE_2003');
481 create_synonym('D', 'IGS_UC_U_CVEBLSUBJECT_2003', 'CVEBLSUBJECT', p_dblink_name, 'IGS_UC_CVEBLSUBJECT_2003');
482 create_synonym('D', 'IGS_UC_U_CVINSTITUTION_2003', 'CVINSTITUTION', p_dblink_name, 'IGS_UC_CVINSTITUTION_2003');
483 create_synonym('D', 'IGS_UC_U_CVJNTADMISSIONS_2003', 'CVJOINTADMISSIONS', p_dblink_name, 'IGS_UC_CVJOINTADMISSIONS_2003');
484 create_synonym('D', 'IGS_UC_U_CVNAME_2003', 'CVNAME', p_dblink_name, 'IGS_UC_CVNAME_2003');
485 create_synonym('D', 'IGS_UC_U_CVREFAPR_2003', 'CVREFAPR', p_dblink_name, 'IGS_UC_CVREFAPR_2003');
486 create_synonym('D', 'IGS_UC_U_CVREFAWARDBODY_2003', 'CVREFAWARDBODY', p_dblink_name, 'IGS_UC_CVREFAWARDBODY_2003');
487 create_synonym('D', 'IGS_UC_U_CVREFDIS_2003', 'CVREFDIS', p_dblink_name, 'IGS_UC_CVREFDIS_2003');
488 create_synonym('D', 'IGS_UC_U_CVREFERROR_2003', 'CVREFERROR', p_dblink_name, 'IGS_UC_CVREFERROR_2003');
489 create_synonym('D', 'IGS_UC_U_CVREFESTGROUP_2003', 'CVREFESTGROUP', p_dblink_name, 'IGS_UC_CVREFESTGROUP_2003');
490 create_synonym('D', 'IGS_UC_U_CVREFETHNIC_2003', 'CVREFETHNIC', p_dblink_name, 'IGS_UC_CVREFETHNIC_2003');
491 create_synonym('D', 'IGS_UC_U_CVREFEXAM_2003', 'CVREFEXAM', p_dblink_name, 'IGS_UC_CVREFEXAM_2003');
492 create_synonym('D', 'IGS_UC_U_CVREFFEE_2003', 'CVREFFEE', p_dblink_name, 'IGS_UC_CVREFFEE_2003');
493 create_synonym('D', 'IGS_UC_U_CVREFKEYWORD_2003', 'CVREFKEYWORD', p_dblink_name, 'IGS_UC_CVREFKEYWORD_2003');
494 create_synonym('D', 'IGS_UC_U_CVREFOEQ_2003', 'CVREFOEQ', p_dblink_name, 'IGS_UC_CVREFOEQ_2003');
495 create_synonym('D', 'IGS_UC_U_CVREFOFFERABBREV_2003', 'CVREFOFFERABBREV', p_dblink_name, 'IGS_UC_CVREFOFFERABBREV_2003');
496 create_synonym('D', 'IGS_UC_U_CVREFOFFERSUBJ_2003', 'CVREFOFFERSUBJ', p_dblink_name, 'IGS_UC_CVREFOFFERSUBJ_2003');
497 create_synonym('D', 'IGS_UC_U_CVREFPOCC_2003', 'CVREFPOCC', p_dblink_name, 'IGS_UC_CVREFPOCC_2003');
498 create_synonym('D', 'IGS_UC_U_CVREFPRE2000POCC_2003', 'CVREFPRE2000POCC', p_dblink_name, 'IGS_UC_CVREFPRE2000POCC_2003');
499 create_synonym('D', 'IGS_UC_U_CVREFRESCAT_2003', 'CVREFRESCAT', p_dblink_name, 'IGS_UC_CVREFRESCAT_2003');
500 create_synonym('D', 'IGS_UC_U_CVREFSCHOOLTYPE_2003', 'CVREFSCHOOLTYPE', p_dblink_name, 'IGS_UC_CVREFSCHOOLTYPE_2003');
501 create_synonym('D', 'IGS_UC_U_CVREFSOCIALCLASS_2003', 'CVREFSOCIALCLASS', p_dblink_name, 'IGS_UC_CVREFSOCIALCLASS_2003');
502 create_synonym('D', 'IGS_UC_U_CVREFSOCIOECON_2003', 'CVREFSOCIOECONOMIC', p_dblink_name, 'IGS_UC_CVREFSOCIOECONOMIC_2003');
503 create_synonym('D', 'IGS_UC_U_CVREFSTATUS_2003', 'CVREFSTATUS', p_dblink_name, 'IGS_UC_CVREFSTATUS_2003');
504 create_synonym('D', 'IGS_UC_U_CVREFSUBJ_2003', 'CVREFSUBJ', p_dblink_name, 'IGS_UC_CVREFSUBJ_2003');
505 create_synonym('D', 'IGS_UC_U_CVREFTARIFF_2003', 'CVREFTARIFF', p_dblink_name, 'IGS_UC_CVREFTARIFF_2003');
506 create_synonym('D', 'IGS_UC_U_CVREFUCASGROUP_2003', 'CVREFUCASGROUP', p_dblink_name, 'IGS_UC_CVREFUCASGROUP_2003');
507 create_synonym('D', 'IGS_UC_U_CVSCHOOLCONTACT_2003', 'CVSCHOOLCONTACT', p_dblink_name, 'IGS_UC_CVSCHOOLCONTACT_2003');
508 create_synonym('D', 'IGS_UC_U_CVSCHOOL_2003', 'CVSCHOOL', p_dblink_name, 'IGS_UC_CVSCHOOL_2003');
509 create_synonym('D', 'IGS_UC_U_UVCONTACT_2003', 'UVCONTACT', p_dblink_name, 'IGS_UC_UVCONTACT_2003');
510 create_synonym('D', 'IGS_UC_U_UVCONTGRP_2003', 'UVCONTGRP', p_dblink_name, 'IGS_UC_UVCONTGRP_2003');
511 create_synonym('D', 'IGS_UC_U_UVCOURSEKEYWORD_2003', 'UVCOURSEKEYWORD', p_dblink_name, 'IGS_UC_UVCOURSEKEYWORD_2003');
512 create_synonym('D', 'IGS_UC_U_UVCOURSE_2003', 'UVCOURSE', p_dblink_name, 'IGS_UC_UVCOURSE_2003');
513 create_synonym('D', 'IGS_UC_U_UVINSTITUTION_2003', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2003');
514 create_synonym('D', 'IGS_UC_U_UVINSTITUTION_2004', 'UVINSTITUTION', p_dblink_name, 'IGS_UC_UVINSTITUTION_2004');
515 create_synonym('D', 'IGS_UC_U_UVCOURSEVACS_2003', 'UVCOURSEVACANCIES', p_dblink_name, 'IGS_UC_UVCOURSEVACANCIES_2003');
516 create_synonym('D', 'IGS_UC_U_UVCOURSEVACOPS_2003', 'UVCOURSEVACOPTIONS', p_dblink_name, 'IGS_UC_UVCOURSEVACOPTIONS_2003');
517 create_synonym('D', 'IGS_UC_U_UVOFFERABBREV_2003', 'UVOFFERABBREV', p_dblink_name, 'IGS_UC_UVOFFERABBREV_2003');
518 create_synonym('D', 'IGS_UC_U_CVREFCOUNTRY_2007', 'CVREFCOUNTRY', p_dblink_name, 'IGS_UC_CVREFCOUNTRY_2007');
519 create_synonym('D', 'IGS_UC_U_CVREFNATIONALITY_2007', 'CVREFNATIONALITY', p_dblink_name, 'IGS_UC_CVREFNATIONALITY_2007');
520
521
522 END IF ;
523
524 -- APPLICATION RELATED TABLES/VIEWS
525 -- UCAS synonyms for target cycle for applicant tables
526 IF (l_ucas_interface = 'H') THEN
527
528 -- For 2003 - 2003 Synonyms should point to Hercules and 2004 Synonyms should point to dummy
529 IF p_target_cycle = 2003 THEN
530
531 -- 2003 Synonyms to HERCULES
532 create_synonym('C', 'IGS_UC_U_IVQUALIFICATION_2003', 'IVQUALIFICATION', p_dblink_name, 'IGS_UC_IVQUALIFICATION_2003');
533 create_synonym('C', 'IGS_UC_U_IVSTARA_2003', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2003');
534 create_synonym('C', 'IGS_UC_U_IVSTARC_2003', 'IVSTARC', p_dblink_name, 'IGS_UC_IVSTARC_2003');
535 create_synonym('C', 'IGS_UC_U_IVSTARH_2003', 'IVSTARH', p_dblink_name, 'IGS_UC_IVSTARH_2003');
536 create_synonym('C', 'IGS_UC_U_IVSTARK_2003', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2003');
537 create_synonym('C', 'IGS_UC_U_IVSTARN_2003', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2003');
538 create_synonym('C', 'IGS_UC_U_IVSTARPQR_2003', 'IVSTARPQR', p_dblink_name, 'IGS_UC_IVSTARPQR_2003');
539 create_synonym('C', 'IGS_UC_U_IVSTARW_2003', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2003');
540 create_synonym('C', 'IGS_UC_U_IVSTARX_2003', 'IVSTARX', p_dblink_name, 'IGS_UC_IVSTARX_2003');
541 create_synonym('C', 'IGS_UC_U_IVSTARZ1_2003', 'IVSTARZ1', p_dblink_name, 'IGS_UC_IVSTARZ1_2003');
542 create_synonym('C', 'IGS_UC_U_IVSTARZ2_2003', 'IVSTARZ2', p_dblink_name, 'IGS_UC_IVSTARZ2_2003');
543 create_synonym('C', 'IGS_UC_U_IVSTATEMENT_2003', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2003');
544 create_synonym('C', 'IGS_UC_U_TRANIN_2003', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2003');
545 create_synonym('C', 'IGS_UC_U_IVOFFER_2003', 'IVOFFER', p_dblink_name, 'IGS_UC_IVOFFER_2003');
546
547 -- 2004/05 Synonyms to DUMMY
548 create_synonym('D', 'IGS_UC_U_IVFORMQUALS_2004', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2004');
549 create_synonym('D', 'IGS_UC_U_IVREFERENCE_2004', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2004');
550 create_synonym('D', 'IGS_UC_U_IVSTARJ_2004', 'IVSTARJ', p_dblink_name, 'IGS_UC_IVSTARJ_2004');
551 create_synonym('D', 'IGS_UC_U_IVSTARW_2004', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2004');
552 create_synonym('D', 'IGS_UC_U_TRANIN_2004', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2004');
553 create_synonym('D', 'IGS_UC_U_IVSTATEMENT_2004', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2004');
554
555 -- 2006 Synonyms to DUMMY
556 create_synonym('D', 'IGS_UC_U_IVSTARA_2006', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2006');
557 create_synonym('D', 'IGS_UC_U_IVFORMQUALS_2006', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2006');
558 create_synonym('D', 'IGS_UC_U_IVREFERENCE_2006', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2006');
559
560 -- 2007 Synonyms to DUMMY
561 create_synonym('D', 'IGS_UC_U_IVSTARA_2007', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2007');
562 create_synonym('D', 'IGS_UC_U_IVSTARN_2007', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2007');
563 create_synonym('D', 'IGS_UC_U_IVSTARK_2007', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2007');
564
565
566 -- For 2004 - Synonyms in both 2003 and 2004 should point to dummy and others to Hercules
567 -- For 2005 - Synonyms similar to 2004
568 ELSIF p_target_cycle = 2004 OR p_target_cycle = 2005 THEN
569
570 -- New 2004/05 synonyms to HERCULES
571 create_synonym('C', 'IGS_UC_U_IVFORMQUALS_2004', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2004');
572 create_synonym('C', 'IGS_UC_U_IVREFERENCE_2004', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2004');
573 create_synonym('C', 'IGS_UC_U_IVSTARJ_2004', 'IVSTARJ', p_dblink_name, 'IGS_UC_IVSTARJ_2004');
574 create_synonym('C', 'IGS_UC_U_IVSTARW_2004', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2004');
575 create_synonym('C', 'IGS_UC_U_TRANIN_2004', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2004');
576 create_synonym('C', 'IGS_UC_U_IVSTATEMENT_2004', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2004');
577
578 -- 2003 Synonyms to HERCULES
579 create_synonym('C', 'IGS_UC_U_IVQUALIFICATION_2003', 'IVQUALIFICATION', p_dblink_name, 'IGS_UC_IVQUALIFICATION_2003');
580 create_synonym('C', 'IGS_UC_U_IVSTARA_2003', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2003');
581 create_synonym('C', 'IGS_UC_U_IVSTARC_2003', 'IVSTARC', p_dblink_name, 'IGS_UC_IVSTARC_2003');
582 create_synonym('C', 'IGS_UC_U_IVSTARH_2003', 'IVSTARH', p_dblink_name, 'IGS_UC_IVSTARH_2003');
583 create_synonym('C', 'IGS_UC_U_IVSTARK_2003', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2003');
584 create_synonym('C', 'IGS_UC_U_IVSTARN_2003', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2003');
585 create_synonym('C', 'IGS_UC_U_IVSTARPQR_2003', 'IVSTARPQR', p_dblink_name, 'IGS_UC_IVSTARPQR_2003');
586 create_synonym('C', 'IGS_UC_U_IVSTARX_2003', 'IVSTARX', p_dblink_name, 'IGS_UC_IVSTARX_2003');
587 create_synonym('C', 'IGS_UC_U_IVSTARZ1_2003', 'IVSTARZ1', p_dblink_name, 'IGS_UC_IVSTARZ1_2003');
588 create_synonym('C', 'IGS_UC_U_IVSTARZ2_2003', 'IVSTARZ2', p_dblink_name, 'IGS_UC_IVSTARZ2_2003');
589 create_synonym('C', 'IGS_UC_U_IVOFFER_2003', 'IVOFFER', p_dblink_name, 'IGS_UC_IVOFFER_2003');
590
591 -- 2003 Synonyms to DUMMY
592 create_synonym('D', 'IGS_UC_U_IVSTARW_2003', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2003');
593 create_synonym('D', 'IGS_UC_U_IVSTATEMENT_2003', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2003');
594 create_synonym('D', 'IGS_UC_U_TRANIN_2003', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2003');
595
596 -- 2006 Synonyms to DUMMY
597 create_synonym('D', 'IGS_UC_U_IVSTARA_2006', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2006');
598 create_synonym('D', 'IGS_UC_U_IVFORMQUALS_2006', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2006');
599 create_synonym('D', 'IGS_UC_U_IVREFERENCE_2006', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2006');
600
601 -- 2007 Synonyms to DUMMY
602 create_synonym('D', 'IGS_UC_U_IVSTARA_2007', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2007');
603 create_synonym('D', 'IGS_UC_U_IVSTARN_2007', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2007');
604 create_synonym('D', 'IGS_UC_U_IVSTARK_2007', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2007');
605
606
607 -- For 2006 - Synonyms in both 2003 and 2004 should point to dummy and others to Hercules
608 ELSIF p_target_cycle = 2006 THEN
609
610 -- New 2006 Synonyms to HERCULES
611 create_synonym('C', 'IGS_UC_U_IVSTARA_2006', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2006');
612 create_synonym('C', 'IGS_UC_U_IVFORMQUALS_2006', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2006');
613 create_synonym('C', 'IGS_UC_U_IVREFERENCE_2006', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2006');
614
615 -- 2004/05 synonyms to HERCULES
616 create_synonym('C', 'IGS_UC_U_IVSTARJ_2004', 'IVSTARJ', p_dblink_name, 'IGS_UC_IVSTARJ_2004');
617 create_synonym('C', 'IGS_UC_U_IVSTARW_2004', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2004');
618 create_synonym('C', 'IGS_UC_U_TRANIN_2004', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2004');
619 create_synonym('C', 'IGS_UC_U_IVSTATEMENT_2004', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2004');
620
621 -- 2003 Synonyms to HERCULES
622 create_synonym('C', 'IGS_UC_U_IVQUALIFICATION_2003', 'IVQUALIFICATION', p_dblink_name, 'IGS_UC_IVQUALIFICATION_2003');
623 create_synonym('C', 'IGS_UC_U_IVSTARC_2003', 'IVSTARC', p_dblink_name, 'IGS_UC_IVSTARC_2003');
624 create_synonym('C', 'IGS_UC_U_IVSTARH_2003', 'IVSTARH', p_dblink_name, 'IGS_UC_IVSTARH_2003');
625 create_synonym('C', 'IGS_UC_U_IVSTARK_2003', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2003');
626 create_synonym('C', 'IGS_UC_U_IVSTARN_2003', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2003');
627 create_synonym('C', 'IGS_UC_U_IVSTARPQR_2003', 'IVSTARPQR', p_dblink_name, 'IGS_UC_IVSTARPQR_2003');
628 create_synonym('C', 'IGS_UC_U_IVSTARX_2003', 'IVSTARX', p_dblink_name, 'IGS_UC_IVSTARX_2003');
629 create_synonym('C', 'IGS_UC_U_IVSTARZ1_2003', 'IVSTARZ1', p_dblink_name, 'IGS_UC_IVSTARZ1_2003');
630 create_synonym('C', 'IGS_UC_U_IVOFFER_2003', 'IVOFFER', p_dblink_name, 'IGS_UC_IVOFFER_2003');
631
632 -- 2003 Synonyms to DUMMY
633 create_synonym('D', 'IGS_UC_U_IVSTARW_2003', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2003');
634 create_synonym('D', 'IGS_UC_U_IVSTATEMENT_2003', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2003');
635 create_synonym('D', 'IGS_UC_U_TRANIN_2003', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2003');
636 create_synonym('D', 'IGS_UC_U_IVSTARA_2003', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2003');
637 create_synonym('D', 'IGS_UC_U_IVSTARZ2_2003', 'IVSTARZ2', p_dblink_name, 'IGS_UC_IVSTARZ2_2003');
638
639 -- 2004/05 Synonyms to DUMMY
640 create_synonym('D', 'IGS_UC_U_IVFORMQUALS_2004', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2004');
641 create_synonym('D', 'IGS_UC_U_IVREFERENCE_2004', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2004');
642
643 -- 2007 Synonyms to DUMMY
644 create_synonym('D', 'IGS_UC_U_IVSTARA_2007', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2007');
645 create_synonym('D', 'IGS_UC_U_IVSTARN_2007', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2007');
646 create_synonym('D', 'IGS_UC_U_IVSTARK_2007', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2007');
647
648
649 -- For 2007 - Synonyms in both 2003 and 2004 should point to dummy and others to Hercules
650 ELSIF p_target_cycle = 2007 THEN
651
652 -- New 2007 Synonyms to HERCULES
653 create_synonym('C', 'IGS_UC_U_IVSTARA_2007', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2007');
654 create_synonym('C', 'IGS_UC_U_IVSTARN_2007', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2007');
655 create_synonym('C', 'IGS_UC_U_IVSTARK_2007', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2007');
656
657 -- 2006 Synonyms to HERCULES
658 create_synonym('C', 'IGS_UC_U_IVFORMQUALS_2006', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2006');
659 create_synonym('C', 'IGS_UC_U_IVREFERENCE_2006', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2006');
660
661 -- 2004/05 synonyms to HERCULES
662 create_synonym('C', 'IGS_UC_U_IVSTARJ_2004', 'IVSTARJ', p_dblink_name, 'IGS_UC_IVSTARJ_2004');
663 create_synonym('C', 'IGS_UC_U_IVSTARW_2004', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2004');
664 create_synonym('C', 'IGS_UC_U_TRANIN_2004', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2004');
665 create_synonym('C', 'IGS_UC_U_IVSTATEMENT_2004', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2004');
666
667 -- 2003 Synonyms to HERCULES
668 create_synonym('C', 'IGS_UC_U_IVQUALIFICATION_2003', 'IVQUALIFICATION', p_dblink_name, 'IGS_UC_IVQUALIFICATION_2003');
669 create_synonym('C', 'IGS_UC_U_IVSTARC_2003', 'IVSTARC', p_dblink_name, 'IGS_UC_IVSTARC_2003');
670 create_synonym('C', 'IGS_UC_U_IVSTARH_2003', 'IVSTARH', p_dblink_name, 'IGS_UC_IVSTARH_2003');
671 create_synonym('C', 'IGS_UC_U_IVSTARPQR_2003', 'IVSTARPQR', p_dblink_name, 'IGS_UC_IVSTARPQR_2003');
672 create_synonym('C', 'IGS_UC_U_IVSTARX_2003', 'IVSTARX', p_dblink_name, 'IGS_UC_IVSTARX_2003');
673 create_synonym('C', 'IGS_UC_U_IVSTARZ1_2003', 'IVSTARZ1', p_dblink_name, 'IGS_UC_IVSTARZ1_2003');
674 create_synonym('C', 'IGS_UC_U_IVOFFER_2003', 'IVOFFER', p_dblink_name, 'IGS_UC_IVOFFER_2003');
675
676 -- 2003 Synonyms to DUMMY
677 create_synonym('D', 'IGS_UC_U_IVSTARK_2003', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2003');
678 create_synonym('D', 'IGS_UC_U_IVSTARW_2003', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2003');
679 create_synonym('D', 'IGS_UC_U_IVSTATEMENT_2003', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2003');
680 create_synonym('D', 'IGS_UC_U_TRANIN_2003', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2003');
681 create_synonym('D', 'IGS_UC_U_IVSTARA_2003', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2003');
682 create_synonym('D', 'IGS_UC_U_IVSTARZ2_2003', 'IVSTARZ2', p_dblink_name, 'IGS_UC_IVSTARZ2_2003');
683 create_synonym('D', 'IGS_UC_U_IVSTARN_2003', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2003');
684
685 -- 2004/05 Synonyms to DUMMY
686 create_synonym('D', 'IGS_UC_U_IVFORMQUALS_2004', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2004');
687 create_synonym('D', 'IGS_UC_U_IVREFERENCE_2004', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2004');
688
689 -- 2006 Synonyms to DUMMY
690 create_synonym('D', 'IGS_UC_U_IVSTARA_2006', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2006');
691
692
693 END IF;
694
695 ELSE
696 -- For marvin all the synonyms should point to dummy tables
697 -- 2003 Synonyms to DUMMY
698 create_synonym('D', 'IGS_UC_U_IVQUALIFICATION_2003', 'IVQUALIFICATION', p_dblink_name, 'IGS_UC_IVQUALIFICATION_2003');
699 create_synonym('D', 'IGS_UC_U_IVSTARA_2003', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2003');
700 create_synonym('D', 'IGS_UC_U_IVSTARC_2003', 'IVSTARC', p_dblink_name, 'IGS_UC_IVSTARC_2003');
701 create_synonym('D', 'IGS_UC_U_IVSTARH_2003', 'IVSTARH', p_dblink_name, 'IGS_UC_IVSTARH_2003');
702 create_synonym('D', 'IGS_UC_U_IVSTARK_2003', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2003');
703 create_synonym('D', 'IGS_UC_U_IVSTARN_2003', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2003');
704 create_synonym('D', 'IGS_UC_U_IVSTARPQR_2003', 'IVSTARPQR', p_dblink_name, 'IGS_UC_IVSTARPQR_2003');
705 create_synonym('D', 'IGS_UC_U_IVSTARW_2003', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2003');
706 create_synonym('D', 'IGS_UC_U_IVSTARX_2003', 'IVSTARX', p_dblink_name, 'IGS_UC_IVSTARX_2003');
707 create_synonym('D', 'IGS_UC_U_IVSTARZ1_2003', 'IVSTARZ1', p_dblink_name, 'IGS_UC_IVSTARZ1_2003');
708 create_synonym('D', 'IGS_UC_U_IVSTARZ2_2003', 'IVSTARZ2', p_dblink_name, 'IGS_UC_IVSTARZ2_2003');
709 create_synonym('D', 'IGS_UC_U_IVSTATEMENT_2003', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2003');
710 create_synonym('D', 'IGS_UC_U_TRANIN_2003', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2003');
711 create_synonym('D', 'IGS_UC_U_IVOFFER_2003', 'IVOFFER', p_dblink_name, 'IGS_UC_IVOFFER_2003');
712
713 -- 2004/05 Synonyms to DUMMY
714 create_synonym('D', 'IGS_UC_U_IVFORMQUALS_2004', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2004');
715 create_synonym('D', 'IGS_UC_U_IVREFERENCE_2004', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2004');
716 create_synonym('D', 'IGS_UC_U_IVSTARJ_2004', 'IVSTARJ', p_dblink_name, 'IGS_UC_IVSTARJ_2004');
717 create_synonym('D', 'IGS_UC_U_IVSTARW_2004', 'IVSTARW', p_dblink_name, 'IGS_UC_IVSTARW_2004');
718 create_synonym('D', 'IGS_UC_U_TRANIN_2004', 'TRANIN', p_dblink_name, 'IGS_UC_TRANIN_2004');
719 create_synonym('D', 'IGS_UC_U_IVSTATEMENT_2004', 'IVSTATEMENT', p_dblink_name, 'IGS_UC_IVSTATEMENT_2004');
720
721 -- 2006 Synonyms to DUMMY
722 create_synonym('D', 'IGS_UC_U_IVSTARA_2006', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2006');
723 create_synonym('D', 'IGS_UC_U_IVFORMQUALS_2006', 'IVFORMQUALS', p_dblink_name, 'IGS_UC_IVFORMQUALS_2006');
724 create_synonym('D', 'IGS_UC_U_IVREFERENCE_2006', 'IVREFERENCE', p_dblink_name, 'IGS_UC_IVREFERENCE_2006');
725
726 -- 2007 Synonyms to DUMMY
727 create_synonym('D', 'IGS_UC_U_IVSTARA_2007', 'IVSTARA', p_dblink_name, 'IGS_UC_IVSTARA_2007');
728 create_synonym('D', 'IGS_UC_U_IVSTARN_2007', 'IVSTARN', p_dblink_name, 'IGS_UC_IVSTARN_2007');
729 create_synonym('D', 'IGS_UC_U_IVSTARK_2007', 'IVSTARK', p_dblink_name, 'IGS_UC_IVSTARK_2007');
730
731
732 END IF;
733
734
735 -- Fetching 'GTTR_INTERFACE'
736 OPEN cur_ucas_interface (p_target_cycle, 'G');
737 FETCH cur_ucas_interface INTO l_gttr_interface;
738 CLOSE cur_ucas_interface;
739
740 -- APPLICATION RELATED TABLES/VIEWS
741 -- GTTR synonyms for target cycle for applicant tables
742 IF (l_gttr_interface = 'H' AND p_target_cycle >= 2006) THEN
743
744 -- For 2006 - Synonyms in 2006 should point to Hercules
745 IF p_target_cycle = 2006 THEN
746
747 -- GTTR Small System Synonyms specific for 2006 pointing to HERCULES
748 -- 2006 Reference Data to HERCULES
749 create_synonym('C', 'IGS_UC_G_CVGREFAMENDMENTS_2006', 'CVGREFAMENDMENTS', p_dblink_name, 'IGS_UC_CVGREFAMENDMENTS_2006');
750 create_synonym('C', 'IGS_UC_G_CVGNAME_2006', 'CVGNAME', p_dblink_name, 'IGS_UC_CVNAME_2003');
751 create_synonym('C', 'IGS_UC_G_CVGREFDEGREESUBJ_2006', 'CVGREFDEGREESUBJECT', p_dblink_name, 'IGS_UC_CVGREFDEGREESUBJ_2006');
752
753 -- 2006 Application Data to HERCULES
754 create_synonym('C', 'IGS_UC_G_IVGOFFER_2006', 'IVGOFFER', p_dblink_name, 'IGS_UC_IVGOFFER_2006');
755 create_synonym('C', 'IGS_UC_G_IVGREFERENCE_2006', 'IVGREFERENCE', p_dblink_name, 'IGS_UC_IVGREFERENCE_2006');
756 create_synonym('C', 'IGS_UC_G_IVGSTARA_2006', 'IVGSTARA', p_dblink_name, 'IGS_UC_IVGSTARA_2006');
757 create_synonym('C', 'IGS_UC_G_IVGSTARG_2006', 'IVGSTARG', p_dblink_name, 'IGS_UC_IVGSTARG_2006');
758 create_synonym('C', 'IGS_UC_G_IVGSTARH_2006', 'IVGSTARH', p_dblink_name, 'IGS_UC_IVGSTARH_2006');
759 create_synonym('C', 'IGS_UC_G_IVGSTARK_2006', 'IVGSTARK', p_dblink_name, 'IGS_UC_IVGSTARK_2006');
760 create_synonym('C', 'IGS_UC_G_IVGSTARN_2006', 'IVGSTARN', p_dblink_name, 'IGS_UC_IVGSTARN_2006');
761 create_synonym('C', 'IGS_UC_G_IVGSTARW_2006', 'IVGSTARW', p_dblink_name, 'IGS_UC_IVGSTARW_2006');
762 create_synonym('C', 'IGS_UC_G_IVGSTARX_2006', 'IVGSTARX', p_dblink_name, 'IGS_UC_IVGSTARX_2006');
763 create_synonym('C', 'IGS_UC_G_IVGSTATEMENT_2006', 'IVGSTATEMENT', p_dblink_name, 'IGS_UC_IVGSTATEMENT_2006');
764
765 -- 2007 Reference Data to DUMMY
766 create_synonym('D', 'IGS_UC_G_CVGCOURSE_2007', 'CVGCOURSE', p_dblink_name, 'IGS_UC_CVGCOURSE_2007');
767
768 -- 2007 Application Data to DUMMY
769 create_synonym('D', 'IGS_UC_G_IVGSTARA_2007', 'IVGSTARA', p_dblink_name, 'IGS_UC_IVGSTARA_2007');
770 create_synonym('D', 'IGS_UC_G_IVGSTARN_2007', 'IVGSTARN', p_dblink_name, 'IGS_UC_IVGSTARN_2007');
771 create_synonym('D', 'IGS_UC_G_IVGSTARK_2007', 'IVGSTARK', p_dblink_name, 'IGS_UC_IVGSTARK_2007');
772 create_synonym('D', 'IGS_UC_G_IVGSTARW_2007', 'IVGSTARW', p_dblink_name, 'IGS_UC_IVGSTARW_2007');
773
774
775 ELSIF p_target_cycle = 2007 THEN
776
777 -- 2007 Reference Data to HERCULES
778 create_synonym('C', 'IGS_UC_G_CVGCOURSE_2007', 'CVGCOURSE', p_dblink_name, 'IGS_UC_CVGCOURSE_2007');
779
780 -- 2006 Reference Data to HERCULES
781 create_synonym('C', 'IGS_UC_G_CVGREFAMENDMENTS_2006', 'CVGREFAMENDMENTS', p_dblink_name, 'IGS_UC_CVGREFAMENDMENTS_2006');
782 create_synonym('C', 'IGS_UC_G_CVGNAME_2006', 'CVGNAME', p_dblink_name, 'IGS_UC_CVNAME_2003');
783 create_synonym('C', 'IGS_UC_G_CVGREFDEGREESUBJ_2006', 'CVGREFDEGREESUBJECT', p_dblink_name, 'IGS_UC_CVGREFDEGREESUBJ_2006');
784
785 -- 2007 Application Data to HERCULES
786 create_synonym('C', 'IGS_UC_G_IVGSTARA_2007', 'IVGSTARA', p_dblink_name, 'IGS_UC_IVGSTARA_2007');
787 create_synonym('C', 'IGS_UC_G_IVGSTARN_2007', 'IVGSTARN', p_dblink_name, 'IGS_UC_IVGSTARN_2007');
788 create_synonym('C', 'IGS_UC_G_IVGSTARK_2007', 'IVGSTARK', p_dblink_name, 'IGS_UC_IVGSTARK_2007');
789 create_synonym('C', 'IGS_UC_G_IVGSTARW_2007', 'IVGSTARW', p_dblink_name, 'IGS_UC_IVGSTARW_2007');
790
791 -- 2006 Application Data to HERCULES
792 create_synonym('C', 'IGS_UC_G_IVGOFFER_2006', 'IVGOFFER', p_dblink_name, 'IGS_UC_IVGOFFER_2006');
793 create_synonym('C', 'IGS_UC_G_IVGREFERENCE_2006', 'IVGREFERENCE', p_dblink_name, 'IGS_UC_IVGREFERENCE_2006');
794 create_synonym('C', 'IGS_UC_G_IVGSTARG_2006', 'IVGSTARG', p_dblink_name, 'IGS_UC_IVGSTARG_2006');
795 create_synonym('C', 'IGS_UC_G_IVGSTARH_2006', 'IVGSTARH', p_dblink_name, 'IGS_UC_IVGSTARH_2006');
796 create_synonym('C', 'IGS_UC_G_IVGSTARX_2006', 'IVGSTARX', p_dblink_name, 'IGS_UC_IVGSTARX_2006');
797 create_synonym('C', 'IGS_UC_G_IVGSTATEMENT_2006', 'IVGSTATEMENT', p_dblink_name, 'IGS_UC_IVGSTATEMENT_2006');
798
799 -- 2006 Application Data to DUMMY
800 create_synonym('D', 'IGS_UC_G_IVGSTARA_2006', 'IVGSTARA', p_dblink_name, 'IGS_UC_IVGSTARA_2006');
801 create_synonym('D', 'IGS_UC_G_IVGSTARN_2006', 'IVGSTARN', p_dblink_name, 'IGS_UC_IVGSTARN_2006');
802 create_synonym('D', 'IGS_UC_G_IVGSTARK_2006', 'IVGSTARK', p_dblink_name, 'IGS_UC_IVGSTARK_2006');
803 create_synonym('D', 'IGS_UC_G_IVGSTARW_2006', 'IVGSTARW', p_dblink_name, 'IGS_UC_IVGSTARW_2006');
804
805 END IF;
806
807 ELSE
808
809 -- For MARVIN (or prior 2006) all GTTR Small System Synonyms pointing to DUMMY
810 -- Reference Data
811 create_synonym('D', 'IGS_UC_G_CVGREFAMENDMENTS_2006', 'CVGREFAMENDMENTS', p_dblink_name, 'IGS_UC_CVGREFAMENDMENTS_2006');
812 create_synonym('D', 'IGS_UC_G_CVGNAME_2006', 'CVGNAME', p_dblink_name, 'IGS_UC_CVNAME_2003');
813 create_synonym('D', 'IGS_UC_G_CVGREFDEGREESUBJ_2006', 'CVGREFDEGREESUBJECT', p_dblink_name, 'IGS_UC_CVGREFDEGREESUBJ_2006');
814
815 -- 2006 Application Data
816 create_synonym('D', 'IGS_UC_G_IVGOFFER_2006', 'IVGOFFER', p_dblink_name, 'IGS_UC_IVGOFFER_2006');
817 create_synonym('D', 'IGS_UC_G_IVGREFERENCE_2006', 'IVGREFERENCE', p_dblink_name, 'IGS_UC_IVGREFERENCE_2006');
818 create_synonym('D', 'IGS_UC_G_IVGSTARA_2006', 'IVGSTARA', p_dblink_name, 'IGS_UC_IVGSTARA_2006');
819 create_synonym('D', 'IGS_UC_G_IVGSTARG_2006', 'IVGSTARG', p_dblink_name, 'IGS_UC_IVGSTARG_2006');
820 create_synonym('D', 'IGS_UC_G_IVGSTARH_2006', 'IVGSTARH', p_dblink_name, 'IGS_UC_IVGSTARH_2006');
821 create_synonym('D', 'IGS_UC_G_IVGSTARK_2006', 'IVGSTARK', p_dblink_name, 'IGS_UC_IVGSTARK_2006');
822 create_synonym('D', 'IGS_UC_G_IVGSTARN_2006', 'IVGSTARN', p_dblink_name, 'IGS_UC_IVGSTARN_2006');
823 create_synonym('D', 'IGS_UC_G_IVGSTARW_2006', 'IVGSTARW', p_dblink_name, 'IGS_UC_IVGSTARW_2006');
824 create_synonym('D', 'IGS_UC_G_IVGSTARX_2006', 'IVGSTARX', p_dblink_name, 'IGS_UC_IVGSTARX_2006');
825 create_synonym('D', 'IGS_UC_G_IVGSTATEMENT_2006', 'IVGSTATEMENT', p_dblink_name, 'IGS_UC_IVGSTATEMENT_2006');
826
827 -- 2007 Reference Data
828 create_synonym('D', 'IGS_UC_G_CVGCOURSE_2007', 'CVGCOURSE', p_dblink_name, 'IGS_UC_CVGCOURSE_2007');
829
830 -- 2007 Application Data
831 create_synonym('D', 'IGS_UC_G_IVGSTARA_2007', 'IVGSTARA', p_dblink_name, 'IGS_UC_IVGSTARA_2007');
832 create_synonym('D', 'IGS_UC_G_IVGSTARN_2007', 'IVGSTARN', p_dblink_name, 'IGS_UC_IVGSTARN_2007');
833 create_synonym('D', 'IGS_UC_G_IVGSTARK_2007', 'IVGSTARK', p_dblink_name, 'IGS_UC_IVGSTARK_2007');
834 create_synonym('D', 'IGS_UC_G_IVGSTARW_2007', 'IVGSTARW', p_dblink_name, 'IGS_UC_IVGSTARW_2007');
835
836 END IF;
837
838
839 -- Fetching 'NMAS_INTERFACE'
840 OPEN cur_ucas_interface (p_target_cycle,'N');
841 FETCH cur_ucas_interface INTO l_nmas_interface;
842 CLOSE cur_ucas_interface;
843
844 -- APPLICATION RELATED TABLES/VIEWS
845 -- NMAS synonyms for target cycle for applicant tables
846 IF (l_nmas_interface = 'H' AND p_target_cycle >= 2006 ) THEN
847
848 -- For 2006 - Synonyms in 2006 should point to Hercules
849 IF p_target_cycle = 2006 THEN
850
851 -- NMAS Small System Synonyms specific for 2006 pointing to HERCULES
852 -- 2006 Reference Data to HERCULES
853 create_synonym('C', 'IGS_UC_N_CVNNAME_2006', 'CVNNAME', p_dblink_name, 'IGS_UC_CVNAME_2003');
854
855 -- 2006 Application Data to HERCULES
856 create_synonym('C', 'IGS_UC_N_IVNOFFER_2006', 'IVNOFFER', p_dblink_name, 'IGS_UC_IVNOFFER_2006');
857 create_synonym('C', 'IGS_UC_N_IVNREFERENCE_2006', 'IVNREFERENCE', p_dblink_name, 'IGS_UC_IVNREFERENCE_2006');
858 create_synonym('C', 'IGS_UC_N_IVNSTARA_2006', 'IVNSTARA', p_dblink_name, 'IGS_UC_IVNSTARA_2006');
859 create_synonym('C', 'IGS_UC_N_IVNSTARC_2006', 'IVNSTARC', p_dblink_name, 'IGS_UC_IVNSTARC_2006');
860 create_synonym('C', 'IGS_UC_N_IVNSTARH_2006', 'IVNSTARH', p_dblink_name, 'IGS_UC_IVNSTARH_2006');
861 create_synonym('C', 'IGS_UC_N_IVNSTARK_2006', 'IVNSTARK', p_dblink_name, 'IGS_UC_IVNSTARK_2006');
862 create_synonym('C', 'IGS_UC_N_IVNSTARN_2006', 'IVNSTARN', p_dblink_name, 'IGS_UC_IVNSTARN_2006');
863 create_synonym('C', 'IGS_UC_N_IVNSTARW_2006', 'IVNSTARW', p_dblink_name, 'IGS_UC_IVNSTARW_2006');
864 create_synonym('C', 'IGS_UC_N_IVNSTARX_2006', 'IVNSTARX', p_dblink_name, 'IGS_UC_IVNSTARX_2006');
865 create_synonym('C', 'IGS_UC_N_IVNSTARZ1_2006', 'IVNSTARZ1', p_dblink_name, 'IGS_UC_IVNSTARZ1_2006');
866 create_synonym('C', 'IGS_UC_N_IVNSTATEMENT_2006', 'IVNSTATEMENT', p_dblink_name, 'IGS_UC_IVNSTATEMENT_2006');
867
868 -- 2007 Reference Data to DUMMY
869 create_synonym('D', 'IGS_UC_N_CVNCOURSE_2007', 'CVNCOURSE', p_dblink_name, 'IGS_UC_CVNCOURSE_2007');
870
871 -- 2007 Application Data to DUMMY
872 create_synonym('D', 'IGS_UC_N_IVNSTARA_2007', 'IVNSTARA', p_dblink_name, 'IGS_UC_IVNSTARA_2007');
873 create_synonym('D', 'IGS_UC_N_IVNSTARN_2007', 'IVNSTARN', p_dblink_name, 'IGS_UC_IVNSTARN_2007');
874 create_synonym('D', 'IGS_UC_N_IVNSTARK_2007', 'IVNSTARK', p_dblink_name, 'IGS_UC_IVNSTARK_2007');
875 create_synonym('D', 'IGS_UC_N_IVNSTARW_2007', 'IVNSTARW', p_dblink_name, 'IGS_UC_IVNSTARW_2007');
876
877 ELSIF p_target_cycle = 2007 THEN
878
879 -- 2007 Reference Data to HERCULES
880 create_synonym('C', 'IGS_UC_N_CVNCOURSE_2007', 'CVNCOURSE', p_dblink_name, 'IGS_UC_CVNCOURSE_2007');
881
882 -- 2006 Reference Data to HERCULES
883 create_synonym('C', 'IGS_UC_N_CVNNAME_2006', 'CVNNAME', p_dblink_name, 'IGS_UC_CVNAME_2003');
884
885 -- 2007 Application Data to HERCULES
886 create_synonym('C', 'IGS_UC_N_IVNSTARA_2007', 'IVNSTARA', p_dblink_name, 'IGS_UC_IVNSTARA_2007');
887 create_synonym('C', 'IGS_UC_N_IVNSTARN_2007', 'IVNSTARN', p_dblink_name, 'IGS_UC_IVNSTARN_2007');
888 create_synonym('C', 'IGS_UC_N_IVNSTARK_2007', 'IVNSTARK', p_dblink_name, 'IGS_UC_IVNSTARK_2007');
889 create_synonym('C', 'IGS_UC_N_IVNSTARW_2007', 'IVNSTARW', p_dblink_name, 'IGS_UC_IVNSTARW_2007');
890
891 -- 2006 Application Data to HERCULES
892 create_synonym('C', 'IGS_UC_N_IVNOFFER_2006', 'IVNOFFER', p_dblink_name, 'IGS_UC_IVNOFFER_2006');
893 create_synonym('C', 'IGS_UC_N_IVNREFERENCE_2006', 'IVNREFERENCE', p_dblink_name, 'IGS_UC_IVNREFERENCE_2006');
894 create_synonym('C', 'IGS_UC_N_IVNSTARC_2006', 'IVNSTARC', p_dblink_name, 'IGS_UC_IVNSTARC_2006');
895 create_synonym('C', 'IGS_UC_N_IVNSTARH_2006', 'IVNSTARH', p_dblink_name, 'IGS_UC_IVNSTARH_2006');
896 create_synonym('C', 'IGS_UC_N_IVNSTARX_2006', 'IVNSTARX', p_dblink_name, 'IGS_UC_IVNSTARX_2006');
897 create_synonym('C', 'IGS_UC_N_IVNSTARZ1_2006', 'IVNSTARZ1', p_dblink_name, 'IGS_UC_IVNSTARZ1_2006');
898 create_synonym('C', 'IGS_UC_N_IVNSTATEMENT_2006', 'IVNSTATEMENT', p_dblink_name, 'IGS_UC_IVNSTATEMENT_2006');
899
900 -- 2006 Application Data to DUMMY
901 create_synonym('D', 'IGS_UC_N_IVNSTARA_2006', 'IVNSTARA', p_dblink_name, 'IGS_UC_IVNSTARA_2006');
902 create_synonym('D', 'IGS_UC_N_IVNSTARN_2006', 'IVNSTARN', p_dblink_name, 'IGS_UC_IVNSTARN_2006');
903 create_synonym('D', 'IGS_UC_N_IVNSTARK_2006', 'IVNSTARK', p_dblink_name, 'IGS_UC_IVNSTARK_2006');
904 create_synonym('D', 'IGS_UC_N_IVNSTARW_2006', 'IVNSTARW', p_dblink_name, 'IGS_UC_IVNSTARW_2006');
905
906 END IF;
907
908 ELSE
909
910 -- For MARVIN (or prior 2006) all NMAS Small System Synonyms pointing to DUMMY
911 -- Reference Data
912 create_synonym('D', 'IGS_UC_N_CVNNAME_2006', 'CVNNAME', p_dblink_name, 'IGS_UC_CVNAME_2003');
913
914 -- Application Data
915 create_synonym('D', 'IGS_UC_N_IVNOFFER_2006', 'IVNOFFER', p_dblink_name, 'IGS_UC_IVNOFFER_2006');
916 create_synonym('D', 'IGS_UC_N_IVNREFERENCE_2006', 'IVNREFERENCE', p_dblink_name, 'IGS_UC_IVNREFERENCE_2006');
917 create_synonym('D', 'IGS_UC_N_IVNSTARA_2006', 'IVNSTARA', p_dblink_name, 'IGS_UC_IVNSTARA_2006');
918 create_synonym('D', 'IGS_UC_N_IVNSTARC_2006', 'IVNSTARC', p_dblink_name, 'IGS_UC_IVNSTARC_2006');
919 create_synonym('D', 'IGS_UC_N_IVNSTARH_2006', 'IVNSTARH', p_dblink_name, 'IGS_UC_IVNSTARH_2006');
920 create_synonym('D', 'IGS_UC_N_IVNSTARK_2006', 'IVNSTARK', p_dblink_name, 'IGS_UC_IVNSTARK_2006');
921 create_synonym('D', 'IGS_UC_N_IVNSTARN_2006', 'IVNSTARN', p_dblink_name, 'IGS_UC_IVNSTARN_2006');
922 create_synonym('D', 'IGS_UC_N_IVNSTARW_2006', 'IVNSTARW', p_dblink_name, 'IGS_UC_IVNSTARW_2006');
923 create_synonym('D', 'IGS_UC_N_IVNSTARX_2006', 'IVNSTARX', p_dblink_name, 'IGS_UC_IVNSTARX_2006');
924 create_synonym('D', 'IGS_UC_N_IVNSTARZ1_2006', 'IVNSTARZ1', p_dblink_name, 'IGS_UC_IVNSTARZ1_2006');
925 create_synonym('D', 'IGS_UC_N_IVNSTATEMENT_2006', 'IVNSTATEMENT', p_dblink_name, 'IGS_UC_IVNSTATEMENT_2006');
926
927 -- 2007 Reference Data
928 create_synonym('D', 'IGS_UC_N_CVNCOURSE_2007', 'CVNCOURSE', p_dblink_name, 'IGS_UC_CVNCOURSE_2007');
929
930 -- 2007 Application Data
931 create_synonym('D', 'IGS_UC_N_IVNSTARA_2007', 'IVNSTARA', p_dblink_name, 'IGS_UC_IVNSTARA_2007');
932 create_synonym('D', 'IGS_UC_N_IVNSTARN_2007', 'IVNSTARN', p_dblink_name, 'IGS_UC_IVNSTARN_2007');
933 create_synonym('D', 'IGS_UC_N_IVNSTARK_2007', 'IVNSTARK', p_dblink_name, 'IGS_UC_IVNSTARK_2007');
934 create_synonym('D', 'IGS_UC_N_IVNSTARW_2007', 'IVNSTARW', p_dblink_name, 'IGS_UC_IVNSTARW_2007');
935
936 END IF;
937
938 -- If any synonyms failed to create, then set return code = warning
939 IF g_synonym_fail THEN
940 retcode := 1;
941 END IF;
942
943 -- Compile all Data Source Views
944 BEGIN
945 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_CVCONTROL_2003_V COMPILE');
946 log_msg('IGS_UC_CVCONTROL_2003_V', 'O');
947 EXCEPTION
948 WHEN OTHERS THEN
949 retcode := 1 ;
950 log_msg('IGS_UC_CVCONTROL_2003_V', 'I');
951 END;
952
953 BEGIN
954 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVOFFER_2003_V COMPILE');
955 log_msg('IGS_UC_IVOFFER_2003_V', 'O');
956 EXCEPTION
957 WHEN OTHERS THEN
958 retcode := 1 ;
959 log_msg('IGS_UC_IVOFFER_2003_V', 'I');
960 END;
961
962 BEGIN
963 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVQUALIFICATION_2003_V COMPILE');
964 log_msg('IGS_UC_IVQUALIFICATION_2003_V', 'O');
965 EXCEPTION
966 WHEN OTHERS THEN
967 retcode := 1 ;
968 log_msg('IGS_UC_IVQUALIFICATION_2003_V', 'I');
969 END;
970
971 BEGIN
972 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARA_2003_V COMPILE');
973 log_msg('IGS_UC_IVSTARA_2003_V', 'O');
974 EXCEPTION
975 WHEN OTHERS THEN
976 retcode := 1 ;
977 log_msg('IGS_UC_IVSTARA_2003_V', 'I');
978 END;
979
980 BEGIN
981 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARC_2003_V COMPILE');
982 log_msg('IGS_UC_IVSTARC_2003_V', 'O');
983 EXCEPTION
984 WHEN OTHERS THEN
985 retcode := 1 ;
986 log_msg('IGS_UC_IVSTARC_2003_V', 'I');
987 END;
988
989 BEGIN
990 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARH_2003_V COMPILE');
991 log_msg('IGS_UC_IVSTARH_2003_V', 'O');
992 EXCEPTION
993 WHEN OTHERS THEN
994 retcode := 1 ;
995 log_msg('IGS_UC_IVSTARH_2003_V', 'I');
996 END;
997
998 BEGIN
999 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARJ_2004_V COMPILE');
1000 log_msg('IGS_UC_IVSTARJ_2004_V', 'O');
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 retcode := 1 ;
1004 log_msg('IGS_UC_IVSTARJ_2004_V', 'I');
1005 END;
1006
1007 BEGIN
1008 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARK_2003_V COMPILE');
1009 log_msg('IGS_UC_IVSTARK_2003_V', 'O');
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 retcode := 1 ;
1013 log_msg('IGS_UC_IVSTARK_2003_V', 'I');
1014 END;
1015
1016 BEGIN
1017 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARN_2003_V COMPILE');
1018 log_msg('IGS_UC_IVSTARN_2003_V', 'O');
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 retcode := 1 ;
1022 log_msg('IGS_UC_IVSTARN_2003_V', 'I');
1023 END;
1024
1025 BEGIN
1026 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARPQR_2003_V COMPILE');
1027 log_msg('IGS_UC_IVSTARPQR_2003_V', 'O');
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 retcode := 1 ;
1031 log_msg('IGS_UC_IVSTARPQR_2003_V', 'I');
1032 END;
1033
1034 BEGIN
1035 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARW_2003_V COMPILE');
1036 log_msg('IGS_UC_IVSTARW_2003_V', 'O');
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 retcode := 1 ;
1040 log_msg('IGS_UC_IVSTARW_2003_V', 'I');
1041 END;
1042
1043 BEGIN
1044 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARX_2003_V COMPILE');
1045 log_msg('IGS_UC_IVSTARX_2003_V', 'O');
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 retcode := 1 ;
1049 log_msg('IGS_UC_IVSTARX_2003_V', 'I');
1050 END;
1051
1052 BEGIN
1053 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARZ1_2003_V COMPILE');
1054 log_msg('IGS_UC_IVSTARZ1_2003_V', 'O');
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 retcode := 1 ;
1058 log_msg('IGS_UC_IVSTARZ1_2003_V', 'I');
1059 END;
1060
1061 BEGIN
1062 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARZ2_2003_V COMPILE');
1063 log_msg('IGS_UC_IVSTARZ2_2003_V', 'O');
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 retcode := 1 ;
1067 log_msg('IGS_UC_IVSTARZ2_2003_V', 'I');
1068 END;
1069
1070 BEGIN
1071 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTATEMENT_2003_V COMPILE');
1072 log_msg('IGS_UC_IVSTATEMENT_2003_V', 'O');
1073 EXCEPTION
1074 WHEN OTHERS THEN
1075 retcode := 1 ;
1076 log_msg('IGS_UC_IVSTATEMENT_2003_V', 'I');
1077 END;
1078
1079 -- smaddali added new view IGS_UC_IVSTATEMENT_2004_V for bug#3098810
1080 BEGIN
1081 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTATEMENT_2004_V COMPILE');
1082 log_msg('IGS_UC_IVSTATEMENT_2004_V', 'O');
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 retcode := 1 ;
1086 log_msg('IGS_UC_IVSTATEMENT_2004_V', 'I');
1087 END;
1088
1089
1090 BEGIN
1091 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVFORMQUALS_2004_V COMPILE');
1092 log_msg('IGS_UC_IVFORMQUALS_2004_V', 'O');
1093 EXCEPTION
1094 WHEN OTHERS THEN
1095 retcode := 1 ;
1096 log_msg('IGS_UC_IVFORMQUALS_2004_V', 'I');
1097 END;
1098
1099 BEGIN
1100 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVREFERENCE_2004_V COMPILE');
1101 log_msg('IGS_UC_IVREFERENCE_2004_V', 'O');
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 retcode := 1 ;
1105 log_msg('IGS_UC_IVREFERENCE_2004_V', 'I');
1106 END;
1107
1108 BEGIN
1109 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVREFERENCE_2006_V COMPILE');
1110 log_msg('IGS_UC_IVREFERENCE_2006_V', 'O');
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 retcode := 1 ;
1114 log_msg('IGS_UC_IVREFERENCE_2006_V', 'I');
1115 END;
1116
1117 BEGIN
1118 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARA_2006_V COMPILE');
1119 log_msg('IGS_UC_IVSTARA_2006_V', 'O');
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 retcode := 1 ;
1123 log_msg('IGS_UC_IVSTARA_2006_V', 'I');
1124 END;
1125
1126 BEGIN
1127 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVFORMQUALS_2006_V COMPILE');
1128 log_msg('IGS_UC_IVFORMQUALS_2006_V', 'O');
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 retcode := 1 ;
1132 log_msg('IGS_UC_IVFORMQUALS_2006_V', 'I');
1133 END;
1134
1135 BEGIN
1136 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARA_2007_V COMPILE');
1137 log_msg('IGS_UC_IVSTARA_2007_V', 'O');
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 retcode := 1 ;
1141 log_msg('IGS_UC_IVSTARA_2007_V', 'I');
1142 END;
1143
1144 BEGIN
1145 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARN_2007_V COMPILE');
1146 log_msg('IGS_UC_IVSTARN_2007_V', 'O');
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 retcode := 1 ;
1150 log_msg('IGS_UC_IVSTARN_2007_V', 'I');
1151 END;
1152
1153 BEGIN
1154 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVSTARK_2007_V COMPILE');
1155 log_msg('IGS_UC_IVSTARK_2007_V', 'O');
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158 retcode := 1 ;
1159 log_msg('IGS_UC_IVSTARK_2007_V', 'I');
1160 END;
1161
1162 -- Small System 2006 Views
1163 BEGIN
1164 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGOFFER_2006_V COMPILE');
1165 log_msg('IGS_UC_IVGOFFER_2006_V', 'O');
1166 EXCEPTION
1167 WHEN OTHERS THEN
1168 retcode := 1 ;
1169 log_msg('IGS_UC_IVGOFFER_2006_V', 'I');
1170 END;
1171
1172 BEGIN
1173 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNOFFER_2006_V COMPILE');
1174 log_msg('IGS_UC_IVNOFFER_2006_V', 'O');
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 retcode := 1 ;
1178 log_msg('IGS_UC_IVNOFFER_2006_V', 'I');
1179 END;
1180
1181 BEGIN
1182 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARA_2006_V COMPILE');
1183 log_msg('IGS_UC_IVGSTARA_2006_V', 'O');
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 retcode := 1 ;
1187 log_msg('IGS_UC_IVGSTARA_2006_V', 'I');
1188 END;
1189
1190 BEGIN
1191 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARA_2006_V COMPILE');
1192 log_msg('IGS_UC_IVNSTARA_2006_V', 'O');
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 retcode := 1 ;
1196 log_msg('IGS_UC_IVNSTARA_2006_V', 'I');
1197 END;
1198
1199 BEGIN
1200 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARC_2006_V COMPILE');
1201 log_msg('IGS_UC_IVNSTARC_2006_V', 'O');
1202 EXCEPTION
1203 WHEN OTHERS THEN
1204 retcode := 1 ;
1205 log_msg('IGS_UC_IVNSTARC_2006_V', 'I');
1206 END;
1207
1208 BEGIN
1209 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARG_2006_V COMPILE');
1210 log_msg('IGS_UC_IVGSTARG_2006_V', 'O');
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 retcode := 1 ;
1214 log_msg('IGS_UC_IVGSTARG_2006_V', 'I');
1215 END;
1216
1217 BEGIN
1218 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARH_2006_V COMPILE');
1219 log_msg('IGS_UC_IVGSTARH_2006_V', 'O');
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 retcode := 1 ;
1223 log_msg('IGS_UC_IVGSTARH_2006_V', 'I');
1224 END;
1225
1226 BEGIN
1227 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARH_2006_V COMPILE');
1228 log_msg('IGS_UC_IVNSTARH_2006_V', 'O');
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 retcode := 1 ;
1232 log_msg('IGS_UC_IVNSTARH_2006_V', 'I');
1233 END;
1234
1235 BEGIN
1236 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARK_2006_V COMPILE');
1237 log_msg('IGS_UC_IVGSTARK_2006_V', 'O');
1238 EXCEPTION
1239 WHEN OTHERS THEN
1240 retcode := 1 ;
1241 log_msg('IGS_UC_IVGSTARK_2006_V', 'I');
1242 END;
1243
1244 BEGIN
1245 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARK_2006_V COMPILE');
1246 log_msg('IGS_UC_IVNSTARK_2006_V', 'O');
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 retcode := 1 ;
1250 log_msg('IGS_UC_IVNSTARK_2006_V', 'I');
1251 END;
1252
1253 BEGIN
1254 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARN_2006_V COMPILE');
1255 log_msg('IGS_UC_IVGSTARN_2006_V', 'O');
1256 EXCEPTION
1257 WHEN OTHERS THEN
1258 retcode := 1 ;
1259 log_msg('IGS_UC_IVGSTARN_2006_V', 'I');
1260 END;
1261
1262 BEGIN
1263 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARN_2006_V COMPILE');
1264 log_msg('IGS_UC_IVNSTARN_2006_V', 'O');
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 retcode := 1 ;
1268 log_msg('IGS_UC_IVNSTARN_2006_V', 'I');
1269 END;
1270
1271 BEGIN
1272 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARW_2006_V COMPILE');
1273 log_msg('IGS_UC_IVGSTARW_2006_V', 'O');
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 retcode := 1 ;
1277 log_msg('IGS_UC_IVGSTARW_2006_V', 'I');
1278 END;
1279
1280 BEGIN
1281 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARW_2006_V COMPILE');
1282 log_msg('IGS_UC_IVNSTARW_2006_V', 'O');
1283 EXCEPTION
1284 WHEN OTHERS THEN
1285 retcode := 1 ;
1286 log_msg('IGS_UC_IVNSTARW_2006_V', 'I');
1287 END;
1288
1289 BEGIN
1290 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARX_2006_V COMPILE');
1291 log_msg('IGS_UC_IVGSTARX_2006_V', 'O');
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 retcode := 1 ;
1295 log_msg('IGS_UC_IVGSTARX_2006_V', 'I');
1296 END;
1297
1298 BEGIN
1299 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARX_2006_V COMPILE');
1300 log_msg('IGS_UC_IVNSTARX_2006_V', 'O');
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 retcode := 1 ;
1304 log_msg('IGS_UC_IVNSTARX_2006_V', 'I');
1305 END;
1306
1307 BEGIN
1308 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARZ1_2006_V COMPILE');
1309 log_msg('IGS_UC_IVNSTARZ1_2006_V', 'O');
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 retcode := 1 ;
1313 log_msg('IGS_UC_IVNSTARZ1_2006_V', 'I');
1314 END;
1315
1316 BEGIN
1317 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTATEMENT_2006_V COMPILE');
1318 log_msg('IGS_UC_IVGSTATEMENT_2006_V', 'O');
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 retcode := 1 ;
1322 log_msg('IGS_UC_IVGSTATEMENT_2006_V', 'I');
1323 END;
1324
1325 BEGIN
1326 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTATEMENT_2006_V COMPILE');
1327 log_msg('IGS_UC_IVNSTATEMENT_2006_V', 'O');
1328 EXCEPTION
1329 WHEN OTHERS THEN
1330 retcode := 1 ;
1331 log_msg('IGS_UC_IVNSTATEMENT_2006_V', 'I');
1332 END;
1333
1334 BEGIN
1335 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGREFERENCE_2006_V COMPILE');
1336 log_msg('IGS_UC_IVGREFERENCE_2006_V', 'O');
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 retcode := 1 ;
1340 log_msg('IGS_UC_IVGREFERENCE_2006_V', 'I');
1341 END;
1342
1343 BEGIN
1344 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNREFERENCE_2006_V COMPILE');
1345 log_msg('IGS_UC_IVNREFERENCE_2006_V', 'O');
1346 EXCEPTION
1347 WHEN OTHERS THEN
1348 retcode := 1 ;
1349 log_msg('IGS_UC_IVNREFERENCE_2006_V', 'I');
1350 END;
1351
1352 -- Small System 2007 views
1353 BEGIN
1354 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARA_2007_V COMPILE');
1355 log_msg('IGS_UC_IVGSTARA_2007_V', 'O');
1356 EXCEPTION
1357 WHEN OTHERS THEN
1358 retcode := 1 ;
1359 log_msg('IGS_UC_IVGSTARA_2007_V', 'I');
1360 END;
1361
1362 BEGIN
1363 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARA_2007_V COMPILE');
1364 log_msg('IGS_UC_IVNSTARA_2007_V', 'O');
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 retcode := 1 ;
1368 log_msg('IGS_UC_IVNSTARA_2007_V', 'I');
1369 END;
1370
1371 BEGIN
1372 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARN_2007_V COMPILE');
1373 log_msg('IGS_UC_IVGSTARN_2007_V', 'O');
1374 EXCEPTION
1375 WHEN OTHERS THEN
1376 retcode := 1 ;
1377 log_msg('IGS_UC_IVGSTARN_2007_V', 'I');
1378 END;
1379
1380 BEGIN
1381 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARN_2007_V COMPILE');
1382 log_msg('IGS_UC_IVNSTARN_2007_V', 'O');
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 retcode := 1 ;
1386 log_msg('IGS_UC_IVNSTARN_2007_V', 'I');
1387 END;
1388
1389 BEGIN
1390 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARK_2007_V COMPILE');
1391 log_msg('IGS_UC_IVGSTARK_2007_V', 'O');
1392 EXCEPTION
1393 WHEN OTHERS THEN
1394 retcode := 1 ;
1395 log_msg('IGS_UC_IVGSTARK_2007_V', 'I');
1396 END;
1397
1398 BEGIN
1399 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARK_2007_V COMPILE');
1400 log_msg('IGS_UC_IVNSTARK_2007_V', 'O');
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 retcode := 1 ;
1404 log_msg('IGS_UC_IVNSTARK_2007_V', 'I');
1405 END;
1406
1407
1408 BEGIN
1409 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVGSTARW_2007_V COMPILE');
1410 log_msg('IGS_UC_IVGSTARW_2007_V', 'O');
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 retcode := 1 ;
1414 log_msg('IGS_UC_IVGSTARW_2007_V', 'I');
1415 END;
1416
1417 BEGIN
1418 apps_ddl.apps_ddl('ALTER VIEW IGS_UC_IVNSTARW_2007_V COMPILE');
1419 log_msg('IGS_UC_IVNSTARW_2007_V', 'O');
1420 EXCEPTION
1421 WHEN OTHERS THEN
1422 retcode := 1 ;
1423 log_msg('IGS_UC_IVNSTARW_2007_V', 'I');
1424 END;
1425
1426 -- Compile all database packages (body)
1427 BEGIN
1428 apps_ddl.apps_ddl('ALTER PACKAGE IGS_UC_LOAD_HERCULES_DATA COMPILE BODY');
1429 -- Log message for compiling packages
1430 log_msg('IGS_UC_LOAD_HERCULES_DATA', 'O');
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 retcode := 1 ;
1434 log_msg('IGS_UC_LOAD_HERCULES_DATA', 'I');
1435 END;
1436
1437 BEGIN
1438 apps_ddl.apps_ddl('ALTER PACKAGE IGS_UC_EXT_MARVIN COMPILE BODY');
1439 -- Log message for compiling packages
1440 log_msg('IGS_UC_EXT_MARVIN', 'O');
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 retcode := 1 ;
1444 log_msg('IGS_UC_EXT_MARVIN', 'I');
1445 END;
1446
1447 BEGIN
1448 apps_ddl.apps_ddl('ALTER PACKAGE IGS_UC_MV_DATA_UPLD COMPILE BODY');
1449 -- Log message for compiling packages
1450 log_msg('IGS_UC_MV_DATA_UPLD', 'O');
1451 EXCEPTION
1452 WHEN OTHERS THEN
1453 retcode := 1 ;
1454 log_msg('IGS_UC_MV_DATA_UPLD', 'I');
1455 END;
1456
1457 BEGIN
1458 apps_ddl.apps_ddl('ALTER PACKAGE IGS_UC_EXPORT_UCAS_PKG COMPILE BODY');
1459 -- Log message for compiling packages
1460 log_msg('IGS_UC_EXPORT_UCAS_PKG', 'O');
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463 retcode := 1 ;
1464 log_msg('IGS_UC_EXPORT_UCAS_PKG', 'I');
1465 END;
1466
1467 BEGIN
1468 apps_ddl.apps_ddl('ALTER PACKAGE IGS_UC_TRAN_PROCESSOR_PKG COMPILE BODY');
1469 -- Log message for compiling packages
1470 log_msg('IGS_UC_TRAN_PROCESSOR_PKG', 'O');
1471 EXCEPTION
1472 WHEN OTHERS THEN
1473 retcode := 1 ;
1474 log_msg('IGS_UC_TRAN_PROCESSOR_PKG', 'I');
1475 END;
1476
1477 -- Compile the gen 001 package
1478 BEGIN
1479 apps_ddl.apps_ddl('ALTER PACKAGE IGS_UC_GEN_001 COMPILE BODY');
1480 log_msg('IGS_UC_GEN_001', 'O');
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 retcode := 1 ;
1484 log_msg('IGS_UC_GEN_001', 'I');
1485 END;
1486
1487 --Bug No: 3083664. Package IGS_UC_PROC_COM_INST_DATA included for compilation
1488 BEGIN
1489 apps_ddl.apps_ddl('ALTER PACKAGE IGS_UC_PROC_COM_INST_DATA COMPILE BODY');
1490 log_msg('IGS_UC_PROC_COM_INST_DATA', 'O');
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 retcode := 1 ;
1494 log_msg('IGS_UC_PROC_COM_INST_DATA', 'I');
1495 END;
1496
1497
1498 -- Update 'configured cycle' to 'target cycle'
1499 FOR rec_cur_defaults IN cur_uc_defaults_data
1500 LOOP
1501 igs_uc_defaults_pkg.update_row( x_rowid => rec_cur_defaults.rowid,
1502 x_current_inst_code => rec_cur_defaults.current_inst_code,
1503 x_ucas_id_format => rec_cur_defaults.ucas_id_format,
1504 x_test_app_no => rec_cur_defaults.test_app_no,
1505 x_test_choice_no => rec_cur_defaults.test_choice_no,
1506 x_test_transaction_type => rec_cur_defaults.test_transaction_type,
1507 x_copy_ucas_id => rec_cur_defaults.copy_ucas_id,
1508 x_mode => 'R',
1509 x_decision_make_id => rec_cur_defaults.decision_make_id,
1510 x_decision_reason_id => rec_cur_defaults.decision_reason_id,
1511 x_obsolete_outcome_status => rec_cur_defaults.obsolete_outcome_status,
1512 x_pending_outcome_status => rec_cur_defaults.pending_outcome_status,
1513 x_rejected_outcome_status => rec_cur_defaults.rejected_outcome_status,
1514 x_system_code => rec_cur_defaults.system_code,
1515 x_ni_number_alt_pers_type => rec_cur_defaults.ni_number_alt_pers_type,
1516 x_application_type => rec_cur_defaults.application_type,
1517 x_name => rec_cur_defaults.name,
1518 x_description => rec_cur_defaults.description,
1519 x_ucas_security_key => rec_cur_defaults.ucas_security_key,
1520 x_current_cycle => rec_cur_defaults.current_cycle,
1521 x_configured_cycle => p_target_cycle,
1522 x_prev_inst_left_date => rec_cur_defaults.prev_inst_left_date
1523 );
1524 END LOOP;
1525
1526 EXCEPTION
1527 WHEN OTHERS THEN
1528 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1529 fnd_message.set_token('NAME','IGS_UC_CONFIG_CYCLE.conf_system_for_ucas_cycle');
1530 fnd_file.put_line(fnd_file.log, fnd_message.get);
1531 fnd_file.put_line(fnd_file.log, 'Exception ' || sqlerrm);
1532 errbuf := fnd_message.get ;
1533 retcode := 2;
1534
1535 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1536
1537 END conf_system_for_ucas_cycle;
1538
1539 END igs_uc_config_cycle;