--！！！执行脚本时，要确保没有人在做凭证相关的业务，最好停掉中间件，然后再执行脚本createtablegl_vouchermaxno_0409asselect*fromgl_vouchermaxno;createtablegl_vouchernosuppl_0409asselect*fromgl_vouchernosuppl;deletefromgl_vouchermaxno;--删除最大号表数据deletefromgl_vouchernosuppl;--删除补号表数据/*以下语句根据凭证表数据重新插入凭证最大号*/insertintogl_vouchermaxno(select0,max(num),a.period,a.pk_accountingbook,null,a.pk_group,max(a.pk_voucher),a.pk_vouchertype,max(ts),a.yearfromgl_voucherawherea.dr=0and(a.year||a.period>(selects.settledyear||s.settledperiodfromgl_syssettledswheres.pk_accountingbook=a.pk_accountingbook)or(notexists(selects.settledyear||s.settledperiodfromgl_syssettledswheres.pk_accountingbook=a.pk_accountingbookands.settledyearisnotnullands.settledperiodisnotnull)))groupbya.pk_accountingbook,a.pk_group,a.year,a.period,a.pk_vouchertype);/*创建一个序列，插补号表数据时用*/createsequencestttstartwith100000000000000;/*以下语句根据最大号表和凭证表数据查出空号，并将其插入到补号表*/DECLAREv_orgbookVARCHAR2(20);v_yearchar(4);v_periodchar(2);v_vouchertypechar(20);CURSORv_cursorISSELECTpk_accountingbook,year,period,pk_vouchertypeFROMgl_vouchermaxno;v_rowv_cursor%ROWTYPE;BEGINOPENv_cursor;LoopFETCHv_cursorINTOv_row;v_orgbook:=v_row.pk_accountingbook;v_year:=v_row.year;v_period:=v_row.period;v_vouchertype:=v_row.pk_vouchertype;INSERTINTOgl_vouchernosupplSELECT2,b.num,(SELECTpk_vouchermaxnoFROMgl_vouchermaxnoWHEREpk_accountingbook=v_orgbookANDYEAR=v_yearANDperiod=v_periodANDpk_vouchertype=v_vouchertype),substr(b.pk_accountingbook,16,20)||sttt.NEXTVAL,tsFROM(SELECTa.nASnum,ts,voucher.pk_voucher,a.pk_accountingbook,nosuppl.pk_vouchermaxnoFROM(SELECTROWNUMASn,tsASts,v_orgbookaspk_accountingbookFROMgl_voucherWHEREROWNUM<=(SELECTmaxnoFROMgl_vouchermaxnoWHEREpk_accountingbook=v_orgbookANDYEAR=v_yearANDperiod=v_periodANDpk_vouchertype=v_vouchertype)andgl_voucher.dr=0)aLEFTOUTERJOIN(SELECTpk_voucher,numFROMgl_voucherWHEREgl_voucher.pk_accountingbook=v_orgbookANDgl_voucher.YEAR=v_yearANDgl_voucher.period=v_periodANDgl_voucher.pk_vouchertype=v_vouchertypeANDgl_voucher.dr=0)voucherONvoucher.num=a.nLEFTOUTERJOIN(SELECTnum,pk_vouchermaxnoFROMgl_vouchernosupplWHEREgl_vouchernosuppl.pk_vouchermaxno=(SELECTpk_vouchermaxnoFROMgl_vouchermaxnoWHEREpk_accountingbook=v_orgbookANDYEAR=v_yearANDperiod=v_periodANDpk_vouchertype=v_vouchertype))nosupplONa.n=nosuppl.num)bWHEREb.pk_voucherISNULLANDpk_vouchermaxnoISNULL;EXITWHENv_cursor%NOTFOUND;endLoop;closev_cursor;end;/*删除序列*///dropsequencesttt;
