-- 20012431.70       Reason code stockroom per BU


CREATE SEQUENCE SEQ_STOCKROOMSPERREASONCODE
START WITH 1
INCREMENT BY 1;

CREATE TABLE STOCKROOMSPERREASONCODE
(
	STOCKROOMSPERREASONCODE_ID NUMBER NOT NULL ,
	REASONCODE_ID        NUMBER NOT NULL ,
	DEFAULTSTOCKROOM_ID  NUMBER NULL ,
	BUSINESSUNIT_ID      NUMBER NOT NULL ,
	FIXEDSTOCKROOM       VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField67 CHECK (FIXEDSTOCKROOM IN ('Y', 'N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE STOCKROOMSPERREASONCODE
ADD CONSTRAINT  XPKSTOCKROOMSPERREASONCODE PRIMARY KEY (STOCKROOMSPERREASONCODE_ID);


ALTER TABLE STOCKROOMSPERREASONCODE
ADD CONSTRAINT STOCKREASONCODE_REASONCODE FOREIGN KEY (REASONCODE_ID) REFERENCES REASONCODE (REASONCODE_ID) ON DELETE CASCADE;

CREATE  INDEX XIF1STOCKROOMSPERREASONCODE ON STOCKROOMSPERREASONCODE
(REASONCODE_ID   ASC);


ALTER TABLE STOCKROOMSPERREASONCODE
ADD CONSTRAINT STOCKREASONCODE_STOCKROOM FOREIGN KEY (DEFAULTSTOCKROOM_ID) REFERENCES STOCKROOM (STOCKROOM_ID);

CREATE  INDEX XIF2STOCKROOMSPERREASONCODE ON STOCKROOMSPERREASONCODE
(DEFAULTSTOCKROOM_ID   ASC);


ALTER TABLE STOCKROOMSPERREASONCODE
ADD CONSTRAINT STOCKREASONCODE_BUSINESSUNIT FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

CREATE  INDEX XIF3STOCKROOMSPERREASONCODE ON STOCKROOMSPERREASONCODE
(BUSINESSUNIT_ID   ASC);


ALTER TABLE STOCKROOMSPERREASONCODE
  ADD CONSTRAINT FIXEDSTOCKROOM_valid
 CHECK ((FIXEDSTOCKROOM = 'Y' AND DEFAULTSTOCKROOM_ID IS NOT NULL)  OR  (FIXEDSTOCKROOM = 'N')) ;

CREATE UNIQUE INDEX XAK1STOCKROOMSPERREASONCODE ON STOCKROOMSPERREASONCODE
(REASONCODE_ID   ASC,BUSINESSUNIT_ID   ASC,DEFAULTSTOCKROOM_ID   ASC);


INSERT INTO STOCKROOMSPERREASONCODE
  (STOCKROOMSPERREASONCODE_ID, REASONCODE_ID, DEFAULTSTOCKROOM_ID, BUSINESSUNIT_ID, FIXEDSTOCKROOM, SYSTEMUSER_ID, TIMESTAMP)
  SELECT SEQ_STOCKROOMSPERREASONCODE.nextval,
         R.REASONCODE_ID,
         R.DEFAULTSTOCKROOM_ID,
         B.BUSINESSUNIT_ID,
         R.FIXEDSTOCKROOM,
         GETMINSYSTEMUSER(),
         SYSDATE 
    FROM BUSINESSUNIT   B, STOCKROOMSSERVICINGBU S,
    REASONCODE R WHERE S.BUSINESSUNIT_ID=B.BUSINESSUNIT_ID
    AND S.STOCKROOM_ID=R.DEFAULTSTOCKROOM_ID
    AND R.DEFAULTSTOCKROOM_ID IS NOT NULL;

COMMIT;



ALTER TABLE REASONCODE
DROP CONSTRAINT REASONCODE_DEFAULTSTOCKROOM_ID;


ALTER TABLE REASONCODE
  DROP COLUMN DEFAULTSTOCKROOM_ID CASCADE CONSTRAINTS;


ALTER TABLE REASONCODE
  DROP COLUMN FIXEDSTOCKROOM CASCADE CONSTRAINTS;