--ABS-54612/ABS-55584

---------- PLANTSPERRSCONTRACT ---------- 
CREATE TABLE PLANTSPERRSCONTRACT
( CONTRACT_ID   NUMBER NOT NULL,
  PLANT_ID      NUMBER NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP     DATE NOT NULL);
  
ALTER TABLE PLANTSPERRSCONTRACT
  ADD CONSTRAINT XPKPLANTSPERRSCONTRACT PRIMARY KEY (CONTRACT_ID, PLANT_ID);

CREATE INDEX XIF1PLANTSPERRSCONTRACT ON PLANTSPERRSCONTRACT (CONTRACT_ID ASC);
CREATE INDEX XIF2PLANTSPERRSCONTRACT ON PLANTSPERRSCONTRACT (PLANT_ID ASC);

ALTER TABLE PLANTSPERRSCONTRACT
  ADD CONSTRAINT PLANTSPERRSCONTRACT_CONTRACT FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT (CONTRACT_ID) ON DELETE CASCADE;
ALTER TABLE PLANTSPERRSCONTRACT
  ADD CONSTRAINT PLANTSPERRSCONTRACT_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

---------- CONTRACT ----------
ALTER TABLE CONTRACT ADD (ALLOWNONCONTRACTPRODUCTS VARCHAR2(1) DEFAULT 'Y' NULL
                         CONSTRAINT YN_VALIDATIONFIELD396 CHECK (ALLOWNONCONTRACTPRODUCTS IN ('Y', 'N')));

COMMENT ON COLUMN CONTRACT.CATEGORY
  IS '1 = Service Contract
2 = Billing Contract
3 = All Inclusive Contract
4 = Route Service Contract';

ALTER TABLE CONTRACT
  DROP CONSTRAINT CATEGORY_VALID1;
ALTER TABLE CONTRACT
  ADD CONSTRAINT CATEGORY_VALID1
  CHECK (CATEGORY IN (1, 2, 3, 4));

---------- ROUTETRANSACTIONCATEGORY ----------
UPDATE ROUTETRANSACTIONCATEGORY RTC
SET RTC.SHOWTAB_YN = 'Y'
WHERE RTC.ROUTETRANSACTIONCATEGORY_ID = 4;
COMMIT;

---------- ROUTETRANSACTIONTYPE ----------
UPDATE ROUTETRANSACTIONTYPE RTT
SET RTT.ALLOWMANUALAPPROVAL_YN = 'Y', RTT.APPROVEMANUALLY_YN = 'Y'
WHERE RTT.ROUTETRANSACTIONTYPE_ID = 500;
COMMIT;

UPDATE ROUTETRANSACTIONTYPE RTT
SET RTT.ALLOWMANUALAPPROVAL_YN = 'Y',RTT.APPROVEMANUALLY_YN = 'Y'
WHERE RTT.ROUTETRANSACTIONTYPE_ID = 501;
COMMIT;


INSERT INTO ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID,FACTORYDESCRIPTION,ALLOWMANUALAPPROVAL_YN,APPROVEMANUALLY_YN, ROUTETRANSACTIONCATEGORY_ID, SYSTEMUSER_ID,TIMESTAMP) 
VALUES (504, 'Modify Product','Y','Y', 4, GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID,FACTORYDESCRIPTION,ALLOWMANUALAPPROVAL_YN,APPROVEMANUALLY_YN, ROUTETRANSACTIONCATEGORY_ID, SYSTEMUSER_ID,TIMESTAMP) 
VALUES (505, 'Add delivery / collection','Y','Y', 4, GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID,FACTORYDESCRIPTION,ALLOWMANUALAPPROVAL_YN,APPROVEMANUALLY_YN, ROUTETRANSACTIONCATEGORY_ID, SYSTEMUSER_ID,TIMESTAMP) 
VALUES (506, 'Modify delivery / collection','Y','Y', 4, GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID,FACTORYDESCRIPTION,ALLOWMANUALAPPROVAL_YN,APPROVEMANUALLY_YN, ROUTETRANSACTIONCATEGORY_ID, SYSTEMUSER_ID,TIMESTAMP) 
VALUES (507, 'Quit delivery / collection day','Y','Y', 4, GETMINSYSTEMUSER(),SYSDATE);
COMMIT;


---------- FIRSTDELIVERYSCHEME ----------
CREATE SEQUENCE SEQ_FIRSTDELIVERYSCHEME
START WITH 1
INCREMENT BY 1;

