--ABS-162232

--WORKSTATION TABLE

ALTER TABLE WORKSTATION ADD 
(PRINTSOILRECEPTIONTICKET VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_WORKSTATION_PRINTSOILRECEPTIONTICKET CHECK (PRINTSOILRECEPTIONTICKET IN ('Y', 'N')),
 AUTOCONFCUSTCHANGE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_WORKSTATION_AUTOCONFCUSTCHANGE CHECK (AUTOCONFCUSTCHANGE IN ('Y', 'N')),
 DEFAULTSOILWEIGHINGTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_WORKSTATION_DEFAULTSOILWEIGHINGTYPE CHECK (DEFAULTSOILWEIGHINGTYPE IN (0, 1)));

COMMENT ON COLUMN WORKSTATION.PRINTSOILRECEPTIONTICKET
  IS 'Setting used in V10 Soil Weighing';
COMMENT ON COLUMN WORKSTATION.AUTOCONFCUSTCHANGE
  IS 'Setting used in V10 Soil Weighing';
COMMENT ON COLUMN WORKSTATION.DEFAULTSOILWEIGHINGTYPE
  IS '0 = Soil, 1 = Credit. Default 0. Setting used in V10 Soil Weighing';
  
--DEPARTMENT TABLE  
ALTER TABLE DEPARTMENT ADD (ALLOWEDFORSOILWEIGHING VARCHAR2(1) DEFAULT 'Y' NOT NULL 
                            CONSTRAINT CHK_DEPARTMENT_ALLOWEDFORSOILWEIGHING CHECK (ALLOWEDFORSOILWEIGHING IN ('Y', 'N')));
                            
COMMENT ON COLUMN DEPARTMENT.ALLOWEDFORSOILWEIGHING IS 'Y/N. Flag that indicates if department may be used for soil weighing, default Y. Setting used in v10 Soil Weighing';     

--WEIGHINGTICKET TABLE
ALTER TABLE WEIGHINGTICKET
DROP CONSTRAINT WEIGHINGTICKET_CREATIONWS;

DROP INDEX XIF8WEIGHINGTICKET;

--WEIGHINGTRANSACTION TABLE
CREATE SEQUENCE SEQ_WEIGHINGTRANSACTION
START WITH 1 INCREMENT BY 1;

CREATE TABLE WEIGHINGTRANSACTION
(WEIGHINGTRANSACTION_ID        NUMBER NOT NULL,
 WEIGHINGDATE                  DATE   NOT NULL,
 BUSINESSUNIT_ID               NUMBER NOT NULL,
 CONTAINER_ID                  NUMBER NULL,
 CONTAINERTYPE_ID              NUMBER NOT NULL,
 CUSTOMER_LINK_ID              NUMBER NOT NULL,
 DEPARTMENT_LINK_ID            NUMBER NOT NULL,
 DISTRIBUTIONPOINT_LINK_ID     NUMBER NULL,
 WEIGHINGCATEGORY_LINK_ID      NUMBER NOT NULL,
 GROSSWEIGHT                   NUMBER(8,2) NOT NULL,
 TAREWEIGHT                    NUMBER(6,2) NULL,
 WEIGHTDECIMALS                NUMBER NOT NULL,
 WEIGHINGSEQUENCENR            VARCHAR2(12) NULL, 
 EMPLOYEE_ID                   NUMBER NULL,
 WEIGHINGTRANSACTIONTYPE       NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_WEIGHINGTRANSACTION_WEIGHINGTRANSTYPE CHECK (WEIGHINGTRANSACTIONTYPE IN (1, 2, 3)),
 ORIGIN                        NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_WEIGHINGTRANSACTION_ORIGIN CHECK (ORIGIN IN (1, 2)),
 CREATIONWORKSTATION           VARCHAR2(12) NULL,
 BRANDTYPE                     VARCHAR2(10) NULL, 
 LASTINDATE                    DATE NULL,
 LASTINROUTE_ID                NUMBER NULL,
 DELIVERYDATE                  DATE NULL,
 CREATIONTIMESTAMP             DATE NOT NULL,
 CREATIONUSER_ID               NUMBER NOT NULL,
 CANCELLATIONUSER_ID           NUMBER NULL,
 CANCELLATIONTIMESTAMP         DATE NULL,
 WEIGHINGTICKETLINE_ID         NUMBER NULL,
 SYSTEMUSER_ID                 NUMBER NOT NULL,
 TIMESTAMP                     DATE NOT NULL,
 TRANSFERSTATUS                NUMBER NOT NULL CONSTRAINT CHK_WEIGHINGTRANSACTION_TRANSFERSTATUS CHECK (TRANSFERSTATUS IN (0, 1) OR TRANSFERSTATUS < 0),
 PARENTWEIGHINGTRANSACTION_ID  NUMBER NOT NULL
 );       

