[Home] [Help]
PACKAGE BODY: APPS.ARP_APP_CALC_PKG
Source
1 package body ARP_APP_CALC_PKG as
2 /* $Header: ARAPPRUB.pls 120.6 2006/06/02 23:16:06 kmaheswa 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 ,line_remaining
547 ,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 /* Amounts applied during mixed sign treatment */
743 l_ms_applied_line ar_receivable_applications.tax_applied%TYPE:=0;
744 l_ms_applied_line_tax ar_receivable_applications.tax_applied%TYPE:=0;
745 l_ms_applied_freight ar_receivable_applications.tax_applied%TYPE:=0;
746 l_ms_applied_freight_tax ar_receivable_applications.tax_applied%TYPE:=0;
747 l_ms_applied_charges ar_receivable_applications.tax_applied%TYPE:=0;
748 l_ms_applied_charges_tax ar_receivable_applications.tax_applied%TYPE:=0;
749
750 /* Amounts applied during non-mixed sign treatment */
751 l_nonms_applied_line ar_receivable_applications.tax_applied%TYPE:=0;
752 l_nonms_applied_line_tax ar_receivable_applications.tax_applied%TYPE:=0;
753 l_nonms_applied_freight ar_receivable_applications.tax_applied%TYPE:=0;
754 l_nonms_applied_freight_tax ar_receivable_applications.tax_applied%TYPE:=0;
755 l_nonms_applied_charges ar_receivable_applications.tax_applied%TYPE:=0;
756 l_nonms_applied_charges_tax ar_receivable_applications.tax_applied%TYPE:=0;
757
758 /* To hold original values */
759 l_org_line_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
760 l_org_line_tax_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
761 l_org_fr_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
762 l_org_fr_tax_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
763 l_org_ch_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
764 l_org_ch_tax_remaining ar_payment_schedules.tax_remaining%TYPE:=0;
765
766 /*
767 * Currently FREIGHT_TAX and CHARGES_TAX are not treated, so they
768 * appear as local variables.
769 */
770 l_freight_tax_remaining number:=0;
771 l_charges_tax_remaining number:=0;
772 l_freight_tax_applied number:=0;
773 l_charges_tax_applied number:=0;
774
775 BEGIN
776 arp_standard.debug('ARP_APP_CALC_PKG.calc_applied_and_remaining() Wrapper +');
777
778 /*
779 * gjayanth: Bug 772847: When CM is applied to CB, payment
780 * schedule's line_remaining was not getting updated correctly.
781 * Assigning zero to null amount parameters.
782 */
783 p_line_remaining := nvl(p_line_remaining, 0);
784 p_line_tax_remaining := nvl(p_line_tax_remaining, 0);
785 p_freight_remaining := nvl(p_freight_remaining, 0);
786 p_charges_remaining := nvl(p_charges_remaining, 0);
787
788 arp_standard.debug('in calc_applied_and_remaining Wrapper, debug');
789 arp_standard.debug(' .. p_created_from = ' || p_created_from);
790 arp_standard.debug(' .. p_line_remaining = ' || to_char(p_line_remaining));
791 arp_standard.debug(' .. p_line_tax_remaining = ' || to_char(p_line_tax_remaining));
792 arp_standard.debug(' .. p_freight_remaining = ' || to_char(p_freight_remaining));
793 arp_standard.debug(' .. p_charges_remaining = ' || to_char(p_charges_remaining));
794 arp_standard.debug(' .. p_amt = ' || to_char(p_amt));
795
796 /* Save original values */
797 l_org_line_remaining := p_line_remaining;
798 l_org_line_tax_remaining := p_line_tax_remaining;
799 l_org_fr_remaining := p_freight_remaining;
800 l_org_fr_tax_remaining := l_freight_tax_remaining;
801 l_org_ch_remaining := p_charges_remaining;
802 l_org_ch_tax_remaining := l_charges_tax_remaining;
803
804 l_amt_remaining := p_amt;
805 /*
806 * First find out NOCOPY which components have signs different
807 * from that of 'amt'. Amounts with same signs are treated
808 * first, unless all happen to have same sign or all are
809 * different, in which case, we do not have a case of 'mixed-sign'
810 * balance.
811
812 * Bug 2389772 : changed relational operator below from < to <=
813 * so that when p_amt = 0, it is *not* treated as a mixed sign
814 * application
815 *
816 */
817
818 IF nvl(p_created_from,'XXX') = 'ARBRMAIB' then
819
820 -- Bug 4758340, if called from BR
821 -- re-init all to 0, and set to 1 when they are opposite signs
822 -- apply to OPPOSITE sign first
823
824 l_ms_use_line := 0;
825 l_ms_use_line_tax := 0;
826 l_ms_use_freight := 0;
827 l_ms_use_freight_tax := 0;
828 l_ms_use_charges := 0;
829 l_ms_use_charges_tax := 0;
830
831 IF sign(p_amt) * sign(p_line_remaining) <= 0 THEN
832 l_ms_use_line := 1;
833 END IF;
834
835 if sign(p_amt) * sign(p_line_tax_remaining) <= 0 THEN
836 l_ms_use_line_tax := 1;
837 END IF;
838
839 IF sign(p_amt) * sign (p_freight_remaining) <= 0 THEN
840 l_ms_use_freight := 1;
841 END IF;
842
843
844 IF sign(p_amt) * sign(l_freight_tax_remaining) <= 0 THEN
845 l_ms_use_freight_tax := 1;
846 END IF;
847
848 IF sign(p_amt) * sign(p_charges_remaining) <= 0 THEN
849 l_ms_use_charges := 1;
850 END IF;
851
852 IF sign(p_amt) * sign(l_charges_tax_remaining) <= 0 THEN
853 l_ms_use_charges_tax := 1;
854 END IF;
855
856 ELSE
857
858 -- '1' indicates, amount is NOT a candidate for mixed-sign treatment
859 -- re-init all to 1, and set to 0 when they are opposite signs
860 -- apply to SAME sign first
861
862 l_ms_use_line := 1;
863 l_ms_use_line_tax := 1;
864 l_ms_use_freight := 1;
865 l_ms_use_freight_tax := 1;
866 l_ms_use_charges := 1;
867 l_ms_use_charges_tax := 1;
868
869 -- Forward Port of Bug 4487954 - See Bug 4592507
870 -- reverted the check below from <= to < since
871 -- amounts were not getting prorated between line and tax when
872 -- prorate all application rule set is used
873
874
875 IF sign(p_amt) * sign(p_line_remaining) < 0 THEN
876 l_ms_use_line := 0;
877 END IF;
878
879 if sign(p_amt) * sign(p_line_tax_remaining) < 0 THEN
880 l_ms_use_line_tax := 0;
881 END IF;
882
883 IF sign(p_amt) * sign (p_freight_remaining) < 0 THEN
884 l_ms_use_freight := 0;
885 END IF;
886
887 IF sign(p_amt) * sign(l_freight_tax_remaining) < 0 THEN
888 l_ms_use_freight_tax := 0;
889 END IF;
890
891 IF sign(p_amt) * sign(p_charges_remaining) < 0 THEN
892 l_ms_use_charges := 0;
893 END IF;
894
895 IF sign(p_amt) * sign(l_charges_tax_remaining) < 0 THEN
896 l_ms_use_charges_tax := 0;
897 END IF;
898
899
900 END IF;
901
902 l_mixed_sign_count := l_ms_use_line + l_ms_use_line_tax +
903 l_ms_use_freight + l_ms_use_freight_tax +
904 l_ms_use_charges + l_ms_use_charges_tax;
905
906 arp_standard.debug(' l_mixed_sign_count = ' || to_char(l_mixed_sign_count));
907 arp_standard.debug(' l_ms_use_line = ' || to_char(l_ms_use_line) ||
908 ' l_ms_use_line_tax = ' || to_char(l_ms_use_line_tax));
909 arp_standard.debug(' l_ms_use_freight = ' || to_char(l_ms_use_freight) ||
910 ' l_ms_use_freight_tax = ' || to_char(l_ms_use_freight_tax));
911 arp_standard.debug(' l_ms_use_charges = ' || to_char(l_ms_use_charges) ||
912 ' l_ms_use_charges_tax = ' || to_char(l_ms_use_charges_tax));
913
914 /*
915 * If all amounts had sign same as p_amt, l_mixed_sign_count = 6.
916 * If all amounts had sign opposite to that of p_amt, l_mixed_sign_count = 0.
917 * If l_mixed_sign_count is between 0 and 6, amounts have a mixed-sign,
918 * and are treated differently.
919 */
920 IF (l_mixed_sign_count > 0) and (l_mixed_sign_count < l_no_of_balance_components) THEN
921 /*
922 * MIXED-SIGN TREATMENT
923 *
924 * Temporarily zero out NOCOPY components with sign opposite to that of 'amt',
925 * so that 'amt' may be applied to the same-sign amounts first.
926 */
927 arp_util.debug('Treating invoice components that have mixed signs.');
928 arp_util.debug('p_amt = ' || to_char(p_amt));
929
930 p_line_remaining := l_ms_use_line * p_line_remaining;
931 p_line_tax_remaining := l_ms_use_line_tax * p_line_tax_remaining;
932 p_freight_remaining := l_ms_use_freight * p_freight_remaining;
933 l_freight_tax_remaining := l_ms_use_freight_tax * l_freight_tax_remaining;
934 p_charges_remaining := l_ms_use_charges * p_charges_remaining;
935 l_charges_tax_remaining := l_ms_use_charges_tax * l_charges_tax_remaining;
936
937 /* Find the gross of same-sign amounts */
938 l_ms_gross_remaining := p_line_remaining + p_line_tax_remaining +
939 p_freight_remaining + l_freight_tax_remaining +
940 p_charges_remaining + l_charges_tax_remaining;
941
942 IF (abs(p_amt) <= abs(l_ms_gross_remaining)) THEN
943 l_ms_applied_amt := p_amt;
944 ELSE
945 l_ms_applied_amt := l_ms_gross_remaining;
946 END IF;
947
948 arp_util.debug('1. call calc_applied_and_remaining with params : ');
949 arp_util.debug(' l_ms_applied_amt = ' || to_char(l_ms_applied_amt));
950 arp_util.debug(' p_rule_set_id = ' || to_char(p_rule_set_id));
951 arp_util.debug(' p_line_remaining = ' || to_char(p_line_remaining));
952 arp_util.debug(' p_line_tax_remaining = ' || to_char(p_line_tax_remaining));
953 arp_util.debug(' p_freight_remaining = ' || to_char(p_freight_remaining));
954 arp_util.debug(' l_freight_tax_remaining = ' || to_char(l_freight_tax_remaining));
955 arp_util.debug(' p_charges_remaining = ' || to_char(p_charges_remaining));
956 arp_util.debug(' l_charges_tax_remaining = ' || to_char(l_charges_tax_remaining));
957
958 calc_applied_and_remaining ( l_ms_applied_amt
959 ,p_rule_set_id
960 ,p_currency
961 ,p_line_remaining
962 ,p_line_tax_remaining
963 ,p_freight_remaining
964 ,l_freight_tax_remaining
965 ,p_charges_remaining
966 ,l_charges_tax_remaining
967 ,l_ms_applied_line
968 ,l_ms_applied_line_tax
969 ,l_ms_applied_freight
970 ,l_ms_applied_freight_tax
971 ,l_ms_applied_charges
972 ,l_ms_applied_charges_tax ) ;
973
974 /* Amount remaining for overapplication */
975 l_amt_remaining := sign(p_amt) * ( abs(p_amt) - abs(l_ms_applied_amt) );
976
977 /*
978 * Restore opp-sign values so they can be treated now.
979 */
980 IF (l_ms_use_line = 0) THEN
981 p_line_remaining := l_org_line_remaining;
982 END IF;
983
984 IF (l_ms_use_line_tax = 0) THEN
985 p_line_tax_remaining := l_org_line_tax_remaining;
986 END IF;
987
988 IF (l_ms_use_freight = 0) THEN
989 p_freight_remaining := l_org_fr_remaining;
990 END IF;
991
992 IF (l_ms_use_freight_tax = 0) THEN
993 l_freight_tax_remaining := l_org_fr_tax_remaining;
994 END IF;
995
996 IF (l_ms_use_charges = 0) THEN
997 p_charges_remaining := l_org_ch_remaining;
998 END IF;
999
1000 IF (l_ms_use_charges_tax = 0) THEN
1001 l_charges_tax_remaining := l_org_ch_tax_remaining;
1002 END IF;
1003
1004 END IF; /* MIXED-SIGN TREATMENT */
1005
1006
1007 /*
1008 * After mixed sign balances (if any) are treated, balances are now of the
1009 * same sign. This is because those components that had same sign as amt,
1010 * have been reduced to zero by the previous calc_applied_and_remaining().
1011 * If any amt is left, apply.
1012 */
1013
1014 IF ( abs(l_amt_remaining) > 0 ) THEN
1015
1016 /*
1017 * Still some amt left. Apply as usual.
1018 */
1019
1020 arp_util.debug('Treating invoice components that have the same sign.');
1021
1022 arp_util.debug('2. call calc_applied_and_remaining with params : ');
1023 arp_util.debug(' l_amt_remaining = ' || to_char(l_amt_remaining));
1024 arp_util.debug(' p_rule_set_id = ' || to_char(p_rule_set_id));
1025 arp_util.debug(' p_line_remaining = ' || to_char(p_line_remaining));
1026 arp_util.debug(' p_line_tax_remaining = ' || to_char(p_line_tax_remaining));
1027 arp_util.debug(' p_freight_remaining = ' || to_char(p_freight_remaining));
1028 arp_util.debug(' l_freight_tax_remaining = ' || to_char(l_freight_tax_remaining));
1029 arp_util.debug(' p_charges_remaining = ' || to_char(p_charges_remaining));
1030 arp_util.debug(' l_charges_tax_remaining = ' || to_char(l_charges_tax_remaining));
1031
1032 calc_applied_and_remaining ( l_amt_remaining
1033 ,p_rule_set_id
1034 ,p_currency
1035 ,p_line_remaining
1036 ,p_line_tax_remaining
1037 ,p_freight_remaining
1038 ,l_freight_tax_remaining
1039 ,p_charges_remaining
1040 ,l_charges_tax_remaining
1041 ,l_nonms_applied_line
1042 ,l_nonms_applied_line_tax
1043 ,l_nonms_applied_freight
1044 ,l_nonms_applied_freight_tax
1045 ,l_nonms_applied_charges
1046 ,l_nonms_applied_charges_tax ) ;
1047
1048 END IF;
1049
1050 p_line_applied := l_ms_applied_line + l_nonms_applied_line ;
1051 p_line_tax_applied := l_ms_applied_line_tax + l_nonms_applied_line_tax ;
1052 p_freight_applied := l_ms_applied_freight + l_nonms_applied_freight ;
1053 l_freight_tax_applied := l_ms_applied_freight_tax + l_nonms_applied_freight_tax ;
1054 p_charges_applied := l_ms_applied_charges + l_nonms_applied_charges ;
1055 l_charges_tax_applied := l_ms_applied_charges_tax + l_nonms_applied_charges_tax ;
1056
1057 arp_standard.debug('done calc_applied_and_remaining Wrapper, debug');
1058 arp_standard.debug(' .. p_line_applied = ' || to_char(l_ms_applied_line + l_nonms_applied_line));
1059 arp_standard.debug(' .. p_line_tax_applied = ' || to_char(l_ms_applied_line_tax + l_nonms_applied_line_tax));
1060 arp_standard.debug(' .. p_freight_applied = ' || to_char(l_ms_applied_freight + l_nonms_applied_freight));
1061 arp_standard.debug(' .. l_freight_tax_applied = ' || to_char(l_ms_applied_freight_tax + l_nonms_applied_freight_tax));
1062 arp_standard.debug(' .. p_charges_applied = ' || to_char(l_ms_applied_charges + l_nonms_applied_charges));
1063 arp_standard.debug(' .. l_charges_tax_applied = ' || to_char(l_ms_applied_charges_tax + l_nonms_applied_charges_tax));
1064
1065 arp_standard.debug('calc_applied_and_remaining() Wrapper -');
1066
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 arp_util.debug('EXCEPTION: ARP_APP_CALC_PKG.calc_applied_and_remaining: Wrapper: '||SQLERRM);
1070 RAISE;
1071
1072 END;
1073
1074
1075 /*===========================================================================+
1076 | PROCEDURE |
1077 | COMPILE_RULE () |
1078 | DESCRIPTION |
1079 | This procedure will create a long column that will be |
1080 | inserted into |
1081 | the ar_app_rule_sets table with the according rule |
1082 | This compilation makes it possible for the calc_applied_and_remaining |
1083 | procedure not to select multiple times from app_rule... tables |
1084 | |
1085 | This procedure should be called from the application rules set up form |
1086 | from the post_update trigger when the freeze flag gets set to 'Y' |
1087 | |
1088 | Whenever a rule will be frozen a compiled rule will be created and stored|
1089 | in the long column rule_source. |
1090 | |
1091 | Before creating the long column the procedure will check whether |
1092 | the rule is valid. Following checks will be made : |
1093 | 1. Is there one and only one Over Application Rule |
1094 | 2. Are there one or more non-overapplication Rules |
1095 | 3. Is every Line type present in one of the non-overapplication rules |
1096 | 4. Has one and only one of the application rule details in every |
1097 | application rule the rounding correction checked |
1098 | 5. Are the sequence numbers of the application rules different |
1099 | |
1100 | |
1101 | SCOPE -- Public -- To be called from the application rules set up form |
1102 | PARAMETERS |
1103 | IN -- rule_id -- This is the id from the rule you want to compile. |
1104 | RULE_SOURCE (Example Code )
1105 | -----------
1106 | DECLARE Rule Name : Pro Ratio
1107 | Date Generated : 20-MAR-1997,15:15
1108 | 1. Pro Ratio Rule
1109 | LINE, PRORATE , Rounding Correction : Y
1110 | FREIGHT, NONE , Rounding Correction : N
1111 | CHARGES, NONE , Rounding Correction : N
1112 | Over Application Rule : Pro Ratio Over App
1113 | LINE, PRORATE , Rounding Correction : Y
1114 | FREIGHT, NONE , Rounding Correction : N
1115 | CHARGES, NONE , Rounding Correction : N
1116 |
1117 | l_amt ar_payment_schedules.amount_due_remaining%TYPE;
1118 | l_line_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1119 | l_freight_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1120 | l_charges_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1121 | l_gross_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1122 | l_line_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1123 | l_freight_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1124 | l_charges_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1125 | l_counter number := 0;
1126 | BEGIN l_amt := :amt ;
1127 | l_line_remaining := :line_remaining ;
1128 | l_freight_remaining := :freight_remaining ;
1129 | l_charges_remaining := :charges_remaining ;
1130 |
1131 | l_gross_remaining := l_line_remaining + l_freight_remaining + l_charges_rema
1132 | ining ;
1133 | if (l_amt > 0) and (l_amt < l_gross_remaining) then
1134 |
1135 | l_freight_applied := arpcurr.currround((l_amt * l_freight_remaining /
1136 | l_gross_remaining),:currency) ;
1137 |
1138 | l_charges_applied := arpcurr.currround((l_amt * l_charges_remaining /
1139 | l_gross_remaining),:currency) ;
1140 |
1141 | l_line_applied := l_amt - l_freight_applied - l_charges_applied ;
1142 |
1143 | l_amt := 0 ;
1144 |
1145 | elsif (l_amt > l_gross_remaining) then
1146 | l_line_applied := l_line_remaining ;
1147 |
1148 | l_freight_applied := l_freight_remaining ;
1149 | l_charges_applied := l_charges_remaining ;
1150 |
1151 |
1152 | l_amt := l_amt - l_gross_remaining ;
1153 |
1154 | end if;
1155 |
1156 | l_gross_remaining :=
1157 | l_line_remaining + l_freight_remaining + l_charges_remaining ;
1158 | if (l_amt > 0) then
1159 |
1160 | while l_amt > l_gross_remaining loop
1161 |
1162 | l_line_applied := l_line_applied + l_line_remaining ;
1163 | l_amt := l_amt - l_line_remaining ;
1164 |
1165 | l_freight_applied := l_freight_applied + l_freight_remaining ;
1166 | l_amt := l_amt - l_freight_remaining ;
1167 |
1168 | l_charges_applied := l_charges_applied + l_charges_remaining ;
1169 | l_amt := l_amt - l_charges_remaining ;
1170 |
1171 | end loop;
1172 |
1173 | l_freight_applied := l_freight_applied + arpcurr.currround((l_amt * l
1174 | _freight_remaining / l_gross_remaining),:currency) ;
1175 |
1176 | l_charges_applied := l_charges_applied + arpcurr.currround((l_amt * l_
1177 | charges_remaining / l_gross_remaining),:currency) ;
1178 |
1179 | l_line_applied := l_line_applied + l_amt - arpcurr.currround((l_amt *
1180 | l_freight_remaining / l_gross_remaining),:currency) - arpcurr.currround((l_amt
1181 | * l_charges_remaining / l_gross_remaining),:currency) ;
1182 |
1183 | l_amt := 0 ;
1184 |
1185 | end if;
1186 |
1187 | :line_applied := l_line_applied ;
1188 | :freight_applied := l_freight_applied ;
1189 | :charges_applied := l_charges_applied ;
1190 | :line_tax_treatment := 'PRORATE' ;
1191 | :freight_tax_treatment := 'NONE' ;
1192 | :charges_tax_treatment := 'NONE' ;
1193 |
1194 | :o_line_tax_treatment := 'PRORATE' ;
1195 | | :o_freight_tax_treatment := 'NONE' ;
1196 | :o_charges_tax_treatment := 'NONE' ;
1197 | END ;
1198 | |
1199 | MODIFICATION HISTORY |
1200 | 03-11-97 -- Joan Zaman -- Created |
1201 | 07-SEP-99 J Rautiainen Bugfix for bug 973520 |
1202 +===========================================================================*/
1203
1204 procedure COMPILE_RULE ( p_rule_set_id in ar_app_rule_sets.rule_set_id%TYPE) is
1205
1206
1207 prorate_line_gross varchar2(100) :='arpcurr.currround((l_amt * l_line_remaining / l_gross_remaining),:currency) ';
1208 prorate_freight_gross varchar2(100) :='arpcurr.currround((l_amt * l_freight_remaining / l_gross_remaining),:currency) ';
1209 prorate_charges_gross varchar2(100) :='arpcurr.currround((l_amt * l_charges_remaining / l_gross_remaining),:currency) ';
1210
1211 cursor rules is
1212 select rule_set_name
1213 from ar_app_rule_sets
1214 where rule_set_id = p_rule_set_id;
1215
1216 cursor all_application_blocks is
1217 select rule_name, rule_id,rule_sequence
1218 from ar_app_rules
1219 where rule_set_id = p_rule_set_id
1220 order by rule_sequence;
1221
1222 cursor application_block is
1223 select rule_name, rule_id,rule_sequence
1224 from ar_app_rules
1225 where rule_set_id = p_rule_set_id
1226 and overapp_flag = 'N'
1227 order by rule_sequence;
1228
1229 cursor over_application_block is
1230 select rule_name, rule_id,rule_sequence
1231 from ar_app_rules
1232 where rule_set_id = p_rule_set_id
1233 and overapp_flag = 'Y' ;
1234
1235 cursor all_block (p_rule_id in ar_app_rules.rule_id%TYPE) is
1236 select rule_detail_id , line_type , rounding_correction_flag , tax_treatment
1237 from ar_app_rule_details
1238 where rule_id = p_rule_id ;
1239
1240 cursor round_block (p_rule_id in ar_app_rules.rule_id%TYPE ) is
1241 select rule_detail_id , line_type , rounding_correction_flag , tax_treatment
1242 from ar_app_rule_details
1243 where rule_id = p_rule_id
1244 and rounding_correction_flag = 'Y';
1245
1246 cursor other_block (p_rule_id in ar_app_rules.rule_id%TYPE ) is
1247 select rule_detail_id , line_type , rounding_correction_flag , tax_treatment
1248 from ar_app_rule_details
1249 where rule_id = p_rule_id
1250 and rounding_correction_flag <> 'Y';
1251
1252 cursor over_app_lines (p_rule_id in number ) is
1253 select rule_detail_id , line_type , rounding_correction_flag , tax_treatment
1254 from ar_app_rule_details
1255 where rule_id = p_rule_id;
1256
1257
1258 l_line_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1259 l_freight_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1260 l_charges_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1261 o_line_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1262 o_freight_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1263 o_charges_tax_treatment ar_app_rule_details.tax_treatment%TYPE:='NONE';
1264
1265 l_source long;
1266 l_round_minus long;
1267 l_counter number:=0;
1268 l_else_source long;
1269 l_doc_source long;
1270
1271 -- Error Checking Variables
1272 l_num_overappblock number :=0;
1273 l_num_appblock number :=0 ;
1274 l_num_linetype_line number:=0;
1275 l_num_linetype_freight number :=0;
1276 l_num_linetype_charges number :=0;
1277 l_num_round_error number:=0;
1278 l_num_sequence number:=0;
1279 l_prv_sequence_num number:=-99;
1280
1281 l_num_error_flag number := 0;
1282 l_temp_round number:=0;
1283
1284 begin
1285
1286 IF PG_DEBUG in ('Y', 'C') THEN
1287 arp_standard.debug('ARP_APP_CALC_PKG.COMPILE_RULE()+');
1288 END IF;
1289
1290 for rulerec in rules loop
1291 l_doc_source := '/* Rule Set Name : ' || rulerec.rule_set_name ||'
1292 | Date Generated : ' || to_char(sysdate,'DD-MM-YYYY,HH24:MI') ;
1293 end loop;
1294
1295 /* 07-SEP-99 J Rautiainen Bugfix for bug 973520. Added boolean variable
1296 * l_force_exit_flag. The flag is used to prevent infinite loops */
1297 l_source := '
1298 l_amt ar_payment_schedules.amount_due_remaining%TYPE;
1299 l_line_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1300 l_freight_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1301 l_charges_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1302 l_gross_remaining ar_payment_schedules.amount_due_remaining%TYPE;
1303 l_line_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1304 l_freight_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1305 l_charges_applied ar_payment_schedules.amount_due_remaining%TYPE:=0;
1306 l_currency ar_payment_schedules.invoice_currency_code%TYPE;
1307 l_counter number := 0;
1308 l_force_exit_flag BOOLEAN := TRUE;
1309
1310 BEGIN l_amt := :amt ;
1311 l_line_remaining := :line_remaining ;
1312 l_freight_remaining := :freight_remaining ;
1313 l_charges_remaining := :charges_remaining ;
1314 l_currency := :currency;
1315 ';
1316
1317 for blocks in application_block loop
1318
1319 -- Check whether the data is correct
1320 l_num_appblock := l_num_appblock + 1;
1321
1322 l_doc_source := l_doc_source || '
1323 | ' || blocks.rule_sequence || '. ' || blocks.rule_name ;
1324
1325 for all_lines in all_block (blocks.rule_id) loop
1326 -- Data checking -- one rounding error per block
1327 l_counter := l_counter + 1;
1328
1329 l_doc_source := l_doc_source || '
1330 | ' || all_lines.line_type || ', Tax Treatment : ' || all_lines.tax_treatment ||
1331 ' , Rounding Correction : ' || all_lines.rounding_correction_flag ;
1332
1333
1334 if l_counter = 1 then
1335
1336 l_source := l_source || '
1337 l_gross_remaining := ';
1338
1339 if all_lines.line_type = 'LINE' then
1340 -- Data checking
1341 l_num_linetype_line := l_num_linetype_line + 1;
1342 --
1343 l_source := l_source || 'l_line_remaining';
1344 l_else_source := l_else_source || '
1345 l_line_applied := l_line_remaining ;
1346 ';
1347 l_line_tax_treatment := all_lines.tax_treatment ;
1348
1349 elsif all_lines.line_type = 'FREIGHT' then
1350 -- Data checking
1351 l_num_linetype_freight := l_num_linetype_freight + 1;
1352 --
1353
1354 l_source := l_source || 'l_freight_remaining';
1355 l_else_source := l_else_source || '
1356 l_freight_applied := l_freight_remaining ;
1357 ';
1358 l_freight_tax_treatment := all_lines.tax_treatment ;
1359 elsif all_lines.line_type = 'CHARGES' then
1360 -- Data checking
1361 l_num_linetype_charges := l_num_linetype_charges + 1;
1362 --
1363
1364 l_source := l_source || 'l_charges_remaining';
1365 l_else_source := l_else_source || '
1366 l_charges_applied := l_charges_remaining ;
1367 ';
1368 l_charges_tax_treatment := all_lines.tax_treatment ;
1369 end if;
1370 else
1371 if all_lines.line_type = 'LINE' then
1372 -- Data checking
1373 l_num_linetype_line := l_num_linetype_line + 1;
1374 --
1375
1376 l_source := l_source || ' + l_line_remaining ';
1377 l_else_source := l_else_source || '
1378 l_line_applied := l_line_remaining ;
1379 ';
1380 l_line_tax_treatment := all_lines.tax_treatment ;
1381 elsif all_lines.line_type = 'FREIGHT' then
1382 -- Data checking
1383 l_num_linetype_freight := l_num_linetype_freight + 1;
1384 --
1385
1386 l_source := l_source || ' + l_freight_remaining ';
1387 l_else_source := l_else_source || '
1388 l_freight_applied := l_freight_remaining ;
1389 ';
1390 l_freight_tax_treatment := all_lines.tax_treatment ;
1391 elsif all_lines.line_type = 'CHARGES' then
1392 -- Data checking
1393 l_num_linetype_charges := l_num_linetype_charges + 1;
1394 --
1395
1396 l_source := l_source || ' + l_charges_remaining ';
1397 l_else_source := l_else_source || '
1398 l_charges_applied := l_charges_remaining ;
1399 ';
1400 l_charges_tax_treatment := all_lines.tax_treatment ;
1401 end if;
1402 end if;
1403 end loop;
1404
1405 l_source := l_source || ' ; ';
1406
1407 l_source := l_source || '
1408 if (l_amt > 0) and (l_amt <= l_gross_remaining) then
1409 ' ;
1410
1411 for other_lines in other_block ( blocks.rule_id) loop
1412
1413 if other_lines.line_type = 'LINE' then
1414 l_source := l_source || '
1415 l_line_applied := ' || prorate_line_gross ||';
1416 ' ;
1417 l_round_minus := l_round_minus || ' - l_line_applied ';
1418 elsif other_lines.line_type = 'FREIGHT' then
1419 l_source := l_source || '
1420 l_freight_applied := ' || prorate_freight_gross || ';
1421 ' ;
1422 l_round_minus := l_round_minus || ' - l_freight_applied ';
1423 elsif other_lines.line_type = 'CHARGES' then
1424 l_source := l_source || '
1425 l_charges_applied := ' || prorate_charges_gross ||';
1426 ' ;
1427 l_round_minus := l_round_minus || ' - l_charges_applied ';
1428 end if;
1429
1430 end loop;
1431
1432 for round_lines in round_block (blocks.rule_id) loop
1433
1434 if round_lines.line_type = 'LINE' then
1435 l_source := l_source || '
1436 l_line_applied := l_amt ' || l_round_minus ||';
1437 ';
1438 elsif round_lines.line_type ='FREIGHT' then
1439 l_source := l_source || '
1440 l_freight_applied := l_amt ' || l_round_minus || ';
1441 ' ;
1442 elsif round_lines.line_type = 'CHARGES' then
1443 l_source := l_source || '
1444 l_charges_applied := l_amt ' || l_round_minus || ';
1445 ' ;
1446 end if;
1447
1448 l_source := l_source || '
1449 l_amt := 0 ;
1450 ';
1451
1452 end loop;
1453
1454 l_source := l_source || '
1455 elsif (l_amt > l_gross_remaining) then ' || l_else_source || '
1456 ';
1457 l_source := l_source || '
1458 l_amt := l_amt - l_gross_remaining ;
1459 ';
1460 l_source := l_source || '
1461 end if;
1462 ';
1463
1464 l_counter := 0;
1465 l_else_source := '';
1466 l_round_minus := '';
1467
1468 end loop;
1469
1470 for overapp in over_application_block loop
1471 -- Data checking
1472 l_num_overappblock := l_num_overappblock + 1;
1473 --
1474
1475 l_doc_source := l_doc_source || '
1476 | ' || ' Over Application Rule : ' || overapp.rule_name ;
1477
1478
1479 for all_lines in all_block (overapp.rule_id) loop
1480 l_counter := l_counter + 1;
1481
1482 l_doc_source := l_doc_source || '
1483 | ' || all_lines.line_type || ', Tax Treatment : ' || all_lines.tax_treatment ||
1484 ' , Rounding Correction : ' || all_lines.rounding_correction_flag ;
1485
1486 if l_counter = 1 then
1487
1488 l_source := l_source || '
1489 l_gross_remaining := ';
1490
1491 if all_lines.line_type = 'LINE' then
1492 l_source := l_source || '
1493 l_line_remaining';
1494 l_else_source := l_else_source || '
1495 l_line_applied := l_line_remaining ; ';
1496 o_line_tax_treatment := all_lines.tax_treatment ;
1497
1498 elsif all_lines.line_type = 'FREIGHT' then
1499 l_source := l_source || '
1500 l_freight_remaining';
1501 l_else_source := l_else_source || '
1502 l_freight_applied := l_freight_remaining ; ';
1503 o_freight_tax_treatment := all_lines.tax_treatment ;
1504 elsif all_lines.line_type = 'CHARGES' then
1505 l_source := l_source || '
1506 l_charges_remaining';
1507 l_else_source := l_else_source || '
1508 l_charges_applied := l_charges_remaining ; ';
1509 o_charges_tax_treatment := all_lines.tax_treatment ;
1510 end if;
1511 else
1512 if all_lines.line_type = 'LINE' then
1513 l_source := l_source || ' + l_line_remaining ';
1514 l_else_source := l_else_source || '
1515 l_line_applied := l_line_remaining ; ';
1516 o_line_tax_treatment := all_lines.tax_treatment ;
1517 elsif all_lines.line_type = 'FREIGHT' then
1518 l_source := l_source || ' + l_freight_remaining ';
1519 l_else_source := l_else_source || '
1520 l_freight_applied := l_freight_remaining ; ';
1521 o_freight_tax_treatment := all_lines.tax_treatment ;
1522 elsif all_lines.line_type = 'CHARGES' then
1523 l_source := l_source || ' + l_charges_remaining ';
1524 l_else_source := l_else_source || '
1525 l_charges_applied := l_charges_remaining ; ';
1526 o_charges_tax_treatment := all_lines.tax_treatment ;
1527 end if;
1528 end if;
1529 end loop;
1530 l_source := l_source || ';' ;
1531
1532 /* R Yeluri for bug fix 1105018. If a transaction which has a transaction
1533 * type 'Allow OverApplication' set to Yes, and if that transaction is overapplied
1534 * after it has been closed(meaning that the amount_due_remaining, line_remaining,
1535 * tax_remaining, freight_remaining and charges_remaining are all = 0), then the
1536 * following 'IF' condition for overapplication fails, because it checks to see
1537 * whether any of the line_remaining,freight_remaining,charges_remaining is > 0.
1538 * Consequently the statement l_line_applied := l_line_applied + l_amt is never
1539 * executed, and hence l_line_applied from which AMOUNT_LINE_ITEMS_REMAINING column
1540 * ar_payment_schedules is populated is 0. As a result updates in ar_payment_schedules
1541 * are incorrect.
1542 * Fix is to remove the condition 'and ((l_line_remaining >0) OR (l_freight_remaining >0)
1543 * OR (l_charges_remaining >0))' introduced as part of bug fix 840642, while retaining the
1544 * fix made for bug 973520. Such a fix would resolve all three bugs 840642, 973520 and 1105018.
1545 */
1546
1547 l_source := l_source || '
1548 if (l_amt > 0)then
1549 ' ;
1550
1551 /* 07-SEP-99 J Rautiainen Bugfix for bug 973520. Added boolean variable
1552 * l_force_exit_flag. The flag is used to prevent infinite loops.
1553 * Ie. if the overapplication is done on LINE, but the transaction against
1554 * which the application is made, doesn't have any lines (l_line_remaining = 0)
1555 * then the loop will never exit. */
1556
1557 l_source := l_source || '
1558 while l_amt > l_gross_remaining loop
1559 ';
1560 for over_app_rec in over_app_lines (overapp.rule_id) loop
1561
1562 if over_app_rec.line_type= 'LINE' then
1563
1564 l_source := l_source || '
1565 IF l_line_remaining > 0 THEN
1566 l_line_applied := l_line_applied + l_line_remaining ;
1567 l_amt := l_amt - l_line_remaining ;
1568 l_force_exit_flag := FALSE;
1569 END IF;
1570 ';
1571 elsif over_app_rec.line_type= 'FREIGHT' then
1572
1573 l_source := l_source || '
1574 IF l_freight_remaining > 0 THEN
1575 l_freight_applied := l_freight_applied + l_freight_remaining ;
1576 l_amt := l_amt - l_freight_remaining ;
1577 l_force_exit_flag := FALSE;
1578 END IF;
1579 ';
1580 elsif over_app_rec.line_type= 'CHARGES' then
1581
1582 l_source := l_source || '
1583 IF l_charges_remaining > 0 THEN
1584 l_charges_applied := l_charges_applied + l_charges_remaining ;
1585 l_amt := l_amt - l_charges_remaining ;
1586 l_force_exit_flag := FALSE;
1587 END IF;
1588 ';
1589
1590 end if;
1591 end loop;
1592
1593 /* 07-SEP-99 J Rautiainen Bugfix for bug 973520. Forcing exit in case of an
1594 * infinite loop */
1595 l_source := l_source || '
1596 IF l_force_exit_flag THEN
1597 EXIT;
1598 END IF;
1599 end loop;
1600 ';
1601 for other_lines in other_block ( overapp.rule_id) loop
1602
1603 if other_lines.line_type = 'LINE' then
1604 l_source := l_source || '
1605 l_line_applied := l_line_applied + ' || prorate_line_gross ||';
1606 ' ;
1607 l_round_minus := l_round_minus || ' - ' || prorate_line_gross ;
1608 elsif other_lines.line_type = 'FREIGHT' then
1609 l_source := l_source || '
1610 l_freight_applied := l_freight_applied + ' || prorate_freight_gross || ';
1611 ' ;
1612 l_round_minus := l_round_minus || ' - ' || prorate_freight_gross ;
1613 elsif other_lines.line_type = 'CHARGES' then
1614 l_source := l_source || '
1615 l_charges_applied := l_charges_applied + ' || prorate_charges_gross ||';
1616 ' ;
1617 l_round_minus := l_round_minus || ' - ' || prorate_charges_gross ;
1618 end if;
1619
1620 end loop;
1621
1622 for round_lines in round_block (overapp.rule_id) loop
1623
1624 if round_lines.line_type = 'LINE' then
1625 l_source := l_source || '
1626 l_line_applied := l_line_applied + l_amt ' || l_round_minus ||';
1627 ';
1628 elsif round_lines.line_type ='FREIGHT' then
1629 l_source := l_source || '
1630 l_freight_applied := l_freight_applied + l_amt ' || l_round_minus || ';
1631 ' ;
1632 elsif round_lines.line_type = 'CHARGES' then
1633 l_source := l_source || '
1634 l_charges_applied := l_charges_applied + l_amt ' || l_round_minus || ';
1635 ' ;
1636 end if;
1637
1638 l_source := l_source || '
1639 l_amt := 0 ;
1640 ';
1641
1642 l_source := l_source || '
1643 end if;
1644 ';
1645
1646
1647 end loop;
1648
1649 end loop;
1650
1651 l_source := l_source || '
1652 :line_applied := l_line_applied ;
1653 :freight_applied := l_freight_applied ;
1654 :charges_applied := l_charges_applied ;
1655
1656 :line_tax_treatment := ' || '''' || l_line_tax_treatment ||''''|| ' ;
1657 :freight_tax_treatment := '||'''' || l_freight_tax_treatment || ''''||' ;
1658 :charges_tax_treatment := '||'''' || l_charges_tax_treatment ||''''|| ' ;
1659 :o_line_tax_treatment := ' || '''' || o_line_tax_treatment ||''''|| ' ;
1660 :o_freight_tax_treatment := '||'''' || o_freight_tax_treatment || ''''||' ;
1661 :o_charges_tax_treatment := '||'''' || o_charges_tax_treatment ||''''|| ' ; ' ;
1662
1663 l_source := 'DECLARE ' || l_doc_source || ' */' || '
1664 ' || l_source ;
1665
1666 l_source := l_source || '
1667 END ; ';
1668
1669 -- Checking whether every application rule one and only one time rounding
1670 -- error flag checked.
1671
1672 for blocks_rec in all_application_blocks loop
1673 if l_prv_sequence_num = nvl(blocks_rec.rule_sequence,-98) then
1674 l_num_sequence := l_num_sequence + 1;
1675 -- l_num_sequence cannot be bigger than 0
1676 end if;
1677 for lines_rec in all_block(blocks_rec.rule_id) loop
1678 if lines_rec.rounding_correction_flag = 'Y' then
1679 l_temp_round := l_temp_round + 1;
1680 end if;
1681 end loop;
1682 if l_temp_round <> 1 then
1683 l_num_round_error := 2;
1684 end if;
1685 l_temp_round :=0;
1686 l_prv_sequence_num := nvl(blocks_rec.rule_sequence,-97) ;
1687 end loop;
1688
1689 if ( (l_num_overappblock = 1)
1690 AND (l_num_appblock >= 1)
1691 AND (l_num_linetype_line = 1)
1692 AND (l_num_linetype_freight = 1)
1693 AND (l_num_linetype_charges = 1)
1694 AND (l_num_sequence = 0)
1695 AND (l_num_round_error <> 2)
1696 ) then
1697
1698
1699 update ar_app_rule_sets
1700 set rule_source = l_source
1701 , last_updated_by = fnd_global.user_id
1702 , last_update_date = sysdate
1703 , last_update_login = fnd_global.login_id
1704 where rule_set_id = p_rule_set_id;
1705 else
1706 -- Error one of the data check rules is not followed the user has to update
1707 -- data before the rule can be frozen.
1708 fnd_message.set_name('AR','AR_INVALID_FREEZE_DATA');
1709 app_exception.raise_exception;
1710
1711 end if;
1712
1713 IF PG_DEBUG in ('Y', 'C') THEN
1714 arp_standard.debug('ARP_APP_CALC_PKG.COMPILE_RULE()-');
1715 END IF;
1716
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719 IF PG_DEBUG in ('Y', 'C') THEN
1720 arp_util.debug('EXCEPTION: ARP_APP_CALC_PKG.compile_rule'||SQLERRM);
1721 END IF;
1722 RAISE;
1723
1724 end COMPILE_RULE;
1725
1726 end ARP_APP_CALC_PKG;