Loading files as attachments into Ora EBS with ODI

Loading files as attachments into Oracle EBS with ODI

At the end of last year I got a new task to work out a solution how to load files into Oracle EBS as a PO attachment. What was the small specification? To load PDF files - containing the PO number is their's name - into Oracle EBS database as an attachment for that PO headers the belong to the PO number. It was a small but quite interesting ODI and EBS development task.

What I needed to do my job? Or what I got for working out the solution

• an Oracle EBS 11i
• an Oracle Data Integrator (ODI 11.1..1.5)
• an ODI agent on that server where the Oracle EBS database installed
• a file directory on the same database node - this will contain the to be imported PDF files 
• a custom EBS interface schema for database connection

I have used these 2 references for solving the problem:

Oracle Data Integrator 11g Cookbook Chapter 8

http://oraclemaniac.com/2012/01/07/attach-concurrent-program-output-to-the-apps-transaction/

Let's go into the details

Main steps of the implementation

1. Create the "infrastructure"
2. Create working directory
3. Create new file attachment category for uploading
4. Develop custom database objects
5. Creating ODI components
6. Testing

Create the infrastructure

First of all create the whole infrastructure. Of course you will need an installed Oracle EBS, Oracle Data Integrator server and an agent on EBS database node. This post's goal is not to go into installation if these products so let's move along.

So what other necessary steps need for me?

Create working directory

In this example I have created a working directory for incoming files what should later upload as attachments into the EBS database. Of course you could use other technic and you could omit this steps if you have opportunity to upload binary data as blob directly into the database.

So create a standard directory on the EBS database node. (If you are using a RAC database then it should be on a shared file system). The directory should be readable as the database user and modificable(writeable) as the ODI agent owner. 

It is true for the incoming files too. You will see later that the ODI file handling technic I have choosed do it work enough strangely. This type of ODI commands needs writable permission on the files what they are handling despite of I use them only for reading the files.

Create new file attachment category for uploading

This is easy step - of course if you are familiar in EBS attachment handling. Just create a new attachment category for that object where you want to see later the attached files. Record the new category properties and send them to the ODI process developers. They will need it during the orchesting the process.

Develop custom database objects

What database objects I have created/developed during creating the final solution? Not so many :)

• An interface database schema
• A table for file listing
• A directory object
• A PL/SQL procedure for handling the files

Some details about them.

An interface database schema

This example ODI process has to handle database objects, but for security reason it is not a good idea to give the apps schema directly to the ODI process. So I created a new database schema for connection and storing interface data.

A table for file listing

Under the new database schema I created a new table for storing the file names what the ODI process has discovered in the incoming file directory. 

At early phase of the development - during planning - I have thought that if I use the OdiFileWait command it will not only watching a directory for incoming files but this will store the discovered filenames too. During the development I faced with the verity that this command is just a "hi something happened in the directory" command and you should handling anything else.

An example for the filelist table:

• BATCH_ID - for storing an ID for every batch process
• FILE_TYPE - for storing the purpose of the file
• FILE_NAME - for string the full file URL

A Directory object

This directory should point to the working file directory.

A PL/SQL procedure

This procedure is the soul of the whole ODI process. What is it doing?

• Identify the file purpose. Somehow you should find out that the incoming file where you has to put. If the incoming directory contains a mix salad, you have to find out the purpose for each file. This could be done here, but of course if you are enough good ODI developer you could do it at ODI level too. In my example I could use the file name, the file generated used by a naming convention and the working directory contained files only for one purpose. So fortunatly I had to develop only "decryption" function that find out po numbers from the file's name. And I had to use only one attachment category too.
• Loading files into the fnd_lobs table with correct content type, language, character set and file format. The program_name could be FNDATTACH for example.
• Create file description data with fnd_documents_pkg. This is the first time when you has to categorized the uploaded file.  (be aware to gave the correct datatype_id and category_id)
• Register file data with fnd_attached_documents_pkg package. 
• Optionally this package is the best places to invoke other function. I mention to not call them directly. Instead of create a custom Business Event and raise this event after succesful uploading.

I have created the package based on the example source I have found on Oracle Maniacs blog site. (see URL above). Cause of copyright I couldn't share publicly the code, but the example code is good enough! :)

Creating ODI components

So I have all basic building blocks. Let's create the ODI process. During development I have used the example chapter from the ODI 11g CookBook book. Please read it before you continue to read the below. I hope the chapter is still available through the link...

What I have done:

1. I have created 2 ODI variable
1. BATCH_ID - for storing a batch id for every running
2. FILE_SIGNATURE - this variable contain the search string, the filter string what I have used for filtering files in files from the file directory.
2. Register the PL/SQL procedure as an ODI procedure. A very simple one, just one command with Oracle technology and the command source should be only PL/SQL code for invoking the PL/SQL program.
3. Register a Jython ODI procedure. This will collect all filenames from the working directory and put them into a temporary data file.
4. Register the 2 files list object (the table and the temporary data file) in the ODI model. You should register the temporary data file manually, for the table registering you could use the standard ODI reverse enginering.
5. Create a new ODI interface for loading the data from the temporary file into the file list table. It is a very simple one. Use the LKM SQL to Oracle, IKM SQL Control Append, map the BATCH_ID with #BATCH_ID variable, file_name column with the content of the temporary file list model column.
6. Create a new ODI package, the below ODI commands should connected with "ok" arrow, and the order should be as you see below.
1. Put the FILE_SIGNATURE variable as a first step on the diagram. Give a new step name, the type should be "Set Variable" and give the filter string under the "Assign"
2. Put and OdiFileWait command on the diagram. Give a new step name, set the "Directory" parameter to incoming working file directory, set the "Filename Mask" paramater to #FILE_SIGNATURE, leave empty the targer parameters, set the other parameters as you wish.
3. Put the BATCH_ID variable on the diagram. Give a new step name, the type should be "Set Variable", set to "Increment" and set the increment value. (at least 1 of course)
4. Put the Jython ODI procedure on the diagram.
5. Put the ODI interface on the diagram
6. Put the PL/SQL ODI procedure on the diagram
7. Optionally put an OdiFileMove command on the diagram for moving the processed files into an archive directory from the working directory.

And now we are ready. This was my final ODI package diagram after I have made all development and tests:

Testing

Never omit this step. Before you schedule the package, test it! With as many test case as you could find out!

Some example codes:

Jython command:

import os
sCommand=''' ls -1 /interface/POAPPRV/in/#FILE_SIGNATURE > /interface/POAPPRV/in/FilesList.txt '''
iError=os.system(sCommand)
if iError <> 0 :
raise '''OS command has signalled error %d for command %s''' % (iError, sCommand)

PL/SQL command:

begin
 APPS.DOC_PKG.ATTACHMENT_UPLOAD('STANDARD',
                                        'PO_HEADERS',
                                        'ESIGN_PO',
                                        'SYSADMIN',
                                        #BATCH_ID);
end;

I hope the above was useful for your work :)