To Send Concuurent Program Output as an E-mail

CREATE OR REPLACE PROCEDURE apps.erp_send_email




(



errbuf VARCHAR2,



retode NUMBER,



p_concurrent_program_name VARCHAR2,



p_parameter1 NUMBER



)



IS



/*Variable declaration*/



fhandle UTL_FILE.file_type;



vtextout VARCHAR2 (32000);



text VARCHAR2 (32000);



v_request_id NUMBER := NULL;



v_request_status BOOLEAN;



v_phase VARCHAR2 (2000);



v_wait_status VARCHAR2 (2000);



v_dev_phase VARCHAR2 (2000);



v_dev_status VARCHAR2 (2000);



v_message VARCHAR2 (2000);



v_application_id NUMBER;



v_concurrent_program_id NUMBER;



v_conc_prog_short_name VARCHAR2 (100);



v_conc_prog_appl_short_name VARCHAR2 (100);



v_output_file_path VARCHAR2 (200);



BEGIN



fnd_file.put_line (fnd_file.output, '------------------------------------------------------' );



fnd_file.put_line (fnd_file.output, 'Conc Prog: '

p_concurrent_program_name );



fnd_file.put_line (fnd_file.output, 'Parameter 1:'

p_parameter1 );



BEGIN



/* Get Concurrent_program_id of the desired program and application_id */



SELECT concurrent_program_id, application_id



INTO v_concurrent_program_id, v_application_id



FROM fnd_concurrent_programs_tl



WHERE user_concurrent_program_name = p_concurrent_program_name;



fnd_file.put_line (fnd_file.LOG,'Conc Prog ID:'

v_concurrent_program_id );



fnd_file.put_line (fnd_file.LOG, 'Application ID: '

v_application_id );



/* Get the program’s Short name */



SELECT concurrent_program_name



INTO v_conc_prog_short_name



FROM fnd_concurrent_programs



WHERE concurrent_program_id = v_concurrent_program_id;



fnd_file.put_line (fnd_file.LOG,'Conc Prog Short Name:'

v_conc_prog_short_name );



/* Get the Application Short name */



SELECT application_short_name



INTO v_conc_prog_appl_short_name



FROM fnd_application



WHERE application_id = v_application_id;



fnd_file.put_line (fnd_file.LOG,'Application Short Name:'

v_conc_prog_appl_short_name );



EXCEPTION



WHEN OTHERS THEN



fnd_file.put_line (fnd_file.LOG, 'Error: '

SQLERRM);



END;



/* Calling fnd_request.submit_request to submit the desired the concurrent program*/



v_request_id:= fnd_request.submit_request



(v_conc_prog_appl_short_name,



v_conc_prog_short_name,



NULL, --Description



NULL, --Time to start the program



FALSE, -- sub program



p_parameter1



);



fnd_file.put_line (fnd_file.LOG,'Concurrent Request Submitted Successfully: '

v_request_id );



COMMIT;



IF v_request_id IS NOT NULL THEN



/*Calling fnd_concurrent.wait_for_request to wait for the program to complete */



v_request_status:= fnd_concurrent.wait_for_request



(



request_id => v_request_id,



INTERVAL => 10,



max_wait => 0,



phase => v_phase,



status => v_wait_status,



dev_phase => v_dev_phase,



dev_status => v_dev_status,



MESSAGE => v_message



);



v_dev_phase := NULL;



v_dev_status := NULL;



END IF;



/* Getting the path where output file of the program is created */



SELECT outfile_name



INTO v_output_file_path



FROM fnd_concurrent_requests



WHERE request_id = v_request_id;



/* Open the output file in Read mode */



fhandle := UTL_FILE.fopen ('/cdv01/oracle/cdvappl/xxcofidata/1.0.0/outbound','o'

v_request_id

'.out', ‘r');



IF UTL_FILE.is_open (fhandle) THEN



DBMS_OUTPUT.put_line ('File read open');



ELSE



DBMS_OUTPUT.put_line ('File read not open');



END IF;



/* Get the contents of the file into variable “text”*/



LOOP



BEGIN



UTL_FILE.get_line (fhandle, vtextout);



text := text

vtextout

UTL_TCP.crlf;



EXCEPTION



WHEN NO_DATA_FOUND THEN



EXIT;



END;



END LOOP;



UTL_FILE.fclose (fhandle);



/*Calling UTL_MAIL.send_attach_varchar2 to send the output as Email attachment */



UTL_MAIL.send_attach_varchar2



(



sender => 'chandrasekhar_k@in.ibm.com',



recipients => 'pramchand506@gmail.com',



subject => 'Testmail',



MESSAGE => 'Hello',



attachment => text,



att_inline => FALSE



);



END;



/