企业级存储过程实例

create or replace procedure P_TMP_PROJECT /*(P_SYNC_TYPE varchar2) */
is
  --目标表统计数
  V_PROJECTCOUNT       INTEGER;
  V_PROJECTMEMBERCOUNT INTEGER;
  V_ACCOUNTCOUNT       INTEGER;
  V_TYPE_PROJ_1        varchar2(40);
  V_TYPE_PROJ_2        varchar2(40);
  V_TYPE_PROJ_3        varchar2(40);
  V_TYPE_PROJ_4        varchar2(40);
  V_TYPE_PROJ_5        varchar2(40);
  V_STATE_ALLOW        varchar2(40);
  V_STATE_NOTALLOW     VARCHAR2(40);
  V_ENDDATE            DATE;
  --v_err_msg            varchar2(2000);
begin

  V_TYPE_PROJ_1    := '1'; --工程项目
  V_TYPE_PROJ_2    := '2'; --售前项目
  V_TYPE_PROJ_3    := '3'; --C类行动项目
  V_TYPE_PROJ_4    := '4'; --售后项目
  V_TYPE_PROJ_5    := '5'; --售后非项目
  V_STATE_ALLOW    := '正常'; --允许
  V_STATE_NOTALLOW := '结项'; --禁止

  SELECT to_date((to_char(sysdate, 'yyyy') || '-' || '12' || '-' || '31'),
                 'yyyy-MM-dd')
    INTO V_ENDDATE
    FROM DUAL;
  --//----------------处理工程项目信息 start ---------------------------------------//
  select count(*) into V_PROJECTCOUNT from bx_project;
  dbms_output.put_line('工程项目处理开始' || V_PROJECTCOUNT || 'date:' || sysdate);
  if V_PROJECTCOUNT = 0 then
    --首次插入所有在建和暂停的项目
    insert into bx_project
      (id,
       project_code,
       project_name,
       project_type,
       project_status,
       project_manager,
       PROJECT_DIRECTOR,
       PROJECT_COSTSUM,
       project_begindate,
       project_enddate,
       project_customerid,
       project_departmentname)
      select sys_guid(),
             projectcode,
             projectname,
             case
        when projecttype=1 then V_TYPE_PROJ_1 --工程项目
                when projecttype=4 then V_TYPE_PROJ_4 --售后项目
                when projecttype=5 then V_TYPE_PROJ_5 --售后非项目
             end,
             V_STATE_ALLOW,
             projectmanagerid,
             projectdirectorid,
             PRESALESTOTALCOST,
             startdate,
             case
               when to_char(enddate,'yyyy-MM-dd')='1900-01-01' then
                null
               else
                enddate
             end,
             customerid,
             departmentname
        from bx_project_tmp t
      where  ((t.projecttype=1 and t.projectstate <> '终结') or t.projecttype=4  or t.projecttype=5);
  end if;

  --插入新增项目信息
  insert into bx_project
    (id,
     project_code,
     project_name,
     project_type,
     project_status,
     project_manager,
     PROJECT_DIRECTOR,
     PROJECT_COSTSUM,
     project_begindate,
     project_enddate,
     project_customerid,
     project_departmentname)
    select sys_guid(),
           projectcode,
           projectname,
            case
               when projecttype=1 then V_TYPE_PROJ_1 --工程项目
               when projecttype=4 then V_TYPE_PROJ_4 --售后项目
               when projecttype=5 then V_TYPE_PROJ_5 --售后非项目
             end,
           V_STATE_ALLOW, --项目状态：1正常；0禁止
           projectmanagerid,
           projectdirectorid,
           PRESALESTOTALCOST,
           startdate,
          case
            when to_char(enddate,'yyyy-MM-dd')='1900-01-01' then
              null
             else
              enddate
           end,
           customerid,
           departmentname
     from bx_project_tmp t
     where t.projectcode not in (select project_code from bx_project)
     and ((t.projecttype=1 and t.projectstate <> '终结') or t.projecttype=4  or t.projecttype=5);

  --更新项目状态,项目经理,项目总监,开始时间，结束时间
  update bx_project p
     set p.project_status    = (select case
                                         when t.projectstate = '终结' then
                                          V_STATE_NOTALLOW
                                         else
                                          V_STATE_ALLOW
                                       end
                                  from bx_project_tmp t
                                 where t.projectcode = p.project_code),
         p.project_manager   = (select t.projectmanagerid
                                  from bx_project_tmp t
                                 where t.projectcode = p.project_code),
         p.project_director  = (select t.projectdirectorid
                                  from bx_project_tmp t
                                 where t.projectcode = p.project_code),
         p.project_customerid  = (select t.CUSTOMERID
                                  from bx_project_tmp t
                                 where t.projectcode = p.project_code),
         p.project_customername = null,
         p.project_departmentname  = (select t.departmentname
                                  from bx_project_tmp t
                                 where t.projectcode = p.project_code),
         p.project_departmentid=null,
         p.project_begindate = (select t.startdate
                                  from bx_project_tmp t
                                 where t.projectcode = p.project_code),
         p.project_enddate   = (select case
                                         when to_char(enddate,'yyyy-MM-dd')='1900-01-01' then
                                          null
                                         else
                                          enddate
                                       end
                                  from bx_project_tmp t
                                 where t.projectcode = p.project_code),
        p.project_costsum   = (select t.projcostsum
                                  from bx_project_tmp t
                                 where t.projectcode = p.project_code)
   where p.project_code in (select t.projectcode from bx_project_tmp t);

  dbms_output.put_line('工程项目处理完毕' || sysdate);
  --//售前项目-------------------------------------------------//

  if V_PROJECTCOUNT = 0 then
    --首次插入所有售前项目
    insert into bx_project
      (id,
       project_code,
       project_name,
       project_type,
       project_status,
       project_manager,
       project_begindate,
       project_enddate,
       project_CustomerName,
       PROJECT_DEPARTMENTNAME
       )
      select sys_guid(),
             projectcode,
             projectname,
             case
               when max(projecttype)=2 then V_TYPE_PROJ_2 --售前项目
               when max(projecttype)=3 then V_TYPE_PROJ_3 --C类行动项目
             end,
             max(V_STATE_ALLOW),
             case
               when max(saleid) is null then
                max(presaleid)
               else
                max(saleid)
             end,
             min(updatedate),
             max(case
                   when projectstate = '放弃' then
                    case
                   when giveupdate is not null then
                   (giveupdate + 14)
                 END when projectstate = '败标或失败' then case
                   when faildate is not null then
                     (faildate + 14)
                 END when projectstate = '合同签署' then case
                   when submitdate is not null then
                     (submitdate + 14)
                 end else null end) as enddate,
             max(CustomerName),
             max(businesName)
        from bx_sq_project_tmp t
       group by projectcode, projectname;
  end if;

  --插入新增项目信息
  insert into bx_project
    (id,
     project_code,
     project_name,
     project_type,
     project_status,
     project_manager,
     project_begindate,
     project_enddate,
     project_CustomerName,
     PROJECT_DEPARTMENTNAME
     )
    select sys_guid(),
           projectcode,
           projectname,
           case
               when max(projecttype)=2 then V_TYPE_PROJ_2 --售前项目
               when max(projecttype)=3 then V_TYPE_PROJ_3 --C类行动项目
           end,
           max(V_STATE_ALLOW),
           case
             when max(saleid) is null then
              max(presaleid)
             else
              max(saleid)
           end,
           min(updatedate),
           max(case
                 when projectstate = '放弃'
                 then
                    case
                    when giveupdate is not null then (giveupdate + 14)
                    end
                 when projectstate = '败标或失败'
                 then
                   case
                   when faildate is not null then (faildate + 14)
                   end
               when projectstate = '合同签署'
               then
                 case
                 when submitdate is not null then (submitdate + 14)
                 end
               else null end) as enddate,
           max(CustomerName),
           max(businesName)
      from bx_sq_project_tmp t
     group by projectcode, projectname
    having t.projectcode not in (select project_code from bx_project);

  --更新售前项目状态,项目经理,开始时间，结束时间
  update bx_project p
     set p.project_status    = (select max(case
                                             when (t.projectstate = '放弃' or
                                                  t.projectstate = '败标或失败' or
                                                  t.projectstate = '合同签署') then
                                              V_STATE_NOTALLOW
                                             else
                                              V_STATE_ALLOW
                                           end)
                                  from bx_sq_project_tmp t
                                 group by t.projectcode
                                having t.projectcode = p.project_code),
         p.project_manager   = (select case
                                         when max(t.saleid) is null then
                                          max(t.presaleid)
                                         else
                                          max(t.saleid)
                                       end
                                  from bx_sq_project_tmp t
                                 group by t.projectcode, t.projectname
                                having t.projectcode = p.project_code),
         p.project_customername   = (select max(t.CUSTOMERNAME)
                                  from bx_sq_project_tmp t
                                 group by t.projectcode, t.projectname
                                having t.projectcode = p.project_code),
         p.project_customerid = null,
          p.project_departmentname  = (select max( t.businesname)
                                  from bx_sq_project_tmp t
                                 group by t.projectcode, t.projectname
                                having t.projectcode = p.project_code),
         p.project_departmentid=null,
         p.project_begindate = (select min(t.updatedate)
                                  from bx_sq_project_tmp t
                                 group by t.projectcode, t.projectname
                                having t.projectcode = p.project_code),
         p.project_enddate   = (select max(case
                                             when projectstate = '放弃' then
                                              case
                                             when giveupdate is not null then  (giveupdate + 14)
                                             END
                                           when projectstate = '败标或失败' then case
                                             when faildate is not null then
                                              (faildate + 14)
                                            END
                                            when projectstate = '合同签署' then case
                                             when submitdate is not null then
                                              (submitdate + 14)
                                           end
                                           else null end) as enddate
                                  from bx_sq_project_tmp t
                                 group by t.projectcode, t.projectname
                                having t.projectcode = p.project_code)
   where p.project_code in (select t.projectcode from bx_sq_project_tmp t);
  --更新项目户客户信息
  update bx_project a
     set a.project_customername = (select c.customer_name
                                     from bx_customer c
                                    where c.id = a.project_customerid)
   where a.project_customerid is not null;

  update bx_project a
     set a.project_customerid = (select max(c.id)
                                   from bx_customer c
                                  where c.customer_name =
                                        a.project_customername)
   where a.project_customername is not null;

   update bx_project a
     set a.project_departmentid = (select max(o.id)
                                   from org_group o
                                  where o.grouptypeid='dept' and o.NAME=a.project_departmentname)
   where a.project_departmentname is not null;




  -- 更新项目经理，项目总监的id,为报销系统id
  update bx_project p
     set p.project_manager  = (select distinct u.id
                                 from org_user u
                                where upper(u.tel) = upper(p.project_manager) and u.attr2='0'),
         p.project_director = (select distinct u.id
                                 from org_user u
                                where upper(u.tel) = upper(p.project_director) and u.attr2='0');
   -- 更新项目经理为空的为部门经理
   update bx_project p
      set p.project_manager  =(select distinct u.id
          from org_user u
         where u.tel = (select owner
                          from org_group
                         where grouptypeid = 'dept'
                           and id = p.project_departmentid)
           and u.attr2 = '0')
 where p.project_manager is null;
  --更新项目总监name,为报销系统name
  update bx_project p
     set p.project_manager_name  = (select distinct u.name
                                      from org_user u
                                     where u.id = p.project_manager and u.attr2='0'),
         p.project_director_name = (select distinct u.name
                                      from org_user u
                                     where u.id = p.project_director and u.attr2='0');
  dbms_output.put_line('售前项目处理完毕' || sysdate);
  --插入可报销账户信息------------------------------------
  SELECT COUNT(*) INTO V_ACCOUNTCOUNT FROM BX_ACCOUNT;
  IF V_ACCOUNTCOUNT = 0 --判断可报销账户为空时根据BX_PROJECT全量插入
   THEN
    insert into bx_account
      (ACC_ID,
       ACC_CODE,
       acc_project_code,
       ACC_NAME,
       ACC_OWNER,
       acc_owner_name,
       ACC_AMOUNT,
       ACC_BALANCE,
       ACC_VALID_STARTTIME,
       ACC_VALID_ENDTIME,
       ACC_STATUS,
       acc_type,
       acc_type_name,
       ACC_SHARE_DEPT,
       ACC_SHARE_USER,
       acc_state,
       acc_customerid,
       acc_customername,
       acc_departid,
       acc_departname)
      select sys_guid(),
             project_code,
             project_code,
             CASE
             WHEN p.project_type = '1' then
              '[工程]'||project_name
             when p.project_type = '2' then
              '[售前]'||project_name
             when p.project_type = '3' then
              '[C类行动]'||project_name
             when p.project_type = '4' then
              '[售后]'||project_name
             ELSE
              project_name
             end,
             case when project_manager is null
             then project_director else project_manager end,
             case when project_manager is null
             then project_director_name else project_manager_name end,
             case when PROJECT_COSTSUM=0.000 then null
             else PROJECT_COSTSUM end,
             case when PROJECT_COSTSUM=0.000 then null
             else PROJECT_COSTSUM end,
             project_begindate,
             project_enddate,
             case
               when project_status = '正常' then
                1
               else
                0
             end,
             CASE
               WHEN p.project_type = '1' then
                '1374651194796ada2hnvzv2wo8veus4rm6zgmvw6739i0devx'
               WHEN p.project_type = '2' then
                '1374651255197yl0of6isz8g14bds7fwmqqow1524p9b3gw3f'
               WHEN p.project_type = '3' then
                '1381568247245as8ijsz4neo21v7xm7af2ssc9iqhtf3i2j2s'
               WHEN p.project_type = '4' then
                '1381823347964o9whw2n6r88bj1np2fq4b7yspbu50kqb8hds'
               WHEN p.project_type = '5' then
                '13819053219062i7sr087m30r9rrhwitykfm4gaq4upmpwtey'
             END,
             CASE
               WHEN p.project_type = '1' then
                '工程项目直接费用'
               WHEN p.project_type = '2' then
                '售前项目直接费用'
               WHEN p.project_type = '3' then
                'C类行动费用'
               WHEN p.project_type = '4' then
                '售后项目直接费用'
               WHEN p.project_type = '5' then
                '售后非项目费用'
             END,
             '0',
             '0',
             '0',
             project_customerid,
             project_customername,
             project_departmentid,
             project_departmentname
        from bx_project p;
  END IF;
 

  --插入新增的可报销账户
  insert into bx_account
    (ACC_ID,
     ACC_CODE,
     acc_project_code,
     ACC_NAME,
     ACC_OWNER,
     acc_owner_name,
     ACC_AMOUNT,
     ACC_BALANCE,
     ACC_VALID_STARTTIME,
     ACC_VALID_ENDTIME,
     ACC_STATUS,
     acc_type,
     acc_type_name,
     ACC_SHARE_DEPT,
     ACC_SHARE_USER,
     acc_state,
     acc_customerid,
     acc_customername,
     acc_departid,
     acc_departname)
    select sys_guid(),
           project_code,
           project_code,
           CASE
             WHEN p.project_type = '1' then
              '[工程]'||project_name
             WHEN p.project_type = '2' then
              '[售前]'||project_name
             WHEN p.project_type = '3' then
              '[C类行动]'||project_name
             WHEN  p.project_type = '4' then
              '[售后]'||project_name
             ELSE
              project_name
           end,
           case when project_manager is null
           then project_director else project_manager end,
           case when project_manager is null
           then project_director_name else project_manager_name end,
           case when PROJECT_COSTSUM=0.000 then null
           else PROJECT_COSTSUM end,
           case when PROJECT_COSTSUM=0.000 then null
           else PROJECT_COSTSUM end,
           project_begindate,
           project_enddate,
           case
             when project_status = '正常' then
              1
             else
              0
           end,
           CASE
               WHEN p.project_type = '1' then
                '1374651194796ada2hnvzv2wo8veus4rm6zgmvw6739i0devx'
               WHEN p.project_type = '2' then
                '1374651255197yl0of6isz8g14bds7fwmqqow1524p9b3gw3f'
               WHEN p.project_type = '3' then
                '1381568247245as8ijsz4neo21v7xm7af2ssc9iqhtf3i2j2s'
               WHEN p.project_type = '4' then
                '1381823347964o9whw2n6r88bj1np2fq4b7yspbu50kqb8hds'
               WHEN p.project_type = '5' then
                '13819053219062i7sr087m30r9rrhwitykfm4gaq4upmpwtey'
             END,
             CASE
               WHEN p.project_type = '1' then
                '工程项目直接费用'
               WHEN p.project_type = '2' then
                '售前项目直接费用'
               WHEN p.project_type = '3' then
                'C类行动费用'
               WHEN p.project_type = '4' then
                '售后项目直接费用'
               WHEN p.project_type = '5' then
                '售后非项目费用'
             END,
           '0',
           '0',
           '0',
            project_customerid,
            project_customername,
            project_departmentid,
            project_departmentname
      from bx_project p
     where p.project_code not in (select c.acc_project_code from bx_account c where c.acc_project_code is not null);

  --更新可报销账户状态，owner
  update bx_account acc
     set acc.ACC_STATUS          = (select case
                                             when p.project_status = '正常' then
                                              1
                                             else
                                              0
                                           end
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code),
         acc.ACC_OWNER           = 'user:' ||
                                   (select case
                                             when p.project_manager is null then
                                              p.project_director
                                             else
                                              p.project_manager
                                           end
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code),
         acc.ACC_OWNER_NAME      = '用户:' ||
                                   (select case
                                             when p.project_manager is null then
                                              p.project_director_name
                                             else
                                              p.project_manager_name
                                           end
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code),
         acc.ACC_CUSTOMERID      = (select p.project_customerid
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code),
         acc.ACC_CUSTOMERNAME    = (select p.project_customername
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code),
         acc.acc_valid_starttime = (select p.project_begindate
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code),
         acc.acc_valid_endtime   = (select p.project_enddate
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code),
         acc.acc_amount          = (select p.project_costsum
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code
                                       and p.project_type = '1') --只针对工程更新额度
   where acc.acc_project_code in
         (select p.project_code
            from bx_project p
           where p.project_code is not null);
   update bx_account acc
        set acc.acc_departid   = (select p.project_departmentid
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code and p.project_departmentid is not null),
         acc.acc_departname   = (select p.project_departmentname
                                      from bx_project p
                                     where p.project_code =
                                           acc.acc_project_code and p.project_departmentid is not null)
    where acc.acc_project_code in (select p.project_code from bx_project p where p.project_code is not null);
  --更新可报销账户余额
   update bx_account acc
        set acc.acc_balance = acc.acc_amount-acc.acc_usedamount
    where acc.acc_amount is not null and acc.acc_usedamount is not null and acc.acc_project_code in (select p.project_code from bx_project p where p.project_code is not null);
  dbms_output.put_line('报销账户处理完毕' || sysdate);

  ------插入可报销账户与费用关系表---------------------------
  --工程项目与费用关系
  insert into bx_acc_sub_relation r
    (id, acc_id, acc_name, subject_id, subject_name)
    select sys_guid(), acc_id, acc_name, id, sub_name
      from (select acc_id, acc_name
              from bx_account
             where (acc_type =
                   '1374651194796ada2hnvzv2wo8veus4rm6zgmvw6739i0devx')
               and acc_id not in
                   (select distinct acc_id from bx_acc_sub_relation)) a,
           (select id, sub_name
              from bx_subject
             where sub_name in
                   ('电话费', '办公费', '邮运费', '飞机票', '火车票', '订票服务费', '汽车票', '住宿费', '房租', '中介费','物业费',
                    '上网费', '电费', '水费', '燃气费','有线电视费', '暖气费', '礼品','交通票','餐费')) b;

  --售后项目与费用关系
  insert into bx_acc_sub_relation r
    (id, acc_id, acc_name, subject_id, subject_name)
    select sys_guid(), acc_id, acc_name, id, sub_name
      from (select acc_id, acc_name
              from bx_account
             where (acc_type =
                   '1381823347964o9whw2n6r88bj1np2fq4b7yspbu50kqb8hds')
               and acc_id not in
                   (select distinct acc_id from bx_acc_sub_relation)) a,
           (select id, sub_name
              from bx_subject
             where sub_name in
                   ('电话费', '办公费', '邮运费', '飞机票', '火车票', '交通票',
                    '订票服务费', '汽车票', '住宿费', '礼品','餐费')) b;


  --售后非项目与费用关系
  insert into bx_acc_sub_relation r
    (id, acc_id, acc_name, subject_id, subject_name)
    select sys_guid(), acc_id, acc_name, id, sub_name
      from (select acc_id, acc_name
              from bx_account
             where (acc_type =
                   '13819053219062i7sr087m30r9rrhwitykfm4gaq4upmpwtey')
               and acc_id not in
                   (select distinct acc_id from bx_acc_sub_relation)) a,
           (select id, sub_name
              from bx_subject
             where sub_name in('电话费', '邮运费','餐费')) b;

 --售前项目与费用关系
  insert into bx_acc_sub_relation r
    (id, acc_id, acc_name, subject_id, subject_name)
    select sys_guid(), acc_id, acc_name, id, sub_name
      from (select acc_id, acc_name
              from bx_account
             where (acc_type =
                   '1374651255197yl0of6isz8g14bds7fwmqqow1524p9b3gw3f')
               and acc_id not in
                   (select distinct acc_id from bx_acc_sub_relation)) a,
           (select id, sub_name
              from bx_subject
             where sub_name in
                   ('办公费', '邮运费', '印刷装订费', '飞机票', '火车票', '交通票','餐费',
                    '订票服务费', '汽车票', '住宿费', '租车费', '礼品', '会议费', '汽油费', '停车费', '招标服务费','餐费')) b;

