Skip to main content

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 Gareth Roberts 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

Comments

  1. BI Publisher provides API for FTP. Have you got a chance to look at that?

    ReplyDelete

Post a Comment

Popular posts from this blog

Cancel or abort adop session

ADOP is in my opinion still pretty half baked. This is a critical tool that just isn't as intuitive as the old adpatch was. However we move with the times and get the hang of the new way of doing things. Along the way you might want to abort or cancel a patching cycling. With the two file systems and db editioning this can be a bit more to manage. I have learnt the hard way so you need to use the full cleanup after aborting a session. adop phase=abort adop phase=cleanup cleanup_mode=full

Creating new WLS domain ends with exit code 255

Cloning the instance I ran into this weird error. Was not sure what to do but after learning my lesson I dug through the error logs after seeing this in the adcfgclone log file. START: Creating new WLS domain. Running /u03/APPLYES/YES/fs2/FMW_Home/oracle_common/bin/pasteConfig.sh -javaHome /u03/APPLYES/YES/fs2/EBSapps/comn/util/jdk64 -al /u03/APPLYES/YES/fs2/EBSapps/comn/clone/FMW/WLS/EBSdomain.jar -tdl /u03/APPLYES/YES/fs2/FMW_Home/user_projects/domains/EBS_domain_YES -tmw /u03/APPLYES/YES/fs2/FMW_Home -mpl /u03/APPLYES/YES/fs2/EBSapps/comn/clone/FMW/WLS/plan/moveplan.xml -ldl /u03/APPLYES/YES/fs2/inst/apps/YES_erpapyes/admin/log/clone/wlsT2PApply -silent true -debug true -domainAdminPassword /u03/APPLYES/YES/fs2/EBSapps/comn/clone/FMW/tempinfo.txt Script Executed in 1903 milliseconds, returning status 255 ERROR: Script failed, exit code 255 Dig a bit deeper into the log files cd /u03/APPLYES/YES/fs2/inst/apps/YES_erpapyes/admin/log/clone/wlsT2PApply cat CLONE2016-01-10_04-37-

Error during rapid clone on pasteBinary.sh, exit code 255

I needed to run a clone and was running into a really head scratcher. As is Oracle\'s way they sometimes enjoy giving us DBA's a bit of detective work to do. As usual I kept knocking my head into various brick walls instead of tracking back through the logs. Eventually I came up with the problem Start to configure the run filesystem. You can do a dualfs from AD-TXK Delta 7 but for now I just needed to get this up and running: perl adcfgclone.pl appsTier START: Creating FMW Home. Running /u03/APPLUAT/UAT/fs1/EBSapps/comn/clone/FMW/ pasteBinary.sh -javaHome /u03/APPLUAT/UAT/fs1/EBSapps/comn/util/jdk64 -al /u03/APPLUAT/UAT/fs1/EBSapps/comn/clone/FMW/FMW_Home.jar -tl /u03/APPLUAT/UAT/fs1/FMW_Home -invPtrLoc /etc/oraInst.loc -ldl /u03/APPLUAT/UAT/fs1/inst/apps/UAT_erpapp05/admin/log/clone/fmwT2PApply -silent true -debug true -executeSysPrereqs false Script Executed in 21933 milliseconds, returning status 255 ERROR: Script failed, exit code 255 Oracle has left me a cryptic