1 create or replace PROCEDURE SP_WORKFLOW (entity_id varchar,entity_type varchar,userid number ) as
2
3 TYPE CurTyp IS REF CURSOR;
4 cur_cv CurTyp;
5
6
7 sql_fieldval varchar2(100);
8
9 sql_fetch varchar2(4000);
10 sql_condition varchar2(4000);
11 sql_keycondition varchar2(4000);
12
13 sql_finalsql varchar2(4000);
14
15
16 tmp_workflowinstanceid number;
17 tmp_rescount number;
18 tmpseq number;
19 tmp_wakeycolumn varchar2(100);
20 tmp_terminateflag number;
21 tmp_fkuserorroleid number;
22 tmp_wauserorroletype varchar2(500);
23 tmp_wadisplayquery varchar2(4000);
24
25
26 varlogicflag number;
27 varsameseqflag number;
28 varlogicseq varchar2(1000);
29 varsameseq varchar2(1000);
30
31 tmp_fkworkflowactivity number;
32 tmp_workflowid number;
33
34 tmp_keycolumn varchar2(1000);
35
36 tmp_entityid varchar2(1000);
37 tmp_strsucessaction varchar2(1000);
38 tmp_strdefaction varchar2(1000);
39 straction varchar2(1000);
40 inserted_flag number;
41 BEGIN
42 varlogicseq := ',';
43 varsameseq := ',';
44 varlogicflag := 0;
45 varsameseqflag:= 0;
46 sql_condition := '';
47 tmpseq:=0;
48 straction := null;
49 inserted_flag := 0;
50
51 update task set task_userorroleid = userid , task_userorroletype = 'USER' where
52 fk_entity = entity_id and task_startflag = 1 and task_completeflag = 0 and
53 deletedflag = 0;
54
55
56 dbms_output.put_line(' ----------------STARTS------------------ ' );
57
58 select max(pk_workflow) into tmp_workflowid from workflow where upper(workflow_type) = upper(entity_type);
59
60 dbms_output.put_line(' 1-tmp_workflowid--------- ' || tmp_workflowid );
61
62
63 select max(pk_wfinstanceid) into tmp_workflowinstanceid from workflow_instance where fk_entityid = entity_id and fk_workflowid = tmp_workflowid and wi_statusflag= 0;
64
65 dbms_output.put_line(' 2-tmp_workflowinstanceid- ' || tmp_workflowinstanceid );
66
67
68 if(tmp_workflowinstanceid is null) then
69 dbms_output.put_line(' inside if-- new instance ' );
70
71 tmp_workflowinstanceid := 0;
72 insert into workflow_instance(pk_wfinstanceid,fk_entityid,fk_workflowid,wi_statusflag)
73 values
74 (sq_workflowinstance.NEXTVAL,entity_id,tmp_workflowid,0);
75
76 select sq_workflowinstance.CURRVAL into tmp_workflowinstanceid from dual;
77 dbms_output.put_line( tmp_workflowinstanceid ||'IS NEW tmp_workflowinstanceid 3');
78 tmpseq := 0;
79
80 dbms_output.put_line( 'After insert tmp_workflowid ' || tmp_workflowinstanceid || '- ' || entity_id || ' - ' || tmp_workflowid );
81
82
83
84 for logicalcheck in
85 (select wa_sequence,COUNT(wa_sequence) seqcount
86 from workflow_activity wa,wfactivity_condition wac
87 where pk_workflowactivity=fk_workflowactivity and upper(wac.ea_type) =
88 'PRE' and wa.fk_workflowid = tmp_workflowid
89 and wa.deletedflag=0 and wac.deletedflag = 0 and
90 upper(wac.ea_logicaloperator) in('OR','AND')
91 group by wa_sequence order by wa_sequence)
92 loop
93 dbms_output.put_line( ' logical condition ' || logicalcheck.wa_sequence|| ' --- ' || logicalcheck.seqcount);
94 varlogicseq := varlogicseq || logicalcheck.wa_sequence || ',';
95 end loop;
96
97 dbms_output.put_line( ' varlogicseq >>>>' || varlogicseq);
98
99 for samecheck in (select wa_sequence,COUNT(wa_sequence) seqcount
100 from workflow_activity wa,wfactivity_condition wac
101 where pk_workflowactivity=fk_workflowactivity and upper(wac.ea_type) =
102 'PRE' and wa.fk_workflowid = tmp_workflowid
103 and wa.deletedflag=0 and wac.deletedflag = 0 and wac.ea_logicaloperator
104 is null
105 group by wa_sequence order by wa_sequence)
106 loop
107 if( samecheck.seqcount > 1 ) then
108 varsameseq := varsameseq || samecheck.wa_sequence || ',';
109 end if;
110 end loop;
111
112 dbms_output.put_line( ' varsameseq >>>>>' || varsameseq);
113
114
115 for activities in (select
116 pk_workflowactivity,wa.wa_name,wa.fk_workflowid,wa_sequence,wa_terminateflag,wa.wa_keytable,wa.wa_keycolumn,
117 wa.wa_displayquery,
118 wac.ea_type,wac.ea_startbrace,wac.ea_tablename,wac.ea_columnname,wac.ea_value,wac.ea_arithmeticoperator,wac.ea_logicaloperator,
119 wac.ea_endbrace,wa.wa_sucessforwardaction,wa.wa_defaultforwardaction
120 from workflow_activity wa,wfactivity_condition wac
121 where wa.pk_workflowactivity=wac.fk_workflowactivity
122 and upper(wac.ea_type) = 'PRE'
123 and wa.fk_workflowid = tmp_workflowid
124 and wa.deletedflag = 0
125 and wac.deletedflag = 0
126 order by wa_sequence,wac.pk_wfacondition)
127 loop
128 dbms_output.put_line( ' inside pre condition ' );
129 varlogicflag:= instr(varlogicseq,','||activities.wa_sequence ||',');
130 varsameseqflag := instr(varsameseq,','||activities.wa_sequence ||',');
131 dbms_output.put_line (' display sql ' ||
132 activities.wa_sucessforwardaction );
133
134
135 if( tmpseq != activities.wa_sequence or varsameseqflag > 0) then
136
137 if(length(sql_condition) >1 ) then
138 dbms_output.put_line (' display sql ' || tmp_wadisplayquery );
139 OPEN cur_cv FOR tmp_wadisplayquery;
140 LOOP
141 FETCH cur_cv INTO sql_fieldval ;
142 EXIT WHEN cur_cv%NOTFOUND;
143 dbms_output.put_line('display result ' || sql_fieldval );
144 END LOOP;
145 sql_finalsql := sql_fetch || sql_condition ||
146 sql_keycondition;
147 dbms_output.put_line (' check status ' || sql_finalsql);
148 OPEN cur_cv FOR sql_finalsql;
149
150 LOOP
151 FETCH cur_cv INTO tmp_rescount ;
152 EXIT WHEN cur_cv%NOTFOUND;
153 dbms_output.put_line ('pre condition count ' ||tmp_rescount );
154 if(tmp_rescount > 0) then
155
156
157 insert into task(pk_taskid,fk_workflowactivity,fk_entity,fk_wfinstanceid,task_startflag,task_name,createdon,createdby)
158 values (sq_task.NEXTVAL,tmp_fkworkflowactivity,entity_id,tmp_workflowinstanceid,1,sql_fieldval,sysdate,userid);
159 sql_finalsql := '';
160 sql_fetch :='';
161 sql_condition:='';
162 sql_keycondition :='';
163 sql_fieldval := '';
164 straction := tmp_strsucessaction;
165 inserted_flag :=1;
166 end if;
167 END LOOP;
168
169 end if;
170 tmpseq:= activities.wa_sequence;
171 end if;
172 sql_fetch := 'select count(' || activities.ea_columnname || ') from ' ||activities.ea_tablename || ' where ' ;
173
174 sql_keycondition:= keyconditionstr(activities.wa_keycolumn,entity_id);
175 dbms_output.put_line('sql_keycondition ************* ' ||
176 sql_keycondition );
177 dbms_output.put_line('varlogicflag ' || varlogicflag );
178 if(varlogicflag >0) then
179
180 sql_condition := sql_condition || ' ' || activities.ea_startbrace|| activities.ea_columnname ||' ' ||activities.ea_arithmeticoperator ||' (' || activities.ea_value||')' || activities.ea_endbrace || ' ' ||activities.ea_logicaloperator ;
181 else
182 sql_condition := activities.ea_columnname ||' ' ||activities.ea_arithmeticoperator || ' (' || activities.ea_value||')' ;
183 end if;
184 tmp_fkworkflowactivity:= activities.pk_workflowactivity;
185 tmp_wakeycolumn := activities.wa_keycolumn;
186 tmp_wadisplayquery:= populatesql(activities.wa_displayquery,entity_id);
187 tmp_strsucessaction := activities.wa_sucessforwardaction;
188 tmp_strdefaction := activities.wa_defaultforwardaction;
189 dbms_output.put_line(' tmp_strsucessaction = ' || tmp_strsucessaction || '
190 tmp_strdefaction = ' || tmp_strdefaction);
191 end loop;
192
193 dbms_output.put_line ('end pre condition loop : ' );
194 dbms_output.put_line (' pre condition ' || tmpseq || ' -sql_fetch: ' || sql_fetch ||' -sql_condition: ' || sql_condition);
195 dbms_output.put_line ('sql_keycondition: ' || sql_keycondition);
196 dbms_output.put_line('end of loop tmp_strsucessaction = ' || tmp_strsucessaction ||' tmp_strdefaction = ' || tmp_strdefaction);
197
198 if(tmpseq != 0 ) then
199
200 dbms_output.put_line ( 'tmp_wadisplayquery ' || tmp_wadisplayquery);
201
202 OPEN cur_cv FOR tmp_wadisplayquery;
203 LOOP
204 FETCH cur_cv INTO sql_fieldval ;
205 EXIT WHEN cur_cv%NOTFOUND;
206 dbms_output.put_line('display result ' || sql_fieldval );
207 END LOOP;
208
209 sql_finalsql := sql_fetch || sql_condition || sql_keycondition;
210 dbms_output.put_line ( 'Final sql ' || sql_finalsql);
211
212 OPEN cur_cv FOR sql_finalsql;
213 dbms_output.put_line (' result for pre condition ' );
214 LOOP
215 FETCH cur_cv INTO tmp_rescount ;
216 EXIT WHEN cur_cv%NOTFOUND;
217 dbms_output.put_line ('pre condition count ' || tmp_rescount );
218 if(tmp_rescount > 0) then
219
220 insert into task
221 (pk_taskid,fk_workflowactivity,fk_entity,fk_wfinstanceid,task_startflag,task_name,createdon,createdby)
222 values
223 (sq_task.NEXTVAL,tmp_fkworkflowactivity,entity_id,tmp_workflowinstanceid,
224 1,sql_fieldval,sysdate,userid);
225 sql_finalsql := '';
226 sql_fetch :='';
227 sql_condition:='';
228 sql_keycondition :='';
229 straction:= tmp_strsucessaction;
230 else
231 if(straction = null ) then
232 straction:= tmp_strdefaction;
233 end if;
234 end if;
235 END LOOP;
236 end if;
237 dbms_output.put_line('tmp_workflowinstanceid ' || tmp_workflowinstanceid );
238
239
240
241
242
243
244
245 dbms_output.put_line('new instance straction ' || straction );
246
247
248
249 else
250
251
252 dbms_output.put_line(' 22 inside else old instance ' || tmp_workflowinstanceid );
253
254 for pendingtasks in (select pk_taskid,WA_SEQUENCE from task,WORKFLOW_ACTIVITY
255 where PK_WORKFLOWACTIVITY = FK_WORKFLOWACTIVITY and fk_wfinstanceid =
256 tmp_workflowinstanceid and task_completeflag = 0 and task_startflag = 1)
257 loop
258 dbms_output.put_line(' pending taskid ' || pendingtasks.pk_taskid );
259 varlogicseq:= ',';
260 tmpseq := pendingtasks.WA_SEQUENCE;
261 for logicalcheck in (select wa_sequence,COUNT(wa_sequence) seqcount from
262 wfactivity_condition wfac , workflow_activity wfa, workflow_instance wi,task t
263 where wfac.deletedflag = 0
264 and wfac.fk_workflowactivity = wfa.pk_workflowactivity
265 and upper(wfac.ea_type) = 'POST'
266 and wi.pk_wfinstanceid = t.fk_wfinstanceid
267 and t.fk_workflowactivity = wfac.fk_workflowactivity
268 and wfa.fk_workflowid = tmp_workflowid
269 and upper(wfac.ea_logicaloperator) in('OR','AND')
270 and t.pk_taskid = pendingtasks.pk_taskid
271 group by wa_sequence order by wa_sequence)
272 loop
273 dbms_output.put_line( ' logical condition ' || logicalcheck.wa_sequence|| ' --- ' || logicalcheck.seqcount);
274 varlogicseq := varlogicseq || logicalcheck.wa_sequence || ',';
275 end loop;
276
277 dbms_output.put_line(' pending task varlogicseq ' || varlogicseq );
278
279 sql_fetch := '';
280 sql_condition := '';
281
282 for activities in
283 ( select
284 ea_type,ea_startbrace,ea_tablename,ea_columnname,ea_arithmeticoperator,
285 ea_value , ea_endbrace,ea_logicaloperator
286 ,wa_keytable,wa_keycolumn,
287 wfac.pk_wfacondition,wfac.fk_workflowactivity,wfa.pk_workflowactivity,wfa.wa_sequence,wfa.wa_terminateflag,wi.fk_workflowid,t.pk_taskid,t.fk_entity,t.fk_wfinstanceid
288 from wfactivity_condition wfac , workflow_activity wfa,
289 workflow_instance wi,task t
290 where wfac.deletedflag = 0
291 and wfac.fk_workflowactivity = wfa.pk_workflowactivity
292 and upper(wfac.ea_type) = 'POST'
293 and wi.pk_wfinstanceid = t.fk_wfinstanceid
294 and t.fk_workflowactivity = wfac.fk_workflowactivity
295 and t.pk_taskid = pendingtasks.pk_taskid
296 order by pk_wfacondition,ea_type,wa_sequence,wfac.pk_wfacondition)
297 loop
298
299 tmp_fkworkflowactivity:= activities.fk_workflowactivity;
300
301 tmp_terminateflag := activities.wa_terminateflag;
302
303
304 OPEN cur_cv FOR activities.ea_value ;
305 dbms_output.put_line (' result ' );
306 LOOP
307 FETCH cur_cv INTO sql_fieldval ;
308 EXIT WHEN cur_cv%NOTFOUND;
309 dbms_output.put_line (' SQL result ' || sql_fieldval );
310 END LOOP;
311 dbms_output.put_line (' activities.wa_sequencet ' ||activities.wa_sequence );
312 sql_fetch := 'select count(' || activities.ea_columnname ||') from ' || activities.ea_tablename || ' where ' ;
313 sql_keycondition:= keyconditionstr(activities.wa_keycolumn,entity_id);
314 varlogicflag:= instr(varlogicseq,','||activities.wa_sequence ||',');
315 if ( varlogicflag > 0) then
316 dbms_output.put_line( ' logical condition ');
317 sql_condition := sql_condition || ' ' ||activities.ea_startbrace || activities.ea_columnname ||'' || activities.ea_arithmeticoperator || ' ' ||sql_fieldval || activities.ea_endbrace || ' ' ||activities.ea_logicaloperator ;
318 else
319 sql_condition := activities.ea_columnname ||' ' ||activities.ea_arithmeticoperator || ' ' || sql_fieldval;
320 end if;
321 tmpseq:= activities.wa_sequence;
322 end loop;
323 sql_finalsql := sql_fetch || sql_condition || sql_keycondition;
324 dbms_output.put_line( pendingtasks.pk_taskid || ' sql postcondition for task ------------- ' || sql_finalsql);
325 if(length(sql_finalsql) >1 ) then
326
327 OPEN cur_cv FOR sql_finalsql;
328 dbms_output.put_line (' result for post condition ' );
329 LOOP
330 FETCH cur_cv INTO tmp_rescount ;
331 EXIT WHEN cur_cv%NOTFOUND;
332 dbms_output.put_line ('post condition count ' ||
333 tmp_rescount );
334 update task set task_completeflag = 1,completedon =
335 sysdate where pk_taskid in (pendingtasks.pk_taskid);
336 if(tmp_rescount > 0) then
337 dbms_output.put_line (' inside if ' ||
338 pendingtasks.pk_taskid );
339
340 update task set task_completeflag = 1,completedon =
341 sysdate where pk_taskid in (pendingtasks.pk_taskid);
342 dbms_output.put_line (' **********end of update task
343 complete seq******************* ' || tmpseq || '
344 ----taskid----------' || pendingtasks.pk_taskid);
345
346 if(tmp_terminateflag =1) then
347 dbms_output.put_line (' end of update workflow
348 instance ' || tmp_workflowinstanceid );
349 update workflow_instance set wi_statusflag = 1 where
350 pk_wfinstanceid = tmp_workflowinstanceid;
351 end if;
352 select wfa.wa_sucessforwardaction into straction from
353 workflow_activity wfa where pk_workflowactivity =
354 tmp_fkworkflowactivity;
355 dbms_output.put_line (tmp_fkworkflowactivity || '
356 sucess action ' || straction );
357
358 else
359 select wfa.wa_defaultforwardaction into straction from
360 workflow_activity wfa where pk_workflowactivity =
361 tmp_fkworkflowactivity;
362 dbms_output.put_line (tmp_fkworkflowactivity || '
363 sucess action ' || straction );
364
365 end if;
366 END LOOP;
367 end if;
368
369 end loop;
370
371
372
373
374 end if;
375 dbms_output.put_line(' end 2-- ' || tmp_workflowinstanceid );
376
377
378
379 dbms_output.put_line(' **************************next activity
380 ************************** ' );
381 if (inserted_flag = 0) then
382
383 for nextactivities in ( select pk_wfacondition,ea_type,ea_startbrace,ea_tablename,ea_columnname,ea_value,
384 ea_arithmeticoperator,ea_logicaloperator,ea_endbrace,
385 wa.pk_workflowactivity,wa.wa_keycolumn,wa.wa_displayquery,wa.wa_defaultforwardaction
386 from wfactivity_condition wfac, workflow_activity wa
387 where wfac.fk_workflowactivity = wa.pk_workflowactivity
388 and fk_workflowid = tmp_workflowid
389 and wa.wa_sequence != tmpseq
390 and upper(ea_type)= 'PRE' )
391 loop
392
393 dbms_output.put_line(' next Activity ' );
394 tmp_entityid := entity_id;
395 tmp_keycolumn := nextactivities.wa_keycolumn;
396 sql_keycondition:= keyconditionstr(nextactivities.wa_keycolumn,entity_id);
397
398 sql_fetch := 'select count(' || nextactivities.ea_columnname || ') from ' || nextactivities.ea_tablename || ' where ' ||
399 nextactivities.ea_columnname ||' ' || nextactivities.ea_arithmeticoperator ||
400 '(' || nextactivities.ea_value || ') ' ||sql_keycondition;
401
402 dbms_output.put_line(' new sql count ------------- ' || sql_fetch);
403
407 EXECUTE IMMEDIATE sql_fetch;
408
409 OPEN cur_cv FOR sql_fetch;
410 dbms_output.put_line (' result ``````````` ' );
411
412 LOOP
413 FETCH cur_cv INTO tmp_rescount ;
414 EXIT WHEN cur_cv%NOTFOUND;
415 dbms_output.put_line ( 'count ' ||tmp_rescount );
416 if (tmp_rescount > 0 ) then
417 dbms_output.put_line('Precondition statisfied ');
418
419 sql_fetch := populatesql(nextactivities.wa_displayquery,entity_id);
420 dbms_output.put_line('displaysql ' || sql_fetch);
421
422 OPEN cur_cv FOR sql_fetch;
423
424 LOOP
425 FETCH cur_cv INTO sql_fieldval ;
426 EXIT WHEN cur_cv%NOTFOUND;
427 dbms_output.put_line('displayresult ' || sql_fieldval);
428 END LOOP;
429
430 insert into task (pk_taskid,fk_workflowactivity,fk_entity,fk_wfinstanceid,
431 task_startflag,task_name,createdon,createdby)
432 values (sq_task.NEXTVAL,nextactivities.pk_workflowactivity,entity_id,tmp_workflowinstanceid,1,sql_fieldval,sysdate,userid);
433
434 dbms_output.put_line(' End of Insert ' );
435 else
436 dbms_output.put_line('Precondition not statisfied else');
437 end if;
438 END LOOP;
439
440 end loop;
441
442 end if;
443
444 dbms_output.put_line(' Action ' || straction );
445 straction := populatesql(straction,entity_id);
446 dbms_output.put_line(' 44444444444 ' );
447
448 End sp_workflow;
What I have tried:
I have tried SQL server query but not idea and using dynamic query this query you solve this query?