--ABS-10560

------------ SYSTEMUSER -------------------
ALTER TABLE SYSTEMUSER ADD (OOSALLOWED NUMBER DEFAULT 0 NOT NULL CONSTRAINT OOSALLOWED_VALID1_1  CHECK (OOSALLOWED IN (0,1,2,3)));

COMMENT ON COLUMN SYSTEMUSER.OOSALLOWED
  IS 'Possible values: 
0 = Not authorized (default)  
1 = Authorized to mark OOS
2 = Authorized to unmark OOS
3 = Authorized to mark and unmark OOS';

------------ OUTOFSERVICEPRODUCT -------------------
CREATE SEQUENCE SEQ_OUTOFSERVICEPRODUCT
START WITH 1
INCREMENT BY 1;

CREATE TABLE OUTOFSERVICEPRODUCT
(
  OUTOFSERVICEPRODUCT_ID      NUMBER NOT NULL,
  OUTOFSERVICEDATE            DATE NOT NULL,
  SEQUENCENUMBER              NUMBER NOT NULL,
  STOCKRESERVATIONPERGRADE_ID NUMBER NOT NULL,
  STATUS                      NUMBER DEFAULT 0 NOT NULL CONSTRAINT STATUS_VALID1_1  CHECK (STATUS IN (0,1)),
  REMARK                      VARCHAR2(300),
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL
);

COMMENT ON COLUMN OUTOFSERVICEPRODUCT.STATUS
  IS 'Possible values:
0 = Active
1 = Inactive
';
ALTER TABLE OUTOFSERVICEPRODUCT
  ADD CONSTRAINT XPKOUTOFSERVICEPRODUCT PRIMARY KEY (OUTOFSERVICEPRODUCT_ID);
  
CREATE UNIQUE INDEX XAK1OUTOFSERVICEPRODUCT ON OUTOFSERVICEPRODUCT (SEQUENCENUMBER, STOCKRESERVATIONPERGRADE_ID);

CREATE INDEX XIF1OUTOFSERVICEPRODUCT ON OUTOFSERVICEPRODUCT (STOCKRESERVATIONPERGRADE_ID);
 
ALTER TABLE OUTOFSERVICEPRODUCT
  ADD CONSTRAINT OUTOFSERVICEPRODUCT_SRPG_ID FOREIGN KEY (STOCKRESERVATIONPERGRADE_ID)
  REFERENCES STOCKRESERVATIONPERGRADE (STOCKRESERVATIONPERGRADE_ID);
  
------------ OOSACTION -------------------
CREATE TABLE OOSACTION
(
  OOSACTION_ID       NUMBER NOT NULL,
  FACTORYDESCRIPTION VARCHAR2(60) NOT NULL,
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL
);

ALTER TABLE OOSACTION
  ADD CONSTRAINT XPKOOSACTION PRIMARY KEY (OOSACTION_ID);
 
--
INSERT INTO OOSACTION O
  (O.OOSACTION_ID, 
   O.FACTORYDESCRIPTION, 
   O.SYSTEMUSER_ID, 
   O.TIMESTAMP)
  VALUES (1,
         'Manual creation of internal purchase order lines',
         GETMINSYSTEMUSER(),
         SYSDATE);
COMMIT;		 
INSERT INTO OOSACTION O
  (O.OOSACTION_ID, 
   O.FACTORYDESCRIPTION, 
   O.SYSTEMUSER_ID, 
   O.TIMESTAMP)
  VALUES (2,
         'Receive items on Stock from internal purchase orders',
         GETMINSYSTEMUSER(),
         SYSDATE);
COMMIT;		 
INSERT INTO OOSACTION O
  (O.OOSACTION_ID, 
   O.FACTORYDESCRIPTION, 
   O.SYSTEMUSER_ID, 
   O.TIMESTAMP)
  VALUES (3,
         'Issue items with Stock Delivery Order',
         GETMINSYSTEMUSER(),
         SYSDATE);		 
		 
COMMIT;


---------------- OOSACTION_DESC --------------
CREATE TABLE OOSACTION_DESC
(
  OOSACTION_ID    NUMBER NOT NULL,
  LANGUAGE_ID     NUMBER NOT NULL,
  DESCRIPTION     VARCHAR2(60) NOT NULL,
  SYSTEMUSER_ID   NUMBER NOT NULL,
  TIMESTAMP       DATE NOT NULL
);

ALTER TABLE OOSACTION_DESC
  ADD CONSTRAINT XPKOOSACTION_DESC PRIMARY KEY (OOSACTION_ID, LANGUAGE_ID);

CREATE INDEX XIF1OOSACTION_DESC ON OOSACTION_DESC(OOSACTION_ID);

CREATE INDEX XIF2OOSACTION_DESC ON OOSACTION_DESC(LANGUAGE_ID);

