[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AR_TX_CATEG_PKG
Source
1 PACKAGE BODY JL_ZZ_AR_TX_CATEG_PKG AS
2 /* $Header: jlzztctb.pls 120.4 2006/02/11 17:45:29 pla ship $ */
3
4 PROCEDURE Insert_Row
5 (X_rowid IN OUT NOCOPY VARCHAR2,
6 X_tax_category_id NUMBER,
7 X_tax_category VARCHAR2,
8 X_end_date_active DATE,
9 X_last_updated_by NUMBER,
10 X_last_update_date DATE,
11 X_created_by NUMBER,
12 X_creation_date DATE,
13 X_last_update_login NUMBER,
14 X_threshold_check_level VARCHAR2,
15 X_threshold_check_grp_by VARCHAR2,
16 --X_description VARCHAR2,
17 X_min_amount NUMBER,
18 X_min_taxable_basis NUMBER,
19 X_min_percentage NUMBER,
20 X_tax_inclusive VARCHAR2,
21 X_org_tax_attribute VARCHAR2,
22 X_cus_tax_attribute VARCHAR2,
23 X_txn_tax_attribute VARCHAR2,
24 X_tributary_substitution VARCHAR2,
25 X_used_to_reduce VARCHAR2,
26 X_tax_categ_to_reduce_id NUMBER,
27 X_tax_code VARCHAR2,
28 X_tax_authority_code VARCHAR2,
29 X_mandatory_in_class VARCHAR2,
30 X_print_flag VARCHAR2,
31 X_tax_rule_set VARCHAR2,
32 X_start_date_active DATE,
33 X_tax_regime VARCHAR2 DEFAULT NULL,
34 X_org_id NUMBER,
35 X_attribute_category VARCHAR2,
36 X_attribute1 VARCHAR2,
37 X_attribute2 VARCHAR2,
38 X_attribute3 VARCHAR2,
39 X_attribute4 VARCHAR2,
40 X_attribute5 VARCHAR2,
41 X_attribute6 VARCHAR2,
42 X_attribute7 VARCHAR2,
43 X_attribute8 VARCHAR2,
44 X_attribute9 VARCHAR2,
45 X_attribute10 VARCHAR2,
46 X_attribute11 VARCHAR2,
47 X_attribute12 VARCHAR2,
48 X_attribute13 VARCHAR2,
49 X_attribute14 VARCHAR2,
50 X_attribute15 VARCHAR2,
51 X_calling_sequence IN VARCHAR2) IS
52
53 CURSOR C IS
54 SELECT rowid
55 FROM JL_ZZ_AR_TX_CATEG
56 WHERE tax_category_id = X_tax_category_id;
57 --AND org_id = X_org_id;
58 --AND end_date_active = X_end_date_active;
59
60 current_calling_sequence VARCHAR2(2000);
61 debug_info VARCHAR2(100);
62
63 BEGIN
64 --Update the calling sequence
65
66 current_calling_sequence := 'JL_ZZ_AR_TX_CATEG_PKG.INSERT_ROW<-' ||
67 X_calling_sequence;
68
69 debug_info := 'Insert into JL_ZZ_AR_TX_CATEG ';
70 INSERT INTO JL_ZZ_AR_TX_CATEG (tax_category_id,
71 tax_category,
72 end_date_active,
73 last_updated_by,
74 last_update_date,
75 created_by,
76 creation_date,
77 last_update_login,
78 threshold_check_level,
79 threshold_check_grp_by,
80 --description,
81 min_amount,
82 min_taxable_basis,
83 min_percentage,
84 tax_inclusive,
85 org_tax_attribute,
86 cus_tax_attribute,
87 txn_tax_attribute,
88 tributary_substitution,
89 used_to_reduce,
90 tax_categ_to_reduce_id,
91 tax_code,
92 tax_authority_code,
93 mandatory_in_class,
94 print_flag,
95 tax_rule_set,
96 start_date_active,
97 tax_regime,
98 org_id,
99 attribute_category,
100 attribute1,
101 attribute2,
102 attribute3,
103 attribute4,
104 attribute5,
105 attribute6,
106 attribute7,
107 attribute8,
108 attribute9,
109 attribute10,
110 attribute11,
111 attribute12,
112 attribute13,
113 attribute14,
114 attribute15)
115 VALUES (X_tax_category_id,
116 X_tax_category,
117 NVL(X_end_date_active,TO_DATE('31/12/4712', 'DD/MM/YYYY')),
118 X_last_updated_by,
119 X_last_update_date,
120 X_created_by,
121 X_creation_date,
122 X_last_update_login,
123 X_threshold_check_level,
124 X_threshold_check_grp_by,
125 --X_description,
126 X_min_amount,
127 X_min_taxable_basis,
128 X_min_percentage,
129 X_tax_inclusive,
130 X_org_tax_attribute,
131 X_cus_tax_attribute,
132 X_txn_tax_attribute,
133 X_tributary_substitution,
134 X_used_to_reduce,
135 X_tax_categ_to_reduce_id,
136 X_tax_code,
137 X_tax_authority_code,
138 X_mandatory_in_class,
139 X_print_flag,
140 X_tax_rule_set,
141 NVL(X_start_date_active,TO_DATE('01/01/1000', 'DD/MM/YYYY')),
142 X_tax_regime,
143 X_org_id,
144 X_attribute_category,
145 X_attribute1,
146 X_attribute2,
147 X_attribute3,
148 X_attribute4,
149 X_attribute5,
150 X_attribute6,
151 X_attribute7,
152 X_attribute8,
153 X_attribute9,
154 X_attribute10,
155 X_attribute11,
156 X_attribute12,
157 X_attribute13,
158 X_attribute14,
159 X_attribute15);
160
161 debug_info := 'Open cursor C';
162 OPEN C;
163 debug_info := 'Fetch cursor C';
164 FETCH C INTO X_rowid;
165 if (C%NOTFOUND) then
166 debug_info := 'Close cursor C - DATA NOTFOUND';
167 CLOSE C;
168 Raise NO_DATA_FOUND;
169 end if;
170 debug_info := 'Close cursor C';
171 CLOSE C;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 IF (SQLCODE <> -20001) THEN
176 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
177 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
178 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
179 FND_MESSAGE.SET_TOKEN('PARAMETERS',
180 ' tax_category_id = ' || X_tax_category_id ||
181 ' end_date_active = ' || X_end_date_active );
182 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
183 END IF;
184 APP_EXCEPTION.RAISE_EXCEPTION;
185 END Insert_Row;
186
187 PROCEDURE Lock_Row
188 (X_rowid VARCHAR2,
189 X_tax_category_id NUMBER,
190 X_tax_category VARCHAR2,
191 X_end_date_active DATE,
192 X_last_updated_by NUMBER,
193 X_last_update_date DATE,
194 X_created_by NUMBER,
195 X_creation_date DATE,
196 X_last_update_login NUMBER,
197 X_threshold_check_level VARCHAR2,
198 X_threshold_check_grp_by VARCHAR2,
199 --X_description VARCHAR2,
200 X_min_amount NUMBER ,
201 X_min_taxable_basis NUMBER ,
202 X_min_percentage NUMBER ,
203 X_tax_inclusive VARCHAR2,
204 X_org_tax_attribute VARCHAR2,
205 X_cus_tax_attribute VARCHAR2,
206 X_txn_tax_attribute VARCHAR2,
207 X_tributary_substitution VARCHAR2,
208 X_used_to_reduce VARCHAR2,
209 X_tax_categ_to_reduce_id NUMBER ,
210 X_tax_code VARCHAR2,
211 X_tax_authority_code VARCHAR2 ,
212 X_mandatory_in_class VARCHAR2 ,
213 X_print_flag VARCHAR2 ,
214 X_tax_rule_set VARCHAR2 ,
215 X_start_date_active DATE ,
216 X_tax_regime VARCHAR2 DEFAULT NULL,
217 X_org_id IN NUMBER,
218 X_attribute_category VARCHAR2,
219 X_attribute1 VARCHAR2,
220 X_attribute2 VARCHAR2,
221 X_attribute3 VARCHAR2,
222 X_attribute4 VARCHAR2,
223 X_attribute5 VARCHAR2,
224 X_attribute6 VARCHAR2,
225 X_attribute7 VARCHAR2,
226 X_attribute8 VARCHAR2,
227 X_attribute9 VARCHAR2,
228 X_attribute10 VARCHAR2,
229 X_attribute11 VARCHAR2,
230 X_attribute12 VARCHAR2,
231 X_attribute13 VARCHAR2,
232 X_attribute14 VARCHAR2,
233 X_attribute15 VARCHAR2,
234 X_calling_sequence IN VARCHAR2) IS
235
236 CURSOR C IS
237 SELECT TAX_CATEGORY_ID,
238 TAX_CATEGORY,
239 DESCRIPTION,
240 END_DATE_ACTIVE,
241 LAST_UPDATE_DATE,
242 LAST_UPDATED_BY,
243 THRESHOLD_CHECK_LEVEL,
244 THRESHOLD_CHECK_GRP_BY,
245 MIN_AMOUNT,
246 MIN_TAXABLE_BASIS,
247 MIN_PERCENTAGE,
248 TAX_INCLUSIVE,
249 ORG_TAX_ATTRIBUTE,
250 CUS_TAX_ATTRIBUTE,
251 TXN_TAX_ATTRIBUTE,
252 TRIBUTARY_SUBSTITUTION,
253 USED_TO_REDUCE,
254 TAX_CATEG_TO_REDUCE_ID,
255 TAX_CODE,
256 MANDATORY_IN_CLASS,
257 TAX_AUTHORITY_CODE,
258 TAX_RULE_SET,
259 PRINT_FLAG,
260 START_DATE_ACTIVE,
261 TAX_REGIME,
262 ORG_ID,
263 LAST_UPDATE_LOGIN,
264 CREATION_DATE,
265 CREATED_BY,
266 ATTRIBUTE_CATEGORY,
267 ATTRIBUTE1,
268 ATTRIBUTE2,
269 ATTRIBUTE3,
270 ATTRIBUTE4,
271 ATTRIBUTE5,
272 ATTRIBUTE6,
273 ATTRIBUTE7,
274 ATTRIBUTE8,
275 ATTRIBUTE9,
276 ATTRIBUTE10,
277 ATTRIBUTE11,
278 ATTRIBUTE12,
279 ATTRIBUTE13,
280 ATTRIBUTE14,
281 ATTRIBUTE15
282 FROM JL_ZZ_AR_TX_CATEG
283 WHERE tax_category_id = X_tax_category_id
284 AND end_date_active = X_end_date_active
285 FOR UPDATE OF tax_category_id, end_date_active NOWAIT;
286 Recinfo C%ROWTYPE;
287
288 current_calling_sequence VARCHAR2(2000);
289 debug_info VARCHAR2(100);
290
291 BEGIN
292 --Update the calling sequence
293 --
294 current_calling_sequence := 'JL_ZZ_AR_TX_CATEG_PKG.LOCK_ROW<-' ||
295 X_calling_sequence;
296 debug_info := 'Open cursor C';
297 OPEN C;
298 debug_info := 'Fetch cursor C';
299 FETCH C INTO Recinfo;
300
301 IF (C%NOTFOUND) THEN
302 debug_info := 'Close cursor C - DATA NOTFOUND';
303 CLOSE C;
304 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
305 APP_EXCEPTION.Raise_Exception;
306 END IF;
307
308 debug_info := 'Close cursor C';
309 CLOSE C;
310
311 IF ((Recinfo.tax_category_id = X_tax_category_id) AND
312 (Recinfo.tax_category = X_tax_category) AND
313 (Recinfo.end_date_active = X_end_date_active) AND
314 (Recinfo.last_updated_by = X_last_updated_by) AND
315 (Recinfo.last_update_date = X_last_update_date) AND
316 ((Recinfo.created_by = X_created_by) OR
317 ((Recinfo.created_by IS NULL) AND
318 (X_created_by IS NULL))) AND
319 ((Recinfo.creation_date = X_creation_date) OR
320 ((Recinfo.creation_date IS NULL) AND
321 (X_creation_date IS NULL))) AND
322 ((Recinfo.last_update_login = X_last_update_login) OR
323 ((Recinfo.last_update_login IS NULL) AND
324 (X_last_update_login IS NULL))) AND
325 (Recinfo.threshold_check_level = X_threshold_check_level) AND
326 (Recinfo.threshold_check_grp_by = X_threshold_check_grp_by) AND
327 -- AND ((Recinfo.description = X_description) OR ((Recinfo.description IS NULL)
328 -- AND (X_description IS NULL)))
329 ((Recinfo.min_amount = X_min_amount) OR
330 ((Recinfo.min_amount IS NULL) AND
331 (X_min_amount IS NULL))) AND
332 ((Recinfo.min_taxable_basis = X_min_taxable_basis) OR
333 ((Recinfo.min_taxable_basis IS NULL) AND
334 (X_min_taxable_basis IS NULL))) AND
335 ((Recinfo.min_percentage = X_min_percentage) OR
336 ((Recinfo.min_percentage IS NULL) AND
337 (X_min_percentage IS NULL))) AND
338 ((Recinfo.tax_inclusive = X_tax_inclusive) OR
339 ((Recinfo.tax_inclusive IS NULL) AND
340 (X_tax_inclusive IS NULL ))) AND
341 ((Recinfo.org_tax_attribute = X_org_tax_attribute) OR
342 ((Recinfo.org_tax_attribute IS NULL) AND
343 (X_org_tax_attribute IS NULL))) AND
344 ((Recinfo.cus_tax_attribute = X_cus_tax_attribute) OR
345 ((Recinfo.cus_tax_attribute IS NULL) AND
346 (X_cus_tax_attribute IS NULL))) AND
347 ((Recinfo.txn_tax_attribute = X_txn_tax_attribute) OR
348 ((Recinfo.txn_tax_attribute IS NULL) AND
352 (X_tributary_substitution IS NULL))) AND
349 (X_txn_tax_attribute IS NULL))) AND
350 ((Recinfo.tributary_substitution = X_tributary_substitution) OR
351 ((Recinfo.tributary_substitution IS NULL) AND
353 ((Recinfo.used_to_reduce = X_used_to_reduce) OR
354 ((Recinfo.used_to_reduce IS NULL) AND
355 (X_used_to_reduce IS NULL))) AND
356 ((Recinfo.tax_categ_to_reduce_id = X_tax_categ_to_reduce_id) OR
357 ((Recinfo.tax_categ_to_reduce_id IS NULL) AND
358 (X_tax_categ_to_reduce_id IS NULL))) AND
359 ((Recinfo.tax_code = X_tax_code) OR
360 ((Recinfo.tax_code IS NULL) AND
361 (X_tax_code IS NULL))) AND
362 ((Recinfo.tax_authority_code = X_tax_authority_code) OR
363 ((Recinfo.tax_authority_code IS NULL) AND
364 (X_tax_authority_code IS NULL))) AND
365 ((Recinfo.mandatory_in_class = X_mandatory_in_class) OR
366 ((Recinfo.mandatory_in_class IS NULL) AND
367 (X_mandatory_in_class IS NULL))) AND
368 ((Recinfo.print_flag = X_print_flag) OR
369 ((Recinfo.print_flag IS NULL) AND
370 (X_print_flag IS NULL))) AND
371 ((Recinfo.tax_rule_set = X_tax_rule_set) OR
372 ((Recinfo.tax_rule_set IS NULL) AND
373 (X_tax_rule_set IS NULL))) AND
374 ((Recinfo.start_date_active = X_start_date_active) OR
375 ((Recinfo.start_date_active IS NULL) AND
376 (X_start_date_active IS NULL))) AND
377 ((Recinfo.tax_regime = X_tax_regime) OR
378 ((Recinfo.tax_regime IS NULL) AND
379 (X_tax_regime IS NULL))) AND
380 ((Recinfo.org_id = X_org_id) OR
381 ((Recinfo.org_id IS NULL) AND
382 (X_org_id IS NULL))) AND
383 ((Recinfo.attribute1 = X_attribute1) OR
384 ((Recinfo.attribute1 IS NULL) AND
385 (X_attribute1 IS NULL))) AND
386 ((Recinfo.attribute2 = X_attribute2) OR
387 ((Recinfo.attribute2 IS NULL) AND
388 (X_attribute2 IS NULL))) AND
389 ((Recinfo.attribute3 = X_attribute3) OR
390 ((Recinfo.attribute3 IS NULL) AND
391 (X_attribute3 IS NULL))) AND
392 ((Recinfo.attribute4 = X_attribute4) OR
393 ((Recinfo.attribute4 IS NULL) AND
394 (X_attribute4 IS NULL))) AND
395 ((Recinfo.attribute5 = X_attribute5) OR
396 ((Recinfo.attribute5 IS NULL) AND
397 (X_attribute5 IS NULL))) AND
398 ((Recinfo.attribute6 = X_attribute6) OR
399 ((Recinfo.attribute6 IS NULL) AND
400 (X_attribute6 IS NULL))) AND
401 ((Recinfo.attribute7 = X_attribute7) OR
402 ((Recinfo.attribute7 IS NULL) AND
403 (X_attribute7 IS NULL))) AND
404 ((Recinfo.attribute8 = X_attribute8) OR
405 ((Recinfo.attribute8 IS NULL) AND
406 (X_attribute8 IS NULL))) AND
407 ((Recinfo.attribute9 = X_attribute9) OR
408 ((Recinfo.attribute9 IS NULL) AND
409 (X_attribute9 IS NULL))) AND
410 ((Recinfo.attribute10 = X_attribute10) OR
411 ((Recinfo.attribute10 IS NULL) AND
412 (X_attribute10 IS NULL))) AND
413 ((Recinfo.attribute11 = X_attribute11) OR
414 ((Recinfo.attribute11 IS NULL) AND
415 (X_attribute11 IS NULL))) AND
416 ((Recinfo.attribute12 = X_attribute12) OR
417 ((Recinfo.attribute12 IS NULL) AND
418 (X_attribute12 IS NULL))) AND
419 ((Recinfo.attribute13 = X_attribute13) OR
420 ((Recinfo.attribute13 IS NULL) AND
421 (X_attribute13 IS NULL))) AND
422 ((Recinfo.attribute14 = X_attribute14) OR
423 ((Recinfo.attribute14 IS NULL) AND
424 (X_attribute14 IS NULL))) AND
425 ((Recinfo.attribute15 = X_attribute15) OR
426 ((Recinfo.attribute15 IS NULL) AND
427 (X_attribute15 IS NULL)))) THEN
428 return;
429 ELSE
430 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
431 APP_EXCEPTION.Raise_Exception;
432 END IF;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 IF (SQLCODE <> -20001) THEN
437 IF (SQLCODE = -54) THEN
438 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
439 ELSE
440 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
441 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
442 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
443 FND_MESSAGE.SET_TOKEN('PARAMETERS',
444 ' tax_category_id = ' || X_tax_category_id ||
445 ' end_date_active = ' || X_end_date_active );
446 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
447 END IF;
448 END IF;
449 APP_EXCEPTION.RAISE_EXCEPTION;
450
451 END Lock_Row;
452
453 PROCEDURE UpDate_Row
454 (X_rowid VARCHAR2,
455 X_tax_category_id NUMBER,
456 X_tax_category VARCHAR2,
457 X_end_date_active DATE,
458 X_last_updated_by NUMBER,
459 X_last_update_date DATE,
460 X_created_by NUMBER,
461 X_creation_date DATE,
462 X_last_update_login NUMBER,
463 X_threshold_check_level VARCHAR2,
464 X_threshold_check_grp_by VARCHAR2,
465 --X_description VARCHAR2,
469 X_tax_inclusive VARCHAR2,
466 X_min_amount NUMBER,
467 X_min_taxable_basis NUMBER,
468 X_min_percentage NUMBER,
470 X_org_tax_attribute VARCHAR2,
471 X_cus_tax_attribute VARCHAR2,
472 X_txn_tax_attribute VARCHAR2,
473 X_tributary_substitution VARCHAR2,
474 X_used_to_reduce VARCHAR2,
475 X_tax_categ_to_reduce_id NUMBER,
476 X_tax_code VARCHAR2,
477 X_tax_authority_code VARCHAR2,
478 X_mandatory_in_class VARCHAR2,
479 X_print_flag VARCHAR2,
480 X_tax_rule_set VARCHAR2,
481 X_start_date_active DATE,
482 X_tax_regime VARCHAR2 DEFAULT NULL,
483 X_org_id IN NUMBER,
484 X_attribute_category VARCHAR2,
485 X_attribute1 VARCHAR2,
486 X_attribute2 VARCHAR2,
487 X_attribute3 VARCHAR2,
488 X_attribute4 VARCHAR2,
489 X_attribute5 VARCHAR2,
490 X_attribute6 VARCHAR2,
491 X_attribute7 VARCHAR2,
492 X_attribute8 VARCHAR2,
493 X_attribute9 VARCHAR2,
494 X_attribute10 VARCHAR2,
495 X_attribute11 VARCHAR2,
496 X_attribute12 VARCHAR2,
497 X_attribute13 VARCHAR2,
498 X_attribute14 VARCHAR2,
499 X_attribute15 VARCHAR2,
500 X_calling_sequence IN VARCHAR2) IS
501
502 current_calling_sequence VARCHAR2(2000);
503 debug_info VARCHAR2(100);
504
505 BEGIN
506 --Update the calling sequence
507 --
508 current_calling_sequence := 'JL_ZZ_AR_TX_CATEG_PKG.UPDATE_ROW' ||
509 X_calling_sequence;
510 debug_info := 'Update JL_ZZ_AR_TX_CATEG';
511
512 UPDATE JL_ZZ_AR_TX_CATEG
513 SET tax_category_id = X_tax_category_id,
514 tax_category = X_tax_category,
515 end_date_active = X_end_date_active,
516 last_update_date = X_last_update_date,
517 last_updated_by = X_last_updated_by,
518 --description = X_description,
519 min_amount = X_min_amount,
520 min_taxable_basis = X_min_taxable_basis,
521 min_percentage = X_min_percentage,
522 tax_inclusive = X_tax_inclusive,
523 threshold_check_level = X_threshold_check_level,
524 threshold_check_grp_by = X_threshold_check_grp_by,
525 org_tax_attribute = X_org_tax_attribute,
526 cus_tax_attribute = X_cus_tax_attribute,
527 txn_tax_attribute = X_txn_tax_attribute,
528 tributary_substitution = X_tributary_substitution,
529 used_to_reduce = X_used_to_reduce,
530 tax_categ_to_reduce_id = X_tax_categ_to_reduce_id,
531 tax_code = X_tax_code,
532 tax_authority_code = X_tax_authority_code,
533 mandatory_in_class = X_mandatory_in_class,
534 print_flag = X_print_flag,
535 tax_rule_set = X_tax_rule_set,
536 start_Date_active = X_start_Date_active,
537 tax_regime = X_tax_regime,
538 org_id = X_org_id,
539 last_upDate_login = X_last_upDate_login,
540 creation_Date = X_creation_Date,
541 created_by = X_created_by,
542 attribute_category = X_attribute_category,
543 attribute1 = X_attribute1,
544 attribute2 = X_attribute2,
545 attribute3 = X_attribute3,
546 attribute4 = X_attribute4,
547 attribute5 = X_attribute5,
548 attribute6 = X_attribute6,
549 attribute7 = X_attribute7,
550 attribute8 = X_attribute8,
551 attribute9 = X_attribute9,
552 attribute10 = X_attribute10,
553 attribute11 = X_attribute11,
554 attribute12 = X_attribute12,
555 attribute13 = X_attribute13,
556 attribute14 = X_attribute14,
557 attribute15 = X_attribute15
558 WHERE rowid = X_rowid;
559
560 IF (SQL%NOTFOUND) THEN
561 raise NO_DATA_FOUND;
562 END IF;
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 IF (SQLCODE <> -20001) THEN
567 IF (SQLCODE = -54) THEN
568 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
569 ELSE
570 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
571 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
572 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
573 FND_MESSAGE.SET_TOKEN('PARAMETERS',
574 ' tax_category_id = ' || X_tax_category_id ||
575 ' end_date_active = ' || X_end_date_active );
576 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
577 END IF;
578 END IF;
582
579 APP_EXCEPTION.RAISE_EXCEPTION;
580
581 END UpDate_Row;
583 PROCEDURE Delete_Row
584 (X_rowid VARCHAR2,
585 X_tax_category_id NUMBER,
586 X_end_date_active DATE,
587 X_calling_sequence IN VARCHAR2) IS
588
589 current_calling_sequence VARCHAR2(2000);
590 debug_info VARCHAR2(100);
591
592 BEGIN
593 --Update the calling sequence
594 --
595 current_calling_sequence := 'JL_ZZ_AR_TX_CATEG_PKG.UPDATE_ROW' ||
596 X_calling_sequence;
597 debug_info := 'Open cursor C';
598
599 DELETE FROM JL_ZZ_AR_TX_CATEG
600 WHERE rowid = X_rowid;
601
602 IF (SQL%NOTFOUND) THEN
603 raise NO_DATA_FOUND;
604 END IF;
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 IF (SQLCODE <> -20001) THEN
609 IF (SQLCODE = -54) THEN
610 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
611 ELSE
612 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
613 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
614 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
615 FND_MESSAGE.SET_TOKEN('PARAMETERS',
616 ' tax_category_id = ' || X_tax_category_id ||
617 ' end_date_active = ' || X_end_date_active );
618 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
619 END IF;
620 END IF;
621 APP_EXCEPTION.RAISE_EXCEPTION;
622
623 END Delete_Row;
624
625 PROCEDURE Check_Unique
626 (X_rowid VARCHAR2,
627 X_tax_category_id NUMBER,
628 X_end_Date_active DATE,
629 X_calling_sequence IN VARCHAR2) IS
630
631 l_dummy NUMBER;
632 current_calling_sequence VARCHAR2(2000);
633 debug_info VARCHAR2(100);
634
635 BEGIN
636 -- Update the calling sequence
637 --
638 current_calling_sequence := 'JL_ZZ_AR_TX_CATEG_PKG.CHECK_UNIQUE<-' ||
639 X_calling_sequence;
640 SELECT COUNT(1)
641 INTO l_dummy
642 FROM JL_ZZ_AR_TX_CATEG
643 WHERE tax_category_id = X_tax_category_id
644 AND end_date_active = X_end_date_active
645 AND ((X_rowid IS NULL) OR (rowid <> X_rowid));
646
647 IF (l_dummy >=1) THEN
648 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
649 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
650 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
651 FND_MESSAGE.SET_TOKEN('PARAMETERS',
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
658 END Check_Unique;
659
660 PROCEDURE Check_Overlapped_Dates
661 (X_rowid VARCHAR2,
662 X_tax_category_id NUMBER,
663 X_end_date_active DATE,
664 X_start_date_active DATE,
665 X_org_id NUMBER,
666 X_calling_sequence IN VARCHAR2) IS
667
668 l_dummy NUMBER;
669 current_calling_sequence VARCHAR2(2000);
670 debug_info VARCHAR2(100);
671
672 BEGIN
673
674 -- Update the calling sequence
675 --
676 current_calling_sequence := 'JL_ZZ_AR_TX_CATEG_PKG.CHECK_UNIQUE<-' ||
677 X_calling_sequence;
678 SELECT COUNT(1)
679 INTO l_dummy
680 FROM JL_ZZ_AR_TX_CATEG
681 WHERE tax_category_id = X_tax_category_id
682 AND end_date_active = X_end_date_active
683 AND org_id = X_org_id
684 AND ((X_rowid IS NULL) OR (rowid <> X_rowid));
685
686 IF (l_dummy >=1) THEN
687 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
688 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
689 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
690 FND_MESSAGE.SET_TOKEN('PARAMETERS',
691 ' tax_category_id = ' || X_tax_category_id ||
692 ' end_date_active = ' || X_end_date_active );
693 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
694 APP_EXCEPTION.RAISE_EXCEPTION;
695 END IF;
696
697 --
698 -- below code is not used in JLZZTCTG.form
699 -- for Tax categories form, CHECK_OVERLAPPED_DATES
700 -- does not apply
701 -- just return from here
702 RETURN;
703
704 -- Update the calling sequence
705 --
706 current_calling_sequence := 'JL_ZZ_AR_TX_CATEG_PKG.<-CHECK_OVERLAPPED_DATES' ||
707 X_calling_sequence;
708 SELECT COUNT(1)
709 INTO l_dummy
710 FROM jl_zz_ar_tx_categ a
711 WHERE tax_category_id = X_tax_category_id
712 AND ((a.end_date_active <= X_end_date_active AND
713 a.end_date_active >= X_start_date_active) OR
714 (a.start_date_active <= X_end_date_active AND
715 a.start_date_active >= X_start_date_active) OR
716 (a.start_date_active <= X_start_date_active AND
717 a.end_date_active >= X_end_date_active))
721 IF (l_dummy >=1) THEN
718 AND org_id <> X_org_id
719 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
720
722 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
723 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
724 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
725 FND_MESSAGE.SET_TOKEN('PARAMETERS',
726 ' tax_category_id = ' || to_char(X_tax_category_id) ||
727 ' end_date_active = ' || X_end_date_active ||
728 ' start_date_active = ' || X_start_date_active ||
729 ' org_id = ' || X_org_id );
730 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
731 APP_EXCEPTION.RAISE_EXCEPTION;
732 END IF;
733 END Check_Overlapped_Dates;
734
735 PROCEDURE Check_Gaps
736 (X_rowid VARCHAR2,
737 X_tax_category_id NUMBER,
738 X_end_date_active DATE,
739 X_start_date_active DATE,
740 X_calling_sequence IN VARCHAR2) IS
741
742 l_dummy NUMBER;
743 l_dummy1 NUMBER;
744 l_dummy2 NUMBER;
745 current_calling_sequence VARCHAR2(2000);
746 debug_info VARCHAR2(100);
747
748 BEGIN
749 -- Update the calling sequence
750 --
751 current_calling_sequence := 'JL_ZZ_AR_TX_CATEG_PKG.CHECK_GAPS<-' ||
752 X_calling_sequence;
753 --
754 -- Check if there is one row with it's end date exactly one day
755 -- less than the current row's start date
756 --
757 SELECT COUNT(1)
758 INTO l_dummy
759 FROM jl_zz_ar_tx_categ a
760 WHERE tax_category_id = X_tax_category_id
761 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
762
763 IF (l_dummy = 0) THEN
764 BEGIN
765 --
766 --Check if there is one row with the start date one day more than the
767 --the current row's end-date
768 --
769 SELECT COUNT(1)
770 INTO l_dummy1
771 FROM jl_zz_ar_tx_categ a
772 WHERE tax_category_id = X_tax_category_id
773 AND (trunc(a.start_date_active) = (trunc(X_end_date_active) + 1))
774 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
775 --
776 --Check if there are no (other) rows at all for the primary key.
777 --If there are no rows, then it is not an error. Otherwise, it is.
778 --
779 IF (l_dummy1 = 0) THEN
780 BEGIN
781 SELECT COUNT(1)
782 INTO l_dummy2
783 FROM jl_zz_ar_tx_categ a
784 WHERE tax_category_id = X_tax_category_id
785 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
786
787 IF (l_dummy2 <> 0) THEN
788 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
789 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
790 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
791 FND_MESSAGE.SET_TOKEN('PARAMETERS',
792 ' tax_category_id = ' || to_char(X_tax_category_id) ||
793 ' end_date_active = ' || X_end_date_active ||
794 ' start_date_active = ' || X_start_date_active );
795 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
796 APP_EXCEPTION.RAISE_EXCEPTION;
797 END IF;
798 END;
799 END IF;
800 END;
801 END IF;
802 END Check_Gaps;
803
804 END JL_ZZ_AR_TX_CATEG_PKG;