1 PACKAGE BODY AP_AWT_TAX_RATES_PKG AS
2 /* $Header: aptaxckb.pls 120.3 2005/05/12 06:30:38 sguddeti noship $ */
3
4 PROCEDURE CHECK_AMOUNT_OVERLAP(X_tax_name IN VARCHAR2,
5 X_calling_sequence In VARCHAR2) IS
6
7 CURSOR c_rate_type(v_tax_name VARCHAR2) IS
8 SELECT DISTINCT RATE_TYPE
9 FROM AP_AWT_TAX_RATES
10 WHERE TAX_NAME = v_tax_name
11 -- For bug2995926
12 -- Removing 'CERTIFICATE' and 'EXCEPTION' type in the
13 -- validation.
14 AND RATE_TYPE NOT IN ('CERTIFICATE','EXCEPTION');
15
16
17 CURSOR c_rate_date(v_tax_name VARCHAR2,
18 v_rate_type AP_AWT_TAX_RATES.RATE_TYPE%TYPE) IS
19 SELECT nvl(Start_date,to_date('01-01-1000','DD-MM-YYYY')),
20 nvl(END_DATE,to_date('01-12-3000','DD-MM-YYYY')),
21 vendor_id, --BUG 1974076
22 vendor_site_id --BUG 1974076
23 FROM AP_AWT_TAX_RATES
24 WHERE TAX_NAME = v_tax_name
25 AND RATE_TYPE = v_rate_type
26 GROUP BY nvl(Start_date,to_date('01-01-1000','DD-MM-YYYY')),
27 nvl(END_DATE,to_date('01-12-3000','DD-MM-YYYY')),
28 vendor_id, --BUG 1974076
29 vendor_site_id; --BUG 1974076
30
31 CURSOR c_start_end(v_tax_name VARCHAR2,
32 v_rate_type AP_AWT_TAX_RATES.RATE_TYPE%TYPE,
33 v_start_dt AP_AWT_TAX_RATES.START_DATE%TYPE,
34 v_end_dt AP_AWT_TAX_RATES.END_DATE%TYPE) IS
35 SELECT NVL(START_AMOUNT,0),
36 NVL(END_AMOUNT, 99999999999999), TAX_RATE_ID, TAX_NAME
37 FROM AP_AWT_TAX_RATES
38 WHERE TAX_NAME = v_tax_name
39 AND RATE_TYPE = v_rate_type
40 AND nvl(Start_date,to_date('01-01-1000','DD-MM-YYYY')) = v_start_dt
41 AND nvl(END_DATE,to_date('01-12-3000','DD-MM-YYYY')) = v_end_dt;
42
43 var_start AP_AWT_TAX_RATES.START_AMOUNT%TYPE;
44 var_end AP_AWT_TAX_RATES.END_AMOUNT%TYPE;
45 var_rate_type AP_AWT_TAX_RATES.RATE_TYPE%TYPE;
46 var_start_dt AP_AWT_TAX_RATES.START_DATE%TYPE;
47 var_end_dt AP_AWT_TAX_RATES.END_DATE%TYPE;
48 duplicate_check NUMBER;
49 num_duplicate NUMBER;
50 var_check NUMBER;
51 var_tax_id AP_AWT_TAX_RATES.TAX_RATE_ID%TYPE;
52 var_tax_name AP_AWT_TAX_RATES.TAX_NAME%TYPE;
53 var_vendor_id AP_AWT_TAX_RATES.VENDOR_ID%TYPE; --BUG 1974076
54 var_vendor_site_id AP_AWT_TAX_RATES.VENDOR_SITE_ID%TYPE; --BUG 1974076
55
56 current_calling_sequence VARCHAR2(2000);
57 debug_info VARCHAR2(100);
58
59 AMOUNT_OVERLAP EXCEPTION;
60 DATE_OVERLAP EXCEPTION;
61
62 test1 VARCHAR2(10);
63 test2 VARCHAR2(10);
64 test3 VARCHAR2(10);
65 test4 VARCHAR2(10);
66
67 BEGIN
68 current_calling_sequence := 'AP_AWT_TAX_RATES_PKG.CHECK_AMOUNT_OVERLAP<-'||X_calling_sequence;
69
70 duplicate_check := 0;
71 num_duplicate :=0;
72 var_check := 0;
73 OPEN c_rate_type(X_tax_name);
74 LOOP
75 FETCH c_rate_type INTO var_rate_type;
76 EXIT WHEN c_rate_type%NOTFOUND;
77 OPEN c_rate_date(X_tax_name, var_rate_type);
78
79 LOOP
80 FETCH c_rate_date INTO var_start_dt, var_end_dt, var_vendor_id, var_vendor_site_id;
81 EXIT WHEN c_rate_date%NOTFOUND;
82
83
84 /* The following SQL statement checks to see if the amount ranges are
85 null so that we can go ahead and check for any duplicates.
86 Duplicate_check gets incremented if the query finds amount ranges
87 that are not null.
88 */
89
90 SELECT COUNT(*)
91 INTO duplicate_check
92 FROM AP_AWT_TAX_RATES
93 WHERE TAX_NAME = X_tax_name
94 AND RATE_TYPE = var_rate_type
95 AND NOT (
96 START_AMOUNT IS NULL AND END_AMOUNT IS NULL
97 );
98
99 IF duplicate_check = 0 THEN
100
101 -- For bug 2995926
102 -- Removing vendor id conditions as it is applicable only for
103 -- 'CERTIFICATE' and 'EXCEPTION'. We are not going to handle
104 -- this anymore in this package.
105
106 SELECT Count(*)
107 INTO num_duplicate
108 FROM AP_AWT_TAX_RATES
109 WHERE TAX_NAME = X_tax_name
110 AND RATE_TYPE = var_rate_type
111 AND nvl(start_date,to_date('01-01-1000','DD-MM-YYYY'))
112 IN ( Select nvl(start_date,to_date('01-01-1000','DD-MM-YYYY'))
113 FROM ap_awt_tax_rates
114 WHERE TAX_NAME = X_tax_name /* BUG 1666209 */
115 AND RATE_TYPE = var_rate_type -- BUG 1974076
116 GROUP BY nvl(start_date,to_date('01-01-1000','DD-MM-YYYY')),
117 nvl(end_date,to_date('31-12-3000','DD-MM-YYYY'))
118 HAVING count(*)>1
119 AND nvl(end_date,to_date('31-12-3000','DD-MM-YYYY')) =
120 nvl(ap_awt_tax_rates.end_date,
121 to_date('31-12-3000','DD-MM-YYYY'))
122 )
123 ORDER BY start_date, end_date;
124
125
126 IF num_duplicate > 0 THEN /* BUG 1666209 */
127 RAISE DATE_OVERLAP;
128 END IF;
129
130 END IF;
131
132
133
134 /* The following sql statement checks to see if any of the dates lie
135 in between any other dates. If they do, then we have overlapping
136 dates in which case var_check > 0. Then, we throw an exception to
137 display an error message.
138 */
139
140 -- For bug 2995926
141 -- Removing vendor id conditions as it is applicable only for
142 -- 'CERTIFICATE' and 'EXCEPTION'. We are not going to handle
143 -- this anymore in this package.
144
145
146 SELECT COUNT(*)
147 INTO var_check
148 FROM AP_AWT_TAX_RATES
149 WHERE TAX_NAME = X_tax_name
150 AND RATE_TYPE = var_rate_type
151 AND NOT (
152
153 nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY')) = var_start_dt
154 AND
155 nvl(END_DATE,to_date('01-12-3000','DD-MM-YYYY')) = var_end_dt
156
157 ) -- current cursor row should not be counted
158
159 AND ( (
160
161 var_start_dt >= nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY'))
162 AND
163 var_start_dt <= nvl(END_DATE,to_date('01-12-3000','DD-MM-YYYY'))
164
165 )
166 OR
167 (
168
169 var_end_dt >= nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY'))
170 AND
171 var_end_dt <= nvl(END_DATE,to_date('01-12-2999','DD-MM-YYYY'))
172
173 )
174 );
175
176 IF var_check >0 THEN
177 RAISE DATE_OVERLAP;
178 END IF;
179
180 OPEN c_start_end(X_tax_name,var_rate_type,var_start_dt,var_end_dt);
181
182 LOOP
183 FETCH c_start_end INTO var_start, var_end, var_tax_id, var_tax_name;
184 EXIT WHEN c_start_end%NOTFOUND;
185
186 SELECT COUNT(*)
187 INTO var_check
188 FROM AP_AWT_TAX_RATES
189 WHERE TAX_NAME = X_tax_name
190 AND RATE_TYPE = var_rate_type
191 AND nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY'))
192 = var_start_dt
193 AND nvl(END_DATE,to_date('01-12-2999','DD-MM-YYYY'))
194 = var_end_dt
195 AND ((var_start >= START_AMOUNT AND
196 var_start < END_AMOUNT)
197 OR
198 (var_end > START_AMOUNT AND
199 var_end <= END_AMOUNT))
200 AND TAX_RATE_ID <> var_tax_id;
201 IF var_check >0 THEN
202 RAISE AMOUNT_OVERLAP;
203 END IF;
204 END LOOP;
205 CLOSE c_start_end;
206 END LOOP;
207 CLOSE c_rate_date;
208 END LOOP;
209 CLOSE c_rate_type;
210
211 EXCEPTION
212 WHEN DATE_OVERLAP THEN
213 FND_MESSAGE.SET_NAME('SQLAP','AP_AWT_DATE_OVRLP');
214 APP_EXCEPTION.RAISE_EXCEPTION;
215 WHEN AMOUNT_OVERLAP THEN
216 FND_MESSAGE.SET_NAME('SQLAP','AP_OVERLAP1');
217 APP_EXCEPTION.RAISE_EXCEPTION;
218 WHEN OTHERS THEN
219 IF (SQLCODE <> -20001) THEN
220 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
221 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
222 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
223 FND_MESSAGE.SET_TOKEN('PARAMETERS','tax_name = '||X_tax_name);
224 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
225 END IF;
226 APP_EXCEPTION.RAISE_EXCEPTION;
227 END CHECK_AMOUNT_OVERLAP;
228
229 PROCEDURE CHECK_AMOUNT_GAPS(X_tax_name IN VARCHAR2,
230 X_calling_sequence IN VARCHAR2) IS
231
232 CURSOR c_rate_type(v_tax_name VARCHAR2) IS
233 SELECT RATE_TYPE
234 FROM AP_AWT_TAX_RATES
235 WHERE TAX_NAME = v_tax_name
236 -- For bug 2995926
237 -- Removing 'CERTIFICATE' and 'EXCEPTION' type in the
238 -- validation.
239 AND RATE_TYPE NOT IN ('CERTIFICATE','EXCEPTION');
240
241
242 CURSOR c_rate_date(v_tax_name VARCHAR2,
243 v_rate_type AP_AWT_TAX_RATES.RATE_TYPE%TYPE) IS
244 SELECT nvl(Start_date,to_date('01-01-1000','DD-MM-YYYY')), nvl(END_DATE,to_date('01-12-1000','DD-MM-YYYY'))
245 FROM AP_AWT_TAX_RATES
246 WHERE TAX_NAME = v_tax_name
247 AND RATE_TYPE = v_rate_type;
248
249 CURSOR c_start_amount(v_tax_name VARCHAR2,
250 v_rate_type AP_AWT_TAX_RATES.RATE_TYPE%TYPE,
251 v_start_dt AP_AWT_TAX_RATES.START_DATE%TYPE,
252 v_end_dt AP_AWT_TAX_RATES.END_DATE%TYPE) IS
253 SELECT START_AMOUNT
254 FROM AP_AWT_TAX_RATES
255 WHERE TAX_NAME = v_tax_name
256 AND RATE_TYPE = v_rate_type
257 AND nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY')) = v_start_dt
258 AND nvl(END_DATE,to_date('01-12-2999','DD-MM-YYYY')) = v_end_dt
259 AND START_AMOUNT <> 0;
260
261 var_start AP_AWT_TAX_RATES.START_AMOUNT%TYPE;
262 var_rate_type AP_AWT_TAX_RATES.RATE_TYPE%TYPE;
263 var_start_dt AP_AWT_TAX_RATES.START_DATE%TYPE;
264 var_end_dt AP_AWT_TAX_RATES.END_DATE%TYPE;
265 var_check NUMBER;
266
267 current_calling_sequence VARCHAR2(2000);
268 debug_info VARCHAR2(100);
269
270 AMOUNT_GAP EXCEPTION;
271
272 BEGIN
273 current_calling_sequence := 'AP_AWT_TAX_RATES_PKG.CHECK_AMOUNT_GAPS<-'||X_calling_sequence;
274 var_check := 0;
275
276 OPEN c_rate_type(X_tax_name);
277 LOOP
278 FETCH c_rate_type INTO var_rate_type;
279 EXIT WHEN c_rate_type%NOTFOUND;
280 OPEN c_rate_date(X_tax_name, var_rate_type);
281
282 LOOP
283 FETCH c_rate_date INTO var_start_dt, var_end_dt;
284 EXIT WHEN c_rate_date%NOTFOUND;
285 OPEN c_start_amount(X_tax_name, var_rate_type, var_start_dt, var_end_dt);
286
287 LOOP
288 FETCH c_start_amount INTO var_start;
289 EXIT WHEN c_start_amount%NOTFOUND;
290
291 SELECT COUNT(*)
292 INTO var_check
293 FROM AP_AWT_TAX_RATES
294 WHERE TAX_NAME = X_tax_name
295 AND RATE_TYPE = var_rate_type
296 AND nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY')) = var_start_dt
297 AND nvl(END_DATE,to_date('01-12-2999','DD-MM-YYYY')) = var_end_dt
298 AND END_AMOUNT = var_start;
299 IF var_check = 0 THEN
300 RAISE AMOUNT_GAP;
301 END IF;
302 END LOOP;
303 CLOSE c_start_amount;
304 END LOOP;
305 CLOSE c_rate_date;
306 END LOOP;
307 CLOSE c_rate_type;
308
309 EXCEPTION
310 WHEN AMOUNT_GAP THEN
311 FND_MESSAGE.SET_NAME('SQLAP','AP_GAPS');
312 APP_EXCEPTION.RAISE_EXCEPTION;
313 WHEN OTHERS THEN
314 IF (SQLCODE <> -20001) THEN
315 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
316 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
317 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
318 FND_MESSAGE.SET_TOKEN('PARAMETERS','tax_name = '||X_tax_name);
319 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
320 END IF;
321 APP_EXCEPTION.RAISE_EXCEPTION;
322 END CHECK_AMOUNT_GAPS;
323
324 PROCEDURE CHECK_LAST_AMOUNT(X_tax_name IN VARCHAR2,
325 X_calling_sequence IN VARCHAR2) IS
326 CURSOR c_rate_type(v_tax_name VARCHAR2) IS
327 SELECT RATE_TYPE
328 FROM AP_AWT_TAX_RATES
329 WHERE TAX_NAME = v_tax_name
330 -- For Bug 2995926
331 -- Removing 'CERTIFICATE' and 'EXCEPTION' type in the
332 -- validation.
333 AND RATE_TYPE NOT IN ('CERTIFICATE','EXCEPTION');
334
335
336 CURSOR c_rate_date(v_tax_name VARCHAR2,
337 v_rate_type AP_AWT_TAX_RATES.RATE_TYPE%TYPE) IS
338 SELECT nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY')), nvl(END_DATE,to_date('01-12-2999','DD-MM-YYYY'))
339 FROM AP_AWT_TAX_RATES
340 WHERE TAX_NAME = v_tax_name
341 AND RATE_TYPE = v_rate_type;
342
343 var_start AP_AWT_TAX_RATES.START_AMOUNT%TYPE;
344 var_max_end AP_AWT_TAX_RATES.START_AMOUNT%TYPE;
345 var_rate_type AP_AWT_TAX_RATES.RATE_TYPE%TYPE;
346 var_start_dt AP_AWT_TAX_RATES.START_DATE%TYPE;
347 var_end_dt AP_AWT_TAX_RATES.END_DATE%TYPE;
348
349 current_calling_sequence VARCHAR2(2000);
350 debug_info VARCHAR2(100);
351
352 LAST_AMOUNT_ERROR EXCEPTION;
353
354 BEGIN
355 current_calling_sequence := 'AP_AWT_TAX_RATES_PKG.CHECK_AMOUNT_GAPS<-'||X_calling_sequence;
356 var_max_end := 0;
357 OPEN c_rate_type(X_tax_name);
358 LOOP
359 FETCH c_rate_type INTO var_rate_type;
360 EXIT WHEN c_rate_type%NOTFOUND;
361 OPEN c_rate_date(X_tax_name,var_rate_type);
362
363 LOOP
364 FETCH c_rate_date INTO var_start_dt, var_end_dt;
365 EXIT WHEN c_rate_date%NOTFOUND;
366
367 SELECT START_AMOUNT
368 INTO var_start
369 FROM AP_AWT_TAX_RATES
370 WHERE TAX_NAME = X_tax_name
371 AND RATE_TYPE = var_rate_type
372 AND nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY')) = var_start_dt
373 AND nvl(END_DATE,to_date('01-12-2999','DD-MM-YYYY')) = var_end_dt
374 AND END_AMOUNT IS NULL;
375 IF SQL%FOUND THEN
376 SELECT MAX(END_AMOUNT)
377 INTO var_max_end
378 FROM AP_AWT_TAX_RATES
379 WHERE TAX_NAME = X_tax_name
380 AND RATE_TYPE = var_rate_type
381 AND nvl(START_DATE,to_date('01-01-1000','DD-MM-YYYY')) = var_start_dt
382 AND nvl(END_DATE,to_date('01-12-2999','DD-MM-YYYY')) = var_end_dt;
383 IF var_start <> var_max_end AND var_start <> 0 THEN
384 RAISE LAST_AMOUNT_ERROR;
385 END IF;
386 END IF;
387 END LOOP;
388 CLOSE c_rate_date;
389 END LOOP;
390 CLOSE c_rate_type;
391
392 EXCEPTION
393 WHEN LAST_AMOUNT_ERROR THEN
394 FND_MESSAGE.SET_NAME('SQLAP','AP_LAST1');
395 APP_EXCEPTION.RAISE_EXCEPTION;
396 WHEN NO_DATA_FOUND THEN
397 FND_MESSAGE.SET_NAME('SQLAP','AP_LAST2');
398 APP_EXCEPTION.RAISE_EXCEPTION;
399 WHEN TOO_MANY_ROWS THEN
400 FND_MESSAGE.SET_NAME('SQLAP','AP_LAST3');
401 APP_EXCEPTION.RAISE_EXCEPTION;
402 WHEN OTHERS THEN
403 IF (SQLCODE <> -20001) THEN
404 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
405 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
406 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
407 FND_MESSAGE.SET_TOKEN('PARAMETERS','tax_name = '||X_tax_name);
408 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
409 END IF;
410 APP_EXCEPTION.RAISE_EXCEPTION;
411 END CHECK_LAST_AMOUNT;
412
413 END AP_AWT_TAX_RATES_PKG;
[Home] [Help]