/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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
    := 'XxasApplicantNotificationXsl.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="irec_output_MANAGER_ADHOC.xml" -->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html" media-type="application/html" omit-xml-declaration="yes" 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="margin:0;padding:0; background-color:#F4F4F4; font-family:Arial, Helvetica, sans-serif;">
<xsl:variable name="image-base-path">
<xsl:value-of select="Notification/Header/ImageBasePath" />
</xsl:variable>
<xsl:variable name="image-focus-triangle">
<xsl:value-of select="$image-base-path" />focus-triangle.png</xsl:variable>
<!--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="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 style="padding:15px ;margin:0;">
<table width="100%" border="0" cellspacing="0" cellpadding="0" style="table-layout:fixed">
<tr>
<td width="25%">
<img alt="Logo" style="max-width:80px; max-height:160px;">
<xsl:attribute name="src">
<xsl:value-of select="Notification/Header/CustomerLogoUrl" />
</xsl:attribute>
</img>
</td>
<td style="padding-left:20px;">
<span style="font-size:28px;">
<xsl:if test="Notification/Header/RecipientIsApplicant = 'true'">
<xsl:value-of select="Notification/Header/AboutPersonFirstName" />,
</xsl:if>
<xsl:value-of select="Notification/Header/H1Title" />
</span>
</td>
</tr>
</table>
</td>
</tr>
<!--Main Bulk of Email-->
<tr>
<td style="padding:15px; padding-top:0px; margin:0px;">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr valign="top">
<td colspan="2">
<span style="font-size:20px;">
<xsl:value-of select="Notification/Header/H2Title" />
</span>
<br />
<span style="font-size:12px;">
<xsl:value-of select="Notification/Header/H2Subtext" />
</span>
<br />
<p align="right" style="font-size:12px; margin-bottom:20px;">
<xsl:value-of select="Notification/Header/ContextLine1" disable-output-escaping="yes" />
</p>
<br />
</td>
</tr>
<!--START PROCESS TRAIN-->
<xsl:if test="Notification/Header/RecipientIsApplicant = 'true'">
<tr>
<td nowrap="nowrap">
<p style="font-size:15px;">
<xsl:value-of select="Notification/Header/ContextLine2" />
</p>
</td>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0" style="padding:0px;">
<tbody>
<tr>
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:bold">
<xsl:value-of select="Notification/Header/ProcessTrain/StepOneTitle" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:bold">
<xsl:value-of select="Notification/Header/ProcessTrain/StepTwoTitle" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:bold">
<xsl:value-of select="Notification/Header/ProcessTrain/StepThreeTitle" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:bold">
<xsl:value-of select="Notification/Header/ProcessTrain/StepFourTitle" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:bold">
<xsl:value-of select="Notification/Header/ProcessTrain/StepFiveTitle" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:bold">
<xsl:value-of select="Notification/Header/ProcessTrain/StepSixTitle" />
</span>
</td>
</tr>
<tr>
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:normal">
<xsl:value-of select="Notification/Header/ProcessTrain/StepOneSubtext" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:normal">
<xsl:value-of select="Notification/Header/ProcessTrain/StepTwoSubtext" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:normal">
<xsl:value-of select="Notification/Header/ProcessTrain/StepThreeSubtext" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:normal">
<xsl:value-of select="Notification/Header/ProcessTrain/StepFourSubtext" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:normal">
<xsl:value-of select="Notification/Header/ProcessTrain/StepFiveSubtext" />
</span>
</td>
<td align="center" />
<td align="center" nowrap="nowrap">
<span style="font-size:10px; font-weight:normal">
<xsl:value-of select="Notification/Header/ProcessTrain/StepSixSubtext" />
</span>
</td>
</tr>
<tr>
<td align="center" nowrap="nowrap">
<img width="20" height="20" style="max-width:20px; max-height:20px;">
<xsl:attribute name="src"><xsl:value-of select="$image-base-path" /><xsl:value-of select="Notification/Header/ProcessTrain/StepOneStatus" />-dot.png</xsl:attribute>
</img>
</td>
<td align="center">
<hr style=" border:none; border-top: 2px dotted #cccccc; width:30px;" />
</td>
<td align="center" nowrap="nowrap">
<img width="20" height="20" style="max-width:20px; max-height:20px;">
<xsl:attribute name="src"><xsl:value-of select="$image-base-path" /><xsl:value-of select="Notification/Header/ProcessTrain/StepTwoStatus" />-dot.png</xsl:attribute>
</img>
</td>
<td align="center">
<hr style=" border:none; border-top: 2px dotted #cccccc; width:30px;" />
</td>
<td align="center" nowrap="nowrap">
<img width="20" height="20" style="max-width:20px; max-height:20px;">
<xsl:attribute name="src"><xsl:value-of select="$image-base-path" /><xsl:value-of select="Notification/Header/ProcessTrain/StepThreeStatus" />-dot.png</xsl:attribute>
</img>
</td>
<td align="center">
<hr style=" border:none; border-top: 2px dotted #cccccc; width:30px;" />
</td>
<td align="center" nowrap="nowrap">
<img width="20" height="20" style="max-width:20px; max-height:20px;">
<xsl:attribute name="src"><xsl:value-of select="$image-base-path" /><xsl:value-of select="Notification/Header/ProcessTrain/StepFourStatus" />-dot.png</xsl:attribute>
</img>
</td>
<td align="center">
<hr style=" border:none; border-top: 2px dotted #cccccc; width:30px;" />
</td>
<td align="center" nowrap="nowrap">
<img width="20" height="20" style="max-width:20px; max-height:20px;">
<xsl:attribute name="src"><xsl:value-of select="$image-base-path" /><xsl:value-of select="Notification/Header/ProcessTrain/StepFiveStatus" />-dot.png</xsl:attribute>
</img>
</td>
<td align="center">
<hr style=" border:none; border-top: 2px dotted #cccccc; width:30px;" />
</td>
<td align="center" nowrap="nowrap">
<img width="20" height="20" style="max-width:20px; max-height:20px;">
<xsl:attribute name="src"><xsl:value-of select="$image-base-path" /><xsl:value-of select="Notification/Header/ProcessTrain/StepSixStatus" />-dot.png</xsl:attribute>
</img>
</td>
</tr>
<tr valign="bottom">
<td align="center">
<xsl:if test="Notification/Header/ProcessTrain/StepPosition = '1'">
<img src="{$image-focus-triangle}" width="30" height="15" style="display:block"/>
</xsl:if>
</td>
<td align="center" />
<td align="center">
<xsl:if test="Notification/Header/ProcessTrain/StepPosition = '2'">
<img src="{$image-focus-triangle}" width="30" height="15" style="display:block"/>
</xsl:if>
</td>
<td align="center" />
<td align="center">
<xsl:if test="Notification/Header/ProcessTrain/StepPosition = '3'">
<img src="{$image-focus-triangle}" width="30" height="15" style="display:block"/>
</xsl:if>
</td>
<td align="center" />
<td align="center">
<xsl:if test="Notification/Header/ProcessTrain/StepPosition = '4'">
<img src="{$image-focus-triangle}" width="30" height="15" style="display:block"/>
</xsl:if>
</td>
<td align="center" />
<td align="center">
<xsl:if test="Notification/Header/ProcessTrain/StepPosition = '5'">
<img src="{$image-focus-triangle}" width="30" height="15" style="display:block"/>
</xsl:if>
</td>
<td align="center" />
<td align="center">
<xsl:if test="Notification/Header/ProcessTrain/StepPosition = '6'">
<img src="{$image-focus-triangle}" width="30" height="15" style="display:block"/>
</xsl:if>
</td>
</tr>
</tbody>
</table>
</td>
</tr>
</xsl:if>
<!--START INFO BAR-->
<xsl:if test="Notification/Header/cc = 'true'">
<tr style="background-color:#2E9AFE;">
<td style="padding-left:10px;">
<img style="max-width:32px; max-height:32px; width:32px; height:32px; display:block;"><xsl:attribute name="src"><xsl:value-of select="$image-base-path" />info-icon.png</xsl:attribute></img>
</td>
<td style="padding-left:20px;">
<p style="font-size:16px; color:#FFFFFF;">
<xsl:value-of select="Notification/Header/InfoBarLabel" />
</p>
</td>
</tr>
</xsl:if>
<!--END INFO BAR-->
<!--START BODY MESSAGE-->
<tr valign="top">
<td colspan="2">
<xsl:if test="Notification/Body/Text != '' ">
<table width="100%" style="background-color:#EDEDED; margin-top:0px; border:0px none #EDEDED; -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; padding:15px" border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td>
<span style="font-weight:bold; font-size:13px; padding-bottom:15px;">
<xsl:value-of select="Notification/Body/Context" disable-output-escaping="yes" />
</span>
</td>
</tr>
<xsl:if test="Notification/Header/cc = 'false'">
<tr>
<td>
<span style="font-size:18px">
<xsl:value-of select="Notification/Body/Title" disable-output-escaping="yes" />
</span>
</td>
</tr>
</xsl:if>
<tr>
<td>
<p style="font-size:14px">
<xsl:value-of select="Notification/Body/Text" disable-output-escaping="yes" />
</p>
</td>
</tr>
</tbody>
</table>
</xsl:if>
</td>
</tr>
<!--RECIPIENT LIST-->
<tr>
<td colspan="2">
<xsl:if test="count(Notification/Header/Recipients/Recipient) != 0">
<p style="font-size:18px;">
<xsl:value-of select="Notification/Header/RecipientListLabel" />
</p>
<table width="100%" cellpadding="0" cellspacing="0" style="font-family: Arial, Helvetica, sans-serif; background-color: #EDEDED; text-align: left; font-size: 12px; padding: 0px; color:#000000; margin:10px 0px 10px 0px;">
<tbody>
<tr>
<xsl:if test="Notification/Header/RecipientsTable/ColZeroHeading != '' ">
<th align="left" valign="middle" style="background-color:#666666; color:#ffffff; border-style:solid; border:1px; padding:6px">
<xsl:value-of select="Notification/Header/RecipientsTable/ColZeroHeading" />
</th>
</xsl:if>
<th align="left" valign="middle" style="background-color:#666666; color:#ffffff; border-style:solid; border:1px; padding:6px">
<xsl:value-of select="Notification/Header/RecipientsTable/ColOneHeading" />
</th>
<th align="left" valign="middle" style="background-color:#666666; color:#ffffff; border-style:solid; border:1px; padding:6px">
<xsl:value-of select="Notification/Header/RecipientsTable/ColTwoHeading" />
</th>
</tr>
<xsl:for-each select="Notification/Header/Recipients/Recipient">
<tr>
<xsl:if test="Notification/Header/RecipientsTable/ColZeroHeading != '' ">
<td align="left" valign="middle" style="background-color:#FFFFFF; border-bottom:2px solid #EDEDED; padding:6px">
<img width="20" height="20" style="max-width:20px; max-height:20px;">
<xsl:attribute name="src">
<xsl:value-of select="$image-base-path" />
<xsl:value-of select="Status" />-dot.png
</xsl:attribute>
</img>
</td>
</xsl:if>
<td align="left" valign="middle" style="background-color:#FFFFFF; border-bottom:2px solid #EDEDED; padding:6px">
<xsl:value-of select="Name" />
</td>
<td align="left" valign="middle" style="background-color:#FFFFFF; border-bottom:2px solid #EDEDED; padding:6px">
<a href="">
<xsl:attribute name="href">
mailto:
<xsl:value-of select="Email" />
</xsl:attribute>
<xsl:value-of select="Email" />
</a>
</td>
</tr>
</xsl:for-each>
</tbody>
</table>
</xsl:if>
</td>
</tr>
<tr>
<td colspan="2">
<hr style="border:none; width:100%; height:20px; filter: alpha(opacity=0);" />
<xsl:value-of select="Notification/Footer/Title" disable-output-escaping="yes" />
<br />
<xsl:value-of select="Notification/Footer/Text" disable-output-escaping="yes" />
</td>
</tr>
</tbody>
</table>
</td>
</tr>
<!--Footer-->
<tr style="background:#0b172f; color:#FFFFFF;">
<td height="48" style="padding:15px ;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/Header/LoginUrl" />
</xsl:attribute>
<xsl:value-of select="Notification/Header/LoginLabel" />
</a>
</td>
</tr>
<tr>
<td>
<p align="center" style="font-size:10px; color:#FFFFFF;">
<xsl:value-of select="Notification/Footer/Copyright" disable-output-escaping="yes" />
</p>
</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;
