Oracle XML Publisher Moving Templates and Data Definitions between EBS Instances

XML Publisher stores all the meta data and physical files for templates and data definition in BLOB columns in its schema. It is therefore time consuming to manually download information from a development instance to a test and then to a production environment especially if there are many templates to manage.

XML Publisher uses the FNDLOAD loader technology and its own XDOLoader to allow you to move the metadata and physical files for your templates and datasources from one instance to another.

Managing Metadata

Both templates and data definitions are stored in the database and we can use the FNDLOAD loader to download the metadata for these objects and then upload them to another instance. The FNDLOAD program needs a control file (lct) that the XML Publisher team have provided. Its structure is as follows:

XDO_DS_DEFINITIONS – attributes for data source

|           KEY   APPLICATION_SHORT_NAME

            |           KEY   DATA_SOURCE_CODE

|

X_TEMPLATES – attributes for templates

|           KEY TMPL_APP_SHORT_NAME

|           KEY  TEMPLATE_CODE

|

X_TEMPLATE_FIELDS – template fields

|           KEY FIELD_NAME

Usage

Download

Using this structure we could use the FNDLOAD program to download any XML Publisher object from all data definitions and their associated templates to just an individual template file.

The FNDLOAD command takes the following format:

FNDLOAD usr/pwd@db 0 Y DOWNLOAD|UPLOAD xdotmpl.lct targetldtfile.ldt XMLP ATTRIBUTES

If we wanted to download all data definitions and templates for Accounts Receivable we would use:

FNDLOAD apps/apps@mydb 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XMLPData.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=AR

Notice we had to provide the full path to the xdotmpl.lct file, this is under XDO_TOP under APPL_TOP. The definitions will be downloaded to XMLPData.ldt. Other examples of the FNLOAD command might be:

FNDLOAD apps_read_only/apps@seed115 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XMLPData.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=AR DATA_SOURCE_CODE=ARXCOBLX

To download the data definition (ARXCOBLX) and all of its associated template definitions.

FNDLOAD apps_read_only/apps@seed115 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XMLPData.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=AR DATA_SOURCE_CODE=ARXCOBLX TMPL_APP_SHORT_NAME=AR TEMPLATE_CODE=ARLETTER1

To download the data definition (ARXCOBLX) and one of its associated templates (ARLETTER1) from that datasource.

Upload

Now we have a file definition of the data definition and template meta data we can now upload this data to any EBS instance. The FNDLOAD command is similar to before except we use the UPLOAD switch.

If we wanted to upload all of our data definitions and templates we could use:

FNDLOAD apps/apps@mydb 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XMLPData.ldt

If we only wanted to load certain data definitions or templates we could use attributes similar to the DOWNLOAD options

FNDLOAD apps/apps@mydb 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XMLPData.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=AR

This command will upload all data definitions and templates from the AR application that appear in our ldt file.

Loading template and sample data files

XML Publisher provides a java based command line program to load template (RTF, PDF and XSLFO), XML and XSD files to the XML Publisher tables. The program can be used to download files from one instance and load to another.

The loader has two modes:

·        File download only mode : With this mode, you can download files from the XDO_LOBS table. You need to specify the target LOB_CODE, APPS_SHORT_NAME, LOB_TYPE then it will download all files that match the criteria. You can also specify LANGUAGE and TERRITORY.

·        File download and LDT/DRVX generation mode : With this mode, you can download files from the XDO_LOBS table. It also creates LDT and DRVX files for these downloaded files, for your purposes you do not need the drvx file. This is used by Oracle development to load templates during a patch application. You need to specify APPS_SHORT_NAME, it will download all files (including template files, data definition files and sample xml files) that have the same application short name. You can also specify DS_CODE to select files that are related to the the specific data source definition.

Usage

Download

Firstly you need to ensure you have setup your environment for your session ie setting APPL_TOP, CLASSPATH, etc.

DOWNLOAD usage (file download only mode):

 % 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>

Parameter Name	Description
DOWNLOAD	(Mandatory) The first parameter : DOWNLOAD will be implemented in the feature
DB_USERNAME	(Mandatory) Database user name (e.g. apps)
DB_PASSWORD	(Mandatory) Database user password (e.g. manager)
JDBC_CONNETION	(Mandatory) JDBC database connection string (e.g.: ap000sun:1521:dev115)
LOB_TYPE	(Mandatory) XDO LOB type, either one of following: TEMPLATE, XML_SCHEMA, XML_SAMPLE
APPS_SHORT_NAME	(Mandatory) Application short name (e.g. XDO)
LOB_CODE	(Mandatory) XDO LOB code, either of Tempate code or Data source code.
LANGUAGE	(Optional)ISO 2 letter language code (e.g.en)
TERRITORY	(Optional)ISO 2 letter territory code (e.g. US)
LOG_FILE	(Optional) Output log file (default: xdotmpl.log)
DEBUG	(Optional) [true|false] Debug mode (default: false)

 Sample usage as follows:

 % java oracle.apps.xdo.oa.util.XDOLoader \

 DOWNLOAD \

 -DB_USERNAME apps \

 -DB_PASSWORD apps \

 -JDBC_CONNECTION ap000sun:1521:apps115 \

 -LOB_TYPE TEMPLATE \

 -APPS_SHORT_NAME XDO \

 -LOB_CODE XDOTMPL1 \

 -LANGUAGE ja \

 -TERRITORY JP 

 DOWNLOAD usage (file download and LDT/DRVX generation mode) :

 % java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \

 -DB_USERNAME <db_username> \

 -DB_PASSWORD <db_password> \

 -JDBC_CONNECTION <jdbc_con_string> \

 -APPS_SHORT_NAME <application_short_name> \

 -DS_CODE <data source code> \

 -LCT_FILE <lct file> \

 -LDT_FILE <ldt file> \

 -DRVX_FILE <drvx file> \

 -LOG_FILE <log file> 

