- Print Attachments in EBS -

The attachments feature in Oracle Application/E-Business Suite enables users to link unstructured data, such as images, word processing documents, spreadsheets, or text to their application data. For example, users can link images to items, PDF/Word document to operations as operation instructions.

It is very common requirement to print these attached documents during various processes. For example, Customer wants the Contract Agreement document along with billing invoice. In these cases users should open the attachment form and print the document manually every time. As of now EBS does not have standard mechanism to print the attachments.

This BLOG helps to print the attachment without opening the attachment form.


Approach

  1. Create Oracle Directory 'OUTFILE_FOLDER' and set the path to Concurrent program Output file location.
  2. Write a Java stored procedure to store the binary file to the specified location and compile it in APPS schema. (Refer: Sample-1). Instead of Java API, UTL_FILE.put_raw API can also be used if the database version is on or above 10g. 9i has limitations and bug to use this pl/sql API.
  3. Write a pl/sql API to get the binary file and make the call to above Java API to store the file in the outfile location. (Refer: Sample-2)
  4. Create concurrent program with Attachment File (FileID) as parameter and above pl/sql API as executable.
  5. If the attachments are PDF and want to print automatically then do the printer setups and format as PDF. Note that the printer drivers should support to print PDF files (Eg: PASTA Drivers).
  6. If the attachments can be in any format then set it as HTML output. In this case users have to open the concurrent program output and print it manually.
  7. Initiate this concurrent program along with the standard program either by creating Request Set or customizing the standard objects.
Sample-1: Java Program

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CopyFile" AS

import java.io.*;
import java.lang.*;
import java.sql.*;
import oracle.sql.*;

public class CopyFile extends Object
{
public static int execute(String filename, BLOB blob)
{

int success = 1;

try
{
File blobFile = new File(filename);
FileOutputStream outStream = new FileOutputStream(blobFile);
InputStream inStream = blob.getBinaryStream();

int length = -1;
int size = blob.getBufferSize();
byte[] buffer = new byte[size];

while ((length = inStream.read(buffer)) != -1)
{
outStream.write(buffer, 0, length);
outStream.flush();
}

inStream.close();
outStream.close();
}
catch (Exception e)
{
e.printStackTrace();
System.out.println("ERROR(djv_exportBlob) Unable to export:"+filename);
success = 0;
}
finally
{
return success;
}

}
};

Sample-2: Pl/sql API

CREATE OR REPLACE PACKAGE print is
PROCEDURE url(
x_status OUT VARCHAR2
,x_status_code OUT VARCHAR2
,p_file_id IN NUMBER
);
END print;
/

CREATE OR REPLACE PACKAGE BODY print IS

FUNCTION executeJava(
p_filename IN VARCHAR2
,p_blob IN BLOB
)
RETURN NUMBER IS
LANGUAGE JAVA
NAME 'CopyFile.execute(java.lang.String, oracle.sql.BLOB) return integer';

PROCEDURE url(
x_status OUT VARCHAR2
,x_status_code OUT VARCHAR2
,p_file_id IN NUMBER
) IS

lb_doc BLOB;
ln_status NUMBER;
ln_request_id NUMBER;
lc_outfile_path VARCHAR2(250);

BEGIN

-- Get the file ID
SELECT file_data
INTO lb_doc
FROM fnd_lobs
WHERE file_id = p_file_id;

-- Directory Path
SELECT directory_path
INTO lc_outfile_path
FROM all_directories
WHERE directory_name = 'OUTFILE_FOLDER';

-- Concurrent Program Request ID
ln_request_id := FND_GLOBAL.conc_request_id;

ln_status := executeJava(lc_outfile_path'/o'ln_request_id'.out',lb_doc);

END url;

END print;

Further Reading

Java I/O

4 comments:

Anonymous said...

Can you please demonstrate the same example using UTL_FILE.put_raw instead of JAVA program?

Anonymous said...

Quite good sharing. Deployed and working.

Craig Winger said...

I have version working with UTL_FILE.PUT_RAW.

Did you need to create a special print driver to get the PDF document to print?

Anonymous said...

I performed steps till 4... what should be the outcome at the end of this step? the lob file should be in the out folder?

after 4th that what exactly needs to be done? not very clear. could you please explain in detail?