[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_COM_REC_INTERFACE
Source
1 PACKAGE BODY igs_fi_com_rec_interface AS
2 /* $Header: IGSFI81B.pls 120.2 2006/05/04 07:45:15 abshriva noship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGS_FI_COM_REC_INTERFACE |
11 | |
12 | NOTES |
13 | New Package created for procedures and functions as per |
14 | Commercial Receivables TD. (Enh 2831569) |
15 | HISTORY |
16 | Who When What |
17 |abshriva 4-May-2006 Bug 5178077: Modification in procedure Transfer |
18 | svuppala 30-MAY-2005 Enh 3442712 - Done the TBH |
19 | modifications by adding new columns |
20 | Unit_Type_Id, Unit_Level in |
21 | igs_fi_invln_int_all |
22 | pathipat 22-Apr-2004 Enh 3558549 - Comm Rec Enh build |
23 | Added 2 new cols to igs_fi_com_recs_int
24 | Modified transfer() for the above |
25 |uudayapr 16-oct-2003 Enh #3117341 Modified the cur_charges in |
26 | Transfer procedure as a part of AUDIT and |
27 | SPECIAL FEES BUILD. |
28 *=======================================================================*/
29
30
31 g_b_data_found BOOLEAN := FALSE;
32
33 g_v_space CONSTANT VARCHAR2(10) := ' '; -- Constant 7 char space used in logging messages in log file.
34 g_v_tutnfee CONSTANT VARCHAR2(20) := 'TUTNFEE';
35 g_v_other CONSTANT VARCHAR2(20) := 'OTHER';
36 g_v_external CONSTANT VARCHAR2(20) := 'EXTERNAL';
37 g_v_ancillary CONSTANT VARCHAR2(20) := 'ANCILLARY';
38 g_v_sponsor CONSTANT VARCHAR2(20) := 'SPONSOR';
39 g_v_aid_adj CONSTANT VARCHAR2(20) := 'AID_ADJ';
40 g_v_document CONSTANT VARCHAR2(20) := 'DOCUMENT';
41
42 -- Constant variables for Credit Classes SPNSP and CHGADJ
43 g_v_spnsp CONSTANT VARCHAR2(7) := 'SPNSP';
44 g_v_chgadj CONSTANT VARCHAR2(7) := 'CHGADJ';
45
46 --Added the constant variable for AUDIT and SPECIAL FEE TYPE CATEGORY.
47 g_v_audit CONSTANT VARCHAR2(20) := 'AUDIT';
48 g_v_special CONSTANT VARCHAR2(20) := 'SPECIAL';
49
50
51 PROCEDURE chk_manage_account( p_v_manage_acc OUT NOCOPY VARCHAR2,
52 p_v_message_name OUT NOCOPY VARCHAR2
53 ) AS
54 ------------------------------------------------------------------
55 --Created by : Priya Athipatla, Oracle IDC
56 --Date created: 10-APR-2003
57 --
58 --Purpose: Procedure returns value of MANAGE_ACCOUNTS column in
59 -- IGS_FI_CONTROL_ALL
60 --
61 --Known limitations/enhancements and/or remarks:
62 --
63 --Change History:
64 --Who When What
65 --
66 -------------------------------------------------------------------
67
68 CURSOR cur_manage_acct IS
69 SELECT manage_accounts
70 FROM igs_fi_control_all;
71
72 BEGIN
73
74 OPEN cur_manage_acct;
75 FETCH cur_manage_acct INTO p_v_manage_acc;
76 IF cur_manage_acct%NOTFOUND THEN
77 p_v_manage_acc := NULL;
78 p_v_message_name := 'IGS_FI_SYSTEM_OPT_SETUP';
79 RETURN;
80 END IF;
81 CLOSE cur_manage_acct;
82
83 -- If manage_accounts is NULL
84 IF (p_v_manage_acc IS NULL) THEN
85 p_v_message_name := 'IGS_FI_MANAGE_ACC_NULL';
86 RETURN;
87 -- If manage_accounts is OTHER
88 ELSIF (p_v_manage_acc = 'OTHER') THEN
89 p_v_message_name := 'IGS_FI_MANAGE_ACC_OTH';
90 RETURN;
91 -- If manage_Accounts is STUDENT_FINANCE
92 ELSIF (p_v_manage_acc = 'STUDENT_FINANCE') THEN
93 p_v_message_name := NULL;
94 RETURN;
95 END IF;
96
97 END chk_manage_account;
98
99
100 FUNCTION get_party_number(p_party_id IN hz_parties.party_id%TYPE) RETURN VARCHAR2 AS
101 ------------------------------------------------------------------
102 --Created by : Priya Athipatla, Oracle IDC
103 --Date created: 21-APR-2003
104 --
105 --Purpose: Function returning party_number for the passed party_id
106 --
107 --Known limitations/enhancements and/or remarks:
108 --
109 --Change History:
110 --Who When What
111 --
112 -------------------------------------------------------------------
113 CURSOR cur_get_party(cp_party_id hz_parties.party_id%TYPE) IS
114 SELECT party_number
115 FROM hz_parties
116 WHERE party_id = cp_party_id;
117
118 l_v_party_number hz_parties.party_number%TYPE := NULL;
119
120 BEGIN
121
122 -- Obtain the party_number corresponding to the party_id passed
123 OPEN cur_get_party(p_party_id);
124 FETCH cur_get_party INTO l_v_party_number;
125 CLOSE cur_get_party;
126
127 RETURN l_v_party_number;
128
129 END get_party_number;
130
131
132 PROCEDURE transfer(errbuf OUT NOCOPY VARCHAR2,
133 retcode OUT NOCOPY NUMBER
134 ) AS
135 ------------------------------------------------------------------
136 --Created by : Priya Athipatla, Oracle IDC
137 --Date created: 21-APR-2003
138 --
139 --Purpose: Concurrent program to Transfer data to Commercial Receivables
140 --
141 --Known limitations/enhancements and/or remarks:
142 --
143 --Change History:
144 --Who When What
145 --abshriva 4-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
146 --svuppala 30-MAY-2005 Enh 3442712 - Done the TBH modifications by adding
147 -- new columns Unit_Type_Id, Unit_Level in igs_fi_invln_int_all
148 --pathipat 22-Apr-2004 Enh 3558549 - Comm Rec Enh build
149 -- Added code w.r.t new columns student_party_id and
150 -- source_invoice_id in igs_fi_com_recs_int
151 --uudayapr 16-oct-2003 Enh#3117341 Modified cur_charges cursor as a part
152 -- of audit and special fees built.
153 -------------------------------------------------------------------
154
155 CURSOR cur_charges IS
156 SELECT inv.person_id,
157 inv.fee_type,
158 inv.course_cd,
159 inv.invoice_creation_date,
160 inv.fee_cat,
161 inv.fee_ci_sequence_number,
162 inv.fee_cal_type,
163 inv.effective_date,
164 inv.waiver_flag,
165 inv.waiver_reason,
166 inv.attendance_type,
167 inv.attendance_mode,
168 inv.currency_cd,
169 inv.invoice_amount charge_amount,
170 inv.invoice_number charge_number,
171 ft.s_fee_type,
172 invln.row_id invln_rowid,
173 invln.*
174 FROM igs_fi_inv_int inv,
175 igs_fi_invln_int invln,
176 igs_fi_fee_type ft
177 WHERE inv.invoice_id = invln.invoice_id
178 AND inv.fee_type = ft.fee_type
179 AND ft.s_fee_type IN (g_v_tutnfee,g_v_other,g_v_external,
180 g_v_ancillary,g_v_sponsor,g_v_aid_adj,g_v_document,
181 g_v_audit,g_v_special) -- Added audit and special fees.
182 AND invln.gl_posted_date IS NULL
183 ORDER BY inv.person_id, ft.s_fee_type, inv.invoice_number
184 FOR UPDATE OF invln.gl_posted_date NOWAIT;
185
186 CURSOR cur_credits IS
187 SELECT crd.credit_id cr_id,
188 crd.credit_number,
189 crd.party_id,
190 crd.transaction_date trans_date,
191 crd.effective_date,
192 crd.fee_cal_type,
193 crd.fee_ci_sequence_number,
194 crd.currency_cd,
195 crd.description,
196 crd.credit_type_id,
197 crd.source_invoice_id,
198 cra.*,
199 cra.rowid cra_rowid,
200 crt.credit_class
201 FROM igs_fi_credits crd,
202 igs_fi_cr_activities cra,
203 igs_fi_cr_types crt
204 WHERE crd.credit_id = cra.credit_id
205 AND crd.credit_type_id = crt.credit_type_id
206 AND crt.credit_class IN (g_v_chgadj, g_v_spnsp)
207 AND cra.gl_posted_date IS NULL
208 FOR UPDATE OF cra.gl_posted_date NOWAIT;
209
210 CURSOR cur_course_desc(cp_course_cd igs_ps_ver.course_cd%TYPE) IS
211 SELECT title
212 FROM igs_ps_ver
213 WHERE course_cd = cp_course_cd;
214
215 -- Returns the party_id from Credits table corresponding to the charge denoted by
216 -- the SOURCE_INVOICE_ID column.
217 CURSOR cur_chg_stdnt_party(cp_invoice_id igs_fi_inv_int_all.invoice_id%TYPE) IS
218 SELECT party_id
219 FROM igs_fi_credits
220 WHERE source_invoice_id = cp_invoice_id;
221
222 -- Returns the party_id from Charges Table correponding to the value in Source_invoice_id
223 -- column of the Credits Table of type Aid Adjustment
224 CURSOR cur_credit_stdnt_party(cp_source_invoice_id igs_fi_credits_all.source_invoice_id%TYPE) IS
225 SELECT person_id
226 FROM igs_fi_inv_int
227 WHERE invoice_id = cp_source_invoice_id
228 AND transaction_type = g_v_aid_adj;
229
230 l_v_title igs_ps_ver.title%TYPE := NULL;
231
232 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
233 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
234
235 l_v_party igs_lookup_values.meaning%TYPE := NULL;
236 l_v_charge_number igs_lookup_values.meaning%TYPE := NULL;
237 l_v_s_fee_type igs_lookup_values.meaning%TYPE := NULL;
238 l_v_fee_type igs_lookup_values.meaning%TYPE := NULL;
239 l_v_charge_amt igs_lookup_values.meaning%TYPE := NULL;
240 l_v_credit_number igs_lookup_values.meaning%TYPE := NULL;
241
242 l_n_last_person_id hz_parties.party_id%TYPE := NULL;
243 l_org_id VARCHAR2(15);
244 l_rowid ROWID := NULL;
245
246 skip_record EXCEPTION;
247 e_resource_busy EXCEPTION;
248
249 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
250
251 l_n_chg_stdnt_party igs_fi_credits_all.party_id%TYPE;
252 l_n_credit_stdnt_party igs_fi_inv_int_all.person_id%TYPE;
253
254 BEGIN
255 BEGIN
256 l_org_id := NULL;
257 igs_ge_gen_003.set_org_id(l_org_id);
258 EXCEPTION
259 WHEN OTHERS THEN
260 fnd_file.put_line (fnd_file.log, fnd_message.get);
261 retcode:=2;
262 RETURN;
263 END;
264 retcode := 0;
265 errbuf := NULL;
266
267 -- Step 1:
268 -- Call the generic proc to obtain the Manage Accounts set up
269 -- in the System Options form.
270 chk_manage_account(l_v_manage_acc,
271 l_v_message_name);
272
273 -- If Manage Accounts <> 'Other' then this process is not available.
274 IF (l_v_manage_acc <> 'OTHER') OR (l_v_manage_acc IS NULL) THEN
275 fnd_message.set_name('IGS','IGS_FI_MANAGE_ACC_PRC_OTH');
276 fnd_file.put_line(fnd_file.log,fnd_message.get());
277 fnd_file.new_line(fnd_file.log);
278 retcode := 2;
279 RETURN;
280 END IF;
281
282 -- This message is always logged irrespective of error records being found or not
283 fnd_message.set_name('IGS','IGS_FI_LOG_ERR_TRX');
284 fnd_file.put_line(fnd_file.log,fnd_message.get());
285 fnd_file.put_line(FND_FILE.LOG,' ');
286
287 -- Obtain the meaning of the lookup codes to log the details.
288 l_v_party := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PARTY');
289 l_v_charge_number := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CHARGE_NUMBER');
290 l_v_s_fee_type := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','SYSTEM_FEE_TYPE');
291 l_v_fee_type := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPE');
292 l_v_charge_amt := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CHG_AMOUNT');
293 l_v_credit_number := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','CREDIT_NUMBER');
294
295 -- Step 2: Loop across all the transactions in the Charges table (cur_charges)
296 FOR l_rec_charges IN cur_charges
297 LOOP
298 BEGIN
299
300 SAVEPOINT sp_charges;
301
302 -- Step 3: If Error Account of record is 'Y' then log the person details
303 -- and skip the record. Move to the next record fetched.
304 IF l_rec_charges.error_account = 'Y' THEN
305 g_b_data_found := TRUE;
306 -- Log Person Number only once for a given person
307 IF l_n_last_person_id = l_rec_charges.person_id THEN
308 NULL;
309 ELSE
310 -- Log the details of the party with error_account = 'Y'
311 fnd_file.put_line(fnd_file.log,l_v_party||': '||get_party_number(l_rec_charges.person_id));
312 l_n_last_person_id := l_rec_charges.person_id;
313 END IF;
314
315 fnd_file.put_line(fnd_file.log,g_v_space ||l_v_charge_number||': '||l_rec_charges.charge_number);
316 fnd_file.put_line(fnd_file.log,g_v_space ||l_v_s_fee_type ||': '||l_rec_charges.s_fee_type);
317 fnd_file.put_line(fnd_file.log,g_v_space ||l_v_fee_type ||': '||l_rec_charges.fee_type);
318 fnd_file.put_line(fnd_file.log,g_v_space ||l_v_charge_amt ||': '||l_rec_charges.charge_amount);
319 fnd_file.new_line(fnd_file.log);
320
321 -- Skip the record and move to the next record from Step 2
322 RAISE skip_record;
323
324 END IF; -- End IF for error_account = 'Y'
325
326 -- Processing for Error Account = 'N'
327 l_rowid := NULL;
328 -- Obtain the Description corresponding to the Course_Cd
329 OPEN cur_course_desc(l_rec_charges.course_cd);
330 FETCH cur_course_desc INTO l_v_title;
331 CLOSE cur_course_desc;
332
333 -- Obtain value to be inserted into Student Party ID column
334 IF l_rec_charges.s_fee_type = g_v_sponsor THEN
335 OPEN cur_chg_stdnt_party(l_rec_charges.invoice_id);
336 FETCH cur_chg_stdnt_party INTO l_n_chg_stdnt_party;
337 CLOSE cur_chg_stdnt_party;
338 ELSE
339 l_n_chg_stdnt_party := NULL;
340 END IF;
341
342 -- Step 4: If Error Account = N, then insert the record into IGS_FI_COM_RECS_INT table
343 igs_fi_com_recs_int_pkg.insert_row ( x_rowid => l_rowid ,
344 x_transaction_category => 'CHARGE' ,
345 x_transaction_header_id => l_rec_charges.invoice_id ,
346 x_transaction_number => l_rec_charges.charge_number ,
347 x_party_id => l_rec_charges.person_id ,
348 x_transaction_date => l_rec_charges.invoice_creation_date ,
349 x_effective_date => l_rec_charges.effective_date ,
350 x_fee_type => l_rec_charges.fee_type ,
351 x_s_fee_type => l_rec_charges.s_fee_type ,
352 x_fee_cal_type => l_rec_charges.fee_cal_type ,
353 x_fee_ci_sequence_number => l_rec_charges.fee_ci_sequence_number ,
354 x_fee_category => l_rec_charges.fee_cat ,
355 x_course_cd => l_rec_charges.course_cd ,
356 x_attendance_mode => l_rec_charges.attendance_mode ,
357 x_attendance_type => l_rec_charges.attendance_type ,
358 x_course_description => l_v_title ,
359 x_reversal_flag => l_rec_charges.waiver_flag,
360 x_reversal_reason => l_rec_charges.waiver_reason,
361 x_line_number => l_rec_charges.line_number,
362 x_transaction_line_id => l_rec_charges.invoice_lines_id,
363 x_charge_method_type => l_rec_charges.s_chg_method_type,
364 x_description => l_rec_charges.description,
365 x_charge_elements => l_rec_charges.chg_elements,
366 x_amount => l_rec_charges.amount,
367 x_credit_points => l_rec_charges.credit_points,
368 x_unit_offering_option_id => l_rec_charges.uoo_id,
369 x_cr_gl_code_combination_id => l_rec_charges.rev_gl_ccid,
370 x_dr_gl_code_combination_id => l_rec_charges.rec_gl_ccid,
371 x_credit_account_code => l_rec_charges.rev_account_cd,
372 x_debit_account_code => l_rec_charges.rec_account_cd,
373 x_org_unit_cd => l_rec_charges.org_unit_cd,
374 x_location_cd => l_rec_charges.location_cd,
375 x_gl_date => l_rec_charges.gl_date,
376 x_credit_type_id => NULL,
377 x_credit_class => NULL,
378 x_currency_cd => l_rec_charges.currency_cd,
379 x_extract_flag => NULL,
380 x_mode => 'R',
381 x_student_party_id => l_n_chg_stdnt_party,
382 x_source_invoice_id => NULL
383 );
384
385 -- Step 5: For every record inserted into the Receivables interface table,
386 -- update the record in IGS_FI_INVLN_INT - set gl_posted_date to sysdate
387 igs_fi_invln_int_pkg.update_row( x_rowid => l_rec_charges.invln_rowid,
388 x_invoice_id => l_rec_charges.invoice_id,
389 x_line_number => l_rec_charges.line_number,
390 x_invoice_lines_id => l_rec_charges.invoice_lines_id,
391 x_attribute2 => l_rec_charges.attribute2,
392 x_chg_elements => l_rec_charges.chg_elements,
393 x_amount => l_rec_charges.amount,
394 x_unit_attempt_status => l_rec_charges.unit_attempt_status,
395 x_eftsu => l_rec_charges.eftsu,
396 x_credit_points => l_rec_charges.credit_points,
397 x_attribute_category => l_rec_charges.attribute_category,
398 x_attribute1 => l_rec_charges.attribute1,
399 x_s_chg_method_type => l_rec_charges.s_chg_method_type,
400 x_description => l_rec_charges.description,
401 x_attribute3 => l_rec_charges.attribute3,
402 x_attribute4 => l_rec_charges.attribute4,
403 x_attribute5 => l_rec_charges.attribute5,
404 x_attribute6 => l_rec_charges.attribute6,
405 x_attribute7 => l_rec_charges.attribute7,
406 x_attribute8 => l_rec_charges.attribute8,
407 x_attribute9 => l_rec_charges.attribute9,
408 x_attribute10 => l_rec_charges.attribute10,
409 x_rec_account_cd => l_rec_charges.rec_account_cd,
410 x_rev_account_cd => l_rec_charges.rev_account_cd,
411 x_rec_gl_ccid => l_rec_charges.rec_gl_ccid,
412 x_rev_gl_ccid => l_rec_charges.rev_gl_ccid,
413 x_org_unit_cd => l_rec_charges.org_unit_cd,
414 x_posting_id => l_rec_charges.posting_id,
415 x_attribute11 => l_rec_charges.attribute11,
416 x_attribute12 => l_rec_charges.attribute12,
417 x_attribute13 => l_rec_charges.attribute13,
418 x_attribute14 => l_rec_charges.attribute14,
419 x_attribute15 => l_rec_charges.attribute15,
420 x_attribute16 => l_rec_charges.attribute16,
421 x_attribute17 => l_rec_charges.attribute17,
422 x_attribute18 => l_rec_charges.attribute18,
423 x_attribute19 => l_rec_charges.attribute19,
424 x_attribute20 => l_rec_charges.attribute20,
425 x_error_string => l_rec_charges.error_string,
426 x_error_account => l_rec_charges.error_account,
427 x_location_cd => l_rec_charges.location_cd,
428 x_uoo_id => l_rec_charges.uoo_id,
429 x_gl_date => l_rec_charges.gl_date,
430 x_gl_posted_date => TRUNC(SYSDATE),
431 x_posting_control_id => l_rec_charges.posting_control_id,
432 x_mode => 'R' ,
433 x_unit_type_id => l_rec_charges.unit_type_id,
434 x_unit_level => l_rec_charges.unit_level
435 );
436
437 EXCEPTION
438 WHEN skip_record THEN
439 -- Process ends with a warning status
440 retcode := 1;
441 NULL;
442 WHEN OTHERS THEN
443 -- Record with some error encountered, so set flag to TRUE
444 g_b_data_found := TRUE;
445 -- Process ends with a warning status
446 retcode := 1;
447 ROLLBACK TO sp_charges;
448 fnd_file.put_line(fnd_file.log,l_v_charge_number||' - '||l_rec_charges.invoice_id || ': '||SQLERRM);
449 fnd_file.new_line(fnd_file.log);
450 END;
451 END LOOP; -- End of Step 2 - Loop across all Charges
452
453 -- Commit transactions after charges are processed
454 COMMIT;
455
456 -- Step 6: Loop across all the Credits, i.e., cur_credits
457 FOR l_rec_credits IN cur_credits
458 LOOP
459 BEGIN
460 SAVEPOINT sp_credits;
461 l_rowid := NULL;
462
463 IF (l_rec_credits.credit_class = g_v_spnsp) THEN
464 OPEN cur_credit_stdnt_party(l_rec_credits.source_invoice_id);
465 FETCH cur_credit_stdnt_party INTO l_n_credit_stdnt_party;
466 CLOSE cur_credit_stdnt_party;
467 ELSE
468 l_n_credit_stdnt_party := NULL;
469 END IF;
470
471 -- Step 7: Insert the credit record into the interface table.
472 igs_fi_com_recs_int_pkg.insert_row( x_rowid => l_rowid ,
473 x_transaction_category => 'CREDIT' ,
474 x_transaction_header_id => l_rec_credits.cr_id ,
475 x_transaction_number => l_rec_credits.credit_number ,
476 x_party_id => l_rec_credits.party_id ,
477 x_transaction_date => l_rec_credits.trans_date ,
478 x_effective_date => l_rec_credits.effective_date ,
479 x_fee_type => NULL,
480 x_s_fee_type => NULL,
481 x_fee_cal_type => l_rec_credits.fee_cal_type ,
482 x_fee_ci_sequence_number => l_rec_credits.fee_ci_sequence_number ,
483 x_fee_category => NULL,
484 x_course_cd => NULL,
485 x_attendance_mode => NULL,
486 x_attendance_type => NULL,
487 x_course_description => NULL,
488 x_reversal_flag => NULL,
489 x_reversal_reason => NULL,
490 x_line_number => NULL,
491 x_transaction_line_id => l_rec_credits.credit_activity_id,
492 x_charge_method_type => NULL,
493 x_description => l_rec_credits.description,
494 x_charge_elements => NULL,
495 x_amount => l_rec_credits.amount,
496 x_credit_points => NULL,
497 x_unit_offering_option_id => NULL,
498 x_cr_gl_code_combination_id => l_rec_credits.cr_gl_ccid,
499 x_dr_gl_code_combination_id => l_rec_credits.dr_gl_ccid,
500 x_credit_account_code => l_rec_credits.cr_account_cd,
501 x_debit_account_code => l_rec_credits.dr_account_cd,
502 x_org_unit_cd => NULL,
503 x_location_cd => NULL,
504 x_gl_date => l_rec_credits.gl_date,
505 x_credit_type_id => l_rec_credits.credit_type_id,
506 x_credit_class => l_rec_credits.credit_class,
507 x_currency_cd => l_rec_credits.currency_cd,
508 x_extract_flag => NULL,
509 x_mode => 'R',
510 x_student_party_id => l_n_credit_stdnt_party,
511 x_source_invoice_id => l_rec_credits.source_invoice_id
512 );
513
514 -- Step 8: For each record inserted, update gl_posted_Date to sysdate in the Activities table
515 igs_fi_cr_activities_pkg.update_row( x_rowid => l_rec_credits.cra_rowid,
516 x_credit_activity_id => l_rec_credits.credit_activity_id,
517 x_credit_id => l_rec_credits.credit_id,
518 x_status => l_rec_credits.status,
519 x_transaction_date => l_rec_credits.transaction_date,
520 x_amount => l_rec_credits.amount,
521 x_dr_account_cd => l_rec_credits.dr_account_cd,
522 x_cr_account_cd => l_rec_credits.cr_account_cd,
523 x_dr_gl_ccid => l_rec_credits.dr_gl_ccid,
524 x_cr_gl_ccid => l_rec_credits.cr_gl_ccid,
525 x_bill_id => l_rec_credits.bill_id,
526 x_bill_number => l_rec_credits.bill_number,
527 x_bill_date => l_rec_credits.bill_date,
528 x_posting_id => l_rec_credits.posting_id,
529 x_gl_date => l_rec_credits.gl_date,
530 x_gl_posted_date => TRUNC(SYSDATE),
531 x_posting_control_id => l_rec_credits.posting_control_id,
532 x_mode => 'R'
533 );
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 -- Record with some error encountered, so set flag to TRUE
538 g_b_data_found := TRUE;
539 -- Process ends with a warning status
540 retcode := 1;
541 fnd_file.put_line(fnd_file.log,l_v_credit_number||' - '||l_rec_credits.credit_id || ': '||SQLERRM);
542 fnd_file.new_line(fnd_file.log);
543 ROLLBACK TO sp_credits;
544 END;
545
546 END LOOP; -- End of looping across Credits in IGS_FI_CREDITS table (Step 6)
547
548 -- Step 9: If there are no records, log 'No Data Found' in the log file.
549 -- This message is logged in 2 cases: (a) No data found for transferring to interface table
550 -- (b) All the data transferred successfully without any error records
551
552 IF (NOT g_b_data_found) THEN
553 fnd_message.set_name('IGS','IGS_GE_NO_DATA_FOUND');
554 fnd_file.put_line(fnd_file.log, fnd_message.get);
555 fnd_file.put_line(fnd_file.log,' ');
556 RETURN;
557 END IF;
558
559 -- Step 10: Commit the transactions
560 COMMIT;
561
562 EXCEPTION
563 WHEN e_resource_busy THEN
564 fnd_message.set_name('IGS','IGS_FI_RFND_REC_LOCK');
565 fnd_file.put_line(fnd_file.log,fnd_message.get());
566 fnd_file.new_line(fnd_file.log);
567 retcode := 2;
568
569 WHEN OTHERS THEN
570 ROLLBACK;
571 retcode := 2;
572 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION'||' - '||SQLERRM);
573 igs_ge_msg_stack.add;
574 igs_ge_msg_stack.conc_exception_hndl;
575
576 END transfer;
577
578 END igs_fi_com_rec_interface;