create or replace procedure yuchuli_recovery is
begin -------恢复企业 -----------不需要恢复央企、财政、全部退休人员的企业(建会状态 in 1 3 11 12 19 20 26) -----------不满足剔除条件 -----------不是个体工商户 declare cursor cur is select qy.*, tcls.jhzt as oldjhzt 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.djzclxdm ！ = '410' and t.istc = '1' and t.istccondition = '0' and qyghgx.jhzt not in ('1', '3', '11', '12', '19', '20', '26')) qy, (select * from GH_QYTC_LS t where czsj = (select max(czsj) from GH_QYTC_LS where qybm = t.qybm)) tcls where tcls.qybm = qy.qybm; begin insert into gh_qyhf_ls select * from gh_qyhf; --往期放到历史 delete from gh_qyhf; ---删除掉恢复数据 for rc in cur loop if (rc.jhzt = rc.oldjhzt) then insert into GH_QYHF (YWLSDM, QYBM, QYSQZT, QYBQZT, CZYYDM, CZRYDM, CZSJ) values (SEQ_QYHF.NEXTVAL, rc.qybm, '0', '1', '符合条件' ， '系统' ，sysdate); else insert into GH_QYHF (YWLSDM, QYBM, QYSQZT, QYBQZT, CZYYDM, CZRYDM, CZSJ) values (SEQ_QYHF.NEXTVAL, rc.qybm, '0', '1', '符合条件' ， '系统' ，sysdate); update gh_qyghgx set jhzt = rc.oldjhzt where qybm = rc.qybm; ----修改建会状态 -----增加到变更历史表中 insert into GH_QYBG_LS (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_YWLS.NEXTVAL, SEQ_YWLS.NEXTVAL, rc.QYBM, rc.oldjhzt, 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, rc.DRRQ, rc.QYMC, rc.SJJYDZ, rc.LXR, rc.LXDH, rc.KHH, rc.KHZH, rc.KHMC, rc.JSJDM, rc.TYSHXYDM, rc.DJZCLXDM, rc.ZZJGDM, rc.JYDZ, rc.JYDZYB, rc.ZCDZ, rc.ZCDZYB, rc.JYDZLXDM, rc.SWJGZZJGDM, rc.SCJXDM, rc.NSRZTMC, rc.GJBZHYDM, rc.KYDJRQ, rc.SWDJLXMC, rc.SJQYMC, rc.SJJYDZYB, rc.QYZT, rc.QYFR, rc.JHH, rc.XEZFH, rc.GSRS, rc.SSGLYDM, rc.ISTC, rc.NSRSBH, rc.XGRQ, rc.SFFQ); end if; end loop; ----把剔除条件更新 update gh_qyxx set istc = '1' where istccondition = '1' and istc = '0'; insert into YUCHULI_MARK values (SYS_GUID(), 'recover', sysdate); commit; end;end yuchuli_recovery;