/*ORDER 502346*/      
      /*
      ACTION is CREATE Table SORTADDRESSPERWASHPROCESS
      */

CREATE TABLE SORTADDRESSPERWASHPROCESS (
       WASHINGPROCESS_ID    NUMBER NOT NULL,
       PLANT_ID             NUMBER NOT NULL,
       SORTADDRESS          VARCHAR2(6) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1SORTADDRESSPERWASHPROCESS ON SORTADDRESSPERWASHPROCESS
(
       WASHINGPROCESS_ID              ASC
);

CREATE INDEX XIF2SORTADDRESSPERWASHPROCESS ON SORTADDRESSPERWASHPROCESS
(
       PLANT_ID                       ASC
);


ALTER TABLE SORTADDRESSPERWASHPROCESS
       ADD  ( CONSTRAINT XPKSORTADDRESSPERWASHPROCESS PRIMARY KEY (
              WASHINGPROCESS_ID, PLANT_ID) ) ;


ALTER TABLE SORTADDRESSPERWASHPROCESS
       ADD  ( CONSTRAINT SORTADDRESSPERWASHPROCESS_PLAN
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT ) ;


ALTER TABLE SORTADDRESSPERWASHPROCESS
       ADD  ( CONSTRAINT SORTADDRESSPERWASHPROCESS_WASH
              FOREIGN KEY (WASHINGPROCESS_ID)
                             REFERENCES WASHINGPROCESS
                             ON DELETE CASCADE ) ;


--insert records in SORTADDRESSPERWASHPROCESS table

INSERT INTO SORTADDRESSPERWASHPROCESS
  SELECT WP_ID, P_ID, SC, 1, SYSDATE
    FROM (SELECT WP.WASHINGPROCESS_ID WP_ID, WP.SORTCODE SC
            FROM WASHINGPROCESS WP
           WHERE WP.SORTCODE IS NOT NULL),
         (SELECT P.PLANT_ID P_ID FROM PLANT P) ;

--insert process

insert into process(process_id,description,active) values ('79','Import soil scans','Y');



