1 PACKAGE BODY igs_fi_gl_interface AS
2 /* $Header: IGSFI76B.pls 120.8 2006/05/26 13:19:45 skharida ship $ */
3 /* **********************************************************************************************
4
5 Created By : Amit Gairola
6 Date Created By: 1-Nov-2002
7 Purpose : This package contains the procedures for processing the GL Interface
8 Known limitations,enhancements,remarks:
9 Change History
10 Who When What
11 abshriva 12-May-2006 Bug 5217319: Amount precision change in insert_gl_int
12 sapanigr 05-May-2006 Bug 5178077: Modified procedure transfer to disable process in R12.
13 bannamal 05-Jul-2005 Enh# 3392095, Tuition Waivers Build.
14 Modified functions get_crd_cat, get_inv_cat, get_app_cat, validate_parm
15 and procedures transfer_credit, transfer_app for this build.
16 svuppala 30-MAY-2005 Enh 3442712 - Done the TBH modifications by adding
17 new columns Unit_Type_Id, Unit_Level in igs_fi_invln_int_all
18 vvutukur 11-Dec-2003 Bug#3310756.Modified procedures transfer_credit,igs_ad_appl.
19 shtatiko 18-NOV-2003 Enh# 3256915, modified get_crd_cat and get_app_cat
20 vvutukur 09-Oct-2003 Bug#3160036. Modified procedure transfer_admapp.
21 pathipat 14-Jun-2003 Enh 2831587 - Credit Card Fund Transfer build
22 Modified transfer_admapp() - modified call to igs_ad_app_req_pkg.update_row()
23 shtatiko 22-APR-2003 Enh# 2831569, Modified validate_parm.
24 agairola 27-Jan-2003 Bug 2711195: Modified the generate_log and transfer procedures
25 agairola 02-Jan-2003 Bug 2714777,2727324: Modified the process for the following
26 1. In the validate_parm procedure, if the rec_installed is set to N
27 then after logging the parameters, the process exits.
28 2. In the validate_parm procedure, the message name changed if the
29 run journal import is set to Y and the start date is in a period that
30 is closed.
31 vchappid 20-Dec-2002 Bug 2720702: In the procedure transfer_charge, for cursor cur_chg,
32 NVL is missing while checking for Error Account. When the Error Account
33 is set to NULL, it has to be treated as a valid transaction
34 i.e. error_account is treated as 'N'
35 agairola 16-Dec-02 Bug 2584741: Added the code for the Deposits in get_crd_cat
36
37 ********************************************************************************************** */
38
39 g_v_rec_inst igs_fi_control.rec_installed%TYPE;
40 g_n_sob_id igs_fi_control.set_of_books_id%TYPE;
41 g_v_currency_cd igs_fi_control.currency_cd%TYPE;
42 g_v_accounting_method igs_fi_control.accounting_method%TYPE;
43 g_v_new CONSTANT VARCHAR2(10) := 'NEW';
44 g_v_actual CONSTANT VARCHAR2(3) := 'A';
45 g_v_je_source_name CONSTANT VARCHAR2(80) := 'Student System';
46 g_v_user_je_src_name gl_je_sources.user_je_source_name%TYPE;
47 g_n_batch_id NUMBER(38);
48 g_v_cash CONSTANT VARCHAR2(6) := 'CASH';
49 g_v_adm_cat CONSTANT VARCHAR2(25) := 'Application Fee';
50 g_v_aid CONSTANT VARCHAR2(10) := 'Aid';
51 g_v_charges CONSTANT VARCHAR2(10) := 'Charges';
52 g_v_credits CONSTANT VARCHAR2(10) := 'Credits';
53 g_v_deposits CONSTANT VARCHAR2(10) := 'Deposits';
54 g_v_refund_offst CONSTANT VARCHAR2(20) := 'Refund Offset';
55 g_v_transferred CONSTANT VARCHAR2(15) := 'TRANSFERRED';
56 g_b_rec_found BOOLEAN := FALSE;
57
58 g_v_party_number igs_lookup_values.meaning%TYPE;
59 g_v_invoice_number igs_lookup_values.meaning%TYPE;
60 g_v_credit_number igs_lookup_values.meaning%TYPE;
61 g_v_application igs_lookup_values.meaning%TYPE;
62
63 --Added the constant variable for Tuition Waivers Build
64 g_v_waiver CONSTANT VARCHAR2(10) := 'Waivers';
65 g_v_post_waiver_gl_flag igs_fi_control.post_waiver_gl_flag%TYPE;
66
67 PROCEDURE initialize IS
68 /***********************************************************************************************
69
70 Created By : Amit Gairola
71 Date Created By: 1-Nov-2002
72 Purpose : This procedure will initialize the package variables
73 Known limitations,enhancements,remarks:
74 Change History
75 Who When What
76
77 ********************************************************************************************** */
78
79 CURSOR cur_gl_je_src(cp_je_src_name gl_je_sources.je_source_name%TYPE) IS
80 SELECT user_je_source_name
81 FROM gl_je_sources
82 WHERE je_source_name = cp_je_src_name;
83 BEGIN
84 g_v_invoice_number := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
85 'CHARGE_NUMBER');
86 g_v_credit_number := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
87 'CREDIT_NUMBER');
88 g_v_party_number := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
89 'PARTY');
90 g_v_application := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_SOURCE_TRANSACTION_TYPE',
91 'APPLFEE');
92 g_v_accounting_method := NULL;
93 g_v_currency_cd := NULL;
94 g_v_rec_inst := NULL;
95 g_n_sob_id := NULL;
96 g_n_batch_id := NULL;
97
98 OPEN cur_gl_je_src(g_v_je_source_name);
99 FETCH cur_gl_je_src INTO g_v_user_je_src_name;
100 CLOSE cur_gl_je_src;
101
102 END initialize;
103
104 FUNCTION get_crd_cat (p_n_credit_id IN NUMBER) RETURN VARCHAR2 AS
105
106 /******************************************************************
107 Created By : agairola
108 Date Created By : 22-NOV-2002
109 Purpose : Function to get the source category name for the passed credit id
110 Known limitations, - Hard Coded texts are returned as per TD requriements.
111 enhancements,
112 remarks :
113 Change History
114 Who When What
115 bannamal 05-Jul-05 Bug# 3392095, Tuition Waivers Build.
116 Added condition to check whether credit class is WAIVER
117 shtatiko 18-NOV-03 Bug# 3256915, Added INSTALLMENT_PAYMENTS when checking the credit class.
118 agairola 16-Dec-02 Bug: 2584741 added the code for Deposits functionality
119 ******************************************************************/
120
121 CURSOR cur_cred (cp_n_credit_id IN igs_fi_credits.credit_id%TYPE) IS
122 SELECT crtype.credit_class
123 FROM igs_fi_credits_all crd,
124 igs_fi_cr_types_all crtype
125 WHERE crd.credit_type_id = crtype.credit_type_id
126 AND crd.credit_id = cp_n_credit_id;
127
128 CURSOR cur_tran_type (cp_n_credit_id IN igs_fi_credits.credit_id%TYPE) IS
129 SELECT inv.transaction_type
130 FROM igs_fi_inv_int_all inv,
131 igs_fi_applications app
132 WHERE inv.invoice_id = app.invoice_id
133 AND app.credit_id = cp_n_credit_id;
134
135 l_v_credit_class igs_fi_cr_types_all.credit_class%TYPE;
136 l_v_trans_type igs_fi_inv_int_all.transaction_type%TYPE;
137 l_v_category VARCHAR2(20):= NULL;
138
139
140 BEGIN
141
142 IF (p_n_credit_id IS NULL) THEN
143 RETURN null;
144 END IF;
145
146 -- Get the credit class of the credit record.
147 OPEN cur_cred(p_n_credit_id);
148 FETCH cur_cred INTO l_v_credit_class;
149 CLOSE cur_cred;
150
151 -- Added INSTALLMENT_PAYMENTS credit class as part of Bug# 3256915
152 IF l_v_credit_class IN ('ONLINE PAYMENT','PMT','OTH', 'INSTALLMENT_PAYMENTS') THEN
153 l_v_category := g_v_credits;
154
155 ELSIF l_v_credit_class IN ('EXTFA','INTFA','SPNSP') THEN
156 l_v_category := g_v_aid;
157
158 ELSIF l_v_credit_class IN ('ENRDEPOSIT','OTHDEPOSIT') THEN
159 l_v_category := g_v_deposits;
160
161 ELSIF l_v_credit_class = 'CHGADJ'THEN
162 OPEN cur_tran_type (p_n_credit_id);
163 FETCH cur_tran_type INTO l_v_trans_type;
164 CLOSE cur_tran_type;
165
166 IF l_v_trans_type = 'REFUND' THEN
167 l_v_category := g_v_refund_offst;
168 ELSE
169 l_v_category := g_v_charges;
170 END IF;
171
172 --Added this condition as part of Tuition Waivers Build
173 ELSIF l_v_credit_class = 'WAIVER' THEN
174 l_v_category := g_v_waiver;
175
176 -- Added this ELSE condition so that if at all any credit class is missed, it will be imported as a credit instead of erroring out.
177 -- Added this as part of Bug# 3256915
178 ELSE
179 l_v_category := g_v_credits;
180 END IF;
181
182 RETURN l_v_category;
183
184 END get_crd_cat;
185
186
187 FUNCTION get_inv_cat (p_v_transaction_type IN VARCHAR2) RETURN VARCHAR2 AS
188
189 /******************************************************************
190 Created By : agairola
191 Date Created By : 22-NOV-2002
192 Purpose : Function to get the source category name for the passed transaction type
193 Known limitations, - Hard Coded texts are returned as per TD requriements.
194 enhancements,
195 remarks :
196 Change History
197 Who When What
198 bannamal 5-Jul-05 Bug# 3392095, Tuition Waivers Build.
199 Added condition to check whether transaction type is WAIVER_ADJ
200 ******************************************************************/
201
202 l_v_category VARCHAR2(20) := NULL;
203
204
205 BEGIN
206
207 IF (p_v_transaction_type IS NULL) THEN
208 RETURN NULL;
209 END IF;
210
211 IF p_v_transaction_type = 'AID_ADJ' THEN
212 l_v_category := g_v_aid;
213 ELSIF p_v_transaction_type = 'REFUND' THEN
214 l_v_category := g_v_refund_offst;
215
216 ELSIF p_v_transaction_type = 'WAIVER_ADJ' THEN
217 l_v_category := g_v_waiver;
218
219 ELSE
220 l_v_category := g_v_charges;
221 END IF;
222 RETURN l_v_category;
223
224
225 END get_inv_cat;
226
227
228 FUNCTION get_app_cat (p_n_invoice_id IN NUMBER,
229 p_n_credit_id IN NUMBER) RETURN VARCHAR2 AS
230
231 /******************************************************************
232 Created By : agairola
233 Date Created By : 22-NOV-2002
234 Purpose : Function to get the source category name for the passed invoice/credit id for applications
235 Known limitations, - Hard Coded texts are returned as per TD requriements.
236 enhancements,
237 remarks :
238 Change History
239 Who When What
240 bannamal 05-Jul-05 Bug# 3392095, Tuition Waivers Build.
241 Added condition to check whether credit class is WAIVER
242 shtatiko 18-NOV-03 Bug# 3256915, Added INSTALLMENT_PAYMENTS when checking the credit class.
243 ******************************************************************/
244
245
246 CURSOR cur_cred (cp_n_credit_id IN igs_fi_credits.credit_id%TYPE) IS
247 SELECT crtype.credit_class
248 FROM igs_fi_credits_all crd,
249 igs_fi_cr_types_all crtype
250 WHERE crd.credit_type_id = crtype.credit_type_id
251 AND crd.credit_id = cp_n_credit_id;
252
253
254 CURSOR cur_tran_type (cp_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE) IS
255 SELECT inv.transaction_type
256 FROM igs_fi_inv_int_all inv
257 WHERE inv.invoice_id = cp_n_invoice_id;
258
259 l_v_category VARCHAR2(20) := NULL;
260 l_v_trans_type igs_fi_inv_int_all.transaction_type%TYPE;
261 l_v_credit_class igs_fi_cr_types_all.credit_class%TYPE;
262
263 BEGIN
264
265 IF (p_n_invoice_id IS NULL) OR (p_n_credit_id IS NULL) THEN
266 RETURN null;
267 END IF;
268
269 -- Get the credit class of the credit record.
270 OPEN cur_cred(p_n_credit_id);
271 FETCH cur_cred INTO l_v_credit_class;
272 CLOSE cur_cred;
273
274 -- Added INSTALLMENT_PAYMENTS credit class as part of Bug# 3256915
275 IF l_v_credit_class IN ('ONLINE PAYMENT','PMT', 'INSTALLMENT_PAYMENTS') THEN
276 l_v_category := g_v_credits;
277
278 ELSIF l_v_credit_class IN ('EXTFA','INTFA','SPNSP') THEN
279 l_v_category := g_v_aid;
280
281 ELSIF l_v_credit_class = 'CHGADJ'THEN
282 -- Fetch the transaction type of the invoice record.
283 OPEN cur_tran_type (p_n_invoice_id);
284 FETCH cur_tran_type INTO l_v_trans_type;
285 CLOSE cur_tran_type;
286
287 IF l_v_trans_type = 'REFUND' THEN
288 l_v_category := g_v_refund_offst;
289 ELSE
290 l_v_category := g_v_charges;
291 END IF;
292
293 --Added the condition as part of Tuition Waivers Build
294 ELSIF l_v_credit_class = 'WAIVER' THEN
295 l_v_category := g_v_waiver;
296
297 -- Added this ELSE condition so that if at all any credit class is missed, it will be imported as a credit instead of erroring out.
298 -- Added this as part of Bug# 3256915
299 ELSE
300 l_v_category := g_v_credits;
301 END IF;
302
303 RETURN l_v_category;
304
305
306 END get_app_cat;
307
308 FUNCTION get_batch_id RETURN NUMBER AS
309 /***********************************************************************************************
310
311 Created By : Amit Gairola
312 Date Created By: 1-Nov-2002
313 Purpose : This function will return the unique batch identifier
314 Known limitations,enhancements,remarks:
315 Change History
316 Who When What
317
318 ********************************************************************************************** */
319 l_n_batch_id igs_fi_invln_int.posting_control_id%TYPE;
320
321 CURSOR cur_batch IS
322 SELECT IGS_FI_POSTING_CONTROL_S.NEXTVAL
323 FROM dual;
324 BEGIN
325 OPEN cur_batch;
326 FETCH cur_batch INTO l_n_batch_id;
327 CLOSE cur_batch;
328
329 RETURN l_n_batch_id;
330 END get_batch_id;
331
332 FUNCTION get_log_details(p_v_lookup_code igs_lookup_values.lookup_code%TYPE,
333 p_v_value VARCHAR2) RETURN VARCHAR2 AS
334 /***********************************************************************************************
335
336 Created By : Amit Gairola
337 Date Created By: 1-Nov-2002
338 Purpose : This function will get the log information
339 Known limitations,enhancements,remarks:
340 Change History
341 Who When What
342
343 ********************************************************************************************** */
344 l_v_log_line VARCHAR2(2000);
345 BEGIN
346 l_v_log_line := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',p_v_lookup_code)||' : '||p_v_value;
347
348 RETURN l_v_log_line;
349 END get_log_details;
350
351 FUNCTION get_party_number(p_n_party_id hz_parties.party_id%TYPE) RETURN VARCHAR2 AS
352 /***********************************************************************************************
353
354 Created By : Amit Gairola
355 Date Created By: 1-Nov-2002
356 Purpose : This function will retrieve the party number for the party id passed as input
357 Known limitations,enhancements,remarks:
358 Change History
359 Who When What
360
361 ********************************************************************************************** */
362 CURSOR cur_hzp(cp_party_id hz_parties.party_id%TYPE) IS
363 SELECT party_number
364 FROM hz_parties hzp
365 WHERE party_id = cp_party_id;
366
367 l_v_person_number hz_parties.party_number%TYPE;
368 BEGIN
369 IF p_n_party_id IS NULL THEN
370 l_v_person_number := NULL;
371 ELSE
372 OPEN cur_hzp(p_n_party_id);
373 FETCH cur_hzp INTO l_v_person_number;
374 CLOSE cur_hzp;
375 END IF;
376
377 RETURN l_v_person_number;
378 END get_party_number;
379
380 PROCEDURE insert_gl_int(p_d_gl_date DATE,
381 p_user_cat_name VARCHAR2,
382 p_dr_ccid NUMBER,
383 p_cr_ccid NUMBER,
384 p_amount NUMBER,
385 p_ref23 NUMBER,
386 p_ref30 VARCHAR2,
387 p_desc VARCHAR2) AS
388 /***********************************************************************************************
389
390 Created By : Amit Gairola
391 Date Created By: 1-Nov-2002
392 Purpose : This procedure will create the records in the GL Interface table
393 Known limitations,enhancements,remarks:
394 Change History
395 Who When What
396 abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
397 ********************************************************************************************** */
398 l_n_dr_ccid gl_interface.code_combination_id%TYPE;
399 l_n_cr_ccid gl_interface.code_combination_id%TYPE;
400 l_n_amnt gl_interface.entered_dr%TYPE;
401 l_n_user_id gl_interface.created_by%TYPE;
402 l_v_user_je_cat gl_interface.user_je_category_name%TYPE;
403
404 CURSOR cur_je_cat(cp_je_cat gl_je_categories.je_category_name%TYPE) IS
405 SELECT user_je_category_name
406 FROM gl_je_categories
407 WHERE je_category_name = cp_je_cat;
408
409 BEGIN
410 l_n_dr_ccid := p_dr_ccid;
411 l_n_cr_ccid := p_cr_ccid;
412 l_n_amnt := p_amount;
413
414 l_n_user_id := fnd_global.user_id;
415
416 OPEN cur_je_cat(p_user_cat_name);
417 FETCH cur_je_cat INTO l_v_user_je_cat;
418 CLOSE cur_je_cat;
419
420 l_n_amnt :=igs_fi_gen_gl.get_formatted_amount(l_n_amnt);
421
422 -- If the amount is negative, then swap the accounts and the amount is made positive
423 IF l_n_amnt < 0 THEN
424 l_n_dr_ccid := p_cr_ccid;
425 l_n_cr_ccid := p_dr_ccid;
426 l_n_amnt := ABS(l_n_amnt);
427 END IF;
428
429 -- Create a transaction in the GL Interface for the debit account
430 INSERT INTO gl_interface(status,
431 set_of_books_id,
432 accounting_date,
433 currency_code,
434 date_created,
435 created_by,
436 actual_flag,
437 user_je_category_name,
438 user_je_source_name,
439 code_combination_id,
440 entered_dr,
441 entered_cr,
442 accounted_dr,
443 accounted_cr,
444 reference1,
445 reference10,
446 reference23,
447 reference30,
448 group_id)
449 VALUES (g_v_new,
450 g_n_sob_id,
451 p_d_gl_date,
452 g_v_currency_cd,
453 sysdate,
454 l_n_user_id,
455 g_v_actual,
456 l_v_user_je_cat,
457 g_v_user_je_src_name,
458 l_n_dr_ccid,
459 l_n_amnt,
460 NULL,
461 l_n_amnt,
462 NULL,
463 to_char(g_n_batch_id),
464 p_desc,
465 p_ref23,
466 p_ref30,
467 g_n_batch_id);
468
469 -- Create a transaction in the GL Interface for the credit account
470 INSERT INTO gl_interface(status,
471 set_of_books_id,
472 accounting_date,
473 currency_code,
474 date_created,
475 created_by,
476 actual_flag,
477 user_je_category_name,
478 user_je_source_name,
479 code_combination_id,
480 entered_dr,
481 entered_cr,
482 accounted_dr,
483 accounted_cr,
484 reference1,
485 reference10,
486 reference23,
487 reference30,
488 group_id)
489 VALUES (g_v_new,
490 g_n_sob_id,
491 p_d_gl_date,
492 g_v_currency_cd,
493 sysdate,
494 l_n_user_id,
495 g_v_actual,
496 l_v_user_je_cat,
497 g_v_user_je_src_name,
498 l_n_cr_ccid,
499 NULL,
500 l_n_amnt,
501 NULL,
502 l_n_amnt,
503 to_char(g_n_batch_id),
504 p_desc,
505 p_ref23,
506 p_ref30,
507 g_n_batch_id);
508 END insert_gl_int;
509
510 PROCEDURE transfer_credit(p_d_gl_date_start DATE,
511 p_d_gl_date_end DATE,
512 p_d_gl_date_posted DATE) AS
513 /***********************************************************************************************
514
515 Created By : Amit Gairola
516 Date Created By: 1-Nov-2002
517 Purpose : This procedure will transfer the credit transactions to the GL Interface
518 Known limitations,enhancements,remarks:
519 Change History
520 Who When What
521 bannamal 05-Jul-2005 Bug# 3392095, Tuition Waivers Build.
522 Added code to skip the waiver credit record if the post_waiver_gl_flag is set to 'N'.
523 vvutukur 11-Dec-2003 Bug#3310756.Modified cursor cur_crd to exclude the deposit credit
524 activity txns that does not have accounting information and having 'TRANSFERRED' Status.
525 ********************************************************************************************** */
526
527 -- Cursor for selecting the credit transactions from the credit activities table
528 CURSOR cur_crd(cp_gl_date_start DATE,
529 cp_gl_date_end DATE) IS
530 SELECT cra.rowid,cra.*,
531 crd.credit_number credit_number,
532 crd.party_id party_id
533 FROM igs_fi_cr_activities cra,
534 igs_fi_credits crd
535 WHERE cra.gl_date IS NOT NULL
536 AND ((cra.POSTING_ID IS NULL) AND (cra.POSTING_CONTROL_ID IS NULL))
537 AND TRUNC(cra.gl_date) >= TRUNC(cp_gl_date_start)
538 AND TRUNC(cra.gl_date) <= TRUNC(cp_gl_date_end)
539 AND cra.credit_id = crd.credit_id
540 AND cra.dr_gl_ccid IS NOT NULL
541 AND cra.cr_gl_ccid IS NOT NULL
542 AND cra.status <> g_v_transferred;
543
544 l_b_exception_flag BOOLEAN;
545 l_v_crd_cat gl_je_categories.je_category_name%TYPE;
546 l_v_crd_desc gl_interface.reference10%TYPE;
547 l_b_waiver_flag BOOLEAN;
548
549 BEGIN
550
551 -- Select all the credit transactions which have the GL Date between the
552 -- the Gl Date start and GL date end passed as input to the procedure.
553 FOR crdrec IN cur_crd(p_d_gl_date_start,
554 p_d_gl_date_end) LOOP
555 l_b_exception_flag := FALSE;
556 l_v_crd_cat := NULL;
557
558 -- Fetch the journal category for the credit transaction
559 l_v_crd_cat := get_crd_cat(crdrec.credit_id);
560
561 l_b_waiver_flag := TRUE;
562
563 IF (l_v_crd_cat = g_v_waiver) THEN
564 IF (g_v_accounting_method = g_v_cash) THEN
565 IF (g_v_post_waiver_gl_flag = 'N' ) THEN
566 l_b_waiver_flag := FALSE;
567 END IF;
568 END IF;
569 END IF;
570
571 IF (l_b_waiver_flag = TRUE) THEN
572
573 -- Prepare the description for the credit transaction
574 l_v_crd_desc := g_v_credit_number;
575 l_v_crd_desc := l_v_crd_desc||' :'||crdrec.credit_number;
576 l_v_crd_desc := l_v_crd_desc||' ;'||g_v_party_number||' :';
577 l_v_crd_desc := l_v_crd_desc||get_party_number(crdrec.party_id);
578 l_v_crd_desc := SUBSTR(l_v_crd_desc,1,240);
579
580 -- Create the transactions in the GL Interface table
581 BEGIN
582 insert_gl_int(p_d_gl_date => crdrec.gl_date,
583 p_user_cat_name => l_v_crd_cat,
584 p_dr_ccid => crdrec.dr_gl_ccid,
585 p_cr_ccid => crdrec.cr_gl_ccid,
586 p_amount => crdrec.amount,
587 p_ref23 => crdrec.credit_activity_id,
588 p_ref30 => 'IGS_FI_CR_ACTIVITIES',
589 p_desc => l_v_crd_desc);
590 EXCEPTION
591 WHEN OTHERS THEN
592 l_b_exception_flag := TRUE;
593 fnd_file.put_line(fnd_file.log,
594 sqlerrm);
595 END;
596
597 -- Update the Credit Activity record with the batch identifier generated the posted date.
598 IF NOT l_b_exception_flag THEN
599 igs_fi_cr_activities_pkg.update_row(x_rowid => crdrec.rowid,
600 x_credit_activity_id => crdrec.credit_activity_id,
601 x_credit_id => crdrec.credit_id,
602 x_status => crdrec.status,
603 x_transaction_date => crdrec.transaction_date,
604 x_amount => crdrec.amount,
605 x_dr_account_cd => crdrec.dr_account_cd,
606 x_cr_account_cd => crdrec.cr_account_cd,
607 x_dr_gl_ccid => crdrec.dr_gl_ccid,
608 x_cr_gl_ccid => crdrec.cr_gl_ccid,
609 x_bill_id => crdrec.bill_id,
610 x_bill_number => crdrec.bill_number,
611 x_bill_date => crdrec.bill_date,
612 x_posting_id => crdrec.posting_id,
613 x_posting_control_id => g_n_batch_id,
614 x_gl_date => crdrec.gl_date,
615 x_gl_posted_date => p_d_gl_date_posted);
616 END IF;
617 COMMIT;
618 END IF;
619 END LOOP;
620 END transfer_credit;
621
622 PROCEDURE transfer_charge(p_d_gl_date_start DATE,
623 p_d_gl_date_end DATE,
624 p_d_gl_date_posted DATE) AS
625 /***********************************************************************************************
626
627 Created By : Amit Gairola
628 Date Created By: 1-Nov-2002
629 Purpose : This procedure will transfer the charge transactions to the GL Interface
630 Known limitations,enhancements,remarks:
631 Change History
632 Who When What
633 svuppala 30-MAY-2005 Enh 3442712 - Done the TBH modifications by adding
634 new columns Unit_Type_Id, Unit_Level in igs_fi_invln_int_all
635
636 vchappid 20-Dec-2002 Bug 2720702: In the procedure transfer_charge, for cursor cur_chg,
637 NVL is missing while checking for Error Account. When the Error Account
638 is set to NULL, it has to be treated as a valid transaction
639 i.e. error_account is treated as 'N'
640 ********************************************************************************************** */
641 l_b_exception_flag BOOLEAN;
642 l_v_inv_cat gl_je_categories.je_category_name%TYPE;
643 l_v_inv_desc gl_interface.reference10%TYPE;
644
645 -- Cursor for selecting the Charge transactions from the Invoice Lines table
646 CURSOR cur_chg(cp_gl_date_start igs_fi_invln_int.gl_date%TYPE,
647 cp_gl_date_end igs_fi_invln_int.gl_date%TYPE) IS
648 SELECT ln.*,
649 inv.invoice_number,
650 inv.transaction_type,
651 inv.person_id
652 FROM igs_fi_invln_int ln,
653 igs_fi_inv_int inv
654 WHERE ln.invoice_id = inv.invoice_id
655 AND ln.gl_date IS NOT NULL
656 AND TRUNC(ln.gl_date) >= TRUNC(cp_gl_date_start)
657 AND TRUNC(ln.gl_date) <= TRUNC(cp_gl_date_end)
658 AND NVL(ln.error_account,'N') = 'N'
659 AND ((ln.posting_id IS NULL) AND (ln.posting_control_id IS NULL));
660
661 BEGIN
662
663 -- If the accounting method is CASH, then the charge transactions are not transferred
664 IF g_v_accounting_method = g_v_cash THEN
665 RETURN;
666 END IF;
667
668 -- Loop across all the charge transactions selected by the cursor cur_chg
669 FOR chgrec IN cur_chg(p_d_gl_date_start,
670 p_d_gl_date_end) LOOP
671 l_b_exception_flag := FALSE;
672
673 l_v_inv_cat := NULL;
674 -- Derive the category for the charge transaction
675 l_v_inv_cat := get_inv_cat(chgrec.transaction_type);
676
677 -- Derive the description
678 l_v_inv_desc := g_v_invoice_number;
679 l_v_inv_desc := l_v_inv_desc||' :'||chgrec.invoice_number||' ;';
680 l_v_inv_desc := l_v_inv_desc||g_v_party_number;
681 l_v_inv_desc := l_v_inv_desc||' :'||get_party_number(chgrec.person_id);
682
683 l_v_inv_desc := SUBSTR(l_v_inv_desc,1,240);
684
685 -- Create transactions in the GL Interface
686 SAVEPOINT SP_INV1;
687 BEGIN
688 insert_gl_int(p_d_gl_date => chgrec.gl_date,
689 p_user_cat_name => l_v_inv_cat,
690 p_dr_ccid => chgrec.rec_gl_ccid,
691 p_cr_ccid => chgrec.rev_gl_ccid,
692 p_amount => chgrec.amount,
693 p_ref23 => chgrec.invoice_lines_id,
694 p_ref30 => 'IGS_FI_INVLN_INT',
695 p_desc => l_v_inv_desc);
696 EXCEPTION
697 WHEN OTHERS THEN
698 ROLLBACK TO SP_INV1;
699 l_b_exception_flag := TRUE;
700 fnd_file.put_line(fnd_file.log,
701 sqlerrm);
702 END;
703
704 IF NOT l_b_exception_flag THEN
705 igs_fi_invln_int_pkg.update_row(x_rowid => chgrec.row_id,
706 x_invoice_id => chgrec.invoice_id,
707 x_line_number => chgrec.line_number,
708 x_invoice_lines_id => chgrec.invoice_lines_id,
709 x_attribute2 => chgrec.attribute2,
710 x_chg_elements => chgrec.chg_elements,
711 x_amount => chgrec.amount,
712 x_unit_attempt_status => chgrec.unit_attempt_status,
713 x_eftsu => chgrec.eftsu,
714 x_credit_points => chgrec.credit_points,
715 x_attribute_category => chgrec.attribute_category,
716 x_attribute1 => chgrec.attribute1,
717 x_s_chg_method_type => chgrec.s_chg_method_type,
718 x_description => chgrec.description,
719 x_attribute3 => chgrec.attribute3,
720 x_attribute4 => chgrec.attribute4,
721 x_attribute5 => chgrec.attribute5,
722 x_attribute6 => chgrec.attribute6,
723 x_attribute7 => chgrec.attribute7,
724 x_attribute8 => chgrec.attribute8,
725 x_attribute9 => chgrec.attribute9,
726 x_attribute10 => chgrec.attribute10,
727 x_rec_account_cd => chgrec.rec_account_cd,
728 x_rev_account_cd => chgrec.rev_account_cd,
729 x_rec_gl_ccid => chgrec.rec_gl_ccid,
730 x_rev_gl_ccid => chgrec.rev_gl_ccid,
731 x_org_unit_cd => chgrec.org_unit_cd,
732 x_posting_id => chgrec.posting_id,
733 x_attribute11 => chgrec.attribute11,
734 x_attribute12 => chgrec.attribute12,
735 x_attribute13 => chgrec.attribute13,
736 x_attribute14 => chgrec.attribute14,
737 x_attribute15 => chgrec.attribute15,
738 x_attribute16 => chgrec.attribute16,
739 x_attribute17 => chgrec.attribute17,
740 x_attribute18 => chgrec.attribute18,
741 x_attribute19 => chgrec.attribute19,
742 x_attribute20 => chgrec.attribute20,
743 x_error_string => chgrec.error_string,
744 x_error_account => chgrec.error_account,
745 x_location_cd => chgrec.location_cd,
746 x_uoo_id => chgrec.uoo_id,
747 x_gl_date => chgrec.gl_date,
748 x_posting_control_id => g_n_batch_id,
749 x_gl_posted_date => p_d_gl_date_posted,
750 x_unit_type_id => chgrec.unit_type_id,
751 x_unit_level => chgrec.unit_level);
752 END IF;
753 COMMIT;
754 END LOOP;
755 END transfer_charge;
756
757 PROCEDURE transfer_app(p_d_gl_date_start DATE,
758 p_d_gl_date_end DATE,
759 p_d_gl_date_posted DATE) AS
760 /***********************************************************************************************
761
762 Created By : Amit Gairola
763 Date Created By: 1-Nov-2002
764 Purpose : This procedure will transfer the application transactions to the GL Interface
765 Known limitations,enhancements,remarks:
766 Change History
767 Who When What
768 bannamal 05-Jul-05 Bug# 3392095, Tuition Waivers Build.
769 Added code to skip the waiver application record if the post_waiver_gl_flag is set to 'N'.
770 ********************************************************************************************** */
771 l_v_app_cat gl_je_categories.je_category_name%TYPE;
772 l_v_app_desc gl_interface.reference10%TYPE;
773 l_b_exception_flag BOOLEAN;
774 l_b_waiver_flag BOOLEAN;
775
776 -- Cursor for selecting the unposted application records
777 CURSOR cur_app(cp_gl_date_start igs_fi_applications.gl_date%TYPE,
778 cp_gl_date_end igs_fi_applications.gl_date%TYPE) IS
779 SELECT app.*,app.rowid row_id,
780 inv.invoice_number,
781 crd.credit_number,
782 inv.person_id
783 FROM igs_fi_applications app,
784 igs_fi_credits crd,
785 igs_fi_inv_int inv
786 WHERE crd.credit_id = app.credit_id
787 AND inv.invoice_id = app.invoice_id
788 AND app.gl_date IS NOT NULL
789 AND TRUNC(app.gl_date) >= TRUNC(cp_gl_date_start)
790 AND TRUNC(app.gl_date) <= TRUNC(cp_gl_date_end)
791 AND ((app.posting_id IS NULL) AND (app.posting_control_id IS NULL));
792 BEGIN
793
794 -- Loop across all the application records fetched by the cur_app
795 FOR apprec IN cur_app(p_d_gl_date_start,
796 p_d_gl_date_end) LOOP
797 l_v_app_desc := null;
798 l_b_exception_flag := FALSE;
799 l_v_app_cat := NULL;
800
801 -- Get the category name for the application transaction
802 l_v_app_cat := get_app_cat(p_n_invoice_id => apprec.invoice_id,
803 p_n_credit_id => apprec.credit_id);
804 l_b_waiver_flag := TRUE;
805
806 IF (l_v_app_cat = g_v_waiver) THEN
807 IF (g_v_accounting_method = g_v_cash) THEN
808 IF (g_v_post_waiver_gl_flag = 'N') THEN
809 l_b_waiver_flag := FALSE;
810 END IF;
811 END IF;
812 END IF;
813
814 IF (l_b_waiver_flag = TRUE) THEN
815 -- Form the description for the application record
816 l_v_app_desc := SUBSTR(g_v_invoice_number||' :'||
817 apprec.invoice_number||' ;'||
818 g_v_credit_number||' :'||
819 apprec.credit_number||' ;'||
820 g_v_party_number||' :'||
821 get_party_number(apprec.person_id),
822 1,240);
823
824 -- Create the transactions in the GL interface table
825 SAVEPOINT SP_APP1;
826 BEGIN
827
828 insert_gl_int(p_d_gl_date => apprec.gl_date,
829 p_user_cat_name => l_v_app_cat,
830 p_dr_ccid => apprec.dr_gl_code_ccid,
831 p_cr_ccid => apprec.cr_gl_code_ccid,
832 p_amount => apprec.amount_applied,
833 p_ref23 => apprec.application_id,
834 p_ref30 => 'IGS_FI_APPLICATIONS',
835 p_desc => l_v_app_desc);
836
837 EXCEPTION
838 WHEN OTHERS THEN
839 ROLLBACK TO SP_APP1;
840 l_b_exception_flag := TRUE;
841 fnd_file.put_line(fnd_file.log,
842 sqlerrm);
843 END;
844
845 -- If there is no error in creation of the transaction in the GL Interface table,
846 -- then update the application record with the posting control id and the gl posted date
847 IF NOT l_b_exception_flag THEN
848 igs_fi_applications_pkg.update_row(x_rowid => apprec.row_id,
849 x_application_id => apprec.application_id,
850 x_application_type => apprec.application_type,
851 x_invoice_id => apprec.invoice_id,
852 x_credit_id => apprec.credit_id,
853 x_credit_activity_id => apprec.credit_activity_id,
854 x_amount_applied => apprec.amount_applied,
855 x_apply_date => apprec.apply_date,
856 x_link_application_id => apprec.link_application_id,
857 x_dr_account_cd => apprec.dr_account_cd,
858 x_cr_account_cd => apprec.cr_account_cd,
859 x_dr_gl_code_ccid => apprec.dr_gl_code_ccid,
860 x_cr_gl_code_ccid => apprec.cr_gl_code_ccid,
861 x_applied_invoice_lines_id => apprec.applied_invoice_lines_id,
862 x_appl_hierarchy_id => apprec.appl_hierarchy_id,
863 x_posting_id => apprec.posting_id,
864 x_gl_date => apprec.gl_date,
865 x_gl_posted_date => p_d_gl_date_posted,
866 x_posting_control_id => g_n_batch_id);
867 END IF;
868 COMMIT;
869 END IF;
870 END LOOP;
871 END transfer_app;
872
873 PROCEDURE transfer_admapp(p_d_gl_date_start DATE,
874 p_d_gl_date_end DATE,
875 p_d_gl_date_posted DATE) AS
876 /***********************************************************************************************
877
878 Created By : Amit Gairola
879 Date Created By: 1-Nov-2002
880 Purpose : This procedure will transfer the admission application
881 transactions to the GL Interface
882 Known limitations,enhancements,remarks:
883 Change History
884 Who When What
885 vvutukur 15-Dec-2003 Bug#3310756.Modified cursor cur_adm to check for cash_gl_ccid is not null also.
886 vvutukur 09-Oct-2003 Bug#3160036.Replaced the call to igs_ad_app_req.update_row with
887 the call to igs_ad_gen_015.update_igs_ad_app_req.
888 pathipat 14-Jun-2003 Enh 2831587 - Credit Card Fund Transfer build
889 Modified call to igs_ad_app_req_pkg.update_row - added 3 new parameters
890 ********************************************************************************************** */
891 l_b_exception_flag BOOLEAN;
892 l_v_adm_cat gl_je_categories.je_category_name%TYPE;
893 l_v_adm_desc gl_interface.reference10%TYPE;
894
895 -- Cursor for selecting the records from the admission application table
896 CURSOR cur_adm(cp_gl_date_start DATE,
897 cp_gl_date_end DATE) IS
898 SELECT fee.*, fee.rowid row_id, appl.application_id
899 FROM igs_ad_app_req fee,
900 igs_ad_appl appl
901 WHERE appl.person_id = fee.person_id
902 AND appl.admission_appl_number = fee.admission_appl_number
903 AND fee.gl_date IS NOT NULL
904 AND fee.posting_control_id IS NULL
905 AND fee.gl_posted_date IS NULL
906 AND fee.rev_gl_ccid IS NOT NULL
907 AND fee.cash_gl_ccid IS NOT NULL
908 AND TRUNC(fee.gl_date) >= TRUNC(cp_gl_date_start)
909 AND TRUNC(fee.gl_date) <= TRUNC(cp_gl_date_end);
910
911 BEGIN
912
913 -- Loop across all the records fetched by the cursor cur_adm
914 FOR admrec IN cur_adm(p_d_gl_date_start,
915 p_d_gl_date_end) LOOP
916 l_b_exception_flag := FALSE;
917
918 -- Get the je category name
919 l_v_adm_cat := g_v_adm_cat;
920
921 l_v_adm_desc := SUBSTR(g_v_application||' :'||
922 admrec.app_req_id||' ;'||
923 g_v_party_number||' :'||
924 get_party_number(admrec.person_id),1,240);
925
926 -- Create transactions in the GL Interface table
927 SAVEPOINT SP_ADAPP1;
928 BEGIN
929 insert_gl_int(p_d_gl_date => admrec.gl_date,
930 p_user_cat_name => l_v_adm_cat,
931 p_dr_ccid => admrec.cash_gl_ccid,
932 p_cr_ccid => admrec.rev_gl_ccid,
933 p_amount => admrec.fee_amount,
934 p_ref23 => admrec.app_req_id,
935 p_ref30 => 'IGS_AD_APP_REQ',
936 p_desc => l_v_adm_desc);
937 EXCEPTION
938 WHEN OTHERS THEN
939 ROLLBACK TO SP_ADAPP1;
940 l_b_exception_flag := TRUE;
941 fnd_file.put_line(fnd_file.log,
942 sqlerrm);
943 END;
944
945 -- If there is no error in creating transaction in the GL Interface table,
946 -- then update the admission application record by the value of the Posting control id
947 -- and the GL Posted Date
948 IF NOT l_b_exception_flag THEN
949 igs_ad_gen_015.update_igs_ad_app_req(
950 p_rowid => admrec.row_id,
951 p_app_req_id => admrec.app_req_id,
952 p_person_id => admrec.person_id,
953 p_admission_appl_number => admrec.admission_appl_number,
954 p_applicant_fee_type => admrec.applicant_fee_type,
955 p_applicant_fee_status => admrec.applicant_fee_status,
956 p_fee_date => admrec.fee_date,
957 p_fee_payment_method => admrec.fee_payment_method,
958 p_fee_amount => admrec.fee_amount,
959 p_reference_num => admrec.reference_num,
960 p_credit_card_code => admrec.credit_card_code,
961 p_credit_card_holder_name => admrec.credit_card_holder_name,
962 p_credit_card_number => admrec.credit_card_number,
963 p_credit_card_expiration_date => admrec.credit_card_expiration_date,
964 p_rev_gl_ccid => admrec.rev_gl_ccid,
965 p_cash_gl_ccid => admrec.cash_gl_ccid,
966 p_rev_account_cd => admrec.rev_account_cd,
967 p_cash_account_cd => admrec.cash_account_cd,
968 p_posting_control_id => g_n_batch_id,
969 p_gl_date => admrec.gl_date,
970 p_gl_posted_date => p_d_gl_date_posted,
971 p_credit_card_tangible_cd => admrec.credit_card_tangible_cd,
972 p_credit_card_payee_cd => admrec.credit_card_payee_cd,
973 p_credit_card_status_code => admrec.credit_card_status_code,
974 p_mode => 'R'
975 );
976 END IF;
977 COMMIT;
978 END LOOP;
979 END transfer_admapp;
980
981 FUNCTION validate_parm(p_d_gl_date_start DATE,
982 p_d_gl_date_end DATE,
983 p_v_post_detail VARCHAR2,
984 p_d_gl_date_posted DATE,
985 p_v_jrnl_import VARCHAR2) RETURN BOOLEAN AS
986 /***********************************************************************************************
987
988 Created By : Amit Gairola
989 Date Created By: 1-Nov-2002
990 Purpose : This function will validate the input parameters
991 Known limitations,enhancements,remarks:
992 Change History
993 Who When What
994 bannamal 05-Jul-2005 Bug# 3392095, Tuition Waivers Build.
995 Modified the cursor cur_ctrl.Added post_waiver_gl_flag in the select clause.
996 shtatiko 22-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option.
997 agairola 02-Jan-2003 Bug 2714777,2727324: Modified the process for the following
998 1. In the validate_parm procedure, if the rec_installed is set to N
999 then after logging the parameters, the process exits.
1000 2. In the validate_parm procedure, the message name changed if the
1001 run journal import is set to Y and the start date is in a period that
1002 is closed.
1003 ********************************************************************************************** */
1004 l_b_val_parm BOOLEAN;
1005 l_v_period_name gl_period_statuses.period_name%TYPE;
1006 l_d_end_date DATE;
1007 l_v_sob_name gl_sets_of_books.name%TYPE;
1008 l_log_line VARCHAR2(2000);
1009 l_v_message_name fnd_new_messages.message_name%TYPE;
1010 l_v_manage_accounts igs_fi_control.manage_accounts%TYPE;
1011
1012 -- Cursor for selecting the information from the System Options
1013 CURSOR cur_ctrl IS
1014 SELECT rec_installed,
1015 currency_cd,
1016 set_of_books_id,
1017 accounting_method,
1018 post_waiver_gl_flag
1019 FROM igs_fi_control;
1020
1021 CURSOR cur_sob(cp_sob_id gl_sets_of_books.set_of_books_id%TYPE) IS
1022 SELECT name
1023 FROM gl_sets_of_books
1024 WHERE set_of_books_id = cp_sob_id;
1025
1026 -- Cursor for selecting the records from gl period statuses table
1027 CURSOR cur_glp(cp_app_id NUMBER,
1028 cp_gl_date_start DATE,
1029 cp_sob_id igs_fi_control.set_of_books_id%TYPE) IS
1030 SELECT period_name, end_date
1031 FROM gl_period_statuses a
1032 WHERE TRUNC(start_date) <= TRUNC(cp_gl_date_start)
1033 AND TRUNC(end_date) >= TRUNC(cp_gl_date_start)
1034 AND CLOSING_STATUS = 'O'
1035 AND APPLICATION_ID = cp_app_id
1036 AND adjustment_period_flag = 'N'
1037 AND set_of_books_id = cp_sob_id;
1038
1039 BEGIN
1040 l_b_val_parm := TRUE;
1041
1042 -- Select the currency, set of books and accounting method information from the
1043 -- system options
1044 -- If any error occurs store the message name in l_v_message_name.
1045 -- Logging of the same is done after logging parameters
1046 OPEN cur_ctrl;
1047 FETCH cur_ctrl INTO g_v_rec_inst,
1048 g_v_currency_cd,
1049 g_n_sob_id,
1050 g_v_accounting_method,
1051 g_v_post_waiver_gl_flag;
1052 IF cur_ctrl%NOTFOUND THEN
1053 l_v_message_name := 'IGS_FI_SYSTEM_OPT_SETUP';
1054 l_b_val_parm := FALSE;
1055 ELSE
1056
1057 -- If the receivables installed is set to No, then this process should not be run
1058 IF g_v_rec_inst = 'N' THEN
1059 l_v_message_name := 'IGS_FI_INVALID_PROCESS';
1060 l_b_val_parm := FALSE;
1061 END IF;
1062 END IF;
1063 CLOSE cur_ctrl;
1064
1065 -- Get the batch identifier
1066 g_n_batch_id := get_batch_id;
1067
1068 OPEN cur_sob(g_n_sob_id);
1069 FETCH cur_sob INTO l_v_sob_name;
1070 CLOSE cur_sob;
1071
1072 -- Log the values for the parameters to the process
1073 fnd_file.put_line(fnd_file.log, ' ');
1074 fnd_message.set_name('IGS',
1075 'IGS_FI_ANC_LOG_PARM');
1076 fnd_file.put_line(fnd_file.log,
1077 fnd_message.get);
1078
1079 fnd_file.new_line(fnd_file.log);
1080 fnd_file.put_line(fnd_file.log,
1081 get_log_details('SET_OF_BOOKS',l_v_sob_name));
1082 fnd_file.put_line(fnd_file.log,
1083 get_log_details('SYS_DATE', sysdate));
1084 fnd_file.put_line(fnd_file.log,
1085 get_log_details('GL_DT_START',p_d_gl_date_start));
1086 fnd_file.put_line(fnd_file.log,
1087 get_log_details('GL_DT_END',p_d_gl_date_end));
1088 fnd_file.put_line(fnd_file.log,
1089 get_log_details('POSTING_DETAIL',
1090 igs_fi_gen_gl.get_lkp_meaning('IGS_FI_POSTING_DETAIL',
1091 p_v_post_detail)));
1092 fnd_file.put_line(fnd_file.log,
1093 get_log_details('GL_POSTED_DT',p_d_gl_date_posted));
1094 fnd_file.put_line(fnd_file.log,
1095 get_log_details('RUN_JNL_IMP',
1096 igs_fi_gen_gl.get_lkp_meaning('YES_NO',
1097 p_v_jrnl_import)));
1098 fnd_file.put_line(fnd_file.log,
1099 get_log_details('POSTING_CTRL_ID',g_n_batch_id));
1100
1101 fnd_file.new_line(fnd_file.log);
1102
1103 -- Added the code here to exit from this procedure if the Receivables is not installed
1104 -- If validations of igs_fi_control fails then log the corresponding message and return.
1105 IF NOT l_b_val_parm THEN
1106 fnd_message.set_name ( 'IGS', l_v_message_name );
1107 IF l_v_message_name = 'IGS_FI_INVALID_PROCESS' THEN
1108 fnd_message.set_token('YES_NO', igs_fi_gen_gl.get_lkp_meaning('YES_NO', 'Y'));
1109 END IF;
1110 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
1111 RETURN l_b_val_parm;
1112 END IF;
1113
1114 -- Check the value of Manage Accounts System Option value.
1115 -- If its NULL or OTHER then this process should error out by logging message.
1116 l_v_message_name := NULL;
1117 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
1118 p_v_message_name => l_v_message_name );
1119 IF l_v_manage_accounts IS NULL OR l_v_manage_accounts = 'OTHER' THEN
1120 fnd_message.set_name ( 'IGS', l_v_message_name );
1121 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
1122 RETURN FALSE;
1123 END IF;
1124
1125 -- If any of the parameters is NULL, then log the error in the log file
1126 IF ((p_d_gl_date_start IS NULL) OR
1127 (p_d_gl_date_end IS NULL) OR
1128 (p_v_post_detail IS NULL) OR
1129 (p_d_gl_date_posted IS NULL) OR
1130 (p_v_jrnl_import IS NULL)) THEN
1131 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
1132 fnd_file.put_line(fnd_file.log,
1133 fnd_message.get);
1134 l_b_val_parm := FALSE;
1135 END IF;
1136
1137 -- If the GL Date start is greater than the gl date end, then log error message
1138 IF p_d_gl_date_start IS NOT NULL AND p_d_gl_date_end IS NOT NULL THEN
1139 IF TRUNC(p_d_gl_date_start) > TRUNC(p_d_gl_date_end) THEN
1140 fnd_message.set_name('IGS',
1141 'IGS_FI_VAL_GL_END_DATE');
1142 fnd_message.set_token('START_DATE',
1143 TRUNC(p_d_gl_date_start));
1144 fnd_file.put_line(fnd_file.log,
1145 fnd_message.get);
1146 l_b_val_parm := FALSE;
1147 END IF;
1148
1149 -- If the GL Date Start and the GL Date end are not within the same Open accounting period,
1150 -- then log the error message
1151 OPEN cur_glp(8405,
1152 p_d_gl_date_start,
1153 g_n_sob_id);
1154 FETCH cur_glp INTO l_v_period_name, l_d_end_date;
1155 IF cur_glp%NOTFOUND THEN
1156 fnd_message.set_name('IGS',
1157 'IGS_FI_NO_OPEN_PERIOD');
1158 fnd_file.put_line(fnd_file.log,
1159 fnd_message.get);
1160 l_b_val_parm := FALSE;
1161 END IF;
1162 CLOSE cur_glp;
1163
1164 IF TRUNC(l_d_end_date) < TRUNC(p_d_gl_date_end) THEN
1165 fnd_message.set_name('IGS',
1166 'IGS_FI_VAL_GL_START_END_DATE');
1167 fnd_message.set_token('GL_START_DATE',
1168 p_d_gl_date_start);
1169 fnd_message.set_token('PERIOD',
1170 l_v_period_name);
1171
1172 fnd_file.put_line(fnd_file.log,
1173 fnd_message.get);
1174 l_b_val_parm := FALSE;
1175 END IF;
1176
1177 l_v_period_name := NULL;
1178 l_d_end_date := NULL;
1179
1180 -- If the Import Journal process is to be run, then validate if the GL Date Start and GL Date End
1181 -- are within the same open period for the GL Application (Application Id = 101)
1182 IF p_v_jrnl_import = 'Y' THEN
1183 OPEN cur_glp(101,
1184 p_d_gl_date_start,
1185 g_n_sob_id);
1186 FETCH cur_glp INTO l_v_period_name, l_d_end_date;
1187 IF cur_glp%NOTFOUND THEN
1188 fnd_message.set_name('IGS',
1189 'IGS_FI_GL_NO_OPEN_PERIOD');
1190 fnd_file.put_line(fnd_file.log,
1191 fnd_message.get);
1192 l_b_val_parm := FALSE;
1193 END IF;
1194 CLOSE cur_glp;
1195 END IF;
1196
1197 IF TRUNC(l_d_end_date) < TRUNC(p_d_gl_date_end) THEN
1198 fnd_message.set_name('IGS',
1199 'IGS_FI_NO_OPEN_PERIOD_GL');
1200 l_log_line := fnd_message.get;
1201 fnd_message.set_name('IGS',
1202 'IGS_FI_VAL_GL_START_END_DATE');
1203 fnd_message.set_token('GL_START_DATE',
1204 p_d_gl_date_start);
1205 fnd_message.set_token('PERIOD',
1206 l_v_period_name);
1207 l_log_line := l_log_line||fnd_message.get;
1208 fnd_file.put_line(fnd_file.log,
1209 l_log_line);
1210 l_b_val_parm := FALSE;
1211 END IF;
1212 END IF;
1213
1214
1215 -- Validate if the parameter for Posting Detail is a valid lookup
1216 IF p_v_post_detail IS NOT NULL THEN
1217 IF NOT igs_lookups_view_pkg.get_pk_for_validation('IGS_FI_POSTING_DETAIL',
1218 p_v_post_detail) THEN
1219 fnd_message.set_name('IGS',
1220 'IGS_FI_INV_POSTING_DETAIL');
1221 fnd_file.put_line(fnd_file.log,
1222 fnd_message.get);
1223 l_b_val_parm := FALSE;
1224 END IF;
1225 END IF;
1226 -- Validate if the Journal Import parameter is a valid Lookup
1227
1228 IF p_v_jrnl_import IS NOT NULL THEN
1229 IF NOT igs_lookups_view_pkg.get_pk_for_validation('YES_NO',
1230 p_v_jrnl_import) THEN
1231 fnd_message.set_name('IGS',
1232 'IGS_FI_INV_JNL_IMPORT');
1233 fnd_file.put_line(fnd_file.log,
1234 fnd_message.get);
1235 l_b_val_parm := FALSE;
1236 END IF;
1237 END IF;
1238
1239 RETURN l_b_val_parm;
1240
1241 END validate_parm;
1242
1243 FUNCTION run_jrnl_imp(p_d_gl_date_start DATE,
1244 p_d_gl_date_end DATE,
1245 p_v_post_detail VARCHAR2) RETURN NUMBER AS
1246 /***********************************************************************************************
1247
1248 Created By : Amit Gairola
1249 Date Created By: 1-Nov-2002
1250 Purpose : This function will submit the Import Journal Process
1251 Known limitations,enhancements,remarks:
1252 Change History
1253 Who When What
1254
1255 ********************************************************************************************** */
1256
1257 l_n_unique_id NUMBER(38);
1258 l_n_req_id NUMBER(38);
1259 l_v_post_detail VARCHAR2(1);
1260 BEGIN
1261
1262 -- If the Posting Detail parameter is DETAIL then pass N to GL Interface
1263 -- Else Y for Create Summary Journal parameter
1264 IF p_v_post_detail = 'DETAIL' THEN
1265 l_v_post_detail := 'N';
1266 ELSE
1267 l_v_post_detail := 'Y';
1268 END IF;
1269
1270 -- Get the unique run id using the get_unique_run_id procedure
1271 -- l_n_unique_id := gl_interface_control_pkg.get_unique_run_id;
1272
1273 -- Create a record in the GL_INTERFACE_CONTROL_PKG
1274 /* gl_interface_control_pkg.insert_row(xset_of_books_id => g_n_sob_id,
1275 xinterface_run_id => l_n_unique_id,
1276 xje_source_name => g_v_je_source_name,
1277 xgroup_id => g_n_batch_id,
1278 xpacket_id => NULL); */
1279
1280 -- Run the Journal Import Process
1281 l_n_req_id := fnd_request.submit_request('SQLGL',
1282 'GLLEZL',
1283 '',
1284 '',
1285 FALSE,
1286 to_char(l_n_unique_id),
1287 to_char(g_n_sob_id),
1288 'N',
1289 to_char(TRUNC(p_d_gl_date_start),'YYYY/MM/DD'),
1290 to_char(TRUNC(p_d_gl_date_end),'YYYY/MM/DD'),
1291 l_v_post_detail,
1292 'N',
1293 CHR(0),
1294 '', '', '', '', '', '', '', '', '', '',
1295 '', '', '', '', '', '', '', '', '', '',
1296 '', '', '', '', '', '', '', '', '', '',
1297 '', '', '', '', '', '', '', '', '', '',
1298 '', '', '', '', '', '', '', '', '', '',
1299 '', '', '', '', '', '', '', '', '', '',
1300 '', '', '', '', '', '', '', '', '', '',
1301 '', '', '', '', '', '', '', '', '', '',
1302 '', '', '', '', '', '', '', '', '', '','');
1303 RETURN l_n_req_id;
1304 END run_jrnl_imp;
1305
1306 PROCEDURE generate_log AS
1307 /***********************************************************************************************
1308
1309 Created By : Amit Gairola
1310 Date Created By: 1-Nov-2002
1311 Purpose : This procedure will generate the log file
1312 Known limitations,enhancements,remarks:
1313 Change History
1314 Who When What
1315 agairola 27-Jan-2003 Bug 2711195: Modified the code to display No Data Found in the log file
1316 ********************************************************************************************** */
1317 l_v_line_txt VARCHAR2(80);
1318 l_n_cntr PLS_INTEGER;
1319 l_n_dr_amnt gl_interface.entered_dr%TYPE;
1320 l_n_cr_amnt gl_interface.entered_cr%TYPE;
1321
1322 -- Cursor for getting the debit and credit amount group by the user_je_category name
1323 CURSOR cur_glint(cp_batch_id NUMBER,
1324 cp_source_name VARCHAR2,
1325 cp_sob_id NUMBER) IS
1326 SELECT user_je_category_name,
1327 SUM(decode(entered_dr,NULL,0,entered_dr)) dr_amnt,
1328 SUM(decode(entered_cr,NULL,0,entered_cr)) cr_amnt
1329 FROM gl_interface
1330 WHERE group_id = cp_batch_id
1331 AND set_of_books_id = cp_sob_id
1332 AND user_je_source_name = cp_source_name
1333 GROUP BY user_je_category_name
1334 ORDER BY user_je_category_name;
1335 BEGIN
1336 l_v_line_txt := NULL;
1337 g_b_rec_found := FALSE;
1338
1339 FOR l_n_cntr IN 1..80 LOOP
1340 l_v_line_txt := l_v_line_txt||'-';
1341 END LOOP;
1342
1343 fnd_file.new_line(fnd_file.log);
1344
1345 fnd_message.set_name('IGS',
1346 'IGS_FI_SUM_GL_TRX');
1347 fnd_file.put_line(fnd_file.log,
1348 fnd_message.get);
1349 fnd_file.put_line(fnd_file.log,
1350 l_v_line_txt);
1351
1352 -- Loop across the GL Interface table
1353 FOR glrec IN cur_glint(g_n_batch_id,
1354 g_v_user_je_src_name,
1355 g_n_sob_id) LOOP
1356 l_n_dr_amnt := NVL(l_n_dr_amnt,0) + NVL(glrec.dr_amnt,0);
1357 l_n_cr_amnt := NVL(l_n_cr_amnt,0) + NVL(glrec.cr_amnt,0);
1358
1359 -- Log the debit and the credit amount category wise
1360 fnd_file.put_line(fnd_file.log,
1361 get_log_details('CATEGORY',glrec.user_je_category_name));
1362 fnd_file.put_line(fnd_file.log,
1363 get_log_details('ENT_DEBITS',glrec.dr_amnt));
1364 fnd_file.put_line(fnd_file.log,
1365 get_log_details('ENT_CREDITS',glrec.cr_amnt));
1366 fnd_file.new_line(fnd_file.log);
1367
1368 -- Set the value of the global variable g_b_rec_found to TRUE
1369 g_b_rec_found := TRUE;
1370 END LOOP;
1371
1372 fnd_file.new_line(fnd_file.log);
1373
1374 -- Log the total entered debits and credits. If no data has been
1375 -- created in the GL_INTERFACE table,then log the message NO
1376 -- DATA FOUND
1377 IF g_b_rec_found THEN
1378 fnd_file.put_line(fnd_file.log,
1379 l_v_line_txt);
1380 fnd_file.put_line(fnd_file.log,
1381 get_log_details('TOT_ENT_DEBITS',l_n_dr_amnt));
1382 fnd_file.put_line(fnd_file.log,
1383 get_log_details('TOT_ENT_CREDITS',l_n_cr_amnt));
1384 ELSE
1385 fnd_message.set_name('IGS',
1386 'IGS_GE_NO_DATA_FOUND');
1387 fnd_file.put_line(fnd_file.log,
1388 fnd_message.get);
1389 END IF;
1390 END generate_log;
1391
1392 PROCEDURE transfer(errbuf OUT NOCOPY VARCHAR2,
1393 retcode OUT NOCOPY NUMBER,
1394 p_d_gl_date_start VARCHAR2,
1395 p_d_gl_date_end VARCHAR2,
1396 p_v_post_detail VARCHAR2,
1397 p_d_gl_date_posted VARCHAR2,
1398 p_v_jrnl_import VARCHAR2) AS
1399 /***********************************************************************************************
1400
1401 Created By : Amit Gairola
1402 Date Created By: 1-Nov-2002
1403 Purpose : This procedure is the main concurrent program procedure
1404 Known limitations,enhancements,remarks:
1405 Change History
1406 Who When What
1407 sapanigr 05-May-2006 Bug 5178077: Added call to igs_ge_gen_003.set_org_id. to disable process in R12
1408 agairola 27-Jan-2003 Bug 2711195: Modified the code to run the Journal Import only
1409 when the records have been created in the GL_INTERFACE table
1410 ********************************************************************************************** */
1411 l_d_gl_date_start DATE;
1412 l_d_gl_date_end DATE;
1413 l_d_gl_date_posted DATE;
1414 l_n_req_id NUMBER(38);
1415 l_b_val_parm BOOLEAN;
1416 l_org_id VARCHAR2(15);
1417
1418 BEGIN
1419
1420 retcode := 0;
1421 initialize;
1422
1423 BEGIN
1424 l_org_id := NULL;
1425 igs_ge_gen_003.set_org_id(l_org_id);
1426 EXCEPTION
1427 WHEN OTHERS THEN
1428 fnd_file.put_line (fnd_file.log, fnd_message.get);
1429 RETCODE:=2;
1430 RETURN;
1431 END;
1432
1433 -- Convert the Date parameters
1434 l_d_gl_date_start := igs_ge_date.igsdate(p_d_gl_date_start);
1435 l_d_gl_date_end := igs_ge_date.igsdate(p_d_gl_date_end);
1436 l_d_gl_date_posted := igs_ge_date.igsdate(p_d_gl_date_posted);
1437
1438 -- Validate the parameters. If the validate_parm returns false, then raise error
1439 l_b_val_parm := validate_parm(p_d_gl_date_start => l_d_gl_date_start,
1440 p_d_gl_date_end => l_d_gl_date_end,
1441 p_v_post_detail => p_v_post_detail,
1442 p_d_gl_date_posted => l_d_gl_date_posted,
1443 p_v_jrnl_import => p_v_jrnl_import);
1444
1445 -- Transfer the Charge records
1446 IF NOT l_b_val_parm THEN
1447 retcode := 2;
1448 RETURN;
1449 END IF;
1450
1451 transfer_charge(p_d_gl_date_start => l_d_gl_date_start,
1452 p_d_gl_date_end => l_d_gl_date_end,
1453 p_d_gl_date_posted => l_d_gl_date_posted);
1454
1455 -- Transfer the Credit Transactions
1456 transfer_credit(p_d_gl_date_start => l_d_gl_date_start,
1457 p_d_gl_date_end => l_d_gl_date_end,
1458 p_d_gl_date_posted => l_d_gl_date_posted);
1459
1460 -- Transfer the application records
1461 transfer_app(p_d_gl_date_start => l_d_gl_date_start,
1462 p_d_gl_date_end => l_d_gl_date_end,
1463 p_d_gl_date_posted => l_d_gl_date_posted);
1464
1465 -- Transfer the admission application transactions
1466 transfer_admapp(p_d_gl_date_start => l_d_gl_date_start,
1467 p_d_gl_date_end => l_d_gl_date_end,
1468 p_d_gl_date_posted => l_d_gl_date_posted);
1469
1470 -- Generate the log
1471 generate_log;
1472
1473 -- If the records have been inserted in the GL_INTERFACE, then
1474 -- then the Journal Import Process should be run.
1475 IF g_b_rec_found THEN
1476 -- If the Journal Import parameter is Yes, then
1477 IF p_v_jrnl_import = 'Y' THEN
1478
1479 -- Run the Journal Import process
1480 l_n_req_id := run_jrnl_imp(l_d_gl_date_start,
1481 l_d_gl_date_end,
1482 p_v_post_detail);
1483
1484 -- If the request id is null or 0, i.e. the request is not submitted, then
1485 -- log message indicating to the user to run the Import Journal Process manually
1486 IF ((l_n_req_id IS NULL) OR (l_n_req_id = 0))THEN
1487 fnd_message.set_name('IGS',
1488 'IGS_FI_UNABLE_TO_SUB_GL_IMP');
1489 ELSE
1490
1491 -- Else log the request identifier
1492 fnd_file.put_line(fnd_file.log,
1493 get_log_details('REQ_ID',l_n_req_id));
1494 END IF;
1495 ELSE
1496
1497 -- Else if the run journal import parameter is No, then log a message to indicate that
1498 -- the user should manually run the Journal Import process
1499 fnd_message.set_name('IGS',
1500 'IGS_FI_RUN_IMP_JNL');
1501 fnd_file.put_line(fnd_file.log,
1502 fnd_message.get);
1503 END IF;
1504 END IF;
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507 retcode := 2;
1508 fnd_message.set_name('IGS',
1509 'IGS_GE_UNHANDLED_EXCEPTION');
1510 errbuf := fnd_message.get;
1511 fnd_file.put_line(fnd_file.log,
1512 sqlerrm);
1513 igs_ge_msg_stack.conc_exception_hndl;
1514 END transfer;
1515
1516 END igs_fi_gl_interface;
[Home] [Help]