-- Order 20011179.130 ((32) Sticker for each single item to be located in stock or 1 sticker for stack of same items)

-----------------------PRINTINGRECEPTIONSTICKERS TABLE-------------------------

CREATE SEQUENCE SEQ_PRINTINGRECEPTIONSTICKERS
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRINTINGRECEPTIONSTICKERS
(	PRINTINGRECEPTIONSTICKERS_ID NUMBER NOT NULL ,
	DIALOG_ID            NUMBER NOT NULL ,
	NEW                  VARCHAR2(1) NOT NULL  CONSTRAINT  NEW_valid CHECK (NEW IN ('Y', 'N')),
	TYPEOFMERCHANDISE    NUMBER NOT NULL  CONSTRAINT  TYPEOFMERCH_valid CHECK (TYPEOFMERCHANDISE IN (1, 2, 3, 4)),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL);

COMMENT ON COLUMN PRINTINGRECEPTIONSTICKERS.TYPEOFMERCHANDISE IS '1 = general (linen)
2 = garments
3 = sales only items
4 = emblems';


ALTER TABLE PRINTINGRECEPTIONSTICKERS
ADD CONSTRAINT  XPKPRINTINGRECEPTIONSTICKERS PRIMARY KEY (PRINTINGRECEPTIONSTICKERS_ID);

CREATE  INDEX XIF1PRINTINGRECEPTIONSTICKERS ON PRINTINGRECEPTIONSTICKERS
(DIALOG_ID   ASC);

ALTER TABLE PRINTINGRECEPTIONSTICKERS
ADD CONSTRAINT PRINTINGRECEPTIONSTICK_DIALOGS FOREIGN KEY (DIALOG_ID) REFERENCES DIALOGS (DIALOG_ID) ON DELETE CASCADE;

CREATE UNIQUE INDEX XAK1PRINTINGRECEPTIONSTICKERS ON PRINTINGRECEPTIONSTICKERS
(DIALOG_ID   ASC,TYPEOFMERCHANDISE   ASC,NEW   ASC);


-----------------------INSERT INTO PRINTINGRECEPTIONSTICKERS TABLE-------------------------

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsInStockNoPO''),
         ''Y'',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromPO''),
         ''Y'',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReturnToStock''),
         ''Y'',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''MoveStockWithinStockroom''),
        ''Y'',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsInStockNoPO''),
         ''Y'',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromPO''),
         ''Y'',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReturnToStock''),
         ''Y'',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''MoveStockWithinStockroom''),
         ''Y'',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsInStockNoPO''),
         ''Y'',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromPO''),
         ''Y'',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReturnToStock''),
         ''Y'',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''MoveStockWithinStockroom''),
         ''Y'',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsInStockNoPO''),
         ''Y'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromPO''),
         ''Y'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReturnToStock''),
         ''Y'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''MoveStockWithinStockroom''),
         ''Y'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsInStockNoPO''),
         ''N'',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromPO''),
        ''N'',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReturnToStock''),
         ''N'',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''MoveStockWithinStockroom''),
         ''N'',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsInStockNoPO''),
         ''N'',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromPO''),
         ''N'',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReturnToStock''),
         ''N'',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''MoveStockWithinStockroom''),
         ''N'',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsInStockNoPO''),
         ''N'',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromPO''),
         ''N'',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReturnToStock''),
         ''N'',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''MoveStockWithinStockroom''),
         ''N'',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsInStockNoPO''),
         ''N'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromPO''),
         ''N'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReturnToStock''),
         ''N'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''MoveStockWithinStockroom''),
         ''N'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

-----------------------STOCKROOMPRINTINGSTICKERS TABLE-------------------------

CREATE TABLE STOCKROOMPRINTINGSTICKERS
(
	PRINTINGRECEPTIONSTICKERS_ID NUMBER NOT NULL ,
	STOCKROOM_ID         NUMBER NOT NULL ,
	AUTOPRINT            VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  AUTOPRINT_valid CHECK (AUTOPRINT IN ('Y','N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


ALTER TABLE STOCKROOMPRINTINGSTICKERS
ADD CONSTRAINT  XPKSTOCKROOMPRINTINGSTICKERS PRIMARY KEY (PRINTINGRECEPTIONSTICKERS_ID,STOCKROOM_ID);

CREATE  INDEX XIF1STOCKROOMPRINTINGSTICKERS ON STOCKROOMPRINTINGSTICKERS
(PRINTINGRECEPTIONSTICKERS_ID   ASC);

CREATE  INDEX XIF2STOCKROOMPRINTINGSTICKERS ON STOCKROOMPRINTINGSTICKERS
(STOCKROOM_ID   ASC);


ALTER TABLE STOCKROOMPRINTINGSTICKERS
ADD CONSTRAINT STOCKROOMPRINTSTICK_PRINTRECST FOREIGN KEY (PRINTINGRECEPTIONSTICKERS_ID) REFERENCES PRINTINGRECEPTIONSTICKERS (PRINTINGRECEPTIONSTICKERS_ID) ON DELETE CASCADE;

ALTER TABLE STOCKROOMPRINTINGSTICKERS
ADD CONSTRAINT STOCKROOMPRINTSTICK_STOCKROOM FOREIGN KEY (STOCKROOM_ID) REFERENCES STOCKROOM (STOCKROOM_ID) ON DELETE CASCADE;

