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