--ABSN-8431
declare
begin
  execute immediate 'insert into characteristicstemplate
  (characteristicstemplate_id, code, type, active, systemuser_id, timestamp, dialog_id)
  select seq_characteristicstemplate.nextval, ch.code, 2, ''Y'', 1, sysdate, ch.dialog_id
  from   characteristic ch
  where  ch.code = ''PEPPOL''
  and    not exists (select 1
          from   characteristicstemplate ct
          where  ct.code = ch.code
          and    ct.dialog_id = ch.dialog_id)';
  commit;
end;
/

declare
begin
  execute immediate 'insert into characteristicstemplate_desc
  (characteristicstemplate_id, language_id, description, systemuser_id, timestamp)
  select ct.characteristicstemplate_id, l.language_id, ct.code, 1, sysdate
  from   characteristicstemplate ct
  cross  join languages l
  where  ct.code = ''PEPPOL''
  and    ct.dialog_id = (select dlg.dialog_id from dialogs dlg where dlg.code = ''Customer'')
  and    not exists (select 1
          from   characteristicstemplate_desc cd
          where  cd.characteristicstemplate_id = ct.characteristicstemplate_id
          and    cd.language_id = l.language_id)';
  commit;
end;
/

declare
begin
  execute immediate 'insert into characteristic_desc
  (characteristic_id, language_id, description, systemuser_id, timestamp)
  select ch.characteristic_id, l.language_id, ch.code, 1, sysdate
  from   characteristic ch
  cross  join languages l
  where  ch.code = ''PEPPOL''
  and    not exists (select 1
          from   characteristic_desc cd
          where  cd.characteristic_id = ch.characteristic_id
          and    cd.language_id = l.language_id)';
  commit;
end;
/

declare
begin
  execute immediate 'insert into characteristicstempline
  (characteristicstemplate_id, characteristic_id, sequencenumber, systemuser_id, timestamp)
  select cht.characteristicstemplate_id,
         (select max(ch.characteristic_id) from characteristic ch where ch.code = ''PEPPOL''), 1, 1,
         sysdate
  from   characteristicstemplate cht
  where  cht.code = ''PEPPOL''
  and    not exists (select 1
          from   characteristicstempline ecl
          where  ecl.characteristicstemplate_id = cht.characteristicstemplate_id
          and    ecl.sequencenumber = 1)';
  commit;
end;
/