[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_CC_PMT
Source
1 PACKAGE BODY igs_fi_cc_pmt AS
2 /* $Header: IGSFI86B.pls 120.2 2006/05/04 08:07:37 abshriva ship $ */
3
4 CURSOR c_credits(cp_cc IN VARCHAR2,
5 cp_pending IN VARCHAR2)
6 IS
7 SELECT fc.rowid, fc.*
8 FROM igs_fi_credits_all fc
9 WHERE credit_instrument = cp_cc
10 AND credit_card_status_code = cp_pending
11 AND credit_card_tangible_cd IS NOT NULL
12 FOR UPDATE NOWAIT;
13
14 CURSOR c_app_req(cp_pending IN VARCHAR2)
15 IS
16 SELECT aar.rowid, aar.*
17 FROM igs_ad_app_req aar
18 WHERE credit_card_number IS NOT NULL
19 AND credit_card_status_code = cp_pending
20 AND credit_card_tangible_cd IS NOT NULL
21 FOR UPDATE NOWAIT;
22
23 l_b_records BOOLEAN := FALSE;
24
25 PROCEDURE local_upd(p_r_crd_row IN c_credits%ROWTYPE,
26 p_r_ad_app_row IN c_app_req%ROWTYPE
27 ) AS
28 ------------------------------------------------------------------
29 --Created by : schodava, Oracle IDC
30 --Date created: 09-Jun-2003
31 --
32 --Purpose: Local Procedure, to query the IBY table, and update the
33 -- Credits and AD Applications Request table.
34 --Known limitations/enhancements and/or remarks:
35 --
36 --Change History:
37 --Who When What
38 --agairola 29-Aug-2005 Tuition Waiver build: 3392095: Changes done for this build
39 --pathipat 21-Apr-2004 Enh 3558549 - Comm Receivables Enh
40 -- Added param x_source_invoice_id in call to igs_fi_credits_pkg.update_row()
41 --vvutukur 09-Oct-2003 Bug#3160036.Replaced the call to igs_ad_app_req.update_row with
42 -- call to igs_ad_gen_015.update_igs_ad_app_req.
43 -------------------------------------------------------------------
44
45 -- Get the payment status
46 CURSOR c_iby_trans(cp_tangible_id IN iby_trans_all_v.tangibleid%TYPE,
47 cp_payee_id IN iby_trans_all_v.payeeid%TYPE,
48 cp_capture IN VARCHAR2) IS
49 SELECT status
50 FROM iby_trans_all_v
51 WHERE tangibleid = cp_tangible_id AND
52 payeeid = cp_payee_id AND
53 reqtype = cp_capture
54 ORDER BY reqdate DESC;
55
56 -- Get the person number of the person
57 CURSOR c_pers(cp_person_id IN hz_parties.party_id%TYPE
58 ) IS
59 SELECT party_number
60 FROM hz_parties
61 WHERE party_id = cp_person_id;
62
63 e_skip EXCEPTION;
64
65 l_n_status iby_trans_all_v.status%TYPE;
66 l_c_cc_status igs_fi_credits_all.credit_card_status_code%TYPE := 'PENDING';
67 l_c_tangible_cd igs_fi_credits_all.credit_card_tangible_cd%TYPE;
68 l_c_payee_cd igs_fi_credits_all.credit_card_payee_cd%TYPE;
69 l_flag BOOLEAN := FALSE;
70 l_c_pnum hz_parties.party_number%TYPE;
71 l_b_exception_flag BOOLEAN := FALSE;
72
73 g_c_capture CONSTANT VARCHAR2(13):= 'ORAPMTCAPTURE';
74 g_c_failure CONSTANT VARCHAR2(7) := 'FAILURE';
75 g_c_success CONSTANT VARCHAR2(7) := 'SUCCESS';
76
77 BEGIN
78
79 -- There exists at least on credit or admission record to process
80 l_b_records := TRUE;
81 -- Fetch the tangible code and payee code depending
82 -- on whether a credit or admission appln. record is passed
83 IF p_r_crd_row.party_id IS NOT NULL THEN
84 l_c_tangible_cd := p_r_crd_row.credit_card_tangible_cd;
85 l_c_payee_cd := p_r_crd_row.credit_card_payee_cd;
86 ELSIF p_r_ad_app_row.person_id IS NOT NULL THEN
87 l_c_tangible_cd := p_r_ad_app_row.credit_card_tangible_cd;
88 l_c_payee_cd := p_r_ad_app_row.credit_card_payee_cd;
89 END IF;
90
91 -- Fetch the status number from the IBY table
92 OPEN c_iby_trans(cp_tangible_id => l_c_tangible_cd,
93 cp_payee_id => l_c_payee_cd,
94 cp_capture => g_c_capture);
95 FETCH c_iby_trans INTO l_n_status;
96 CLOSE c_iby_trans;
97
98 IF (l_n_status = 114) OR (l_n_status < 99 AND l_n_status > 0) THEN
99 -- the transaction is a failure
100 l_c_cc_status := g_c_failure;
101 ELSIF l_n_status = 0 THEN
102 -- the transaction is a success
103 l_c_cc_status := g_c_success;
104 END IF;
105
106 -- Fetch the person number
107 OPEN c_pers(NVL(p_r_crd_row.party_id,p_r_ad_app_row.person_id));
108 FETCH c_pers INTO l_c_pnum;
109 CLOSE c_pers;
110
111 -- Log the person number
112 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||l_c_pnum);
113
114 -- Update the OSS Tables (Credits, Admissions Applications Request)
115 -- only if the credit card status is changed to failure or success
116 -- from 'Pending'
117 IF l_c_cc_status in (g_c_failure,g_c_success) THEN
118 l_flag := TRUE;
119 END IF;
120
121 IF p_r_crd_row.party_id IS NOT NULL THEN
122
123 -- Log the Receipt Number
124 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','RECEIPT_NUMBER')||': '||p_r_crd_row.credit_number);
125
126 IF l_flag THEN
127 -- Update the credit card status in the Credits Table
128 SAVEPOINT sp_crd;
129 BEGIN
130 igs_fi_credits_pkg.update_row(
131 x_rowid => p_r_crd_row.rowid,
132 x_credit_id => p_r_crd_row.credit_id,
133 x_credit_number => p_r_crd_row.credit_number,
134 x_status => p_r_crd_row.status,
135 x_credit_source => p_r_crd_row.credit_source,
136 x_party_id => p_r_crd_row.party_id,
137 x_credit_type_id => p_r_crd_row.credit_type_id,
138 x_credit_instrument => p_r_crd_row.credit_instrument,
139 x_description => p_r_crd_row.description,
140 x_amount => p_r_crd_row.amount,
141 x_currency_cd => p_r_crd_row.currency_cd,
142 x_exchange_rate => p_r_crd_row.exchange_rate,
143 x_transaction_date => p_r_crd_row.transaction_date,
144 x_effective_date => p_r_crd_row.effective_date,
145 x_reversal_date => p_r_crd_row.reversal_date,
146 x_reversal_reason_code => p_r_crd_row.reversal_reason_code,
147 x_reversal_comments => p_r_crd_row.reversal_comments,
148 x_unapplied_amount => p_r_crd_row.unapplied_amount,
149 x_source_transaction_id => p_r_crd_row.source_transaction_id,
150 x_receipt_lockbox_number => p_r_crd_row.receipt_lockbox_number,
151 x_merchant_id => p_r_crd_row.merchant_id,
152 x_credit_card_code => p_r_crd_row.credit_card_code,
153 x_credit_card_holder_name => p_r_crd_row.credit_card_holder_name,
154 x_credit_card_number => p_r_crd_row.credit_card_number,
155 x_credit_card_expiration_date => p_r_crd_row.credit_card_expiration_date,
156 x_credit_card_approval_code => p_r_crd_row.credit_card_approval_code,
157 x_awd_yr_cal_type => p_r_crd_row.awd_yr_cal_type,
158 x_awd_yr_ci_sequence_number => p_r_crd_row.awd_yr_ci_sequence_number,
159 x_fee_cal_type => p_r_crd_row.fee_cal_type,
160 x_fee_ci_sequence_number => p_r_crd_row.fee_ci_sequence_number,
161 x_attribute_category => p_r_crd_row.attribute_category,
162 x_attribute1 => p_r_crd_row.attribute1,
163 x_attribute2 => p_r_crd_row.attribute2,
164 x_attribute3 => p_r_crd_row.attribute3,
165 x_attribute4 => p_r_crd_row.attribute4,
166 x_attribute5 => p_r_crd_row.attribute5,
167 x_attribute6 => p_r_crd_row.attribute6,
168 x_attribute7 => p_r_crd_row.attribute7,
169 x_attribute8 => p_r_crd_row.attribute8,
170 x_attribute9 => p_r_crd_row.attribute9,
171 x_attribute10 => p_r_crd_row.attribute10,
172 x_attribute11 => p_r_crd_row.attribute11,
173 x_attribute12 => p_r_crd_row.attribute12,
174 x_attribute13 => p_r_crd_row.attribute13,
175 x_attribute14 => p_r_crd_row.attribute14,
176 x_attribute15 => p_r_crd_row.attribute15,
177 x_attribute16 => p_r_crd_row.attribute16,
178 x_attribute17 => p_r_crd_row.attribute17,
179 x_attribute18 => p_r_crd_row.attribute18,
180 x_attribute19 => p_r_crd_row.attribute19,
181 x_attribute20 => p_r_crd_row.attribute20,
182 x_gl_date => p_r_crd_row.gl_date,
183 x_check_number => p_r_crd_row.check_number,
184 x_source_transaction_type => p_r_crd_row.source_transaction_type,
185 x_source_transaction_ref => p_r_crd_row.source_transaction_ref,
186 x_credit_card_status_code => l_c_cc_status,
187 x_credit_card_payee_cd => p_r_crd_row.credit_card_payee_cd,
188 x_credit_card_tangible_cd => p_r_crd_row.credit_card_tangible_cd,
189 x_source_invoice_id => p_r_crd_row.source_invoice_id,
190 x_tax_year_code => p_r_crd_row.tax_year_code,
191 x_waiver_name => p_r_crd_row.waiver_name
192 );
193 EXCEPTION
194 WHEN OTHERS THEN
195 ROLLBACK TO sp_crd;
196 l_b_exception_flag := TRUE;
197 fnd_file.put_line(fnd_file.log,sqlerrm);
198 fnd_file.new_line(fnd_file.log);
199 END;
200 END IF; -- For l_flag
201
202 ELSIF p_r_ad_app_row.person_id IS NOT NULL THEN
203
204 -- Log the Admission Application Id
205 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','ADM_APPL_ID')||': '||p_r_ad_app_row.app_req_id);
206
207 IF l_flag THEN
208 SAVEPOINT sp_adm;
209 BEGIN
210 -- Update the credit card status in the Admissions Applications Request Table
211 igs_ad_gen_015.update_igs_ad_app_req(
212 p_rowid => p_r_ad_app_row.rowid,
213 p_app_req_id => p_r_ad_app_row.app_req_id,
214 p_person_id => p_r_ad_app_row.person_id,
215 p_admission_appl_number => p_r_ad_app_row.admission_appl_number,
216 p_applicant_fee_type => p_r_ad_app_row.applicant_fee_type,
217 p_applicant_fee_status => p_r_ad_app_row.applicant_fee_status,
218 p_fee_date => p_r_ad_app_row.fee_date,
219 p_fee_payment_method => p_r_ad_app_row.fee_payment_method,
220 p_fee_amount => p_r_ad_app_row.fee_amount,
221 p_reference_num => p_r_ad_app_row.reference_num,
222 p_credit_card_code => p_r_ad_app_row.credit_card_code,
223 p_credit_card_holder_name => p_r_ad_app_row.credit_card_holder_name,
224 p_credit_card_number => p_r_ad_app_row.credit_card_number,
225 p_credit_card_expiration_date => p_r_ad_app_row.credit_card_expiration_date,
226 p_rev_gl_ccid => p_r_ad_app_row.rev_gl_ccid,
227 p_cash_gl_ccid => p_r_ad_app_row.cash_gl_ccid,
228 p_rev_account_cd => p_r_ad_app_row.rev_account_cd,
229 p_cash_account_cd => p_r_ad_app_row.cash_account_cd,
230 p_posting_control_id => p_r_ad_app_row.posting_control_id,
231 p_gl_date => p_r_ad_app_row.gl_date,
232 p_gl_posted_date => p_r_ad_app_row.gl_posted_date,
233 p_credit_card_tangible_cd => p_r_ad_app_row.credit_card_tangible_cd,
234 p_credit_card_payee_cd => p_r_ad_app_row.credit_card_payee_cd,
235 p_credit_card_status_code => l_c_cc_status,
236 p_mode => 'R'
237 );
238 EXCEPTION
239 WHEN OTHERS THEN
240 ROLLBACK TO sp_adm;
241 l_b_exception_flag := TRUE;
242 fnd_file.put_line(fnd_file.log,sqlerrm);
243 fnd_file.new_line(fnd_file.log);
244 END;
245 END IF;
246 END IF;
247
248 IF NOT l_b_exception_flag THEN
249 -- Log the Credit Card Status of the person
250 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CC_STATUS')||': '||
251 igs_fi_gen_gl.get_lkp_meaning('IGS_FI_CREDIT_CARD_STATUS',l_c_cc_status));
252 fnd_file.new_line(fnd_file.log);
253 END IF;
254
255 END local_upd;
256
257 PROCEDURE upd_status(errbuf OUT NOCOPY VARCHAR2,
258 retcode OUT NOCOPY NUMBER
259 ) AS
260 ------------------------------------------------------------------
261 --Created by : schodava, Oracle IDC
262 --Date created: 09-Jun-2003
263 --
264 --Purpose: Concurrent program to Update the Credit Card Status
265 --
266 --Known limitations/enhancements and/or remarks:
267 --
268 --Change History:
269 --Who When What
270 --abshriva 4-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
271 -------------------------------------------------------------------
272
273 g_c_pending CONSTANT VARCHAR2(7) := 'PENDING';
274 g_c_cc CONSTANT VARCHAR2(2) := 'CC';
275 l_c_manage_acc igs_fi_control_all.manage_accounts%TYPE;
276 l_c_message_name fnd_new_messages.message_name%TYPE;
277 l_org_id VARCHAR2(15);
278 e_resource_busy EXCEPTION;
279 PRAGMA EXCEPTION_INIT(e_resource_busy,-0054);
280
281 BEGIN
282
283 BEGIN
284 l_org_id := NULL;
285 igs_ge_gen_003.set_org_id(l_org_id);
286 EXCEPTION
287 WHEN OTHERS THEN
288 fnd_file.put_line (fnd_file.log, fnd_message.get);
289 retcode:=2;
290 RETURN;
291 END;
292 retcode := 0;
293 errbuf := NULL;
294
295 -- Call the generic proc to obtain the Manage Accounts set up
296 -- in the System Options form.
297 igs_fi_com_rec_interface.chk_manage_account(p_v_manage_acc => l_c_manage_acc,
298 p_v_message_name => l_c_message_name);
299
300 -- If Manage Accounts is 'Other' or Null, then this process is not available.
301 IF (l_c_manage_acc = 'OTHER') OR (l_c_manage_acc IS NULL) THEN
302 fnd_message.set_name('IGS',l_c_message_name);
303 fnd_file.put_line(fnd_file.log,fnd_message.get());
304 fnd_file.new_line(fnd_file.log);
305 retcode := 2;
306 RETURN;
307 END IF;
308
309 -- For each credit record
310 FOR rec_credits IN c_credits(cp_cc => g_c_cc,
311 cp_pending => g_c_pending) LOOP
312 -- call the local procedure for processing
313 local_upd(p_r_crd_row => rec_credits,
314 p_r_ad_app_row => NULL);
315 END LOOP;
316
317 -- For each admission application record
318 FOR rec_app_req IN c_app_req(cp_pending => g_c_pending) LOOP
319 -- call the local procedure for processing
320 local_upd(p_r_crd_row => NULL,
321 p_r_ad_app_row => rec_app_req);
322 END LOOP;
323
324 -- Log a message if there are no records to process
325 IF NOT l_b_records THEN
326 fnd_message.set_name('IGS','IGS_FI_NO_RECORD_AVAILABLE');
327 fnd_file.put_line(fnd_file.log,fnd_message.get());
328 END IF;
329
330
331 EXCEPTION
332 WHEN e_resource_busy THEN
333 ROLLBACK;
334 fnd_message.set_name('IGS','IGS_FI_RFND_REC_LOCK');
335 fnd_file.put_line(fnd_file.log,fnd_message.get());
336 fnd_file.new_line(fnd_file.log);
337 retcode := 2;
338
339 WHEN OTHERS THEN
340 ROLLBACK;
341 retcode := 2;
342 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' - '||SUBSTR(SQLERRM,1,40);
343 igs_ge_msg_stack.add;
344 igs_ge_msg_stack.conc_exception_hndl;
345
346 END upd_status;
347
348 END igs_fi_cc_pmt;