Part 1: Adding new fields to the R12 Payment Funds Disbursement XML file

Hands up if you or someone you know has customised the standard 11i (or earlier) Format Payments program (APXPBFOR.rdf) to add additional fields, change the cheque / check layout or something similiar??

Now, hands up if you have customised the RDF for your 11i BACS or electronic paymentfile??

Crikey… there are a lot of hands up out there.

In fact, I also have worked on lots of sites in the past where we have ‘tweaked’ the content and layout of these seeded 11i payment documents. For example, on my current project, there is a requirement to add some descriptive flexfield data from the invoice record (ap_invoices_all)
onto the cheque.

Well, the good news is…
From R12, the Payments module has replaced the previous Payables functionality and has reimplemented all the templates using XML Publisher :-)

And the bad news is…
From R12, the Payments module has replaced the previous Payables functionality and has reimplemented all the templates using XML Publisher :-(

OK – that was a poor attempt at humour – but from the technical and work-load perspective, the upgrade to Payments really is a double edge sword:
You get all of the great XML Publisher functionality BUT you have to re-implement any customisations to your payment instructions!

Luckily for us, Oracle has made it relatively easy to configure the R12 payment instructions and add new fields, content and layout. They have provided:

1) - A PL/SQL Package ‘IBY_FD_EXTRACT_EXT_PUB‘ to add additional tags to the XML file created during the Discbursement Process.
2) - Loads of RTF and ETEXT presentation templates for BACS, cheque printing and other payment types.

In my next post, I’ll take you through the steps to add new fields and tags to the XML file created by the XML Publisher data definition ‘Oracle Payments Funds Disbursement Payment Instruction Extract 1.0’.

XDOLoader Part 2 - UPLOAD syntax

Yesterday, I ran through the DOWNLOAD syntax of XDOLoader to download XML Publisher templates and data definitions to a local machine.
OK - I admit that having the file download syntax without the corresponding UPLOAD syntax is not much use! So, let's take a closer look at the UPLOAD syntax:

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-XDO_FILE_TYPE \
-NLS_LANG \
-FILE_NAME \
-CUSTOM_MODE [FORCENOFORCE] \
-LOG_FILE

I'll only describe those parameters that are different from yesterday:

XDO_FILE_TYPE: The XML Publisher Type i.e. PDF, RTF, XLS, XSL-FO, XSL-HTML, XSL-XML,
XSL-TEXT, XSD, XML, RTF-ETEXT
NLS_LANG: The value of your NLS_LANG environment variable e.g. American_America.WE8ISO8859P1
FILE_NAME: The name of XML Publisher File to upload
CUSTOM_MODE: Set this to FORCE to update an existing template

UPLOAD EXAMPLE:

Here's a shell script that I use to upload files:
------------------------
# Get parameters
apps_psw=$1
jdbc_con=$2
app_short_name=$3
lob_code=$4
filename=$5
filetype=$6
#Upload XML Publisher Template
% java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $apps_psw \
-JDBC_CONNECTION $jdbc_con \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME $app_short_name \
-LOB_CODE $lob_code \
-NLS_LANG American_America.WE8ISO8859P1 \
-LANGUAGE en \
-TERRITORY US \
-XDO_FILE_TYPE $filetype \
-FILE_NAME $filename \
-CUSTOM_MODE FORCE \
-LOG_FILE xdotmpl.log
------------------------

ADDITIONAL NOTES:

1) You must set your APPL_TOP and CLASSPATH before running the XDOLoader
2) Don't forget to FNDLOAD the XML Publisher metadata using xdotmpl.lct before uploading the XML Publisher template or data template. Without this, you wont be able to view the file uploaded from the XML Publisher Administrator responsibility. I'll leave that syntax for you to look up ;-)

XDOLoader Part 1 - XML Publisher DOWNLOAD mode syntax

It surprises me how few people are aware of the generic loader (XDOLoader) for XML / BI Publisher (BIP) files.

Like FNDLOAD for AOL / XML Publisher metadata and WFLOAD for Workflow Item Types, XDOLoader is is a command line utility that allows you to migrate the BIP files between environments. The following BIP file types can be upload and downloaded:

- TEMPLATE
- XML_SCHEMA
- XML_SAMPLE
- DATA TEMPLATE

Call XDOLoader in DOWNLOAD mode to create a local copy of the relevant object.
Call XDOLoader in UPLOAD mode to load your local definitions into the database.

Here's the DOWNLOAD syntax and some examples for Oracle E-Business suite users:

DOWNLOAD MODE SYNTAX:

% java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-LOG_FILE \
-DEBUG

KEY (for E-Business suite Users):

-DB_USERNAME: apps username for the target instance
-DB_PASSWORD: apps password for the target instance
-JDBC_CONNECTION: JDBC connection string (e.g. server:port:sid)
of the target instance
-LOB_TYPE: The XML Publisher object type.
One of 'TEMPLATE', 'XML_SCHEMA', 'XML_SAMPLE' or 'DATA TEMPLATE'
-APPS_SHORT_NAME: Application short name used to register the XML Publisher object
-LOB_CODE: Template code i.e. concurrent program short name
-LANGUAGE: ISO language code(i.e. en) used when registering the object
-TERRITORY: ISO language code(i.e. GB) used when registering the object
-LOG_FILE: Name of the log file. The default filename is xdotmpl.log
-DEBUG: Run in Debug mode. Valid values are 'true' or 'false'


