tax_package.sql

/*
|| 消費税率のカレンダーテーブル
*/
DROP TABLE TAX_CALENDAR;
/
-- TAX CALENDAR TABLE
CREATE TABLE TAX_CALENDAR (
  DtS date NOT NULL,
  DtE date NOT NULL,
  TAX number(3,2)
);
-- TAX TABLE
ALTER TABLE TAX_CALENDAR ADD CONSTRAINT DISP_TAX_CALENDAR_PK PRIMARY KEY (DtS,DtE);
/
/*
|| TAX_CALENDAR INSERTION
*/
DELETE FROM TAX_CALENDAR;
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1900-01-01'),to_date('1989-04-01'),0.000);
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1989-04-01'),to_date('1997-04-01'),0.03);
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1997-04-01'),to_date('2999-04-01'),0.05);


CREATE OR REPLACE PACKAGE TAX_PKG AS
  -- 日付で消費税率を返す
  FUNCTION TAX_RATE(i_date IN date DEFAULT SYSDATE) RETURN NUMBER;
  -- 日付と金額で消費税額を返す
  FUNCTION TAX(i_price IN NUMBER,i_date IN date DEFAULT SYSDATE) RETURN NUMBER;
  -- 日付と金額で税込金額を返す
  FUNCTION INCLUDE_TAX(i_price IN NUMBER,i_date IN date DEFAULT SYSDATE) RETURN NUMBER;
  PRAGMA RESTRICT_REFERENCES(TAX_RATE,WNDS);
  PRAGMA RESTRICT_REFERENCES(TAX,WNDS);
  PRAGMA RESTRICT_REFERENCES(INCLUDE_TAX,WNDS);
END TAX_PKG;
/

CREATE OR REPLACE PACKAGE BODY TAX_PKG AS
    /*
    || 日付で消費税率を返す
    ||  TAX_CALENDARテーブルを参照する
    */
    FUNCTION TAX_RATE(i_date IN date DEFAULT SYSDATE) RETURN NUMBER
    IS
    ATax number;
    BEGIN
        SELECT TAX INTO ATAX FROM TAX_CALENDAR WHERE i_date >= DtS AND i_date < DtE;
        RETURN ATAX;
    END;
    /*
    || 日付で消費税率を返す
    */
    FUNCTION TAX_RATE2(i_date IN date DEFAULT SYSDATE) RETURN NUMBER
    IS
    BEGIN
        IF i_date < to_date('89-04-01') THEN
            RETURN 0.00;
        ELSIF i_date < to_date('97-04-01') THEN
            RETURN 0.03;
        ELSE
            RETURN 0.05; --現状
        END IF;
    END;
    -------------------------------------------------------
    -- 日付と金額で消費税額を返す
    -------------------------------------------------------
    FUNCTION TAX(i_price IN NUMBER, i_date IN date DEFAULT SYSDATE) RETURN NUMBER
    IS
    BEGIN
        RETURN ROUND(i_price * TAX_RATE(i_date));
    END;
    -------------------------------------------------------
    -- 日付と金額で税込金額を返す
    -------------------------------------------------------
    FUNCTION INCLUDE_TAX(i_price IN NUMBER, i_date IN date DEFAULT SYSDATE) RETURN NUMBER
    IS
    BEGIN
        RETURN ROUND(i_price + (i_price * TAX_RATE(i_date)));
    END;
END TAX_PKG;
/


-------------------------------------------------------
-- 消費税計算のテスト用
-------------------------------------------------------
select tax_pkg.tax_rate('89-03-31'),
 tax_pkg.tax_rate('89-04-01'),
 tax_pkg.tax_rate('97-03-31'),
 tax_pkg.tax_rate('97-04-01'),
 tax_pkg.tax_rate(SYSDATE)
from dual;

select tax_pkg.tax(101,'89-03-31'),
 tax_pkg.tax(101,'89-04-01'),
 tax_pkg.tax(101,'97-03-31'),
 tax_pkg.tax(101,'97-04-01'),
 tax_pkg.tax(101,SYSDATE)
from dual;

select tax_pkg.include_tax(101,'89-03-31'),
 tax_pkg.include_tax(101,'89-04-01'),
 tax_pkg.include_tax(101,'97-03-31'),
 tax_pkg.include_tax(101,'97-04-01'),
 tax_pkg.include_tax(101,SYSDATE)
from dual;