<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content

Green

color

Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color

Who's Online

We have 2 guests online

Related Items

Home
This website has now moved to http://apps2fusion.com





Email output of Concurrent Program Request | Print |  E-mail
Written by Anil Passi   
Tuesday, 23 October 2007
There are a handful of options available to Email the output of a Concurrent Request from eBusiness Suite.
In this article we will see three different options that are available to us, so
that output of the Concurrent Program can be sent as an email attachment.

Hereby I would also like to share the contribution from Mr Santosh Moturu.
My sincere thanks to Santosh, as he has been kind enough to send a PDF that provides step by step
instruction with PL/SQL code, that Emails output of a XMLP request using PL/SQL.

What are the three different options at hand to email the output of Concurrent Request?
Option 1. Use printer setup to execute a shell script that emails the request output
Option 2. Use deliveryManager java API
Option 3. Use PL/SQL to Email output of XMLP as attachment [thanks to Santosh]

Use Printer Driver to execute a shell for sendmail/mailx
Using this technique, a shell script can be executed instead of the output being sent to a physical printer.
You can then use that shell script to send the output of concurrent program by Email.
The details can be found from Metalink Note 164085.1
This Metalink note has a pdf white paper, that explains this technique on page 20
You can also reference this white paper for your faxing requirements.




Use java based Delivery manager API to send output of XMLP as email attachment

This is explained in the page 322 of the whitepaper that can be accessed from link below
Click on this link to access the white paper for Delivery Manager API
The advantage of this technique is that is can be used in conjunction with bursting, so that a single output can be split and emailed separately.
The steps for bursting are explained in the same white paper
 



Use conventional pl/sql technique
Using this technique, the output of the concurrent program can be emailed to the desired recipient.
For programmers with PL/SQL the option 3 would be the quickest means to achieve the business requirement.
Santosh has written this article to explain the 3rd approach with great details. It also includes the source code.
This article can be downloaded from this link

Comments (18)add
4th Option
written by Anil Passi , October 24, 2007
In fact the 4th option would be to load the output of the Concurrent Request into a BLOB type column and then call a workflow that will attach the blob content as attachment. The workflow bit can be seen from the link below
http://oracle.anilpassi.com/se...kflow.html

Thanks,
Anil Passi
how to load the output of the Concurrent Request into a BLOB type
written by rahul pardeshi1 , October 24, 2007
Hi
how to load the output of the Concurrent Request into a BLOB type
is it stored somewhere in the database
Thanks,
Rahul.
...
written by Anil Passi , October 24, 2007
Hi Rahul

The file will be in $APPLCSF/$APPLOUT
You will have to load that into database blob

I will try to paste the code here, as a friend of mine implemented this option for this client.

Thanks
Anil Passi
Comments
written by Nitin Jain , October 25, 2007
Hi Santosh/Anil,

My suggestion here would be to use a shell script to achieve the same functionality. We have been doing this in plenty of our projects. UNIX shell scripts are more powerful than PL/SQL and require less coding smilies/grin.gif as compared to PL/SQL.

We have had issues in using PL/SQL for emailing attachments (like excel documents containing macros in them and size > 100 KB). The fix to that lies in a 10g database upgrade.

Hence suggest readers/techies to make use of shell scripts than PL/SQL.

Hope this piece of info helps.

Thanks,
Nitin
Sample Code
written by Nitin Jain , October 25, 2007
Hi all,

I am pasting a sample shell script that can be useful for all readers.

#====================================================== =====
echo "XXFND_EMAIL_RPT.prog started "

if [ $# = 0 ]
then
echo "No parms were passed to the Shell Program "
exit 1
fi

V_COUNTER=0
while [ "$V_COUNTER" -le $# ]
do
#echo "Passed Parm $V_COUNTER : c"
case "$V_COUNTER" in
0) echo "$0" ;;
1) echo "$1"
V_LOGIN="$1";;
2) echo "$2" ;;
3) echo "$3" ;;
4) echo "$4"
V_CURRENT_REQ_ID="$4";;
5) V_EMAILID="$5";;
6) V_CONC_PGM_EMAILED="$6";;

esac
V_COUNTER=`expr $V_COUNTER 1`
done

echo "Current Req ID=$V_CURRENT_REQ_ID"
echo $V_EMAILID
echo $V_CONC_PGM_EMAILED

#====================================================== =====
# derive other settings
#====================================================== =====

FILE_IN_BASE=o${V_CURRENT_REQ_ID}.out # output file generated by Application
FILE_ERR_BASE=l${V_CURRENT_REQ_ID}.log # log file generated by Application
FILE_OUT=${APPLCSF}/${APPLOUT} # output file directory
FILE_LOG=${APPLCSF}/${APPLLOG} # log file directory
FILE_IN=${FILE_OUT}/${FILE_IN_BASE}
FILE_ERR=${FILE_LOG}/${FILE_ERR_BASE}

#====================================================== =====
#Email Body
#====================================================== =====
MESSAGE="Hi,

Please find attached the output of the XXX Invoice Validation Report.

Thanks,
ENP-FIN"


