1: PACKAGE BODY JL_ZZ_AR_TX_SCHEDULES_PKG AS
2: /* $Header: jlzztscb.pls 120.5 2005/03/31 22:34:13 nipatel ship $ */
3:
4: PROCEDURE Insert_Row
5: (X_rowid IN OUT NOCOPY VARCHAR2,
35: X_calling_sequence IN VARCHAR2) IS
36:
37: CURSOR C IS
38: SELECT rowid
39: FROM jl_zz_ar_tx_schedules
40: WHERE tax_category_id = X_tax_category_id
41: AND tax_categ_slab_id = X_tax_categ_slab_id;
42: --AND end_date_active = X_end_date_active;
43:
46:
47: BEGIN
48: -- Update the calling sequence
49: --
50: current_calling_sequence := 'JL_ZZ_AR_TX_SCHEDULES_PKG.INSERT_ROW<-' ||
51: X_calling_sequence;
52:
53: debug_info := 'Insert into JL_ZZ_AR_TX_SCHEDULES ';
54: INSERT INTO jl_zz_ar_tx_schedules (tax_categ_slab_id,
49: --
50: current_calling_sequence := 'JL_ZZ_AR_TX_SCHEDULES_PKG.INSERT_ROW<-' ||
51: X_calling_sequence;
52:
53: debug_info := 'Insert into JL_ZZ_AR_TX_SCHEDULES ';
54: INSERT INTO jl_zz_ar_tx_schedules (tax_categ_slab_id,
55: tax_category_id,
56: end_date_active,
57: min_taxable_basis,
50: current_calling_sequence := 'JL_ZZ_AR_TX_SCHEDULES_PKG.INSERT_ROW<-' ||
51: X_calling_sequence;
52:
53: debug_info := 'Insert into JL_ZZ_AR_TX_SCHEDULES ';
54: INSERT INTO jl_zz_ar_tx_schedules (tax_categ_slab_id,
55: tax_category_id,
56: end_date_active,
57: min_taxable_basis,
58: last_update_date,
199: ATTRIBUTE12,
200: ATTRIBUTE13,
201: ATTRIBUTE14,
202: ATTRIBUTE15
203: FROM jl_zz_ar_tx_schedules
204: WHERE rowid = X_rowid
205: FOR UPDATE OF tax_category_id,
206: tax_categ_slab_id,
207: end_date_active
214:
215: BEGIN
216: -- Update the calling sequence
217: --
218: current_calling_sequence := 'JL_ZZ_AR_TX_SCHEDULES_PKG.LOCK_ROW<-' ||
219: X_calling_sequence;
220: debug_info := 'Open cursor C';
221: OPEN C;
222: debug_info := 'Fetch cursor C';
371:
372: BEGIN
373: --Update the calling sequence
374: --
375: current_calling_sequence := 'JL_ZZ_AR_TX_SCHEDULES_PKG.UPDATE_ROW<-' ||
376: X_calling_sequence;
377: debug_info := 'Update JL_ZZ_AR_TX_SCHEDULES';
378:
379: UPDATE jl_zz_ar_tx_schedules
373: --Update the calling sequence
374: --
375: current_calling_sequence := 'JL_ZZ_AR_TX_SCHEDULES_PKG.UPDATE_ROW<-' ||
376: X_calling_sequence;
377: debug_info := 'Update JL_ZZ_AR_TX_SCHEDULES';
378:
379: UPDATE jl_zz_ar_tx_schedules
380: SET tax_categ_slab_id = X_tax_categ_slab_id,
381: tax_category_id = X_tax_category_id,
375: current_calling_sequence := 'JL_ZZ_AR_TX_SCHEDULES_PKG.UPDATE_ROW<-' ||
376: X_calling_sequence;
377: debug_info := 'Update JL_ZZ_AR_TX_SCHEDULES';
378:
379: UPDATE jl_zz_ar_tx_schedules
380: SET tax_categ_slab_id = X_tax_categ_slab_id,
381: tax_category_id = X_tax_category_id,
382: end_date_active = X_end_date_active,
383: min_taxable_basis = X_min_taxable_basis,
421: X_calling_sequence IN VARCHAR2) IS
422:
423: BEGIN
424: DELETE
425: FROM jl_zz_ar_tx_schedules
426: WHERE rowid = X_rowid;
427:
428: IF (SQL%NOTFOUND) THEN
429: raise NO_DATA_FOUND;
447: current_calling_sequence :='JL_ZZ_AR_TX_SCHEDULE_PKG.CHECK_UNIQUE<-' ||
448: X_calling_sequence;
449: SELECT COUNT(1)
450: INTO l_dummy
451: FROM jl_zz_ar_tx_schedules
452: WHERE tax_category_id = X_tax_category_id
453: AND tax_categ_slab_id = X_tax_categ_slab_id
454: AND end_date_active = X_end_date_active
455: AND ((X_rowid IS NULL) OR (rowid <> X_rowid));
482:
483: BEGIN
484: -- Update the calling sequence
485: --
486: current_calling_sequence :=' JL_ZZ_AR_TX_SCHEDULES_PKG.<-CHECK_OVERLAPPED_DATES' ||
487: X_calling_sequence;
488: SELECT COUNT(1)
489: INTO l_dummy
490: FROM jl_zz_ar_tx_schedules a
486: current_calling_sequence :=' JL_ZZ_AR_TX_SCHEDULES_PKG.<-CHECK_OVERLAPPED_DATES' ||
487: X_calling_sequence;
488: SELECT COUNT(1)
489: INTO l_dummy
490: FROM jl_zz_ar_tx_schedules a
491: WHERE tax_category_id = X_tax_category_id
492: AND tax_categ_slab_id = X_tax_categ_slab_id
493: AND ((a.end_date_active <= X_end_date_active AND
494: a.end_date_active >= X_start_date_active) OR
528:
529: BEGIN
530: -- Update the calling sequence
531: --
532: current_calling_sequence := 'JL_ZZ_AR_TX_SCHEDULES_PKG.CHECK_GAPS<-' ||
533: X_calling_sequence;
534: --
535: -- Check if there is one row with it's end date exactly one day
536: -- less than the current row's start date
536: -- less than the current row's start date
537: --
538: SELECT COUNT(1)
539: INTO l_dummy
540: FROM jl_zz_ar_tx_schedules a
541: WHERE a.tax_category_id = X_tax_category_id
542: AND a.tax_categ_slab_id = X_tax_categ_slab_id
543: AND trunc(a.end_date_active) = (trunc(X_start_date_active) -1)
544: AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
550: -- the current row's end-date
551: --
552: SELECT COUNT(1)
553: INTO l_dummy1
554: FROM jl_zz_ar_tx_schedules a
555: WHERE a.tax_category_id = X_tax_category_id
556: AND a.tax_categ_slab_id = X_tax_categ_slab_id
557: AND (trunc(a.start_date_active) = (trunc(X_end_date_active) + 1))
558: AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
563: IF (l_dummy1 = 0) THEN
564: BEGIN
565: SELECT COUNT(1)
566: INTO l_dummy2
567: FROM jl_zz_ar_tx_schedules a
568: WHERE a.tax_category_id = X_tax_category_id
569: AND a.tax_categ_slab_id = X_tax_categ_slab_id
570: AND ((X_rowid IS NULL) OR (a.rowid <> X_rowid));
571:
586: END;
587: END IF;
588: END Check_Gaps;
589:
590: END JL_ZZ_AR_TX_SCHEDULES_PKG;