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.
