[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_UPD_PENDING_TRANS_PKG
Source
1 PACKAGE BODY igs_uc_upd_pending_trans_pkg AS
2 /* $Header: IGSUC70B.pls 120.2 2006/08/21 03:53:06 jbaber noship $*/
3
4 l_debug_level NUMBER:= fnd_log.g_current_runtime_level;
5
6 PROCEDURE upd_pending_transactions (
7 errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY NUMBER
9 ) IS
10
11 /*------------------------------------------------------------------
12 --Created by : jchakrab, Oracle Corporation
13 --Date created: 31-Oct-2005
14 --
15 --Purpose : Retrieve transaction details from the UCAS TRANIN view (over the
16 -- database link) for transactions currently marked as pending in
17 -- IGS_UC_TRANSACTIONS table and update their details, if processed
18 -- by the UCAS transaction processing system(Topaz).
19 --
20 --Known limitations/enhancements and/or remarks:
21 --
22 --Change History:
23 WHO WHEN WHAT
24 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
25 jbaber 11-Jul-06 Added modular and part_time fields for UC325 - UCAS 2007 Support
26 -----------------------------------------------------------------------*/
27 CURSOR c_cycle IS
28 SELECT max(current_cycle) current_cycle, max(configured_cycle) configured_cycle
29 FROM igs_uc_defaults ;
30
31 c_cycle_rec c_cycle%ROWTYPE;
32
33 CURSOR c_control IS
34 SELECT TO_NUMBER(LPAD(entry_year,4,200))
35 FROM igs_uc_ucas_control
36 WHERE system_code = 'U'
37 AND ucas_cycle = c_cycle_rec.configured_cycle;
38
39 CURSOR c_pending_trans(cp_conf_cycle igs_uc_defaults.configured_cycle%TYPE) IS
40 SELECT TRANS.ROWID,
41 TRANS.UC_TRAN_ID,
42 TRANS.TRANSACTION_ID,
43 TRANS.DATETIMESTAMP,
44 TRANS.UPDATER,
45 TRANS.ERROR_CODE,
46 TRANS.TRANSACTION_TYPE,
47 TRANS.APP_NO,
48 TRANS.CHOICE_NO,
49 TRANS.DECISION,
50 TRANS.PROGRAM_CODE,
51 TRANS.CAMPUS,
52 TRANS.ENTRY_MONTH,
53 TRANS.ENTRY_YEAR,
54 TRANS.ENTRY_POINT,
55 TRANS.SOC,
56 TRANS.COMMENTS_IN_OFFER,
57 TRANS.RETURN1,
58 TRANS.RETURN2,
59 TRANS.HOLD_FLAG,
60 TRANS.SENT_TO_UCAS,
61 TRANS.TEST_COND_CAT,
62 TRANS.TEST_COND_NAME,
63 TRANS.INST_REFERENCE ,
64 TRANS.AUTO_GENERATED_FLAG,
65 TRANS.SYSTEM_CODE,
66 TRANS.UCAS_CYCLE,
67 TRANS.MODULAR,
68 TRANS.PART_TIME
69 FROM
70 IGS_UC_TRANSACTIONS TRANS,
71 IGS_UC_CYC_DEFAULTS DFLTS
72 WHERE
73 TRANS.ERROR_CODE = -1
74 AND TRANS.SENT_TO_UCAS = 'Y'
75 AND TRANS.UCAS_CYCLE = cp_conf_cycle
76 AND TRANS.SYSTEM_CODE = DFLTS.SYSTEM_CODE
77 AND TRANS.UCAS_CYCLE = DFLTS.UCAS_CYCLE
78 AND DFLTS.UCAS_INTERFACE = 'H'
79 ORDER BY TRANS.CREATION_DATE;
80
81 CURSOR c_pending_trans_count(cp_conf_cycle igs_uc_defaults.configured_cycle%TYPE) IS
82 SELECT COUNT(*)
83 FROM
84 IGS_UC_TRANSACTIONS TRANS,
85 IGS_UC_CYC_DEFAULTS DFLTS
86 WHERE
87 TRANS.ERROR_CODE = -1
88 AND TRANS.SENT_TO_UCAS = 'Y'
89 AND TRANS.UCAS_CYCLE = cp_conf_cycle
90 AND TRANS.SYSTEM_CODE = DFLTS.SYSTEM_CODE
91 AND TRANS.UCAS_CYCLE = DFLTS.UCAS_CYCLE
92 AND DFLTS.UCAS_INTERFACE = 'H';
93
94 CURSOR c_tranin_info(cp_trans_id igs_uc_transactions.transaction_id%TYPE,
95 cp_app_no igs_uc_u_tranin_2004.appno%TYPE) IS
96 SELECT APPNO,
97 CHOICENO,
98 TRANSACTIONID,
99 ERRORCODE,
100 TIMESTAMP,
101 UPDATER,
102 RETURN1,
103 RETURN2,
104 SOC
105 FROM
106 IGS_UC_U_TRANIN_2004
107 WHERE
108 TRANSACTIONID = cp_trans_id
109 AND APPNO = cp_app_no;
110
111 c_tranin_info_rec c_tranin_info%ROWTYPE;
112
113 -- Cursor to convert 8-digit appno to 9 digit NUMBER with check digit for UCAS 2006 Cycle Support
114 CURSOR c_appno(cp_appno igs_uc_applicants.app_no%TYPE) IS
115 SELECT TO_NUMBER(APP_NO || CHECK_DIGIT)
116 FROM IGS_UC_APPLICANTS
117 WHERE APP_NO = CP_APPNO;
118
119 l_soc igs_uc_transactions.SOC%TYPE;
120 l_entry_year igs_uc_defaults.configured_cycle%TYPE;
121 l_pending_cnt NUMBER;
122 l_valid_cnt NUMBER;
123 l_invalid_cnt NUMBER;
124 l_appno igs_uc_u_tranin_2004.appno%TYPE;
125
126 BEGIN
127
128 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
129 IGS_GE_GEN_003.SET_ORG_ID;
130
131 /* Checking whether the UK profile is enabled */
132 IF NOT (igs_uc_utils.is_ucas_hesa_enabled) THEN
133
134 fnd_message.set_name('IGS','IGS_UC_HE_NOT_ENABLED');
135 fnd_file.put_line(fnd_file.log, fnd_message.get()); -- display to user also
136 -- also log using the fnd logging framework
137 IF (fnd_log.level_statement >= l_debug_level ) THEN
138 fnd_log.string( fnd_log.level_statement, 'igs.plsql.ucas.upd_pending_transactions.validation', fnd_message.get());
139 END IF;
140 errbuf := fnd_message.get_string ('IGS', 'IGS_UC_HE_NOT_ENABLED');
141 retcode := 3 ;
142 RETURN ;
143
144 END IF;
145
146
147 -- get the ucas configured cycle
148 OPEN c_cycle;
149 FETCH c_cycle INTO c_cycle_rec;
150 CLOSE c_cycle;
151
152 -- Get the hercules entry year
153 OPEN c_control;
154 FETCH c_control INTO l_entry_year;
155 CLOSE c_control;
156
157 -- Check whether configured cycle is same as entry year
158 IF l_entry_year <> c_cycle_rec.configured_cycle THEN
159 fnd_message.set_name('IGS','IGS_UC_CYCLES_NOT_SYNC');
160 fnd_message.set_token('UCAS_CYCLE',c_cycle_rec.configured_cycle);
161 fnd_message.set_token('HERC_CYCLE',l_entry_year);
162 fnd_file.put_line(fnd_file.log, fnd_message.get());
163 errbuf := fnd_message.get;
164 retcode := 2;
165 RETURN;
166 END IF;
167
168 l_pending_cnt := 0;
169 l_valid_cnt := 0;
170 l_invalid_cnt := 0;
171
172 -- get the initial count of pending transactions
173 OPEN c_pending_trans_count(c_cycle_rec.configured_cycle);
174 FETCH c_pending_trans_count INTO l_pending_cnt;
175 CLOSE c_pending_trans_count;
176
177 fnd_message.set_name('IGS','IGS_UC_INIT_TRAN_PENDING_CNT');
178 fnd_message.set_token('REC_CNT',l_pending_cnt);
179 fnd_file.put_line(fnd_file.log, fnd_message.get());
180
181 --When the Interface is Hercules then get the Transaction records from IGS_UC_TRANSACTIONS table and insert into Hercules igs_uc_u_tranin_2004 .
182 FOR c_pending_trans_rec IN c_pending_trans(c_cycle_rec.configured_cycle)
183 LOOP
184
185 -- Determine appno based on configured year.
186 IF c_cycle_rec.configured_cycle < 2006 THEN
187 l_appno := c_pending_trans_rec.app_no;
188 ELSE
189 -- Convert 8-digit appno to 9 digit NUMBER with check digit for UC315 - UCAS 2006 Support
190 OPEN c_appno(c_pending_trans_rec.app_no);
191 FETCH c_appno INTO l_appno;
192 CLOSE c_appno;
193 END IF;
194
195 OPEN c_tranin_info(c_pending_trans_rec.transaction_id, l_appno);
196 FETCH c_tranin_info INTO c_tranin_info_rec;
197 CLOSE c_tranin_info;
198
199 --check if the transaction has been processed
200 IF c_tranin_info_rec.errorcode <> -1 THEN
201
202 --perform updates in IGS_UC_TRANSACTIONS based on error code
203
204 --to update the SOC based on the errorcode value
205 IF c_tranin_info_rec.errorcode = 0 THEN
206 l_soc := c_tranin_info_rec.soc;
207 --update the count of valid transactions processed by UCAS
208 l_valid_cnt := l_valid_cnt + 1;
209 ELSE
210 --use old SOC value
211 l_soc := c_pending_trans_rec.SOC;
212 --update the count of invalid transactions processed by UCAS
213 l_invalid_cnt := l_invalid_cnt + 1;
214 END IF;
215
216 igs_uc_transactions_pkg.update_row (
217 x_mode => 'R',
218 x_rowid => c_pending_trans_rec.ROWID,
219 x_uc_tran_id => c_pending_trans_rec.uc_tran_id,
220 x_transaction_id => c_pending_trans_rec.transaction_id,
221 x_datetimestamp => c_tranin_info_rec.timestamp,
222 x_updater => c_tranin_info_rec.updater,
223 x_error_code => c_tranin_info_rec.errorcode,
224 x_transaction_type => c_pending_trans_rec.transaction_type,
225 x_app_no => c_pending_trans_rec.app_no,
226 x_choice_no => c_pending_trans_rec.choice_no,
227 x_decision => c_pending_trans_rec.decision,
228 x_program_code => c_pending_trans_rec.program_code,
229 x_campus => c_pending_trans_rec.campus,
230 x_entry_month => c_pending_trans_rec.entry_month,
231 x_entry_year => c_pending_trans_rec.entry_year,
232 x_entry_point => c_pending_trans_rec.entry_point,
233 x_soc => l_soc,
234 x_comments_in_offer => c_pending_trans_rec.comments_in_offer,
235 x_return1 => c_tranin_info_rec.return1,
236 x_return2 => c_tranin_info_rec.return2,
237 x_hold_flag => c_pending_trans_rec.hold_flag,
238 x_sent_to_ucas => c_pending_trans_rec.sent_to_ucas,
239 x_test_cond_cat => c_pending_trans_rec.test_cond_cat,
240 x_test_cond_name => c_pending_trans_rec.test_cond_name,
241 x_inst_reference => c_pending_trans_rec.inst_reference ,
242 x_auto_generated_flag => c_pending_trans_rec.auto_generated_flag,
243 x_system_code => c_pending_trans_rec.system_code,
244 x_ucas_cycle => c_pending_trans_rec.ucas_cycle,
245 x_modular => c_pending_trans_rec.modular,
246 x_part_time => c_pending_trans_rec.part_time);
247
248 END IF;
249
250 END LOOP;
251
252 COMMIT;
253
254 --print statistics
255 fnd_message.set_name('IGS','IGS_UC_TRAN_VALID_CNT');
256 fnd_message.set_token('REC_CNT',l_valid_cnt);
257 fnd_file.put_line(fnd_file.log, fnd_message.get());
258
259 fnd_message.set_name('IGS','IGS_UC_TRAN_INVALID_CNT');
260 fnd_message.set_token('REC_CNT',l_invalid_cnt);
261 fnd_file.put_line(fnd_file.log, fnd_message.get());
262
263 -- get the final count of pending transactions
264 OPEN c_pending_trans_count(c_cycle_rec.configured_cycle);
265 FETCH c_pending_trans_count INTO l_pending_cnt;
266 CLOSE c_pending_trans_count;
267
268 fnd_message.set_name('IGS','IGS_UC_TRAN_PENDING_CNT');
269 fnd_message.set_token('REC_CNT',l_pending_cnt);
270 fnd_file.put_line(fnd_file.log, fnd_message.get());
271
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 ROLLBACK;
276
277 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
278 fnd_message.set_token('NAME','igs_uc_upd_pending_trans_pkg.upd_pending_transactions - '||SQLERRM);
279 fnd_file.put_line(fnd_file.log,fnd_message.get);
280
281 IF ( fnd_log.level_unexpected >= l_debug_level ) THEN
282 fnd_log.message(fnd_log.level_unexpected, 'igs.plsql.ucas.upd_pending_transactions.exception', FALSE);
283 END IF;
284
285 fnd_message.retrieve (errbuf);
286 retcode := 2 ;
287 igs_ge_msg_stack.conc_exception_hndl;
288
289 END upd_pending_transactions;
290
291 END igs_uc_upd_pending_trans_pkg;