-- ABS-57480 / ABS-65431
--- DIALOGS table---
ALTER TRIGGER AFTER_INSERT_DIALOGS DISABLE;

INSERT INTO DIALOGS
      SELECT SEQ_DIALOGS.NEXTVAL,
             'Uit roulatie nemen in batch',
             'ReturnToStockOrRagInBatch',
             'Take Items Out Of Circulation in Batch',
             'Zurücknahme Teile/Kleidung in Batch',
             GETMINSYSTEMUSER(),
             'Y',
             SYSDATE,
             'N',
             'N',
             'Y',
             'N',
             'N',
             'Y',
             'N',
             'Reprise pièces/vêtements en groupe',
             'Tage beklædningsdele ud af cirkulation',
             0,
             0,
             '',
             1,
             'Retornar en Stock en Lote',
             'N'
        FROM DUAL;
COMMIT;

create or replace procedure CreateDialogDescForABS57480 is
  doInsert      number;
  LanguageID_EN number;
  LanguageID_GB number;
  LanguageID_NL number;
  LanguageID_FR number;
  LanguageID_DE number;
  LanguageID_DK number;
  LanguageID_ES number;
  count_DK      number;
  count_EN      number;
  count_GB      number;
  count_NL      number;
  count_FR      number;
  count_DE      number;
  count_ES      number;
  DialogID      dialogs.dialog_id%type;
  Endescription dialogs.endescription%type;
  Nldescription dialogs.nldescription%type;
  Frdescription dialogs.frdescription%type;
  Dedescription dialogs.gedescription%type;
  Dkdescription dialogs.dkdescription%type;
  Esdescription dialogs.spdescription%type;
begin
  doInsert      := 1;
  Endescription := 'Take Items Out Of Circulation in Batch';
  Nldescription := 'Uit roulatie nemen in batch';
  Frdescription := 'Reprise pièces/vêtements en groupe';
  Dedescription := 'Zurücknahme Teile/Kleidung in Batch';
  Dkdescription := 'Tage beklædningsdele ud af cirkulation';
  Esdescription := 'Retornar en Stock en Lote';

  begin
    select d.dialog_id 
    into DialogID 
    from dialogs d
    where d.code='ReturnToStockOrRagInBatch';
  end;
  -----check if US - English translation exists

  select count(1)
    into count_EN
    from dialogs_desc dd
   where dd.dialog_id = DialogID
     and dd.language_id =
         (select l.language_id from languages l where l.code = 'EN_US');
  if count_EN = 0 then
    begin
    
      begin
        select l.language_id
          into LanguageID_EN
          from languages l
         where l.code = 'EN_US';
      exception
        when no_data_found then
          doInsert := 0;
      end;
      if doInsert = 1 then
        insert into dialogs_desc dd
          (dialog_id, language_id, description, systemuser_id, timestamp)
        values
          (DialogID, LanguageID_EN, Endescription, 1, sysdate);
      end if;
    end;
  end if;

  -----check if GB - English translation exists

  select count(1)
    into count_GB
    from dialogs_desc dd
   where dd.dialog_id = DialogID
     and dd.language_id =
         (select l.language_id from languages l where l.code = 'EN_GB');
  if count_GB = 0 then
    begin
      begin
        select l.language_id
          into LanguageID_GB
          from languages l
         where l.code = 'EN_GB';
      exception
        when no_data_found then
          doInsert := 0;
      end;
      if doInsert = 1 then
        insert into dialogs_desc dd
          (dialog_id, language_id, description, systemuser_id, timestamp)
        values
          (DialogID, LanguageID_GB, Endescription, 1, sysdate);
      end if;
    end;
  end if;

  -----check if Dutch (NL) translation exists
  select count(1)
    into count_NL
    from dialogs_desc dd
   where dd.dialog_id = DialogID
     and dd.language_id =
         (select l.language_id from languages l where l.code = 'NL_NL');
  if count_NL = 0 then
    begin
      begin
        select l.language_id
          into LanguageID_NL
          from languages l
         where l.code = 'NL_NL';
      exception
        when no_data_found then
          doInsert := 0;
      end;
      if doInsert = 1 then
        insert into dialogs_desc dd
          (dialog_id, language_id, description, systemuser_id, timestamp)
        values
          (DialogID, LanguageID_NL, Nldescription, 1, sysdate);
      end if;
    end;
  end if;

  -----check if French (FR) translation exists
  select count(1)
    into count_FR
    from dialogs_desc dd
   where dd.dialog_id = DialogID
     and dd.language_id =
         (select l.language_id from languages l where l.code = 'FR_FR');
  if count_FR = 0 then
    begin
      begin
        select l.language_id
          into LanguageID_FR
          from languages l
         where l.code = 'FR_FR';
      exception
        when no_data_found then
          doInsert := 0;
      end;
      if doInsert = 1 then
        insert into dialogs_desc dd
          (dialog_id, language_id, description, systemuser_id, timestamp)
        values
          (DialogID, LanguageID_FR, Frdescription, 1, sysdate);
      end if;
    end;
  end if;

  -----check if German (DE_DE) translation exists
  select count(1)
    into count_DE
    from dialogs_desc dd
   where dd.dialog_id = DialogID
     and dd.language_id =
         (select l.language_id from languages l where l.code = 'DE_DE');
  if count_DE = 0 then
    begin
      begin
        select l.language_id
          into LanguageID_DE
          from languages l
         where l.code = 'DE_DE';
      exception
        when no_data_found then
          doInsert := 0;
      end;
      if doInsert = 1 then
        insert into dialogs_desc dd
          (dialog_id, language_id, description, systemuser_id, timestamp)
        values
          (DialogID, LanguageID_DE, Dedescription, 1, sysdate);
      end if;
    end;
  end if;

  -----check if Danish (DA_DK) translation exists
  select count(1)
    into count_DK
    from dialogs_desc dd
   where dd.dialog_id = DialogID
     and dd.language_id =
         (select l.language_id from languages l where l.code = 'DA_DK');
  if count_DK = 0 then
    begin
      begin
        select l.language_id
          into LanguageID_DK
          from languages l
         where l.code = 'DA_DK';
      exception
        when no_data_found then
          doInsert := 0;
      end;
      if doInsert = 1 then
        insert into dialogs_desc dd
          (dialog_id, language_id, description, systemuser_id, timestamp)
        values
          (DialogID, LanguageID_DK, Dkdescription, 1, sysdate);
      end if;
    end;
  end if;

  -----check if Spanish (ES_ES) translation exists
  select count(1)
    into count_ES
    from dialogs_desc dd
   where dd.dialog_id = DialogID
     and dd.language_id =
         (select l.language_id from languages l where l.code = 'ES_ES');
  if count_ES = 0 then
    begin
      begin
        select l.language_id
          into LanguageID_ES
          from languages l
         where l.code = 'ES_ES';
      exception
        when no_data_found then
          doInsert := 0;
      end;
      if doInsert = 1 then
        insert into dialogs_desc dd
          (dialog_id, language_id, description, systemuser_id, timestamp)
        values
          (DialogID, LanguageID_ES, Esdescription, 1, sysdate);
      end if;
    end;
  end if;
