Requirement: We need to trigger an event to send a mail to a particular recipient with the documents attached to a Customer Order.
Problem Overview: We have set up an event action using online SQL to generate and send emails with relevant attachments. The process involves fetching the appropriate attachment and sending it along with the email. However, we've encountered a challenge where, even though the attachment is successfully fetched, it's not getting attached to the email. Consequently, when we trigger the event, we receive a mail without the expected attachment.
What's Been Done:
Please note that we have hard-coded some of the values for testing purposes.
- SQL query to fetch the relevant attachment.
DECLARE
keyref_ VARCHAR2(4000);
order_no_ VARCHAR2(20) := '&ORDER_NO';
CURSOR get_attachments IS
select a.doc_class, a.doc_no, a.doc_sheet, a.doc_rev, b.doc_type, b.file_no, b.file_extention, b.file_name, b.user_file_name
from doc_reference_object a, edm_file b
where a.lu_name = 'CustomerOrder'
and b.doc_class = a.doc_class
and b.doc_no = a.doc_no
and b.doc_sheet = a.doc_sheet
and b.doc_rev = a.doc_rev
and a.key_ref = keyref_ ;
BEGIN
keyref_ := 'ORDER_NO=' || order_no_ || '^';
FOR rec_ IN get_attachments LOOP
SELECT
SUBSTR(rec_.file_name , INSTR(rec_.file_name , '.') + 1)
INTO extention_
FROM dual;
END LOOP;
END;
- SQL to Attach the documents (This is where we encounter the issue)
DECLARE
download_path_ VARCHAR2(4000) := DOCMAN_DEFAULT_API.Get_Default_Value_('BatchTransferHandler', 'FILE_PATH_FOR_BATCH_TRANSFER');
error_msg_ VARCHAR2(4000);
attachment_name_ VARCHAR2(4000);
attachments_ VARCHAR2(4000);
BEGIN
keyref_ := 'ORDER_NO=' || order_no_ || '^';
FOR rec_ IN get_attachments LOOP
attachment_name_ := download_path_ || 'attachment-' || rec_.doc_class || '_' || rec_.doc_no || '.' || extention_;
Batch_Transfer_Handler_Api.Download_From_Db(error_msg_,
rec_.doc_class,
rec_.doc_no,
rec_.doc_sheet,
rec_.doc_rev,
rec_.doc_type,
rec_.file_no,
attachment_name_);
IF (error_msg_ IS NOT NULL) THEN
Batch_Transfer_Handler_Api.Download_From_Db(error_msg_,
rec_.doc_class,
rec_.doc_no,
rec_.doc_sheet,
rec_.doc_rev,
'VIEW',
rec_.file_no,
attachment_name_);
END IF;
attachments_ := attachments_ || attachment_name_ || ';';
END LOOP;
END;
- SQL to generate the mail
DECLARE
to_user_ VARCHAR2(2000);
from_user_name_ VARCHAR2(200);
subject_ VARCHAR2(32000);
text_ VARCHAR2(32000);
BEGIN
subject_:= 'Customer Order Attachments';
text_:= 'Attached Customer Order Details' ;
to_user_ := 'test@gmail.com';
Command_SYS.MAIL
(
from_user_name_ => from_user_name_,
to_user_name_ => to_user_ ,
text_ => text_,
subject_ => subject_ ,
attach_ => attachments_ || '&PDF_FILE'
);
END;
We are reaching out to the community to seek advice and insights into potential solutions for this issue. If you have experience with similar scenarios or have expertise in email attachment handling within the context of events, your input would be highly valuable.