CREATE TABLE FIRSTDELIVERYSCHEME
( FIRSTDELIVERYSCHEME_ID NUMBER NOT NULL,
  CODE                   VARCHAR2(10) NOT NULL,
  BASIS                  NUMBER NOT NULL CONSTRAINT BASIS_VALID1_1 CHECK (BASIS IN (0,1)),
  ACTIVE                 VARCHAR2(1) NOT NULL CONSTRAINT YN_VALIDATIONFIELD397 CHECK (ACTIVE IN ('Y', 'N')),
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL);

COMMENT ON COLUMN FIRSTDELIVERYSCHEME.BASIS
  IS '0 = Delivery basis
1 = Week basis';

ALTER TABLE FIRSTDELIVERYSCHEME
  ADD CONSTRAINT XPKFIRSTDELIVERYSCHEME PRIMARY KEY (FIRSTDELIVERYSCHEME_ID);
  
---------- FIRSTDELIVERYSCHEMESPLIT ----------
CREATE SEQUENCE SEQ_FIRSTDELIVERYSCHEMESPLIT
START WITH 1
INCREMENT BY 1;

CREATE TABLE FIRSTDELIVERYSCHEMESPLIT
( FIRSTDELIVERYSCHEMESPLIT_ID NUMBER NOT NULL,
  FIRSTDELIVERYSCHEME_ID      NUMBER NOT NULL,
  SEQUENCE                    NUMBER NOT NULL,
  PERCENTAGE                  NUMBER NOT NULL CONSTRAINT PERCENTAGE_VALID1_2 CHECK (PERCENTAGE BETWEEN 0 AND 100),
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL);
  
ALTER TABLE FIRSTDELIVERYSCHEMESPLIT
  ADD CONSTRAINT XPKFIRSTDELIVERYSCHEMESPLIT PRIMARY KEY (FIRSTDELIVERYSCHEMESPLIT_ID);

CREATE UNIQUE INDEX XAK1FIRSTDELIVERYSCHEMESPLIT ON FIRSTDELIVERYSCHEMESPLIT (FIRSTDELIVERYSCHEME_ID ASC, SEQUENCE ASC);

CREATE INDEX XIF1FIRSTDELIVERYSCHEMESPLIT ON FIRSTDELIVERYSCHEMESPLIT (FIRSTDELIVERYSCHEME_ID ASC);

ALTER TABLE FIRSTDELIVERYSCHEMESPLIT
  ADD CONSTRAINT FDSSPLIT_FIRSTDELIVERYSCHEME FOREIGN KEY (FIRSTDELIVERYSCHEME_ID)
  REFERENCES FIRSTDELIVERYSCHEME (FIRSTDELIVERYSCHEME_ID) ON DELETE CASCADE;

---------- FIRSTDELIVERYSCHEME_DESC ----------
CREATE TABLE FIRSTDELIVERYSCHEME_DESC
( FIRSTDELIVERYSCHEME_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 FIRSTDELIVERYSCHEME_DESC
  ADD CONSTRAINT XPKFIRSTDELIVERYSCHEME_DESC PRIMARY KEY (FIRSTDELIVERYSCHEME_ID, LANGUAGE_ID);
  
CREATE INDEX XIF1FIRSTDELIVERYSCHEME_DESC ON FIRSTDELIVERYSCHEME_DESC (FIRSTDELIVERYSCHEME_ID ASC);
CREATE INDEX XIF2FIRSTDELIVERYSCHEME_DESC ON FIRSTDELIVERYSCHEME_DESC (LANGUAGE_ID ASC);

ALTER TABLE FIRSTDELIVERYSCHEME_DESC
  ADD CONSTRAINT FDS_DESC_FIRSTDELIVERYSCHEME FOREIGN KEY (FIRSTDELIVERYSCHEME_ID)
  REFERENCES FIRSTDELIVERYSCHEME (FIRSTDELIVERYSCHEME_ID) ON DELETE CASCADE;
ALTER TABLE FIRSTDELIVERYSCHEME_DESC
  ADD CONSTRAINT FDS_DESC_LANGUAGE FOREIGN KEY (LANGUAGE_ID)
  REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

---------- PLANT ----------
ALTER TABLE PLANT ADD (DEFAULTFSTDELSCHEME_ID NUMBER NULL);

CREATE INDEX XIF8PLANT ON PLANT (DEFAULTFSTDELSCHEME_ID ASC);
 
ALTER TABLE PLANT
  ADD CONSTRAINT PLANT_DEFAULTFSTDELSCHEME FOREIGN KEY (DEFAULTFSTDELSCHEME_ID)
  REFERENCES FIRSTDELIVERYSCHEME (FIRSTDELIVERYSCHEME_ID) ON DELETE SET NULL;

