E-mailing Report Output to User (Method 2)

To redirect the output of a report, you need to create a database trigger on fnd_concurrent_requests and a PRO*C program.
The PRO*C program is a daemon program which runs in background and continuously monitor the PIPE to see whether there is
something to e-mail.

/*Database Trigger */
CREATE OR REPLACE TRIGGER email_output_trg
  AFTER INSERT OR UPDATE ON fnd_concurrent_requests
  FOR EACH ROW
DECLARE
   l_program_name
            fnd_concurrent_programs.user_concurrent_program_name%TYPE;
   l_mail_string varchar2(200);
   result NUMBER;
   l_print_flag VARCHAR2(1);
BEGIN
  BEGIN
       SELECT  DISTINCT USER_CONCURRENT_PROGRAM_NAME,
                            PRINT_FLAG
        INTO  l_program_name,l_print_flag
        FROM  fnd_concurrent_programs
        WHERE  application_id =
                            :new.program_application_id
         AND  concurrent_program_id =
                            :new.concurrent_program_id ;
  EXCEPTION
  WHEN no_data_found THEN
   null;
  END;
        /* Create the e-mail string to be sent to PRO*C program */
  SELECT 'mail -s '||'"'||l_program_name||'"'||
   ' alok_chadda@usa.net'  || ' < '||
                  :new.outfile_name
  INTO l_mail_string
 FROM dual ;

        /* Check to see whether the Concurrent Program
           Ended successfully */
  IF :new.phase_code = 'C'
  and :new.status_code in ('C','G','I','R')
   and :new.outcome_product = 'FND' THEN
                /* Pack and Send the Message to PRO*C program */
       DBMS_PIPE.PACK_MESSAGE(l_mail_string);
       result :=
                      DBMS_PIPE.SEND_MESSAGE('emailoutputpipe');
                /* Raise Error in case SEND_MESSAG is
                       not successful */
  IF result <> 0 THEN
           RAISE_APPLICATION_ERROR(-2000,'Mailer Error');
       END IF;
  END IF;
END;
/
 
 
 
 
 

EXEC SQL BEGIN DECLARE SECTION;
      int     status;
      char    retval[2000];
 char *uid = "apps/fnd";
EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLCA;

main()
{
     /* Connect to the same schema which created the pipe in
  the database trigger */
     EXEC SQL CONNECT :uid;
     for (;;)
     {
         EXEC SQL EXECUTE
             DECLARE
              typ INTEGER;
          sta INTEGER;
              chr VARCHAR2(200);
             BEGIN
                 chr := '';
          /* Receive the message */
                 sta := dbms_pipe.receive_message('emailoutputpipe');
                 IF sta = 0 THEN
   /* Unpack the message */
                     dbms_pipe.unpack_message(chr);
                 END IF;
                 :status := sta;
                 :retval := chr;
             END;
         END-EXEC;
         if (status == 0)
  system(retval);
  /* The system() call will execute the
   "mail" string which is sent over the pipe */
     }
}

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.