-- ABS-100319/ ABS-106636

----------------- MAATEMPLATEATTRIBUTE TABLE -----------------
CREATE SEQUENCE SEQ_MAATEMPLATEATTRIBUTE
START WITH 1
INCREMENT BY 1;

CREATE TABLE MAATEMPLATEATTRIBUTE
(
  MAATEMPLATEATTRIBUTE_ID NUMBER NOT NULL,
  DESCRIPTION             VARCHAR2(50) NOT NULL,
  MODULE                  VARCHAR2(100) NULL,
  DISABLE                 VARCHAR2(1) DEFAULT 'N' NOT NULL
                          CONSTRAINT CHK_MAATEMPLATT_DISABLE CHECK (DISABLE IN ('Y', 'N')),
  HIDE                    VARCHAR2(1) DEFAULT 'N' NOT NULL
                          CONSTRAINT CHK_MAATEMPLATT_HIDE CHECK (HIDE IN ('Y', 'N')),
  DECREASEADJUSTMENTLIMIT VARCHAR2(1) DEFAULT 'N' NOT NULL
                          CONSTRAINT CHK_MAATEMPLATT_DECREASEADJ CHECK (DECREASEADJUSTMENTLIMIT IN ('Y', 'N')),
  INVOICELINETYE_ID       NUMBER NULL,
  ROUTEASSISTANTVERSION   VARCHAR2(5) NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
);

ALTER TABLE MAATEMPLATEATTRIBUTE
  ADD CONSTRAINT XPKMAATEMPLATEATTRIBUTE PRIMARY KEY (MAATEMPLATEATTRIBUTE_ID);
  
CREATE INDEX XIF1MAATEMPLATEATTRIBUTE ON MAATEMPLATEATTRIBUTE (INVOICELINETYE_ID ASC);

ALTER TABLE MAATEMPLATEATTRIBUTE
  ADD CONSTRAINT FK_MAATEMPLATT_INVLINETYPE FOREIGN KEY (INVOICELINETYE_ID)
  REFERENCES INVOICELINETYPE (INVOICELINETYPE_ID) ON DELETE CASCADE;

-------------- MAATEMPLATEHEADER TABLE -----------------
CREATE SEQUENCE SEQ_MAATEMPLATEHEADER
START WITH 1
INCREMENT BY 1;

CREATE TABLE MAATEMPLATEHEADER
(
  MAATEMPLATEHEADER_ID NUMBER NOT NULL,
  CODE                 VARCHAR2(10) NOT NULL,
  DESCRIPTION          VARCHAR2(50) NOT NULL,
  TYPE                 NUMBER DEFAULT 1 NOT NULL
                       CONSTRAINT CHK_TYPE CHECK (TYPE IN (1)),
  SYSTEMUSER_ID        NUMBER NOT NULL,
  TIMESTAMP            DATE NOT NULL
);

COMMENT ON COLUMN MAATEMPLATEHEADER.TYPE
  IS '1 = RASA';

ALTER TABLE MAATEMPLATEHEADER
  ADD CONSTRAINT XPKMAATEMPLATEHEADER PRIMARY KEY (MAATEMPLATEHEADER_ID);

-------------- MAATEMPLATEDETAIL TABLE -----------------
CREATE SEQUENCE SEQ_MAATEMPLATEDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE MAATEMPLATEDETAIL
(
  MAATEMPLATEDETAIL_ID    NUMBER NOT NULL,
  MAATEMPLATEHEADER_ID    NUMBER NOT NULL,
  MAATEMPLATEATTRIBUTE_ID NUMBER NOT NULL,
  DISABLE                 VARCHAR2(1) DEFAULT 'N' NOT NULL,
  HIDE                    VARCHAR2(1) DEFAULT 'N' NOT NULL,
  DECREASEADJUSTPERCENT   NUMBER(5,2) NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
);

ALTER TABLE MAATEMPLATEDETAIL
  ADD CONSTRAINT XPKMAATEMPLATEDETAIL PRIMARY KEY (MAATEMPLATEDETAIL_ID);
  
CREATE UNIQUE INDEX XAK1MAATEMPLATEDETAIL ON MAATEMPLATEDETAIL (MAATEMPLATEHEADER_ID ASC, MAATEMPLATEATTRIBUTE_ID ASC);

CREATE INDEX XIF1MAATEMPLATEDETAIL ON MAATEMPLATEDETAIL (MAATEMPLATEHEADER_ID ASC);
CREATE INDEX XIF2MAATEMPLATEDETAIL ON MAATEMPLATEDETAIL (MAATEMPLATEATTRIBUTE_ID ASC);

ALTER TABLE MAATEMPLATEDETAIL
  ADD CONSTRAINT FK_MAATEMPLDET_MAATEMPLH FOREIGN KEY (MAATEMPLATEHEADER_ID)
  REFERENCES MAATEMPLATEHEADER (MAATEMPLATEHEADER_ID) ON DELETE CASCADE;
  
ALTER TABLE MAATEMPLATEDETAIL
  ADD CONSTRAINT FK_MAATEMPLDET_MAATEMPLA FOREIGN KEY (MAATEMPLATEATTRIBUTE_ID)
  REFERENCES MAATEMPLATEATTRIBUTE (MAATEMPLATEATTRIBUTE_ID) ON DELETE CASCADE;

----------------- CONTRACT TABLE ---------------- 
ALTER TABLE CONTRACT ADD (RAATEMPLATEHEADER_ID NUMBER NULL);

COMMENT ON COLUMN CONTRACT.RAATEMPLATEHEADER_ID
  IS 'Used for module MOBILEAPPACCESSTEMPLATE';

CREATE INDEX XIF22CONTRACT ON CONTRACT (RAATEMPLATEHEADER_ID ASC);

ALTER TABLE CONTRACT
  ADD CONSTRAINT FK_CONTRACT_MAATEMPLH FOREIGN KEY (RAATEMPLATEHEADER_ID)
  REFERENCES MAATEMPLATEHEADER (MAATEMPLATEHEADER_ID) ON DELETE SET NULL;

-------------- CUSTOMEREXTRAINFO TABLE ----------- 
ALTER TABLE CUSTOMEREXTRAINFO ADD (RAATEMPLATEHEADER_ID NUMBER NULL);

COMMENT ON COLUMN CUSTOMEREXTRAINFO.RAATEMPLATEHEADER_ID
  IS 'Used for module MOBILEAPPACCESSTEMPLATE';

CREATE INDEX XIF1CUSTOMEREXTRAINFO ON CUSTOMEREXTRAINFO (RAATEMPLATEHEADER_ID ASC);

ALTER TABLE CUSTOMEREXTRAINFO
  ADD CONSTRAINT FK_CUSTEXTRAINFO_MAATEMPLH FOREIGN KEY (RAATEMPLATEHEADER_ID)
  REFERENCES MAATEMPLATEHEADER (MAATEMPLATEHEADER_ID) ON DELETE SET NULL;
