1 PACKAGE BODY igs_fi_charges_api_pvt AS
2 /* $Header: IGSFI53B.pls 120.24 2006/06/27 14:17:12 skharida ship $ */
3 ------------------------------------------------------------------
4 --Known limitations/enhancements and/or remarks:
5 --
6 --Change History:
7 --Who When What
8 --skharida 26-Jun-2006 Bug 5208136 - Modified procedure create_charge, removed the obsoleted columns of the IGS_FI_INV_INT_PKG
9 --akandreg 20-Jun-2006 Bug 5116519 - The Message 'IGS_FI_INVALID_FTCI' is modified .The old token FEE_CAL_TYPE
10 -- is replaced by new token CI_DESC and passed fee CI description to that. Also the cursor
11 -- cur_alt_cd_desc is modified to select another column igs_ca_inst_all.description.
12 --pathipat 12-Jun-2006 Bug 5306868 - Modified create_charge - charge adjustment logic revamped
13 --sapanigr 29-May-2006 Bug 5251760 Modified create_charge. Added new local function chk_charge_adjusted.
14 --svuppala 05-May-2006 Bug 3924836 Precision Issue. Modified create_charge and validate_neg_amt
15 --sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified create_charge
16 --sapanigr 24-Feb-2006 Bug 5018036 - Removed cursor 'cur_ret' and cursor variable 'l_ret_rec'in procedure 'create_charge'.
17 --sapanigr 09-Feb-2006 Bug 5018036 - Modified query procedure 'create_charge' for R12 Repository tuning issue.
18 --abshriva 24-Oct-2005 Bug 4680553-Modification made in procedure create_charge
19 --pathipat 05-Oct-2005 Bug 4383148 - Fees not assessed if attendance type cannot be derived
20 -- Removed functions validate_atd_mode and validate_atd_type and their invocation
21 --gurprsin 13-Sep-2005 Bug 3627209, Modified existing logic as to return the new message in case if there is no credit type defined
22 -- for negative charge adjustment credit class.
23 --svuppala 07-JUL-2005 Enh 3392095 - Tution Waivers build
24 -- Modified HEADER_REC_TYPE -- included waiver_name.
25 -- Modified create_charge
26 --pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
27 -- parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
28 -- svuppala 9-JUN-2005 Enh 4213629 - Impact of automatic generation of the Receipt Number
29 -- changed logic for l_v_credit_number in procedure proc_neg_chg.
30 --gurprsin 03-Jun-2005 Enh# 3442712 Modified call to igs_fi_prc_acct_pkg.build_accounts and igs_fi_invln_int_pkg.insert_row methods.
31 -- svuppala 05-APR-2005 Bug# "4240402" Additional fix done as part of Time Zone impact
32 -- Changed the negative charge adjustment logic with ORDER BY application_id
33 -- instead of APPLY_DATE as time part of it is truncated.
34 --pathipat 30-Sep-2004 Bug 3908040 - Modified proc_neg_chg() - Removed check on Manage Accounts = Other before application/unapplication
35 --uudayapr 10-mar-2004 Bug#3478599,modified create_charge procedure
36 --shtatiko 10-DEC-2003 Bug# 3288973, modified call_build_process.
37 --vvutukur 27-Jun-2003 Bug#2849185.Modified create_charge procedure.
38 --jbegum 20-Jun-2003 Bug# 2998266, NEXT_INVOICE_NUMBER in the IGS_FI_CONTROL table will not be used for
39 -- generating unique charge numbers. Next Value from a DB sequence will be used for
40 -- for generating unique charges numbers.
41 --vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Modified proc_neg_chg,create_charge.
42 --vvutukur 26-May-2003 Enh#2831572.Financial Accounting Build. Modified procedure validate_course,
43 --shtatiko 05-MAY-2003 Enh# 2831569, Modified proc_neg_chg and create_charge
44 --pathipat 14-Apr-2003 Enh 2831569 - Commercial Receivables Interface build
45 -- Modified call to igs_fi_control_pkg.update_row in proc create_charge()
46 --vvutukur 05-Apr-2003 Enh#2831554.Internal Credits API Build.Removed procedure validate_lkup,modified proc_neg_chg,create_charge.
47 --agairola 11-Mar-2003 Bug 2762740: Modified validate_neg_amnt and create_charge
48 --vchappid 03-Mar-2003 Bug: 2820197, In functions validate_ftci, validate_fcfl,Fee Structure Status validation
49 -- should be bypassed when the transaction type is either Assessment or Retention
50 --smadathi 18-Feb-2002 Enh. Bug 2747329.Modified create_charge procedure.
51 --vvutukur 12-Dec-2002 Enh#2584741.Deposits Build. Modified procedure proc_neg_chg.
52 --pathipat 14-Nov-2002 Enh 2584986 -
53 -- 1. Modified create_charge(), added parameter p_d_gl_date
54 -- in call to igs_fi_invln_int.insert_row().
55 -- 2. Modified proc_neg_chg(), added p_gl_date and p_currency_cd as IN parameter. Passed gl_date
56 -- in calls to create_application() and create_credit() in proc_neg_chg
57 -- 3. Removed local procedure get_local_amount() - exchange_rate = 1 always.
58 -- 4. Modified function validate_cur()
59 --vvutukur 30-Sep-2002 Enh#2562745.Modified create_charge procedure.
60 --vvutukur 23-Sep-2002 Enh#2564643.removed the references to subaccount_id. ie.,removed
61 -- function validate_subaccount.Modified procedures proc_neg_chg,
62 -- create_charge.
63 --smadathi 03-Jul-2002 Bug 2443082. Modified create_charge procedure.
64 --agairola 10-Jun-2002 Bug Number: 2407624 Modified get_local_amount and create_charge
65 --agairola 17-May-2002 Modified the Call_Build_Accounts procedure and Create Charge procedure for
66 -- bug 2323555
67 -- agairola 30-Apr-2002 Added the fee structure status to the query and equated the s_fee_structure_status to ACTIVE
68 -- for bug fix 2348883 in validate_ftci and validate_fcfl
69 --SYkrishn 15/APR/2002 Included column planned_credits_ind in the update_row call of IGS_FI_CONTROL_PKG
70 -- as part of ENh SFCR018 Build 2293676
71 --smvk 08-Mar-2002 Updated the call to igs_fi_control_pkg as per the Bug No 2144600
72 --vvutukur 27-02-2002 removed local function validate_person and placed call to igs_fi_gen_007.validate_person
73 -- in create_charge procedure.for bug:2238362
74 --jbegum 20-Feb-02 As part Enh bug#2228910
75 -- Removed source_transaction_id column from calls to
76 -- IGS_FI_INV_INT_PKG.insert_row and IGS_FI_INVLN_INT_PKG.insert_row
77 --vvutukur 18-feb-2002 added ar_int_org_id column in call to igs_fi_control_pkg
78 -- for bug:2222272.
79 --jbegum 14-Feb-2001 As part of Enh bug # 2201081
80 -- Added call to IGS_FI_GEN_005.validate_psa and IGS_FI_PARTY_SA_PKG.insert_row
81 -- Removed Cursor cur_psa
82 --sykrishn 14feb2002 SFCR020 Build - 2191470 - Added the 4 new params to Credits API call.
83 --vchappid 20-Jan-2002 Enh # 2162747, Modified igs_fi_control table, introduced two columns
84 --masehgal 15-Jan-2002 Enh # 2170429
85 -- Obsoletion of SPONSOR_CD
86 --sarakshi 18-dec-2001 removed the parameters p_source_date,p_fee_type,p_credit_type_id from
87 -- the call to procedure Update_Balances and added parameter p_source_id
88 -- as a part of Enh. bug:2124001
89 --smadathi 12-oct-2001 As part of enhancement bug#2042716 , create_charge
90 -- procedure modified .
91 --nalkumar 19-Dec-2001 Changed the call to IGS_FI_PARTY_SUBACTS_PKG.insert_row.
92 -- This is as per the SF015 Holds DLD. Bug# 2126091.
93 --agairola 12-Feb-2002 Added the functionality for negative charge creation for
94 -- SFCR003 DLD Bug No 2195715
95 ------------------------------------------------------------------
96 g_active CONSTANT VARCHAR2(10) := 'ACTIVE';
97 g_ind_no CONSTANT VARCHAR2(1) := 'N';
98 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_FI_CHARGES_API_PVT';
99 g_transaction_type CONSTANT VARCHAR2(20) := 'TRANSACTION_TYPE';
100 g_chg_method CONSTANT VARCHAR2(15) := 'CHG_METHOD';
101 g_app CONSTANT VARCHAR2(5) := 'APP';
102 g_unapp CONSTANT VARCHAR2(5) := 'UNAPP';
103 g_ind_yes CONSTANT VARCHAR2(1) := 'Y';
104 g_standard CONSTANT VARCHAR2(10) := 'STANDARD';
105 g_charge CONSTANT VARCHAR2(10) := 'CHARGE';
106 g_neg_cr_class CONSTANT VARCHAR2(6) := 'CHGADJ';
107 g_null CONSTANT VARCHAR2(6) := NULL;
108 g_cleared CONSTANT VARCHAR2(30) := 'CLEARED';
109 g_adj CONSTANT VARCHAR2(30) := 'ADJ';
110 g_crd_char CONSTANT VARCHAR2(5) := '-';
111
112 g_c_assessment CONSTANT VARCHAR2(30) := 'ASSESSMENT';
113 g_c_retention CONSTANT VARCHAR2(30) := 'RETENTION';
114
115 g_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
116 g_curr_cd igs_fi_control.currency_cd%TYPE;
117 g_v_manage_accounts igs_fi_control_all.manage_accounts%TYPE;
118
119 FUNCTION chk_charge_adjusted(p_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
120 p_n_inv_amt IN igs_fi_inv_int_all.invoice_amount%TYPE) RETURN BOOLEAN;
121
122 FUNCTION validate_ftci(p_c_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
123 p_n_fee_ci_sequence_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
124 p_c_fee_type IN igs_fi_f_typ_ca_inst.fee_type%TYPE,
125 p_c_transaction_type IN igs_lookup_values.lookup_code%TYPE) RETURN BOOLEAN AS
126 /***********************************************************************************************
127
128 Created By: Amit Gairola
129
130 Date Created By: 03-05-2001
131
132 Purpose: This function validates the Fee Type Calendar Instance.
133
134 Known limitations,enhancements,remarks:
135
136 Change History
137
138 Who When What
139 vchappid 03-Mar-2003 Bug: 2820197, Fee Structure Status validation should be bypassed when the transaction type is
140 either Assessment or Retention
141 agairola 30-Apr-2002 added the fee structure status to the query and equated the s_fee_structure_status to ACTIVE
142 for bug fix 2348883
143
144 ********************************************************************************************** */
145 l_temp VARCHAR2(1);
146 l_bool BOOLEAN;
147
148 -- Cursor for validating the Fee Type Calendar Instance for active FTCI
149 CURSOR cur_ftci(cp_c_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
150 cp_n_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
151 cp_c_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
152 cp_c_status VARCHAR2,
153 cp_c_transaction_type igs_lookup_values.lookup_code%TYPE) IS
154 SELECT 'x'
155 FROM igs_fi_f_typ_ca_inst ftci,
156 igs_fi_fee_str_stat fsst
157 WHERE ftci.fee_cal_type = cp_c_fee_cal_type
158 AND ftci.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
159 AND ftci.fee_type = cp_c_fee_type
160 AND ftci.fee_type_ci_status = fsst.fee_structure_status
161 AND (
162 (fsst.s_fee_structure_status = cp_c_status
163 AND
164 cp_c_transaction_type NOT IN (g_c_assessment,g_c_retention)
165 )
166 OR
167 (cp_c_transaction_type IN (g_c_assessment,g_c_retention))
168 );
169 BEGIN
170 OPEN cur_ftci(p_c_fee_cal_type,
171 p_n_fee_ci_sequence_number,
172 p_c_fee_type,
173 g_active,
174 p_c_transaction_type);
175 FETCH cur_ftci INTO l_temp;
176 IF cur_ftci%NOTFOUND THEN
177 l_bool := FALSE;
178 ELSE
179 l_bool := TRUE;
180 END IF;
181 CLOSE cur_ftci;
182
183 RETURN l_bool;
184
185 END validate_ftci;
186
187 FUNCTION validate_fcfl(p_c_fee_cat IN igs_fi_f_cat_fee_lbl.fee_cat%TYPE,
188 p_c_fee_type IN igs_fi_f_cat_fee_lbl.fee_type%TYPE,
189 p_c_transaction_type IN igs_lookup_values.lookup_code%TYPE) RETURN BOOLEAN AS
190
191 /***********************************************************************************************
192
193 Created By: Amit Gairola
194
195 Date Created By: 03-05-2001
196
197 Purpose: This function validates the Fee Category Fee Liability.
198
199 Known limitations,enhancements,remarks:
200
201 Change History
202
203 Who When What
204 vchappid 03-Mar-2003 Bug: 2820197, Fee Structure Status validation should be bypassed when the transaction type is
205 either Assessment or Retention
206 agairola 30-Apr-2002 added the fee structure status to the query and equated the s_fee_structure_status to ACTIVE
207 for bug fix 2348883
208 ********************************************************************************************** */
209 l_temp VARCHAR2(1);
210 l_bool BOOLEAN;
211
212
213 -- Cursor for validating the Fee Category Fee Liability
214 CURSOR cur_fcfl(cp_c_fee_cat igs_fi_f_cat_fee_lbl.fee_cat%TYPE,
215 cp_c_fee_type igs_fi_f_cat_fee_lbl.fee_type%TYPE,
216 cp_c_status VARCHAR2,
217 cp_c_transaction_type igs_lookup_values.lookup_code%TYPE) IS
218 SELECT 'x'
219 FROM igs_fi_f_cat_fee_lbl fcfl,
220 igs_fi_fee_str_stat fsst
221 WHERE fcfl.fee_cat = cp_c_fee_cat
222 AND fcfl.fee_type = cp_c_fee_type
223 AND fcfl.fee_liability_status = fsst.fee_structure_status
224 AND (
225 (fsst.s_fee_structure_status = cp_c_status
229 OR
226 AND
227 cp_c_transaction_type NOT IN (g_c_assessment,g_c_retention)
228 )
230 (cp_c_transaction_type IN (g_c_assessment,g_c_retention))
231 );
232
233 BEGIN
234 OPEN cur_fcfl(p_c_fee_cat,
235 p_c_fee_type,
236 g_active,
237 p_c_transaction_type);
238 FETCH cur_fcfl INTO l_temp;
239 IF cur_fcfl%NOTFOUND THEN
240 l_bool := FALSE;
241 ELSE
242 l_bool := TRUE;
243 END IF;
244 CLOSE cur_fcfl;
245
246 RETURN l_bool;
247 END validate_fcfl;
248
249 FUNCTION validate_course(p_course_cd IN igs_ps_ver.course_cd%TYPE) RETURN BOOLEAN AS
250
251 /***********************************************************************************************
252
253 Created By: Amit Gairola
254
255 Date Created By: 03-05-2001
256
257 Purpose: This function validates the Program.
258
259 Known limitations,enhancements,remarks:
260
261 Change History
262
263 Who When What
264 vvutukur 26-May-2003 Bug#2869357.Modified cursor cur_course.
265 ********************************************************************************************** */
266 l_temp VARCHAR2(1);
267 l_bool BOOLEAN;
268
269 -- Cursor for validating the program
270 CURSOR cur_course(cp_course_cd igs_ps_ver.course_cd%TYPE,
271 cp_status VARCHAR2) IS
272 SELECT 'x'
273 FROM igs_ps_ver pv, igs_ps_stat ps
274 WHERE pv.course_cd = cp_course_cd
275 AND pv.course_status = ps.course_status
276 AND ps.s_course_status = cp_status;
277
278 BEGIN
279
280 OPEN cur_course(p_course_cd,g_active);
281 FETCH cur_course INTO l_temp;
282 IF cur_course%NOTFOUND THEN
283 l_bool := FALSE;
284 ELSE
285 l_bool := TRUE;
286 END IF;
287 CLOSE cur_course;
288
289 RETURN l_bool;
290 END validate_course;
291
292 --removed function validate_subaccount as part of subaccount removal build. enh#2564643.
293 --removed function validate_lkup as part of Internal Credits API build. enh#2831554.
294
295
296 FUNCTION validate_cur(p_currency_cd IN igs_fi_control.currency_cd%TYPE) RETURN BOOLEAN AS
297
298 /***********************************************************************************************
299
300 Created By: Amit Gairola
301
302 Date Created By: 03-05-2001
303
304 Purpose: This function validates the currency code.
305
306 Known limitations,enhancements,remarks:
307
308 Change History
309
310 Who When What
311 pathipat 16-Nov-2002 Enh Bug: 2584986: Replaced cursor selecting from igs_fi_cur with generic
312 function to get the currency_cd set in igs_fi_control
313 ********************************************************************************************** */
314 l_bool BOOLEAN;
315 l_v_message_name fnd_new_messages.message_name%TYPE;
316 l_v_curr_desc fnd_currencies_tl.name%TYPE;
317
318 BEGIN
319
320 -- Call generic function to get the currency_cd setup in igs_fi_control
321 igs_fi_gen_gl.finp_get_cur( p_v_currency_cd => g_curr_cd,
322 p_v_curr_desc => l_v_curr_desc,
323 p_v_message_name => l_v_message_name
324 );
325 IF l_v_message_name IS NULL THEN
326 -- If the currency_cd passed is not same as currency_cd setup in the System options form
327 -- then error is given
328 IF g_curr_cd <> p_currency_cd THEN
329 l_bool := FALSE;
330 ELSE
331 l_bool := TRUE;
332 END IF;
333 ELSE
334 l_bool := FALSE;
335 END IF;
336
337 RETURN l_bool;
338
339 END validate_cur;
340
341 FUNCTION validate_uoo(p_uoo_id IN igs_ps_unit_ofr_opt.uoo_id%TYPE) RETURN BOOLEAN AS
342
343 /***********************************************************************************************
344
345 Created By: Amit Gairola
346
347 Date Created By: 03-05-2001
348
349 Purpose: This function validates the Unit Section .
350
351 Known limitations,enhancements,remarks:
352
353 Change History
354
355 Who When What
356 jbegum 24-Sep-01 The function has been modified to validate the uoo_id instead of the six UOO columns
357 ie. unit_cd , unit_version_number, unit_location_cd , cal_type ,
358 ci_sequence_number and unit_class .
359 Hence the function takes only the uoo_id as input parameter and not all the six UOO
360 columns , as was the case earlier.
361 This change was carried out as part of bug #1962286
362 ************************************************************************************************/
363 l_temp VARCHAR2(1);
364 l_bool BOOLEAN;
365
366 -- Cursor for validating the unit section
367 CURSOR cur_uoo(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE ) IS
368 SELECT 'x'
369 FROM igs_ps_unit_ofr_opt
370 WHERE uoo_id = cp_uoo_id;
371
372 BEGIN
373
377 l_bool := FALSE;
374 OPEN cur_uoo(p_uoo_id);
375 FETCH cur_uoo INTO l_temp;
376 IF cur_uoo%NOTFOUND THEN
378 ELSE
379 l_bool := TRUE;
380 END IF;
381 CLOSE cur_uoo;
382
383 RETURN l_bool;
384
385 END validate_uoo;
386
387 FUNCTION validate_org_unit_cd(p_org_unit_cd IN igs_or_unit.org_unit_cd%TYPE) RETURN BOOLEAN AS
388
389 /***********************************************************************************************
390
391 Created By: Amit Gairola
392
393 Date Created By: 03-05-2001
394
395 Purpose: This function validates the Org Unit Code .
396
397 Known limitations,enhancements,remarks:
398
399 Change History
400
401 Who When What
402
403 ********************************************************************************************** */
404 l_temp VARCHAR2(1);
405 l_bool BOOLEAN;
406
407 -- Cursor for validating the Org_Unit_Code
408 CURSOR cur_org_unit(cp_org_unit_cd igs_or_unit.org_unit_cd%TYPE) IS
409 SELECT 'x'
410 FROM igs_or_unit
411 WHERE org_unit_cd = cp_org_unit_cd;
412 BEGIN
413 OPEN cur_org_unit(p_org_unit_cd);
414 FETCH cur_org_unit INTO l_temp;
415 IF cur_org_unit%NOTFOUND THEN
416 l_bool := FALSE;
417 ELSE
418 l_bool := TRUE;
419 END IF;
420 CLOSE cur_org_unit;
421
422 RETURN l_bool;
423 END validate_org_unit_cd;
424
425 FUNCTION validate_source_txn_id(p_source_txn_id IN igs_fi_inv_int.invoice_id%TYPE) RETURN BOOLEAN AS
426
427 /***********************************************************************************************
428
429 Created By: Amit Gairola
430
431 Date Created By: 03-05-2001
432
433 Purpose: This function validates the Source Transaction Id. This should be a valid Invoice Id .
434
435 Known limitations,enhancements,remarks:
436
437 Change History
438
439 Who When What
440
441 ********************************************************************************************** */
442 l_temp VARCHAR2(1);
443 l_bool BOOLEAN;
444
445 -- Cursor for validating if the source transaction id is a valid invoice id
446 CURSOR cur_txn_id(cp_source_txn_id igs_fi_inv_int.invoice_id%TYPE) IS
447 SELECT 'x'
448 FROM igs_fi_inv_int
449 WHERE invoice_id = cp_source_txn_id;
450 BEGIN
451 OPEN cur_txn_id(p_source_txn_id);
452 FETCH cur_txn_id INTO l_temp;
453 IF cur_txn_id%NOTFOUND THEN
454 l_bool := FALSE;
455 ELSE
456 l_bool := TRUE;
457 END IF;
458 CLOSE cur_txn_id;
459
460 RETURN l_bool;
461 END validate_source_txn_id;
462
463 -- enh bug#2030448, removed accounting method function
464
465 -- enh bug#2584986, removed proc get_local_amount
466
467 PROCEDURE call_build_process ( p_header_rec IN igs_fi_charges_api_pvt.header_rec_type,
468 p_line_rec IN igs_fi_charges_api_pvt.line_rec_type,
469 p_dr_gl_ccid IN OUT NOCOPY igs_fi_invln_int_all.rec_gl_ccid%TYPE,
470 p_cr_gl_ccid IN OUT NOCOPY igs_fi_invln_int_all.rec_gl_ccid%TYPE,
471 p_dr_account_cd IN OUT NOCOPY igs_fi_invln_int_all.rec_account_cd%TYPE,
472 p_cr_account_cd IN OUT NOCOPY igs_fi_invln_int_all.rev_account_cd%TYPE,
473 p_error_string OUT NOCOPY igs_fi_invln_int.error_string%TYPE,
474 p_flag OUT NOCOPY NUMBER
475 ) AS
476 /**********************************************************************************************************************
477
478 Created By: kkillams
479
480 Date Created By: 01-08-2001
481
482 Purpose: This function calls the build accounting process
483
484 Known limitations,enhancements,remarks:
485
486 Change History
487
488 Who When What
489 gurprsin 02-Jun-2005 Enh# 3442712, Modified the Call Build Process
490 Added new parameters to igs_fi_prc_acct_pkg.build_accounts to include unit level attributes.
491 shtatiko 10-DEC-2003 Bug# 3288973, Replaced hard coded transaction type, 'CHARGE', with l_v_transaction_type.
492 vvutukur 17-may-2003 Enh#2831572.Financial Accounting Build. Added 3 new parameters to the call to igs_fi_prc_acct_pkg.build_accounts.
493 vvutukur 17-Sep-2002 Enh#2564643.Removed p_subacccount_id from igs_fi_prc_acct_pkg.build_accounts
494 call.
495 agairola 17-May-2002 Following modifications were done for the bugs 2323555.
496 1. The parameters p_dr_gl_ccid, p_cr_gl_ccid , p_dr_account_cd and
497 p_cr_account_cd are made IN OUT NOCOPY from OUT NOCOPY type.
498 2. The values l_dr_rec_ccid, l_cr_rev_ccid, l_dr_account_cd, l_cr_account_cd
499 were assigned the values passed as input parameters.
500 3. After the Build Account Process is called, if the error type is 1 and
504 Two new local variables l_unit_cd and l_unit_version_number were added.
501 the return status is FALSE, make the account parameters as NULL.
502 jbegum 24-Sep-01 As part of the bug #1962286 the following changes were done:
503 The local variable l_uoo_id was removed.
505 The cursors cur_uoo_id and cur_loc were removed and cursor cur_unit_cd_ver was added.
506 The cursor FOR loop for retrieving the uoo_id has been removed.
507 The IF condition which obtained the location_cd depending on the charge method method was removed
508 and now the value of location_cd was obtained directly from p_line_rec.p_location_cd.
509 The call to the procedure igs_fi_prc_acct_pkg.build_accounts was modified.
510 **********************************************************************************************************************/
511
512
513 l_dr_gl_ccid igs_fi_invln_int_all.rec_gl_ccid%TYPE;
514 l_cr_gl_ccid igs_fi_invln_int_all.rev_gl_ccid%TYPE;
515 l_dr_account_cd igs_fi_invln_int_all.rec_account_cd%TYPE;
516 l_cr_account_cd igs_fi_invln_int_all.rev_account_cd%TYPE;
517 l_err_string igs_fi_invln_int_all.error_string%TYPE;
518 l_ret_status BOOLEAN;
519 l_err_type NUMBER;
520 l_version_number igs_en_stdnt_ps_att.version_number%TYPE;
521 l_st_date igs_or_unit.start_dt%TYPE;
522 l_v_transaction_type igs_fi_inv_int_all.transaction_type%TYPE;
523
524 -- As part of the bug #1962286 the local variables l_unit_cd and l_unit_version_number were added
525
526 l_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
527 l_unit_version_number igs_ps_unit_ofr_opt.version_number%TYPE;
528
529 -- cusor to get the version number form igs_en_stdnt_ps_att table for a person , course
530
531 CURSOR cur_ver (l_course_cd igs_fi_inv_int.course_Cd%TYPE,
532 l_person_id igs_fi_inv_int.person_id%TYPE) IS
533 SELECT version_number
534 FROM igs_en_stdnt_ps_att
535 WHERE course_cd = l_course_cd
536 AND person_id = l_person_id;
537
538 --
539 -- cursor to get the start date for org_unit_cd from igs_or_unit
540 --
541 CURSOR cur_org_st_dt (l_org_unit_cd igs_or_unit.org_unit_cd%TYPE) IS
542 SELECT start_dt
543 FROM igs_or_unit
544 WHERE org_unit_cd = l_org_unit_cd;
545
546
547 -- As part of the bug #1962286 the cursor cur_unit_cd_ver was added
548
549 --
550 -- cursor to get the unit_cd and unit_version_number for a given uoo_id from igs_ps_unit_ofr_opt
551 --
552 CURSOR cur_unit_cd_ver (l_uoo_id igs_ps_unit_ofr_opt_all.uoo_id%TYPE ) IS
553 SELECT unit_cd ,version_number
554 FROM igs_ps_unit_ofr_opt
555 WHERE uoo_id = l_uoo_id;
556
557
558 BEGIN
559 p_error_string := NULL;
560 p_flag := 0;
561
562 FOR rec_ver IN cur_ver(p_header_rec.p_course_cd,
563 p_header_rec.p_person_id )
564 LOOP
565 l_version_number := rec_ver.version_number;
566 END LOOP;
567
568
569 FOR rec_org_st_dt IN cur_org_st_dt ( p_line_rec.p_org_unit_cd)
570 LOOP
571 l_st_date:= rec_org_st_dt.start_dt;
572 END LOOP;
573
574 -- As part of bug #1962286 the following cursor FOR loop was added to fetch the
575 -- unit_cd and unit_version_number values to pass in the call to igs_fi_prc_acct_pkg.build_accounts
576
577 FOR rec_unit_cd_ver IN cur_unit_cd_ver ( p_line_rec.p_uoo_id)
578 LOOP
579 l_unit_cd:= rec_unit_cd_ver.unit_cd;
580 l_unit_version_number:= rec_unit_cd_ver.version_number;
581 END LOOP;
582
583 -- As part of bug #1962286
584 -- The IF condition which obtained the location_cd depending on the charge method method was removed
585 -- and now the value of location_cd is obtained directly from p_line_rec.p_location_cd.Hence in the
586 -- call to igs_fi_prc_acct_pkg.build_accounts p_line_rec.p_location_cd is being passed directly.
587
588
589 -- As part of bug #1962286 the call to igs_fi_prc_acct_pkg.build_accounts was modified.
590 -- Instead of p_line_rec.p_unit_cd , p_line_rec.p_unit_version_number , l_uoo_id being passed
591 -- l_unit_cd , l_unit_version_number , p_line_rec.p_uoo_id are passed
592
593 l_dr_gl_ccid := p_dr_gl_ccid;
594 l_cr_gl_ccid := p_cr_gl_ccid;
595 l_dr_account_cd := p_dr_account_cd;
596 l_cr_account_cd := p_cr_account_cd;
597
598 IF p_header_rec.p_transaction_type = 'RETENTION' THEN
599 l_v_transaction_type := p_header_rec.p_transaction_type;
600 ELSE
601 l_v_transaction_type := 'CHARGE';
602 END IF;
603
604 igs_fi_prc_acct_pkg.build_accounts(
605 p_fee_type => p_header_rec.p_fee_type,
606 p_fee_cal_type => p_header_rec.p_fee_cal_type,
607 p_fee_ci_sequence_number => p_header_rec.p_fee_ci_sequence_number,
608 p_course_cd => p_header_rec.p_course_cd,
609 p_course_version_number => l_version_number,
613 p_unit_version_number => l_unit_version_number,
610 p_org_unit_cd => p_line_rec.p_org_unit_cd,
611 p_org_start_dt => l_st_date,
612 p_unit_cd => l_unit_cd,
614 p_uoo_id => p_line_rec.p_uoo_id,
615 p_location_cd => p_line_rec.p_location_cd,
616 p_transaction_type => l_v_transaction_type, -- replaced hard-coded string 'CHARGE' (Bug# 3288973)
617 p_credit_type_id => NULL,
618 p_source_transaction_id => NULL,
619 x_dr_gl_ccid => l_dr_gl_ccid,
620 x_cr_gl_ccid => l_cr_gl_ccid,
621 x_dr_account_cd => l_dr_account_cd,
622 x_cr_account_cd => l_cr_account_cd,
623 x_err_type => l_err_type,
624 x_err_string => l_err_string,
625 x_ret_status => l_ret_status,
626 p_v_attendance_type => p_header_rec.p_attendance_type,
627 p_v_attendance_mode => p_header_rec.p_attendance_mode,
628 p_v_residency_status_cd => p_line_rec.p_residency_status_cd,
629 p_n_unit_type_id => p_line_rec.p_unit_type_id,
630 p_v_unit_class => p_line_rec.p_unit_class,
631 p_v_unit_mode => p_line_rec.p_unit_mode,
632 p_v_unit_level => p_line_rec.p_unit_level,
633 p_v_waiver_name => p_header_rec.p_waiver_name
634 );
635
636 IF NOT l_ret_status AND l_err_type = 1 THEN
637 p_flag := 1;
638 p_error_string := l_err_string;
639 p_dr_gl_ccid := NULL;
640 p_cr_gl_ccid := NULL;
641 p_dr_account_cd:= NULL;
642 p_cr_account_cd:= NULL;
643 ELSIF NOT l_ret_status AND l_err_type > 1 THEN
644 p_flag := 2;
645 p_error_string := l_err_string;
646 p_dr_gl_ccid := l_dr_gl_ccid;
647 p_cr_gl_ccid := l_cr_gl_ccid;
648 p_dr_account_cd:= l_dr_account_cd;
649 p_cr_account_cd:= l_cr_account_cd;
650 ELSIF l_ret_status THEN
651 p_flag := 0;
652 p_dr_gl_ccid := l_dr_gl_ccid;
653 p_cr_gl_ccid := l_cr_gl_ccid;
654 p_dr_account_cd:= l_dr_account_cd;
655 p_cr_account_cd:= l_cr_account_cd;
656 END IF;
657 END call_build_process;
658
659 PROCEDURE proc_neg_chg(p_source_transaction_id IN NUMBER,
660 p_adj_amount IN NUMBER,
661 p_d_gl_date IN DATE,
662 p_v_currency_cd IN VARCHAR2,
663 p_err_msg OUT NOCOPY VARCHAR2,
664 p_status OUT NOCOPY BOOLEAN) AS
665 /***********************************************************************************************
666
667 Created By: Amit Gairola
668
669 Date Created By: 24-01-2002
670
671 Purpose: This procedure processes the negative charge creation if the invoice amount
672 passed as input to the procedure is negative.
673
674 Known limitations,enhancements,remarks:
675
676 Change History
677
678 Who When What
679 gurprsin 13-Sep-2005 Bug 3627209, Modified existing logic as to return the new message in case if there is no credit type defined
680 for negative charge adjustment credit class.
681 pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
682 parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
683 svuppala 9-JUN-2005 Enh 3442712 - Impact of automatic generation of the Receipt Number
684 changed logic for l_v_credit_number
685 pathipat 30-Sep-2004 Bug 3908040 - Removed condition checking for Manage Accounts = 'Other' since application/unapplication
686 would hereafter be allowed even if Manage Accounts = Other.
687 vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new parameters(lockbox_interface_id,batch_name,deposit_date) to the record type
688 variable l_credit_rec in the call to credits api.
689 shtatiko 05-MAY-2003 Enh# 2831569, Application and Unapplication of credits is done only if Manage Accounts System Option
690 has value STUDENT_FINANCE.
691 vvutukur 05-Apr-2003 Enh#2831554.Internal Credits API Build. Replaced local function validate_lkp with the generic function call
692 igs_fi_crdapi_util.validate_igs_lkp for validating transaction type and charge method. Added check for validity of credit
693 instrument ADJ,fee calendar instance and existence of load calendar instance for a fee calendar instance.
697 p_v_check_number,p_v_source_tran_type,p_v_source_tran_ref_number in the call to Credits
694 Removed the call to igs_fi_credits_api_pub.create_credit, instead placed a call to private credits api with
695 validation level as none.
696 vvutukur 12-Dec-2002 Enh#2584741.Deposits Build.Removed paramter p_validation_level and added 3 new parameters
698 API.
699 pathipat 14-NOV-2002 EnhBug:2584986 - Added IN parameter, p_d_gl_date
700 Added parameter p_d_gl_date in the call to create_credit()
701 and create_application().
702 vvutukur 17-sep-2002 Enh#2564643.Removed references to subaccount_id from i)cursor cur_crt and from its
703 usage in the for loop.ii)igs_fi_credits_api_pub.create_credit.
704 ***********************************************************************************************/
705
706 -- Cursor for the selection of the Invoice Details from the
707 -- Charges table based on the Invoice Id being passed
708 CURSOR cur_inv(cp_invoice_id NUMBER) IS
709 SELECT ROWID,
710 inv.*
711 FROM igs_fi_inv_int inv
712 WHERE invoice_id = cp_invoice_id;
713
714 -- Cursor for the selection of the Application Id from the Application table
715 -- based on the Credits and the Credit Types
716 -- Changed the negative charge adjustment logic with ORDER BY application_id instead of APPLY_DATE
717 -- as it is truncated as part of Time Zone impact bug# "4240402"
718 CURSOR cur_app(cp_invoice_id NUMBER,
719 cp_app_type VARCHAR2,
720 cp_credit_class VARCHAR2,
721 cp_unapp_type VARCHAR2) IS
722 SELECT appl.application_id
723 FROM igs_fi_applications appl,
724 igs_fi_credits fc,
725 igs_fi_cr_types crt
726 WHERE appl.invoice_id = cp_invoice_id
727 AND appl.credit_id = fc.credit_id
728 AND fc.credit_type_id = crt.credit_type_id
729 AND crt.credit_class <> cp_credit_class
730 AND appl.application_type = cp_app_type
731 AND NOT EXISTS (SELECT 'x'
732 FROM igs_fi_applications appl2
733 WHERE appl2.application_type = cp_unapp_type
734 AND appl2.link_application_id = appl.application_id
735 AND appl2.amount_applied = - appl.amount_applied)
736 ORDER BY appl.application_id DESC;
737
738 -- Cursor for selection of the Credit Type Id from the Credit Types table
739 -- based on the Sub Account and the Negative Charges Credit Class
740 CURSOR cur_crt(cp_neg_credit_class VARCHAR2,
741 cp_effective_date DATE) IS
742 SELECT credit_type_id
743 FROM igs_fi_cr_types
744 WHERE credit_class = cp_neg_credit_class
745 AND cp_effective_date BETWEEN effective_start_date AND NVL(effective_end_date,
746 cp_effective_date);
747
748
749 l_rec_crt cur_crt%ROWTYPE;
750 l_attr_rec igs_fi_credits_api_pub.attribute_rec_type;
751 l_adj_amount igs_fi_inv_int.invoice_amount_due%TYPE;
752 l_inv cur_inv%ROWTYPE;
753 l_ret_amount igs_fi_applications.amount_applied%TYPE;
754 l_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
755 l_dr_gl_ccid igs_fi_invln_int.rec_gl_ccid%TYPE;
756 l_cr_gl_ccid igs_fi_invln_int.rev_gl_ccid%TYPE;
757 l_dr_account_cd igs_fi_invln_int.rec_account_cd%TYPE;
758 l_cr_account_cd igs_fi_invln_int.rev_account_cd%TYPE;
759 l_unapply_amt igs_fi_applications.amount_applied%TYPE;
760 l_unapp_amt igs_fi_applications.amount_applied%TYPE;
761 l_diff_amt igs_fi_applications.amount_applied%TYPE;
762 l_cr_desc fnd_new_messages.message_text%TYPE;
763 l_appl_rec_exists BOOLEAN;
764 l_appl_amt igs_fi_applications.amount_applied%TYPE;
765 l_credit_id igs_fi_credits.credit_id%TYPE;
766 l_v_credit_number igs_fi_credits.credit_number%TYPE;
767 l_credit_activity_id igs_fi_cr_activities.credit_activity_id%TYPE;
768 l_rec_cntr NUMBER(10);
769
770 l_ret_status VARCHAR2(1);
771 l_msg_count NUMBER(3);
772 l_msg_data VARCHAR2(2000);
773 l_application_id igs_fi_applications.application_id%TYPE;
774 l_credit_rec igs_fi_credit_pvt.credit_rec_type;
775
776 l_v_ld_cal_type igs_ca_inst.cal_type%TYPE;
777 l_n_ld_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
778 l_b_return_status BOOLEAN;
779 l_v_message_name fnd_new_messages.message_name%TYPE;
780
781
782 BEGIN
783
784 -- If the adjustment amount is NULL, then raise the error message and exit out
785 -- of the procedure
786 IF p_adj_amount IS NULL THEN
787 p_err_msg := 'IGS_FI_INVALID_INV_AMT';
788 p_status := FALSE;
789 END IF;
790
791 -- If the Source Transaction Id is null, then exit out of the procedure without any
792 -- processing
793 IF p_source_transaction_id IS NULL THEN
794 p_err_msg := 'IGS_FI_INV_TXN_ID';
795 p_status := FALSE;
796 END IF;
797
801 IF cur_inv%NOTFOUND THEN
798 -- Fetch the Invoice Details based on the Source Transaction Id
799 OPEN cur_inv(p_source_transaction_id);
800 FETCH cur_inv INTO l_inv;
802 p_err_msg := 'IGS_FI_INV_TXN_ID';
803 p_status := FALSE;
804 END IF;
805 CLOSE cur_inv;
806
807 IF NOT p_status THEN
808 RETURN;
809 END IF;
810
811 l_adj_amount := NVL(p_adj_amount,0);
812
813 -- If the Adjustment Amount passed as input to the procedure is
814 -- less than the Invoice Amount Due of the procedure
815 IF l_adj_amount > NVL(l_inv.invoice_amount_due,0) THEN
816
817 -- Removed condition checking for Manage Accounts = Other
818 -- Application/Unapplication by the Fee Assessment process would be allowed even
819 -- when Manage Accounts = Other. (Bug 3908040)
820
821 -- Calculate the Difference amount as Adjustment Amount minus the Invoice
822 -- Amount Due
823 l_diff_amt := l_adj_amount -
824 NVL(l_inv.invoice_amount_due,0);
825
826 -- Loop through the Application Records for the Invoice Id
827 FOR apprec IN cur_app(p_source_transaction_id,
828 g_app,
829 g_neg_cr_class,
830 g_unapp) LOOP
831 l_appl_rec_exists := TRUE;
832
833 -- Get the Application amount as returned by the procedure get_sum_appl_amnt
834 -- This procedure shall sum up the amount applied for all the UNAPP records for the
835 -- Application Id and then shall return the difference between the Amount Applied of the Application
836 -- record and the UNAPP records
837 l_appl_amt := igs_fi_gen_007.get_sum_appl_amnt(apprec.application_id);
838
839
840 -- If the Difference amount is greater than the Application Amount, then
841 IF l_diff_amt > NVL(l_appl_amt,0) THEN
842
843 -- Then Unapplied Amount is the value of the Application record
844 l_unapply_amt := NVL(l_appl_amt,0);
845 ELSE
846 -- Else the Unapply Amount is the Difference amount
847 l_unapply_amt := l_diff_amt;
848 END IF;
849
850 -- Create an unapplication
851 -- Enh 2584986 - Added parameter p_d_gl_date
852 igs_fi_gen_007.create_application(p_application_id => apprec.application_id,
853 p_credit_id => g_null,
854 p_invoice_id => g_null,
855 p_amount_apply => l_unapply_amt,
856 p_appl_type => g_unapp,
857 p_appl_hierarchy_id => g_null,
858 p_validation => g_ind_yes,
859 p_unapp_amount => l_unapp_amt,
860 p_inv_amt_due => l_inv_amt_due,
861 p_dr_gl_ccid => l_dr_gl_ccid,
862 p_cr_gl_ccid => l_cr_gl_ccid,
863 p_dr_account_cd => l_dr_account_cd,
864 p_cr_account_cd => l_cr_account_cd,
865 p_err_msg => p_err_msg,
866 p_status => p_status,
867 p_d_gl_date => p_d_gl_date
868 );
869
870
871 -- If the status returned by the create application procedure is FALSE, return
872 IF NOT p_status THEN
873 RETURN;
874 END IF;
875
876 -- The adjustment amount is reduced by the amount unapplied
877 l_adj_amount := l_adj_amount - l_unapply_amt;
878
879 -- If the Adjustment Amount is less than 0 then exit
880 IF l_adj_amount <=0 THEN
881 EXIT;
882 END IF;
883 END LOOP;
884
885 IF NOT l_appl_rec_exists THEN
886 p_status := FALSE;
887 p_err_msg := 'IGS_FI_NO_APP_REC';
888 RETURN;
889 END IF;
890
891 END IF; -- l_adj_amount > NVL(l_inv.invoice_amount_due,0)
892
893 -- Loop through the Credit Types for the Sub Account id
894 l_rec_cntr := 0;
895 FOR crtrec IN cur_crt(g_neg_cr_class,TRUNC(SYSDATE))
896 LOOP
897 l_rec_cntr := l_rec_cntr + 1;
898 --If there are more than one credit type records for the Negative Credit Class, then this is an error.
899 IF l_rec_cntr > 1 THEN
900 p_err_msg := 'IGS_FI_ONE_NCA_REC';
901 p_status := FALSE;
902 EXIT;
903 ELSE
904 l_rec_crt := crtrec;
905 END IF;
906 END LOOP;
907
908 --Bug 3627209, Modified the logic as to return the new message in case if there is no credit type defined for negative charge adjustment credit class.
909 IF l_rec_cntr = 0 THEN
910 p_err_msg := 'IGS_FI_NO_NCA_REC';
911 p_status := FALSE;
912 END IF;
913
914 IF NOT p_status THEN
915 RETURN;
916 END IF;
917
918 --Get the description of the Credit to be created from the message
922 l_credit_id := NULL;
919 fnd_message.set_name('IGS','IGS_FI_NEG_CHG_ADJ');
920 l_cr_desc := fnd_message.get;
921
923
924
925 --Check for validity of the Fee Calendar Instance, whether it is active as on system date.
926 IF NOT igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => l_inv.fee_cal_type,
927 p_n_ci_sequence_number => l_inv.fee_ci_sequence_number,
928 p_v_s_cal_cat => 'FEE') THEN
929 --if not active as on system date, raise error and stop processing further.
930 p_status := FALSE;
931 p_err_msg := 'IGS_FI_FCI_NOTFOUND';
932 RETURN;
933 END IF;
934
935
936 l_v_message_name := NULL;
937 --Check if there exists a relation between the Fee Calendar Instance and the Load Calendar Instance...
938 igs_fi_crdapi_util.validate_fci_lci_reln( p_v_fee_cal_type => l_inv.fee_cal_type,
939 p_n_fee_ci_sequence_number => l_inv.fee_ci_sequence_number,
940 p_v_ld_cal_type => l_v_ld_cal_type,
941 p_n_ld_ci_sequence_number => l_n_ld_ci_sequence_number,
942 p_v_message_name => l_v_message_name,
943 p_b_return_stat => l_b_return_status);
944 --if no relation exists, then raise error and stop processing further.
945 IF l_b_return_status = FALSE THEN
946 p_status := FALSE;
947 p_err_msg := l_v_message_name;
948 RETURN;
949 END IF;
950
951
952 l_credit_rec.p_credit_status := g_cleared;
953 l_credit_rec.p_credit_source := g_null;
954 l_credit_rec.p_party_id := l_inv.person_id;
955 l_credit_rec.p_credit_type_id := l_rec_crt.credit_type_id;
956 l_credit_rec.p_credit_instrument := g_adj;
957 l_credit_rec.p_description := l_cr_desc;
958 l_credit_rec.p_amount := NVL(p_adj_amount,0);
959 l_credit_rec.p_currency_cd := p_v_currency_cd;
960 l_credit_rec.p_exchange_rate := g_null;
961 l_credit_rec.p_transaction_date := TRUNC(SYSDATE);
962 l_credit_rec.p_effective_date := TRUNC(SYSDATE);
963 l_credit_rec.p_source_transaction_id := g_null;
964 l_credit_rec.p_receipt_lockbox_number := g_null;
965 l_credit_rec.p_credit_card_code := g_null;
966 l_credit_rec.p_credit_card_holder_name := g_null;
967 l_credit_rec.p_credit_card_number := g_null;
968 l_credit_rec.p_credit_card_expiration_date:= g_null;
969 l_credit_rec.p_credit_card_approval_code := g_null;
970 l_credit_rec.p_invoice_id := l_inv.invoice_id;
971 l_credit_rec.p_awd_yr_cal_type := g_null;
972 l_credit_rec.p_awd_yr_ci_sequence_number := g_null;
973 l_credit_rec.p_fee_cal_type := l_inv.fee_cal_type;
974 l_credit_rec.p_fee_ci_sequence_number := l_inv.fee_ci_sequence_number;
975 l_credit_rec.p_check_number := g_null;
976 l_credit_rec.p_source_tran_type := g_null;
977 l_credit_rec.p_source_tran_ref_number := g_null;
978 l_credit_rec.p_gl_date := p_d_gl_date;
979 l_credit_rec.p_v_credit_card_payee_cd := NULL;
980 l_credit_rec.p_v_credit_card_status_code := NULL;
981 l_credit_rec.p_v_credit_card_tangible_cd := NULL;
982 l_credit_rec.p_lockbox_interface_id := NULL;
983 l_credit_rec.p_batch_name := NULL;
984 l_credit_rec.p_deposit_date := NULL;
985
986 --Create a credit by calling the Private Credits API with p_validation_level as fnd_api.g_valid_level_none.
987 igs_fi_credit_pvt.create_credit( p_api_version => 2.1,
988 p_init_msg_list => fnd_api.g_false,
989 p_commit => fnd_api.g_false,
990 p_validation_level => fnd_api.g_valid_level_none,
991 x_return_status => l_ret_status,
992 x_msg_count => l_msg_count,
993 x_msg_data => l_msg_data,
994 p_credit_rec => l_credit_rec,
995 p_attribute_record => l_attr_rec,
996 x_credit_id => l_credit_id,
997 x_credit_activity_id => l_credit_activity_id,
998 x_credit_number => l_v_credit_number);
999
1000 --If the Private Credits API returns an error, then exit.
1001 IF l_ret_status <> 'S' THEN
1002 p_status := FALSE;
1003 RETURN;
1004 END IF;
1005
1006 -- Removed condition checking for Manage Accounts = Other
1007 -- Application/Unapplication by the Fee Assessment process would be allowed even
1008 -- when Manage Accounts = Other. (Bug 3908040)
1009
1010 l_application_id := NULL;
1011
1012 -- Create an application record
1013 -- Enh 2584986 - Added parameter p_d_gl_date
1014
1015 igs_fi_gen_007.create_application(p_application_id => l_application_id,
1016 p_credit_id => l_credit_id,
1017 p_invoice_id => l_inv.invoice_id,
1021 p_validation => g_ind_yes,
1018 p_amount_apply => NVL(p_adj_amount,0),
1019 p_appl_type => g_app,
1020 p_appl_hierarchy_id => g_null,
1022 p_unapp_amount => l_unapp_amt,
1023 p_inv_amt_due => l_inv_amt_due,
1024 p_dr_gl_ccid => l_dr_gl_ccid,
1025 p_cr_gl_ccid => l_cr_gl_ccid,
1026 p_dr_account_cd => l_dr_account_cd,
1027 p_cr_account_cd => l_cr_account_cd,
1028 p_err_msg => p_err_msg,
1029 p_status => p_status,
1030 p_d_gl_date => p_d_gl_date
1031 );
1032 IF NOT p_status THEN
1033 RETURN;
1034 END IF;
1035
1036 p_status := TRUE;
1037 p_err_msg := NULL;
1038 END proc_neg_chg;
1039
1040 FUNCTION validate_neg_amt(p_person_id IN igs_fi_inv_int.person_id%TYPE,
1041 p_fee_type IN igs_fi_inv_int.fee_type%TYPE,
1042 p_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
1043 p_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE,
1044 p_fee_cat IN igs_fi_inv_int.fee_cat%TYPE,
1045 p_course_cd IN igs_fi_inv_int.course_Cd%TYPE,
1046 p_uoo_id IN igs_fi_invln_int.uoo_id%TYPE,
1047 p_location_cd IN igs_fi_invln_int.location_cd%TYPE,
1048 p_transaction_type IN igs_fi_inv_int.transaction_type%TYPE,
1049 p_amt IN igs_fi_inv_int.invoice_amount%TYPE,
1050 p_source_txn_id IN igs_fi_inv_int.invoice_id%TYPE)
1051 /***********************************************************************************************
1052
1053 Created By: Amit Gairola
1054
1055 Date Created By: 24-01-2002
1056
1057 Purpose: This procedure validates if the amount passed for adjust
1058
1059 Known limitations,enhancements,remarks:
1060
1061 Change History
1062
1063 Who When What
1064 svuppala 05-May-2006 Bug 3924836; Formated amounts by rounding off to currency precision
1065 agairola 11-Mar-2003 Bug 2762740: Following modifications were done
1066 1. Modified the return type for this procedure from Boolean to
1067 Number
1068 2. Modified the cursor cur_val_chg to include error account and
1069 group by
1070 3. In case there are some error account transactions, return value
1071 is 1.
1072 4. For the existing validation related to amount, return value is 2.
1073 5. For normal completion, return value is 0.
1074
1075 ***********************************************************************************************/
1076 RETURN NUMBER AS
1077
1078 -- Cursor for getting the sum of the Invoice Amount and the Invoice Amount Due
1079
1080
1081 CURSOR cur_val_chg(cp_person_id igs_pe_person.person_id%TYPE,
1082 cp_fee_type igs_fi_fee_type.fee_type%TYPE,
1083 cp_fee_cal_type igs_ca_inst.cal_type%TYPE,
1084 cp_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
1085 cp_fee_cat igs_fi_fee_cat.fee_cat%TYPE,
1086 cp_course_cd igs_ps_ver.course_cd%TYPE,
1087 cp_uoo_id igs_fi_invln_int.uoo_id%TYPE,
1088 cp_location_cd igs_fi_invln_int.location_cd%TYPE,
1089 cp_transaction_type igs_fi_inv_int.transaction_type%TYPE,
1090 cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
1091 SELECT SUM(invoice_amount) inv_amt,
1092 SUM(invoice_amount_due) inv_due,
1093 iln.error_account
1094 FROM igs_fi_inv_int inv,
1095 igs_fi_invln_int iln
1096 WHERE inv.invoice_id = iln.invoice_id
1097 AND ((cp_invoice_id IS NULL
1098 AND inv.person_id = cp_person_id
1099 AND inv.fee_type = cp_fee_type
1100 AND inv.fee_cal_type = cp_fee_cal_type
1101 AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
1102 AND inv.transaction_type = cp_transaction_type
1103 AND ((inv.fee_cat = cp_fee_cat)
1104 OR (inv.fee_cat IS NULL and cp_fee_cat IS NULL))
1105 AND ((iln.uoo_id = cp_uoo_id)
1106 OR (iln.uoo_id IS NULL and cp_uoo_id IS NULL))
1107 AND ((iln.location_cd = cp_location_cd)
1108 OR (iln.location_cd IS NULL and cp_location_cd IS NULL))
1109 AND ((inv.course_cd = cp_course_cd)
1110 OR (inv.course_cd IS NULL and cp_course_cd IS NULL)))
1111 OR (inv.invoice_id = cp_invoice_id))
1112 AND NOT EXISTS (SELECT 'x'
1113 FROM igs_fi_credits fc,
1117 AND app.credit_id = fc.credit_id
1114 igs_fi_cr_types crt,
1115 igs_fi_applications app
1116 WHERE app.invoice_id = inv.invoice_id
1118 AND fc.status = g_cleared
1119 AND fc.credit_type_id = crt.credit_type_id
1120 AND crt.credit_class = g_neg_cr_class
1121 AND app.amount_applied = inv.invoice_amount)
1122 GROUP BY iln.error_account;
1123 l_inv_amt cur_val_chg%ROWTYPE;
1124 l_val_err VARCHAR2(1);
1125
1126 l_ret_val NUMBER(2);
1127
1128 BEGIN
1129
1130 -- Fetch the Invoice Amount and Invoice Amount due for the Cursor
1131 l_ret_val := 0;
1132
1133 FOR l_chg_rec IN cur_val_chg(p_person_id,
1134 p_fee_type,
1135 p_fee_cal_type,
1136 p_fee_ci_sequence_number,
1137 p_fee_cat,
1138 p_course_cd,
1139 p_uoo_id,
1140 p_location_cd,
1141 p_transaction_type,
1142 p_source_txn_id) LOOP
1143 IF l_chg_rec.error_account = g_ind_yes THEN
1144 l_ret_val := 1;
1145 EXIT;
1146 ELSE
1147 l_chg_rec.inv_due := igs_fi_gen_gl.get_formatted_amount(l_chg_rec.inv_due);
1148 l_chg_rec.inv_amt := igs_fi_gen_gl.get_formatted_amount(l_chg_rec.inv_amt);
1149 l_inv_amt.inv_due := NVL(l_inv_amt.inv_due,0) + NVL(l_chg_rec.inv_due,0);
1150 l_inv_amt.inv_amt := NVL(l_inv_amt.inv_amt,0) + NVL(l_chg_rec.inv_amt,0);
1151 END IF;
1152 END LOOP;
1153
1154 IF l_ret_val = 1 THEN
1155 RETURN l_ret_val;
1156 END IF;
1157
1158
1159 -- Assign the due amount to the global variable for Invoice Amount Due
1160 g_inv_amt_due := l_inv_amt.inv_due;
1161
1162 -- If the Invoice Amount is less than the adjustment amount
1163 IF NVL(l_inv_amt.inv_amt,0) < ABS(NVL(p_amt,0)) THEN
1164
1165 -- then return
1166 RETURN 2;
1167 ELSE
1168 RETURN 0;
1169 END IF;
1170
1171 END validate_neg_amt;
1172
1173 PROCEDURE create_charge(p_api_version IN NUMBER,
1174 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1175 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1176 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1177 p_header_rec IN header_rec_type,
1178 p_line_tbl IN Line_Tbl_Type,
1179 x_invoice_id OUT NOCOPY NUMBER,
1180 x_line_id_tbl OUT NOCOPY line_id_tbl_type,
1181 x_return_status OUT NOCOPY VARCHAR2,
1182 x_msg_count OUT NOCOPY NUMBER,
1183 x_msg_data OUT NOCOPY VARCHAR2,
1184 x_waiver_amount OUT NOCOPY NUMBER) AS
1185
1186 /***********************************************************************************************
1187
1188 Created By: Amit Gairola
1189
1190 Date Created By: 03-05-2001
1191
1192 Purpose: This procedure is the main procedure for creating the charges.
1193
1194 Known limitations,enhancements,remarks:
1195
1196 Change History
1197
1198 Who When What
1199 skharida 26-Jun-2006 Bug 5208136 - Removed the obsoleted columns of the table IGS_FI_INV_INT_ALL
1200 akandreg 20-Jun-2006 Bug 5116519 - The Message 'IGS_FI_INVALID_FTCI' is modified .The old token FEE_CAL_TYPE
1201 is replaced by new token CI_DESC and passed fee CI description to that. Also the cursor
1202 cur_alt_cd_desc is modified to select another column 'description' from igs_ca_inst_all.
1203 pathipat 12-Jun-2006 Bug 5306868 - Modified cursor cur_chg: Added filter on invoice_amount_due
1204 and removed ORDER_BY on diff_amt
1205 Added cursors cur_chgadj, cur_charges, associated code and local variables.
1206 sapanigr 29-May-2006 Bug 5251760 Added cursor cur_chg_inv. Removed UNION in cur_chg. Modified related code appropriately
1207 svuppala 05-May-2006 Bug 3924836; Added l_n_invoice_amount, l_n_amount variables to
1208 format amounts by rounding off to currency precision
1209 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_inv_int
1210 and igs_fi_invln_int are now rounded off to currency precision
1211 sapanigr 24-Feb-2006 Bug 5018036 - Removed cursor 'cur_ret' and cursor variable 'l_ret_rec' as it was not being used anywhere in the code.
1212 sapanigr 09-Feb-2006 Bug 5018036 - Modifed cursor 'cur_ret' to take values from base table 'igs_fi_f_typ_ca_inst_all'.
1213 The view igs_fi_f_typ_ca_inst_lkp_v used earlier lead to high shared memory usage.
1214 abshriva 24-Oct-2005 Bug 4680553 - The Message 'IGS_FI_WAV_TRANS_CREATED' was removed as it was being
1215 called in error page when waiver transaction was successful
1219 Modified HEADER_REC_TYPE -- included waiver_name.
1216 pathipat 05-Oct-2005 Bug 4383148 - Fees not assessed if attendance type cannot be derived
1217 Removed invocation of validate_atd_mode and validate_atd_type
1218 svuppala 07-JUL-2005 Enh 3392095 - Tution Waivers build
1220 Modified l_api_version
1221 gurprsin 02-Jun-2005 Enh# 3442712 Modified TBH call of table IGS_fi_invln_int_all
1222 i.e. igs_fi_inv_int_pkg.insert_row to include unit_type_id and unti_level
1223 pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
1224 uudayapr 08-Mar-2004 Bug#3478599.Added the code to prevent charge creation with
1225 error account as Y for Transaction Type as
1226 Document when Revenue Account Derivation fails.
1227 vvutukur 27-Jun-2003 Bug#2849185.Bypassed unncessary validations in case of negative charge.
1228 jbegum 20-Jun-2003 Bug# 2998266, NEXT_INVOICE_NUMBER in the IGS_FI_CONTROL table will not be used for
1229 generating unique charge numbers. Next Value from a DB sequence will be used for
1230 for generating unique charges numbers.
1231 Removed the call to IGS_FI_CONTROL_PKG.update_row
1232 vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Removed 3 columns(lockbox_context,lockbox_number,attribute,ar_int_org_id)
1233 from igs_fi_control_pkg.update_row.
1234 vvutukur 26-May-2003 Enh#2831572.Financial Accounting Build. Changes as specified in TD.
1235 shtatiko 05-MAY-2003 Enh# 2831569, Added check Manage Accounts System Option before creating a charge.
1236 And updation of Holds or Standard balances is done only if its value is STUDENT_FINANCE.
1237 pathipat 14-Apr-2003 Enh 2831569 - Commercial Receivables Interface build
1238 Modified call to igs_fi_control_pkg.update_row
1239 vvutukur 11-Apr-2003 Enh#2831554.Internal Credits API Build. Added checks to validate ADJ credit instrument,charge method and transaction type.
1240 agairola 11-Mar-2003 Bug 2762740: 1. Modified the call to the validate_neg_amnt.
1241 2. Added a new variable l_val_neg_amnt and this variable
1242 gets the value from validate_neg_amnt
1243 3. Added the code to check for l_val_neg_amnt = 1
1244 4. Modified the code for the amount validation(l_val_neg_amnt=2)
1245 smadathi 18-Feb-2002 Enh. Bug 2747329.Modified the TBH call to IGS_FI_CONTROL to Add new columns
1246 rfnd_destination, ap_org_id, dflt_supplier_site_name
1247 pathipat 14-Nov-2002 Enh Bug: 2584986 -
1248 1. Added parameters gl_date, gl_posted_date and posting_control_id
1249 in the call to igs_fi_invln_int_pkg.insert_row()
1250 2. Added parameter p_gl_date in call to proc_neg_chg() for negative charges
1251 3. Removed calls to get_local_amount, passed the invoice_amount and currency_cd
1252 directly from p_header_rec, instead of conversion to local currency
1253 vvutukur 20-sep-2002 Enh#2562745.1)Added conv_process_run_ind parameter to call to igs_fi_control_pkg.
1254 update_row.2)Added call to igs_fi_prc_balance.update_balances to update the Holds
1255 Balance real time whenever a charge gets created.3)Added two new validations to
1256 error out of charges api in the following scenarios.a)if holds conversion process
1257 is running b)if no active balance rule exist for HOLDS.
1258 vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie.,Removed call to the private
1259 function validate_subaccount and its related code.Removed declaration of local
1260 variables l_party_subaccount_id,l_subacc_name.Removed cursor cur_sa.Removed
1261 reference to subaccount_id from the calls to IGS_FI_INV_INT_PKG.Insert_Row and
1262 igs_fi_prc_balances.update_balances.Removed call to igs_fi_gen_005.validate_psa
1263 since the function igs_fi_gen_005.validate_psa is being removed.Removed call to
1264 igs_fi_party_sa_pkg.insert_row as the table igs_fi_party_sa is being obsoleted.
1265 Also added 7 new parameters to the call to igs_fi_control_pkg.update_row.
1266 smadathi 03-Jul-2002 Bug 2443082. Modified update_balances procedure call. Modified to pass transaction date
1267 instead of system date.
1268 agairola 10-Jun-2002 Bug Number: 2407624 Modified the call to the Get_Local_Amount
1269 agairola 17-May-2002 Following modifications were done for the bug 2323555 - Call Build Accounts
1270 procedure is called in all cases even if the accounts are passed.
1271 Also assigned the value of the accounts passed as input to the Charges API
1272 to the local variables used in the call to Call Build Accounts procedure
1273 SYkrishn 15-APR-2002 Added planned_credits_ind to the IGS_FI_CONTROL_PKG.update_row call as part of Enh 2293676
1274 smvk 08-Mar-2002 Added four attributes refund_dr_gl_ccid,refund_cr_gl_ccid,refund_dr_account_cd,
1275 refund_cr_account_cd and removed three attributes last_account_trans,last_payment_trans,
1276 last_pay_term_trans to the call to IGS_FI_CONTROL_PKG.Update_row as per Bug #2144600
1277 vvutukur 27-02-2002 added call to igs_fi_gen_007.validate_person instead of calling local function
1278 validate_person for bug:2238362
1279 vvutukur 18-feb-2002 added ar_int_org_id column to igs_fi_control_pkg.update_row call. bug:2222272
1280 jbegum 14-Feb-2001 As part of Enh bug # 2201081
1284 Build accounting process is calling only if these parameters are
1281 Added call to IGS_FI_GEN_005.validate_psa and IGS_FI_PARTY_SA_PKG.insert_row
1282 Removed cursor cur_psa
1283 kkillams 01-08-2001 Modification done w.r.t student finance dld bug id :1882122
1285 don't have values p_override_dr_rec_ccid, p_override_cr_rev_ccid,
1286 p_override_dr_rec_account_cd and p_override_cr_rev_account_cd
1287 before inserting the data into igs_fi_invln_int.
1288 jbegum 26-Sep-2001 As part of bug #1962286 the following changes were done:
1289 Changed the call to the local function validate_uoo.
1290 Changed the call to IGS_FI_INVLN_INT_PKG.Insert_row.
1291 vchappid 05-Oct-2001 As a part of Enh Bug#2030448, the call to the calculate balances process is
1292 replaced with a call to the new procedure Update_Balances created as a part
1293 of the SFCR010. Limitation of the Accounting Method to CASH is removed,
1294 Balance_Flag has been removed from IGS_FI_INV_INT_ALL, IGS_FI_CREDITS_ALL tables,
1295 New column optional_fee_flag column is added in IGS_FI_INV_INT_ALL Table.
1296 smadathi 12-oct-2001 As part of enhancement bug#2042716 , the TBH calls to
1297 IGS_FI_PARTY_SUBACTS modified . Payment_plan_flag added.
1298 jbegum 19-Nov-2001 As part of Enhancement bug #2113459 the following changes were done:
1299 Added a new cursor cur_ret.
1300 Added an if condition that checks for transaction type RETENTION.
1301 If transaction type is RETENTION then creates the credit and debit side of
1302 retention charge.
1303
1304 nalkumar 19-Dec-2001 Changed the call to IGS_FI_PARTY_SUBACTS_PKG.insert_row.
1305 This is as per the SF015 Holds DLD. Bug# 2126091.
1306 agairola 12-Feb-2002 Changed the functionality for creation of the negative charges as per the DLD
1307 specfied for the negative charges. SFCR003 Bug No: 2195715
1308 ********************************************************************************************** */
1309
1310 l_api_name CONSTANT VARCHAR2(30) := 'Create_Charge';
1311 l_api_version CONSTANT NUMBER := 2.0;
1312 l_bool BOOLEAN;
1313 l_valid BOOLEAN := TRUE;
1314 l_line_cnt NUMBER := 1;
1315 l_var NUMBER;
1316 l_acnt_mthd IGS_FI_CONTROL.Accounting_Method%TYPE;
1317 l_rowid VARCHAR2(25);
1318 l_application_id igs_fi_applications.application_id%TYPE;
1319 l_unapplied_amount igs_fi_credits.unapplied_amount%TYPE;
1320 l_invoice_lines_id igs_fi_invln_int.invoice_lines_id%TYPE;
1321 l_invoice_id igs_fi_inv_int.invoice_id%TYPE;
1322 l_temp VARCHAR2(1);
1323 l_org_id igs_fi_inv_int.org_id%TYPE := igs_ge_gen_003.get_org_id;
1324 l_ret VARCHAR2(1);
1325 l_adj_amt igs_fi_inv_int.invoice_amount%TYPE;
1326 l_amt_neg_chg igs_fi_inv_int.invoice_amount%TYPE;
1327 l_err_msg VARCHAR2(100);
1328 l_status BOOLEAN;
1329 l_rec_exist BOOLEAN;
1330 --
1331 -- these variable are added w.r.t finance accounting bug no : 1882122
1332 l_error_string igs_fi_invln_int.error_string%TYPE;
1333 l_flag NUMBER;
1334 l_error_account VARCHAR2(1) := 'N';
1335 l_dr_gl_ccid igs_fi_invln_int_all.rec_gl_ccid%TYPE;
1336 l_cr_gl_ccid igs_fi_invln_int_all.rev_gl_ccid%TYPE;
1337 l_dr_account_cd igs_fi_invln_int_all.rec_account_cd%TYPE;
1338 l_cr_account_cd igs_fi_invln_int_all.rev_account_cd%TYPE;
1339
1340 l_message_name fnd_new_messages.message_name%TYPE := NULL;
1341 l_n_invoice_amount igs_fi_inv_int_all.invoice_amount%TYPE;
1342 l_n_amount igs_fi_invln_int_all.amount%TYPE;
1343
1344 -- Cursor for getting the record from the Charges table based on the invoice id
1345 -- passed to the cursor
1346 CURSOR cur_inv(cp_invoice_id igs_fi_inv_int.Invoice_Id%TYPE) IS
1347 SELECT rowid,
1348 igs_fi_inv_int.*
1349 FROM igs_fi_inv_int
1350 WHERE invoice_id = cp_invoice_id;
1351
1352 -- Cursor for getting the records from the Applications table based on the
1353 -- invoice id and the application type as APP
1354 CURSOR cur_app(cp_invoice_id igs_fi_inv_int.Invoice_Id%TYPE,
1355 cp_app VARCHAR2) IS
1356 SELECT rowid,
1357 igs_fi_applications.*
1358 FROM igs_fi_applications
1359 WHERE invoice_id = cp_invoice_id
1360 AND application_type = cp_app;
1361
1362 -- Cursor for getting the credit record.
1363 CURSOR cur_crd(cp_credit_id igs_fi_credits.Credit_Id%TYPE) IS
1364 SELECT rowid,
1365 igs_fi_credits.*
1366 FROM igs_fi_credits
1367 WHERE credit_id = cp_credit_id;
1368
1369 -- Cursor for getting the record from the IGS_FI_CONTROL table
1370 CURSOR cur_ctrl IS
1371 SELECT rowid,
1372 igs_fi_control.*
1373 FROM igs_fi_control;
1374
1375 -- Cursor for selecting all the charges based on the Person Id, Fee Type, Fee Cal Type,
1379 -- descending order
1376 -- Fee Calendar Instance, Fee Category, Course Cd, Transaction type, Unit Section Id
1377 -- and the location code. If the invoice id is passed as input to the cursor then this is
1378 -- used to select the charges. The charges are ordered by the Invoice Amount Due in
1380 CURSOR cur_chg(cp_person_id igs_pe_person.person_id%TYPE,
1381 cp_fee_type igs_fi_fee_type.fee_type%TYPE,
1382 cp_fee_cal_type igs_ca_inst_all.cal_type%TYPE,
1383 cp_fee_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1384 cp_fee_cat igs_fi_fee_cat.fee_cat%TYPE,
1385 cp_course_cd igs_ps_ver.course_cd%TYPE,
1386 cp_transaction_type igs_fi_inv_int_all.transaction_type%TYPE,
1387 cp_uoo_id igs_fi_invln_int_all.uoo_id%TYPE,
1388 cp_location_cd igs_fi_invln_int_all.location_cd%TYPE,
1389 cp_invoice_id igs_fi_inv_int_all.invoice_id%TYPE) IS
1390 SELECT inv.rowid row_id,
1391 inv.invoice_id invoice_id,
1392 inv.invoice_amount_due invoice_amount_due,
1393 inv.invoice_amount invoice_amount
1394 FROM igs_fi_inv_int_all inv,
1395 igs_fi_invln_int_all iln
1396 WHERE inv.invoice_id = iln.invoice_id
1397 AND inv.person_id = cp_person_id
1398 AND inv.fee_type = cp_fee_type
1399 AND inv.fee_cal_type = cp_fee_cal_type
1400 AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
1401 AND inv.transaction_type = cp_transaction_type
1402 AND iln.error_account <> g_ind_yes
1403 AND inv.invoice_amount_due > 0
1404 AND ((iln.uoo_id = cp_uoo_id)
1405 OR (iln.uoo_id IS NULL AND cp_uoo_id IS NULL))
1406 AND ((iln.location_cd = cp_location_cd)
1407 OR (iln.location_cd IS NULL AND cp_location_cd IS NULL))
1408 AND ((fee_cat = cp_fee_cat)
1409 OR (fee_cat IS NULL and cp_fee_cat IS NULL))
1410 AND ((course_cd = cp_course_cd)
1411 OR (course_cd IS NULL and cp_course_cd IS NULL))
1412 ORDER BY invoice_amount_due DESC;
1413
1414 -- Cursor for selecting all the charges based on the Invoice ID. This is used if the invoice_id has been passed.
1415 CURSOR cur_chg_inv(cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
1416 SELECT inv.rowid row_id,
1417 inv.invoice_id,
1418 inv.invoice_amount_due,
1419 inv.invoice_amount
1420 FROM igs_fi_inv_int_all inv
1421 WHERE inv.invoice_id = cp_invoice_id
1422 AND inv.invoice_amount_due > 0
1423 ORDER BY invoice_amount_due DESC;
1424
1425 --3392095 cursor to check whether waiver programs exists or not
1426 CURSOR cur_waiver(cp_fee_cal_type igs_fi_inv_int.fee_cal_type%TYPE,
1427 cp_fee_ci_sequence_number igs_fi_inv_int.fee_ci_sequence_number%TYPE,
1428 cp_waiver_name igs_fi_inv_int.waiver_name%TYPE) IS
1429 SELECT 'X'
1430 FROM igs_fi_waiver_pgms fwp
1431 WHERE fwp.fee_cal_type = cp_fee_cal_type
1432 AND fwp.fee_ci_sequence_number = cp_fee_ci_sequence_number
1433 AND fwp.waiver_name = cp_waiver_name;
1434
1435 l_n_waiver_amount NUMBER;
1436 l_v_return_status VARCHAR2(1);
1437 l_n_msg_count NUMBER ;
1438 l_v_msg_data VARCHAR2(32767) ;
1439 l_v_waiver_exists VARCHAR2(1);
1440
1441 l_rec_chg_inv cur_chg_inv%ROWTYPE;
1442
1443 l_ctrl_rec cur_ctrl%ROWTYPE;
1444
1445 l_sum_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
1446
1447 l_validate_flag BOOLEAN;
1448 l_valid_person VARCHAR2(1); --bug:2238362
1449
1450 l_cnv_prc igs_fi_control.conv_process_run_ind%TYPE := NULL;
1451 l_hold_bal_type CONSTANT igs_fi_balance_rules.balance_name%TYPE := 'HOLDS';
1452 l_action_active CONSTANT VARCHAR2(10) := 'ACTIVE';
1453 l_version_number igs_fi_balance_rules.version_number%TYPE;
1454 l_last_conversion_date DATE;
1455 l_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE;
1456
1457 l_val_neg_amnt NUMBER(2);
1458 l_v_manage_accounts igs_fi_control_all.manage_accounts%TYPE;
1459 l_v_message_name fnd_new_messages.message_name%TYPE;
1460
1461
1462 -- Bug #2998266 Added the following cursor
1463 CURSOR c_get_nextval
1464 IS
1465 SELECT igs_fi_inv_int_all_s1.NEXTVAL
1466 FROM DUAL;
1467 l_n_get_nextval NUMBER;
1468
1469 l_v_igs_fi_auto_calc_waivers VARCHAR2(1);
1470
1471 --Cursor to get alternate code, description to pass as token for IGS_FI_WAV_PGM_NO_REC_FOUND,
1472 --IGS_FI_INVALID_FTCI respectively
1473 CURSOR cur_alt_cd_desc(cp_fee_cal_type igs_ca_inst_all.cal_type%TYPE,
1474 cp_fee_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE) IS
1475 SELECT alternate_code , description
1476 FROM igs_ca_inst_all
1477 WHERE cal_type = cp_fee_cal_type
1478 AND sequence_number = cp_fee_ci_sequence_number;
1479
1480 l_v_fee_alt_cd igs_ca_inst_all.alternate_code%TYPE;
1481 l_v_fee_ci_desc igs_ca_inst_all.description%TYPE;
1485 CURSOR cur_charges(cp_person_id igs_pe_person.person_id%TYPE,
1482
1483 -- Cursor to pick up all charges following the unique key (or) for a
1484 -- specific invoice_id (similar to cur_chg, except the ORDER BY clause and check on invoice_amount_due)
1486 cp_fee_type igs_fi_fee_type.fee_type%TYPE,
1487 cp_fee_cal_type igs_ca_inst.cal_type%TYPE,
1488 cp_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
1489 cp_fee_cat igs_fi_fee_cat.fee_cat%TYPE,
1490 cp_course_cd igs_ps_ver.course_cd%TYPE,
1491 cp_transaction_type igs_fi_inv_int.transaction_type%TYPE,
1492 cp_uoo_id igs_fi_invln_int.uoo_id%TYPE,
1493 cp_location_cd igs_fi_invln_int.location_cd%TYPE,
1494 cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
1495 SELECT inv.rowid row_id,
1496 inv.invoice_id invoice_id,
1497 inv.invoice_amount_due invoice_amount_due,
1498 inv.invoice_amount invoice_amount
1499 FROM igs_fi_inv_int inv,
1500 igs_fi_invln_int iln
1501 WHERE inv.invoice_id = iln.invoice_id
1502 AND cp_invoice_id IS NULL
1503 AND inv.person_id = cp_person_id
1504 AND inv.fee_type = cp_fee_type
1505 AND inv.fee_cal_type = cp_fee_cal_type
1506 AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
1507 AND inv.transaction_type = cp_transaction_type
1508 AND iln.error_account <> g_ind_yes
1509 AND ((iln.uoo_id = cp_uoo_id)
1510 OR (iln.uoo_id IS NULL AND cp_uoo_id IS NULL))
1511 AND ((iln.location_cd = cp_location_cd)
1512 OR (iln.location_cd IS NULL AND cp_location_cd IS NULL))
1513 AND ((fee_cat = cp_fee_cat)
1514 OR (fee_cat IS NULL and cp_fee_cat IS NULL))
1515 AND ((course_cd = cp_course_cd)
1516 OR (course_cd IS NULL and cp_course_cd IS NULL))
1517 ORDER BY invoice_id ASC;
1518
1519 -- Cursor for selecting all the charges based on the Invoice ID. This is used if the invoice_id has been passed.
1520 CURSOR cur_charges_inv(cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
1521 SELECT inv.rowid row_id,
1522 inv.invoice_id,
1523 inv.invoice_amount_due,
1524 inv.invoice_amount
1525 FROM igs_fi_inv_int_all inv
1526 WHERE inv.invoice_id = cp_invoice_id
1527 ORDER BY invoice_id ASC;
1528
1529 -- Cursor to fetch sum of all negative adjustment credits for a given invoice_id
1530 CURSOR cur_chgadj(cp_invoice_id igs_fi_inv_int_all.invoice_id%TYPE) IS
1531 SELECT SUM(amount_applied)
1532 FROM igs_fi_credits fc,
1533 igs_fi_cr_types crt,
1534 igs_fi_applications app
1535 WHERE app.invoice_id = cp_invoice_id
1536 AND app.credit_id = fc.credit_id
1537 AND fc.status = g_cleared
1538 AND fc.credit_type_id = crt.credit_type_id
1539 AND crt.credit_class = g_neg_cr_class;
1540
1541 l_n_elg_amt igs_fi_inv_int_all.invoice_amount%TYPE;
1542 l_n_chgadj_amt igs_fi_applications.amount_applied%TYPE;
1543
1544 BEGIN
1545
1546
1547 -- Savepoint
1548 SAVEPOINT create_charge_pvt;
1549 l_v_igs_fi_auto_calc_waivers := NVL(FND_PROFILE.VALUE('IGS_FI_AUTO_CALC_WAIVERS'),'N');
1550
1551 -- Check if the API call is compatible
1552 IF NOT fnd_api.compatible_api_call( l_api_version,
1553 p_api_version,
1554 l_api_name,
1555 g_pkg_name) THEN
1556
1557 -- If the call is incompatible, then raise the unexpected error
1558 RAISE fnd_api.g_exc_unexpected_error;
1559 END IF;
1560
1561 l_n_invoice_amount := igs_fi_gen_gl.get_formatted_amount(p_header_rec.p_invoice_amount);
1562
1563 -- initialise the table which returns the invice lines id
1564 x_line_id_tbl.DELETE;
1565
1566 -- If the p_init_msg_list is T, i.e. the calling program wants to initialise
1567 -- the message list, then the message list is initialised using the API call
1568 IF fnd_api.to_boolean(p_init_msg_list) THEN
1569 fnd_msg_pub.initialize;
1570 END IF;
1571
1572 -- Set the return status as success for the api
1573 x_return_status := fnd_api.g_ret_sts_success;
1574
1575 -- Get the value of "Manage Accounts" System Option value.
1576 -- If this value is NULL then this process should error out.
1577 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
1578 p_v_message_name => l_v_message_name );
1579 IF l_v_manage_accounts IS NULL THEN
1580 fnd_message.set_name ( 'IGS', l_v_message_name );
1581 fnd_msg_pub.add;
1582 RAISE fnd_api.g_exc_error;
1583 END IF;
1584
1585 -- Fetch the record from the Control table.
1586 OPEN cur_ctrl;
1587 FETCH cur_ctrl INTO l_ctrl_rec;
1588 CLOSE cur_ctrl;
1589 --If Oracle General Ledger is not installed and Account Conversion Flag is null, raise error.
1590 IF l_ctrl_rec.rec_installed = 'N' AND l_ctrl_rec.acct_conv_flag IS NULL THEN
1591 fnd_message.set_name('IGS','IGS_FI_ACCT_CONV_NOTRUN');
1592 fnd_msg_pub.add;
1593 RAISE fnd_api.g_exc_error;
1597 l_v_message_name := NULL;
1594 END IF;
1595
1596 g_v_manage_accounts := l_v_manage_accounts;
1598 -- Check for Holds Balance Conversion Process and Existance of Balance Rule for Holds Balance
1599 -- are not required if Manage Accounts Option has value OTHER.
1600 IF l_v_manage_accounts <> 'OTHER' THEN
1601 igs_fi_gen_007.finp_get_conv_prc_run_ind(
1602 p_n_conv_process_run_ind => l_cnv_prc,
1603 p_v_message_name => l_message_name
1604 );
1605
1606 --If Holds Balance conversion process is running..error out from Charges API.
1607 IF l_cnv_prc = 1 AND l_message_name IS NULL THEN
1608 fnd_message.set_name('IGS','IGS_FI_REASS_BAL_PRC_RUN');
1609 fnd_msg_pub.add;
1610 RAISE fnd_api.g_exc_error;
1611 END IF;
1612
1613 IF l_message_name IS NOT NULL THEN
1614 fnd_message.set_name('IGS',l_message_name);
1615 fnd_msg_pub.add;
1616 RAISE fnd_api.g_exc_error;
1617 END IF;
1618
1619 --Get the latest active balance rule for 'HOLDS' balance type.
1620 igs_fi_gen_007.finp_get_balance_rule(
1621 p_v_balance_type => l_hold_bal_type,
1622 p_v_action => l_action_active,
1623 p_n_balance_rule_id => l_balance_rule_id,
1624 p_d_last_conversion_date=> l_last_conversion_date,
1625 p_n_version_number => l_version_number
1626 );
1627
1628 --If no active balance rule exists for 'HOLDS', error out Charges API.
1629 IF l_version_number = 0 THEN
1630 fnd_message.set_name('IGS','IGS_FI_CANNOT_CRT_TXN');
1631 fnd_msg_pub.add;
1632 RAISE fnd_api.g_exc_error;
1633 END IF;
1634 END IF;
1635
1636 -- Check if there are records in the Lines table being input to API
1637
1638 IF p_line_tbl.COUNT = 0 THEN
1639 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1640 fnd_message.set_name('IGS','IGS_FI_SAT_NO_ROWS_FOUND');
1641 fnd_msg_pub.add;
1642 RAISE fnd_api.g_exc_error;
1643 END IF;
1644 END IF;
1645
1646 --Following validations are not necessary to be performed in case of negative charges
1647 --because in case of negative charges, only a credit will be created.
1648 IF ( NVL(l_n_invoice_amount,0) >= 0 ) THEN
1649 -- Call the function for validating the person id
1650 l_valid_person:= igs_fi_gen_007.validate_person(p_header_rec.p_person_id); --bug:2238362
1651
1652 -- If the function returns false, then
1653 IF l_valid_person = 'N' THEN --bug:2238362
1654
1655 -- Add the message to the Message list and error out.
1656 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1657 l_valid := FALSE;
1658 fnd_message.set_name('IGS','IGS_FI_INVALID_PERSON');
1659 fnd_message.set_token('PERSON_ID',TO_CHAR(p_header_rec.p_person_id));
1660 fnd_msg_pub.add;
1661 RAISE fnd_api.g_exc_error;
1662 END IF;
1663 END IF;
1664
1665
1666 -- Call the function for validating the Fee Type Calendar Instance
1667 l_bool := validate_ftci(p_header_rec.p_fee_cal_type,
1668 p_header_rec.p_fee_ci_sequence_number,
1669 p_header_rec.p_fee_type,
1670 p_header_rec.p_transaction_type);
1671
1672 -- If the function returns false then
1673 IF NOT l_bool THEN
1674 -- Add the message to the message list and error out.
1675 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1676 --Fetch the cursor to get FEE CI description to pass as token for IGS_FI_INVALID_FTCI
1677 OPEN cur_alt_cd_desc(p_header_rec.p_fee_cal_type,
1678 p_header_rec.p_fee_ci_sequence_number);
1679 FETCH cur_alt_cd_desc INTO l_v_fee_alt_cd, l_v_fee_ci_desc;
1680 CLOSE cur_alt_cd_desc;
1681
1682 l_valid := FALSE;
1683 fnd_message.set_name('IGS','IGS_FI_INVALID_FTCI');
1684 fnd_message.set_token('FEE_TYPE',p_header_rec.p_fee_type);
1685 fnd_message.set_token('CI_DESC',l_v_fee_ci_desc);
1686 fnd_msg_pub.add;
1687 RAISE fnd_api.g_exc_error;
1688 END IF;
1689 END IF;
1690 -- call the function for validating the Fee Category Fee Liability
1691 IF p_header_rec.p_fee_cat IS NOT NULL THEN
1692 l_bool := validate_fcfl(p_header_rec.p_fee_cat,
1693 p_header_rec.p_fee_type,
1694 p_header_rec.p_transaction_type);
1695
1696 -- If the function returns false then
1697 IF NOT l_bool THEN
1698 -- Add the message to the Message list and error out.
1699 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1700 l_valid := FALSE;
1701 fnd_message.set_name('IGS','IGS_FI_INVALID_FCFL');
1702 fnd_message.set_token('FEE_CAT',p_header_rec.p_fee_cat);
1703 fnd_message.set_token('FEE_TYPE',p_header_rec.p_fee_type);
1704 fnd_msg_pub.add;
1705 RAISE fnd_api.g_exc_error;
1706 END IF;
1707 END IF;
1708 END IF;
1709 -- Call the function for validating the Program
1710 IF p_header_rec.p_course_cd IS NOT NULL THEN
1714 IF NOT l_bool THEN
1711 l_bool := validate_course(p_header_rec.p_course_cd);
1712
1713 -- If the function returns false, then
1715 -- Add the message to the Message list and error out.
1716 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1717 l_valid := FALSE;
1718 fnd_message.set_name('IGS','IGS_FI_INVALID_COURSE');
1719 fnd_message.set_token('COURSE_CD',p_header_rec.p_course_cd);
1720 fnd_msg_pub.add;
1721 RAISE fnd_api.g_exc_error;
1722 END IF;
1723 END IF;
1724 END IF;
1725
1726 --Removed the code that deals with the validation of the parameter p_header_rec.p_subaccount_id as
1727 --part of subaccount removal build. Enh#2564643.
1728
1729 -- Validate if the transaction type passed is a valid transaction type
1730 l_bool := igs_fi_crdapi_util.validate_igs_lkp( p_v_lookup_type => g_transaction_type,
1731 p_v_lookup_code => p_header_rec.p_transaction_type);
1732
1733 -- If the function returns false, then
1734 IF NOT l_bool THEN
1735 -- Add the message to the message stack and error out.
1736 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1737 l_valid := FALSE;
1738 fnd_message.set_name('IGS','IGS_FI_INVALID_TXN_TYPE');
1739 fnd_message.set_token('TXN_TYPE',p_header_rec.p_transaction_type);
1740 fnd_msg_pub.add;
1741 RAISE fnd_api.g_exc_error;
1742 END IF;
1743 END IF;
1744
1745 -- Validate if the currency code passed is a valid currency code
1746 l_bool := validate_cur(p_header_rec.p_currency_cd);
1747
1748 -- If the currency code is invalid, then
1749 IF NOT l_bool THEN
1750 -- Add the message to the message list and error out.
1751 -- Error message changed as part of bug 2584986
1752 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1753 l_valid := FALSE;
1754 fnd_message.set_name('IGS','IGS_FI_INVALID_CUR');
1755 fnd_message.set_token('CUR_CD',p_header_rec.p_currency_cd);
1756 fnd_msg_pub.add;
1757 RAISE fnd_api.g_exc_error;
1758 END IF;
1759 END IF;
1760 --3392095 check for waiver_name
1761 IF p_header_rec.p_waiver_name IS NOT NULL THEN
1762
1763 OPEN cur_waiver(p_header_rec.p_fee_cal_type,
1764 p_header_rec.p_fee_ci_sequence_number,
1765 p_header_rec.p_waiver_name);
1766
1767 FETCH cur_waiver INTO l_v_waiver_exists;
1768 IF cur_waiver%NOTFOUND THEN
1769 CLOSE cur_waiver;
1770 --Cursor to get alternate code to pass as token for IGS_FI_WAV_PGM_NO_REC_FOUND
1771 OPEN cur_alt_cd_desc(p_header_rec.p_fee_cal_type,
1772 p_header_rec.p_fee_ci_sequence_number);
1773 FETCH cur_alt_cd_desc INTO l_v_fee_alt_cd, l_v_fee_ci_desc;
1774 CLOSE cur_alt_cd_desc;
1775
1776 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1777 fnd_message.set_name('IGS','IGS_FI_WAV_PGM_NO_REC_FOUND');
1778 fnd_message.set_token('FEE_ALT_CD',l_v_fee_alt_cd);
1779 fnd_message.set_token('FEE_TYPE',p_header_rec.p_fee_type);
1780 fnd_msg_pub.add;
1781 RAISE fnd_api.g_exc_error;
1782 END IF;
1783 END IF;
1784 CLOSE cur_waiver;
1785
1786 END IF;
1787
1788
1789 --Check for validity of the credit instrument 'ADJ' as on system date.
1790 IF NOT igs_fi_crdapi_util.validate_igs_lkp( p_v_lookup_type => 'IGS_FI_CREDIT_INSTRUMENT',
1791 p_v_lookup_code => g_adj) THEN
1792 --If not valid, then raise error and stop processing further.
1793 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1794 fnd_message.set_name('IGS','IGS_FI_CAPI_CRD_INSTR_NULL');
1795 fnd_message.set_token('CR_INSTR',g_adj);
1796 fnd_msg_pub.add;
1797 RAISE fnd_api.g_exc_error;
1798 END IF;
1799 END IF;
1800
1801
1802 -- Loop through the table
1803 FOR l_var IN p_line_tbl.FIRST..p_line_tbl.LAST LOOP
1804
1805 -- Validate if the Charge Method passed is a valid lookup
1806 IF p_line_tbl(l_var).p_s_chg_method_type IS NOT NULL THEN
1807 l_bool := igs_fi_crdapi_util.validate_igs_lkp(p_v_lookup_type => g_chg_method,
1808 p_v_lookup_code => p_line_tbl(l_var).p_s_chg_method_type);
1809
1810 -- If the charge method passed for one of the records is not valid, then
1811 IF NOT l_bool THEN
1812 -- Add the message to the message list and error out.
1813 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1814 l_valid := FALSE;
1815 fnd_message.set_name('IGS','IGS_FI_INV_CHG_MTHD_TYPE');
1816 fnd_message.set_token('CHG_METHOD',p_line_tbl(l_var).p_s_chg_method_type);
1817 fnd_msg_pub.add;
1818 EXIT;
1819 END IF;
1820 END IF;
1821 END IF;
1822 END LOOP;
1823
1824 -- If the global variable has been set to false, then this means
1825 -- that one of the charge methods were invalid, hence error out.
1826 IF NOT l_valid THEN
1827 RAISE fnd_api.g_exc_error;
1828 END IF;
1829 -- Loop through the table for the Unit Section
1830 FOR l_var IN p_line_tbl.FIRST..p_line_tbl.LAST LOOP
1831
1835 IF p_line_tbl(l_var).p_uoo_id IS NOT NULL THEN
1832 -- If the unit section has been passed in the table, then
1833 -- As part of the bug #1962286 p_uoo_id is being checked for NOT NULL instead of p_unit_cd
1834
1836
1837 -- Call the function for validating the Unit Section
1838
1839 -- As part of the bug #1962286 the call to validate_uoo was modified.
1840 -- Instead of passing all the six UOO columns only the uoo_id is being passed
1841
1842 l_bool := validate_uoo(p_line_tbl(l_var).p_uoo_id );
1843
1844 -- If the function returns false, then
1845 IF NOT l_bool THEN
1846 -- Add the message to the message list
1847 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1848 l_valid := FALSE;
1849 fnd_message.set_name('IGS','IGS_FI_INVALID_UOO_ID');
1850 fnd_msg_pub.add;
1851 EXIT;
1852 END IF;
1853 END IF;
1854 END IF;
1855 END LOOP;
1856
1857 -- If the global variable has been set to false, then this means
1858 -- that one of the Unit Sections were invalid, hence error out.
1859 IF NOT l_valid THEN
1860 RAISE fnd_api.g_exc_error;
1861 END IF;
1862
1863
1864 -- Loop through the table
1865 FOR l_var IN p_line_tbl.FIRST..p_line_tbl.LAST LOOP
1866
1867 -- If the Org_Unit_Cd is passed into the table then
1868 IF p_line_tbl(l_var).p_org_unit_cd IS NOT NULL THEN
1869
1870 -- Call the function for validating the Org_Unit_Cd
1871 l_bool := validate_org_unit_cd(p_line_tbl(l_var).p_org_unit_cd);
1872
1873 -- If the function returns false, then
1874 IF NOT l_bool THEN
1875 -- Add the message to the message list
1876 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1877 l_valid := FALSE;
1878 fnd_message.set_name('IGS','IGS_FI_INVALID_ORG_UNIT_CD');
1879 fnd_message.set_token('ORG_CD',p_line_tbl(l_var).p_org_unit_cd);
1880 fnd_msg_pub.add;
1881 EXIT;
1882 END IF;
1883 END IF;
1884 END IF;
1885 END LOOP;
1886
1887 -- If the global variable has been set to false, then this means
1888 -- that one of the Org_Unit_Cd were invalid, hence error out.
1889 IF NOT l_valid THEN
1890 RAISE fnd_api.g_exc_error;
1891 END IF;
1892
1893
1894 -- If the Source Transaction Id is not null
1895 IF p_header_rec.p_source_transaction_id IS NOT NULL THEN
1896
1897 -- Call the procedure for validating the Source Transaction Id
1898 -- This source transaction id should be a valid Invoice Id in the
1899 -- IGS_FI_INV_INT table
1900 l_bool := validate_source_txn_id(p_header_rec.p_source_transaction_id);
1901
1902 -- If the function returns false, then
1903 IF NOT l_bool THEN
1904
1905 -- Add the message to the message list and error out.
1906 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1907 l_valid := FALSE;
1908 fnd_message.set_name('IGS','IGS_FI_INVALID_SRC_TXN_ID');
1909 fnd_msg_pub.add;
1910 RAISE fnd_api.g_exc_error;
1911 END IF;
1912 END IF;
1913 END IF;
1914
1915 -- If the global boolean flag has been set, then
1916 -- error out.
1917 IF NOT l_valid THEN
1918 RAISE fnd_api.g_exc_error;
1919 END IF;
1920 END IF;
1921
1922
1923
1924 -- If the Invoice Amount passed to the Charges API is negative, i.e. it is an adjustment over the
1925 -- earlier charges, then the negative charges processing needs to be done.
1926 IF NVL(l_n_invoice_amount,0) < 0 THEN
1927 g_inv_amt_due := 0;
1928
1929 -- validate if the negative amount passed as input to the Charges API is
1930 l_val_neg_amnt := validate_neg_amt(p_person_id => p_header_rec.p_person_id,
1931 p_fee_type => p_header_rec.p_fee_type,
1932 p_fee_cal_type => p_header_rec.p_fee_cal_type,
1933 p_fee_ci_sequence_number => p_header_rec.p_fee_ci_sequence_number,
1934 p_fee_cat => p_header_rec.p_fee_cat,
1935 p_course_cd => p_header_rec.p_course_cd,
1936 p_uoo_id => p_line_tbl(1).p_uoo_id,
1937 p_location_cd => p_line_tbl(1).p_location_cd,
1938 p_transaction_type => p_header_rec.p_transaction_type,
1939 p_source_txn_id => p_header_rec.p_source_transaction_id,
1940 p_amt => l_n_invoice_amount);
1941
1942 -- If the validate negative amount procedure returns an error, then exit out of the charges api
1943 IF l_val_neg_amnt = 1 THEN
1944 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1945 l_valid := FALSE;
1946 fnd_message.set_name('IGS','IGS_FI_SRC_TXN_ACC_INV');
1947 fnd_msg_pub.add;
1948 RAISE fnd_api.g_exc_error;
1949 END IF;
1950 ELSIF l_val_neg_amnt = 2 THEN
1951 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1952 l_valid := FALSE;
1956 END IF;
1953 fnd_message.set_name('IGS','IGS_FI_INVALID_INV_AMT');
1954 fnd_msg_pub.add;
1955 RAISE fnd_api.g_exc_error;
1957 END IF;
1958
1959 l_rec_exist := FALSE;
1960 l_adj_amt := NVL(ABS(l_n_invoice_amount),0);
1961
1962 -- If Invoice ID has been passed, use cursor cur_chg_inv
1963 IF p_header_rec.p_source_transaction_id IS NOT NULL THEN
1964 OPEN cur_chg_inv(p_header_rec.p_source_transaction_id);
1965 FETCH cur_chg_inv INTO l_rec_chg_inv;
1966 IF cur_chg_inv%FOUND THEN
1967 CLOSE cur_chg_inv;
1968 IF NOT(chk_charge_adjusted(p_header_rec.p_source_transaction_id, l_rec_chg_inv.invoice_amount)) THEN
1969 l_rec_exist := TRUE;
1970
1971 -- If the Adjustment Amount is greater than 0, then
1972 IF NVL(l_adj_amt,0) > 0 THEN
1973 -- If the validate flag is true i.e. The invoice amount due is greater than the adjustment amount, then
1974 -- If the Adjustment Amount is greater than the Invoice Amount Due, then
1975 IF NVL(l_adj_amt,0) > NVL(l_rec_chg_inv.invoice_amount_due,0) THEN
1976 -- Amount for the negative charge is the invoice amount due
1977 l_amt_neg_chg := NVL(l_rec_chg_inv.invoice_amount_due,0);
1978 ELSE
1979 -- else the amount for the negative charge is the adjustment amount
1980 l_amt_neg_chg := NVL(l_adj_amt,0);
1981 END IF;
1982
1983 -- The adjustment amount is now reduced by the amount passed to the negative
1984 -- charges procedure
1985 l_adj_amt := NVL(l_adj_amt,0) - NVL(l_amt_neg_chg,0);
1986 -- Call the procedure for the negative charges
1987 proc_neg_chg(p_source_transaction_id => l_rec_chg_inv.invoice_id,
1988 p_adj_amount => l_amt_neg_chg,
1989 p_d_gl_date => p_line_tbl(1).p_d_gl_date,
1990 p_v_currency_cd => p_header_rec.p_currency_cd,
1991 p_err_msg => l_err_msg,
1992 p_status => l_status);
1993 -- If the procedure returns error, then raise the error message returned by the procedure.
1994 -- If the procedure has not passed the error message then just raise the error
1995 IF NOT l_status THEN
1996 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
1997 IF l_err_msg IS NOT NULL THEN
1998 fnd_message.set_name('IGS',l_err_msg);
1999 fnd_msg_pub.add;
2000 END IF;
2001 RAISE fnd_api.g_exc_error;
2002 END IF;
2003 END IF;
2004 END IF; -- End if for IF l_adj_amt > 0 THEN
2005 END IF; -- End if for IF NOT(chk_charge_adjusted())
2006 ELSE
2007 CLOSE cur_chg_inv;
2008 END IF; -- End if for IF cur_chg_inv%FOUND THEN
2009
2010 ELSE -- Else for IF p_header_rec.p_source_transaction_id IS NOT NULL THEN
2011
2012 -- Loop through the Charges records
2013 FOR chgrec IN cur_chg(p_header_rec.p_person_id,
2014 p_header_rec.p_fee_type,
2015 p_header_rec.p_fee_cal_type,
2016 p_header_rec.p_fee_ci_sequence_number,
2017 p_header_rec.p_fee_cat,
2018 p_header_rec.p_course_cd,
2019 p_header_rec.p_transaction_type,
2020 p_line_tbl(1).p_uoo_id,
2021 p_line_tbl(1).p_location_cd,
2022 p_header_rec.p_source_transaction_id) LOOP
2023 IF NOT(chk_charge_adjusted(p_header_rec.p_source_transaction_id, chgrec.invoice_amount)) THEN
2024 l_rec_exist := TRUE;
2025 chgrec.invoice_amount_due := igs_fi_gen_gl.get_formatted_amount(chgrec.invoice_amount_due);
2026 chgrec.invoice_amount := igs_fi_gen_gl.get_formatted_amount(chgrec.invoice_amount);
2027
2028 -- If the Adjustment Amount is greater than 0, then
2029 IF NVL(l_adj_amt,0) > 0 THEN
2030
2031 -- the invoice amount due is greater than the adjustment amount, then
2032 -- If the Adjustment Amount is greater than the Invoice Amount Due, then
2033 IF NVL(l_adj_amt,0) > NVL(chgrec.invoice_amount_due,0) THEN
2034 -- Amount for the negative charge is the invoice amount due
2035 l_amt_neg_chg := NVL(chgrec.invoice_amount_due,0);
2036 ELSE
2037 -- else the amount for the negative charge is the adjustment amount
2038 l_amt_neg_chg := NVL(l_adj_amt,0);
2039 END IF;
2040 -- The adjustment amount is now reduced by the amount passed to the negative
2041 -- charges procedure
2042 l_adj_amt := NVL(l_adj_amt,0) - NVL(l_amt_neg_chg,0);
2043
2044 -- Call the procedure for the negative charges
2045 -- Enh 2584986 - Added parameter p_gl_date
2046
2047 proc_neg_chg(p_source_transaction_id => chgrec.invoice_id,
2051 p_err_msg => l_err_msg,
2048 p_adj_amount => l_amt_neg_chg,
2049 p_d_gl_date => p_line_tbl(1).p_d_gl_date,
2050 p_v_currency_cd => p_header_rec.p_currency_cd,
2052 p_status => l_status);
2053
2054 -- If the procedure returns error, then raise the error message returned by the
2055 -- procedure. If the procedure has not passed the error message then just raise the
2056 -- error
2057 IF NOT l_status THEN
2058 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2059 IF l_err_msg IS NOT NULL THEN
2060 fnd_message.set_name('IGS',l_err_msg);
2061 fnd_msg_pub.add;
2062 END IF;
2063 RAISE fnd_api.g_exc_error;
2064 END IF;
2065 END IF;
2066 ELSE
2067 EXIT;
2068 END IF;
2069 END IF; -- End if for IF NOT(chk_charge_adjusted())
2070 END LOOP;
2071 END IF;
2072
2073 -- In case of negative charge or adjustment creation, there is no invoice record created
2074 -- hence the invoice id returned by the procedure is NULL
2075 x_invoice_id := NULL;
2076
2077 -- If no charges were found in cur_chg or if the Adjustment Amount is still not zero, then
2078 -- (Changes as part of Bug 5234312)
2079 IF (l_rec_exist = FALSE OR NVL(l_adj_amt,0) > 0) THEN
2080
2081 -- If Invoice ID has been passed, use cursor cur_chg_inv
2082 IF p_header_rec.p_source_transaction_id IS NOT NULL THEN
2083
2084 -- Fetch charges in ascending order of Invoice_Id
2085 FOR rec_charges IN cur_charges_inv(p_header_rec.p_source_transaction_id) LOOP
2086 IF NOT(chk_charge_adjusted(p_header_rec.p_source_transaction_id, rec_charges.invoice_amount)) THEN
2087
2088 -- If Adjustment Amount is not zero
2089 IF (NVL(l_adj_amt,0) > 0) THEN
2090 rec_charges.invoice_amount_due := igs_fi_gen_gl.get_formatted_amount(rec_charges.invoice_amount_due);
2091 rec_charges.invoice_amount := igs_fi_gen_gl.get_formatted_amount(rec_charges.invoice_amount);
2092
2093 -- Fetch sum of all negative adjustment credits for the Invoice in context
2094 OPEN cur_chgadj(rec_charges.invoice_id);
2095 FETCH cur_chgadj INTO l_n_chgadj_amt;
2096 CLOSE cur_chgadj;
2097
2098 -- Calculate Eligible Amount as difference between Invoice Amount and
2099 -- Sum of all neg adj credits for the Invoice
2100 l_n_elg_amt := NVL(rec_charges.invoice_amount,0) - NVL(l_n_chgadj_amt, 0);
2101
2102 -- Consider lesser of (Eligible Amount, Adjustment Amount) for l_amt_neg_chg
2103 IF (NVL(l_n_elg_amt,0) <= NVL(l_adj_amt,0)) THEN
2104 l_amt_neg_chg := NVL(l_n_elg_amt,0);
2105 ELSE
2106 l_amt_neg_chg := NVL(l_adj_amt,0);
2107 END IF;
2108
2109 -- Decrease Adjustment Amount by the amount that is being adjusted (l_amt_neg_chg)
2110 l_adj_amt := NVL(l_adj_amt,0) - NVL(l_amt_neg_chg,0);
2111
2112 proc_neg_chg(p_source_transaction_id => rec_charges.invoice_id,
2113 p_adj_amount => l_amt_neg_chg,
2114 p_d_gl_date => p_line_tbl(1).p_d_gl_date,
2115 p_v_currency_cd => p_header_rec.p_currency_cd,
2116 p_err_msg => l_err_msg,
2117 p_status => l_status);
2118 IF NOT l_status THEN
2119 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2120 IF l_err_msg IS NOT NULL THEN
2121 fnd_message.set_name('IGS',l_err_msg);
2122 fnd_msg_pub.add;
2123 END IF;
2124 RAISE fnd_api.g_exc_error;
2125 END IF;
2126 END IF;
2127 END IF; -- End if for IF (NVL(l_adj_amt,0) > 0) THEN
2128 END IF; -- End if for NOT(chk_charge_adjusted)
2129 END LOOP;
2130 ELSE
2131 -- If Invoice ID is not being passed, then use the UK to fetch the charges
2132 -- Fetch charges in ascending order of Invoice_Id
2133 FOR rec_charges IN cur_charges(p_header_rec.p_person_id,
2134 p_header_rec.p_fee_type,
2135 p_header_rec.p_fee_cal_type,
2136 p_header_rec.p_fee_ci_sequence_number,
2137 p_header_rec.p_fee_cat,
2138 p_header_rec.p_course_cd,
2139 p_header_rec.p_transaction_type,
2140 p_line_tbl(1).p_uoo_id,
2141 p_line_tbl(1).p_location_cd,
2142 p_header_rec.p_source_transaction_id) LOOP
2143
2147 IF (NVL(l_adj_amt,0) > 0) THEN
2144 IF NOT(chk_charge_adjusted(p_header_rec.p_source_transaction_id, rec_charges.invoice_amount)) THEN
2145
2146 -- If Adjustment Amount is not zero
2148 rec_charges.invoice_amount_due := igs_fi_gen_gl.get_formatted_amount(rec_charges.invoice_amount_due);
2149 rec_charges.invoice_amount := igs_fi_gen_gl.get_formatted_amount(rec_charges.invoice_amount);
2150
2151 -- Fetch sum of all negative adjustment credits for the Invoice in context
2152 OPEN cur_chgadj(rec_charges.invoice_id);
2153 FETCH cur_chgadj INTO l_n_chgadj_amt;
2154 CLOSE cur_chgadj;
2155
2156 -- Calculate Eligible Amount as difference between Invoice Amount and
2157 -- Sum of all neg adj credits for the Invoice
2158 l_n_elg_amt := NVL(rec_charges.invoice_amount,0) - NVL(l_n_chgadj_amt, 0);
2159
2160 -- Consider lesser of (Eligible Amount, Adjustment Amount) for l_amt_neg_chg
2161 IF (NVL(l_n_elg_amt,0) <= NVL(l_adj_amt,0)) THEN
2162 l_amt_neg_chg := NVL(l_n_elg_amt,0);
2163 ELSE
2164 l_amt_neg_chg := NVL(l_adj_amt,0);
2165 END IF;
2166
2167 -- Decrease Adjustment Amount by the amount that is being adjusted (l_amt_neg_chg)
2168 l_adj_amt := NVL(l_adj_amt,0) - NVL(l_amt_neg_chg,0);
2169
2170 proc_neg_chg(p_source_transaction_id => rec_charges.invoice_id,
2171 p_adj_amount => l_amt_neg_chg,
2172 p_d_gl_date => p_line_tbl(1).p_d_gl_date,
2173 p_v_currency_cd => p_header_rec.p_currency_cd,
2174 p_err_msg => l_err_msg,
2175 p_status => l_status);
2176 IF NOT l_status THEN
2177 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2178 IF l_err_msg IS NOT NULL THEN
2179 fnd_message.set_name('IGS',l_err_msg);
2180 fnd_msg_pub.add;
2181 END IF;
2182 RAISE fnd_api.g_exc_error;
2183 END IF;
2184 END IF;
2185 END IF; -- End if for IF (NVL(l_adj_amt,0) > 0) THEN
2186 END IF; -- End if for NOT(chk_charge_adjusted)
2187 END LOOP;
2188 END IF; -- End if for p_header_rec.p_source_transaction_id IS NOT NULL
2189
2190 END IF;
2191
2192 END IF; -- End if for NVL(l_n_invoice_amount,0) < 0
2193
2194 -- If the Invoice Amount passed to the Charges API is greater than zero,
2195 -- this means that this is a positive charge and needs to be created normally
2196 IF NVL(l_n_invoice_amount,0) >= 0 THEN --bug :2222272
2197
2198 -- Added following code for bug#2998266
2199 -- Fetch the sequence value which will be used to generate unique charge numbers.
2200 OPEN c_get_nextval;
2201 FETCH c_get_nextval INTO l_n_get_nextval;
2202 CLOSE c_get_nextval;
2203
2204 l_rowid := NULL;
2205 l_invoice_id := NULL;
2206 -- Call the table handler for the Charges table for creating the charges record
2207 -- from the header record passed as input to the API
2208
2209 -- Modified by jbegum as part of Enh bug #2228910
2210 -- Removed the column source_transaction_id in the call to IGS_FI_INV_INT_PKG.Insert_Row
2211 -- Added column reversal_gl_date in call to insert_row part of bug 2584986
2212
2213 -- Call to igs_fi_gen_gl.get_formatted_amount formats amounts by rounding off to currency precision
2214 igs_fi_inv_int_pkg.insert_row(x_rowid => l_rowid,
2215 x_invoice_id => l_invoice_id,
2216 x_person_id => p_header_rec.p_person_id,
2217 x_fee_type => p_header_rec.p_fee_type,
2218 x_fee_cat => p_header_rec.p_fee_cat,
2219 x_fee_cal_type => p_header_rec.p_fee_cal_type,
2220 x_fee_ci_sequence_number => p_header_rec.p_fee_ci_sequence_number,
2221 x_course_cd => p_header_rec.p_course_cd,
2222 x_attendance_mode => p_header_rec.p_attendance_mode,
2223 x_attendance_type => p_header_rec.p_attendance_type,
2224 x_invoice_amount_due => l_n_invoice_amount,
2225 x_invoice_creation_date => p_header_rec.p_invoice_creation_date,
2226 x_invoice_desc => p_header_rec.p_invoice_desc,
2227 x_transaction_type => p_header_rec.p_transaction_type,
2228 x_currency_cd => p_header_rec.p_currency_cd,
2229 x_status => 'TODO',
2230 x_attribute_category => NULL,
2231 x_attribute1 => NULL,
2232 x_attribute2 => NULL,
2233 x_attribute3 => NULL,
2234 x_attribute4 => NULL,
2235 x_attribute5 => NULL,
2236 x_attribute6 => NULL,
2237 x_attribute7 => NULL,
2238 x_attribute8 => NULL,
2239 x_attribute9 => NULL,
2240 x_attribute10 => NULL,
2241 x_invoice_amount => l_n_invoice_amount,
2242 x_bill_id => NULL,
2243 x_bill_number => NULL,
2244 x_bill_date => NULL,
2245 x_waiver_flag => p_header_rec.p_waiver_flag, -- Enh BUG 2030448, Removed Balance Flag Column
2246 x_waiver_reason => p_header_rec.p_waiver_reason,
2247 x_effective_date => p_header_rec.p_effective_date,
2248 x_invoice_number => l_n_get_nextval,
2249 x_exchange_rate => 1, -- Always passed as 1
2250 x_org_id => l_org_id,
2251 x_bill_payment_due_date => NULL,
2252 x_optional_fee_flag => NULL,
2253 x_reversal_gl_date => NULL,
2254 x_tax_year_code => NULL,
2255 x_waiver_name => p_header_rec.p_waiver_name --Enh 3392095 Added waiver_name
2256 );
2257
2258 -- Loop through the PL/SQL table for the Charges Lines records
2259 FOR l_var IN p_line_tbl.FIRST..p_line_tbl.LAST LOOP
2260 l_rowid := NULL;
2261 l_invoice_lines_id := NULL;
2262 l_n_amount := igs_fi_gen_gl.get_formatted_amount(p_line_tbl(l_var).p_amount);
2263
2264 -- Modifacation done by kkillams; w.r.t to finance accounting dld bug id : 1882122
2265 l_dr_gl_ccid := p_line_tbl(l_var).p_override_dr_rec_ccid;
2266 l_cr_gl_ccid := p_line_tbl(l_var).p_override_cr_rev_ccid;
2267 l_dr_account_cd := p_line_tbl(l_var).p_override_dr_rec_account_cd;
2268 l_cr_account_cd := p_line_tbl(l_var).p_override_cr_rev_account_cd;
2269
2270 l_error_string := NULL;
2271 l_error_account := NULL;
2272
2273 -- Following If condition checking for transaction type was added as part of Enhancement bug#2113459
2274 -- Added by jbegum
2275
2276 -- Bug# 3288973, Removed the check of Retention System Fee Type.
2277 -- Handling of Retention fee types is done in Build Accounts Process itself.
2278 call_build_process (p_header_rec => p_header_rec,
2279 p_line_rec => p_line_tbl(l_var),
2280 p_dr_gl_ccid => l_dr_gl_ccid,
2281 p_cr_gl_ccid => l_cr_gl_ccid,
2282 p_dr_account_cd => l_dr_account_cd,
2283 p_cr_account_cd => l_cr_account_cd,
2284 p_error_string => l_error_string,
2285 p_flag => l_flag);
2286 IF l_flag =1 THEN
2287 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2288 fnd_message.set_name('IGS',l_error_string);
2289 fnd_msg_pub.add;
2290 RAISE fnd_api.g_exc_error;
2291 END IF;
2292 l_error_account := 'Y';
2293 ELSIF l_flag =2 THEN
2294 -- if the transaction type is Document then the user should not be allowed to create
2295 -- any charge with error account as 'Y' when revenue account segment derivation fails
2296 -- even after zero fill flag is provided
2297 l_error_account := 'Y';
2298 IF p_header_rec.p_transaction_type = 'DOCUMENT' THEN
2299 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2300 fnd_message.set_name('IGS','IGS_FI_SRC_TXN_ACC_INV');
2301 fnd_msg_pub.add;
2302 RAISE fnd_api.g_exc_error;
2303 END IF;
2304 END IF;
2305 ELSE
2306 l_error_account := 'N';
2307 l_error_string := NULL;
2308 END IF;
2309
2310 -- As part of bug #1962286 the call to the IGS_FI_INVLN_INT_PKG.Insert_Row was modified.
2311 -- The six UOO columns were removed and the two new columns location_cd and uoo_id were added in the call
2312
2313 -- Modified by jbegum as part of Enh bug #2228910
2314 -- Removed the column source_transaction_id in the call to IGS_FI_INVLN_INT_PKG.Insert_Row
2315
2316 -- Call the Table Handler for creating the Charges Lines record
2317 -- Enh bug 2584986: Added parameters gl_date, gl_posted_date and posting_control_id in call to insert_row
2318
2319 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
2320 igs_fi_invln_int_pkg.insert_row(x_rowid => l_rowid,
2321 x_invoice_lines_id => l_invoice_lines_id,
2322 x_invoice_id => l_invoice_id,
2323 x_line_number => l_var,
2324 x_chg_elements => p_line_tbl(l_var).p_chg_elements,
2325 x_s_chg_method_type => p_line_tbl(l_var).p_s_chg_method_type,
2326 x_description => p_line_tbl(l_var).p_description,
2327 x_amount => l_n_amount,
2328 x_unit_attempt_status => p_line_tbl(l_var).p_unit_attempt_status,
2329 x_credit_points => p_line_tbl(l_var).p_credit_points,
2330 x_eftsu => p_line_tbl(l_var).p_eftsu,
2331 x_org_unit_cd => p_line_tbl(l_var).p_org_unit_cd,
2332 x_attribute_category => p_line_tbl(l_var).p_attribute_category,
2333 x_attribute1 => p_line_tbl(l_var).p_attribute1,
2334 x_attribute2 => p_line_tbl(l_var).p_attribute2,
2335 x_attribute3 => p_line_tbl(l_var).p_attribute3,
2336 x_attribute4 => p_line_tbl(l_var).p_attribute4,
2337 x_attribute5 => p_line_tbl(l_var).p_attribute5,
2338 x_attribute6 => p_line_tbl(l_var).p_attribute6,
2339 x_attribute7 => p_line_tbl(l_var).p_attribute7,
2340 x_attribute8 => p_line_tbl(l_var).p_attribute8,
2341 x_attribute9 => p_line_tbl(l_var).p_attribute9,
2342 x_attribute10 => p_line_tbl(l_var).p_attribute10,
2343 x_attribute11 => p_line_tbl(l_var).p_attribute11,
2344 x_attribute12 => p_line_tbl(l_var).p_attribute12,
2345 x_attribute13 => p_line_tbl(l_var).p_attribute13,
2346 x_attribute14 => p_line_tbl(l_var).p_attribute14,
2347 x_attribute15 => p_line_tbl(l_var).p_attribute15,
2348 x_attribute16 => p_line_tbl(l_var).p_attribute16,
2349 x_attribute17 => p_line_tbl(l_var).p_attribute17,
2350 x_attribute18 => p_line_tbl(l_var).p_attribute18,
2351 x_attribute19 => p_line_tbl(l_var).p_attribute19,
2352 x_attribute20 => p_line_tbl(l_var).p_attribute20,
2353 x_org_id => l_org_id,
2354 x_rec_account_cd => l_dr_account_cd,
2355 x_rev_account_cd => l_cr_account_cd,
2356 x_rec_gl_ccid => l_dr_gl_ccid,
2357 x_rev_gl_ccid => l_cr_gl_ccid,
2358 x_posting_id => NULL,
2359 x_error_string => SUBSTR(l_error_string,1,1000),
2360 x_error_account => l_error_account,
2361 x_location_cd => p_line_tbl(l_var).p_location_cd,
2362 x_uoo_id => p_line_tbl(l_var).p_uoo_id,
2363 x_gl_date => p_line_tbl(1).p_d_gl_date,
2364 x_gl_posted_date => NULL,
2365 x_posting_control_id => NULL,
2366 x_unit_type_id => p_line_tbl(l_var).p_unit_type_id,
2367 x_unit_level => p_line_tbl(l_var).p_unit_level
2368 );
2369
2370 -- Add the Invoice Line Id to the output table
2371 x_line_id_tbl(l_line_cnt) := l_invoice_lines_id;
2372 l_line_cnt := l_line_cnt + 1;
2373 END LOOP;
2374
2375 -- Call the Balances Process for updating the Balances table
2376 BEGIN
2377 l_valid := TRUE;
2378 l_message_name := NULL;
2379
2380 -- Enh BUG 2030448, Removed the call to the Calculate Balance Process. Call to Update_Balances has been added
2381 -- If any of the validations are failing then the process will return the error message
2382 -- Updation of Balances should not be done if Manage Accounts has value OTHER.
2383 IF l_v_manage_accounts <> 'OTHER' THEN
2384 igs_fi_prc_balances.update_balances ( p_party_id => p_header_rec.p_person_id,
2385 p_balance_type => g_standard,
2386 p_balance_date => TRUNC(p_header_rec.p_invoice_creation_date),
2387 p_amount => l_n_invoice_amount,
2388 p_source => g_charge,
2389 p_source_id => l_invoice_id,
2390 p_message_name => l_message_name);
2391
2392 -- Enh BUG 2030448, Check if the l_message_name is not null , If it is NOT NULL then there is an error occurred in
2393 -- the update_balances procedure
2394 IF l_message_name IS NOT NULL THEN
2395 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2396 l_valid := FALSE;
2397 fnd_message.set_name('IGS',l_message_name);
2398 fnd_msg_pub.add;
2399 RAISE fnd_api.g_exc_error;
2400 END IF;
2401 END IF;
2402 END IF;
2403
2404 EXCEPTION
2405
2406 -- If the process has raised some errors then set the valid flag
2407 -- to false
2408 WHEN OTHERS THEN
2409 l_valid := FALSE;
2410 END;
2411
2412 --removed the calls to igs_fi_gen_005.valiate_psa,igs_fi_party_sa_pkg.insert_row and related code,
2413 --as part of subaccount removal build(enh#2564643),as the table igs_fi_party_sa has been obsoleted.
2414
2415 BEGIN
2416 l_valid := TRUE;
2417 l_message_name := NULL;
2418
2419 -- Updation of Balances should not be done if Manage Accounts has value OTHER.
2420 IF l_v_manage_accounts <> 'OTHER' THEN
2421 igs_fi_prc_balances.update_balances ( p_party_id => p_header_rec.p_person_id,
2422 p_balance_type => l_hold_bal_type,
2423 p_balance_date => TRUNC(p_header_rec.p_invoice_creation_date),
2424 p_amount => l_n_invoice_amount,
2425 p_source => g_charge,
2426 p_source_id => l_invoice_id,
2427 p_message_name => l_message_name);
2428
2429 -- Check if the l_message_name is not null , If it is NOT NULL then there is an error occurred in
2430 -- the update_balances procedure
2431 IF l_message_name IS NOT NULL THEN
2432 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
2433 l_valid := FALSE;
2434 fnd_message.set_name('IGS',l_message_name);
2435 fnd_msg_pub.add;
2436 RAISE fnd_api.g_exc_error;
2437 END IF;
2438 END IF;
2439 END IF;
2440 EXCEPTION
2441 -- If the process has raised some errors then set the valid flag to false
2442 WHEN OTHERS THEN
2443 l_valid := FALSE;
2444 END;
2445 x_invoice_id := l_invoice_id;
2446 END IF;
2447
2448 --Enh 3392095 Create_waivers
2449 -- check if Manage Accounts Option has value OTHER.
2450 IF g_v_manage_accounts <> 'OTHER' THEN
2451 /* Check if the IGS_FI_AUTO_CALC_WAIVERS profile is set to Y */
2452 IF l_v_igs_fi_auto_calc_waivers = 'Y' THEN
2453 --checking P_REVERSE_FLAG holds a value other than "Y"
2454 IF (NVL(p_header_rec.p_reverse_flag,'N') <> 'Y') THEN
2455 IF p_header_rec.p_transaction_type NOT IN ('RETENTION', 'REFUND', 'SPONSOR', 'AID_ADJ',
2456 'PAY_PLAN', 'WAIVER_ADJ', 'ASSESSMENT') THEN
2457
2458 --Call create waivers routine
2459 igs_fi_prc_waivers.create_waivers( p_n_person_id => p_header_rec.p_person_id,
2460 p_v_fee_type => p_header_rec.p_fee_type,
2461 p_v_fee_cal_type => p_header_rec.p_fee_cal_type,
2462 p_n_fee_ci_seq_number => p_header_rec.p_fee_ci_sequence_number,
2463 p_v_waiver_name => p_header_rec.p_waiver_name,
2464 p_v_currency_cd => p_header_rec.p_currency_cd,
2465 p_d_gl_date => p_line_tbl(1).p_d_gl_date,
2466 p_v_real_time_flag => 'Y',
2467 p_v_process_mode => NULL,
2468 p_v_career => NULL,
2469 p_b_init_msg_list => FALSE,
2470 p_validation_level => 0,
2471 p_v_raise_wf_event => 'Y',
2472 x_waiver_amount => l_n_waiver_amount,
2473 x_return_status => l_v_return_status,
2474 x_msg_count => l_n_msg_count,
2475 x_msg_data => l_v_msg_data
2476 );
2477 --If return status of Error
2478 IF l_v_return_status = 'E' THEN
2479 -- Message that no transactions have been carried out due to some error
2480 fnd_message.set_name('IGS','IGS_FI_WAV_NO_TRANS_CREATED');
2481 fnd_msg_pub.add;
2482 x_waiver_amount := 0;
2483 --If return status of Success
2484 ELSIF l_v_return_status = 'S' THEN
2485 x_waiver_amount := l_n_waiver_amount;
2486 END IF;
2487 END IF;
2488 -- If the P_REVERSE_FLAG holds a value "Y" and P_INVOICE_AMOUNT is less than zero
2489 ELSIF (NVL(p_header_rec.p_reverse_flag,'N') = 'Y' AND NVL(l_n_invoice_amount,0) < 0 )THEN
2490 IF p_header_rec.p_transaction_type NOT IN ('RETENTION', 'REFUND', 'SPONSOR', 'AID_ADJ',
2491 'PAY_PLAN', 'WAIVER_ADJ') THEN
2492 --Call create waivers routine
2493 igs_fi_prc_waivers.create_waivers( p_n_person_id => p_header_rec.p_person_id,
2494 p_v_fee_type => p_header_rec.p_fee_type,
2495 p_v_fee_cal_type => p_header_rec.p_fee_cal_type,
2496 p_n_fee_ci_seq_number => p_header_rec.p_fee_ci_sequence_number,
2497 p_v_waiver_name => p_header_rec.p_waiver_name,
2498 p_v_currency_cd => p_header_rec.p_currency_cd,
2499 p_d_gl_date => p_line_tbl(1).p_d_gl_date,
2500 p_v_real_time_flag => 'Y',
2501 p_v_process_mode => NULL,
2502 p_v_career => NULL,
2503 p_b_init_msg_list => FALSE,
2504 p_v_raise_wf_event => 'Y',
2505 x_waiver_amount => l_n_waiver_amount,
2506 x_return_status => l_v_return_status,
2507 x_msg_count => l_n_msg_count,
2508 x_msg_data => l_v_msg_data
2509 );
2510 --If return status of Error
2511 IF l_v_return_status = 'E' THEN
2512 -- Message that no transactions have been carried out due to some error
2513 fnd_message.set_name('IGS','IGS_FI_WAV_NO_TRANS_CREATED');
2514 fnd_msg_pub.add;
2515 x_waiver_amount := 0;
2516 --If return status of Success
2517 ELSIF l_v_return_status = 'S' THEN
2518 x_waiver_amount := l_n_waiver_amount;
2519 END IF;
2520 END IF;
2521 END IF;
2522 END IF;
2523 END IF;
2524
2525 -- If the p_commit parameter is set to True and no errors have been raised by the
2526 -- balances process,
2527 -- then commit the work
2528 IF ((fnd_api.to_boolean(p_commit)) AND (l_valid)) THEN
2529 COMMIT WORK;
2530 END IF;
2531
2532 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2533 p_data => x_msg_data);
2534
2535 EXCEPTION
2536 WHEN fnd_api.g_exc_error THEN
2537 ROLLBACK TO create_charge_pvt;
2538 x_return_status := fnd_api.g_ret_sts_error;
2539 fnd_msg_pub.count_and_get( p_count => x_msg_count,
2540 p_data => x_msg_data);
2541
2542 WHEN fnd_api.g_exc_unexpected_error THEN
2543 ROLLBACK TO create_charge_pvt;
2544 x_return_status := fnd_api.g_ret_sts_unexp_error;
2545 fnd_msg_pub.count_and_get( p_count => x_msg_count,
2546 p_data => x_msg_data);
2547
2548 WHEN OTHERS THEN
2549 ROLLBACK TO create_charge_pvt;
2550 x_return_status := fnd_api.g_ret_sts_unexp_error;
2551
2552 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2553 fnd_msg_pub.add_exc_msg(g_pkg_name,
2554 l_api_name);
2555 END IF;
2556 fnd_msg_pub.count_and_get( p_count => x_msg_count,
2557 p_data => x_msg_data);
2558
2559 END create_charge;
2560
2561
2562 FUNCTION chk_charge_adjusted(p_n_invoice_id IN igs_fi_inv_int_all.invoice_id%TYPE,
2563 p_n_inv_amt IN igs_fi_inv_int_all.invoice_amount%TYPE) RETURN BOOLEAN AS
2564 /***********************************************************************************************
2565 Created By: Priya Athipatla
2566 Date Created By: 29-03-2006
2567 Purpose: This function validates if the charge has been completely adjusted or not.
2568 Known limitations,enhancements,remarks:
2569 Change History
2570 Who When What
2571 ********************************************************************************************** */
2572
2573 -- Cursor to check if the invoice has been completely adjusted previously
2574 CURSOR cur_chk_adj(cp_invoice_id igs_fi_inv_int_all.invoice_id%TYPE,
2575 cp_inv_amt igs_fi_inv_int_all.invoice_amount%TYPE) IS
2576 SELECT 'x'
2577 FROM igs_fi_credits_all fc,
2578 igs_fi_cr_types_all crt,
2579 igs_fi_applications app
2580 WHERE app.invoice_id = cp_invoice_id
2581 AND app.credit_id = fc.credit_id
2582 AND fc.status = g_cleared
2583 AND fc.credit_type_id = crt.credit_type_id
2584 AND crt.credit_class = g_neg_cr_class
2585 AND app.amount_applied = cp_inv_amt
2586 AND ROWNUM = 1;
2587
2588 l_v_chg_adjusted VARCHAR2(1);
2589
2590 BEGIN
2591
2592 -- If there is a negative adjustment credit against the charge, then return TRUE
2593 OPEN cur_chk_adj(p_n_invoice_id, p_n_inv_amt);
2594 FETCH cur_chk_adj INTO l_v_chg_adjusted;
2595 IF cur_chk_adj%FOUND THEN
2596 CLOSE cur_chk_adj;
2597 RETURN TRUE;
2598 ELSE
2599 CLOSE cur_chk_adj;
2600 RETURN FALSE;
2601 END IF;
2602
2603 END chk_charge_adjusted;
2604
2605 END igs_fi_charges_api_pvt;
[Home] [Help]