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