1 PACKAGE BODY igs_fi_gen_007 AS
2 /* $Header: IGSFI62B.pls 120.5 2006/06/27 14:13:57 skharida ship $ */
3
4 CURSOR cur_credit(cp_credit_id igs_fi_credits.credit_id%TYPE) IS
5 SELECT *
6 FROM igs_fi_credits
7 WHERE credit_id = cp_credit_id
8 AND status = 'CLEARED'
9 AND unapplied_amount >0;
10
11 CURSOR cur_invoice(cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
12 SELECT inv.*
13 FROM igs_fi_inv_int inv
14 WHERE inv.invoice_id = cp_invoice_id
15 AND inv.invoice_amount_due >0;
16
17 CURSOR cur_credit_unapp(cp_credit_id igs_fi_credits.credit_id%TYPE) IS
18 SELECT *
19 FROM igs_fi_credits
20 WHERE credit_id = cp_credit_id
21 AND status = 'CLEARED'
22 AND unapplied_amount >=0;
23
24 CURSOR cur_invoice_unapp(cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
25 SELECT inv.*
26 FROM igs_fi_inv_int inv
27 WHERE inv.invoice_id = cp_invoice_id
28 AND inv.invoice_amount_due >=0;
29
30 g_chg_adj CONSTANT VARCHAR2(10) :='CHGADJ';
31 g_app CONSTANT VARCHAR2(10) :='APP';
32 g_unapp CONSTANT VARCHAR2(10) :='UNAPP';
33 g_yes CONSTANT VARCHAR2(1) :='Y';
34
35
36 FUNCTION get_sob_id RETURN NUMBER IS
37
38 /*
39 || Created By : Sridhar Koppula
40 || Created On : 26-JUL-2001
41 || Purpose : To return Set of Books ID
42 || Known limitations, enhancements or remarks :
43 || Change History :
44 || Who When What
45 || (reverse chronological order - newest change first)
46 */
47
48
49 l_sob igs_fi_control.set_of_books_id%TYPE;
50
51 CURSOR cur_sob IS SELECT set_of_books_id FROM igs_fi_control;
52
53 BEGIN
54 OPEN cur_sob;
55 FETCH cur_sob INTO l_sob;
56 CLOSE cur_sob;
57 RETURN l_sob;
58 EXCEPTION
59 WHEN OTHERS THEN
60 RAISE;
61 END get_sob_id;
62
63
64 FUNCTION get_coa_id RETURN NUMBER IS
65
66 /*
67 || Created By : Sridhar Koppula
68 || Created On : 26-JUL-2001
69 || Purpose : To return Chart of Accounts ID
70 || Known limitations, enhancements or remarks :
71 || Change History :
72 || Who When What
73 || (reverse chronological order - newest change first)
74 */
75
76
77 l_coa gl_sets_of_books.chart_of_accounts_id%TYPE;
78
79 CURSOR cur_coa IS SELECT chart_of_accounts_id FROM gl_sets_of_books
80 WHERE set_of_books_id = get_sob_id;
81
82 BEGIN
83 OPEN cur_coa;
84 FETCH cur_coa INTO l_coa;
85 CLOSE cur_coa;
86 RETURN l_coa;
87 EXCEPTION
88 WHEN OTHERS THEN
89 RAISE;
90 END get_coa_id;
91
92
93 FUNCTION get_gl_shortname RETURN VARCHAR2 IS
94
95 /*
96 || Created By : Sridhar Koppula
97 || Created On : 26-JUL-2001
98 || Purpose : To return Gl short name
99 || Known limitations, enhancements or remarks :
100 || Change History :
101 || Who When What
102 || (reverse chronological order - newest change first)
103 */
104
105
106 l_sname gl_sets_of_books.short_name%TYPE;
107
108 CURSOR cur_sname IS SELECT short_name FROM gl_sets_of_books
109 WHERE set_of_books_id = get_sob_id;
110
111 BEGIN
112 OPEN cur_sname;
113 FETCH cur_sname INTO l_sname;
114 CLOSE cur_sname;
115 RETURN l_sname;
116 EXCEPTION
117 WHEN OTHERS THEN
118 RAISE;
119 END get_gl_shortname;
120
121 FUNCTION get_segval_desc(p_value_set_id NUMBER,p_value VARCHAR2) RETURN VARCHAR2 IS
122 /*
123 || Created By : Sridhar Koppula
124 || Created On : 26-JUL-2001
125 || Purpose : To return Gl short name
126 || Known limitations, enhancements or remarks :
127 || Change History :
128 || Who When What
129 || (reverse chronological order - newest change first)
130 */
131 l_segdesc VARCHAR2(240);
132
133 CURSOR cur_vdesc IS SELECT description FROM fnd_flex_values_vl
134 WHERE flex_value_Set_id = p_value_set_id AND
135 flex_value=p_value;
136 BEGIN
137 OPEN cur_vdesc;
138 FETCH cur_vdesc INTO l_segdesc;
139 CLOSE cur_vdesc;
140 RETURN l_segdesc;
141 EXCEPTION
142 WHEN OTHERS THEN
143 RAISE;
144 END get_segval_desc;
145
146
147 PROCEDURE validate_parameters(p_credit_id IN igs_fi_credits.credit_id%TYPE,
148 p_invoice_id IN igs_fi_inv_int.invoice_id%TYPE,
149 p_amount_apply IN igs_fi_applications.amount_applied%TYPE,
150 p_appl_type IN igs_fi_applications.application_type%TYPE,
151 p_application_id IN igs_fi_applications.application_id%TYPE,
152 p_appl_hierarchy_id IN igs_fi_applications.appl_hierarchy_id%TYPE,
153 p_err_msg OUT NOCOPY fnd_new_messages.message_name%TYPE,
154 p_status OUT NOCOPY BOOLEAN,
155 p_d_gl_date IN DATE
156 ) AS
157
158 /*|| Created By :Sarakshi
159 || Created On :24-Jan-2002
160 || Purpose : For validating parameters.
161 || Known limitations, enhancements or remarks :
162 || Change History :
163 || Who When What
164 || (reverse chronological order - newest change first)
165 || smadathi 20-NOV-2002 Enh. Bug 2584986. Added new parameter GL Date
166 || to procedure.
167 || vvutukur 27-Sep-2002 Enh#2564643.Modified the message name IGS_AD_INVALID_PARAM_COMB to
168 || the new message IGS_FI_INVAMT_ZERO.This change is wrt bug#2463855,
169 || which is being released as part of Enh#2564643.
170 || vvutukur 22-Jul-2002 Thrown two separate messages while validating p_amount_apply, as
171 || part of bugfix#2463855,instead of throwing IGS_AD_INVALID_PARAM_COMB.
172 */
173
174 CURSOR cur_appl_hier IS
175 SELECT 'X'
176 FROM igs_fi_a_hierarchies
177 WHERE appl_hierarchy_id = p_appl_hierarchy_id;
178
179
180 CURSOR cur_chk_crd_inv(cp_credit_id igs_fi_credits.credit_id%TYPE,
181 cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
182 SELECT 'X'
183 FROM igs_fi_applications
184 WHERE application_id=p_application_id
185 AND credit_id=cp_credit_id
186 AND invoice_id=cp_invoice_id;
187
188 CURSOR cur_app_rec IS
189 SELECT 'X'
190 FROM igs_fi_applications a1,
191 igs_fi_applications a2
192 WHERE a1.application_id=p_application_id
193 AND a1.application_id=a2.link_application_id
194 AND a1.amount_applied= - a2.amount_applied;
195
196 CURSOR cur_chk_app IS
197 SELECT credit_id,invoice_id
198 FROM igs_fi_applications
199 WHERE application_id=p_application_id
200 AND application_type='APP';
201
202 CURSOR c_igs_fi_cr_types(cp_n_credit_id igs_fi_credits.credit_id%TYPE) IS
203 SELECT credit_class
204 FROM igs_fi_cr_types crtyp
205 WHERE EXISTS (SELECT '1'
206 FROM igs_fi_credits crd
207 WHERE crd.credit_type_id = crtyp.credit_type_id
208 AND crd.credit_id = cp_n_credit_id
209 );
210
211 rec_c_igs_fi_cr_types c_igs_fi_cr_types%ROWTYPE;
212
213 l_cur_chk_app cur_chk_app%ROWTYPE;
214 l_check VARCHAR2(1);
215 l_cur_credit cur_credit%ROWTYPE;
216 l_cur_invoice cur_invoice%ROWTYPE;
217 l_cur_credit_unapp cur_credit_unapp%ROWTYPE;
218 l_cur_invoice_unapp cur_invoice_unapp%ROWTYPE;
219 l_status BOOLEAN := FALSE;
220
221 BEGIN
222 p_status:=TRUE;
223 p_err_msg:=NULL;
224
225 --Check all the mandatory parameter are supplied or not
226 IF p_appl_type IS NULL THEN
227 p_status:=FALSE;
228 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
229 ELSIF p_appl_type NOT IN (g_app,g_unapp) THEN
230 --Checking application Type in APP/UNAPP
231 p_status:=FALSE;
232 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
233 ELSIF (((p_credit_id IS NULL) OR (p_invoice_id IS NULL)) AND (p_appl_type = g_app)) THEN
234 p_status:=FALSE;
235 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
236 ELSIF ((p_appl_type=g_app) AND (p_application_id IS NOT NULL)) THEN
237 --Validating the application Id
238 p_status:=FALSE;
239 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
240 ELSIF ((p_appl_type=g_unapp) AND (p_application_id IS NULL)) THEN
241 p_status:=FALSE;
242 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
243 END IF;
244 IF p_d_gl_date IS NULL THEN
245 p_status := FALSE;
246 p_err_msg := 'IGS_GE_INSUFFICIENT_PARAMETER';
247 END IF;
248
249 -- Return Back to the calling porcedure/finction if validate_parameters procedure is invoked
250 -- if either of the mnadatory parameters are not supplied or incorrect values are specified
251 -- for the parameters.
252 IF NOT (p_status) THEN
253 RETURN;
254 END IF;
255
256 OPEN c_igs_fi_cr_types(p_credit_id);
257 FETCH c_igs_fi_cr_types INTO rec_c_igs_fi_cr_types;
258 CLOSE c_igs_fi_cr_types;
259 IF rec_c_igs_fi_cr_types.credit_class <> 'ONLINE PAYMENT' THEN
260 l_status := igs_fi_gen_gl.check_gl_dt_appl_not_valid ( p_d_gl_date => p_d_gl_date,
261 p_n_invoice_id => p_invoice_id,
262 p_n_credit_id => p_credit_id
263 );
264 -- IF the passed GL Date value is before the GL Date of the charge or credit being applied to,
265 -- validate_parameters procedure returns the warning message IGS_FI_CHG_CRD_GL_DATE
266 -- and status as true to p_status out NOCOPY parameter and message to out NOCOPY p_err_msg
267 IF (l_status ) THEN
268 p_status := TRUE;
269 p_err_msg := 'IGS_FI_CHG_CRD_GL_DATE';
270 RETURN;
271 END IF;
272 END IF;
273
274
275 --validating application hierarchy Id
276 IF ((p_status = TRUE) AND ((p_appl_type = g_app) AND (p_appl_hierarchy_id IS NOT NULL))) THEN
277 OPEN cur_appl_hier;
278 FETCH cur_appl_hier INTO l_check;
279 IF cur_appl_hier%NOTFOUND THEN
280 p_status:=FALSE;
281 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
282 END IF;
283 CLOSE cur_appl_hier;
284 END IF;
285
286 --Validating Credit Id and invoice Id for application type of APP
287 IF ((p_status = TRUE) AND (p_appl_type = g_app))THEN
288 OPEN cur_credit(p_credit_id);
289 FETCH cur_credit INTO l_cur_credit;
290 IF cur_credit%NOTFOUND THEN
291 CLOSE cur_credit;
292 p_status:=FALSE;
293 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
294 ELSE
295 CLOSE cur_credit;
296 OPEN cur_invoice(p_invoice_id);
297 FETCH cur_invoice INTO l_cur_invoice;
298 IF cur_invoice%NOTFOUND THEN
299 p_status:=FALSE;
300 p_err_msg:='IGS_FI_INVAMT_ZERO';
301 END IF;
302 CLOSE cur_invoice;
303 END IF;
304 END IF;
305
306 --Validating amount applied
307 IF ((p_status = TRUE) AND ( p_amount_apply IS NOT NULL)) THEN
308 IF p_amount_apply < 0 THEN
309 p_status:=FALSE;
310 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
311 --Validating if amount applied parameter is greater than unapplied amount of the credit id
312 --when application type is APP
313 ELSIF (( p_appl_type = g_app ) AND (p_amount_apply > l_cur_credit.unapplied_amount)) THEN
314 p_status := FALSE;
315 p_err_msg := 'IGS_FI_HIGH_APPL_AMT';
316 --Validating if amount applied parameter is greater than invoice_amount_due
317 --when application type is APP
318 ELSIF ((p_appl_type = g_app) AND (p_amount_apply > l_cur_invoice.invoice_amount_due)) THEN
319 p_status:=FALSE;
320 p_err_msg:='IGS_FI_AMT_MOR_BUD';
321 END IF;
322 END IF;
323
324
325 --To validate if the application Id passed is a valid one
326 IF ((p_status) AND (p_appl_type=g_unapp)) THEN
327 OPEN cur_app_rec;
328 FETCH cur_app_rec INTO l_check;
329 IF cur_app_rec%FOUND THEN
330 p_status:=FALSE;
331 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
332 ELSE
333 OPEN cur_chk_app;
334 FETCH cur_chk_app INTO l_cur_chk_app;
335 IF cur_chk_app%NOTFOUND THEN
336 p_status:=FALSE;
337 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
338 END IF;
339 CLOSE cur_chk_app;
340 END IF;
341 CLOSE cur_app_rec;
342 END IF;
343
344 --Validating credit Id, invoice Id(if passed) for the given application Id for UNAPP processing
345 IF ( p_status = TRUE) THEN
346 IF ((p_appl_type=g_unapp) AND ((p_credit_id IS NOT NULL) OR (p_invoice_id IS NOT NULL))) THEN
347 OPEN cur_chk_crd_inv(NVL(p_credit_id,l_cur_chk_app.credit_id),NVL(p_invoice_id,l_cur_chk_app.invoice_id));
348 FETCH cur_chk_crd_inv INTO l_check;
349 IF cur_chk_crd_inv%NOTFOUND THEN
350 p_status:=FALSE;
351 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
352 END IF;
353 CLOSE cur_chk_crd_inv;
354 END IF;
355 END IF;
356
357 --Validating Credit Record for Unapplication
358 IF ((p_status = TRUE) AND (p_appl_type = g_unapp))THEN
359 OPEN cur_credit_unapp(l_cur_chk_app.credit_id);
360 FETCH cur_credit_unapp INTO l_cur_credit_unapp;
361 IF cur_credit_unapp%NOTFOUND THEN
362 p_status:=FALSE;
363 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
364 END IF;
365 CLOSE cur_credit_unapp;
366 END IF;
367
368 --Validating Charges Record for Unapplication
369 IF ((p_status = TRUE) AND (p_appl_type = g_unapp))THEN
370 OPEN cur_invoice_unapp(l_cur_chk_app.invoice_id);
371 FETCH cur_invoice_unapp INTO l_cur_invoice_unapp;
372 IF cur_invoice_unapp%NOTFOUND THEN
373 p_status:=FALSE;
374 p_err_msg:='IGS_AD_INVALID_PARAM_COMB';
375 END IF;
376 CLOSE cur_invoice_unapp;
377 END IF;
378
379
380 END validate_parameters;
381
382 PROCEDURE call_update_charges(p_invoice_amount_due IN igs_fi_inv_int_all.invoice_amount_due%TYPE,
383 p_cur_invoice IN cur_invoice%ROWTYPE,
384 p_v_opt_fee_flag IN igs_fi_inv_int_all.optional_fee_flag%TYPE,
385 p_flag OUT NOCOPY BOOLEAN ) AS
386 /*|| Created By :Sarakshi
387 || Created On :24-Jan-2002
388 || Purpose : For updating charges record once a application/unapplication has happened.
389 || Known limitations, enhancements or remarks :
390 || Change History :
391 || Who When What
392 || skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns of the table IGS_FI_INV_INT_ALL
393 || gurprsin 16-Aug-2005 Enh 3392095 - Tution Waiver build - Added new column waiver_name in the TBH call igs_fi_inv_int_pkg.update_row
394 || pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
395 || pathipat 07-Jun-2003 Enh 2831584 - SS Enhancements build
396 || Added parameter p_v_opt_fee_flag
397 || smadathi 20-NOV-2002 Enh. Bug 2584986. Modified igs_fi_inv_int_pkg.update_row
398 || to add new parameter reversal GL Date
399 || jbegum 20 feb 02 Enh bug # 2228910
400 || Removed the source_transaction_id column from igs_fi_inv_int_pkg.update_row
401 || (reverse chronological order - newest change first) */
402
403 l_v_opt_fee_flag igs_fi_inv_int_all.optional_fee_flag%TYPE := NULL;
404
405 BEGIN
406
407 p_flag := TRUE;
408
409 IF (p_v_opt_fee_flag IS NULL) THEN
410 l_v_opt_fee_flag := p_cur_invoice.optional_fee_flag;
411 ELSE
412 l_v_opt_fee_flag := p_v_opt_fee_flag;
413 END IF;
414
415 BEGIN
416 igs_fi_inv_int_pkg.update_row(x_rowid => p_cur_invoice.row_id,
417 x_invoice_id => p_cur_invoice.invoice_id,
418 x_person_id => p_cur_invoice.person_id,
419 x_fee_type => p_cur_invoice.fee_type,
420 x_fee_cat => p_cur_invoice.fee_cat,
421 x_fee_cal_type => p_cur_invoice.fee_cal_type,
422 x_fee_ci_sequence_number => p_cur_invoice.fee_ci_sequence_number,
423 x_course_cd => p_cur_invoice.course_cd,
424 x_attendance_mode => p_cur_invoice.attendance_mode,
425 x_attendance_type => p_cur_invoice.attendance_type,
426 x_invoice_amount_due => p_invoice_amount_due,
427 x_invoice_creation_date => p_cur_invoice.invoice_creation_date,
428 x_invoice_desc => p_cur_invoice.invoice_desc,
429 x_transaction_type => p_cur_invoice.transaction_type,
430 x_currency_cd => p_cur_invoice.currency_cd,
431 x_status => p_cur_invoice.status,
432 x_attribute_category => p_cur_invoice.attribute_category,
433 x_attribute1 => p_cur_invoice.attribute1,
434 x_attribute2 => p_cur_invoice.attribute2,
435 x_attribute3 => p_cur_invoice.attribute3,
436 x_attribute4 => p_cur_invoice.attribute4,
437 x_attribute5 => p_cur_invoice.attribute5,
438 x_attribute6 => p_cur_invoice.attribute6,
439 x_attribute7 => p_cur_invoice.attribute7,
440 x_attribute8 => p_cur_invoice.attribute8,
441 x_attribute9 => p_cur_invoice.attribute9,
442 x_attribute10 => p_cur_invoice.attribute10,
443 x_invoice_amount => p_cur_invoice.invoice_amount,
444 x_bill_id => p_cur_invoice.bill_id,
445 x_bill_number => p_cur_invoice.bill_number,
446 x_bill_date => p_cur_invoice.bill_date,
447 x_waiver_flag => p_cur_invoice.waiver_flag,
448 x_waiver_reason => p_cur_invoice.waiver_reason,
449 x_effective_date => p_cur_invoice.effective_date,
450 x_invoice_number => p_cur_invoice.invoice_number,
451 x_exchange_rate => p_cur_invoice.exchange_rate,
452 x_bill_payment_due_date => p_cur_invoice.bill_payment_due_date,
453 x_optional_fee_flag => l_v_opt_fee_flag,
454 x_mode => 'R',
455 x_reversal_gl_date => p_cur_invoice.reversal_gl_date,
456 x_tax_year_code => p_cur_invoice.tax_year_code,
457 x_waiver_name => p_cur_invoice.waiver_name
458 );
459 EXCEPTION
460 WHEN OTHERS THEN
461 p_flag :=FALSE;
462 END;
463
464 END call_update_charges;
465
466 PROCEDURE call_update_credits(p_unapplied_amount IN igs_fi_credits_all.unapplied_amount%TYPE,
467 p_cur_credit IN cur_credit%ROWTYPE,
468 p_flag OUT NOCOPY BOOLEAN)
469 AS
470 /*|| Created By :Sarakshi
471 || Created On :24-Jan-2002
472 || Purpose : For updating a credit record once a application/unappliaction has happened.
473 || Known limitations, enhancements or remarks :
474 || Change History :
475 || Who When What
476 || gurprsin 16-Aug-2005 Enh 3392095 - Tution Waiver build - Added new column waiver_name in the TBH call igs_fi_credits_pkg.update_row
477 || pathipat 21-Apr-2004 Enh 3558549 - Commercial Receivables Enhancements
478 || Added parameter x_source_invoice_id in call to igs_fi_credits_pkg.update_row()
479 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new parameters(lockbox_interface_id,batch_name,deposit_date) to the update_row
480 || call of credits table.
481 || schodava 16-Jun-2003 Enh # 2831587 - Credit Card Fund Transfer Build - Modified call to update row.
482 || pathipat 16-Dec-2002 Enh bug 2584741 - Deposits build - Modified call to update_row
483 || Added columns check_number, source_transaction_type and source_transaction_ref
484 || smadathi 20-NOV-2002 Enh. Bug 2584986. Modified igs_fi_credits_pkg.update_row
485 || to add new parameter GL Date
486 || sykrishn 14feb2002 SFCR020 build 2191470- Added 4 new params to update_row call for credits
487 || (reverse chronological order - newest change first) */
488
489 BEGIN
490 p_flag:=TRUE;
491 BEGIN
492 igs_fi_credits_pkg.update_row( X_ROWID => p_cur_credit.row_id,
493 X_CREDIT_ID => p_cur_credit.credit_id,
494 X_CREDIT_NUMBER => p_cur_credit.credit_number,
495 X_STATUS => p_cur_credit.status,
496 X_CREDIT_SOURCE => p_cur_credit.credit_source,
497 X_PARTY_ID => p_cur_credit.party_id,
498 X_CREDIT_TYPE_ID => p_cur_credit.credit_type_id,
499 X_CREDIT_INSTRUMENT => p_cur_credit.credit_instrument,
500 X_DESCRIPTION => p_cur_credit.description,
501 X_AMOUNT => p_cur_credit.amount,
502 X_CURRENCY_CD => p_cur_credit.currency_cd,
503 X_EXCHANGE_RATE => p_cur_credit.exchange_rate,
504 X_TRANSACTION_DATE => p_cur_credit.transaction_date,
505 X_EFFECTIVE_DATE => p_cur_credit.effective_date,
506 X_REVERSAL_DATE => p_cur_credit.reversal_date,
507 X_REVERSAL_REASON_CODE => p_cur_credit.reversal_reason_code,
508 X_REVERSAL_COMMENTS => p_cur_credit.reversal_comments,
509 X_UNAPPLIED_AMOUNT => p_unapplied_amount,
510 X_SOURCE_TRANSACTION_ID => p_cur_credit.source_transaction_id,
511 X_RECEIPT_LOCKBOX_NUMBER => p_cur_credit.receipt_lockbox_number,
512 X_MERCHANT_ID => p_cur_credit.merchant_id,
513 X_CREDIT_CARD_CODE => p_cur_credit.credit_card_code,
514 X_CREDIT_CARD_HOLDER_NAME => p_cur_credit.credit_card_holder_name,
515 X_CREDIT_CARD_NUMBER => p_cur_credit.credit_card_number,
516 X_CREDIT_CARD_EXPIRATION_DATE => p_cur_credit.credit_card_expiration_date,
517 X_CREDIT_CARD_APPROVAL_CODE => p_cur_credit.credit_card_approval_code,
518 X_AWD_YR_CAL_TYPE => p_cur_credit.awd_yr_cal_type,
519 X_AWD_YR_CI_SEQUENCE_NUMBER => p_cur_credit.awd_yr_ci_sequence_number,
520 X_FEE_CAL_TYPE => p_cur_credit.fee_cal_type ,
521 X_FEE_CI_SEQUENCE_NUMBER => p_cur_credit.fee_ci_sequence_number,
522 X_ATTRIBUTE_CATEGORY => p_cur_credit.attribute_category,
523 X_ATTRIBUTE1 => p_cur_credit.attribute1,
524 X_ATTRIBUTE2 => p_cur_credit.attribute2,
525 X_ATTRIBUTE3 => p_cur_credit.attribute3,
526 X_ATTRIBUTE4 => p_cur_credit.attribute4,
527 X_ATTRIBUTE5 => p_cur_credit.attribute5,
528 X_ATTRIBUTE6 => p_cur_credit.attribute6,
529 X_ATTRIBUTE7 => p_cur_credit.attribute7,
530 X_ATTRIBUTE8 => p_cur_credit.attribute8,
531 X_ATTRIBUTE9 => p_cur_credit.attribute9,
532 X_ATTRIBUTE10 => p_cur_credit.attribute10,
533 X_ATTRIBUTE11 => p_cur_credit.attribute11,
534 X_ATTRIBUTE12 => p_cur_credit.attribute12,
535 X_ATTRIBUTE13 => p_cur_credit.attribute13,
536 X_ATTRIBUTE14 => p_cur_credit.attribute14,
537 X_ATTRIBUTE15 => p_cur_credit.attribute15,
538 X_ATTRIBUTE16 => p_cur_credit.attribute16,
539 X_ATTRIBUTE17 => p_cur_credit.attribute17,
540 X_ATTRIBUTE18 => p_cur_credit.attribute18,
541 X_ATTRIBUTE19 => p_cur_credit.attribute19,
542 X_ATTRIBUTE20 => p_cur_credit.attribute20,
543 X_MODE => 'R',
544 X_GL_DATE => p_cur_credit.gl_date,
545 X_CHECK_NUMBER => p_cur_credit.check_number,
546 X_SOURCE_TRANSACTION_TYPE => p_cur_credit.source_transaction_type,
547 X_SOURCE_TRANSACTION_REF => p_cur_credit.source_transaction_ref,
548 x_credit_card_status_code => p_cur_credit.credit_card_status_code,
549 x_credit_card_payee_cd => p_cur_credit.credit_card_payee_cd,
550 x_credit_card_tangible_cd => p_cur_credit.credit_card_tangible_cd,
551 x_lockbox_interface_id => p_cur_credit.lockbox_interface_id,
552 x_batch_name => p_cur_credit.batch_name,
553 x_deposit_date => p_cur_credit.deposit_date,
554 x_source_invoice_id => p_cur_credit.source_invoice_id,
555 x_tax_year_code => p_cur_credit.tax_year_code,
556 x_waiver_name => p_cur_credit.waiver_name
557 );
558 EXCEPTION
559 WHEN OTHERS THEN
560 p_flag :=FALSE;
561 END;
562 END call_update_credits;
563
564
565 PROCEDURE application(p_credit_id IN igs_fi_credits.credit_id%TYPE,
566 p_invoice_id IN igs_fi_inv_int.invoice_id%TYPE,
567 p_amount_apply IN igs_fi_applications.amount_applied%TYPE,
568 p_cur_credit IN cur_credit%ROWTYPE,
569 p_cur_invoice IN cur_invoice%ROWTYPE,
570 p_dr_gl_ccid OUT NOCOPY igs_fi_cr_activities.dr_gl_ccid%TYPE,
571 p_cr_gl_ccid OUT NOCOPY igs_fi_cr_activities.cr_gl_ccid%TYPE,
572 p_dr_account_cd OUT NOCOPY igs_fi_cr_activities.dr_account_cd%TYPE,
573 p_cr_account_cd OUT NOCOPY igs_fi_cr_activities.cr_account_cd%TYPE,
574 p_application_id OUT NOCOPY igs_fi_applications.application_id%TYPE,
575 p_appl_hierarchy_id IN igs_fi_applications.appl_hierarchy_id%TYPE,
576 p_unapp_amount OUT NOCOPY igs_fi_credits_all.unapplied_amount%TYPE,
577 p_inv_amt_due OUT NOCOPY igs_fi_inv_int_all.invoice_amount_due%TYPE,
578 p_err_msg OUT NOCOPY fnd_new_messages.message_name%TYPE,
579 p_status OUT NOCOPY BOOLEAN,
580 p_d_gl_date IN DATE
581 )
582 AS
583 /*|| Created By :Sarakshi
584 || Created On :24-Jan-2002
585 || Purpose : For creating application .
586 || Known limitations, enhancements or remarks :
587 || Change History :
588 || Who When What
589 || vvutukur 20-Nov-2003 Bug#3249288.Modified the existing conditional check for a charge to be
590 || optional or not is modified to look at the OPTIONAL_FEE_FLAG column of
591 || charges table instead of the optional_payment_ind column of fee type table.
592 || pathipat 07-Jun-2003 Enh 2831584 - SS Enhancements build
593 || Modified call to call_update_charges() - Added check for optional_payment_ind
594 || and waiver flag
595 || smadathi 20-NOV-2002 Enh. Bug 2584986. Modified igs_fi_applications_pkg.insert_row
596 || to add new parameters GL Date , GL_POSTED_DATE, POSTING_CONTROL_ID
597 || (reverse chronological order - newest change first) */
598
599 CURSOR cur_cr_act IS
600 SELECT credit_activity_id
601 FROM igs_fi_cr_activities
602 WHERE credit_id = p_credit_id;
603 l_cur_cr_act cur_cr_act%ROWTYPE;
604
605 CURSOR cur_invln IS
606 SELECT invoice_lines_id
607 FROM igs_fi_invln_int
608 WHERE invoice_id = p_invoice_id;
609 l_cur_invln cur_invln%ROWTYPE;
610
611 l_rowid VARCHAR2(25):=NULL;
612 l_amount igs_fi_applications.amount_applied%TYPE;
613 l_status BOOLEAN :=TRUE;
614 l_appl_success BOOLEAN :=TRUE;
615 l_crd_upd_success BOOLEAN :=TRUE;
616 l_chg_upd_success BOOLEAN :=TRUE;
617
618 l_v_optional_fee_flag igs_fi_inv_int_all.optional_fee_flag%TYPE := NULL;
619
620 BEGIN
621 SAVEPOINT S1;
622 p_application_id:=NULL;
623 p_unapp_amount:=NULL;
624 p_inv_amt_due:=NULL;
625 p_status:=TRUE;
626 p_err_msg:=NULL;
627
628 --Fetch the credit activity id
629 OPEN cur_cr_act;
630 FETCH cur_cr_act INTO l_cur_cr_act;
631 CLOSE cur_cr_act;
632
633 --Fetch the invoice lines Id
634 OPEN cur_invln;
635 FETCH cur_invln INTO l_cur_invln;
636 CLOSE cur_invln;
637
638 -- Fetch the accounting Codes
639 get_appl_acc(p_cr_activity_id =>l_cur_cr_act.credit_activity_id,
640 p_invoice_lines_id =>l_cur_invln.invoice_lines_id,
641 p_dr_gl_ccid =>p_dr_gl_ccid,
642 p_cr_gl_ccid =>p_cr_gl_ccid,
643 p_dr_account_cd =>p_dr_account_cd,
644 p_cr_account_cd =>p_cr_account_cd,
645 p_status =>l_status);
646
647 IF l_status=FALSE THEN
648 p_status:=FALSE;
649 p_err_msg:='IGS_FI_APPL_ACC_ERR';
650 ELSE
651 --setting the amount applied in applications table
652 IF p_amount_apply IS NOT NULL THEN
653 l_amount:=p_amount_apply;
654 ELSE
655 IF p_cur_credit.unapplied_amount > p_cur_invoice.invoice_amount_due THEN
656 l_amount:=p_cur_invoice.invoice_amount_due;
657 ELSE
658 l_amount := p_cur_credit.unapplied_amount;
659 END IF;
660 END IF;
661
662 --Insert record into application table
663 -- Modified apply_date entry as Trunc(Sysdate) as part of Bug 4240402: Timezone impact
664 BEGIN
665 p_application_id:=NULL;
666 igs_fi_applications_pkg.insert_row( X_ROWID => l_rowid,
667 X_APPLICATION_ID => p_application_id,
668 X_APPLICATION_TYPE => g_app,
669 X_INVOICE_ID => p_invoice_id,
670 X_CREDIT_ID => p_credit_id,
671 X_CREDIT_ACTIVITY_ID => l_cur_cr_act.credit_activity_id,
672 X_AMOUNT_APPLIED => l_amount,
673 X_APPLY_DATE => TRUNC(SYSDATE),
674 X_LINK_APPLICATION_ID => NULL,
675 X_DR_ACCOUNT_CD => p_dr_account_cd,
676 X_CR_ACCOUNT_CD => p_cr_account_cd,
677 X_DR_GL_CODE_CCID => p_dr_gl_ccid,
678 X_CR_GL_CODE_CCID => p_cr_gl_ccid,
679 X_APPLIED_INVOICE_LINES_ID => l_cur_invln.invoice_lines_id,
680 X_APPL_HIERARCHY_ID => p_appl_hierarchy_id,
681 X_POSTING_ID => NULL,
682 X_MODE => 'R' ,
683 X_GL_DATE => TRUNC(p_d_gl_date),
684 X_GL_POSTED_DATE => NULL,
685 X_POSTING_CONTROL_ID => NULL
686 );
687 EXCEPTION
688 WHEN OTHERS THEN
689 l_appl_success:=FALSE;
690 END;
691
692 IF l_appl_success THEN
693 --update the record in the credits table, the unapplied amount
694 p_unapp_amount:=p_cur_credit.unapplied_amount - l_amount;
695 call_update_credits(p_unapp_amount,p_cur_credit,l_crd_upd_success);
696 END IF;
697
698
699 IF ((l_appl_success= TRUE) AND (l_crd_upd_success= TRUE)) THEN
700 --Update the charges table , invoice amount due column
701 p_inv_amt_due:= p_cur_invoice.invoice_amount_due - l_amount;
702
703 -- The existing conditional check is modified to look at the
704 -- OPTIONAL_FEE_FLAG column of charges table instead of the
705 -- optional_payment_ind column of fee type table.
706
707 IF (p_cur_invoice.optional_fee_flag = 'O') THEN
708 IF (p_cur_invoice.waiver_flag = 'Y') THEN
709 -- Pass 'D' - Declined - as value for parameter p_v_opt_flag
710 l_v_optional_fee_flag := 'D';
711 ELSIF (p_cur_invoice.waiver_flag = 'N') THEN
712 -- Pass 'A' - Accepted - as value for parameter p_v_opt_flag
713 l_v_optional_fee_flag := 'A';
714 END IF;
715 ELSIF (p_cur_invoice.optional_fee_flag = 'N') THEN
716 -- Pass null to p_v_opt_fee_flag since optional_fee_flag = 'N'
717 l_v_optional_fee_flag := NULL;
718 END IF;
719
720 call_update_charges( p_invoice_amount_due => p_inv_amt_due,
721 p_cur_invoice => p_cur_invoice,
722 p_v_opt_fee_flag => l_v_optional_fee_flag,
723 p_flag => l_chg_upd_success);
724 END IF;
725
726 IF ((l_appl_success=FALSE) OR (l_crd_upd_success=FALSE) OR (l_chg_upd_success=FALSE)) THEN
727 p_application_id:=NULL;
728 p_unapp_amount:=NULL;
729 p_inv_amt_due:=NULL;
730 p_status:=FALSE;
731 p_err_msg:='IGS_GE_UNHANDLED_EXCEPTION'; -- fnd_message.get was replaced by message name IGS_GE_UNHANDLED_EXCEPTION
732 ROLLBACK TO S1;
733 END IF;
734 END IF;
735
736 END application;
737
738 PROCEDURE unapplication(p_credit_id IN igs_fi_credits.credit_id%TYPE,
739 p_invoice_id IN igs_fi_inv_int.invoice_id%TYPE,
740 p_amount_apply IN igs_fi_applications.amount_applied%TYPE,
741 p_cur_credit IN cur_credit%ROWTYPE,
742 p_cur_invoice IN cur_invoice%ROWTYPE,
743 p_application_id IN OUT NOCOPY igs_fi_applications.application_id%TYPE,
744 p_unapp_amount OUT NOCOPY igs_fi_credits_all.unapplied_amount%TYPE,
745 p_inv_amt_due OUT NOCOPY igs_fi_inv_int_all.invoice_amount_due%TYPE,
746 p_err_msg OUT NOCOPY fnd_new_messages.message_name%TYPE,
747 p_status OUT NOCOPY BOOLEAN,
748 p_d_gl_date IN DATE
749 ) AS
750
751 /*|| Created By :Sarakshi
752 || Created On :24-Jan-2002
753 || Purpose : For creating unapplication .
754 || Known limitations, enhancements or remarks :
755 || Change History :
756 || Who When What
757 || pathipat 07-Jun-2003 Enh 2831584 - SS Enhancements build
758 || Modified call to call_update_charges()
759 || smadathi 20-NOV-2002 Enh. Bug 2584986. Modified igs_fi_applications_pkg.insert_row
760 || to add new parameters GL Date , GL_POSTED_DATE, POSTING_CONTROL_ID
761 || (reverse chronological order - newest change first) */
762
763
764 CURSOR cur_unapp IS
765 SELECT *
766 FROM igs_fi_applications
767 WHERE application_id = p_application_id;
768 l_cur_unapp cur_unapp%ROWTYPE;
769
770 l_unapp_amnt igs_fi_applications.amount_applied%TYPE;
771
772 l_rowid VARCHAR2(25);
773 l_application_id igs_fi_applications.application_id%TYPE ;
774 l_amount igs_fi_applications.amount_applied%TYPE;
775 l_appl_success BOOLEAN :=TRUE;
776 l_crd_upd_success BOOLEAN :=TRUE;
777 l_chg_upd_success BOOLEAN :=TRUE;
778 BEGIN
779 SAVEPOINT S2;
780 p_status:=TRUE;
781 p_err_msg:=NULL;
782 p_unapp_amount:=NULL;
783 p_inv_amt_due:=NULL;
784
785 OPEN cur_unapp;
786 FETCH cur_unapp INTO l_cur_unapp;
787 CLOSE cur_unapp;
788
789 l_unapp_amnt:=get_sum_appl_amnt(p_application_id);
790
791 IF l_unapp_amnt > 0 THEN
792 --Calculate the amount to be unapplied
793 IF p_amount_apply IS NULL THEN
794 l_amount := - l_unapp_amnt;
795 ELSE
796 IF p_amount_apply > l_unapp_amnt THEN
797 l_amount := - l_unapp_amnt;
798 ELSE
799 l_amount := - p_amount_apply;
800 END IF;
801 END IF;
802
803 --Insert the unapplication record
804 -- Modified apply_date entry as Trunc(Sysdate) as part of Bug 4240402: Timezone impact
805 BEGIN
806 l_rowid:=NULL;
807 l_application_id:=NULL;
808 igs_fi_applications_pkg.insert_row( X_ROWID => l_rowid,
809 X_APPLICATION_ID => l_application_id,
810 X_APPLICATION_TYPE => g_unapp,
811 X_INVOICE_ID => p_invoice_id,
812 X_CREDIT_ID => p_credit_id,
813 X_CREDIT_ACTIVITY_ID => l_cur_unapp.credit_activity_id,
814 X_AMOUNT_APPLIED => l_amount,
815 X_APPLY_DATE => TRUNC(SYSDATE),
816 X_LINK_APPLICATION_ID => l_cur_unapp.application_id,
817 X_DR_ACCOUNT_CD => l_cur_unapp.dr_account_cd,
818 X_CR_ACCOUNT_CD => l_cur_unapp.cr_account_cd,
819 X_DR_GL_CODE_CCID => l_cur_unapp.dr_gl_code_ccid,
820 X_CR_GL_CODE_CCID => l_cur_unapp.cr_gl_code_ccid,
821 X_APPLIED_INVOICE_LINES_ID => l_cur_unapp.applied_invoice_lines_id,
822 X_APPL_HIERARCHY_ID => l_cur_unapp.appl_hierarchy_id,
823 X_POSTING_ID => NULL,
824 X_MODE => 'R' ,
825 X_GL_DATE => TRUNC(p_d_gl_date),
826 X_GL_POSTED_DATE => NULL,
827 X_POSTING_CONTROL_ID => NULL
828 );
829 EXCEPTION
830 WHEN OTHERS THEN
831 l_appl_success:=FALSE;
832 END;
833
834 IF l_appl_success THEN
835 --Update the credits table with the unapplied amount
836 p_unapp_amount:=p_cur_credit.unapplied_amount - l_amount;
837 call_update_credits(p_unapp_amount,p_cur_credit,l_crd_upd_success);
838 IF l_crd_upd_success THEN
839 --Update the charges table with invoice amount due
840 p_inv_amt_due:=p_cur_invoice.invoice_amount_due - l_amount;
841 -- Pass NULL as value to parameter p_v_opt_fee_flag
842 call_update_charges( p_invoice_amount_due => p_inv_amt_due,
843 p_cur_invoice => p_cur_invoice,
844 p_v_opt_fee_flag => NULL,
845 p_flag => l_chg_upd_success);
846 END IF;
847 END IF;
848
849 p_application_id :=l_application_id;
850
851 IF ((l_appl_success=FALSE) OR (l_crd_upd_success=FALSE) OR (l_chg_upd_success=FALSE)) THEN
852 p_application_id:=NULL;
853 p_unapp_amount:=NULL;
854 p_inv_amt_due:=NULL;
855 p_status:=FALSE;
856 p_err_msg:='IGS_GE_UNHANDLED_EXCEPTION'; -- fnd_message.get was replaced by message name IGS_GE_UNHANDLED_EXCEPTION
857
858 ROLLBACK TO S2;
859 END IF;
860 ELSE
861 p_status:=FALSE;
862 p_err_msg:='IGS_FI_NO_APP_REC';
863 END IF;
864
865 END unapplication;
866
867
868 PROCEDURE get_appl_acc(p_cr_activity_id IN igs_fi_cr_activities.credit_activity_id%TYPE,
869 p_invoice_lines_id IN igs_fi_invln_int.invoice_lines_id%TYPE,
870 p_dr_gl_ccid OUT NOCOPY igs_fi_cr_activities.dr_gl_ccid%TYPE,
871 p_cr_gl_ccid OUT NOCOPY igs_fi_cr_activities.cr_gl_ccid%TYPE,
872 p_dr_account_cd OUT NOCOPY igs_fi_cr_activities.dr_account_cd%TYPE,
873 p_cr_account_cd OUT NOCOPY igs_fi_cr_activities.cr_account_cd%TYPE,
874 p_status OUT NOCOPY BOOLEAN) AS
875 /*|| Created By :Sarakshi
876 || Created On :23-Jan-2002
877 || Purpose :To derive the Accounting Information for the Application Record.
878 || Known limitations, enhancements or remarks :
879 || Change History :
880 || Who When What
881 || (reverse chronological order - newest change first) */
882
883 CURSOR cur_cr_act IS
884 SELECT dr_gl_ccid,cr_gl_ccid,dr_account_cd,cr_account_cd
885 FROM igs_fi_cr_activities
886 WHERE credit_activity_id = p_cr_activity_id;
887 l_cur_cr_act cur_cr_act%ROWTYPE;
888
889 CURSOR cur_inv IS
890 SELECT rec_gl_ccid,rev_gl_ccid,rec_account_cd,rev_account_cd
891 FROM igs_fi_invln_int
892 WHERE invoice_lines_id = p_invoice_lines_id
893 AND NVL(error_account,'N') <> 'Y';
894 l_cur_inv cur_inv%ROWTYPE;
895
896 CURSOR cur_class IS
897 SELECT ct.credit_class
898 FROM igs_fi_cr_types ct,
899 igs_fi_credits c,
900 igs_fi_cr_activities ca
901 WHERE ct.credit_type_id=c.credit_type_id
902 AND c.credit_id=ca.credit_id
903 AND ca.credit_activity_id=p_cr_activity_id;
904 l_cur_class cur_class%ROWTYPE;
905
906 l_rec_installed igs_fi_control.rec_installed%TYPE;
907 l_accounting_method igs_fi_control.accounting_method%TYPE;
908 BEGIN
909 p_dr_gl_ccid := NULL;
910 p_cr_gl_ccid := NULL;
911 p_dr_account_cd :=NULL;
912 p_cr_account_cd :=NULL;
913 p_status := TRUE;
914
915 IF ((p_cr_activity_id IS NULL) OR (p_invoice_lines_id IS NULL)) THEN
916 p_status:= FALSE;
917 END IF;
918
919 IF p_status THEN
920 --Fetching the accounting method
921 l_accounting_method := igs_fi_gen_005.finp_get_acct_meth;
922 IF l_accounting_method IS NULL THEN
923 p_status:= FALSE;
924 END IF;
925 END IF;
926
927 IF p_status THEN
928 --Fetch the accounting information from Credits Activities Table
929 OPEN cur_cr_act;
930 FETCH cur_cr_act INTO l_cur_cr_act;
931 IF cur_cr_act%NOTFOUND THEN
932 p_status:=FALSE;
933 END IF;
934 CLOSE cur_cr_act;
935 END IF;
936
937 IF p_status THEN
938 --Fetch the accounting information from Invoice Lines Table
939 OPEN cur_inv;
940 FETCH cur_inv INTO l_cur_inv;
941 IF cur_inv%NOTFOUND THEN
942 p_status := FALSE;
943 END IF;
944 CLOSE cur_inv;
945 END IF;
946
947 IF p_status THEN
948 --Fetching the Receivables installed
949 l_rec_installed := igs_fi_gen_005.finp_get_receivables_inst;
950 IF l_accounting_method = 'CASH' THEN
951 --Fetch the credit class information
952 OPEN cur_class;
953 FETCH cur_class INTO l_cur_class;
954 CLOSE cur_class;
955 IF l_rec_installed = g_yes THEN
956 p_dr_gl_ccid := l_cur_cr_act.cr_gl_ccid;
957 IF l_cur_class.credit_class = g_chg_adj THEN
958 p_cr_gl_ccid := l_cur_cr_act.dr_gl_ccid;
959 ELSE
960 p_cr_gl_ccid :=l_cur_inv.rev_gl_ccid;
961 END IF;
962 ELSIF l_rec_installed <> g_yes THEN
963 p_dr_account_cd := l_cur_cr_act.cr_account_cd;
964 IF l_cur_class.credit_class = g_chg_adj THEN
965 p_cr_account_cd := l_cur_cr_act.dr_account_cd;
966 ELSE
967 p_cr_account_cd := l_cur_inv.rev_account_cd;
968 END IF;
969 END IF;
970 ELSIF l_accounting_method = 'ACCRUAL' THEN
971 IF l_rec_installed = g_yes THEN
972 p_dr_gl_ccid := l_cur_cr_act.cr_gl_ccid;
973 p_cr_gl_ccid := l_cur_inv.rec_gl_ccid;
974 ELSIF l_rec_installed <> g_yes THEN
975 p_dr_account_cd :=l_cur_cr_act.cr_account_cd;
976 p_cr_account_cd := l_cur_inv.rec_account_cd;
977 END IF;
978 END IF;--End of accounting method CASH
979 END IF;--End of p_status
980
981 END get_appl_acc;
982
983 FUNCTION get_sum_appl_amnt(p_application_id IN igs_fi_applications.application_id%TYPE)
984 RETURN NUMBER AS
985 /*
986 || Created By :Sarakshi
987 || Created On :31-Jan-2002
988 || Purpose :To return the sum of amount applied for an application Id and its corresponding unapplication
989 || records ,if no record is found or parameter passed is null then return null
990 || Known limitations, enhancements or remarks :
991 || Change History :
992 || Who When What
993 || (reverse chronological order - newest change first)
994 || pmarada 14-Mar-2005 Bug 4224386, Instead of sum builtin, repeating in a loop to sum the unapply amount.
995 */
996
997 CURSOR cur_unapp_amnt IS
998 SELECT amount_applied FROM igs_fi_applications
999 WHERE (application_id = p_application_id AND application_type='APP')
1000 OR link_application_id=p_application_id;
1001
1002 l_cur_unapp_amnt igs_fi_applications.amount_applied%TYPE;
1003
1004 BEGIN
1005 --To return the sum of amount applied for an application Id and its corresponding unapplication
1006 --records ,if no record is found or parameter passed is null then return null
1007 IF p_application_id IS NULL THEN
1008 RETURN NULL;
1009 ELSE
1010 l_cur_unapp_amnt := 0;
1011 FOR cur_unapp_amnt_rec IN cur_unapp_amnt LOOP
1012 l_cur_unapp_amnt := l_cur_unapp_amnt + cur_unapp_amnt_rec.amount_applied;
1013 END LOOP;
1014 RETURN l_cur_unapp_amnt;
1015 END IF;
1016
1017 END get_sum_appl_amnt;
1018
1019
1020 PROCEDURE create_application (p_application_id IN OUT NOCOPY igs_fi_applications.application_id%TYPE,
1021 p_credit_id IN igs_fi_applications.credit_id%TYPE,
1022 p_invoice_id IN igs_fi_applications.invoice_id%TYPE,
1023 p_amount_apply IN igs_fi_applications.amount_applied%TYPE,
1024 p_appl_type IN igs_fi_applications.application_type%TYPE,
1025 p_appl_hierarchy_id IN igs_fi_applications.appl_hierarchy_Id%TYPE,
1026 p_validation IN VARCHAR2 ,
1027 p_dr_gl_ccid OUT NOCOPY igs_fi_cr_activities.dr_gl_ccid%TYPE,
1028 p_cr_gl_ccid OUT NOCOPY igs_fi_cr_activities.cr_gl_ccid%TYPE,
1029 p_dr_account_cd OUT NOCOPY igs_fi_cr_activities.dr_account_cd%TYPE,
1030 p_cr_account_cd OUT NOCOPY igs_fi_cr_activities.cr_account_cd%TYPE,
1031 p_unapp_amount OUT NOCOPY igs_fi_credits_all.unapplied_amount%TYPE,
1032 p_inv_amt_due OUT NOCOPY igs_fi_inv_int_all.invoice_amount_due%TYPE,
1033 p_err_msg OUT NOCOPY fnd_new_messages.message_name%TYPE,
1034 p_status OUT NOCOPY BOOLEAN,
1035 p_d_gl_date IN DATE
1036 ) AS
1037 /*|| Created By :Sarakshi
1038 || Created On :24-Jan-2002
1039 || Purpose : For creating application of credit against a charge.
1040 || Known limitations, enhancements or remarks :
1041 || Change History :
1042 || Who When What
1043 || smadathi 20-NOV-2002 Enh. Bug 2584986. Added new parameter GL Date
1044 || to procedure create_application. Added new parameter GL Date
1045 || to calls to application and unapplication procedures.
1046 || (reverse chronological order - newest change first) */
1047
1048 CURSOR cur_app IS
1049 SELECT credit_id,invoice_id
1050 FROM igs_fi_applications
1051 WHERE application_id=p_application_id;
1052
1053 l_cur_app cur_app%ROWTYPE;
1054 l_cur_credit cur_credit%ROWTYPE;
1055 l_cur_invoice cur_invoice%ROWTYPE;
1056 l_cur_credit_unapp cur_credit_unapp%ROWTYPE;
1057 l_cur_invoice_unapp cur_invoice_unapp%ROWTYPE;
1058
1059 l_b_flag BOOLEAN := FALSE;
1060 BEGIN
1061 p_status:=TRUE;
1062 p_err_msg:=NULL;
1063 p_dr_gl_ccid:=NULL;
1064 p_cr_gl_ccid:=NULL;
1065 p_dr_account_cd:=NULL;
1066 p_cr_account_cd:=NULL;
1067 p_unapp_amount:=NULL;
1068 p_inv_amt_due:=NULL;
1069
1070 --Validate the parameters. PROCEDURE validate_parameters will only be invoked
1071 --if p_validation parameter value is Y
1072 IF p_validation = 'Y' THEN
1073 validate_parameters(p_credit_id , p_invoice_id , p_amount_apply , p_appl_type ,
1074 p_application_id ,p_appl_hierarchy_id , p_err_msg , p_status,
1075 p_d_gl_date
1076 );
1077 END IF;
1078
1079 --If parameter validation is successful then only proceed
1080 IF p_status THEN
1081 IF p_appl_type = g_app THEN
1082 -- if the validate_parameters procedure returns the message IGS_FI_CHG_CRD_GL_DATE
1083 -- return status as true to p_status parameter and message to p_err_msg
1084 IF p_err_msg = 'IGS_FI_CHG_CRD_GL_DATE' THEN
1085 l_b_flag := TRUE;
1086 END IF;
1087
1088 --Get the credit record
1089 OPEN cur_credit(p_credit_id);
1090 FETCH cur_credit INTO l_cur_credit;
1091 CLOSE cur_credit;
1092 --Get the charge record
1093 OPEN cur_invoice(p_invoice_id);
1094 FETCH cur_invoice INTO l_cur_invoice;
1095 CLOSE cur_invoice;
1096
1097 application( p_credit_id => p_credit_id ,
1098 p_invoice_id => p_invoice_id ,
1099 p_amount_apply => p_amount_apply ,
1100 p_cur_credit => l_cur_credit ,
1101 p_cur_invoice => l_cur_invoice ,
1102 p_dr_gl_ccid => p_dr_gl_ccid,
1103 p_cr_gl_ccid => p_cr_gl_ccid,
1104 p_dr_account_cd => p_dr_account_cd,
1105 p_cr_account_cd => p_cr_account_cd,
1106 p_application_id => p_application_id,
1107 p_appl_hierarchy_id => p_appl_hierarchy_id,
1108 p_unapp_amount => p_unapp_amount,
1109 p_inv_amt_due => p_inv_amt_due,
1110 p_err_msg => p_err_msg ,
1111 p_status => p_status,
1112 p_d_gl_date => TRUNC(p_d_gl_date)
1113 );
1114 -- if the validate_parameters procedure returns the message IGS_FI_CHG_CRD_GL_DATE
1115 -- return status as true to p_status parameter and message to p_err_msg
1116 IF p_status AND (l_b_flag) THEN
1117 l_b_flag := FALSE;
1118 p_status := TRUE;
1119 p_err_msg := 'IGS_FI_CHG_CRD_GL_DATE';
1120 END IF;
1121
1122 ELSIF p_appl_type = g_unapp THEN
1123
1124 --Get the credit_id and invoice_id if not supplied that is possible for UNAPP only
1125 OPEN cur_app;
1126 FETCH cur_app INTO l_cur_app;
1127 CLOSE cur_app;
1128
1129 --Get the credit record
1130 OPEN cur_credit_unapp(l_cur_app.credit_id);
1131 FETCH cur_credit_unapp INTO l_cur_credit_unapp;
1132 CLOSE cur_credit_unapp;
1133 --Get the charge record
1134 OPEN cur_invoice_unapp(l_cur_app.invoice_id);
1135 FETCH cur_invoice_unapp INTO l_cur_invoice_unapp;
1136 CLOSE cur_invoice_unapp;
1137
1138 unapplication(p_credit_id => l_cur_app.credit_id ,
1139 p_invoice_id => l_cur_app.invoice_id ,
1140 p_amount_apply => p_amount_apply ,
1141 p_cur_credit => l_cur_credit_unapp ,
1142 p_cur_invoice => l_cur_invoice_unapp ,
1143 p_application_id => p_application_id,
1144 p_unapp_amount => p_unapp_amount,
1145 p_inv_amt_due => p_inv_amt_due,
1146 p_err_msg => p_err_msg ,
1147 p_status => p_status,
1148 p_d_gl_date => TRUNC(p_d_gl_date)
1149 );
1150 END IF;
1151 END IF;
1152
1153 IF p_status =FALSE THEN
1154 p_application_id:=NULL;
1155 END IF;
1156
1157 END create_application;
1158
1159 FUNCTION validate_person(p_person_id igs_pe_person.person_id%TYPE) RETURN VARCHAR2 IS
1160 /*|| Created By :Sarakshi
1161 || Created On :27-Feb-2002
1162 || Purpose : For validating the input person_id, for the person_type of PERSON,ORGANIZATION.
1163 || If record is found in igs_fi_parties_v then it returns 'Y'else 'N'.
1164 || Known limitations, enhancements or remarks :
1165 || Change History :
1166 || Who When What
1167 || sapanigr 09-Feb-2006 Bug 5018036: Cursor cur_person now uses hz_parties instead of igs_fi_parties_v
1168 || (reverse chronological order - newest change first)
1169 */
1170 CURSOR cur_person IS
1171 SELECT 'X'
1172 FROM hz_parties
1173 WHERE party_id=p_person_id;
1174 l_var VARCHAR2(1);
1175 BEGIN
1176 IF p_person_id IS NULL THEN
1177 RETURN 'N';
1178 END IF;
1179 OPEN cur_person;
1180 FETCH cur_person INTO l_var;
1181 IF cur_person%FOUND THEN
1182 CLOSE cur_person;
1183 RETURN 'Y';
1184 ELSE
1185 CLOSE cur_person;
1186 RETURN 'N';
1187 END IF;
1188 END validate_person;
1189
1190 FUNCTION get_ccid_concat(p_ccid IN NUMBER) RETURN VARCHAR2 AS
1191 /*|| Created By : agairola
1192 || Created On :10-Apr-2002
1193 || Purpose : For fetching the Concatenated Segments for the Code Combination Id passed as
1194 || input to the function
1195 || Known limitations, enhancements or remarks :
1196 || Change History :
1197 || Who When What
1198 || (reverse chronological order - newest change first) */
1199
1200 -- Cursor for fetching the Concatenated Segments for the CCID passed as input to the function
1201 CURSOR cur_gl_ccid(cp_ccid NUMBER) IS
1202 SELECT concatenated_segments
1203 FROM gl_code_combinations_kfv
1204 WHERE code_combination_id = cp_ccid;
1205
1206 l_ccid_concat gl_code_combinations_kfv.concatenated_segments%TYPE;
1207 BEGIN
1208
1209 -- If the p_ccid is passed as NULL, then return NULL
1210 IF p_ccid IS NULL THEN
1211 l_ccid_concat := NULL;
1212
1213 -- Else
1214 ELSE
1215
1216 -- Fetch the Concatenated Segments from the GL_CODE_COMBINATIONS_KFV view
1217 OPEN cur_gl_ccid(p_ccid);
1218 FETCH cur_gl_ccid INTO l_ccid_concat;
1219 IF cur_gl_ccid%NOTFOUND THEN
1220 l_ccid_concat := NULL;
1221 END IF;
1222 CLOSE cur_gl_ccid;
1223 END IF;
1224
1225 -- Return the value set for the l_ccid_concat
1226 RETURN l_ccid_concat;
1227 END get_ccid_concat;
1228
1229 FUNCTION get_person_id_type
1230 RETURN VARCHAR2 IS
1231 /*|| Created By :Sarakshi
1232 || Created On :13-JUN-2002
1233 || Purpose : For getting the person id type which is preffered .
1234 || Known limitations, enhancements or remarks :
1235 || Change History :
1236 || Who When What
1237 || smadathi 01-jan-2003 Bug 2713272. Modified the cursor cur_pref_person to select
1238 || from igs_pe_person_id_typ instead of the igs_pe_person_id_typ_v.
1239 || This is done due to Non-Meargabile view and higher value of shared memory
1240 || beyong the acceptable limit
1241 || (reverse chronological order - newest change first) */
1242 CURSOR cur_pref_person IS
1243 SELECT person_id_type
1244 FROM igs_pe_person_id_typ
1245 WHERE preferred_ind='Y';
1246 l_person_id_type igs_pe_person_id_typ.person_id_type%TYPE;
1247 BEGIN
1248 OPEN cur_pref_person;
1249 FETCH cur_pref_person INTO l_person_id_type;
1250 CLOSE cur_pref_person;
1251 RETURN l_person_id_type;
1252 END get_person_id_type;
1253
1254
1255 PROCEDURE finp_get_conv_prc_run_ind(p_n_conv_process_run_ind OUT NOCOPY igs_fi_control.conv_process_run_ind%TYPE,
1256 p_v_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE) AS
1257
1258 /*|| Created By : PATHIPAT
1259 || Created On : 02-OCT-2002
1260 || Purpose : For getting the value of the conv_process_run_ind which indicates
1261 || whether the holds conversion process is running or not
1262 || Known limitations, enhancements or remarks :
1263 || Change History :
1264 || Who When What
1265 || pathipat 23-Dec-02 Bug: 2723531 - Returned 0 if the value of the
1266 || column conv_process_run_ind is NULL in the table
1267 || (reverse chronological order - newest change first) */
1268
1269 CURSOR cur_prc_run_ind IS
1270 SELECT conv_process_run_ind
1271 FROM igs_fi_control_all ;
1272
1273 l_prc_run_ind igs_fi_control_all.conv_process_run_ind%TYPE;
1274
1275 BEGIN
1276
1277 OPEN cur_prc_run_ind;
1278 FETCH cur_prc_run_ind INTO l_prc_run_ind;
1279 IF cur_prc_run_ind%NOTFOUND THEN
1280 p_n_conv_process_run_ind := NULL;
1281 p_v_message_name := 'IGS_FI_SYSTEM_OPT_SETUP';
1282 CLOSE cur_prc_run_ind;
1283 RETURN;
1284 END IF;
1285
1286 CLOSE cur_prc_run_ind;
1287
1288 IF l_prc_run_ind IS NULL THEN
1289 l_prc_run_ind := 0;
1290 END IF;
1291
1292 p_n_conv_process_run_ind := l_prc_run_ind;
1293 p_v_message_name := NULL;
1294 RETURN;
1295
1296 END finp_get_conv_prc_run_ind;
1297
1298
1299 PROCEDURE finp_get_balance_rule (p_v_balance_type IN igs_fi_balance_rules.balance_name%TYPE,
1300 p_v_action IN VARCHAR2,
1301 p_n_balance_rule_id OUT NOCOPY igs_fi_balance_rules.balance_rule_id%TYPE,
1302 p_d_last_conversion_date OUT NOCOPY igs_fi_balance_rules.last_conversion_date%TYPE,
1303 p_n_version_number OUT NOCOPY igs_fi_balance_rules.version_number%TYPE ) AS
1304 /*|| Created By : PATHIPAT
1305 || Created On : 02-OCT-2002
1306 || Purpose : For getting the balance rule defined in the system for an input balance type
1307 || Known limitations, enhancements or remarks :
1308 || Change History :
1309 || Who When What
1310 || (reverse chronological order - newest change first) */
1311
1312 -- Cursor to get the balance rule details when the p_v_action is 'ACTIVE'
1313 CURSOR cur_get_active(cp_balance_type igs_fi_balance_rules.balance_name%TYPE) IS
1314 SELECT balance_rule_id,
1315 version_number,
1316 last_conversion_date
1317 FROM IGS_FI_BALANCE_RULES
1318 WHERE balance_name = cp_balance_type
1319 AND last_conversion_date IS NOT NULL
1320 ORDER BY version_number DESC;
1321
1322 -- Cursor to get the balance rule details when the p_v_action is 'MAX'
1323 CURSOR cur_get_max(cp_balance_type igs_fi_balance_rules.balance_name%TYPE) IS
1324 SELECT balance_rule_id,
1325 version_number,
1326 last_conversion_date
1327 FROM IGS_FI_BALANCE_RULES
1328 WHERE balance_name = cp_balance_type
1329 ORDER BY version_number DESC;
1330
1331 -- Cursor to check if the input balance type is a valid lookup code or not
1332 CURSOR cur_balance_type(cp_balance_type igs_fi_balance_rules.balance_name%TYPE) IS
1333 SELECT *
1334 FROM igs_lookup_values
1335 WHERE lookup_type = 'IGS_FI_BALANCE_TYPE'
1336 AND lookup_code = cp_balance_type
1337 AND lookup_code IN ('HOLDS','FEE')
1338 AND enabled_flag = 'Y'
1339 AND ( (start_date_active < TRUNC(SYSDATE))
1340 AND
1341 (end_date_active IS NULL OR end_date_active > TRUNC(SYSDATE))
1342 );
1343
1344 l_cur_get_active cur_get_active%ROWTYPE;
1345 l_cur_get_max cur_get_max%ROWTYPE;
1346 l_cur_get_bal cur_balance_type%ROWTYPE;
1347
1348 BEGIN
1349
1350 OPEN cur_balance_type(p_v_balance_type);
1351 FETCH cur_balance_type INTO l_cur_get_bal;
1352 -- 1
1353 IF cur_balance_type%FOUND THEN
1354 IF p_v_action = 'ACTIVE' THEN -- (2)
1355 IF p_v_balance_type = 'FEE' THEN
1356 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1357 app_exception.raise_exception; -- raise exception and return
1358 END IF;
1359 OPEN cur_get_active(p_v_balance_type);
1360 FETCH cur_get_active INTO l_cur_get_active;
1361 IF (cur_get_active%FOUND) THEN -- IF (3)
1362 p_n_balance_rule_id := l_cur_get_active.balance_rule_id;
1363 p_d_last_conversion_date := l_cur_get_active.last_conversion_date;
1364 p_n_version_number := l_cur_get_active.version_number;
1365 CLOSE cur_get_active;
1366 RETURN;
1367 END IF; -- (3)
1368 CLOSE cur_get_active;
1369
1370 ELSIF p_v_action = 'MAX' THEN -- (2)
1371
1372 OPEN cur_get_max(p_v_balance_type);
1373 FETCH cur_get_max INTO l_cur_get_max;
1374 IF (cur_get_max%FOUND) THEN -- (4)
1375 p_n_balance_rule_id := l_cur_get_max.balance_rule_id;
1376 p_d_last_conversion_date := l_cur_get_max.last_conversion_date;
1377 p_n_version_number := l_cur_get_max.version_number;
1378 CLOSE cur_get_max;
1379 RETURN;
1380 END IF; -- (4)
1381 CLOSE cur_get_max;
1382 ELSE -- if the parameter is not 'ACTIVE' or 'MAX', then raise exception
1383 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1384 app_exception.raise_exception;
1385 END IF; --- (2)
1386
1387 CLOSE cur_balance_type;
1388 ELSE
1389 -- No data found in the cursor
1390 CLOSE cur_balance_type;
1391 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1392 app_exception.raise_exception;
1393 END IF; -- 1
1394
1395 p_n_balance_rule_id := NULL;
1396 p_d_last_conversion_date := NULL;
1397 p_n_version_number := 0;
1398 RETURN;
1399
1400 END finp_get_balance_rule;
1401
1402 END igs_fi_gen_007;
[Home] [Help]