Sending XML via FTP using BI Publisher

After finally getting hold of bursting for BI Publisher I started to play around and with the help of  I got it up and running sending PDF templates. I would suggest reading his article if you want a quick rundown of how it all work's. One item I would highlight is that when using dataTigger's inside Data Definition's the heading of the XML is case sensitive: 

<dataTemplate name="XXXMLEXAMPLE" description="XSL-XML Example" Version="1.0" defaultPackage="XXOM_AAABBBCCC_INTERFACE_PKG">

Pay special attention when defining the defaultPackage as I was left scratching my head with the defaultPackage was not being picked up. Also the dataTrigger placement is also in the fine print. beforeReport should be placed before the dataStructure and the afterReport trigger should be placed afterwards. Somehow this managed to catch my eye while reading the documentation.  

Data Definition
Create the data definition or use an existing report to create the data for the report. I would suggest gong the data definition root and this example will use that as the example:

<?xml version="1.0" encoding="utf-8"?>
<dataTemplate name="XXXMLEXAMPLE" description="XSL-XML Example" Version="1.0" defaultPackage="XXOM_AAABBBCCC_INTERFACE_PKG">
<dataQuery>
<sqlStatement name="Q_HEADER">
<![CDATA[
SELECT instance_number
  ,instance_name
  ,host_name
  ,version
  ,startup_time
  ,status
  ,parallel
  ,archiver
  ,log_switch_wait
  ,logins
  ,shutdown_pending
  ,database_status
  ,instance_role
  ,active_state
  ,blocked
FROM   v$instance
]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_HEADER" source="Q_HEADER">
<element name="instance_number" value="instance_number" />
<element name="instance_name" value="instance_name" />
<element name="host_name" value="host_name" />
<element name="version" value="version" />
<element name="startup_time" value="startup_time" />
<element name="status" value="status" />
<element name="parallel" value="parallel" />
<element name="archiver" value="archiver" />
<element name="log_switch_wait" value="log_switch_wait" />
<element name="logins" value="logins" />
<element name="instance_role" value="instance_role" />
<element name="database_status" value="database_status" />
</group>
</dataStructure>
<dataTrigger name="afterReport" source="xxom_aaabbbccc_interface_pkg.afterReport()"/>
</dataTemplate>

XSL-XML

 

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:xdoxslt="http://www.oracle.com/XSL/Transform/java/oracle.apps.xdo.template.rtf.XSLTFunctions">
<xsl:output method="xml" encoding="UTF-8"/>
<xsl:template match="/">
<xsl:element name="DATA">
<xsl:for-each select="/XXXMLEXAMPLE/LIST_G_HEADER/G_HEADER">
<xsl:element name="INSTANCE_NAME">
<xsl:value-of select="INSTANCE_NAME"></xsl:value-of>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>
</xsl:stylesheet>

Package

So this is just a simple example to show you that the hook I am using from BI Publisher is the dataTrigger. Below is the package that will then launch the concurrent that will copy the file to a FTP site, normally a company you are working with that needs to process the data:

 

CREATE OR REPLACE PACKAGE BODY xxom_aaabbbccc_interface_pkg AS
  --------------------------------------------------------------------
  --  customization code: CUST456
  --  name: xxom_aaabbbccc_interface_pkg
  --  create by:  Hilton Meyer
  --  $Revision: 1.0  $
  --  creation date:  15/01/2011
  --------------------------------------------------------------------
  --  ver   date            name        desc
  --  1.0   15/01/2011      Hilton      initial build
  --------------------------------------------------------------------
  module_name CONSTANT VARCHAR2(30) := 'xxom_aaabbbccc_interface_pkg';
  --------------------------------------------------------------------
  --  customization code: CUST456
  --  name: AFTERREPORT
  --  create by:  Hilton Meyer
  --  $Revision: 1.0  $
  --  creation date:  06/10/2011
  --------------------------------------------------------------------
  --------------------------------------------------------------------
  --  process : Run's Email's Rejection Notice from Concurrent
  --------------------------------------------------------------------
  --  ver   date            name        desc
  --  1.0   06/10/2011      Hilton      initial build
  --------------------------------------------------------------------
  FUNCTION afterreport RETURN BOOLEAN IS
    action_name            CONSTANT VARCHAR2(30) := 'AFTERREPORT';
    c_concurrent_shortname CONSTANT VARCHAR2(30) := 'XXUTILFTP';
    l_req_id    NUMBER := 0;
    l_directory VARCHAR2(255);
    l_file_name VARCHAR2(255);
  BEGIN
    xxtpi_utils.log(module_name, action_name, 'Running FTP Program');
    SELECT (SELECT VALUE
            FROM   fnd_env_context
            WHERE  variable_name = 'APPLCSF'
                   AND concurrent_process_id =
                   (SELECT MAX(concurrent_process_id)
                        FROM   fnd_env_context)) || '/' ||
           (SELECT VALUE
            FROM   fnd_env_context
            WHERE  variable_name = 'APPLOUT'
                   AND concurrent_process_id =
                   (SELECT MAX(concurrent_process_id)
                        FROM   fnd_env_context))
          ,'o' || fnd_global.conc_request_id || '.out'
    INTO   l_directory
          ,l_file_name
    FROM   dual;
    xxtpi_utils.log(module_name, action_name, 'l_directory:' || l_directory);
    xxtpi_utils.log(module_name, action_name, 'l_file_name:' || l_file_name);
    l_req_id := fnd_request.submit_request(application => 'XX'
                                          ,program     => c_concurrent_shortname
                                          ,description => NULL
                                          ,start_time  => NULL
                                          ,sub_request => FALSE
                                          ,argument1   => fnd_global.org_id --P_ORG_ID
                                          ,argument2   => fnd_global.conc_request_id --P_TRX_ID
                                          ,argument3   => 'ftp.aaabbbccc.com' --P_SERVER
                                          ,argument4   => 'user' --P_USER
                                          ,argument5   => 'psswrd' --P_PASSWORD
                                          ,argument6   => l_directory --P_FOLDER
                                          ,argument7   => l_file_name --P_FILENAME
                                          ,argument8   => 'in/messages/'
                                          ,argument9   => to_char(SYSDATE
                                                                 ,'yyyymmddhhmi') ||
                                                          '.xml');
 
    IF l_req_id = 0
    THEN
      RETURN FALSE;
    ELSE
      COMMIT;
      RETURN TRUE;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      xxtpi_utils.log(module_name, action_name, SQLERRM);
      RETURN FALSE;
  END;
END xxom_aaabbbccc_interface_pkg;

Host File 

 

From this package you can see I take the XML I had created via the output, $APPLCSF/$APPLOUT, these are EBS environment variables that should be set on the server. using the concurrentID I build the file name and then run the concurrent that will copy it to the FTP server. This is a HOST file and is posted below:

 

# +====================================================================+
# | $Revision: 2.0    $                                                  |
# +====================================================================+
# | Customization Code: CUST466                                           |
# | name              : XXUTILFFTP                                    |
# | create by         : Hilton Meyer                                   |
# | Creation Date     : 21/04/2010                                       |
# +====================================================================+
# | Process           : put a file with FTP                            |
# | Retrun Status     : 1 - failed building process                    |
# |                   : 0 - Success                                    |
# | Depend On         :                                                |
# +====================================================================+
# | to register please do :                                               |  
# |                ln -s $FND_TOP/bin/fndcpesr XXUTILFFTP              |
# +====================================================================+
# | Parameters                                                         |
# |                P_SERVER    -     IP Address                           |
# |                P_USER      -     User                                |
# |                P_PASSWORD  -     Password                           |
# |                P_FOLDER    -     Folder                               |
# |                P_FILENAME  -     File Name                           |
# +====================================================================+
# | Change History    :                                                |
# |                                                                    |
# | Vers      Author               Date         Description            |
# +--------------------------------------------------------------------+
# | 1.0       Hilton               17/02/2011   initial Build          |
# | 2.0       Hilton               08/10/2011   added dest info        |
# +====================================================================+

export P_SERVER=$7
export P_USER=$8
export P_PASSWORD=$9
export P_FOLDER=${10}
export P_FILENAME=${11}
export P_DEST_DIR=${12}
export P_DEST_FILE=${13}

echo "+===========================================================+"
echo "| Date Of Running             : "`date +%B-%d`"             |"
echo "+===========================================================+"
echo "|Parameters"
echo "|P_SERVER               : $P_SERVER"
echo "|P_USER               : $P_USER"
echo "|P_PASSWORD              : $P_PASSWORD"
echo "|P_FOLDER              : $P_FOLDER"
echo "|P_FILENAME              : $P_FILENAME"
echo "|P_DEST_DIR              : $P_DEST_DIR"
echo "|P_DEST_FILE          : $P_DEST_FILE"
echo "+===========================================================+"

echo "Copy file"
echo "============================================================="

cd $P_FOLDER
echo `pwd`
ftp -i -n $P_SERVER << EOF
user $P_USER $P_PASSWORD
pwd
cd $P_DEST_DIR
pwd
put $P_FILENAME $P_DEST_FILE
ls -l
bye
EOF
echo "*** End ***"

This is a host file that is used by the concurrent and takes in the variables of the server details and then also the source file destination and name and the destination directory and name. Thats about it. I will follow up this post with a look into the HOST program so if it doesn't make complete sense stay tuned.
This really is just a rough overview so feel free to ask any questions if something doesn't make sense in the comments below or by dropping me an email: tarmenel {AT} gmail [d0t] com