-- ABS-167648

---ERP_I_TAX_STATES table---
CREATE TABLE ERP_I_TAX_STATES
(
  TRANSFER_ID                    NUMBER NOT NULL,
  TRANSFER_STATUS                NUMBER DEFAULT 0 NOT NULL,
  STATECODE                      VARCHAR2(6) NOT NULL, 
  TAXTYPE                        NUMBER NOT NULL, 
  STARTDATE                      DATE DEFAULT TRUNC(SYSDATE) NOT NULL, 
  TAXRATE                        NUMBER(7,3) NOT NULL, 
  TIMESTAMP                      DATE DEFAULT SYSDATE NOT NULL
);
 
ALTER TABLE ERP_I_TAX_STATES
  ADD CONSTRAINT PK_ERP_I_TAX_STATES PRIMARY KEY (TRANSFER_ID);

---ERP_I_SUPPLIERMODEL_LOG table---
CREATE TABLE ERP_I_TAX_STATES_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_TAXSTATES_TRANSFER_STATUS
                         CHECK (TRANSFER_STATUS IN (0, 1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_TAXSTATES_PROCESS_STATUS
                         CHECK (PROCESS_STATUS IN (0, 1)),
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);


COMMENT ON COLUMN ERP_I_TAX_STATES_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_TAX_STATES_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
ALTER TABLE ERP_I_TAX_STATES_LOG
  ADD CONSTRAINT PK_ERP_I_TAXSTATES_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_I_TAX_STATES_LOG
  ADD CONSTRAINT FK_ERP_I_TAXSTATES_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_TAX_STATES(TRANSFER_ID) ON DELETE CASCADE;
  

---ERP_I_TAX_CITYCOUNTY table---
CREATE TABLE ERP_I_TAX_CITYCOUNTY
(
  TRANSFER_ID                    NUMBER NOT NULL,
  TRANSFER_STATUS                NUMBER DEFAULT 0 NOT NULL,
  TAXAUTHORITYCODE               VARCHAR2(12) NOT NULL,
  TAXTYPE                        NUMBER NOT NULL, 
  STARTDATE                      DATE DEFAULT TRUNC(SYSDATE) NOT NULL,  
  TAXRATE                        NUMBER(7,3) NOT NULL,
  NAME                           VARCHAR(30) NULL, 
  STATECODE                      VARCHAR(6) NULL, 
  TURNOVERGROUPCODE              VARCHAR(12) NULL, 
  TIMESTAMP                      DATE DEFAULT SYSDATE NOT NULL,
  TYPE                           NUMBER
);

 
ALTER TABLE ERP_I_TAX_CITYCOUNTY
  ADD CONSTRAINT PK_ERP_I_TAX_CITYCOUNTY PRIMARY KEY (TRANSFER_ID);

COMMENT ON COLUMN ERP_I_TAX_CITYCOUNTY.TYPE
  IS '1=City, 2=County';

---ERP_I_SUPPLIERMODEL_LOG table---
CREATE TABLE ERP_I_TAX_CITYCOUNTY_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_TAX_CITYCOUNTY_TRANSFER_STATUS
                         CHECK (TRANSFER_STATUS IN (0, 1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT ERP_I_TAX_CITYCOUNTY_PROCESS_STATUS
                         CHECK (PROCESS_STATUS IN (0, 1)),
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN ERP_I_TAX_CITYCOUNTY_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_TAX_CITYCOUNTY_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
  
ALTER TABLE ERP_I_TAX_CITYCOUNTY_LOG
  ADD CONSTRAINT PK_ERP_I_TAX_CITYCOUNTY_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE ERP_I_TAX_CITYCOUNTY_LOG
  ADD CONSTRAINT FK_ERP_I_TAX_CITYCOUNTY_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_TAX_CITYCOUNTY(TRANSFER_ID) ON DELETE CASCADE;
