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