33333333333

来源：互联网发布：西南交大网络远程教育编辑：程序博客网时间：2024/06/03 18:58

CREATE OR REPLACE PROCEDURE P_PBL_TREE2(parentId   IN bas_project.Project_Id%TYPE,
                                       userId     IN PLT_Employee.Emp_Id%TYPE,
                                       pOutCursor OUT CHZMB_DT.CurType) IS

/*--------------------------------------------
* 过程：获取按招标项目过程管理下一层节点记录集
* 入参；parentId 上级节点ID
* userId 当前用户ID (未用到)
* 出参：pOutCursor 返回下一层节点记录集

* --------------------------------------------*/

t_vDebugFlag VARCHAR2(10) := '0'; --是否记录错误日志标志
t_iErrNum INT := 0; --记录错误位置
t_vErrMsg VARCHAR2(512) := ''; --错误描述

t_rscount INT := 0; --是否是招标项目等于0不是大于0是
t_sql VARCHAR2(512) := ''; --项目项的sql

BEGIN
--建设项目情况
IF parentId = '0' THEN
    OPEN pOutCursor FOR
      SELECT p.project_id AS itemId,
             '0' AS parentId,
             p.prj_itm_nm AS itemName,
             p.prj_itm_cd AS sortCode,
             'P' AS nodeType,
             'prjList.jsp?prjId='|| p.project_id as nodeUrl,
             '0' AS isLeaf
        FROM bas_project p order by p.prj_itm_cd;
ELSE
    --区分招标项目与招标项目项
    select count(*)
      into t_rscount
      from pbl_reg_prj rp
     where rp.reg_prj_id = parentId;
    --如果上一层不是招标项目则该层为招标项目
    IF (t_rscount = 0) THEN
      OPEN pOutCursor FOR
        SELECT rp.reg_prj_id as itemId,
               rp.project_id as parentId,
               rp.pbl_prj_name as itemName,
               rp.reg_code as sortCode,
               'RP' as nodeType,
               'regPrjList.jsp?' as nodeUrl,
               '0' as isLeaf
          from pbl_reg_prj rp
         where rp.project_id = parentId order by rp.reg_code;

    ELSE
      --上级节点是招标项目则该层为项目项
      t_sql := 'SELECT w.work_item_id as itemId,''' || parentId ||
               ''' as parentId, w.item_name as itemName, w.Sort_Num as sortCode,''PI'' as nodeType,w.uri as nodeUrl, ''1'' as isLeaf   from pbl_work_item w order by w.Sort_Num ';
      OPEN pOutCursor FOR t_sql;

    END IF;

END IF;
--默认返回值
<<defaultMethod>>

IF t_vDebugFlag = '1' THEN
t_vErrMsg := '执行失败！';
END IF;
RETURN;

EXCEPTION
WHEN OTHERS THEN
    IF pOutCursor% ISOPEN THEN
      CLOSE pOutCursor;
    END IF;

    IF t_vDebugFlag = '1' THEN
      t_iErrNum := 3;
      t_vErrMsg := 'SQLCODE：' || TO_CHAR(SQLCODE) || '|' || SQLERRM;

    END IF;

    RETURN;

END;