Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Miscellaneous
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 
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


Anil Passi

Comments   

0 #1 Anil Passi 2007-10-24 05:54
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
www.google.com/search?q=site:apps2fusion.com+send-binary-attachments-with-notifications-in-workflow

Thanks,
Anil Passi
Quote
0 #2 rahul pardeshi1 2007-10-24 17:04
Hi
how to load the output of the Concurrent Request into a BLOB type
is it stored somewhere in the database
Thanks ,
Rahul.
Quote
0 #3 Anil Passi 2007-10-24 19:14
Hi Rahul

The file will be in $APPLCSF/$APPLO UT
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
Quote
0 #4 Abdul Sami Mohammed 2007-10-25 03:27
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 ;D 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
Quote
0 #5 Abdul Sami Mohammed 2007-10-25 03:34
Hi all,

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

#==== =============== =============== =============== ==========
echo "XXFND_EMAIL_RP T.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_EMAI LED="$6";;

esa c
V_COUNTER=`ex pr $V_COUNTER 1`
done

echo "Current Req ID=$V_CURRENT_R EQ_ID"
echo $V_EMAILID
echo $V_CONC_PGM_EMA ILED

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

FILE _IN_BASE=o$ 3;V_CURRENT_REQ _ID}.out # output file generated by Application
FIL E_ERR_BASE=l$&# 123;V_CURRENT_R EQ_ID}.log # log file generated by Application
FIL E_OUT=${AP PLCSF}/$&# 123;APPLOUT 5; # output file directory
FILE_ LOG=${APPL CSF}/$ 3;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
#==== =============== =============== =============== ==========

DAT AFILE_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_R EQ_ID" $V_EMAILID

exi t 0
#============ =============== =============== =============== ==

Hope this helps.

Cheers,
Nitin
Quote
0 #6 Anil Passi 2007-10-25 06:14
Great stuff Nitin, much appreciated.
Quote
0 #7 sudarsan 2007-10-25 12:49
wonderfull..... ..nice code
Quote
0 #8 Abdul Sami Mohammed 2007-10-26 00:54
Thx all, i'll see if i can contribute more to this group :-*
Quote
0 #9 rahul pardeshi1 2007-10-28 18:02
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.
Quote
0 #10 Anil Passi 2007-10-28 19:08
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/fn dcpesr XXCONC
4. ls -l XXCONC

Also ensure that there is execute permission for executable

Thanks,
Anil Passi
Quote
0 #11 test 2007-11-15 19:58
how do i load outfile into blob table
Quote
0 #12 agadudasu 2007-12-29 11:52
Hi,

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

Thanks,
AJAY
Quote
0 #13 Muddu Krishnama Naidu 2008-01-11 03:11
Thx Anil/Santosh,
w hen executing the procedure (spsendemail)wh ich is used to send the output of concurrent programm,i am getting the error
ORA-29277 : invalid SMTP operation
ORA-0 6512: at "SYS.UTL_SMTP", line 43
ORA-06512: at "SYS.UTL_SMTP", line 279
ORA-06512: at "APPS.PKGSENDMA IL", line 136
ORA-06512: at "APPS.SPSENDEMA IL", line 136
ORA-29278: SMTP transient error: 421 Service not available
ORA-0 6512: at line 2

execution statement is:
begin
spsendemail('xxsan','SANTELOO','en','US','TEXT','')
end;

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

ple ase help me

Thx
SrinivasReddy
Quote
0 #14 prakaash 2008-01-22 20:47
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.
Than ks
Prakaash
Quote
0 #15 Harshita 2008-01-23 01:38
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_I D, $1, $2 etc ?
Should I first call the Report in the shell script?
Please help.
Thanks,
Harshi ta
Quote
0 #16 Gowri 2008-01-30 11:53
Hi Nitin/Anil,

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

This '.txt' file has already been generated/creat ed 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_FI LENAME | mailx -s "$LV_PROGRAM_NA ME" $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.

Regar ds,
Gowri
Quote
0 #17 SureshV 2008-02-06 20:04
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.sub mit_request

Th e 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.
Quote
0 #18 dhanunjaya 2008-03-20 14:29
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.

waiti ng.. :)
Quote
0 #19 siva.n 2008-11-25 02:03
Hai Santhosh,

I am having an requirement that to send the output of a concurrent request(pdf report file) to vendor's email. I have gone through the material send by you, but i need to pass parameters to the pdf report through srs window.. how to do this? plz clarify me and also send ur email id to contact

Regard s,
Siva.n
Quote
0 #20 Ajith 2008-12-11 06:30
Hi Anil,

