ORACLE EBS WORKFLOW实现多附件下载

 

   1. 在WORKFLOW BUILDER中定义 Attributes 变量DOWNLOADFILE_INFO 显示名为 ：附件下载

 

   

 

  2. 将定义的变量拖到Message中

 

 

 

   3. 在消息体 Message Body中 显示：&DOWNLOADFILE_INFO 最好在HTML Body中

 

   4.写一个PROCEDURE

    例：

 

    PROCEDURE  get_downloadfile_info(document_id in varchar2,
                                display_type in varchar2,
                                document in out NOCOPY varchar2,
                                document_type in out NOCOPY varchar2) IS
                               
       l_item_type wf_items.item_type%TYPE := substr(document_id, 1, instr(document_id, ':') - 1);
       l_item_key wf_items.item_key%TYPE := substr(document_id,instr(document_id, ':') + 1, length(document_id) - 2);
       url VARCHAR2(500);
       l_bh       NUMBER :=0;
       l_document VARCHAR2(32000) := '';
       l_con_head_id NUMBER := wf_engine.GetItemAttrNumber(itemtype => l_item_type,
                                                        itemkey => l_item_key,
                                                        aname => 'CON_HEAD_ID');
      
       --获取附件文件ID
       CURSOR file_id_list IS
       SELECT d.file_name as 附件名称,d.media_id
         -- ad.pk1_value
       FROM FND_DOCUMENT_DATATYPES     DAT,
            FND_DOCUMENT_ENTITIES_TL   DET,
            FND_DOCUMENTS_TL           DT,
            FND_DOCUMENTS              D,
            FND_DOCUMENT_CATEGORIES_TL DCT,
            FND_DOC_CATEGORY_USAGES    DCU,
            FND_ATTACHMENT_FUNCTIONS   AF,
            FND_ATTACHED_DOCUMENTS     AD,
            CUX_CON_HEADERS_ALL        CH
       WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
       AND DT.DOCUMENT_ID = D.DOCUMENT_ID
       AND DT.LANGUAGE = USERENV('LANG')
       AND DCT.CATEGORY_ID = D.CATEGORY_ID
       AND DCT.LANGUAGE = USERENV('LANG')
       AND DCU.CATEGORY_ID = D.CATEGORY_ID
       AND DCU.ATTACHMENT_FUNCTION_ID = AF.ATTACHMENT_FUNCTION_ID
       AND D.DATATYPE_ID = DAT.DATATYPE_ID
       AND DAT.LANGUAGE = USERENV('LANG')
       AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
       AND DET.LANGUAGE = USERENV('LANG')
       AND DCU.ENABLED_FLAG = 'Y'
       AND AD.PK1_VALUE = CH.CON_HEAD_ID
       AND CH.CON_HEAD_ID = l_con_head_id;
   
                               
  BEGIN
       

        --使用DIV + SCROLL 实现的多附件显示
        l_document := '<div style="width: 450px; height: 94px; overflow: scroll">';
       
        l_document := l_document || '<table border="1" cellspacing="0" width="100%">' || '<caption>'   ||
                         '<p class="x3w" align="left">' || '附件下载</p>' ||'</caption>' ||
                         '<tr>' ||
                         '<td class="x1r x4j" width="2%">' ||'<p class="x24" align="center">编号</td>' ||
                         '<td class="x1r x4j" width="15%">' ||'<p class="x24" align="center">附件名称</td>' ||
                         '</tr>';
                         
        --获取附件文件ID
        for file_id in file_id_list loop
             l_bh := l_bh+1;
             url :=fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,file_id.media_id,TRUE);
             l_document := l_document ||
                       '<tr>' ||
                       '<td class="x1l x4x" width="2%">'  ||   l_bh       || '</td>' ||
                       '<td class="x1l x4x" width="15%">'  || '<a href='|| url || '>' || file_id.附件名称  ||'</a>' || '</td>' ||
                       '</tr>';
        end loop;
       
        l_document := l_document || '</table>';
       
        l_document := l_document || '</div>';
       
        document:=l_document;
   
  END get_downloadfile_info;

 

 5.最后效果