Hi Friends,
I want to send daily alert to user with there daily activity.So i have created schedular to send daily mail to user.
For schedular i have created procedure But i need to display email body result in to table form so i have try html tag to create tablen with daily activity details but when i put this code into procedure then i found error
"Compilation failed,line 51 (11:19:01)
PLS-00222: no function with name 'P' exists in this scopeCompilation failed,line 51 (11:19:01)
PL/SQL: Statement ignored
"
create or replace PROCEDURE "ALERT_ON_PENDING_ACTIVITY4"
IS
CURSOR C2
IS
SELECT DISTINCT CSA.CREATED_BY, UM.USER_NAME uname, UM.EMAIL_ID email_id
FROM CRM_SALES_ACCOUNT_ACTIVITY CSA, USER_MAS UM
WHERE
CSA.UPGRADE_DATE >= TO_DATE (SYSDATE)
AND CSA.CREATED_BY = UM.USER_ID
ORDER BY CREATED_BY;
sub VARCHAR2 (4000);
sub1 VARCHAR2 (4000);
m_msg VARCHAR2 (500);
v_enter VARCHAR2 (10) := CHR (10) || CHR (13);
v_enter1 VARCHAR2 (50) := CHR (10) || CHR (10);
v_enter2 VARCHAR2 (50) := CHR (10) || CHR (10) || CHR (10);
v_body VARCHAR2 (10000);
desc1 VARCHAR2 (10000);
mail VARCHAR2 (500);
cnt NUMBER := 0;
v_case VARCHAR2 (32000);
ATT VARCHAR2(32767);
BEGIN
IF TRIM (TO_CHAR (SYSDATE, 'DAY')) NOT IN ('SATURDAY', 'SUNDAY')
THEN
BEGIN
FOR i IN C2
LOOP
DECLARE
CURSOR C3
IS
SELECT CSA.DEAL_ID,
CSA.ACTIVITY_NAME,
CSA.ACTIVITY_SUMMARY,
CSA.ACTIVITY_DATE,
CSA.UPGRADE_DATE,
CSA.CLOSE_ACTIVITY
FROM CRM_SALES_ACCOUNT_ACTIVITY CSA
WHERE CSA.CLOSE_ACTIVITY = '0'
AND CSA.NOTE_TYPE = 'OPT'
AND CSA.UPGRADE_DATE IS NOT NULL
AND CSA.UPGRADE_DATE >= TO_DATE (SYSDATE)
AND CSA.CREATED_BY = i.CREATED_BY;
BEGIN
v_body:=htp.p('<html> (here when i add v_body varible then i found above error)
<head></head>
<body>
<table width="100%" border="1" >
<tr>
<td>Opportunity ID</td>
<td>Activity Name</td>
<td>Activity Date</td>
<td>Follow up Date</td>
<td>Activity Description</td>
</tr>');
FOR j IN c3
LOOP
CNT := CNT + 1;
htp.p('<tr>
<td>'||j.DEAL_ID||'</td>
<td>'||j.ACTIVITY_NAME||'</td>
<td>'||j.ACTIVITY_DATE||'</td>
<td>'||j.UPGRADE_DATE||'</td>
<td>'||j.ACTIVITY_SUMMARY||'</td>
</tr>');
END LOOP;
END;
apex_mail.send (
p_to => 'testemailk@test.com',
p_from => 'info@test.com',
p_body => v_body,
p_body_html => v_body,
P_subj => 'sub');
apex_mail.push_queue (P_SMTP_HOSTNAME => '10.3.6.12',
P_SMTP_PORTNO => 25);
end loop;
end;
END IF;
htp.p('
</table>
</body>
</html>');
end;
How to resolve above mention error and how to display email body in table form with daily activity ?
Thanks