Click here to Skip to main content
15,901,853 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
SQL
  1  create or replace PROCEDURE SP_WORKFLOW (entity_id varchar,entity_type varchar,userid number ) as
  2  
  3   TYPE CurTyp IS REF CURSOR;  -- define weak REF CURSOR type
  4   cur_cv   CurTyp;  -- declare cursor variable
  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  -- tmp variables
 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  -- variables for activity condition
 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  -- update task type
 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  -- single workflow change to multiple
 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  -- fetch the active workflow instance id [one entity have only one workflow]
 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  -- check the running workflowinstance
 68  if(tmp_workflowinstanceid is null)  then -- workflow not init
 69    dbms_output.put_line(' inside if-- new instance ' );
 70    -- start the workflow
 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   -- 2  check logical conditions
 83   -- HERE THEY CHECK THE PRE CONDITION AND WITH THE LOGICAL OPERATOR OR AND
 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  -- NO VALUE
 97   dbms_output.put_line( '  varlogicseq >>>>' ||  varlogicseq);
 98   -- more than one has same seq
 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  -- NO VALUE
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              -- sequence must start with 1
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 ;  -- fetch next row
152                              EXIT WHEN cur_cv%NOTFOUND;  -- exit loop when last row is fetched
153                                dbms_output.put_line ('pre condition count  ' ||tmp_rescount );
154                                 if(tmp_rescount > 0) then
155                                    -- insert new task
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                   -- logical condition
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   -- insert the last condition
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 ;  -- fetch next row
216                    EXIT WHEN cur_cv%NOTFOUND;  -- exit loop when last row is fetched
217                      dbms_output.put_line ('pre condition count  ' || tmp_rescount );
218                       if(tmp_rescount > 0) then
219                          -- insert new task
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  --   select wa_defaultforwardaction into straction from workflow_activity where
240  --pk_workflowactivity in(
241  --     select min(fk_workflowactivity) from task where fk_entity = entity_id and
242  --fk_wfinstanceid = tmp_workflowinstanceid and task_startflag = 1 and deletedflag = 0
243  --and task_completeflag =0);
244  
245      dbms_output.put_line('new instance straction ' || straction );
246  -- end of 2 (PRE Conditions).
247  -----------end of new instance
248  
249  else
250  -----old instance
251  -- workflow already init
252      dbms_output.put_line(' 22 inside else old instance ' || tmp_workflowinstanceid );
253      -- select the pending taks
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 := ''; -- clear
280             sql_condition := '';
281              -- check the conditions and update the tasks
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  --tmp_wakeycolumn := activities.wa_keycolumn;
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 ;  -- fetch next row
308                        EXIT WHEN cur_cv%NOTFOUND;  -- exit loop when last row is fetched
309                          dbms_output.put_line (' SQL result  ' || sql_fieldval );
310                  END LOOP; -- end of cursor
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; -- end of activities
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                      -- check the post condition status
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 ;  -- fetch next row
331                          EXIT WHEN cur_cv%NOTFOUND;  -- exit loop when last row isfetched
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                             -- update task
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                                -- update terminate flag
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                               -- next activities pre conditions
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; -- END OF POST CONDITION STATUS
367                 end if;
368  
369      end loop; -- end of pending tasks
370  
371  
372  
373  --- end of pending tasks
374  end if;
375   dbms_output.put_line(' end 2-- ' || tmp_workflowinstanceid );
376   --========end of 1
377  
378  ----------- next activity not and / or conditions
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                                 /*    if(length(straction) <1) then
404                                         straction:= nextactivities.wa_defaultforwardaction;
405                                     end if;
406                                  */
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            -- insert the task into task table
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 ;  -- fetchnext row
426                EXIT WHEN cur_cv%NOTFOUND; -- exit loop when last rowis fetched
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; -- end of next activities
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?
Posted
Updated 10-Apr-24 1:01am
v2

1 solution

This is not a code conversion service.

Try it yourself. If you get stuck on a particular part, then ask for help on that particular part, explaining precisely what you have tried and where you are stuck.

Dumping nearly 450 lines of unformatted, unexplained code, and expecting someone to convert it for you is just rude!
 
Share this answer
 
Comments
Maciej Los 10-Apr-24 16:28pm    
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900