COMMENT ON COLUMN WEIGHINGTRANSACTION.WEIGHINGDATE IS 'Date the weighing transaction was created';
COMMENT ON COLUMN WEIGHINGTRANSACTION.WEIGHINGSEQUENCENR IS 'Unique ID that may have been generated by weighing scale, otherwise empty';
COMMENT ON COLUMN WEIGHINGTRANSACTION.WEIGHINGTRANSACTIONTYPE IS '1 = Soil Weighing 2 = Credit weighing 3 = Extraction weighing';
COMMENT ON COLUMN WEIGHINGTRANSACTION.ORIGIN IS '1 = Electronically captured 2 = Manually entered';
COMMENT ON COLUMN WEIGHINGTRANSACTION.TRANSFERSTATUS IS '0 = Created new 1 = Transffered <0 = select for transfer (plant_id * - 1)';

ALTER TABLE WEIGHINGTRANSACTION ADD CONSTRAINT XPKWEIGHINGTRANSACTION PRIMARY KEY (WEIGHINGTRANSACTION_ID);

CREATE INDEX XIE1WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (CREATIONWORKSTATION ASC, CREATIONTIMESTAMP ASC);
CREATE INDEX XIE2WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (TRANSFERSTATUS ASC, BUSINESSUNIT_ID ASC);

CREATE INDEX XIF1WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (CONTAINER_ID ASC);
CREATE INDEX XIF3WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (CONTAINERTYPE_ID ASC);
CREATE INDEX XIF4WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF5WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (DEPARTMENT_LINK_ID ASC);
CREATE INDEX XIF6WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (DISTRIBUTIONPOINT_LINK_ID ASC);
CREATE INDEX XIF7WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (WEIGHINGCATEGORY_LINK_ID ASC);
CREATE INDEX XIF8WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (EMPLOYEE_ID ASC);
CREATE INDEX XIF9WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (LASTINROUTE_ID ASC);
CREATE INDEX XIF10WEIGHINGTRANSACTION ON WEIGHINGTRANSACTION (PARENTWEIGHINGTRANSACTION_ID ASC);

ALTER TABLE WEIGHINGTRANSACTION 
  ADD CONSTRAINT FK_WEIGHINGTRANSACTION_BUSINESSUNIT_ID FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
  
ALTER TABLE WEIGHINGTRANSACTION   
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_CONTAINER_ID FOREIGN KEY (CONTAINER_ID)
 REFERENCES CONTAINER (CONTAINER_ID);
 
ALTER TABLE WEIGHINGTRANSACTION
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_CONTAINERTYPE_ID FOREIGN KEY (CONTAINERTYPE_ID)
 REFERENCES CONTAINERTYPE (CONTAINERTYPE_ID);
 
ALTER TABLE WEIGHINGTRANSACTION
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_CUSTOMER_LINK_ID FOREIGN KEY (CUSTOMER_LINK_ID)
 REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);
 
ALTER TABLE WEIGHINGTRANSACTION
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_DEPARTMENT_LINK_ID FOREIGN KEY (DEPARTMENT_LINK_ID)
 REFERENCES DEPARTMENT_LINK (DEPARTMENT_LINK_ID);
 
ALTER TABLE WEIGHINGTRANSACTION
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_DISTRPOINT_LINK_ID FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID)
 REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);

ALTER TABLE WEIGHINGTRANSACTION
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_WEIGHINGCAT_LINK_ID FOREIGN KEY (WEIGHINGCATEGORY_LINK_ID)
 REFERENCES WEIGHINGCATEGORY_LINK (WEIGHINGCATEGORY_LINK_ID);

ALTER TABLE WEIGHINGTRANSACTION
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID)
 REFERENCES EMPLOYEE (EMPLOYEE_ID);

ALTER TABLE WEIGHINGTRANSACTION
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_LASTINROUTE_ID FOREIGN KEY (LASTINROUTE_ID)
 REFERENCES ROUTE (ROUTE_ID);

ALTER TABLE WEIGHINGTRANSACTION
 ADD CONSTRAINT FK_WEIGHINGTRANSACTION_PRTWEIGHINGTRANS_ID FOREIGN KEY (PARENTWEIGHINGTRANSACTION_ID)
 REFERENCES WEIGHINGTRANSACTION (WEIGHINGTRANSACTION_ID);
  
  
--USERINSTRUCTIONPERSCREEN TABLE
insert into userinstructionperscreen
  (userinstructionperscreen_id,
   predefinedscreen_id,
   customeruserinstruction_id,
   generaluserinstruction_id,
   systemuser_id,
   timestamp,
   dialog_id)
  select seq_userinstructionperscreen.nextval,
         null,
         uis.customeruserinstruction_id,
         uis.generaluserinstruction_id,
         0,
         sysdate,
         (select d.dialog_id
            from dialogs d
           where d.code = 'SoilCreditWeighing')
    from userinstructionperscreen uis
   where uis.dialog_id =
         (select d.dialog_id from dialogs d where d.code = 'SoilWeighing')
     and exists
   (select 1 from dialogs d where d.code = 'SoilCreditWeighing')
     and not exists
   (select 1
            from userinstructionperscreen uis2
           where nvl(uis2.customeruserinstruction_id, -1) =
                 nvl(uis.customeruserinstruction_id, -1)
             and nvl(uis2.generaluserinstruction_id, -1) =
                 nvl(uis.generaluserinstruction_id, -1)
             and uis2.dialog_id =
                 (select d.dialog_id
                    from dialogs d
                   where d.code = 'SoilCreditWeighing'));
COMMIT;



