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