XML Publisher

To generate output in excel using XML
=====================================

<?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" 
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ora="http://www.oracle.com/XSL/Transform/java/" 
xmlns:xdofo="http://xmlns.oracle.com/oxp/fo/extensions" 

xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions" 

xmlns:xdoxliff="urn:oasis:names:tc:xliff:document:1.1" xmlns:xlink="http://www.w3.org/1999/xlink">
  <xsl:param name="_XDOCALENDAR">GREGORIAN</xsl:param>
  <xsl:param name="_XDOLOCALE">en-US</xsl:param>
  <xsl:param name="_XDOTIMEZONE">GMT</xsl:param>
  <xsl:param name="_XDODFOVERRIDE">;</xsl:param>
  <xsl:param name="_XDOCURMASKS">;</xsl:param>
  <xsl:param name="_XDOCHARTTYPE">image/svg+xml</xsl:param>
  <xsl:param name="_XDOOUTPUTFORMAT">application/pdf</xsl:param>
  <xsl:param name="_XDOSVGFONTEMBED">true</xsl:param>
  <xsl:param name="_XDOCTX">#</xsl:param>
  <xsl:variable name="_XDOXSLTCTX" select="xdoxslt:set_xslt_locale($_XDOCTX, $_XDOLOCALE, $_XDOTIMEZONE, 

$_XDOCALENDAR, concat($_XDODFOVERRIDE,'
',$_XDOCURMASKS))"/>
  <xsl:variable name="_XDOFOPOS" select="''"/>
  <xsl:variable name="_XDOFOPOS2" select="number(1)"/>
  <xsl:variable name="_XDOFOTOTAL" select="number(1)"/>
  <xsl:variable name="_XDOFOOSTOTAL" select="number(0)"/>
  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">
      <xsl:text>progid="Excel.Sheet"</xsl:text>
    </xsl:processing-instruction>
 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:o="urn:schemas-microsoft-com:office:office"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
   <Author>indi-skothandan</Author>
   <LastAuthor>indi-skothandan</LastAuthor>
   <Created>2008-08-04T07:11:34Z</Created>
   <Version>11.9999</Version>
  </DocumentProperties>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
   <WindowHeight>12405</WindowHeight>
   <WindowWidth>19020</WindowWidth>
   <WindowTopX>120</WindowTopX>
   <WindowTopY>45</WindowTopY>
   <ProtectStructure>False</ProtectStructure>
   <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
   <Style ss:ID="Default" ss:Name="Normal">
    <Alignment ss:Vertical="Bottom"/>
    <Borders/>
    <Font/>
    <Interior/>
    <NumberFormat/>
    <Protection/>
   </Style>
   <Style ss:ID="s21">
    <NumberFormat/>
   </Style>
   <Style ss:ID="s22">
    <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
    <NumberFormat ss:Format="Fixed"/>
   </Style>
   <Style ss:ID="s24">
    <Font x:Family="Swiss" ss:Size="12"/>
    <NumberFormat/>
   </Style>
   <Style ss:ID="s25">
    <Font x:Family="Swiss" ss:Size="12" ss:Bold="1"/>
    <NumberFormat/>
   </Style>
   <Style ss:ID="s26">
    <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
    <Font x:Family="Swiss" ss:Size="12"/>
    <NumberFormat ss:Format="Fixed"/>
   </Style>
   <Style ss:ID="s27">
    <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
    <Font x:Family="Swiss" ss:Bold="1"/>
    <NumberFormat/>
   </Style>
   <Style ss:ID="s28">
    <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
    <Font x:Family="Swiss" ss:Bold="1"/>
   </Style>
   <Style ss:ID="s29">
    <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
    <NumberFormat/>
   </Style>
   <Style ss:ID="s30">
    <Font x:Family="Swiss" ss:Bold="1"/>
    <NumberFormat/>
   </Style>
   <Style ss:ID="s31">
    <Alignment ss:Horizontal="Right" ss:Vertical="Bottom"/>
    <Font x:Family="Swiss" ss:Bold="1"/>
    <NumberFormat ss:Format="Fixed"/>
   </Style>
  </Styles>
  <Worksheet ss:Name="Sheet1">
   <Table ss:ExpandedColumnCount="6" x:FullColumns="1"
    x:FullRows="1" ss:StyleID="s21">
     <Row ss:Index="1" ss:StyleID="s30">
     <Cell><Data ss:Type="String">Company</Data></Cell>
     <Cell><Data ss:Type="String">Vendor Number</Data></Cell>
     <Cell><Data ss:Type="String">Vendor Name</Data></Cell>
     <Cell><Data ss:Type="String">Amount Paid</Data></Cell>
   </Row>
    <xsl:for-each select=".//G_COMPANY">
     <Row>
      <Cell><Data ss:Type="String">     <xsl:value-of select=".//COMPANY"/></Data></Cell>
      <Cell><Data ss:Type="String">     <xsl:value-of select=".//VENDOR_NUMBER"/></Data></Cell>
      <Cell><Data ss:Type="String">     <xsl:value-of select=".//VENDOR_NAME"/></Data></Cell>
      <Cell><Data ss:Type="String">     <xsl:value-of select=".//AMT_PAID"/></Data></Cell>
         </Row>
    </xsl:for-each>
   </Table>
   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <Print>
     <ValidPrinterInfo/>
     <HorizontalResolution>600</HorizontalResolution>
     <VerticalResolution>600</VerticalResolution>
    </Print>
    <Selected/>
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
   </WorksheetOptions>
  </Worksheet>
  <Worksheet ss:Name="Sheet2">
   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
   </WorksheetOptions>
  </Worksheet>
  <Worksheet ss:Name="Sheet3">
   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
    <ProtectObjects>False</ProtectObjects>
    <ProtectScenarios>False</ProtectScenarios>
   </WorksheetOptions>
  </Worksheet>
 </Workbook>
  </xsl:template>
</xsl:stylesheet>

XML Publisher Tags Examples
===========================

<?trx_date?> 

Places the information in the output from the trx_date element.

<?for-each:g_invoice?>
  <?trx_date?>
<?end for-each?>

For each occurrance of g_invoice the trx date will be placed in the output.

<?split-by-page-break:?>

For page breaks

Use the following solution for generating AR Statements in XML
==============================================================

Source : http://garethroberts.blogspot.com/2008/01/beautiful-statements-in-1-easy-step.html

Create a package:

create or replace package XXV8_XMLP_PKG AUTHID CURRENT_USER AS
  function submit_request_xmlp
  ( p_code in varchar2
  , p_request_id in number
  ) return number;
end XXV8_XMLP_PKG;
/
create or replace package body XXV8_XMLP_PKG AS
function submit_request_xmlp
( p_code in varchar2
, p_request_id in number
) return number
is
  l_req_id number := 0;
begin
  if p_code = 'ARXSGP' then
    l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB',NULL,NULL,FALSE,
                                           p_request_id,
                                           222, -- Receivables
                                           'ARXSGP', -- Statement Generate
                                           'en-US', -- English
                                           'N','RTF','PDF');
  end if;
  return l_req_id;