ALTER TABLE OOSACTION_DESC
  ADD CONSTRAINT OOSACTION_DESC_OOSACTION_ID FOREIGN KEY (OOSACTION_ID)
  REFERENCES OOSACTION (OOSACTION_ID);
  
ALTER TABLE OOSACTION_DESC
  ADD CONSTRAINT OOSACTION_DESC_LANGUAGE_ID FOREIGN KEY (LANGUAGE_ID)
  REFERENCES LANGUAGES(LANGUAGE_ID);
  
---------------------- OOSRESTRICTION -----------------------------
CREATE TABLE OOSRESTRICTION
(
  OOSRESTRICTION_ID  NUMBER NOT NULL,
  FACTORYDESCRIPTION VARCHAR2(50) NOT NULL,
  SYSTEMUSER_ID      NUMBER NOT NULL,
  TIMESTAMP          DATE NOT NULL
);
ALTER TABLE OOSRESTRICTION
  ADD CONSTRAINT XPKOOSRESTRICTION PRIMARY KEY (OOSRESTRICTION_ID);
  
--  
INSERT INTO OOSRESTRICTION
  (OOSRESTRICTION_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
VALUES
  (1, 'Always allowed', GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

INSERT INTO OOSRESTRICTION
  (OOSRESTRICTION_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
VALUES
  (2, 'Never allowed', GETMINSYSTEMUSER(), SYSDATE);  
COMMIT;

INSERT INTO OOSRESTRICTION
  (OOSRESTRICTION_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
VALUES
  (3, 'Only allowed blocked locations', GETMINSYSTEMUSER(), SYSDATE); 
COMMIT; 
  
INSERT INTO OOSRESTRICTION
  (OOSRESTRICTION_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
VALUES
  (4, 'Only allowed blocked-and picking locations', GETMINSYSTEMUSER(), SYSDATE); 
COMMIT;  
  
INSERT INTO OOSRESTRICTION
  (OOSRESTRICTION_ID, FACTORYDESCRIPTION, SYSTEMUSER_ID, TIMESTAMP)
VALUES
  (5, 'Maximum available quantity', GETMINSYSTEMUSER(), SYSDATE); 
COMMIT;   


-------------------- OOSRESTRICTION_DESC -------------------------------
CREATE TABLE OOSRESTRICTION_DESC
(
  OOSRESTRICTION_ID NUMBER NOT NULL,
  LANGUAGE_ID       NUMBER NOT NULL,
  DESCRIPTION       VARCHAR2(50) NOT NULL,
  SYSTEMUSER_ID     NUMBER NOT NULL,
  TIMESTAMP         DATE NOT NULL
);

ALTER TABLE OOSRESTRICTION_DESC
  ADD CONSTRAINT XPKOOSRESTRICTION_DESC PRIMARY KEY (OOSRESTRICTION_ID, LANGUAGE_ID);
  
CREATE INDEX XIF1OOSRESTRICTION_DESC ON OOSRESTRICTION_DESC (OOSRESTRICTION_ID);

CREATE INDEX XIF2OOSRESTRICTION_DESC ON OOSRESTRICTION_DESC (LANGUAGE_ID);

ALTER TABLE OOSRESTRICTION_DESC
  ADD CONSTRAINT OOSRESTRICTION_DESC_OOSREST_ID FOREIGN KEY (OOSRESTRICTION_ID)
  REFERENCES OOSRESTRICTION (OOSRESTRICTION_ID);
  
ALTER TABLE OOSRESTRICTION_DESC
  ADD CONSTRAINT OOSRESTRICTION_DESC_LANG_ID FOREIGN KEY (LANGUAGE_ID)
  REFERENCES LANGUAGES (LANGUAGE_ID);
  
  
--------------------------  OOSACTIONRESTRICTION  -----------------------
CREATE SEQUENCE SEQ_OOSACTIONRESTRICTION
START WITH 1
INCREMENT BY 1;

CREATE TABLE OOSACTIONRESTRICTION
(
  OOSACTIONRESTRICTION_ID NUMBER NOT NULL,
  OOSACTION_ID            NUMBER NOT NULL,
  OOSRESTRICTION_ID       NUMBER NOT NULL,
  SYSTEMUSER_ID           NUMBER NOT NULL,
  TIMESTAMP               DATE NOT NULL
);

ALTER TABLE OOSACTIONRESTRICTION
  ADD CONSTRAINT XPKOOSACTIONRESTRICTION PRIMARY KEY (OOSACTIONRESTRICTION_ID);
 
CREATE UNIQUE INDEX XAK1OOSACTIONRESTRICTION ON OOSACTIONRESTRICTION (OOSACTION_ID,OOSRESTRICTION_ID);

CREATE INDEX XIF1OOSACTIONRESTRICTION ON OOSACTIONRESTRICTION (OOSACTION_ID);

CREATE INDEX XIF2OOSACTIONRESTRICTION ON OOSACTIONRESTRICTION (OOSRESTRICTION_ID);

ALTER TABLE OOSACTIONRESTRICTION
  ADD CONSTRAINT OOSACTIONRESTRI_OOSACTION_ID FOREIGN KEY (OOSACTION_ID)
  REFERENCES OOSACTION (OOSACTION_ID);
  
ALTER TABLE OOSACTIONRESTRICTION
  ADD CONSTRAINT OOSRESTRICTION_ID_OOSREST_ID FOREIGN KEY (OOSRESTRICTION_ID)
  REFERENCES OOSRESTRICTION (OOSRESTRICTION_ID);
  
-- 
INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 1, 1, GETMINSYSTEMUSER(), SYSDATE);  
COMMIT;

INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 1, 2, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 1, 5, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 2, 1, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 2, 2, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 2, 3, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 2, 4, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;

INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 3, 1, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;
 
INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 3, 2, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;
  
INSERT INTO OOSACTIONRESTRICTION
  (OOSACTIONRESTRICTION_ID,
   OOSACTION_ID,
   OOSRESTRICTION_ID,
   SYSTEMUSER_ID,
   TIMESTAMP)
VALUES
  (SEQ_OOSACTIONRESTRICTION.NEXTVAL, 3, 5, GETMINSYSTEMUSER(), SYSDATE);
COMMIT;
  
-------------------  STOCKROOMOOSACTIONS  -------------------
CREATE SEQUENCE SEQ_STOCKROOMOOSACTIONS
START WITH 1
INCREMENT BY 1;

CREATE TABLE STOCKROOMOOSACTIONS
(
  STOCKROOMOOSACTIONS_ID NUMBER NOT NULL,
  STOCKROOM_ID           NUMBER NOT NULL,
  OOSACTION_ID           NUMBER NOT NULL,
  OOSRESTRICTION_ID      NUMBER DEFAULT 1 NOT NULL,
  SYSTEMUSER_ID          NUMBER NOT NULL,
  TIMESTAMP              DATE NOT NULL
);
ALTER TABLE STOCKROOMOOSACTIONS
  ADD CONSTRAINT XPKSTOCKROOMOOSACTIONS PRIMARY KEY (STOCKROOMOOSACTIONS_ID);
  
CREATE UNIQUE INDEX XAK1STOCKROOMOOSACTIONS ON STOCKROOMOOSACTIONS (STOCKROOM_ID, OOSACTION_ID);

CREATE INDEX XIF1STOCKROOMOOSACTIONS ON STOCKROOMOOSACTIONS (STOCKROOM_ID);

CREATE INDEX XIF2STOCKROOMOOSACTIONS ON STOCKROOMOOSACTIONS (OOSACTION_ID);

CREATE INDEX XIF3STOCKROOMOOSACTIONS ON STOCKROOMOOSACTIONS (OOSRESTRICTION_ID);

ALTER TABLE STOCKROOMOOSACTIONS
  ADD CONSTRAINT STOCKROOMOOSA_STOCK_ID FOREIGN KEY (STOCKROOM_ID)
  REFERENCES STOCKROOM (STOCKROOM_ID);
  
ALTER TABLE STOCKROOMOOSACTIONS
  ADD CONSTRAINT STOCKROOMOOS_OOSA_ID FOREIGN KEY (OOSACTION_ID)
  REFERENCES OOSACTION (OOSACTION_ID);
  
ALTER TABLE STOCKROOMOOSACTIONS
  ADD CONSTRAINT STOCKROOMOOSA_OOSR_ID FOREIGN KEY (OOSRESTRICTION_ID)
  REFERENCES OOSRESTRICTION (OOSRESTRICTION_ID);

INSERT INTO STOCKROOMOOSACTIONS
(SELECT SEQ_STOCKROOMOOSACTIONS.NEXTVAL,
       S.STOCKROOM_ID,
       1,
       1,
       GETMINSYSTEMUSER(),
       TIMESTAMP
FROM STOCKROOM S);
COMMIT;

INSERT INTO STOCKROOMOOSACTIONS
(SELECT SEQ_STOCKROOMOOSACTIONS.NEXTVAL,
       S.STOCKROOM_ID,
       2,
       1,
       GETMINSYSTEMUSER(),
       TIMESTAMP
FROM STOCKROOM S);
COMMIT;

INSERT INTO STOCKROOMOOSACTIONS
(SELECT SEQ_STOCKROOMOOSACTIONS.NEXTVAL,
       S.STOCKROOM_ID,
       3,
       1,
       GETMINSYSTEMUSER(),
       TIMESTAMP
FROM STOCKROOM S);
COMMIT;