Prabhakar Somanathan

A BI Publisher / XML Publisher report development is a multi step process requiring a technical consultant to develop multiple files and register with XML Publisher Administrator. In addition to registering, consultant needs to integrate the report with oracle applications using Concurrent Program or OA Framework Self Service Application. Due to these complexity, its very important to understand the steps involved in moving the components of a XML Publisher report across instances.

Term XML Publisher and BI Publisher are used interchangeably. In this article i will be using the term BI Publisher.

In this article we will discuss in details the steps involved in the moving the XML Publisher / BI Publisher report components across instances.

The mechanism by which BI Publisher reports can be integrated in Oracle Applications is Listed below

• BI Publisher + Bursting
• BI Publisher + Concurrent Program + Oracle Reports.
• BI Publisher + Concurrent Program + PLSQL API.
• BI Publisher + Concurrent Program + Data Template.
• BI Publisher + OA Framework.
• BI Publisher + Document Viewer + OA Framework
• BI Publisher + Concurrent Program + OA Framework
• BI Publisher + Concurrent Program + Document Viewer + OA Framework
  

Each of the above mechanism requires the creation and registration of

• Data Definition
• Data template
• Data Template file in RTF, PDF, XSL-HTML, XSL-XML, XSL-FO, XSL-TEXT, eTEXT, Microsoft Excel.
• Optional creation of XSD schema and Sample Data XML

Data Template requires

• Creation of XML DATA TEMPLATE file.

XML Bursting requires

• Bursting Control File

BI Publisher provides the FNDLOAD and XDOLoader utilities to move component across instances.
The FNDLOAD utility is used to upload and download the data definition and Data template created via XML Publisher Administrator responsibility.

The XDOLoader utility is used to upload and download the physical files (Template files, XSD Schema, Sample XML Data , Bursting Control File, Data Template ........)

Execute the FNDLOAD and XDOLoader utility from Application Server Linux terminal with the appsenv initialized. This will provide you access to the FND and appl_top files on the application server classpath.

FNDLOAD : Download and Upload Data Definition and Data Template
Use the following commands to
1) Download all the data definitions and corresponding templates in an instance.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS

2) Download all the data definitions and the corresponding Templates in a specific product.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX

3) Download a particular data definitions and its corresponding Templates.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX DATA_SOURCE_CODE=EMP_DD

4) Upload the data definitions and its corresponding Templates to an instance.
FNDLOAD apps/apps_pwd@db 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct
targetldtfile.ldt

Sample LDT File:
# $Header$

# dbdrv: exec fnd bin FNDLOAD bin &phase=dat checkfile:~PROD:~PATH:~FILE &ui_apps 0 Y UPLOAD @XDO:patch/115/import/xdotmpl.lct @~PROD:~PATH/~FILE
LANGUAGE = "US"
LDRCONFIG = "xdotmpl.lct 120.5"

#Source Database R124

#RELEASE_NAME 12.0.4

# -- Begin Entity Definitions --

DEFINE XDO_DS_DEFINITIONS
KEY APPLICATION_SHORT_NAME VARCHAR2(50)
KEY DATA_SOURCE_CODE VARCHAR2(80)
CTX OWNER VARCHAR2(4000)
BASE DATA_SOURCE_STATUS VARCHAR2(1)
BASE START_DATE VARCHAR2(11)
BASE END_DATE VARCHAR2(11)
BASE OBJECT_VERSION_NUMBER NUMBER(22)
BASE LAST_UPDATE_DATE VARCHAR2(11)
BASE ATTRIBUTE_CATEGORY VARCHAR2(90)
BASE ATTRIBUTE1 VARCHAR2(450)
BASE ATTRIBUTE2 VARCHAR2(450)
BASE ATTRIBUTE3 VARCHAR2(450)
BASE ATTRIBUTE4 VARCHAR2(450)
BASE ATTRIBUTE5 VARCHAR2(450)
BASE ATTRIBUTE6 VARCHAR2(450)
BASE ATTRIBUTE7 VARCHAR2(450)
BASE ATTRIBUTE8 VARCHAR2(450)
BASE ATTRIBUTE9 VARCHAR2(450)
BASE ATTRIBUTE10 VARCHAR2(450)
BASE ATTRIBUTE11 VARCHAR2(450)
BASE ATTRIBUTE12 VARCHAR2(450)
BASE ATTRIBUTE13 VARCHAR2(450)
BASE ATTRIBUTE14 VARCHAR2(450)
BASE ATTRIBUTE15 VARCHAR2(450)
TRANS DATA_SOURCE_NAME VARCHAR2(120)
TRANS DESCRIPTION VARCHAR2(2000)

