时间：2021-07-01 10:21:17 帮助过：20人阅读
create or replace procedure yuchuli_delete is
begin
----是否满足剔除条件 1hours
declare
---1、地税状态（税务登记状态字段）：证件失效企业、停业企业、注销、非正常注销企业
cursor ds 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
left join GH_ZD z
on qyghgx.jhzt = z.zddm
where (t.nsrztmc = ‘证件失效‘ or t.nsrztmc like ‘%停业%‘ or
t.nsrztmc = ‘注销‘ or t.nsrztmc like ‘%非正常%‘)
and z.zdlx = ‘JHZT‘;
---2、是否是京外经营 28未建会-不应代收-京外经营
----2.1、所属街乡代码是否为空，不为空，则根据scjxdm来判断是否为京外经营
cursor scjx 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.ISTCCONDITION = ‘0‘
and substr(t.scjxdm, 0, 2) != ‘11‘;
---2.2、如果scjxdm是空的，则根据经营地址来判断是否为京外经营
cursor scjxnull 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.ISTCCONDITION = ‘0‘
and t.scjxdm is null;
--3、空壳单位
---3.1已经建会的单位
cursor jh is
select qy.*
from (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.ISTCCONDITION = ‘0‘
and qyghgx.jhzt in (‘0‘,
‘1‘,
‘2‘,
‘3‘,
‘4‘,
‘5‘,
‘6‘,
‘7‘,
‘8‘,
‘9‘,
‘10‘,
‘11‘,
‘12‘,
‘13‘,
‘14‘,
‘15‘,
‘16‘,
‘17‘)) qy
left join (select qybm,
sum(zgrs) / count(zgrs) as zgrs,
sum(gzze) / count(gzze) as gzze
from gh_qygsgz
where (skssy = to_number(to_char(add_months(to_date(‘2017/08/01‘,
‘yyyy/mm/dd‘),
-1),
‘mm‘)) and
skssn = to_number(to_char(add_months(to_date(‘2017/08/01‘,
‘yyyy/mm/dd‘),
-1),
‘yyyy‘)))
or (skssy = to_number(to_char(add_months(to_date(‘2017/08/01‘,
‘yyyy/mm/dd‘),
-2),
‘mm‘)) and
skssn = to_number(to_char(add_months(to_date(‘2017/08/01‘,
‘yyyy/mm/dd‘),
-2),
‘yyyy‘)))
or (skssy = to_number(to_char(add_months(to_date(‘2017/08/01‘,
‘yyyy/mm/dd‘),
-3),
‘mm‘)) and
skssn = to_number(to_char(add_months(to_date(‘2017/08/01‘,
‘yyyy/mm/dd‘),
-3),
‘yyyy‘)))
group by qybm) gsgz
on qy.qybm = gsgz.qybm
where gsgz.zgrs < 3
or gsgz.gzze = 0
or gsgz.qybm is null;
----3.2未建会的单位
cursor nojh is
select qy.*, gsgz.zgrs, gsgz.gzze
from (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.ISTCCONDITION = ‘0‘
and qyghgx.jhzt in (‘18‘,
‘19‘,
‘20‘,
‘21‘,
‘22‘,
‘23‘,
‘24‘,
‘25‘ ， ‘28‘)) qy
left join (select qybm, zgrs, gzze
from gh_qygsgz
where skssy = to_number(to_char(add_months(to_date(‘2017/08/01‘,
‘yyyy/mm/dd‘),
-1),
‘mm‘))
and skssn = to_number(to_char(add_months(to_date(‘2017/08/01‘,
‘yyyy/mm/dd‘),
-1),
‘yyyy‘))) gsgz
on qy.qybm = gsgz.qybm
where gsgz.zgrs < 3
or gsgz.gzze = 0
or gsgz.qybm is null;
begin
---1、地税状态（税务登记状态字段）：证件失效企业、停业企业、注销、非正常注销企业
---停业 6联合工会-不应代收-停业 15独立工会-不应代收-停业 23未建会-不应代收-停业
---注销 7联合工会-不应代收-注销 16独立工会-不应代收-注销 24未建会-不应代收-注销
update GH_QYXX SET ISTCCONDITION = ‘0‘;
for rc in ds loop
update GH_QYXX SET ISTCCONDITION = ‘1‘ where qybm = rc.qybm;
if (rc.nsrztmc = ‘证件失效‘ or rc.nsrztmc like ‘%停业%‘) then
if (rc.jhzt in
(‘0‘, ‘1‘, ‘2‘, ‘3‘, ‘4‘, ‘5‘, ‘6‘, ‘7‘, ‘8‘)) then
---联合工会
insert into GH_QYTC
(YWLSDM,
QYBM,
QYSQZT,
QYBQZT,
SFBL,
CZYYDM,
CZRYDM,
JHZT)
values
(SEQ_QYTC.NEXTVAL,
rc.qybm,
‘1‘,
‘1‘,
‘0‘,
rc.nsrztmc,
‘1‘,
rc.jhzt);
if (rc.jhzt != ‘6‘) then
---修改建会状态
update gh_qyghgx
set jhzt = ‘6‘
where qybm = rc.qybm;
-----增加变更历史表数据
insert into GH_QYBG_LS
(QYTCID,
ID,
YWLSDM,
QYBM,
BGXXZ,
BGXJZ,
SPZT,
CZRYDM,
CZSJ,
BGXDM,
GHDM,
GHMC,
GHLX,
ZHLX,
QYGHRS,
JHZT,
JHSJ,
SHSJ,
JFLCBL,
GHDM1,
BL1,
GHDM2,
BL2,
GHDM3,
BL3,
GHDM4,
BL4,
GHDM5,
BL5,
GHDM6,
BL6,
QYJC,
DRRQ,
QYMC,
SJJYDZ,
LXR,
LXDH,
KHH,
KHZH,
KHMC,
JSJDM,
TYSHXYDM,
DJZCLXDM,
ZZJGDM,
JYDZ,
JYDZYB,
ZCDZ,
ZCDZYB,
JYDZLXDM,
SWJGZZJGDM,
SCJXDM,
NSRZTMC,
GJBZHYDM,
KYDJRQ,
SWDJLXMC,
SJQYMC,
SJJYDZYB,
QYZT,
QYFR,
JHH,
XEZFH,
GSRS,
SSGLYDM,
ISTC,
NSRSBH,
XGRQ,
SFFQ)
values
(SEQ_QYTC.Currval,
SEQ_YWLS.NEXTVAL,
SEQ_YWLS.NEXTVAL,
rc.QYBM,
‘6‘,
rc.jhzt,
‘0‘,
‘预处理‘,
sysdate,
‘jhzt‘,
rc.GHDM,
rc.GHMC,
rc.GHLX,
rc.ZHLX,
rc.QYGHRS,
rc.JHZT,
rc.JHSJ,
rc.SHSJ,
rc.JFLCBL,
rc.GHDM1,
rc.BL1,
rc.GHDM2,
rc.BL2,
rc.GHDM3,
rc.BL3,
rc.GHDM4,
rc.BL4,
rc.GHDM5,
rc.BL5,
rc.GHDM6,
rc.BL6,
rc.QYJC,
&