Parameter Name	Description
DOWNLOAD	(Mandatory) The first parameter
DB_USERNAME	(Mandatory) Database user name (e.g. apps)
DB_PASSWORD	(Mandatory) Database user password (e.g. manager)
JDBC_CONNETION	(Mandatory) JDBC database connection string (e.g.: ap000sun:1521:dev115)
APPS_SHORT_NAME	(Mandatory) Application short name (e.g. XDO)
LCT_FILE	(Mandatory) Full path to the xdotmpl.lct
DS_CODE	(Optional) Data source code
LDT_FILE	(Optional) Output LDT file name. (default: xdotmpl.ldt)
DRVX_FILE	(Optional) Output DRVX file name. (default: xdotmpl.drvx)
LOG_FILE	(Optional) Output log file (default: xdotmpl.log)
DEBUG	(Optional) [true|false] Debug mode (default: false)

This mode will create the template or data files, one LDT file, one DRVX file and one log file.

 Sample usage as follows:

 % java oracle.apps.xdo.oa.util.XDOLoader \

 DOWNLOAD \

 -DB_USERNAME apps \

 -DB_PASSWORD apps \

 -JDBC_CONNECTION ap000sun:1521:apps115 \

 -APPS_SHORT_NAME XDO \

 -LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct \

 -DS_CODE XDODS1

In this mode the ldt file can then be used with the FNDLOAD utility to upload the metadata for te downloaded templates.

Upload

UPLOAD usage :

 % 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>

Parameter Name	Description
UPLOAD	(Mandatory) The first parameter
DB_USERNAME	(Mandatory) Database user name (e.g. apps)
DB_PASSWORD	(Mandatory) Database user password (e.g. manager)
JDBC_CONNETION	(Mandatory) JDBC database connection string (e.g.: ap000sun:1521:dev115)
LOB_TYPE	(Mandatory) XDO LOB type, either one of following: TEMPLATE, XML_SCHEMA, XML_SAMPLE
APPS_SHORT_NAME	(Mandatory) 3 letter Application short name (e.g. XDO)
LOB_CODE	(Mandatory) XDO LOB code, either of Tempate code or Data source code.
NLS_LANG	(Mandatory) To pass NLS_LANG environment variable
LANGUAGE	(Optional) ISO 2 letter language code (ex: en) If NLS_LANGUAGE='TRADITIONAL CHINESE', then cn_TW and if NLS_LANGUAGE='SIMPLEFIED CHINESE' then cn_CN for combination of language and territory.
TERRITORY	(Optional) ISO 2 letter territory code (ex: US) default is '00'
XDO_FILE_TYPE	(Mandatory) : XDO file type, any of following: PDF, RTF, XLS, XSL-FO, XSL-HTML, XSL-XML, XSL-TEXT, XSD, XML, RTF-ETEXT
FILE_CONTENT_TYPE	(Optional) Content type of the file (ex: text/html, application/pdf)
FILE_NAME	(Mandatory) Name of the file you want to upload. (ex: sample.pdf, test.xml) This file name can be full path (e.g. /u01/oracle/11iapp/xdo/115/patch/115/publisher/templates
OWNER	(Optional) Owner of the template. Default is "ORACLE"
CUSTOM_MODE	(Optional) [FORCE|NOFORCE] Whether force update, default is NOFORCE
LOG_FILE	(Optional) Output log file (default: xdotmpl.log)
DEBUG	(Optional) [true|false] Debug mode (default: false)
USE_APPS_CONTEXT	(Optional) [true|false] Use AppsContext or not. If you don't use AppsContext, always '1' is used for Apps Login ID. (default: false)

 Sample usage is following.

 % java oracle.apps.xdo.oa.util.XDOLoader \

 UPLOAD \

 -DB_USERNAME apps \

 -DB_PASSWORD apps \

 -JDBC_CONNECTION ap000sun:1521:apps115 \

 -LOB_TYPE TEMPLATE \

 -APPS_SHORT_NAME XDO \

 -LOB_CODE XDOTMPL1 \

 -LANGUAGE ja \

 -TERRITORY JP \

 -XDO_FILE_TYPE PDF \

 -FILE_CONTENT_TYPE 'aplication/pdf' \

 -FILE_NAME $XDO_TOP/patch/115/publisher/templates/XDOTMPL1_ja_JP.pdf

 -NLS_LANG JAPANESE_JAPAN.JA16EUC  

The loader can be used before or after the FNDLOAD command. The files will be loaded with its LOB_CODE, this will join to the main metadata loaded using the TEMPLATE_CODE or DATA_SOURCE_CODE mapping to the LOB_CODE.