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;