end submit_request_xmlp;

end XXV8_XMLP_PKG;
/

Add the following to the after report trigger in ARXSGPO.rdf:

 
  declare
    v_req_id number := 0;
  begin
    v_req_id := xxv8_xmlp_pkg.submit_request_xmlp('ARXSGP',:p_conc_request_id);
    if v_req_id > 0 then
      srw.message(20002, 'Submitted request_id ' || v_req_id);
      commit;
    else
      srw.message(20002, 'Failed to submit request');
    end if;
  end;

"No Corresponding Lob Data Found" Error From XDOREPPB
=====================================================

Issue
-----

Getting ''No Corresponding Lob Data Found'' Error From XDOREPPB 'XML Report Publisher' 

ERROR
XML Report Publisher 5.0
Updating request description
Waiting for XML request
Retrieving XML request information
Preparing parameters
Process template
--XDOException
java.sql.SQLException: No corresponding LOB data found :SELECT L.FILE_DATA
FILE_DATA, DBMS_LOB.GETLENGTH(L.FILE_DATA) FILE_LENGTH, L.LANGUAGE LANGUAGE,
L.TERRITORY TERRITORY, B.DEFAULT_LANGUAGE DEFAULT_LANGUAGE, B.DEFAULT_TERRITORY
DEFAULT_TERRITORY,B.TEMPLATE_TYPE_CODE TEMPLATE_TYPE_CODE, B.USE_ALIAS_TABLE
USE_ALIAS_TABLE, B.START_DATE START_DATE, B.END_DATE END_DATE,
B.TEMPLATE_STATUS TEMPLATE_STATUS, B.USE_ALIAS_TABLE USE_ALIAS_TABLE,
B.DS_APP_SHORT_NAME DS_APP_SHORT_NAME, B.DATA_SOURCE_CODE DATA_SOURCE_CODE,
L.LOB_TYPE LOB_TYPE FROM XDO_LOBS L, XDO_TEMPLATES_B B WHERE (L.LOB_TYPE =
'TEMPLATE' OR L.LOB_TYPE = 'MLS_TEMPLATE') AND L.APPLICATION_SHORT_NAME= :1 AND
L.LOB_CODE = :2 AND L.APPLICATION_SHORT_NAME = B.APPLICATION_SHORT_NAME AND
L.LOB_CODE = B.TEMPLATE_CODE AND ( (L.LANGUAGE = :3 AND L.TERRITORY = :4 ) OR
(L.LANGUAGE = :5 AND L.TERRITORY = :6) OR (L.LANGUAGE= B.DEFAULT_LANGUAGE AND
L.TERRITORY= B.DEFAULT_TERRITORY ) )
at
oracle.apps.xdo.oa.schema.server.TemplateInputStream.initStream(TemplateInputStream.java:357)
at
oracle.apps.xdo.oa.schema.server.TemplateInputStream.(TemplateInputStream.java:193)
at
oracle.apps.xdo.oa.schema.server.TemplateHelper.getTemplateFile(TemplateHelper.java:1223)
at
oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3547)
at
oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3614)
at
oracle.apps.xdo.oa.cp.JCP4XMLPublisher.runProgram(JCP4XMLPublisher.java:814)
at oracle.apps.fnd.cp.request.Run.main(Run.java:161)


