--SO 20013877
----------------------------------------------------------------------------------------------------------ROUTETRANSACTIONCATEGORY----------------------------------
CREATE SEQUENCE SEQ_ROUTETRANSACTIONCATEGORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROUTETRANSACTIONCATEGORY
(
	ROUTETRANSACTIONCATEGORY_ID NUMBER NOT NULL ,
	FACTORYDESCRIPTION   VARCHAR2(30) NOT NULL ,
	SHOWTAB_YN           VARCHAR2(1) NOT NULL  CONSTRAINT  YN_VALIDATIONFIELD1 CHECK (SHOWTAB_YN IN ('Y', 'N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN ROUTETRANSACTIONCATEGORY.SHOWTAB_YN IS 'Users Choice to see this tab';

ALTER TABLE ROUTETRANSACTIONCATEGORY
ADD CONSTRAINT  XPKROUTETRANSACTIONCATEGORY PRIMARY KEY (ROUTETRANSACTIONCATEGORY_ID );

INSERT INTO ROUTETRANSACTIONCATEGORY
SELECT 0,
       'No Tab',
       'N',
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONCATEGORY
SELECT 1,
       'Invoice Adjustments',
       'Y',
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONCATEGORY
SELECT 2,
       'Orders',
       'Y',
       1,
       SYSDATE
FROM DUAL;
COMMIT;

--------------------------------------------------------------------------------------------------------------ROUTETRANSACTIONCATEGORY_DESC-----------------------------------

CREATE TABLE ROUTETRANSACTIONCATEGORY_DESC
(
	ROUTETRANSACTIONCATEGORY_ID NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(30) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE ROUTETRANSACTIONCATEGORY_DESC
ADD CONSTRAINT  XPKROUTETRANSACTIONCATEGORY_DE PRIMARY KEY (ROUTETRANSACTIONCATEGORY_ID,LANGUAGE_ID);

ALTER TABLE ROUTETRANSACTIONCATEGORY_DESC
ADD CONSTRAINT ROUTETRANSCAT_DESC_ROUTETRCAT FOREIGN KEY (ROUTETRANSACTIONCATEGORY_ID) REFERENCES ROUTETRANSACTIONCATEGORY (ROUTETRANSACTIONCATEGORY_ID) ON DELETE CASCADE;

CREATE INDEX XIF1ROUTETRANSCATEGORY_DESC ON ROUTETRANSACTIONCATEGORY_DESC
(ROUTETRANSACTIONCATEGORY_ID ASC);

ALTER TABLE ROUTETRANSACTIONCATEGORY_DESC
ADD CONSTRAINT ROUTETRANSCATEG_DESC_LANGUAGE FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

CREATE INDEX XIF2ROUTETRANSCATEGORY_DESC ON ROUTETRANSACTIONCATEGORY_DESC
(LANGUAGE_ID ASC);

--------------------------------------------------------------------------------------------------------ROUTETRANSACTIONTYPE--------------------------------------------
CREATE SEQUENCE SEQ_ROUTETRANSACTIONTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROUTETRANSACTIONTYPE
(
	ROUTETRANSACTIONTYPE_ID NUMBER NOT NULL ,
	FACTORYDESCRIPTION   VARCHAR2(40) NOT NULL ,
	ALLOWMANUALAPPROVAL_YN VARCHAR2(1) NOT NULL  CONSTRAINT  YN_VALIDATIONFIELD3 CHECK (ALLOWMANUALAPPROVAL_YN IN ('Y', 'N')),
	APPROVEMANUALLY_YN   VARCHAR2(1) NOT NULL  CONSTRAINT  YN_VALIDATIONFIELD130 CHECK (APPROVEMANUALLY_YN IN ('Y', 'N')),
	ROUTETRANSACTIONCATEGORY_ID NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);
COMMENT ON COLUMN ROUTETRANSACTIONTYPE.ALLOWMANUALAPPROVAL_YN IS 'Defines if Manual Approval is implemented';

COMMENT ON COLUMN ROUTETRANSACTIONTYPE.APPROVEMANUALLY_YN IS 'Only allowed to put to Y when  AllowManualApproval_YN = Y';

ALTER TABLE ROUTETRANSACTIONTYPE
ADD CONSTRAINT  XPKROUTETRANSACTIONTYPE PRIMARY KEY (ROUTETRANSACTIONTYPE_ID);

ALTER TABLE ROUTETRANSACTIONTYPE
ADD CONSTRAINT ROUTETRANSACTIONTYPE_ROUTETRCA FOREIGN KEY (ROUTETRANSACTIONCATEGORY_ID) REFERENCES ROUTETRANSACTIONCATEGORY (ROUTETRANSACTIONCATEGORY_ID);

CREATE INDEX XIF1ROUTETRANSACTIONTYPE ON ROUTETRANSACTIONTYPE
(ROUTETRANSACTIONCATEGORY_ID ASC);

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 100,
       'Start Route',
       'N',
       'N',
       0,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 101,
       'End Route',
       'N',
       'N',
       0,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 102,
       'Safety Check',
       'N',
       'N',
       0,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 103,
       'Safety Remarks',
       'N',
       'N',
       0,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 200,
       'Arrive at Stop',
       'N',
       'N',
       0,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 201,
       'Depart from Stop',
       'N',
       'N',
       0,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 202,
       'Undeliverable Stop',
       'N',
       'N',
       0,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 300,
       'Add Wearer',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 301,
       'Quit Wearer',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 302,
       'Add Product Existing Wearer',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 303,
       'Quit Product Existing Wearer',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 304,
       'Style Exchange',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 305,
       'Size Exchange',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 306,
       'Decrease Wearer Inventory',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 307,
       'Increase Wearer Inventory',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 308,
       'Decrease inventory',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 309,
       'Increase inventory',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 310,
       'One Time Order',
       'Y',
       'N',
       2,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 700,
       'Settle Invoice',
       'N',
       'N',
       0,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 701,
       'Minus Adjustment',
       'N',
       'N',
       1,
       1,
       SYSDATE
FROM DUAL;
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE
SELECT 702,
       'Plus Adjustment',
       'N',
       'N',
       1,
       1,
       SYSDATE
FROM DUAL;
COMMIT;
-------------------------------------------------------------------------ROUTETRANSACTION--------------------------------------------------
CREATE SEQUENCE SEQ_ROUTETRANSACTION
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROUTETRANSACTION
(
	ROUTETRANSACTION_ID  NUMBER NOT NULL ,
	DEVICETRANSACTION_ID NUMBER NULL ,
	ROUTECONTROL_ID      NUMBER  NOT NULL ,
	STOP_ID              NUMBER NULL ,
	ROUTETRANSACTIONTYPE_ID NUMBER NOT NULL ,
	WEARER_ID            NUMBER NULL ,
	WEARERINVENTORY_ID   NUMBER NULL ,
	INVOICEHEADER_ID     NUMBER NULL ,
	INVOICELINE_ID       NUMBER NULL ,
	REASONCODE_ID        NUMBER NULL ,
	DATEVALUE1           DATE NULL ,
	DATEVALUE2           DATE NULL ,
	DATEVALUE3           DATE NULL ,
	DATEVALUE4           DATE NULL ,
	DATEVALUE5           DATE NULL ,
	STRINGVALUE1         VARCHAR2(40) NULL ,
	STRINGVALUE2         VARCHAR2(40) NULL ,
	STRINGVALUE3         VARCHAR2(40) NULL ,
	STRINGVALUE4         VARCHAR2(40) NULL ,
	STRINGVALUE5         VARCHAR2(40) NULL ,
	STRINGVALUE6         VARCHAR2(40) NULL ,
	STRINGVALUE7         VARCHAR2(40) NULL ,
	STRINGVALUE8         VARCHAR2(40) NULL ,
	STRINGVALUE9         VARCHAR2(40) NULL ,
	STRINGVALUE10        VARCHAR2(40) NULL ,
	NUMBERVALUE1         NUMBER NULL ,
	NUMBERVALUE2         NUMBER NULL ,
	NUMBERVALUE3         NUMBER NULL ,
	NUMBERVALUE4         NUMBER NULL ,
	NUMBERVALUE5         NUMBER NULL ,
	DOUBLEVALUE1         NUMBER(15,4) NULL ,
	DOUBLEVALUE2         NUMBER(15,4) NULL ,
	DOUBLEVALUE3         NUMBER(15,4) NULL ,
	DOUBLEVALUE4         NUMBER(15,4) NULL ,
	DOUBLEVALUE5         NUMBER(15,4) NULL ,
	DOUBLEVALUE6         NUMBER(15,4) NULL ,
	DOUBLEVALUE7         NUMBER(15,4) NULL ,
	DOUBLEVALUE8         NUMBER(15,4) NULL ,
	DOUBLEVALUE9         NUMBER(15,4) NULL ,
	DOUBLEVALUE10        NUMBER(15,4) NULL ,
	APPROVALSTATUS       NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  APPROVALSTATUS_valid CHECK (APPROVALSTATUS IN (0, 1, 2)),
	CREATIONSYSTEMUSER_ID NUMBER NOT NULL ,
	APPROVEDBYSYSTEMUSER_ID NUMBER NULL ,
	IMPORTEDTIMESTAMP    DATE NULL ,
	PDACREATIONTIMESTAMP DATE NULL ,
	APPROVALTIMESTAMP    DATE NULL ,
	MOBILEDEVICE_ID      NUMBER NULL ,
	MASTERROUTETRANSACTION_ID NUMBER NULL ,
	SIGNATUREIMAGE       BLOB NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN ROUTETRANSACTION.DEVICETRANSACTION_ID IS 'Transaction ID in device Database (populated for Android app generated transactions only)';

COMMENT ON COLUMN ROUTETRANSACTION.APPROVALSTATUS IS '0 = Pending
1 = Approved
2 = Rejected
Default 0
';

COMMENT ON COLUMN ROUTETRANSACTION.MASTERROUTETRANSACTION_ID IS 'Foreign key to RouteTransaction table (self referring). A transaction can consist of multiple rows. They are linked together via the MasterRouteTransaction_ID. For example ‘Add wearer’ with several ‘Add product’ lines.';

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT  XPKROUTETRANSACTION PRIMARY KEY (ROUTETRANSACTION_ID);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_ROUTECONTROL_ FOREIGN KEY (ROUTECONTROL_ID) REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;

CREATE INDEX XIF1ROUTETRANSACTION ON ROUTETRANSACTION
(ROUTECONTROL_ID   ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_STOP_ID FOREIGN KEY (STOP_ID) REFERENCES STOP (STOP_ID) ON DELETE CASCADE;

CREATE INDEX XIF2ROUTETRANSACTION ON ROUTETRANSACTION
(STOP_ID   ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_ROUTETRTYPE_I FOREIGN KEY (ROUTETRANSACTIONTYPE_ID) REFERENCES ROUTETRANSACTIONTYPE (ROUTETRANSACTIONTYPE_ID);

CREATE INDEX XIF3ROUTETRANSACTION ON ROUTETRANSACTION
(ROUTETRANSACTIONTYPE_ID   ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_WEARER_ID FOREIGN KEY (WEARER_ID) REFERENCES WEARER (WEARER_ID) ON DELETE SET NULL;

CREATE INDEX XIF4ROUTETRANSACTION ON ROUTETRANSACTION
(WEARER_ID ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_WEARERINV_ID FOREIGN KEY (WEARERINVENTORY_ID) REFERENCES WEARERINVENTORY (WEARERINVENTORY_ID) ON DELETE SET NULL;

CREATE INDEX XIF5ROUTETRANSACTION ON ROUTETRANSACTION
(WEARERINVENTORY_ID ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_INVOICEHEADER FOREIGN KEY (INVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;

CREATE INDEX XIF6ROUTETRANSACTION ON ROUTETRANSACTION
(INVOICEHEADER_ID ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_INVOICELINE_I FOREIGN KEY (INVOICELINE_ID) REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;

CREATE INDEX XIF7ROUTETRANSACTION ON ROUTETRANSACTION
(INVOICELINE_ID ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_REASONCODE_ID FOREIGN KEY (REASONCODE_ID) REFERENCES REASONCODE (REASONCODE_ID) ON DELETE SET NULL;

CREATE INDEX XIF8ROUTETRANSACTION ON ROUTETRANSACTION
(REASONCODE_ID ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_MOBILEDEVICE FOREIGN KEY (MOBILEDEVICE_ID) REFERENCES MOBILEDEVICE (MOBILEDEVICE_ID) ON DELETE SET NULL;

CREATE INDEX XIF9ROUTETRANSACTION ON ROUTETRANSACTION
(MOBILEDEVICE_ID ASC);

ALTER TABLE ROUTETRANSACTION
ADD CONSTRAINT ROUTETRANSACTION_ROUTETR_ID FOREIGN KEY (MASTERROUTETRANSACTION_ID) REFERENCES ROUTETRANSACTION (ROUTETRANSACTION_ID) ON DELETE CASCADE;

CREATE INDEX XIF10ROUTETRANSACTION ON ROUTETRANSACTION
(MASTERROUTETRANSACTION_ID ASC);

CREATE INDEX XIE1ROUTETRANSACTION ON ROUTETRANSACTION
(ROUTECONTROL_ID   ASC,ROUTETRANSACTIONTYPE_ID   ASC);

CREATE INDEX XIE2ROUTETRANSACTION ON ROUTETRANSACTION
(STOP_ID   ASC,ROUTETRANSACTIONTYPE_ID   ASC);


-------------------------------------------------------------------------------------ROUTETRANSACTIONTYPE_DESC----------------------------------------

CREATE TABLE ROUTETRANSACTIONTYPE_DESC
(
	ROUTETRANSACTIONTYPE_ID NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE ROUTETRANSACTIONTYPE_DESC
ADD CONSTRAINT  XPKROUTETRANSACTIONTYPE_DESC PRIMARY KEY (ROUTETRANSACTIONTYPE_ID,LANGUAGE_ID);

ALTER TABLE ROUTETRANSACTIONTYPE_DESC
ADD CONSTRAINT ROUTETRANSTYPE_DESC_ROUTETRTY FOREIGN KEY (ROUTETRANSACTIONTYPE_ID) REFERENCES ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID) ON DELETE CASCADE;

CREATE INDEX XIF1ROUTETRANSTYPE_DESC ON ROUTETRANSACTIONTYPE_DESC
(ROUTETRANSACTIONTYPE_ID ASC);

ALTER TABLE ROUTETRANSACTIONTYPE_DESC
ADD CONSTRAINT ROUTETRANSTYPE_DESC_LANGUAGE FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

CREATE INDEX XIF2ROUTETRANSTYPE_DESC ON ROUTETRANSACTIONTYPE_DESC
(LANGUAGE_ID ASC);

-------------------------------------------------------------------------ROUTETRANSTYPEREASONCODE----------------------------------------

CREATE TABLE ROUTETRANSTYPEREASONCODE
(
	ROUTETRANSACTIONTYPE_ID NUMBER NOT NULL ,
	REASONCODE_ID        NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE ROUTETRANSTYPEREASONCODE
ADD CONSTRAINT  XPKROUTETRANSACTIONTYPEREASONC PRIMARY KEY (ROUTETRANSACTIONTYPE_ID,REASONCODE_ID);

ALTER TABLE ROUTETRANSTYPEREASONCODE
ADD CONSTRAINT ROUTETRANSTYPERC_ROUTETRANSTY FOREIGN KEY (ROUTETRANSACTIONTYPE_ID) REFERENCES ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID) ON DELETE CASCADE;

CREATE INDEX XIF1ROUTETRANSTYPEREASONCODE ON ROUTETRANSTYPEREASONCODE
(ROUTETRANSACTIONTYPE_ID ASC);

ALTER TABLE ROUTETRANSTYPEREASONCODE
ADD CONSTRAINT ROUTETRANSTYPERC_RC FOREIGN KEY (REASONCODE_ID) REFERENCES REASONCODE (REASONCODE_ID) ON DELETE CASCADE;

CREATE INDEX XIF2ROUTETRANSTYPEREASONCODE ON ROUTETRANSTYPEREASONCODE
(REASONCODE_ID ASC);