1 PACKAGE BODY ARP_PROCESS_ADJUSTMENT1 AS
2 /* $Header: ARTEAD1B.pls 115.4 2002/10/23 23:50:48 vahluwal ship $ */
3
4 /*===========================================================================+
5 | FUNCTION |
6 | is_autoadj_candidate |
7 | |
8 | DESCRIPTION |
9 | For an Autoadjustment made to the payment schedule of an Invoice, if the|
10 | Receivable Activity passed as parameter is Tax Inclusive meaning tax |
11 | code source is 'INVOICE' or 'ACTIVITY' then the charge is tax inclusive |
12 | or the line adjustment to be created is tax inclusive, so the charge or |
13 | line bucket is closed and tax calculated and an adjustment sum of charge|
14 | or line amount including tax is created using the tax remaining amounts |
15 | on the payment schedule or the rate for ACTIVITY tax code. |
16 | |
17 | A common rate is used to split the amount low and amount high, and the |
18 | percent low and percent high into tax and line parts which are then |
19 | compared with the actual line/charge amount and tax amount which sum of |
20 | which is used to create the final adjustment. |
21 | |
22 | If these charges/line and tax amounts are withing the low and high |
23 | amount and percent ranges, then the payment schedule is a valid |
24 | candidate for creation of an autoadjustment. |
25 | |
26 | SCOPE - PUBLIC |
27 | |
28 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
29 | arpcurr.currround |
30 | |
31 | ARGUMENTS : IN: |
32 | p_adj_low_amt Autoadjust low amount |
33 | p_adj_high_amt Autoadjust high amount |
34 | p_adj_low_pct Autoadjust low percent |
35 | p_adj_high_pct Autoadjust high percent |
36 | p_type Type of Adjustment (LINE/CHARGES) |
37 | p_over_apply Over application flag for Trx Type |
38 | p_tax_code_source Tax code source based on Activity |
39 | p_tax_rate Actual tax rate when Tax code source|
40 | is ACTIVITY |
41 | p_line_remaining Line amount remaining |
42 | p_charges_remaining Line amount remaining |
43 | p_tax_remaining Tax remaining |
44 | p_line_original Line or Charges amount original |
45 | p_charges_original Line or Charges amount original |
46 | p_tax_original Tax original |
47 | p_currency Currency |
48 | |
49 | OUT : NONE |
50 | |
51 | RETURNS : BOOLEAN indicating whether payment schedule is |
52 | autoadjustment candidate. |
53 | |
54 | NOTES: |
55 | Null amounts must not be passed to this function always use an NVL with|
56 | 0 when passing amounts and calling this function. |
57 | A seperate tax percent low and high parameter is required for Tax |
58 | Inclusive adjustments, otherwise the maximum percentage over line, |
59 | charges amount and tax amount high should be entered and a minimum low |
60 | percentage range over line charges amount and tax amount low should be |
61 | entered. |
62 | |
63 | MODIFICATION HISTORY |
64 | 16-MAR-99 Vikram Ahluwalia Created |
65 +===========================================================================*/
66
67 FUNCTION is_autoadj_candidate(p_adj_low_amt IN NUMBER ,
68 p_adj_high_amt IN NUMBER ,
69 p_adj_low_pct IN NUMBER ,
70 p_adj_high_pct IN NUMBER ,
71 p_type IN VARCHAR2 ,
72 p_over_apply IN VARCHAR2 ,
73 p_tax_code_source IN VARCHAR2 ,
74 p_tax_rate IN NUMBER ,
75 p_line_remaining IN NUMBER ,
76 p_charges_remaining IN NUMBER ,
77 p_tax_remaining IN NUMBER ,
78 p_line_original IN NUMBER ,
79 p_charges_original IN NUMBER ,
80 p_tax_original IN NUMBER ,
81 p_currency IN VARCHAR2 ) RETURN VARCHAR2 IS
82 l_adj_low_amt NUMBER;
83 l_adj_tax_low_amt NUMBER;
84 l_adj_high_amt NUMBER;
85 l_adj_tax_high_amt NUMBER;
86 l_cal_tax_remaining NUMBER;
87 l_adj_low_pct NUMBER;
88 l_adj_tax_low_pct NUMBER;
89 l_adj_high_pct NUMBER;
90 l_adj_tax_high_pct NUMBER;
91 l_base_remaining NUMBER;
92 l_base_original NUMBER;
93 l_accum_tax_rem1 NUMBER;
94 l_accum_base_rem1 NUMBER;
95 l_accum_rem1 NUMBER;
96
97 BEGIN
98
99 /*---------------------------------------------------------------------+
100 |Set the base amounts to be used for processing, for type other than |
101 |Line, Charges a true condition is returned. |
102 +---------------------------------------------------------------------*/
103 IF p_type = 'LINE' THEN
104 l_base_remaining := p_line_remaining;
105 l_base_original := p_line_original;
106 ELSIF p_type = 'CHARGES' THEN
107 l_base_remaining := p_charges_remaining;
108 l_base_original := p_charges_original;
109 ELSE
110 RETURN('Y');
111 END IF;
112
113 /*---------------------------------------------------------------------+
114 |Compare tax inclusive adjustment low and high amount for tax code |
115 |source 'INVOICE' or 'ACTIVITY' using tax rate. |
116 +---------------------------------------------------------------------*/
117 IF ((p_adj_low_amt IS NOT NULL) AND (p_adj_high_amt IS NOT NULL)) THEN
118
119 IF p_tax_code_source = 'INVOICE' AND p_type IN ('LINE','CHARGES') THEN
120
121 IF ((l_base_remaining + p_tax_remaining) = 0)
122 OR (l_base_remaining = 0) THEN
123
124 RETURN('N');
125
126 ELSE
127
128 l_adj_tax_low_amt := arpcurr.currround((p_tax_remaining/(l_base_remaining + p_tax_remaining))
129 * p_adj_low_amt, p_currency);
130
131 l_adj_low_amt := p_adj_low_amt - l_adj_tax_low_amt;
132
133 l_adj_tax_high_amt := arpcurr.currround((p_tax_remaining/(l_base_remaining + p_tax_remaining))
134 * p_adj_high_amt, p_currency);
135
136 l_adj_high_amt := p_adj_high_amt - l_adj_tax_high_amt;
137
138 /*---------------------------------------------------------------------------+
139 |Charges/Line amount should be between the charges/line low and high amount |
140 |ranges. Tax amount should be between the tax low and high amount ranges. |
141 +---------------------------------------------------------------------------*/
142 IF ((l_base_remaining BETWEEN l_adj_low_amt AND l_adj_high_amt)
143 AND (p_tax_remaining BETWEEN l_adj_tax_low_amt AND l_adj_tax_high_amt)) THEN
144 RETURN('Y');
145 ELSE
146 RETURN('N');
147 END IF;
148 END IF;
149
150 ELSIF ((p_tax_code_source = 'ACTIVITY') AND (p_type IN ('LINE','CHARGES'))) THEN
151
152 l_cal_tax_remaining := arpcurr.currround(l_base_remaining * p_tax_rate/100, p_currency);
153
154 IF ((l_base_remaining + l_cal_tax_remaining) = 0) THEN
155 RETURN('N');
156 END IF;
157
158 l_adj_tax_low_amt :=
159 arpcurr.currround(p_adj_low_amt *
160 l_cal_tax_remaining/(l_base_remaining + l_cal_tax_remaining ),
161 p_currency);
162
163 l_adj_low_amt := p_adj_low_amt - l_adj_tax_low_amt;
164
165 l_adj_tax_high_amt :=
166 arpcurr.currround(p_adj_high_amt *
167 l_cal_tax_remaining/(l_base_remaining + l_cal_tax_remaining ),
168 p_currency);
169
170 l_adj_high_amt := p_adj_high_amt - l_adj_tax_high_amt;
171
172
173 /*---------------------------------------------------------------------------+
174 |Charges/Line amount should be between the charges/line low and high amount |
175 |ranges. Tax amount should be between the tax low and high amount ranges. |
176 |If Overapply not allowed then we should not change sign of tax remaining. |
177 +---------------------------------------------------------------------------*/
178 IF ((p_tax_remaining < l_cal_tax_remaining)
179 OR ((l_base_remaining NOT BETWEEN l_adj_low_amt AND l_adj_high_amt)
180 AND (l_cal_tax_remaining NOT BETWEEN l_adj_tax_low_amt AND l_adj_tax_high_amt))) THEN
181
182 RETURN('N');
183 ELSE
184 RETURN('Y');
185 END IF;
186
187 ELSE -- implies p_tax_code_source = 'NONE' or p_type other than line or charges
188 RETURN('Y');
189
190 END IF;
191
192 ELSE --Check percentage range for tax inclusive line or charges adjustment
193 /*---------------------------------------------------------------------+
194 |Compare tax inclusive adjustment low and high percentage for tax code|
195 |source 'INVOICE' or 'ACTIVITY' using tax rate. |
196 +---------------------------------------------------------------------*/
197
198 IF ((p_tax_code_source = 'INVOICE') AND (p_type IN ('LINE','CHARGES'))) THEN
199
200 IF (((p_tax_remaining + l_base_remaining) = 0)
201 OR (l_base_remaining = 0)
202 OR (l_base_original = 0)) THEN
203 RETURN('N');
204 END IF;
205
206 /*-------------------------------------------------------------------------------+
207 | In reality there should be two percentage ranges one for tax low and high |
208 | and the other for line/charges amount low and high, this is because of the |
209 | fact that their percentage basis are different over the line/charges original |
210 | or tax original, the check is done using a combined percent of line plus tax |
211 | remaining over the original line plus tax remaining and evaluated with the |
212 | percentage range which the user enters in the form |
213 +-------------------------------------------------------------------------------*/
214 l_accum_rem1 := ROUND(ABS((l_base_remaining + p_tax_remaining)
215 /(l_base_original + p_tax_original)) * 100,2);
216
217 IF l_accum_rem1 BETWEEN p_adj_low_pct and p_adj_high_pct THEN
218 RETURN('Y');
219 ELSE
220 RETURN('N');
221 END IF;
222
223 ELSIF ((p_tax_code_source = 'ACTIVITY') AND (p_type IN ('LINE','CHARGES'))) THEN
224
225 l_cal_tax_remaining := arpcurr.currround(l_base_remaining * p_tax_rate/100, p_currency);
226
227 IF (((l_base_remaining + l_cal_tax_remaining) = 0)
228 OR (l_base_original = 0)
229 OR (sign(p_tax_remaining - l_cal_tax_remaining) NOT IN (0,sign(p_tax_remaining)))
230 OR ((sign(p_tax_remaining - l_cal_tax_remaining) = sign(p_tax_remaining))
231 AND (ABS(p_tax_remaining - l_cal_tax_remaining) > ABS(p_tax_remaining)))) THEN
232
233 RETURN('N');
234 END IF;
235
236 /*---------------------------------------------------------------------------+
237 |Charges/Line percent amount should be between the charges/line low and high|
238 |percent amount ranges. Tax percent amount should be between the tax low and|
239 |high percent amount ranges. Percent calculated using tax amount calculated |
240 |using tax rate on Activity on Line amount remaining. |
241 +---------------------------------------------------------------------------*/
242 l_accum_rem1 := ROUND(ABS((l_base_remaining + l_cal_tax_remaining)
243 /(l_base_original + p_tax_original)) * 100,2);
244
245 IF l_accum_rem1 BETWEEN p_adj_low_pct AND p_adj_high_pct THEN
246 RETURN('Y');
247 ELSE
248 RETURN('N');
249 END IF;
250
251 ELSE -- implies p_tax_code_source = 'NONE' or p_type other than line or charges
252 RETURN('Y');
253 END IF; --end if p_tax_code_source is Invoice construct
254
255 END IF; --end if adj amount low, high is not null
256
257 END is_autoadj_candidate;
258
259 END ARP_PROCESS_ADJUSTMENT1;
[Home] [Help]