《非正常户认定期间仍然开票户数（负向）.docx》由会员分享，可在线阅读，更多相关《非正常户认定期间仍然开票户数（负向）.docx（4页珍藏版）》请在第壹文秘上搜索。
1、非正常户认定期间仍然开票户数（负向）-非正常户认定期间仍然开票户数（负向）-创建临时表CREATETAB1.E1.S_TSZB_Z019_FPXX(XfnsrdzdahVARCHAR2(40),KPRQDATE,JENUMBER,FSDMVARCHAR2(100)zYXQQDATE,YXQZDATE)NO1.OGGING;CREATETAB1.E1.S_TSZB_Z019_NSR(Nsrdzdahvarchar2(40)notnull,YXQQDATE,YXQZDATE)NO1.OGGING;计算纳税人清单INSERT/*+append*/INTO1.S_TSZB_Z019_NSR(NSRDZ
2、DAH,YXQQ,YXQZ)selectm.nsrdzdah,m.YXQQ+n.lxtsYXQQ,m.YXQZfrom(SE1.ECTDISTINCTP.NSRDZDAHzTRNC(A.RDRQ)YXQQ,Trunc(Ajcfzchrq)-1yxqzFROMWD_NSRXXP,DJ_FZCHRDXXAwherep.Nsrdzdah=a.nsrdzdahANDP.NSRZT_IDIN(,03,04,z,05,06,13,)ANDP.KZZTDJ1.X_ID=TRUNC(fxqjzJYYYY,)分析期间止，参数为日期型，格式:FXQJZJDBCTYPE=TIMESTAMPANDtrunc(A.JC
3、FZCHRQ)=trunc(a.rdrq)UNIONA1.1.SE1.ECTRNSRDZDAH,TRNC(MAX(A.RDRQ)YXQQ,DATE,9999-12-31,YXQZFROMWD_NSRXXP,DJ_FZCHRDXXAwherep.Nsrdzdah=a.nsrdzdahandRNSRZTJDIN(,03,z,04,O5,06,z13)ANDP.KZZTDJ1.X_ID=trunc(sysdae+1)ANDTRUNC(A.RDRQ)=TRUNC(fxqjz)分析期间止，参数为日期型，格式：-FXJQZGROUPBYP.NSRDZDAH)minnerjoin(selectnn.nsrd
4、zdahznn.yxqq,nn.yxqz,casewhenCEI1.(nvl(nn.1.XKPSX,0)24)10then10elseCEI1.(nvl(nn.1.XKPSX,0)24)endasIxtsfrom(selecta.nsrdzdah,a.yxqq,a.yxqz,a.1.XKPSX,row-number()over(partitionbynsrdzdahorderbya.1.XKPSXdesc)pxfromFP_PZHDXXa,WD_FPZ1.bwherea.FPZ1._ID=b.FPZ1._IDandb.ZPPZ_IDnotin(,0,4z,8,)andYXQZYXQQANDtr
5、unc(YXQQ)=fxqjz)nnwherepx=l)nonm.nsrdzdah=n.nsrdzdah;commit;一计算发票数据INSERT/*+APPEND*/INTO1.S_TSZB_Z019_FPXX(XFNSRDZDAH,KPRQ,JE,FSDM,YXQQ,YXQZ)Selecttxfnsrdzdah,t.kprq,tje,t.fpdmt.fphm11t.kpyffsdm,nsr.yxqq,nsr.yxqzFROMDZDZ_DZDZ_FPXX_ZZSFPTINNERJOIN1.S_TSZB_Z019_NSRNSRont.xfnsrdzdah=nsr.nsrdzdahANDtrun
6、c(T.KPRQ)=trunc(NSR.YXQQ)ANDtrunc(T.KPRQ)=TO_CHAR(TRUNC(fxqjzz,YYYY,),YYYYMM,)-FXJQZANDT.KPYF=TO_CHAR(fxqjz,YYYYMM)-FXJQZANDT.FPZT-BZIN(,O,z,l,)and(TSPZ-DM,02,orTSPZ_DMisnull);COMMIT;INSERT/*+APPEND*/INTO1.S_TSZB_Z019_FPXX(XFNSRDZDAH,KPRQ,JE,FSDMzYXQQ,YXQZ)Selecttxfnsrdzdah,t.kprq,tjezt.fpdmt.fphm11
7、t.kpyffsdmznsr.yxqq,nsr.yxqzFROMDZDZ_DZDZ_FPXX_PTFPTInnerjoinls_tszb_zoi9_nsrnsront.xfnsrdzdah=nsr.nsrdzdahANDtrunc(T.KPRQ)=trunc(NSR.YXQQ)ANDtrunc(T.KPRQ)=TO_CHAR(TRUNC(fxqjz,YYYY),YYYYMM)-FXJQZANDT.KPYF=TO_CHAR(fxqjz,YYYYMM,)-FXJQZANDT.FPZT_BZIN(,0,z,1,)and(TSPZ-DM,02,orTSPZ_DMisnull);COMMIT;INSER
8、T/*+APPEND*/INTO1.S_TSZB_Z019_FPXX(XFNSRDZDAH,KPRQ,JE,FSDM,YXQQ,YXQZ)Selecttxfnsrdzdah,t.kprq,tje,t.fpdmt.fphm11t.kpyffsdm,nsr.yxqq,nsr.yxqzFROMDZDZ_DZDZ_FPXX_DZZPTInnerjoinls_tszb_zoi9_nsrnsrONT.XFNSRDZDAH=NSR.NSRDZDAHANDtrunc(T.KPRQ)=trunc(NSR.YXQQ)ANDtrunc(T.KPRQ)=TO_CHAR(TRUNC(fxqjz,YYYY,),YYYYM
9、M,)-FXJQZANDT.KPYF=TO_CHAR(fxqjz,YYYYMM)-FXJQZANDT.FPZT-BZIN(,O,z,l,)and(TSPZ-DM,02,orTSPZ_DMisnull);COMMIT;INSERT/*+APPEND*/INTO1.S_TSZB_Z019_FPXX(XFNSRDZDAH,KPRQ,JE,FSDMzYXQQ,YXQZ)Selecttxfnsrdzdah,t.kprq,tjezt.fpdmt.fphm11t.kpyffsdm,nsr.yxqq,nsr.yxqzFROMDZDZ_DZDZ_FPXX_DZFPTInnerjoinls_tszb_zoi9_n
10、srnsrONT.XFNSRDZDAH=NSR.NSRDZDAHANDtrunc(T.KPRQ)=trunc(NSR.YXQQ)ANDtrunc(T.KPRQ)=TO_CHAR(TRUNC(fxqjz,YYYY),YYYYMM)-FXJQZANDT.KPYF=TO_CHAR(fxqjz,YYYYMM,)-FXJQZANDT.FPZT_BZIN(0,z1,)and(TSPZ-DM,02,orTSPZ_DMisnull);COMMIT;结果数据SE1.ECTDISTINCTRNSRMCas纳税人名称，nvl(P.SHXYDMzP.NSRSBH)as社会信用代码，p.nsrdzdahas纳税人电子档
11、案号，RSWJGMCas主管税务机关，RSSG1.YSWRYMCas税收管理员，非正常户YS纳税人状态，TO-CHAR(y.YQQ,YYYY-MM-DD)as认定有效期起,TO_CHAR(y.YXQZ,1YYYY-MM-DD)as认定有效期止,TO_CHAR(fxqjz,YYYY,)as年份,TFXQJZFSas开票份数，JEas开票金额，TO,CHAR(fxqjzfYYYY-MM-DD,)as分析期间止，p.dsswjgmcas地市级税务机关，p.qxswjgmcas区县级税务机关from(Selectxfnsrdzdahnsrdzdah,YXQQ,YXQZ,SM(NV1.(JE,0)JE,COUNTfDISTINCTFSDM)FSFROM1.S_TSZB_Z019_FPXXFPXXGROUPBYXFNSRDZDAH,YXQQ,YXQZ)YWD_NSRXXPWHEREY.NSRDZDAH=P.NSRDZDAH