#====================================================== =====
#Check for Outfile Validity and existence
#====================================================== =====

DATAFILE_COUNT=`ls -l $FILE_IN | wc -l`

if [ $DATAFILE_COUNT -eq 1 ]
then
echo "One file!"
fi

if [ $DATAFILE_COUNT -lt 1 ]
then
echo "No input file exists"
exit 2
fi

echo ======================================================= ========
echo "Emailing Outfile to $5"
echo ======================================================= ========

(echo "$MESSAGE"; uuencode $FILE_IN Report.txt)| mailx -s "XXX Invoice Validation Report for Request ID:$V_CURRENT_REQ_ID" $V_EMAILID

exit 0
#====================================================== =====

Hope this helps.

Cheers,
Nitin
...
written by Anil Passi , October 25, 2007
Great stuff Nitin, much appreciated.
wonderfull
written by sudarsan , October 25, 2007
wonderfull.......nice code
Thx all :)-
written by Nitin Jain , October 26, 2007
Thx all, i'll see if i can contribute more to this group smilies/kiss.gif
...
written by rahul pardeshi1 , October 28, 2007
Hi,

Thanks, for your help
i just wanted to know how to call the shell script
as a concurrent program in application
Thanks,
Rahul

Thanks,
Rahul.
...
written by Anil Passi , October 28, 2007
Hi Rahul


To run a UNIX shell script as a concurrent program,you need to register this as Host Concurrent Program. Say you register an executable named XXCONC within XXPO Module.

on unix, do

1. cd $XXPO_TOP/bin
2. vi XXCONC.prog and edit and write script and save
3. ln -s $FND_TOP/bin/fndcpesr XXCONC
4. ls -l XXCONC

Also ensure that there is execute permission for executable

Thanks,
Anil Passi
...
written by test , November 15, 2007
how do i load outfile into blob table
How to pass parameters to shell script
written by agadudasu , December 29, 2007
Hi,

I need to pass Email id dynamically to a Shell Script which is an exe file of conc.program.

Thanks,
AJAY
...
written by SrinivasReddy Chilkuri , January 11, 2008
Thx Anil/Santosh,
when executing the procedure (spsendemail)which is used to send the output of concurrent programm,i am getting the error
ORA-29277: invalid SMTP operation
ORA-06512: at "SYS.UTL_SMTP", line 43
ORA-06512: at "SYS.UTL_SMTP", line 279
ORA-06512: at "APPS.PKGSENDMAIL", line 136
ORA-06512: at "APPS.SPSENDEMAIL", line 136
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at line 2

execution statement is:
begin
spsendemail('xxsan','SANTELOO','en','US','TEXT','sriniv asreddi.ch@gmail.com')
end;

**and onemorething i have used this is for not XML reports(reports 6i,concurrent programm).

please help me

Thx
SrinivasReddy



Shell script for email
written by prakaash , January 22, 2008
Thanks Anil. This discussion really helping me.
My question is in shell script written by Nitin .. I have a requirement to send PDF docs(which are out put of one concurrent request) to users through e-mail. Iam using almost same code as listed above and it is working perfectly if the mail recipient is using outlook otherwise if the email id is normal yahoo,gmail the attachments are decoded. Is there any way to make this script to work for even normal yahoo,gmail email-ids.
Thanks
Prakaash
Output of Report 6i via email.
written by Harshita , January 23, 2008
Hi Nitin/Anil,
I need to mail the output of a Concurrent Program. Can you tell me how I can use the shell script given by Nitin ?
If i did so, what are the values of V_CURRENT_REQ_ID, $1, $2 etc ?
Should I first call the Report in the shell script?
Please help.
Thanks,
Harshita
Shell Script to send email with file attachment
written by Gowri , January 30, 2008
Hi Nitin/Anil,

I am using shell script to send .txt file has mail to users.

This '.txt' file has already been generated/created through a Concurrent Program of PL/SQL using UTL File Write Package.Because of which the file has been genearated in ASCII mode.

If i use unnencode command as below:
uuencode $LV_HOLD_LOG_FILENAME | mailx -s "$LV_PROGRAM_NAME" $LV_EMAIL_LIST

to send the file attachment, at the receivers end the file is not in correct format.
Because uuencode, transfers file in Binary mode, but the file is already in ASCII mode.

How to overcome this issue? Thanks in advance.

Regards,
Gowri
In response to Harshita
written by SureshV , February 06, 2008
There are 2 ways on how you can achieve this

1) Write the shell script code to send mail and save it in the bin directory. Create a concurrent program for this shell script.
2) In the after report trigger of your report call the shell script program using fnd_request.submit_request

The other way could be to directly call report from the shell script and after the report is completed use mailx utility to send output file.
need a shell script which saves the report output into a specific location immediately after the execution of the report.
written by dhanunjaya , March 20, 2008
Hi all.
please guide me..i need a shell script which has to save/move the output of a report in to specific location at the end of the concurrent request.

waiting.. smilies/smiley.gif
You must be logged in to a comment. Please register if you do not have an account yet.

busy