1 create or replace procedure P_TMP_PROJECT /*(P_SYNC_TYPE varchar2) */
2 is
3 --目标表统计数
4 V_PROJECTCOUNT INTEGER;
5 V_PROJECTMEMBERCOUNT INTEGER;
6 V_ACCOUNTCOUNT INTEGER;
7 V_TYPE_PROJ_1 varchar2(40);
8 V_TYPE_PROJ_2 varchar2(40);
9 V_TYPE_PROJ_3 varchar2(40);
10 V_TYPE_PROJ_4 varchar2(40);
11 V_TYPE_PROJ_5 varchar2(40);
12 V_STATE_ALLOW varchar2(40);
13 V_STATE_NOTALLOW VARCHAR2(40);
14 V_ENDDATE DATE;
15 --v_err_msg varchar2(2000);
16 begin
17
18 V_TYPE_PROJ_1 := '1'; --工程项目
19 V_TYPE_PROJ_2 := '2'; --售前项目
20 V_TYPE_PROJ_3 := '3'; --C类行动项目
21 V_TYPE_PROJ_4 := '4'; --售后项目
22 V_TYPE_PROJ_5 := '5'; --售后非项目
23 V_STATE_ALLOW := '正常'; --允许
24 V_STATE_NOTALLOW := '结项'; --禁止
25
26 SELECT to_date((to_char(sysdate, 'yyyy') || '-' || '12' || '-' || '31'),
27 'yyyy-MM-dd')
28 INTO V_ENDDATE
29 FROM DUAL;
30 --//----------------处理工程项目信息 start ---------------------------------------//
31 select count(*) into V_PROJECTCOUNT from bx_project;
32 dbms_output.put_line('工程项目处理开始' || V_PROJECTCOUNT || 'date:' || sysdate);
33 if V_PROJECTCOUNT = 0 then
34 --首次插入所有在建和暂停的项目
35 insert into bx_project
36 (id,
37 project_code,
38 project_name,
39 project_type,
40 project_status,
41 project_manager,
42 PROJECT_DIRECTOR,
43 PROJECT_COSTSUM,
44 project_begindate,
45 project_enddate,
46 project_customerid,
47 project_departmentname)
48 select sys_guid(),
49 projectcode,
50 projectname,
51 case
52 when projecttype=1 then V_TYPE_PROJ_1 --工程项目
53 when projecttype=4 then V_TYPE_PROJ_4 --售后项目
54 when projecttype=5 then V_TYPE_PROJ_5 --售后非项目
55 end,
56 V_STATE_ALLOW,
57 projectmanagerid,
58 projectdirectorid,
59 PRESALESTOTALCOST,
60 startdate,
61 case
62 when to_char(enddate,'yyyy-MM-dd')='1900-01-01' then
63 null
64 else
65 enddate
66 end,
67 customerid,
68 departmentname
69 from bx_project_tmp t
70 where ((t.projecttype=1 and t.projectstate <> '终结') or t.projecttype=4 or t.projecttype=5);
71 end if;
72
73 --插入新增项目信息
74 insert into bx_project
75 (id,
76 project_code,
77 project_name,
78 project_type,
79 project_status,
80 project_manager,
81 PROJECT_DIRECTOR,
82 PROJECT_COSTSUM,
83 project_begindate,
84 project_enddate,
85 project_customerid,
86 project_departmentname)
87 select sys_guid(),
88 projectcode,
89 projectname,
90 case
91 when projecttype=1 then V_TYPE_PROJ_1 --工程项目
92 when projecttype=4 then V_TYPE_PROJ_4 --售后项目
93 when projecttype=5 then V_TYPE_PROJ_5 --售后非项目
94 end,
95 V_STATE_ALLOW, --项目状态：1正常；0禁止
96 projectmanagerid,
97 projectdirectorid,
98 PRESALESTOTALCOST,
99 startdate,
100 case
101 when to_char(enddate,'yyyy-MM-dd')='1900-01-01' then
102 null
103 else
104 enddate
105 end,
106 customerid,
107 departmentname
108 from bx_project_tmp t
109 where t.projectcode not in (select project_code from bx_project)
110 and ((t.projecttype=1 and t.projectstate <> '终结') or t.projecttype=4 or t.projecttype=5);
111
112 --更新项目状态,项目经理,项目总监,开始时间，结束时间
113 update bx_project p
114 set p.project_status = (select case
115 when t.projectstate = '终结' then
116 V_STATE_NOTALLOW
117 else
118 V_STATE_ALLOW
119 end
120 from bx_project_tmp t
121 where t.projectcode = p.project_code),
122 p.project_manager = (select t.projectmanagerid
123 from bx_project_tmp t
124 where t.projectcode = p.project_code),
125 p.project_director = (select t.projectdirectorid
126 from bx_project_tmp t
127 where t.projectcode = p.project_code),
128 p.project_customerid = (select t.CUSTOMERID
129 from bx_project_tmp t
130 where t.projectcode = p.project_code),
131 p.project_customername = null,
132 p.project_departmentname = (select t.departmentname
133 from bx_project_tmp t
134 where t.projectcode = p.project_code),
135 p.project_departmentid=null,
136 p.project_begindate = (select t.startdate
137 from bx_project_tmp t
138 where t.projectcode = p.project_code),
139 p.project_enddate = (select case
140 when to_char(enddate,'yyyy-MM-dd')='1900-01-01' then
141 null
142 else
143 enddate
144 end
145 from bx_project_tmp t
146 where t.projectcode = p.project_code),
147 p.project_costsum = (select t.projcostsum
148 from bx_project_tmp t
149 where t.projectcode = p.project_code)
150 where p.project_code in (select t.projectcode from bx_project_tmp t);
151
152 dbms_output.put_line('工程项目处理完毕' || sysdate);
153 --//售前项目-------------------------------------------------//
154
155 if V_PROJECTCOUNT = 0 then
156 --首次插入所有售前项目
157 insert into bx_project
158 (id,
159 project_code,
160 project_name,
161 project_type,
162 project_status,
163 project_manager,
164 project_begindate,
165 project_enddate,
166 project_CustomerName,
167 PROJECT_DEPARTMENTNAME
168 )
169 select sys_guid(),
170 projectcode,
171 projectname,
172 case
173 when max(projecttype)=2 then V_TYPE_PROJ_2 --售前项目
174 when max(projecttype)=3 then V_TYPE_PROJ_3 --C类行动项目
175 end,
176 max(V_STATE_ALLOW),
177 case
178 when max(saleid) is null then
179 max(presaleid)
180 else
181 max(saleid)
182 end,
183 min(updatedate),
184 max(case
185 when projectstate = '放弃' then
186 case
187 when giveupdate is not null then
188 (giveupdate + 14)
189 END when projectstate = '败标或失败' then case
190 when faildate is not null then
191 (faildate + 14)
192 END when projectstate = '合同签署' then case
193 when submitdate is not null then
194 (submitdate + 14)
195 end else null end) as enddate,
196 max(CustomerName),
197 max(businesName)
198 from bx_sq_project_tmp t
199 group by projectcode, projectname;
200 end if;
201
202 --插入新增项目信息
203 insert into bx_project
204 (id,
205 project_code,
206 project_name,
207 project_type,
208 project_status,
209 project_manager,
210 project_begindate,
211 project_enddate,
212 project_CustomerName,
213 PROJECT_DEPARTMENTNAME
214 )
215 select sys_guid(),
216 projectcode,
217 projectname,
218 case
219 when max(projecttype)=2 then V_TYPE_PROJ_2 --售前项目
220 when max(projecttype)=3 then V_TYPE_PROJ_3 --C类行动项目
221 end,
222 max(V_STATE_ALLOW),
223 case
224 when max(saleid) is null then
225 max(presaleid)
226 else
227 max(saleid)
228 end,
229 min(updatedate),
230 max(case
231 when projectstate = '放弃'
232 then
233 case
234 when giveupdate is not null then (giveupdate + 14)
235 end
236 when projectstate = '败标或失败'
237 then
238 case
239 when faildate is not null then (faildate + 14)
240 end
241 when projectstate = '合同签署'
242 then
243 case
244 when submitdate is not null then (submitdate + 14)
245 end
246 else null end) as enddate,
247 max(CustomerName),
248 max(businesName)
249 from bx_sq_project_tmp t
250 group by projectcode, projectname
251 having t.projectcode not in (select project_code from bx_project);
252
253 --更新售前项目状态,项目经理,开始时间，结束时间
254 update bx_project p
255 set p.project_status = (select max(case
256 when (t.projectstate = '放弃' or
257 t.projectstate = '败标或失败' or
258 t.projectstate = '合同签署') then
259 V_STATE_NOTALLOW
260 else
261 V_STATE_ALLOW
262 end)
263 from bx_sq_project_tmp t
264 group by t.projectcode
265 having t.projectcode = p.project_code),
266 p.project_manager = (select case
267 when max(t.saleid) is null then
268 max(t.presaleid)
269 else
270 max(t.saleid)
271 end
272 from bx_sq_project_tmp t
273 group by t.projectcode, t.projectname
274 having t.projectcode = p.project_code),
275 p.project_customername = (select max(t.CUSTOMERNAME)
276 from bx_sq_project_tmp t
277 group by t.projectcode, t.projectname
278 having t.projectcode = p.project_code),
279 p.project_customerid = null,
280 p.project_departmentname = (select max( t.businesname)
281 from bx_sq_project_tmp t
282 group by t.projectcode, t.projectname
283 having t.projectcode = p.project_code),
284 p.project_departmentid=null,
285 p.project_begindate = (select min(t.updatedate)
286 from bx_sq_project_tmp t
287 group by t.projectcode, t.projectname
288 having t.projectcode = p.project_code),
289 p.project_enddate = (select max(case
290 when projectstate = '放弃' then
291 case
292 when giveupdate is not null then (giveupdate + 14)
293 END
294 when projectstate = '败标或失败' then case
295 when faildate is not null then
296 (faildate + 14)
297 END
298 when projectstate = '合同签署' then case
299 when submitdate is not null then
300 (submitdate + 14)
301 end
302 else null end) as enddate
303 from bx_sq_project_tmp t
304 group by t.projectcode, t.projectname
305 having t.projectcode = p.project_code)
306 where p.project_code in (select t.projectcode from bx_sq_project_tmp t);
307 --更新项目户客户信息
308 update bx_project a
309 set a.project_customername = (select c.customer_name
310 from bx_customer c
311 where c.id = a.project_customerid)
312 where a.project_customerid is not null;
313
314 update bx_project a
315 set a.project_customerid = (select max(c.id)
316 from bx_customer c
317 where c.customer_name =
318 a.project_customername)
319 where a.project_customername is not null;
320
321 update bx_project a
322 set a.project_departmentid = (select max(o.id)
323 from org_group o
324 where o.grouptypeid='dept' and o.NAME=a.project_departmentname)
325 where a.project_departmentname is not null;
326
327
328
329
330 -- 更新项目经理，项目总监的id,为报销系统id
331 update bx_project p
332 set p.project_manager = (select distinct u.id
333 from org_user u
334 where upper(u.tel) = upper(p.project_manager) and u.attr2='0'),
335 p.project_director = (select distinct u.id
336 from org_user u
337 where upper(u.tel) = upper(p.project_director) and u.attr2='0');
338 -- 更新项目经理为空的为部门经理
339 update bx_project p
340 set p.project_manager =(select distinct u.id
341 from org_user u
342 where u.tel = (select owner
343 from org_group
344 where grouptypeid = 'dept'
345 and id = p.project_departmentid)
346 and u.attr2 = '0')
347 where p.project_manager is null;
348 --更新项目总监name,为报销系统name
349 update bx_project p
350 set p.project_manager_name = (select distinct u.name
351 from org_user u
352 where u.id = p.project_manager and u.attr2='0'),
353 p.project_director_name = (select distinct u.name
354 from org_user u
355 where u.id = p.project_director and u.attr2='0');
356 dbms_output.put_line('售前项目处理完毕' || sysdate);
357 --插入可报销账户信息------------------------------------
358 SELECT COUNT(*) INTO V_ACCOUNTCOUNT FROM BX_ACCOUNT;
359 IF V_ACCOUNTCOUNT = 0 --判断可报销账户为空时根据BX_PROJECT全量插入
360 THEN
361 insert into bx_account
362 (ACC_ID,
363 ACC_CODE,
364 acc_project_code,
365 ACC_NAME,
366 ACC_OWNER,
367 acc_owner_name,
368 ACC_AMOUNT,
369 ACC_BALANCE,
370 ACC_VALID_STARTTIME,
371 ACC_VALID_ENDTIME,
372 ACC_STATUS,
373 acc_type,
374 acc_type_name,
375 ACC_SHARE_DEPT,
376 ACC_SHARE_USER,
377 acc_state,
378 acc_customerid,
379 acc_customername,
380 acc_departid,
381 acc_departname)
382 select sys_guid(),
383 project_code,
384 project_code,
385 CASE
386 WHEN p.project_type = '1' then
387 '[工程]'||project_name
388 when p.project_type = '2' then
389 '[售前]'||project_name
390 when p.project_type = '3' then
391 '[C类行动]'||project_name
392 when p.project_type = '4' then
393 '[售后]'||project_name
394 ELSE
395 project_name
396 end,
397 case when project_manager is null
398 then project_director else project_manager end,
399 case when project_manager is null
400 then project_director_name else project_manager_name end,
401 case when PROJECT_COSTSUM=0.000 then null
402 else PROJECT_COSTSUM end,
403 case when PROJECT_COSTSUM=0.000 then null
404 else PROJECT_COSTSUM end,
405 project_begindate,
406 project_enddate,
407 case
408 when project_status = '正常' then
409 1
410 else
411 0
412 end,
413 CASE
414 WHEN p.project_type = '1' then
415 '1374651194796ada2hnvzv2wo8veus4rm6zgmvw6739i0devx'
416 WHEN p.project_type = '2' then
417 '1374651255197yl0of6isz8g14bds7fwmqqow1524p9b3gw3f'
418 WHEN p.project_type = '3' then
419 '1381568247245as8ijsz4neo21v7xm7af2ssc9iqhtf3i2j2s'
420 WHEN p.project_type = '4' then
421 '1381823347964o9whw2n6r88bj1np2fq4b7yspbu50kqb8hds'
422 WHEN p.project_type = '5' then
423 '13819053219062i7sr087m30r9rrhwitykfm4gaq4upmpwtey'
424 END,
425 CASE
426 WHEN p.project_type = '1' then
427 '工程项目直接费用'
428 WHEN p.project_type = '2' then
429 '售前项目直接费用'
430 WHEN p.project_type = '3' then
431 'C类行动费用'
432 WHEN p.project_type = '4' then
433 '售后项目直接费用'
434 WHEN p.project_type = '5' then
435 '售后非项目费用'
436 END,
437 '0',
438 '0',
439 '0',
440 project_customerid,
441 project_customername,
442 project_departmentid,
443 project_departmentname
444 from bx_project p;
445 END IF;
446
447
448 --插入新增的可报销账户
449 insert into bx_account
450 (ACC_ID,
451 ACC_CODE,
452 acc_project_code,
453 ACC_NAME,
454 ACC_OWNER,
455 acc_owner_name,
456 ACC_AMOUNT,
457 ACC_BALANCE,
458 ACC_VALID_STARTTIME,
459 ACC_VALID_ENDTIME,
460 ACC_STATUS,
461 acc_type,
462 acc_type_name,
463 ACC_SHARE_DEPT,
464 ACC_SHARE_USER,
465 acc_state,
466 acc_customerid,
467 acc_customername,
468 acc_departid,
469 acc_departname)
470 select sys_guid(),
471 project_code,
472 project_code,
473 CASE
474 WHEN p.project_type = '1' then
475 '[工程]'||project_name
476 WHEN p.project_type = '2' then
477 '[售前]'||project_name
478 WHEN p.project_type = '3' then
479 '[C类行动]'||project_name
480 WHEN p.project_type = '4' then
481 '[售后]'||project_name
482 ELSE
483 project_name
484 end,
485 case when project_manager is null
486 then project_director else project_manager end,
487 case when project_manager is null
488 then project_director_name else project_manager_name end,
489 case when PROJECT_COSTSUM=0.000 then null
490 else PROJECT_COSTSUM end,
491 case when PROJECT_COSTSUM=0.000 then null
492 else PROJECT_COSTSUM end,
493 project_begindate,
494 project_enddate,
495 case
496 when project_status = '正常' then
497 1
498 else
499 0
500 end,
501 CASE
502 WHEN p.project_type = '1' then
503 '1374651194796ada2hnvzv2wo8veus4rm6zgmvw6739i0devx'
504 WHEN p.project_type = '2' then
505 '1374651255197yl0of6isz8g14bds7fwmqqow1524p9b3gw3f'
506 WHEN p.project_type = '3' then
507 '1381568247245as8ijsz4neo21v7xm7af2ssc9iqhtf3i2j2s'
508 WHEN p.project_type = '4' then
509 '1381823347964o9whw2n6r88bj1np2fq4b7yspbu50kqb8hds'
510 WHEN p.project_type = '5' then
511 '13819053219062i7sr087m30r9rrhwitykfm4gaq4upmpwtey'
512 END,
513 CASE
514 WHEN p.project_type = '1' then
515 '工程项目直接费用'
516 WHEN p.project_type = '2' then
517 '售前项目直接费用'
518 WHEN p.project_type = '3' then
519 'C类行动费用'
520 WHEN p.project_type = '4' then
521 '售后项目直接费用'
522 WHEN p.project_type = '5' then
523 '售后非项目费用'
524 END,
525 '0',
526 '0',
527 '0',
528 project_customerid,
529 project_customername,
530 project_departmentid,
531 project_departmentname
532 from bx_project p
533 where p.project_code not in (select c.acc_project_code from bx_account c where c.acc_project_code is not null);
534
535 --更新可报销账户状态，owner
536 update bx_account acc
537 set acc.ACC_STATUS = (select case
538 when p.project_status = '正常' then
539 1
540 else
541 0
542 end
543 from bx_project p
544 where p.project_code =
545 acc.acc_project_code),
546 acc.ACC_OWNER = 'user:' ||
547 (select case
548 when p.project_manager is null then
549 p.project_director
550 else
551 p.project_manager
552 end
553 from bx_project p
554 where p.project_code =
555 acc.acc_project_code),
556 acc.ACC_OWNER_NAME = '用户:' ||
557 (select case
558 when p.project_manager is null then
559 p.project_director_name
560 else
561 p.project_manager_name
562 end
563 from bx_project p
564 where p.project_code =
565 acc.acc_project_code),
566 acc.ACC_CUSTOMERID = (select p.project_customerid
567 from bx_project p
568 where p.project_code =
569 acc.acc_project_code),
570 acc.ACC_CUSTOMERNAME = (select p.project_customername
571 from bx_project p
572 where p.project_code =
573 acc.acc_project_code),
574 acc.acc_valid_starttime = (select p.project_begindate
575 from bx_project p
576 where p.project_code =
577 acc.acc_project_code),
578 acc.acc_valid_endtime = (select p.project_enddate
579 from bx_project p
580 where p.project_code =
581 acc.acc_project_code),
582 acc.acc_amount = (select p.project_costsum
583 from bx_project p
584 where p.project_code =
585 acc.acc_project_code
586 and p.project_type = '1') --只针对工程更新额度
587 where acc.acc_project_code in
588 (select p.project_code
589 from bx_project p
590 where p.project_code is not null);
591 update bx_account acc
592 set acc.acc_departid = (select p.project_departmentid
593 from bx_project p
594 where p.project_code =
595 acc.acc_project_code and p.project_departmentid is not null),
596 acc.acc_departname = (select p.project_departmentname
597 from bx_project p
598 where p.project_code =
599 acc.acc_project_code and p.project_departmentid is not null)
600 where acc.acc_project_code in (select p.project_code from bx_project p where p.project_code is not null);
601 --更新可报销账户余额
602 update bx_account acc
603 set acc.acc_balance = acc.acc_amount-acc.acc_usedamount
604 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);
605 dbms_output.put_line('报销账户处理完毕' || sysdate);
606
607 ------插入可报销账户与费用关系表---------------------------
608 --工程项目与费用关系
609 insert into bx_acc_sub_relation r
610 (id, acc_id, acc_name, subject_id, subject_name)
611 select sys_guid(), acc_id, acc_name, id, sub_name
612 from (select acc_id, acc_name
613 from bx_account
614 where (acc_type =
615 '1374651194796ada2hnvzv2wo8veus4rm6zgmvw6739i0devx')
616 and acc_id not in
617 (select distinct acc_id from bx_acc_sub_relation)) a,
618 (select id, sub_name
619 from bx_subject
620 where sub_name in
621 ('电话费', '办公费', '邮运费', '飞机票', '火车票', '订票服务费', '汽车票', '住宿费', '房租', '中介费','物业费',
622 '上网费', '电费', '水费', '燃气费','有线电视费', '暖气费', '礼品','交通票','餐费')) b;
623
624 --售后项目与费用关系
625 insert into bx_acc_sub_relation r
626 (id, acc_id, acc_name, subject_id, subject_name)
627 select sys_guid(), acc_id, acc_name, id, sub_name
628 from (select acc_id, acc_name
629 from bx_account
630 where (acc_type =
631 '1381823347964o9whw2n6r88bj1np2fq4b7yspbu50kqb8hds')
632 and acc_id not in
633 (select distinct acc_id from bx_acc_sub_relation)) a,
634 (select id, sub_name
635 from bx_subject
636 where sub_name in
637 ('电话费', '办公费', '邮运费', '飞机票', '火车票', '交通票',
638 '订票服务费', '汽车票', '住宿费', '礼品','餐费')) b;
639
640
641 --售后非项目与费用关系
642 insert into bx_acc_sub_relation r
643 (id, acc_id, acc_name, subject_id, subject_name)
644 select sys_guid(), acc_id, acc_name, id, sub_name
645 from (select acc_id, acc_name
646 from bx_account
647 where (acc_type =
648 '13819053219062i7sr087m30r9rrhwitykfm4gaq4upmpwtey')
649 and acc_id not in
650 (select distinct acc_id from bx_acc_sub_relation)) a,
651 (select id, sub_name
652 from bx_subject
653 where sub_name in('电话费', '邮运费','餐费')) b;
654
655 --售前项目与费用关系
656 insert into bx_acc_sub_relation r
657 (id, acc_id, acc_name, subject_id, subject_name)
658 select sys_guid(), acc_id, acc_name, id, sub_name
659 from (select acc_id, acc_name
660 from bx_account
661 where (acc_type =
662 '1374651255197yl0of6isz8g14bds7fwmqqow1524p9b3gw3f')
663 and acc_id not in
664 (select distinct acc_id from bx_acc_sub_relation)) a,
665 (select id, sub_name
666 from bx_subject
667 where sub_name in
668 ('办公费', '邮运费', '印刷装订费', '飞机票', '火车票', '交通票','餐费',
669 '订票服务费', '汽车票', '住宿费', '租车费', '礼品', '会议费', '汽油费', '停车费', '招标服务费','餐费')) b;
670
671 --售前C类行到与费用类型关系
672 insert into bx_acc_sub_relation r
673 (id, acc_id, acc_name, subject_id, subject_name)
674 select sys_guid(), acc_id, acc_name, id, sub_name
675 from (select acc_id, acc_name
676 from bx_account
677 where (acc_type =
678 '1381568247245as8ijsz4neo21v7xm7af2ssc9iqhtf3i2j2s')
679 and acc_id not in
680 (select distinct acc_id from bx_acc_sub_relation)) a,
681 (select id, sub_name
682 from bx_subject
683 where sub_name in
684 ('办公费', '邮运费', '印刷装订费', '飞机票', '火车票', '交通票','餐费',
685 '订票服务费', '汽车票', '住宿费', '租车费', '礼品', '会议费', '汽油费', '停车费', '招标服务费')) b;
686
687
688 --插入工程项目成员信息-------------------------------------
689 SELECT COUNT(*) INTO V_PROJECTMEMBERCOUNT FROM bx_project_member;
690 IF V_PROJECTMEMBERCOUNT = 0 --判断是否全量插入
691 THEN
692 insert into bx_project_member
693 (id,
694 project_code,
695 project_name,
696 project_member,
697 project_member_startdate,
698 project_member_enddate,
699 sa_id)
700 select sys_guid(),
701 projectcode,
702 projectname,
703 userid,
704 startdate,
705 enddate,
706 resourceid
707 from bx_project_member_tmp mt
708 where mt.projectcode in (select p.project_code from bx_project p where p.project_code is not null)
709 and mt.userid is not null and mt.projecttype='1';
710 END IF;
711 insert into bx_project_member
712 (id,
713 project_code,
714 project_name,
715 project_member,
716 project_member_startdate,
717 project_member_enddate,
718 sa_id)
719 select sys_guid(),
720 projectcode,
721 projectname,
722 userid,
723 startdate,
724 enddate,
725 resourceid
726 from bx_project_member_tmp mt
727 where mt.projectcode in (select p.project_code from bx_project p where p.project_code is not null)
728 and mt.userid is not null and mt.projecttype='1'
729 and mt.resourceid not in (select pm.sa_id from bx_project_member pm);
730 --更新成员，开始时间，结束时间
731 update bx_project_member m
732 set m.project_member = (select mt.userid
733 from bx_project_member_tmp mt
734 where mt.resourceid = m.sa_id),
735 m.project_member_startdate = (select mt.startdate
736 from bx_project_member_tmp mt
737 where mt.resourceid = m.sa_id),
738 m.project_member_enddate = (select mt.enddate
739 from bx_project_member_tmp mt
740 where mt.resourceid = m.sa_id)
741 where m.sa_id in (select mt.resourceid from bx_project_member_tmp mt where mt.resourceid is not null);
742 --更新成员信息为报销系统id
743 update bx_project_member m
744 set m.project_member = (select distinct u.id
745 from org_user u
746 where u.tel = m.project_member and u.attr2='0');
747
748 update bx_project_member m
749 set m.project_member_name = (select distinct u.name
750 from org_user u
751 where u.id = m.project_member and u.attr2='0');
752 dbms_output.put_line('工程项目成员处理完毕' || sysdate);
753 --插入可报销账户权限表
754 insert into bx_acc_limit
755 (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
756 select sys_guid(),
757 acc.acc_id,
758 acc.acc_name,
759 m.project_member,
760 m.project_member_name
761 from bx_account acc, bx_project_member m
762 where acc.acc_project_code = m.project_code
763 and m.project_member is not null
764 and (select count(*)
765 from bx_acc_limit l
766 where l.acc_id = acc.acc_id
767 and l.limit_user_id = m.project_member) <= 0;
768 dbms_output.put_line('工程项目权限处理完毕' || sysdate);
769
770
771 --//售前项目权限表----------------------------//
772
773 --插入可报销账户权限表
774 insert into bx_acc_limit
775 (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
776 select sys_guid(),
777 acc.acc_id,
778 acc.acc_name,
779 (select distinct u.id from org_user u where u.tel = t.saleid and u.attr2='0'),
780 (select distinct u.name
781 from org_user u
782 where substr(u.id, instr(u.id, '/') + 1) = t.saleid and u.attr2='0')
783 from bx_account acc, bx_sq_project_tmp t
784 where acc.acc_project_code = t.projectcode
785 and t.saleid is not null
786 and (select count(*)
787 from bx_acc_limit l
788 where substr(l.limit_user_id, instr(l.limit_user_id, '/') + 1) =
789 t.saleid
790 and l.acc_id = acc.acc_id) <= 0;
791 insert into bx_acc_limit
792 (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
793 select sys_guid(),
794 acc.acc_id,
795 acc.acc_name,
796 (select distinct u.id from org_user u where u.tel = t.saleid1 and u.attr2='0'),
797 (select distinct u.name
798 from org_user u
799 where substr(u.id, instr(u.id, '/') + 1) = t.saleid1 and u.attr2='0')
800 from bx_account acc, bx_sq_project_tmp t
801 where acc.acc_project_code = t.projectcode
802 and t.saleid1 is not null
803 and (select count(*)
804 from bx_acc_limit l
805 where substr(l.limit_user_id, instr(l.limit_user_id, '/') + 1) =
806 t.saleid1
807 and l.acc_id = acc.acc_id) <= 0;
808 insert into bx_acc_limit
809 (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
810 select sys_guid(),
811 acc.acc_id,
812 acc.acc_name,
813 (select distinct u.id from org_user u where u.tel = t.presaleid and u.attr2='0'),
814 (select distinct u.name
815 from org_user u
816 where substr(u.id, instr(u.id, '/') + 1) = t.presaleid and u.attr2='0')
817 from bx_account acc, bx_sq_project_tmp t
818 where acc.acc_project_code = t.projectcode
819 and t.presaleid is not null
820 and (select count(*)
821 from bx_acc_limit l
822 where substr(l.limit_user_id, instr(l.limit_user_id, '/') + 1) =
823 t.presaleid
824 and l.acc_id = acc.acc_id) <= 0;
825 insert into bx_acc_limit
826 (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
827 select sys_guid(),
828 acc.acc_id,
829 acc.acc_name,
830 (select distinct u.id from org_user u where u.tel = t.presaleid1 and u.attr2='0'),
831 (select distinct u.name
832 from org_user u
833 where substr(u.id, instr(u.id, '/') + 1) = t.presaleid1 and u.attr2='0')
834 from bx_account acc, bx_sq_project_tmp t
835 where acc.acc_project_code = t.projectcode
836 and t.presaleid1 is not null
837 and (select count(*)
838 from bx_acc_limit l
839 where substr(l.limit_user_id, instr(l.limit_user_id, '/') + 1) =
840 t.presaleid1
841 and l.acc_id = acc.acc_id) <= 0;
842 dbms_output.put_line('售前项目权限1处理完毕' || sysdate);
843 --根据售前行动报备插入可报销账户权限表
844
845 insert into bx_acc_limit l
846 (limit_id, acc_id, limit_user_name)
847 select sys_guid(), a.acc_id, pro.d
848 from (select p.projectcode, p.d
849 from (SELECT projectcode,
850 substr(t.ca,
851 instr(t.ca, ',', 1, d.lv) + 1,
852 instr(t.ca, ',', 1, d.lv + 1) -
853 (instr(t.ca, ',', 1, d.lv) + 1)) AS d
854 FROM (SELECT projectcode,
855 ',' || participants || ',' AS ca,
856 length(participants || ',') -
857 nvl(length(REPLACE(participants, ',')), 0) AS cnt
858 FROM bx_sq_report r
859 where r.participants is not null) t,
860 (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) d
861 WHERE d.lv <= t.cnt
862 ORDER BY projectcode) p
863 where p.d is not null
864 group by p.projectcode, p.d
865 order by p.projectcode) pro,
866 bx_account a
867 where a.acc_project_code = pro.projectcode
868 and (select count(*)
869 from bx_acc_limit t
870 where t.acc_id = a.acc_id
871 and t.limit_user_name = pro.d) <= 0;
872
873 update bx_acc_limit l
874 set l.acc_name = (select a.acc_name
875 from bx_account a
876 where a.acc_id = l.acc_id)
877 where l.acc_name is null;
878
879 update bx_acc_limit l
880 set l.limit_user_id = (select distinct u.id
881 from org_user u
882 where u.name = l.limit_user_name and u.attr2='0')
883 where l.limit_user_id is null;
884
885 dbms_output.put_line('售前项目权限2处理完毕' || sysdate);
886 --// 售后项目权限----
887
888
889 insert into bx_acc_limit l
890 (limit_id, acc_id, limit_user_id)
891 select sys_guid(), a.acc_id, pro.d
892 from (select p.projectcode, p.d
893 from (SELECT projectcode,
894 substr(t.ca,
895 instr(t.ca, ',', 1, d.lv) + 1,
896 instr(t.ca, ',', 1, d.lv + 1) -
897 (instr(t.ca, ',', 1, d.lv) + 1)) AS d
898 FROM (SELECT projectcode,
899 ',' || userid || ',' AS ca,
900 length(userid || ',') -
901 nvl(length(REPLACE(userid, ',')), 0) AS cnt
902 FROM bx_project_member_tmp r
903 where r.userid is not null and (r.projecttype='4')) t,
904 (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) d
905 WHERE d.lv <= t.cnt
906 ORDER BY projectcode) p
907 where p.d is not null
908 group by p.projectcode, p.d
909 order by p.projectcode) pro,
910 bx_account a
911 where a.acc_project_code = pro.projectcode
912 and (select count(*)
913 from bx_acc_limit t
914 where t.acc_id = a.acc_id
915 and t.limit_user_name = pro.d) <= 0;
916
917 --更新账户名称
918 update bx_acc_limit l
919 set l.acc_name = (select a.acc_name
920 from bx_account a
921 where a.acc_id = l.acc_id)
922 where l.acc_name is null;
923 --更新人员id
924 update bx_acc_limit l
925 set l.limit_user_id = (select distinct u.id
926 from org_user u
927 where u.tel = substr(l.limit_user_id,instr(l.limit_user_id,'/')+1) and u.attr2='0')
928 where l.limit_user_id is not null;
929 --更新人员名称
930 update bx_acc_limit l
931 set l.limit_user_name = (select distinct u.name
932 from org_user u
933 where u.id = l.limit_user_id and u.attr2='0')
934 where l.limit_user_id is not null;
935 -- 插入售后非项目OWNER 报销权限
936 insert into bx_acc_limit l
937 (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
938 select sys_guid(),
939 a.acc_id,
940 a.acc_name,
941 replace(a.acc_owner, 'user:', ''),
942 replace(a.acc_owner_name, '用户:', '')
943 from bx_account a
944 where a.acc_type_name = '售后非项目费用'
945 and a.acc_owner not in (select 'user:' || limit_user_id
946 from bx_acc_limit
947 where acc_id = a.acc_id);
948 -- 插入售后项目OWNER 报销权限
949 insert into bx_acc_limit l
950 (limit_id, acc_id, acc_name, limit_user_id, limit_user_name)
951 select sys_guid(),
952 a.acc_id,
953 a.acc_name,
954 replace(a.acc_owner, 'user:', ''),
955 replace(a.acc_owner_name, '用户:', '')
956 from bx_account a
957 where a.acc_type_name = '售后项目直接费用'
958 and a.acc_owner not in (select 'user:' || limit_user_id
959 from bx_acc_limit
960 where acc_id = a.acc_id);
961
962 COMMIT;
963 --清理无用的表数据
964 --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) ;
965 --delete from bx_project_member m where not exists(select 1 from bx_project_tmp t where t.projectcode=m.project_code);
966 --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);
967 --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);
968 --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);
969 --清除无用的账户权限
970 delete from bx_acc_limit l
971 where not exists (select 1 from bx_account a where a.acc_id = l.acc_id);
972 --清除离职的人员账户权限表
973 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));
974 --清除没有人员的账户权限
975 delete from bx_acc_limit where limit_user_id is null ;
976 --清除无用的账户关系
977 delete from bx_acc_sub_relation r
978 where not exists (select 1 from bx_account a where a.acc_id = r.acc_id);
979 --清除无用的票据登记信息
980 delete from bx_invoice_info b where not exists (select 1 from bx_approval a where a.batch_code=b.batch);
981 --更新工程项目费用承担人为报销人
982 update bx_account a set a.acc_share_user='1' where a.acc_type_name='工程项目直接费用' and a.acc_share_user<>'1';
983 --更新售前项目的费用承担方式为报销人和报销人所在部门承担
984 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类行动费用';
985 --更新售后项目和售后非项目的费用承担方式为报销人承担
986 update bx_account a set a.acc_share_user=1 where a.acc_type_name='售后项目直接费用' or a.acc_type_name='售后非项目费用';
987
988 commit;
989 /*
990 Exception
991 WHEN OTHERS Then
992 ROLLBACK;
993 v_err_msg:=sqlcode||';'||sqlerrm;
994 INSERT INTO BX_SYNC_RESULT R(
995 id,
996 sync_date,
997 sync_type,
998 sync_state,
999 exception,
1000 sync_data
1001 )
1002 SELECT sys_guid(),sysdate,P_SYNC_TYPE,'0',v_err_msg,'项目' from dual;
1003 commit;
1004 */
1005 End P_TMP_PROJECT;
project.sql