create sequence seq_stocktransferheader start with 1 increment by 1;
      
      /*
      ACTION is CREATE Table STOCKTRANSFERHEADER
      */

CREATE TABLE STOCKTRANSFERHEADER (
       STOCKTRANSFERHEADER_ID NUMBER NOT NULL,
       SOURCESTOCKROOM_ID   NUMBER NOT NULL,
       SOURCEBUSINESSUNIT_ID NUMBER NOT NULL,
       TARGETSTOCKROOM_ID   NUMBER NOT NULL,
       TARGETBUSINESSUNIT_ID NUMBER NOT NULL,
       INTERNALPURCHASEORDERHEADER_ID NUMBER NULL,
       TRANSFERTYPE         NUMBER NOT NULL
                                   CONSTRAINT ValidTransferType
                                          CHECK (TRANSFERTYPE IN (1, 2)),
       ACCEPTED             VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationField
                                          CHECK (ACCEPTED IN ('Y', 'N')),
       DATECREATED          DATE NOT NULL,
       DATEDELIVERED        DATE NOT NULL,
       DATEACCEPTED         DATE NOT NULL,
       DATEREJECTED         DATE NOT NULL,
       DATECANCELLED        DATE NOT NULL,
       CREATIONUSER_ID      NUMBER NULL,
       CONFIRMATIONUSER_ID  NUMBER NULL,
       COMPLETIONUSER_ID    NUMBER NULL,
       REJECTIONUSER_ID     NUMBER NULL,
       CANCELLATIONUSER_ID  NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       STOCKDELIVERYWORKORDER_ID NUMBER NOT NULL
);

COMMENT ON COLUMN STOCKTRANSFERHEADER.TRANSFERTYPE IS '1 = Internal
2 = External';
CREATE INDEX XIF1STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       SOURCESTOCKROOM_ID             ASC
);

CREATE INDEX XIF10STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       CANCELLATIONUSER_ID            ASC
);

CREATE INDEX XIF11STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       STOCKDELIVERYWORKORDER_ID      ASC
);

CREATE INDEX XIF2STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       SOURCEBUSINESSUNIT_ID          ASC
);

CREATE INDEX XIF3STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       TARGETSTOCKROOM_ID             ASC
);

CREATE INDEX XIF4STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       TARGETBUSINESSUNIT_ID          ASC
);

CREATE INDEX XIF5STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       INTERNALPURCHASEORDERHEADER_ID ASC
);

CREATE INDEX XIF6STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       CREATIONUSER_ID                ASC
);

CREATE INDEX XIF7STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       CONFIRMATIONUSER_ID            ASC
);

CREATE INDEX XIF8STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       COMPLETIONUSER_ID              ASC
);

CREATE INDEX XIF9STOCKTRANSFERHEADER ON STOCKTRANSFERHEADER
(
       REJECTIONUSER_ID               ASC
);


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT XPKSTOCKTRANSFERHEADER PRIMARY KEY (
              STOCKTRANSFERHEADER_ID) ) ;


create sequence seq_stocktransferline start with 1 increment by 1;
      
      /*
      ACTION is CREATE Table STOCKTRANSFERLINE
      */

CREATE TABLE STOCKTRANSFERLINE (
	STOCKTRANSFERLINE_ID NUMBER NOT NULL,       
	STOCKTRANSFERHEADER_ID NUMBER NOT NULL,
       	SOURCESTOCKROOMLOCATION_ID NUMBER NULL,
       	PRODUCT_LINK_ID      NUMBER NOT NULL,
       	SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
       	QUALITYGRADE_ID      NUMBER NOT NULL,
       	UNIQUELYCODED        NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT UniquelyCoded_Valid
                                          CHECK (UNIQUELYCODED IN (0, 1, 2)),
       	CUSTOMER_LINK_ID     NUMBER NULL,
       	CUSTOMEROWNED        VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_Validation8714
                                          CHECK (CUSTOMEROWNED IN ('Y', 'N')),
       	PIECEVALUE_CURRENCY_ID NUMBER NOT NULL,
       	QTYSHIPPED           NUMBER DEFAULT 0 NOT NULL,
       	PIECEVALUE           NUMBER NOT NULL,
       	QTYACCEPTED          NUMBER DEFAULT 0 NOT NULL,
       	SHIPPINGUSER_ID      NUMBER NOT NULL,
       	ACCEPTINGUSER_ID     NUMBER NOT NULL,
       	REJECTINGUSER_ID     NUMBER NOT NULL,
       	QTYREJECTED          NUMBER DEFAULT 0 NOT NULL,
       	SYSTEMUSER_ID        NUMBER NOT NULL,
       	TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       STOCKTRANSFERHEADER_ID         ,
       SOURCESTOCKROOMLOCATION_ID     ,
       PRODUCT_LINK_ID                ,
       SIZEDEFINITION_LINK_ID         ,
       QUALITYGRADE_ID                ,
       UNIQUELYCODED                  ,
       CUSTOMER_LINK_ID               ,
       CUSTOMEROWNED                  
);

