/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  l_XSL_NAME CONSTANT fnd_lobs.file_name%TYPE
    := 'XxasNoteNotificationXsl.xsl';
  l_clob              CLOB;
  l_blob              BLOB;
  l_dest_offsset      INTEGER := 1;
  l_src_offsset       INTEGER := 1;
  l_lang_context      INTEGER := dbms_lob.default_lang_ctx;
  l_warning           INTEGER;

BEGIN

  l_clob := '<?xml version="1.0" encoding="UTF-8"?><!-- DWXMLSource="notes-source.xml" -->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:template match="/">
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    </head>
    <body style="margin:0; padding:0; background-color:#F4F4F4; font-family:Arial, Helvetica, sans-serif;">  
    
    <!--Outermost Table-->
    <table border="0" cellpadding="0" cellspacing="0" style="font-family:Arial, Helvetica, sans-serif;width: 100%; background-color:#F4F4F4;">
      <tr>
        <td><!--Main Container Table-->
          <table width="604" align="center" style=" margin-top:20px;background-color:#FFFFFF; border:1px solid #e2e2e2; -moz-box-shadow:0 0 3px rgba(0,0,0,0.10);-webkit-box-shadow:0 0 3px rgba(0,0,0,0.10); border-radius: 5px;-moz-border-radius:5px; -webkit-border-radius: 5px;" border="0" cellpadding="0" cellspacing="0">
            <!--Customer Logo and Name Table-->
            <tr>
              <td width="100%" style="padding-left:15px; padding-right:15px; padding-top:15px; padding-bottom:0px; margin:0;"><table width="100%%" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td>
                      <img alt="Logo">
                        <xsl:attribute name="src"> <xsl:value-of select="Notification/Note/CustomerLogoUrl" /> </xsl:attribute>
                      </img>
                    </td>
                    <td><xsl:value-of select="Notification/Note/CustomerStrap" /></td>
                  </tr>
                </table></td>
            </tr>
            
            <!--Main Bulk of Email-->
            <tr>
              <td style="padding:15px ;margin:0;"><table width="100%%" border="0" cellspacing="0" cellpadding="0" style="margin:0px; padding:0px;">
                  <tr>
                    <td><h2 style="color:#222; font-family:Arial, Helvetica, sans-serif; font-size:16px;padding:0px 0 0px 0;margin:0;font-weight:bold ;margin-top:10px">Dear <xsl:value-of select="Notification/Note/ReminderFirstName"/></h2>
                      <p style="font-family:Arial, Helvetica, sans-serif; font-size:12px; color:#333333;">
                        <!--NOTE START-->On <strong><xsl:value-of select="Notification/Note/ReminderCreationDate"/></strong>, you set a reminder on a Note entered for <strong><xsl:value-of select="Notification/Note/PersonName"/></strong>.</p>
<p style="font-family:Arial, Helvetica, sans-serif; font-size:12px; color:#333333;">Please review this note below.  If further action or update is needed, you may amend the text in the Note or change the Reminder Date through logging into <xsl:value-of select="Notification/Note/SystemName"/>.</p>
                      <table cellspacing="0" cellpadding="0" hspace="0" vspace="0" width="571" style="border:1px solid #e2e2e2; -moz-box-shadow:0 0 3px rgba(0,0,0,0.10);-webkit-box-shadow:0 0 3px rgba(0,0,0,0.10); border-radius: 5px;-moz-border-radius:5px; -webkit-border-radius: 5px;">
                        <tr>
                          <td>
                          <img width="571" height="14">
                          <xsl:attribute name="src"> <xsl:value-of select="Notification/Note/NoteTopImgUrl" /> </xsl:attribute>
                          </img>
                          </td>
                        </tr>
                        <tr>
                          <td style="background-color:#FAFAFA; padding-left:10px; padding-right:10px; padding-top:0px; padding-bottom:0px;"><p style="font-family:Arial, Helvetica, sans-serif; text-align:left; font-size:12px; font-weight:normal; color:#000000;">
                          <strong><xsl:value-of select="Notification/Note/NoteCreationDate"/></strong>  |  <strong><xsl:value-of select="Notification/Note/NoteType"/></strong> | <strong><xsl:value-of select="Notification/Note/AuthorName"/></strong> | Reminder: <strong><xsl:value-of select="Notification/Note/ReminderDate"/></strong> </p></td>
                        </tr>
                        <tr>
                          <td style="background-color:#FAFAFA; padding-left:10px; padding-right:10px; padding-top:0px; padding-bottom:0px; font-family:Arial, Helvetica, sans-serif;font-style:normal; text-align:left; font-size:15px; line-height:21px; color:#666666;"><xsl:value-of select="Notification/Note/Content"/></td>
                        </tr>
                        <tr>
                          <td> <img width="571" height="15">
                          <xsl:attribute name="src"> <xsl:value-of select="Notification/Note/NoteBottomImgUrl" /> </xsl:attribute>
                          </img></td>
                        </tr>
                      </table>
                      <p style="font-family:Arial, Helvetica, sans-serif; font-size:11px; color:#333333; ">Thanks for using <xsl:value-of select="Notification/Note/SystemName"/>.</p>
                      <!--NOTE END--> 
                    </td>
                  </tr>
                </table></td>
            </tr>
            
            <!--Footer-->
            <tr style="background:#0b172f; color:#FFFFFF;">
              <td style="padding:5px ;margin:0;"><table width="100%%" border="0" cellspacing="0" cellpadding="0">
                  <tr>
                    <td style="text-align:center;"><a style="color:#0099FF; font-family:Arial, Helvetica, sans-serif; text-align:center;">
                      <xsl:attribute name="href"> <xsl:value-of select="Notification/Note/NotesUrl" /> </xsl:attribute>
                      Access <xsl:value-of select="Notification/Note/SystemName"/></a></td>
                  </tr>
                </table></td>
            </tr>
          </table></td>
      </tr> 
    </table>
    </body>
    </html>
  </xsl:template>
</xsl:stylesheet>';


  dbms_lob.createtemporary(l_blob,TRUE);

  dbms_lob.convertToBlob(dest_lob       => l_blob
                        ,src_clob       => l_clob
                        ,amount         => dbms_lob.lobmaxsize
                        ,dest_offset    => l_dest_offsset
                        ,src_offset     => l_src_offsset
                        ,blob_csid      => dbms_lob.default_csid
                        ,lang_context   => l_lang_context
                        ,warning        => l_warning);

  INSERT INTO fnd_lobs
        (file_id
        ,file_name
        ,file_content_type
        ,file_data
        ,upload_date
        ,expiration_date
        ,program_name
        ,program_tag
        ,language
        ,oracle_charset
        ,file_format)
  VALUES(fnd_lobs_s.nextval
        ,l_XSL_NAME
        ,'text/xml'
        ,l_blob
        ,null
        ,null
        ,''
        ,''
        ,'US'
        ,'UTF8'
        ,'text');

  dbms_lob.freetemporary(l_blob);

END;
/
COMMIT;
EXIT;
