--ABS-71501 / ABS-71709
-- step 1 move plant specific flags to high ID range
UPDATE AUTOMATICFLAG F 
SET F.AUTOMATICFLAG_ID = 10000 + F.AUTOMATICFLAG_ID
WHERE PLANT_ID IS NOT NULL;
COMMIT;							

-- step 2 move flags inserted by db scripts to correct number, first moving them to -1 to prevent the next step goes wrong on unique index
UPDATE AUTOMATICFLAG F 
SET F.AUTOMATICFLAG_ID = -1 * F.AUTOMATICFLAG_ID
WHERE F.PLANT_ID IS NULL;
COMMIT;

-- step 3 correct wrongly inserted flags
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 1 WHERE F.EVENT = 'Maximum number of washes reached' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 2 WHERE F.EVENT = 'End of norm life time reached, auto replacement' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 3 WHERE F.EVENT = 'Repair requested by repair pointer' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 4 WHERE F.EVENT = 'Replacement requested using replacement button' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 5 WHERE F.EVENT = 'Return free of charge item' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 6 WHERE F.EVENT = 'Every xx washes reached' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 7 WHERE F.EVENT = 'Flag for return to stock due to manual decrease of max inv' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 8 WHERE F.EVENT = 'Quit wearer' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 9 WHERE F.EVENT = 'End of Season' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 10 WHERE F.EVENT = 'Start of Season' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 11 WHERE F.EVENT = 'Missing emblem' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 12 WHERE F.EVENT = 'Substitute garment' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 13 WHERE F.EVENT = 'Quit Mat' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 14 WHERE F.EVENT = 'Charged for Loss' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 15 WHERE F.EVENT = 'Temporary wearer inventory rent stop' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 16 WHERE F.EVENT = 'Decrease max inv. after return to stock items with this flag' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 17 WHERE F.EVENT = 'Product contract ended for customer' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 18 WHERE F.EVENT = 'Customer no longer active' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 19 WHERE F.EVENT = 'Rewash' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 20 WHERE F.EVENT = 'Show on replaced item before replacement is active' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 21 WHERE F.EVENT = 'Saving changed wearer data' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 22 WHERE F.EVENT = 'Rewashed' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 23 WHERE F.EVENT = 'Garment Dispenser' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 24 WHERE F.EVENT = 'Quit Wearer Inventory' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 25 WHERE F.EVENT = 'Direct Sales Only' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 26 WHERE F.EVENT = 'Returned' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 27 WHERE F.EVENT = 'Maximum Number of Repairs' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 28 WHERE F.EVENT = 'Lost And Found' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 29 WHERE F.EVENT = 'Repair requested by Web' AND F.PLANT_ID IS NULL;
COMMIT;
UPDATE AUTOMATICFLAG F SET F.AUTOMATICFLAG_ID = 30 WHERE F.EVENT = 'Repair' AND F.PLANT_ID IS NULL;
COMMIT;


-- step 4 insert flags that failed to be inserted before.
INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   20,
   'Show on replaced item before replacement is active',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'N',
   'N',
   'N',
   1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 20);
COMMIT;

INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   21,
   'Saving changed wearer data',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'Y',
   'Y',
   'Y',
   1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 21);
COMMIT;

INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   22,
   'Rewashed',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'Y',
   'Y',
   'Y',
   4 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 22);
COMMIT;

INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   23,
   'Garment Dispenser',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'Y',
   'Y',
   'Y',
   1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 23);
COMMIT;

INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   24,
   'Quit Wearer Inventory',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'Y',
   'N',
   'Y',
   1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 24);
COMMIT;

INSERT INTO AUTOMATICFLAG
    (AUTOMATICFLAG_ID,
    EVENT,
    SYSTEMUSER_ID,
    TIMESTAMP,
    SELECTABLE,
    DELETABLE,
    TRANSFERABLE,
    FLAGTYPE)
SELECT 
  25,
  'Direct Sales Only',
  GETMINSYSTEMUSER(),
  SYSDATE,
  'Y',
  'N',
  'N',
  1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 25);   
COMMIT;
   
INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   26,
   'Returned',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'N',
   'N',
   'Y',
   1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 26);
COMMIT;

INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   27,
   'Maximum Number of Repairs',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'N',
   'Y',
   'Y',
   1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 27);
COMMIT;

INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   28,
   'Lost And Found',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'N',
   'N',
   'N',
   1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 28);
COMMIT;

INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
   EVENT,
   SYSTEMUSER_ID,
   TIMESTAMP,
   SELECTABLE,
   DELETABLE,
   TRANSFERABLE,
   FLAGTYPE)
SELECT
   29,
   'Repair requested by Web',
   GETMINSYSTEMUSER(),
   SYSDATE,
   'N',
   'Y',
   'Y',
   1 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 29);
COMMIT;
   
INSERT INTO AUTOMATICFLAG
  (AUTOMATICFLAG_ID,
  EVENT,
  SYSTEMUSER_ID,
  TIMESTAMP,
  SELECTABLE,
  DELETABLE,
  TRANSFERABLE,
  FLAGTYPE)
SELECT 
  30,
  'Repair',
  GETMINSYSTEMUSER(),
  SYSDATE,
  'Y',
  'Y',
  'Y',
  4 FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM AUTOMATICFLAG Q WHERE Q.AUTOMATICFLAG_ID = 30);
COMMIT;

DROP SEQUENCE SEQ_AUTOMATICFLAG;

create or replace procedure fixAutomaticFlag is
  maxprimarykey number;
begin
  select max(a.automaticflag_id) + 1
    into maxprimarykey
    from automaticflag a;
  begin
    execute immediate 'CREATE SEQUENCE SEQ_AUTOMATICFLAG START WITH ' ||
                      maxprimarykey || ' INCREMENT BY 1';
  end;
end fixAutomaticFlag;
/

execute fixAutomaticFlag;
drop procedure fixAutomaticFlag;

DELETE FROM AUTOMATICFLAG AF
WHERE AF.AUTOMATICFLAG_ID < 0;

COMMIT;