--ABSN-8431
DROP TYPE T_PEPPOL_ENDPOINT_TAB;
DROP TYPE T_PEPPOL_ENDPOINT;
CREATE OR REPLACE TYPE T_PEPPOL_ENDPOINT AS OBJECT(CUSTOMER_ID NUMBER, SCHEMEID VARCHAR2(4), ENDPOINTID VARCHAR2(30));
/
CREATE OR REPLACE TYPE T_PEPPOL_ENDPOINT_TAB AS TABLE OF T_PEPPOL_ENDPOINT;
/

CREATE TABLE PEPPOL_ENDPOINT
(
  COUNTRY_CODE VARCHAR2(2) NOT NULL,
  FLEXFIELD_PEPPOL_CODE VARCHAR2(3) NOT NULL,
  SCHEMEID VARCHAR2(4) NOT NULL
);
ALTER TABLE PEPPOL_ENDPOINT ADD CONSTRAINT PK_PEPPOL_ENDPOINT PRIMARY KEY (COUNTRY_CODE, FLEXFIELD_PEPPOL_CODE);
COMMENT ON COLUMN PEPPOL_ENDPOINT.COUNTRY_CODE IS 'First 2 characters of the customers country.code';
COMMENT ON COLUMN PEPPOL_ENDPOINT.FLEXFIELD_PEPPOL_CODE IS 'Customers flex field called PEPPOL with fixed values like VAT (default), COC and GLN';
COMMENT ON COLUMN PEPPOL_ENDPOINT.SCHEMEID IS 'The schemeid to be used for the country flexfield';

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'', ''Y'', 5, 1, 0, ''N'', ''N'', 1, sysdate, ''N'', null,
           dg.dialog_id
    from   dialogs dg
    where  dg.code = ''Customer''';
  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''
    and    not exists (select 1
            from   characteristicpredefvalue z
            where  z.characteristic_id = ch.characteristic_id
            and    z.sequencenumber = x.sequencenumber)';
 commit;            
end;
/

begin
  --set customer peppol to GLN when they have globallocationnumber filled
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, cg.customer_id, ch.characteristic_id,
           cpv.characteristicpredefvalue_id, 1, sysdate
    from   customergeneral cg
    cross  join characteristic ch
    join   characteristicpredefvalue cpv
    on     cpv.characteristic_id = ch.characteristic_id
    and    cpv.sequencenumber = 2
    where  cg.globallocationnumber is not null
    and    ch.code = ''PEPPOL''
    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 = cg.customer_id)';
  commit;
end;
/
