create or replace procedure yuchuli_insert is
begin
---预处理新增
declare
--标记地税过来的脏数据
cursor isallnull is
select qybm
from gh_dsqyxx
where (shxydm is null and nsrsbh is null and jsjdm is null);
--统一社会信用重复标识
cursor tyshxydmData is
select djxh from GH_DSQYXX_TEMP t where t.isdirtydata = '1';
--纳税人识别号重复标识
cursor nsrsbhData is
select djxh from GH_DSQYXX_TEMP t where t.isdirtydata = '2';
isallnullFlag varchar2(1) := 0;
tyshxydmFlag varchar2(1) := 0;
nsrsbhFlag varchar2(1) := 0;
-----c.新增用户和核实表
cursor yg is
select t.* ，qyghgx.GHDM,
qyghgx.GHMC,
qyghgx.GHLX,
qyghgx.ZHLX,
qyghgx.QYGHRS,
qyghgx.JHZT,
qyghgx.JHSJ,
qyghgx.SHSJ,
qyghgx.JFLCBL,
qyghgx.GHDM1,
qyghgx.BL1,
qyghgx.GHDM2,
qyghgx.BL2,
qyghgx.GHDM3,
qyghgx.BL3,
qyghgx.GHDM4,
qyghgx.BL4,
qyghgx.GHDM5,
qyghgx.BL5,
qyghgx.GHDM6,
qyghgx.BL6,
qyghgx.QYJC,
qyghgx.sffq
from GH_QYXX t
left join GH_QYGHGX qyghgx
on t.qybm = qyghgx.qybm
where t.isxz = '1';
begin
--------标记脏数据
---组织机构代码、社会统一社会信用、税务登记账号全部为空
update gh_dsqyxx set isdirtydata = '0'; ----1.初始化数据
update gh_dsqyxx qyxx
set qyxx.isdirtydata = '1'
where (shxydm is null and nsrsbh is null and zzjgdm is null);
for rc in isallnull loop
if (isallnullFlag = 0) then
isallnullFlag := 1;
end if;
end loop;
--通知总工会
if (isallnullFlag = 1) then
insert into GH_XTXX
(ID,
TITLE,
CONTENT,
YGLXID,
TYPE,
CJSJ,
CZRYDM,
YWLX,
QYGHDM)
values
(seq_xtxx.nextval,
'预处理数据有问题',
'组织机构代码、社会统一社会信用、税务登记账号全部为空',
'1',
'0',
sysdate,
'预处理',
'0',
'1');
insert into YUCHULI_MARK
values
(SYS_GUID(), '通知总工会', sysdate);
commit;
end if;
--删除临时表数据
execute immediate 'truncate table gh_dsqyxx_temp';
--社会统一信用代码重复
insert into gh_dsqyxx_temp
(djxh, isdirtydata)
select n.djxh, '1'
from (select row_number() over(partition by t.shxydm order by t.xgrq desc) as rown,
t.*
from gh_dsqyxx t
where t.shxydm is not null) n
where n.rown > 1;
insert into YUCHULI_MARK
values
(SYS_GUID(),
'insert into gh_dsqyxx_temp社会统一信用代码重复',
sysdate);
commit;
---社会统一信用代码有重复发送通知
for rc in tyshxydmData loop
if (tyshxydmFlag = 0) then
tyshxydmFlag := 1;
end if;
end loop;
if (tyshxydmFlag = 1) then
insert into GH_XTXX
(ID,
TITLE,
CONTENT,
YGLXID,
TYPE,
CJSJ,
CZRYDM,
YWLX,
QYGHDM)
values
(seq_xtxx.nextval,
'预处理数据有问题',
'社会统一信用代码有重复的',
'1',
'0',
sysdate,
'预处理',
'0',
'1');
insert into YUCHULI_MARK
values
(SYS_GUID(), '社会统一信用代码有重复发送通知', sysdate);
commit;
end if;
---税务登记证号重复
insert into gh_dsqyxx_temp
(djxh, isdirtydata)
select n.djxh, '2'
from (select row_number() over(partition by t.nsrsbh order by t.xgrq desc) as rown,
t.*
from gh_dsqyxx t
where t.shxydm is null
and t.nsrsbh is not null) n
where n.rown > 1;
insert into YUCHULI_MARK
values
(SYS_GUID(),
'insert into gh_dsqyxx_temp ---税务登记证号重复',
sysdate);
commit;
---税务登记证号重复发送通知
for rc in nsrsbhData loop
if (nsrsbhFlag = 0) then
nsrsbhFlag := 1;
end if;
end loop;
if (nsrsbhFlag = 1) then
insert into GH_XTXX
(ID,
TITLE,
CONTENT,
YGLXID,
TYPE,
CJSJ,
CZRYDM,
YWLX,
QYGHDM)
values
(seq_xtxx.nextval,
'预处理数据有问题',
'税务登记证号有重复的',
'1',
'0',
sysdate,
'预处理',
'0',
'1');
insert into YUCHULI_MARK
values
(SYS_GUID(), '---税务登记证号重复发送通知', sysdate);
commit;
end if;
--更新地税企业信息的isdirtydata数据
update gh_dsqyxx t
set t.isdirtydata =
(select m.isdirtydata
from gh_dsqyxx_temp m
where m.djxh = t.djxh)
where t.djxh in (select djxh from gh_dsqyxx_temp);
insert into YUCHULI_MARK
values
(SYS_GUID(), '--更新地税企业信息的isdirtydata数据', sysdate);
commit;
--在地税企业信息表中加入企业编码
update gh_dsqyxx t
set t.qybm =
(select m.qybm
from gh_qyxx m
where m.tyshxydm = t.shxydm
and rownum = 1)
where t.shxydm is not null;
insert into YUCHULI_MARK
values
(SYS_GUID(),
'--在地税企业信息表中加入企业编码tyshxydm',
sysdate);
commit;
update gh_dsqyxx t
set t.qybm =
(select m.qybm
from gh_qyxx m
where m.nsrsbh = t.nsrsbh
and rownum = 1)
where t.shxydm is null;
insert into YUCHULI_MARK
values
(SYS_GUID(), '--在地税企业信息表中加入企业编码nsrsbh', sysdate);
commit;
------1、新增企业
-----a、增加gh_qyxx
update gh_qyxx set isxz = '0'; ----初始化数据
insert into gh_qyxx
(QYBM, ----1、企业编码
QYMC, ----2、企业名称
JSJDM, ----3、计算机代码
TYSHXYDM, --4、统一社会信用代码
DJZCLXDM, --5、登记注册类型代码
ZZJGDM, ---6、组织机构代码
JYDZ, --7、经营地址
JYDZYB, --8实际经营地址邮编
ZCDZ, ---9注册地址
ZCDZYB, --10注册地址邮编
JYDZLXDM, ---11经营地址联系电话
SWJGZZJGDM, ---12主管地税代码
SCJXDM, ---13所处街乡代码
NSRZTMC, --14纳税人状态
GJBZHYDM, ---15国家标准行业代码
KYDJRQ, --16税务登记日期
SWDJLXMC, --17税务登记名称
SJQYMC, ---18实际企业名称
SJJYDZ, ---19实际经营地址
SJJYDZYB, ---20实际经营地址邮编
QYZT, ---21企业状态 2未进生产库 3进入生产库
GSRS, --22个税人数
ISTC, ---23是否已剔除 0未剔除 1已剔除
NSRSBH, --24纳税人识别号（税务登记证号）
ISXZ ---25是否新增 0否 1是
)
select CONCAT('A', seq_qybm.nextval) as qybm, ----1、企业编码
qyxx.nsrmc as qymc, ----2、企业名称
qyxx.jsjdm, ---3、计算机代码
qyxx.shxydm as TYSHXYDM, ---4、统一社会信用代码
qyxx.djzclxdm, ----5、登记注册类型代码
qyxx.zzjgdm1, ---6、组织机构代码
qyxx.jydz, --7、经营地址
qyxx.jydzyb, ---8、经营地址邮编
qyxx.zcdz, ---9、注册地址
qyxx.zcdzyb, ---10、注册地址邮编
qyxx.jydzlxdm, ---11、经营地址联系电话
CASE
WHEN qyxx.zgswsdm is not null THEN
qyxx.zgswsdm
else
qyxx.zgswjdm
end as SWJGZZJGDM, ---12主管地税代码
qyxx.scjxdm, ---13、所属街乡代码
qyxx.nsrzt as NSRZTMC, --14、纳税人状态
qyxx.gjbzhydm, -----15、国家标准行业代码
qyxx.kydjrq, ---16、税务登记日期
qyxx.swdjlxmc, ---17、税务登记种类
qyxx.nsrmc as SJQYMC, ---18、纳税人名称
null as SJJYDZ, ---19、实际经营地址
null as SJJYDZYB, ---20、实际经营地址邮编
'0' as QYZT, ---21、企业状态 2未进生产库 3进入生产库
qyxx.zgrs as gsrs, -----22、职工人数
'0' as ISTC, ---23是否已剔除 0未剔除 1已剔除
qyxx.nsrsbh, ------24、纳税人识别号
'1' as ISXZ ---25是否新增 0否 1是
from (select *
from gh_dsqyxx A WHERE a.qybm is null and a.isdirtydata = '0') qyxx;
insert into YUCHULI_MARK
values
(SYS_GUID(), '--新增企业', sysdate);
commit;
----b、增加gh_qyghgx
insert into gh_qyghgx
(qybm, ---企业编码
jhzt, ---建会状态
ghdm, --- 企业工会代码
ghlx, ----工会类型0产业1区域 默认1,
ghdm1,
ghdm2,
ghdm3,
ghdm4,
ghdm5,
ghdm6,
qyjc,
drrq)
select qyxx.qybm,
'18' as jhzt,
--CONCAT('B', seq_qybm.nextval) as ghdm,
nvl（scjx.ZGGHDM,
'scjxZgghdm' ）as ghdm,
'1' as ghlx,
nvl(scjx.ghdm1,
(select ghdm1
from GH_ZD_SCJX
where instr(scjxdm, substr(qyxx.scjxdm, 0, 6)) > 0
and rownum = 1)) as ghdm1,
nvl(scjx.ghdm2,
(select ghdm2
from GH_ZD_SCJX
where instr(scjxdm, substr(qyxx.scjxdm, 0, 6)) > 0
and rownum = 1)) as ghdm2,
nvl(scjx.zgghdm,
(select zgghdm
from GH_ZD_SCJX
where instr(scjxdm, substr(qyxx.scjxdm, 0, 6)) > 0
and rownum = 1)) as ghdm3,
'0' as ghdm4,
'0' as ghdm5,
'0' as ghdm6,
'4' as qyjc,
sysdate as drrq
from gh_qyxx qyxx
left join GH_ZD_SCJX scjx
on qyxx.scjxdm = scjx.scjxdm
where isxz = '1';
insert into YUCHULI_MARK
values
(SYS_GUID(), '--新增企业工会关系', sysdate);
commit;
-----c.新增用户和核实数据
for rc in yg loop
---员工
insert into GH_JC_YG
(YGDM, YHM, MM, BZ, YGZT)
values
(CONCAT('Y', seq_ygdm.nextval),
CASE
WHEN rc.tyshxydm is not null THEN
rc.tyshxydm
WHEN rc.nsrsbh is not null THEN
rc.nsrsbh
else
rc.jsjdm
end,
/* CONCAT(CASE
WHEN rc.tyshxydm is not null THEN
substr(rc.tyshxydm, 0, 7)
WHEN rc.nsrsbh is not null THEN
substr(rc.nsrsbh, 0, 7)
else
substr(rc.jsjdm, 0, 7)
end,
TRUNC(DBMS_RANDOM.VALUE(10000000, 99999999))),*/
md5('1'),
rc.qymc,
'1');
---员工 企业关系
insert into GH_JC_YGBMGX
(YGBMGXDM, YGDM, QYGHDM, YGLX, BZ)
values
(CONCAT('Y', seq_ygdm.Currval),
CONCAT('Y', seq_ygdm.Currval),
rc.qybm,
'0',
rc.qymc);
--员工 菜单
insert into GH_JC_YGJSGX
(YGJSGXDM, YGDM, JSDM)
values
(CONCAT('J', seq_ygdm.Currval),
CONCAT('Y', seq_ygdm.Currval),
'3');
------插入核实表中数据
insert into GH_HS
(YWLSDM,
QYBM,
HZLX,
LXR,
LXDH,
KHH,
KHZH,
KHMC,
CJHSZT,
JHZTDM,
QYGHMC,
QYGHRS,
SJJYDZ,
JHSJ,
SHSJ,
CZRYDM,
CZSJ,
ZGGHDM,
ZHLX,
QYMC,
SFZC,
JFLCBL,
BL1,
BL2,
BL3,
BL4,
BL5,
BL6,
ISFIRST,
XEZFH)
values
(SEQ_YWLS.NEXTVAL,
rc.QYBM,
'0',
rc.LXR,
rc.LXDH,
rc.KHH,
rc.KHZH,
rc.KHMC,
'0',
rc.JHZT,
rc.GHMC,
rc.QYGHRS,
rc.SJJYDZ,
rc.JHSJ,
rc.SHSJ,
'预处理',
sysdate,
CASE WHEN rc.QYJC = '2' THEN rc.GHDM1 WHEN rc.QYJC = '3' THEN
rc.GHDM2 WHEN rc.QYJC = '4' THEN rc.GHDM3 WHEN
rc.QYJC = '5' THEN rc.GHDM4 WHEN rc.QYJC = '6' THEN
rc.GHDM5 else rc.GHDM5 end,
rc.ZHLX,
rc.QYMC,
'0',
rc.JFLCBL,
rc.BL1,
rc.BL2,
rc.BL3,
rc.BL4,
rc.BL5,
rc.BL6,
'1',
rc.XEZFH);
end loop;
insert into YUCHULI_MARK
values
(SYS_GUID(), '--员工核实', sysdate);
commit;
----更新地税企业信息SKSSY
update gh_dsgsgz tt
set tt.skssy = substr(tt.skssy, 2, 1)
WHERE tt.skssy like '0%';
----地税个税加入企业编码
update gh_dsgsgz t
set t.qybm =
(select m.qybm
from gh_qyxx m
where m.tyshxydm = t.shxydm
and rownum = 1)
where t.shxydm is not null;
update gh_dsgsgz t
set t.qybm =
(select m.qybm
from gh_qyxx m
where m.nsrsbh = t.nsrsbh
and rownum = 1)
where t.shxydm is null;
-------g.新增地税个税数据
----增量地税信息
insert into gh_qygsgz
(GZDM, -- 工资代码
QYBM, -- 企业编码
SKSSN, --税款所属年
SKSSY, -- 税款所属月
ZGRS, --职工人数
GZZE ---职工工资总额
)
select CONCAT('D', SEQ_GSGZDM.nextval) as gzdm,
dsgsgz.qybm,
dsgsgz.skssn,
dsgsgz.skssy,
dsgsgz.zgrs,
dsgsgz.gzze
from gh_dsgsgz dsgsgz
where not exists (select *
from gh_qygsgz
where qybm = dsgsgz.qybm
and skssn = dsgsgz.skssn
and skssy = dsgsgz.skssy);
insert into YUCHULI_MARK
values
(SYS_GUID(), '增量地税信息', sysdate);
commit;
end;
end yuchuli_insert;