Printing Word doc FND attachments

We recently came across an unique requirement of printing the Word document attachments along with a report. I googled and metalinked. But found no direct solution and there were few open Enhancement Requests open with Oracle. We finally found a way. The idea is very simple. Get the attachments BLOB stored in database to apps server using JDBC and print the file using soffice. But lotsa work must be done and most of them need an APPS DBA's help. This article outlines the steps but not in detail. You should spend some time based on your apps configuration to get it work.

Technical Overview of FND attachments.

All file attachments are stored in FND_LOBS table in file_data column. You can link this back to your attachment entity using the following query.

SELECT fl.file_data,
fdt.media_id,
fad.entity_name,
fdt.description
FROM fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_lobs fl
WHERE fad.document_id = fdt.document_id
AND fl.file_id = fdt.media_id
AND fdt.language = userenv('Lang')
AND fad.entity_name = 'MTL_SYSTEM_ITEMS'
AND fad.pk1_value = 183
AND fad.pk2_value = 164217


Here we are getting the attachment details of Discrete Item Master (Entity_Name: MTL_SYSTEM_ITEMS) and primary key values PK1_VALUE(Organization_ID) and PK2_VALUE (Inventory_Item_id).

For your entity you can get the details by navigating to Responsibility: Application Developer and Menu Navigaton: Attachments > Document Entity and Attachments > Attachment Functions.

Steps to print the attachments

Step 1: Java program to bring attachment BLOB to apps tier. Link.
This program gets media id and brings the BLOB from fnd_lob to apps tier.

Parameters : 1:User name 2:Password 3:Host 4:Port 5:SID 6:MediaId 7:FileSuffix

Step 2: Download Open Office and configure it in apps tier. I used my Apps DBA's help for it and it is a linux expert job.

Vaguely we have to do the following.
a.Install open office on the server
b.Log in as the oraowner, start a X session and launch open office writer
c.From root run the following: (This will need to be run after reboot or added as a service)
$ unset DISPLAY
$ soffice –headless –accept="socket,host=127.0.0.1,port=8100;urp;" –nofirststarwizard


Step 3: Create a simple shell script to use Java program in Step 1 and Open Office in Step 2 to print the file.

#!/bin/ksh
# $Header: XXAttachmentPrint.ksh$
#
# Purpose:
# Copy a file from the db to a temp location and print it.
#
# $5 MediaId
# $6 FileName
# $7 PrinterName
#
# Set Application Environment Variables
export PATH=${PATH}:/usr/local/bin
export XXENVPREF=`echo ${TWO_TASK}|awk ' { print tolower($1) } '`

# intialize the apps context in linux. You must replace this with your logic for executing APPSORA.env from $APPL_TOP
. XXsetenv${XXENVPREF}.ksh
. XXSetAppsEnv.ksh


export MEDIAID=${5}
echo "MediaId=" ${MEDIAID}
export FILENAME=${6}
echo "FileName=" ${FILENAME}
export PRINTERNAME=${7}
echo "PrinterName=" ${PRINTERNAME}

# Change Directory to temp location
cd ${COMMON_TOP}/temp

# Execute the java code to extract file. You can read db user name, passwd host etc from concurrent program params 1-4 or read it from another
# file/ exported variables in instance.
java tempel.oracle.apps.xxtmg.crm.AttachmentReader apps `cat ~/APPSpswd${XXENVPREF}.txt` ${XXDBHOST} ${XXDBPORT} ${XXENV} ${MEDIAID} ${FILENAME}.doc

# Open Office print command
soffice -headless -pt ${PRINTERNAME} ${FILENAME}.doc

# Clean up extracted document
rm ${FILENAME}.doc

#
# End of File



Step 4: Seed the above shell script as a concurrent program in APPS.

Application Developer > Concurrent > Programs/Executable

Execution file name should be same as the script name in Step 3.

Step 5: Write a simple PLSQL program which based on entity name and PK value launches the Step 4 program by passing parameters for media id, printer and file name.
PROCEDURE print_mtl_attachments(p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER) IS

CURSOR c IS
SELECT fdt.media_id
FROM fnd_attached_documents fad,
fnd_documents_tl fdt,
fnd_lobs fl
HERE fad.document_id = fdt.document_id
AND fl.file_id = fdt.media_id
AND fdt.language = userenv('Lang')
AND fad.entity_name = 'MTL_SYSTEM_ITEMS'
AND fad.pk1_value = p_organization_id
AND fad.pk2_value = p_inventory_item_id;

l_request_id NUMBER;
BEGIN

FOR c_rec IN c LOOP
x_request_id :=
fnd_request.submit_request (<app short name>,
<prog short name from step 4>,
<some desc>,
TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
FALSE,
c_rec.media_id,
'F' || c_rec._media_id || '-' || to_char(SYSDATE,'MMDDYYYYHH24MISS', -- unique file name,
<printer name>
);
END LOOP;

END print_mtl_attachments;

Step 6: Seed another concurrent program for the PLSQL in step 5.

Step 7: Add concurrent program in Step 6 to a request set containing your report.