--售前C类行到与费用类型关系
 insert into bx_acc_sub_relation r
    (id, acc_id, acc_name, subject_id, subject_name)
    select sys_guid(), acc_id, acc_name, id, sub_name
      from (select acc_id, acc_name
              from bx_account
             where (acc_type =
                   '1381568247245as8ijsz4neo21v7xm7af2ssc9iqhtf3i2j2s')
               and acc_id not in
                   (select distinct acc_id from bx_acc_sub_relation)) a,
           (select id, sub_name
              from bx_subject
             where sub_name in
                   ('办公费', '邮运费', '印刷装订费', '飞机票', '火车票', '交通票','餐费',
                     '订票服务费', '汽车票', '住宿费', '租车费', '礼品', '会议费', '汽油费', '停车费', '招标服务费')) b;


  --插入工程项目成员信息-------------------------------------
  SELECT COUNT(*) INTO V_PROJECTMEMBERCOUNT FROM bx_project_member;
  IF V_PROJECTMEMBERCOUNT = 0 --判断是否全量插入
   THEN
    insert into bx_project_member
      (id,
       project_code,
       project_name,
       project_member,
       project_member_startdate,
       project_member_enddate,
       sa_id)
      select sys_guid(),
             projectcode,
             projectname,
             userid,
             startdate,
             enddate,
             resourceid
        from bx_project_member_tmp mt
       where mt.projectcode in (select p.project_code from bx_project p where p.project_code is not null)
         and mt.userid is not null and mt.projecttype='1';
  END IF;
  insert into bx_project_member
    (id,
     project_code,
     project_name,
     project_member,
     project_member_startdate,
     project_member_enddate,
     sa_id)
    select sys_guid(),
           projectcode,
           projectname,
           userid,
           startdate,
           enddate,
           resourceid
      from bx_project_member_tmp mt
     where mt.projectcode in (select p.project_code from bx_project p where  p.project_code is not null)
       and mt.userid is not null and mt.projecttype='1'
       and mt.resourceid not in (select pm.sa_id from bx_project_member pm);
  --更新成员，开始时间，结束时间
  update bx_project_member m
     set m.project_member           = (select mt.userid
                                         from bx_project_member_tmp mt
                                        where mt.resourceid = m.sa_id),
         m.project_member_startdate = (select mt.startdate
                                         from bx_project_member_tmp mt
                                        where mt.resourceid = m.sa_id),
         m.project_member_enddate   = (select mt.enddate
                                         from bx_project_member_tmp mt
                                        where mt.resourceid = m.sa_id)
   where m.sa_id in (select mt.resourceid from bx_project_member_tmp mt where mt.resourceid is not null);
  --更新成员信息为报销系统id
  update bx_project_member m
     set m.project_member = (select distinct u.id
                               from org_user u
                              where u.tel = m.project_member and u.attr2='0');

  update bx_project_member m
     set m.project_member_name = (select distinct u.name
                                    from org_user u
                                   where u.id = m.project_member and u.attr2='0');
  dbms_output.put_line('工程项目成员处理完毕' || sysdate);
  --插入可报销账户权限表
  insert into bx_acc_limit
    (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
    select sys_guid(),
           acc.acc_id,
           acc.acc_name,
           m.project_member,
           m.project_member_name
      from bx_account acc, bx_project_member m
     where acc.acc_project_code = m.project_code
       and m.project_member is not null
       and (select count(*)
              from bx_acc_limit l
             where l.acc_id = acc.acc_id
               and l.limit_user_id = m.project_member) <= 0;
  dbms_output.put_line('工程项目权限处理完毕' || sysdate);


  --//售前项目权限表----------------------------//

  --插入可报销账户权限表
  insert into bx_acc_limit
    (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
    select sys_guid(),
           acc.acc_id,
           acc.acc_name,
           (select distinct u.id from org_user u where u.tel = t.saleid and u.attr2='0'),
           (select distinct u.name
              from org_user u
             where substr(u.id, instr(u.id, '/') + 1) = t.saleid and u.attr2='0')
      from bx_account acc, bx_sq_project_tmp t
     where acc.acc_project_code = t.projectcode
       and t.saleid is not null
       and (select count(*)
              from bx_acc_limit l
             where substr(l.limit_user_id, instr(l.limit_user_id, '/') + 1) =
                   t.saleid
               and l.acc_id = acc.acc_id) <= 0;
  insert into bx_acc_limit
    (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
    select sys_guid(),
           acc.acc_id,
           acc.acc_name,
           (select distinct u.id from org_user u where u.tel = t.saleid1 and u.attr2='0'),
           (select distinct u.name
              from org_user u
             where substr(u.id, instr(u.id, '/') + 1) = t.saleid1 and u.attr2='0')
      from bx_account acc, bx_sq_project_tmp t
     where acc.acc_project_code = t.projectcode
       and t.saleid1 is not null
       and (select count(*)
              from bx_acc_limit l
             where substr(l.limit_user_id, instr(l.limit_user_id, '/') + 1) =
                   t.saleid1
               and l.acc_id = acc.acc_id) <= 0;
  insert into bx_acc_limit
    (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
    select sys_guid(),
           acc.acc_id,
           acc.acc_name,
           (select distinct u.id from org_user u where u.tel = t.presaleid and u.attr2='0'),
           (select distinct u.name
              from org_user u
             where substr(u.id, instr(u.id, '/') + 1) = t.presaleid and u.attr2='0')
      from bx_account acc, bx_sq_project_tmp t
     where acc.acc_project_code = t.projectcode
       and t.presaleid is not null
       and (select count(*)
              from bx_acc_limit l
             where substr(l.limit_user_id, instr(l.limit_user_id, '/') + 1) =
                   t.presaleid
               and l.acc_id = acc.acc_id) <= 0;
  insert into bx_acc_limit
    (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
    select sys_guid(),
           acc.acc_id,
           acc.acc_name,
           (select distinct u.id from org_user u where u.tel = t.presaleid1 and u.attr2='0'),
           (select distinct u.name
              from org_user u
             where substr(u.id, instr(u.id, '/') + 1) = t.presaleid1 and u.attr2='0')
      from bx_account acc, bx_sq_project_tmp t
     where acc.acc_project_code = t.projectcode
       and t.presaleid1 is not null
       and (select count(*)
              from bx_acc_limit l
             where substr(l.limit_user_id, instr(l.limit_user_id, '/') + 1) =
                   t.presaleid1
               and l.acc_id = acc.acc_id) <= 0;
  dbms_output.put_line('售前项目权限1处理完毕' || sysdate);
  --根据售前行动报备插入可报销账户权限表

  insert into bx_acc_limit l
    (limit_id, acc_id, limit_user_name)
    select sys_guid(), a.acc_id, pro.d
      from (select p.projectcode, p.d
              from (SELECT projectcode,
                           substr(t.ca,
                                  instr(t.ca, ',', 1, d.lv) + 1,
                                  instr(t.ca, ',', 1, d.lv + 1) -
                                  (instr(t.ca, ',', 1, d.lv) + 1)) AS d
                      FROM (SELECT projectcode,
                                   ',' || participants || ',' AS ca,
                                   length(participants || ',') -
                                   nvl(length(REPLACE(participants, ',')), 0) AS cnt
                              FROM bx_sq_report r
                             where r.participants is not null) t,
                           (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) d
                     WHERE d.lv <= t.cnt
                     ORDER BY projectcode) p
             where p.d is not null
             group by p.projectcode, p.d
             order by p.projectcode) pro,
           bx_account a
     where a.acc_project_code = pro.projectcode
       and (select count(*)
              from bx_acc_limit t
             where t.acc_id = a.acc_id
               and t.limit_user_name = pro.d) <= 0;

  update bx_acc_limit l
     set l.acc_name = (select a.acc_name
                         from bx_account a
                        where a.acc_id = l.acc_id)
   where l.acc_name is null;

  update bx_acc_limit l
     set l.limit_user_id = (select distinct u.id
                              from org_user u
                             where u.name = l.limit_user_name and u.attr2='0')
   where l.limit_user_id is null;

  dbms_output.put_line('售前项目权限2处理完毕' || sysdate);
 --// 售后项目权限----


 insert into bx_acc_limit l
    (limit_id, acc_id, limit_user_id)
    select sys_guid(), a.acc_id, pro.d
      from (select p.projectcode, p.d
              from (SELECT projectcode,
                           substr(t.ca,
                                  instr(t.ca, ',', 1, d.lv) + 1,
                                  instr(t.ca, ',', 1, d.lv + 1) -
                                  (instr(t.ca, ',', 1, d.lv) + 1)) AS d
                      FROM (SELECT projectcode,
                                   ',' || userid || ',' AS ca,
                                   length(userid || ',') -
                                   nvl(length(REPLACE(userid, ',')), 0) AS cnt
                              FROM bx_project_member_tmp r
                             where r.userid is not null and (r.projecttype='4')) t,
                           (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) d
                     WHERE d.lv <= t.cnt
                     ORDER BY projectcode) p
             where p.d is not null
             group by p.projectcode, p.d
             order by p.projectcode) pro,
           bx_account a
     where a.acc_project_code = pro.projectcode
       and (select count(*)
              from bx_acc_limit t
             where t.acc_id = a.acc_id
               and t.limit_user_name = pro.d) <= 0;

 --更新账户名称
  update bx_acc_limit l
     set l.acc_name = (select a.acc_name
                         from bx_account a
                        where a.acc_id = l.acc_id)
   where l.acc_name is null;
--更新人员id
  update bx_acc_limit l
     set l.limit_user_id = (select distinct u.id
                              from org_user u
                             where u.tel = substr(l.limit_user_id,instr(l.limit_user_id,'/')+1) and u.attr2='0')
    where l.limit_user_id is not null;
 --更新人员名称
  update bx_acc_limit l
     set l.limit_user_name = (select distinct u.name
                              from org_user u
                             where u.id = l.limit_user_id and u.attr2='0')
   where l.limit_user_id is not null;
 -- 插入售后非项目OWNER 报销权限
 insert into bx_acc_limit l
  (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
  select sys_guid(),
         a.acc_id,
         a.acc_name,
         replace(a.acc_owner, 'user:', ''),
         replace(a.acc_owner_name, '用户:', '')
    from bx_account a
   where a.acc_type_name = '售后非项目费用'
     and a.acc_owner not in (select 'user:' || limit_user_id
                               from bx_acc_limit
                              where acc_id = a.acc_id);
  -- 插入售后项目OWNER 报销权限
  insert into bx_acc_limit l
  (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
  select sys_guid(),
         a.acc_id,
         a.acc_name,
         replace(a.acc_owner, 'user:', ''),
         replace(a.acc_owner_name, '用户:', '')
    from bx_account a
   where a.acc_type_name = '售后项目直接费用'
     and a.acc_owner not in (select 'user:' || limit_user_id
                               from bx_acc_limit
                              where acc_id = a.acc_id);

  COMMIT;
  --清理无用的表数据
  --delete from bx_project p where p.project_type='1' and not exists( select 1 from bx_project_tmp t where  p.project_code=t.projectcode) ;
  --delete from bx_project_member m where not exists(select 1 from bx_project_tmp t where t.projectcode=m.project_code);
  --delete from bx_project p where p.project_type='2' and not exists(select 1 from bx_sq_project_tmp t where p.project_code=t.projectcode);
  --delete from bx_account a where a.acc_type_name='工程项目直接费用' and not exists (select 1 from bx_project_tmp t where t.projectcode=a.acc_project_code);
  --delete from bx_account a where a.acc_type_name='售前项目直接费用' and not exists (select 1 from bx_sq_project_tmp t where t.projectcode=a.acc_project_code);
  --清除无用的账户权限
  delete from bx_acc_limit l
   where not exists (select 1 from bx_account a where a.acc_id = l.acc_id);
  --清除离职的人员账户权限表
  delete from bx_acc_limit l where not exists (select 1 from org_user u where u.attr2='0' and u.tel = substr(l.limit_user_id,instr(l.limit_user_id,'/')+1));
   --清除没有人员的账户权限
  delete from bx_acc_limit where limit_user_id is null ;
  --清除无用的账户关系
  delete from bx_acc_sub_relation r
   where not exists (select 1 from bx_account a where a.acc_id = r.acc_id);
  --清除无用的票据登记信息
  delete from bx_invoice_info b where not exists (select 1 from bx_approval a where  a.batch_code=b.batch);
   --更新工程项目费用承担人为报销人
  update bx_account a set a.acc_share_user='1' where a.acc_type_name='工程项目直接费用' and  a.acc_share_user<>'1';
  --更新售前项目的费用承担方式为报销人和报销人所在部门承担
  update bx_account a set a.acc_share_dept=1 ,a.acc_share_user=1 where a.acc_type_name='售前项目直接费用' or a.acc_type_name='C类行动费用';
  --更新售后项目和售后非项目的费用承担方式为报销人承担
  update bx_account a set a.acc_share_user=1  where a.acc_type_name='售后项目直接费用' or a.acc_type_name='售后非项目费用';

  commit;
  /*
  Exception
  WHEN OTHERS Then
  ROLLBACK;
  v_err_msg:=sqlcode||';'||sqlerrm;
  INSERT INTO BX_SYNC_RESULT R(
  id,
  sync_date,
  sync_type,
  sync_state,
  exception,
  sync_data
  )
  SELECT sys_guid(),sysdate,P_SYNC_TYPE,'0',v_err_msg,'项目' from dual;
  commit;
  */
End P_TMP_PROJECT;