-- ABS-74457 ERP interface commission --
-- ROUTECONTROL_COMMISSION --
CREATE TABLE ROUTECONTROL_COMMISSION
(
  ROUTECONTROL_ID          NUMBER NOT NULL,
  SERVICECOMMISSION_EXP_YN VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ROUTECONTROL_COMM_V1 CHECK (SERVICECOMMISSION_EXP_YN IN ('Y','N')),
  EXTRACT_DATE             DATE NULL,
  LOCATION_CODE            VARCHAR2(6) NULL,
  ROUTECODE                VARCHAR2(6) NULL,
  COMMISSIONPAYABLE        VARCHAR2(1) NULL,
  FROZEN_ROUTE_VOLUME      NUMBER NULL,
  EMPLOYEENUMBER           NUMBER NULL,
  ROUTE_VOLUME_AMOUNT      NUMBER NULL,
  COMMISSION_AMOUNT        NUMBER NULL,
  CHARGE_BACK_AMOUNT       NUMBER NULL,
  SYSTEMUSER_ID            NUMBER NOT NULL,
  TIMESTAMP                DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.SERVICECOMMISSION_EXP_YN
  IS 'Exported for service commission Y or N (default)';
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.EXTRACT_DATE
  IS 'Date when route commission data is extracted';
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.LOCATION_CODE
  IS 'Plant code that is linked to the route';  
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.ROUTECODE
  IS 'Data from Route table, coming from ROUTECODEMASTER table';
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.FROZEN_ROUTE_VOLUME
  IS 'Frozen Total Invoice of RSR deliveries';
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.EMPLOYEENUMBER
  IS 'Employee number of the driver';
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.ROUTE_VOLUME_AMOUNT
  IS 'Not used for now';
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.COMMISSION_AMOUNT
  IS 'Total value of RSR deliveries';
COMMENT ON COLUMN ROUTECONTROL_COMMISSION.CHARGE_BACK_AMOUNT
  IS 'Chargeback for returns on deliveries /credits';
ALTER TABLE ROUTECONTROL_COMMISSION
  ADD CONSTRAINT PK_ROUTECONTROL_COMMISSION PRIMARY KEY (ROUTECONTROL_ID);
CREATE INDEX INDX01_ROUTECONTROL_COMMISSION ON ROUTECONTROL_COMMISSION (SERVICECOMMISSION_EXP_YN);
ALTER TABLE ROUTECONTROL_COMMISSION
  ADD CONSTRAINT FK_ROUTECONTROL_COMMISSION FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;
  
-- ERP_O_SERVICECOMMISSION --
create table ERP_O_SERVICECOMMISSION
(
  TRANSFER_ID            NUMBER NOT NULL,
  TRANSFER_STATUS        NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_O_SERVICECOMMISSION_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  EXTRACT_DATE           DATE NULL,
  LOCATION_CODE          VARCHAR2(6) NULL,
  ROUTECODE              VARCHAR2(6) NULL,
  COMMISSIONPAYABLE      VARCHAR2(1) NULL,
  FROZEN_ROUTE_VOLUME    NUMBER NULL,
  EMPLOYEENUMBER         NUMBER NULL,
  ROUTE_VOLUME_AMOUNT    NUMBER NULL,
  COMMISSION_AMOUNT      NUMBER NULL,
  CHARGE_BACK_AMOUNT     NUMBER NULL,
  ROUTECONTROL_ID        NUMBER NOT NULL
);
COMMENT ON COLUMN  ERP_O_SERVICECOMMISSION.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_SERVICECOMMISSION
  ADD CONSTRAINT PK_ERP_O_SERVICECOMMISSION PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_ERP_O_SERVICECOMMISSION ON ERP_O_SERVICECOMMISSION (ROUTECONTROL_ID);  

-- ERP_O_SERVICECOMMISSION_LOG --
CREATE TABLE ERP_O_SERVICECOMMISSION_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_O_SERVICECOMM_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN  ERP_O_SERVICECOMMISSION_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN  ERP_O_SERVICECOMMISSION_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_O_SERVICECOMMISSION_LOG
  ADD CONSTRAINT PK_ERP_O_SERVICECOMMISSION_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_SERVICECOMMISSION_LOG
  ADD CONSTRAINT FK_ERP_O_SERVICECOMMISSION_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_SERVICECOMMISSION (TRANSFER_ID) ON DELETE CASCADE;

-- COMMISSIONAVAILABLE --
CREATE TABLE COMMISSIONAVAILABLE
(
  TRANSFER_STATUS NUMBER(2) DEFAULT 0 NOT NULL CONSTRAINT CHK_COMMISSIONAVAILABLE_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  YEAR            NUMBER(4) NULL,
  WEEK            NUMBER(2) NULL,
  AVAILABLE_YN    VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_COMMISSIONAVAILABLE_V2 CHECK (AVAILABLE_YN IN ('Y','N'))
);
COMMENT ON COLUMN  COMMISSIONAVAILABLE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE COMMISSIONAVAILABLE
  ADD CONSTRAINT PK_COMMISSIONAVAILABLE PRIMARY KEY (YEAR, WEEK);