Hello
I am trying to send out an email that includes specific information for each individual user that is to be emailed. Problem I am having is that it sends out the emails to each person listed but keeps all the all the specific detail that is in the first record in the data list for all the emails that go out. Each person that is emailed should have their on specific detailed information listed.
Thank in advanced.
declare
l_body clob := q'{
Hello %user,
This notification, is to let you know that you have been assinged Gate Duty for %sport by your Athletic Director.
Game Date: %date
Game Time: %time
Location/School: %loc
You will receive reminders as this date gets closer.
Thank You!
***Please DO NOT REPLY to this Message***
}';
begin
for c1 in (
SELECT A.* FROM (
SELECT distinct NAME, GAME_DATE, LOCATION, EMAIL, TIME, SPORT
,(GAME_DATE - TRUNC(SYSDATE)) chk
FROM ATHLETICS_GATE_DUTY
) A
WHERE round(A.CHK,0) in (0,1)
)
loop
if c1.EMAIL is not null then
l_body := replace(l_body, '%user', c1.NAME);
l_body := replace(l_body, '%date', c1.GAME_DATE);
l_body := replace(l_body, '%time', c1.TIME);
l_body := replace(l_body, '%loc', c1.LOCATION);
l_body := replace(l_body, '%sport', c1.SPORT);
htmldb_mail.send(
p_to => c1.EMAIL,
p_from => 'ApexApps@rock.k12.nc.us',
p_body => l_body,
p_subj => 'Gate Duty Reminder');
end if;
end loop;
end;