asked on
attaching banner in a message format
Here v_message_body tht is given i have to attach in the beginning of a message body of a sample email template if its for a non prod db. the message format of the sample email template is given in html format below. so i have to attach the banner in v_message_body to the message body of an email template whose format is given below
what will be the code for it and im calling this proc in a proc tht generates the email, if the question is not clear i can explain further but please help me with the code
PROCEDURE non_prod_instance_pr (
i_instance_name IN VARCHAR2,
i_email_cd IN CQ_MESSAGE_TYPE.MESSAGE_TY
io_message_to IN OUT VARCHAR2,
io_message_cc IN OUT VARCHAR2,
io_message_bcc IN OUT VARCHAR2,
io_message_body IN OUT CLOB,
io_message_text IN OUT CQ_MESSAGE_TYPE.MESSAGE_FO
)
IS
v_message_body CLOB;
v_email_cd VARCHAR2(100);
BEGIN
v_message_body :=
'
<br> <br>
==============THE FOLLOWING SECTION WILL NOT APPEAR ON PRODUCTION================
<br>
DB = '
|| i_instance_name
|| '<br> TO = '
|| io_message_to
|| '<br> CC = '
|| io_message_cc
|| '<br> BCC = '
|| io_message_bcc
|| '<br>
==========================
';
-- io_message_body := REG_EXPREPLACE(io_message_
io_message_body :=
REGEXP_REPLACE (io_message_body,
'(^|\W)' || LOWER ('<BODY>') || '(\W|$)',
'\1' || LOWER ('<BODY>' || v_message_body) || '\2',
1,
0,
'i'
);
BEGIN
SELECT VALUE
INTO io_message_to
FROM cq_application_context
WHERE UPPER (NAME) = 'PDR_QA_EMAIL';
EXCEPTION
WHEN OTHERS
THEN
io_message_to := 'quoting-qa@cisco.com';
END;
io_message_cc := 'quoting-qa@cisco.com, quoting-dev@cisco.com';
io_message_bcc := '';
EXCEPTION
WHEN OTHERS
THEN
NULL;
END non_prod_instance_pr;
-- Added by sanjeev ends
END cq_notification_pkg;
/
Message format
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>New Page 2</title>
<style type="text/css">
.style1 {
font-family: Tahoma;
font-size: x-small;
}
.style2 {
font-family: Tahoma;
font-weight: bold;
font-size: x-small;
}
</style>
</head>
<body>
<div>
<div>
<span class="style1">|PROMOTION_
for |END_CUSTOMER_COMPANY_NAME
APPROVED.</span></div>
<div class="style1">
</div>
<div class="style1">
|REBATE_VERBIAGE|</div>
<div class="style1">
</div>
<div class="style1">
You are APPROVED to place your DISTRIBUTION order using the Distribution
Authorization ID(DART) below. The Distributor/s you selected
have received notification of the price Deviation approval. Please
proceed to place your order with</div>
<div class="style1">
</div>
<table height="36" cellspacing="0" cellpadding="0" width="67%" border="1">
<tr>
<td width="50%">
<div align="center" class="style2">
Distributor Name</div>
</td>
<td width="50%">
<div align="center" class="style2">
Distribution Authorization ID (DART)</div>
</td>
</tr>
<tr>
<td width="50%">
<div class="style1">
|DISTRIBUTOR_NAME|</div>
</td>
<td width="50%">
<div class="style1">
|DEVIATION_ID|</div>
</td>
</tr>
</table>
<div class="style1">
</div>
<div align="center">
<table cellspacing="0" cellpadding="0" width="100%" border="0">
<tr>
<td width="30%">
<div class="style1">
Deal Expiration Date</div>
</td>
<td width="70%">
<div class="style1">
: |FIRST_EXPIRATION_DATE|</d
</td>
</tr>
<tr>
<td width="30%">
<font size="2">|LIST_PRICE|</fon
<td width="70%">
<font size="2"> |LIST_PRICE_1|</font></td>
</tr>
<tr width="100%">
<td width="30%">
<div class="style1">
TMP Quote No</div>
</td>
<td width="70%">
<div class="style1">
: |TMP_QUOTE_NO|</div>
</td>
</tr>
</table>
</div>
</div>
<div>
</div>
<div>
Note: The TMP Quote # will only be visible for CCW Deals with Trade-In credits and is only applicable to participating countries/theaters.
</div>
<p class="style1"><strong>If you are a CAM, no action is required at this time;
this email is strictly a notification.</strong></p>
<p class="style1">Please do not respond to this email.
<BR><BR>For corporate legal information please click here: <A HREF="|CORPORATE_LEGAL_INF
</p>
</body>
</html>
Oracle Database
Last Comment
slightwv (䄆 Netminder)
ASKER
thts one more way of doing it which i wasnt getting i mean i can do this way right wht can be the code should we add the code here in this proc or the proc where we r generating email
will the code be something like replace ( v_message_body || mail_text) i would like to know how it can be done
will the code be something like replace ( v_message_body || mail_text) i would like to know how it can be done
I assume you are still looking to INSERT your banner into the existing message. You need to have some way to figure out where to insert it. Either with a placeholder and replace or possible using XML to insert a new node after or before some other node. That is is the existing message and your new replacement text are XML compliant.
If you think the REPLACE method is hard, using the XML DOM will be a nightmare for you.
Stick to the replace method.
If you think the REPLACE method is hard, using the XML DOM will be a nightmare for you.
Stick to the replace method.
ASKER
u mean the placeholder and replace method is more easy
In my opinion: Its easier than learning XML and the DOM or UPDATEXML SQL function if you've never used XML.
For example: is not valid in XML...
For example: is not valid in XML...
ASKER
can we use the code something like this in this proc or the proc where we are generating the email
if v_db_name<>'DMPROD' then
v_email_text:= v_message_body || i_email_text
else
v_email_text:=i_email_text
where v_email_text is the message body which will be inserted into the table
i_email_txt will be the original message body of the email template
v_message_body is the banner
if v_db_name<>'DMPROD' then
v_email_text:= v_message_body || i_email_text
else
v_email_text:=i_email_text
where v_email_text is the message body which will be inserted into the table
i_email_txt will be the original message body of the email template
v_message_body is the banner
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
What changed?