-- order 20011611 (SMG100) Receive items on stock: redesign of receiving items from SDO (push orders)



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

ALTER TABLE WORKORDER
	ADD (SUBTYPE NUMBER  DEFAULT 0 NOT NULL CONSTRAINT SUBTYPE_valid CHECK (SUBTYPE IN (0, 1)));

COMMENT ON COLUMN WORKORDER.SUBTYPE IS '0 = Standard
1 = Stock Delivery Order (Quick Scan)
';

-----------------------INSERT INTO DIALOGS TABLE-------------------------

BEGIN
  EXECUTE IMMEDIATE ('INSERT INTO DIALOGS 
SELECT SEQ_DIALOGS.NEXTVAL, ''-'', ''ReceiveItemsFromSDOPush'', 
''Receive Items in Stock from Push Order'', 
''-'',
 GETMINSYSTEMUSER(), 
 ''N'', 
 SYSDATE, 
 ''N'',
 ''N'', 
 ''N'', 
 ''N'', 
 ''N'', 
 ''N'', 
 ''N'', 
 ''-'', 
 ''-'', 
 0, 
 0, 
 NULL 
 FROM DUAL');
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/


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

BEGIN
EXECUTE IMMEDIATE ('INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE=''ReceiveItemsFromSDOPush''),
         ''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=''ReceiveItemsFromSDOPush''),
         ''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=''ReceiveItemsFromSDOPush''),
         ''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=''ReceiveItemsFromSDOPush''),
        ''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=''ReceiveItemsFromSDOPush''),
         ''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=''ReceiveItemsFromSDOPush''),
         ''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=''ReceiveItemsFromSDOPush''),
         ''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=''ReceiveItemsFromSDOPush''),
         ''N'',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL');
EXCEPTION WHEN OTHERS THEN NULL;
END;
/


/*-----------------------INSERT INTO PREDEFINEDREPORT TABLE-------------------------

INSERT INTO PREDEFINEDREPORT
VALUES
  (SEQ_PREDEFINEDREPORT.NEXTVAL,
   (SELECT DD.DIALOG_ID FROM DIALOGS DD WHERE DD.CODE LIKE 'ReceiveItemsFromSDOPush'),
   'StockReceptionSticker',
   'Voorraad ontvangst stickers',
   'Stock Reception Stickers',
   'Lager Eingang Kleber',
   getminsystemuser(),
   SYSDATE);*/







