[Home] [Help]
PACKAGE BODY: APPS.ARP_APP_CALC_PKG
Source
1 package body ARP_APP_CALC_PKG as
2 /* $Header: ARAPPRUB.pls 120.10 2010/05/29 06:50:36 rvelidi ship $ */
3
4 -- PL/SQL tables will hold the values of the rules already in memory
5 TYPE rule_start_tab_typ IS TABLE of number INDEX BY BINARY_INTEGER;
6 TYPE rule_end_tab_typ IS TABLE of number INDEX BY BINARY_INTEGER;
7 TYPE rule_set_id_tab_typ IS TABLE of number INDEX BY BINARY_INTEGER;
8 rule_start_tab rule_start_tab_typ;
9 rule_end_tab rule_end_tab_typ;
10 rule_set_id_tab rule_set_id_tab_typ;
11 j binary_integer:=0; -- Number of rule-sets cached in a session
12
13 subtype varchar2s is dbms_sql.varchar2s;
14 g_rule_source varchar2s; -- Rule set cache (table)
15 g_rule_start number; -- Starting row-index of a rule-set in cache
16 g_rule_end number; -- Ending row-index of a rule-set in the cache
17
18 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
19
20 /*===========================================================================+
21 | FUNCTION |
22 | GET_RULE_SET_ID |
23 | DESCRIPTION |
24 | Given the cust_trx_type_id from the payment schedule this function will |
25 | return the rule_set_id to be used for this transaction |
26 | |
27 | SCOPE |
28 | -- Public |
29 | |
30 | PARAMETERS |
31 | IN - cust_trx_type_id - this is the transaction type of the item |
32 | associated with the payment schedule |
33 | OUT NOCOPY This function will return the rule_set_id to be used while |
34 | calling the calc_applied_and_remaining procedure |
35 | MODIFICATION HISTORY |
36 | 06-25-97 Joan Zaman -- Created |
37 +===========================================================================*/
38
39
40 function get_rule_set_id ( p_trx_type_id in number ) return number is
41
42 l_rule_set_id number;
43
44 begin
45
46 arp_util.debug('ARP_APP_CALC_PKG: GET_RULE_SET_ID()+');
47 select nvl(rule_set_id,nvl(arp_standard.sysparm.rule_set_id,-1))
48 into l_rule_set_id
49 from ra_cust_trx_types
50 where cust_trx_type_id = p_trx_type_id ;
51
52 if (l_rule_set_id = -1) then
53 fnd_message.set_name('AR','AR_NO_RULE_DEFINED');
54 app_exception.raise_exception;
55 end if;
56
57 arp_util.debug('ARP_APP_CALC_PKG: GET_RULE_SET_ID()- Rule Set Id = '||to_char(l_rule_set_id));
58
59 return(l_rule_set_id);
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 arp_util.debug('EXCEPTION: ARP_APP_CALC_PKG.GET_RULE_SET_ID() - OTHERS'||SQLERRM);
64 RAISE;
65 end;
66
67 /*===========================================================================+
68 | PROCEDURE |
69 | SET_RULE_SET |
70 | DESCRIPTION |
71 | This procedure will given a rule_set_id check whether the rule is |
72 | already loaded in the pl/sql table and or will load the rule in the |
73 | table when it is not already loaded. |
74 | |
75 | Note that g_rule_source,g_rule_start and g_rule_end are global variables|
76 | for the package. |
77 | |
78 | SCOPE |
79 | -- Private |
80 | |
81 | PARAMETERS |
82 | IN - rule_set_id |
83 | OUT NOCOPY This function will return the start and end of the pl/sql table |
84 | that holds the respective rule |
85 | MODIFICATION HISTORY |
86 | 06-25-97 Joan Zaman -- Created |
87 | 11-20-97 Govind Jayanth Bug fix 583787 - Grep 583787 for details. |
88 +===========================================================================*/
89
90 procedure set_rule_set ( p_rule_set_id in number ) is
91
92 l_rule_source_lng long;
93 l_rule_chunk number;
94 l_in_rule_start number;
95 l_test_source long;
96
97 begin
98 -- First Set global variables back to 0
99 g_rule_start := 0;
100 g_rule_end :=0;
101
102 for rules_in_cache in 1..j loop
103 if p_rule_set_id = rule_set_id_tab(rules_in_cache) then
104 g_rule_start := rule_start_tab(rules_in_cache);
105 g_rule_end := rule_end_tab(rules_in_cache);
106 arp_util.debug('ARP_APP_CALC_PKG.set_rule_set - Rules cached = '|| to_char(j));
107 arp_util.debug('ARP_APP_CALC_PKG.set_rule_set - Current rule-set-id = '|| to_char(p_rule_set_id));
108 arp_util.debug('ARP_APP_CALC_PKG.set_rule_set - Cached at index = '|| to_char(rules_in_cache));
109 arp_util.debug('ARP_APP_CALC_PKG.set_rule_set - Rule Start = '|| to_char(g_rule_start));
110 arp_util.debug('ARP_APP_CALC_PKG.set_rule_set - Rule End = '|| to_char(g_rule_end));
111 end if;
112 end loop;
113
114 if ((g_rule_start = 0) and ( g_rule_end = 0 )) then
115
116 -- No rule found in cache so we will load the next rule into memory
117
118 select rule_source
119 into l_rule_source_lng
120 from ar_app_rule_sets
121 where rule_set_id = p_rule_set_id ;
122
123 l_in_rule_start := 1;
124 -- The rule chunk defines the size of the pieces that we are taken out NOCOPY of
125 -- the rule source. It is set to 150 but can be changed. The real size is
126 -- defined in the dbms_sys_dbms_sql package but i could not find any
127 -- documentation on it. I tried 150 and it seemed to work fine.
128 -- Everything smaller than 150 will also work fine.
129
130 l_rule_chunk := 150 ;
131 --
132 -- Bug 583787: ON-INSERT error while applying a CM to multiple invoices with different rule-sets.
133 -- Make sure that each rule that comes into cache starts at the end of the previous.
134 --
135 if (j = 0) then
136 g_rule_start := j+1; -- See note below. Tables starting at index 1
137 else
138 g_rule_start := rule_end_tab(j)+1;
139 end if;
140 g_rule_end := nvl(g_rule_start - 1
141 + CEIL( ( length (l_rule_source_lng) / l_rule_chunk ) ),1);
142
143 arp_util.debug('ARP_APP_CALC_PKG: Rule Start = ' || to_char(g_rule_start));
144 arp_util.debug('ARP_APP_CALC_PKG: Rule End = ' || to_char(g_rule_end)) ;
145 arp_util.debug('ARP_APP_CALC_PKG: Rule length = ' || to_char(length(l_rule_source_lng))) ;
146 arp_util.debug('ARP_APP_CALC_PKG: l_in_rule_start = ' || to_char(l_in_rule_start));
147 arp_util.debug('ARP_APP_CALC_PKG: l_rule_chunk = ' || to_char(l_rule_chunk));
148 arp_util.debug('ARP_APP_CALC_PKG: rule_set_id = ' || to_char(p_rule_set_id));
149
150 for i in g_rule_start..g_rule_end loop
151 g_rule_source(i) := substr(l_rule_source_lng,l_in_rule_start,l_rule_chunk);
152 l_in_rule_start := l_in_rule_start + l_rule_chunk ;
153 end loop;
154
155
156 -- Updating the rules in cache table.
157 -- A tricky stuff here: For the 1st rule coming into cache, j happens to be zero
158 -- but tables below are starting at index 1. Hence the increment to j each time.
159 --
160 j := j + 1;
161 rule_start_tab(j) := g_rule_start;
162 rule_end_tab(j) := g_rule_end;
163 rule_set_id_tab(j) := p_rule_set_id;
164
165 for i in g_rule_start..g_rule_end loop
166 l_test_source := g_rule_source(i) ;
167
168 -- bug 2389772 : turn off display of source code
169 -- arp_util.debug(l_test_source);
170 end loop;
171
172 end if;
173 EXCEPTION
174 WHEN OTHERS THEN
175 arp_util.debug('EXCEPTION: ARP_APP_CALC_PKG.SET_RULE_SET()'||SQLERRM);
176 RAISE;
177 end;
178
179
180
181 /*===========================================================================+
182 | PROCEDURE |
183 | EXTRACT_TAXES |
184 | DESCRIPTION |
185 | Given the Gross Amounts this procedure will calculate the net amount and|
186 | the tax amount given the tax treatment |
187 | The tax Treatment can be : PRORATE , BEFORE , AFTER , NONE |
188 | |
189 | PRORATE will use the line and tax remaining amounts to prorate amounts|
190 | the formula used : |
191 | tax_applied = tax_remaining * amt / total_remaining |
192 | BEFORE will first try to substract the amt from the tax_remaining |
193 | AFTER will first try to substract the amt from the line_remaining |
194 | NONE will just return the gross amount and make tax_applied = 0 |
195 | |
196 | SCOPE |
197 | -- Private |
198 | |
199 | PARAMETERS |
200 | IN - amt -- This is the gross amount to be split up |
201 | tax_treatment -- One of the above , related to TAX |
202 | o_tax_treatment -- Tax Treatment for overapplications |
203 | currency -- Currency of the amount used for rounding |
204 | line_remaining -- The original Line Remaining amount |
205 | tax_remaining -- The original Tax Reamaining amount |
206 | OUT NOCOPY
207 | line_applied -- The taxable amount calculated using the orig |
208 | line_remaining and tax_remaining amounts. |
209 | MODIFICATION HISTORY |
210 | 03-11-97 Joan Zaman -- Created |
211 | 12-05-97 Govind J Prevent zero_divide error by checking if |
212 | tax_remaining+line_remaining = 0 |
213 +===========================================================================*/
214
215 procedure extract_taxes ( amt in number
216 ,tax_treatment in varchar2
217 ,o_tax_treatment in varchar2
218 ,currency in varchar2
219 ,line_remaining in number
220 ,tax_remaining in number
221 ,line_applied out NOCOPY number
222 ,tax_applied out NOCOPY number ) is
223
224 l_tax_applied number:=0;
225
226 begin
227
228 IF PG_DEBUG in ('Y', 'C') THEN
229 arp_standard.debug('ARP_APP_CALC_PKG.extract_taxes()+');
230 END IF;
231
232 if amt < (line_remaining + tax_remaining) then
233
234 IF PG_DEBUG in ('Y', 'C') THEN
235 arp_standard.debug('extract_taxes: ' || 'partial');
236 END IF;
237
238 if tax_treatment = 'PRORATE' then
239 if ((tax_remaining+line_remaining) <> 0) then
240 l_tax_applied := arpcurr.currround((tax_remaining*amt/(tax_remaining+line_remaining)),currency);
241 end if;
242 line_applied := amt - l_tax_applied;
243 IF PG_DEBUG in ('Y', 'C') THEN
244 arp_standard.debug('extract_taxes: ' || '.. PRORATE : line_applied = ' || to_char(line_applied));
245 arp_standard.debug('extract_taxes: ' || ' l_tax_applied = ' || to_char(l_tax_applied));
246 END IF;
247
248 elsif tax_treatment = 'BEFORE' then
249 if (amt > tax_remaining) then
250 l_tax_applied := tax_remaining ;
251 line_applied := amt - tax_remaining ;
252 else
253 l_tax_applied := amt;
254 line_applied := 0;
255 end if;
256 elsif tax_treatment = 'AFTER' then
257 if amt > line_remaining then
258 line_applied := line_remaining ;
259 l_tax_applied := amt - line_remaining ;
260 else
261 line_applied := amt ;
262 l_tax_applied := 0;
263 end if;
264 else /* No Treatment -- > tax not considerated */
265 line_applied := amt ;
266 l_tax_applied := 0;
267 end if;
268 else
269 if o_tax_treatment = 'PRORATE' then
270 if ((tax_remaining+line_remaining) <> 0) then
271 l_tax_applied := arpcurr.currround((tax_remaining*amt/(tax_remaining+line_remaining)),currency);
272 end if;
273 line_applied := amt - l_tax_applied;
274 elsif o_tax_treatment = 'BEFORE' then
275 line_applied := line_remaining ;
276 l_tax_applied := amt - line_remaining ;
277 elsif o_tax_treatment = 'AFTER' then
278 -- Fix 1378222, Added sign(tax_remaining)
279 l_tax_applied := sign(tax_remaining)*tax_remaining ;
280 line_applied := amt - sign(tax_remaining)*tax_remaining ;
281 else /* No Treatment -- > tax not considerated */
282 line_applied := amt ;
283 l_tax_applied := 0;
284 end if;
285 end if;
286
287 tax_applied := l_tax_applied;
288
289 IF PG_DEBUG in ('Y', 'C') THEN
290 arp_standard.debug('ARP_APP_CALC_PKG.extract_taxes()-');
291 END IF;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 IF PG_DEBUG in ('Y', 'C') THEN
296 arp_util.debug('EXCEPTION: ARP_APP_CALC_PKG.extract_taxes'||SQLERRM);
297 END IF;
298 RAISE;
299
300 end extract_taxes;
301
302 /*===========================================================================+
303 | PROCEDURE |
304 | CALC_APPLIED_AND_REMAINING |
305 | DESCRIPTION |
306 | Given all the remaining amounts this procedure will calculate the new |
307 | applied amounts and remaining amounts based on the amount you want to |
308 | apply. The rule used for calculating the applied amounts is the rule |
309 | currently active in the system options form. |
310 | |
311 | |
312 | SCOPE |
313 | -- Public |
314 | |
315 | PARAMETERS |
316 | IN - amt -- This is the mount to be applied to all parts using |
317 | the rule |
318 | currency -- currency the amount is in , used for rounding when |
319 | prorating |
320 | line_remaining -- Remaining line amt at the time of the applic. |
321 | line_tax_remaining -- Remaining tax amt related to the line |
322 | freight_remaining -- Remaining line amt at the time of the applic. |
323 | freight_tax_remaining -- Remaining tax amt related to the freight |
324 | charges_remaining -- Remaining line amt at the time of the applic. |
325 | charges_tax_remaining -- Remaining tax amt related to the charges |
326 | OUT NOCOPY
327 | line_applied - Amount applied for this part |
328 | line_tax_applied - Amount applied for this part |
329 | freight_applied - Amount applied for this part |
330 | freight_tax_applied - Amount applied for this part |
331 | charges_applied - Amount applied for this part |
332 | charges_tax_applied - Amount applied for this part |
333 | |
334 | Also all the new remaining amounts will be provided back. This is |
335 | mainly important for the c-functions |
336 | Most PL/SQL procedures will calculate their own remaining amounts |
337 | USAGE NOTES |
338 | 1. What happens to negative values :
339 | If remaining amounts are mixed sign -- > error. |
340 | One remaining amount -ve ---> other remaining zero or -ve |
341 | AMT -ve , remaining amts +ve ---> new remaining higher. |
342 | Applied amts -ve. |
343 | AMT +ve , remaining amts -ve ---> new abs remaining higher |
344 | Applied amts +ve |
345 | AMT -ve , remaining amts -ve ---> new abs remaining lower |
346 | Applied amts -ve |
347 | AMT +ve , remaining amts +ve ---> new remaining lower |
348 | Applied amts +ve |
349 | 2. Only Line_Tax_ values will be used for now because tax on |
350 | freight and charges does not yet exists. Pass a zero value for it|
351 | MODIFICATION HISTORY |
352 | 03-11-97 Joan Zaman -- Created |
353 | 12-05-97 Govind J Before calling extract_taxes, check the |
354 | amount applied that needs to be divide into |
355 | line (or freight or charges) and the correspon-|
356 | ding tax amount, instead of checking the |
357 | remaining amounts (e.g., line_remaining + |
358 | tax_remaining). This is because, amount can be |
359 | overapplied, if remaining amounts are zero. |
360 +===========================================================================*/
361 --131
362 procedure calc_applied_and_remaining ( amt in number
363 ,rule_set_id number
364 ,currency in varchar2
365 ,line_remaining in out NOCOPY number
366 ,line_tax_remaining in out NOCOPY number
367 ,freight_remaining in out NOCOPY number
368 ,freight_tax_remaining in out NOCOPY number
369 ,charges_remaining in out NOCOPY number
370 ,charges_tax_remaining in out NOCOPY number
371 ,line_applied out NOCOPY number
372 ,line_tax_applied out NOCOPY number
373 ,freight_applied out NOCOPY number
374 ,freight_tax_applied out NOCOPY number
375 ,charges_applied out NOCOPY number
376 ,charges_tax_applied out NOCOPY number) is
377
378 cursor_name INTEGER;
379 rows_processed INTEGER;
380
381 t_line_remaining number:=0;
382 t_freight_remaining number:=0;
383 t_charges_remaining number:=0;
384 t_line_applied number:=0;
385 t_freight_applied number:=0;
386 t_charges_applied number:=0;
387 r_line_applied number:=0;
388 r_freight_applied number:=0;
389 r_charges_applied number:=0;
390 l_line_applied number:=0;
391 l_line_tax_applied number:=0;
392 l_freight_applied number:=0;
393 l_freight_tax_applied number:=0;
394 l_charges_applied number:=0;
395 l_charges_tax_applied number:=0;
396
397 --164
398 l_line_tax_treatment VARCHAR2(30):='NONE';
399 l_freight_tax_treatment VARCHAR2(30):='NONE';
400 l_charges_tax_treatment VARCHAR2(30):='NONE';
401 o_line_tax_treatment VARCHAR2(30):='NONE';
402 o_freight_tax_treatment VARCHAR2(30):='NONE';
403 o_charges_tax_treatment VARCHAR2(30):='NONE';
404
405
406 begin
407
408 IF PG_DEBUG in ('Y', 'C') THEN
409 arp_standard.debug('ARP_APP_CALC_PKG.calc_applied_and_remaining()+ ');
410 arp_standard.debug('calc_applied_and_remaining: ' || ' ..amount applied '||to_char(amt));
411 arp_standard.debug('calc_applied_and_remaining: ' || ' ..currency '||currency);
412 arp_standard.debug('calc_applied_and_remaining: ' || ' ..line_remaining '||to_char(line_remaining));
413 arp_standard.debug('calc_applied_and_remaining: ' || ' ..line_tax_remaining '||to_char(line_tax_remaining));
414 arp_standard.debug('calc_applied_and_remaining: ' || ' ..l_line_applied '||to_char(l_line_applied));
415 arp_standard.debug('calc_applied_and_remaining: ' || ' ..l_line_tax_applied '||to_char(l_line_tax_applied));
416 END IF;
417
418 -- Checking for negative values.
419 -- Rule source only expects +ve values , so values will have to be
420 -- checked before passing to the cursor.
421
422 IF ( (sign(line_remaining) * sign(line_tax_remaining) < 0 )
423 OR (sign(freight_remaining) * sign(freight_tax_remaining) < 0 )
424 OR (sign(charges_remaining) * sign(charges_tax_remaining) < 0 )
425 ) THEN
426
427 --error an associated tax line cannot be negative when the main line is positive or viceversa.
428 fnd_message.set_name('AR','AR_INVALID_REMAINING');
429 app_exception.raise_exception;
430
431 ELSE
432 t_line_remaining := line_remaining + line_tax_remaining ;
433 t_freight_remaining := freight_remaining + freight_tax_remaining ;
434 t_charges_remaining := charges_remaining + charges_tax_remaining ;
435 END IF;
436
437
438 /*
439 The following mixed-signs condition should not occur, as the calc_applied_and_remaining
440 calling routine, divides a mixed-sign balance application into 2 phases, such that in each
441 phase amounts with same sign are passed for application. However the error will notify us
442 of a mixed-sign at this point, for debugging.
443 */
444
445 IF ( (sign(t_line_remaining) * sign(t_freight_remaining) < 0 )
446 OR (sign(t_line_remaining) * sign(t_charges_remaining) < 0 )
447 OR (sign(t_freight_remaining) * sign(t_charges_remaining) < 0 )
448 ) THEN
449
450 -- error , if one of the remaining amounts is negative then the other have to be
451 -- zero or negative.
452
453 fnd_message.set_name('AR','AR_INVALID_REMAINING' );
454 app_exception.raise_exception;
455
456 END IF;
457
458 -- This will set the rule start and rule end variables.
459 set_rule_set(rule_set_id);
460
461 cursor_name := dbms_sql.open_cursor;
462
463 dbms_sql.parse(cursor_name , g_rule_source , g_rule_start , g_rule_end ,
464 FALSE , dbms_sql.v7);
465
466 IF PG_DEBUG in ('Y', 'C') THEN
467 arp_util.debug('calc_applied_and_remaining: ' || '(After Parse): dbms_sql bind variables: ');
468 arp_util.debug('calc_applied_and_remaining: ' || ':currency : '||currency);
469 arp_util.debug('calc_applied_and_remaining: ' || ':amt : '||to_char(amt));
470 arp_util.debug('calc_applied_and_remaining: ' || ':line_remaining : '||to_char(t_line_remaining));
471 arp_util.debug('calc_applied_and_remaining: ' || ':freight_remaining : '||to_char(t_freight_remaining));
472 arp_util.debug('calc_applied_and_remaining: ' || ':charges_remaining : '||to_char(t_charges_remaining));
473 arp_util.debug('calc_applied_and_remaining: ' || ':line_applied : '||to_char(t_line_applied));
474 arp_util.debug('calc_applied_and_remaining: ' || ':freight_applied : '||to_char(t_freight_applied));
475 arp_util.debug('calc_applied_and_remaining: ' || ':charges_applied : '||to_char(t_charges_applied));
476 arp_util.debug('calc_applied_and_remaining: ' || ':line_tax_treatment : '||l_line_tax_treatment);
477 arp_util.debug('calc_applied_and_remaining: ' || ':freight_tax_treatment : '||l_freight_tax_treatment);
478 arp_util.debug('calc_applied_and_remaining: ' || ':charges_tax_treatment : '||l_charges_tax_treatment);
479 arp_util.debug('calc_applied_and_remaining: ' || ':o_line_tax_treatment : '||o_line_tax_treatment);
480 arp_util.debug('calc_applied_and_remaining: ' || ':o_freight_tax_treatment : '||o_freight_tax_treatment);
481 arp_util.debug('calc_applied_and_remaining: ' || ':o_charges_tax_treatment : '||o_charges_tax_treatment);
482 END IF;
483
484 -- +ve values are passed here. It will not affect the applied amounts
485 -- They will be converted back at the end of this procedure.
486
487 dbms_sql.bind_variable(cursor_name ,':currency',currency);
488 dbms_sql.bind_variable(cursor_name ,':amt',abs(amt));
489 dbms_sql.bind_variable(cursor_name ,':line_remaining',abs(t_line_remaining) );
490 dbms_sql.bind_variable(cursor_name ,':freight_remaining',abs(t_freight_remaining));
491 dbms_sql.bind_variable(cursor_name ,':charges_remaining',abs(t_charges_remaining));
492 dbms_sql.bind_variable(cursor_name , ':line_applied',t_line_applied);
493 dbms_sql.bind_variable(cursor_name , ':freight_applied' ,t_freight_applied );
494 dbms_sql.bind_variable(cursor_name , ':charges_applied' ,t_charges_applied);
495 dbms_sql.bind_variable(cursor_name , ':line_tax_treatment' ,l_line_tax_treatment,30);
496 dbms_sql.bind_variable(cursor_name , ':freight_tax_treatment' ,l_freight_tax_treatment,30);
497 dbms_sql.bind_variable(cursor_name , ':charges_tax_treatment' ,l_charges_tax_treatment,30);
498 dbms_sql.bind_variable(cursor_name , ':o_line_tax_treatment' ,o_line_tax_treatment,30);
499 dbms_sql.bind_variable(cursor_name , ':o_freight_tax_treatment' ,o_freight_tax_treatment,30);
500 dbms_sql.bind_variable(cursor_name , ':o_charges_tax_treatment' ,o_charges_tax_treatment,30);
501
502 rows_processed := dbms_sql.execute(cursor_name);
503 IF PG_DEBUG in ('Y', 'C') THEN
504 arp_util.debug('calc_applied_and_remaining: ' || 'After dbms_sql.execute: rows_processed = : '||to_char(rows_processed));
505 END IF;
506
507 dbms_sql.variable_value(cursor_name , ':line_applied',r_line_applied );
508 dbms_sql.variable_value(cursor_name , ':freight_applied',r_freight_applied );
509 dbms_sql.variable_value(cursor_name , ':charges_applied',r_charges_applied );
510 dbms_sql.variable_value(cursor_name , ':line_tax_treatment' ,l_line_tax_treatment);
511 dbms_sql.variable_value(cursor_name , ':freight_tax_treatment' ,l_freight_tax_treatment);
512 dbms_sql.variable_value(cursor_name , ':charges_tax_treatment' ,l_charges_tax_treatment);
513 dbms_sql.variable_value(cursor_name , ':o_line_tax_treatment' ,o_line_tax_treatment);
514 dbms_sql.variable_value(cursor_name , ':o_freight_tax_treatment' ,o_freight_tax_treatment);
515 dbms_sql.variable_value(cursor_name , ':o_charges_tax_treatment' ,o_charges_tax_treatment);
516
517 IF PG_DEBUG in ('Y', 'C') THEN
518 arp_util.debug('calc_applied_and_remaining: ' || '(After Execute): dbms_sql variable values: ');
519 arp_util.debug('calc_applied_and_remaining: ' || ':currency : '||currency);
520 arp_util.debug('calc_applied_and_remaining: ' || ':line_applied : '||to_char(r_line_applied));
521 arp_util.debug('calc_applied_and_remaining: ' || ':freight_applied : '||to_char(r_freight_applied));
522 arp_util.debug('calc_applied_and_remaining: ' || ':charges_applied : '||to_char(r_charges_applied));
523 arp_util.debug('calc_applied_and_remaining: ' || ':line_tax_treatment : '||l_line_tax_treatment);
524 arp_util.debug('calc_applied_and_remaining: ' || ':freight_tax_treatment : '||l_freight_tax_treatment);
525 arp_util.debug('calc_applied_and_remaining: ' || ':charges_tax_treatment : '||l_charges_tax_treatment);
526 arp_util.debug('calc_applied_and_remaining: ' || ':o_line_tax_treatment : '||o_line_tax_treatment);
527 arp_util.debug('calc_applied_and_remaining: ' || ':o_freight_tax_treatment : '||o_freight_tax_treatment);
528 arp_util.debug('calc_applied_and_remaining: ' || ':o_charges_tax_treatment : '||o_charges_tax_treatment);
529 END IF;
530
531 --
532 --Sometimes one could overapply on an invoice that has remaining amounts equal to zero.
533 --So check the applied amount (instead of remaining), while distributing applied_amount
534 --into a line_type (line/freight/charges) and its corresponding tax, based on whatever
535 --is the tax-treatment for the line-type. Tax-treatment can be PRORATE,BEFORE,AFTER or NONE.
536 --
537 --if (line_remaining + line_tax_remaining = 0 ) THEN
538 --
539 if (r_line_applied = 0 ) THEN
540 NULL;
541 ELSE
542 extract_taxes ( r_line_applied
543 ,l_line_tax_treatment
544 ,o_line_tax_treatment
545 ,currency
546 ,abs(line_remaining)
547 ,abs(line_tax_remaining)
548 ,l_line_applied
549 ,l_line_tax_applied );
550 END IF;
551
552 if (r_freight_applied = 0 ) THEN
553 NULL;
554 ELSE
555 extract_taxes ( r_freight_applied
556 ,l_freight_tax_treatment
557 ,o_freight_tax_treatment
558 ,currency
559 ,freight_remaining
560 ,freight_tax_remaining
561 ,l_freight_applied
562 ,l_freight_tax_applied );
563
564 END IF;
565
566 if (r_charges_applied = 0 ) THEN
567 NULL;
568 ELSE
569 extract_taxes ( r_charges_applied
570 ,l_charges_tax_treatment
571 ,o_charges_tax_treatment
572 ,currency
573 ,charges_remaining
574 ,charges_tax_remaining
575 ,l_charges_applied
576 ,l_charges_tax_applied );
577
578 END IF;
579
580 dbms_sql.close_cursor(cursor_name);
581
582 IF ( (sign(amt) * sign ( t_line_remaining ) < 0 )
583 OR (sign(amt) * sign ( t_freight_remaining ) < 0 )
584 OR (sign(amt) * sign ( t_charges_remaining ) < 0 )
585 ) THEN
586 -- amount to be applied and remaining amounts have different signs
587 -- This means the absolute value of the remaining amount will be higher
588
589 line_remaining := sign(line_remaining) * (abs(line_remaining) + abs(l_line_applied)) ;
590 freight_remaining := sign(freight_remaining) * (abs(freight_remaining) + abs(l_freight_applied)) ;
591 charges_remaining := sign(charges_remaining) * (abs(charges_remaining) + abs(l_charges_applied)) ;
592 line_tax_remaining := sign(line_tax_remaining) * (abs(line_tax_remaining) + abs(l_line_tax_applied)) ;
593 freight_tax_remaining := sign(freight_tax_remaining) * (abs(freight_tax_remaining) + abs(l_freight_tax_applied)) ;
594 charges_tax_remaining := sign(charges_tax_remaining) * (abs(charges_tax_remaining) + abs(l_charges_tax_applied)) ;
595
596 ELSE
597 -- amount to be applied has the same sign as the remaining amounts
598 -- This means the absolute value of the remaining amount will be lower
599
600 line_remaining := sign(line_remaining) * (abs(line_remaining) - abs(l_line_applied)) ;
601 freight_remaining := sign(freight_remaining) * (abs(freight_remaining) - abs(l_freight_applied)) ;
602 charges_remaining := sign(charges_remaining) * (abs(charges_remaining) - abs(l_charges_applied)) ;
603 line_tax_remaining := sign(line_tax_remaining) * (abs(line_tax_remaining) - abs(l_line_tax_applied)) ;
604 freight_tax_remaining := sign(freight_tax_remaining) * (abs(freight_tax_remaining) - abs(l_freight_tax_applied)) ;
605 charges_tax_remaining := sign(charges_tax_remaining) * (abs(charges_tax_remaining) - abs(l_charges_tax_applied)) ;
606
607
608
609 END IF;
610
611 -- Applied amount will have the same sign as the amount that was applied
612 --
613 line_applied := sign(amt) * abs(l_line_applied);
614 line_tax_applied := sign(amt) * abs(l_line_tax_applied);
615 freight_applied := sign(amt) * abs(l_freight_applied);
616 freight_tax_applied := sign(amt) * abs(l_freight_tax_applied);
617 charges_applied := sign(amt) * abs(l_charges_applied) ;
618 charges_tax_applied := sign(amt) * abs(l_charges_tax_applied);
619
620
621 IF PG_DEBUG in ('Y', 'C') THEN
622 arp_standard.debug('done with ARP_APP_CALC_PKG.calc_applied_and_remaining()-');
623 arp_standard.debug('calc_applied_and_remaining: ' || ' ..amount applied '||to_char(amt));
624 arp_standard.debug('calc_applied_and_remaining: ' || ' ..currency '||currency);
625 arp_standard.debug('calc_applied_and_remaining: ' || ' ..line_remaining '||to_char(line_remaining));
626 arp_standard.debug('calc_applied_and_remaining: ' || ' ..line_tax_remaining '||to_char(line_tax_remaining));
627 arp_standard.debug('calc_applied_and_remaining: ' || ' ..line_applied '||to_char(line_applied));
628 arp_standard.debug('calc_applied_and_remaining: ' || ' ..line_tax_applied '||to_char(line_tax_applied));
629 END IF;
630
631 EXCEPTION
632 WHEN OTHERS THEN
633 IF PG_DEBUG in ('Y', 'C') THEN
634 arp_util.debug('EXCEPTION: ARP_APP_CALC_PKG.calc_applied_and_remaining()'||SQLERRM);
635 END IF;
636 RAISE;
637
638
639 end calc_applied_and_remaining ;
640
641
642 /*===========================================================================+
643 | PROCEDURE |
644 | CALC_APPLIED_AND_REMAINING |
645 | DESCRIPTION |
646 | This is a cover routine that calls the other calc_applied_and_remaining |
647 | to compute the applied amounts to the various invoice balance components.|
648 | The routine also handles the case where invoice balance is of a mixed |
649 | sign (e.g., line +ve, tax -ve , freight +ve ). See USAGE NOTES |
650 | |
651 | SCOPE |
652 | -- Public |
653 | |
654 | PARAMETERS |
655 | IN - amt -- This is the mount to be applied to all parts using |
656 | the rule |
657 | currency -- currency the amount is in , used for rounding when |
658 | prorating |
659 | line_remaining -- Remaining line amt at the time of the applic. |
660 | line_tax_remaining -- Remaining tax amt related to the line |
661 | freight_remaining -- Remaining line amt at the time of the applic. |
662 | freight_tax_remaining -- Remaining tax amt related to the freight |
663 | charges_remaining -- Remaining line amt at the time of the applic. |
664 | charges_tax_remaining -- Remaining tax amt related to the charges |
665 | OUT NOCOPY
666 | line_applied - Amount applied for this part |
667 | line_tax_applied - Amount applied for this part |
668 | freight_applied - Amount applied for this part |
669 | freight_tax_applied - Amount applied for this part |
670 | charges_applied - Amount applied for this part |
671 | charges_tax_applied - Amount applied for this part |
672 | |
673 | Also all the new remaining amounts will be provided back. This is |
674 | mainly important for the c-functions |
675 | Most PL/SQL procedures will calculate their own remaining amounts |
676 | |
677 | USAGE NOTES |
678 | 1. What happens to mixed-sign balance : |
679 | If remaining amounts are of mixed sign -- > |
680 | Apply in 2 phases,passing same-sign values in each phase |
681 | |
682 | Phase 1: MIXED-SIGN TREATMENT |
683 | Pass in amounts that have same sign as applied |
684 | amount,to be reduced to zero. Thus in this phase,|
685 | only the gross of such components will be passed |
686 | to calc_applied_and_remaining if applied amount |
687 | happens to be greater than this gross. |
688 | |
689 | Phase 2: If applied amount happens to be greater than the |
690 | gross in phase 1 above, pass the balance to |
691 | calc_applied_and_remaining again for over- |
692 | application. Since some components have |
693 | been reduced to zero in phase 1, pass the other |
694 | components (+ the zeroed components) this time. |
695 | |
696 | 2. If there is no mixed_sign balance --- > |
697 | Only Phase 2 is necessary. |
698 | |
699 | 3. Only Line_Tax_ values will be used for now because tax on |
700 | freight and charges does not yet exists. Pass a zero value for it|
701 | |
702 | MODIFICATION HISTORY |
703 | 03-11-97 Joan Zaman -- Created |
704 | 12-05-97 Govind Jayanth -- Modified to provide mixed-sign treatment. |
705 | 12-09-98 Govind Jayanth -- Bug fix : 772847 |
706 | 01/10/06 V Crisostomo Bug 4758340 : modify logic to process |
707 | mixed sign applications differently when |
708 | called from BR |
709 +===========================================================================*/
710 procedure calc_applied_and_remaining ( p_amt in number
711 ,p_rule_set_id number
712 ,p_currency in varchar2
713 ,p_line_remaining in out NOCOPY number
714 ,p_line_tax_remaining in out NOCOPY number
715 ,p_freight_remaining in out NOCOPY number
716 ,p_charges_remaining in out NOCOPY number
717 ,p_line_applied out NOCOPY number
718 ,p_line_tax_applied out NOCOPY number
719 ,p_freight_applied out NOCOPY number
720 ,p_charges_applied out NOCOPY number
721 ,p_created_from in varchar2 default NULL
722 ) is
723
724 /*
725 * LINE, LINE_TAX, FREIGHT, FREIGHT_TAX, CHARGES, CHARGES_TAX are
726 * the 6 components of the invoice balance,that the p_amt is applied to.
727 */
728 l_no_of_balance_components CONSTANT number:= 6;
729
730 l_mixed_sign_count number:= 0;
731 l_ms_gross_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
732 l_amt_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
733 l_ms_applied_amt ar_receivable_applications.tax_applied%TYPE:=0;
734
735 l_ms_use_line number:= 0;
736 l_ms_use_line_tax number:= 0;
737 l_ms_use_freight number:= 0;
738 l_ms_use_freight_tax number:= 0;
739 l_ms_use_charges number:= 0;
740 l_ms_use_charges_tax number:= 0;
741
742 l_ms_net_remaining number:= 0;
743
744 /* Amounts applied during mixed sign treatment */
745 l_ms_applied_line ar_receivable_applications.tax_applied%TYPE:=0;
746 l_ms_applied_line_tax ar_receivable_applications.tax_applied%TYPE:=0;
747 l_ms_applied_freight ar_receivable_applications.tax_applied%TYPE:=0;
748 l_ms_applied_freight_tax ar_receivable_applications.tax_applied%TYPE:=0;
749 l_ms_applied_charges ar_receivable_applications.tax_applied%TYPE:=0;
750 l_ms_applied_charges_tax ar_receivable_applications.tax_applied%TYPE:=0;
751
752 /* Amounts applied during non-mixed sign treatment */
753 l_nonms_applied_line ar_receivable_applications.tax_applied%TYPE:=0;
754 l_nonms_applied_line_tax ar_receivable_applications.tax_applied%TYPE:=0;
755 l_nonms_applied_freight ar_receivable_applications.tax_applied%TYPE:=0;
756 l_nonms_applied_freight_tax ar_receivable_applications.tax_applied%TYPE:=0;
757 l_nonms_applied_charges ar_receivable_applications.tax_applied%TYPE:=0;
758 l_nonms_applied_charges_tax ar_receivable_applications.tax_applied%TYPE:=0;
759
760 /* To hold original values */
761 l_org_line_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
762 l_org_line_tax_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
763 l_org_fr_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
764 l_org_fr_tax_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
765 l_org_ch_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
766 l_org_ch_tax_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
767
768 /*
769 * Currently FREIGHT_TAX and CHARGES_TAX are not treated, so they
770 * appear as local variables.
771 */
772 l_freight_tax_remaining number:=0;
773 l_charges_tax_remaining number:=0;
774 l_freight_tax_applied number:=0;
775 l_charges_tax_applied number:=0;
776
777 BEGIN
778 arp_standard.debug('ARP_APP_CALC_PKG.calc_applied_and_remaining() Wrapper +');
779
780 /*
781 * gjayanth: Bug 772847: When CM is applied to CB, payment
782 * schedule's line_remaining was not getting updated correctly.
783 * Assigning zero to null amount parameters.
784 */
785 p_line_remaining := nvl(p_line_remaining, 0);
786 p_line_tax_remaining := nvl(p_line_tax_remaining, 0);
787 p_freight_remaining := nvl(p_freight_remaining, 0);
788 p_charges_remaining := nvl(p_charges_remaining, 0);
789
790 arp_standard.debug('in calc_applied_and_remaining Wrapper, debug');
791 arp_standard.debug(' .. p_created_from = ' || p_created_from);
792 arp_standard.debug(' .. p_line_remaining = ' || to_char(p_line_remaining));
793 arp_standard.debug(' .. p_line_tax_remaining = ' || to_char(p_line_tax_remaining));
794 arp_standard.debug(' .. p_freight_remaining = ' || to_char(p_freight_remaining));
795 arp_standard.debug(' .. p_charges_remaining = ' || to_char(p_charges_remaining));
796 arp_standard.debug(' .. p_amt = ' || to_char(p_amt));
797
798 /* Save original values */
799 l_org_line_remaining := p_line_remaining;
800 l_org_line_tax_remaining := p_line_tax_remaining;
801 l_org_fr_remaining := p_freight_remaining;
802 l_org_fr_tax_remaining := l_freight_tax_remaining;
803 l_org_ch_remaining := p_charges_remaining;
804 l_org_ch_tax_remaining := l_charges_tax_remaining;
805 /* Bug 9611163 */
806 l_amt_remaining := p_amt;
807 l_ms_net_remaining := l_org_line_remaining + l_org_line_tax_remaining + l_org_fr_remaining + l_org_fr_tax_remaining + l_org_ch_remaining +
808 l_org_ch_tax_remaining;
809 /*
810 * First find out NOCOPY which components have signs different
811 * from that of 'amt'. Amounts with same signs are treated
812 * first, unless all happen to have same sign or all are
813 * different, in which case, we do not have a case of 'mixed-sign'
814 * balance.
815
816 * Bug 2389772 : changed relational operator below from < to <=
817 * so that when p_amt = 0, it is *not* treated as a mixed sign
818 * application
819 *
820 */
821
822 IF nvl(p_created_from,'XXX') = 'ARBRMAIB' then
823
824 -- Bug 4758340, if called from BR
825 -- re-init all to 0, and set to 1 when they are opposite signs
826 -- apply to OPPOSITE sign first
827
828 l_ms_use_line := 0;
829 l_ms_use_line_tax := 0;
830 l_ms_use_freight := 0;
831 l_ms_use_freight_tax := 0;
832 l_ms_use_charges := 0;
833 l_ms_use_charges_tax := 0;
834
835 IF sign(p_amt) * sign(p_line_remaining) <= 0 THEN
836 l_ms_use_line := 1;
837 END IF;
838
839 if sign(p_amt) * sign(p_line_tax_remaining) <= 0 THEN
840 l_ms_use_line_tax := 1;
841 END IF;
842
843 IF sign(p_amt) * sign (p_freight_remaining) <= 0 THEN
844 l_ms_use_freight := 1;
845 END IF;
846
847
848 IF sign(p_amt) * sign(l_freight_tax_remaining) <= 0 THEN
849 l_ms_use_freight_tax := 1;
850 END IF;
851
852 IF sign(p_amt) * sign(p_charges_remaining) <= 0 THEN
853 l_ms_use_charges := 1;
854 END IF;
855
856 IF sign(p_amt) * sign(l_charges_tax_remaining) <= 0 THEN
857 l_ms_use_charges_tax := 1;
858 END IF;
859
860 ELSE
861
862 -- '1' indicates, amount is NOT a candidate for mixed-sign treatment
863 -- re-init all to 1, and set to 0 when they are opposite signs
864 -- apply to SAME sign first
865
866 l_ms_use_line := 1;
867 l_ms_use_line_tax := 1;
868 l_ms_use_freight := 1;
869 l_ms_use_freight_tax := 1;
870 l_ms_use_charges := 1;
871 l_ms_use_charges_tax := 1;
872
873 -- Forward Port of Bug 4487954 - See Bug 4592507
874 -- reverted the check below from <= to < since
875 -- amounts were not getting prorated between line and tax when
876 -- prorate all application rule set is used
877
878
879 IF sign(p_amt) * sign(p_line_remaining) < 0 THEN
880 l_ms_use_line := 0;
881 END IF;
882
883 if sign(p_amt) * sign(p_line_tax_remaining) < 0 THEN
884 l_ms_use_line_tax := 0;
885 END IF;
886
887 IF sign(p_amt) * sign (p_freight_remaining) < 0 THEN
888 l_ms_use_freight := 0;
889 END IF;
890
891 IF sign(p_amt) * sign(l_freight_tax_remaining) < 0 THEN
892 l_ms_use_freight_tax := 0;
893 END IF;
894
895 IF sign(p_amt) * sign(p_charges_remaining) < 0 THEN
896 l_ms_use_charges := 0;
897 END IF;
898
899 IF sign(p_amt) * sign(l_charges_tax_remaining) < 0 THEN
900 l_ms_use_charges_tax := 0;
901 END IF;
902
903
904 END IF;
905
906 l_mixed_sign_count := l_ms_use_line + l_ms_use_line_tax +
907 l_ms_use_freight + l_ms_use_freight_tax +
908 l_ms_use_charges + l_ms_use_charges_tax;
909
910 arp_standard.debug(' l_mixed_sign_count = ' || to_char(l_mixed_sign_count));
911 arp_standard.debug(' l_ms_use_line = ' || to_char(l_ms_use_line) ||
912 ' l_ms_use_line_tax = ' || to_char(l_ms_use_line_tax));
913 arp_standard.debug(' l_ms_use_freight = ' || to_char(l_ms_use_freight) ||
914 ' l_ms_use_freight_tax = ' || to_char(l_ms_use_freight_tax));
915 arp_standard.debug(' l_ms_use_charges = ' || to_char(l_ms_use_charges) ||
916 ' l_ms_use_charges_tax = ' || to_char(l_ms_use_charges_tax));
917
918 /*
919 * If all amounts had sign same as p_amt, l_mixed_sign_count = 6.
920 * If all amounts had sign opposite to that of p_amt, l_mixed_sign_count = 0.
921 * If l_mixed_sign_count is between 0 and 6, amounts have a mixed-sign,
922 * and are treated differently.
923 */
924 IF (l_mixed_sign_count > 0) and (l_mixed_sign_count < l_no_of_balance_components) THEN
925 /*
926 * MIXED-SIGN TREATMENT
927 *
928 * Temporarily zero out NOCOPY components with sign opposite to that of 'amt',
929 * so that 'amt' may be applied to the same-sign amounts first.
930 */
931 arp_util.debug('Treating invoice components that have mixed signs.');
932 arp_util.debug('p_amt = ' || to_char(p_amt));
933
934 p_line_remaining := l_ms_use_line * p_line_remaining;
935 p_line_tax_remaining := l_ms_use_line_tax * p_line_tax_remaining;
936 p_freight_remaining := l_ms_use_freight * p_freight_remaining;
937 l_freight_tax_remaining := l_ms_use_freight_tax * l_freight_tax_remaining;
938 p_charges_remaining := l_ms_use_charges * p_charges_remaining;
939 l_charges_tax_remaining := l_ms_use_charges_tax * l_charges_tax_remaining;
940
941 /* Find the gross of same-sign amounts */
942 l_ms_gross_remaining := p_line_remaining + p_line_tax_remaining +
943 p_freight_remaining + l_freight_tax_remaining +
944 p_charges_remaining + l_charges_tax_remaining;
945
946 IF (abs(p_amt) <= abs(l_ms_gross_remaining)) THEN
947
948 IF abs(p_amt) = abs(l_ms_net_remaining) THEN
949 l_ms_applied_amt := l_ms_gross_remaining;
950 ELSE
951 l_ms_applied_amt := p_amt;
952 END IF;
953 ELSE
954 l_ms_applied_amt := l_ms_gross_remaining;
955 END IF;
956
957
958 arp_util.debug('1. call calc_applied_and_remaining with params : ');
959 arp_util.debug(' l_ms_applied_amt = ' || to_char(l_ms_applied_amt));
960 arp_util.debug(' p_rule_set_id = ' || to_char(p_rule_set_id));
961 arp_util.debug(' p_line_remaining = ' || to_char(p_line_remaining));
962 arp_util.debug(' p_line_tax_remaining = ' || to_char(p_line_tax_remaining));
963 arp_util.debug(' p_freight_remaining = ' || to_char(p_freight_remaining));
964 arp_util.debug(' l_freight_tax_remaining = ' || to_char(l_freight_tax_remaining));
965 arp_util.debug(' p_charges_remaining = ' || to_char(p_charges_remaining));
966 arp_util.debug(' l_charges_tax_remaining = ' || to_char(l_charges_tax_remaining));
967
968 calc_applied_and_remaining ( l_ms_applied_amt
969 ,p_rule_set_id
970 ,p_currency
971 ,p_line_remaining
972 ,p_line_tax_remaining
973 ,p_freight_remaining
974 ,l_freight_tax_remaining
975 ,p_charges_remaining
976 ,l_charges_tax_remaining
977 ,l_ms_applied_line
978 ,l_ms_applied_line_tax
979 ,l_ms_applied_freight
980 ,l_ms_applied_freight_tax
981 ,l_ms_applied_charges
982 ,l_ms_applied_charges_tax ) ;
983
984 /* Amount remaining for overapplication */
985 l_amt_remaining := sign(p_amt) * ( abs(p_amt) - abs(l_ms_applied_amt) );
986
987 /*
988 * Restore opp-sign values so they can be treated now.
989 */
990 IF (l_ms_use_line = 0) THEN
991 p_line_remaining := l_org_line_remaining;
992 END IF;
993
994 IF (l_ms_use_line_tax = 0) THEN
995 p_line_tax_remaining := l_org_line_tax_remaining;
996 END IF;
997
998 IF (l_ms_use_freight = 0) THEN
999 p_freight_remaining := l_org_fr_remaining;
1000 END IF;
1001
1002 IF (l_ms_use_freight_tax = 0) THEN
1003 l_freight_tax_remaining := l_org_fr_tax_remaining;
1004 END IF;
1005
1006 IF (l_ms_use_charges = 0) THEN
1007 p_charges_remaining := l_org_ch_remaining;
1008 END IF;
1009
1010 IF (l_ms_use_charges_tax = 0) THEN
1011 l_charges_tax_remaining := l_org_ch_tax_remaining;
1012 END IF;
1013
1014 END IF; /* MIXED-SIGN TREATMENT */
1015
1016
1017 /*
1018 * After mixed sign balances (if any) are treated, balances are now of the
1019 * same sign. This is because those components that had same sign as amt,
1020 * have been reduced to zero by the previous calc_applied_and_remaining().
1021 * If any amt is left, apply.
1022 */
1023
1024 IF ( abs(l_amt_remaining) > 0 ) THEN
1025
1026 /*
1027 * Still some amt left. Apply as usual.
1028 */
1029
1030 arp_util.debug('Treating invoice components that have the same sign.');
1031
1032 arp_util.debug('2. call calc_applied_and_remaining with params : ');
1033 arp_util.debug(' l_amt_remaining = ' || to_char(l_amt_remaining));
1034 arp_util.debug(' p_rule_set_id = ' || to_char(p_rule_set_id));
1035 arp_util.debug(' p_line_remaining = ' || to_char(p_line_remaining));
1036 arp_util.debug(' p_line_tax_remaining = ' || to_char(p_line_tax_remaining));
1037 arp_util.debug(' p_freight_remaining = ' || to_char(p_freight_remaining));
1038 arp_util.debug(' l_freight_tax_remaining = ' || to_char(l_freight_tax_remaining));
1039 arp_util.debug(' p_charges_remaining = ' || to_char(p_charges_remaining));
1040 arp_util.debug(' l_charges_tax_remaining = ' || to_char(l_charges_tax_remaining));
1041
1042 calc_applied_and_remaining ( l_amt_remaining
1043 ,p_rule_set_id
1044 ,p_currency
1045 ,p_line_remaining
1046 ,p_line_tax_remaining
1047 ,p_freight_remaining
1048 ,l_freight_tax_remaining
1049 ,p_charges_remaining
1050 ,l_charges_tax_remaining
1051 ,l_nonms_applied_line
1052 ,l_nonms_applied_line_tax
1053 ,l_nonms_applied_freight
1054 ,l_nonms_applied_freight_tax
1055 ,l_nonms_applied_charges
1056 ,l_nonms_applied_charges_tax ) ;
1057
1058 END IF;
1059
1060 p_line_applied := l_ms_applied_line + l_nonms_applied_line ;
1061 p_line_tax_applied := l_ms_applied_line_tax + l_nonms_applied_line_tax ;
1062 p_freight_applied := l_ms_applied_freight + l_nonms_applied_freight ;
1063 l_freight_tax_applied := l_ms_applied_freight_tax + l_nonms_applied_freight_tax ;
1064 p_charges_applied := l_ms_applied_charges + l_nonms_applied_charges ;
1065 l_charges_tax_applied := l_ms_applied_charges_tax + l_nonms_applied_charges_tax ;
1066
1067 arp_standard.debug('done calc_applied_and_remaining Wrapper, debug');
1068 arp_standard.debug(' .. p_line_applied = ' || to_char(l_ms_applied_line + l_nonms_applied_line));
1069 arp_standard.debug(' .. p_line_tax_applied = ' || to_char(l_ms_applied_line_tax + l_nonms_applied_line_tax));
1070 arp_standard.debug(' .. p_freight_applied = ' || to_char(l_ms_applied_freight + l_nonms_applied_freight));
1071 arp_standard.debug(' .. l_freight_tax_applied = ' || to_char(l_ms_applied_freight_tax + l_nonms_applied_freight_tax));
1072 arp_standard.debug(' .. p_charges_applied = ' || to_char(l_ms_applied_charges + l_nonms_applied_charges));
1073 arp_standard.debug(' .. l_charges_tax_applied = ' || to_char(l_ms_applied_charges_tax + l_nonms_applied_charges_tax));
1074
1075 arp_standard.debug('calc_applied_and_remaining() Wrapper -');
1076
1077 EXCEPTION
1078 WHEN OTHERS THEN
1079 arp_util.debug('EXCEPTION: ARP_APP_CALC_PKG.calc_applied_and_remaining: Wrapper: '||SQLERRM);
1080 RAISE;
1081
1082 END;
1083
1084
1085 /*===========================================================================+
1086 | PROCEDURE |
1087 | COMPILE_RULE () |
1088 | DESCRIPTION |
1089 | This procedure will create a long column that will be |
1090 | inserted into |
1091 | the ar_app_rule_sets table with the according rule |
1092 | This compilation makes it possible for the calc_applied_and_remaining |
1093 | procedure not to select multiple times from app_rule... tables |
1094 | |
1095 | This procedure should be called from the application rules set up form |
1096 | from the post_update trigger when the freeze flag gets set to 'Y' |
1097 | |
1098 | Whenever a rule will be frozen a compiled rule will be created and stored|
1099 | in the long column rule_source. |
1100 | |
1101 | Before creating the long column the procedure will check whether |
1102 | the rule is valid. Following checks will be made : |
1103 | 1. Is there one and only one Over Application Rule |
1104 | 2. Are there one or more non-overapplication Rules |
1105 | 3. Is every Line type present in one of the non-overapplication rules |
1106 | 4. Has one and only one of the application rule details in every |
1107 | application rule the rounding correction checked |
1108 | 5. Are the sequence numbers of the application rules different |
1109 | |
1110 | |
1111 | SCOPE -- Public -- To be called from the application rules set up form |
1112 | PARAMETERS |
1113 | IN -- rule_id -- This is the id from the rule you want to compile. |
1114 | RULE_SOURCE (Example Code )
1115 | -----------
1116 | DECLARE Rule Name : Pro Ratio
1117 | Date Generated : 20-MAR-1997,15:15
1118 | 1. Pro Ratio Rule
1119 | LINE, PRORATE , Rounding Correction : Y
1120 | FREIGHT, NONE , Rounding Correction : N
1121 | CHARGES, NONE , Rounding Correction : N
1122 | Over Application Rule : Pro Ratio Over App
1123 | LINE, PRORATE , Rounding Correction : Y
1124 | FREIGHT, NONE , Rounding Correction : N
1125 | CHARGES, NONE , Rounding Correction : N
1126 |
1127 | l_amt ar_payment_schedules.amount_due_remaining%TYPE;
1128 | l_line_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1129 | l_freight_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1130 | l_charges_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1131 | l_gross_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1132 | l_line_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1133 | l_freight_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1134 | l_charges_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1135 | l_counter number := 0;
1136 | BEGIN l_amt := :amt ;
1137 | l_line_remaining := :line_remaining ;
1138 | l_freight_remaining := :freight_remaining ;
1139 | l_charges_remaining := :charges_remaining ;
1140 |
1141 | l_gross_remaining := l_line_remaining + l_freight_remaining + l_charges_rema
1142 | ining ;
1143 | if (l_amt > 0) and (l_amt < l_gross_remaining) then
1144 |
1145 | l_freight_applied := arpcurr.currround((l_amt * l_freight_remaining /
1146 | l_gross_remaining),:currency) ;
1147 |
1148 | l_charges_applied := arpcurr.currround((l_amt * l_charges_remaining /
1149 | l_gross_remaining),:currency) ;
1150 |
1151 | l_line_applied := l_amt - l_freight_applied - l_charges_applied ;
1152 |
1153 | l_amt := 0 ;
1154 |
1155 | elsif (l_amt > l_gross_remaining) then
1156 | l_line_applied := l_line_remaining ;
1157 |
1158 | l_freight_applied := l_freight_remaining ;
1159 | l_charges_applied := l_charges_remaining ;
1160 |
1161 |
1162 | l_amt := l_amt - l_gross_remaining ;
1163 |
1164 | end if;
1165 |
1166 | l_gross_remaining :=
1167 | l_line_remaining + l_freight_remaining + l_charges_remaining ;
1168 | if (l_amt > 0) then
1169 |
1170 | while l_amt > l_gross_remaining loop
1171 |
1172 | l_line_applied := l_line_applied + l_line_remaining ;
1173 | l_amt := l_amt - l_line_remaining ;
1174 |
1175 | l_freight_applied := l_freight_applied + l_freight_remaining ;
1176 | l_amt := l_amt - l_freight_remaining ;
1177 |
1178 | l_charges_applied := l_charges_applied + l_charges_remaining ;
1179 | l_amt := l_amt - l_charges_remaining ;
1180 |
1181 | end loop;
1182 |
1183 | l_freight_applied := l_freight_applied + arpcurr.currround((l_amt * l
1184 | _freight_remaining / l_gross_remaining),:currency) ;
1185 |
1186 | l_charges_applied := l_charges_applied + arpcurr.currround((l_amt * l_
1187 | charges_remaining / l_gross_remaining),:currency) ;
1188 |
1189 | l_line_applied := l_line_applied + l_amt - arpcurr.currround((l_amt *
1190 | l_freight_remaining / l_gross_remaining),:currency) - arpcurr.currround((l_amt
1191 | * l_charges_remaining / l_gross_remaining),:currency) ;
1192 |
1193 | l_amt := 0 ;
1194 |
1195 | end if;
1196 |
1197 | :line_applied := l_line_applied ;
1198 | :freight_applied := l_freight_applied ;
1199 | :charges_applied := l_charges_applied ;
1200 | :line_tax_treatment := 'PRORATE' ;
1201 | :freight_tax_treatment := 'NONE' ;
1202 | :charges_tax_treatment := 'NONE' ;
1203 |
1204 | :o_line_tax_treatment := 'PRORATE' ;
1205 | | :o_freight_tax_treatment := 'NONE' ;
1206 | :o_charges_tax_treatment := 'NONE' ;
1207 | END ;
1208 | |
1209 | MODIFICATION HISTORY |
1210 | 03-11-97 -- Joan Zaman -- Created |
1211 | 07-SEP-99 J Rautiainen Bugfix for bug 973520 |
1212 +===========================================================================*/
1213
1214 procedure COMPILE_RULE ( p_rule_set_id in ar_app_rule_sets.rule_set_id%TYPE) is
1215
1216
1217 prorate_line_gross varchar2(100) :='arpcurr.currround((l_amt * l_line_remaining / l_gross_remaining),:currency) ';
1218 prorate_freight_gross varchar2(100) :='arpcurr.currround((l_amt * l_freight_remaining / l_gross_remaining),:currency) ';
1219 prorate_charges_gross varchar2(100) :='arpcurr.currround((l_amt * l_charges_remaining / l_gross_remaining),:currency) ';
1220
1221 cursor rules is
1222 select rule_set_name
1223 from ar_app_rule_sets
1224 where rule_set_id = p_rule_set_id;
1225
1226 cursor all_application_blocks is
1227 select rule_name, rule_id,rule_sequence
1228 from ar_app_rules
1229 where rule_set_id = p_rule_set_id
1230 order by rule_sequence;
1231
1232 cursor application_block is
1233 select rule_name, rule_id,rule_sequence
1234 from ar_app_rules
1235 where rule_set_id = p_rule_set_id
1236 and overapp_flag = 'N'
1237 order by rule_sequence;
1238
1239 cursor over_application_block is
1240 select rule_name, rule_id,rule_sequence
1241 from ar_app_rules
1242 where rule_set_id = p_rule_set_id
1243 and overapp_flag = 'Y' ;
1244
1245 cursor all_block (p_rule_id in ar_app_rules.rule_id%TYPE) is
1246 select rule_detail_id , line_type , rounding_correction_flag , tax_treatment
1247 from ar_app_rule_details
1248 where rule_id = p_rule_id ;
1249
1250 cursor round_block (p_rule_id in ar_app_rules.rule_id%TYPE ) is
1251 select rule_detail_id , line_type , rounding_correction_flag , tax_treatment
1252 from ar_app_rule_details
1253 where rule_id = p_rule_id
1254 and rounding_correction_flag = 'Y';
1255
1256 cursor other_block (p_rule_id in ar_app_rules.rule_id%TYPE ) is
1257 select rule_detail_id , line_type , rounding_correction_flag , tax_treatment
1258 from ar_app_rule_details
1259 where rule_id = p_rule_id
1260 and rounding_correction_flag <> 'Y';
1261
1262 cursor over_app_lines (p_rule_id in number ) is
1263 select rule_detail_id , line_type , rounding_correction_flag , tax_treatment
1264 from ar_app_rule_details
1265 where rule_id = p_rule_id;
1266
1267
1268 l_line_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1269 l_freight_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1270 l_charges_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1271 o_line_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1272 o_freight_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1273 o_charges_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1274
1275 l_source long;
1276 l_round_minus long;
1277 l_counter number:=0;
1278 l_else_source long;
1279 l_doc_source long;
1280
1281 -- Error Checking Variables
1282 l_num_overappblock number :=0;
1283 l_num_appblock number :=0 ;
1284 l_num_linetype_line number:=0;
1285 l_num_linetype_freight number :=0;
1286 l_num_linetype_charges number :=0;
1287 l_num_round_error number:=0;
1288 l_num_sequence number:=0;
1289 l_prv_sequence_num number:=-99;
1290
1291 l_num_error_flag number := 0;
1292 l_temp_round number:=0;
1293
1294 begin
1295
1296 IF PG_DEBUG in ('Y', 'C') THEN
1297 arp_util.debug('ARP_APP_CALC_PKG.COMPILE_RULE()+');
1298 END IF;
1299
1300 for rulerec in rules loop
1301 l_doc_source := '/* Rule Set Name : ' || rulerec.rule_set_name ||'
1302 | Date Generated : ' || to_char(sysdate,'DD-MM-YYYY,HH24:MI') ;
1303 end loop;
1304
1305 /* 07-SEP-99 J Rautiainen Bugfix for bug 973520. Added boolean variable
1306 * l_force_exit_flag. The flag is used to prevent infinite loops */
1307 l_source := '
1308 l_amt ar_payment_schedules.amount_due_remaining%TYPE;
1309 l_line_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1310 l_freight_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1311 l_charges_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1312 l_gross_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1313 l_line_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1314 l_freight_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1315 l_charges_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1316 l_currency ar_payment_schedules.invoice_currency_code%TYPE;
1317 l_counter number := 0;
1318 l_force_exit_flag BOOLEAN := TRUE;
1319
1320 BEGIN l_amt := :amt ;
1321 l_line_remaining := :line_remaining ;
1322 l_freight_remaining := :freight_remaining ;
1323 l_charges_remaining := :charges_remaining ;
1324 l_currency := :currency;
1325 ';
1326
1327 for blocks in application_block loop
1328
1329 -- Check whether the data is correct
1330 l_num_appblock := l_num_appblock + 1;
1331
1332 l_doc_source := l_doc_source || '
1333 | ' || blocks.rule_sequence || '. ' || blocks.rule_name ;
1334
1335 for all_lines in all_block (blocks.rule_id) loop
1336 -- Data checking -- one rounding error per block
1337 l_counter := l_counter + 1;
1338
1339 l_doc_source := l_doc_source || '
1340 | ' || all_lines.line_type || ', Tax Treatment : ' || all_lines.tax_treatment ||
1341 ' , Rounding Correction : ' || all_lines.rounding_correction_flag ;
1342
1343
1344 if l_counter = 1 then
1345
1346 l_source := l_source || '
1347 l_gross_remaining := ';
1348
1349 if all_lines.line_type = 'LINE' then
1350 -- Data checking
1351 l_num_linetype_line := l_num_linetype_line + 1;
1352 --
1353 l_source := l_source || 'l_line_remaining';
1354 l_else_source := l_else_source || '
1355 l_line_applied := l_line_remaining ;
1356 ';
1357 l_line_tax_treatment := all_lines.tax_treatment ;
1358
1359 elsif all_lines.line_type = 'FREIGHT' then
1360 -- Data checking
1361 l_num_linetype_freight := l_num_linetype_freight + 1;
1362 --
1363
1364 l_source := l_source || 'l_freight_remaining';
1365 l_else_source := l_else_source || '
1366 l_freight_applied := l_freight_remaining ;
1367 ';
1368 l_freight_tax_treatment := all_lines.tax_treatment ;
1369 elsif all_lines.line_type = 'CHARGES' then
1370 -- Data checking
1371 l_num_linetype_charges := l_num_linetype_charges + 1;
1372 --
1373
1374 l_source := l_source || 'l_charges_remaining';
1375 l_else_source := l_else_source || '
1376 l_charges_applied := l_charges_remaining ;
1377 ';
1378 l_charges_tax_treatment := all_lines.tax_treatment ;
1379 end if;
1380 else
1381 if all_lines.line_type = 'LINE' then
1382 -- Data checking
1383 l_num_linetype_line := l_num_linetype_line + 1;
1384 --
1385
1386 l_source := l_source || ' + l_line_remaining ';
1387 l_else_source := l_else_source || '
1388 l_line_applied := l_line_remaining ;
1389 ';
1390 l_line_tax_treatment := all_lines.tax_treatment ;
1391 elsif all_lines.line_type = 'FREIGHT' then
1392 -- Data checking
1393 l_num_linetype_freight := l_num_linetype_freight + 1;
1394 --
1395
1396 l_source := l_source || ' + l_freight_remaining ';
1397 l_else_source := l_else_source || '
1398 l_freight_applied := l_freight_remaining ;
1399 ';
1400 l_freight_tax_treatment := all_lines.tax_treatment ;
1401 elsif all_lines.line_type = 'CHARGES' then
1402 -- Data checking
1403 l_num_linetype_charges := l_num_linetype_charges + 1;
1404 --
1405
1406 l_source := l_source || ' + l_charges_remaining ';
1407 l_else_source := l_else_source || '
1408 l_charges_applied := l_charges_remaining ;
1409 ';
1410 l_charges_tax_treatment := all_lines.tax_treatment ;
1411 end if;
1412 end if;
1413 end loop;
1414
1415 l_source := l_source || ' ; ';
1416
1417 l_source := l_source || '
1418 if (l_amt > 0) and (l_amt <= l_gross_remaining) then
1419 ' ;
1420
1421 for other_lines in other_block ( blocks.rule_id) loop
1422
1423 if other_lines.line_type = 'LINE' then
1424 l_source := l_source || '
1425 l_line_applied := ' || prorate_line_gross ||';
1426 ' ;
1427 l_round_minus := l_round_minus || ' - l_line_applied ';
1428 elsif other_lines.line_type = 'FREIGHT' then
1429 l_source := l_source || '
1430 l_freight_applied := ' || prorate_freight_gross || ';
1431 ' ;
1432 l_round_minus := l_round_minus || ' - l_freight_applied ';
1433 elsif other_lines.line_type = 'CHARGES' then
1434 l_source := l_source || '
1435 l_charges_applied := ' || prorate_charges_gross ||';
1436 ' ;
1437 l_round_minus := l_round_minus || ' - l_charges_applied ';
1438 end if;
1439
1440 end loop;
1441
1442 for round_lines in round_block (blocks.rule_id) loop
1443
1444 if round_lines.line_type = 'LINE' then
1445 l_source := l_source || '
1446 l_line_applied := l_amt ' || l_round_minus ||';
1447 ';
1448 elsif round_lines.line_type ='FREIGHT' then
1449 l_source := l_source || '
1450 l_freight_applied := l_amt ' || l_round_minus || ';
1451 ' ;
1452 elsif round_lines.line_type = 'CHARGES' then
1453 l_source := l_source || '
1454 l_charges_applied := l_amt ' || l_round_minus || ';
1455 ' ;
1456 end if;
1457
1458 l_source := l_source || '
1459 l_amt := 0 ;
1460 ';
1461
1462 end loop;
1463
1464 l_source := l_source || '
1465 elsif (l_amt > l_gross_remaining) then ' || l_else_source || '
1466 ';
1467 l_source := l_source || '
1468 l_amt := l_amt - l_gross_remaining ;
1469 ';
1470 l_source := l_source || '
1471 end if;
1472 ';
1473
1474 l_counter := 0;
1475 l_else_source := '';
1476 l_round_minus := '';
1477
1478 end loop;
1479
1480 for overapp in over_application_block loop
1481 -- Data checking
1482 l_num_overappblock := l_num_overappblock + 1;
1483 --
1484
1485 l_doc_source := l_doc_source || '
1486 | ' || ' Over Application Rule : ' || overapp.rule_name ;
1487
1488
1489 for all_lines in all_block (overapp.rule_id) loop
1490 l_counter := l_counter + 1;
1491
1492 l_doc_source := l_doc_source || '
1493 | ' || all_lines.line_type || ', Tax Treatment : ' || all_lines.tax_treatment ||
1494 ' , Rounding Correction : ' || all_lines.rounding_correction_flag ;
1495
1496 if l_counter = 1 then
1497
1498 l_source := l_source || '
1499 l_gross_remaining := ';
1500
1501 if all_lines.line_type = 'LINE' then
1502 l_source := l_source || '
1503 l_line_remaining';
1504 l_else_source := l_else_source || '
1505 l_line_applied := l_line_remaining ; ';
1506 o_line_tax_treatment := all_lines.tax_treatment ;
1507
1508 elsif all_lines.line_type = 'FREIGHT' then
1509 l_source := l_source || '
1510 l_freight_remaining';
1511 l_else_source := l_else_source || '
1512 l_freight_applied := l_freight_remaining ; ';
1513 o_freight_tax_treatment := all_lines.tax_treatment ;
1514 elsif all_lines.line_type = 'CHARGES' then
1515 l_source := l_source || '
1516 l_charges_remaining';
1517 l_else_source := l_else_source || '
1518 l_charges_applied := l_charges_remaining ; ';
1519 o_charges_tax_treatment := all_lines.tax_treatment ;
1520 end if;
1521 else
1522 if all_lines.line_type = 'LINE' then
1523 l_source := l_source || ' + l_line_remaining ';
1524 l_else_source := l_else_source || '
1525 l_line_applied := l_line_remaining ; ';
1526 o_line_tax_treatment := all_lines.tax_treatment ;
1527 elsif all_lines.line_type = 'FREIGHT' then
1528 l_source := l_source || ' + l_freight_remaining ';
1529 l_else_source := l_else_source || '
1530 l_freight_applied := l_freight_remaining ; ';
1531 o_freight_tax_treatment := all_lines.tax_treatment ;
1532 elsif all_lines.line_type = 'CHARGES' then
1533 l_source := l_source || ' + l_charges_remaining ';
1534 l_else_source := l_else_source || '
1535 l_charges_applied := l_charges_remaining ; ';
1536 o_charges_tax_treatment := all_lines.tax_treatment ;
1537 end if;
1538 end if;
1539 end loop;
1540 l_source := l_source || ';' ;
1541
1542 /* R Yeluri for bug fix 1105018. If a transaction which has a transaction
1543 * type 'Allow OverApplication' set to Yes, and if that transaction is overapplied
1544 * after it has been closed(meaning that the amount_due_remaining, line_remaining,
1545 * tax_remaining, freight_remaining and charges_remaining are all = 0), then the
1546 * following 'IF' condition for overapplication fails, because it checks to see
1547 * whether any of the line_remaining,freight_remaining,charges_remaining is > 0.
1548 * Consequently the statement l_line_applied := l_line_applied + l_amt is never
1549 * executed, and hence l_line_applied from which AMOUNT_LINE_ITEMS_REMAINING column
1550 * ar_payment_schedules is populated is 0. As a result updates in ar_payment_schedules
1551 * are incorrect.
1552 * Fix is to remove the condition 'and ((l_line_remaining >0) OR (l_freight_remaining >0)
1553 * OR (l_charges_remaining >0))' introduced as part of bug fix 840642, while retaining the
1554 * fix made for bug 973520. Such a fix would resolve all three bugs 840642, 973520 and 1105018.
1555 */
1556
1557 l_source := l_source || '
1558 if (l_amt > 0)then
1559 ' ;
1560
1561 /* 07-SEP-99 J Rautiainen Bugfix for bug 973520. Added boolean variable
1562 * l_force_exit_flag. The flag is used to prevent infinite loops.
1563 * Ie. if the overapplication is done on LINE, but the transaction against
1564 * which the application is made, doesn't have any lines (l_line_remaining = 0)
1565 * then the loop will never exit. */
1566
1567 l_source := l_source || '
1568 while l_amt > l_gross_remaining loop
1569 ';
1570 for over_app_rec in over_app_lines (overapp.rule_id) loop
1571
1572 if over_app_rec.line_type= 'LINE' then
1573
1574 l_source := l_source || '
1575 IF l_line_remaining > 0 THEN
1576 l_line_applied := l_line_applied + l_line_remaining ;
1577 l_amt := l_amt - l_line_remaining ;
1578 l_force_exit_flag := FALSE;
1579 END IF;
1580 ';
1581 elsif over_app_rec.line_type= 'FREIGHT' then
1582
1583 l_source := l_source || '
1584 IF l_freight_remaining > 0 THEN
1585 l_freight_applied := l_freight_applied + l_freight_remaining ;
1586 l_amt := l_amt - l_freight_remaining ;
1587 l_force_exit_flag := FALSE;
1588 END IF;
1589 ';
1590 elsif over_app_rec.line_type= 'CHARGES' then
1591
1592 l_source := l_source || '
1593 IF l_charges_remaining > 0 THEN
1594 l_charges_applied := l_charges_applied + l_charges_remaining ;
1595 l_amt := l_amt - l_charges_remaining ;
1596 l_force_exit_flag := FALSE;
1597 END IF;
1598 ';
1599
1600 end if;
1601 end loop;
1602
1603 /* 07-SEP-99 J Rautiainen Bugfix for bug 973520. Forcing exit in case of an
1604 * infinite loop */
1605 l_source := l_source || '
1606 IF l_force_exit_flag THEN
1607 EXIT;
1608 END IF;
1609 end loop;
1610 ';
1611 for other_lines in other_block ( overapp.rule_id) loop
1612
1613 if other_lines.line_type = 'LINE' then
1614 l_source := l_source || '
1615 l_line_applied := l_line_applied + ' || prorate_line_gross ||';
1616 ' ;
1617 l_round_minus := l_round_minus || ' - ' || prorate_line_gross ;
1618 elsif other_lines.line_type = 'FREIGHT' then
1619 l_source := l_source || '
1620 l_freight_applied := l_freight_applied + ' || prorate_freight_gross || ';
1621 ' ;
1622 l_round_minus := l_round_minus || ' - ' || prorate_freight_gross ;
1623 elsif other_lines.line_type = 'CHARGES' then
1624 l_source := l_source || '
1625 l_charges_applied := l_charges_applied + ' || prorate_charges_gross ||';
1626 ' ;
1627 l_round_minus := l_round_minus || ' - ' || prorate_charges_gross ;
1628 end if;
1629
1630 end loop;
1631
1632 for round_lines in round_block (overapp.rule_id) loop
1633
1634 if round_lines.line_type = 'LINE' then
1635 l_source := l_source || '
1636 l_line_applied := l_line_applied + l_amt ' || l_round_minus ||';
1637 ';
1638 elsif round_lines.line_type ='FREIGHT' then
1639 l_source := l_source || '
1640 l_freight_applied := l_freight_applied + l_amt ' || l_round_minus || ';
1641 ' ;
1642 elsif round_lines.line_type = 'CHARGES' then
1643 l_source := l_source || '
1644 l_charges_applied := l_charges_applied + l_amt ' || l_round_minus || ';
1645 ' ;
1646 end if;
1647
1648 l_source := l_source || '
1649 l_amt := 0 ;
1650 ';
1651
1652 l_source := l_source || '
1653 end if;
1654 ';
1655
1656
1657 end loop;
1658
1659 end loop;
1660
1661 l_source := l_source || '
1662 :line_applied := l_line_applied ;
1663 :freight_applied := l_freight_applied ;
1664 :charges_applied := l_charges_applied ;
1665
1666 :line_tax_treatment := ' || '''' || l_line_tax_treatment ||''''|| ' ;
1667 :freight_tax_treatment := '||'''' || l_freight_tax_treatment || ''''||' ;
1668 :charges_tax_treatment := '||'''' || l_charges_tax_treatment ||''''|| ' ;
1669 :o_line_tax_treatment := ' || '''' || o_line_tax_treatment ||''''|| ' ;
1670 :o_freight_tax_treatment := '||'''' || o_freight_tax_treatment || ''''||' ;
1671 :o_charges_tax_treatment := '||'''' || o_charges_tax_treatment ||''''|| ' ; ' ;
1672
1673 l_source := 'DECLARE ' || l_doc_source || ' */' || '
1674 ' || l_source ;
1675
1676 l_source := l_source || '
1677 END ; ';
1678
1679 -- Checking whether every application rule one and only one time rounding
1680 -- error flag checked.
1681
1682 for blocks_rec in all_application_blocks loop
1683 if l_prv_sequence_num = nvl(blocks_rec.rule_sequence,-98) then
1684 l_num_sequence := l_num_sequence + 1;
1685 -- l_num_sequence cannot be bigger than 0
1686 end if;
1687 for lines_rec in all_block(blocks_rec.rule_id) loop
1688 if lines_rec.rounding_correction_flag = 'Y' then
1689 l_temp_round := l_temp_round + 1;
1690 end if;
1691 end loop;
1692 if l_temp_round <> 1 then
1693 l_num_round_error := 2;
1694 end if;
1695 l_temp_round :=0;
1696 l_prv_sequence_num := nvl(blocks_rec.rule_sequence,-97) ;
1697 end loop;
1698
1699 if ( (l_num_overappblock = 1)
1700 AND (l_num_appblock >= 1)
1701 AND (l_num_linetype_line = 1)
1702 AND (l_num_linetype_freight = 1)
1703 AND (l_num_linetype_charges = 1)
1704 AND (l_num_sequence = 0)
1705 AND (l_num_round_error <> 2)
1706 ) then
1707
1708
1709 update ar_app_rule_sets
1710 set rule_source = l_source
1711 , last_updated_by = fnd_global.user_id
1712 , last_update_date = sysdate
1713 , last_update_login = fnd_global.login_id
1714 where rule_set_id = p_rule_set_id;
1715 else
1716 -- Error one of the data check rules is not followed the user has to update
1717 -- data before the rule can be frozen.
1718 fnd_message.set_name('AR','AR_INVALID_FREEZE_DATA');
1719 app_exception.raise_exception;
1720
1721 end if;
1722
1723 IF PG_DEBUG in ('Y', 'C') THEN
1724 arp_util.debug('ARP_APP_CALC_PKG.COMPILE_RULE()-');
1725 END IF;
1726
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 IF PG_DEBUG in ('Y', 'C') THEN
1730 arp_util.debug('EXCEPTION: ARP_APP_CALC_PKG.compile_rule'||SQLERRM);
1731 END IF;
1732 RAISE;
1733
1734 end COMPILE_RULE;
1735
1736 end ARP_APP_CALC_PKG;