1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Email concurrent output in output file type (.pdf,.xls) to a random email address

Discussion in 'Oracle Forms and Reports' started by ac.arijit, Nov 6, 2012.

  1. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,
    I'm new to the forums, I've an issue which I'm not able to resolve. Plz Help!

    I need to send the output(pdf) of a concurrent to a random email address, say "xyz@abc.com". But in the server concurrent output directory($APPLCSF/$APPLOUT) the output file is stored in ".out" format. So I'm not able to find a solution of how to send the output in pdf format.

    Plz Help guyz
     
  2. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Guyz Plz Help me,

    If even i can send an URL of the concurrent output file in the output format, the purpose would be solved. And also its a better way to go as UTL_MAIL has an attachment limit of 32K.

    Guyz Plz Help, don't hesitate or preserve any portion of your thoughts or comments about this topic.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
  4. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Kiran,

    Thanks a lot for the reply and also for the doc.

    I went through the doc, but according to the doc the provided script is able to utilize the UTL_MAIL package to send the report .pdf as an attachment after converting it from the .out file stored in $APPLCSF/$APPLOUT folder before sending the mail.

    PS: /*This procedure is written to send the pos (STD and BLANKET with OSP items POS) of the suppliers
    to their respective mail ids. The first cursor is written to take the pos data which have been
    created on previous day, then submits the program to run PO report for the POS. once the reports
    are submitted, then it checks for the status. If the status is COMPLETED then it uses UTL_MAIL package
    to send the report as an attachment. The report which is stored in .out folder is converted to .pdf
    before sending as an attachment.*/


    Although, I doubt whether it is converting .out to .pdf as attachment going by the below script (Page 17 in doc):

    Code (SQL):

    DECLARE
                       
                           CRLF                   CHAR(2) := CHR(10) || CHR(13);
                           v_utl_filehandler      UTL_FILE.FILE_TYPE;
                           v_rawfile              RAW(32767);
                           v_size                 NUMBER;
                           v_block                NUMBER;
                           v_boolean              BOOLEAN;
                           v_file_dir             varchar2(100);
                           [B]v_file_name            varchar2(100) := 'o' || l_info_table(i).request_id || '.out' ;
                           ----v_file_name        varchar2(100) := 'ICCPO_TEST.pdf';--'ICCACAN55681143.pdf';--'o3330558'||'.out' ;[/B]
                           v_att_filename         varchar2(100);
                       
                        BEGIN
                       
                           [B]v_att_filename := 'Po_Number'||'_'||l_info_table(i).po_number||'.pdf' ; [/B]                
                           v_file_dir := '/proddb_data/applprod/prodcomn/admin/out/PROD_proddb'; --for 11i,report out path set in "utl_file_dir" parameter in dev instance
                           --v_file_dir := '/mnt/uat/obatest/inst/apps/TEST_erpserver/logs/appl/conc/out';   -- for R12 ,report out path set in "utl_file_dir" parameter in dev instance              
                           v_utl_filehandler := UTL_FILE.FOPEN(v_file_dir, [B]v_file_name[/B], 'r');
                           UTL_FILE.fgetattr(v_file_dir, v_file_name, v_boolean, v_size, v_block);
                           dbms_output.PUT_LINE(v_file_name || '--****---' ||v_size ||'--****---'|| v_block);
                           [B]UTL_FILE.get_raw(v_utl_filehandler, v_rawfile, v_size);[/B]
                           UTL_FILE.FCLOSE(v_utl_filehandler);
                           sys.utl_mail.send_attach_raw(   --sender          => v_sender_id
                                                             sender          => 'no.reply@cardindia.com',--'itech@cardindia.com',                                                          
                                                             recipients      => l_info_table(i).email_address                                                                                                                                                          
                                                            ,cc              => l_info_table(i).cc_email_address --'itech@cardindia.com'
                                                            --,bcc             => 'itech@cardindia.com'
                                                            ,subject         => 'Po Number : '|| l_info_table(i).po_number
                                                            ,message         => '<html><body><font size=''2.5'' face="Arial"> '|| CRLF
                                                            || '<br><br>Dear '|| 'Supplier' || ',<br>' || CRLF
                                                            ||'<br>' || CRLF
                                                            || 'Kindly find attached the PO with number '|| l_info_table(i).po_number || '.' || CRLF
                                                            || '<br>In case of any queries, please feel free to contact the Purchage Team.' || CRLF || CRLF
                                                            || '<br><br>Kind Regards,' || CRLF
                                                            || '<br> '|| l_info_table(i).agent_name || '.' || CRLF
                                                            || '<br> ICC </font></body></html>'
                                                            , mime_type       => 'text/html; charset=us-ascii'
                                                            , priority        => 3
                                                            , attachment      =>  v_rawfile
                                                            , att_inline      =>  TRUE --FALSE
                                                            , att_mime_type   => 'application/pdf'
                                                            ,[B] att_filename    =>  v_att_filename --  '1234_MAR2009.pdf'[/B]
                                                            );

     
    If you observe carefully the highlights, i don't see any conversion. Rather i would say the attachment filename which is passed won't be actually existing in the location unless its a XMLP report.

    Please respond quickly, i'm all screwed up!! :confused:
     
  5. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Kiran,
    Thanks a ton for the reply and also for the doc.

    I went through the doc but doubt what it claims by:

    /*This procedure is written to send the pos (STD and BLANKET with OSP items POS) of the suppliers
    to their respective mail ids. The first cursor is written to take the pos data which have been
    created on previous day, then submits the program to run PO report for the POS. once the reports
    are submitted, then it checks for the status. If the status is COMPLETED then it uses UTL_MAIL package
    to send the report as an attachment. The report which is stored in .out folder is converted to .pdf
    before sending as an attachment.*/



    On the contrary when you see the below script on page 17:
    Code (SQL):

    DECLARE
                       
                           CRLF                   CHAR(2) := CHR(10) || CHR(13);
                           v_utl_filehandler      UTL_FILE.FILE_TYPE;
                           v_rawfile              RAW(32767);
                           v_size                 NUMBER;
                           v_block                NUMBER;
                           v_boolean              BOOLEAN;
                           v_file_dir             varchar2(100);
                           [B]v_file_name            varchar2(100) := 'o' || l_info_table(i).request_id || '.out' ;
                           ----v_file_name        varchar2(100) := 'ICCPO_TEST.pdf';--'ICCACAN55681143.pdf';--'o3330558'||'.out' ;[/B]
                           v_att_filename         varchar2(100);
                       
                        BEGIN
                       
                           [B]v_att_filename := 'Po_Number'||'_'||l_info_table(i).po_number||'.pdf' ;  [/B]                
                           v_file_dir := '/proddb_data/applprod/prodcomn/admin/out/PROD_proddb'; --for 11i,report out path set in "utl_file_dir" parameter in dev instance
                           --v_file_dir := '/mnt/uat/obatest/inst/apps/TEST_erpserver/logs/appl/conc/out';   -- for R12 ,report out path set in "utl_file_dir" parameter in dev instance              
                           v_utl_filehandler := UTL_FILE.FOPEN(v_file_dir, [B]v_file_name[/B], 'r');
                           UTL_FILE.fgetattr(v_file_dir, [B]v_file_name[/B], v_boolean, v_size, v_block);
                           dbms_output.PUT_LINE(v_file_name || '--****---' ||v_size ||'--****---'|| v_block);
                           [B]UTL_FILE.get_raw(v_utl_filehandler, v_rawfile, v_size);[/B]
                           UTL_FILE.FCLOSE(v_utl_filehandler);
                           sys.utl_mail.send_attach_raw(   --sender          => v_sender_id
                                                             sender          => 'no.reply@cardindia.com',--'itech@cardindia.com',                                                          
                                                             recipients      => l_info_table(i).email_address                                                                                                                                                          
                                                            ,cc              => l_info_table(i).cc_email_address --'itech@cardindia.com'
                                                            --,bcc             => 'itech@cardindia.com'
                                                            ,subject         => 'Po Number : '|| l_info_table(i).po_number
                                                            ,message         => '<html><body><font size=''2.5'' face="Arial"> '|| CRLF
                                                            || '<br><br>Dear '|| 'Supplier' || ',<br>' || CRLF
                                                            ||'<br>' || CRLF
                                                            || 'Kindly find attached the PO with number '|| l_info_table(i).po_number || '.' || CRLF
                                                            || '<br>In case of any queries, please feel free to contact the Purchage Team.' || CRLF || CRLF
                                                            || '<br><br>Kind Regards,' || CRLF
                                                            || '<br> '|| l_info_table(i).agent_name || '.' || CRLF
                                                            || '<br> ICC </font></body></html>'
                                                            , mime_type       => 'text/html; charset=us-ascii'
                                                            , priority        => 3
                                                            , [B]attachment      =>  v_rawfile[/B]
                                                            , att_inline      =>  TRUE --FALSE
                                                            , att_mime_type   => 'application/pdf'
                                                            , [B]att_filename    =>  v_att_filename[/B] --  '1234_MAR2009.pdf'
                                                            );

     
    As i said earlier, i don't see any conversion of the .out file to .pdf before attaching it to the mail. Rather i would say the attachment filename passed may not exist for non XMLP reports.

    Please respond ASAP and try and sort out this issue alongside me. I'm all screwed up !! :confused:

    Regards
     
  6. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Guyz,

    I resolved the issue somewhat, as now i'm able to send raw attachments bigger than 32K using chunking methodology. But the earlier issue still remains. How do i convert the .out file stored in the server to .pdf for the raw attachment purpose.

    Guyz Plz reply, I'll be really thankful.
     
  7. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Kiran,

    Thanks a ton for the reply and the doc.

    I went through the doc carefully, but a li'l doubtful whether it is actually converting the .out file to .pdf or not as on 'page 17' the script uses the .out file as raw file to read and continues with it, never does it converts to pdf.

    I doubt whether the pdf filename provided in the UTL_MAIL call exists in the location or not for non-XMLP reports. Plz clarify this for me.

    Thanks again.
    Regards
     
  8. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    If that concurrent program doesn't return any document then it doesn't attach any thing to the mail.
     
  9. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Bharat,
    Thanks for the reply

    I've already done that part, the mail is sending the attachment alongwith the text. But what the problem remains is that on completion of the concurrent it saves the output in .out format for non-XMLP reports in $APPLCSF/$APPLOUT, which needs to be then converted to a readable format (.rtf/.pdf/.doc/.xls) before attaching it. This motive unfortunately is not accomplished by the doc mentioned above.

    Plz reply ASAP.
    Thanks again
    Regards
     
  10. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Ari,

    Have you resolved this issue?
     
  11. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Bharat,

    Its still not done completely, i'd say partially. Its quite an old thread, i'm somewhat detached to it now. As far as i can recall it, i was able to email the concurrent output as pdf or xls file for XMLP reports as its published in $APPLCSF/$APPLOUT in the readable format. But for non-XMLP reports i just dunno what to do as its stored in .out format and its not readable. So, right now i can only email the concurrent outputs of XMLP reports.

    If u got anything to offer, or any logical thought at it then Plz share.
     
  12. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Ari,

    Externally we need to think how to convert that document type from .out to .pdf or whatever we need. We need to change whether the concurrent program output to xml and we need to design an template or we need to do some conversion codes at server level.
     
  13. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Bharat,

    Exactly, That's what i've been struggling with for 1 month now. The point is i'm able to get the rdf concurrent outputs in the desired readable format. That's only what i want & its done. I've tried one more thing for this. Running OS command from the API itself to generate the rdf in .pdf format by rwrun command.

    I've utilised the Java Class publishing method as described in metalink through wrapper class. But in the end it got stuck on some DBA related .so shared library file in the OS.

    If u got any optimized idea to resolve it then plz share.
     
  14. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Arijit,

    Did your Problem Solved.
    You can try a Unix Script on the After Report Trigger to convert the .out file into .pdf in the $APPLCSF/$APPLOUT location and then you can try attaching it in your mail which you are doing it.

    Hope this helps you for your problem.

    Cheers
    Kamal(kamal.love@gmail.com)
     
  15. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Kamal,

    Can you please provide the script to do so?
     
  16. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Bharath,

    Its generally out file of a report will be requst_id.out in the $APPLCSF/$APPLOUT

    1. Goto $APPLCSF/$APPLOUT
    2. then do --> cp $APPLCSF/$APPLOUT/request_id.out $APPLCSF/$APPLOUT/request_id.pdf (This unix script should be registered as concurrent program and should be called in the after report trigger in the report).
    3. then call your procedure which attachs the output .pdf in your mail

    Cheers
    Kamal (kamal.love@gmail.com)
     
    Bharat likes this.
  17. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi Bharat,

    Did it work .. ? I just followed this thread.
    And also to Kamal, i would say i tried this script on unix shell but it generated a .pdf file which is not-readable or damaged. I believe this is supposed to be behaving in this way as "cp" is copy command so, i dont think it would be actually converting the data.
     
  18. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Yes Ari... Its not showing all the content and some of the data is getting corrupted. Needs certain Converting program to do so..
     
  19. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi Bharat,

    If tats the case try the Report in XML publisher Template with PDF as output.
    Even tat output will be stored as concurrent_program_short_name_request_id.out.
    and you can follow the same wat u followed before and this will work fine.(as i did the same for one of my PDF afftachment from XML Publisher output).

    Cheers
    Kamal (kamal.love@gmail.com)
     
  20. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Its not much advisable solution to change the entire report. And moreover its complicated to create an template based on that program. Hope we need to convert the data what we are having in .out file.

    If we are going to use that program regularly then it's fine to design XML Report, but if we work often on them, then we need to use some converting program or something else to do so rather than creating XML Report.