-- ABS-134500 Truck Pairing

--ERP_O_ROUTECONTROL

CREATE TABLE ERP_O_ROUTECONTROL(TRANSFER_ID NUMBER NOT NULL,
                                TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_ROUTECONTROL_TS CHECK(TRANSFER_STATUS IN(-1,0,1)),
                                ROUTENUMBER NUMBER NOT NULL,
                                DRIVINGDATE DATE NOT NULL,
                                DAY VARCHAR2(10) NOT NULL,
                                DRIVER NUMBER NULL,
                                DEPOT VARCHAR2(6) NULL,
                                TRUCKCODE NUMBER NULL,
                                STARTMILEAGE NUMBER(15,6) NOT NULL,
                                ENDMILEAGE NUMBER(15,6) NOT NULL,
                                TOTALMILEAGE NUMBER(15,6) NOT NULL,
                                ORIGINALDATE DATE NOT NULL,
                                RELIEFDRIVER VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_ERP_O_ROUTECONTROL_RELIEFDRIVER CHECK(RELIEFDRIVER IN('Y','N')),
                                RETURNDATE DATE NULL,
                                ROUTEMANAGERNAME VARCHAR2(40) NULL,
                                ROUTEMANAGERNO NUMBER NULL,
                                SCHEDULEDENDTIME DATE NULL,
                                SCHEDULEDSTARTTIME DATE NULL,
                                STARTTIME DATE NULL,
                                RETURNTIME DATE NULL,
                                TOTALROUTETIME VARCHAR2(10) NULL,
                                DRIVETIME VARCHAR2(10) NULL,
                                TOTALCHARGEPREBILLED NUMBER(15,6) NOT NULL,
                                TOTALCHARGEMINUSADJ NUMBER(15,6) NOT NULL,
                                TOTALCHARGEPLUSADJ NUMBER(15,6) NOT NULL,
                                TOTALCHARGEPAYTHISAMOUNT NUMBER(15,6) NOT NULL,
                                TOTALCHARGEUNDELIVERED NUMBER(15,6) NOT NULL,
                                TOTALPLANNEDSTOPS NUMBER NOT NULL,
                                TOTALSETTLEDSTOPS NUMBER NOT NULL,
                                TOTALUNDELIVERABLESTOPS NUMBER NOT NULL,
                                STOPNUMBER NUMBER NULL,
                                CUSTOMERNUMBER NUMBER NULL,
                                CUSTOMERNAME VARCHAR2(40) NULL,
                                ADDRESS VARCHAR2(30) NULL,
                                CITY VARCHAR2(30) NULL,
                                ZIPCODE VARCHAR2(15) NULL,
                                STATUS NUMBER NULL,
                                INVOICENUMBER NUMBER NULL,
                                INVTOTALCHARGEPREBILL NUMBER(15,6) NULL,
                                INVTOTALCHARGEMINUSADJ NUMBER(15,6) NULL,
                                INVTOTALCHARGEPLUSADJ NUMBER(15,6) NULL,
                                INVTOTALCHARGEPAYTHISAMT NUMBER(15,6) NULL,
                                ARRIVALTIME DATE NULL,
                                DEPARTURETIME DATE NULL,
                                GPSLATITUDE VARCHAR2(12) NULL,
                                GPSLONGITUDE VARCHAR2(12) NULL,
                                REASONCODE VARCHAR2(6) NULL,
                                TRAVELMINUTES NUMBER NULL,
                                VISITMINUTES NUMBER NULL
                                );

COMMENT ON COLUMN ERP_O_ROUTECONTROL.TRANSFER_STATUS IS '-1 = staging, 0 = ready to be read, 1 = read correctly';

ALTER TABLE ERP_O_ROUTECONTROL
ADD CONSTRAINT XPKERP_O_ROUTECONTROL PRIMARY KEY(TRANSFER_ID);

CREATE INDEX INDX_ERP_O_ROUTECONTROL_TRANS ON ERP_O_ROUTECONTROL
(TRANSFER_STATUS ASC);

--ERP_O_ROUTECONTROL_LOG

CREATE TABLE ERP_O_ROUTECONTROL_LOG(TRANSFER_ID NUMBER NOT NULL,
                                    TRANSFER_STATUS NUMBER NOT NULL CONSTRAINT CHK_ERP_O_ROUTECONTROL_LOG_TS 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_ROUTECONTROL_LOG.TRANSFER_STATUS IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_ROUTECONTROL_LOG.PROCESS_STATUS IS '0 = PROCESSED CORRECTLY, 1 = PROCESS STOPPED WITH ERRORS';

ALTER TABLE ERP_O_ROUTECONTROL_LOG
ADD CONSTRAINT XPKERP_O_ROUTECONTROL_LOG PRIMARY KEY(TRANSFER_ID);

ALTER TABLE ERP_O_ROUTECONTROL_LOG
ADD CONSTRAINT ERP_O_ROUTECONTROL_LOG_TI FOREIGN KEY(TRANSFER_ID) REFERENCES ERP_O_ROUTECONTROL(TRANSFER_ID) ON DELETE CASCADE;

--ERP_I_TRUCK

CREATE TABLE ERP_I_TRUCK(TRANSFER_ID NUMBER NOT NULL,
                         TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_I_TRUCK_TS CHECK(TRANSFER_STATUS IN(-1,0,1)),
                         PLANTCODE VARCHAR2(6) NOT NULL,
                         TRUCKCODE VARCHAR2(12) NOT NULL,
                         DESCRIPTION VARCHAR2(30) NOT NULL,
                         DEFAULTDRIVER NUMBER NULL,
                         ACTIVE VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_ERP_I_TRUCK_ACTIVE CHECK(ACTIVE IN('Y','N'))
                         );
COMMENT ON COLUMN ERP_I_TRUCK.TRANSFER_STATUS IS '-1 = staging, 0 = ready to be read, 1 = read correctly';

ALTER TABLE ERP_I_TRUCK
ADD CONSTRAINT XPKERP_I_TRUCK PRIMARY KEY(TRANSFER_ID);

CREATE INDEX INDX_ERP_I_TRUCK_TRANS ON ERP_I_TRUCK
(TRANSFER_STATUS ASC);

--ERP_I_TRUCK_LOG

CREATE TABLE ERP_I_TRUCK_LOG(TRANSFER_ID NUMBER NOT NULL,
                           TRANSFER_STATUS NUMBER DEFAULT 0  NOT NULL CONSTRAINT CHK_ERP_I_TRUCK_LOG_TS 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_I_TRUCK_LOG.TRANSFER_STATUS IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_I_TRUCK_LOG.PROCESS_STATUS IS '0 = PROCESSED CORRECTLY, 1 = PROCESS STOPPED WITH ERRORS';

ALTER TABLE ERP_I_TRUCK_LOG
ADD CONSTRAINT XPKERP_I_TRUCK_LOG PRIMARY KEY(TRANSFER_ID);

ALTER TABLE ERP_I_TRUCK_LOG
ADD CONSTRAINT ERP_I_TRUCK_LOG_TI FOREIGN KEY(TRANSFER_ID) REFERENCES ERP_I_TRUCK(TRANSFER_ID) ON DELETE CASCADE;

