1 PACKAGE BODY IGS_FI_PRC_BALANCES AS
2 /* $Header: IGSFI57B.pls 120.4 2006/05/12 05:48:01 abshriva ship $ */
3
4 ------------------------------------------------------------------
5 --Created by : Sanil Madathil, Oracle IDC
6 --Date created: 27052001
7 --
8 --Purpose: Package Body contains code for procedures/Functions defined in
9 -- package specification . Also body includes Functions/Procedures
10 -- private to it.
11 --
12 --Known limitations/enhancements and/or remarks:
13 --
14 --Change History:
15 --Who When What
16 --abshriva 12-May-2006 Bug 5217319: Amount precision change calculate_balance,insert_or_update_balance
17 -- retro_update_balance,convert_holds_balances
18 --sapanigr 05-May-2006 Bug 5178077: Modified procedure conv_balances to disable process in R12.
19 --sapanigr 14-Feb_2006 Bug 5018036. Cursor c_credit changed in check_exclusion_rules for Repository Perf tuning
20 --svuppala 14-JUL-2005 Enh 3392095 - impact of Tution Waivers build
21 -- Modified igs_fi_control_pkg.update_row by adding two new columns
22 -- post_waiver_gl_flag, waiver_notify_finaid_flag
23 --uudayapr 23-DEC-2003 ENH#3167098 -Term Based Fee Calc build.
24 --schodava 06-Oct-2003 Bug # 3123405 - Modified calculate_balance procedure.
25 --jbegum 14-June-2003 Bug# 2998266 Obsoleted the column NEXT_INVOICE_NUMBER.
26 --shtatiko 27-MAY-2003 Enh# 2831582, Removed references to columns lockbox_context, lockbox_number_attribute
27 -- and ar_int_org_id. For this, Modified finpl_upd_conv_prc_run_ind.
28 --vvutukur 16-May-2003 Enh#2831572.Financial Accounting Build.Modified finpl_upd_conv_prc_run_ind.
29 --pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables Interface
30 -- Modified call to igs_fi_control_pkg.update_row
31 -- Modified conv_balances - added validation for manage_accounts
32 --smadathi 18-Feb-2002 Enh. Bug 2747329.Modified finpl_upd_conv_prc_run_ind procedure
33 --shtatiko 15-JAN-2003 Bug# 2736389, Modified convert_holds_balances to handle validation failure
34 -- cases.
35 --pathipat 07-Jan-2003 Bug: 2672837 - Modified convert_holds_balances
36 -- Removed func lookup_desc() as it is no longer used here.
37 --vvutukur 11-Dec-2002 Enh#2584741.Modification done in calculate_balance,finpl_upd_conv_prc_run_ind
38 -- procedure.
39 --smadathi 02-dec-2002 Bug 2690020. NOCOPY hint added
40 --shtatiko 10-Oct-2002 Enh# 2562745 Obsoleted calc_balances concurrent executable.
41 --pathipat 08-OCT-2002 Enh# 2562745 Added 2 new public procedures convert_holds_balances() and
42 -- conv_balances() for new concurrent program, Holds Conversion.
43 -- Added private procedure finpl_upd_conv_prc_run_ind().
44 --vvutukur 07-Oct-2002 Enh#2562745.Renamed function calculate_balance_1 to public procedure
45 -- calculate_balance. Removed previously existing procedure
46 -- calculate_balance procedure from spec and body.Modified function
47 -- lookup_desc.
48 --vvutukur 01-Oct-2002 Enh#2562745.Modified update_balances,retro_update_balance,
49 -- insert_or_update_balance,check_exclusion_rules,calculate_balance_1.
50 -- smvk 17-Sep-2002 Removed the usage of subaccout_id in the entire package, As a part of Bug # 2564643.
51 --smadathi 03-Jul-2002 Bug 2443082. Modified update_balances procedure. Added new private function
52 -- retro_update_balance.
53 --agairola 11-Jun-2002 Bug No:2373963 Modified the code for the calculate_balance and calculate_balance_1
54 --agairola 30-May-2002 Bug # 2364505, modified the code for the removal of the Standard Balance Rule Id
55 --vvutukur 09-may-2002 Bugs#2329042,2309047,Modifications done in calculate_balance,calculate_balance_1,
56 -- check_exclusion_rules.
57 --smadathi 10-APR-2002 Bug 2289191. Function calculate_balance_1,procedure calculate_balance
58 -- procedure check_exclusion_rules Modified.
59 --vvutukur 09-apr-2002 Modifications done in calculate_balance and calculate_balance_1 for
60 -- bug:2172457.
61 --vvutukur 10-MAR-2002 Modified the code in calculate_balance_1 for bug:2172457.
62 --schodava 28-FEB-2002 Bug # 2244532
63 -- Removed the function check_valid_party_subaccts
64 -- and its call.
65 --schodava 27-FEB-2002 Enh # 2238362
66 -- Changes in Person Context Block of Student Finance Forms
67 -- Modified procedure calculate_balance, calculate_balance_1
68 --Sarakshi 8-OCT-2001 Bug No:2030448 ,modified procedure calculate_balance and
69 -- calculate_balance_1
70 --smadathi 07082001 Fixed Bug No. 1921761 .Modified procedure calculate_balance .
71 --Nishikant 10DEC2001 The function check_exclusion_rules added for the
72 -- enhancement bug# 2124001
73 -------------------------------------------------------------------
74
75 g_ind_yes CONSTANT VARCHAR2(1) := 'Y';
76 l_validation_exp exception;
77
78 -- Cursor for validating the person id
79 CURSOR cur_person_number(cp_party_id IN hz_parties.party_id%TYPE) IS
80 SELECT party_number
81 FROM hz_parties
82 WHERE party_id = cp_party_id;
83
84 /* Removed the global variable uses to store the subaccount Name, as a part of Bug # 2564643 */
85
86 -- Forward declaration of function retro_update_balance
87 FUNCTION retro_update_balance
88 (
89 p_n_party_id IN igs_fi_balances.party_id%TYPE ,
90 /* Removed th parameter p_n_subaccount_id, as a part of Bug # 2564643 */
91 p_c_balance_type IN igs_lookups_view.lookup_code%TYPE ,
92 p_d_balance_date IN igs_fi_balances.balance_date%TYPE ,
93 p_n_amount IN igs_fi_inv_int.invoice_amount%TYPE ,
94 p_c_message OUT NOCOPY fnd_new_messages.message_name%TYPE
95 )
96 RETURN BOOLEAN;
97
98
99 -- Bug # 2244532
100 -- Removed the FUNCTION check_valid_party_subaccts
101 -- as it is deemed obsolete (as per Enh # 2201081)
102
103
104 PROCEDURE calculate_balance(
105 p_person_id IN igs_pe_person_v.person_id%TYPE,
106 p_balance_type IN igs_lookup_values.lookup_code%TYPE,
107 p_balance_date IN igs_fi_balances.balance_date%TYPE,
108 p_action IN VARCHAR2,
109 p_balance_rule_id IN igs_fi_balance_rules.balance_rule_id%TYPE,
110 p_balance_amount OUT NOCOPY igs_fi_balances.standard_balance%TYPE,
111 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
112 ) AS
113 ------------------------------------------------------------------
114 --Created by : Sanil Madathil, Oracle IDC
115 --Date created: 27052001
116 --Purpose:This public procedure was earlier a local function calculate_balance_1.
117 -- As part of Reassess Balances Build(Enh#2562745), this is made public procedure by adding
118 -- 4 new parameters hence added to the package spec also.This procedure is called from
119 -- Holds Conversion Process, a newly created concurrent program as part of the Reassess
120 -- Balances Build, and existing Finance and Late Charges Process also for Holds and Fee Balance
121 -- calculation.
122 -- This procedure returns the balance amount conditionally on whether the requirement is
123 -- "As on a particular balance date" or "For a Particular Balance Date" through the OUT
124 -- parameter p_balance_amount.
125 --
126 --Known limitations/enhancements and/or remarks:
127 --
128 --Change History:
129 --Who When What
130 --abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
131 --shtatiko 17-OCT-2003 Bug# 3192641, Modified cursor cur_chrgs_for_balance so that waived charges are considered
132 -- only when the balance type is FEE. For Holds, waived charges are considered in Process that apply/release holds.
133 --schodava 06-Oct-2003 Bug # 3123405 - Modified cursors cur_chrgs_for_balance and cur_crds_for_balance.
134 -- Also modified logic, to use the invoice_amount and credit amount, instead of
135 -- invoice_amount_due and unapplied_amount respectively.
136 --vvutukur 10-DEC-2002 Enh# 2584741 - Modified cursor cur_crds_for_balance - added join with igs_fi_cr_types
137 -- to exclude deposit records while obtaining records for balances calculation
138 --vvutukur 04-Oct-2002 Enh#2562745.Removed references to balance types 'Installment','Other'
139 -- as the same have become obsolete.Renamed this local function calculate_balance_1
140 -- to a public procedure calculate_balance.Modified charges and credits cursors
141 -- to fetch the balance depending upon this procedure's newly added input
142 -- parameter p_action, also added check to pickup records with invoice_amount_due
143 -- or unapplied_amount > 0 only.
144 --smvk 17-Sep-2002 Removed the input parameter p_subaccount_id and
145 -- its is usage in this function, as a part of Bug # 2564643.
146 --agairola 11-Jun-2002 Bug No:2373963 The following modifications were done
147 -- 1. In the WHERE clause of the Cursor for charges added the condition for the
148 -- invoice_creation_date to be less than sysdate
149 -- 2. The Balances were getting created/updated only if there were any charges
150 -- or credit records found. Incase of no charges or credits, the balances were
151 -- not getting updated/created. Modified the code to update or create the balance
152 -- even if no charge or credits were found
153 --agairola 30-May-2002 Bug # 2364505, modified the code for the removal of the Standard Balance Rule Id
154 --vvutukur 09-may-2002 Bugs#2329042,2309047. Modified c_fi_inv_int_all,c_fi_credits_all cursors
155 -- and removed cursor c_igs_fi_bal_rules. Put a call to check_exclusion_rules
156 -- and removed redundant code for assigning cursor fetched values into local variables.
157 --smadathi 10-APR-2002 Bug 2289191. References to enabled_flag column removed from cursor
158 -- c_fi_inv_int_all, c_fi_credits_all select list.
159 --vvutukur 09-APR-2002 Removed unnecessary condition check for balance_rule_id in where clause of
160 -- c_fi_inv_int_all,c_fi_credits_all cursors.Moved manipulation of cursors
161 -- cur_person_number,cur_subaccount_name out of for loop in calculate_balance_1
162 -- for the improvement of code.for bug#2293676.
163 --vvutukur 10-MAR-2002 Removed code which throws error message if no balance rules exist.
164 -- Modified the cursors c_fi_inv_int_all,c_fi_credits_all to select rows even if
165 -- there are no balance rules defined.Shown numbers instead of IDs in case of
166 -- Party,Credit,Invoice and name for Subaccount in the LOG. bug:2172457.
167 --schodava 28-FEB-2002 Enh # 2238362
168 -- Modified the logging of messages
169 -- Changed references to 'Person' to 'Party'
170 --Nishikant 18DEC2001 The cursor c_fi_inv_int_all modified to exclude the waived charges in the balance Rule.
171 -- Enh Bug#2124001.
172 --sarakshi 8-oct-2001 1. removed the parameter accounting method from the call as well as the definition.
173 -- of this function and removed the logic of calculating balance based on accounting method
174 -- 2. removed from the cursors selecting records based on the balance_flag condition from
175 -- charges and credits table.
176 -- 3. Now balance amount = sum of invoice amount due(from charges table)
177 -- - sum of unapplied amount(credits table).
178 -- 4. We insert a record in the balance table if for a combination of party_id,subaccount_id
179 -- and balance_date no records exists there , else we update the balance amount depending
180 -- upon the balance type.
181 -------------------------------------------------------------------
182 l_as_on_baldate CONSTANT VARCHAR2(20) := 'ASONBALDATE';
183 l_for_baldate CONSTANT VARCHAR2(20) := 'FORBALDATE';
184
185 -- cursor reads from the charges tables
186 CURSOR cur_chrgs_for_balance IS
187 SELECT inv.*
188 FROM igs_fi_inv_int inv
189 WHERE person_id = p_person_id /*for person id passed as parameter*/
190 AND ((p_action = l_for_baldate AND TRUNC(inv.invoice_creation_date) = TRUNC(p_balance_date))
191 OR (p_action = l_as_on_baldate AND TRUNC(inv.invoice_creation_date) <= TRUNC(NVL(p_balance_date,sysdate)))
192 )
193 AND NOT EXISTS ( SELECT '1'
194 FROM igs_fi_inv_wav_det fiw
195 WHERE fiw.invoice_id = inv.invoice_id
196 AND fiw.balance_type = p_balance_type
197 AND p_balance_type = 'FEE'
198 AND ((fiw.end_dt IS NOT NULL AND p_balance_date BETWEEN fiw.start_dt AND fiw.end_dt)
199 OR (fiw.end_dt IS NULL AND p_balance_date >= fiw.start_dt)
200 )
201 );
202
203 -- cursor reads from credits table
204 CURSOR cur_crds_for_balance IS
205 SELECT cra.*
206 FROM igs_fi_credits crd,
207 igs_fi_cr_activities cra,
208 igs_fi_cr_types cty
209 WHERE party_id = p_person_id
210 AND crd.credit_id = cra.credit_id
211 AND cty.credit_type_id = crd.credit_type_id
212 AND cty.credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT')
213 AND ((p_action = l_as_on_baldate AND TRUNC(crd.effective_date) <= TRUNC(NVL(p_balance_date,SYSDATE)))
214 OR (p_action = l_for_baldate AND TRUNC(crd.effective_date) = TRUNC(p_balance_date))
215 );
216
217 -- Added by sarakshi, as a part of SFCR010
218 CURSOR cur_rec_exists IS
219 SELECT ifb.rowid, ifb.*
220 FROM igs_fi_balances ifb
221 WHERE party_id = p_person_id
222 AND TRUNC(balance_date) = TRUNC(p_balance_date);
223
224 l_cur_rec_exists cur_rec_exists%ROWTYPE;
225 l_invoice_amount igs_fi_balances.standard_balance%TYPE := 0.0;
226 l_credit_amount igs_fi_credits_all.amount%TYPE := 0.0;
227 l_balance igs_fi_balances.standard_balance%TYPE := 0.0;
228 l_rowid igs_fi_inv_int_v.row_id%TYPE;
229 l_balance_id igs_fi_balances.balance_id%TYPE;
230 l_bal_standard igs_fi_balances.standard_balance%TYPE := 0.0;
231 l_bal_fee igs_fi_balances.standard_balance%TYPE := 0.0;
232 l_bal_hold igs_fi_balances.standard_balance%TYPE := 0.0;
233 l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
234 l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
235 l_message fnd_new_messages.message_name%TYPE := NULL;
236 l_return_status BOOLEAN := FALSE;
237
238 BEGIN
239 p_message_name := NULL;
240 l_invoice_amount := 0.0; -- Initialise the total invoice amount to 0.0
241
242 --loop thru all charge records.
243 FOR l_cur_chrgs_for_baldate IN cur_chrgs_for_balance
244 LOOP
245 l_message:= NULL;
246 l_return_status := FALSE;
247
248 --Check exclusion rules only if balance_rule_id is not null.
249 IF p_balance_rule_id IS NOT NULL THEN
250 --check for exclusion rules for charge record.
251 l_return_status := igs_fi_prc_balances.check_exclusion_rules(
252 p_balance_type => p_balance_type,
253 p_balance_date => p_balance_date,
254 p_source_type => 'CHARGE',
255 p_source_id => l_cur_chrgs_for_baldate.invoice_id,
256 p_balance_rule_id => p_balance_rule_id,
257 p_message_name => l_message);
258 END IF;
259
260 --if charge is not excluded only, calculate the sum of invoice amount due.
261 IF l_message IS NULL AND l_return_status = FALSE THEN
262 l_invoice_amount := NVL(l_invoice_amount ,0.0) + NVL(l_cur_chrgs_for_baldate.invoice_amount ,0.0) ; /* accumulates invoice amount */
263 END IF;
264 END LOOP ;
265
266 l_credit_amount := 0.0; --Initialise total credit amount to 0.
267
268 --Loop thru all credit records.
269 FOR l_cur_crds_for_balance IN cur_crds_for_balance
270 LOOP
271 l_message := NULL;
272 l_return_status := FALSE;
273
274 --Check exclusion rules only if balance_rule_id is not null.
275 IF p_balance_rule_id IS NOT NULL THEN
276 --check for exclusion rules for credit record.
277 l_return_status := igs_fi_prc_balances.check_exclusion_rules(
278 p_balance_type => p_balance_type,
279 p_balance_date => p_balance_date,
280 p_source_type => 'CREDIT',
281 p_source_id => l_cur_crds_for_balance.credit_id,
282 p_balance_rule_id => p_balance_rule_id,
283 p_message_name => l_message);
284 END IF;
285
286 --if credit is not excluded, calculate sum of credit amount
287 IF l_message IS NULL AND l_return_status = FALSE THEN
288 l_credit_amount := NVL(l_credit_amount,0.0) + NVL(l_cur_crds_for_balance.amount,0.0) ; /* accumulates unapplied amount */
289 END IF;
290 END LOOP;
291
292 -- Added by sarakshi , as a part of SFCR010
293 --get the balance amount by subtracting total unapplied amount from the total invoice due.
294 l_balance := NVL(l_invoice_amount,0.0) - NVL(l_credit_amount,0.0);
295
296 --assign the amount to the corresponding balance.
297 IF p_balance_type = 'STANDARD' THEN
298 l_bal_standard := l_balance ;
299 l_bal_fee := NULL;
300 l_bal_hold := NULL;
301 l_bal_rule_fee := NULL;
302 l_bal_rule_hold:= NULL;
303 ELSIF p_balance_type = 'FEE' THEN
304 l_bal_standard := NULL;
305 l_bal_fee := l_balance;
306 l_bal_hold := NULL;
307 l_bal_rule_fee := p_balance_rule_id;
308 l_bal_rule_hold:= NULL;
309 ELSIF p_balance_type = 'HOLDS' THEN
310 l_bal_standard := NULL;
311 l_bal_fee := NULL;
312 l_bal_hold := l_balance;
313 l_bal_rule_fee := NULL;
314 l_bal_rule_hold := p_balance_rule_id;
315 END IF;
316
317 --Added by sarakshi , as a part of SFCR010
318 --insertion/updation of record in igs_fi_balances table will happen only if p_action is 'ASONBALDATE'.
319 IF p_action = l_as_on_baldate THEN
320 OPEN cur_rec_exists ;
321 FETCH cur_rec_exists INTO l_cur_rec_exists;
322 IF cur_rec_exists%FOUND THEN
323 CLOSE cur_rec_exists;
324 IF p_balance_type = 'STANDARD' THEN
325 l_cur_rec_exists.standard_balance := l_balance;
326 ELSIF p_balance_type = 'FEE' THEN
327 l_cur_rec_exists.fee_balance := l_balance;
328 l_cur_rec_exists.fee_balance_rule_id := p_balance_rule_id;
329 ELSIF p_balance_type = 'HOLDS' THEN
330 l_cur_rec_exists.holds_balance := l_balance;
331 l_cur_rec_exists.holds_balance_rule_id := p_balance_rule_id;
332 END IF;
333
334 l_balance_id:= l_cur_rec_exists.balance_id;
335
336 --update the row in igs_fi_balances table if already a record exists with a combination of party,
337 --balance type and balance date.
338 -- Removed the parameter subaccount_id, as a part of Bug # 2564643
339 igs_fi_balances_pkg.update_row(
340 X_ROWID => l_cur_rec_exists.rowid,
341 X_BALANCE_ID => l_cur_rec_exists.balance_id,
342 X_PARTY_ID => l_cur_rec_exists.party_id,
343 X_STANDARD_BALANCE => l_cur_rec_exists.standard_balance,
344 X_FEE_BALANCE => l_cur_rec_exists.fee_balance,
345 X_HOLDS_BALANCE => l_cur_rec_exists.holds_balance,
346 X_BALANCE_DATE => l_cur_rec_exists.balance_date,
347 X_FEE_BALANCE_RULE_ID => l_cur_rec_exists.fee_balance_rule_id,
348 X_HOLDS_BALANCE_RULE_ID => l_cur_rec_exists.holds_balance_rule_id,
349 X_MODE => 'R'
350 );
351 ELSE --if no record exists in igs_fi_balances...
352 l_balance_id:=null;
353 CLOSE cur_rec_exists;
354 --Insert a row in igs_fi_balances table.
355 -- Removed the parameter subaccount_id, as a part of Bug # 2564643
356
357 l_bal_standard := igs_fi_gen_gl.get_formatted_amount(l_bal_standard);
358 l_bal_fee := igs_fi_gen_gl.get_formatted_amount(l_bal_fee);
359 l_bal_hold := igs_fi_gen_gl.get_formatted_amount(l_bal_hold);
360
361 igs_fi_balances_pkg.insert_row
362 ( X_ROWID => l_rowid,
363 X_BALANCE_ID => l_balance_id,
364 X_PARTY_ID => p_person_id,
365 X_STANDARD_BALANCE => l_bal_standard,
366 X_FEE_BALANCE => l_bal_fee,
367 X_HOLDS_BALANCE => l_bal_hold,
368 X_BALANCE_DATE => p_balance_date,
369 X_FEE_BALANCE_RULE_ID => l_bal_rule_fee,
370 X_HOLDS_BALANCE_RULE_ID => l_bal_rule_hold,
371 X_MODE => 'R'
372 );
373 END IF;
374 END IF;
375
376 p_balance_amount := l_balance;
377 EXCEPTION
378 WHEN OTHERS THEN
379 p_balance_amount := 0.0;
380 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
381
382 END calculate_balance; --procedure ends here.
383
384
385 PROCEDURE calc_balances ( errbuf OUT NOCOPY VARCHAR2 ,
386 retcode OUT NOCOPY NUMBER ,
387 p_person_id IN igs_pe_person_v.person_id%TYPE ,
388 p_person_id_grp IN igs_pe_persid_group_v.group_id%TYPE ,
389 p_bal_type IN igs_lookups_view.lookup_code%TYPE ,
390 p_bal_date IN VARCHAR2 ,
391 p_org_id IN NUMBER
392 ) IS
393
394 ------------------------------------------------------------------
395 --Created by : Sanil Madathil, Oracle IDC
396 --Date created: 02062001
397 --
398 --Purpose: This Procedure calls Calculate balance Procedure .
399 -- This procedure is registered with Concurrent Manager.
400 -- The concurrent manager initiates this procedure .
401 --
402 --
403 --Known limitations/enhancements and/or remarks:
404 --
405 --Change History:
406 --Who When What
407 --shtatiko 10-Oct-2002 Obsoleted this concurrent executable as part of
408 -- Enh# 2562745.
409 --smvk 17-Sep-2002 Removed the input parameter p_subaccount_id and its usage
410 -- in this functin as a part of Bug # 2564643
411 -------------------------------------------------------------------
412 l_bal_date igs_fi_balances.balance_date%TYPE ;
413
414 BEGIN
415
416 -- This concurrent job is made obsolete as part of Enh#2562745. If user tried to
417 -- run the program then an error message should be written to the Log file that
418 -- the Concurrent Program is obsolete and this should not be run
419 FND_MESSAGE.Set_Name('IGS',
420 'IGS_GE_OBSOLETE_JOB');
421 FND_FILE.Put_Line(FND_FILE.Log,
422 FND_MESSAGE.Get);
423 retcode := 0;
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 RETCODE:=2;
428 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
429 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
430
431 END calc_balances ; /* procedure ends here */
432
433 /**************The below procedure added as part of SFCR 10 **************/
434 PROCEDURE update_balances ( p_party_id IN igs_fi_balances.party_id%TYPE ,
435 p_balance_type IN igs_lookups_view.lookup_code%TYPE ,
436 p_balance_date IN igs_fi_balances.balance_date%TYPE ,
437 p_amount IN igs_fi_inv_int.invoice_amount%TYPE ,
438 p_source IN VARCHAR2 ,
439 p_source_id IN NUMBER ,
440 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE
441 ) IS
442
443 ------------------------------------------------------------------
444 --Created by : Syam Krishnan, Oracle IDC
445 --Date created: 03/10/2001
446 --
447 --Purpose: For Updation of real time balances
448 --Known limitations/enhancements and/or remarks:
449 --
450 --Change History:
451 --Who When What
452 --vvutukur 01-Oct-2002 Enh#2562745.Removed cursor c_igs_fi_bal_rules and its usage in this procedure.
453 -- Instead added call to generic procedure igs_fi_gen_007.finp_get_balance_rule.
454 -- Modified local function insert_or_update_balance.
455 --smvk 17-Sep-2002 Removed the input parameter p_subaccount_id and
456 -- its usage from this function as a part of Bug # 2564643.
457 --smadathi 03-Jul-2002 Bug 2443082. Modified insert_or_update_balance function. Incorporated invokation of
458 -- retro_update_balance function for retroactive updation of balances
459 --agairola 30-May-2002 Bug # 2364505, modified the code for the removal of the Standard Balance Rule Id
460 --Nishikant 18DEC2001 A new parameter p_source_id added to the procedure and
461 -- three parameters p_source_date, p_fee_type, p_credit_type_id removed
462 -- from the procedure. The code written to check the credit or charge
463 -- transaction is excluded or not based upon exclusion rules is
464 -- removed by the call to the check exclusion rules function.
465 -- Enhancement bug#2124001.
466 -------------------------------------------------------------------
467
468 l_v_message fnd_new_messages.message_name%TYPE;
469 l_v_insert_upd_message fnd_new_messages.message_name%TYPE;
470 l_func_ret_status BOOLEAN := TRUE;
471 l_return_status BOOLEAN;
472
473 --following local variables are declared as part of enh#2562745.
474 l_action_active CONSTANT VARCHAR2(10):= 'ACTIVE';
475 l_action_max CONSTANT VARCHAR2(10):= 'MAX';
476 l_hold_bal_type CONSTANT igs_lookup_values.lookup_code%TYPE := 'HOLDS';
477 l_fee_bal_type CONSTANT igs_lookup_values.lookup_code%TYPE := 'FEE';
478 l_std_bal_type CONSTANT igs_lookup_values.lookup_code%TYPE := 'STANDARD';
479
480 l_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE := NULL;
481 l_last_conversion_date DATE := NULL;
482 l_version_number igs_fi_balance_rules.version_number%TYPE := NULL;
483
484 --removed cursor c_igs_fi_bal_rules.bug#2562745.
485
486 /** Local function for Validation of parameters **/
487 FUNCTION validate_params (p_message OUT NOCOPY fnd_new_messages.message_name%TYPE ) RETURN BOOLEAN IS
488
489 ------------------------------------------------------------------
490 --Created by : Syam Krishnan, Oracle IDC
491 --Date created: 03/10/2001
492 --
493 --Purpose: Local Function for Validation of parameters
494 --Known limitations/enhancements and/or remarks:
495 --
496 --Change History:
497 --Who When What
498 --smvk 17-Sep-2002 Removed the validation for the parameter p_subaccount_id
499 -- as a part of Bug # 2564643.
500 -------------------------------------------------------------------
501 BEGIN
502
503 IF ( p_party_id IS NULL OR p_balance_type IS NULL
504 OR p_balance_date IS NULL OR p_source IS NULL OR p_amount IS NULL
505 OR p_source_id IS NULL ) THEN
506 p_message := 'IGS_FI_PARAMETER_NULL';
507 RETURN FALSE;
508 END IF;
509 RETURN TRUE;
510 END validate_params;
511
512 /** Local function for Validation of parameters **/
513
514 /* Local Function for Updation or insert into balances table */
515
516 FUNCTION insert_or_update_balance (p_balance_rule_id IN igs_fi_balance_rules.balance_rule_id%TYPE,
517 p_message OUT NOCOPY fnd_new_messages.message_name%TYPE ) RETURN BOOLEAN IS
518
519 ------------------------------------------------------------------
520 --Created by : Syam Krishnan, Oracle IDC
521 --Date created: 03/10/2001
522 --
523 --Purpose: Local Function for Updation or insert into balances table
524 --Known limitations/enhancements and/or remarks:
525 --
526 --Change History:
527 --Who When What
528 --abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
529 --vvutukur 01-Oct-2002 Enh#2562745.Removed cursors c_inst_balance,c_other_balance and their
530 -- usage in the code.Also removed references to balance types INSTALLMENT,
531 -- OTHER as these have been obsolete.Modified tbh calls(igs_fi_balances_pkg)accordingly.
532 --smvk 17-Sep-2002 Removed the references of the parameter p_subaccount_id from this function, as a part of Bug # 2564643
533 --smadathi 03-Jul-2002 Bug 2443082. Modified cursor c_std_balance, c_fee_balance, c_holds_balance,c_inst_balance,
534 -- c_other_balance select statements.
535 --agairola 30-May-2002 Bug # 2364505, modified the code for the removal of the Standard Balance Rule Id
536 --Nishikant 18DEC2001 Code added to check for the parameter p_balance_type
537 -- is valid or not. Enh bug#2124001
538 -------------------------------------------------------------------
539
540 /* Cursors to select the previously existing balance and rule id n table irresspective of the balance type..will select only for a particular balance type - see where clause */
541
542 -- Cursor selects all the standard balances for person , subaccount combination
543 -- which are created before the balance date parameter in the descending order of balance date
544
545 CURSOR c_std_balance IS
546 SELECT standard_balance
547 FROM igs_fi_balances
548 WHERE party_id = p_party_id
549 /* Removed the parameter p_subaccount_id from the where clause, as a part of Bug # 2564643 */
550 AND standard_balance IS NOT NULL
551 AND TO_CHAR(balance_date,'YYYYMMDD') <= TO_CHAR(p_balance_date,'YYYYMMDD')
552 ORDER by balance_date desc;
553
554
555 CURSOR c_fee_balance IS
556 SELECT fee_balance
557 FROM igs_fi_balances
558 WHERE party_id = p_party_id
559 /* Removed the parameter p_subaccount_id from the where clause, as a part of Bug # 2564643 */
560 AND fee_balance IS NOT NULL
561 AND TO_CHAR(balance_date,'YYYYMMDD') <= TO_CHAR(p_balance_date,'YYYYMMDD')
562 ORDER by balance_date desc;
563
564
565 CURSOR c_holds_balance IS
566 SELECT holds_balance
567 FROM igs_fi_balances
568 WHERE party_id = p_party_id
569 /* Removed the parameter p_subaccount_id from the where clause, as a part of Bug # 2564643 */
570 AND holds_balance IS NOT NULL
571 AND TO_CHAR(balance_date,'YYYYMMDD') <= TO_CHAR(p_balance_date,'YYYYMMDD')
572 ORDER by balance_date desc;
573
574
575 /* Cursor used for updation into the table igs_fi_balances for the party_id, subaccount_id and balance_date */
576 CURSOR c_upd_balance IS
577 SELECT bal.rowid, bal.*
578 FROM igs_fi_balances bal
579 WHERE party_id = p_party_id
580 /* Removed the parameter p_subaccount_id from the where clause, as a part of Bug # 2564643 */
581 AND TO_CHAR(balance_date,'YYYYMMDD') = TO_CHAR(p_balance_date,'YYYYMMDD');
582
583
584 l_b_insert BOOLEAN := FALSE;
585 rec_upd_balance c_upd_balance%ROWTYPE;
586
587 l_rowid rowid;
588 l_balance_id igs_fi_balances.balance_id%TYPE := NULL;
589 l_bal_standard igs_fi_balances.standard_balance%TYPE := NULL;
590 l_bal_fee igs_fi_balances.standard_balance%TYPE := NULL;
591 l_bal_hold igs_fi_balances.standard_balance%TYPE := NULL;
592 l_bal_rule_fee igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
593 l_bal_rule_hold igs_fi_balance_rules.balance_rule_id%TYPE := NULL ;
594
595 l_n_table_standard_balance igs_fi_balances.standard_balance%TYPE := NUll;
596 l_n_table_fee_balance igs_fi_balances.fee_balance%TYPE := NULL;
597 l_n_table_holds_balance igs_fi_balances.holds_balance%TYPE := NUll;
598
599
600
601 BEGIN
602
603 /* Fetch the existin table balances */
604 OPEN c_std_balance;
605 FETCH c_std_balance INTO l_n_table_standard_balance;
606 CLOSE c_std_balance;
607
608 OPEN c_fee_balance;
609 FETCH c_fee_balance INTO l_n_table_fee_balance;
610 CLOSE c_fee_balance;
611
612 OPEN c_holds_balance;
613 FETCH c_holds_balance INTO l_n_table_holds_balance;
614 CLOSE c_holds_balance;
615
616
617 /** Based on The balance type the new balance p_amount is added to the existing balance as below used for update_row and insert_row**/
618 IF p_balance_type = 'STANDARD' THEN
619 l_n_table_standard_balance := NVL(l_n_table_standard_balance,0) + NVL(p_amount,0);
620 ELSIF p_balance_type = 'FEE' THEN
621 l_n_table_fee_balance := NVL(l_n_table_fee_balance,0) + NVL(p_amount,0);
622 ELSIF p_balance_type = 'HOLDS' THEN
623 l_n_table_holds_balance := NVL(l_n_table_holds_balance,0) + NVL(p_amount,0);
624 END IF;
625
626 /* Open cursor for updation */
627 OPEN c_upd_balance;
628 FETCH c_upd_balance INTO rec_upd_balance;
629 IF c_upd_balance%FOUND THEN
630 CLOSE c_upd_balance;
631 l_b_insert := FALSE; /* When record found then No insert required */
632 ELSE
633 CLOSE c_upd_balance;
634 l_b_insert := TRUE; /* No revord found Insert required */
635 END IF;
636
637 IF l_b_insert THEN
638 /* When insert required then the corresping colummns balance amount + origibal table balance (will be 0 if nothing was present) and rule id is populated depending on the balance type - We prefer to keep the other fields null */
639
640 IF p_balance_type = 'STANDARD' THEN
641 l_bal_standard := NVL(l_n_table_standard_balance,0) ;
642 l_bal_fee := NULL ;
643 l_bal_hold := NULL ;
644 l_bal_rule_fee := NULL ;
645 l_bal_rule_hold := NULL ;
646 ELSIF p_balance_type = 'FEE' THEN
647 l_bal_standard := NULL ;
648 l_bal_fee := NVL(l_n_table_fee_balance,0) ;
649 l_bal_hold := NULL ;
650 l_bal_rule_fee := p_balance_rule_id ;
651 l_bal_rule_hold := NULL ;
652 ELSIF p_balance_type = 'HOLDS' THEN
653 l_bal_standard := NULL ;
654 l_bal_fee := NULL ;
655 l_bal_hold := NVL(l_n_table_holds_balance,0) ;
656 l_bal_rule_fee := NULL ;
657 l_bal_rule_hold := p_balance_rule_id ;
658 ELSE
659 p_message := 'IGS_GE_INVALID_VALUE';
660 RETURN FALSE;
661 END IF;
662
663 /* Start of insert into the IGS_FI_BALANCES */
664 BEGIN
665 l_bal_standard := igs_fi_gen_gl.get_formatted_amount(l_bal_standard);
666 l_bal_fee := igs_fi_gen_gl.get_formatted_amount(l_bal_fee);
667 l_bal_hold := igs_fi_gen_gl.get_formatted_amount(l_bal_hold);
668
669
670 igs_fi_balances_pkg.insert_row ( X_ROWID => l_rowid ,
671 X_BALANCE_ID => l_balance_id ,
672 X_PARTY_ID => p_party_id ,
673 /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
674 X_STANDARD_BALANCE => l_bal_standard ,
675 X_FEE_BALANCE => l_bal_fee ,
676 X_HOLDS_BALANCE => l_bal_hold ,
677 X_BALANCE_DATE => p_balance_date ,
678 X_FEE_BALANCE_RULE_ID => l_bal_rule_fee ,
679 X_HOLDS_BALANCE_RULE_ID => l_bal_rule_hold ,
680 X_MODE => 'R'
681 );
682 EXCEPTION
683 WHEN OTHERS THEN
684 p_message := 'IGS_GE_UNHANDLED_EXCEPTION';
685 RETURN FALSE;
686 END;
687
688 ELSIF NOT l_b_insert THEN
689 /* For update we update only the corresponding balance amoounts abd leave others same */
690
691 IF p_balance_type = 'STANDARD' THEN
692 BEGIN
693 l_n_table_standard_balance :=igs_fi_gen_gl.get_formatted_amount(l_n_table_standard_balance);
694 igs_fi_balances_pkg.update_row
695 (
696 X_ROWID => rec_upd_balance.rowid ,
697 X_BALANCE_ID => rec_upd_balance.balance_id ,
698 X_PARTY_ID => rec_upd_balance.party_id ,
699 /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
700 X_STANDARD_BALANCE => l_n_table_standard_balance ,
701 X_FEE_BALANCE => rec_upd_balance.fee_balance,
702 X_HOLDS_BALANCE => rec_upd_balance.holds_balance ,
703 X_BALANCE_DATE => rec_upd_balance.balance_date ,
704 X_FEE_BALANCE_RULE_ID => rec_upd_balance.fee_balance_rule_id ,
705 X_HOLDS_BALANCE_RULE_ID => rec_upd_balance.holds_balance_rule_id ,
706 X_MODE => 'R'
707 ) ;
708
709 EXCEPTION
710 WHEN OTHERS THEN
711 p_message := 'IGS_GE_UNHANDLED_EXCEPTION';
712 RETURN FALSE;
713 END;
714
715 ELSIF p_balance_type = 'FEE' THEN
716 BEGIN
717 l_n_table_fee_balance :=igs_fi_gen_gl.get_formatted_amount(l_n_table_fee_balance);
718 igs_fi_balances_pkg.update_row
719 ( X_ROWID => rec_upd_balance.rowid ,
720 X_BALANCE_ID => rec_upd_balance.balance_id ,
721 X_PARTY_ID => rec_upd_balance.party_id ,
722 /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
723 X_STANDARD_BALANCE => rec_upd_balance.standard_balance ,
724 X_FEE_BALANCE => l_n_table_fee_balance,
725 X_HOLDS_BALANCE => rec_upd_balance.holds_balance ,
726 X_BALANCE_DATE => rec_upd_balance.balance_date ,
727 X_FEE_BALANCE_RULE_ID => p_balance_rule_id ,
728 X_HOLDS_BALANCE_RULE_ID => rec_upd_balance.holds_balance_rule_id ,
729 X_MODE => 'R'
730 ) ;
731 EXCEPTION
732 WHEN OTHERS THEN
733 p_message := 'IGS_GE_UNHANDLED_EXCEPTION';
734 RETURN FALSE;
735 END;
736
737 ELSIF p_balance_type = 'HOLDS' THEN
738 BEGIN
739 l_n_table_holds_balance :=igs_fi_gen_gl.get_formatted_amount(l_n_table_holds_balance);
740 igs_fi_balances_pkg.update_row
741 ( X_ROWID => rec_upd_balance.rowid ,
742 X_BALANCE_ID => rec_upd_balance.balance_id ,
743 X_PARTY_ID => rec_upd_balance.party_id ,
744 /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
745 X_STANDARD_BALANCE => rec_upd_balance.standard_balance ,
746 X_FEE_BALANCE => rec_upd_balance.fee_balance,
747 X_HOLDS_BALANCE => l_n_table_holds_balance ,
748 X_BALANCE_DATE => rec_upd_balance.balance_date ,
749 X_FEE_BALANCE_RULE_ID => rec_upd_balance.fee_balance_rule_id ,
750 X_HOLDS_BALANCE_RULE_ID => p_balance_rule_id ,
751 X_MODE => 'R'
752 ) ;
753 EXCEPTION
754 WHEN OTHERS THEN
755 p_message := 'IGS_GE_UNHANDLED_EXCEPTION';
756 RETURN FALSE;
757 END;
758 ELSE
759 p_message := 'IGS_GE_INVALID_VALUE';
760 RETURN FALSE;
761 END IF;
762
763 END IF;
764
765 /* If everything is OK then return TRUE */
766 RETURN TRUE;
767 END insert_or_update_balance;
768
769
770 BEGIN /* Main procedure update_balances */
771 --Validation of all parameters.
772 IF NOT validate_params(l_v_message) THEN
773 --If any of the validation fails then return message and get out of procedure.
774 p_message_name := l_v_message;
775 RETURN;
776 END IF;
777
778 IF p_amount <> 0 THEN
779 -- The entire update balance process happens only if the passed parameter p_amount is not equal
780 -- to 0 since there is no use updating a 0 balance.
781
782 --For Standard balance,there is no need to derive the balance_rule_id and hence no exclusion
783 --rules can be checked.
784 IF p_balance_type <> l_std_bal_type THEN
785 --Fetch balance_rule_id of active Holds balance_type.
786 IF p_balance_type = l_hold_bal_type THEN
787 igs_fi_gen_007.finp_get_balance_rule(p_v_balance_type => p_balance_type,
788 p_v_action => l_action_active,
789 p_n_balance_rule_id => l_balance_rule_id,
790 p_d_last_conversion_date => l_last_conversion_date,
791 p_n_version_number => l_version_number
792 );
793 --If no balance rule exists for Holds Balance Type.
794 IF l_version_number = 0 THEN
795 --Error out of the procedure.
796 p_message_name := 'IGS_FI_CANNOT_CRT_TXN';
797 RETURN;
798 END IF;
799 --Fetch balance rule id of latest Fee balance_type.
800 ELSIF p_balance_type = l_fee_bal_type THEN
801 igs_fi_gen_007.finp_get_balance_rule(p_v_balance_type => p_balance_type,
802 p_v_action => l_action_max,
803 p_n_balance_rule_id => l_balance_rule_id,
804 p_d_last_conversion_date => l_last_conversion_date,
805 p_n_version_number => l_version_number
806 );
807 END IF;
808 -- following code added to call the check exclusion rules function, Enh Bug#2124001
809 IF p_source = 'CHARGE' THEN
810 l_return_status := igs_fi_prc_balances.check_exclusion_rules
811 (p_balance_type,
812 p_balance_date,
813 'CHARGE',
814 p_source_id,
815 l_balance_rule_id,
816 l_v_message);
817 IF l_v_message IS NOT NULL THEN
818 p_message_name := l_v_message;
819 RETURN;
820 END IF;
821 ELSIF p_source = 'CREDIT' THEN
822 l_return_status := igs_fi_prc_balances.check_exclusion_rules(
823 p_balance_type,
824 p_balance_date,
825 'CREDIT',
826 p_source_id,
827 l_balance_rule_id,
828 l_v_message);
829 IF l_v_message IS NOT NULL THEN
830 p_message_name := l_v_message;
831 RETURN;
832 END IF;
833 ELSE
834 p_message_name := 'IGS_GE_INVALID_VALUE';
835 RETURN;
836 END IF;
837 END IF;
838
839 --initialises the variable l_func_ret_status
840 l_func_ret_status := TRUE;
841 /* Step 9 to insert or update into the balances table */
842 IF NOT insert_or_update_balance(l_balance_rule_id,
843 l_v_insert_upd_message ) THEN
844 -- sets the function return status to false
845 l_func_ret_status := FALSE;
846 p_message_name := l_v_insert_upd_message;
847 END IF;
848 -- invokes retro_update_balance function for retroactive updation of balances
849 -- if insert_or_update_balanc has been successfully executed.
850 -- retro_update_balance function will be invoked for retroactive updation of
851 -- all the records whose balance date is greater than the
852 -- parameter balance date
853
854 IF (l_func_ret_status) THEN
855 l_return_status := retro_update_balance
856 (
857 p_n_party_id => p_party_id,
858 /* Removed the subaccount from this procedure call, as a part of Bug # 2564643 */
859 p_c_balance_type => p_balance_type,
860 p_d_balance_date => p_balance_date,
861 p_n_amount => p_amount,
862 p_c_message => l_v_message
863 );
864 IF NOT(l_return_status) THEN
865 p_message_name := l_v_message;
866 END IF;
867 END IF;
868 END IF;
869
870 EXCEPTION
871 WHEN OTHERS THEN
872 p_message_name :='IGS_GE_UNHANDLED_EXCEPTION';
873 END update_balances ;
874 /* procedure ends here */
875 /***************The procedure update balances added as part of SFCR 10 ***************/
876
877
878
879 /*** The below check_exclusion_rules function added for the enhancement bug#2124001. ***/
880
881 FUNCTION check_exclusion_rules (
882 p_balance_type IN igs_fi_balance_rules.balance_name%TYPE ,
883 p_balance_date IN igs_fi_balance_rules.effective_start_date%TYPE,
884 p_source_type IN VARCHAR2 ,
885 p_source_id IN NUMBER ,
886 p_balance_rule_id IN igs_fi_balance_rules.balance_rule_id%TYPE,
887 p_message_name OUT NOCOPY VARCHAR2 )
888 RETURN BOOLEAN AS
889 -------------------------------------------------------------------------------
890 -- Created By : Nishikant
891 -- Date Created On : 06-12-2001
892 -- Purpose: It checks for all the Charges and Credits transactions based upon
893 -- the exclusion rules set up for a particular balance type before
894 -- arriving at the final balances.
895 -- Change History
896 -- Who When What
897 -- sapanigr 14-Feb_2006 Bug 5018036. Cursor c_credit now uses igs_fi_credits_all instead of igs_fi_credits
898 -- vvutukur 04-Oct-2002 Enh#2562745.Added a new mandatory parametre p_balance_rule_id.Removed
899 -- cursor c_balance and its usage in the code.Modified cursor c_bal_type
900 -- as 'INSTALLMENT','OTHER' balance types have been obsoleted.
901 -- smvk 17-Sep-2002 Removed the references to subaccount_id, as a part of Bug # 2564643
902 -- vvutukur 01-may-2002 Bug 2329042. Modified to return FALSE for Standard Balance Type as there
903 -- there will be no exclusion rules defined for Standard Bal. type.
904 -- smadathi 10-APR-2002 Bug 2289191. References to enabled_flag column removed from cursor
905 -- c_subacct_excl, c_ftype_excl, c_ctyp_excl select list.
906 -- (reverse chronological order - newest change first)
907 -------------------------------------------------------------------------------
908 l_fee_type igs_fi_inv_int_v.fee_type%TYPE;
909 l_credit_type_id igs_fi_credits_v.credit_type_id%TYPE;
910 l_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE := p_balance_rule_id;
911 l_std_bal igs_lookup_values.lookup_code%TYPE := 'STANDARD';
912 l_sysdate DATE := TRUNC(SYSDATE);
913 l_lkp_type igs_lookup_values.lookup_type%TYPE := 'IGS_FI_BALANCE_TYPE';
914
915 CURSOR c_bal_type(cp_balance_type igs_fi_balance_rules.balance_name%TYPE) IS
916 SELECT 'X'
917 FROM igs_lookup_values
918 WHERE lookup_type = l_lkp_type
919 AND lookup_code = cp_balance_type
920 AND lookup_code NOT IN ('INSTALLMENT','OTHER')
921 AND enabled_flag = 'Y'
922 AND l_sysdate BETWEEN NVL(TRUNC(start_date_active),l_sysdate) AND
923 NVL(TRUNC(end_date_active),l_sysdate);
924
925 --removed cursor c_balance.
926
927 CURSOR c_charge(cp_source_id igs_fi_inv_int.invoice_id%TYPE) IS
928 SELECT fee_type, invoice_creation_date
929 FROM igs_fi_inv_int_v
930 WHERE invoice_id = cp_source_id;
931
932 CURSOR c_credit(cp_source_id igs_fi_credits_all.credit_id%TYPE)IS
933 SELECT credit_type_id, effective_date
934 FROM igs_fi_credits_all
935 WHERE credit_id = cp_source_id;
936
937 /* Removed the cursor c_subacct_excl, as a part of Bug # 2564643 */
938
939 CURSOR c_ftype_excl(cp_fee_type igs_fi_fee_type.fee_type%TYPE,
940 cp_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE
941 )IS
942 SELECT 'X'
943 FROM IGS_FI_BAL_EX_F_TYPS_V
944 WHERE fee_type = cp_fee_type
945 AND balance_rule_id = cp_balance_rule_id;
946
947 CURSOR c_ctyp_excl(cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE,
948 cp_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE
949 )IS
950 SELECT 'X'
951 FROM IGS_FI_BAL_EX_C_TYPS_V
952 WHERE credit_type_id = cp_credit_type_id
953 AND balance_rule_id = cp_balance_rule_id;
954
955 l_charge c_charge%ROWTYPE;
956 l_credit c_credit%ROWTYPE;
957 l_temp VARCHAR2(1);
958
959 BEGIN
960
961 -- The below parameters are required so they cannot be NULL
962 IF (p_balance_type IS NULL OR
963 p_balance_date IS NULL OR
964 p_source_type IS NULL OR
965 p_source_id IS NULL OR
966 p_balance_rule_id IS NULL
967 )THEN
968 p_message_name := 'IGS_GE_INVALID_VALUE';
969 RETURN FALSE;
970 END IF;
971
972 IF p_balance_type = l_std_bal THEN
973 p_message_name := NULL;
974 RETURN FALSE;
975 END IF;
976
977 -- The parameter Balance Type should be either of the Lookup Codes enabled
978 -- for the Lookup Type 'IGS_FI_BALANCE_TYPE'.
979 OPEN c_bal_type(cp_balance_type => p_balance_type);
980 FETCH c_bal_type INTO l_temp;
981 IF c_bal_type%NOTFOUND THEN
982 p_message_name := 'IGS_GE_INVALID_VALUE';
983 CLOSE c_bal_type;
984 RETURN FALSE;
985 END IF;
986 CLOSE c_bal_type;
987
988 -- The parameter Source Type should be either CHARGE or CREDIT
989 IF p_source_type NOT IN ('CHARGE','CREDIT') THEN
990 p_message_name := 'IGS_GE_INVALID_VALUE';
991 RETURN FALSE;
992 END IF;
993
994 IF p_source_type = 'CHARGE' THEN
995 -- If Source Type is CHARGE then it retrieves the Fee Type, Invoice Creation Date by
996 -- matching the invoice_id with the parameter p_source_id.
997 OPEN c_charge(cp_source_id => p_source_id);
998 FETCH c_charge INTO l_charge;
999 IF c_charge%FOUND THEN
1000 -- Storing the Fee Type found from the above cursor to the local variable.
1001 l_fee_type := l_charge.fee_type;
1002 END IF;
1003 CLOSE c_charge;
1004
1005 -- Here it checks the Fee Type found above is excluded or not. If excluded it returns
1006 -- TRUE and the mentioned message.
1007 OPEN c_ftype_excl(cp_fee_type => l_fee_type,
1008 cp_balance_rule_id => p_balance_rule_id
1009 );
1010 FETCH c_ftype_excl INTO l_temp;
1011 IF c_ftype_excl%FOUND THEN
1012 CLOSE c_ftype_excl;
1013 p_message_name := 'IGS_FI_FTYP_EXCLDED';
1014 RETURN TRUE;
1015 END IF;
1016 CLOSE c_ftype_excl;
1017 ELSE
1018
1019 -- If Source Type is CREDIT then it retrieves the Credit Type ID, Effective Date
1020 -- by matching the credit_id with the parameter p_source_id. Then it checks whether
1021 -- the Credit Effective Date for the Credit Type ID is in between the Transaction Low Date
1022 -- and the Transaction High Date of the Balance Name. If it found the it returns TRUE and
1023 -- the mentioned message.
1024 OPEN c_credit(cp_source_id => p_source_id);
1025 FETCH c_credit INTO l_credit;
1026 IF c_credit%FOUND THEN
1027 -- Storing the Credit Type found from the above cursor into the local variable.
1028 l_credit_type_id := l_credit.credit_type_id;
1029 END IF;
1030 CLOSE c_credit;
1031
1032 -- Here it checks the Credit Type found above is excluded or not. If excluded it
1033 -- returns TRUE and the mentioned message.
1034 OPEN c_ctyp_excl(cp_credit_type_id => l_credit_type_id,
1035 cp_balance_rule_id => p_balance_rule_id
1036 );
1037 FETCH c_ctyp_excl INTO l_temp;
1038 IF c_ctyp_excl%FOUND THEN
1039 CLOSE c_ctyp_excl;
1040 p_message_name := 'IGS_FI_CTYP_EXCLDED';
1041 RETURN TRUE;
1042 END IF;
1043 CLOSE c_ctyp_excl;
1044 END IF;
1045
1046 /* removed the validation of subaccount_id from exclusion, as a part of Bug # 2564643 */
1047
1048 -- If nowhere found Excluded the it returns FALSE.
1049 p_message_name := NULL;
1050 RETURN FALSE;
1051
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 IF c_charge%ISOPEN THEN
1055 CLOSE c_charge;
1056 END IF;
1057 IF c_credit%ISOPEN THEN
1058 CLOSE c_credit;
1059 END IF;
1060 IF c_ftype_excl%ISOPEN THEN
1061 CLOSE c_ftype_excl;
1062 END IF;
1063 IF c_ctyp_excl%ISOPEN THEN
1064 CLOSE c_ctyp_excl;
1065 END IF;
1066 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
1067 RETURN FALSE;
1068 END check_exclusion_rules;
1069
1070
1071 FUNCTION retro_update_balance
1072 (
1073 p_n_party_id IN igs_fi_balances.party_id%TYPE ,
1074 /* Removed the parameter p_subaccount_id as a part of Bug # 2564643 */
1075 p_c_balance_type IN igs_lookups_view.lookup_code%TYPE ,
1076 p_d_balance_date IN igs_fi_balances.balance_date%TYPE ,
1077 p_n_amount IN igs_fi_inv_int.invoice_amount%TYPE ,
1078 p_c_message OUT NOCOPY fnd_new_messages.message_name%TYPE
1079 )
1080 RETURN BOOLEAN AS
1081 ------------------------------------------------------------------
1082 --Created by : Sanil Madathil, Oracle IDC
1083 --Date created: 02 Jul 2002
1084 --
1085 --Purpose: This private Function is invoked from update_balances procedure
1086 --
1087 --
1088 --Known limitations/enhancements and/or remarks:
1089 --
1090 --Change History:
1091 --Who When What
1092 --abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
1093 --vvutukur 01-Oct-2002 Enh#2562745.Removed references to balance types INSTALLMENT,OTHER from
1094 -- tbh calls(igs_fi_balances_pkg) as the same are obsolete.
1095 --smvk 17-Sep-2002 Removed the input parameter p_n_subaccount_id and its usage
1096 -- in this function. As a part of Bug # 2564643
1097 ------------------------------------------------------------------
1098 --Cursor used for retroactive updation of the table igs_fi_balances for the
1099 --party_id, subaccount_id and balance_date
1100
1101 CURSOR c_igs_fi_balances IS
1102 SELECT bal.rowid, bal.*
1103 FROM igs_fi_balances bal
1104 WHERE party_id = p_n_party_id
1105 AND TO_CHAR(balance_date,'YYYYMMDD') > TO_CHAR(p_d_balance_date,'YYYYMMDD')
1106 ORDER BY balance_date ;
1107
1108 rec_c_igs_fi_balances c_igs_fi_balances%ROWTYPE;
1109
1110 BEGIN
1111 -- for retroactive updation all the records whose balance date is greater than the
1112 -- parameter balance date will be fetched and updated with the amount
1113 FOR rec_c_igs_fi_balances IN c_igs_fi_balances
1114 LOOP
1115 IF p_c_balance_type = 'STANDARD'
1116 THEN
1117 BEGIN
1118 igs_fi_balances_pkg.update_row
1119 (
1120 X_ROWID => rec_c_igs_fi_balances.rowid ,
1121 X_BALANCE_ID => rec_c_igs_fi_balances.balance_id ,
1122 X_PARTY_ID => rec_c_igs_fi_balances.party_id ,
1123 /* Removed subaccount_id from this procedure call, as a part of Bug # 2564643 */
1124 X_STANDARD_BALANCE => igs_fi_gen_gl.get_formatted_amount(NVL(rec_c_igs_fi_balances.standard_balance,0) + NVL(p_n_amount,0)) ,
1125 X_FEE_BALANCE => rec_c_igs_fi_balances.fee_balance ,
1126 X_HOLDS_BALANCE => rec_c_igs_fi_balances.holds_balance ,
1127 X_BALANCE_DATE => rec_c_igs_fi_balances.balance_date ,
1128 X_FEE_BALANCE_RULE_ID => rec_c_igs_fi_balances.fee_balance_rule_id ,
1129 X_HOLDS_BALANCE_RULE_ID => rec_c_igs_fi_balances.holds_balance_rule_id ,
1130 X_MODE => 'R'
1131 ) ;
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 -- log the error message returned by the tbh
1135 p_c_message := FND_MESSAGE.GET;
1136 RETURN FALSE;
1137 END;
1138 ELSIF p_c_balance_type = 'FEE'
1139 THEN
1140 BEGIN
1141 igs_fi_balances_pkg.update_row
1142 (
1143 X_ROWID => rec_c_igs_fi_balances.rowid ,
1144 X_BALANCE_ID => rec_c_igs_fi_balances.balance_id ,
1145 X_PARTY_ID => rec_c_igs_fi_balances.party_id ,
1146 /* Removed subaccount_id from this procedure call, as a part of Bug # 2564643 */
1147 X_STANDARD_BALANCE => rec_c_igs_fi_balances.standard_balance ,
1148 X_FEE_BALANCE => igs_fi_gen_gl.get_formatted_amount(NVL(rec_c_igs_fi_balances.fee_balance,0) + NVL(p_n_amount,0) ) ,
1149 X_HOLDS_BALANCE => rec_c_igs_fi_balances.holds_balance ,
1150 X_BALANCE_DATE => rec_c_igs_fi_balances.balance_date ,
1151 X_FEE_BALANCE_RULE_ID => rec_c_igs_fi_balances.fee_balance_rule_id ,
1152 X_HOLDS_BALANCE_RULE_ID => rec_c_igs_fi_balances.holds_balance_rule_id ,
1153 X_MODE => 'R'
1154 ) ;
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 -- log the error message returned by the tbh
1158 p_c_message := FND_MESSAGE.GET;
1159 RETURN FALSE;
1160 END;
1161 ELSIF p_c_balance_type = 'HOLDS'
1162 THEN
1163 BEGIN
1164 igs_fi_balances_pkg.update_row
1165 (
1166 X_ROWID => rec_c_igs_fi_balances.rowid ,
1167 X_BALANCE_ID => rec_c_igs_fi_balances.balance_id ,
1168 X_PARTY_ID => rec_c_igs_fi_balances.party_id ,
1169 /* Removed subaccount_id from this procedure call, as a part of Bug # 2564643 */
1170 X_STANDARD_BALANCE => rec_c_igs_fi_balances.standard_balance ,
1171 X_FEE_BALANCE => rec_c_igs_fi_balances.fee_balance ,
1172 X_HOLDS_BALANCE => igs_fi_gen_gl.get_formatted_amount( NVL(rec_c_igs_fi_balances.holds_balance,0) + NVL(p_n_amount,0) ) ,
1173 X_BALANCE_DATE => rec_c_igs_fi_balances.balance_date ,
1174 X_FEE_BALANCE_RULE_ID => rec_c_igs_fi_balances.fee_balance_rule_id ,
1175 X_HOLDS_BALANCE_RULE_ID => rec_c_igs_fi_balances.holds_balance_rule_id ,
1176 X_MODE => 'R'
1177 ) ;
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 -- log the error message returned by the tbh
1181 p_c_message := FND_MESSAGE.GET;
1182 RETURN FALSE;
1183 END;
1184 END IF;
1185 END LOOP;
1186 RETURN TRUE;
1187 END retro_update_balance;
1188
1189
1190 /****** Following 3 procedures finpl_upd_conv_prc_run_ind(), convert_holds_balances() and conv_balances() added
1191 as part of Reassess Balances Build FI102, Bug 2562745 ******/
1192
1193
1194 PROCEDURE finpl_upd_conv_prc_run_ind ( p_n_value IN NUMBER)
1195 IS
1196 PRAGMA AUTONOMOUS_TRANSACTION;
1197 ------------------------------------------------------------------
1198 --Created by : Priya Athipatla, Oracle IDC
1199 --Date created: 08-OCT-2002
1200 --
1201 --Purpose: Private procedure to update value of conv_process_run_ind
1202 -- in the igs_fi_control_all table to 0 or 1 when the holds process is
1203 -- not-running/running anymore.
1204 --
1205 --Known limitations/enhancements and/or remarks:
1206 --
1207 --Change History:
1208 --Who When What
1209 --svuppala 14-JUL-2005 Enh 3392095 - impact of Tution Waivers build
1210 -- Modified igs_fi_control_pkg.update_row by adding two new columns
1211 -- post_waiver_gl_flag, waiver_notify_finaid_flag
1212 --pmarada 19-Nov-2004 Bug 4017841, Removed the obsoleted res_dt_alias column reference from igs_fi_control table update row
1213 --uudayapr 23-dec-2003 ENH3167098 Modified igs_fi_control_pkg.update_row by changing the Column Name PRG_CHG_DT_ALAIS
1214 -- to RES_DT_ALAIS
1215 --jbegum 14-june-2003 Bug#2998266 Removed the column next_invoice_number from call to igs_fi_control_pkg.update_row
1216 --shtatiko 27-MAY-2003 Enh# 2831582, Removed columns lockbox_context, lockbox_number_attribute and ar_int_org_id from
1217 -- call igs_fi_control_pkg.update_row.
1218 --vvutukur 16-May-2003 Enh#2831572.financial Accounting Build. Modified TBH call to add parameter acct_conv_flag.
1219 --pathipat 14-Apr-2003 Enh 2831569 - Commercial Receivables Interface
1220 -- Modified call to igs_fi_control_pkg.update_row
1221 --smadathi 18-Feb-2002 Enh. Bug 2747329.Modified the TBH call to IGS_FI_CONTROL to Add new columns
1222 -- rfnd_destination, ap_org_id, dflt_supplier_site_name
1223 --vvutukur 11-Dec-2002 Enh#2584741.Added currency_cd parameter to the tbh call of igs_fi_control_pkg.update_row.
1224 ------------------------------------------------------------------
1225
1226 CURSOR c_get_data IS
1227 SELECT fc.rowid, fc.*
1228 FROM igs_fi_control_all fc;
1229
1230 l_rec_get_data c_get_data%ROWTYPE;
1231
1232 BEGIN
1233
1234 OPEN c_get_data;
1235 FETCH c_get_data INTO l_rec_get_data;
1236 IF c_get_data%FOUND THEN
1237 igs_fi_control_pkg.update_row (
1238 x_rowid => l_rec_get_data.rowid,
1239 x_rec_installed => l_rec_get_data.rec_installed,
1240 x_mode => 'R',
1241 x_accounting_method => l_rec_get_data.accounting_method,
1242 x_set_of_books_id => l_rec_get_data.set_of_books_id,
1243 x_refund_dr_gl_ccid => l_rec_get_data.refund_dr_gl_ccid,
1244 x_refund_cr_gl_ccid => l_rec_get_data.refund_cr_gl_ccid,
1245 x_refund_dr_account_cd => l_rec_get_data.refund_dr_account_cd,
1246 x_refund_cr_account_cd => l_rec_get_data.refund_cr_account_cd,
1247 x_refund_dt_alias => l_rec_get_data.refund_dt_alias,
1248 x_fee_calc_mthd_code => l_rec_get_data.fee_calc_mthd_code,
1249 x_planned_credits_ind => l_rec_get_data.planned_credits_ind,
1250 x_rec_gl_ccid => l_rec_get_data.rec_gl_ccid,
1251 x_cash_gl_ccid => l_rec_get_data.cash_gl_ccid,
1252 x_unapp_gl_ccid => l_rec_get_data.unapp_gl_ccid,
1253 x_rec_account_cd => l_rec_get_data.rec_account_cd,
1254 x_rev_account_cd => l_rec_get_data.rev_account_cd,
1255 x_cash_account_cd => l_rec_get_data.cash_account_cd,
1256 x_unapp_account_cd => l_rec_get_data.unapp_account_cd,
1257 x_conv_process_run_ind => p_n_value,
1258 x_currency_cd => l_rec_get_data.currency_cd,
1259 x_rfnd_destination => l_rec_get_data.rfnd_destination,
1260 x_ap_org_id => l_rec_get_data.ap_org_id,
1261 x_dflt_supplier_site_name => l_rec_get_data.dflt_supplier_site_name,
1262 x_manage_accounts => l_rec_get_data.manage_accounts,
1263 x_acct_conv_flag => l_rec_get_data.acct_conv_flag,
1264 x_post_waiver_gl_flag => l_rec_get_data.post_waiver_gl_flag,
1265 x_waiver_notify_finaid_flag => l_rec_get_data.waiver_notify_finaid_flag
1266 );
1267 COMMIT;
1268 CLOSE c_get_data;
1269 ELSE
1270 fnd_message.set_name('IGS','IGS_FI_SYSTEM_OPT_SETUP');
1271 fnd_file.put_line(fnd_file.log,fnd_message.get());
1272 fnd_file.put_line(FND_FILE.LOG,' ');
1273 app_exception.raise_exception;
1274 END IF;
1275
1276 END finpl_upd_conv_prc_run_ind;
1277
1278
1279 PROCEDURE convert_holds_balances( p_conv_st_date IN igs_fi_balance_rules.last_conversion_date%TYPE ) AS
1280 ------------------------------------------------------------------
1281 --Created by : Priya Athipatla, Oracle IDC
1282 --Date created: 08-OCT-2002
1283 --
1284 --Purpose: Public procedure invoked by conv_balances --> holds conversion concurrent program
1285 --
1286 --
1287 --Known limitations/enhancements and/or remarks:
1288 --
1289 --Change History:
1290 --Who When What
1291 --abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
1292 --shtatiko 15-JAN-2003 Bug# 2736389, Introduced l_validation_exp to handle validation failure
1293 -- cases separately. Because in these cases if we raise exception using
1294 -- app_exception.raise_exception then message "Process raised unhandled
1295 -- exception" is logged along with validation failure error message.
1296 --pathipat 07-Jan-2003 Bug: 2672837 - Log file format modified to be multiline instead
1297 -- of in tabular format. Used generic function to obtain the lookup
1298 -- description instead of local func lookup_Desc
1299 ------------------------------------------------------------------
1300
1301 -- to obtain the process start date for conversion
1302 CURSOR c_get_process_dt IS
1303 SELECT a.process_start_dt
1304 FROM igs_fi_person_holds a,
1305 igs_pe_pers_encumb b,
1306 igs_fi_hold_plan c
1307 WHERE a.hold_plan_name = c.hold_plan_name
1308 AND c.hold_plan_level = 'S'
1309 AND a.person_id = b.person_id
1310 AND a.hold_start_dt = b.start_dt
1311 AND a.hold_type = b.encumbrance_type
1312 AND (b.expiry_dt IS NULL OR TRUNC(b.expiry_dt) >= TRUNC(SYSDATE))
1313 ORDER BY 1 ;
1314
1315 -- to obtain all the records in the balances table for conversion
1316 CURSOR c_get_balances IS
1317 SELECT fb.rowid, fb.*
1318 FROM igs_fi_balances fb
1319 WHERE TRUNC(balance_date) >= TRUNC(p_conv_st_date)
1320 ORDER BY party_id, balance_date ;
1321
1322 -- to obtain the rowid for the record that is to be updated after the conversion process is successful
1323 CURSOR c_rule_update(cp_balance_rule_id IN igs_fi_balance_rules.balance_rule_id%TYPE) IS
1324 SELECT rowid
1325 FROM igs_fi_balance_rules
1326 WHERE balance_rule_id = cp_balance_rule_id;
1327
1328 l_rec_get_balances c_get_balances%ROWTYPE;
1329 l_process_start_dt igs_fi_person_holds.process_start_dt%TYPE;
1330 l_conv_process_run_ind igs_fi_control_all.conv_process_run_ind%TYPE;
1331 l_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE;
1332 l_version_number igs_fi_balance_rules.version_number%TYPE;
1333 l_last_conv_dt igs_fi_balance_rules.last_conversion_date%TYPE;
1334 l_balance_amt igs_fi_balances.holds_balance%TYPE := 0;
1335 l_balance_sum igs_fi_balances.holds_balance%TYPE := 0;
1336 l_party_id igs_fi_balances.party_id%TYPE := NULL;
1337 l_message_name fnd_new_messages.message_name%TYPE := NULL;
1338 l_message_name_1 fnd_new_messages.message_name%TYPE := NULL;
1339 l_msg_str_0 VARCHAR2(1000) := NULL;
1340 l_msg_str_1 VARCHAR2(1000) := NULL;
1341 l_rowid VARCHAR2(25);
1342 l_person_number hz_parties.party_number%TYPE;
1343 l_user_exception EXCEPTION;
1344 l_exception BOOLEAN;
1345
1346 BEGIN
1347
1348 -- if conversion start date is not given, then error out
1349 IF p_conv_st_date IS NULL THEN
1350 fnd_message.set_name('IGS','IGS_GE_INSUFFICIENT_PARAMETER');
1351 fnd_file.put_line(fnd_file.log,fnd_message.get());
1352 fnd_file.put_line(FND_FILE.LOG,' ');
1353 RAISE l_validation_exp;
1354 END IF;
1355
1356 -- conversion start date should not be greater than sysdate
1357 IF TRUNC(p_conv_st_date) > TRUNC(SYSDATE) THEN
1358 fnd_message.set_name('IGS','IGS_FI_CONV_GT_SYSDT');
1359 fnd_file.put_line(fnd_file.log,fnd_message.get());
1360 fnd_file.put_line(FND_FILE.LOG,' ');
1361 RAISE l_validation_exp;
1362 END IF;
1363
1364 -- Check if the holds conversion process is not already running
1365 IGS_FI_GEN_007.finp_get_conv_prc_run_ind( p_n_conv_process_run_ind => l_conv_process_run_ind,
1366 p_v_message_name => l_message_name_1) ;
1367
1368 IF l_message_name_1 IS NOT NULL THEN
1369 fnd_message.set_name('IGS',l_message_name_1);
1370 fnd_file.put_line(fnd_file.log,fnd_message.get());
1371 fnd_file.put_line(FND_FILE.LOG,' ');
1372 RAISE l_validation_exp;
1373 END IF;
1374
1375 -- indicator = 1 if the process is already running
1376 IF l_conv_process_run_ind = 1 THEN
1377 fnd_message.set_name('IGS','IGS_FI_REASS_BAL_PRC_RUN');
1378 fnd_file.put_line(fnd_file.log,fnd_message.get());
1379 fnd_file.put_line(FND_FILE.LOG,' ');
1380 RAISE l_validation_exp;
1381 -- if indicator <> 1, then make it 1 to show that the process will be run now
1382 ELSIF l_conv_process_run_ind = 0 THEN
1383 finpl_upd_conv_prc_run_ind(1);
1384 END IF;
1385
1386 -- Get the balance_rule_id, last_conversion_date and the version number
1387 IGS_FI_GEN_007.finp_get_balance_rule(p_v_balance_type => 'HOLDS',
1388 p_v_action => 'MAX',
1389 p_n_balance_rule_id => l_balance_rule_id,
1390 p_d_last_conversion_date => l_last_conv_dt,
1391 p_n_version_number => l_version_number);
1392 -- 1
1393 IF l_version_number = 0 THEN
1394 -- means no balance rule has been defined, so error out
1395 fnd_message.set_name('IGS','IGS_FI_NO_BAL_CONV');
1396 fnd_file.put_line(fnd_file.log,fnd_message.get());
1397 fnd_file.put_line(FND_FILE.LOG,' ');
1398
1399 finpl_upd_conv_prc_run_ind(0); -- to update the run indicator back to 0 before erroring out
1400 RAISE l_validation_exp;
1401 -- 1
1402 END IF;
1403
1404 -- 2
1405 IF ( (l_last_conv_dt IS NOT NULL) AND (TRUNC(p_conv_st_date) > TRUNC(l_last_conv_dt)) ) THEN
1406
1407 fnd_message.set_name('IGS','IGS_FI_PD_LE_INP_DT');
1408 fnd_message.set_token('DATE1',l_last_conv_dt);
1409 fnd_message.set_token('DATE2',p_conv_st_date);
1410 fnd_file.put_line(fnd_file.log,fnd_message.get());
1411 fnd_file.put_line(FND_FILE.LOG,' ');
1412
1413 -- update the run indicator back to 0 before erroring out
1414 finpl_upd_conv_prc_run_ind(0);
1415 RAISE l_validation_exp;
1416 -- 2
1417 END IF;
1418
1419 OPEN c_get_process_dt;
1420 FETCH c_get_process_dt INTO l_process_start_dt; -- l_process_start_dt now holds the earliest process start date
1421 CLOSE c_get_process_dt;
1422 -- 3
1423 IF ( (l_process_start_dt IS NOT NULL) AND (TRUNC(l_process_start_dt) < TRUNC(p_conv_st_date)) ) THEN
1424 -- Check if earliest process start date is earlier than p_conv_st_date
1425 fnd_message.set_name('IGS','IGS_FI_EPSD_LE_PRC_DT');
1426 fnd_message.set_token('DATE1',p_conv_st_date);
1427 fnd_message.set_token('DATE2',l_process_start_dt);
1428 fnd_file.put_line(fnd_file.log,fnd_message.get());
1429 fnd_file.put_line(FND_FILE.LOG,' ');
1430
1431 -- update the run indicator back to 0 before erroring out
1432 finpl_upd_conv_prc_run_ind(0);
1433 RAISE l_validation_exp;
1434 -- 3
1435 END IF;
1436
1437 SAVEPOINT A;
1438
1439 FOR l_rec_get_balances IN c_get_balances
1440 LOOP
1441 BEGIN
1442
1443 -- 4 For same party id, get the cumulative balance amount. First party id, calculate balances with action as
1444 -- ASONBALDATE. For consecutive same party id records, calculate based on FORBALDATE.
1445
1446 IF NVL(l_party_id,-99) <> l_rec_get_balances.party_id THEN
1447 -- if l_party_id is null, then initialize the first record of that party id to l_party_id
1448 l_party_id := l_rec_get_balances.party_id;
1449 l_balance_sum := 0; -- cumulative sum is set to zero
1450 l_balance_amt := 0;
1451 l_exception := FALSE;
1452
1453 SAVEPOINT B;
1454
1455 IGS_FI_PRC_BALANCES.calculate_balance( p_person_id => l_rec_get_balances.party_id,
1456 p_balance_type => 'HOLDS',
1457 p_balance_date => l_rec_get_balances.balance_date,
1458 p_action => 'ASONBALDATE',
1459 p_balance_rule_id => l_balance_rule_id,
1460 p_balance_amount => l_balance_amt, -- OUT parameter
1461 p_message_name => l_message_name -- OUT parameter
1462 );
1463 IF l_message_name IS NOT NULL THEN
1464 RAISE l_user_exception;
1465 END IF;
1466
1467 l_balance_sum := NVL(l_balance_amt,0) + NVL(l_balance_sum,0);
1468 l_balance_amt := 0;
1469
1470 -- Update the log file
1471 -- (pathipat) Log file format changed from tabular format to multiline format
1472 -- Used generic function to get the description, and not the local func lookup_Desc
1473 -- Bug: 2672837
1474 OPEN cur_person_number(l_rec_get_balances.party_id);
1475 FETCH cur_person_number INTO l_person_number;
1476
1477 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
1478 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
1479 p_v_lookup_code => 'PERSON')
1480 );
1481 fnd_message.set_token('PARM_CODE', l_person_number);
1482 fnd_file.put_line(fnd_file.log, fnd_message.get);
1483
1484 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
1485 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
1486 p_v_lookup_code => 'BALANCE_DATE')
1487 );
1488 fnd_message.set_token('PARM_CODE', l_rec_get_balances.balance_date);
1489 fnd_file.put_line(fnd_file.log, fnd_message.get);
1490
1491 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
1492 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
1493 p_v_lookup_code => 'BALANCE_RULE_VERSION')
1494 );
1495 fnd_message.set_token('PARM_CODE', l_version_number);
1496 fnd_file.put_line(fnd_file.log, fnd_message.get);
1497
1498 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
1499 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
1500 p_v_lookup_code => 'BALANCE_AMOUNT')
1501 );
1502 fnd_message.set_token('PARM_CODE',igs_fi_gen_gl.get_formatted_amount(l_balance_sum));
1503 fnd_file.put_line(fnd_file.log,fnd_message.get);
1504 fnd_file.new_line(fnd_file.log);
1505
1506 CLOSE cur_person_number;
1507
1508 -- 4
1509 ELSIF NVL(l_party_id,-99) = l_rec_get_balances.party_id AND NOT (l_exception) THEN --4
1510
1511 IGS_FI_PRC_BALANCES.calculate_balance( p_person_id => l_rec_get_balances.party_id,
1512 p_balance_type => 'HOLDS',
1513 p_balance_date => l_rec_get_balances.balance_date,
1514 p_action => 'FORBALDATE',
1515 p_balance_rule_id => l_balance_rule_id,
1516 p_balance_amount => l_balance_amt,
1517 p_message_name => l_message_name
1518 );
1519 IF l_message_name IS NOT NULL THEN
1520 RAISE l_user_exception;
1521 END IF;
1522
1523 l_balance_sum := NVL(l_balance_amt,0) + NVL(l_balance_sum,0); -- cumulative balance amount for each party id
1524 l_balance_amt := 0;
1525
1526 l_balance_sum := igs_fi_gen_gl.get_formatted_amount(l_balance_sum);
1527 -- Update the cumulative balance amount in the fi_balances table under holds_balances
1528 -- and the balance_rule_id under holds_balance_rule_id
1529 IGS_FI_BALANCES_PKG.update_row ( x_rowid => l_rec_get_balances.rowid,
1530 x_balance_id => l_rec_get_balances.balance_id,
1531 x_party_id => l_rec_get_balances.party_id,
1532 x_standard_balance => l_rec_get_balances.standard_balance,
1533 x_fee_balance => l_rec_get_balances.fee_balance,
1534 x_holds_balance => l_balance_sum,
1535 x_balance_date => l_rec_get_balances.balance_date,
1536 x_fee_balance_rule_id => l_rec_get_balances.fee_balance_rule_id,
1537 x_holds_balance_rule_id => l_balance_rule_id,
1538 x_mode => 'R'
1539 );
1540
1541 -- Update the log file
1542 -- (pathipat) Log file format changed from tabular format to multiline format
1543 -- Used generic function to get the description, and not the local func lookup_Desc
1544 -- Bug: 2672837
1545 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
1546 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
1547 p_v_lookup_code => 'PERSON')
1548 );
1549 fnd_message.set_token('PARM_CODE', l_person_number);
1550 fnd_file.put_line(fnd_file.log, fnd_message.get);
1551
1552 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
1553 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
1554 p_v_lookup_code => 'BALANCE_DATE')
1555 );
1556 fnd_message.set_token('PARM_CODE', l_rec_get_balances.balance_date);
1557 fnd_file.put_line(fnd_file.log, fnd_message.get);
1558
1559 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
1560 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
1561 p_v_lookup_code => 'BALANCE_RULE_VERSION')
1562 );
1563 fnd_message.set_token('PARM_CODE', l_version_number);
1564 fnd_file.put_line(fnd_file.log, fnd_message.get);
1565
1566 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
1567 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
1568 p_v_lookup_code => 'BALANCE_AMOUNT')
1569 );
1570 fnd_message.set_token('PARM_CODE',igs_fi_gen_gl.get_formatted_amount(l_balance_sum));
1571 fnd_file.put_line(fnd_file.log,fnd_message.get);
1572 fnd_file.new_line(fnd_file.log);
1573
1574 -- 4
1575 END IF;
1576
1577 EXCEPTION
1578 WHEN l_user_exception THEN
1579 l_exception := TRUE;
1580 finpl_upd_conv_prc_run_ind(0);
1581 ROLLBACK TO SAVEPOINT B;
1582 END;
1583 END LOOP;
1584
1585
1586 BEGIN
1587 -- update the rules table with the version number and rule_id and set the last_conversion_date
1588 -- to be p_conv_st_date if the holds calculation process above completed without any errors
1589 OPEN c_rule_update(l_balance_rule_id);
1590 FETCH c_rule_update INTO l_rowid;
1591 CLOSE c_rule_update;
1592 IGS_FI_BALANCE_RULES_PKG.update_row ( x_rowid => l_rowid,
1593 x_balance_rule_id => l_balance_rule_id,
1594 x_balance_name => 'HOLDS',
1595 x_version_number => l_version_number,
1596 x_last_conversion_date => p_conv_st_date,
1597 x_mode => 'R'
1598 );
1599
1600 EXCEPTION
1601 WHEN OTHERS THEN
1602 finpl_upd_conv_prc_run_ind(0);
1603 fnd_file.put_line(fnd_file.log,fnd_message.get());
1604 fnd_file.put_line(FND_FILE.LOG,' ');
1605 ROLLBACK TO SAVEPOINT A;
1606 END;
1607
1608 finpl_upd_conv_prc_run_ind(0);
1609
1610 EXCEPTION
1611
1612 WHEN OTHERS THEN
1613 IF c_get_process_dt%ISOPEN THEN
1614 CLOSE c_get_process_dt;
1615 END IF;
1616 IF c_get_balances%ISOPEN THEN
1617 CLOSE c_get_balances;
1618 END IF;
1619 IF c_rule_update%ISOPEN THEN
1620 CLOSE c_rule_update;
1621 END IF;
1622 IF cur_person_number%ISOPEN THEN
1623 CLOSE cur_person_number;
1624 END IF;
1625 ROLLBACK;
1626 finpl_upd_conv_prc_run_ind(0) ;
1627 RAISE;
1628
1629 END convert_holds_balances;
1630
1631
1632
1633 PROCEDURE conv_balances ( errbuf OUT NOCOPY VARCHAR2,
1634 retcode OUT NOCOPY NUMBER,
1635 p_conv_st_date IN VARCHAR2) AS
1636 ------------------------------------------------------------------
1637 --Created by : Priya Athipatla, Oracle IDC
1638 --Date created: 08-OCT-2002
1639 --
1640 --Purpose: Wrapper procedure for convert_holds_balances(), registered
1641 -- as a concurrent manager job executable.
1642 --
1643 --
1644 --Known limitations/enhancements and/or remarks:
1645 --
1646 --Change History:
1647 --Who When What
1648 --sapanigr 05-May-2006 Bug 5178077: Added call to igs_ge_gen_003.set_org_id. to disable process in R12
1649 --pathipat 23-Apr-2003 Enh 2831569 - Commercial Receivables build - Added call to
1650 -- igs_fi_com_rec_interface.chk_manage_account()
1651 ------------------------------------------------------------------
1652
1653 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
1654 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
1655 l_org_id VARCHAR2(15);
1656
1657 BEGIN
1658
1659 BEGIN
1660 l_org_id := NULL;
1661 igs_ge_gen_003.set_org_id(l_org_id);
1662 EXCEPTION
1663 WHEN OTHERS THEN
1664 fnd_file.put_line (fnd_file.log, fnd_message.get);
1665 RETCODE:=2;
1666 RETURN;
1667 END;
1668
1669 -- Obtain the value of manage_accounts in the System Options form
1670 -- If it is null or 'OTHER', then this process is not available, so error out.
1671 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
1672 p_v_message_name => l_v_message_name
1673 );
1674 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
1675 fnd_message.set_name('IGS',l_v_message_name);
1676 fnd_file.put_line(fnd_file.log,fnd_message.get());
1677 fnd_file.put_line(FND_FILE.LOG,' ');
1678 RAISE l_validation_exp;
1679 END IF;
1680
1681 -- call the main holds conversion procedure
1682 convert_holds_balances(TRUNC(igs_ge_date.igsdate(p_conv_st_date)));
1683
1684 EXCEPTION
1685 WHEN l_validation_exp THEN
1686 ROLLBACK;
1687 retcode := 2;
1688 WHEN OTHERS THEN
1689 ROLLBACK;
1690 retcode := 2;
1691 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ': ' || SQLERRM;
1692 igs_ge_msg_stack.add;
1693 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1694 END conv_balances;
1695
1696 END IGS_FI_PRC_BALANCES;
[Home] [Help]