In one of the posts above, you mentioned the file in one UNIX server location can be loaded in database blob. From there we can use it to be sent as an attachment in workflow notification.
C an you please provide me the sample code that loads the file in database blob.

Thanks.

Ajith
Quote
0 #21 Jithen1 2009-07-09 02:13
Hi Anil,
I am a little bit confused where to post a question and my question is,
How to retriev the last updated records when we updated suppose 5 records
and i want display that 5 last updated records.
Please reply my email id untill i get familiar to this site.


Thanks,
Jithen.
Quote
0 #22 Ted Cain 2009-07-30 14:27
Hi,

In May of 2990, I used your sample code to get a .pdf document emailed from the concurrent manager, It was working fine, I lost the code due to an instance refresh and rebuilt the package and procedure, I get the email sending but lost the attachment, is there a mime_type or something that I must have changed in May?

Thank you again,

Ted Cain 262-893-4881
Quote
0 #23 Ted Cain 2009-07-31 15:16
Hi Santosh,

Below is the syntax of my stored procedure. It is running the concurrent job, the output file is created, the email is sent, however, the body text and the attachment are not included in the email.

Can you help?

Thank you,

Ted Cain
Quote
0 #24 Ted Cain 2009-07-31 15:18
create or replace
PROCEDU RE lic_spsendemail (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2) IS

vdirectory varchar2(200) := 'CONC_MGR_OUT' ;
l_app_name varchar2(200) := 'XBOL' ;
l_prog_desc varchar2(200) := 'COA to be emailed' ;
l_prog_short varchar2(200) := 'COACLO' ;
l_printer_nam e varchar2(200) := 'milit2' ;
l_print_style varchar2(200) := 'PDF Publisher' ;
l_language varchar2(200) := 'en' ;
l_territory varchar2(200) := 'US' ;
l_format varchar2(200) := 'PDF' ;
l_template_ap p varchar2(200) := 'XBOL';
l_templ ate_code varchar2(200) := 'COACLO';
l_email_to varchar2(200) := ';' ;
l_start_when varchar2(200) := to_char(sysdate ,'DD-MON-YYYY') ;
l_copies varchar2(1) := 1 ;
l_item varchar2(200) := '956' ;
l_lot varchar2(200) := 'D71715BB' ;
l_order varchar2(200) := '245212' ;
l_space varchar2(1) := '' ;
l_two varchar2(1) := '2' ;
l_zero varchar2(1) := '0' ;
l_null varchar2(200) := null ;
l_false boolean := FALSE ;
l_result boolean ;
y number ;
vfile bfile;
vfilelen pls_integer;
vm axlinewidth pls_integer := 54;
vbuffer raw(2100);
vamt binary_integer := 672;
vfilepos pls_integer := 1;
vconn utl_smtp.CONNEC TION;
vdata raw(2100);
vchu nks pls_integer;
vm odulo pls_integer;
vp ieces pls_integer;
vm imetypebin VARCHAR2(30) := 'application/pd f';
vrequestid NUMBER(20);
vfl ag1 boolean;
vflag2 boolean;
l_prin t_stuff1 boolean ;
l_print_stuff 2 boolean ;
vreqphase VARCHAR2(50);
v reqstatus VARCHAR2(50);
v reqdevphase VARCHAR2(50);
v reqdevstatus VARCHAR2(50);
v reqmessage VARCHAR2(50);

BEGIN
Quote
0 #25 Ted Cain 2009-07-31 15:20
BEGIN

--Make Oracle think this is run from within Oracle Apps
fnd_global.apps _initialize(355 0, 21623, 660);

--Set Print Options
l_print_stuff1 := fnd_request.set _print_options (l_printer_name , l_print_style, l_copies, TRUE, 'N');
IF(l_print_stuf f1) THEN
dbms_output.put _line( 'Print options set');
ELSE
dbms_output.put _line( 'Print options NOT set');
END IF;

--Add printer to request
l_print_stuff2 := fnd_request.add _printer (l_printer_name , 1);
IF(l_print_stuf f2) THEN
dbms_output.put _line( 'Printer added');
ELSE
dbms_output.put _line( 'Printer NOT added');
END IF;

--Add layout to the XMLP report
vflag1 := fnd_request.add _layout (l_template_app ,l_template_cod e,l_language,l_ territory,l_for mat);
IF(vflag1) THEN
dbms_output.put _line( 'The layout has been submitted');
ELSE
dbms_output.put _line( 'The layout has not been submitted');
END IF;

