[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AR_TX_CAT_DTL_PKG
Source
1 PACKAGE BODY JL_ZZ_AR_TX_CAT_DTL_PKG AS
2 /* $Header: jlzztcdb.pls 120.2 2003/03/03 19:34:17 opedrega ship $ */
3
4 PROCEDURE Insert_Row
5 (X_rowid IN OUT NOCOPY VARCHAR2,
6 X_tax_categ_dtl_id NUMBER,
7 X_tax_category_id NUMBER,
8 X_end_date_active DATE,
9 X_min_taxable_basis NUMBER,
10 X_last_update_date DATE,
11 X_last_updated_by NUMBER,
12 X_min_amount NUMBER,
13 X_min_percentage NUMBER,
14 X_tax_code VARCHAR2,
15 X_start_date_active DATE,
16 X_org_id NUMBER,
17 X_last_update_login NUMBER,
18 X_creation_date DATE,
19 X_created_by NUMBER,
20 X_attribute_category VARCHAR2,
21 X_attribute1 VARCHAR2,
22 X_attribute2 VARCHAR2,
23 X_attribute3 VARCHAR2,
24 X_attribute4 VARCHAR2,
25 X_attribute5 VARCHAR2,
26 X_attribute6 VARCHAR2,
27 X_attribute7 VARCHAR2,
28 X_attribute8 VARCHAR2,
29 X_attribute9 VARCHAR2,
30 X_attribute10 VARCHAR2,
31 X_attribute11 VARCHAR2,
32 X_attribute12 VARCHAR2,
33 X_attribute13 VARCHAR2,
34 X_attribute14 VARCHAR2,
35 X_attribute15 VARCHAR2,
36 X_calling_sequence IN VARCHAR2) IS
37
38 CURSOR C IS
39 SELECT rowid
40 FROM jl_zz_ar_tx_cat_dtl
41 WHERE tax_category_id = X_tax_category_id
42 AND tax_categ_dtl_id = X_tax_categ_dtl_id;
43 --AND end_date_active = X_end_date_active;
44
45 current_calling_sequence VARCHAR2(2000);
46 debug_info VARCHAR2(100);
47
48 BEGIN
49 --Update the calling sequence
50 --
51 current_calling_sequence := 'JL_ZZ_AR_TX_CAT_DTL_PKG.INSERT_ROW<-' ||X_calling_sequence;
52
53 debug_info := 'Insert into JL_ZZ_AR_TX_CAT_DTL';
54 INSERT INTO jl_zz_ar_tx_cat_dtl (tax_categ_dtl_id,
55 tax_category_id,
56 end_date_active,
57 min_taxable_basis,
58 last_update_date,
59 last_updated_by,
60 min_amount,
61 min_percentage,
62 tax_code,
63 start_date_active,
64 org_id,
65 last_update_login,
66 creation_date,
67 created_by,
68 attribute_category,
69 attribute1,
70 attribute2,
71 attribute3,
72 attribute4,
73 attribute5,
74 attribute6,
75 attribute7,
76 attribute8,
77 attribute9,
78 attribute10,
79 attribute11,
80 attribute12,
81 attribute13,
82 attribute14,
83 attribute15)
84 VALUES (X_tax_categ_dtl_id,
85 X_tax_category_id,
86 NVL(X_end_date_active,
87 TO_DATE('31/12/4712', 'DD/MM/YYYY')),
88 X_min_taxable_basis,
89 X_last_update_date,
90 X_last_updated_by,
91 X_min_amount,
92 X_min_percentage,
93 X_tax_code,
94 NVL(X_start_date_active,
95 TO_DATE('01/01/1000', 'DD/MM/YYYY')),
96 X_org_id,
97 X_last_update_login,
98 X_creation_date,
99 X_created_by,
100 X_attribute_category,
101 X_attribute1,
102 X_attribute2,
103 X_attribute3,
104 X_attribute4,
105 X_attribute5,
106 X_attribute6,
107 X_attribute7,
108 X_attribute8,
109 X_attribute9,
110 X_attribute10,
111 X_attribute11,
112 X_attribute12,
113 X_attribute13,
114 X_attribute14,
115 X_attribute15);
116
117 debug_info := 'Open cursor C';
118 OPEN C;
119 debug_info := 'Fetch cursor C';
120 FETCH C INTO X_rowid;
121 IF (C%NOTFOUND) THEN
122 debug_info := 'Close cursor C - DATA NOTFOUND';
123 CLOSE C;
124 Raise NO_DATA_FOUND;
125 END IF;
126 debug_info := 'Close cursor C';
127 CLOSE C;
128
129 EXCEPTION
130 WHEN OTHERS THEN
131 IF (SQLCODE <> -20001) THEN
132 FND_MESSAGE.SET_NAME('SQLAR','AR_DEBUG');
133 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
134 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
135 FND_MESSAGE.SET_TOKEN('PARAMETERS',
136 ' tax_category_id = ' || X_tax_category_id ||
137 ' tax_categ_dtl_id= ' || X_tax_categ_dtl_id ||
138 ' end_date_active = ' || X_end_date_active );
139 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
140 END IF;
141 APP_EXCEPTION.RAISE_EXCEPTION;
142
143 END Insert_Row;
144
145 PROCEDURE Lock_Row
146 (X_rowid IN OUT NOCOPY VARCHAR2,
147 X_tax_categ_dtl_id NUMBER,
148 X_tax_category_id NUMBER,
149 X_end_date_active DATE,
150 X_min_taxable_basis NUMBER,
151 X_last_update_date DATE,
152 X_last_updated_by NUMBER,
153 X_min_amount NUMBER,
154 X_min_percentage NUMBER,
155 X_tax_code VARCHAR2,
156 X_start_date_active DATE,
157 X_org_id NUMBER,
158 X_last_update_login NUMBER,
159 X_creation_date DATE,
160 X_created_by NUMBER,
161 X_attribute_category VARCHAR2,
162 X_attribute1 VARCHAR2,
163 X_attribute2 VARCHAR2,
164 X_attribute3 VARCHAR2,
165 X_attribute4 VARCHAR2,
166 X_attribute5 VARCHAR2,
167 X_attribute6 VARCHAR2,
168 X_attribute7 VARCHAR2,
169 X_attribute8 VARCHAR2,
170 X_attribute9 VARCHAR2,
171 X_attribute10 VARCHAR2,
172 X_attribute11 VARCHAR2,
173 X_attribute12 VARCHAR2,
174 X_attribute13 VARCHAR2,
175 X_attribute14 VARCHAR2,
176 X_attribute15 VARCHAR2,
177 X_calling_sequence IN VARCHAR2) IS
178
179 CURSOR C IS
180 SELECT TAX_CATEG_DTL_ID,
181 TAX_CATEGORY_ID,
182 END_DATE_ACTIVE,
183 MIN_TAXABLE_BASIS,
184 LAST_UPDATE_DATE,
185 LAST_UPDATED_BY,
186 MIN_AMOUNT,
187 MIN_PERCENTAGE,
188 TAX_CODE,
189 START_DATE_ACTIVE,
190 ORG_ID,
191 LAST_UPDATE_LOGIN,
192 CREATION_DATE,
193 CREATED_BY,
194 ATTRIBUTE_CATEGORY,
195 ATTRIBUTE1,
196 ATTRIBUTE2,
197 ATTRIBUTE3,
198 ATTRIBUTE4,
199 ATTRIBUTE5,
200 ATTRIBUTE6,
201 ATTRIBUTE7,
202 ATTRIBUTE8,
203 ATTRIBUTE9,
204 ATTRIBUTE10,
205 ATTRIBUTE11,
206 ATTRIBUTE12,
207 ATTRIBUTE13,
208 ATTRIBUTE14,
209 ATTRIBUTE15
210 FROM JL_ZZ_AR_TX_CAT_DTL
211 WHERE rowid = X_rowid
212 --AND tax_category_id = X_tax_category_id
213 --AND tax_categ_dtl_id = X_tax_categ_dtl_id
214 --AND end_date_active = X_end_date_active
215 FOR UPDATE OF tax_categ_dtl_id
216 NOWAIT;
217
218 Recinfo C%ROWTYPE;
219
220 current_calling_sequence VARCHAR2(2000);
221 debug_info VARCHAR2(100);
222
223 BEGIN
224 -- Update the calling sequence
225 --
226 current_calling_sequence := 'JL_ZZ_AR_TX_CAT_DTL_PKG.LOCK_ROW<-' ||X_calling_sequence;
227 debug_info := 'Open cursor C';
228 OPEN C;
229 debug_info := 'Fetch cursor C';
230 FETCH C INTO Recinfo;
231 IF (C%NOTFOUND) THEN
232 debug_info := 'Close cursor C - DATA NOTFOUND';
233 CLOSE C;
234 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
235 APP_EXCEPTION.Raise_Exception;
236 END IF;
237 debug_info := 'Close cursor C';
238 CLOSE C;
239
240 IF ((Recinfo. tax_categ_dtl_id = X_tax_categ_dtl_id) AND
241 (Recinfo.tax_category_id = X_tax_category_id) AND
242 (Recinfo.end_date_active = X_end_date_active) AND
243 --(Recinfo.min_taxable_basis = X_min_taxable_basis) AND
244 (Recinfo.last_update_date = X_last_update_date) AND
245 (Recinfo.last_updated_by = X_last_updated_by) AND
246 ((Recinfo.min_taxable_basis = X_min_taxable_basis) OR
247 ((Recinfo.min_taxable_basis IS NULL) AND
248 (X_min_taxable_basis IS NULL))) AND
249 ((Recinfo.min_amount = X_min_amount) OR
250 ((Recinfo.min_amount IS NULL) AND
251 (X_min_amount IS NULL))) AND
252 ((Recinfo.min_percentage = X_min_percentage) OR
253 ((Recinfo.min_percentage IS NULL) AND
254 (X_min_percentage IS NULL))) AND
255 ((Recinfo.tax_code = X_tax_code) OR
256 ((Recinfo.tax_code IS NULL) AND
257 (X_tax_code IS NULL))) AND
258 ((Recinfo.start_date_active = X_start_date_active) OR
259 ((Recinfo.start_date_active IS NULL) AND
260 (X_start_date_active IS NULL))) AND
261 ((Recinfo.org_id = X_org_id) OR
262 ((Recinfo.org_id IS NULL) AND
263 (X_org_id IS NULL))) AND
264 ((Recinfo.last_update_login = X_last_update_login) OR
265 ((Recinfo.last_update_login IS NULL) AND
266 (X_last_update_login IS NULL))) AND
267 ((Recinfo.creation_date = X_creation_date) OR
268 ((Recinfo.creation_date IS NULL) AND
269 (X_creation_date IS NULL))) AND
270 ((Recinfo.created_by = X_created_by) OR
271 ((Recinfo.created_by IS NULL) AND
272 (X_created_by IS NULL))) AND
273 ((Recinfo.attribute_category = X_attribute_category) OR
274 ((Recinfo.attribute_category IS NULL) AND
275 (X_attribute_category IS NULL))) AND
276 ((Recinfo.attribute1 = X_attribute1) OR
277 ((Recinfo.attribute1 IS NULL) AND
278 (X_attribute1 IS NULL))) AND
279 ((Recinfo.attribute2 = X_attribute2) OR
280 ((Recinfo.attribute2 IS NULL) AND
281 (X_attribute2 IS NULL))) AND
282 ((Recinfo.attribute3 = X_attribute3) OR
283 ((Recinfo.attribute3 IS NULL) AND
284 (X_attribute3 IS NULL))) AND
285 ((Recinfo.attribute4 = X_attribute4) OR
286 ((Recinfo.attribute4 IS NULL) AND
287 (X_attribute4 IS NULL))) AND
288 ((Recinfo.attribute5 = X_attribute5) OR
289 ((Recinfo.attribute5 IS NULL) AND
290 (X_attribute5 IS NULL))) AND
291 ((Recinfo.attribute6 = X_attribute6) OR
292 ((Recinfo.attribute6 IS NULL) AND
293 (X_attribute6 IS NULL))) AND
294 ((Recinfo.attribute7 = X_attribute7) OR
295 ((Recinfo.attribute7 IS NULL) AND
296 (X_attribute7 IS NULL))) AND
297 ((Recinfo.attribute8 = X_attribute8) OR
298 ((Recinfo.attribute8 IS NULL) AND
299 (X_attribute8 IS NULL))) AND
300 ((Recinfo.attribute9 = X_attribute9) OR
301 ((Recinfo.attribute9 IS NULL) AND
302 (X_attribute9 IS NULL))) AND
303 ((Recinfo.attribute10 = X_attribute10) OR
304 ((Recinfo.attribute10 IS NULL) AND
305 (X_attribute10 IS NULL))) AND
306 ((Recinfo.attribute11 = X_attribute11) OR
307 ((Recinfo.attribute11 IS NULL) AND
308 (X_attribute11 IS NULL))) AND
309 ((Recinfo.attribute12 = X_attribute12) OR
310 ((Recinfo.attribute12 IS NULL) AND
311 (X_attribute12 IS NULL))) AND
312 ((Recinfo.attribute13 = X_attribute13) OR
313 ((Recinfo.attribute13 IS NULL) AND
314 (X_attribute13 IS NULL))) AND
315 ((Recinfo.attribute14 = X_attribute14) OR
316 ((Recinfo.attribute14 IS NULL) AND
317 (X_attribute14 IS NULL))) AND
318 ((Recinfo.attribute15 = X_attribute15) OR
319 ((Recinfo.attribute15 IS NULL) AND
320 (X_attribute15 IS NULL)))) THEN
321 return;
322 ELSE
323 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
324 APP_EXCEPTION.Raise_Exception;
325 END IF;
326
327 EXCEPTION
328 WHEN OTHERS THEN
329 IF (SQLCODE <> -20001) THEN
330 IF (SQLCODE = -54) THEN
331 FND_MESSAGE.SET_NAME('SQLAR','AR_RESOURCE_BUSY');
332 ELSE
333 FND_MESSAGE.SET_NAME('SQLAR','AR_DEBUG');
334 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
335 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
336 FND_MESSAGE.SET_TOKEN('PARAMETERS',
337 ' tax_category_id = ' || X_tax_category_id ||
338 ' tax_categ_dtl_id = ' || X_tax_categ_dtl_id ||
339 ' end_date_active = ' || X_end_date_active );
340 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
341 END IF;
342 END IF;
343 APP_EXCEPTION.RAISE_EXCEPTION;
344 END Lock_Row;
345
346 PROCEDURE UpDate_Row
347 (X_rowid IN OUT NOCOPY VARCHAR2,
348 X_tax_categ_dtl_id NUMBER,
349 X_tax_category_id NUMBER,
350 X_end_date_active DATE,
351 X_min_taxable_basis NUMBER,
352 X_last_update_date DATE,
353 X_last_updated_by NUMBER,
354 X_min_amount NUMBER,
355 X_min_percentage NUMBER,
356 X_tax_code VARCHAR2,
357 X_start_date_active DATE,
358 X_org_id NUMBER,
362 X_attribute_category VARCHAR2,
359 X_last_update_login NUMBER,
360 X_creation_date DATE,
361 X_created_by NUMBER,
363 X_attribute1 VARCHAR2,
364 X_attribute2 VARCHAR2,
365 X_attribute3 VARCHAR2,
366 X_attribute4 VARCHAR2,
367 X_attribute5 VARCHAR2,
368 X_attribute6 VARCHAR2,
369 X_attribute7 VARCHAR2,
370 X_attribute8 VARCHAR2,
371 X_attribute9 VARCHAR2,
372 X_attribute10 VARCHAR2,
373 X_attribute11 VARCHAR2,
374 X_attribute12 VARCHAR2,
375 X_attribute13 VARCHAR2,
376 X_attribute14 VARCHAR2,
377 X_attribute15 VARCHAR2,
378 X_calling_sequence IN VARCHAR2) IS
379
380 current_calling_sequence VARCHAR2(2000);
381 debug_info VARCHAR2(100);
382
383 BEGIN
384 UPDATE jl_zz_ar_tx_cat_dtl
385 SET tax_categ_dtl_id = X_tax_categ_dtl_id,
386 tax_category_id = X_tax_category_id,
387 end_date_active = X_end_date_active,
388 min_taxable_basis = X_min_taxable_basis,
389 last_update_date = X_last_update_date,
390 last_updated_by = X_last_updated_by,
391 min_amount = X_min_amount,
392 min_percentage = X_min_percentage,
393 tax_code = X_tax_code,
394 start_Date_active = X_start_Date_active,
395 org_id = X_org_id,
396 last_upDate_login = X_last_upDate_login,
397 creation_Date = X_creation_Date,
398 created_by = X_created_by,
399 attribute_category = X_attribute_category,
400 attribute1 = X_attribute1,
401 attribute2 = X_attribute2,
402 attribute3 = X_attribute3,
403 attribute4 = X_attribute4,
404 attribute5 = X_attribute5,
405 attribute6 = X_attribute6,
406 attribute7 = X_attribute7,
407 attribute8 = X_attribute8,
408 attribute9 = X_attribute9,
409 attribute10 = X_attribute10,
410 attribute11 = X_attribute11,
411 attribute12 = X_attribute12,
412 attribute13 = X_attribute13,
413 attribute14 = X_attribute14,
414 attribute15 = X_attribute15
415 WHERE rowid = X_rowid
416 AND tax_category_id = X_tax_category_id
417 AND tax_categ_dtl_id = X_tax_categ_dtl_id;
418
419 IF (SQL%NOTFOUND) THEN
420 raise NO_DATA_FOUND;
421 END IF;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 IF (SQLCODE <> -20001) THEN
426 IF (SQLCODE = -54) THEN
427 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
428 ELSE
429 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
430 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
431 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
432 FND_MESSAGE.SET_TOKEN('PARAMETERS',
433 ' tax_categ_dtl_id = ' || X_tax_categ_dtl_id ||
434 ' tax_category_id = ' || X_tax_category_id ||
435 ' end_date_active = ' || X_end_date_active );
436 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
437 END IF;
438 END IF;
439 APP_EXCEPTION.RAISE_EXCEPTION;
440 END UpDate_Row;
441
442 PROCEDURE Delete_Row
443 (X_rowid VARCHAR2,
444 X_tax_category_id NUMBER,
445 X_tax_categ_dtl_id NUMBER,
446 X_end_date_active DATE,
447 X_calling_sequence IN VARCHAR2) IS
448
449 current_calling_sequence VARCHAR2(2000);
450 debug_info VARCHAR2(100);
451
452 BEGIN
453 DELETE FROM jl_zz_ar_tx_cat_dtl
454 WHERE rowid = X_rowid
455 AND tax_category_id = X_tax_category_id
456 AND tax_categ_dtl_id = X_tax_categ_dtl_id;
457
458 IF (SQL%NOTFOUND) THEN
459 raise NO_DATA_FOUND;
460 END IF;
461
462 EXCEPTION
463 WHEN OTHERS THEN
464 IF (SQLCODE <> -20001) THEN
465 IF (SQLCODE = -54) THEN
466 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
467 ELSE
468 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
469 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
470 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
471 FND_MESSAGE.SET_TOKEN('PARAMETERS',
472 ' tax_categ_dtl_id = '|| X_tax_categ_dtl_id ||
473 ' tax_category_id = ' || X_tax_category_id ||
474 ' end_date_active = ' || X_end_date_active );
475 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
476 END IF;
477 END IF;
478 APP_EXCEPTION.RAISE_EXCEPTION;
479 END Delete_Row;
480
481 PROCEDURE Check_Unique
485 X_end_date_active DATE,
482 (X_rowid VARCHAR2,
483 X_tax_category_id NUMBER,
484 X_tax_categ_dtl_id NUMBER,
486 X_calling_sequence IN VARCHAR2) IS
487
488 l_dummy NUMBER;
489 current_calling_sequence VARCHAR2(2000);
490 debug_info VARCHAR2(100);
491
492 BEGIN
493 -- Update the calling sequence
494 --
495 current_calling_sequence :='JL_ZZ_AR_TX_CAT_PKG.CHECK_UNIQUE<-' ||
496 X_calling_sequence;
497 SELECT COUNT(1)
498 INTO l_dummy
499 FROM jl_zz_ar_tx_cat_dtl
500 WHERE tax_category_id = X_tax_category_id
501 AND tax_categ_dtl_id = X_tax_categ_dtl_id
502 AND end_date_active = X_end_date_active
503 AND ((X_rowid IS NULL) OR (rowid <> X_rowid));
504
505 IF (l_dummy >=1) THEN
506 FND_MESSAGE.SET_NAME('SQLAR','AR_DEBUG');
507 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
508 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
509 FND_MESSAGE.SET_TOKEN('PARAMETERS',
510 ' tax_category_id = ' || X_tax_category_id ||
511 ' tax_categ_dtl_id = ' || X_tax_categ_dtl_id ||
512 ' end_date_active = ' || X_end_date_active );
513 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
514 APP_EXCEPTION.RAISE_EXCEPTION;
515 END IF;
516 END Check_Unique;
517
518 PROCEDURE Check_Overlapped_Dates
519 (X_rowid VARCHAR2,
520 X_tax_category_id NUMBER,
521 X_tax_categ_dtl_id NUMBER,
522 X_end_date_active DATE,
523 X_start_date_active DATE,
524 X_calling_sequence IN VARCHAR2) IS
525
526 l_dummy NUMBER;
527 current_calling_sequence VARCHAR2(2000);
528 debug_info VARCHAR2(100);
529
530 BEGIN
531 -- Update the calling sequence
532 --
533 current_calling_sequence := 'JL_ZZ_AR_TX_CAT_DTL_PKG.<-CHECK_OVERLAPPED_DATES' ||
534 X_calling_sequence;
535 SELECT COUNT(1)
536 INTO l_dummy
537 FROM jl_zz_ar_tx_cat_dtl a
538 WHERE tax_category_id = X_tax_category_id
539 AND tax_categ_dtl_id = X_tax_categ_dtl_id
540 AND ((a.end_date_active <= X_end_date_active AND a.end_date_active >= X_start_date_active) OR
541 (a.start_date_active <= X_end_date_active AND a.start_date_active >= X_start_date_active) OR
542 (a.start_date_active <= X_start_date_active AND a.end_date_active >= X_end_date_active))
543 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
544
545 IF (l_dummy >=1) THEN
546 FND_MESSAGE.SET_NAME('SQLAR','AR_DEBUG');
547 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
548 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
549 FND_MESSAGE.SET_TOKEN('PARAMETERS',
550 ' tax_category_id = ' || to_char(X_tax_category_id) ||
551 ' tax_categ_dtl_id = ' || X_tax_categ_dtl_id ||
552 ' end_date_active = ' || X_end_date_active ||
553 ' start_date_active = ' || X_start_date_active );
554 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
555 APP_EXCEPTION.RAISE_EXCEPTION;
556 END IF;
557 END Check_Overlapped_Dates;
558
559 PROCEDURE Check_Gaps
560 (X_rowid VARCHAR2,
561 X_tax_category_id NUMBER,
562 X_tax_categ_dtl_id NUMBER,
563 X_end_date_active DATE,
564 X_start_date_active DATE,
565 X_calling_sequence IN VARCHAR2) IS
566
567 l_dummy NUMBER;
568 l_dummy1 NUMBER;
569 l_dummy2 NUMBER;
570 current_calling_sequence VARCHAR2(2000);
571 debug_info VARCHAR2(100);
572
573 BEGIN
574 -- Update the calling sequence
575 --
576 current_calling_sequence := 'JL_ZZ_AR_TX_CAT_DTL_PKG.CHECK_GAPS<-' ||
577 X_calling_sequence;
578 --
579 -- Check if there is one row with it's end date exactly one day
580 -- less than the current row's start date
581 --
582 SELECT COUNT(1)
583 INTO l_dummy
584 FROM jl_zz_ar_tx_cat_dtl a
585 WHERE tax_category_id = X_tax_category_id
586 AND tax_categ_dtl_id = X_tax_categ_dtl_id
587 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
588
589 IF (l_dummy = 0) THEN
590 BEGIN
591 --
592 -- Check if there is one row with the start date one day more than the
593 -- the current row's end-date
594 --
595 SELECT COUNT(1)
596 INTO l_dummy1
597 FROM jl_zz_ar_tx_cat_dtl a
598 WHERE tax_category_id = X_tax_category_id
599 AND tax_categ_dtl_id = X_tax_categ_dtl_id
600 AND (trunc(a.start_date_active) = (trunc(X_end_date_active) + 1))
601 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
602 --
603 -- Check if there are no (other) rows at all for the primary key.
607 BEGIN
604 -- If there are no rows, then it is not an error. Otherwise, it is.
605 --
606 IF (l_dummy1 = 0) THEN
608 SELECT COUNT(1)
609 INTO l_dummy2
610 FROM jl_zz_ar_tx_cat_dtl a
611 WHERE tax_category_id = X_tax_category_id
612 AND tax_categ_dtl_id = X_tax_categ_dtl_id
613 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
614
615 IF (l_dummy2 <> 0) THEN
616 FND_MESSAGE.SET_NAME('SQLAR','AR_DEBUG');
617 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
618 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
619 FND_MESSAGE.SET_TOKEN('PARAMETERS',
620 ' tax_category_id = ' || to_char(X_tax_category_id) ||
621 ' tax_categ_dtl_id = ' || X_tax_categ_dtl_id ||
622 ' end_date_active = ' || X_end_date_active ||
623 ' start_date_active = ' || X_start_date_active );
624 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
625 APP_EXCEPTION.RAISE_EXCEPTION;
626 END IF;
627 END;
628 END IF;
629 END;
630 END IF;
631 END Check_Gaps;
632
633 END JL_ZZ_AR_TX_CAT_DTL_PKG;