原文：
ORACLE workflow审批界面显示附件信息和附件的下载链接
代码如下：
CREATE OR REPLACE PACKAGE cux_attachment_util_pkg IS TYPE curtype IS REF CURSOR;
TYPE c_attached_record IS RECORD(
row_num NUMBER,
pk1_value NUMBER,
user_entity_name VARCHAR2(1000),
title VARCHAR2(1000),
file_name VARCHAR2(1000),
datatype_name VARCHAR2(1000),
file_data BLOB,
file_id NUMBER);
-------------------------------------------------------------------
---------------
-- 功能名称：
-- GET_ATTACHED_HTML
-- 功能说明：
-- 获取附件列表html 代码
-- 参数说明：
-- PITEM_TYPE 工作流代码
-- P_ITEM_KEY 审批关键字
-- P_FUNCTION_NAME 附件功能名称
-- P_ENTITY_NAME 文档实体标识
-- P_TABLE_NAME 文档实体表
-- p_BLOCK_NAME 附件功能的块名
-- 返回附件列表的HTML代码
-- 版本信息:
-- 1.0 Initial Creation
-------------------------------------------------------------------
---------------
FUNCTION get_attached_html(pitem_type IN VARCHAR2,
pitem_key IN VARCHAR2,
p_function_name IN VARCHAR2,
p_entity_name IN VARCHAR2,
p_table_name IN VARCHAR2,
p_block_name IN VARCHAR2) RETURN VARCHAR2;
---------------------------------------------------------------------
-------------
-- 功能名称：
-- GET_OUTPUT_URL
-- 功能说明：
-- 获取附件下载链接地址
-- 参数说明：
-- P_PK1_VALUE 附件的主关键字值
-- P_FILE_ID 附件文件ID
-- P_ENTITY_NAME 文档实体标识
-- 返回指定附件文件的下载链接地址
-- 版本信息:
-- 1.0 Initial Creation
---------------------------------------------------------------------
-------------
FUNCTION get_output_url(p_pk1_value IN NUMBER,
p_file_id IN NUMBER,
p_entity_name IN VARCHAR2) RETURN VARCHAR2; -------------------------------------------------------------------
---------------
-- 功能名称：
-- GET_ATTACHED_HTML
-- 功能说明：
-- 获取附件列表html 代码
-- 参数说明：
-- PITEM_TYPE 工作流代码
-- P_ITEM_KEY 审批关键字
-- P_FUNCTION_NAME 附件功能名称
-- P_ENTITY_NAME 文档实体标识
-- P_TABLE_NAME 文档实体表
-- p_BLOCK_NAME 附件功能的块名
-- 返回附件列表的HTML代码
-- 版本信息:
-- 1.0 Initial Creation
---------------------------------------------------------------------
------------- END;
CREATE OR REPLACE PACKAGE BODY cux_attachment_util_pkg IS FUNCTION get_attached_html(pitem_type IN VARCHAR2,
pitem_key IN VARCHAR2,
p_function_name IN VARCHAR2,
p_entity_name IN VARCHAR2,
p_table_name IN VARCHAR2,
p_block_name IN VARCHAR2) RETURN VARCHAR2 IS
c_attached curtype;
v_document_attached VARCHAR2(32000);
vattachedinfo c_attached_record;
v_count NUMBER := 1;
v_pk1_value NUMBER;
vpk1_filed VARCHAR2(61);
vpk2_filed VARCHAR2(61);
vpk3_filed VARCHAR2(61);
vpk4_filed VARCHAR2(61);
vpk5_filed VARCHAR2(61);
vpk1 VARCHAR2(61);
vpk2 VARCHAR2(61);
vpk3 VARCHAR2(61);
vpk4 VARCHAR2(61);
vpk5 VARCHAR2(61);
vurl VARCHAR2(500);
vcondition_field VARCHAR2(100);
vcondition_operator VARCHAR2(10);
vcondition_value1 VARCHAR2(100);
vcondition_value2 VARCHAR2(100);
vquery_permission_type VARCHAR2(10);
vinsert_permission_type VARCHAR2(10);
vupdate_permission_type VARCHAR2(10);
vdelete_permission_type VARCHAR2(10);
v_sql VARCHAR2(32000);
c_sql VARCHAR2(32000);
BEGIN
BEGIN
SELECT abe.pk1_field,
abe.pk2_field,
abe.pk3_field,
abe.pk4_field,
abe.pk5_field,
abe.condition_field,
abe.condition_operator,
abe.condition_value1,
abe.condition_value2,
abe.query_permission_type,
abe.insert_permission_type,
abe.update_permission_type,
abe.delete_permission_type
INTO vpk1_filed,
vpk2_filed,
vpk3_filed,
vpk4_filed,
vpk5_filed,
vcondition_field,
vcondition_operator,
vcondition_value1,
vcondition_value2,
vquery_permission_type,
vinsert_permission_type,
vupdate_permission_type,
vdelete_permission_type
FROM fnd_attachment_blk_entities abe,
fnd_attachment_blocks ab,
fnd_attachment_functions af
WHERE abe.attachment_blk_id = ab.attachment_blk_id
AND ab.attachment_function_id = af.attachment_function_id
AND af.function_name = p_function_name
AND af.function_type = 'F'
AND abe.data_object_code = p_entity_name
AND ab.block_name = p_block_name
AND rownum = 1;
EXCEPTION
WHEN no_data_found THEN
SELECT abe.pk1_field,
abe.pk2_field,
abe.pk3_field,
abe.pk4_field,
abe.pk5_field,
abe.condition_field,
abe.condition_operator,
abe.condition_value1,
abe.condition_value2,
abe.query_permission_type,
abe.insert_permission_type,
abe.update_permission_type,
abe.delete_permission_type
INTO vpk1_filed,
vpk2_filed,
vpk3_filed,
vpk4_filed,
vpk5_filed,
vcondition_field,
vcondition_operator,
vcondition_value1,
vcondition_value2,
vquery_permission_type,
vinsert_permission_type,
vupdate_permission_type,
vdelete_permission_type
FROM fnd_attachment_blk_entities abe,
fnd_attachment_blocks ab,
fnd_attachment_functions af
WHERE abe.attachment_blk_id = ab.attachment_blk_id
AND ab.attachment_function_id = af.attachment_function_id
AND af.function_name = p_function_name
AND af.function_type = 'O'
AND abe.data_object_code = p_entity_name
AND ab.block_name = p_block_name
AND rownum = 1;
END;
v_sql := 'SELECT ' || substr(vpk1_filed,
instr(vpk1_filed, '.', 1) + 1,
length(vpk1_filed)) || ' FROM ' ||
p_table_name || ' WHERE ITEM_KEY = ''' || pitem_key || '''' ||
' AND ROWNUM = 1';
EXECUTE IMMEDIATE v_sql
INTO v_pk1_value;
v_document_attached := '<table width=100% border="0"
cellspacing="0" cellpadding="0" >
<tr><td><table cellpadding="0" cellspacing="0" border="0"
width="100%" class="x74"
style="background-image:url(/OA_HTML/cabo/images/swan/headingBarBg.gi
f);">
<tr>
<td width="100%"><h2 class="x75"> 附件列表
</h2></td></tr></table></td></tr>
<tr><td><table width="100%" class="x1h" cellpadding="1"
cellspacing="0" summary="Action History" border="1" >
<tr>
<th class="x1r x4j" scope="col" width="2%" align="LEFT"
valign="baseline" ><span class="x24"> 序号</span></th>
<th class="x1r x4j" scope="col" width="5%" align="LEFT"
valign="baseline" ><span class="x24"> 实体名</span></th>
<th class="x1r x4j" scope="col" width="10%" align="LEFT"
valign="baseline" ><span class="x24"> 标题</span></th>
<th class="x1r x4j" scope="col" width="15%" align="LEFT"
valign="baseline" ><span class="x24"> 文件名</span></th>
</tr>';
c_sql := 'SELECT ROWNUM ROW_NUM,
PK1_VALUE,
USER_ENTITY_NAME,
TITLE,
FILE_NAME,
DATATYPE_NAME,
FILE_DATA,
FILE_ID
FROM (SELECT FAD.PK1_VALUE,
FAD.USER_ENTITY_NAME,
FAD.TITLE,
FAD.FILE_NAME,
FAD.DATATYPE_NAME,
FL.FILE_DATA,
FL.FILE_ID
FROM FND_ATTACHED_DOCS_FORM_VL FAD, FND_LOBS FL
WHERE FAD.MEDIA_ID = FL.FILE_ID
AND FAD.FUNCTION_NAME = ''' || p_function_name || '''' ||
' AND FAD.PK1_VALUE IN ' || '(SELECT T.' ||
substr(vpk1_filed,
instr(vpk1_filed, '.', 1) + 1,
length(vpk1_filed)) || ' FROM ' ||
p_table_name || ' T WHERE T.ITEM_KEY = ''' ||
pitem_key || '''' || ')
AND FAD.ENTITY_NAME = ''' || p_entity_name || '''' ||
' ORDER BY FAD.USER_ENTITY_NAME, FAD.SEQ_NUM)';
OPEN c_attached FOR c_sql;
FETCH c_attached
INTO vattachedinfo;
WHILE c_attached% FOUND LOOP
v_document_attached := v_document_attached ||
'<tr>
<td align="LEFT" valign="baseline" class="x1l x4x">' ||
v_count ||
'</td>
<td align="LEFT" valign="baseline" class="x1l x4x">' ||
vattachedinfo.user_entity_name ||
'</td>
<td align="LEFT" valign="baseline" class="x1l x4x">' ||
vattachedinfo.title || '</td>
<td align="LEFT" valign="baseline" class="x1l x4x">
<a href="' ||
get_output_url(v_pk1_value,
vattachedinfo.file_id,
p_entity_name) || '">' ||
vattachedinfo.file_name || '</a></td>
</tr>';
v_count := v_count + 1;
FETCH c_attached
INTO vattachedinfo;
END LOOP;
v_document_attached := v_document_attached ||
'</table></td></tr></table>';
RETURN v_document_attached;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_attached_html;
---------------------------------------------------------------------
-------------
-- 功能名称：
-- GET_OUTPUT_URL
-- 功能说明：
-- 获取附件下载链接地址
-- 参数说明：
-- P_PK1_VALUE 附件的主关键字值
-- P_FILE_ID 附件文件ID
-- P_ENTITY_NAME 文档实体标识
-- 返回指定附件文件的下载链接地址
-- 版本信息:
-- 1.0 Initial Creation
---------------------------------------------------------------------
-------------
/*FUNCTION GET_OUTPUT_URL(P_PK1_VALUE IN NUMBER, P_FILE_ID IN
NUMBER,P_ENTITY_NAME IN VARCHAR2)
RETURN VARCHAR2 IS
P_ACCESS_ID NUMBER;
V_OUTPUT_URL VARCHAR2(10000);
BEGIN
V_OUTPUT_URL := '';
SELECT ACCESS_ID
INTO P_ACCESS_ID
FROM FND_LOB_ACCESS
WHERE FILE_ID = P_FILE_ID;
V_OUTPUT_URL := RPT_REG_PKG.G_XML_PATH\* 是一个配置文件值可以自己定义修改，
值如： http://db1.dtpower.zszy.com:8001\*\ ||
'OA_HTML/fndgfm.jsp?mode=download_blob&fid=' ||
P_FILE_ID || '&accessid=' || P_ACCESS_ID;
RETURN V_OUTPUT_URL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO FND_LOB_ACCESS
(ACCESS_ID, FILE_ID, TIMESTAMP)
SELECT TO_NUMBER(TO_CHAR(FND_LOBS_S.NEXTVAL) ||
TO_CHAR(SYSDATE, 'SSSSS')),
D.MEDIA_ID,
SYSDATE + 1
FROM FND_ATTACHED_DOCUMENTS B,
FND_DOCUMENTS_TL C,
FND_DOCUMENTS D
WHERE B.DOCUMENT_ID = C.DOCUMENT_ID
AND B.DOCUMENT_ID = D.DOCUMENT_ID
AND B.PK1_VALUE = P_PK1_VALUE
AND D.MEDIA_ID = P_FILE_ID
AND B.ENTITY_NAME = P_ENTITY_NAME
AND C.LANGUAGE = 'ZHS';
COMMIT;
SELECT ACCESS_ID
INTO P_ACCESS_ID
FROM FND_LOB_ACCESS
WHERE FILE_ID = P_FILE_ID;
V_OUTPUT_URL := RPT_REG_PKG.G_XML_PATH ||
'OA_HTML/fndgfm.jsp?mode=download_blob&fid=' ||
P_FILE_ID || '&accessid=' || P_ACCESS_ID;
RETURN V_OUTPUT_URL;
WHEN OTHERS THEN
P_ACCESS_ID := 0;
V_OUTPUT_URL := '';
XZTZ_LOG_PKG.INSERT_LOG(' 附件列表文件下载链接生成异常',
SQLERRM,
'XZ_PO_PUB_PKG.GET_OUTPUT_URL',
30);
RETURN V_OUTPUT_URL;
END GET_OUTPUT_URL;*/
FUNCTION get_output_url(p_pk1_value IN NUMBER,
p_file_id IN NUMBER,
p_entity_name IN VARCHAR2) RETURN VARCHAR2 IS
p_access_id NUMBER;
v_output_url VARCHAR2(10000);
BEGIN
/* V_SQL_STR := 'SELECT d.file_name,d.media_id ,a.access_id,
fnd_gfm.construct_download_url(fnd_web_config.gfm_agent,d.media_id)
URL ' ||
' from fnd_attached_documents b,fnd_documents_tl c,fnd_lob_access
a,fnd_documents d' ||
' where b.document_id=c.document_id and
b.document_id=d.document_id and b.pk1_value=''' ||
V_PK1_VALUE || '''' ||
' and a.file_id=d.media_id and b.entity_name IN
(''XZ_PO_EXPENDED'',''XZ_PO_EXPENDED'')
and C.LANGUAGE=''ZHS'' '; */
v_output_url := fnd_gfm.construct_download_url(fnd_web_config.gfm_agent, /* 应用产品 Web
代理配置文件值： http://db1.dtpower.zszy.com:8001/pls/TEST*/
p_file_id);
RETURN v_output_url;
EXCEPTION
WHEN OTHERS THEN
p_access_id := 0;
v_output_url := '';
RETURN v_output_url;
END get_output_url; END;
效果如下：
