1 package body WF_ENGINE_RAC as
2 /* $Header: wfengrcb.pls 120.4 2011/08/10 18:01:46 alsosa noship $ */
3
4 l_IsInstIdPartitioned boolean := Is_INSTID_Partitioned;
5
6 FUNCTION WF_RUNTIME_TABLE_POLICY(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2 IS
7 Predicate VARCHAR2(200);
8 BEGIN
9 Predicate := null;
10 --IF Is_INSTID_Partitioned THEN
11 IF l_IsInstIdPartitioned THEN
12 IF SYS_CONTEXT('WF_RAC_CTX','INST_ID') is not null then
13 Predicate := 'Inst_ID = SYS_CONTEXT(''WF_RAC_CTX'',''INST_ID'')';
14 END IF;
15 END IF;
16 RETURN Predicate;
17 EXCEPTION
18 WHEN OTHERS THEN
19 RETURN null;
20 END WF_RUNTIME_TABLE_POLICY;
21
22 -- NOTES: See specification files for further notes
23 FUNCTION WF_QUEUE_TABLE_POLICY(Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2 IS
24 Predicate VARCHAR2(200);
25 l_apps varchar(20) := sys_context('USERENV', 'CURRENT_SCHEMA');
26 BEGIN
27 IF SYS_CONTEXT('WF_RAC_CTX','INST_ID') is NOT NULL THEN
28 Predicate := 'CORRID like '''||l_apps||'%:'||SYS_CONTEXT('WF_RAC_CTX','INST_ID')||'''';
29 ELSE
30 Predicate := 'CORRID like '''||l_apps||'%:'||SYS_CONTEXT('USERENV','instance')||'''';
31 END IF;
32 RETURN Predicate;
33 END WF_QUEUE_TABLE_POLICY;
34
35 PROCEDURE SET_CONTEXT (p_name in varchar2, p_value in varchar2) IS
36 BEGIN
37 if p_name in ('INST_ID') then
38 dbms_session.set_context('WF_RAC_CTX', p_name, p_value);
39 end if;
40 END SET_CONTEXT;
41
42 FUNCTION IS_INSTID_PARTITIONED return BOOLEAN is
43 l_partitioned varchar2(10);
44 l_applsys varchar2(20) := WF_CORE.Translate('WF_SCHEMA');
45 BEGIN
46 SELECT 'TRUE'
47 into l_partitioned
48 FROM DUAL
49 WHERE 4=(select count(1)
50 from dba_tab_partitions
51 where table_owner=l_applsys and
52 table_name in ('WF_ITEMS',
53 'WF_ITEM_ATTRIBUTE_VALUES',
54 'WF_ITEM_ACTIVITY_STATUSES',
55 'WF_ITEM_ACTIVITY_STATUSES_H')
56 and partition_name='INST_ID1');
57 IF l_partitioned = 'TRUE' THEN
58 return TRUE;
59 ELSE
60 return FALSE;
61 END IF;
62 EXCEPTION
63 WHEN OTHERS THEN
64 return FALSE;
65 END IS_INSTID_PARTITIONED;
66
67 -- Item_Is_RAC_Enabled. See specification file for details
68 FUNCTION Item_Is_RAC_Enabled (p_itemtype IN WF_ITEM_TYPES.NAME%TYPE)
69 return boolean RESULT_CACHE IS
70 l_dummy number;
71 Begin
72 select count(1) into l_dummy
73 from (select null
74 from FND_LOOKUP_VALUES_VL
75 where LOOKUP_TYPE='WF_RAC_ENABLED_TYPES'
76 and ENABLED_FLAG='Y'
77 and (p_itemtype is null OR substr(LOOKUP_CODE,1, instr(LOOKUP_CODE,':',1)-1)=p_itemtype));
78 if l_dummy>0 then
79 return TRUE;
80 else
81 return FALSE;
82 end if;
83 exception
84 when others then
85 return FALSE;
86 End Item_Is_RAC_Enabled;
87
88 -- Process_Is_RAC_Enabled. See specification file for details
89 FUNCTION Process_Is_RAC_Enabled (p_itemtype IN WF_ITEM_TYPES.NAME%TYPE,
90 p_processname IN WF_ITEMS.ROOT_ACTIVITY%TYPE)
91 return boolean RESULT_CACHE IS
92 l_dummy number;
93 l_string varchar2(100) := upper(p_itemtype)||':'||upper(p_processname);
94 Begin
95 select count(1) into l_dummy
96 from (select null
97 from FND_LOOKUP_VALUES_VL
98 where LOOKUP_TYPE='WF_RAC_ENABLED_TYPES'
99 and ENABLED_FLAG='Y'
100 and LOOKUP_CODE=l_string);
101 if l_dummy>0 then
102 return TRUE;
103 else
104 return FALSE;
105 end if;
106 exception
107 when others then
108 return FALSE;
109 End Process_Is_RAC_Enabled;
110
111 function Number_Of_Partitions return number is
112 l_num_partitions number;
113 begin
114 select count(1) into l_num_partitions
115 from dba_tab_partitions
116 where table_name='WF_ITEMS' and
117 table_owner=wf_core.translate('WF_SCHEMA');
118 return l_num_partitions;
119 exception
120 when others then
121 raise;
122
123 end Number_Of_Partitions;
124
125 procedure BackgroundConcurrent (
126 errbuf out NOCOPY varchar2,
127 retcode out NOCOPY varchar2,
128 itemtype in varchar2,
129 minthreshold in varchar2,
130 maxthreshold in varchar2,
131 process_deferred in varchar2,
132 process_timeout in varchar2,
133 process_stuck in varchar2,
134 partition_number in varchar2)
135 is
136 minthreshold_num number;
137 maxthreshold_num number;
138 process_deferred_bool boolean;
139 process_timeout_bool boolean;
140 process_stuck_bool boolean;
141
142 errname varchar2(30);
143 errmsg varchar2(2000);
144 errstack varchar2(4000);
145 rac_req_id number;
146 n_partitions number := WF_ENGINE_RAC.Number_Of_Partitions;
147 TYPE Item_Type_Tab IS TABLE of WF_ITEMS.ITEM_TYPE%TYPE;
148 ItemsNum Item_Type_Tab := Item_Type_Tab();
149 cursor c_RACTypes is
150 select WITV.NAME
151 from WF_ITEM_TYPES_VL WITV,
152 (select unique(substr(LOOKUP_CODE,1, instr(LOOKUP_CODE,':',1)-1)) L_CODE
153 from FND_LOOKUP_VALUES_VL
154 where LOOKUP_TYPE='WF_RAC_ENABLED_TYPES'
155 and ENABLED_FLAG='Y') WFRAC
156 where WITV.NAME=WFRAC.L_CODE
157 and (itemtype is null or WITV.NAME=itemtype);
158 l_count number := 1;
159 begin
160 -- Convert arguments from varchar2 to real type.
161 minthreshold_num := to_number(minthreshold);
162 maxthreshold_num := to_number(maxthreshold);
163
164 if (upper(substr(process_deferred, 1, 1)) = 'Y') then
165 process_deferred_bool := TRUE;
166 else
167 process_deferred_bool := FALSE;
168 end if;
169
170 if (upper(substr(process_timeout, 1, 1)) = 'Y') then
171 process_timeout_bool := TRUE;
172 else
173 process_timeout_bool := FALSE;
174 end if;
175
176 if (upper(substr(process_stuck, 1, 1)) = 'Y') then
177 process_stuck_bool := TRUE;
178 else
179 process_stuck_bool := FALSE;
180 end if;
181
182 -- Initialize retcode so that it changes only no error
183 retcode := '0';
184 errbuf := '';
185
186 -- Let's check parameter itemtype to determine how to launch the engine
187 for thisRec in c_RACTypes loop
188 ItemsNum.EXTEND(1);
189 ItemsNum(l_count) := thisRec.NAME;
190 l_count := l_count+1;
191 end loop;
192 if ItemsNum.COUNT<1 then
193 -- This is not a RAC enabled Item type or there is non active. This should not
194 -- happen because of the definition of the concurrent program parameter lookup.
195 errbuf := 'The given item type ('||itemtype||') is not enabled for WF RAC engine.';
196 retcode := '1';
197 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
198 elsif ItemsNum.COUNT=1 and partition_number is not null then
199 -- Submit a singular standard background engine
200 WF_ENGINE_RAC.Set_Context('INST_ID', partition_number);
201 Wf_Engine.Background(itemtype,
202 minthreshold_num,
203 maxthreshold_num,
204 process_deferred_bool,
205 process_timeout_bool,
206 process_stuck_bool);
207 else
208 -- There are multiple item types or partitions to process so we need to loop
209 for i in ItemsNum.FIRST..ItemsNum.LAST loop
210 for j in nvl(partition_number,1)..nvl(partition_number, n_partitions) loop
211 rac_req_id := FND_REQUEST.Submit_Request(APPLICATION=>'FND',
212 PROGRAM=>'FNDWFBG',
213 argument1 => ItemsNum(i),
214 argument2 => minthreshold,
215 argument3 => maxthreshold,
216 argument4 => process_deferred,
217 argument5 => process_timeout,
218 argument6 => process_stuck,
219 argument7 => to_char(j),
220 DESCRIPTION=>'Submitted by FNDWFBGRAC');
221 if rac_req_id >0 then --The submission succeeded
222 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Please check request ID '||to_char(rac_req_id)||
223 ' to view more details for background engine.');
224 else
225 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Failed to submit request for partition '||to_char(i));
226 wf_core.get_error(errname, errmsg, errstack);
227 if (errmsg is not null) then
228 errbuf := errmsg;
229 else
230 errbuf := sqlerrm;
231 end if;
232 retcode := '2';
233 end if;
234 end loop;
235 end loop;
236 end if;
237 exception
238 when others then
239 -- Retrieve error message into errbuf
240 wf_core.get_error(errname, errmsg, errstack);
241 if (errmsg is not null) then
242 errbuf := errmsg;
243 else
244 errbuf := sqlerrm;
245 end if;
246
247 -- Return 2 for error.
248 retcode := '2';
249 end BackgroundConcurrent;
250 end WF_ENGINE_RAC;
[Home] [Help]