作者官方网站:http://www.wxl568.cn
/*
*-- Author:
*-- Creation time:2017-05-08 12:16:00
*-- Description:ActualCom同步实战值模型
*/
procedure PROC_SYNC_ACTUALCOM is
V_TEMP_COUNT number;
V_TEMPBIG_COUNT number;
begin
select
count(*)
into
V_TEMP_COUNT
from
TM_HRMIS_ACTUALCOMBAT ttc where ttc.xmnametype in('hxxmjy','expl','trans_org_team_mgmt');
if V_TEMP_COUNT > 0 then
delete from TM_HRMIS_ACTUALCOMBAT ttc where ttc.xmnametype in('hxxmjy','expl','trans_org_team_mgmt');
--xmfzr项目负责人
--xmjl项目经理
--xmcy项目成员
insert into TM_HRMIS_ACTUALCOMBAT(emp_code,xmsum,xmjxtype,constr, constr1,xmnametype,xmnamet,cratedate )
select
lst.emp_code,
'在司参加过'|| lst.proj_count||'个项目' xmsum,
lst.info xmjxType,
'曾参与的项目有:</br>'||(select listagg(substr(column_value, 11)||'</br>', chr(10)) within group(order by column_value desc) from table(cux_hrmis_utils_pkg.STRING_SPLIT(lst.comm, chr(10)))) conStr,'' constr1,'hxxmjy' xmNameType,'项目经验' xmNameT,sysdate cratedate
from
(select
tmp.emp_code,
sum(tmp.proj_count) proj_count,
listagg(tmp.info, ',') within group(order by tmp.role_name desc) info,
listagg(tmp.comm, chr(10)) within group(order by tmp.comm desc) comm
from
(select
pexp.emp_code,
pexp.role_name,
count(pexp.emp_code) proj_count,
count(pexp.emp_code) || '个项目担任' || pexp.role_name info,
listagg(to_char(NVL(pexp.invalid_start,TRUNC(SYSDATE-10000)), 'yyyy-mm-dd') || pexp.attr1 || '【' || pexp.attr5 || '】(' || to_char(pexp.invalid_start, 'yyyy-mm-dd') || ')', chr(10)) within group(order by NVL(pexp.invalid_start,TRUNC(SYSDATE-10000)) desc) comm
from
(select
t.emp_code,
case
when nvl(t.attr6, 'EMPTY') not in(select item_name from sys_dict_item where type_code = 'hxxmjy' and status = '1') then
'项目成员'
else
t.attr6
end role_name,
t.attr1,
t.attr5,
t.invalid_start
from
tm_hrmis_search_info t
---搜索项目经验,除兼职、全职,临时变动配合搜索接口暂时都要
---and t.attr4 in('1', '2')
where
t.styp_code = 'PROJ_EXP'
and t.data_source='SAP'
and t.attr1 != 'Null') pexp
group by
pexp.emp_code,
pexp.role_name) tmp
group by
tmp.emp_code) lst;
--处理()没值
update TM_HRMIS_ACTUALCOMBAT ttc set ttc.constr=replace(ttc.constr, '()', '') where ttc.xmnametype in('hxxmjy');
--司外经验
insert into TM_HRMIS_ACTUALCOMBAT(emp_code,xmsum,xmjxtype,constr,constr1,xmnametype,xmnamet,cratedate )
select expl.pernr emp_code,to_char(expl.begda, 'yyyy-mm-dd') xmsum,to_char(expl.begda, 'yyyy.mm')||'~'||to_char(expl.endda, 'yyyy.mm') xmjxtype,expl.zhrdw constr1,expl.Zhrzw constr1,'expl' xmNameType,'司外经验' xmNameT,sysdate crateDate from tm_hrmis_external_exp expl
left join tm_hrmis_user thu on thu.emp_code=expl.pernr
where 1=1 order by expl.pernr asc,expl.begda desc ;
--其它经验
insert into TM_HRMIS_ACTUALCOMBAT(emp_code,xmsum,xmjxtype,constr,constr1,xmnametype,xmnamet,cratedate )
select oneEmp.emp_code,'' xmsum,'在司有'||oneEmp.str constr,'' constr1,'' xmjxtype,'trans_org_team_mgmt' xmNameType,'其它经验' xmNameT,sysdate crateDate from(
select hsi.emp_code,wm_concat(hsi.label_alias) str from (
select gl.emp_code,gl.label_alias from tm_hrmis_search_info gl
where gl.styp_code in('TEAM_MGMT','TRANS_ORG') group by gl.emp_code,gl.label_alias
) hsi group by hsi.emp_code
) oneEmp ;
commit;
end if;
-----检查清理大数据同步数据
select
count(*)
into
V_TEMPBIG_COUNT
from
SYNC_TM_HRMIS_GWQSJ ;
if V_TEMPBIG_COUNT > 0 then
delete from TM_HRMIS_ACTUALCOMBAT ttc where ttc.xmnametype in('gwjy');
delete from TM_HRMIS_ES_GWQSJ;
-------同步大数据同步过来岗位经验
-----------------处理岗位后边乱七八糟永久的日期
update SYNC_TM_HRMIS_GWQSJ gww set gww.dateto=to_char(sysdate,'yyyyMMdd') where gww.dateto> to_char(sysdate,'yyyyMMdd');
---------处理管理线上显示不正常时间
update SYNC_TM_HRMIS_GLZXL hg set hg.dateto=to_char(sysdate,'yyyyMMdd') where hg.dateto> to_char(sysdate,'yyyyMMdd');
delete SYNC_TM_HRMIS_GWQSJ so where rowid < (select max(rowid) from SYNC_TM_HRMIS_GWQSJ fso where fso.empcode = so.empcode and fso.dateto=so.dateto and fso.datefrom = so.datefrom and fso.createtm=so.createtm );
delete SYNC_TM_HRMIS_GLZXL so where rowid < (select max(rowid) from SYNC_TM_HRMIS_GLZXL fso where fso.empcode = so.empcode and fso.dateto=so.dateto);
---------处理M显示职位
merge into SYNC_TM_HRMIS_GWQSJ empu
using (
select distinct hg.empcode hgempcode,hg.txtsh hgtxtsh,hg.dateto hgdateto from SYNC_TM_HRMIS_GLZXL hg
left join sys_sap_job_sys SSJS on SSJS.OBJID=hg.biczczixl
where
SSJS.Otype='JF' and ssjs.zhrxlfl='M') zn
on (empu.empcode=zn.hgempcode and empu.dateto=zn.hgdateto)
when matched then--满足逻辑跟新M线职位
update
set
empu.txtsh=zn.hgtxtsh;
---------------合并后的日期排序
insert into TM_HRMIS_ACTUALCOMBAT(emp_code,xmsum,xmjxtype,constr,constr1,xmnametype,xmnamet,cratedate )
select zn.empcode emp_code,zn.strdateto xmsum,'' xmjxtype,zn.yearstr||'年'||''||'' constr,zn.txtsh constr1,'gwjy' xmnametype,'岗位经验' xmnamet,sysdate as cratedate from(
select cn.*
from (select CUX_HRMIS_UTILS_PKG.CODE_8BIT_TO_6BIT(gw.empcode) empcode,
gw.txtsh,
to_char(trunc(((to_date((max(gw.dateto)),'yyyymmdd')) -to_date(min(gw.datefrom), 'yyyymmdd')) / 365,2), 'FM990.00') yearstr,
max(gw.dateto) strdateto
from SYNC_TM_HRMIS_GWQSJ gw
-- where gw.empcode in('00693731',000092)
group by gw.txtsh, gw.empcode) cn)
zn order by zn.empcode,zn.strdateto desc;
-------搜索条件岗位数据
insert into TM_HRMIS_ES_GWQSJ (empcode,xmsum,CONSTR,CRATEDATE,XMNAMETYPE,XMNAMET,XMJXTYPE,CONSTR1)
select ttc.emp_code empcode,ttc.constr xmsum,ttc.constr1 constr,ttc.cratedate,ttc.xmnametype,ttc.xmnamet,ttc.xmjxtype,ttc.constr1 from TM_HRMIS_ACTUALCOMBAT ttc where ttc.xmnametype in('gwjy');
------处理管理岗位的(去掉岗、储备)
update TM_HRMIS_ACTUALCOMBAT ttc set ttc.constr1=replace(ttc.constr1, '岗', '') where ttc.xmnametype in('gwjy');
update TM_HRMIS_ACTUALCOMBAT ttc set ttc.constr1=replace(ttc.constr1, '储备', '') where ttc.xmnametype in('gwjy');
update TM_HRMIS_ES_GWQSJ esc set esc.constr1=replace(esc.constr1, '岗', '') where esc.xmnametype in('gwjy');
update TM_HRMIS_ES_GWQSJ esc set esc.constr1=replace(esc.constr1, '储备', '') where esc.xmnametype in('gwjy');
---帮助大数据清空数据
delete from SYNC_TM_HRMIS_GLZXL;
delete from SYNC_TM_HRMIS_GWQSJ ;
commit;
end if;
exception
when others then
rollback;
RECORD_PROC_ERR_LOG('同步实战值模型',
'PROC_SYNC_ACTUALCOM',
sqlcode,
sqlerrm,
SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 400));
end PROC_SYNC_ACTUALCOM;
/*
*-- Creation time:2017-05-24 18:43:28
*-- Description:同步从多棱镜、大数据中心过来的数据、区总处理更新
*/
procedure PROC_SYNC_TM_HISTORY_PERSON is
V_TEMP_COUNT number;
begin
----检查大数据是否同步成功,
select
count(*)
into
V_TEMP_COUNT
from
SYNC_TM_HRMIS_HISTORY_PERSON;
if V_TEMP_COUNT > 0 then
--删除正式表同步新表
delete from TM_HRMIS_HISTORY_PERSON;
---处理正式表
insert into TM_HRMIS_HISTORY_PERSON ( empcode,movedate, zcwddm )
select empcode,movedate, zcwddm from SYNC_TM_HRMIS_HISTORY_PERSON ;
--处理重复数据,生产不存在
delete from TM_HRMIS_HISTORY_PERSON_REPEAT;
insert into TM_HRMIS_HISTORY_PERSON_REPEAT
select max(EMPCODE) EMPCODE, MOVEDATE, T.ZCWDDM from TM_HRMIS_HISTORY_PERSON T
--where T.ZCWDDM = '595Y'
group by T.MOVEDATE, T.ZCWDDM;
---下面处理区总历史数据更新
--防止当前月重复数据
delete from TM_HRMIS_DLJ_BIE_IDXEL dbi where dbi.mont_code=to_char(add_months(sysdate,-1) , 'yyyymm');
--同步当前月数据
insert into TM_HRMIS_DLJ_BIE_IDXEL
(mont_code,
dept_code,
dept_name,
dept_level,
team_id,
health_status,
sum_score,
group_rank,
profit_score,
market_score,
quality_score,
risk_score,
brand_score,
load_tm,
inc_month,
all_rank)
select mont_code,
dept_code,
dept_name,
dept_level,
team_id,
health_status,
sum_score,
group_rank,
profit_score,
market_score,
quality_score,
risk_score,
brand_score,
load_tm,
sysdate inc_month,
all_rank
from SYNC_DLJ_BIE_IDXEL
where mont_code = to_char(add_months(sysdate, -1), 'yyyymm');
---更新
for zn in (select thp.empcode, thp.zcwddm, thp.movedate
from TM_HRMIS_HISTORY_PERSON_REPEAT thp) loop
update TM_HRMIS_DLJ_BIE_IDXEL empu
set empu.emp_code = CUX_HRMIS_UTILS_PKG.CODE_8BIT_TO_6BIT(zn.empcode)
where empu.mont_code = substr(zn.movedate, 0, 6)
and empu.dept_code = zn.zcwddm;
end loop;
---补交数据替换下个月的区总
for znOne in( select oneA.Dept_Code
from (
select bi.dept_code
from TM_HRMIS_DLJ_BIE_IDXEL bi
order by bi.mont_code asc
) oneA
--where oneA.dept_code='024Y'
group by oneA.dept_code ) loop
for znTwo in(select Dv.* from( select dbiC.* from TM_HRMIS_DLJ_BIE_IDXEL dbiC where dbiC.Dept_Code =znOne.Dept_Code and dbiC.emp_code is not null order by dbiC.mont_code
asc )Dv
) loop
dbms_output.put_line(znTwo.Emp_Code||':'||znTwo.Dept_Code);
update TM_HRMIS_DLJ_BIE_IDXEL empu
set empu.emp_code = znTwo.Emp_Code,
empu.load_tm=sysdate
where empu.dept_code = znTwo.Dept_Code and empu.mont_code=(znTwo.Mont_Code+1) and empu.Emp_Code is null;
end loop;
end loop;
---帮助大数据清空数据
delete from SYNC_TM_HRMIS_HISTORY_PERSON;
commit;
end if;
exception
when others then
rollback;
RECORD_PROC_ERR_LOG('同步战绩值区总数据',
'PROC_SYNC_TM_HRMIS_HISTORY_PERSON',
sqlcode,
sqlerrm,
SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 400));
end PROC_SYNC_TM_HISTORY_PERSON;
/*
*-- Creation time:2017-07-28 11:32:16
*-- Description:按科技组织汇总创新指数、文化建设、人才管理活力
*/
procedure PROC_SYNC_INNOVATION_COUNT is
-- yearmmddStr varchar2(10);
yearTitle varchar2(500);--年度
jdTitle varchar2(500);--季度
onestr varchar2(10);
twoStr varchar2(10);
threeStr varchar2(10);
fourStr varchar2(10);
fiveStr varchar2(10);
kjenddateStr varchar2(10);
flgNumber number;
begin
--- yearmmddStr:=to_char(sysdate, 'yyyymm');
onestr:=to_char(sysdate, 'yyyy')||'01';
twoStr:=to_char(sysdate, 'yyyy')||'04';
threeStr:=to_char(sysdate, 'yyyy')||'07';
fourStr:=to_char(sysdate, 'yyyy')||'10';
fiveStr:=to_char(sysdate, 'yyyy')||'12';
--计算总数 创新指数季度日期时间控制
select to_char(ic.kj_enddate,'yyyymm') into
kjenddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc;
if (onestr<=kjenddateStr) and (kjenddateStr<twoStr) then
flgNumber:=1;
jdTitle:='第一季度';
elsif (twoStr<=kjenddateStr)and (kjenddateStr<threeStr) then
flgNumber:=2;
jdTitle:='第二季度';
elsif (threeStr<=kjenddateStr) and (kjenddateStr<fourStr) then
flgNumber:=3;
jdTitle:='第三季度';
elsif (fourStr<kjenddateStr) and (kjenddateStr<=fiveStr) then
flgNumber:=4;
jdTitle:='第四季度';
end if;
--删除数据
if flgNumber > 0 then
delete from TM_KJ_INNOVATIONINDEX_COUNT kji where to_char(kji.kj_enddate, 'yyyymm')=kjenddateStr;
--计算总数 创新指数
for countZs in(select count(ic.thiopid) coZs
from TM_KJ_INNOVATIONINDEX ic,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr
where to_char(ic.kj_enddate, 'yyyy-mm') =timeStr.kj_enddateStr
order by ic.THETOTALSCORE desc, ic.kj_enddate desc)
loop
-----------创新指数Cultype=1
insert into TM_KJ_INNOVATIONINDEX_COUNT kjIc
(kjIc.thiopid,
kjIc.thioporgid,
kjIc.thioporgname,
kjIc.Cultype,
kjIc.micro_innovation,
kjIc.theme_innovation,
kjIc.annlinnovccompe,
kjIc.thetotalscore,
kjIc.ranking,
kjIc.kj_create,
kjIc.create_tm,
kjIc.modify_tm,
kjIc.modify_emp_code,
kjIc.create_emp_code,
kjIc.kj_enddate,
kjIc.currentmoney,
kjIc.totalmoney,
kjIc.titlename)
select cn.thiopid,
cn.thioporgid,
cn.thioporgname,
'1' Cultype,
cn.micro_innovation,
cn.theme_innovation,
cn.annlinnovccompe,
cn.thetotalscore,
DENSE_RANK()over(order by nvl(cn.THETOTALSCORE, 0) desc) ranking,
cn.kj_create,
cn.create_tm,
cn.modify_tm,
cn.modify_emp_code,
cn.create_emp_code,
cn.kj_enddate,
cn.currentmoney,
cn.totalmoney,
'CIO线排名(' || to_char(sysdate, 'yyyy') || jdTitle||'):' || DENSE_RANK()over(order by nvl(cn.THETOTALSCORE, 0) desc) || '/'||countZs.Cozs||'' titlename from(
select kji.thiopid,
kji.thioporgid,
kji.thioporgname,
-- '1' Cultype,
kji.micro_innovation,
kji.theme_innovation,
kji.annlinnovccompe,
kji.thetotalscore,
--kji.ranking,
kji.kj_create,
sysdate create_tm,
kji.modify_tm,
kji.modify_emp_code,
kji.create_emp_code,
kji.kj_enddate,
kji.currentmoney,
kji.totalmoney
from TM_KJ_INNOVATIONINDEX kji,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr where to_char(kji.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr order by kji.THETOTALSCORE desc , kji.kj_enddate desc
)cn;
-----------创新指数Cultype=2平均
insert into TM_KJ_INNOVATIONINDEX_COUNT kjIc
(kjIc.thiopid,
kjIc.thioporgid,
kjIc.thioporgname,
kjIc.Cultype,
kjIc.micro_innovation,
kjIc.theme_innovation,
kjIc.annlinnovccompe,
kjIc.thetotalscore,
kjIc.ranking,
kjIc.kj_create,
kjIc.create_tm,
kjIc.modify_tm,
kjIc.modify_emp_code,
kjIc.create_emp_code,
kjIc.kj_enddate,
kjIc.currentmoney,
kjIc.totalmoney,
kjIc.titlename)
select kji.thiopid,
kji.thioporgid,
kji.thioporgname,
'2' Cultype,
(select (sum(a.micro_innovation)/countZs.Cozs) micro_innovation from TM_KJ_INNOVATIONINDEX a,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr where to_char(a.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr) micro_innovation,
(select (sum(a.theme_innovation)/countZs.Cozs) theme_innovation from TM_KJ_INNOVATIONINDEX a,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr where to_char(a.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr) theme_innovation,
(select (sum(a.annlinnovccompe)/countZs.Cozs) annlinnovccompe from TM_KJ_INNOVATIONINDEX a ,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr where to_char(a.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr) annlinnovccompe,
kji.thetotalscore,
'' ranking,
kji.kj_create,
sysdate create_tm,
kji.modify_tm,
kji.modify_emp_code,
kji.create_emp_code,
kji.kj_enddate,
kji.currentmoney,
kji.totalmoney,
'' titlename
from TM_KJ_INNOVATIONINDEX kji,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr where to_char(kji.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr order by kji.THETOTALSCORE desc , kji.kj_enddate desc;
end loop;
-- commit;
end if;
--计算总数文化建设季度日期时间控制
flgNumber:=0;--重新计算文化建设
select to_char(ic.kj_enddate,'yyyymm') into
kjenddateStr
from TM_KJ_CULCON ic
where 1 = ROWNUM
order by ic.kj_enddate desc;
if (onestr<=kjenddateStr) and (kjenddateStr<twoStr) then
flgNumber:=1;
jdTitle:='第一季度';
elsif (twoStr<=kjenddateStr)and (kjenddateStr<threeStr) then
flgNumber:=2;
jdTitle:='第二季度';
elsif (threeStr<=kjenddateStr) and (kjenddateStr<fourStr) then
flgNumber:=3;
jdTitle:='第三季度';
elsif (fourStr<kjenddateStr) and (kjenddateStr<=fiveStr) then
flgNumber:=4;
jdTitle:='第四季度';
end if;
--删除数据
if flgNumber > 0 then
delete from TM_KJ_CULCON_COUNT a
where to_char(a.kj_enddate, 'yyyymm')=kjenddateStr;
-------------------文化建设手机统计表------------------------------
--计算总数 文化建设
for countZs in( select count(cc.org_id) coZs from TM_KJ_CULCON cc ,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr
where to_char(cc.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr order by cc.THETOTALSCORE desc , cc.kj_enddate desc)
loop
insert into TM_KJ_CULCON_COUNT kjIc
(kjIc.thfiopid,
kjIc.org_id,
kjIc.org_name,
kjIc.Cultype,
kjIc.activity_org_id,
kjIc.positive_influence,
kjIc.comm_platform,
kjIc.thetotalscore,
kjIc.ranking,
kjIc.kj_create,
kjIc.create_tm,
kjIc.modify_tm,
kjIc.modify_emp_code,
kjIc.create_emp_code,
kjIc.kj_enddate,
kjIc.currentmoney,
kjIc.totalmoney,
kjIc.titlename)
select cn.thfiopid,
cn.org_id,
cn.org_name,
'1' Cultype,
cn.activity_org_id,
cn.positive_influence,
cn.comm_platform,
cn.thetotalscore,
DENSE_RANK()over(order by nvl(cn.THETOTALSCORE, 0) desc) ranking,
cn.kj_create,
cn.create_tm,
cn.modify_tm,
cn.modify_emp_code,
cn.create_emp_code,
cn.kj_enddate,
cn.currentmoney,
cn.totalmoney ,
'CIO线排名(' || to_char(sysdate, 'yyyy') ||jdTitle||'):' || DENSE_RANK()over(order by nvl(cn.THETOTALSCORE, 0) desc) || '/'||countZs.Cozs||'' titlename
from (
select kji.thfiopid,
kji.org_id,
kji.org_name,
kji.activity_org_id,
kji.positive_influence,
kji.comm_platform,
kji.thetotalscore,
kji.kj_create,
sysdate create_tm,
kji.modify_tm,
kji.modify_emp_code,
kji.create_emp_code,
kji.kj_enddate,
kji.currentmoney,
kji.totalmoney
from TM_KJ_CULCON kji,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr
where to_char(kji.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr order by kji.THETOTALSCORE desc , kji.kj_enddate desc
)cn ;
-----------文化建设Cultype=2平均
insert into TM_KJ_CULCON_COUNT kjIc
(kjIc.thfiopid,
kjIc.org_id,
kjIc.org_name,
kjIc.Cultype,
kjIc.activity_org_id,
kjIc.positive_influence,
kjIc.comm_platform,
kjIc.thetotalscore,
kjIc.ranking,
kjIc.kj_create,
kjIc.create_tm,
kjIc.modify_tm,
kjIc.modify_emp_code,
kjIc.create_emp_code,
kjIc.kj_enddate,
kjIc.currentmoney,
kjIc.totalmoney,
kjIc.titlename)
select kji.thfiopid,
kji.org_id,
kji.org_name,
'2' Cultype,
(select (sum(a.activity_org_id)/countZs.Cozs) activity_org_id from TM_KJ_CULCON a,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr
where to_char(a.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr) activity_org_id,
(select (sum(a.positive_influence)/countZs.Cozs) positive_influence from TM_KJ_CULCON a,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr
where to_char(a.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr) positive_influence,
(select (sum(a.comm_platform)/countZs.Cozs) comm_platform from TM_KJ_CULCON a,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr
where to_char(a.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr ) comm_platform,
kji.thetotalscore,
'' ranking,
kji.kj_create,
sysdate create_tm,
kji.modify_tm,
kji.modify_emp_code,
kji.create_emp_code,
kji.kj_enddate,
kji.currentmoney,
kji.totalmoney,
'' titlename
from TM_KJ_CULCON kji,(select to_char(ic.kj_enddate,'yyyy-mm') kj_enddateStr
from TM_KJ_INNOVATIONINDEX ic
where 1 = ROWNUM
order by ic.kj_enddate desc) timeStr
where to_char(kji.kj_enddate, 'yyyy-mm')=timeStr.kj_enddateStr order by kji.THETOTALSCORE desc , kji.kj_enddate desc;
end loop;
end if;
-------------------人才培养手机统计表------------------------------
flgNumber:=0;--重新计算人才培养
select to_char(ic.end_date,'yyyymm') into
kjenddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc;
if(onestr<=kjenddateStr) and (kjenddateStr<threeStr) then
yearTitle:='上半年';
flgNumber:=1;
elsif (threeStr<=kjenddateStr) and (kjenddateStr<=fiveStr) then
yearTitle:='下半年';
flgNumber:=2;
end if;
if flgNumber > 0 then
delete from TM_KJ_PERSONNELTRAINING_COUNT pc where to_char(pc.end_date, 'yyyymm')=kjenddateStr;
for countZs in( select count(cc.org_id) coZs from TM_KJ_PERSONNELTRAINING cc,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(cc.end_date, 'yyyy-mm')=timeStr.kj_enddateStr order by cc.THETOTALSCORE desc , cc.end_date)
loop
insert into TM_KJ_PERSONNELTRAINING_COUNT kjIc
(kjIc.pe_id,
kjIc.org_id,
kjIc.org_name,
kjIc.Cultype,
kjIc.personnelcompetence,
kjIc.cultivationmechanism,
kjIc.satisfactiondegree,
kjIc.thetotalscore,
kjIc.ranking,
kjIc.begin_date,
kjIc.create_tm,
kjIc.modify_tm,
kjIc.modify_emp_code,
kjIc.create_emp_code,
kjIc.end_date,
kjIc.currentmoney,
kjIc.totalmoney,
kjIc.contribute_out,
kjIc.org_effect,
kjIc.inner_comptest,
kjIc.titlename)
select cn.pe_id,
cn.org_id,
cn.org_name,
'1' Cultype,
cn.personnelcompetence,
cn.cultivationmechanism,
cn.satisfactiondegree,
cn.thetotalscore,
DENSE_RANK()over(order by nvl(cn.THETOTALSCORE, 0) desc) ranking,
cn.begin_date,
cn.create_tm,
cn.modify_tm,
cn.modify_emp_code,
cn.create_emp_code,
cn.end_date,
cn.currentmoney,
cn.totalmoney,
cn.contribute_out,
cn.org_effect,
cn.inner_comptest,
'CIO线排名(' || to_char(sysdate, 'yyyy') ||yearTitle||'):' || DENSE_RANK()over(order by nvl(cn.THETOTALSCORE, 0) desc) || '/'||countZs.Cozs||'' titlename
from (
select kji.pe_id,
kji.org_id,
kji.org_name,
'1' Cultype,
kji.personnelcompetence,
kji.cultivationmechanism,
'' satisfactiondegree,
kji.thetotalscore,
kji.begin_date,
sysdate create_tm,
kji.modify_tm,
kji.modify_emp_code,
kji.create_emp_code,
kji.end_date,
kji.currentmoney,
kji.totalmoney,
kji.contribute_out,
kji.org_effect,
kji.inner_comptest
from TM_KJ_PERSONNELTRAINING kji,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(kji.end_date, 'yyyy-mm')=timeStr.kj_enddateStr order by kji.THETOTALSCORE desc , kji.end_date
)cn ;
insert into TM_KJ_PERSONNELTRAINING_COUNT kjIc
(kjIc.pe_id,
kjIc.org_id,
kjIc.org_name,
kjIc.Cultype,
kjIc.personnelcompetence,
kjIc.cultivationmechanism,
kjIc.satisfactiondegree,
kjIc.thetotalscore,
kjIc.ranking,
kjIc.begin_date,
kjIc.create_tm,
kjIc.modify_tm,
kjIc.modify_emp_code,
kjIc.create_emp_code,
kjIc.end_date,
kjIc.currentmoney,
kjIc.totalmoney,
kjIc.contribute_out,
kjIc.org_effect,
kjIc.inner_comptest,
kjIc.titlename)
select kji.pe_id,
kji.org_id,
kji.org_name,
'2' Cultype,
(select (sum(a.personnelcompetence)/countZs.Cozs) personnelcompetence from TM_KJ_PERSONNELTRAINING a,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(a.end_date, 'yyyy-mm')=timeStr.kj_enddateStr ) personnelcompetence,
(select (sum(a.cultivationmechanism)/countZs.Cozs) cultivationmechanism from TM_KJ_PERSONNELTRAINING a,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(a.end_date, 'yyyy-mm')=timeStr.kj_enddateStr ) cultivationmechanism,
(select (sum(a.satisfactiondegree)/countZs.Cozs) satisfactiondegree from TM_KJ_PERSONNELTRAINING a,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(a.end_date, 'yyyy-mm')=timeStr.kj_enddateStr ) satisfactiondegree,
kji.thetotalscore,
'' ranking,
kji.begin_date,
sysdate create_tm,
kji.modify_tm,
kji.modify_emp_code,
kji.create_emp_code,
kji.end_date,
kji.currentmoney,
kji.totalmoney,
(select (sum(a.contribute_out)/countZs.Cozs) contribute_out from TM_KJ_PERSONNELTRAINING a,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(a.end_date, 'yyyy-mm')=timeStr.kj_enddateStr ) contribute_out,
(select (sum(a.org_effect)/countZs.Cozs) org_effect from TM_KJ_PERSONNELTRAINING a,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(a.end_date, 'yyyy-mm')=timeStr.kj_enddateStr ) org_effect,
(select (sum(a.inner_comptest)/countZs.Cozs) inner_comptest from TM_KJ_PERSONNELTRAINING a,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(a.end_date, 'yyyy-mm')=timeStr.kj_enddateStr ) inner_comptest ,
'' titlename
from TM_KJ_PERSONNELTRAINING kji,(select to_char(ic.end_date,'yyyy-mm') kj_enddateStr
from TM_KJ_PERSONNELTRAINING ic
where 1 = ROWNUM
order by ic.end_date desc) timeStr
where to_char(kji.end_date, 'yyyy-mm')=timeStr.kj_enddateStr order by kji.THETOTALSCORE desc , kji.end_date;
end loop;
end if;
commit;
exception
when others then
rollback;
RECORD_PROC_ERR_LOG('按科技组织汇总创新指数活力',
'PROC_SYNC_INNOVATIONINDEX_COUNT',
sqlcode,
sqlerrm,
SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
1,
400));
end PROC_SYNC_INNOVATION_COUNT;
/*
*-- Creation time:2017-07-28 11:32:16
*-- Description:按科技组织架构汇总
*/
procedure PROC_SYNC_ORGJG_COUNT is
----------------OK架构统计总数---------------------------------------
begin
--清空再统计
delete from TM_HRMIS_KJORG_JG;
for znOne in (select o.ORGID,
o.org_short_name ORGNAME,
o.ORGSUPID,
o.path ORGPATH,
o.DEPTMANAGER
from TM_ORG o
where O.STATE = 1
start with (O.ORGID = 82)
connect by prior O.ORGID = O.ORGSUPID) loop
insert into TM_HRMIS_KJORG_JG kj
(kj.org_id,
kj.org_short_name,
kj.m_emp_code,
kj.m_emp_name,
kj.tote_number,
kj.org_id_parent,
kj.position_name,
kj.createtm)
select cn.org_id,
tc2.org_short_name,
tc2.emp_code,
tc2.emp_name,
tote_number,
tc2.org_id_parent,
tc2.position_name,
sysdate
from (select count(orgid) tote_number, znOne.Orgid org_id
from (select t2.*
from (select ORGID
from TM_ORG o
where O.STATE = 1
start with (O.ORGID = znOne.Orgid)
connect by prior O.ORGID = O.ORGSUPID) t1,
(select u.org_id orgid, u.emp_code, u.emp_name
from tm_hrmis_user u
where u.status = 1
and nvl(U.out_time, trunc(sysdate)) >=
trunc(sysdate)) t2
where t1.orgid = t2.orgid)
) cn,
(select o.orgid,
o.org_short_name,
thu.emp_code,
thu.emp_name,
o.orgsupid org_id_parent,
thu.position_name,
sysdate
from TM_ORG o
inner join tm_hrmis_user thu
on thu.emp_code = o.deptmanager
where o.orgid = znOne.Orgid) tc2
where cn.org_id = tc2.orgid;
end loop;
commit;
exception
when others then
rollback;
RECORD_PROC_ERR_LOG('按科技组织架构汇总',
'PROC_SYNC_ORGJG_COUNT',
sqlcode,
sqlerrm,
SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
1,
400));
end PROC_SYNC_ORGJG_COUNT;
end CUX_HRMIS_SYNC_KJBUSINESS_PKG;