DEFINE X_TEMPLATES
KEY TMPL_APP_SHORT_NAME VARCHAR2(50)
KEY TEMPLATE_CODE VARCHAR2(80)
CTX OWNER VARCHAR2(4000)
BASE TEMPLATE_TYPE_CODE VARCHAR2(30)
BASE DEFAULT_LANGUAGE VARCHAR2(6)
BASE DEFAULT_TERRITORY VARCHAR2(6)
BASE MLS_LANGUAGE VARCHAR2(6)
BASE MLS_TERRITORY VARCHAR2(6)
BASE TEMPLATE_STATUS VARCHAR2(6)
BASE USE_ALIAS_TABLE VARCHAR2(6)
BASE DEPENDENCY_FLAG VARCHAR2(1)
BASE START_DATE VARCHAR2(11)
BASE END_DATE VARCHAR2(11)
BASE OBJECT_VERSION_NUMBER NUMBER(22)
BASE LAST_UPDATE_DATE VARCHAR2(11)
BASE ATTRIBUTE_CATEGORY VARCHAR2(100)
BASE ATTRIBUTE1 VARCHAR2(450)
BASE ATTRIBUTE2 VARCHAR2(450)
BASE ATTRIBUTE3 VARCHAR2(450)
BASE ATTRIBUTE4 VARCHAR2(450)
BASE ATTRIBUTE5 VARCHAR2(450)
BASE ATTRIBUTE6 VARCHAR2(450)
BASE ATTRIBUTE7 VARCHAR2(450)
BASE ATTRIBUTE8 VARCHAR2(450)
BASE ATTRIBUTE9 VARCHAR2(450)
BASE ATTRIBUTE10 VARCHAR2(450)
BASE ATTRIBUTE11 VARCHAR2(450)
BASE ATTRIBUTE12 VARCHAR2(450)
BASE ATTRIBUTE13 VARCHAR2(450)
BASE ATTRIBUTE14 VARCHAR2(450)
BASE ATTRIBUTE15 VARCHAR2(450)
TRANS TEMPLATE_NAME VARCHAR2(120)
TRANS DESCRIPTION VARCHAR2(2000)

DEFINE X_TEMPLATE_FIELDS
KEY FIELD_NAME VARCHAR2(255)
CTX OWNER VARCHAR2(4000)
BASE ALIAS_NAME VARCHAR2(255)
BASE LAST_UPDATE_DATE VARCHAR2(11)
END X_TEMPLATE_FIELDS
END X_TEMPLATES
END XDO_DS_DEFINITIONS

# -- End Entity Definitions --

BEGIN XDO_DS_DEFINITIONS "XXX" "EMP_DD"
OWNER = "PRABHAKAR"
DATA_SOURCE_STATUS = "E"
START_DATE = "2008/05/11"
OBJECT_VERSION_NUMBER = "1"
LAST_UPDATE_DATE = "2008/05/11"
DATA_SOURCE_NAME = "EMP DD"
DESCRIPTION = "Data Template Demo"

BEGIN X_TEMPLATES "XXX" "Emp_Template"
OWNER = "PRABHAKAR"
TEMPLATE_TYPE_CODE = "RTF"
DEFAULT_LANGUAGE = "en"
DEFAULT_TERRITORY = "US"
TEMPLATE_STATUS = "E"
USE_ALIAS_TABLE = "N"
DEPENDENCY_FLAG = "P"
START_DATE = "2008/05/12"
OBJECT_VERSION_NUMBER = "1"
LAST_UPDATE_DATE = "2008/05/12"
TEMPLATE_NAME = "EmpTemplate"
END X_TEMPLATES

END XDO_DS_DEFINITIONS

XDOLOADER : Download and Upload physical files

The XDO Loader comes in two mode .

• File download only mode
• File download and LDT/DRVX generation mode

