Invantive Estate

In this example using ISP a newsletter will be composed in the next steps:

•Create a profile option containing the layout of the newsletter.

•Create a profile option containing the ISP.

•Send newsletter, select the users and create the emails used for the newsletter.

Note that you can merge both profile options to one profile option.

The final result is a newsletter in the following format:

Profile Option Layout Newsletter

This local profile option contains the layout of the newsletter. The texts ‘: Payload’ and ‘: FOOTER’ are replaced by the message:

Profile Option ISP

This local profile option contains the ISP to compose the message:

The code is:

<%@ page language="ISP" %>

<%

declare

 l_gbr_aanhef      varchar2(500);

 l_template        clob;

 l_payload         clob;

 l_tak_found       boolean;

begin

 l_template := bubs#profiel_opties.get_value('xxinet-html-nieuwsbrief-template');

 l_payload  := '';

 --

 -- Determine greeting.

 --

 select 'Geachte '

        || case

           when gbr.gbr_geslacht_ind = 'M'

           then 'heer '

           when gbr.gbr_geslacht_ind = ‘V’

           then 'mevrouw '

           else ''

           end

        || gbr_naam

 into   l_gbr_aanhef

 from   bubs_gebruikers_v gbr

 where  gbr.gbr_id = l_parameters_map('GBR_ID')

 ;

 l_payload  := l_payload || l_gbr_aanhef || ‘,’;

 l_payload  := l_payload || '<br/>';

 l_payload  := l_payload || 'De processen die door u gemeld zijn:';

 l_payload  := l_payload || '<ul>';

 l_tak_found := false;

 --

 for r_tak in

 ( select tak.code tak_code

   ,      tak.omschrijving tak_omschrijving

   from   bubs_processen tak

   where  tak.gbr_id_melder = l_parameters_map('GBR_ID')

   order

   by     tak.id desc

 )

 loop

   l_payload := l_payload || '<li>Proces ' || r_tak.tak_code || ': ' || r_tak.tak_omschrijving || '</li>';

 end loop;

 --

 if not l_tak_found

 then

   l_payload := l_payload || '<li>Geen meldingen.</li>';

 end if;

 --

 l_payload  := l_payload || '</ul>';

 --

 -- Merge both.

 --

 l_template := replace(l_template, ':PAYLOAD', l_payload);

 l_template := replace

               ( l_template

               , ':FOOTER'

               , 'Deze e-mail is verzonden op '

                 || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS')

                 || ' aan '

                 || l_parameters_map('GBR_EMAIL_ADRES')

                 || '. Klik <a href="http://site/afmelden?GBR_ID='

                 || to_char(l_parameters_map('GBR_ID'))

                 || '">hier</a> om af te melden.'

               );

 itgen_isp.print_clob(l_template);

end;

%>

<!-- Aangemaakt met Invantive Estate -->

Send Newsletter

After this one time activities it is easy to send the newsletter with for example:

begin

 --

 -- Log on to Invantive Estate

 --

 bubs_session.set_session_info

 ( 'toad.sql'

 , 'install'

 , 'system'

 , 'Newsletter'

 , coalesce(sys_context('userenv', 'ip_address'), '?')

 , sys_context('userenv', 'host')

 , 'n/a'

 , 'TOAD ' || to_char(sysdate, 'YYYYMMDDHH24MISS')

 );

end;

/

insert into bubs_berichten_v

( brt_afgehandeld_vlag

, brt_afzender

, brt_afzender_naam

, brt_boodschap_l

, brt_mime_type

, brt_onderwerp

, brt_ontvanger

, brt_ontvanger_naam

, brt_ref_tabel

, brt_ref_sleutel

, brt_vertalen_vlag

)

select 'N'

,      'info@acme.com'

,      'ACME'

,      itgen_isp.run

      ( bubs#profiel_opties.get_value('xxacme-html-nieuwsbrief-isp')

      , 'GBR_ID=' || itgen_url.escape_cached(to_char(gbr.gbr_id), 'Y')

        || '&' || 'GBR_NAAM=' || itgen_url.escape_cached(gbr.gbr_naam, 'Y')

        || '&' || 'GBR_EMAIL_ADRES=' || itgen_url.escape_cached(gbr.gbr_email_adres, 'Y')

      )

,     'text/html'

,     'ACME nieuwsbrief november 2009'

,     gbr.gbr_emil_adres

,     gbr.gbr_naam

,     'GBR'

,     gbr.gbr_id

,     'Y'

from  bubs_gebruikers_v gbr

where 1=1

and   gbr.gbr_email_adres is not null

/

commit

/