[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_START_NEW_CYCLE
Source
1 PACKAGE BODY igs_uc_start_new_cycle AS
2 /* $Header: IGSUC40B.pls 120.3 2006/02/08 19:57:22 anwest noship $ */
3
4 PROCEDURE start_new_cycle( errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER
6 ) IS
7 /*************************************************************
8 Created By : dsridhar
9 Date Created On : 19-JUN-2003
10 Purpose : This procedure will configure the UCAS System to a new
11 Admission Cycle.
12
13 Know limitations, enhancements or remarks
14 Change History
15 Who When What
16 (reverse chronological order - newest change first)
17 dsridhar 19-JUN-2003 Create Version as part of UC203FD
18 Bug# 2669208
19 dsridhar 16-JUL-2003 Changed cursor cur_uc_defaults, added new cursor, added code to run the process
20 when maximum current cycle and maximum configured cycle are NULL
21 dsridhar 17-JUL-2003 Added code to display message after updating igs_uc_defaults
22 dsridhar 24-JUL-2003 Bug No: 3022067, part of change request for UCAS Application Calendar Mapping.
23 Removed calendar fields from the IGS_UC_CYC_DEFAULTS_PKG procedure calls.
24 jchakrab 20-Sep-2005 Modified for 4616246 - If a record with null current/conf cycle is found in
25 IGS_UC_DEFAULTS and no record exists for the current cycle in IGS_UC_TRANSACTIONS
26 a empty record is inserted into IGS_UC_UCAS_CONTROL to be updated later by user
27 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
28 ***************************************************************/
29
30 -- Cursor to check if any records exist in IGS_UC_DEFAULTS
31 CURSOR cur_uc_defaults IS
32 SELECT 'X'
33 FROM igs_uc_defaults;
34
35 -- Cursor to get the maximum current cycle and maximum configured cycle
36 CURSOR cur_max_cycles IS
37 SELECT MAX(current_cycle) current_cycle, MAX(configured_cycle) configured_cycle
38 FROM igs_uc_defaults;
39
40 -- Cursor to get the any cycles is null
41 -- Modified for 4589994 to exclude SWAS
42 CURSOR cur_null_cycles IS
43 SELECT 'X' FROM igs_uc_defaults
44 WHERE (current_cycle IS NULL OR configured_cycle IS NULL)
45 AND system_code <> 'S';
46
47 -- Cursor to obtain the Supported Cycle - 1
48 CURSOR cur_sup_cycle IS
49 SELECT MAX(TO_NUMBER(lookup_code)) - 1
50 FROM igs_lookup_values
51 WHERE lookup_type = 'IGS_UC_SUP_CYCLE'
52 AND enabled_flag = 'Y'
53 AND NVL(closed_Ind, 'N') = 'N';
54
55 -- Cursor to obtain the Next Cycle i.e. Supported Cycle + 1
56 CURSOR cur_next_cycle (p_current_cycle igs_uc_defaults.current_cycle%TYPE) IS
57 SELECT TO_NUMBER(lookup_code)
58 FROM igs_lookup_values
59 WHERE lookup_type = 'IGS_UC_SUP_CYCLE'
60 AND TO_NUMBER(lookup_code) = p_current_cycle + 1
61 AND enabled_flag = 'Y'
62 AND NVL(closed_Ind, 'N') = 'N';
63
64 -- Cursor to select the Current Cycle details from IGS_UC_CYC_DEFAULTS
65 -- Modified for UC315 - UCAS 2006 Support to exclude SWAS
66 CURSOR cur_uc_cyc_defaults (p_current_cycle igs_uc_defaults.current_cycle%TYPE) IS
67 SELECT ucd.*
68 FROM igs_uc_cyc_defaults ucd
69 WHERE ucas_cycle = p_current_cycle
70 AND ucd.system_code <> 'S';
71
72 -- Cursor to check if the record exists in IGS_UC_CYC_DEFAULTS
73 CURSOR cur_check_uc_cyc_defaults (p_system_code igs_uc_cyc_defaults.system_code%TYPE,
74 p_ucas_cycle igs_uc_cyc_defaults.ucas_cycle%TYPE) IS
75 SELECT 'X'
76 FROM igs_uc_cyc_defaults ucd
77 WHERE system_code = p_system_code
78 AND ucas_cycle = p_ucas_cycle;
79
80 -- Cursor to select current cycle details from IGS_UC_UCAS_CONTROL
81 -- Modified for UC315 - UCAS 2006 Support to include FTUG and exclude SWAS
82 CURSOR cur_uc_ucas_control (p_current_cycle igs_uc_defaults.current_cycle%TYPE) IS
83 SELECT uco.rowid, uco.*
84 FROM igs_uc_ucas_control uco
85 WHERE uco.system_code <> 'S'
86 AND ucas_cycle = p_current_cycle;
87
88 -- Cursor to check if the record exists in IGS_UC_UCAS_CONTROL
89 CURSOR cur_check_ucas_control (p_system_code igs_uc_ucas_control.system_code%TYPE,
90 p_ucas_cycle igs_uc_ucas_control.ucas_cycle%TYPE) IS
91 SELECT 'X'
92 FROM igs_uc_ucas_control ucd
93 WHERE system_code = p_system_code
94 AND ucas_cycle = p_ucas_cycle;
95
96 -- Cursor to select records from IGS_UC_DEFAULTS
97 -- Modified for UC315 - UCAS 2006 Support to exclude SWAS
98 CURSOR cur_defaults IS
99 SELECT ucd.rowid, ucd.*
100 FROM igs_uc_defaults ucd
101 WHERE ucd.system_code <> 'S';
102
103 l_max_cycles cur_max_cycles%ROWTYPE;
104 l_current_cycle igs_uc_defaults.current_cycle%TYPE;
105 l_sup_cycle igs_uc_defaults.current_cycle%TYPE;
106 l_next_cycle igs_uc_defaults.current_cycle%TYPE;
107 l_rowid VARCHAR2(26);
108 l_exists VARCHAR2(1);
109 l_curr_control_exists VARCHAR2(1);
110 l_null_cycles VARCHAR2(1);
111 l_curr_cycle igs_uc_defaults.current_cycle%TYPE;
112 l_conf_cycle igs_uc_defaults.configured_cycle%TYPE;
113 l_appno_first igs_uc_ucas_control.appno_first%TYPE;
114 l_appno_maximum igs_uc_ucas_control.appno_maximum%TYPE;
115
116 BEGIN
117
118 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
119 IGS_GE_GEN_003.SET_ORG_ID;
120
121 -- Check if records exist in IGS_UC_DEFAULTS
122 l_exists := NULL;
123 retcode := 0;
124 OPEN cur_uc_defaults;
125 FETCH cur_uc_defaults INTO l_exists;
126 IF cur_uc_defaults%NOTFOUND THEN
127 CLOSE cur_uc_defaults;
128 fnd_message.set_name('IGS', 'IGS_UC_NO_SYSTEM_CONFIG');
129 fnd_file.put_line(fnd_file.log, fnd_message.get);
130 retcode := 2;
131 RETURN;
132 END IF;
133 CLOSE cur_uc_defaults;
134
135 -- Get maximum current cycle or maximum configured cycle and check if they are NULL
136 OPEN cur_max_cycles;
137 FETCH cur_max_cycles INTO l_max_cycles;
138 CLOSE cur_max_cycles;
139
140 -- Get maximum of supported cycle - 1 and update current cycle and configured cycle with it
141 OPEN cur_sup_cycle;
142 FETCH cur_sup_cycle INTO l_sup_cycle;
143 CLOSE cur_sup_cycle;
144
145 -- Check whether any cycle is null then populate correct cycle value,bug 3420747
146 OPEN cur_null_cycles;
147 FETCH cur_null_cycles INTO l_null_cycles;
148
149 IF cur_null_cycles%FOUND THEN
150 CLOSE cur_null_cycles;
151 -- take the max of the cycle value or look up value
152 l_curr_cycle := NVL(l_max_cycles.current_cycle,l_sup_cycle);
153 l_conf_cycle := NVL(l_max_cycles.configured_cycle,l_sup_cycle);
154
155 FOR rec_cur_defaults IN cur_defaults LOOP
156 IF rec_cur_defaults.current_cycle IS NULL OR rec_cur_defaults.configured_cycle IS NULL THEN
157 igs_uc_defaults_pkg.update_row ( x_rowid => rec_cur_defaults.rowid,
158 x_current_inst_code => rec_cur_defaults.current_inst_code,
159 x_ucas_id_format => rec_cur_defaults.ucas_id_format,
160 x_test_app_no => rec_cur_defaults.test_app_no,
161 x_test_choice_no => rec_cur_defaults.test_choice_no,
162 x_test_transaction_type => rec_cur_defaults.test_transaction_type,
163 x_copy_ucas_id => rec_cur_defaults.copy_ucas_id,
164 x_mode => 'R',
165 x_decision_make_id => rec_cur_defaults.decision_make_id,
166 x_decision_reason_id => rec_cur_defaults.decision_reason_id,
167 x_obsolete_outcome_status => rec_cur_defaults.obsolete_outcome_status,
168 x_pending_outcome_status => rec_cur_defaults.pending_outcome_status,
169 x_rejected_outcome_status => rec_cur_defaults.rejected_outcome_status,
170 x_system_code => rec_cur_defaults.system_code,
171 x_ni_number_alt_pers_type => rec_cur_defaults.ni_number_alt_pers_type,
172 x_application_type => rec_cur_defaults.application_type,
173 x_name => rec_cur_defaults.name,
174 x_description => rec_cur_defaults.description,
175 x_ucas_security_key => rec_cur_defaults.ucas_security_key,
176 x_current_cycle => l_curr_cycle,
177 x_configured_cycle => l_conf_cycle,
178 x_prev_inst_left_date => rec_cur_defaults.prev_inst_left_date
179 );
180
181 fnd_message.set_name('IGS', 'IGS_UC_CRNT_CYCLE_UPDTD');
182 fnd_message.set_token('SYSTEM_CODE', rec_cur_defaults.system_code);
183 fnd_message.set_token('CRNT_CYCLE', l_curr_cycle);
184 fnd_file.put_line(fnd_file.log, fnd_message.get);
185
186 --jchakrab - Modified for 4616246
187 --Create a record in the IGS_UC_UCAS_CONTROL table if no record exists for l_curr_cycle
188 l_curr_control_exists := NULL;
189 OPEN cur_check_ucas_control(rec_cur_defaults.system_code, l_curr_cycle);
190 FETCH cur_check_ucas_control into l_curr_control_exists;
191 CLOSE cur_check_ucas_control;
192
193 IF l_curr_control_exists IS NULL THEN
194 --this is a scenario where the customer is trying to setup UCAS in a fresh-install env
195 --we need to create a empty record in IGS_UC_UCAS_CONTROL which should be updated later by user
196
197 --derive app-no ranges based on system code and current cycle
198 --FTUG = Y000001 to Y689999, GTTR = Y700001 to Y799999, NMAS = Y800001 to Y899999, Y = entry_year
199 IF rec_cur_defaults.system_code = 'U' THEN
200 l_appno_first := Ltrim(Substr(l_curr_cycle,3,2)) || '000001';
201 l_appno_maximum := Ltrim(Substr(l_curr_cycle,3,2)) || '689999';
202 ELSIF rec_cur_defaults.system_code = 'G' THEN
203 l_appno_first := Ltrim(Substr(l_curr_cycle,3,2)) || '700001';
204 l_appno_maximum := Ltrim(Substr(l_curr_cycle,3,2)) || '799999';
205 ELSIF rec_cur_defaults.system_code = 'N' THEN
206 l_appno_first := Ltrim(Substr(l_curr_cycle,3,2)) || '800001';
207 l_appno_maximum := Ltrim(Substr(l_curr_cycle,3,2)) || '899999';
208 ELSE
209 l_appno_first := 0;
210 l_appno_maximum := 0;
211 END IF;
212 l_rowid := NULL;
213 igs_uc_ucas_control_pkg.insert_row( x_rowid => l_rowid,
214 x_entry_year => Ltrim(Substr(l_curr_cycle,3,2)),
215 x_time_of_year => NULL,
216 x_time_of_day => NULL,
217 x_routeb_time_of_year => NULL,
218 x_appno_first => l_appno_first, -- put value to expected appno ranges as value cannot be derived at this stage
219 x_appno_maximum => l_appno_maximum, -- put value to expected appno ranges as value cannot be derived at this stage
220 x_appno_last_used => NULL,
221 x_last_daily_run_no => NULL,
222 x_last_daily_run_date => NULL,
223 x_appno_15dec => NULL,
224 x_run_date_15dec => NULL,
225 x_appno_24mar => NULL,
226 x_run_date_24mar => NULL,
227 x_appno_16may => NULL,
228 x_run_date_16may => NULL,
229 x_appno_decision_proc => NULL,
230 x_run_date_decision_proc => NULL,
231 x_appno_first_pre_num => NULL,
232 x_news => NULL,
233 x_no_more_la_tran => NULL,
234 x_star_x_avail => NULL,
235 x_mode => 'R',
236 x_appno_first_opf => 0,
237 x_appno_first_rpa_noneu => 0,
238 x_appno_first_rpa_eu => 0,
239 x_extra_start_date => NULL,
240 x_last_passport_date => NULL,
241 x_last_le_date => NULL,
242 x_system_code => rec_cur_defaults.system_code,
243 x_ucas_cycle => l_curr_cycle,
244 x_gttr_clear_toy_code => NULL,
245 x_transaction_toy_code => 'S'
246 );
247
248 fnd_message.set_name('IGS', 'IGS_UC_REC_INSRT');
249 fnd_message.set_token('SYSTEM_CODE', rec_cur_defaults.system_code);
250 fnd_message.set_token('TNAME', 'IGS_UC_UCAS_CONTROL');
251 fnd_file.put_line(fnd_file.log, fnd_message.get);
252 END IF;
253
254 END IF;
255 END LOOP;
256 RETURN;
257 END IF;
258
259 IF cur_null_cycles%ISOPEN THEN
260 CLOSE cur_null_cycles;
261 END IF;
262 -- Get the next higher cycle i.e. Supported Cycle + 1
263 l_next_cycle := NULL ;
264 IF l_max_cycles.current_cycle IS NOT NULL THEN
265 l_current_cycle := l_max_cycles.current_cycle;
266 ELSE
267 l_current_cycle := l_sup_cycle;
268 END IF;
269
270 OPEN cur_next_cycle (l_current_cycle);
271 FETCH cur_next_cycle INTO l_next_cycle;
272 -- If Current Cycle = Maximum of Supported Cycle
273 IF cur_next_cycle%NOTFOUND THEN
274 CLOSE cur_next_cycle;
275 fnd_message.set_name('IGS', 'IGS_UC_ALRDY_MAX_CYCLE');
276 fnd_file.put_line(fnd_file.log, fnd_message.get);
277 retcode := 2 ;
278 RETURN;
279 END IF;
280 CLOSE cur_next_cycle;
281
282 -- If next cycle is not null i.e. Current Cycle + 1 is supported
283 IF l_next_cycle IS NOT NULL THEN
284
285 -- Populate records for new cycle in IGS_UC_CYC_DEFAULTS from previous cycle.
286 FOR rec_uc_cyc_defaults IN cur_uc_cyc_defaults (l_current_cycle)
287 LOOP
288 l_exists := NULL;
289 OPEN cur_check_uc_cyc_defaults(rec_uc_cyc_defaults.system_code, l_next_cycle);
290 FETCH cur_check_uc_cyc_defaults INTO l_exists;
291 CLOSE cur_check_uc_cyc_defaults;
292 IF l_exists IS NULL THEN
293 l_rowid := NULL;
294 igs_uc_cyc_defaults_pkg.insert_row( x_rowid => l_rowid,
295 x_SYSTEM_CODE => rec_uc_cyc_defaults.system_code,
296 x_UCAS_CYCLE => l_next_cycle,
297 x_UCAS_INTERFACE => rec_uc_cyc_defaults.ucas_interface,
298 x_MARVIN_SEQ => 0,
299 x_CLEARING_FLAG => rec_uc_cyc_defaults.clearing_flag,
300 x_EXTRA_FLAG => rec_uc_cyc_defaults.extra_flag,
301 x_CVNAME_FLAG => rec_uc_cyc_defaults.cvname_flag,
302 x_MODE => 'R'
303 );
304
305 fnd_message.set_name('IGS', 'IGS_UC_REC_INSRT');
306 fnd_message.set_token('TNAME', 'IGS_UC_CYC_DEFAULTS');
307 fnd_message.set_token('SYSTEM_CODE', rec_uc_cyc_defaults.system_code);
308 fnd_file.put_line(fnd_file.log, fnd_message.get);
309 END IF;
310
311 END LOOP;
312
313 -- Populate records for new cycle in IGS_UC_UCAS_CONTROL from previous cycle.
314 FOR rec_cur_uc_ucas_control IN cur_uc_ucas_control(l_current_cycle)
315 LOOP
316 l_exists := NULL;
317 OPEN cur_check_ucas_control(rec_cur_uc_ucas_control.system_code, l_next_cycle);
318 FETCH cur_check_ucas_control INTO l_exists;
319 CLOSE cur_check_ucas_control;
320 IF l_exists IS NULL THEN
321 l_rowid := NULL;
322 igs_uc_ucas_control_pkg.insert_row( x_rowid => l_rowid,
323 x_entry_year => rec_cur_uc_ucas_control.entry_year + 1,
324 x_time_of_year => NULL,
325 x_time_of_day => NULL,
326 x_routeb_time_of_year => NULL,
327 x_appno_first => rec_cur_uc_ucas_control.appno_first + 1000000,
328 x_appno_maximum => rec_cur_uc_ucas_control.appno_maximum + 1000000,
329 x_appno_last_used => NULL,
330 x_last_daily_run_no => NULL,
331 x_last_daily_run_date => NULL,
332 x_appno_15dec => NULL,
333 x_run_date_15dec => NULL,
334 x_appno_24mar => NULL,
335 x_run_date_24mar => NULL,
336 x_appno_16may => NULL,
337 x_run_date_16may => NULL,
338 x_appno_decision_proc => NULL,
339 x_run_date_decision_proc => NULL,
340 x_appno_first_pre_num => NULL,
341 x_news => NULL,
342 x_no_more_la_tran => NULL,
343 x_star_x_avail => NULL,
344 x_mode => 'R',
345 x_appno_first_opf => 0,
346 x_appno_first_rpa_noneu => 0,
347 x_appno_first_rpa_eu => 0,
348 x_extra_start_date => NULL,
349 x_last_passport_date => NULL,
350 x_last_le_date => NULL,
351 x_system_code => rec_cur_uc_ucas_control.system_code,
352 x_ucas_cycle => l_next_cycle,
353 x_gttr_clear_toy_code => NULL,
354 x_transaction_toy_code => 'S'
355 );
356
357 fnd_message.set_name('IGS', 'IGS_UC_REC_INSRT');
358 fnd_message.set_token('SYSTEM_CODE', rec_cur_uc_ucas_control.system_code);
359 fnd_message.set_token('TNAME', 'IGS_UC_UCAS_CONTROL');
360 fnd_file.put_line(fnd_file.log, fnd_message.get);
361 END IF;
362 END LOOP;
363
364 -- Update the current_cycle to maximum supported cycle in IGS_UC_DEFAULTS
365 FOR rec_cur_defaults IN cur_defaults
366 LOOP
367 igs_uc_defaults_pkg.update_row ( x_rowid => rec_cur_defaults.rowid,
368 x_current_inst_code => rec_cur_defaults.current_inst_code,
369 x_ucas_id_format => rec_cur_defaults.ucas_id_format,
370 x_test_app_no => rec_cur_defaults.test_app_no,
371 x_test_choice_no => rec_cur_defaults.test_choice_no,
372 x_test_transaction_type => rec_cur_defaults.test_transaction_type,
373 x_copy_ucas_id => rec_cur_defaults.copy_ucas_id,
374 x_mode => 'R',
375 x_decision_make_id => rec_cur_defaults.decision_make_id,
376 x_decision_reason_id => rec_cur_defaults.decision_reason_id,
377 x_obsolete_outcome_status => rec_cur_defaults.obsolete_outcome_status,
378 x_pending_outcome_status => rec_cur_defaults.pending_outcome_status,
379 x_rejected_outcome_status => rec_cur_defaults.rejected_outcome_status,
380 x_system_code => rec_cur_defaults.system_code,
381 x_ni_number_alt_pers_type => rec_cur_defaults.ni_number_alt_pers_type,
382 x_application_type => rec_cur_defaults.application_type,
383 x_name => rec_cur_defaults.name,
384 x_description => rec_cur_defaults.description,
385 x_ucas_security_key => rec_cur_defaults.ucas_security_key,
386 x_current_cycle => l_next_cycle,
387 x_configured_cycle => rec_cur_defaults.configured_cycle,
388 x_prev_inst_left_date => rec_cur_defaults.prev_inst_left_date
389 );
390
391 fnd_message.set_name('IGS', 'IGS_UC_CRNT_CYCLE_UPDTD');
392 fnd_message.set_token('SYSTEM_CODE', rec_cur_defaults.system_code);
393 fnd_message.set_token('CRNT_CYCLE', l_next_cycle);
394 fnd_file.put_line(fnd_file.log, fnd_message.get);
395 END LOOP;
396
397 END IF;
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 ROLLBACK;
402
403 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
404 fnd_message.set_token('NAME','IGS_UC_START_NEW_CYCLE.START_NEW_CYCLE');
405 fnd_file.put_line(fnd_file.log, fnd_message.get);
406 fnd_file.put_line(fnd_file.log, sqlerrm);
407 errbuf := fnd_message.get ;
408 retcode := 2;
409 igs_ge_msg_stack.conc_exception_hndl;
410
411 END start_new_cycle;
412
413 END igs_uc_start_new_cycle;