We help IT Professionals succeed at work.

attaching banner in a message format

thota198
thota198 asked
on
359 Views
Last Modified: 2012-05-11

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>
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I thought you had a placeholder like V_BANNER that you were going to use for the replace command.

What changed?

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
u mean the placeholder and replace method is more easy
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
In my opinion:  Its easier than learning XML and the DOM or UPDATEXML SQL function if you've never used XML.

For example:  &nbsp; is not valid in XML...

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.