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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 | 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.