办件流转中执行步骤中的所挂接的存储过程

 /*
     办件流转
  */
  procedure P_DoFileFlow(varError      out varchar2,
                         varWaitID     in varchar2,
                         varFlowID     in varchar2,
                         varUserID     in varchar2,
                         varDepID      in varchar2,
                         varModifytime in varchar2) as
  
    varPackagename   varchar2(50);
    varFunctionname  varchar2(50);
    varFunctionParam varchar2(200);
    numDataCount     number(12);
  
    varStepID       varchar2(20);
    varTargetStepID varchar2(20);
    varFileID       varchar2(20);
    numControlID    number(12);
    varUserRealname oa_user.user_realname%type;
    varUserName     oa_user.user_name%type;
    varDepName      oa_department.dep_name%type;
    varIslast       ly_f_flowinfo.islast%type;
    varFlowName     ly_f_flowinfo.flowname%type;
  
    varAdviseID oa_advise.advise_id%type;
    varDestId   ly_f_flowinfo.dest_content%type;
    varDestType ly_f_flowinfo.dest_type%type;
    varOpinion  oa_wait.opinion%type;
  
    varFileTitle ly_s_docbase.file_title%type;
    varSql       varchar2(2000);
    varAbbSql    varchar2(2000);
  begin
    select count(1)
      into numDataCount
      from oa_wait
     where wait_id = varWaitID;
  
    if numDataCount = 0 then
      raise_application_error('-20001', '该办件不存在或已被转至其他步骤');
    end if;
  
    select file_id, file_title
      into varFileID, varFileTitle
      from oa_wait
     where wait_id = varWaitID;
  
    select count(1)
      into numDataCount
      from ly_s_docbase t
     where file_id = varFileID
       and t.modifytime = varModifytime;
  
    if numDataCount = 0 then
      raise_application_error('-20001', pk_systemoperation.ConModify);
    end if;
  
    /*本流是否需要写意见，1为写意见*/
    select count(1)
      into numDataCount
      from ly_f_flowinfo t
     where flowid = varFlowID
       and t.opinion = '1';
    select opinion into varOpinion from oa_wait where wait_id = varWaitID;
    /*需要写意见*/
    if numDataCount > 0 then
    
      if varOpinion is null then
        varError := '请填写转件意见';
        return;
      end if;
    end if;
  
    select f.stepid, f.tarstepid, f.control_id, f.islast
      into varStepID, varTargetStepID, numControlID, varIslast
      from ly_f_flowinfo f
     where flowid = varFlowID;
  
    select user_name, user_realname
      into varUserName, varUserRealname
      from oa_user
     where user_id = varUserID;
  
    select dep_name
      into varDepName
      from oa_department
     where dep_id = varDepID;
  
    for cc in (select *
                 from LY_F_FLOWINFO_todolist
                where flowid = varFlowID
                  and VALID_FLAG = 0
                order by orderno) loop
      begin
        select upper(substr(cc.todo, 1, instr(cc.todo, '.') - 1)),
               upper(substr(cc.todo,
                            instr(cc.todo, '.') + 1,
                            length(cc.todo)))
          into varPackagename, varFunctionname
          from dual;
      
        select count(*)
          into numDataCount
          from ALL_PROCEDURES
         where procedure_name = upper(varFunctionname)
           and object_name = upper(varPackagename);
        if numDataCount = 0 then
          raise_application_error('-20001', '不存在函数' || cc.todo);
        end if;
      
        varFunctionParam := cc.todoparameter;
      
        varFunctionParam := replace(varFunctionParam,
                                    'varSTEP_ID',
                                    varStepID);
        varFunctionParam := replace(varFunctionParam,
                                    'varDESTSTEP_ID',
                                    varTargetStepID);
        varFunctionParam := replace(varFunctionParam,
                                    'varCONTROL_ID',
                                    numControlID);
        varFunctionParam := replace(varFunctionParam,
                                    'varFILE_ID',
                                    varFileID);
        varFunctionParam := replace(varFunctionParam,
                                    'varWAIT_ID',
                                    varWaitID);
        varFunctionParam := replace(varFunctionParam,
                                    'varUSER_NAME',
                                    '''' || varUserName || '''');
        varFunctionParam := replace(varFunctionParam,
                                    'varUSER_REALNAME',
                                    '''' || varUserRealname || '''');
        varFunctionParam := replace(varFunctionParam,
                                    'varUSER_ID',
                                    varUserID);
        varFunctionParam := replace(varFunctionParam, 'varDEP_ID', varDepID);
        varFunctionParam := replace(varFunctionParam,
                                    'varDEP_NAME',
                                    '''' || varDepName || '''');
      
        varSql := 'declare varError  varchar2(2000); begin varError:=' ||
                  cc.todo || '(' || varFunctionParam || ');
     if varError is not null then raise_application_error(''-20001'',varError) ; end if;
      end;';
        execute immediate varSql;
      
      exception
        when others then
          varError := sqlerrm;
          raise_application_error('-20001', varError);
      end;
    
    end loop;
  
    select f.dest_content,
           f.dest_type,
           f.flowname,
           SEQ_MAXID_OA_ADVISE.Nextval
      into varDestId, varDestType, varFlowName, varAdviseID
      from ly_f_flowinfo f
     where flowid = varFlowID;
  
    if varIslast = '0' then
      /*已经是最后一步啦，删件、更新走人；*/
      update ly_s_docbase set is_flow = '0' where file_id = varFileID;
    else
      /*
          Dest_Type 171           1人员           部门           角色          列信息          拟稿部门           上一步操作者
          人员代词
          部门代词
          本部门
          上级部门
          abb中用100之后的代词
      */
      if varDestType = '5' then
        select dep_id
          into varDestId
          from ly_s_docbase
         where file_id = varFileID;
        varDestType := 2;
      end if;
    
      if varDestType = '6' then
        select w.source_id
          into varDestId
          from oa_wait w
         where wait_id = varWaitID;
        varDestType := 2;
      end if;
    
      if varDestType in ('1', '3', '7') then
        raise_application_error('-20001',
                                '人员、角色、人员代词已停用，请联系管理员修改流程');
      end if;
    
      if varDestType = '8' then
        select ABB_CONTENT
          into varAbbSql
          from oa_abbreviate abb
         where abb.abb_id = varDestId;
      
        execute immediate varAbbSql
          into varDestId
          using varFileID;
        varDestType := 2;
      
      end if;
    
      --本部门
      if varDestType = '9' then
        varDestType := 2;
        varDestId   := varDepID;
      end if;
      --上级部门
      if varDestType = '10' then
        varDestType := 2;
        select father_id
          into varDestId
          from oa_department
         where dep_id = varDepID;
      end if;
    
      insert into oa_wait
        (wait_id,
         control_id,
         step_id,
         file_id,
         file_title,
         wait_date,
         dest_id,
         dest_type,
         source_id,
         advise_id,
         flowid,
         DB_NAME)
      values
        (SEQ_MAXID_OA_WAIT.nextval,
         numControlID,
         varTargetStepID,
         varFileID,
         varFileTitle,
         to_char(sysdate, 'yyyy-mm-dd hh24:mi;ss'),
         varDestId,
         varDestType,
         varDepID,
         varAdviseID,
         varFlowID,
         (select controlname
            from ly_f_controlflow
           where control_id = numControlID));
    
    end if;
  
    insert into oa_advise
      (advise_id,
       control_id,
       file_id,
       advise,
       user_name,
       user_id,
       dep_id,
       dep_name,
       ad_date,
       tarcontestname,
       targetstep,
       doflow,
       sourcestep,
       flowid,
       userrealname,
       tarcontestid)
    values
      (varAdviseID,
       numControlID,
       varFileID,
       varOpinion,
       varUserName,
       varUserID,
       varDepID,
       varDepName,
       to_char(sysdate, 'yyyy-mm-dd hh24:mi;ss'),
       (select dep_name from oa_department where dep_id = varDestId),
       varTargetStepID,
       varFlowName,
       varStepID,
       varFlowID,
       varUserRealname,
       varDestId);
  
    delete from oa_wait where wait_id = varWaitID;
  
    commit;
  exception
    when others then
      rollback;
      varError := sqlerrm;
  end P_DoFileFlow;