--order 20010849.110

---------------------------TABLE ORDERPRIORITY------------------------

CREATE TABLE ORDERPRIORITY
(
  ORDERPRIORITY_ID     NUMBER NOT NULL ,
  ORDERTYPE            NUMBER DEFAULT  1  NOT NULL  CONSTRAINT  ORDERTYPEPRIO_valid CHECK (ORDERTYPE IN (1, 2, 3)),
  PRIORITYCODE         VARCHAR2(12) NOT NULL ,
  FACTORYDESCRIPTION   VARCHAR2(30) NULL ,
  SEQUENCENUMBER       NUMBER DEFAULT  1  NOT NULL ,
  PRIORITYCOLOR        VARCHAR2(6) DEFAULT  'ffffff'  NOT NULL ,
  STANDARDPRIORITY     VARCHAR2(1) DEFAULT  'N'  NOT NULL ,
  ACTIVE               VARCHAR2(1) DEFAULT  'Y'  NOT NULL ,
  DEFAULTFORREPLACEMENT VARCHAR2(1) DEFAULT  'N'  NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
)TABLESPACE ABS_DATA;


CREATE SEQUENCE SEQ_ORDERPRIORITY
START WITH 1
INCREMENT BY 1;

COMMENT ON COLUMN ORDERPRIORITY.ORDERTYPE IS '1 = Work Order
2 = Purchase Order
3 = Packing Note
';



ALTER TABLE ORDERPRIORITY
ADD CONSTRAINT  XPKORDERPRIORITY PRIMARY KEY (ORDERPRIORITY_ID) USING INDEX TABLESPACE INDX;

---unique indexes------

CREATE UNIQUE INDEX XAK1ORDERPRIORITY ON ORDERPRIORITY
(ORDERTYPE   ASC,PRIORITYCODE   ASC)TABLESPACE INDX;



CREATE UNIQUE INDEX XAK2ORDERPRIORITY ON ORDERPRIORITY
(ORDERTYPE   ASC,SEQUENCENUMBER   ASC)TABLESPACE INDX;


---nonunique indexes------

CREATE  INDEX XIE1ORDERPRIORITY ON ORDERPRIORITY
(ORDERTYPE   ASC,ACTIVE   ASC)TABLESPACE INDX;



CREATE  INDEX XIE2ORDERPRIORITY ON ORDERPRIORITY
(ORDERTYPE   ASC,DEFAULTFORREPLACEMENT   ASC)TABLESPACE INDX;

-----inserted values-----------

INSERT INTO ORDERPRIORITY
     SELECT SEQ_ORDERPRIORITY.NEXTVAL, 
       1,
       '-',
       'Standard priority',
       1,
       'ffffff',
       'Y',
       'Y',
       'Y',
       GETMINSYSTEMUSER(),
       SYSDATE from dual;

INSERT INTO ORDERPRIORITY
    SELECT SEQ_ORDERPRIORITY.NEXTVAL,
       2,
       '-',
       'Standard priority',
       1,
       'ffffff',
       'Y',
       'Y',
       'N',
       GETMINSYSTEMUSER(),
       SYSDATE from dual;

INSERT INTO ORDERPRIORITY
       SELECT SEQ_ORDERPRIORITY.NEXTVAL,
       3,
       '-',
       'Standard priority',
       1,
       'ffffff',
       'Y',
       'Y',
       'N',
       GETMINSYSTEMUSER(),
       SYSDATE from dual;



-------------------------------------TABLE ORDERPRIORITY_DESC--------------------------------

CREATE TABLE ORDERPRIORITY_DESC
(
  ORDERPRIORITY_ID     NUMBER NOT NULL ,
  LANGUAGE_ID          NUMBER NOT NULL ,
  DESCRIPTION          VARCHAR2(40) NOT NULL ,
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
)TABLESPACE ABS_DATA;




ALTER TABLE ORDERPRIORITY_DESC
ADD CONSTRAINT  XPKORDERPRIORITY_DESC PRIMARY KEY (ORDERPRIORITY_ID  ,LANGUAGE_ID) USING INDEX TABLESPACE INDX;

---- foreign key--------------

CREATE  INDEX XIF1ORDERPRIORITY_DESC ON ORDERPRIORITY_DESC
(ORDERPRIORITY_ID   ASC)TABLESPACE INDX;



CREATE INDEX XIF2ORDERPRIORITY_DESC ON ORDERPRIORITY_DESC
(LANGUAGE_ID   ASC)TABLESPACE INDX;


ALTER TABLE ORDERPRIORITY_DESC
ADD CONSTRAINT ORDERPRIORITY_D_ORDERPRIORITY FOREIGN KEY (ORDERPRIORITY_ID) REFERENCES ORDERPRIORITY (ORDERPRIORITY_ID) ON DELETE CASCADE;


ALTER TABLE ORDERPRIORITY_DESC
ADD CONSTRAINT ORDERPRIORITY_D_LANGUAGES FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;


INSERT INTO ORDERPRIORITY_DESC
SELECT OPR.ORDERPRIORITY_ID, L.LANGUAGE_ID, OPR.FACTORYDESCRIPTION, GETMINSYSTEMUSER(), SYSDATE
FROM ORDERPRIORITY OPR,
     LANGUAGES L
WHERE L.TRANSLATEDESCRIPTION='Y'; 


--------------------------TABLE WORKORDER------------------

ALTER TABLE WORKORDER
  ADD (ORDERPRIORITY_ID NUMBER NULL);

CREATE INDEX XIF23WORKORDER ON WORKORDER
(ORDERPRIORITY_ID   ASC)TABLESPACE INDX;

ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_ORDERPRIORITY_ID FOREIGN KEY (ORDERPRIORITY_ID) REFERENCES ORDERPRIORITY (ORDERPRIORITY_ID);

UPDATE WORKORDER
SET ORDERPRIORITY_ID=(SELECT OPR.ORDERPRIORITY_ID FROM ORDERPRIORITY OPR WHERE OPR.ORDERTYPE=1 AND OPR.STANDARDPRIORITY='Y');

COMMIT;

ALTER TABLE WORKORDER
  MODIFY (ORDERPRIORITY_ID NUMBER NOT NULL);


--------------------TABLE SYSTEMUSER-----------------------------

ALTER TABLE SYSTEMUSER
  ADD (MAYCHANGEPRIORITYFORWO VARCHAR2(1) DEFAULT  'N'  NOT NULL);