CREATE UNIQUE INDEX XAK2STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       STOCKTRANSFERHEADER_ID         ,
       STOCKTRANSFERLINE_ID                
);

CREATE INDEX XIF2STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       STOCKTRANSFERHEADER_ID         ASC
);

CREATE INDEX XIF3STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       SOURCESTOCKROOMLOCATION_ID     ASC
);

CREATE INDEX XIF4STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       PRODUCT_LINK_ID                ASC
);

CREATE INDEX XIF5STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       SIZEDEFINITION_LINK_ID         ASC
);

CREATE INDEX XIF6STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       QUALITYGRADE_ID                ASC
);

CREATE INDEX XIF7STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       CUSTOMER_LINK_ID               ASC
);

CREATE INDEX XIF8STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       PIECEVALUE_CURRENCY_ID         ASC
);

CREATE INDEX XIE1STOCKTRANSFERLINE ON STOCKTRANSFERLINE
(
       STOCKTRANSFERHEADER_ID         ,
       PRODUCT_LINK_ID                ,
       SIZEDEFINITION_LINK_ID         ,
       QUALITYGRADE_ID                ,
       CUSTOMER_LINK_ID               ,
       CUSTOMEROWNED                  
);


ALTER TABLE STOCKTRANSFERLINE
       ADD  ( CONSTRAINT XPKSTOCKTRANSFERLINE PRIMARY KEY (
              STOCKTRANSFERLINE_ID) ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_STOCKDELIV
              FOREIGN KEY (STOCKDELIVERYWORKORDER_ID)
                             REFERENCES WORKORDER ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_CANCELLATI
              FOREIGN KEY (CANCELLATIONUSER_ID)
                             REFERENCES SYSTEMUSER ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_REJECTIONU
              FOREIGN KEY (REJECTIONUSER_ID)
                             REFERENCES SYSTEMUSER ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_COMPLETION
              FOREIGN KEY (COMPLETIONUSER_ID)
                             REFERENCES SYSTEMUSER ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_CONFIRMATI
              FOREIGN KEY (CONFIRMATIONUSER_ID)
                             REFERENCES SYSTEMUSER ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_CREATIONUS
              FOREIGN KEY (CREATIONUSER_ID)
                             REFERENCES SYSTEMUSER ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_INTERNALPU
              FOREIGN KEY (INTERNALPURCHASEORDERHEADER_ID)
                             REFERENCES PURCHASEORDERHEADER ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_TARGETBUSI
              FOREIGN KEY (TARGETBUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_TARGETSTOC
              FOREIGN KEY (TARGETSTOCKROOM_ID)
                             REFERENCES STOCKROOM ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_SOURCEBUSI
              FOREIGN KEY (SOURCEBUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;


ALTER TABLE STOCKTRANSFERHEADER
       ADD  ( CONSTRAINT STOCKTRANSFERHEADER_SOURCESTOC
              FOREIGN KEY (SOURCESTOCKROOM_ID)
                             REFERENCES STOCKROOM ) ;


ALTER TABLE STOCKTRANSFERLINE
       ADD  ( CONSTRAINT STOCKTRANSFERLINE_PIECEVALUE_C
              FOREIGN KEY (PIECEVALUE_CURRENCY_ID)
                             REFERENCES CURRENCY ) ;


ALTER TABLE STOCKTRANSFERLINE
       ADD  ( CONSTRAINT STOCKTRANSFERLINE_CUSTOMER_LIN
              FOREIGN KEY (CUSTOMER_LINK_ID)
                             REFERENCES CUSTOMER_LINK ) ;


ALTER TABLE STOCKTRANSFERLINE
       ADD  ( CONSTRAINT STOCKTRANSFERLINE_QUALITYGRADE
              FOREIGN KEY (QUALITYGRADE_ID)
                             REFERENCES QUALITYGRADE ) ;


ALTER TABLE STOCKTRANSFERLINE
       ADD  ( CONSTRAINT STOCKTRANSFERLINE_SIZEDEFINITI
              FOREIGN KEY (SIZEDEFINITION_LINK_ID)
                             REFERENCES SIZEDEFINITION_LINK ) ;


ALTER TABLE STOCKTRANSFERLINE
       ADD  ( CONSTRAINT STOCKTRANSFERLINE_PRODUCT_LINK
              FOREIGN KEY (PRODUCT_LINK_ID)
                             REFERENCES PRODUCT_LINK ) ;


ALTER TABLE STOCKTRANSFERLINE
       ADD  ( CONSTRAINT STOCKTRANSFERLINE_SOURCESTOCKR
              FOREIGN KEY (SOURCESTOCKROOMLOCATION_ID)
                             REFERENCES STOCKROOMLOCATION ) ;


ALTER TABLE STOCKTRANSFERLINE
       ADD  ( CONSTRAINT STOCKTRANSFERLINE_STOCKTRANSFE
              FOREIGN KEY (STOCKTRANSFERHEADER_ID)
                             REFERENCES STOCKTRANSFERHEADER ) ;





