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 */
     }
}

Return to : Oracle Database, SQL, Application, Programming Tips