create or replace procedure p_abc_dim_dept(p_dt date default sysdate, --to日期
v_init_flg number default 0 --1:初始化 0:非初始化
) is
/*************************************************************
author : blt
created : 2019-05-04
purpose :
version modify time desc
------- ----- ---------- -------------------------------
v1.0 blt 2019-05-04 生成机构维表
**************************************************************/
v_sqlstate varchar2(1000);
v_proc_name varchar2(300);
v_rowcount number(12);
--自定义变量
v_fm_date date;
v_max_sgkey number;
begin
v_sqlstate := '变量赋值';
v_proc_name := 'P_ABC_DIM_DEPT';
v_fm_date := p_dt;
v_sqlstate := '清空临时表';
execute immediate 'TRUNCATE TABLE ABC_DIM_DEPT_TMP01';
execute immediate 'TRUNCATE TABLE ABC_DIM_DEPT_TMP02';
execute immediate 'TRUNCATE TABLE ABC_DIM_DEPT_TMP03';
v_sqlstate := '初始化删除工作';
if v_init_flg = 1 then
delete abc_dim_dept;
end if;
v_sqlstate := '得到ASU机构路径';
insert into abc_dim_dept_tmp01
select dept_code,
dept_name,
parent_dept_code,
level type_level,
sys_connect_by_path(dept_code, '@') || '@' code_path,
sys_connect_by_path(dept_name, '@') || '@' name_path
from (select a.dept_code, a.dept_name, a.parent_dept_code
from t_ods_dept a) t
start with parent_dept_code is null --从集团往下找
connect by parent_dept_code = prior dept_code;
v_sqlstate := '得到机构遍平化';
insert into abc_dim_dept_tmp02
select a.code_path,
nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '') dept_code,
nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '') dept_name,
case
when v_init_flg = 1 then
date '1993-03-01' --初始化时初始机构起始时间改创建日期当月第一天
else
v_fm_date
end fm_tm, --起始时间
to_date('9999-12-31', 'YYYY-MM-DD') to_tm,
nvl(regexp_substr(code_path, '[^@]+', 1, 1),
nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level1_code,
nvl(regexp_substr(name_path, '[^@]+', 1, 1),
nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level1_name,
nvl(regexp_substr(code_path, '[^@]+', 1, 2),
nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level2_code,
nvl(regexp_substr(name_path, '[^@]+', 1, 2),
nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level2_name,
nvl(regexp_substr(code_path, '[^@]+', 1, 3),
nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level3_code,
nvl(regexp_substr(name_path, '[^@]+', 1, 3),
nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level3_name,
nvl(regexp_substr(code_path, '[^@]+', 1, 4),
nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level4_code,
nvl(regexp_substr(name_path, '[^@]+', 1, 4),
nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level4_name,
nvl(regexp_substr(code_path, '[^@]+', 1, 5),
nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level5_code,
nvl(regexp_substr(name_path, '[^@]+', 1, 5),
nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level5_name,
a.parent_dept_code
from abc_dim_dept_tmp01 a;
v_sqlstate := '取阿修罗与ABC机构表最大代理键';
select nvl(max(dept_id), 0) into v_max_sgkey from abc_dim_dept;
insert into abc_dim_dept_tmp03
select nvl(b.dept_id,
v_max_sgkey + row_number()
over(partition by b.dept_id order by b.dept_id)) dept_id, --对新状态记录分配新代理键
a.dept_code,
a.dept_name,
a.fm_tm,
a.to_tm,
a.level1_code,
a.level1_name,
a.level2_code,
a.level2_name,
a.level3_code,
a.level3_name,
a.level4_code,
a.level4_name,
a.level5_code,
a.level5_name,
a.parent_dept_code
from abc_dim_dept_tmp02 a
left join abc_dim_dept b
on a.dept_code || a.dept_name || a.level1_code || a.level1_name ||
a.level2_code || a.level2_name || a.level3_code || a.level3_name ||
a.level4_code || a.level4_name || a.level5_code || a.level5_name ||
a.parent_dept_code =
b.dept_code || b.dept_name || b.level1_code || b.level1_name ||
b.level2_code || b.level2_name || b.level3_code || b.level3_name ||
b.level4_code || b.level4_name || b.level5_code || b.level5_name ||
b.parent_dept_code
and b.to_tm = date '9999-12-31';
v_sqlstate := '更新有变更的阿修罗与ABC机构日期到前一天';
update abc_dim_dept a
set a.to_tm = v_fm_date - 1, load_tm = sysdate
where a.to_tm = date
'9999-12-31'
and a.dept_code in (select b.dept_code
from abc_dim_dept_tmp03 b
where b.dept_id > v_max_sgkey);
commit;
v_sqlstate := '插入新阿修罗与ABC机构数据';
insert into abc_dim_dept
(dept_id,
dept_code,
dept_name,
fm_tm,
to_tm,
level1_code,
level1_name,
level2_code,
level2_name,
level3_code,
level3_name,
level4_code,
level4_name,
level5_code,
level5_name,
parent_dept_code,
load_tm)
select dept_id,
dept_code,
dept_name,
fm_tm,
to_tm,
level1_code,
level1_name,
level2_code,
level2_name,
level3_code,
level3_name,
level4_code,
level4_name,
level5_code,
level5_name,
parent_dept_code,
sysdate load_tm
from abc_dim_dept_tmp03 a
where a.dept_id > v_max_sgkey;
commit;
v_sqlstate := '删除ASU_DEPT无效部门数据';
delete abc_dim_dept a where a.fm_tm > a.to_tm;
commit;
v_sqlstate := '结束';
exception
when others then
rollback;
commit;
end p_abc_dim_dept;
/
---------------------------------------------------------------------------------------------------------
--创建源头机构表
create table t_ods_dept
(
id int,
dept_code varchar2(10),
dept_name varchar2(100),
parent_dept_code varchar2(10)
)
--生成数据
insert into t_ods_dept values(1,'001','总部',null);
insert into t_ods_dept values(2,'00101','华南','001');
insert into t_ods_dept values(3,'00102','华北','001');
insert into t_ods_dept values(4,'00103','华西','001');
insert into t_ods_dept values(5,'0010101','深圳','00101');
insert into t_ods_dept values(6,'0010102','广州','00101');
insert into t_ods_dept values(7,'0010103','东莞','00101');
insert into t_ods_dept values(8,'0010201','北京','00102');
insert into t_ods_dept values(9,'0010202','天津','00102');
insert into t_ods_dept values(10,'0010301','重庆','00103');
commit;
--初始化机构表
begina
-- Call the procedure
p_abc_dim_dept(date'2019-05-01',1);
end;
select * from abc_dim_dept;
--5月10号把东莞机构划到了华北地区。
update t_ods_dept a set a.parent_dept_code='00102' where a.dept_code='0010103';
begin
-- Call the procedure
p_abc_dim_dept(date'2019-05-10',0);
end;
select * from abc_dim_dept;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31535951/viewspace-2644364/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31535951/viewspace-2644364/