/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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
    := 'XxasPerfFyiNotificationXsl.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 := q'[<?xml version="1.0" encoding="UTF-8"?>
<!-- DWXMLSource="xml-for-fyi-performance.xml" -->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html" encoding="utf-8" doctype-public="-//W3C//DTD XHTML 1.0 Transitional//EN" doctype-system="http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd" />
<xsl:template match="/">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body style="background:#cccccc; font-family:'Open Sans', Segoe, 'Segoe UI', Helvetica, Arial, sans-serif">
<table cellspacing="0" cellpadding="0" style="background:#FFFFFF; width:90%; max-width:660px; margin-left:auto; margin-right:auto; padding-bottom:16px;">
<tr>
<td valign="top" align="center">
<xsl:attribute name="style">
<xsl:value-of select="Notification/Header/Style"/>
</xsl:attribute>
<img border="0" style="max-height:50px; padding:16px;">
    <xsl:attribute name="src">
        <xsl:value-of select="Notification/Header/ImageSource"/>
    </xsl:attribute>
</img>
</td>
</tr>
<tr>
<td valign="top" style="padding:16px;">
<xsl:copy-of select="Notification/Header/MessageHTML/PreLinkText"/>
<a>
<xsl:attribute name="href">
<xsl:value-of select="Notification/Header/PerfUrl" />
</xsl:attribute>
<xsl:copy-of select="Notification/Header/MessageHTML/LinkText"/>
</a>
<xsl:copy-of select="Notification/Header/MessageHTML/PostLinkText"/>
</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;