In theroy, the File download Only Mode allows you to download files of the specified type (BURSTING_FILE, DATA_TEMPLATE,TEMPLATE,XML_SAMPLE,XML_SCHEMA) from a specified application short name. If you specify the data definition or template code, then it download the specified file type of that data definition or Template. But when i tested the command, it generated files of all type attached to a data definition in a specified application short name including the drv file.

The File Download and LDT/DRVX Generation Mode allows you to download all the files (BURSTING_FILE,DATA_TEMPLATE,TEMPLATE,XML_SAMPLE,XML_SCHEMA) for a specified application short name. If you specify the data definition, then it will download all the files for the specified data definition. IN addition these files, it also generates a DRVX files, which has the upload script for each of the file downloaded.

As a technical consultant, i would suggest every one to use the File Download and LDT/DRVX Generation Mode as it is defined to downloads all the files types in one go including the drvx file that contains the upload scripts. This will reduce our effort considerably.
If you want to download a specify file type, then you can use the File Download Mode.

File Download Only Mode
Syntax :
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME <db_username> \
-DB_PASSWORD <db_password> \
-JDBC_CONNECTION <jdbc_con_string> \
-LOB_TYPE <lob_type> \
-APPS_SHORT_NAME <application_short_name> \
-LOB_CODE <lob_code> \
-LANGUAGE <language> \
-TERRITORY <territory> \
-LOG_FILE <log file>




Example :
1) Download all the physical files in the instance.

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION apsrtc:1521:SID \
-LANGUAGE en \
-TERRITORY US

2) Download all the physical files for a specified application short name

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION apsrtc:1521:SID \
-APPS_SHORT_NAME AR \
-LANGUAGE en \
-TERRITORY US

3) Download all the physical files for a specified application short name and LOB Type

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION apsrtc:1521:SID \
-LOB_TYPE BURSTING_FILE \
-LANGUAGE en \
-TERRITORY US

As per syntax, it should download only Bursting File. But it will downloads all the file types.

The File Download and LDT/DRVX Generation Mode
Syntax :
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME <db_username> \
-DB_PASSWORD <db_password> \
-JDBC_CONNECTION <jdbc_conn_string> \
-APPS_SHORT_NAME <application_short_name> \
-DS_CODE (data source code> \
-LCT_FILE <full path to lct file> \
-LDT_FILE <ldt file> \
-DRVX_FILE <drvx file> \
-LOG_FILE <log file>

Example :
1) Download all the physical files of a specified application short name in an instance.

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD welcome \
-JDBC_CONNECTION r12.com:1533:R124 \
-APPS_SHORT_NAME AR \
-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct

2) Download all the physical files of a specified application short name and in and data definition in an instance.
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD welcome \
-JDBC_CONNECTION r12.com:1533:R124 \
-APPS_SHORT_NAME AMW \
-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct \
-DS_CODE EMP_DD


Uploading Physical Files

Syntax :
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME <db_username> \
-DB_PASSWORD <db_password> \
-JDBC_CONNECTION <jdbc_con_string> \
-LOB_TYPE <lob_type> \
-APPS_SHORT_NAME <application_short_name> \
-LOB_CODE <lob_code> \
-LANGUAGE <language> \
-TERRITORY <territory> \
-XDO_FILE_TYPE <xdo_file_type> \
-NLS_LANG <NLS_LANG> \
-FILE_CONTENT_TYPE <file_content_type> \
-FILE_NAME <file_name> \
-OWNER <owner> \
-CUSTOM_MODE [FORCE|NOFORCE] \
-LOG_FILE <log file>


Example :

1) Upload the RTF file.

java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD welcome \
-JDBC_CONNECTION r12.com:1533:R124 \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME AMW \
-LOB_CODE EMP_DD \
-LANGUAGE en \
-TERRITORY US \
-NLS_LANG American_America.WE8ISO8859P1 \
-XDO_FILE_TYPE RTF \
-FILE_CONTENT_TYPE ’text/html’ \
-FILE_NAME /tmp/psomanat/TEMPLATE_SOURCE_AMW_Emp_Template_en_US.rtf

Add comment

Name (required)

E-mail (required, but will not display)

Website

Notify me of follow-up comments

Refresh

Send

Cancel