This article explains how to attach a concurrent program output as a Workflow email notification.
Prerequisites
- DBA Directory should be created with Concurrent Program output path
- A new document category should be created under “Application Developer –> Attachments –> Document Categories”
Steps to Implement
- Call API “FND_DOCUMENTS_PKG.INSERT_ROW” to detail the information of document to be created
- Insert a BLOB type of record into “FND_LOBS” table
- Use “DBMS_LOB” API to read the output from Concurrent Program output path and point it to the BLOB type of record created in step 2
Below is the sample PL/SQL procedure which takes Concurrent Request ID as input parameter and returns the media_id of the BLOB document created
PROCEDURE load_inv_pdf_p (
p_conc_req_id IN apps.fnd_concurrent_requests.request_id%TYPE,
p_project_number IN apps.pa_projects_all.segment1%TYPE,
p_draft_invoice_num IN apps.pa_draft_invoices_all.draft_invoice_num%TYPE,
p_file_id OUT apps.fnd_lobs.file_id%TYPE
)
-- +===================================================================+
-- | Name : load_inv_pdf_p |
-- | Description : Loads a file to FND_LOBS as a BLOB and returns |
-- | BLOB file_id |
-- | Parameters : |
-- | |
-- | p_conc_req_id --> Concurrent Request ID |
-- | p_project_number --> Project Number |
-- | p_draft_invoice_num--> Draft Invoice Number |
-- | p_file_id --> File ID from fnd_lobs |
-- | |
-- +===================================================================+
IS
lr_row_id ROWID;
ln_document_id NUMBER;
ln_media_id NUMBER;
lb_blob_data BLOB;
ln_category_id NUMBER;
lb_blob BLOB;
lb_bfile BFILE;
lc_actual_file_name VARCHAR2 (200);
lc_display_file_name VARCHAR2 (200);
lc_description VARCHAR2 (400);
lb_go BOOLEAN := TRUE;
ln_count NUMBER;
lc_cp_short_name apps.fnd_concurrent_programs.concurrent_program_name%TYPE;
--
BEGIN
--
--Finding CP Short name based on request ID
--
SELECT program_short_name
INTO lc_cp_short_name
FROM apps.fnd_conc_req_summary_v
WHERE 1 = 1 AND request_id = p_conc_req_id;
lc_actual_file_name := lc_cp_short_name || '_' || p_conc_req_id || '_1.PDF';
lc_display_file_name :=
'Project#'
|| p_project_number
|| '_DraftInvoice#'
|| p_draft_invoice_num
|| '.pdf';
lc_description :=
'Draft Invoice#'
|| p_draft_invoice_num
|| ' for Project#'
|| p_project_number;
--
DBMS_OUTPUT.put_line ( 'lc_actual_file_name: ' || lc_actual_file_name);
DBMS_OUTPUT.put_line ( 'lc_display_file_name: ' || lc_display_file_name);
--
BEGIN
SELECT category_id
INTO ln_category_id
FROM apps.fnd_document_categories_tl
WHERE user_name = 'XX Project Draft Invoice PDF'
AND LANGUAGE = USERENV ('lang');
EXCEPTION
WHEN OTHERS
THEN
--
lb_go := FALSE;
p_file_id := -1;
DBMS_OUTPUT.put_line (
'Error While Deriving Document Category ' || SQLERRM
);
--
END;
--
--Create a FND DOCUMENT
--
BEGIN
DBMS_OUTPUT.put_line ( 'FND_DOCUMENTS_PKG.INSERT_ROW Call');
apps.fnd_documents_pkg.insert_row
(x_rowid => lr_row_id,
x_document_id => ln_document_id,
x_creation_date => SYSDATE,
x_created_by => fnd_global.user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_global.user_id,
x_last_update_login => fnd_global.user_id,
x_datatype_id => 6,
--Indicates BLOB type of data
x_category_id => ln_category_id,
x_security_type => 1,
x_publish_flag => 'Y',
x_usage_type => 'S',
x_start_date_active => SYSDATE,
x_request_id => fnd_global.conc_request_id,
x_program_application_id => fnd_global.prog_appl_id,
x_program_update_date => SYSDATE,
x_language => fnd_global.current_language,
x_description => lc_description,
x_file_name => lc_display_file_name,
x_media_id => ln_media_id
);
DBMS_OUTPUT.put_line ( 'file_id: ' || ln_media_id);
--Setting out parameter p_file_id
p_file_id := ln_media_id;
--
EXCEPTION
WHEN OTHERS
THEN
lb_go := FALSE;
p_file_id := -1;
DBMS_OUTPUT.put_line (
'Error during FND_DOCUMENTS_PKG.INSERT_ROW Call '
|| SQLERRM
);
END;
--
--Creating Empty BLOB with reference to the above ceated document
--
BEGIN
lb_blob_data := EMPTY_BLOB ();
DBMS_OUTPUT.put_line ( 'Inserting to FND LOBS');
INSERT INTO apps.fnd_lobs
(file_id, file_name,
file_content_type, upload_date, expiration_date,
program_name, program_tag, file_data,
LANGUAGE, oracle_charset, file_format
)
VALUES (ln_media_id, lc_display_file_name,
'application/octet-stream', SYSDATE, NULL,
'FNDATTCH', NULL, lb_blob_data,
fnd_global.current_language, NULL, 'binary'
)
RETURNING file_data
INTO lb_blob;
EXCEPTION
WHEN OTHERS
THEN
lb_go := FALSE;
p_file_id := -1;
DBMS_OUTPUT.put_line (
'Error during Insert into FND_LOBS ' || SQLERRM
);
END;
--
BEGIN
--
--Loading physical file to LOBS table as a BLOB
--
DBMS_OUTPUT.put_line ( 'Loading File to Lobs');
--
--Getting a pointer for Physical file
--
lb_bfile := BFILENAME ('XX_CP_OUTPUT_DIR', lc_actual_file_name);
--
--Open the file with the pointer returned above
--
DBMS_LOB.fileopen (lb_bfile);
--
--load the file from disk to the table directly using lb_blob created in previous insert statement
--
DBMS_LOB.loadfromfile (lb_blob, lb_bfile,
DBMS_LOB.getlength (lb_bfile));
--
--close the file after storing it in the table
--
DBMS_LOB.fileclose (lb_bfile);
--
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'There is no such File or Directory..File Name : '
|| lc_actual_file_name
|| ' Error : '
|| SQLERRM
);
--
ROLLBACK;
END;
--
COMMIT;
--
END load_inv_pdf_p;
Attaching the BLOB document to workflow Notification
Below is the package to set BLOB file file_id to workflow notification
SET serveroutput ON;
CREATE OR REPLACE PACKAGE "BOLINF"."XX_TEST1_PKG" AUTHID CURRENT_USER
IS
PROCEDURE set_att_invoice_media_id (
p_item_key apps.wf_notifications.item_key%TYPE
);
--
PROCEDURE notif_attach_procedure (
document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT BLOB,
document_type IN OUT VARCHAR2
);
END xx_test1_pkg;
/
--
--
CREATE OR REPLACE PACKAGE BODY "BOLINF"."XX_TEST1_PKG"
AS
-- +===================================================================+
-- | Name : notif_attach_procedure |
-- | Description : Attach draft invoice to workflow notification |
-- | Parameters : |
-- | |
-- | document_id --> Unique String to identify document |
-- | display_type --> type used for notification presentation |
-- | document --> The outbound LOB locator pointing to where |
-- | the document text is stored |
-- | document_type --> The outbound text buffer where the document |
-- | content type is returned |
-- | |
-- | RETURNS : No Return |
-- +===================================================================+
PROCEDURE notif_attach_procedure (
document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT BLOB,
document_type IN OUT VARCHAR2
)
IS
lob_id NUMBER;
bdoc BLOB;
content_type VARCHAR2 (100);
filename VARCHAR2 (300);
BEGIN
--
DBMS_OUTPUT.put_line ('XX In notif_attach_procedure');
--
--–set_debug_context('notif_attach_procedure');
lob_id := TO_NUMBER (document_id);
--
--Creating a temporary blob type of document
--
DBMS_LOB.createtemporary (bdoc, FALSE, DBMS_LOB.CALL);
--
DBMS_OUTPUT.put_line ('XX lob_id: ' || lob_id);
--
-- Obtain the BLOB version of the document
--
SELECT file_name, file_content_type, file_data
INTO filename, content_type, bdoc
FROM apps.fnd_lobs
WHERE file_id = lob_id;
--
document_type := content_type || ';name=' || filename;
--
--Copying the blob document 'document' to the temporary blob document 'bdoc'
--
DBMS_LOB.COPY (document, bdoc, DBMS_LOB.getlength (bdoc));
--
DBMS_OUTPUT.put_line ('XX End of notif_attach_procedure');
--
EXCEPTION
WHEN OTHERS
THEN
--
DBMS_OUTPUT.put_line ( 'XX Exception in notif_attach_procedure :'
|| SQLERRM
);
--
END notif_attach_procedure;
--
--
PROCEDURE set_att_invoice_media_id (
p_item_key apps.wf_notifications.item_key%TYPE
)
IS
-- +===================================================================+
-- | Name : set_att_invoice_media_id |
-- | Description : To trigger workflow |
-- | Parameters : |
-- | |
-- | p_item_key --> Unique String to identify workflow instance |
-- | |
-- +===================================================================+
l_process VARCHAR2 (20) := 'XX_TEST_PROCESS';
l_itemtype VARCHAR2 (8) := 'XXTEST1';
l_itemkey apps.wf_notifications.item_key%TYPE := p_item_key;
l_user_key apps.wf_notifications.user_key%TYPE := 'XX-TEST-11-PDF';
l_doc_id VARCHAR2 (20) := '417633'; --media_id generated from procedure load_inv_pdf_p
--
BEGIN
--
DBMS_OUTPUT.put_line ('XX Begin set_att_invoice_media_id');
--
--
--Creating Workflow Process
--
apps.wf_engine.createprocess (itemtype => l_itemtype,
itemkey => l_itemkey,
process => l_process,
user_key => l_user_key,
owner_role => 'SYSADMIN'
);
--
DBMS_OUTPUT.put_line ('XX Setting Attribute');
--
apps.wf_engine.setitemattrtext
(itemtype => l_itemtype,
itemkey => l_itemkey,
aname => 'XX_BLOB_DOC_ID',
avalue => TO_CHAR
(l_doc_id)
--doc_id must be in character format
);
--
--Start Process
--
apps.wf_engine.startprocess (itemtype => l_itemtype,
itemkey => l_itemkey
);
--
DBMS_OUTPUT.put_line ('XX End set_att_invoice_media_id');
--
EXCEPTION
WHEN OTHERS
THEN
--
DBMS_OUTPUT.put_line ( 'XX Exception in set_att_invoice_media_id :'
|| SQLERRM
);
--
END set_att_invoice_media_id;
END xx_test1_pkg;
/
Create a workflow with two types of attributes, one is of type ‘Text’ (XX_BLOB_DOC_ID) and other is of type ‘Document’ (XX_DRAFT_INV_FILE_ATTACH)
Workflow Structure:
Workflow Process
Document type attribute
Value: plsqlblob:XX_TEST1_PKG.notif_attach_procedure/&XX_BLOB_DOC_ID
Assigning Document type attribute to a message
file_id of blob document should be set to attribute XX_BLOB_DOC_ID and it should be passed as parameter to Document type attribute XX_DRAFT_INV_FILE_ATTACH which in turn calls procedure “XX_TEST1_PKG.notif_attach_procedure” which copies the original blob document in fnd_lobs to workflow notification.
Below is the block of code to trigger the workflow
SET SERVEROUTPUT ON;
DECLARE
l_item_key NUMBER := 101;
BEGIN
xx_test1_pkg.set_att_invoice_media_id (l_item_key);
END;
/
You can download a copy of workflow here.