[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AR_TX_FSC_CLS_PKG
Source
1 PACKAGE BODY JL_ZZ_AR_TX_FSC_CLS_PKG as
2 /* $Header: jlzztfcb.pls 120.3.12010000.2 2009/06/16 11:44:31 ssohal ship $ */
3
4 g_current_runtime_level NUMBER;
5 g_level_statement NUMBER;
6 g_level_procedure NUMBER;
7 g_level_event NUMBER;
8 g_level_exception NUMBER;
9 g_level_unexpected NUMBER;
10
11 PROCEDURE Insert_Row
12 (X_Rowid IN OUT NOCOPY VARCHAR2,
13 X_fsc_cls_id NUMBER,
14 X_fiscal_classification_code VARCHAR2,
15 X_tax_category_id NUMBER,
16 X_tax_code VARCHAR2,
17 X_end_date_active DATE,
18 X_base_rate NUMBER,
19 X_start_date_active DATE,
20 X_org_id NUMBER,
21 X_enabled_flag VARCHAR2,
22 X_last_update_date DATE,
23 X_last_updated_by NUMBER,
24 X_creation_date DATE,
25 X_created_by NUMBER,
26 X_last_update_login NUMBER,
27 X_attribute_category VARCHAR2,
28 X_attribute1 VARCHAR2,
29 X_attribute2 VARCHAR2,
30 X_attribute3 VARCHAR2,
31 X_attribute4 VARCHAR2,
32 X_attribute5 VARCHAR2,
33 X_attribute6 VARCHAR2,
34 X_attribute7 VARCHAR2,
35 X_attribute8 VARCHAR2,
36 X_attribute9 VARCHAR2,
37 X_attribute10 VARCHAR2,
38 X_attribute11 VARCHAR2,
39 X_attribute12 VARCHAR2,
40 X_attribute13 VARCHAR2,
41 X_attribute14 VARCHAR2,
42 X_attribute15 VARCHAR2,
43 X_calling_sequence VARCHAR2) IS
44
45 CURSOR C IS
46 SELECT rowid
47 FROM JL_ZZ_AR_TX_FSC_CLS
48 WHERE fiscal_classification_code = X_fiscal_classification_code
49 AND tax_category_id = X_tax_category_id
50 AND end_date_active = X_end_date_active;
51
52 current_calling_sequence VARCHAR2(2000);
53 debug_info VARCHAR2(100);
54
55 BEGIN
56 -- Update the calling sequence
57 --
58 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.INSERT_ROW<-' ||
59 X_calling_sequence;
60
61 debug_info := 'Insert into JL_ZZ_AR_TX_FSC_CLS';
62 INSERT INTO JL_ZZ_AR_TX_FSC_CLS (fsc_cls_id,
63 fiscal_classification_code,
64 tax_category_id,
65 tax_code,
66 end_date_active,
67 base_rate,
68 start_date_active,
69 enabled_flag,
70 org_id,
71 last_update_date,
72 last_updated_by,
73 creation_date,
74 created_by,
75 last_update_login,
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_fsc_cls_id,
93 X_fiscal_classification_code,
94 X_tax_category_id,
95 X_tax_code,
96 X_end_date_active,
97 X_base_rate,
98 X_start_date_active,
99 X_enabled_flag,
100 X_org_id,
101 X_last_update_date,
102 X_last_updated_by,
103 X_creation_date,
104 X_created_by,
105 X_last_update_login,
106 X_attribute_category,
107 X_attribute1,
108 X_attribute2,
109 X_attribute3,
110 X_attribute4,
111 X_attribute5,
112 X_attribute6,
113 X_attribute7,
114 X_attribute8,
115 X_attribute9,
116 X_attribute10,
117 X_attribute11,
118 X_attribute12,
119 X_attribute13,
120 X_attribute14,
121 X_attribute15);
122
123 debug_info := 'Open cursor C';
124 OPEN C;
125 debug_info := 'Fetch cursor C';
126 FETCH C INTO X_Rowid;
127 IF (C%NOTFOUND) THEN
128 debug_info := 'Close cursor C - DATA NOTFOUND';
129 CLOSE C;
130 Raise NO_DATA_FOUND;
131 END IF;
132 debug_info := 'Close cursor C';
133 CLOSE C;
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 IF (SQLCODE <> -20001) THEN
138 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
139 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
140 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
141 FND_MESSAGE.SET_TOKEN('PARAMETERS','fiscal_classification_code = ' ||
142 X_fiscal_classification_code ||
143 'tax_category_id = ' || X_tax_category_id ||
144 'end_date_active = ' || X_end_date_active );
145 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
146 END IF;
147 APP_EXCEPTION.RAISE_EXCEPTION;
148 END Insert_Row;
149
150 PROCEDURE Lock_Row
151 (X_Rowid VARCHAR2,
152 X_fsc_cls_id NUMBER,
153 X_fiscal_classification_code VARCHAR2,
154 X_tax_category_id NUMBER,
155 X_tax_code VARCHAR2,
156 X_end_date_active DATE,
157 X_base_rate NUMBER,
158 X_start_date_active DATE,
159 X_org_id NUMBER,
160 X_enabled_flag VARCHAR2,
161 X_last_update_date DATE,
162 X_last_updated_by NUMBER,
163 X_creation_date DATE,
164 X_created_by NUMBER,
165 X_last_update_login NUMBER,
166 X_attribute_category VARCHAR2,
167 X_attribute1 VARCHAR2,
168 X_attribute2 VARCHAR2,
169 X_attribute3 VARCHAR2,
170 X_attribute4 VARCHAR2,
171 X_attribute5 VARCHAR2,
172 X_attribute6 VARCHAR2,
173 X_attribute7 VARCHAR2,
174 X_attribute8 VARCHAR2,
175 X_attribute9 VARCHAR2,
176 X_attribute10 VARCHAR2,
177 X_attribute11 VARCHAR2,
178 X_attribute12 VARCHAR2,
179 X_attribute13 VARCHAR2,
180 X_attribute14 VARCHAR2,
181 X_attribute15 VARCHAR2,
182 X_calling_sequence VARCHAR2) IS
183
184 CURSOR C IS
185 SELECT FSC_CLS_ID,
186 FISCAL_CLASSIFICATION_CODE,
187 TAX_CATEGORY_ID,
188 TAX_CODE,
189 END_DATE_ACTIVE,
190 ENABLED_FLAG,
191 LAST_UPDATED_BY,
192 LAST_UPDATE_DATE,
193 BASE_RATE,
194 START_DATE_ACTIVE,
195 ORG_ID,
196 LAST_UPDATE_LOGIN,
197 CREATION_DATE,
198 CREATED_BY,
199 ATTRIBUTE_CATEGORY,
200 ATTRIBUTE1,
201 ATTRIBUTE2,
202 ATTRIBUTE3,
203 ATTRIBUTE4,
204 ATTRIBUTE5,
205 ATTRIBUTE6,
206 ATTRIBUTE7,
207 ATTRIBUTE8,
208 ATTRIBUTE9,
209 ATTRIBUTE10,
210 ATTRIBUTE11,
211 ATTRIBUTE12,
212 ATTRIBUTE13,
213 ATTRIBUTE14,
214 ATTRIBUTE15
215 FROM JL_ZZ_AR_TX_FSC_CLS
216 WHERE rowid = X_Rowid
217 FOR UPDATE of fiscal_classification_code,
218 tax_category_id,
219 end_date_active,
220 org_id NOWAIT;
221 Recinfo C%ROWTYPE;
222
223 current_calling_sequence VARCHAR2(2000);
224 debug_info VARCHAR2(100);
225
226 BEGIN
227 -- Update the calling sequence
228 --
229 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.LOCK_ROW<-' ||
230 X_calling_sequence;
231 debug_info := 'Open cursor C';
232 OPEN C;
233 debug_info := 'Fetch cursor C';
234 FETCH C INTO Recinfo;
235 IF (C%NOTFOUND) THEN
236 debug_info := 'Close cursor C - DATA NOTFOUND';
237 CLOSE C;
238 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
239 APP_EXCEPTION.Raise_Exception;
240 END IF;
241
242 debug_info := 'Close cursor C';
243 CLOSE C;
244 IF ((Recinfo.fsc_cls_id = X_fsc_cls_id) AND
245 (Recinfo.fiscal_classification_code = X_fiscal_classification_code) AND
246 (Recinfo.tax_category_id = X_tax_category_id) AND
247 (Recinfo.end_date_active = X_end_date_active) AND
248 (Recinfo.tax_code = X_tax_code) AND
249 (Recinfo.enabled_flag = X_enabled_flag) 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.attribute_category = X_attribute_category) OR
260 ((Recinfo.attribute_category IS NULL) AND
261 (X_attribute_category IS NULL))) AND
262 ((Recinfo.attribute1 = X_attribute1) OR
263 ((Recinfo.attribute1 IS NULL) AND
264 (X_attribute1 IS NULL))) AND
265 ((Recinfo.attribute2 = X_attribute2) OR
266 ((Recinfo.attribute2 IS NULL) AND
267 (X_attribute2 IS NULL))) AND
268 ((Recinfo.attribute3 = X_attribute3) OR
269 ((Recinfo.attribute3 IS NULL) AND
270 (X_attribute3 IS NULL))) AND
271 ((Recinfo.attribute4 = X_attribute4) OR
272 ((Recinfo.attribute4 IS NULL) AND
273 (X_attribute4 IS NULL))) AND
274 ((Recinfo.attribute5 = X_attribute5) OR
275 ((Recinfo.attribute5 IS NULL) AND
276 (X_attribute5 IS NULL))) AND
277 ((Recinfo.attribute6 = X_attribute6) OR
278 ((Recinfo.attribute6 IS NULL) AND
279 (X_attribute6 IS NULL))) AND
280 ((Recinfo.attribute7 = X_attribute7) OR
281 ((Recinfo.attribute7 IS NULL) AND
282 (X_attribute7 IS NULL))) AND
283 ((Recinfo.attribute8 = X_attribute8) OR
284 ((Recinfo.attribute8 IS NULL) AND
285 (X_attribute8 IS NULL))) AND
286 ((Recinfo.attribute9 = X_attribute9) OR
287 ((Recinfo.attribute9 IS NULL) AND
288 (X_attribute9 IS NULL))) AND
289 ((Recinfo.attribute10 = X_attribute10) OR
290 ((Recinfo.attribute10 IS NULL) AND
291 (X_attribute10 IS NULL))) AND
292 ((Recinfo.attribute11 = X_attribute11) OR
293 ((Recinfo.attribute11 IS NULL) AND
294 (X_attribute11 IS NULL))) AND
295 ((Recinfo.attribute12 = X_attribute12) OR
296 ((Recinfo.attribute12 IS NULL) AND
297 (X_attribute12 IS NULL))) AND
298 ((Recinfo.attribute13 = X_attribute13) OR
299 ((Recinfo.attribute13 IS NULL) AND
300 (X_attribute13 IS NULL))) AND
301 ((Recinfo.attribute14 = X_attribute14) OR
302 ((Recinfo.attribute14 IS NULL) AND
303 (X_attribute14 IS NULL))) AND
304 ((Recinfo.attribute15 = X_attribute15) OR
305 ((Recinfo.attribute15 IS NULL) AND
306 (X_attribute15 IS NULL)))) THEN
307 return;
308 ELSE
309 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
310 APP_EXCEPTION.Raise_Exception;
311 END IF;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315 IF (SQLCODE <> -20001) THEN
316 IF (SQLCODE = -54) THEN
317 FND_MESSAGE.SET_NAME('AR','AR_RESOURCE_BUSY');
318 ELSE
319 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
320 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
321 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
322 FND_MESSAGE.SET_TOKEN('PARAMETERS','fiscal_classification_code = ' ||
323 X_fiscal_classification_code ||
324 'tax_category_id = ' || X_tax_category_id ||
325 'end_date_active = ' || X_end_date_active );
326 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
327 END IF;
328 END IF;
329 APP_EXCEPTION.RAISE_EXCEPTION;
330 END Lock_Row;
331
332 PROCEDURE Update_Row
333 (X_Rowid VARCHAR2,
334 X_fsc_cls_id NUMBER,
335 X_fiscal_classification_code VARCHAR2,
336 X_tax_category_id NUMBER,
337 X_tax_code VARCHAR2,
338 X_end_date_active DATE,
339 X_base_rate NUMBER,
340 X_start_date_active DATE,
341 X_org_id NUMBER,
342 X_enabled_flag VARCHAR2,
343 X_last_update_date DATE,
344 X_last_updated_by NUMBER,
345 X_creation_date DATE,
346 X_created_by NUMBER,
347 X_last_update_login NUMBER,
348 X_attribute_category VARCHAR2,
349 X_attribute1 VARCHAR2,
350 X_attribute2 VARCHAR2,
351 X_attribute3 VARCHAR2,
352 X_attribute4 VARCHAR2,
353 X_attribute5 VARCHAR2,
354 X_attribute6 VARCHAR2,
355 X_attribute7 VARCHAR2,
356 X_attribute8 VARCHAR2,
357 X_attribute9 VARCHAR2,
358 X_attribute10 VARCHAR2,
359 X_attribute11 VARCHAR2,
360 X_attribute12 VARCHAR2,
361 X_attribute13 VARCHAR2,
362 X_attribute14 VARCHAR2,
363 X_attribute15 VARCHAR2,
364 X_calling_sequence VARCHAR2) IS
365
366 current_calling_sequence VARCHAR2(2000);
367 debug_info VARCHAR2(100);
368
369 BEGIN
370 -- Update the calling sequence
371 --
372 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.UPDATE_ROW<-' ||
373 X_calling_sequence;
374 debug_info := 'Update JL_ZZ_AR_TX_FSC_CLS';
375
376 UPDATE JL_ZZ_AR_TX_FSC_CLS
377 SET fsc_cls_id = X_fsc_cls_id,
378 fiscal_classification_code = X_fiscal_classification_code,
379 tax_category_id = X_tax_category_id,
380 end_date_active = X_end_date_active,
381 tax_code = X_tax_code,
382 base_rate = X_base_rate,
383 start_date_active = X_start_date_active,
384 enabled_flag = X_enabled_flag,
385 last_update_date = X_last_update_date,
386 last_updated_by = X_last_updated_by,
387 creation_date = X_creation_date,
388 created_by = X_created_by,
389 last_update_login = X_last_update_login ,
390 attribute_category = X_attribute_category,
391 attribute1 = X_attribute1,
392 attribute2 = X_attribute2,
393 attribute3 = X_attribute3,
394 attribute4 = X_attribute4,
395 attribute5 = X_attribute5,
396 attribute6 = X_attribute6,
397 attribute7 = X_attribute7,
398 attribute8 = X_attribute8,
399 attribute9 = X_attribute9,
400 attribute10 = X_attribute10,
401 attribute11 = X_attribute11,
402 attribute12 = X_attribute12,
403 attribute13 = X_attribute13,
404 attribute14 = X_attribute14,
405 attribute15 = X_attribute15
406 WHERE rowid = X_Rowid;
407
408 IF (SQL%NOTFOUND) THEN
409 RAISE NO_DATA_FOUND;
410 END IF;
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 IF (SQLCODE <> -20001) THEN
415 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
416 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
417 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
418 FND_MESSAGE.SET_TOKEN('PARAMETERS','fiscal_classification_code = ' ||
419 X_fiscal_classification_code ||
420 'tax_category_id = ' || X_tax_category_id ||
421 'end_date_active = ' || X_end_date_active );
422 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
423 END IF;
424 APP_EXCEPTION.RAISE_EXCEPTION;
425 END Update_Row;
426
427 PROCEDURE Delete_Row
428 (X_Rowid VARCHAR2,
429 X_calling_sequence VARCHAR2) IS
430
431 current_calling_sequence VARCHAR2(2000);
432 debug_info VARCHAR2(100);
433
434 BEGIN
435 -- Update the calling sequence
436 --
437 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.DELETE_ROW<-' ||
438 X_calling_sequence;
439 debug_info := 'Delete from JL_ZZ_AR_TX_FSC_CLS';
440
441 DELETE FROM JL_ZZ_AR_TX_FSC_CLS
442 WHERE rowid = X_Rowid;
443
444 IF (SQL%NOTFOUND) THEN
445 RAISE NO_DATA_FOUND;
446 END IF;
447
448 EXCEPTION
449 WHEN OTHERS THEN
450 IF (SQLCODE <> -20001) THEN
451 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
452 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
453 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
454 FND_MESSAGE.SET_TOKEN('PARAMETERS','ROWID = ' || X_Rowid);
455 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
456 END IF;
457 APP_EXCEPTION.RAISE_EXCEPTION;
458
459 END Delete_Row;
460
461
462 PROCEDURE Check_Unique
463 (X_rowid VARCHAR2,
464 X_fiscal_classification_code VARCHAR2,
465 X_tax_category_id NUMBER,
466 X_end_date_active DATE,
467 X_org_id NUMBER,
468 X_calling_sequence IN VARCHAR2) IS
469
470 l_dummy NUMBER;
471 current_calling_sequence VARCHAR2(2000);
472 debug_info VARCHAR2(100);
473
474 BEGIN
475 -- Update the calling sequence
476 --
477 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.CHECK_UNIQUE<-' ||
478 X_calling_sequence;
479 SELECT COUNT(1)
480 INTO l_dummy
481 FROM jl_zz_ar_tx_fsc_cls
482 WHERE fiscal_classification_code = X_fiscal_classification_code
483 AND tax_category_id = X_tax_category_id
484 AND end_date_active = X_end_date_active
485 AND org_id = X_org_id
486 AND ((X_rowid IS NULL) OR (rowid <> X_rowid));
487
488 IF (l_dummy >=1) THEN
489 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
490 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
491 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
492 FND_MESSAGE.SET_TOKEN('PARAMETERS',
493 ' fiscal_classification_code = ' || X_fiscal_classification_code ||
494 ' tax_category_id = ' || X_tax_category_id ||
495 ' end_date_active = ' || X_end_date_active);
496 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
497 APP_EXCEPTION.RAISE_EXCEPTION;
498 END IF;
499 END Check_Unique;
500
501 PROCEDURE Check_Overlapped_Dates
502 (X_rowid VARCHAR2,
503 X_fiscal_classification_code VARCHAR2,
504 X_tax_category_id NUMBER,
505 X_end_date_active DATE,
506 X_start_date_active DATE,
507 X_org_id NUMBER,
508 X_calling_sequence IN VARCHAR2) IS
509
510 l_dummy NUMBER;
511 current_calling_sequence VARCHAR2(2000);
512 debug_info VARCHAR2(100);
513
514 BEGIN
515 -- Update the calling sequence
516 --
517 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.<-CHECK_OVERLAPPED_DATES' ||
518 X_calling_sequence;
519 SELECT COUNT(1)
520 INTO l_dummy
521 FROM jl_zz_ar_tx_fsc_cls a
522 WHERE a.fiscal_classification_code = X_fiscal_classification_code
523 AND a.tax_category_id = X_tax_category_id
524 AND ((a.end_date_active <= X_end_date_active AND
525 a.end_date_active >= X_start_date_active) OR
526 (a.start_date_active <= X_end_date_active AND
527 a.start_date_active >= X_start_date_active) OR
528 (a.start_date_active <= X_start_date_active AND
529 a.end_date_active >= X_end_date_active))
530 AND org_id = X_org_id
531 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
532
533 IF (l_dummy >=1) THEN
534 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
535 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
536 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
537 FND_MESSAGE.SET_TOKEN('PARAMETERS',
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
547 PROCEDURE Check_Gaps
548 (X_rowid VARCHAR2,
549 X_fiscal_classification_code VARCHAR2,
550 X_tax_category_id NUMBER,
551 X_end_date_active DATE,
552 X_start_date_active DATE,
553 X_org_id NUMBER,
554 X_calling_sequence IN VARCHAR2) IS
555
556 l_dummy NUMBER;
557 l_dummy1 NUMBER;
558 l_dummy2 NUMBER;
559 current_calling_sequence VARCHAR2(2000);
560 debug_info VARCHAR2(100);
561
562 BEGIN
563 -- Update the calling sequence
564 --
565 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.CHECK_GAPS<-' ||
566 X_calling_sequence;
567 --
568 -- Check if there is one row with it's end date exactly one day
569 -- less than the current row's start date
570 --
571 SELECT COUNT(1)
572 INTO l_dummy
573 FROM jl_zz_ar_tx_fsc_cls a
574 WHERE a.fiscal_classification_code = X_fiscal_classification_code
575 AND a.tax_category_id = X_tax_category_id
576 AND trunc(a.end_date_active) = (trunc(X_start_date_active) -1)
577 AND org_id = X_org_id
578 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
579
580 IF (l_dummy = 0) THEN
581 BEGIN
582 --
583 -- Check if there is one row with the start date one day more than the
584 -- the current row's end-date
585 --
586 SELECT COUNT(1)
587 INTO l_dummy1
588 FROM jl_zz_ar_tx_fsc_cls a
589 WHERE a.fiscal_classification_code = X_fiscal_classification_code
590 AND a.tax_category_id = X_tax_category_id
591 AND (trunc(a.start_date_active) = (trunc(X_end_date_active) + 1))
592 AND org_id = X_org_id
593 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
594
595 -- Check if there are no (other) rows at all for the primary key.
596 -- If there are no rows, then it is not an error. Otherwise, it is.
597 --
598 IF (l_dummy1 = 0) THEN
599 BEGIN
600 SELECT COUNT(1)
601 INTO l_dummy2
602 FROM jl_zz_ar_tx_fsc_cls a
603 WHERE a.fiscal_classification_code = X_fiscal_classification_code
604 AND a.tax_category_id = X_tax_category_id
605 AND org_id = X_org_id
606 AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
607
608 IF (l_dummy2 <> 0) THEN
609 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
610 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
611 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
612 FND_MESSAGE.SET_TOKEN('PARAMETERS',
613 ' fiscal_classification_code = ' || X_fiscal_classification_code ||
614 ' tax_category_id = ' || X_tax_category_id ||
615 ' end_date_active = ' || X_end_date_active );
616 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
617 APP_EXCEPTION.RAISE_EXCEPTION;
618 END IF;
619 END;
620 END IF;
621 END;
622 END IF;
623 END Check_Gaps;
624
625 PROCEDURE Create_Category
626 (X_Rowid IN OUT NOCOPY VARCHAR2,
627 X_structure_id NUMBER,
628 X_segment1 VARCHAR2,
629 X_summary_flag VARCHAR2,
630 X_enabled_flag VARCHAR2,
631 X_start_date_active DATE,
632 X_end_date_active DATE,
633 X_description VARCHAR2,
634 X_attribute_category VARCHAR2,
635 X_attribute1 VARCHAR2,
636 X_attribute2 VARCHAR2,
637 X_attribute3 VARCHAR2,
638 X_attribute4 VARCHAR2,
639 X_attribute5 VARCHAR2,
640 X_attribute6 VARCHAR2,
641 X_attribute7 VARCHAR2,
642 X_attribute8 VARCHAR2,
643 X_attribute9 VARCHAR2,
644 X_attribute10 VARCHAR2,
645 X_attribute11 VARCHAR2,
646 X_attribute12 VARCHAR2,
647 X_attribute13 VARCHAR2,
648 X_attribute14 VARCHAR2,
649 X_attribute15 VARCHAR2,
650 X_calling_sequence VARCHAR2) IS
651
652 l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
653 l_return_status VARCHAR2(80);
654 l_errorcode NUMBER;
655 l_msg_count NUMBER;
656 l_msg_data VARCHAR2(240);
657 l_category_id NUMBER;
658
659 l_api_version CONSTANT NUMBER := 1.0;
660
661 current_calling_sequence VARCHAR2(2000);
662 debug_info VARCHAR2(100);
663
664 BEGIN
665 -- Update the calling sequence
666 --
667 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
668
669 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.CREATE_CATEGORY<-' ||
670 X_calling_sequence;
671
672 l_category_rec.structure_id := X_structure_id;
673 l_category_rec.segment1 := X_segment1;
674 l_category_rec.summary_flag := X_summary_flag;
675 l_category_rec.enabled_flag := X_enabled_flag;
676 l_category_rec.start_date_active := X_start_date_active;
677 l_category_rec.end_date_active := X_end_date_active;
678 l_category_rec.description := X_description;
679 l_category_rec.attribute_category := X_attribute_category;
680 l_category_rec.attribute1 := X_attribute1;
681 l_category_rec.attribute2 := X_attribute2;
682 l_category_rec.attribute3 := X_attribute3;
683 l_category_rec.attribute4 := X_attribute4;
684 l_category_rec.attribute5 := X_attribute5;
685 l_category_rec.attribute6 := X_attribute6;
686 l_category_rec.attribute7 := X_attribute7;
687 l_category_rec.attribute8 := X_attribute8;
688 l_category_rec.attribute9 := X_attribute9;
689 l_category_rec.attribute10 := X_attribute10;
690 l_category_rec.attribute11 := X_attribute11;
691 l_category_rec.attribute12 := X_attribute12;
692 l_category_rec.attribute13 := X_attribute13;
693 l_category_rec.attribute14 := X_attribute14;
694 l_category_rec.attribute15 := X_attribute15;
695
696 debug_info := 'calling INV_ITEM_CATEGORY_PUB.GET_CATEGORY_ID_FROM_CAT_REC';
697
698 INV_ITEM_CATEGORY_PUB.GET_CATEGORY_ID_FROM_CAT_REC
699 (
700 p_category_rec => l_category_rec,
701 x_category_id => l_category_id,
702 x_return_status => l_return_status,
703 x_msg_data => l_msg_data
704 );
705
706 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
707 IF l_category_id = -1 THEN -- Category does not exist, create new category
708 debug_info := 'calling INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY';
709
710 INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY
711 (
712 P_API_VERSION => l_api_version,
713 P_INIT_MSG_LIST => FND_API.G_FALSE,
714 P_COMMIT => FND_API.G_FALSE,
715 X_RETURN_STATUS => l_return_status,
716 X_ERRORCODE => l_errorcode,
717 X_MSG_COUNT => l_msg_count,
718 X_MSG_DATA => l_msg_data,
719 P_CATEGORY_REC => l_category_rec,
720 X_CATEGORY_ID => l_category_id
721 );
722
723 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
724 IF l_msg_count > 0 THEN
725 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
726 FND_MESSAGE.SET_TOKEN('ERROR', l_msg_data);
727 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
728 FND_MESSAGE.SET_TOKEN('PARAMETERS','segment1 = ' || X_segment1 ||
729 'structure_id = ' || X_structure_id ||
730 'end_date_active = ' || X_end_date_active );
731 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
732 END IF;
733 APP_EXCEPTION.RAISE_EXCEPTION;
734 END IF;
735
736 IF (g_level_procedure >= g_current_runtime_level ) THEN
737 FND_LOG.STRING(g_level_procedure,
738 'JL.PLSQL.JL_ZZ_AR_TX_FSC_CLS_PKG.CREATE_CATEGORY',
739 'New Category created in MTL_CATEGORIES_B. '||
740 'Category_Id : '||l_category_id);
741 END IF;
742
743 ELSE -- Category already exists
744 IF (g_level_procedure >= g_current_runtime_level ) THEN
745 FND_LOG.STRING(g_level_procedure,
746 'JL.PLSQL.JL_ZZ_AR_TX_FSC_CLS_PKG.CREATE_CATEGORY',
747 'Category Already Exists in MTL_CATEGORIES_B. '||
748 'Category_Id : '||l_category_id);
749 END IF;
750 END IF;
751
752 ELSE
753 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
754 FND_MESSAGE.SET_TOKEN('ERROR', l_msg_data);
755 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
756 FND_MESSAGE.SET_TOKEN('PARAMETERS','segment1 = ' || X_segment1 ||
757 'structure_id = ' || X_structure_id ||
758 'end_date_active = ' || X_end_date_active );
759 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
760 APP_EXCEPTION.RAISE_EXCEPTION;
761 END IF;
762
763 EXCEPTION
764 WHEN OTHERS THEN
765 IF (SQLCODE <> -20001) THEN
766 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
767 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
768 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
769 FND_MESSAGE.SET_TOKEN('PARAMETERS','segment1 = ' ||
770 X_segment1 ||
771 'structure_id = ' || X_structure_id ||
772 'end_date_active = ' || X_end_date_active );
773 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
774 END IF;
775 APP_EXCEPTION.RAISE_EXCEPTION;
776 END Create_category;
777
778
779 PROCEDURE Update_Category
780 (X_category_id NUMBER,
781 X_structure_id NUMBER,
782 X_disable_date DATE,
783 X_web_status VARCHAR2,
784 X_supplier_enabled_flag VARCHAR2,
785 X_segment1 VARCHAR2,
786 X_segment2 VARCHAR2,
787 X_segment3 VARCHAR2,
788 X_segment4 VARCHAR2,
789 X_segment5 VARCHAR2,
790 X_segment6 VARCHAR2,
791 X_segment7 VARCHAR2,
792 X_segment8 VARCHAR2,
793 X_segment9 VARCHAR2,
794 X_segment10 VARCHAR2,
795 X_segment11 VARCHAR2,
796 X_segment12 VARCHAR2,
797 X_segment13 VARCHAR2,
798 X_segment14 VARCHAR2,
799 X_segment15 VARCHAR2,
800 X_segment16 VARCHAR2,
801 X_segment17 VARCHAR2,
802 X_segment18 VARCHAR2,
803 X_segment19 VARCHAR2,
804 X_segment20 VARCHAR2,
805 X_summary_flag VARCHAR2,
806 X_enabled_flag VARCHAR2,
807 X_start_date_active DATE,
808 X_end_date_active DATE,
809 X_description VARCHAR2,
810 X_attribute_category VARCHAR2,
811 X_attribute1 VARCHAR2,
812 X_attribute2 VARCHAR2,
813 X_attribute3 VARCHAR2,
814 X_attribute4 VARCHAR2,
815 X_attribute5 VARCHAR2,
816 X_attribute6 VARCHAR2,
817 X_attribute7 VARCHAR2,
818 X_attribute8 VARCHAR2,
819 X_attribute9 VARCHAR2,
820 X_attribute10 VARCHAR2,
821 X_attribute11 VARCHAR2,
822 X_attribute12 VARCHAR2,
823 X_attribute13 VARCHAR2,
824 X_attribute14 VARCHAR2,
825 X_attribute15 VARCHAR2,
826 X_calling_sequence VARCHAR2) IS
827
828 l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
829 l_return_status VARCHAR2(80);
830 l_errorcode NUMBER;
831 l_msg_count NUMBER;
832 l_msg_data VARCHAR2(240);
833
834 l_api_version CONSTANT NUMBER := 1.0;
835
836 current_calling_sequence VARCHAR2(2000);
837 debug_info VARCHAR2(100);
838
839 BEGIN
840 -- Update the calling sequence
841 --
842 current_calling_sequence := 'JL_ZZ_AR_TX_FSC_CLS_PKG.UPDATE_CATEGORY<-' ||
843 X_calling_sequence;
844
845 debug_info := 'calling INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY';
846
847 l_category_rec.category_id := X_category_id;
848 l_category_rec.structure_id := X_structure_id;
849 l_category_rec.disable_date := X_disable_date;
850 l_category_rec.web_status := X_web_status;
851 l_category_rec.supplier_enabled_flag := X_supplier_enabled_flag;
852 l_category_rec.segment1 := X_segment1;
853 l_category_rec.segment2 := X_segment2;
854 l_category_rec.segment3 := X_segment3;
855 l_category_rec.segment4 := X_segment4;
856 l_category_rec.segment5 := X_segment5;
857 l_category_rec.segment6 := X_segment6;
858 l_category_rec.segment7 := X_segment7;
859 l_category_rec.segment8 := X_segment8;
860 l_category_rec.segment9 := X_segment9;
861 l_category_rec.segment10 := X_segment10;
862 l_category_rec.segment11 := X_segment11;
863 l_category_rec.segment12 := X_segment12;
864 l_category_rec.segment13 := X_segment13;
865 l_category_rec.segment14 := X_segment14;
866 l_category_rec.segment15 := X_segment15;
867 l_category_rec.segment16 := X_segment16;
868 l_category_rec.segment17 := X_segment17;
869 l_category_rec.segment18 := X_segment18;
870 l_category_rec.segment19 := X_segment19;
871 l_category_rec.segment20 := X_segment20;
872 l_category_rec.summary_flag := X_summary_flag;
873 l_category_rec.enabled_flag := X_enabled_flag;
874 l_category_rec.start_date_active := X_start_date_active;
875 l_category_rec.end_date_active := X_end_date_active;
876 l_category_rec.description := X_description;
877 l_category_rec.attribute_category := X_attribute_category;
878 l_category_rec.attribute1 := X_attribute1;
879 l_category_rec.attribute2 := X_attribute2;
880 l_category_rec.attribute3 := X_attribute3;
881 l_category_rec.attribute4 := X_attribute4;
882 l_category_rec.attribute5 := X_attribute5;
883 l_category_rec.attribute6 := X_attribute6;
884 l_category_rec.attribute7 := X_attribute7;
885 l_category_rec.attribute8 := X_attribute8;
886 l_category_rec.attribute9 := X_attribute9;
887 l_category_rec.attribute10 := X_attribute10;
888 l_category_rec.attribute11 := X_attribute11;
889 l_category_rec.attribute12 := X_attribute12;
890 l_category_rec.attribute13 := X_attribute13;
891 l_category_rec.attribute14 := X_attribute14;
892 l_category_rec.attribute15 := X_attribute15;
893
894 INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY
895 (
896 P_API_VERSION => l_api_version,
897 P_INIT_MSG_LIST => FND_API.G_FALSE,
898 P_COMMIT => FND_API.G_FALSE,
899 X_RETURN_STATUS => l_return_status,
900 X_ERRORCODE => l_errorcode,
901 X_MSG_COUNT => l_msg_count,
902 X_MSG_DATA => l_msg_data,
903 P_CATEGORY_REC => l_category_rec
904 );
905
906 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
907 IF l_msg_count > 0 THEN
908 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
909 FND_MESSAGE.SET_TOKEN('ERROR', l_msg_data);
910 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
911 FND_MESSAGE.SET_TOKEN('PARAMETERS','segment1 = ' ||
912 X_segment1 ||
913 'structure_id = ' || X_structure_id ||
914 'end_date_active = ' || X_end_date_active );
915 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
916 END IF;
917 APP_EXCEPTION.RAISE_EXCEPTION;
918 END IF;
919
920
921 EXCEPTION
922 WHEN OTHERS THEN
923 IF (SQLCODE <> -20001) THEN
924 FND_MESSAGE.SET_NAME('AR','AR_DEBUG');
925 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
926 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
927 FND_MESSAGE.SET_TOKEN('PARAMETERS','segment1 = ' ||
928 X_segment1 ||
929 'structure_id = ' || X_structure_id ||
930 'category_id = ' || X_category_id );
931 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
932 END IF;
933 APP_EXCEPTION.RAISE_EXCEPTION;
934 END Update_Category;
935
936 -- Constructor
937 BEGIN
938 g_level_statement := FND_LOG.LEVEL_STATEMENT;
939 g_level_procedure := FND_LOG.LEVEL_PROCEDURE;
940 g_level_event := FND_LOG.LEVEL_EVENT;
941 g_level_exception := FND_LOG.LEVEL_EXCEPTION;
942 g_level_unexpected := FND_LOG.LEVEL_UNEXPECTED;
943
944 END JL_ZZ_AR_TX_FSC_CLS_PKG;