create or replace package body "PAC_ORGANUNIT" as
PROCEDURE PRO_RIGHTORGANUNITTREE (OUTORGANUNITTREE OUT CURSOR_ORGANUNITTREE,LOGINID NUMBER,ORGANUNITPID NUMBER DEFAULT NULL,FILTERTYPE CHAR DEFAULT '1',ISSTATCHILDNODENUM CHAR DEFAULT '0',SHOWCOLUMNNAME VARCHAR DEFAULT NULL,ADDWHERE VARCHAR DEFAULT NULL,SORTINFO VARCHAR DEFAULT NULL,JOINTABLEINFO VARCHAR DEFAULT NULL,KIND CHAR DEFAULT NULL) AS
COUNTVALUE NUMBER(9);
SQLSYNTAX CLOB;
COLUMNSYNTAX CLOB;
BEGIN
--注使用前需提交以下事务临时表语法;
--CREATE GLOBAL TEMPORARY TABLE TEMP_RIGHTORGANUNITTREE ON COMMIT DELETE ROWS AS
--SELECT ORGANUNIT.ORGANID ,ORGANUNIT.VID ,ORGANUNIT.PID ,ORGANUNIT.CODE ,ORGANUNIT.NO ,ORGANUNIT.NAME ,
-- ORGANUNIT.ASSISTANT_SIGN ,ORGANUNIT.KIND ,ORGANUNIT.SORT ,ORGANUNIT.ISENABLED ,0 CHILDNODENUM
--FROM VIEW_ORGANUNIT_LIST ORGANUNIT WHERE 1 = 2; SELECT COUNT(*) INTO COUNTVALUE FROM LOGIN_LIST WHERE LOGIN_LIST.LOGIN_ID = LOGINID AND LOGIN_LIST.IS_ADMIN = '1' AND ROWNUM = 1; IF SHOWCOLUMNNAME IS NULL THEN
COLUMNSYNTAX := 'SELECT ' || 'organUnit.organID , ' ||
'organUnit.vid , ' ||
'organUnit.pid , ' ||
'organUnit.code , ' ||
'organUnit.no , ' ||
'organUnit.name , ' ||
'organUnit.assistant_sign , ' ||
'organUnit.kind , ' ||
'organUnit.sort , ' ||
'organUnit.isenabled , ' ||
'( CASE WHEN ''' || ISSTATCHILDNODENUM || ''' = ''1'' THEN ( SELECT COUNT(*) FROM view_organUnit_list temp WHERE temp.isenabled = ''1'' AND temp.vid != temp.pid AND temp.pid = organUnit.vid ) ELSE 0 END ) childNodeNum';
ELSE
COLUMNSYNTAX := 'SELECT ' || SHOWCOLUMNNAME;
END IF; IF COUNTVALUE > 0 THEN
--ADMIN单位部门树数据
SELECT COLUMNSYNTAX ||
' FROM view_organUnit_list organUnit ' || NVL( JOINTABLEINFO , '' ) ||
' WHERE ( organUnit.isenabled = ''1'' ) ' || NVL2( ORGANUNITPID , ( ' AND organUnit.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' )
INTO SQLSYNTAX
FROM DUAL;
ELSE
--权限单位部门树数据
IF LOGINID > 0 THEN
SELECT COLUMNSYNTAX ||
' FROM ( SELECT DISTINCT view_organUnit_list.organID , ' ||
'view_organUnit_list.vid , ' ||
'view_organUnit_list.pid , ' ||
'view_organUnit_list.code , ' ||
'view_organUnit_list.no , ' ||
'view_organUnit_list.name , ' ||
'view_organUnit_list.assistant_sign , ' ||
'view_organUnit_list.kind , ' ||
'view_organUnit_list.sort , ' ||
'view_organUnit_list.isenabled ' ||
' FROM view_organUnit_list , ' ||
'( SELECT organ_list.organ_id id , organ_list.organ_code code ' ||
'FROM sys_use_organ_list , organ_list ' ||
'WHERE sys_use_organ_list.organ_id = organ_list.organ_id AND ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( organ_list.is_enabled = ''1'' )' ELSE '( organ_list.is_enabled = ''1'' OR ( organ_list.is_enabled = ''0'' AND organ_list.interface_pk_id IS NOT NULL ) )' END ) || 'AND sys_use_organ_list.login_id = ' || TO_CHAR( LOGINID ) ||
' UNION ' ||
'SELECT unit_list.unit_id id , unit_list.unit_code code
FROM sys_use_unit_list , unit_list
WHERE sys_use_unit_list.unit_id = unit_list.unit_id AND ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( unit_list.is_enabled = ''1'' )' ELSE '( unit_list.is_enabled = ''1'' OR ( unit_list.is_enabled = ''0'' AND unit_list.interface_pk_id IS NOT NULL ) )' END ) || 'AND sys_use_unit_list.login_id = ' || TO_CHAR( LOGINID ) || ' ) tempOrganUnit ' ||
' WHERE ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( view_organUnit_list.isenabled = ''1'' )' ELSE '( view_organUnit_list.isenabled = ''1'' OR ( view_organUnit_list.isenabled = ''0'' AND view_organUnit_list.interfaceID IS NOT NULL ) )' END ) ||
'AND ( tempOrganUnit.code like view_organUnit_list.code || ''%'' ) ' || NVL2( ORGANUNITPID , ( ' AND view_organUnit_list.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' ) || ' ) organUnit '
|| NVL( JOINTABLEINFO , '' ) || ' WHERE ( 1 = 1 ) '
INTO SQLSYNTAX
FROM DUAL;
ELSE
SELECT COLUMNSYNTAX ||
' FROM ( SELECT distinct view_organUnit_list.organID , ' ||
'view_organUnit_list.vid , ' ||
'view_organUnit_list.pid , ' ||
'view_organUnit_list.code , ' ||
'view_organUnit_list.no , ' ||
'view_organUnit_list.name , ' ||
'view_organUnit_list.assistant_sign , ' ||
'view_organUnit_list.kind , ' ||
'view_organUnit_list.sort , ' ||
'view_organUnit_list.isenabled ' ||
' FROM view_organUnit_list , ' ||
' ( select view_organUnit_list.code ' ||
' from view_organUnit_list ,role_organ_list , role_list , ' ||
' ( select employee_list.positionnum ' ||
' from employee_list ' ||
' where employee_list.work_code in ( select temp.work_code from employee_list temp where temp.employee_id = loginID ) ) tempDuty ' ||
' where ( view_organUnit_list.organid = role_organ_list.organ_id or view_organUnit_list.vid = role_organ_list.unit_id ) and ' ||
' role_organ_list.role_id = role_list.role_id and ' ||
' role_list.duty = tempDuty.positionnum ) tempRole ' ||
' WHERE ( view_organUnit_list.code like tempRole.code || ''%'' or tempRole.code like view_organUnit_list.code || ''%'' ) and ' ||
( CASE WHEN ( FILTERTYPE = '1' ) THEN ' view_organUnit_list.isenabled = ''1'' ' ELSE ' (view_organUnit_list.isenabled = ''1'' or (view_organUnit_list.isenabled = ''0'' and view_organUnit_list.interface_pk_id is not null)) ' END ) ||
NVL2( ORGANUNITPID , ( ' and view_organUnit_list.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' ) || ' ) organUnit '
|| NVL( JOINTABLEINFO , '' ) || ' WHERE ( 1 = 1 ) '
INTO SQLSYNTAX
FROM DUAL;
END IF;
END IF; SELECT SQLSYNTAX || ( CASE WHEN ' and ' != SUBSTR( LOWER( LTRIM(ADDWHERE) ) , 0 , 4 ) AND ADDWHERE IS NOT NULL THEN ' AND ' END ) || ADDWHERE || NVL2( SORTINFO , ( ' order by ' || SORTINFO ) , '' )
INTO SQLSYNTAX
FROM DUAL; DBMS_OUTPUT.PUT_LINE(SQLSYNTAX); IF KIND IS NULL THEN
OPEN OUTORGANUNITTREE FOR TO_CHAR( SQLSYNTAX ); DBMS_OUTPUT.PUT_LINE(TO_CHAR( SQLSYNTAX )); ELSE
SQLSYNTAX := ' insert into temp_RightOrganUnitTree(organID ,vid ,pid ,code ,no ,name ,assistant_sign ,kind ,sort ,isenabled ,childNodeNum) ' || SQLSYNTAX; DBMS_OUTPUT.PUT_LINE(SQLSYNTAX);
EXECUTE IMMEDIATE TO_CHAR( SQLSYNTAX );
END IF;
END;
PROCEDURE PRO_RIGHTORGANUNITTREE (OUTORGANUNITTREE OUT CURSOR_ORGANUNITTREE,LOGINID NUMBER,ORGANUNITPID NUMBER DEFAULT NULL,FILTERTYPE CHAR DEFAULT '1',ISSTATCHILDNODENUM CHAR DEFAULT '0',SHOWCOLUMNNAME VARCHAR DEFAULT NULL,ADDWHERE VARCHAR DEFAULT NULL,SORTINFO VARCHAR DEFAULT NULL,JOINTABLEINFO VARCHAR DEFAULT NULL,KIND CHAR DEFAULT NULL) AS
COUNTVALUE NUMBER(9);
SQLSYNTAX CLOB;
COLUMNSYNTAX CLOB;
BEGIN
--注使用前需提交以下事务临时表语法;
--CREATE GLOBAL TEMPORARY TABLE TEMP_RIGHTORGANUNITTREE ON COMMIT DELETE ROWS AS
--SELECT ORGANUNIT.ORGANID ,ORGANUNIT.VID ,ORGANUNIT.PID ,ORGANUNIT.CODE ,ORGANUNIT.NO ,ORGANUNIT.NAME ,
-- ORGANUNIT.ASSISTANT_SIGN ,ORGANUNIT.KIND ,ORGANUNIT.SORT ,ORGANUNIT.ISENABLED ,0 CHILDNODENUM
--FROM VIEW_ORGANUNIT_LIST ORGANUNIT WHERE 1 = 2; SELECT COUNT(*) INTO COUNTVALUE FROM LOGIN_LIST WHERE LOGIN_LIST.LOGIN_ID = LOGINID AND LOGIN_LIST.IS_ADMIN = '1' AND ROWNUM = 1; IF SHOWCOLUMNNAME IS NULL THEN
COLUMNSYNTAX := 'SELECT ' || 'organUnit.organID , ' ||
'organUnit.vid , ' ||
'organUnit.pid , ' ||
'organUnit.code , ' ||
'organUnit.no , ' ||
'organUnit.name , ' ||
'organUnit.assistant_sign , ' ||
'organUnit.kind , ' ||
'organUnit.sort , ' ||
'organUnit.isenabled , ' ||
'( CASE WHEN ''' || ISSTATCHILDNODENUM || ''' = ''1'' THEN ( SELECT COUNT(*) FROM view_organUnit_list temp WHERE temp.isenabled = ''1'' AND temp.vid != temp.pid AND temp.pid = organUnit.vid ) ELSE 0 END ) childNodeNum';
ELSE
COLUMNSYNTAX := 'SELECT ' || SHOWCOLUMNNAME;
END IF; IF COUNTVALUE > 0 THEN
--ADMIN单位部门树数据
SELECT COLUMNSYNTAX ||
' FROM view_organUnit_list organUnit ' || NVL( JOINTABLEINFO , '' ) ||
' WHERE ( organUnit.isenabled = ''1'' ) ' || NVL2( ORGANUNITPID , ( ' AND organUnit.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' )
INTO SQLSYNTAX
FROM DUAL;
ELSE
--权限单位部门树数据
IF LOGINID > 0 THEN
SELECT COLUMNSYNTAX ||
' FROM ( SELECT DISTINCT view_organUnit_list.organID , ' ||
'view_organUnit_list.vid , ' ||
'view_organUnit_list.pid , ' ||
'view_organUnit_list.code , ' ||
'view_organUnit_list.no , ' ||
'view_organUnit_list.name , ' ||
'view_organUnit_list.assistant_sign , ' ||
'view_organUnit_list.kind , ' ||
'view_organUnit_list.sort , ' ||
'view_organUnit_list.isenabled ' ||
' FROM view_organUnit_list , ' ||
'( SELECT organ_list.organ_id id , organ_list.organ_code code ' ||
'FROM sys_use_organ_list , organ_list ' ||
'WHERE sys_use_organ_list.organ_id = organ_list.organ_id AND ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( organ_list.is_enabled = ''1'' )' ELSE '( organ_list.is_enabled = ''1'' OR ( organ_list.is_enabled = ''0'' AND organ_list.interface_pk_id IS NOT NULL ) )' END ) || 'AND sys_use_organ_list.login_id = ' || TO_CHAR( LOGINID ) ||
' UNION ' ||
'SELECT unit_list.unit_id id , unit_list.unit_code code
FROM sys_use_unit_list , unit_list
WHERE sys_use_unit_list.unit_id = unit_list.unit_id AND ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( unit_list.is_enabled = ''1'' )' ELSE '( unit_list.is_enabled = ''1'' OR ( unit_list.is_enabled = ''0'' AND unit_list.interface_pk_id IS NOT NULL ) )' END ) || 'AND sys_use_unit_list.login_id = ' || TO_CHAR( LOGINID ) || ' ) tempOrganUnit ' ||
' WHERE ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( view_organUnit_list.isenabled = ''1'' )' ELSE '( view_organUnit_list.isenabled = ''1'' OR ( view_organUnit_list.isenabled = ''0'' AND view_organUnit_list.interfaceID IS NOT NULL ) )' END ) ||
'AND ( tempOrganUnit.code like view_organUnit_list.code || ''%'' ) ' || NVL2( ORGANUNITPID , ( ' AND view_organUnit_list.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' ) || ' ) organUnit '
|| NVL( JOINTABLEINFO , '' ) || ' WHERE ( 1 = 1 ) '
INTO SQLSYNTAX
FROM DUAL;
ELSE
SELECT COLUMNSYNTAX ||
' FROM ( SELECT distinct view_organUnit_list.organID , ' ||
'view_organUnit_list.vid , ' ||
'view_organUnit_list.pid , ' ||
'view_organUnit_list.code , ' ||
'view_organUnit_list.no , ' ||
'view_organUnit_list.name , ' ||
'view_organUnit_list.assistant_sign , ' ||
'view_organUnit_list.kind , ' ||
'view_organUnit_list.sort , ' ||
'view_organUnit_list.isenabled ' ||
' FROM view_organUnit_list , ' ||
' ( select view_organUnit_list.code ' ||
' from view_organUnit_list ,role_organ_list , role_list , ' ||
' ( select employee_list.positionnum ' ||
' from employee_list ' ||
' where employee_list.work_code in ( select temp.work_code from employee_list temp where temp.employee_id = loginID ) ) tempDuty ' ||
' where ( view_organUnit_list.organid = role_organ_list.organ_id or view_organUnit_list.vid = role_organ_list.unit_id ) and ' ||
' role_organ_list.role_id = role_list.role_id and ' ||
' role_list.duty = tempDuty.positionnum ) tempRole ' ||
' WHERE ( view_organUnit_list.code like tempRole.code || ''%'' or tempRole.code like view_organUnit_list.code || ''%'' ) and ' ||
( CASE WHEN ( FILTERTYPE = '1' ) THEN ' view_organUnit_list.isenabled = ''1'' ' ELSE ' (view_organUnit_list.isenabled = ''1'' or (view_organUnit_list.isenabled = ''0'' and view_organUnit_list.interface_pk_id is not null)) ' END ) ||
NVL2( ORGANUNITPID , ( ' and view_organUnit_list.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' ) || ' ) organUnit '
|| NVL( JOINTABLEINFO , '' ) || ' WHERE ( 1 = 1 ) '
INTO SQLSYNTAX
FROM DUAL;
END IF;
END IF; SELECT SQLSYNTAX || ( CASE WHEN ' and ' != SUBSTR( LOWER( LTRIM(ADDWHERE) ) , 0 , 4 ) AND ADDWHERE IS NOT NULL THEN ' AND ' END ) || ADDWHERE || NVL2( SORTINFO , ( ' order by ' || SORTINFO ) , '' )
INTO SQLSYNTAX
FROM DUAL; DBMS_OUTPUT.PUT_LINE(SQLSYNTAX); IF KIND IS NULL THEN
OPEN OUTORGANUNITTREE FOR TO_CHAR( SQLSYNTAX ); DBMS_OUTPUT.PUT_LINE(TO_CHAR( SQLSYNTAX )); ELSE
SQLSYNTAX := ' insert into temp_RightOrganUnitTree(organID ,vid ,pid ,code ,no ,name ,assistant_sign ,kind ,sort ,isenabled ,childNodeNum) ' || SQLSYNTAX; DBMS_OUTPUT.PUT_LINE(SQLSYNTAX);
EXECUTE IMMEDIATE TO_CHAR( SQLSYNTAX );
END IF;
END;
PROCEDURE PRO_RIGHTORGANUNITTREE2 (OUTORGANUNITTREE OUT CURSOR_ORGANUNITTREE,LOGID NUMBER,LOGINID NUMBER,ORGANUNITPID NUMBER DEFAULT NULL,FILTERTYPE CHAR DEFAULT '1',ISSTATCHILDNODENUM CHAR DEFAULT '0',SHOWCOLUMNNAME VARCHAR DEFAULT NULL,ADDWHERE VARCHAR DEFAULT NULL,SORTINFO VARCHAR DEFAULT NULL,JOINTABLEINFO VARCHAR DEFAULT NULL,KIND CHAR DEFAULT NULL) AS
COUNTVALUE NUMBER(9);
SQLSYNTAX CLOB;
COLUMNSYNTAX CLOB;
BEGIN
--注使用前需提交以下事务临时表语法;
--CREATE GLOBAL TEMPORARY TABLE TEMP_RIGHTORGANUNITTREE ON COMMIT DELETE ROWS AS
--SELECT ORGANUNIT.ORGANID ,ORGANUNIT.VID ,ORGANUNIT.PID ,ORGANUNIT.CODE ,ORGANUNIT.NO ,ORGANUNIT.NAME ,
-- ORGANUNIT.ASSISTANT_SIGN ,ORGANUNIT.KIND ,ORGANUNIT.SORT ,ORGANUNIT.ISENABLED ,0 CHILDNODENUM
--FROM VIEW_ORGANUNIT_LIST ORGANUNIT WHERE 1 = 2; SELECT COUNT(*) INTO COUNTVALUE FROM LOGIN_LIST WHERE LOGIN_LIST.LOGIN_ID = LOGINID AND LOGIN_LIST.IS_ADMIN = '1' AND ROWNUM = 1; IF SHOWCOLUMNNAME IS NULL THEN
COLUMNSYNTAX := 'SELECT ' || 'organUnit.organID , ' ||
'organUnit.vid , ' ||
'organUnit.pid , ' ||
'organUnit.code , ' ||
'organUnit.no , ' ||
'organUnit.name , ' ||
'organUnit.assistant_sign , ' ||
'organUnit.kind , ' ||
'organUnit.sort , ' ||
'organUnit.isenabled , ' ||
'( CASE WHEN ''' || ISSTATCHILDNODENUM || ''' = ''1'' THEN ( SELECT COUNT(*) FROM view_organUnit_list temp WHERE temp.isenabled = ''1'' AND temp.vid != temp.pid AND temp.pid = organUnit.vid ) ELSE 0 END ) childNodeNum';
ELSE
COLUMNSYNTAX := 'SELECT ' || SHOWCOLUMNNAME;
END IF; IF COUNTVALUE > 0 THEN
--ADMIN单位部门树数据
SELECT COLUMNSYNTAX ||
' FROM view_organUnit_list organUnit ' || NVL( JOINTABLEINFO , '' ) ||
' WHERE ( organUnit.isenabled = ''1'' ) ' || NVL2( ORGANUNITPID , ( ' AND organUnit.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' )
INTO SQLSYNTAX
FROM DUAL;
ELSE
--权限单位部门树数据
IF LOGINID > 0 THEN
SELECT COLUMNSYNTAX ||
' FROM ( SELECT DISTINCT view_organUnit_list.organID , ' ||
'view_organUnit_list.vid , ' ||
'view_organUnit_list.pid , ' ||
'view_organUnit_list.code , ' ||
'view_organUnit_list.no , ' ||
'view_organUnit_list.name , ' ||
'view_organUnit_list.assistant_sign , ' ||
'view_organUnit_list.kind , ' ||
'view_organUnit_list.sort , ' ||
'view_organUnit_list.isenabled ' ||
' FROM view_organUnit_list , ' ||
'( SELECT organ_list.organ_id id , organ_list.organ_code code ' ||
'FROM sys_use_organ_list , organ_list ' ||
'WHERE sys_use_organ_list.organ_id = organ_list.organ_id AND ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( organ_list.is_enabled = ''1'' )' ELSE '( organ_list.is_enabled = ''1'' OR ( organ_list.is_enabled = ''0'' AND organ_list.interface_pk_id IS NOT NULL ) )' END ) || 'AND sys_use_organ_list.login_id = ' || TO_CHAR( LOGINID ) ||
' UNION ' ||
'SELECT unit_list.unit_id id , unit_list.unit_code code
FROM sys_use_unit_list , unit_list
WHERE sys_use_unit_list.unit_id = unit_list.unit_id AND ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( unit_list.is_enabled = ''1'' )' ELSE '( unit_list.is_enabled = ''1'' OR ( unit_list.is_enabled = ''0'' AND unit_list.interface_pk_id IS NOT NULL ) )' END ) || 'AND sys_use_unit_list.login_id = ' || TO_CHAR( LOGINID ) || ' ) tempOrganUnit ' ||
' WHERE ' || ( CASE WHEN ( FILTERTYPE = '1' ) THEN '( view_organUnit_list.isenabled = ''1'' )' ELSE '( view_organUnit_list.isenabled = ''1'' OR ( view_organUnit_list.isenabled = ''0'' AND view_organUnit_list.interfaceID IS NOT NULL ) )' END ) ||
'AND ( tempOrganUnit.code like view_organUnit_list.code || ''%'' ) ' || NVL2( ORGANUNITPID , ( ' AND view_organUnit_list.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' ) || ' ) organUnit '
|| NVL( JOINTABLEINFO , '' ) || ' WHERE ( 1 = 1 ) '
INTO SQLSYNTAX
FROM DUAL;
ELSE
SELECT COLUMNSYNTAX ||
' FROM ( SELECT distinct view_organUnit_list.organID , ' ||
'view_organUnit_list.vid , ' ||
'view_organUnit_list.pid , ' ||
'view_organUnit_list.code , ' ||
'view_organUnit_list.no , ' ||
'view_organUnit_list.name , ' ||
'view_organUnit_list.assistant_sign , ' ||
'view_organUnit_list.kind , ' ||
'view_organUnit_list.sort , ' ||
'view_organUnit_list.isenabled ' ||
' FROM view_organUnit_list , ' ||
' ( select view_organUnit_list.code ' ||
' from view_organUnit_list ,role_organ_list , role_list , ' ||
' ( select employee_list.positionnum ' ||
' from employee_list ' ||
' where employee_list.work_code in ( select temp.work_code from employee_list temp where temp.employee_id = loginID ) ) tempDuty ' ||
' where ( view_organUnit_list.organid = role_organ_list.organ_id or view_organUnit_list.vid = role_organ_list.unit_id ) and ' ||
' role_organ_list.role_id = role_list.role_id and ' ||
' role_list.duty = tempDuty.positionnum ) tempRole ' ||
' WHERE ( view_organUnit_list.code like tempRole.code || ''%'' or tempRole.code like view_organUnit_list.code || ''%'' ) and ' ||
( CASE WHEN ( FILTERTYPE = '1' ) THEN ' view_organUnit_list.isenabled = ''1'' ' ELSE ' (view_organUnit_list.isenabled = ''1'' or (view_organUnit_list.isenabled = ''0'' and view_organUnit_list.interface_pk_id is not null)) ' END ) ||
NVL2( ORGANUNITPID , ( ' and view_organUnit_list.pid = ' || TO_CHAR( ORGANUNITPID ) ) , '' ) || ' ) organUnit '
|| NVL( JOINTABLEINFO , '' ) || ' WHERE ( 1 = 1 ) '
INTO SQLSYNTAX
FROM DUAL;
END IF;
END IF; SELECT SQLSYNTAX || ( CASE WHEN ' and ' != SUBSTR( LOWER( LTRIM(ADDWHERE) ) , 0 , 4 ) AND ADDWHERE IS NOT NULL THEN ' AND ' END ) || ADDWHERE || NVL2( SORTINFO , ( ' order by ' || SORTINFO ) , '' )
INTO SQLSYNTAX
FROM DUAL; DBMS_OUTPUT.PUT_LINE(SQLSYNTAX); IF KIND IS NULL THEN
OPEN OUTORGANUNITTREE FOR TO_CHAR( SQLSYNTAX );
insert into SEARCT_EMPLOYE values(LOGID,'','','','',SQLSYNTAX,sysdate);--20160303,解决人员查询保存问题,保存部门id查询语句当做人员查询的子查询
DBMS_OUTPUT.PUT_LINE(TO_CHAR( SQLSYNTAX )); ELSE
SQLSYNTAX := ' insert into temp_RightOrganUnitTree(organID ,vid ,pid ,code ,no ,name ,assistant_sign ,kind ,sort ,isenabled ,childNodeNum) ' || SQLSYNTAX; DBMS_OUTPUT.PUT_LINE(SQLSYNTAX);
EXECUTE IMMEDIATE TO_CHAR( SQLSYNTAX );
END IF;
END;
COUNTVALUE NUMBER(9);
OUTORGANUNITTREE CURSOR_ORGANUNITTREE;
BEGIN
--注使用前需提交以下事务临时表语法;
--#TEMPRIGHTORGANUNIT
--CREATE GLOBAL TEMPORARY TABLE TEMP_RIGHTMODIFYORGANUNITFLAG ON COMMIT DELETE ROWS AS
--SELECT ORGAN_ID VID ,ORGAN_PID PID ,ORGAN_CODE CODE ,0 FELLOWNUM ,1 FLAG ,'1' KIND
--FROM ORGAN_LIST WHERE 1 = 2;
--#TEMPORGANUNIT
--CREATE GLOBAL TEMPORARY TABLE TEMP_RIGHTMODIFYORGANUNITTEMP ON COMMIT DELETE ROWS AS
--SELECT ORGAN_ID VID ,ORGAN_CODE CODE ,'1' KIND ,1 FLAG
--FROM ORGAN_LIST WHERE 1 = 2; SELECT COUNT(*) INTO COUNTVALUE FROM LOGIN_LIST WHERE LOGIN_LIST.LOGIN_ID = LOGINID AND LOGIN_LIST.IS_ADMIN = '1' AND ROWNUM <= 1; IF LOGINID IS NULL OR COUNTVALUE > 0 THEN
INSERT INTO TEMP_RIGHTMODIFYORGANUNITFLAG(VID ,PID ,CODE ,FELLOWNUM ,FLAG ,KIND)
SELECT DISTINCT MAIN_ORGAN.ORGAN_ID VID ,MAIN_ORGAN.ORGAN_PID PID ,MAIN_ORGAN.ORGAN_CODE CODE ,
( SELECT COUNT(*) FROM VIEW_ORGANUNIT_LIST WHERE VIEW_ORGANUNIT_LIST.PID = MAIN_ORGAN.ORGAN_ID AND VIEW_ORGANUNIT_LIST.ISENABLED = '1' ) FELLOWNUM ,
( CASE WHEN ( ORGAN_LIST.ORGAN_ID = MAIN_ORGAN.ORGAN_ID ) THEN 1 ELSE 0 END ) FLAG ,'1' KIND
FROM ROLE_ORGAN_LIST , ORGAN_LIST , ORGAN_LIST MAIN_ORGAN
WHERE ( ROLE_ORGAN_LIST.ORGAN_ID = ORGAN_LIST.ORGAN_ID ) AND
( ROLE_ORGAN_LIST.ROLE_ID = ROLEID ) AND
( ROLE_ORGAN_LIST.ORGAN_ID IS NOT NULL ) AND
( ORGAN_LIST.IS_ENABLED = '1' ) AND
( MAIN_ORGAN.IS_ENABLED = '1' ) AND
( ORGAN_LIST.ORGAN_CODE LIKE MAIN_ORGAN.ORGAN_CODE || '%' ) ; INSERT INTO TEMP_RIGHTMODIFYORGANUNITFLAG(VID ,PID ,CODE ,FELLOWNUM ,FLAG ,KIND)
SELECT DISTINCT MAIN_ORGAN.ORGAN_ID VID ,
MAIN_ORGAN.ORGAN_PID PID ,
MAIN_ORGAN.ORGAN_CODE CODE ,
( SELECT COUNT(*) FROM VIEW_ORGANUNIT_LIST WHERE VIEW_ORGANUNIT_LIST.PID = MAIN_ORGAN.ORGAN_ID AND VIEW_ORGANUNIT_LIST.ISENABLED = '1' ) FELLOWNUM ,
0 FLAG ,
'1' KIND
FROM ROLE_ORGAN_LIST , UNIT_LIST , ORGAN_LIST , ORGAN_LIST MAIN_ORGAN
WHERE ( ROLE_ORGAN_LIST.UNIT_ID = UNIT_LIST.UNIT_ID ) AND
( UNIT_LIST.ORGAN_ID = ORGAN_LIST.ORGAN_ID ) AND
( ROLE_ORGAN_LIST.ROLE_ID = ROLEID ) AND
( ROLE_ORGAN_LIST.UNIT_ID IS NOT NULL ) AND
( ORGAN_LIST.IS_ENABLED = '1' ) AND
( UNIT_LIST.IS_ENABLED = '1' ) AND
( MAIN_ORGAN.IS_ENABLED = '1' ) AND
( ORGAN_LIST.ORGAN_CODE LIKE MAIN_ORGAN.ORGAN_CODE || '%' ) ; INSERT INTO TEMP_RIGHTMODIFYORGANUNITFLAG(VID ,PID ,CODE ,FELLOWNUM ,FLAG ,KIND)
SELECT DISTINCT MAIN_UNIT.UNIT_ID VID ,
MAIN_UNIT.UNIT_PID PID ,
MAIN_UNIT.UNIT_CODE CODE ,
( SELECT COUNT(*) FROM VIEW_ORGANUNIT_LIST WHERE VIEW_ORGANUNIT_LIST.PID = MAIN_UNIT.UNIT_ID AND VIEW_ORGANUNIT_LIST.ISENABLED = '1' ) FELLOWNUM ,
( CASE WHEN ( UNIT_LIST.UNIT_ID = MAIN_UNIT.UNIT_ID ) THEN 1 ELSE 0 END ) FLAG ,
'2' KIND
FROM ROLE_ORGAN_LIST , UNIT_LIST , UNIT_LIST MAIN_UNIT
WHERE ( ROLE_ORGAN_LIST.UNIT_ID = UNIT_LIST.UNIT_ID ) AND
( ROLE_ORGAN_LIST.ROLE_ID = ROLEID ) AND
( ROLE_ORGAN_LIST.UNIT_ID IS NOT NULL ) AND
( UNIT_LIST.IS_ENABLED = '1' ) AND
( MAIN_UNIT.IS_ENABLED = '1' ) AND
( UNIT_LIST.UNIT_CODE LIKE MAIN_UNIT.UNIT_CODE || '%' ) ;
ELSE
--OUT TABLE TEMP_RIGHTORGANUNITTREE; OLD TABLE #RIGHTORGANUNIT
PAC_ORGANUNIT.PRO_RIGHTORGANUNITTREE(OUTORGANUNITTREE => OUTORGANUNITTREE ,LOGINID => LOGINID ,KIND => '1'); INSERT INTO TEMP_RIGHTMODIFYORGANUNITFLAG(VID ,PID ,CODE ,FELLOWNUM ,FLAG ,KIND)
SELECT DISTINCT MAIN_ORGAN.VID VID ,MAIN_ORGAN.PID PID ,MAIN_ORGAN.CODE CODE ,
( SELECT COUNT(*) FROM TEMP_RIGHTORGANUNITTREE TEMP WHERE TEMP.PID = MAIN_ORGAN.VID ) FELLOWNUM ,
0 FLAG ,'1' KIND
FROM ROLE_ORGAN_LIST , ORGAN_LIST , ( SELECT * FROM TEMP_RIGHTORGANUNITTREE WHERE KIND = '1' ) MAIN_ORGAN
WHERE ( ROLE_ORGAN_LIST.ORGAN_ID = ORGAN_LIST.ORGAN_ID ) AND
( ROLE_ORGAN_LIST.ROLE_ID = ROLEID ) AND
( ROLE_ORGAN_LIST.ORGAN_ID IS NOT NULL ) AND
( ORGAN_LIST.IS_ENABLED = '1' ) AND
( ORGAN_LIST.ORGAN_CODE LIKE MAIN_ORGAN.CODE || '%' ) ; INSERT INTO TEMP_RIGHTMODIFYORGANUNITFLAG(VID ,PID ,CODE ,FELLOWNUM ,FLAG ,KIND)
SELECT DISTINCT MAIN_ORGAN.VID VID ,MAIN_ORGAN.PID PID ,MAIN_ORGAN.CODE CODE,
( SELECT COUNT(*) FROM TEMP_RIGHTORGANUNITTREE TEMP WHERE TEMP.PID = MAIN_ORGAN.VID ) FELLOWNUM ,
0 FLAG ,'1' KIND
FROM ROLE_ORGAN_LIST , UNIT_LIST , ORGAN_LIST , ( SELECT * FROM TEMP_RIGHTORGANUNITTREE WHERE KIND = '1' ) MAIN_ORGAN
WHERE ( ROLE_ORGAN_LIST.UNIT_ID = UNIT_LIST.UNIT_ID ) AND
( UNIT_LIST.ORGAN_ID = ORGAN_LIST.ORGAN_ID ) AND
( ROLE_ORGAN_LIST.ROLE_ID = ROLEID ) AND
( ROLE_ORGAN_LIST.UNIT_ID IS NOT NULL ) AND
( ORGAN_LIST.IS_ENABLED = '1' ) AND
( UNIT_LIST.IS_ENABLED = '1' ) AND
( ORGAN_LIST.ORGAN_CODE LIKE MAIN_ORGAN.CODE || '%' ) ; INSERT INTO TEMP_RIGHTMODIFYORGANUNITFLAG(VID ,PID ,CODE ,FELLOWNUM ,FLAG ,KIND)
SELECT DISTINCT MAIN_UNIT.VID VID ,MAIN_UNIT.PID PID ,MAIN_UNIT.CODE CODE ,
( SELECT COUNT(*) FROM TEMP_RIGHTORGANUNITTREE TEMP WHERE TEMP.PID = MAIN_UNIT.VID ) FELLOWNUM ,
0 FLAG ,'2' KIND
FROM ROLE_ORGAN_LIST , UNIT_LIST , ( SELECT * FROM TEMP_RIGHTORGANUNITTREE WHERE KIND = '2' ) MAIN_UNIT
WHERE ( ROLE_ORGAN_LIST.UNIT_ID = UNIT_LIST.UNIT_ID ) AND
( ROLE_ORGAN_LIST.ROLE_ID = ROLEID ) AND
( ROLE_ORGAN_LIST.UNIT_ID IS NOT NULL ) AND
( UNIT_LIST.IS_ENABLED = '1' ) AND
( UNIT_LIST.UNIT_CODE LIKE MAIN_UNIT.CODE || '%' ) ;
END IF; --设置状态
INSERT INTO TEMP_RIGHTMODIFYORGANUNITTEMP(VID ,CODE ,KIND ,FLAG)
SELECT DISTINCT TEMPORGANUNIT.VID ,TEMPORGANUNIT.CODE ,TEMPORGANUNIT.KIND ,
( CASE WHEN TEMPORGANUNIT.FLAG = 0 THEN
( CASE WHEN ( TEMPORGANUNIT.FELLOWNUM = ( SELECT COUNT(*) FROM ( SELECT DISTINCT * FROM TEMP_RIGHTMODIFYORGANUNITFLAG ) TEMPINFO WHERE TEMPINFO.PID = TEMPORGANUNIT.VID ) ) THEN 1 ELSE 2 END )
ELSE TEMPORGANUNIT.FLAG END ) FLAG
FROM ( SELECT DISTINCT * FROM TEMP_RIGHTMODIFYORGANUNITFLAG ) TEMPORGANUNIT ; UPDATE TEMP_RIGHTMODIFYORGANUNITTEMP
SET TEMP_RIGHTMODIFYORGANUNITTEMP.FLAG = 2
WHERE EXISTS( SELECT * FROM TEMP_RIGHTMODIFYORGANUNITTEMP TEMPINFO WHERE TEMPINFO.FLAG = 2 AND TEMPINFO.CODE LIKE TEMP_RIGHTMODIFYORGANUNITTEMP.CODE || '%' ); OPEN OUTORGANUNITID FOR SELECT VID , KIND , FLAG FROM TEMP_RIGHTMODIFYORGANUNITTEMP ORDER BY VID;
END;
end "PAC_ORGANUNIT";