-
-
- 表设计,首先确定的是树结构表,假设是某一个公司确定是一个项目主项信息,包含字段有,责任部门,责任员工,编辑状态 ,计划类型,编制状态,完成情况
-
CREATE TABLE BAI_PROJECT_PLAN(
BAI_PROJECT_PLAN_ID VARCHAR2(32) PRIMARY KEY ,
BAI_PROJECT_PLAN_P_ID VARCHAR2(32) ,
BAI_ROOT_PROJECT_PLAN_ID VARCHAR2(32) ,
BAI_PROJECT_PLAN_NAME VARCHAR2(225) ,
HOST_DEPT_NAME VARCHAR2(225) ,
HOST_DEPT_CODE VARCHAR2(32) ,
PROJECT_PLAN_STATUS VARCHAR(2),
BAI_PLAN_TYPE VARCHAR2(2)
);
insert into BAI_PROJECT_PLAN
(BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE)
values
('A36DABCA0616496BBD016F787613E22D',
'root',
'A36DABCA0616496BBD016F787613E22D',
'投资计划测试1207',
'信息技术部',
'4147',
'7',
'1');
insert into BAI_PROJECT_PLAN
(BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE)
values
('20171207193248967000100000500000',
'20171207193248967000100000300000',
'A36DABCA0616496BBD016F787613E22D',
'工艺主项1',
'信息技术部',
'4147',
'7',
'1');
insert into BAI_PROJECT_PLAN
(BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE)
values
('20171207193248967000100000600000',
'20171207193248967000100000200000',
'A36DABCA0616496BBD016F787613E22D',
'辅楼',
'企业发展部',
'4021',
'7',
'1');
insert into BAI_PROJECT_PLAN
(BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE)
values
('366C12DFC2A241D7AECBA60553A6624E',
'A36DABCA0616496BBD016F787613E22D',
'A36DABCA0616496BBD016F787613E22D',
'投资计划基本信息',
'信息技术部',
'4147',
'7',
'1');
insert into BAI_PROJECT_PLAN
(BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE)
values
('20171207193248967000100000300000',
'A36DABCA0616496BBD016F787613E22D',
'A36DABCA0616496BBD016F787613E22D',
'RX-2017-0736立项测试1201-007',
'信息技术部',
'4147',
'7',
'1');
insert into BAI_PROJECT_PLAN
(BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE)
values
('20171207193248967000100000400000',
'20171207193248967000100000500000',
'A36DABCA0616496BBD016F787613E22D',
'中央空调',
'经营管理部',
'4025',
'7',
'1');
insert into BAI_PROJECT_PLAN
(BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE)
values
('20171207193248967000100000100000',
'20171207193248967000100000200000',
'A36DABCA0616496BBD016F787613E22D',
'主楼',
'信息技术部',
'4147',
'7',
'1');
insert into BAI_PROJECT_PLAN
(BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE)
values
('20171207193248967000100000200000',
'20171207193248967000100000300000',
'A36DABCA0616496BBD016F787613E22D',
'建筑主项',
'经营管理部',
'4025',
'7',
'1');
-
-
- 查询条件,如果公司领导是公司级领导就不过滤责任部门
-
SELECT BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE
FROM BAI_PROJECT_PLAN
WHERE BAI_PROJECT_PLAN_ID IN
(SELECT BAI_PROJECT_PLAN_ID
FROM (SELECT T.BAI_PROJECT_PLAN_ID, T.BAI_PROJECT_PLAN_P_ID
FROM BAI_PROJECT_PLAN T
WHERE T.BAI_ROOT_PROJECT_PLAN_ID =
'A36DABCA0616496BBD016F787613E22D') A
START WITH A.BAI_PROJECT_PLAN_ID IN (SELECT P.BAI_PROJECT_PLAN_ID
FROM BAI_PROJECT_PLAN P
WHERE P.BAI_ROOT_PROJECT_PLAN_ID =
'A36DABCA0616496BBD016F787613E22D'
--AND P.HOST_DEPT_CODE = '4147'
)
CONNECT BY PRIOR A.BAI_PROJECT_PLAN_P_ID = A.BAI_PROJECT_PLAN_ID)
-
-
- 查询条件,如果公司领导是非公司级领导就过滤责任部门
-
SELECT BAI_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_P_ID,
BAI_ROOT_PROJECT_PLAN_ID,
BAI_PROJECT_PLAN_NAME,
HOST_DEPT_NAME,
HOST_DEPT_CODE,
PROJECT_PLAN_STATUS,
BAI_PLAN_TYPE
FROM BAI_PROJECT_PLAN
WHERE BAI_PROJECT_PLAN_ID IN
(SELECT BAI_PROJECT_PLAN_ID
FROM (SELECT T.BAI_PROJECT_PLAN_ID, T.BAI_PROJECT_PLAN_P_ID
FROM BAI_PROJECT_PLAN T
WHERE T.BAI_ROOT_PROJECT_PLAN_ID =
'A36DABCA0616496BBD016F787613E22D') A
START WITH A.BAI_PROJECT_PLAN_ID IN
(SELECT P.BAI_PROJECT_PLAN_ID
FROM BAI_PROJECT_PLAN P
WHERE P.BAI_ROOT_PROJECT_PLAN_ID =
'A36DABCA0616496BBD016F787613E22D'
AND P.HOST_DEPT_CODE = '4147')
CONNECT BY PRIOR A.BAI_PROJECT_PLAN_P_ID = A.BAI_PROJECT_PLAN_ID)