--ABSN-8431
begin
execute immediate 
  'insert into characteristic
    (characteristic_id, code, active, datatype, length, decimals, mandatory, keepvalue,
     systemuser_id, timestamp, defdatevalue, tooltip, dialog_id)
    select seq_characteristic.nextval, ''PEPPOL_LEGAL'', ''Y'', 5, 1, 0, ''N'', ''N'', 1, sysdate, ''N'', null,
           dg.dialog_id
    from   dialogs dg
    where  dg.code = ''LegalEntity''';
  commit;
exception
  when dup_val_on_index then
    null;
end;
/

begin
execute immediate
  'insert into characteristicpredefvalue
    (characteristicpredefvalue_id, characteristic_id, sequencenumber, stringvalue, systemuser_id,
     timestamp, defaultvalue)
    select seq_characteristicpredefvalue.nextval, ch.characteristic_id, x.sequencenumber,
           x.stringvalue, 1, sysdate, x.defaultvalue
    from   (select 1 sequencenumber, ''VAT'' stringvalue, ''Y'' defaultvalue
             from   dual
             union all
             select 2 sequencenumber, ''GLN'' stringvalue, ''N'' defaultvalue
             from   dual
             union all
             select 3 sequencenumber, ''COC'' stringvalue, ''N'' defaultvalue
             from   dual) x
    cross  join characteristic ch
    where  ch.code = ''PEPPOL_LEGAL''
    and    not exists (select 1
            from   characteristicpredefvalue z
            where  z.characteristic_id = ch.characteristic_id
            and    z.sequencenumber = x.sequencenumber)';
 commit;            
end;
/

begin
execute immediate
  'insert into dialogflexfields
    (dialogflexfield_id, dialog_id, entity_id, characteristic_id, characteristicpredefvalue_id,
     systemuser_id, timestamp)
    select seq_dialogflexfields.nextval, ch.dialog_id, le.legalentity_id, ch.characteristic_id,
           cpv.characteristicpredefvalue_id, 1, sysdate
    from   legalentity le
    cross  join characteristic ch
    join   characteristicpredefvalue cpv
    on     cpv.characteristic_id = ch.characteristic_id
    and    cpv.sequencenumber = 1
    where  ch.code = ''PEPPOL_LEGAL''
    and    not exists (select 1
            from   dialogflexfields dx
            where  dx.characteristic_id = ch.characteristic_id
            and    dx.dialog_id = ch.dialog_id
            and    dx.entity_id = le.legalentity_id)';
  commit;
end;
/

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_LEGAL''
  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_LEGAL''
  and    ct.dialog_id = (select dlg.dialog_id from dialogs dlg where dlg.code = ''LegalEntity'')
  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_LEGAL''
  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_LEGAL''), 1, 1,
         sysdate
  from   characteristicstemplate cht
  where  cht.code = ''PEPPOL_LEGAL''
  and    not exists (select 1
          from   characteristicstempline ecl
          where  ecl.characteristicstemplate_id = cht.characteristicstemplate_id
          and    ecl.sequencenumber = 1)';
  commit;
end;
/
