1 PACKAGE BODY igs_fi_posting_process AS
2 /* $Header: IGSFI59B.pls 120.3 2006/05/12 00:08:02 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 spec for package: IGS_FI_POSTING_PROCESS |
11 | |
12 | NOTES |
13 | This is a batch process that collects all eligible transactions |
14 | for posting purposes from charges lines, credit activities and |
15 | application tables. The output is inserted into the |
16 | IGS_FI_Posting_INT. IF Oracle AR is installed then the data gets |
17 | transfered to the same. |
18 | |
19 | HISTORY |
20 | Who When What |
21 | abshriva 12-May-2006 Enh#5217319 Precision Issue. Modified |
22 | transfer_credit_act_txns, |
23 | transfer_appl_txns, transfer_chgs_txns|
24 | and transfer_ad_appl_fee_txns |
25 | abshriva 5-May-2006 Bug5178077 Modification made in |
26 | posting_interface |
27 | svuppala 30-MAY-2005 Enh 3442712 - Done the TBH |
28 | modifications by adding new columns |
29 | Unit_Type_Id, Unit_Level in |
30 | igs_fi_invln_int_all |
31 | |
32 | vvutukur 09-Oct-03 Bug#3160036. Modified procedure |
33 | transfer_ad_appl_fee_txns. |
34 | pathipat 14-Jun-2003 Enh 2831587 - CC Fund Transfer build |
35 | Modified transfer_ad_appl_fee_txns() |
36 | pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables |
37 | Stubbed transfer_posting. |
38 | Removed proc get_customer_details. |
39 | Modified posting_interface(). |
40 | shtatiko 09-DEC-2002 Modified cursor in transfer_credit_act_txns|
41 | procedure. ( Bug# 2584741, Deposits) |
42 | SYKRISHN 05-NOV/2002 Change posting_interface procedure as |
43 | as per GL Interface TD. 2584986 |
44 | vchappid 16-Jul-2002 Bug#2464172, procedure in body are |
45 | made in sync with the Package Spec |
46 | agairola 04 May 2002 Added the function get_log_line |
47 | Modified the log file for the Transfer|
48 | posting transactions to AR process |
49 | Also removed the references for the |
50 | g_err_party and added the global var |
51 | g_party for the party id |
52 | agairola 21 Apr 2002 Changed the variable g_interface_attr |
53 | to data type igs_fi_control.interface |
54 | line attribute.Added bug no. to header|
55 | for bugs 2326595, 2309929, 2310806 |
56 | agairola 17-APR 2002 Added a new function get_int_val. |
57 | Modified the coding logic for the |
58 | INTERFACE_LINE_ATTRIBUTE11. Modified |
59 | the log file display of the Posting |
60 | Process. |
61 | for bugs 2326595, 2309929, 2310806 |
62 | agairola 12-Apr-2002 Made the width of the CCID displayed |
63 | to 233. Incase of error displayed
64 | party id.Modified the get_customer_details
65 | procedure to return customer account
66 | number. Added the function get_party_num
67 | for bugs 2326595, 2309929, 2310806 |
68 | agairola 11-Apr-2002 Used the comments for the description field
69 | while populating the data in the
70 | RA_INTERFACE_LINES_ALL table in case
71 | the description is null
72 | for bugs 2326595, 2309929, 2310806 |
73 | agairola 10-Apr-2002 Modified the code for displaying the |
74 | Accounting Flex fields. Removed the |
75 | redundant code and added the procedure|
76 | s for updation of the log file. |
77 | for bugs 2326595, 2309929, 2310806 |
78 |
79 | sarakshi 28-Feb-2002 bug:2238362,For message logging modified
80 | person_ref to party_ref lookup |
81 | jbegum 25 Feb 02 As part of Enh bug # 2238226 |
82 | Modified the local procedure |
83 | get_customer_details to derive |
84 | customer account details for the local|
85 | institution . |
86 | In the procedure transfer_posting |
87 | added code to copy value of the field |
88 | orig_appl_fee_ref to comments field of|
89 | RA_INTERFACE_LINES_ALL when |
90 | source_transaction_type is APPLFEE |
91 | Also added column orig_appl_fee_ref to|
92 | the IGS_FI_POSTING_INT_PKG.insert_row,|
93 | IGS_FI_POSTING_INT_PKG.update_row |
94 | agairola 22-Feb-2002 Added the modifications related to |
95 | Customer Account and also calling the |
96 | customer account creation procedure |
97 |
98 | agairola 11-Apr-2002 Used the value of comments for description in case it is null
99 | agairola 10-Apr-2002 Code fixes done as part of bug 2309929
100 | Added the procedures for updating the log file
101 | Code fixes done as part of bug 2310806
102 | Added the brackets for the Batch Name in Cursor cur_postings
103 | Added the code for locking the records
104 | jbegum 25 Feb 02 As part of Enh bug #2238226
105 | Modified the local procedure get_customer_details
106 | to derive customer account details for the local institution
107 | In the procedure transfer_posting added code to copy value of the field
108 | orig_appl_fee_ref to comments field of RA_INTERFACE_LINES_ALL when
109 | source_transaction_type is APPLFEE.Also added column orig_appl_fee_ref to
110 | the IGS_FI_POSTING_INT_PKG.insert_row ,IGS_FI_POSTING_INT_PKG.update_row
111 | jbegum 20 Feb 02 As part of Enh bug #2228910
112 Removed the source_transaction_id column from the IGS_FI_INVLN_INT_PKG.update_row
113 jbegum 16 Feb 02 As part of Enh bug #2222272
114 Set org id of transactions created
115 in the Receivables Invoice Interface tables
116 to the org id value obtained from control table
117
118 Sykrishn 18-FEB-2002 Changes as per build SFCR023 - 2227831
119 *=======================================================================*/
120 -- Declare all Global variables and global constants
121 g_cash CONSTANT VARCHAR2(20) := 'CASH';
122 g_accrual CONSTANT VARCHAR2(20) := 'ACCRUAL';
123 g_credit CONSTANT VARCHAR2(20) := 'CREDIT';
124 g_charge CONSTANT VARCHAR2(20) := 'CHARGE';
125 g_application CONSTANT VARCHAR2(20) := 'APPLICATION';
126 g_todo CONSTANT VARCHAR2(20) := 'TODO';
127 g_transferred CONSTANT VARCHAR2(20) := 'TRANSFERRED';
128 g_error CONSTANT VARCHAR2(20) := 'ERROR';
129
130
131 -- jbegum 25 Feb 02 As part of enh bug #2238226 added the following global constant
132 g_applfee CONSTANT VARCHAR2(20) := 'APPLFEE';
133
134 -- Global variable for receivables installed flag setting
135 g_rec_installed igs_fi_control.rec_installed%TYPE;
136
137 g_party hz_parties.party_id%TYPE;
138
139 g_interface_attr igs_fi_control.interface_line_attribute%TYPE;
140
141 -- Global Variable for posting control id
142 g_n_posting_control_id igs_fi_posting_int_all.posting_control_id%TYPE;
143
144 -- For printing total records processed at end of log file.
145 g_n_rec_processed NUMBER := 0;
146
147 PROCEDURE derive_comments (
148 p_transaction_id IN igs_fi_posting_int_all.source_transaction_id%TYPE,
149 p_transaction_type IN igs_fi_posting_int_all.source_transaction_type%TYPE,
150 p_transaction_number OUT NOCOPY VARCHAR2,
151 p_comments OUT NOCOPY ra_interface_lines_all.comments%TYPE
152 );
153
154 FUNCTION lookup_desc( l_type in igs_lookup_values.lookup_type%TYPE , l_code in igs_lookup_values.lookup_code%TYPE )RETURN VARCHAR2 IS
155 /******************************************************************
156 Created By : sykrishn
157 Date Created By : 18-FEB-2002
158 Purpose : Local Function Returns the meaning for the given lookup code
159
160 Known limitations,
161 enhancements,
162 remarks :
163 Change History
164 Who When What
165 ******************************************************************/
166
167 CURSOR cur_desc( x_type igs_lookups_view.lookup_type%type,
168 x_code igs_lookups_view.lookup_code%type )
169 IS
170 SELECT meaning
171 FROM igs_lookup_values
172 WHERE lookup_code = x_code
173 AND lookup_type = x_type ;
174
175 l_desc igs_lookup_values.meaning%type ;
176 BEGIN
177 IF l_code is null then
178 return null ;
179 ELSE
180 open cur_desc(l_type,l_code);
181 fetch cur_desc into l_desc ;
182 close cur_desc ;
183 END IF ;
184 RETURN l_desc ;
185 END lookup_desc;
186
187 FUNCTION get_party_num(p_party_id IN NUMBER) RETURN VARCHAR2 AS
188 /******************************************************************
189 Created By : agairola
190 Date Created By : 12-Apr-2002
191 Purpose : Local function for getting the party number
192
193 Known limitations,
194 enhancements,
195 remarks :
196 Change History
197 Who When What
198 ******************************************************************/
199 CURSOR cur_party(cp_party_id NUMBER) IS
200 SELECT party_number
201 FROM hz_parties
202 WHERE party_id = cp_party_id;
203
204 l_party_num hz_parties.party_number%TYPE;
205 BEGIN
206 IF p_party_id IS NULL THEN
207 l_party_num := NULL;
208 ELSE
209 OPEN cur_party(p_party_id);
210 FETCH cur_party INTO l_party_num;
211 IF cur_party%NOTFOUND THEN
212 l_party_num := NULL;
213 END IF;
214 CLOSE cur_party;
215 END IF;
216
217 RETURN l_party_num;
218 END get_party_num;
219 PROCEDURE update_log_norec(p_flag IN BOOLEAN,
220 p_trx_type IN VARCHAR2) AS
221 /******************************************************************
222 Created By : agairola
223 Date Created By : 10-Apr-2002
224 Purpose : Local function for updating the log file incase
225 no records are found
226
227 Known limitations,
228 enhancements,
229 remarks :
230 Change History
231 Who When What
232 ******************************************************************/
233 BEGIN
234 IF ((p_flag IS NULL) OR
235 (p_trx_type IS NULL)) THEN
236 RETURN;
237 END IF;
238
239 IF NOT p_flag THEN
240 fnd_message.set_name('IGS',
241 'IGS_FI_NO_TRX_PROCESSED');
242 fnd_message.set_token('TRX_TYPE',
243 lookup_desc('IGS_FI_SOURCE_TRANSACTION_TYPE',
244 p_trx_type));
245 fnd_file.put_line(fnd_file.log,
246 fnd_message.get);
247 END IF;
248 END update_log_norec;
249
250 PROCEDURE update_log_file(p_txn_date IN igs_fi_posting_int.transaction_date%TYPE,
251 p_amount IN igs_fi_posting_int.amount%TYPE,
252 p_txn_id IN igs_fi_posting_int.source_transaction_id%TYPE,
253 p_dr_acc_code IN igs_fi_posting_int.dr_account_cd%TYPE,
254 p_cr_acc_code IN igs_fi_posting_int.cr_account_cd%TYPE,
255 p_src_txn_type IN igs_fi_posting_int.source_transaction_type%TYPE) AS
256
257 /******************************************************************
258 Created By : agairola
259 Date Created By : 10-Apr-2002
260 Purpose : Local function for updating the log file with actual data
261
262 Known limitations,
263 enhancements,
264 remarks :
265 Change History
266 Who When What
267 SYKRISHN 01-NOV-2002 Modifications for Gl Interface Build
268 agairola 17-Apr-2002 Modified the Log file display to be in Single Line instead of Tabular
269 for bugs 2326595, 2309929, 2310806
270 ******************************************************************/
271
272 l_v_txn_num VARCHAR2(240);
273 l_v_comments ra_interface_lines_all.comments%TYPE;
274
275 l_dr_account igs_lookups_view.meaning%TYPE;
276 l_cr_account igs_lookups_view.meaning%TYPE;
277 BEGIN
278
279 -- Log the Source Transaction Type in one single line
280 fnd_file.put_line(fnd_file.log,' '||lookup_desc('IGS_FI_LOCKBOX','SOURCE_TRAN_TYPE')||' : '||
281 lookup_desc('IGS_FI_SOURCE_TRANSACTION_TYPE',p_src_txn_type));
282
283 -- Call the procedure for deriving the comments tand the Transaction Number
284 -- This is a common procedure which is also used in the Transfer Posting process
285 derive_comments(p_transaction_id => p_txn_id,
286 p_transaction_type => p_src_txn_type,
287 p_transaction_number => l_v_txn_num,
288 p_comments => l_v_comments);
289
290 -- Log the Transaction Number
291 fnd_file.put_line(fnd_file.log,
292 ' '||lookup_desc('IGS_FI_LOCKBOX','TRANSACTION_NUM')||' : '||l_v_txn_num);
293 fnd_file.put_line(fnd_file.log,
294 ' '||lookup_desc('IGS_FI_LOCKBOX','TRANSACTION_DATE')||' : '||to_char(p_txn_date));
295 fnd_file.put_line(fnd_file.log,
296 ' '||lookup_desc('IGS_FI_LOCKBOX','AMOUNT')||' : '||to_char(p_amount));
297
298
299 l_dr_account := lookup_desc('IGS_FI_LOCKBOX','DR_ACCOUNT');
300 l_cr_account := lookup_desc('IGS_FI_LOCKBOX','CR_ACCOUNT');
301
302 fnd_file.put_line(fnd_file.log,
303 ' '||l_dr_account||' : '||p_dr_acc_code);
304 fnd_file.put_line(fnd_file.log,
305 ' '||l_cr_account||' : '||p_cr_acc_code);
306 -- Put a new line
307
308 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
309 fnd_file.new_line(fnd_file.log);
310 END update_log_file;
311
312 PROCEDURE transfer_credit_act_txns(
313 p_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
314 p_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE,
315 p_d_gl_posted_date IN igs_fi_posting_int_all.accounting_date%TYPE
316 ) AS
317 /*
318 || Created By : brajendr
319 || Created On : 24-Apr-2001
320 || Purpose :
321 || Known limitations, enhancements or remarks :
322 || Change History :
323 || Who When What
324 || abshriva 12-May-2006 Enh#5217319 Precision Issue. Amount values being inserted into igs_fi_posting_int
325 || is now rounded off to currency precision
326 || shtatiko 09-Dec-2002 Modified to cursor to include conditions for dr_account_cd and cr_account_cd.
327 || ( BUG# 2584741 )
328 || Sykrishn 01-NOV/2002 Gl Interface TD modifications...
329 || The below history is nt valid as the local procedure is revamped completely (including the proc name)
330 || agairola 21 Apr 2002 Initialised the lrec_posting_int
331 || for bugs 2326595, 2309929, 2310806
332 || agairola 10-Apr-2002 Added the code for the printing of message in case of
333 || no records being found and also for the common procedure
334 || for logging messages for bugs 2326595, 2309929, 2310806
335 || jbegum 25 Feb 02 As part of Enh bug # 2238226
336 || Added column orig_appl_fee_ref to the
337 || IGS_FI_POSTING_INT_PKG.insert_row
338 || (reverse chronological order - newest change first)
339 */
340
341 -- Get all the credit activity records, where GL_DATE lies b/w the passed date ranges that are yet to be posted.
342
343 -- Cursor is modified to include the check of credit and debit account codes
344 -- by shtatiko as part of Enh Bug# 2584741.
345 CURSOR cur_credit_activities(cp_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
346 cp_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE ) IS
347
348 SELECT crac.rowid row_id, crac.*
349 FROM igs_fi_cr_activities crac
350 WHERE crac.gl_date IS NOT NULL
351 AND TRUNC(crac.gl_date) BETWEEN TRUNC(cp_d_gl_date_start) AND TRUNC(cp_d_gl_date_end)
352 AND crac.posting_id IS NULL
353 AND crac.posting_control_id IS NULL
354 AND crac.dr_account_cd IS NOT NULL
355 AND crac.cr_account_cd IS NOT NULL
356 ORDER BY gl_date
357 FOR UPDATE OF gl_posted_date NOWAIT;
358
359
360 -- Get the Currency Code from the Credit table with the given credit id.
361 CURSOR cur_credit (cp_credit_id igs_fi_cr_activities.credit_id%TYPE ) IS
362 SELECT currency_cd
363 FROM igs_fi_credits
364 WHERE credit_id = cp_credit_id;
365
366 l_b_exception_flag BOOLEAN := FALSE;
367
368 l_v_currency_cd igs_fi_credits.currency_cd%TYPE;
369 l_v_cr_account_cd igs_fi_cr_activities.cr_account_cd%TYPE;
370 l_v_dr_account_cd igs_fi_cr_activities.dr_account_cd%TYPE;
371 l_n_amount igs_fi_cr_activities.amount%TYPE;
372
373 l_v_posting_rowid ROWID;
374 l_n_posting_id igs_fi_posting_int.posting_id%TYPE;
375
376 BEGIN
377
378 FOR cr_act_rec IN cur_credit_activities( p_d_gl_date_start, p_d_gl_date_end) LOOP
379 -- Looping through each of these records selected , check if the CR_ACT_REC.AMOUNT fetched is negative.
380 --If negative, then make the AMOUNT positive (Eg. -50 to 50) and swap the values of debit and credit account codes.
381 --(Value of DR_ACCOUNT_CD to Value of CR_ACCOUNT_CD).
382
383 IF cr_act_rec.amount < 0 THEN
384 --Make amount +ve
385 l_n_amount := ((-1) * cr_act_rec.amount);
386 -- Swapping
387 l_v_cr_account_cd := cr_act_rec.dr_account_cd;
388 l_v_dr_account_cd := cr_act_rec.cr_account_cd;
389 ELSE
390 l_n_amount := cr_act_rec.amount;
391 l_v_cr_account_cd := cr_act_rec.cr_account_cd;
392 l_v_dr_account_cd := cr_act_rec.dr_account_cd;
393 END IF;
394
395 -- get the currency code from the credits table for credit_id
396 OPEN cur_credit (cr_act_rec.credit_id);
397 FETCH cur_credit INTO l_v_currency_cd;
398 CLOSE cur_credit;
399
400 -- Insert into the posting int table wiht the selected transaction
401
402 l_v_posting_rowid := NULL;
403 l_n_posting_id := NULL;
404
405 BEGIN
406 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
407 igs_fi_posting_int_pkg.insert_row (
408 x_rowid => l_v_posting_rowid,
409 x_posting_control_id => g_n_posting_control_id,
410 x_posting_id => l_n_posting_id,
411 x_batch_name => NULL,
412 x_accounting_date => cr_act_rec.gl_date,
413 x_transaction_date => cr_act_rec.transaction_date,
414 x_currency_cd => l_v_currency_cd,
415 x_dr_account_cd => l_v_dr_account_cd,
416 x_cr_account_cd => l_v_cr_account_cd,
417 x_dr_gl_code_ccid => NULL,
418 x_cr_gl_code_ccid => NULL,
419 x_amount => igs_fi_gen_gl.get_formatted_amount(l_n_amount),
420 x_source_transaction_id => cr_act_rec.credit_activity_id,
421 x_source_transaction_type => g_credit,
422 x_status => g_todo,
423 x_orig_appl_fee_ref => NULL,
424 x_mode => 'R'
425 );
426 EXCEPTION
427 WHEN OTHERS THEN
428 l_b_exception_flag := TRUE;
429 END;
430
431
432 -- Update the credit activities table - posting_control_id and the log file
433
434 IF NOT l_b_exception_flag THEN
435
436 update_log_file
437 (p_txn_date => cr_act_rec.transaction_date,
438 p_amount => l_n_amount,
439 p_txn_id => cr_act_rec.credit_activity_id,
440 p_dr_acc_code => l_v_dr_account_cd,
441 p_cr_acc_code => l_v_cr_account_cd,
442 p_src_txn_type => g_credit);
443
444 BEGIN
445 igs_fi_cr_activities_pkg.update_row(
446 x_rowid => cr_act_rec.row_id,
447 x_credit_activity_id => cr_act_rec.credit_activity_id,
448 x_credit_id => cr_act_rec.credit_id,
449 x_status => cr_act_rec.status,
450 x_transaction_date => cr_act_rec.transaction_date,
451 x_amount => cr_act_rec.amount,
452 x_dr_account_cd => cr_act_rec.dr_account_cd,
453 x_cr_account_cd => cr_act_rec.cr_account_cd,
454 x_dr_gl_ccid => cr_act_rec.dr_gl_ccid,
455 x_cr_gl_ccid => cr_act_rec.cr_gl_ccid,
456 x_bill_id => cr_act_rec.bill_id,
457 x_bill_number => cr_act_rec.bill_number,
458 x_bill_date => cr_act_rec.bill_date,
459 x_posting_id => l_n_posting_id,
460 x_posting_control_id => g_n_posting_control_id,
461 x_gl_date => cr_act_rec.gl_date,
462 x_gl_posted_date => p_d_gl_posted_date,
463 x_mode => 'R'
464 );
465 END;
466 -- Total Records Processed Counter.....
467 g_n_rec_processed := g_n_rec_processed + 1;
468 END IF;
469
470 -- Reseting flag if exception has occured for previous_record.
471 l_b_exception_flag := FALSE;
472
473 END LOOP;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXP');
478 --Unhandled Exception Raised in Procedure NAME
479 fnd_message.set_token('NAME','IGS_FI_POSTING_INTERFACE.TRANSFER_CREDIT_ACT_TXNS');
480 fnd_file.put_line( fnd_file.log, fnd_message.get() || sqlerrm);
481 app_exception.raise_exception;
482 END transfer_credit_act_txns;
483
484
485 PROCEDURE transfer_appl_txns(
486 p_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
487 p_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE,
488 p_d_gl_posted_date IN igs_fi_posting_int_all.accounting_date%TYPE
489 ) AS
490 /*
491 || Created By : brajendr
492 || Created On : 24-Apr-2001
493 || Purpose :
494 || Known limitations, enhancements or remarks :
495 || Change History :
496 || Who When What
497 || abshriva 12-May-2006 Enh#5217319 Precision Issue. Amount values being inserted into igs_fi_posting_int
498 || is now rounded off to currency precision
499 || Sykrishn 01-NOV/2002 Gl Interface TD modifications...
500 || The below history is nt valid as the local procedure is revamped completely
501 || agairola 21 Apr 2002 Initialised the lrec_posting_int
502 || for bugs 2326595, 2309929, 2310806
503 || agairola 10-Apr-2002 Added the code for the printing of message in case of
504 || no records being found and also for the common procedure
505 || for logging messages for bugs 2326595, 2309929, 2310806
506 || jbegum 25 Feb 02 As part of Enh bug # 2238226
507 || Added column orig_appl_fee_ref to the
508 || IGS_FI_POSTING_INT_PKG.insert_row
509 || (reverse chronological order - newest change first)
510 */
511
512 -- Get all the credit activity records, where GL_DATE lies b/w the passedg date ranges that have not been posted.
513
514 CURSOR cur_appl (cp_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
515 cp_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE ) IS
516
517 SELECT appl.rowid row_id, appl.*
518 FROM igs_fi_applications appl
519 WHERE appl.gl_date IS NOT NULL
520 AND TRUNC(appl.gl_date) BETWEEN TRUNC(cp_d_gl_date_start) AND TRUNC(cp_d_gl_date_end)
521 AND appl.posting_id IS NULL
522 AND appl.posting_control_id IS NULL
523 ORDER BY gl_date
524 FOR UPDATE OF gl_posted_date NOWAIT;
525
526
527 -- Get the details like Effective date and Currency Code from the Credit table with the given credit id.
528 CURSOR cur_credit (cp_credit_id igs_fi_applications.credit_id%TYPE ) IS
529 SELECT currency_cd
530 FROM igs_fi_credits
531 WHERE credit_id = cp_credit_id;
532
533
534 l_b_exception_flag BOOLEAN := FALSE;
535
536 l_v_cr_account_cd igs_fi_applications.cr_account_cd%TYPE;
537 l_v_dr_account_cd igs_fi_applications.dr_account_cd%TYPE;
538 l_n_amount igs_fi_applications.amount_applied%TYPE;
539 l_v_posting_rowid ROWID;
540 l_n_posting_id igs_fi_posting_int.posting_id%TYPE;
541 l_v_currency_cd igs_fi_credits.currency_cd%TYPE;
542
543 BEGIN
544
545
546 FOR app_rec IN cur_appl( p_d_gl_date_start, p_d_gl_date_end) LOOP
547 -- Looping through each of these records selected , check if the AMOUNT_APPLIED fetched is negative.
548 --If negative, then make the AMOUNT positive (Eg. -50 to 50) and swap the values of debit and credit account codes.
549 --(Value of DR_ACCOUNT_CD to Value of CR_ACCOUNT_CD).
550
551 IF app_rec.amount_applied < 0 THEN
552 l_n_amount := ((-1) * app_rec.amount_applied);
553 -- Swapping
554 l_v_cr_account_cd := app_rec.dr_account_cd;
555 l_v_dr_account_cd := app_rec.cr_account_cd;
556 ELSE
557 l_n_amount := app_rec.amount_applied;
558 l_v_cr_account_cd := app_rec.cr_account_cd;
559 l_v_dr_account_cd := app_rec.dr_account_cd;
560 END IF;
561
562 -- get the currency code from the credits table
563 OPEN cur_credit (app_rec.credit_id);
564 FETCH cur_credit INTO l_v_currency_cd;
565 CLOSE cur_credit;
566
567 -- Insert into the posting int table wiht the selected transaction
568 l_v_posting_rowid := NULL;
569 l_n_posting_id := NULL;
570 BEGIN
571 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
572 igs_fi_posting_int_pkg.insert_row (
573 x_rowid => l_v_posting_rowid,
574 x_posting_control_id => g_n_posting_control_id,
575 x_posting_id => l_n_posting_id,
576 x_batch_name => NULL,
577 x_accounting_date => app_rec.gl_date,
578 x_transaction_date => app_rec.apply_date,
579 x_currency_cd => l_v_currency_cd,
580 x_dr_account_cd => l_v_dr_account_cd,
581 x_cr_account_cd => l_v_cr_account_cd,
582 x_dr_gl_code_ccid => NULL,
583 x_cr_gl_code_ccid => NULL,
584 x_amount => igs_fi_gen_gl.get_formatted_amount(l_n_amount),
585 x_source_transaction_id => app_rec.application_id,
586 x_source_transaction_type => g_application,
587 x_status => g_todo,
588 x_orig_appl_fee_ref => NULL,
589 x_mode => 'R'
590 );
591 EXCEPTION
592 WHEN OTHERS THEN
593 l_b_exception_flag := TRUE;
594 END;
595
596
597 -- Update the Applications table - posting_control_id and the log file
598
599 IF NOT l_b_exception_flag THEN
600
601 update_log_file
602 (p_txn_date => app_rec.apply_date,
603 p_amount => l_n_amount,
604 p_txn_id => app_rec.application_id,
605 p_dr_acc_code => l_v_dr_account_cd,
606 p_cr_acc_code => l_v_cr_account_cd,
607 p_src_txn_type => g_application);
608
609 BEGIN
610 igs_fi_applications_pkg.update_row(
611 x_rowid => app_rec.row_id,
612 x_application_id => app_rec.application_id,
613 x_application_type => app_rec.application_type,
614 x_invoice_id => app_rec.invoice_id,
615 x_credit_id => app_rec.credit_id,
616 x_credit_activity_id => app_rec.credit_activity_id,
617 x_amount_applied => app_rec.amount_applied,
618 x_apply_date => app_rec.apply_date,
619 x_link_application_id => app_rec.link_application_id,
620 x_dr_account_cd => app_rec.dr_account_cd,
621 x_cr_account_cd => app_rec.cr_account_cd,
622 x_dr_gl_code_ccid => app_rec.dr_gl_code_ccid,
623 x_cr_gl_code_ccid => app_rec.cr_gl_code_ccid,
624 x_applied_invoice_lines_id => app_rec.applied_invoice_lines_id,
625 x_appl_hierarchy_id => app_rec.appl_hierarchy_id,
626 x_posting_id => l_n_posting_id,
627 x_posting_control_id => g_n_posting_control_id,
628 x_gl_date => app_rec.gl_date,
629 x_gl_posted_date => p_d_gl_posted_date,
630 x_mode => 'R'
631 );
632 END;
633 -- Total Records Processed Counter.....
634 g_n_rec_processed := g_n_rec_processed + 1;
635 END IF;
636
637 -- Reseting flag if exception has occured for previous_record.
638 l_b_exception_flag := FALSE;
639 END LOOP;
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXP');
644 --Unhandled Exception Raised in Procedure NAME
645 fnd_message.set_token('NAME','IGS_FI_POSTING_INTERFACE.TRANSFER_APPL_TXNS');
646 fnd_file.put_line( fnd_file.log, fnd_message.get() || sqlerrm);
647 app_exception.raise_exception;
648
649 END transfer_appl_txns;
650
651
652 PROCEDURE transfer_chgs_txns(
653 p_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
654 p_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE,
655 p_d_gl_posted_date IN igs_fi_posting_int_all.accounting_date%TYPE
656 ) AS
657 /*
658 || Created By : brajendr
659 || Created On : 24-Apr-2001
660 || Purpose :
661 || Known limitations, enhancements or remarks :
662 || Change History :
663 || Who When What
664 || abshriva 12-May-2006 Enh#5217319 Precision Issue. Amount values being inserted into igs_fi_posting_int
665 || and igs_fi_invln_int is now rounded off to currency precision
666 || svuppala 30-MAY-2005 Enh 3442712 - Done the TBH modifications by adding
667 || new columns Unit_Type_Id, Unit_Level in igs_fi_invln_int_all
668 || Sykrishn 01-NOV/2002 Gl Interface TD modifications...
669 || The below history is nt valid as the local procedure is revamped completely
670 || agairola 21 Apr 2002 Initialised the lrec_posting_int
671 || for bugs 2326595, 2309929, 2310806
672 || agairola 10-Apr-2002 Added the code for the printing of message in case of
673 || no records being found and also for the common procedure
674 || for logging messages for bugs 2326595, 2309929, 2310806
675 || jbegum 25 Feb 02 As part of Enh bug # 2238226
676 || Added column orig_appl_fee_ref to the
677 || IGS_FI_POSTING_INT_PKG.insert_row
678 || (reverse chronological order - newest change first)
679 */
680
681 -- Get all the invoice lines records, where GL_DATE lies b/w the passedg date ranges that have not been posted.
682
683 CURSOR cur_inv (cp_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
684 cp_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE ) IS
685 SELECT invln.rowid row_id, inv.invoice_creation_date, inv.currency_cd, invln.*
686 FROM igs_fi_invln_int_all invln,
687 igs_fi_inv_int_all inv
688 WHERE invln.gl_date IS NOT NULL
689 AND TRUNC(invln.gl_date) BETWEEN TRUNC(cp_d_gl_date_start) AND TRUNC(cp_d_gl_date_end)
690 AND invln.posting_id IS NULL
691 AND invln.posting_control_id IS NULL
692 AND inv.invoice_id = invln.invoice_id
693 AND NVL(invln.error_account,'N') = 'N'
694 ORDER BY gl_date
695 FOR UPDATE OF gl_posted_date NOWAIT;
696
697
698
699 l_b_exception_flag BOOLEAN := FALSE;
700
701 l_v_cr_account_cd igs_fi_invln_int_all.rev_account_cd%TYPE;
702 l_v_dr_account_cd igs_fi_invln_int_all.rec_account_cd%TYPE;
703 l_n_amount igs_fi_invln_int_all.amount%TYPE;
704 l_v_posting_rowid ROWID;
705 l_n_posting_id igs_fi_posting_int.posting_id%TYPE;
706
707 BEGIN
708
709
710 FOR inv_rec IN cur_inv( p_d_gl_date_start, p_d_gl_date_end) LOOP
711 -- Looping through each of these records selected , check if the AMOUNT fetched is negative.
712 --If negative, then make the AMOUNT positive (Eg. -50 to 50) and swap the values of debit and credit account codes.
713 --(Value of REC_ACCOUNT_CD to Value of REV_ACCOUNT_CD).
714
715 -- NOTE: According to the present functionality this case would never occur. Code kept for future cases if any
716
717 IF inv_rec.amount < 0 THEN
718 l_n_amount := ((-1) * inv_rec.amount);
719 -- Swapping
720 l_v_cr_account_cd := inv_rec.rec_account_cd;
721 l_v_dr_account_cd := inv_rec.rev_account_cd;
722 ELSE
723 l_n_amount := inv_rec.amount;
724 l_v_cr_account_cd := inv_rec.rev_account_cd;
725 l_v_dr_account_cd := inv_rec.rec_account_cd;
726 END IF;
727 -- Insert into the posting int table , the selected transaction
728 l_v_posting_rowid := NULL;
729 l_n_posting_id := NULL;
730 BEGIN
731 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
732 igs_fi_posting_int_pkg.insert_row (
733 x_rowid => l_v_posting_rowid,
734 x_posting_control_id => g_n_posting_control_id,
735 x_posting_id => l_n_posting_id,
736 x_batch_name => NULL,
737 x_accounting_date => inv_rec.gl_date,
738 x_transaction_date => inv_rec.invoice_creation_date,
739 x_currency_cd => inv_rec.currency_cd,
740 x_dr_account_cd => l_v_dr_account_cd,
741 x_cr_account_cd => l_v_cr_account_cd,
742 x_dr_gl_code_ccid => NULL,
743 x_cr_gl_code_ccid => NULL,
744 x_amount => igs_fi_gen_gl.get_formatted_amount(l_n_amount),
745 x_source_transaction_id => inv_rec.invoice_lines_id,
746 x_source_transaction_type => g_charge,
747 x_status => g_todo,
748 x_orig_appl_fee_ref => NULL,
749 x_mode => 'R'
750 );
751 EXCEPTION
752 WHEN OTHERS THEN
753 l_b_exception_flag := TRUE;
754 END;
755
756 -- Update the Invoice Lines Table table - posting_control_id and also the log file
757
758 IF NOT l_b_exception_flag THEN
759
760 update_log_file
761 (p_txn_date => inv_rec.invoice_creation_date,
762 p_amount => l_n_amount,
763 p_txn_id => inv_rec.invoice_lines_id,
764 p_dr_acc_code => l_v_dr_account_cd,
765 p_cr_acc_code => l_v_cr_account_cd,
766 p_src_txn_type => g_charge);
767
768 BEGIN
769 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
770 igs_fi_invln_int_pkg.update_row(
771 x_rowid => inv_rec.row_id,
772 x_invoice_lines_id => inv_rec.invoice_lines_id,
773 x_invoice_id => inv_rec.invoice_id,
774 x_line_number => inv_rec.line_number,
775 x_s_chg_method_type => inv_rec.s_chg_method_type,
776 x_description => inv_rec.description,
777 x_chg_elements => inv_rec.chg_elements,
778 x_amount => igs_fi_gen_gl.get_formatted_amount(inv_rec.amount),
779 x_unit_attempt_status => inv_rec.unit_attempt_status,
780 x_eftsu => inv_rec.eftsu,
781 x_credit_points => inv_rec.credit_points,
782 x_attribute_category => inv_rec.attribute_category,
783 x_attribute1 => inv_rec.attribute1,
784 x_attribute2 => inv_rec.attribute2,
785 x_attribute3 => inv_rec.attribute3,
786 x_attribute4 => inv_rec.attribute4,
787 x_attribute5 => inv_rec.attribute5,
788 x_attribute6 => inv_rec.attribute6,
789 x_attribute7 => inv_rec.attribute7,
790 x_attribute8 => inv_rec.attribute8,
791 x_attribute9 => inv_rec.attribute9,
792 x_attribute10 => inv_rec.attribute10,
793 x_rec_account_cd => inv_rec.rec_account_cd,
794 x_rev_account_cd => inv_rec.rev_account_cd,
795 x_rec_gl_ccid => inv_rec.rec_gl_ccid,
796 x_rev_gl_ccid => inv_rec.rev_gl_ccid,
797 x_org_unit_cd => inv_rec.org_unit_cd,
798 x_posting_id => l_n_posting_id,
799 x_attribute11 => inv_rec.attribute11,
800 x_attribute12 => inv_rec.attribute12,
801 x_attribute13 => inv_rec.attribute13,
802 x_attribute14 => inv_rec.attribute14,
803 x_attribute15 => inv_rec.attribute15,
804 x_attribute16 => inv_rec.attribute16,
805 x_attribute17 => inv_rec.attribute17,
806 x_attribute18 => inv_rec.attribute18,
807 x_attribute19 => inv_rec.attribute19,
808 x_attribute20 => inv_rec.attribute20,
809 x_error_account => inv_rec.error_account,
810 x_error_string => inv_rec.error_string,
811 x_location_cd => inv_rec.location_cd,
812 x_uoo_id => inv_rec.uoo_id,
813 x_posting_control_id => g_n_posting_control_id,
814 x_gl_date => inv_rec.gl_date,
815 x_gl_posted_date => p_d_gl_posted_date,
816 x_mode => 'R',
817 x_unit_type_id => inv_rec.unit_type_id,
818 x_unit_level => inv_rec.unit_level
819 );
820 END;
821
822 -- Total Records Processed Counter.....
823 g_n_rec_processed := g_n_rec_processed + 1;
824 END IF;
825
826 -- Reseting flag if exception has occured for previous_record.
827 l_b_exception_flag := FALSE;
828
829 END LOOP;
830
831 EXCEPTION
832 WHEN OTHERS THEN
833 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXP');
834 --Unhandled Exception Raised in Procedure NAME
835 fnd_message.set_token('NAME','IGS_FI_POSTING_INTERFACE.TRANSFER_CHGS_TXNS');
836 fnd_file.put_line( fnd_file.log, fnd_message.get() || sqlerrm);
837 app_exception.raise_exception;
838 END transfer_chgs_txns;
839
840 PROCEDURE transfer_ad_appl_fee_txns(
841 p_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
842 p_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE,
843 p_d_gl_posted_date IN igs_fi_posting_int_all.accounting_date%TYPE
844 ) AS
845 /*
846 || Created By : SYKRISHN
847 || Created On : 01-NOV/2002
848 || Purpose :
849 || Known limitations, enhancements or remarks :
850 || Change History :
851 || Who When What
852 || abshriva 12-May-2006 Enh#5217319 Precision Issue. Amount values being inserted into igs_fi_posting_int
853 || is now rounded off to currency precision
854 || vvutukur 09-Oct-2003 Bug#3160036.Replaced call to igs_ad_app_req.update_row with
855 || call to igs_ad_gen_015.update_igs_ad_app_req.
856 || pathipat 14-Jun-2003 Enh 2831587 Credit Card Fund Transfer build
857 || Modified call to igs_ad_app_req_pkg.update_row()
858 || Sykrishn 01-NOV/2002 Created this procedure - as part of Gl Interface TD modifications (NEW)
859 || (reverse chronological order - newest change first)
860 */
861
862 -- Get all the non-posted admission application fee records, where GL_DATE lies b/w the passedg date ranges that have not been posted.
863 -- Only posting control id needs to be checked in this case
864
865
866
867 CURSOR cur_adm_fee (cp_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
868 cp_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE ) IS
869 SELECT adm.rowid row_id,adm.*
870 FROM igs_ad_app_req adm
871 WHERE adm.gl_date IS NOT NULL
872 AND TRUNC(adm.gl_date) BETWEEN TRUNC(cp_d_gl_date_start) AND TRUNC(cp_d_gl_date_end)
873 AND adm.posting_control_id IS NULL
874 ORDER BY gl_date
875 FOR UPDATE OF gl_posted_date NOWAIT;
876
877
878 -- Cursor to get admission application id
879 CURSOR cur_app_id (cp_person_id IN igs_fi_parties_v.person_id%TYPE,
880 cp_admission_appl_number IN igs_ad_app_req.admission_appl_number%TYPE) IS
881 SELECT application_id
882 FROM igs_ad_appl
883 WHERE person_id = cp_person_id
884 AND admission_appl_number = cp_admission_appl_number;
885
886 l_b_exception_flag BOOLEAN := FALSE;
887 l_v_posting_rowid ROWID;
888 l_n_posting_id igs_fi_posting_int.posting_id%TYPE;
889 l_v_currency_cd igs_fi_control_all.currency_cd%TYPE := igs_fi_gen_gl.finp_ss_get_cur;
890 l_v_orig_appl_fee_ref igs_fi_posting_int_all.orig_appl_fee_ref%TYPE;
891 l_n_application_id igs_ad_appl.application_id%TYPE;
892
893 BEGIN
894
895
896 FOR ad_app_rec IN cur_adm_fee( p_d_gl_date_start, p_d_gl_date_end) LOOP
897
898 -- Derive the _orig_appl_fee_ref as Admission Application ID : <Admission Application ID> Party Number : <Party Number >
899
900 OPEN cur_app_id (cp_person_id => ad_app_rec.person_id,
901 cp_admission_appl_number => ad_app_rec.admission_appl_number);
902 FETCH cur_app_id INTO l_n_application_id;
903 CLOSE cur_app_id;
904
905 l_v_orig_appl_fee_ref := lookup_desc('IGS_FI_SOURCE_TRANSACTION_TYPE',g_applfee)||' : '|| TO_CHAR(l_n_application_id)||' ; '||lookup_desc('IGS_FI_LOCKBOX','PARTY')||' : '||get_party_num(p_party_id => ad_app_rec.person_id);
906
907 -- Insert into the posting int table , the selected transaction
908 l_v_posting_rowid := NULL;
909 l_n_posting_id := NULL;
910 BEGIN
911 -- Call to igs_fi_gen_gl.get_formatted_amount formats fee_amount by rounding off to currency precision
912 igs_fi_posting_int_pkg.insert_row (
913 x_rowid => l_v_posting_rowid,
914 x_posting_control_id => g_n_posting_control_id,
915 x_posting_id => l_n_posting_id,
916 x_batch_name => NULL,
917 x_accounting_date => ad_app_rec.gl_date,
918 x_transaction_date => ad_app_rec.fee_date,
919 x_currency_cd => l_v_currency_cd,
920 x_dr_account_cd => ad_app_rec.cash_account_cd,
921 x_cr_account_cd => ad_app_rec.rev_account_cd,
922 x_dr_gl_code_ccid => NULL,
923 x_cr_gl_code_ccid => NULL,
924 x_amount => igs_fi_gen_gl.get_formatted_amount(ad_app_rec.fee_amount),
925 x_source_transaction_id => NULL,
926 x_source_transaction_type => g_applfee,
927 x_status => g_todo,
928 x_orig_appl_fee_ref => l_v_orig_appl_fee_ref,
929 x_mode => 'R'
930 );
931 EXCEPTION
932 WHEN OTHERS THEN
933 l_b_exception_flag := TRUE;
934 END;
935
936 -- Update the igs_ad_app_req table - posting_control_id and also the log file
937
938 IF NOT l_b_exception_flag THEN
939
940 update_log_file
941 (p_txn_date => ad_app_rec.fee_date,
942 p_amount => ad_app_rec.fee_amount,
943 p_txn_id => l_n_application_id,
944 p_dr_acc_code => ad_app_rec.cash_account_cd,
945 p_cr_acc_code => ad_app_rec.rev_account_cd,
946 p_src_txn_type => g_applfee);
947
948 BEGIN
949
950 igs_ad_gen_015.update_igs_ad_app_req(
951 p_rowid => ad_app_rec.row_id,
952 p_app_req_id => ad_app_rec.app_req_id,
953 p_person_id => ad_app_rec.person_id,
954 p_admission_appl_number => ad_app_rec.admission_appl_number,
955 p_applicant_fee_type => ad_app_rec.applicant_fee_type,
956 p_applicant_fee_status => ad_app_rec.applicant_fee_status,
957 p_fee_date => ad_app_rec.fee_date,
958 p_fee_payment_method => ad_app_rec.fee_payment_method,
959 p_fee_amount => ad_app_rec.fee_amount,
960 p_reference_num => ad_app_rec.reference_num,
961 p_credit_card_code => ad_app_rec.credit_card_code,
962 p_credit_card_holder_name => ad_app_rec.credit_card_holder_name,
963 p_credit_card_number => ad_app_rec.credit_card_number,
964 p_credit_card_expiration_date => ad_app_rec.credit_card_expiration_date,
965 p_rev_gl_ccid => ad_app_rec.rev_gl_ccid,
966 p_cash_gl_ccid => ad_app_rec.cash_gl_ccid,
967 p_rev_account_cd => ad_app_rec.rev_account_cd,
968 p_cash_account_cd => ad_app_rec.cash_account_cd,
969 p_posting_control_id => g_n_posting_control_id,
970 p_gl_date => ad_app_rec.gl_date,
971 p_gl_posted_date => p_d_gl_posted_date,
972 p_credit_card_tangible_cd => ad_app_rec.credit_card_tangible_cd,
973 p_credit_card_payee_cd => ad_app_rec.credit_card_payee_cd,
974 p_credit_card_status_code => ad_app_rec.credit_card_status_code,
975 p_mode => 'R'
976 );
977
978 END;
979 -- Total Records Processed Counter.....
980 g_n_rec_processed := g_n_rec_processed + 1;
981 END IF;
982
983 -- Reseting flag if exception has occured for previous_record.
984 l_b_exception_flag := FALSE;
985
986 END LOOP;
987
988 EXCEPTION
989 WHEN OTHERS THEN
990 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXP');
991 --Unhandled Exception Raised in Procedure NAME
992 fnd_message.set_token('NAME','IGS_FI_POSTING_INTERFACE.TRANSFER_AD_APPL_FEE_TXNS');
993 fnd_file.put_line( fnd_file.log, fnd_message.get() || sqlerrm);
994 app_exception.raise_exception;
995 END transfer_ad_appl_fee_txns;
996
997
998 PROCEDURE derive_comments (
999 p_transaction_id IN igs_fi_posting_int_all.source_transaction_id%TYPE,
1000 p_transaction_type IN igs_fi_posting_int_all.source_transaction_type%TYPE,
1001 p_transaction_number OUT NOCOPY VARCHAR2,
1002 p_comments OUT NOCOPY ra_interface_lines_all.comments%TYPE
1003 ) AS
1004 /*
1005 || Created By : sykrishn
1006 || Created On : 18-FEB-2002
1007 || Purpose : To Derive the comments for insert
1008 || Known limitations, enhancements or remarks :
1009 || Change History :
1010 || Who When What
1011 || agairola 21 Apr 2002 Added the new out NOCOPY parameter transaction number for the
1012 || transaction number derivation for bugs 2326595, 2309929,
1013 || 2310806
1014 || sykrishn 18-FEB-2002 As per build of SFCR023 - 2227831
1015 ||
1016 || (reverse chronological order - newest change first)
1017 */
1018
1019 --Cursor to fetch the invoice number for the passed invoice_id
1020 CURSOR cur_charge (cp_transaction_id IN igs_fi_posting_int_all.source_transaction_id%TYPE) IS
1021 SELECT inv.invoice_number
1022 FROM igs_fi_inv_int inv, igs_fi_invln_int invln
1023 WHERE invln.invoice_lines_id = cp_transaction_id
1024 AND inv.invoice_id = invln.invoice_id;
1025
1026 --Cursor to fetch the credit number for the passed credit_id
1027 CURSOR cur_credit(cp_transaction_id IN igs_fi_posting_int_all.source_transaction_id%TYPE) IS
1028 SELECT crd.credit_number
1029 FROM igs_fi_credits crd,
1030 igs_fi_cr_activities cra
1031 WHERE cra.credit_activity_id = cp_transaction_id
1032 AND crd.credit_id = cra.credit_id;
1033
1034 --Cursor to fetch the credit id and invoice id for the passed application id
1035 CURSOR cur_appl(cp_transaction_id IN igs_fi_posting_int_all.source_transaction_id%TYPE) IS
1036 SELECT crd.credit_number,
1037 inv.invoice_number
1038 FROM igs_fi_applications app,
1039 igs_fi_credits crd,
1040 igs_fi_inv_int inv
1041 WHERE application_id = cp_transaction_id
1042 AND app.credit_id = crd.credit_id
1043 AND app.invoice_id = inv.invoice_id;
1044
1045 l_v_comments ra_interface_lines_all.comments%TYPE := NULL;
1046 l_v_invoice_number igs_fi_inv_int.invoice_number%TYPE;
1047 l_v_credit_number igs_fi_credits.credit_number%TYPE;
1048
1049 BEGIN
1050 -- Setup the comments with the passes transaction types meaning and colon :
1051 l_v_comments := lookup_desc('IGS_FI_SOURCE_TRANSACTION_TYPE',p_transaction_type)||':';
1052
1053 IF p_transaction_type = g_charge THEN
1054 OPEN cur_charge (p_transaction_id);
1055 FETCH cur_charge INTO l_v_invoice_number;
1056 CLOSE cur_charge;
1057 l_v_comments := l_v_comments || l_v_invoice_number;
1058 p_transaction_number := l_v_invoice_number;
1059 --A sample comments will look like 'Charge: Charge Number1
1060 ELSIF p_transaction_type = g_credit THEN
1061 OPEN cur_credit (p_transaction_id);
1062 FETCH cur_credit INTO l_v_credit_number;
1063 CLOSE cur_credit;
1064 l_v_comments := l_v_comments || l_v_credit_number;
1065 p_transaction_number := l_v_credit_number;
1066 --A sample comments will look like 'Credit: Credit Number1'
1067
1068 ELSIF p_transaction_type = g_applfee THEN
1069 p_transaction_number := TO_CHAR(p_transaction_id);
1070 -- Application ID would be the Transaction Number when APPLFEE
1071
1072 ELSIF p_transaction_type = g_application THEN
1073 OPEN cur_appl(p_transaction_id);
1074 FETCH cur_appl INTO l_v_credit_number,
1075 l_v_invoice_number;
1076 CLOSE cur_appl;
1077
1078 p_transaction_number := l_v_credit_number||'-'||l_v_invoice_number;
1079 -- The comments variable is setup a sample will look like
1080 -- Application:Credit <credit number>-Charge <Charge Number>
1081 l_v_comments := l_v_comments||lookup_desc('IGS_FI_SOURCE_TRANSACTION_TYPE',g_credit)||' '||l_v_credit_number||'-'||
1082 lookup_desc('IGS_FI_SOURCE_TRANSACTION_TYPE',g_charge)||' '|| l_v_invoice_number;
1083
1084 END IF;
1085 -- Since the comments column in the table ra_interface_lines is only 240 - We need to substring it to 240.
1086 p_comments := substr(l_v_comments,1,240);
1087 END derive_comments;
1088
1089 FUNCTION get_log_line(p_lookup_code igs_lookups_view.lookup_code%TYPE,
1090 p_value VARCHAR2) RETURN VARCHAR2 AS
1091 /******************************************************************
1092 Created By : agairola
1093 Date Created By : 04-May-2002
1094 Purpose : Local function for comparing and getting the value for log file
1095
1096 Known limitations,
1097 enhancements,
1098 remarks :
1099 Change History
1100 Who When What
1101 ******************************************************************/
1102
1103 l_data VARCHAR2(2000);
1104
1105 BEGIN
1106 l_data := lookup_desc('IGS_FI_LOCKBOX',
1107 p_lookup_code)||' : '||p_value;
1108 RETURN l_data;
1109 END get_log_line;
1110
1111 FUNCTION get_int_val(p_column_name VARCHAR2,
1112 p_column_value VARCHAR2) RETURN VARCHAR2 AS
1113 /******************************************************************
1114 Created By : agairola
1115 Date Created By : 17-Apr-2002
1116 Purpose : Local function for comparing and getting the value for the
1117 Interface Line Attribute
1118
1119 Known limitations,
1120 enhancements,
1121 remarks :
1122 Change History
1123 Who When What
1124 ******************************************************************/
1125
1126 l_ret_val VARCHAR2(20);
1127 BEGIN
1128 l_ret_val := NULL;
1129
1130 -- If the value passed for the column name matches with the global variable
1131 -- for the Interface Line Attribute, then return the value passed as p_column_value
1132 IF g_interface_attr = p_column_name THEN
1133 l_ret_val := p_column_value;
1134 END IF;
1135
1136 RETURN l_ret_val;
1137 END get_int_val;
1138
1139
1140 PROCEDURE transfer_posting(
1141 ERRBUF OUT NOCOPY VARCHAR2,
1142 RETCODE OUT NOCOPY NUMBER,
1143 p_batch_name IN igs_fi_posting_int_all.batch_name%TYPE,
1144 p_posting_date_low IN VARCHAR2,
1145 p_posting_date_high IN VARCHAR2,
1146 p_org_id IN igs_fi_posting_int_all.org_id%TYPE
1147 ) AS
1148 /*
1149 || Created By : brajendr
1150 || Created On : 24-Apr-2001
1151 || Purpose :
1152 || Known limitations, enhancements or remarks :
1153 || Change History :
1154 || Who When What
1155 || pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables
1156 || Process has been obsoleted, removed code.
1157 || agairola 04-May-2002 Modified the log file format from Tabular to
1158 || multiline
1159 || agairola 17-Apr-2002 Modified the code for the INTERFACE_LINE_ATTRIBUTE
1160 || for bugs 2326595, 2309929, 2310806
1161 || agairola 12-Apr-2002 Displaying party number in case of erros while creating customer account
1162 || for bugs 2326595, 2309929, 2310806
1163 || agairola 11-Apr-2002 Added the code in case the description is NULL, it is equated to
1164 || the comments. Also, removed the commit statement from inside the
1165 || cur_postings as cur_postings has a FOR UPDATE NOWAIT clause
1166 || for bugs 2326595, 2309929, 2310806
1167 || agairola 10_Apr-2002 Modified the cursor cur_postings to include brackets for
1168 || Batch name. Also, added the FOR UPDATE NOWAIT, exception and
1169 || appropriate exception handling for the locking. Removed the
1170 || commit from inside the begin end block for inserting records
1171 || in the RA_INTERFACE_LINES_ALL table.
1172 || for bugs 2326595, 2309929, 2310806
1173 || jbegum 25 Feb 02 As part of Enh bug #2238226
1174 || Added code to copy value of the field orig_appl_fee_ref to
1175 || comments field of RA_INTERFACE_LINES_ALL when
1176 || source_transaction_type is APPLFEE
1177 || Added column orig_appl_fee_ref to the
1178 || IGS_FI_POSTING_INT_PKG.update_row
1179 || sykrishn 19 Feb 02 As part of Enh bug #2227831
1180 || Changes related to get_customer_details
1181 || jbegum 16 Feb 02 As part of Enh bug #2222272
1182 || Set org id of transactions created
1183 || in the Receivables Invoice Interface tables
1184 || to the org id value obtained from control table
1185 || (reverse chronological order - newest change first)
1186 */
1187
1188 BEGIN
1189
1190 -- This process has been obsoleted as part of Commercial Receivables TD
1191 fnd_message.set_name('IGS', 'IGS_GE_OBSOLETE_JOB');
1192 fnd_file.put_line( fnd_file.log, fnd_message.get());
1193 retcode := 0;
1194
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 retcode := 2;
1198 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||SQLERRM;
1199 igs_ge_msg_stack.conc_exception_hndl;
1200
1201 END transfer_posting;
1202
1203
1204 PROCEDURE posting_interface(
1205 errbuf OUT NOCOPY VARCHAR2,
1206 retcode OUT NOCOPY NUMBER,
1207 p_posting_date_low IN VARCHAR2,
1208 p_posting_date_high IN VARCHAR2,
1209 p_accounting_date IN VARCHAR2) AS
1210 /*
1211 || Created By : brajendr
1212 || Created On : 24-Apr-2001
1213 || Purpose :
1214 || Known limitations, enhancements or remarks :
1215 || Change History :
1216 || Who When What
1217 || (reverse chronological order - newest change first)
1218 || abshriva 5-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
1219 || pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build
1220 || Added check for manage_Accounts. Replaced app_exception.raise_exception
1221 || with raise l_user_exception to avoid 'Unhandled exp' in log file
1222 || Sykrishn 01/NOV/02 Build Bug 2584986 - GL interface Build Modifications.... (Revamp)
1223 Refer TD for Modifications
1224 || agairola 17-Apr-2002 Modified the Log file display
1225 || for bugs 2326595, 2309929, 2310806
1226 || schodava 8-OCT-2001 Enh # 2030448 (SFCR002)
1227 */
1228
1229
1230 l_d_gl_date_start igs_fI_applications.gl_date%TYPE;
1231 l_d_gl_date_end igs_fI_applications.gl_date%TYPE;
1232 l_d_gl_posted_date igs_fI_applications.gl_date%TYPE;
1233 l_org_id VARCHAR2(15);
1234 CURSOR cur_gen_control_id IS
1235 SELECT igs_fi_posting_control_s.nextval
1236 FROM dual;
1237
1238 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
1239 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
1240 l_user_exception EXCEPTION;
1241
1242 BEGIN
1243 BEGIN
1244 l_org_id := NULL;
1245 igs_ge_gen_003.set_org_id(l_org_id);
1246 EXCEPTION
1247 WHEN OTHERS THEN
1248 fnd_file.put_line (fnd_file.log, fnd_message.get);
1249 retcode:=2;
1250 RETURN;
1251 END;
1252 retcode:= 0;
1253
1254 -- Obtain the value of manage_accounts in the System Options form
1255 -- If it is null or 'OTHER', then this process is not available, so error out.
1256 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
1257 p_v_message_name => l_v_message_name
1258 );
1259 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
1260 fnd_message.set_name('IGS',l_v_message_name);
1261 fnd_file.put_line(fnd_file.log,fnd_message.get());
1262 fnd_file.put_line(fnd_file.log,' ');
1263 RAISE l_user_exception;
1264 END IF;
1265
1266 -- Get the value of of financials Insalled value defined in System Options form.
1267 IF igs_fi_gen_005.finp_get_receivables_inst = 'Y' THEN
1268 --This process is not valid. This process is only valid when using Oracle Financials "NO"
1269 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PROCESS');
1270 fnd_message.set_token('YES_NO', lookup_desc('YES_NO','N'));
1271 fnd_file.put_line(fnd_file.log, fnd_message.get());
1272 RAISE l_user_exception;
1273 END IF;
1274
1275 -- Convert the varchar2 parameter dates to DATE Datatype if not null else raise insufficient parameter error.
1276
1277 IF p_posting_date_low IS NOT NULL THEN
1278 l_d_gl_date_start := igs_ge_date.igsdate(p_posting_date_low);
1279 ELSE
1280 fnd_message.set_name('IGS', 'IGS_GE_INSUFFICIENT_PARAMETER');
1281 fnd_file.put_line(fnd_file.log, fnd_message.get());
1282 RAISE l_user_exception;
1283 END IF;
1284
1285 IF p_posting_date_high IS NOT NULL THEN
1286 l_d_gl_date_end := igs_ge_date.igsdate(p_posting_date_high);
1287 ELSE
1288 fnd_message.set_name('IGS', 'IGS_GE_INSUFFICIENT_PARAMETER');
1289 fnd_file.put_line(fnd_file.log, fnd_message.get());
1290 RAISE l_user_exception;
1291 END IF;
1292
1293 IF p_accounting_date IS NOT NULL THEN
1294 l_d_gl_posted_date := igs_ge_date.igsdate(p_accounting_date);
1295 ELSE
1296 fnd_message.set_name('IGS', 'IGS_GE_INSUFFICIENT_PARAMETER');
1297 fnd_file.put_line(fnd_file.log, fnd_message.get());
1298 RAISE l_user_exception;
1299 END IF;
1300
1301
1302 -- Validate if the GL date End is lesser than GL Date start
1303 -- The GL Date End should not be earlier than the GL Date Start 'START_DATE'
1304
1305 IF TRUNC(l_d_gl_date_start) > TRUNC(l_d_gl_date_end) THEN
1306 fnd_message.set_name('IGS','IGS_FI_VAL_GL_END_DATE');
1307 fnd_message.set_token('START_DATE',l_d_gl_date_start);
1308 fnd_file.put_line( fnd_file.log, fnd_message.get());
1309 RAISE l_user_exception;
1310 END IF;
1311
1312 -- Generate the batch posting control - id only once per process run ()..
1313 OPEN cur_gen_control_id;
1314 FETCH cur_gen_control_id INTO g_n_posting_control_id;
1315 CLOSE cur_gen_control_id;
1316
1317 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
1318 fnd_file.put_line(fnd_file.log, get_log_line('SYS_DATE',TO_CHAR(SYSDATE)));
1319 fnd_file.put_line(fnd_file.log, get_log_line('GL_DT_START',TO_CHAR(l_d_gl_date_start)));
1320 fnd_file.put_line(fnd_file.log, get_log_line('GL_DT_END',TO_CHAR(l_d_gl_date_end)));
1321 fnd_file.put_line(fnd_file.log, get_log_line('GL_POSTED_DT',TO_CHAR(l_d_gl_posted_date)));
1322 fnd_file.put_line(fnd_file.log, get_log_line('POSTING_CTRL_ID',TO_CHAR(g_n_posting_control_id)));
1323 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
1324
1325
1326 -- Log the heading for the log file "Summary of Transactions posted to Posting Interface"
1327 fnd_file.new_line(fnd_file.log);
1328 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
1329 fnd_message.set_name('IGS','IGS_FI_POST_SUM_TRANS');
1330 fnd_file.put_line( fnd_file.log, fnd_message.get());
1331 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
1332
1333 -- For both the cases, i.e. when Accounting Method is 'CASH' or 'ACCRUAL' the Credit Activities Transactions, Applications Transactions and Admission Application fees transactions need to be posted.
1334 -- For this invoke the local procedures TRANSFER_CREDIT_ACT_TXNS, TRANSFER_APPL_TXNS and TRANSFER_AD_APPL_FEE_TXNS.
1335 -- Hence no derivation and checking of accounting method required at this stage.
1336
1337
1338 transfer_credit_act_txns( p_d_gl_date_start => l_d_gl_date_start,
1339 p_d_gl_date_end => l_d_gl_date_end,
1340 p_d_gl_posted_date => l_d_gl_posted_date);
1341
1342 COMMIT;
1343
1344
1345
1346 transfer_appl_txns ( p_d_gl_date_start => l_d_gl_date_start,
1347 p_d_gl_date_end => l_d_gl_date_end,
1348 p_d_gl_posted_date => l_d_gl_posted_date);
1349
1350
1351 COMMIT;
1352
1353
1354 transfer_ad_appl_fee_txns( p_d_gl_date_start => l_d_gl_date_start,
1355 p_d_gl_date_end => l_d_gl_date_end,
1356 p_d_gl_posted_date => l_d_gl_posted_date);
1357
1358 COMMIT;
1359
1360 -- Only when Accounting Method is ACCRUAL, apart from posting the Credit Activities Transactions, Applications Transactions and Admission Application Fees Transactions,
1361 -- also the Charges Transactions need to be posted. - For this invoke the local procedure TRANSFER_CHGS_TXNS.
1362
1363 -- Get the value of the accounting method defined in System Options form.
1364 IF igs_fi_gen_005.finp_get_acct_meth = g_accrual THEN
1365
1366 transfer_chgs_txns ( p_d_gl_date_start => l_d_gl_date_start,
1367 p_d_gl_date_end => l_d_gl_date_end,
1368 p_d_gl_posted_date => l_d_gl_posted_date);
1369 COMMIT;
1370 END IF;
1371
1372 fnd_file.new_line(fnd_file.log);
1373 fnd_file.put_line(fnd_file.log,'----------------------------------------------------------------------------------------------------');
1374 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_PROCESSED');
1375 fnd_file.put_line(fnd_file.log, fnd_message.get()||TO_CHAR(g_n_rec_processed));
1376
1377 EXCEPTION
1378 WHEN l_user_exception THEN
1379 ROLLBACK;
1380 retcode := 2;
1381 WHEN OTHERS THEN
1382 ROLLBACK;
1383 retcode := 2;
1384 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||sqlerrm;
1385 igs_ge_msg_stack.conc_exception_hndl;
1386 END posting_interface;
1387
1388
1389 END igs_fi_posting_process;
[Home] [Help]