1 PACKAGE BODY igf_db_sf_integration AS
2 /* $Header: IGFDB06B.pls 120.10 2006/08/10 16:56:36 museshad ship $ */
3 ------------------------------------------------------------------
4 --Created by :Sarakshi , Oracle IDC
5 --Date created:24-Dec-2001
6 --
7 --Purpose: Package Body contains code for procedures/Functions defined in
8 -- package specification . Also body includes Functions/Procedures
9 -- private to it .
10 --
11 --Known limitations/enhancements and/or remarks:
12 --
13 --Change History:
14 --Who When What
15 --museshad 10-Aug-2006 Bug 5337555. Build FA 163. TBH Impact.
16 --svuppala 12-May-2006 Bug 5217319 Added call to format amount by rounding off to currency precision
17 -- in the igf_aw_awd_disb_pkg.update_row (l_disb_paid_amt), igf_aw_award_pkg.update_row
18 -- (l_paid_amt) calls in main_disbursement procedure
19 --pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
20 -- parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
21 --svuppala 18-JUL-2005 Enh 4213629 - Impact of automatic generation of the Receipt Number
22 -- changed parameters of igs_fi_credit_pvt.create_credit call
23 --ridas 08-Nov-2004 Bug 3021287 If the profile_value = 'TRANSFER'
24 -- then updating COA at the student level
25
26 --ayedubat 14-OCT-04 FA 149 COD-XML Standards build bug # 3416863
27 -- Changed the TBH calls of the packages: IGF_AW_AWD_DISB_PKG and IGF_DB_AWD_DISB_DTL_PKG
28 --smadathi 01-JUL-2004 Bug 3735396. GSCC Warning File.Sql.35 was also fixed as part of this bug.
29 -- The variable g_chgadj global to this package body is modified as CONSTANT
30 -- variable. Modified procedure transfer_disb_dtls_to_sf,function validate_persid_grp,
31 -- procedure main_disbursement
32 --pathipat 22-Apr-2004 Enh 3558549 - Commercial Receivables Enhancements build
33 -- Modified call_credits_api() and main_disbursement()
34 --veramach 3-NOV-2003 FA 125 Multiple Distr Methods
35 -- Changed signature of igf_aw_award_pkg.update_row(Added adplans_id to the tbh call)
36 -- Changed signature of igf_aw_awd_disb_pkg.update_row(Added attendance_type_code to the tbh call)
37 --pathipat 21-Aug-2003 Enh 3076768 - Auto Release of Holds build
38 -- Modified call_credits_api() - added check when return_status = 'S'
39 -- Modified main_disbursement()
40 --vvutukur 18-Jul-2003 Enh#3038511.FICR106 Build. Modified procedure transfer_disb_dtls_to_sf.
41 --SMADATHI 26-jun-2003 Bug 2852816. Modified procedures call_credits_api,call_charges_api,main_disbursement
42 -- Modified cursor cur_disb select to include fund_code column
43 --vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Modified the procedure call_credits_api.
44 --bkkumar 04-jun-2003 #2858504 Added legacy_record_flag and award_number_txt in the table handler calls for igf_aw_award_pkg.update_row
45 --shtatiko 02-MAY-2003 Enh# 2831569, Modified transfer_disb_dtls_to_sf and added check for Manage Accounts
46 -- System Option to cur_disb cursor.
47 --vvutukur 08-Apr-2003 Enh#2831554.Internal Credits API Build. Modified function lookup_desc and procedures
48 -- call_credits_api,call_charges_api,main_disbursement,transfer_disb_dtls_to_sf.
49 --shtatiko 26-MAR-2003 Bug# 2782124, modified transfer_disb_dtls_to_sf, main_disbursement
50 -- and log_messages.
51 --agairola 07-Mar-2003 Bug# 2814089: Following modifications have been done
52 -- 1. Call_Charges_Api procedure modified
53 -- 2. Call_Credits_Api procedure modified
54 -- 3. Added two global variables - g_separator
55 -- and g_chgadj
56 --vvutukur 26-Feb-2003 Enh#2758823.FA117 Build.Modified procedure main_disbursement.
57 --smadathi 06-Jan-2003 Bug 2684895. Modified transfer_disb_dtls_to_sf
58 --smadathi 31-dec-2002 Bug 2719776. Modified transfer_disb_dtls_to_sf. Modified the
59 -- cursor cur_disb select to remove the Non-mergable view in the select and
60 -- to reduce the Shared memory(M)
61 --smadathi 31-dec-2002 Bug 2620359. Modified the procedure main_disbursement
62 --smadathi 31-dec-2002 Bug 2620343. Modified function validate_persid_grp
63 --vvutukur 13-Dec-2002 Enh#2584741.Modified procedure call_credits_api.
64 --vvutukur 20-Nov-2002 Enh#2584986.Modifications done in transfer_disb_dtls_to_sf,main_disbursement,
65 -- call_charges_api,call_credits_api.
66 -- adhawan 25-oct-2002 Bug #2613546 Added ALT_PELL_SCHEDULE in igf_aw_award_pkg.update_row
67 --jbegum 21-Sep-2002 Bug#2564643 Modified call_credits_api and call_charges_api.
68 --smadathi 10-JUL-2002 Bug 2450332. call_credits_api modified.
69 --smadathi 03-Jun-2002 Bug 2349394. Added new private function get_bill_desc. Also modified call_credits_api,
70 -- call_charges_api. Modified cur_disb to select fund_id column also.
71 --SYkrishn 08-MAY-2002 Procedure main_disbursement - The column DISB_PAID_AMT in the table igf_aw_awd_disb is updated
72 -- with the Cumulation of existing Disb Paid amount with the newly disbursed amount (each iteration)
73 -- instead of overriding with the new value - Bug 2356801.
74 --sarakshi 18-Mar-2002 Bug:2144600, added logic for refunding the excess credit amount in
75 -- main_disbursement program unit
76 --vchappid 11-Feb-2002 Enh#2191470,modified cursor cur_disb, to include igf_aw_awd_disb table and
77 -- disb_dlt.ld_cal_type, disb_dlt.ld_sequence_number are included in the
78 -- cursor selected columns, removed reference to igf_aw_fund_tp_all and the where
79 -- clause is changed from tp_cal_type, tp_sequence_number to
80 -- disb_dlt.ld_cal_type, disb_dlt.ld_sequence_number
81 --
82 -------------------------------------------------------------------
83
84 -- Check for Manage Accounts System Option has been added to following cursor
85 -- so that transfer of disbursements for non-sponser parties from Financial Aid
86 -- is not allowed if Manage Accounts Option has value OTHER.
87 CURSOR cur_disb(cp_cal_type igf_aw_fund_mast_v.ci_cal_type%TYPE,
88 cp_sequence_number igf_aw_fund_mast_v.ci_sequence_number%TYPE,
89 cp_fund_id igf_aw_fund_mast_v.fund_id%TYPE,
90 cp_person_id igf_aw_award_v.person_id%TYPE,
91 cp_tp_cal_type igf_aw_fund_tp.tp_cal_type%TYPE,
92 cp_tp_sequence_number igf_aw_fund_tp.tp_sequence_number%TYPE,
93 cp_manage_accounts igs_fi_control_all.manage_accounts%TYPE
94 ) IS
95 SELECT disb_dlt.award_id,disb_dlt.disb_num,disb_dlt.disb_seq_num,disb_dlt.disb_date,
96 fab.person_id,fcat.fed_fund_code fed_fund_code,fnd.fee_type,fnd.party_id,fnd.spnsr_fee_type,
97 fcat.sys_fund_type sys_fund_type,fnd.ci_cal_type,fnd.ci_sequence_number,fnd.credit_type_id,
98 DECODE(disb_dlt.disb_seq_num,1,disb_dlt.disb_net_amt,disb_dlt.disb_adj_amt) amount,
99 NVL(disb_dlt.ld_cal_type,disb.ld_cal_type) ld_cal_type, NVL(disb_dlt.ld_sequence_number,disb.ld_sequence_number) ld_sequence_number,
100 fnd.fund_id, fnd.fund_code fund_code
101 FROM igf_db_awd_disb_dtl disb_dlt,
102 igf_aw_awd_disb disb,
103 igf_aw_award awd,
104 igf_aw_fund_mast fnd,
105 igf_aw_fund_cat fcat,
106 igf_ap_fa_base_rec fab
107 WHERE disb_dlt.award_id = disb.award_id
108 AND disb_dlt.disb_num = disb.disb_num
109 AND disb.award_id = awd.award_id
110 AND fnd.fund_id = awd.fund_id
111 AND fnd.fund_code = fcat.fund_code
112 AND awd.base_id = fab.base_id
113 AND disb_dlt.sf_status IN ('R','E')
114 AND fnd.ci_cal_type = cp_cal_type
115 AND fnd.ci_sequence_number = cp_sequence_number
116 AND (fnd.fund_id = cp_fund_id OR (cp_fund_id IS NULL))
117 AND (fab.person_id = cp_person_id OR (cp_person_id IS NULL))
118 AND (disb.ld_cal_type = cp_tp_cal_type OR (cp_tp_cal_type IS NULL))
119 AND (disb.ld_sequence_number = cp_tp_sequence_number OR (cp_tp_sequence_number IS NULL))
120 AND ((cp_manage_accounts = 'OTHER' and fcat.sys_fund_type = 'SPONSOR')
121 OR (cp_manage_accounts='STUDENT_FINANCE'));
122
123 g_sponsor CONSTANT VARCHAR2(10) :='SPONSOR';
124 g_aid_adj CONSTANT VARCHAR2(10) :='AID_ADJ';
125 g_separator CONSTANT VARCHAR2(5) := ' : ';
126 g_chgadj CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_CREDIT_CLASS','CHGADJ');
127 g_null CONSTANT VARCHAR2(6) := NULL;
128 g_v_currency igs_fi_control_all.currency_cd%TYPE := NULL;
129 g_print_msg VARCHAR2(200);
130 lv_locking_success VARCHAR2(1);
131
132 FUNCTION validate_award_year(p_cal_type igf_ap_award_year_v.cal_type%TYPE,
133 p_sequence_number igf_ap_award_year_v.sequence_number%TYPE)
134 RETURN BOOLEAN AS
135 /***********************************************************************************************
136
137 Created By : Sarakshi
138 Date Created By: 24-Dec-2001
139 Purpose : Validates award year
140 Known limitations,enhancements,remarks:
141 Change History
142 Who When What
143
144 ********************************************************************************************** */
145
146 CURSOR cur_val IS
147 SELECT 'X'
148 FROM igf_ap_award_year_v
149 WHERE cal_type = p_cal_type
150 AND sequence_number = p_sequence_number;
151 l_temp VARCHAR2(1);
152 BEGIN
153 OPEN cur_val;
154 FETCH cur_val INTO l_temp;
155 IF cur_val%FOUND THEN
156 CLOSE cur_val;
157 RETURN TRUE;
158 ELSE
159 CLOSE cur_val;
160 RETURN FALSE;
161 END IF;
162 END validate_award_year;
163
164 FUNCTION validate_persid_grp(p_persid_grp_id IN igs_pe_persid_group_all.group_id%TYPE)
165 RETURN BOOLEAN AS
166 /***********************************************************************************************
167
168 Created By : Sarakshi
169 Date Created By: 24-Dec-2001
170 Purpose : Validates person Id Group
171
172 Known limitations,enhancements,remarks:
173 Change History
174 Who When What
175 smadathi 01-JUL-2004 Bug 3735396. Modified the cursor cur_val select to validate
176 existance of input person id group from igs_pe_persid_group_all
177 smadathi 31-DEC-2002 Bug 2620343. Modified the cursor cur_val select to fetch
178 the records from view igs_pe_persid_group instead of
179 igs_pe_persid_group_v. This fix is done to remove
180 Non-mergablity due to igs_pe_persid_group_v view and to reduce shared memory
181 ********************************************************************************************** */
182
183 CURSOR cur_val IS
184 SELECT 'X'
185 FROM igs_pe_persid_group_all
186 WHERE group_id = p_persid_grp_id
187 AND TRUNC(create_dt) <= TRUNC(SYSDATE)
188 AND NVL(closed_ind,'N') = 'N';
189
190 l_temp VARCHAR2(1);
191 BEGIN
192 OPEN cur_val;
193 FETCH cur_val INTO l_temp;
194 IF cur_val%FOUND THEN
195 CLOSE cur_val;
196 RETURN TRUE;
197 ELSE
198 CLOSE cur_val;
199 RETURN FALSE;
200 END IF;
201 END validate_persid_grp;
202
203 FUNCTION validate_base_id(p_base_id igf_ap_fa_con_v.base_id%TYPE,
204 p_cal_type igf_ap_fa_con_v.ci_cal_type%TYPE,
205 p_sequence_number igf_ap_fa_con_v.ci_sequence_number%TYPE)
206 RETURN BOOLEAN AS
207 /***********************************************************************************************
208
209 Created By : Sarakshi
210 Date Created By: 24-Dec-2001
211 Purpose : Validates base Id
212
213 Known limitations,enhancements,remarks:
214 Change History
215 Who When What
216 smadathi 31-dec-2002 Bug 2719776. Modified the cursor cur_val select to fetch
217 the records from view igf_ap_fa_base_rec instead of
218 igf_ap_fa_con_v. This fix is done to remove
219 Non-mergable view exists in the select and to reduce shared memory
220 within the acceptable limit
221 ********************************************************************************************** */
222
223 CURSOR cur_val IS
224 SELECT 'X'
225 FROM igf_ap_fa_base_rec
226 WHERE base_id = p_base_id
227 AND ci_cal_type =p_cal_type
228 AND ci_sequence_number=p_sequence_number;
229 l_temp VARCHAR2(1);
230 BEGIN
231 OPEN cur_val;
232 FETCH cur_val INTO l_temp;
233 IF cur_val%FOUND THEN
234 CLOSE cur_val;
235 RETURN TRUE;
236 ELSE
237 CLOSE cur_val;
238 RETURN FALSE;
239 END IF;
240 END validate_base_id;
241
242 FUNCTION validate_fund_id(p_fund_id IN igf_aw_fund_mast.fund_id%TYPE,
243 p_cal_type igf_aw_fund_mast.ci_cal_type%TYPE,
244 p_sequence_number igf_aw_fund_mast.ci_sequence_number%TYPE)
245 RETURN BOOLEAN AS
246 /***********************************************************************************************
247
248 Created By : Sarakshi
249 Date Created By: 24-Dec-2001
250 Purpose : Validates fund Id
251
252 Known limitations,enhancements,remarks:
253 Change History
254 Who When What
255 ********************************************************************************************** */
256
257 CURSOR cur_val IS
258 SELECT 'X'
259 FROM igf_aw_fund_mast
260 WHERE fund_id = p_fund_id
261 AND ci_cal_type =p_cal_type
262 AND ci_sequence_number=p_sequence_number;
263 l_temp VARCHAR2(1);
264 BEGIN
265 OPEN cur_val;
266 FETCH cur_val INTO l_temp;
267 IF cur_val%FOUND THEN
268 CLOSE cur_val;
269 RETURN TRUE;
270 ELSE
271 CLOSE cur_val;
272 RETURN FALSE;
273 END IF;
274 END validate_fund_id;
275
276
277 FUNCTION get_bill_desc(p_n_fund_id IN igf_aw_fund_mast.fund_id%TYPE)
278 RETURN VARCHAR2 IS
279 ------------------------------------------------------------------
280 --Created by : Sanil Madathil, Oracle IDC
281 --Date created: 03 Jun 2002
282 --
283 --Purpose: This function erturns the bill description value for the fund
284 -- passed as parameter.
285 --
286 --Known limitations/enhancements and/or remarks:
287 --
288 --Change History:
289 --Who When What
290 ------------------------------------------------------------------
291 CURSOR c_igf_aw_fund_mast(cp_fund_id igf_aw_fund_mast.fund_id%type) IS
292 SELECT bill_desc
293 FROM igf_aw_fund_mast
294 WHERE fund_id = cp_fund_id;
295
296 -- cursor c_igf_aw_fund_mast row type variable
297 rec_c_igf_aw_fund_mast c_igf_aw_fund_mast%ROWTYPE;
298
299 BEGIN
300 OPEN c_igf_aw_fund_mast(p_n_fund_id);
301 FETCH c_igf_aw_fund_mast INTO rec_c_igf_aw_fund_mast;
302 CLOSE c_igf_aw_fund_mast;
303 RETURN rec_c_igf_aw_fund_mast.bill_desc;
304
305 END get_bill_desc;
306
307
308 FUNCTION lookup_desc( p_type IN igf_lookups_view.lookup_type%TYPE,
309 p_code IN igf_lookups_view.lookup_code%TYPE) RETURN VARCHAR2 IS
310 /***********************************************************************************************
311
312 Created By : Sarakshi
313 Date Created By: 24-Dec-2001
314 Purpose : To fetch the meaning of a corresponding lookup code of a lookup type
315
316 Known limitations,enhancements,remarks:
317 Change History
318 Who When What
319 vvutukur 08-Apr-2003 Enh#2831554.Internal Credits API Build. Removed cursor cur_desc and its usage and replaced with a call
320 to generic function igf_aw_gen.lookup_desc to fetch the meaning of a lookup.
321 ********************************************************************************************** */
322
323 BEGIN
324 IF p_code IS NULL THEN
325 RETURN NULL;
326 ELSE
327 RETURN igf_aw_gen.lookup_desc(l_type => p_type,
328 l_code => p_code
329 );
330 END IF ;
331 END lookup_desc;
332
333 PROCEDURE log_messages ( p_msg_name VARCHAR2 ,
334 p_msg_val VARCHAR2
335 ) IS
336 /***********************************************************************************************
337
338 Created By : Sarakshi
339 Date Created By: 24-Dec-2001
340 Purpose : To log the parameter and other important information
341
342 Known limitations,enhancements,remarks:
343 Change History
344 Who When What
345 shtatiko 26-MAR-2003 Bug# 2782124, Changed the message to IGS_FI_CRD_INT_ALL_PARAMETER
346 from IGS_FI_CAL_BALANCES_LOG.
347 ********************************************************************************************** */
348 BEGIN
349 fnd_message.set_name('IGS','IGS_FI_CRD_INT_ALL_PARAMETER');
350 fnd_message.set_token('PARM_TYPE',p_msg_name);
351 fnd_message.set_token('PARM_CODE' ,p_msg_val);
352 fnd_file.put_line(fnd_file.log,fnd_message.get);
353 END log_messages ;
354
355
356
357 PROCEDURE call_credits_api(p_cur_disb IN cur_disb%ROWTYPE,
358 p_fee_cal_type IN igs_ca_inst.cal_type%TYPE,
359 p_fee_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
360 p_credit_id OUT NOCOPY igs_fi_credits_all.credit_id%TYPE,
361 p_status OUT NOCOPY igf_db_awd_disb_dtl.sf_status%TYPE,
362 p_error_desc OUT NOCOPY igf_db_awd_disb_dtl.error_desc%TYPE,
363 p_d_gl_date IN igs_fi_credits_all.gl_date%TYPE,
364 p_n_source_invoice_id IN igs_fi_credits_all.source_invoice_id%TYPE
365 )AS
366 /***********************************************************************************************
367 Created by : Sarakshi,
368 Date created: 24-Dec-2001
369
370 Purpose:To calls the credits api.
371
372
373 Known limitations/enhancements and/or remarks:
374
375 Change History:
376 Who When What
377 pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
378 parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
379 svuppala 18-JUL-2005 Enh 4213629 - Impact of automatic generation of the Receipt Number
380 changed parameters of igs_fi_credit_pvt.create_credit call
381 Added OUT parameter x_credit_number and local variable l_v_credit_number
382 Removed cur_seq cursor and the usage of the cursor.
383 pathipat 22-Apr-2004 Enh 3558549 - Commercial Receivables Enhancements build
384 Added param p_n_source_invoice_id
385 pathipat 21-Aug-2003 Enh 3076768 - Auto Release of Holds build
386 Modified call_credits_api() - added check when return_status = 'S'
387 SMADATHI 26-jun-2003 Bug 2852816. Removed redundant parameter p_student_sponsor. Modified the creadit description generated
388 when a credit is recorded on the student's account
389 vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Modified the l_credit_rec record type variable to add 3 new parameters lockbox_interface_id,
390 batch_name,deposit_date.
391 vvutukur 08-apr-2003 Enh#2831554.Internal Credits API Build. Removed the call to public credits api, instead added validations of
392 some parameters and placed a call to Private API.Removed finp_get_cur generic procedure which fetches currency code
393 as the same has been moved to procedure transfer_disb_dtls_to_sf
394 agairola 07-Mar-2003 Bug# 2814089: Following modifications have been done
395 1. The cursor cur_api has been modified to select person_number
396 from igs_pe_person_base_v
397 2. For the parameter p_student_sponsor='SPONSOR', the value of the
398 parameter passed to cur_api is changed to p_cur_disb.person_id.
399 3. The credit description is modified for the sponsor transaction as
400 Negative Charge Adjustment : Person Number : Full Name
401 smadathi 31-dec-2002 Bug 2620349. Modified the cursor cur_api select to fetch
402 the records from view igs_fi_parties_v instead of
403 igs_pe_person_v. This fix is done to remove
404 Non-mergablity due to igs_pe_person_v view and to reduce shared memory
405 vvutukur 13-Dec-2002 Enh#2584741.Deposits Build.Modified the call to credits api to remove p_validation_level
406 parameter and add 3 new parameters p_v_check_number,p_v_source_tran_type,p_v_source_tran_ref_number.
407 vvutukur 20-Nov-2002 Enh#2584986.Added new IN parameter p_d_gl_date.Passed p_d_gl_date to the call to
408 igs_fi_credits_api_pub.create_credit.Removed references to igs_fi_cur.Instead defaulted the
409 currency that is set up in System Options Form and passed the same to the call to
410 credits api. Also exchange rate is passed as 1.
411 jbegum 21-Sep-2002 Bug #2564643 Modified the cursor cur_desc to select only description column
412 and not the subaccount_id column from igs_fi_cr_types table.
413 Also modified the call to igs_fi_credits_api_pub.create_credit.Removed the
414 parameter p_subaccount_id.
415 smadathi 10-JUL-2002 Bug 2450332. Call to igs_fi_credits_api_pub.create_credit modified to
416 pass current system date as effective date instead of disbursement date.
417 smadathi 03-Jun-2002 Bug 2349394. call to get_bill_desc function is made to get bill description
418 which is passed as parameter to credits API for all funds except sponsor.
419 vchappid 11-Feb-2002 Enh#2191470,Un-Commented reference to new parameters introduced in the Credits API
420 ********************************************************************************************** */
421
422 -- Bug #2564643 Modified the cursor cur_desc to select only description column
423
424 CURSOR cur_desc(cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE) IS
425 SELECT credit_class
426 FROM igs_fi_cr_types_v
427 WHERE credit_type_id = cp_credit_type_id;
428 l_cur_desc cur_desc%ROWTYPE;
429
430 CURSOR cur_api(cp_person_id igs_pe_person_v.person_id%TYPE) IS
431 SELECT person_number,full_name
432 FROM igs_pe_person_base_v
433 WHERE person_id = cp_person_id;
434 l_cur_api cur_api%ROWTYPE;
435
436 l_credit_activity_id igs_fi_cr_activities.credit_activity_id%TYPE;
437 l_msg_count NUMBER(2);
438 l_msg_data igf_db_awd_disb_dtl.error_desc%TYPE;
439 l_attribute_rec igs_fi_credits_api_pub.attribute_rec_type;
440 l_credit_num NUMBER;
441 l_person_id igs_pe_person_v.person_id%TYPE;
442 l_desc igs_fi_credits_all.description%TYPE;
443 l_amount igf_db_awd_disb_dtl.disb_net_amt%TYPE;
444
445 l_v_message_name fnd_new_messages.message_name%TYPE;
446 l_credit_rec igs_fi_credit_pvt.credit_rec_type;
447 l_v_credit_number igs_fi_credits.credit_number%TYPE;
448
449
450 BEGIN
451 p_error_desc :=NULL;
452
453 --fetching the credit type description
454 OPEN cur_desc(p_cur_disb.credit_type_id);
455 FETCH cur_desc INTO l_cur_desc;
456 IF cur_desc%NOTFOUND THEN
457 l_cur_desc.credit_class :=NULL;
458 END IF;
459 CLOSE cur_desc;
460
461 -- for the sponsor records
462 IF p_cur_disb.sys_fund_type = g_sponsor THEN
463 -- if the disbursement amount is greater than zero, credit will be created
464 -- for the student and if the disbursement amount is less than zero, credit
465 -- will be created for sponsor
466 IF NVL(p_cur_disb.amount,0) < 0 THEN
467 l_person_id := p_cur_disb.party_id;
468 -- for credit created on the sponsor account would be negative charge
469 -- adjustment : person number : full name of the student
470 OPEN cur_api(p_cur_disb.person_id);
471 FETCH cur_api INTO l_cur_api;
472 IF cur_api%NOTFOUND THEN
473 l_desc := NULL;
474 ELSE
475 l_desc := substr(g_chgadj||g_separator||l_cur_api.person_number||g_separator||l_cur_api.full_name,1,240);
476 END IF;
477 CLOSE cur_api;
478 ELSE
479 -- description will be credit class meaning of credit type linked to sponsor : sponsor code
480 l_desc := l_cur_desc.credit_class ||g_separator|| p_cur_disb.fund_code;
481 l_person_id := p_cur_disb.person_id;
482 END IF;
483 ELSE
484 -- for the other financial aid records, credit will be created for the student
485 l_person_id := p_cur_disb.person_id;
486 -- get_bill_desc function returns the bill description for the fund. This
487 -- bill description will be passed as parameter to credits API for all funds
488 -- except sponsor.
489 l_desc := get_bill_desc(p_cur_disb.fund_id);
490 END IF;
491
492 IF NVL(p_cur_disb.amount,0) < 0 THEN
493 l_amount := (-1)*p_cur_disb.amount;
494 ELSE
495 l_amount := p_cur_disb.amount;
496 END IF;
497
498
499 -- Calling Credits API to insert data in the credits table in student finance.
500 l_attribute_rec.p_attribute_category := NULL;
501 l_attribute_rec.p_attribute1 := NULL;
502 l_attribute_rec.p_attribute2 := NULL;
503 l_attribute_rec.p_attribute3 := NULL;
504 l_attribute_rec.p_attribute4 := NULL;
505 l_attribute_rec.p_attribute5 := NULL;
506 l_attribute_rec.p_attribute6 := NULL;
507 l_attribute_rec.p_attribute7 := NULL;
508 l_attribute_rec.p_attribute8 := NULL;
509 l_attribute_rec.p_attribute9 := NULL;
510 l_attribute_rec.p_attribute10 := NULL;
511 l_attribute_rec.p_attribute11 := NULL;
512 l_attribute_rec.p_attribute12 := NULL;
513 l_attribute_rec.p_attribute13 := NULL;
514 l_attribute_rec.p_attribute14 := NULL;
515 l_attribute_rec.p_attribute15 := NULL;
516 l_attribute_rec.p_attribute16 := NULL;
517 l_attribute_rec.p_attribute17 := NULL;
518 l_attribute_rec.p_attribute18 := NULL;
519 l_attribute_rec.p_attribute19 := NULL;
520 l_attribute_rec.p_attribute20 := NULL;
521
522 l_credit_rec.p_credit_status := 'CLEARED';
523 l_credit_rec.p_credit_source := p_cur_disb.fed_fund_code;
524 l_credit_rec.p_party_id := l_person_id;
525 l_credit_rec.p_credit_type_id := p_cur_disb.credit_type_id;
526 l_credit_rec.p_credit_instrument := 'AID';
527 l_credit_rec.p_description := l_desc;
528 l_credit_rec.p_amount := l_amount;
529 l_credit_rec.p_currency_cd := g_v_currency;
530 l_credit_rec.p_exchange_rate := 1;
531 l_credit_rec.p_transaction_date := TRUNC(SYSDATE);
532 l_credit_rec.p_effective_date := TRUNC(SYSDATE);
533 l_credit_rec.p_source_transaction_id := g_null;
534 l_credit_rec.p_receipt_lockbox_number := g_null;
535 l_credit_rec.p_credit_card_code := g_null;
536 l_credit_rec.p_credit_card_holder_name := g_null;
537 l_credit_rec.p_credit_card_number := g_null;
538 l_credit_rec.p_credit_card_expiration_date := g_null;
539 l_credit_rec.p_credit_card_approval_code := g_null;
540 l_credit_rec.p_invoice_id := null;
541 l_credit_rec.p_awd_yr_cal_type := p_cur_disb.ci_cal_type;
542 l_credit_rec.p_awd_yr_ci_sequence_number := p_cur_disb.ci_sequence_number;
543 l_credit_rec.p_fee_cal_type := p_fee_cal_type;
544 l_credit_rec.p_fee_ci_sequence_number := p_fee_ci_sequence_number;
545 l_credit_rec.p_check_number := g_null;
546 l_credit_rec.p_source_tran_type := g_null;
547 l_credit_rec.p_source_tran_ref_number := g_null;
548 l_credit_rec.p_gl_date := p_d_gl_date;
549 l_credit_rec.p_v_credit_card_payee_cd := NULL;
550 l_credit_rec.p_v_credit_card_status_code := NULL;
551 l_credit_rec.p_v_credit_card_tangible_cd := NULL;
552 l_credit_rec.p_lockbox_interface_id := g_null;
553 l_credit_rec.p_batch_name := g_null;
554 l_credit_rec.p_deposit_date := g_null;
555 l_credit_rec.p_invoice_id := p_n_source_invoice_id;
556
557 --Create a credit by calling the Private Credits API with p_validation_level as fnd_api.g_valid_level_none.
558 igs_fi_credit_pvt.create_credit( p_api_version => 2.1,
559 p_init_msg_list => fnd_api.g_true,
560 p_commit => fnd_api.g_false,
561 p_validation_level => fnd_api.g_valid_level_none,
562 x_return_status => p_status,
563 x_msg_count => l_msg_count,
564 x_msg_data => l_msg_data,
565 p_credit_rec => l_credit_rec,
566 p_attribute_record => l_attribute_rec,
567 x_credit_id => p_credit_id,
568 x_credit_activity_id => l_credit_activity_id,
569 x_credit_number => l_v_credit_number
570 );
571 IF p_status <> 'S' THEN
572 fnd_message.set_encoded(l_msg_data);
573 p_error_desc:= fnd_message.get;
574 ELSE
575 IF l_msg_count <> 0 THEN
576 fnd_message.set_encoded(l_msg_data);
577 p_error_desc:= fnd_message.get;
578 END IF;
579 END IF;
580
581 END call_credits_api;
582
583
584 PROCEDURE call_charges_api(p_cur_disb IN cur_disb%ROWTYPE,
585 p_fee_cal_type IN igs_ca_inst.cal_type%TYPE,
586 p_fee_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
587 p_invoice_id OUT NOCOPY igs_fi_inv_int_all.invoice_id%TYPE,
588 p_status OUT NOCOPY igf_db_awd_disb_dtl.sf_status%TYPE,
589 p_error_desc OUT NOCOPY igf_db_awd_disb_dtl.error_desc%TYPE,
590 p_d_gl_date IN igs_fi_credits_all.gl_date%TYPE
591 )AS
592 /***********************************************************************************************
593
594 Created By : Sarakshi
595 Date Created By: 24-Dec-2001
596 Purpose : To create charges.
597
598 Known limitations,enhancements,remarks:
599 Change History
600 Who When What
601 SMADATHI 26-jun-2003 Bug 2852816. Removed redundant parameter p_student_sponsor. Modified the charges description when
602 downward adjustment exists
603 vvutukur 11-Apr-2003 Enh#2831554.Internal Credits API Build. Removed finp_get_cur generic procedure which fetches currency code
604 as the same has been moved to procedure transfer_disb_dtls_to_sf.
605 agairola 07-Mar-2003 Bug# 2814089: Following modifications have been done
606 1. The cursor cur_api has been modified to select person_number
607 from igs_pe_person_base_v
608 2. For the parameter p_student_sponsor='SPONSOR', the value of the
609 parameter passed to cur_api is changed to p_cur_disb.person_id.
610 3. The invoice description is modified for the sponsor transaction as
611 Person Number : Full Name
612 smadathi 31-dec-2002 Bug 2620349. Modified the cursor cur_api select to fetch
613 the records from view igs_fi_parties_v instead of
614 igs_pe_person_v. This fix is done to remove
615 Non-mergablity due to igs_pe_person_v view and to reduce shared memory
616 vvutukur 20-Nov-2002 Enh#2584986.Added new IN parameter p_d_gl_date to this procedure.Passed p_d_gl_date to
617 the call to igs_fi_charges_api_pvt.create_charge.
618 jbegum 21-Sep-2002 Bug #2564643 Modified the cursor cur_desc to select only description column
619 and not the subaccount_id column from igs_fi_cr_types table.
620 Also modified the record structure being passed to igs_fi_charges_api_pvt.create_charge.
621 Removed the field l_chg_rec.p_subaccount_id .
622 smadathi 03-Jun-2002 Bug 2349394. call to get_bill_desc function is made to get bill description
623 which is passed as parameter to charges API for all funds except sponsor.
624 vchappid 11-Feb-2002 Enh#2191470, Un-Commented reference to Fee Cal parameters in the Charges API invoking
625 Fee Cal Parameters Derived for the Load Cal are passed for charges Creation
626 ********************************************************************************************** */
627
628 -- Bug #2564643 Modified the cursor cur_desc to select only description column
629
630 CURSOR cur_desc(cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE) IS
631 SELECT description
632 FROM igs_fi_cr_types
633 WHERE credit_type_id = cp_credit_type_id;
634 l_cur_desc cur_desc%ROWTYPE;
635
636 CURSOR cur_api(cp_person_id igs_pe_person_v.person_id%TYPE) IS
637 SELECT person_number,full_name
638 FROM igs_pe_person_base_v
639 WHERE person_id = cp_person_id;
640 l_cur_api cur_api%ROWTYPE;
641
642 CURSOR c_igs_fi_fee_type (cp_v_fee_type igs_fi_fee_type_all.fee_type%TYPE) IS
643 SELECT description
644 FROM igs_fi_fee_type
645 WHERE fee_type = cp_v_fee_type;
646
647 rec_c_igs_fi_fee_type c_igs_fi_fee_type%ROWTYPE;
648
649
650 l_chg_rec igs_fi_charges_api_pvt.header_rec_type;
651 l_chg_line_tbl igs_fi_charges_api_pvt.line_tbl_type;
652 l_line_tbl igs_fi_charges_api_pvt.line_id_tbl_type;
653 l_invoice_id igs_fi_inv_int.invoice_id%TYPE;
654 l_msg_count NUMBER(5);
655 l_msg_data igf_db_awd_disb_dtl.error_desc%TYPE;
656 l_var NUMBER(5) := 0;
657 l_count NUMBER(5);
658 l_msg igf_db_awd_disb_dtl.error_desc%TYPE;
659 l_person_id igs_pe_person_v.person_id%TYPE;
660 l_fee_type igf_aw_fund_mast.fee_type%TYPE;
661 l_invoice_desc igs_fi_inv_int.invoice_desc%TYPE;
662 l_amount igf_db_awd_disb_dtl.disb_net_amt%TYPE;
663 l_transaction_type igs_lookups_view.lookup_code%TYPE;
664
665 l_v_currency igs_fi_control_all.currency_cd%TYPE;
666 l_v_message_name fnd_new_messages.message_name%TYPE;
667 l_n_waiver_amount igs_fi_credits_all.amount%TYPE;
668
669
670 BEGIN
671 p_error_desc := NULL;
672
673 --fetching the invoice description
674 OPEN cur_desc(p_cur_disb.credit_type_id);
675 FETCH cur_desc INTO l_cur_desc;
676 IF cur_desc%NOTFOUND THEN
677 l_cur_desc.description :=NULL;
678 END IF;
679 CLOSE cur_desc;
680
681 -- for the sponsor records
682 IF p_cur_disb.sys_fund_type = g_sponsor THEN
683 -- if the disbursement amount is greater than zero, charge will be created
684 -- for the sponsor (fee type = sponsor, Transaction type = sponsor) and if the disbursement amount is less than zero, charge
685 -- will be created for student (fee type = adjustment fee type, Transaction type = AID_ADJ)
686 IF NVL(p_cur_disb.amount,0) > 0 THEN
687 l_person_id := p_cur_disb.party_id;
688 l_fee_type := p_cur_disb.spnsr_fee_type;
689 l_transaction_type := g_sponsor;
690 -- for credit created on the sponsor account would be negative charge
691 -- adjustment : person number : full name of the student
692 OPEN cur_api(p_cur_disb.person_id);
693 FETCH cur_api INTO l_cur_api;
694 IF cur_api%NOTFOUND THEN
695 l_invoice_desc := NULL;
696 ELSE
697 l_invoice_desc := substr(l_cur_api.person_number||g_separator||l_cur_api.full_name,1,240);
698 END IF;
699 CLOSE cur_api;
700 ELSE
701 OPEN c_igs_fi_fee_type(p_cur_disb.fee_type);
702 FETCH c_igs_fi_fee_type INTO rec_c_igs_fi_fee_type;
703 IF c_igs_fi_fee_type%NOTFOUND THEN
704 l_invoice_desc := NULL;
705 ELSE
706 -- description will be description of adjustment fee type linked to sponsor : sponsor code
707 l_invoice_desc := rec_c_igs_fi_fee_type.description || g_separator || p_cur_disb.fund_code ;
708 END IF;
709 l_person_id := p_cur_disb.person_id;
710 l_fee_type := p_cur_disb.fee_type;
711 l_transaction_type := g_aid_adj;
712 END IF;
713 ELSE
714 -- for the other financial aid records, credit will be created for the student
715 l_person_id := p_cur_disb.person_id;
716 -- get_bill_desc function returns the bill description for the fund. This
717 -- bill description will be passed as parameter to credits API for all funds
718 -- except sponsor.
719 l_invoice_desc := get_bill_desc(p_cur_disb.fund_id);
720 l_fee_type := p_cur_disb.fee_type;
721 l_transaction_type := g_aid_adj;
722 END IF;
723
724 IF p_cur_disb.amount < 0 THEN
725 l_amount := (-1)*p_cur_disb.amount;
726 ELSE
727 l_amount := p_cur_disb.amount;
728 END IF;
729
730 -- Bug #2564643 .Removed assigning of l_chg_rec.p_subaccount_id with value of l_cur_desc.subaccount_id
731
732 l_chg_rec.p_person_id := l_person_id;
733 l_chg_rec.p_fee_type := l_fee_type;
734 l_chg_rec.p_fee_cat := NULL;
735 l_chg_rec.p_fee_cal_type := p_fee_cal_type; -- Enh#2191470, Passing the Derived Fee Cal Type for the Load Cal Type
736 l_chg_rec.p_fee_ci_sequence_number := p_fee_ci_sequence_number; -- Enh#2191470, Passing the Derived Fee Cal Type for the Load Cal Type
737 l_chg_rec.p_course_cd := NULL;
738 l_chg_rec.p_attendance_type := NULL;
739 l_chg_rec.p_attendance_mode := NULL;
740 l_chg_rec.p_invoice_amount := l_amount;
741 l_chg_rec.p_invoice_creation_date := TRUNC(SYSDATE);
742 l_chg_rec.p_invoice_desc := l_invoice_desc;
743 l_chg_rec.p_transaction_type := l_transaction_type;
744 l_chg_rec.p_currency_cd := g_v_currency;
745 l_chg_rec.p_exchange_rate := 1;
746 l_chg_rec.p_effective_date := p_cur_disb.disb_date;
747 l_chg_rec.p_waiver_flag := NULL;
748 l_chg_rec.p_waiver_reason := NULL;
749 l_chg_rec.p_source_transaction_id := NULL;
750
751
752 l_chg_line_tbl(1).p_s_chg_method_type := NULL;
753 l_chg_line_tbl(1).p_description := l_cur_desc.description;
754 l_chg_line_tbl(1).p_chg_elements := 1;
755 l_chg_line_tbl(1).p_amount := l_amount;
756 l_chg_line_tbl(1).p_unit_attempt_status := NULL;
757 l_chg_line_tbl(1).p_eftsu := NULL;
758 l_chg_line_tbl(1).p_credit_points := NULL;
759 l_chg_line_tbl(1).p_org_unit_cd := NULL;
760 l_chg_line_tbl(1).p_attribute_category := NULL;
761 l_chg_line_tbl(1).p_attribute1 := NULL;
762 l_chg_line_tbl(1).p_attribute2 := NULL;
763 l_chg_line_tbl(1).p_attribute3 := NULL;
764 l_chg_line_tbl(1).p_attribute4 := NULL;
765 l_chg_line_tbl(1).p_attribute5 := NULL;
766 l_chg_line_tbl(1).p_attribute6 := NULL;
767 l_chg_line_tbl(1).p_attribute7 := NULL;
768 l_chg_line_tbl(1).p_attribute8 := NULL;
769 l_chg_line_tbl(1).p_attribute9 := NULL;
770 l_chg_line_tbl(1).p_attribute10 := NULL;
771 l_chg_line_tbl(1).p_attribute11 := NULL;
772 l_chg_line_tbl(1).p_attribute12 := NULL;
773 l_chg_line_tbl(1).p_attribute13 := NULL;
774 l_chg_line_tbl(1).p_attribute14 := NULL;
775 l_chg_line_tbl(1).p_attribute15 := NULL;
776 l_chg_line_tbl(1).p_attribute16 := NULL;
777 l_chg_line_tbl(1).p_attribute17 := NULL;
778 l_chg_line_tbl(1).p_attribute18 := NULL;
779 l_chg_line_tbl(1).p_attribute19 := NULL;
780 l_chg_line_tbl(1).p_attribute20 := NULL;
781 l_chg_line_tbl(1).p_location_cd := NULL;
782 l_chg_line_tbl(1).p_uoo_id := NULL;
783 l_chg_line_tbl(1).p_d_gl_date := p_d_gl_date;
784
785 igs_fi_charges_api_pvt.create_charge(p_api_version => 2.0,
786 p_init_msg_list => 'T',
787 p_commit => 'F',
788 p_validation_level => NULL,
789 p_header_rec => l_chg_rec,
790 p_line_tbl => l_chg_line_tbl,
791 x_invoice_id => p_invoice_id,
792 x_line_id_tbl => l_line_tbl,
793 x_return_status => p_status,
794 x_msg_count => l_msg_count,
795 x_msg_data => l_msg_data,
796 x_waiver_amount => l_n_waiver_amount);
797
798 IF p_status <> 'S' THEN
799 IF l_msg_count = 1 THEN
800 fnd_message.set_encoded(l_msg_data);
801 p_error_desc:= fnd_message.get;
802 ELSE
803 FOR l_count IN 1 .. l_msg_count LOOP
804 l_msg := fnd_msg_pub.get(p_msg_index => l_count, p_encoded => 'T');
805 fnd_message.set_encoded (l_msg);
806 p_error_desc:= p_error_desc||'- '|| fnd_message.get;
807 END LOOP;
808 END IF;
809 END IF;
810 END call_charges_api;
811
812 PROCEDURE main_disbursement(
813 p_rec_disb IN cur_disb%ROWTYPE,
814 p_d_gl_date IN igs_fi_credits_all.gl_date%TYPE
815 ) AS
816 /***********************************************************************************************
817
818 Created By : Sarakshi
819 Date Created By: 24-Dec-2001
820 Purpose :
821
822 Known limitations,enhancements,remarks:
823 Change History
824 Who When What
825 svuppala 12-May-2006 Bug 5217319 Added call to format amount by rounding off to currency precision
826 in igf_aw_awd_disb_pkg.update_row , igf_aw_award_pkg.update_row calls
827 smadathi 01-JUL-2004 Bug 3735396. GSCC Warning File.Sql.35 was fixed as part of this bug. The initialization
828 of local variable l_status in the procedure declaration was removed and the same
829 was initialized at the starting of this procedure.
830 pathipat 22-Apr-2004 Enh 3558549 - Commercial Receivables Enhancements
831 Modified calls to call_credits_api() - added new parameter p_n_source_invoice_id
832 veramach 3-NOV-2003 FA 125 Multiple Distr Methods
833 Changed signature of igf_aw_award_pkg.update_row(Added adplans_id to the tbh call)
834 Changed signature of igf_aw_awd_disb_pkg.update_row(Added attendance_type_code to the tbh call)
835 pathipat 23-Aug-2003 Enh 3076768 - Automatic Release of Holds build
836 Added code to log message is holds release failed in call to Credits API
837 SMADATHI 26-jun-2003 Bug 2852816. Removed parameter p_student_sponsor from the calls to call_credits_api,call_charges_api
838 vvutukur 10-Apr-2003 Enh#2831554.Internal Credits API Build. Added validations for Fee,Load,Award Calendar Instances,credit type,
839 credit source,credit class.Added logic such that the disb. record's sf_status and error_desc fields gets updated with
840 appropriate values.
841 error description fields
842 shtatiko 26-MAR-2003 Bug# 2782124, Changed the logging of results from tabular format to
843 form layout.
844 vvutukur 26-Feb-2003 Enh#2758823.FA117 Build. Modified the call to igf_db_awd_disb_dtl_pkg.update_row to pass TRUNC(SYSDATE)
845 instead of SYSDATE for the parameter x_sf_status_date.
846 smadathi 31-dec-2002 Bug 2620359. Modified the cursor cur_person to fetch the person number
847 from igs_pe_person_base_v instead of the igs_pe_person. This is done
848 due to Non-Meargability and higher value of shared memory beyong the acceptable limit
849 vvutukur 20-Nov-2002 Enh#2584986.Added new parameter p_d_gl_date.Also modified the calls to call_charges_api
850 and call_credits_api to pass this p_d_gl_date parameter.Also added this p_d_gl_date
851 parameter to the call to igs_fi_prc_refunds.process_plus.
852 SYkrishn 08-MAY-2002 The column DISB_PAID_AMT in the table igf_aw_awd_disb is updated with the Cumulation of existing Disb Paid amount
853 with the newly disbursed amount (each iteration) instead of overriding with the new value - Bug 2356801.
854 sarakshi 18-Mar-2002 Bug:2144600, added logic for refunding the excess credit amount
855 vchappid 11-Feb-2002 Enh#2191470, Un-Commented reference to Fee Cal parameters in the Charges API invoking
856 Fee Cal Parameters Derived for the Load Cal are passed for charges Creation
857 ********************************************************************************************** */
858 CURSOR cur_person(cp_person_id igs_pe_person_v.person_id%TYPE) IS
859 SELECT person_number
860 FROM igs_pe_person_base_v
861 WHERE person_id=cp_person_id;
862 l_cur_person cur_person%ROWTYPE;
863
864 CURSOR cur_cr_type(cp_credit_type_id igs_fi_cr_types_all.credit_type_name%TYPE) IS
865 SELECT credit_type_name
866 FROM igs_fi_cr_types_all
867 WHERE credit_type_id = cp_credit_type_id;
868
869 l_v_cr_type_name igs_fi_cr_types_all.credit_type_name%TYPE;
870
871 CURSOR cur_awd(cp_award_id igf_aw_award.award_id%TYPE) IS
872 SELECT a.rowid,a.*
873 FROM igf_aw_award a
874 WHERE award_id=cp_award_id;
875 l_rec_awd cur_awd%ROWTYPE;
876
877 CURSOR cur_awd_disb(cp_award_id igf_aw_awd_disb.award_id%TYPE,
878 cp_disb_num igf_aw_awd_disb.disb_num%TYPE) IS
879 SELECT a.rowid,a.*
880 FROM igf_aw_awd_disb a
881 WHERE award_id=cp_award_id
882 AND disb_num=cp_disb_num;
883 l_rec_awd_disb cur_awd_disb%ROWTYPE;
884
885 CURSOR cur_borrower(cp_award_id igf_aw_awd_disb.award_id%TYPE) IS
886 SELECT lor.p_person_id
887 FROM igf_sl_loans lon ,igf_sl_lor lor
888 WHERE lon.award_id=cp_award_id
889 AND lon.loan_id=lor.loan_id;
890 l_borrower igf_sl_lor.p_person_id%TYPE;
891
892 CURSOR cur_disb_dtl (cp_award_id igf_db_awd_disb_dtl.award_id%TYPE,
893 cp_disb_num igf_db_awd_disb_dtl.disb_num%TYPE,
894 cp_disb_seq_num igf_db_awd_disb_dtl.disb_seq_num%TYPE) IS
895 SELECT a.rowid,a.*
896 FROM igf_db_awd_disb_dtl a
897 WHERE award_id=cp_award_id
898 AND disb_num=cp_disb_num
899 AND disb_seq_num=cp_disb_seq_num;
900 l_rec_disb_dtl cur_disb_dtl%ROWTYPE;
901 l_status igf_db_awd_disb_dtl.sf_status%TYPE;
902 l_error_desc igf_db_awd_disb_dtl.error_desc%TYPE :=NULL;
903 l_sf_credit_id igf_db_awd_disb_dtl.sf_credit_id%TYPE := NULL;
904 l_sf_invoice_num igf_db_awd_disb_dtl.sf_invoice_num%TYPE := NULL;
905 l_spnsr_credit_id igf_db_awd_disb_dtl.spnsr_credit_id%TYPE := NULL;
906 l_spnsr_charge_id igf_db_awd_disb_dtl.spnsr_charge_id%TYPE := NULL;
907 l_paid_amt igf_aw_award.paid_amt%TYPE;
908 l_disb_paid_amt igf_aw_awd_disb.disb_paid_amt%TYPE;
909 l_status_code igf_db_awd_disb_dtl.sf_status%TYPE;
910 l_log_status igf_lookups_view.meaning%TYPE;
911 l_flag1 BOOLEAN :=TRUE;
912 l_flag2 BOOLEAN :=TRUE;
913 l_flag3 BOOLEAN :=TRUE;
914 l_refunds BOOLEAN :=FALSE;
915 l_ref_status BOOLEAN :=TRUE;
916 l_ref_err_msg fnd_new_messages.message_name%TYPE :=NULL;
917
918 -- Start of Modification for Enh#2191470
919 l_fee_cal_type igs_ca_inst.cal_type%TYPE;
920 l_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
921 l_message_name fnd_new_messages.message_name%TYPE;
922 -- End of Modification for Enh#2191470
923
924 l_v_credit_class igs_fi_cr_types_all.credit_class%TYPE;
925 l_b_return_status BOOLEAN;
926
927 l_v_holds_message fnd_new_messages.message_text%TYPE := NULL;
928
929 BEGIN
930 SAVEPOINT S1;
931 -- status variable being initialized to value 'S'.
932 l_status := 'S';
933 --Load Calendar Instance is associated with each disbursement. Check if the Load Calendar instance is currently active.
934 --else, log the error message and skip processing the same record and continue with the next record.
935 IF p_rec_disb.ld_cal_type IS NOT NULL AND p_rec_disb.ld_sequence_number IS NOT NULL THEN
936 IF NOT igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => p_rec_disb.ld_cal_type,
937 p_n_ci_sequence_number => p_rec_disb.ld_sequence_number,
938 p_v_s_cal_cat => 'LOAD'
939 ) THEN
940 fnd_message.set_name('IGS','IGS_FI_LOAD_CAL_NOT_ACTIVE');
941 l_error_desc := fnd_message.get;
942 l_status := 'E';
943 END IF;
944 END IF;
945
946 -- Derive the Fee Period for the passed Load Calendar Period, if there is no
947 -- relation defined between a Fee and Load Calendars then Log an error and exit out NOCOPY of the Process
948 -- If there is a relation defined then the OUT NOCOPY parameters l_fee_cal_type, l_fee_ci_seq will have
949 -- the Fee Period Instance
950 IF l_status = 'S' THEN
951 IF NOT igs_fi_gen_001.finp_get_lfci_reln( p_rec_disb.ld_cal_type,
952 p_rec_disb.ld_sequence_number,
953 'LOAD',
954 l_fee_cal_type,
955 l_fee_ci_sequence_number,
956 l_message_name) THEN
957 IF l_message_name <> 'IGS_FI_NO_RELN_EXISTS' THEN
958 l_error_desc := fnd_message.get_string('IGS',l_message_name);
959 l_status := 'E';
960 ELSE
961 fnd_message.set_name('IGS','IGS_FI_NO_FEE_LOAD_REL');
962 fnd_message.set_token('AWARD_ID',p_rec_disb.award_id);
963 fnd_message.set_token('DISB_NUM',p_rec_disb.disb_num);
964 fnd_message.set_token('LOAD_CAL',p_rec_disb.ld_cal_type);
965 fnd_message.set_token('LOAD_SEQ_NUM',p_rec_disb.ld_sequence_number);
966 l_error_desc := fnd_message.get;
967 l_status := 'E';
968 END IF;
969 END IF;
970 END IF;
971
972 --Check if the Fee Calendar instance is currently active.
973 --else, log the error message and skip processing the same record and continue with the next record.
974 IF l_status = 'S' THEN
975 IF l_fee_cal_type IS NOT NULL AND l_fee_ci_sequence_number IS NOT NULL THEN
976 IF NOT igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => l_fee_cal_type,
977 p_n_ci_sequence_number => l_fee_ci_sequence_number,
978 p_v_s_cal_cat => 'FEE'
979 ) THEN
980 fnd_message.set_name('IGS','IGS_FI_FCI_NOTFOUND');
981 l_error_desc := fnd_message.get;
982 l_status := 'E';
983 END IF;
984 END IF;
985 END IF;
986
987 --The Credit Source parameter, associated with the disbursement records, has to be a valid lookup code for the lookup type 'IGF_AW_FED_FUND'.
988 --else, log the error message and skip processing the same record and continue with the next record.
989 IF l_status = 'S' THEN
990 IF NOT igs_fi_crdapi_util.validate_igf_lkp( p_v_lookup_type => 'IGF_AW_FED_FUND',
991 p_v_lookup_code => p_rec_disb.fed_fund_code) THEN
992 fnd_message.set_name('IGS','IGS_FI_CAPI_CRD_SRC_NULL');
993 fnd_message.set_token('CR_SOURCE',igf_aw_gen.lookup_desc('IGF_AW_FED_FUND',p_rec_disb.fed_fund_code));
994 l_error_desc := fnd_message.get;
995 l_status := 'E';
996 END IF;
997 END IF;
998
999 --Check if the credit type associated with the disbursement record is active as on the current system date.
1000 --else, log the error message and skip processing the same record and continue with the next record.
1001 IF l_status = 'S' THEN
1002 igs_fi_crdapi_util.validate_credit_type( p_n_credit_type_id => p_rec_disb.credit_type_id,
1003 p_v_credit_class => l_v_credit_class,
1004 p_b_return_stat => l_b_return_status
1005 );
1006 IF l_b_return_status = FALSE THEN
1007 OPEN cur_cr_type(p_rec_disb.credit_type_id);
1008 FETCH cur_cr_type INTO l_v_cr_type_name;
1009 CLOSE cur_cr_type;
1010
1011 fnd_message.set_name('IGS','IGS_FI_CAPI_CR_TYPE_INVALID');
1012 fnd_message.set_token('CR_TYPE',l_v_cr_type_name);
1013 l_error_desc := fnd_message.get;
1014 l_status := 'E';
1015 END IF;
1016 END IF;
1017
1018 --If credit class is External or Internal Financial Aid, Fee Calendar Instance and Award Calendar Instances are mandatory.
1019 --If any one of them is null, then log the error message and skip processing the same record and continue with the next record.
1020 IF l_status = 'S' THEN
1021 IF l_v_credit_class IN ('EXTFA','INTFA') THEN
1022 IF p_rec_disb.ci_cal_type IS NULL OR p_rec_disb.ci_sequence_number IS NULL OR l_fee_cal_type IS NULL OR l_fee_ci_sequence_number IS NULL THEN
1023 fnd_message.set_name('IGS','IGS_FI_FPAY_MAND');
1024 l_error_desc := fnd_message.get;
1025 l_status := 'E';
1026 END IF;
1027 END IF;
1028 END IF;
1029
1030 --If the disbursement records has Award Calendar Instance, then check if the Award Calendar instance is currently active.
1031 --else, log the error message and skip processing the same record and continue with the next record.
1032 IF l_status = 'S' THEN
1033 IF p_rec_disb.ci_cal_type IS NOT NULL AND p_rec_disb.ci_sequence_number IS NOT NULL THEN
1034 IF NOT igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => p_rec_disb.ci_cal_type,
1035 p_n_ci_sequence_number => p_rec_disb.ci_sequence_number,
1036 p_v_s_cal_cat => 'AWARD'
1037 ) THEN
1038 fnd_message.set_name('IGS','IGS_FI_INV_AWD_YR');
1039 l_error_desc := fnd_message.get;
1040 l_status := 'E';
1041 END IF;
1042 END IF;
1043 END IF;
1044
1045 IF l_status = 'S' THEN
1046 IF NVL(p_rec_disb.amount,0) >0 THEN
1047 IF p_rec_disb.sys_fund_type = g_sponsor THEN
1048 --Create a charge transaction in Sponsor account
1049 call_charges_api(p_rec_disb,
1050 l_fee_cal_type,
1051 l_fee_ci_sequence_number,
1052 l_spnsr_charge_id,
1053 l_status,
1054 l_error_desc,
1055 p_d_gl_date
1056 );
1057 END IF;
1058
1059 --Create a credit transaction in Student account
1060 -- The invoice_id of the charge created above is inserted
1061 -- into the Credits table as source_invoice_id
1062 IF l_status = 'S' THEN
1063 call_credits_api(p_rec_disb,
1064 l_fee_cal_type,
1065 l_fee_ci_sequence_number,
1066 l_sf_credit_id,
1067 l_status,
1068 l_error_desc,
1069 p_d_gl_date,
1070 l_spnsr_charge_id
1071 );
1072 IF l_status = 'S' AND l_error_desc IS NOT NULL THEN
1073 l_v_holds_message := l_error_desc;
1074 END IF;
1075 END IF;
1076
1077 ELSIF NVL(p_rec_disb.amount,0) < 0 THEN
1078 --Create a charge transaction in Student account
1079 call_charges_api(p_rec_disb,
1080 l_fee_cal_type,
1081 l_fee_ci_sequence_number,
1082 l_sf_invoice_num,
1083 l_status,
1084 l_error_desc,
1085 p_d_gl_date
1086 );
1087 IF p_rec_disb.sys_fund_type = g_sponsor AND l_status = 'S' THEN
1088 -- Create a credit transaction in Sponsor account
1089 -- The invoice_id of the charge created above is inserted
1090 -- into the Credits table as source_invoice_id
1091 call_credits_api(p_rec_disb,
1092 l_fee_cal_type,
1093 l_fee_ci_sequence_number,
1094 l_spnsr_credit_id,
1095 l_status,
1096 l_error_desc,
1097 p_d_gl_date,
1098 l_sf_invoice_num
1099 );
1100 IF l_status = 'S' AND l_error_desc IS NOT NULL THEN
1101 l_v_holds_message := l_error_desc;
1102 END IF;
1103 END IF;
1104 ELSE
1105 l_status:='ZERO';
1106 END IF;
1107 END IF;
1108
1109 IF l_status <> 'S' THEN
1110 IF l_status = 'ZERO' THEN
1111 l_error_desc :=fnd_message.get_string ('IGF','IGF_DB_ZERO_AMOUNT');
1112 END IF;
1113 --If any of the creation of charge or credit failed
1114 l_log_status := lookup_desc('IGF_AW_LOOKUPS_MSG','ERROR') ;
1115 l_sf_credit_id := NULL;
1116 l_sf_invoice_num := NULL;
1117 l_spnsr_credit_id := NULL;
1118 l_spnsr_charge_id := NULL;
1119 l_status_code := 'E';
1120 ROLLBACK TO S1;
1121 ELSE
1122 --If none of the creation of charge or credit failed
1123 l_log_status := lookup_desc('IGF_AW_LOOKUPS_MSG','POSTED');
1124 l_status_code := 'P';
1125
1126 --Update Award Table
1127 OPEN cur_awd(p_rec_disb.award_id);
1128 FETCH cur_awd INTO l_rec_awd;
1129 CLOSE cur_awd;
1130 l_paid_amt := NVL(l_rec_awd.paid_amt,0) + p_rec_disb.amount;
1131
1132 BEGIN
1133 -- Bug 5217319 Added call to format amount by rounding off to currency precision for l_paid_amt
1134 igf_aw_award_pkg.update_row( X_ROWID => l_rec_awd.rowid,
1135 X_AWARD_ID => l_rec_awd.award_id,
1136 X_FUND_ID => l_rec_awd.fund_id,
1137 X_BASE_ID => l_rec_awd.base_id,
1138 X_OFFERED_AMT => l_rec_awd.offered_amt,
1139 X_ACCEPTED_AMT => l_rec_awd.accepted_amt,
1140 X_PAID_AMT => igs_fi_gen_gl.get_formatted_amount(l_paid_amt),
1141 X_PACKAGING_TYPE => l_rec_awd.packaging_type,
1142 X_BATCH_ID => l_rec_awd.batch_id,
1143 X_MANUAL_UPDATE => l_rec_awd.manual_update,
1144 X_RULES_OVERRIDE => l_rec_awd.rules_override,
1145 X_AWARD_DATE => l_rec_awd.award_date,
1146 X_AWARD_STATUS => l_rec_awd.award_status,
1147 X_ATTRIBUTE_CATEGORY => l_rec_awd.attribute_category,
1148 X_ATTRIBUTE1 => l_rec_awd.attribute1,
1149 X_ATTRIBUTE2 => l_rec_awd.attribute2,
1150 X_ATTRIBUTE3 => l_rec_awd.attribute3,
1151 X_ATTRIBUTE4 => l_rec_awd.attribute4,
1152 X_ATTRIBUTE5 => l_rec_awd.attribute5,
1153 X_ATTRIBUTE6 => l_rec_awd.attribute6,
1154 X_ATTRIBUTE7 => l_rec_awd.attribute7,
1155 X_ATTRIBUTE8 => l_rec_awd.attribute8,
1156 X_ATTRIBUTE9 => l_rec_awd.attribute9,
1157 X_ATTRIBUTE10 => l_rec_awd.attribute10,
1158 X_ATTRIBUTE11 => l_rec_awd.attribute11,
1159 X_ATTRIBUTE12 => l_rec_awd.attribute12,
1160 X_ATTRIBUTE13 => l_rec_awd.attribute13,
1161 X_ATTRIBUTE14 => l_rec_awd.attribute14,
1162 X_ATTRIBUTE15 => l_rec_awd.attribute15,
1163 X_ATTRIBUTE16 => l_rec_awd.attribute16,
1164 X_ATTRIBUTE17 => l_rec_awd.attribute17,
1165 X_ATTRIBUTE18 => l_rec_awd.attribute18,
1166 X_ATTRIBUTE19 => l_rec_awd.attribute19,
1167 X_ATTRIBUTE20 => l_rec_awd.attribute20,
1168 X_RVSN_ID => l_rec_awd.rvsn_id,
1169 x_ALT_PELL_SCHEDULE => l_rec_awd.alt_pell_schedule,
1170 X_MODE => 'R',
1171 X_AWARD_NUMBER_TXT => l_rec_awd.award_number_txt,
1172 X_LEGACY_RECORD_FLAG => NULL,
1173 x_adplans_id => l_rec_awd.adplans_id,
1174 x_lock_award_flag => l_rec_awd.lock_award_flag,
1175 x_app_trans_num_txt => l_rec_awd.app_trans_num_txt,
1176 x_awd_proc_status_code => l_rec_awd.awd_proc_status_code,
1177 x_notification_status_code => l_rec_awd.notification_status_code,
1178 x_notification_status_date => l_rec_awd.notification_status_date,
1179 x_publish_in_ss_flag => l_rec_awd.publish_in_ss_flag
1180 );
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 l_flag1:= FALSE;
1184 END;
1185
1186 IF l_flag1 THEN
1187 --Update Disbursement Table
1188 OPEN cur_awd_disb(p_rec_disb.award_id,p_rec_disb.disb_num);
1189 FETCH cur_awd_disb INTO l_rec_awd_disb;
1190 CLOSE cur_awd_disb;
1191 /*
1192 The column DISB_PAID_AMT in the table igf_aw_awd_disb is updated with the Cumulation of existing Disb Paid amount
1193 with the newly disbursed amount (each iteration) instead of overriding with the new value - Bug 2356801.
1194 */
1195 l_disb_paid_amt := NVL(l_rec_awd_disb.disb_paid_amt,0) + p_rec_disb.amount;
1196
1197 /*
1198 Bug 5080692. Disb Date should be the date on which the transaction is posted to student account.
1199 Thus X_DISB_DATE should always be updated with SYSDATE.
1200 */
1201
1202 BEGIN
1203 -- Bug 5217319 Added call to format amount by rounding off to currency precision for l_disb_paid_amt
1204 igf_aw_awd_disb_pkg.update_row( X_ROWID => l_rec_awd_disb.rowid,
1205 X_AWARD_ID => l_rec_awd_disb.award_id,
1206 X_DISB_NUM => l_rec_awd_disb.disb_num,
1207 X_TP_CAL_TYPE => l_rec_awd_disb.tp_cal_type,
1208 X_TP_SEQUENCE_NUMBER => l_rec_awd_disb.tp_sequence_number,
1209 X_DISB_GROSS_AMT => l_rec_awd_disb.disb_gross_amt,
1210 X_FEE_1 => l_rec_awd_disb.fee_1,
1211 X_FEE_2 => l_rec_awd_disb.fee_2,
1212 X_DISB_NET_AMT => l_rec_awd_disb.disb_net_amt,
1213 X_DISB_DATE => TRUNC(SYSDATE),
1214 X_TRANS_TYPE => l_rec_awd_disb.trans_type,
1215 X_ELIG_STATUS => l_rec_awd_disb.elig_status,
1216 X_ELIG_STATUS_DATE => l_rec_awd_disb.elig_status_date,
1217 X_AFFIRM_FLAG => l_rec_awd_disb.affirm_flag,
1218 X_HOLD_REL_IND => l_rec_awd_disb.hold_rel_ind,
1219 X_MANUAL_HOLD_IND => l_rec_awd_disb.manual_hold_ind,
1220 X_DISB_STATUS => l_rec_awd_disb.disb_status,
1221 X_DISB_STATUS_DATE => l_rec_awd_disb.disb_status_date,
1222 X_LATE_DISB_IND => l_rec_awd_disb.late_disb_ind,
1223 X_FUND_DIST_MTHD => l_rec_awd_disb.fund_dist_mthd,
1224 X_PREV_REPORTED_IND => l_rec_awd_disb.prev_reported_ind,
1225 X_FUND_RELEASE_DATE => l_rec_awd_disb.fund_release_date,
1226 X_FUND_STATUS => l_rec_awd_disb.fund_status,
1227 X_FUND_STATUS_DATE => l_rec_awd_disb.fund_status_date,
1228 X_FEE_PAID_1 => l_rec_awd_disb.fee_paid_1,
1229 X_FEE_PAID_2 => l_rec_awd_disb.fee_paid_2,
1230 X_CHEQUE_NUMBER => l_rec_awd_disb.cheque_number,
1231 X_LD_CAL_TYPE => l_rec_awd_disb.ld_cal_type,
1232 X_LD_SEQUENCE_NUMBER => l_rec_awd_disb.ld_sequence_number,
1233 X_DISB_ACCEPTED_AMT => l_rec_awd_disb.disb_accepted_amt,
1234 X_DISB_PAID_AMT => igs_fi_gen_gl.get_formatted_amount(l_disb_paid_amt),
1235 X_RVSN_ID => l_rec_awd_disb.rvsn_id,
1236 X_INT_REBATE_AMT => l_rec_awd_disb.int_rebate_amt,
1237 X_FORCE_DISB => l_rec_awd_disb.force_disb,
1238 X_MIN_CREDIT_PTS => l_rec_awd_disb.min_credit_pts,
1239 X_DISB_EXP_DT => l_rec_awd_disb.disb_exp_dt,
1240 X_VERF_ENFR_DT => l_rec_awd_disb.verf_enfr_dt,
1241 X_FEE_CLASS => l_rec_awd_disb.fee_class,
1242 X_SHOW_ON_BILL => l_rec_awd_disb.show_on_bill,
1243 X_MODE => 'R',
1244 x_attendance_type_code => l_rec_awd_disb.attendance_type_code,
1245 x_base_attendance_type_code => l_rec_awd_disb.base_attendance_type_code,
1246 x_payment_prd_st_date => l_rec_awd_disb.payment_prd_st_date,
1247 x_change_type_code => l_rec_awd_disb.change_type_code,
1248 x_fund_return_mthd_code => l_rec_awd_disb.fund_return_mthd_code,
1249 x_direct_to_borr_flag => l_rec_awd_disb.direct_to_borr_flag
1250 );
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 l_flag2:= FALSE;
1254 END;
1255 END IF;
1256
1257
1258 --setting the call to refunds procedure flag to TRUE
1259 l_refunds:=TRUE;
1260
1261 --If any of the above two update fails then rollback
1262 IF ((l_flag1=FALSE) OR (l_flag2=FALSE)) THEN
1263 ROLLBACK TO S1;
1264 l_log_status := lookup_desc('IGF_AW_LOOKUPS_MSG','ERROR') ;
1265 l_status_code :='E';
1266 l_sf_invoice_num :=NULL;
1267 l_sf_credit_id :=NULL;
1268 l_spnsr_credit_id :=NULL;
1269 l_spnsr_charge_id :=NULL;
1270
1271 l_error_desc:=fnd_message.get;
1272 IF l_error_desc IS NULL THEN
1273 l_error_desc:=fnd_message.get_string('IGF','IGF_DB_UPDATE_FAILED');
1274 END IF;
1275 --setting the call to refunds procedure flag to FALSE
1276 l_refunds:=FALSE;
1277 END IF;
1278
1279 END IF;
1280
1281 --Update disbursement detail table
1282 OPEN cur_disb_dtl(p_rec_disb.award_id,p_rec_disb.disb_num,p_rec_disb.disb_seq_num);
1283 FETCH cur_disb_dtl INTO l_rec_disb_dtl;
1284 CLOSE cur_disb_dtl;
1285 BEGIN
1286 igf_db_awd_disb_dtl_pkg.update_row( X_ROWID => l_rec_disb_dtl.rowid,
1287 X_AWARD_ID => l_rec_disb_dtl.award_id,
1288 X_DISB_NUM => l_rec_disb_dtl.disb_num,
1289 X_DISB_SEQ_NUM => l_rec_disb_dtl.disb_seq_num,
1290 X_DISB_GROSS_AMT => l_rec_disb_dtl.disb_gross_amt,
1291 X_FEE_1 => l_rec_disb_dtl.fee_1,
1292 X_FEE_2 => l_rec_disb_dtl.fee_2,
1293 X_DISB_NET_AMT => l_rec_disb_dtl.disb_net_amt,
1294 X_DISB_ADJ_AMT => l_rec_disb_dtl.disb_adj_amt,
1295 X_DISB_DATE => l_rec_disb_dtl.disb_date,
1296 X_FEE_PAID_1 => l_rec_disb_dtl.fee_paid_1,
1297 X_FEE_PAID_2 => l_rec_disb_dtl.fee_paid_2,
1298 X_DISB_ACTIVITY => l_rec_disb_dtl.disb_activity,
1299 X_DISB_BATCH_ID => NULL, -- obsolete
1300 X_DISB_ACK_DATE => NULL, -- obsolete
1301 X_BOOKING_BATCH_ID => NULL, -- obsolete
1302 X_BOOKED_DATE => NULL, -- obsolete
1303 X_DISB_STATUS => NULL, -- obsolete
1304 X_DISB_STATUS_DATE => NULL, -- obsolete
1305 X_SF_STATUS => l_status_code,
1306 X_SF_STATUS_DATE => TRUNC(SYSDATE),
1307 X_SF_INVOICE_NUM => l_sf_invoice_num,
1308 X_SF_CREDIT_ID => l_sf_credit_id,
1309 X_SPNSR_CREDIT_ID => l_spnsr_credit_id,
1310 X_SPNSR_CHARGE_ID => l_spnsr_charge_id,
1311 X_ERROR_DESC => l_error_desc,
1312 X_MODE => 'R' ,
1313 x_NOTIFICATION_DATE => l_rec_disb_dtl.notification_date,
1314 X_INTEREST_REBATE_AMT => l_rec_disb_dtl.interest_rebate_amt,
1315 x_ld_cal_type => l_rec_disb_dtl.ld_cal_type,
1316 x_ld_sequence_number => l_rec_disb_dtl.ld_sequence_number
1317 );
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 l_flag3:= FALSE;
1321 END;
1322
1323 --Added as a part of Refunds Build, bug:2144600
1324 --Refunds to be created if amount>0 and fedral fund code in DLP/FLP
1325 IF ((l_refunds=TRUE) AND (l_flag3=TRUE)) THEN
1326 BEGIN
1327 IF ((p_rec_disb.amount > 0) AND (p_rec_disb.fed_fund_code IN ('DLP','FLP'))) THEN
1328 OPEN cur_borrower(p_rec_disb.award_id);
1329 FETCH cur_borrower INTO l_borrower;
1330 CLOSE cur_borrower;
1331
1332 igs_fi_prc_refunds.process_plus(p_credit_id => l_sf_credit_id,
1333 p_borrower_id => l_borrower,
1334 p_err_message => l_ref_err_msg,
1335 p_status => l_ref_status,
1336 p_d_gl_date => p_d_gl_date
1337 );
1338 IF l_ref_err_msg IS NOT NULL THEN
1339 fnd_message.set_name('IGS',l_ref_err_msg);
1340 fnd_file.put_line(fnd_file.log,fnd_message.get);
1341 END IF;
1342 END IF;
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGF','IGF_DB_REFUND_NOT_CREATE'));
1346 END;
1347 END IF;
1348
1349 --If the above update fails then rollback the entire transaction else commit
1350 IF l_flag3 THEN
1351 COMMIT;
1352 ELSE
1353 ROLLBACK TO S1;
1354 END IF;
1355
1356 --Logging the data information
1357 OPEN cur_person(p_rec_disb.person_id);
1358 FETCH cur_person INTO l_cur_person;
1359 CLOSE cur_person;
1360
1361 -- Following log format modification has been done as part of Bug fix 2782124
1362 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER'),l_cur_person.person_number);
1363 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','DISBURSEMENT_DATE'),p_rec_disb.disb_date);
1364 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','DISB_AMOUNT'),p_rec_disb.amount);
1365 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','STATUS'),l_log_status);
1366 fnd_file.put_line(fnd_file.LOG,' ' || l_error_desc );
1367 -- Log any messages due to failure to release holds, provided its not already logged as l_error_desc
1368 IF (l_v_holds_message IS NOT NULL) AND (l_v_holds_message <> NVL(l_error_desc,'NULL'))THEN
1369 fnd_file.put_line(fnd_file.LOG,' ' || l_v_holds_message );
1370 END IF;
1371 fnd_file.put_line(fnd_file.log, ' ');
1372
1373 END main_disbursement;
1374
1375 PROCEDURE transfer_disb_dtls_to_sf(
1376 errbuf OUT NOCOPY VARCHAR2,
1377 retcode OUT NOCOPY NUMBER,
1378 p_award_year IN VARCHAR2,
1379 p_base_id IN igf_ap_fa_con_v.base_id%TYPE,
1380 p_person_group_id IN igs_pe_persid_group_v.group_id%TYPE,
1381 p_fund_id IN igf_aw_fund_mast.fund_id%TYPE,
1382 p_term_calendar IN VARCHAR2,
1383 p_d_gl_date IN VARCHAR2
1384 ) AS
1385 /***********************************************************************************************
1386
1387 Created By : Sarakshi
1388 Date Created By: 24-Dec-2001
1389 Purpose :
1390
1391 Known limitations,enhancements,remarks:
1392 Change History
1393 Who When What
1394 ridas 07-FEB-2006 Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL
1395 sapanigr 16-SEP-2005 Modified the cursor, cur_fund to select the fund_code rather than description
1396 for the bug# 3810157
1397 smadathi 01-JUL-2004 Bug 3735396. The logic to handle both static and dynamic person group id
1398 incorporated.
1399 vvutukur 18-Jul-2003 Enh#3038511.FICR106 Build. Added call to generic procedure
1400 igs_fi_crdapi_util.get_award_year_status to validate Award Year Status.
1401 rasahoo 30-june-2003 Removed the cursor cur_ld_cal as it is not used any where.
1402 shtatiko 02-MAY-2003 Enh# 2831569, Added check for Manage Accounts System Option before running this process.
1403 If its value is NULL then process will error out.
1404 vvutukur 11-Apr-2003 Enh#2831554.Internal Credits API Build. Added validations for currency code and credit instrument 'AID'.
1405 shtatiko 26-MAR-2003 Bug# 2782124, Removed logging of header in the log file as log format
1406 is changed from tabular to form layout.
1407 smadathi 06-Jan-2003 Bug 2684895. Removed the logging of person group id. Instead
1408 used call to igs_fi_gen_005.finp_get_prsid_grp_code to
1409 log person group code.
1410 smadathi 31-dec-2002 Bug 2719776. Modified the cursor cur_person select to fetch
1411 the records from view igf_ap_fa_base_rec and igs_pe_person_base_v
1412 instead of igf_ap_fa_con_v. This fix is done to remove
1413 Non-mergable view exists in the select and to reduce shared memory
1414 within the acceptable limit
1415 smadathi 31-DEC-2002 Bug 2719776. Logic has been modified to raise user defined exception
1416 when invalid GL date is passed to the concurrent process. The similar
1417 logic has been implemented for invalid values passed to the rest of
1418 the concurrent parameters. Henceforth, the whenever invalid values for the concurrent
1419 parameteres are provided, control will be transferred to the user defined exception part
1420 and un handled exception will not appear in the log file
1421 vvutukur 20-Nov-2002 Enh#2584986.Added p_d_gl_date parameter to transfer_disb_dtls_to_sf and validations
1422 corresponding to this parameter.
1423 vchappid 11-Feb-2002 Enh#2191470,When the Load Cal Parameter is passed, then check if there exists a
1424 superior Fee Cal relation, if there is no relation set then log error
1425 and abort the process
1426 ********************************************************************************************** */
1427
1428 CURSOR cur_person IS
1429 SELECT pe.person_number person_number ,
1430 fabase.person_id person_id
1431 FROM igf_ap_fa_base_rec fabase,
1432 igs_pe_person_base_v pe
1433 WHERE fabase.person_id = pe.person_id
1434 AND base_id= p_base_id;
1435 l_cur_person cur_person%ROWTYPE;
1436
1437 CURSOR cur_fund IS
1438 SELECT fund_code
1439 FROM igf_aw_fund_mast
1440 WHERE fund_id = p_fund_id;
1441 l_cur_fund cur_fund%ROWTYPE;
1442
1443 CURSOR cur_base(cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
1444 cp_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
1445 cp_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE)
1446 IS
1447 SELECT base_id
1448 FROM igf_ap_fa_base_rec
1449 WHERE person_id = cp_person_id
1450 AND ci_cal_type = cp_ci_cal_type
1451 AND ci_sequence_number = cp_ci_sequence_number;
1452
1453 l_cur_base cur_base%ROWTYPE;
1454
1455 -- declaration of Ref cursor and ref cursor variable type
1456 TYPE typ_ref_cur_persid_grp IS REF CURSOR;
1457 c_ref_personid_grp typ_ref_cur_persid_grp;
1458
1459 -- declaration of variables to receive values returned from get_dynamic_sql callout
1460 -- l_v_dynamicsql: to receive the value of dynamic sql
1461 -- l_v_status : to receive outbound parameter p_status
1462 l_v_dynamicsql VARCHAR2(32767);
1463 l_v_status VARCHAR2(1);
1464
1465 l_n_person_id hz_parties.party_id%TYPE;
1466 l_rec_disb cur_disb%ROWTYPE;
1467 l_cal_type igf_ap_award_year_v.cal_type%TYPE;
1468 l_sequence_number igf_ap_award_year_v.sequence_number%TYPE;
1469 l_record_count NUMBER :=0;
1470 l_ld_cal_type igf_aw_awd_ld_cal_v.ld_cal_type%TYPE;
1471 l_ld_sequence_number igf_aw_awd_ld_cal_v.ld_sequence_number%TYPE;
1472
1473 l_v_message_name fnd_new_messages.message_name%TYPE;
1474 l_v_closing_status gl_period_statuses.closing_status%TYPE;
1475 l_d_gl_date igs_fi_credits_all.gl_date%TYPE;
1476
1477 l_exp_err_exception EXCEPTION;
1478 l_v_curr_desc fnd_currencies_tl.name%TYPE;
1479 l_v_manage_accounts igs_fi_control_all.manage_accounts%TYPE;
1480 l_v_awd_yr_status_cd igf_ap_batch_aw_map.award_year_status_code%TYPE;
1481 lv_profile_value VARCHAR2(30);
1482 lv_person_id igf_ap_fa_base_rec_all.person_id%TYPE;
1483 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1484
1485 BEGIN
1486
1487 igf_aw_gen.set_org_id(NULL) ; -- sets the orgid
1488 retcode := 0 ; -- initialises the out NOCOPY parameter to 0
1489
1490 --Logging the parameters
1491 --Getting the person_number from base_id
1492 IF p_base_id IS NOT NULL THEN
1493 OPEN cur_person;
1494 FETCH cur_person INTO l_cur_person;
1495 CLOSE cur_person;
1496 ELSE
1497 l_cur_person.person_number:=NULL;
1498 l_cur_person.person_id :=NULL;
1499 END IF;
1500
1501 --Getting the fund description
1502 IF p_fund_id IS NOT NULL THEN
1503 OPEN cur_fund;
1504 FETCH cur_fund INTO l_cur_fund;
1505 CLOSE cur_fund;
1506 ELSE
1507 l_cur_fund.fund_code :=NULL;
1508 END IF;
1509
1510 --Getting the load calendar
1511 IF p_term_calendar IS NOT NULL THEN
1512 l_ld_cal_type :=RTRIM(SUBSTR(p_term_calendar,1,10));
1513 l_ld_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_term_calendar,12)));
1514 -- Check for existance of relation with Fee Cal is done after logging parameters.
1515 ELSE
1516 l_ld_cal_type :=NULL;
1517 l_ld_sequence_number:=NULL;
1518 END IF;
1519
1520 --Convert the parameter p_d_gl_date from VARCHAR2 to DATE datatype.
1521 l_d_gl_date := IGS_GE_DATE.IGSDATE(p_d_gl_date);
1522
1523 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','AWARD_YEAR'),p_award_year);
1524 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER'),l_cur_person.person_number );
1525 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','PERSON_GROUP'),igs_fi_gen_005.finp_get_prsid_grp_code(p_person_group_id));
1526 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','FUND_CODE'),l_cur_fund.fund_code);
1527 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','TERM'),l_ld_cal_type||' '||l_ld_sequence_number);
1528 log_messages(igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','GL_DATE'),l_d_gl_date);
1529 fnd_file.put_line(fnd_file.log,' ');
1530
1531 -- Get the value of "Manage Accounts" System Option value.
1532 -- If this value is NULL then this process should error out.
1533 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
1534 p_v_message_name => l_v_message_name );
1535 IF l_v_manage_accounts IS NULL THEN
1536 fnd_message.set_name ( 'IGS', l_v_message_name );
1537 fnd_file.put_line( fnd_file.LOG, fnd_message.get );
1538 RAISE l_exp_err_exception;
1539 END IF;
1540
1541 IF p_term_calendar IS NOT NULL THEN
1542 -- Start of modification Enh#2191470
1543 -- If the Load Calendar instance is passed then check whether a relation exists with the Fee Cal
1544 IF (igs_fi_gen_001.finp_chk_lfci_reln( l_ld_cal_type,
1545 l_ld_sequence_number,
1546 'LOAD')= 'FALSE') THEN
1547 fnd_message.set_name('IGS','IGS_FI_NO_RELN_EXISTS');
1548 fnd_file.put_line(fnd_file.log,fnd_message.get);
1549 RAISE l_exp_err_exception;
1550 END IF;
1551 END IF;
1552 -- End of modification Enh#2191470
1553
1554 --Validating if all the mandatory parameter are passed
1555 IF (p_award_year IS NULL) THEN
1556 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
1557 fnd_file.put_line(fnd_file.log,fnd_message.get);
1558 RAISE l_exp_err_exception;
1559 END IF;
1560
1561 --GL Date parameter is mandatory to this concurrent job, hence it is passed as null, error out NOCOPY the job.
1562 IF p_d_gl_date IS NULL THEN
1563 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
1564 fnd_file.put_line(fnd_file.log,fnd_message.get);
1565 RAISE l_exp_err_exception;
1566 END IF;
1567
1568 --Getting cal type and sequence number
1569 l_cal_type :=RTRIM(SUBSTR(p_award_year,1,10));
1570 l_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
1571
1572 --Validating award year
1573 IF NOT validate_award_year(l_cal_type,l_sequence_number) THEN
1574 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1575 fnd_file.put_line(fnd_file.log,fnd_message.get);
1576 RAISE l_exp_err_exception;
1577 END IF;
1578
1579 l_v_message_name := NULL;
1580 --Validate the Award Year Status. If the status is not open, log the message in log file and
1581 --complete the process with error.
1582 igs_fi_crdapi_util.get_award_year_status( p_v_awd_cal_type => l_cal_type,
1583 p_n_awd_seq_number => l_sequence_number,
1584 p_v_awd_yr_status => l_v_awd_yr_status_cd,
1585 p_v_message_name => l_v_message_name
1586 );
1587 IF l_v_message_name IS NOT NULL THEN
1588 IF l_v_message_name = 'IGF_SP_INVALID_AWD_YR_STATUS' THEN
1589 fnd_message.set_name('IGF',l_v_message_name);
1590 ELSE
1591 fnd_message.set_name('IGS',l_v_message_name);
1592 END IF;
1593 fnd_file.put_line(fnd_file.log,fnd_message.get);
1594 RAISE l_exp_err_exception;
1595 END IF;
1596
1597 --Validating person Id and person id group cannot be present at a same time
1598 IF p_base_id IS NOT NULL AND p_person_group_id IS NOT NULL THEN
1599 fnd_message.set_name('IGS','IGS_FI_PRS_OR_PRSIDGRP');
1600 fnd_file.put_line(fnd_file.log,fnd_message.get);
1601 RAISE l_exp_err_exception;
1602 END IF;
1603
1604 --Validating person id group
1605 IF p_person_group_id IS NOT NULL THEN
1606 IF NOT validate_persid_grp(p_person_group_id) THEN
1607 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1608 fnd_file.put_line(fnd_file.log,fnd_message.get);
1609 RAISE l_exp_err_exception;
1610 END IF;
1611 END IF;
1612
1613 --Validating base_id
1614 IF p_base_id IS NOT NULL THEN
1615 IF NOT validate_base_id(p_base_id,l_cal_type,l_sequence_number) THEN
1616 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1617 fnd_file.put_line(fnd_file.log,fnd_message.get);
1618 RAISE l_exp_err_exception;
1619 END IF;
1620 END IF;
1621
1622 --Validating fund_code
1623 IF p_fund_id IS NOT NULL THEN
1624 IF NOT validate_fund_id(p_fund_id,l_cal_type,l_sequence_number) THEN
1625 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
1626 fnd_file.put_line(fnd_file.log,fnd_message.get);
1627 RAISE l_exp_err_exception;
1628 END IF;
1629 END IF;
1630
1631 --Validate the GL Date.
1632 igs_fi_gen_gl.get_period_status_for_date(p_d_date => l_d_gl_date,
1633 p_v_closing_status => l_v_closing_status,
1634 p_v_message_name => l_v_message_name
1635 );
1636 IF l_v_message_name IS NOT NULL THEN
1637 fnd_message.set_name('IGS',l_v_message_name);
1638 fnd_file.put_line(fnd_file.log,fnd_message.get);
1639 RAISE l_exp_err_exception;
1640 END IF;
1641
1642 --Error out the concurrent process if the GL Date is not a valid one.
1643 IF l_v_closing_status IN ('C','N','W') THEN
1644 fnd_message.set_name('IGS','IGS_FI_INVALID_GL_DATE');
1645 fnd_message.set_token('GL_DATE',l_d_gl_date);
1646 fnd_file.put_line(fnd_file.log,fnd_message.get);
1647 RAISE l_exp_err_exception;
1648 END IF;
1649
1650 l_v_message_name := NULL;
1651 --Capture the default currency that is set up in System Options Form.
1652 igs_fi_gen_gl.finp_get_cur( p_v_currency_cd => g_v_currency,
1653 p_v_curr_desc => l_v_curr_desc,
1654 p_v_message_name => l_v_message_name
1655 );
1656 IF l_v_message_name IS NOT NULL THEN
1657 fnd_message.set_name('IGS',l_v_message_name);
1658 fnd_file.put_line(fnd_file.log,fnd_message.get);
1659 RAISE l_exp_err_exception;
1660 END IF;
1661
1662 --Check if the credit instrument - 'AID' is valid and active lookup code for the 'IGS_FI_CREDIT_INSTRUMENT' lookup type
1663 --as on the current system date.
1664 --if not valid, then log the error message and skip processing the same record and continue with the next record.
1665 IF NOT igs_fi_crdapi_util.validate_igs_lkp( p_v_lookup_type => 'IGS_FI_CREDIT_INSTRUMENT',
1666 p_v_lookup_code => 'AID') THEN
1667 fnd_message.set_name('IGS','IGS_FI_CAPI_CRD_INSTR_NULL');
1668 fnd_message.set_token('CR_INSTR',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_CREDIT_INSTRUMENT','AID'));
1669 fnd_file.put_line(fnd_file.log,fnd_message.get);
1670 RAISE l_exp_err_exception;
1671 END IF;
1672
1673 --To check whether the profile value is set to a value of 'When Transfers are made to student finance' or not
1674 fnd_profile.get('IGF_AW_LOCK_COA',lv_profile_value);
1675
1676 g_print_msg := NULL;
1677
1678 IF p_base_id IS NOT NULL THEN
1679 --When base id is provided
1680 FOR l_rec_disb IN cur_disb(l_cal_type,
1681 l_sequence_number,
1682 p_fund_id,
1683 l_cur_person.person_id,
1684 l_ld_cal_type,
1685 l_ld_sequence_number,
1686 l_v_manage_accounts) LOOP
1687 main_disbursement(l_rec_disb,
1688 l_d_gl_date);
1689 l_record_count := l_record_count + 1;
1690 END LOOP;
1691
1692 --lock COA at the student level
1693 IF lv_profile_value = 'TRANSFER' THEN
1694
1695 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1696 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_sf_integration.transfer_disb_dtls_to_sf.debug','calling igf_aw_coa_gen.doLock for person_id '||l_cur_person.person_id);
1697 END IF;
1698
1699 OPEN cur_base(l_cur_person.person_id,l_cal_type,l_sequence_number);
1700 FETCH cur_base INTO l_cur_base;
1701 CLOSE cur_base;
1702
1703 IF NOT igf_aw_coa_gen.isCOALocked(l_cur_base.base_id) THEN
1704 lv_locking_success := igf_aw_coa_gen.doLock(l_cur_base.base_id);
1705
1706 IF lv_locking_success = 'Y' THEN
1707 fnd_message.set_name('IGF','IGF_AW_STUD_COA_LOCK');
1708 fnd_message.set_token('PERSON_NUM',igf_gr_gen.get_per_num (l_cur_base.base_id));
1709 fnd_file.put_line(fnd_file.log,RPAD(' ',10) ||fnd_message.get);
1710 lv_locking_success := 'N' ;
1711 END IF;
1712 END IF;
1713
1714 END IF;
1715
1716
1717 ELSIF p_person_group_id IS NOT NULL THEN
1718 --Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL
1719 l_v_dynamicsql := igs_pe_dynamic_persid_group.get_dynamic_sql(p_groupid => p_person_group_id,
1720 p_status => l_v_status,
1721 p_group_type => lv_group_type);
1722
1723 -- if the above call out returns an error status, the error message is logged in the
1724 -- log file and process errors out
1725 IF l_v_status <> 'S' THEN
1726 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
1727 fnd_file.put_line(fnd_file.log,fnd_message.get);
1728 RAISE l_exp_err_exception;
1729 END IF;
1730
1731 --Bug #5021084. Passing Group ID if the group type is STATIC.
1732 IF lv_group_type = 'STATIC' THEN
1733 OPEN c_ref_personid_grp FOR l_v_dynamicsql USING p_person_group_id;
1734 ELSIF lv_group_type = 'DYNAMIC' THEN
1735 OPEN c_ref_personid_grp FOR l_v_dynamicsql;
1736 END IF;
1737
1738 LOOP
1739 FETCH c_ref_personid_grp INTO l_n_person_id;
1740 EXIT WHEN c_ref_personid_grp%NOTFOUND;
1741
1742 FOR l_rec_disb IN cur_disb(l_cal_type,
1743 l_sequence_number,
1744 p_fund_id,
1745 l_n_person_id,
1746 l_ld_cal_type,
1747 l_ld_sequence_number,
1748 l_v_manage_accounts) LOOP
1749 main_disbursement(l_rec_disb,
1750 l_d_gl_date);
1751 l_record_count := l_record_count + 1;
1752 END LOOP;
1753
1754 --lock COA at the student level
1755 IF lv_profile_value = 'TRANSFER' THEN
1756
1757 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1758 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_sf_integration.transfer_disb_dtls_to_sf.debug','calling igf_aw_coa_gen.doLock for person_id '||l_n_person_id);
1759 END IF;
1760
1761 OPEN cur_base(l_n_person_id,l_cal_type,l_sequence_number);
1762 FETCH cur_base INTO l_cur_base;
1763 CLOSE cur_base;
1764
1765 IF NOT igf_aw_coa_gen.isCOALocked(l_cur_base.base_id) THEN
1766 lv_locking_success := igf_aw_coa_gen.doLock(l_cur_base.base_id);
1767
1768 IF lv_locking_success = 'Y' THEN
1769 fnd_message.set_name('IGF','IGF_AW_STUD_COA_LOCK');
1770 fnd_message.set_token('PERSON_NUM',igf_gr_gen.get_per_num (l_cur_base.base_id));
1771 fnd_file.put_line(fnd_file.log,RPAD(' ',10) ||fnd_message.get);
1772 lv_locking_success := 'N' ;
1773 END IF;
1774 END IF;
1775 END IF;
1776
1777 END LOOP;
1778 CLOSE c_ref_personid_grp;
1779 ELSE
1780 lv_person_id := NULL;
1781
1782 --When neither of base id nor group id is provided
1783 FOR l_rec_disb IN cur_disb(l_cal_type,
1784 l_sequence_number,
1785 p_fund_id,
1786 NULL,
1787 l_ld_cal_type,
1788 l_ld_sequence_number,
1789 l_v_manage_accounts) LOOP
1790 main_disbursement(l_rec_disb,
1791 l_d_gl_date);
1792 l_record_count := l_record_count + 1;
1793
1794 IF g_print_msg IS NOT NULL AND lv_person_id<>l_rec_disb.person_id THEN
1795 fnd_file.put_line(fnd_file.log,RPAD(' ',10) ||g_print_msg);
1796 g_print_msg := NULL;
1797 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
1798 END IF;
1799
1800 --lock COA at the student level
1801 IF lv_profile_value = 'TRANSFER' THEN
1802 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1803 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_db_sf_integration.transfer_disb_dtls_to_sf.debug','calling igf_aw_coa_gen.doLock for person_id '||lv_person_id);
1804 END IF;
1805
1806 OPEN cur_base(lv_person_id,l_cal_type,l_sequence_number);
1807 FETCH cur_base INTO l_cur_base;
1808 CLOSE cur_base;
1809
1810 IF NOT igf_aw_coa_gen.isCOALocked(l_cur_base.base_id) THEN
1811 lv_locking_success := igf_aw_coa_gen.doLock(l_cur_base.base_id);
1812
1813 IF lv_locking_success = 'Y' THEN
1814 fnd_message.set_name('IGF','IGF_AW_STUD_COA_LOCK');
1815 fnd_message.set_token('PERSON_NUM',igf_gr_gen.get_per_num (l_cur_base.base_id));
1816 fnd_file.put_line(fnd_file.log,RPAD(' ',10) ||fnd_message.get);
1817 lv_locking_success := 'N' ;
1818 END IF;
1819 END IF;
1820
1821
1822 lv_person_id := l_rec_disb.person_id;
1823
1824
1825 END IF;
1826 END LOOP;
1827
1828 --lock COA at the student level
1829 IF g_print_msg IS NOT NULL THEN
1830 fnd_file.put_line(fnd_file.log,RPAD(' ',10) ||g_print_msg);
1831 END IF;
1832
1833 END IF;
1834
1835 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_GE_TOTAL_REC_PROCESSED')||TO_CHAR(l_record_count));
1836
1837 EXCEPTION
1838 WHEN l_exp_err_exception THEN
1839 retcode := 2;
1840 WHEN OTHERS THEN
1841 retcode := 2;
1842 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' ||sqlerrm;
1843 igs_ge_msg_stack.conc_exception_hndl;
1844 END transfer_disb_dtls_to_sf;
1845
1846 END igf_db_sf_integration;
[Home] [Help]