Convert oracle procedure to SQL server procedure and give SQL server procedure?

  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;