OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » CONCSUB send output file with workflow mailer.

Home » Applications » Oracle Fusion Apps & E-Business Suite » CONCSUB send output file with workflow mailer.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CONCSUB send output file with workflow mailer. [message #228247]

Mon, 02 April 2007 04:24

AngeArael
Messages: 1
Registered: April 2007
Location: Bordeaux (France)

Junior Member

Hello,

First excuse my english i'm french! Embarassed

In E-business, when you launch a request, in the forms with the parameters of the concurrent program, there are options concerning the completion of the request, and especially, you can notify a user who will receive the output file of the request by mail (if he configured his oracle well). It is very interesting for me, but now i need to launch my request with concsub, i search in the Metalink, but i found nothing concerning this option. so my question is : Is it possible with concsub to notify user after a request completion as it's possible to do directly in e-business.

thank you very much by advance and excuse me for my english... Razz

Report message to a moderator

Re: CONCSUB send output file with workflow mailer. [message #233366 is a reply to message #228247]

Wed, 25 April 2007 17:21

rferna
Messages: 2
Registered: April 2007

Junior Member

TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: Is It Possible to Use the Completion Options With a CONCSUB Command and/or To Execute a Request Set?
Doc ID: Note:150980.1 Type: HOWTO
Last Revision Date: 07-JUN-2002 Status: PUBLISHED

goal: Is it possible to use the Completion Options with a CONCSUB command
and/or to execute a request set?
fact: Oracle Application Object Library

fix:

NOTE: In Oracle Applications it is possible to use the Completion Options to
send a notification to a user when the program is completed. This is standard
functionality when submitting a concurrent program.
.
But is it possible to use this functionality when executing the concurrent
program via the CONCSUB command on the Unix side? If not, is it possible to
use the CONCSUB command to execute a request set and then register the program
in the request set with the Completion Options?

CONCSUB is only for individual requests, not request sets. As far as
completion options these are for use within Oracle Applications. CONCSUB
doesn't have provision for this. Check the developer guide and try using
Application Program Interfaces (API) to create custom submission routines.

Thanks,

Richard Fernandez

Director - Finance Applications

Answerthink, Inc.

Report message to a moderator

Re: CONCSUB send output file with workflow mailer. [message #272556 is a reply to message #228247]

Fri, 05 October 2007 09:07

Muthupan
Messages: 1
Registered: October 2007

Junior Member

Hi,

send concurrent request output to user using notify function in option when submitting request. However, this is only sending it as a notification with the URL, is there anyway to setup it up to send the .out file as attachment

Is there is any Metalink notes or document to help me out

Thanks in advance

Regards
Muthu

Report message to a moderator

Re: CONCSUB send output file with workflow mailer. [message #273003 is a reply to message #272556]

Mon, 08 October 2007 10:17

rferna
Messages: 2
Registered: April 2007

Junior Member

Unfortunately the only way I have accomplished your request is using a a wrapper PL/SQL program that submits the report using SUBMIT_REQUEST waiting for the request to complete using the request

Below is a sample of the code..this is only a portion you will need to added your specific logic. The second part attaches the output and sends it via smpt which needs to be configured on your box.

l_request_id := fnd_request.submit_request
('XXANSR', --application IN VARCHAR2 DEFAULT NULL
'PADETAIL', --program IN VARCHAR2 DEFAULT NULL
NULL, --description IN VARCHAR2 DEFAULT NULL
NULL, --start_time IN VARCHAR2 DEFAULT NULL
FALSE, --sub_request IN BOOLEAN DEFAULT FALSE
p_gl_period_name, --argument1 -- GL PERIOD
p_project_number, --argument2 -- Project Number
p_pa_org_name --argument3 -- Organization);

COMMIT;

-- Wait the concurrent program finish
IF l_request_id = 0
THEN
l_stage := 'Fail to submit Project Status';
print_debug (p_debug_flag, l_stage);
RAISE excp_fatal_error;
ELSIF NOT fnd_concurrent.wait_for_request
(l_request_id, -- request_id
30, -- interval
0, -- max_time
l_phase, -- phase OUT
l_status, -- status OUT
l_dev_phase, -- dev_phase OUT
l_dev_status,
-- dev_status OUT
l_message -- message OUT
)
THEN
l_stage := 'Error on submit Project Status';
print_debug (p_debug_flag, l_stage);
RAISE excp_fatal_error;
END IF;

IF l_dev_status = 'ERROR'
THEN
l_stage := 'Concurrent program finished with status : ' || l_dev_status;
RAISE excp_fatal_error;
END IF;

Emailing section:
--==========================================================
-- Send attachments greater than 32KB using SMTP mail
--==========================================================
PROCEDURE send (
p_pdffilename IN VARCHAR2,
p_txtfilename IN VARCHAR2,
p_sender IN VARCHAR,
p_recipients IN VARCHAR2,
p_subject IN VARCHAR2,
p_message IN VARCHAR2 DEFAULT NULL
)
IS
/********************************************************************************************
-- Usage Xxansr_Mail.send ('5174-006.pdf',
-- 'textfile.txt',
-- '<sender@email.com>',
-- '<receiver1@email.com>,<receiver2@email.com>',
-- 'email suject text',
-- 'anything you want toappear in the body ofthe message'
-- );
********************************************************************************************/
fil BFILE;
file_len PLS_INTEGER;
max_line_width PLS_INTEGER := 54;
buf RAW (2100);
amt BINARY_INTEGER := 672 * 3;
/* ensures proper format; 2016 */
pos PLS_INTEGER := 1;
/* pointer for each piece */
filepos PLS_INTEGER := 1; /* pointer for the file */
v_file_handle UTL_FILE.file_type;
v_directory_name VARCHAR2 (100) := 'OUTPUT_DIR';
v_line VARCHAR2 (1000);
conn UTL_SMTP.connection;
mesg VARCHAR2 (32767);
mesg_len NUMBER;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
DATA RAW (2100);
chunks PLS_INTEGER;
len PLS_INTEGER := 1;
modulo PLS_INTEGER;
pieces PLS_INTEGER;
err_num NUMBER;
err_msg VARCHAR2 (100);
v_mime_type_bin VARCHAR2 (30) := 'application/pdf';
BEGIN
BEGIN
conn :=
xxansr_mail.begin_mail
(sender => p_sender,
recipients => p_recipients,
subject => p_subject,
mime_type => xxansr_mail.multipart_mime_type
);
END begin_mail;

BEGIN
xxansr_mail.attach_text (conn => conn,
DATA => '<h1>'
|| p_message
|| '</h1>'
|| crlf
|| crlf,
mime_type => 'text/html'
);
END attach_text;

-- BEGIN
-- -- Here I am attaching text files
-- xxansr_mail.begin_attachment (conn => conn,
-- mime_type => 'text/plain',
-- inline => TRUE,
-- filename => p_txtfilename,
-- transfer_enc => '7 bit'
-- );
--
-- BEGIN
-- v_file_handle :=
-- UTL_FILE.fopen (v_directory_name, p_txtfilename, 'r');
--
-- LOOP
-- UTL_FILE.get_line (v_file_handle, v_line);
-- mesg := v_line || crlf;
-- xxansr_mail.write_text (conn => conn, MESSAGE => mesg);
-- END LOOP;
-- EXCEPTION
-- WHEN OTHERS
-- THEN
-- NULL;
-- END;
--
-- UTL_FILE.fclose (v_file_handle);
-- xxansr_mail.end_attachment (conn => conn);
-- END begin_attachment;
BEGIN
--This attaches the pdf file
xxansr_mail.begin_attachment (conn => conn,
mime_type => v_mime_type_bin,
inline => TRUE,
filename => p_pdffilename,
transfer_enc => 'base64'
);

BEGIN
fil :=
BFILENAME ('OUTPUT_DIR', p_pdffilename);
file_len := DBMS_LOB.getlength (fil);
modulo := MOD (file_len, amt);
pieces := TRUNC (file_len / amt);

IF (modulo <> 0)
THEN
pieces := pieces + 1;
END IF;

DBMS_LOB.fileopen (fil, DBMS_LOB.file_readonly);
DBMS_LOB.READ (fil, amt, filepos, buf);
DATA := NULL;

FOR i IN 1 .. pieces
LOOP
filepos := i * amt + 1;
file_len := file_len - amt;
DATA := UTL_RAW.CONCAT (DATA, buf);
chunks :=
TRUNC (UTL_RAW.LENGTH (DATA) / max_line_width);

IF (i <> pieces)
THEN
chunks := chunks - 1;
END IF;

xxansr_mail.write_raw
(conn => conn,
MESSAGE => UTL_ENCODE.base64_encode
(DATA)
);
DATA := NULL;

IF (file_len < amt AND file_len > 0)
THEN
amt := file_len;
END IF;

DBMS_LOB.READ (fil, amt, filepos, buf);
END LOOP;
END;

DBMS_LOB.fileclose (fil);
xxansr_mail.end_attachment (conn => conn);
END begin_attachment;

xxansr_mail.end_mail (conn => conn);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
xxansr_mail.end_attachment (conn => conn);
DBMS_LOB.fileclose (fil);
WHEN OTHERS
THEN
xxansr_mail.end_attachment (conn => conn);
err_num := SQLCODE;
err_msg := SUBSTR (SQLERRM, 1, 100);
DBMS_OUTPUT.put_line ('Error number is ' || err_num);
DBMS_OUTPUT.put_line ('Error message is ' || err_msg);
DBMS_LOB.fileclose (fil);
END send;

Email Program:
CREATE OR REPLACE PACKAGE Xxansr_Mail
IS
smtp_host VARCHAR2 (256) := '127.0.0.1';
smtp_port PLS_INTEGER := 25;
smtp_domain VARCHAR2 (256) := 'answerthink.com';
mailer_id CONSTANT VARCHAR2 (256)
:= 'Mailer by ANSR UTL_SMTP';
boundary CONSTANT VARCHAR2 (256)
:= '-----7D81B75CCC90D2974F7A1CBD';
first_boundary CONSTANT VARCHAR2 (256)
:= '--' || boundary || UTL_TCP.crlf;
last_boundary CONSTANT VARCHAR2 (256)
:= '--' || boundary || '--' || UTL_TCP.crlf;
multipart_mime_type CONSTANT VARCHAR2 (256)
:= 'multipart/mixed; boundary="' || boundary || '"';
max_base64_line_width CONSTANT PLS_INTEGER := 76 / 4 * 3;

PROCEDURE mail (
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
MESSAGE IN VARCHAR2
);

FUNCTION begin_mail (
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL
)
RETURN UTL_SMTP.connection;

PROCEDURE write_text (
conn IN OUT NOCOPY UTL_SMTP.connection,
MESSAGE IN VARCHAR2
);

PROCEDURE write_mb_text (
conn IN OUT NOCOPY UTL_SMTP.connection,
MESSAGE IN VARCHAR2
);

PROCEDURE write_raw (conn IN OUT NOCOPY UTL_SMTP.connection, MESSAGE IN RAW);

PROCEDURE attach_text (
conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE
);

PROCEDURE attach_mb_text (
conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE
);

PROCEDURE attach_base64 (
conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE
);

PROCEDURE begin_attachment (
conn IN OUT NOCOPY UTL_SMTP.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL
);

PROCEDURE end_attachment (
conn IN OUT NOCOPY UTL_SMTP.connection,
LAST IN BOOLEAN DEFAULT FALSE
);

PROCEDURE end_mail (conn IN OUT NOCOPY UTL_SMTP.connection);

FUNCTION begin_session
RETURN UTL_SMTP.connection;

PROCEDURE begin_mail_in_session (
conn IN OUT NOCOPY UTL_SMTP.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL
);

PROCEDURE end_mail_in_session (conn IN OUT NOCOPY UTL_SMTP.connection);

PROCEDURE end_session (conn IN OUT NOCOPY UTL_SMTP.connection);

END Xxansr_Mail;
/

CREATE OR REPLACE PACKAGE BODY Xxansr_Mail
IS
FUNCTION get_address (addr_list IN OUT VARCHAR2)
RETURN VARCHAR2
IS
addr VARCHAR2 (256);
i PLS_INTEGER;

FUNCTION lookup_unquoted_char (str IN VARCHAR2, chrs IN VARCHAR2)
RETURN PLS_INTEGER
AS
c VARCHAR2 (5);
i PLS_INTEGER;
len PLS_INTEGER;
inside_quote BOOLEAN;
BEGIN
inside_quote := FALSE;
i := 1;
len := LENGTH (str);

WHILE (i <= len)
LOOP
c := SUBSTR (str, i, 1);

IF (inside_quote)
THEN
IF (c = '"')
THEN
inside_quote := FALSE;
ELSIF (c = '\')
THEN
i := i + 1; -- Skip the quote character
END IF;
END IF;

IF (c = '"')
THEN
inside_quote := TRUE;
END IF;

IF (INSTR (chrs, c) >= 1)
THEN
RETURN i;
END IF;

i := i + 1;
END LOOP;

RETURN 0;
END;
BEGIN
addr_list := LTRIM (addr_list);
i := lookup_unquoted_char (addr_list, ',;');

IF (i >= 1)
THEN
addr := SUBSTR (addr_list, 1, i - 1);
addr_list := SUBSTR (addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;

i := lookup_unquoted_char (addr, '<');

IF (i >= 1)
THEN
addr := SUBSTR (addr, i + 1);
i := INSTR (addr, '>');

IF (i >= 1)
THEN
addr := SUBSTR (addr, 1, i - 1);
END IF;
END IF;

RETURN addr;
END;

PROCEDURE write_mime_header (
conn IN OUT NOCOPY UTL_SMTP.connection,
NAME IN VARCHAR2,
VALUE IN VARCHAR2
)
IS
BEGIN
UTL_SMTP.write_raw_data (conn,
UTL_RAW.cast_to_raw ( NAME
|| ': '
|| VALUE
|| UTL_TCP.crlf
)
);
END;

PROCEDURE write_boundary (
conn IN OUT NOCOPY UTL_SMTP.connection,
LAST IN BOOLEAN DEFAULT FALSE
)
AS
BEGIN
IF (LAST)
THEN
UTL_SMTP.write_data (conn, last_boundary);
ELSE
UTL_SMTP.write_data (conn, first_boundary);
END IF;
END;

PROCEDURE mail (
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
MESSAGE IN VARCHAR2
)
IS
conn UTL_SMTP.connection;
BEGIN
conn := begin_mail (sender, recipients, subject);
write_text (conn, MESSAGE);
end_mail (conn);
END;

FUNCTION begin_mail (
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL
)
RETURN UTL_SMTP.connection
IS
conn UTL_SMTP.connection;
BEGIN
conn := begin_session;
begin_mail_in_session (conn,
sender,
recipients,
subject,
mime_type,
priority
);
RETURN conn;
END;

PROCEDURE write_text (
conn IN OUT NOCOPY UTL_SMTP.connection,
MESSAGE IN VARCHAR2
)
IS
BEGIN
UTL_SMTP.write_data (conn, MESSAGE);
END;

PROCEDURE write_mb_text (
conn IN OUT NOCOPY UTL_SMTP.connection,
MESSAGE IN VARCHAR2
)
IS
BEGIN
UTL_SMTP.write_raw_data (conn, UTL_RAW.cast_to_raw (MESSAGE));
END;

PROCEDURE write_raw (conn IN OUT NOCOPY UTL_SMTP.connection, MESSAGE IN RAW)
IS
BEGIN
UTL_SMTP.write_raw_data (conn, MESSAGE);
END;

PROCEDURE attach_text (
conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
begin_attachment (conn, mime_type, inline, filename);
write_text (conn, DATA);
end_attachment (conn, LAST);
END;

PROCEDURE attach_mb_text (
conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
begin_attachment (conn, mime_type, inline, filename);
UTL_SMTP.write_raw_data (conn, UTL_RAW.cast_to_raw (DATA));
end_attachment (conn, LAST);
END;

PROCEDURE attach_base64 (
conn IN OUT NOCOPY UTL_SMTP.connection,
DATA IN RAW,
mime_type IN VARCHAR2 DEFAULT 'application/octet',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
LAST IN BOOLEAN DEFAULT FALSE
)
IS
i PLS_INTEGER;
len PLS_INTEGER;
BEGIN
begin_attachment (conn, mime_type, inline, filename, 'base64');
i := 1;
len := UTL_RAW.LENGTH (DATA);

WHILE (i < len)
LOOP
IF (i + max_base64_line_width < len)
THEN
UTL_SMTP.write_raw_data
(conn,
UTL_ENCODE.base64_encode
(UTL_RAW.SUBSTR (DATA,
i,
max_base64_line_width
)
)
);
ELSE
UTL_SMTP.write_raw_data
(conn,
UTL_ENCODE.base64_encode (UTL_RAW.SUBSTR (DATA,
i
)
)
);
END IF;

UTL_SMTP.write_data (conn, UTL_TCP.crlf);
i := i + max_base64_line_width;
END LOOP;

end_attachment (conn, LAST);
END;

PROCEDURE begin_attachment (
conn IN OUT NOCOPY UTL_SMTP.connection,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
inline IN BOOLEAN DEFAULT TRUE,
filename IN VARCHAR2 DEFAULT NULL,
transfer_enc IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
write_boundary (conn);
write_mime_header (conn, 'Content-Type', mime_type);

IF (filename IS NOT NULL)
THEN
IF (inline)
THEN
write_mime_header (conn,
'Content-Disposition',
'inline; filename="' || filename || '"'
);
ELSE
write_mime_header (conn,
'Content-Disposition',
'attachment; filename="' || filename || '"'
);
END IF;
END IF;

IF (transfer_enc IS NOT NULL)
THEN
write_mime_header (conn, 'Content-Transfer-Encoding', transfer_enc);
END IF;

UTL_SMTP.write_data (conn, UTL_TCP.crlf);
END;

PROCEDURE end_attachment (
conn IN OUT NOCOPY UTL_SMTP.connection,
LAST IN BOOLEAN DEFAULT FALSE
)
IS
BEGIN
UTL_SMTP.write_data (conn, UTL_TCP.crlf);

IF (LAST)
THEN
write_boundary (conn, LAST);
END IF;
END;

PROCEDURE end_mail (conn IN OUT NOCOPY UTL_SMTP.connection)
IS
BEGIN
end_mail_in_session (conn);
end_session (conn);
END;

FUNCTION begin_session
RETURN UTL_SMTP.connection
IS
conn UTL_SMTP.connection;
BEGIN
conn := UTL_SMTP.open_connection (smtp_host, smtp_port);
UTL_SMTP.helo (conn, smtp_domain);
RETURN conn;
END;

PROCEDURE begin_mail_in_session (
conn IN OUT NOCOPY UTL_SMTP.connection,
sender IN VARCHAR2,
recipients IN VARCHAR2,
subject IN VARCHAR2,
mime_type IN VARCHAR2 DEFAULT 'text/plain',
priority IN PLS_INTEGER DEFAULT NULL
)
IS
my_recipients VARCHAR2 (32767) := recipients;
my_sender VARCHAR2 (32767) := sender;
BEGIN
UTL_SMTP.mail (conn, get_address (my_sender));

WHILE (my_recipients IS NOT NULL)
LOOP
UTL_SMTP.rcpt (conn, get_address (my_recipients));
END LOOP;

UTL_SMTP.open_data (conn);
write_mime_header (conn, 'From', sender);
write_mime_header (conn, 'To', recipients);
write_mime_header (conn, 'Subject', subject);
write_mime_header (conn, 'Content-Type', mime_type);
write_mime_header (conn, 'X-Mailer', mailer_id);

IF (priority IS NOT NULL)
THEN
write_mime_header (conn, 'X-Priority', priority);
END IF;

UTL_SMTP.write_data (conn, UTL_TCP.crlf);

IF (mime_type LIKE 'multipart/mixed%')
THEN
write_text (conn,
'This is a multi-part message in MIME format.'
|| UTL_TCP.crlf
);
END IF;
END;

PROCEDURE end_mail_in_session (conn IN OUT NOCOPY UTL_SMTP.connection)
IS
BEGIN
UTL_SMTP.close_data (conn);
END;

PROCEDURE end_session (conn IN OUT NOCOPY UTL_SMTP.connection)
IS
BEGIN
UTL_SMTP.quit (conn);
END;

END Xxansr_Mail;
/

Report message to a moderator

Previous Topic:	Question about Oracle ERP absolute url
Next Topic:	WMS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 02:59:32 CDT 2024