1 PROCEDURE GET_DOCUMENT(itemtype IN VARCHAR2
 3                        itemkey IN VARCHAR2,
 5                        actid IN NUMBER,
 7                        funcmode IN VARCHAR2, 
 9                        resultout OUT NOCOPY VARCHAR2) IS
11 BEGIN
13 设置Document类型Attribute，逐条反应PO Line
15 wf_engine.setitemattrtext(itemtype => itemtype,
17                           itemkey => itemkey,
19                           aname => 'DOCUMENT_DETAILS',
21                        avalue => 'PLSQLCLOB:test_pkg.GET_DOCUMENT_DETAILS/' ||itemtype || ':' ||itemkey); 
23 END;

 1 PROCEDURE GET_DOCUMENT_DETAILS(document_id IN VARCHAR2,
 3                                display_type IN VARCHAR2,
 5                                document IN OUT CLOB,
 7                                document_type IN OUT VARCHAR2) IS
 9     l_item_type wf_items.item_type%TYPE;
11     l_item_key wf_items.item_key%TYPE;
13     l_document VARCHAR2(32000) := '';
15     l_clob_document CLOB;
16 
17     CURSOR cur_po_lines IS
19         SELECT line_num,
21                purchase_basis,
23                item_description,
25                unit_meas_lookup_code,
27                unit_price
29            FROM po_lines_all
31         WHERE po_header_id = 37566;
33 BEGIN
34 
35     --1. 初始化
37     l_item_type := substr(document_id, 1, instr(document_id, ':') - 1);
39     l_item_key := substr(document_id,instr(document_id, ':') + 1,length(document_id) - 2);
40 
41     --2. 写PO line资料 
43     l_document := '<table border=1>';
44 
45     FOR r_po_lines IN cur_po_lines LOOP
47         l_document := l_document||'<tr>';
49         l_document := l_document||'<td>'||r_po_lines.line_num||'</td>';
51         l_document := l_document||'<td>'||r_po_lines.purchase_basis||'</td>';
53         l_document := l_document||'<td>'||r_po_lines.item_description||'</td>';
55         l_document := l_document||'<td>'||r_po_lines.unit_meas_lookup_code||'</td>';
57         l_document := l_document||'<td>'||r_po_lines.unit_price||'</td>';
59         l_document := l_document||'</tr>';
61     END LOOP;
62 
63     l_document := l_document||'</table>';
64 
65     --3. 通过dbms_lob讲字符串写到workflow中
67     dbms_lob.createtemporary(lob_loc => l_clob_document,
69                              cache => TRUE,
71                              dur => dbms_lob.session);
72 
73     dbms_lob.OPEN(lob_loc => l_clob_document,
75                   open_mode => dbms_lob.lob_readwrite);
77     wf_notification.writetoclob(l_clob_document, l_document); --追加，可不断写
79     dbms_lob.append(document, l_clob_document);
81     IF (dbms_lob.ISOPEN(l_clob_document) = 1) THEN
83         dbms_lob.CLOSE(l_clob_document);
85         dbms_lob.freetemporary(l_clob_document);
87     END IF;
89 END;

 1   PROCEDURE GET_DOCUMENT(itemtype  IN VARCHAR2 ,
 2 
 3                          itemkey   IN VARCHAR2 ,
 4 
 5                          actid     IN NUMBER ,
 6 
 7                          funcmode  IN VARCHAR2 ,
 8 
 9                          resultout OUT NOCOPY VARCHAR2) IS

13     l_open_form   varchar2(200 );
14 
15     l_document_id varchar2(25 );
16 
17   BEGIN
18 
21     SELECT po_header_id
22 
23       INTO l_document_id
24 
25       FROM po_headers_all
26 
27      WHERE segment1 = 'GP0008720MNC' ;
28 
33     l_open_form := 'PO_POXPOEPO:PO_HEADER_ID="' || l_document_id || '"' ||
34 
35                    ' ACCESS_LEVEL_CODE="MODIFY"' ||
36 
37                    ' POXPOEPO_CALLING_FORM="POXSTNOT"' ;
38 
39  
40 
41     DBMS_OUTPUT.PUT_LINE( 'l_open_form-->' || l_open_form);
42 
45     wf_engine.setitemattrtext(itemtype => itemtype,
46 
47                               itemkey  => itemkey,
48 
49                               aname    => 'OPEN_FORM_COMMAND',
50 
51                               avalue   => l_open_form);
52 
53  
54 
55     resultout := wf_engine.eng_completed || ':' || 'Y' ;
56 
59     return;
60 
61   END;