[Home] [Help]
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: --
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'
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'
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,
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;
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.
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;
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,