1 package body WF_WORKLIST_ENGINE as
2 /* $Header: wfwrkb.pls 115.25 2004/01/18 01:19:56 dlam ship $ */
3
4 /*
5 Modeled the following views and selects
6
7 create or replace force view WF_WORKLIST_V
8 (
9 ROW_ID,
10 NID,
11 PRIORITY,
12 MESSAGE_TYPE,
13 RECIPIENT_ROLE,
14 LOCKED_BY,
15 SUBJECT,
16 BEGIN_DATE,
17 DUE_DATE,
18 END_DATE,
19 DISPLAY_STATUS,
20 STATUS,
21 ORIGINAL_RECIPIENT,
22 ITEM_TYPE,
23 MESSAGE_NAME
24 ) as select
25 WN.ROWID,
26 WN.NOTIFICATION_ID,
27 WN.PRIORITY,
28 WIT.DISPLAY_NAME,
29 WN.RECIPIENT_ROLE,
30 WN.LOCKED_BY,
31 Wf_Notification.GetSubject(notification_id),
32 WN.BEGIN_DATE,
33 WN.DUE_DATE,
34 WN.END_DATE,
35 WL.MEANING,
36 WN.STATUS,
37 WN.ORIGINAL_RECIPIENT,
38 WN.MESSAGE_TYPE,
39 WN.MESSAGE_NAME
40 from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_VL WIT, WF_LOOKUPS WL
41 where WN.MESSAGE_TYPE = WIT.NAME
42 and WL.LOOKUP_TYPE = 'WF_NOTIFICATION_STATUS'
43 and WN.STATUS = WL.LOOKUP_CODE;
44
45 --
46 -- Must use outer joins in the following two situations in order
47 -- to get everything from the listed item type.
48 -- Even if an attribute did not have any value or does not exist,
49 -- users still want to see the row.
50 --
51 -- for RESPOND Lookup
52 select MA1.FORMAT
53 from WF_NOTIFICATIONS WN, WF_MESSAGE_ATTRIBUTES MA1
54 where WN.MESSAGE_TYPE in ('WFDEMO')
55 and WN.MESSAGE_TYPE = MA1.MESSAGE_TYPE (+)
56 and WN.MESSAGE_NAME = MA1.MESSAGE_NAME (+)
57 and MA1.NAME (+) = 'RESULT';
58
59 -- for others attribute
60 select NA1.TEXT_VALUE
61 from WF_NOTIFICATIONS WN, WF_NOTIFICATION_ATTRIBUTES NA1
62 where WN.MESSAGE_TYPE in ('WFDEMO')
63 and WN.NOTIFICATION_ID = NA1.NOTIFICATION_ID (+)
64 and NA1.NAME (+) = 'DOC_OWNER_ROLE';
65 */
66
67 --
68 -- abandoned the intelligent join of tables and views
69 -- just use the wf_worklist_v instead
70 --
71
72 --
73 -- GetRoleClause3 (Internal Public)
74 -- For use only by "Advanced Worklist" in Self Service Framework.
75 -- Based on GetRoleClause and GetRoleClause2
76 -- Returns the expanded roles list separated by commas.
77 --
78 function GetRoleClause3(
79 name in varchar2
80 ) return varchar2
81 as
82 uorig_system varchar2(30); -- User orig_system for indexes
83 uorig_system_id pls_integer; -- User orig_system_id for indexes
84 tmproles varchar2(6000); -- temp variables to stored the expanded role
85
86 cursor rolecur(osys varchar2, osysid number, uname varchar2) is
87 select ROLE_NAME
88 from WF_USER_ROLES
89 where USER_ORIG_SYSTEM = osys
90 and USER_ORIG_SYSTEM_ID = osysid
91 and USER_NAME = uname;
92 begin
93 -- Copy from WFA_HTML.Worklist
94 -- Fetch user orig_system_ids for indexes
95 Wf_Directory.GetRoleOrigSysInfo(name, uorig_system, uorig_system_id);
96
97 if (uorig_system is null) then
98 wf_core.token('ROLE', name);
99 wf_core.raise('WFNTF_ROLE');
100 end if;
101
102 begin
103 for rolr in rolecur(uorig_system, uorig_system_id, name) loop
104 if (tmproles is null) then
105 tmproles := rolr.ROLE_NAME;
106 else
107 tmproles := tmproles || ',' || rolr.ROLE_NAME;
108 end if;
109 end loop;
110 exception
111 when VALUE_ERROR then
112 tmproles := null;
113 end;
114 return tmproles;
115
116 exception
117 when OTHERS then
118 Wf_Core.Context('Wf_WorkList_Engine', 'GetRoleClause3', name);
119
120 raise;
121 end GetRoleClause3;
122
123 --
124 -- List
125 -- Populate a plsql table with query values.
126 -- IN
127 -- startrow - the Nth row that you want to start your query.
128 -- numrow - the number of rows that you want to get back.
129 -- colin - column definition including query criteria.
130 -- OUT
131 -- totalrow - total number of rows returned by such query.
132 -- colout - plsql table contains the query values.
133 -- NOTE
134 -- Return all rows when numrow is less than 0.
135 -- Return no row, but verify all statements when numrow is 0.
136 --
137 procedure List(
138 startrow in number,
139 numrow in number,
140 colin in colTabType,
141 totalrow out nocopy number,
142 colout out nocopy wrkTabType)
143 as
144 begin
145 null;
146 end;
147
148 --
149 -- Debug_On
150 -- Turn on debug info. You must set serveroutput on in sqlplus session.
151 --
152 procedure debug_on
153 is
154 begin
155 wf_worklist_engine.debug := TRUE;
156 end debug_on;
157
158 --
159 -- Debug_Off
160 -- Turn off debug info.
161 --
162 procedure debug_off
163 is
164 begin
165 wf_worklist_engine.debug := FALSE;
166 end debug_off;
167
168 end WF_WORKLIST_ENGINE;