Solution
--------
The parameters to call XDOREPPB need to be changed depending upon the version
of XML Publisher:

XML Report Publisher Parameters: 

Version 5.6.3 / R12 
xdojcp.ldt 115.31, JCP4XMLPublisher.java 115.36 


Column Seq. Parameter SRW_PARM (Token) 
10  Request     ReportRequestID 
15 Template Application TemplateApplication(Application ID) 
20 Template ReportTemplate 
40 Template Locale TemplateLocale 
50 Debug Flag DebugFlag 
60 Template Type ReportTemplateType 
70 Output Format OutputFormat 




Version 5.6.1 
xdojcp.ldt 115.28, JCP4XMLPublisher.java 115.34 

Column Seq. Parameter SRW_PARM (Token) 
10 Request ReportRequestID 
15 Template Application TemplateApplication (Application Short Name) 
20 Template ReportTemplate 
40 Template Locale TemplateLocale 
50 Debug Flag DebugFlag 
60 Template Type ReportTemplateType 
70 Output Format OutputFormat 



Version 5.6.0: 
xdojcp.ldt 115.25, JCP4XMLPublisher.java 115.33 

Column Seq. Parameter SRW_PARM (Token) 
10 Request ReportRequestID 
20 Template ReportTemplate 
30 Report Application ID ReportApplicationID(Application ID) 
40 Template Locale TemplateLocale 
50 Debug Flag DebugFlag 
60 Template Type ReportTemplateType 
70 Output Format OutputFormat 

Version 5.5.0:
xdojcp.ldt 115.17, JCP4XMLPublisher.java 115.32 

Column Seq. Parameter SRW_PARM (Token) 
10 Request ReportRequestID 
20 Template ReportTemplate 
30 Report Application ID ReportApplicationID(Application ID) 
40 Template Locale TemplateLocale 
50 Debug Flag DebugFlag 
60 Template Type ReportTemplateType 
70 Output Format OutputFormat 

Version 5.0.0:
xdojcp.ldt 115.11, JCP4XMLPublisher.java 115.28 

Column Seq. Parameter SRW_PARM (Token) 
10 Request ReportRequestID 
20 Template ReportTemplate 
30 Report Application ID ReportApplicationID(Application ID) 
40 Template Locale TemplateLocale 
50 Debug Flag DebugFlag 
60 Template Type ReportTemplateType 
70 Output Format OutputFormat