DOWNLOAD MODE EXAMPLES:

This is the simple shell script that I use to download 'GB English' presentation and data templates.
(Note - Change your language and territory as appropriate):

-----------------------------------------
# Get parameters
apps_psw=$1
jdbc_con=$2
app_short_name=$3
lob_code=$4

# Download XML Publisher Template RTF and XSL
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $apps_psw \
-JDBC_CONNECTION $jdbc_con \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME $app_short_name \
-LOB_CODE $lob_code \
-LANGUAGE en \
-TERRITORY GB \
-LOG_FILE xdotmpl.log \
-DEBUG true

# Download XML Publisher dataTemplate
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD $apps_psw \
-JDBC_CONNECTION $jdbc_con \
-LOB_TYPE DATA_TEMPLATE \
-APPS_SHORT_NAME $app_short_name \
-LOB_CODE $lob_code \
-LANGUAGE en \
-TERRITORY GB \
-LOG_FILE xdotmpl.log \
-DEBUG true

-----------------------------------------

ADDITIONAL NOTES:

1) Don't forget to download your XML Publisher metadata using the FNDLOAD utility. The control file name is xdotmpl.lct.

More on the UPLOAD syntax tomorrow!

Another Quick Reminder: Linking Discoverer Reports to standard Oracle Applications Menu

Here are the steps required to link a standard Discoverer workbook to an Oracle Applications menu:

Step	Description	Tool
1	Note the value of the workbook ‘Identifier’. Open the workbook, navigate to ‘File -> Manage Workbooks -> Properties.	Discoverer Plus
2	Create a new Form Function (Application -> Function). Required values: Function: XX???_??? User Function Name: Report Name Description: ??? Type: SSWA plsql function Parameters: Workbook=Step 1 Identifier HTML Call: OracleOasis.RunDiscoverer	System Administrator
3	Link the form function to the appropriate menu (Application -> Menu).	System Administrator
4	Share the business areas used by the workbook with the responsibilities associated with the modified menu	Discoverer Administrator
5	Share the workbook with the responsibilities associated with the modified menu	Discoverer Plus

Associated Profile Options:

Profile Name	Description	Value
ICX_DISCOVERER_USEVIEWER	Determines if Discoverer Viewer or Discoverer Plus is launched when a workbook is opened from a menu	Yes or No

References:

Metalink Note: 278095.1

Quick Reminders: Steps to set up General Ledger security for Discoverer

Here’s a quick reminder of the steps required to setup GL ledger and segment value security for the seeded Discoverer General Ledger Business Area:

1. Set the profile option ‘FII: GL BIS Discoverer access’ for all GL responsibilities that will run Discoverer workbooks.
2. Ensure that the profile option ‘GL Set of Books Name’ (‘GL Ledger Name in R12) has been set at responsibility level for all GL responsibilities that will run Discoverer workbooks.
3. Ensure that the ‘Security Enabled’ flag is set to Yes for all ‘enabled’ segments of the chart of accounts. (Setup, Flexfields, Key, Segments)
   
4. Define a flexfield value set security rule for each segment in the chart of accounts that INCLUDES all possible values in the Security Rule Elements i.e. Include 000000 ZZZZZZ (Setup, Flexfields, Validation, Security, Define)
   
5. Assign the value set security rules created in Step 4 to all of the GL responsibilities that will run Discoverer Workbooks (Setup, Flexfields, Validation, Security, Assign)

Normal service resumes

I've been heads down, hands on and snowed under for the last few months working on an R12 implementation in the UK.

I've been doing some interesting things with Oracle Payments and XML Publisher using E-Text templates to integrate their check printing and BACS extract programs with their BottomLine / Paybase solution. I promise a post on this shortly.

Also, have been developing multiple materialised views for GL Balances, Journal Transactions and Drill down to Sub-ledgers reporting that can be integrated into both a Discoverer business area and the OBIEE BI Server.

I'm off to India (Pune) soon to brush up on my OAF skills. I've never been to India before so am really looking forward to the trip. Am planning to spend a few days in Goa after to relax a.k.a. party ;-) and work on my tan.

Connecting to Discoverer 10G

Does this sound familiar?

Connecting to Discoverer Administrator or Desktop as an apps user for the first time on a a project and you get the error message:

"Invalid Username and Password"

You have the oracle apps user checkbox ticked and have validated that both the username and password are correct and have checked your TNS Names file.

Rather than shout at the Apps DBA, here is what you need to do:

1) Create the following 2 new environment variables (path=control panel, system, advanced, environment variables) :

FND_SECURE =\secure
FND_TOP=

for me is C:\Oracle\BIToolsHome_1\

2) Rename the application dbc file from the $FND_TOP/secure directory to the name of the connect entry in your TNSNAMES.ORA file.

For example, if your TNSNAMES entry looks like this:
MYDATA.WORLD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = myserver.myorg.com)(Port = 1521)) (CONNECT_DATA = (SID = MYDATA)) )

then your dbc file must be named MYDATA.DBC


Happy discovering!