troubleshooting Question

attaching banner in a message format

Avatar of thota198
thota198 asked on
Oracle Database
7 Comments1 Solution363 ViewsLast Modified:

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_TYPE%type,
      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_FORMAT%type
   )
   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>
    ==========================================================================<br> <br>


    ';
--      io_message_body := REG_EXPREPLACE(io_message_body,'<BODY>','<BODY>'|| v_message_body);
      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_NAME| - |APPLN_NAME| Deal Id: |DEAL_REGISTRATION_ID|
        for |END_CUSTOMER_COMPANY_NAME| / |PARTNER_COMPANY_NAME| is now </span><span class="style2">
        APPROVED.</span></div>
    <div class="style1">
&nbsp;</div>
    <div class="style1">
        |REBATE_VERBIAGE|</div>
    <div class="style1">
&nbsp;</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">
&nbsp;</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">
&nbsp;</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|</div>
                </td>
            </tr>
            <tr>
                <td width="30%">
                <font size="2">|LIST_PRICE|</font></td>
                <td width="70%">
                <font size="2">&nbsp; |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>
&nbsp;</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_INFO_URL|">|CORPORATE_LEGAL_INFO_URL|</A>
</p>
</body>
</html>
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros