-- Create table
create table M_ORG
(
  ORG_ID          VARCHAR2(32) not null,
  SHORT_NAME      VARCHAR2(50),
  NAME            VARCHAR2(100) not null,
  FULL_ID         VARCHAR2(320),
  FULL_NAME       VARCHAR2(500) not null,
  ENAME           VARCHAR2(200),
  ORG_CLASS       CHAR(1) not null,
  BRANCH_LEVEL    INTEGER,
  NODE_URL        VARCHAR2(100) default '#',
  IS_LAST_NODE    CHAR(1),
  PARENT_ORG_ID   VARCHAR2(32),
  BRANCH_INDEX    INTEGER,
  LOGO            VARCHAR2(100),
  DESC_INFO       VARCHAR2(1000),
  COM_ENABLE      CHAR(1) default 1,
  COM_DEL_FLG     CHAR(1) default 0,
  COM_ADD_DATE    DATE,
  COM_UPD_DATE    DATE,
  COM_DEL_DATE    DATE,
  COM_ADD_USER_ID VARCHAR2(32),
  COM_UPD_USER_ID VARCHAR2(32),
  COM_DEL_USER_ID VARCHAR2(32)
)
tablespace CAH_SMS_TBS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table M_ORG
  is '组织机构表。

用于管理组织机构图 organization chart（组织机构树形图）。
存储组织机构树所有节点信息。

组织机构ID规则说明：
  1.一级组织由001开始；二级组织由001001开始；依次类推。
  2.由于组织机构ID长度限制，最多支持到10级机构。';
-- Add comments to the columns 
comment on column M_ORG.ORG_ID
  is '组织机构ID(根据类别不同标示为不同单位部门职务的ID)';
comment on column M_ORG.SHORT_NAME
  is '短称';
comment on column M_ORG.NAME
  is '组织机构中文名称(机构树节点显示用)';
comment on column M_ORG.FULL_ID
  is '全ID(#号链接)';
comment on column M_ORG.FULL_NAME
  is '组织机构中文全称';
comment on column M_ORG.ENAME
  is '组织机构英文名称';
comment on column M_ORG.ORG_CLASS
  is '类别(1:单位;2:部门;3:职务)【数据字典：ORG_CLASS】';
comment on column M_ORG.BRANCH_LEVEL
  is '级别';
comment on column M_ORG.NODE_URL
  is '资源位置';
comment on column M_ORG.IS_LAST_NODE
  is '是否末级(Y/N)【数据字典：YES_NO】';
comment on column M_ORG.PARENT_ORG_ID
  is '上级组织机构ID';
comment on column M_ORG.BRANCH_INDEX
  is '枝顺序';
comment on column M_ORG.LOGO
  is '图标名称';
comment on column M_ORG.DESC_INFO
  is '描述';
comment on column M_ORG.COM_ENABLE
  is '是否可用(1:可用;0:不可用)';
comment on column M_ORG.COM_DEL_FLG
  is '是否删除(1:已删除;0:未删除)';
comment on column M_ORG.COM_ADD_DATE
  is '创建日期(yyyy-MM-dd hh:mm:ss)';
comment on column M_ORG.COM_UPD_DATE
  is '更新日期(yyyy-MM-dd hh:mm:ss)';
comment on column M_ORG.COM_DEL_DATE
  is '删除日期(yyyy-MM-dd hh:mm:ss)';
comment on column M_ORG.COM_ADD_USER_ID
  is '创建者用户ID';
comment on column M_ORG.COM_UPD_USER_ID
  is '更新者用户ID';
comment on column M_ORG.COM_DEL_USER_ID
  is '删除者用户ID';
-- Create/Recreate primary, unique and foreign key constraints 
alter table M_ORG
  add constraint XPK组织机构表 primary key (ORG_ID)
  using index 
  tablespace CAH_SMS_TBS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

CREATE OR REPLACE VIEW V_ORG_TREE_SUB AS
SELECT REPLACE(LPAD('|-', (LEVEL - 1) * 2), '|-', '<LI>') || NAME AS NAME,
               ORG_ID,
               PARENT_ORG_ID,
               BRANCH_LEVEL,
               SHORT_NAME,
               FULL_ID,
               FULL_NAME,
               ENAME,
               ORG_CLASS,
               NODE_URL,
               IS_LAST_NODE,
               BRANCH_INDEX,
               LOGO,
               DESC_INFO
          FROM CAHSMS_M_ORG -- 组织机构：单位>部门>职务
        CONNECT BY PRIOR ORG_ID = PARENT_ORG_ID
         START WITH ORG_ID = '001';

CREATE OR REPLACE VIEW V_ORG_TREE AS
SELECT CASE
         WHEN ORG.ORG_ID = RN_NEXT.PARENT_ORG_ID THEN
          ORG.NAME || '<ul>' -- 该节点是父节点
         WHEN ORG.BRANCH_LEVEL - 1 > RN_NEXT.BRANCH_LEVEL OR
              RN_NEXT.BRANCH_LEVEL IS NULL THEN
          ORG.NAME || '</li> </ul></li>' ||
          DECODE(ORG.BRANCH_LEVEL - 1 -
                 DECODE(RN_NEXT.BRANCH_LEVEL, NULL, 2, RN