end CreateDialogDescForABS57480;
   /
execute CreateDialogDescForABS57480;
drop procedure CreateDialogDescForABS57480; 

ALTER TRIGGER AFTER_INSERT_DIALOGS ENABLE;

---RAGREASONDIALOGS table---
INSERT INTO RAGREASONDIALOGS
  (RAGREASON_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT R.RAGREASON_ID,
         D.DIALOG_ID,
         'Y',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM RAGREASON R, DIALOGS D
   WHERE D.CODE = 'ReturnToStockOrRagInBatch';

COMMIT;

---REASONCODEDIALOGS table---
INSERT INTO REASONCODEDIALOGS
  (REASONCODE_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT R.REASONCODE_ID,
         D.DIALOG_ID,
         'Y',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM REASONCODE R,
         DIALOGS    D WHERE D.CODE = 'ReturnToStockOrRagInBatch';
COMMIT;

---PRINTINGRECEPTIONSTICKERS table---
INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE='ReturnToStockOrRagInBatch'),
         'Y',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL;
COMMIT;

INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE='ReturnToStockOrRagInBatch'),
         'Y',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL;
COMMIT;

INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE='ReturnToStockOrRagInBatch'),
         'Y',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL;
COMMIT;

INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE='ReturnToStockOrRagInBatch'),
         'Y',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL;
COMMIT;

INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE='ReturnToStockOrRagInBatch'),
         'N',
         1,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL;
COMMIT;

INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE='ReturnToStockOrRagInBatch'),
         'N',
         2,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL;
COMMIT;

INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE='ReturnToStockOrRagInBatch'),
         'N',
         3,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL;
COMMIT;

INSERT INTO PRINTINGRECEPTIONSTICKERS
  SELECT SEQ_PRINTINGRECEPTIONSTICKERS.NEXTVAL,
         (SELECT D.DIALOG_ID FROM DIALOGS D WHERE D.CODE='ReturnToStockOrRagInBatch'),
         'N',
         4,
         GETMINSYSTEMUSER(),
         SYSDATE
  FROM DUAL;
COMMIT;