--submit the XMLP report request
vrequestid := fnd_request.sub mit_request(l_a pp_name,l_prog_ short,l_prog_de sc,l_null,FALSE ,l_order,l_item ,l_lot);
COMMIT;
IF vrequestid = 0 THEN
dbms_output.put _line( 'Request Not Submitted due to "' || fnd_message.GET || '".');
ELSE
dbms_output.put _line( 'The Concurrent Request Id is: ' || vrequestid);
END IF;

--Wait for the request to complete successfully
vflag2 := fnd_concurrent. wait_for_reques t(request_id => vrequestid, phase => vreqphase, status => vreqstatus, dev_phase => vreqdevphase, dev_status => vreqdevstatus, message => vreqmessage);
COMMIT;

IF vreqdevphase = 'COMPLETE' THEN --AND vreqdevstatus = 'NORMAL') THEN
Quote
0 #26 Ted Cain 2009-07-31 15:20
IF vreqdevphase = 'COMPLETE' THEN --AND vreqdevstatus = 'NORMAL') THEN
BEGIN
--Begin to email the report
BEGIN
vconn := lic_sendmail.fn beginmail(sende r => '', recipients => l_email_to, subject => 'Concurrent Request# '||to_char(vreq uestid)||' from TLSFRI', mime_type => lic_sendmail.vm ultipartmimetyp e);
END ;
BEGIN
lic_sendmail.sp attachtext(CONN => vconn, data => 'Check out the blank COA for testing', mime_type => 'text/html');
END ;
BEGIN
lic_sendmail.sp beginattachment (CONN => vconn, mime_type => vmimetypebin, inline => FALSE, filename => l_prog_short || '_' || vrequestid || '_1.' || l_format, transfer_enc => 'base64');
BEGIN
vfile := bfilename(vdire ctory, l_prog_short || '_' || vrequestid || '_1.' || l_format);
vfilelen := dbms_lob.getlen gth(vfile);
vmodulo := MOD(vfilelen, vamt);
vpieces := TRUNC(vfilelen / vamt);
IF(vmodulo 0) THEN
vpieces := vpieces + 1;
END IF;
dbms_lob.fileop en(vfile, dbms_lob.file_r eadonly);
dbms_lob.READ(v file, vamt, vfilepos, vbuffer);
vdata := NULL;
FOR i IN 1 .. vpieces LOOP
vfilepos := i *vamt + 1;
vfilelen := vfilelen -vamt;
vdata := utl_raw.CONCAT( vdata, vbuffer);
vchunks := TRUNC(utl_raw.L ENGTH(vdata) / vmaxlinewidth);
IF(i vpieces) THEN
vchunks := vchunks -1;
END IF;
lic_sendmail.sp writeraw(CONN => vconn, message => utl_encode.base 64_encode(vdata ));
vdata := NULL;
IF(vfilelen < vamt AND vfilelen > 0) THEN
vamt := vfilelen;
END IF;
dbms_lob.READ(v file, vamt, vfilepos, vbuffer);
END LOOP;
END;
dbms_lob.filecl ose(vfile);
lic_sendmail.sp endattachment(C ONN => vconn);
END spbeginattachme nt;
lic_sendmail.sp endmail(CONN => vconn);
EXCEPTION
WHEN no_data_found THEN
lic_sendmail.sp endattachment(C ONN => vconn);
dbms_lob.filecl ose(vfile);
WHEN others THEN
lic_sendmail.sp endattachment(C ONN => vconn);
dbms_output.put _line(SQLCODE || '-' || sqlerrm);
dbms_lob.filecl ose(vfile);
END;
ELSE
dbms_output.put _line(SQLCODE || '-' || sqlerrm);
END IF;
END lic_spsendemail ;
Quote
0 #27 Azam 2010-09-30 06:22
When i run the concurrent program the output is generated on Application Server. When i execute the send email procedure its unable to read the file since appl server is residing on a different maching other than database. Please let me know how i can solve this issue. while defining a BFILE locator i can give the machine other that the batabase machine.

Thank s,

Azam
Quote
0 #28 Chris Alexander 2010-10-06 16:13
Hi,

Steps to be set up in the front end to send the output of the concurrent program to the specified path on the unix box automatically?

Can you please let me know?

Thanks,
Quote
0 #29 Rahul Mangla 2014-12-18 13:25
Hi Anil,

How can we embed a hyperlink in Requisition approval notification page?

This hyperlink will point to a static folder on File server.

Can you please let me know?

Thanks,
Rahul
Quote
0 #30 카지노 2021-07-03 22:12
Amazing issues here. I'm very happy to see your post.
Thanks a lot and I am taking a look forward to
touch you. Will you please drop me a e-mail?
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner