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