一个不简单的Procedure body例子

 1 create or replace package body CountBankData_20150617 is  2 type cursorCommon is ref cursor; --游标类型  3 strSQL varchar2(7000); --sql语句变量  4 strTemp varchar2(3000);  5 strTmp varchar2(3000);  6  7 -- Author : ADMINISTRATOR  8 -- Created : 2005-3-3 8:50:34  9 -- Purpose :  10  11 -- Public type declarations  12 --type <TypeName> is <Datatype>;  13  14 -- Public constant declarations  15 --<ConstantName> constant <Datatype> := <Value>;  16  17 -- Public variable declarations  18 --<VariableName> <Datatype>;  19  20 -- Public function and procedure declarations  21 /*****************************************************************************--  22  --*******************************公共函数*************************************--  23  --*****************************************************************************/  24  25 /*-- 星期--*/  26  27 function getWeek(  28  strDate varchar2  29 )  30 return varchar2 is  31 sWeek varchar2(2);  32 begin  33 select decode(to_char(to_date(strDate,'yyyy-MM-dd')-1,'d'),1,'一',2,'二',3,'三',4,'四',5,'五',6,'六',7,'日')  34  into sWeek from dual;  35 return sWeek;  36 end ;  37 /*-- 合并日期--*/  38 function uniteDate(  39  gYear varchar2,  40  gMonth varchar2,  41  gDay varchar2  42 )  43 return varchar2 is  44 sDay varchar2(2);  45 sMonth varchar2(2);  46 strResult varchar2(10);  47 begin  48 if length(gMonth)=1 then  49 sMonth:='0'||gMonth;  50 else  51 sMonth:=gmonth;  52 end if;  53 if length(gDay)=1 then  54 sDay:='0'||gDay;  55 else  56 sDay:=gDay;  57 end if;  58 strResult:=gYear||'-'||sMonth||'-'||sDay;  59 return strResult;  60 end;  61  62 /*-- 取序号--*/  63  function getNextNumber(  64  gfieldName varchar2,  65  gLength number  66 )return varchar2 is  67  intCount number;  68  intafa007 number;  69 strResult varchar2(20);  70  begin  71 select Count(*) into intCount from fa02 where upper(afa006)=upper(gfieldName);  72 if intCount=0 then  73 strResult:=addZero('1',gLength);  74 insert into fa02 (afa006,afa007) values (upper(gfieldName),1);  75 else  76 select afa007 into intafa007 from fa02 where upper(afa006)=upper(gfieldName);  77 strResult:=addzero(TO_CHAR(intafa007+1),gLength);  78 update fa02 set afa007=afa007+1 where upper(afa006)=upper(gfieldName);  79 end if;  80 return strResult;  81  end;  82  83 --是否已经审核  84  function isAuditing(  85  gAAA010 Varchar2,  86  gBankOCode Varchar2,  87  gYear varchar2,  88  gMonth varchar2,  89  gDay varchar2  90 ) return boolean is  91  intCount number;  92  intn number;  93  begin  94 select count(*) into intn from DCJGZK where FYHDM=gBankOCode and FCJYEAR=gYear and FCJTERM=gMonth and FCJRI=gDay and aaa010=gAAA010;  95 if intn>0 then  96 select FIFCJ into intCount from DCJGZK where FYHDM=gBankOCode and FCJYEAR=gYear and FCJTERM=gMonth and FCJRI=gDay and aaa010=gAAA010;  97 if intCount=1 then  98 return true;  99 else 100 return false; 101 end if; 102 else 103 return false; 104 end if; 105  end; 106 107 --退回银行 108  procedure backbank( 109 gaaa010 AA11.AAA010%type, 110  gBankOCode varchar2, 111  gDate varchar2 112  ) is 113  begin 114 --先插入财政退回待查 再删待查表 115 delete from fc77 where AAA010=gaaa010 and AFC015=gDate and AFA101=gBankOCode; 116  INSERT INTO fc77 117  (AFC401,AAA010,AFC001,AFA031,AFA051,AFA101,AAA011,AFC002,AFC003,AFC004,AFC005,AFA052, 118  AFA053,AFC006,AFC007,AFC008,AFA032,AFC009,AFC010,AFC011,AFC012,AFC013,AFC014,AFC015,AFC016,FZPH,DCLX,AFC200,AFC201) 119  select AFC401,AAA010,AFC001,AFA031,AFA051,AFA101,AAA011,AFC002,AFC003,AFC004,AFC005,AFA052, 120  AFA053,AFC006,AFC007,AFC008,AFA032,AFC009,AFC010,AFC011,AFC012,AFC013,AFC187,AFC015,AFC016,FZPH,DCLX,AFC200,AFC201 121 from fc74 where AAA010=gAAA010 122 and AFC015=gDate 123 and AFA101=gBankOCode; 124 125 delete from fc74 where AAA010=gaaa010 and AFC015=gDate and AFA101=gBankOCode; 126 --将需要删除的bp01表中的数据备份到bp05中，再做删除操作先删除是为了防止重复备份 add at 2015年6月23日 15:25:56 127 delete from bp05 where (select f.aaa010 from bp05 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp05.abp205)=gaaa010 and abp101=gDate and abp203=gBankOCode; 128 insert into bp05(AFC501,ABP201,ABP202,ABP203,ABP204,ABP205,ABP206,ABP207,ABP208,ABP209,ABP101,ABP210,ABP211,ABP212,ABP213,ABP214) select AFC501,ABP201,ABP202,ABP203,ABP204,ABP205,ABP206,ABP207,ABP208,ABP209,ABP101,ABP210,ABP211,ABP212,ABP213,ABP214 from bp01 b where (select f.aaa010 from bp01 p,fpos02 f where p.abp205=f.aaa003 and p.abp205=b.abp205)=gaaa010 and b.abp101=gDate and b.abp203=gBankOCode; 129 delete from bp01 where (select f.aaa010 from bp01 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp01.abp205)=gaaa010 and abp101=gDate and abp203=gBankOCode; 130 131 --将需要删除的bp02表中的数据备份到bp06中，再做删除操作先删除是为了防止重复备份 add at 2015年6月23日 15:25:56 132 delete from bp06 where (select f.aaa010 from bp06 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp06.abp205)=gaaa010 and abp101=gDate and abp203=gBankOCode; 133 insert into bp06(AFC401,ABP201,ABP202,ABP203,ABP204,ABP205,ABP206,ABP207,ABP208,ABP209,ABP101,ABP210,ABP211,ABP212,ABP213,ABP214,ABP222,ABP223) select AFC401,ABP201,ABP202,ABP203,ABP204,ABP205,ABP206,ABP207,ABP208,ABP209,ABP101,ABP210,ABP211,ABP212,ABP213,ABP214,ABP222,ABP223 from bp02 b where (select f.aaa010 from bp02 p,fpos02 f where p.abp205=f.aaa003 and p.abp205=b.abp205)=gaaa010 and b.abp101=gDate and b.abp203=gBankOCode; 134 delete from bp02 where (select f.aaa010 from bp02 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp02.abp205)=gaaa010 and abp101=gDate and abp203=gBankOCode; 135 136 --将需要删除的bp04表中的数据备份到bp07中，再做删除操作先删除是为了防止重复备份 add at 2015年6月23日 15:25:56 137 delete from bp07 where (select f.aaa010 from bp07 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp07.abp205)=gaaa010 and abp101=gDate and abp203=gBankOCode; 138 insert into bp07(AFC401,AFC501,ABP201,ABP202,ABP203,ABP204,ABP205,ABP206,ABP207,ABP208,ABP209,ABP101,ABP210,ABP211,ABP212,ABP213,ABP214) select AFC401,AFC501,ABP201,ABP202,ABP203,ABP204,ABP205,ABP206,ABP207,ABP208,ABP209,ABP101,ABP210,ABP211,ABP212,ABP213,ABP214 from bp04 b where (select f.aaa010 from bp04 p,fpos02 f where p.abp205=f.aaa003 and p.abp205=b.abp205)=gaaa010 and b.abp101=gDate and b.abp203=gBankOCode; 139 delete from bp04 where (select f.aaa010 from bp04 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp04.abp205)=gaaa010 and abp101=gDate and abp203=gBankOCode; 140 141 delete from fc78 where AAA010=gaaa010 and afc015=gDate and AFA101=gBankOCode; 142  INSERT INTO fc78 143  (AFC306,AAA010,AFC041,AFA050,AFC061,AFC062,AFC063,AFC064,AFC065, 144  AFC066,AFA091,AFC067,AFC015,AFA101,AFC001,AAZ015) 145  select AFC306,AAA010,AFC041,AFA050,AFC061,AFC062,AFC063,AFC064,AFC065, 146 AFC066,AFA091,AFC067,AFC015,AFA101,AFC001,'0' 147 from fc75 where AAA010=gaaa010 148 and afc015=gDate and AFA101=gBankOCode; 149 150 delete from fc75 where AAA010= gaaa010 151 and afc015=gDate and AFA101=gBankOCode; 152 153 delete from fc79 where AAA010=gaaa010 and AFC015=gDate and AFC153=gBankOCode; 154  INSERT INTO fc79 155  (AFC001,AFA031,AFC163,AFC187,AFC183,AFC157,AFC181,AFA040,AFC180,AFA192,AFA051,AFA053, 156  AFC166,AFC155,AFC153,AFC154,AFA183,AFA184,AFA185,AFA032,AFA052,AFC015,AAA010,AFC200,AFC201,AFA260) 157  select AFC001,AFA031,AFC163,AFC187,AFC183,AFC157,AFC181,AFA040,AFC180,AFA192,AFA051,AFA053, 158  AFC166,AFC155,AFC153,AFC154,AFA183,AFA184,AFA185,AFA032,AFA052,AFC015,AAA010,AFC200,AFC201,AFA260 159  from fc76 160 where AAA010=gAAA010 and AFC015=gDate and afc153=gBankOCode; 161 162 delete from fc76 where AAA010=gaaa010 and AFC015=gDate and afc153=gBankOCode; 163 --删除单位间分成收入 add by zgz 20081210 164 delete from fc88 where AAA010=gaaa010 and afc015=gDate and afa101=gBankOcode and aaz100='1'; 165 delete from fc88 where AAA010=gaaa010 and afc015=gDate and afc201=gBankOCode and aaz100='2'; 166 -- delete from fc89 where fczqhnm=gaaa010 and fhkrq=gDate and fdsyhdm=gBankOCode; 167 --删除银行补录数据但未确认成功的 168 DELETE FROM fc84 WHERE AAA010=gaaa010 AND afc015=gDate AND afa101=gBankOcode AND fsfqr='0'; 169 delete from DCJGZK where aaa010=gaaa010 and fyhdm=gBankOCode and fcjyear=substr(gdate,1,4) and fcjterm=substr(gdate,6,2) and fcjri=substr(gdate,9,2) ; 170  end; 171 172 --统计银行 利息收入 173  procedure CountBankData( 174 gAAA010 AA11.AAA010%type, 175  gYear varchar2, 176  gMonth varchar2, 177  gDay varchar2, 178  gResult in out varchar2 179  ) is 180 sDate varchar2(10); 181 sWeek varchar2(2); 182 sBankOCode fa22.afa101%type; 183  szcsrbs number; 184 szcsrje fc76.afc157%type;--正常收入 185  sdcsrbs number; 186 sdcsrje fc76.afc157%type;--待查收入 187 szhlxsr fc76.afc157%type;--财政专户利息收入 188 sdchlxsr fc76.afc157%type;--待查户利息收入 189 sdccmje fc76.afc157%type;--待查查明收入 190  sdccmbs number; 191  stfsrbs number; 192 stfsrje fc76.afc157%type; 193  zhstfsrbs number; 194 zhstfsrje fc76.afc157%type; 195 sdwjfcfcsr dcjgzk.dwjfcfcsr%type; 196 sdwjfcfrsr dcjgzk.dwjfcfrsr%type; 197 198 syhye fc76.afc157%type; 199 sdatatype varchar(50); 200 syhbm varchar(5);--银行编码 201 syhmc varchar(200); 202 sjkshm varchar(20); 203 slsh varchar(50); 204 serr varchar(100); 205 sczqh varchar(10); 206 szje fc76.afc157%type; 207 sxmhjje fc76.afc157%type; 208 --add at 2015年6月18日 209 fzcsrposbs number;--正常收入POS笔数 210 fzcsrposje bp01.abp201%type;--正常收入POS金额 211 fdcsrposbs number;--待查收入POS笔数 212 fdcsrposje bp02.abp201%type;--待查收入POS金额 213 fdccmposbs number;--待查查明收入POS笔数 214 fdccmposje bp04.abp201%type;--待查查明收入POS金额 215 216  intFXH NUMBER; 217  csrCommon cursorCommon; 218  csrCheck cursorCommon; 219  begin 220 gResult:='0'; 221 sDate:= uniteDate(gYear,gMonth,gDay); 222 sWeek:=getWeek(sDate); 223 -- strSQL:='select * from fa22'; 224 strSQL:='select BankOCode,(zcsrbs+fzcsrposbs) zcsrbs,(nvl(zcsrje,0)+nvl(fzcsrposje,0)) zcsrje,fzcsrposbs,nvl(fzcsrposje,0) fzcsrposje,fdcsrposbs,nvl(fdcsrposje,0) fdcsrposje,fdccmposbs,nvl(fdccmposje,0) fdccmposje,(dcsrbs+fdcsrposbs) dcsrbs,(nvl(dcsrje, 0)+nvl(fdcsrposje, 0)) dcsrje,nvl(zhlxsr,0) zhlxsr,nvl(dchlxsr,0) dchlxsr,' 225 ||' tfsrbs,nvl(tfsrje,0) tfsrje,zhtfsrbs,nvl(zhtfsrje,0) zhtfsrje, (nvl(zcsrje, 0) + nvl(dcsrje, 0) + nvl(dccmje, 0) - nvl(tfsrje, 0)- nvl(dwjfcfcsr, 0) + nvl(dwjfcfrsr, 0)) yhye,' 226 ||'(nvl(dccmje,0)+nvl(fdccmposje, 0)) dccmje,(dccmbs+fdccmposbs) dccmbs,nvl(dwjfcfcsr,0) dwjfcfcsr,nvl(dwjfcfrsr,0) dwjfcfrsr from ' 227 ||' (select a.BankOCode,' 228 ||'(select count(*) from fc76 where AAA010= '''||gAAA010 229 ||''' and AFC015= '''||sDate||''' and AFC153=a.BankOCode) zcsrbs, ' 230 ||' (select zcsrje from (select AFC153 BankOCode,sum(nvl(AFC181,0)) zcsrje from fc76 ' 231 ||' where AFC015= '''||sDate||''' and AAA010= '''||gAAA010||''' group by AFC153) zc' 232 ||' where zc.BankOCode=a.BankOCode) zcsrje,' 233 --正常收入POS add at 2015年6月18日 234 ||'(select count(*) from bp01 where (select min(f.aaa010) from bp01 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp01.abp205)= '''||gAAA010 235 ||''' and abp101= '''||sDate||''' and abp202=a.BankOCode) fzcsrposbs, ' 236 ||' (select fzcsrposje from (select abp202 BankOCode,sum(nvl(abp201,0)) fzcsrposje from bp01 ' 237 ||' where abp101= '''||sDate||''' and (select min(f.aaa010) from bp01 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp01.abp205)= '''||gAAA010||''' group by abp202) zcp' 238 ||' where zcp.BankOCode=a.BankOCode) fzcsrposje,' 239 --待查收入POS 240 ||'(select count(*) from bp02 where (select min(f.aaa010) from bp02 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp02.abp205)= '''||gAAA010 241 ||''' and abp101= '''||sDate||''' and abp202=a.BankOCode) fdcsrposbs, ' 242 ||' (select fdcsrposje from (select abp202 BankOCode,sum(nvl(abp201 ,0)) fdcsrposje from bp02 ' 243 ||' where abp101 = '''||sDate||''' and (select min(f.aaa010) from bp02 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp02.abp205)= '''||gAAA010||''' group by abp202 ) dcp' 244 ||' where dcp.BankOCode=a.BankOCode) fdcsrposje,' 245 --待查查明收入POS 246 ||'(select count(*) from bp04 where (select min(f.aaa010) from bp04 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp04.abp205)= '''||gAAA010 247 ||''' and abp101= '''||sDate||''' and abp202=a.BankOCode) fdccmposbs, ' 248 ||' (select fdccmposje from (select abp202 BankOCode,sum(nvl(abp201 ,0)) fdccmposje from bp04 ' 249 ||' where abp101 = '''||sDate||''' and (select min(f.aaa010) from bp04 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp04.abp205)= '''||gAAA010||''' group by abp202 ) dccmp' 250 ||' where dccmp.BankOCode=a.BankOCode) fdccmposje,' 251 252 ||' (select count(*) from FC74 where AAA010= '''||gAAA010 253 ||''' and AFC015= '''||sDate||''' and AFA101=a.BankOCode and DCLX=''00'') dcsrbs, ' 254 ||' (select dcsrje from (select AFA101 BankOCode,sum(nvl(AFC011,0)) dcsrje from FC74 ' 255 ||' where AFC015= '''||sDate||''' and AAA010= '''||gAAA010||''' and dclx=''00'' group by AFA101) dc ' 256 ||' where dc.BankOCode=a.BankOCode ) dcsrje,' 257 ||' (select dcsrje from (select AFA101 BankOCode,sum(nvl(AFC011,0)) dcsrje from FC74 ' 258 ||' where AFC015= '''||sDate||''' and AAA010= '''||gAAA010||''' and dclx=''L1'' group by AFA101) dc ' 259 ||' where dc.BankOCode=a.BankOCode ) zhlxsr,' 260 ||' (select dcsrje from (select AFA101 BankOCode,sum(nvl(AFC011,0)) dcsrje from FC74 ' 261 ||' where AFC015= '''||sDate||''' and AAA010= '''||gAAA010||''' and dclx=''L2'' group by AFA101) dc ' 262 ||' where dc.BankOCode=a.BankOCode ) dchlxsr,' 263 ||' (select count(*) from fc75 where AAA010= '''||gAAA010 264 ||''' and afc015= '''||sDate||''' and AFA101=a.BankOCode) tfsrbs, ' 265 ||' (select tfje from (select AFA101 BankOCode,sum(nvl(AFC064,0)) tfje from FC75 ' 266 ||' where afc015= '''||sDate||''' and AAA010= '''||gAAA010||''' group by AFA101) tf ' 267 ||' where tf.BankOCode=a.BankOCode ) tfsrje, ' 268 269 ||' (select count(*) from fc75 where AAA010= '''||gAAA010 270 ||''' and afc015= '''||sDate||''' and AFA101=a.BankOCode and afc066=''0'') zhtfsrbs, ' 271 ||' (select tfje from (select AFA101 BankOCode,sum(nvl(AFC064,0)) tfje from FC75 ' 272 ||' where afc015= '''||sDate||''' and AAA010= '''||gAAA010||''' and afc066=''0'' group by AFA101) tf ' 273 ||' where tf.BankOCode=a.BankOCode ) zhtfsrje, ' 274 275 ||' (select count(*) from FC84 where AAA010='''||gAAA010 276 ||''' and AFC015='''||sDate||''' and AFA101=a.BankOCode) dccmbs,' 277 278 ||'(select dccmje from (select AFA101 BankOCode,sum(nvl(AFC011,0)) dccmje from FC84 ' 279 ||' where AFC015='''||sDate||''' and AAA010='''||gAAA010 280 ||''' group by AFA101) dccm where dccm.BankOCode=a.BankOCode ) dccmje, ' 281 282 ||' (select afc183 from (select afc201 BankOcode,sum(nvl(afc183, 0)) afc183 from fc88 ' 283 ||' where aaa010 ='''|| gAAA010 284 ||''' and afc015 ='''||sDate 285 ||''' and aaz100 = ''2''' 286 ||' group by Afc201) dwfc where dwfc.BankOCode = a.BankOCode) dwjfcfcsr, ' 287 288 ||' (select afc183 from (select afa101 BankOcode,sum(nvl(afc183, 0)) afc183 from fc88 ' 289 ||' where aaa010 ='''|| gAAA010 290 ||''' and afc015 ='''||sDate 291 ||''' and aaz100 = ''1''' 292 ||' group by Afa101) dwfc where dwfc.BankOCode = a.BankOCode) dwjfcfrsr ' 293 294 ||' from ' 295 296 ||' (select c.afa101 BankOCode from ' 297 ||' (select fyhdm BankOCode from DCJGZK where FCJYEAR=to_number('''||gYear||''') ' 298 ||' and FCJTERM=to_number('''||gMonth||''') ' 299 ||' and FCJRI=to_number('''||gDay||''') and FIFCJ=0 ' 300 ||' union ' 301 ||' select AFC153 BankOCode from fc76 where AAA010='''||gAAA010||''' and AFC015='''||sDate||''' group by AFC153 ' 302 ||' union ' 303 ||' select AFA101 BankOCode from fc74 where AAA010='''||gAAA010||''' and AFC015='''||sDate||''' group by AFA101 ' 304 ||' union ' 305 ||' select AFA101 BankOCode from fc84 where AAA010='''||gAAA010||''' and fsfqr=''0'' and AFC015='''||sDate||''' group by AFA101 ' 306 ||' union ' 307 --正常、待查、待查查明 add at 2015年6月19日<start-----> 308 ||' select abp202 BankOCode from bp01 where (select min(f.aaa010) from bp01 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp01.abp205)='''||gAAA010||''' and abp101 ='''||sDate||''' group by abp202 ' 309 ||' union ' 310 ||' select abp202 BankOCode from bp02 where (select min(f.aaa010) from bp02 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp02.abp205)='''||gAAA010||''' and abp101 ='''||sDate||''' group by abp202 ' 311 ||' union ' 312 ||' select abp202 BankOCode from bp04 where (select min(f.aaa010) from bp04 b,fpos02 f where b.abp205=f.aaa003 and b.abp205=bp04.abp205)='''||gAAA010||''' and abp101='''||sDate||''' group by abp202 ' 313 ||' union ' 314 --<end-----> 315 ||' select AFA101 BankOCode from fc75 where AAA010='''||gAAA010||''' and afc015='''||sDate||''' group by AFA101 ' 316 ||' union ' 317 ||' select afa101 BankOCode from fc88 where aaa010 = '''||gAAA010||''' and afc015 = '''||sDate||''' and aaz100 = ''1'' group by Afa101 ' 318 ||' union ' 319 ||' select afc201 BankOCode from fc88 where aaa010 = '''||gAAA010||''' and afc015 = '''||sDate||''' and aaz100 = ''2'' group by Afc201 ' 320 ||' ) b,' 321 ||' fa22 c where b.BankOCode=c.afa101 and c.aaa010='''||gAAA010||''' ) a) '; 322 323 open csrCommon for strSQL; 324  fetch csrCommon into sBankOCode,szcsrbs,szcsrje,fzcsrposbs,fzcsrposje,fdcsrposbs,fdcsrposje,fdccmposbs,fdccmposje,sdcsrbs,sdcsrje,szhlxsr,sdchlxsr,stfsrbs,stfsrje,zhstfsrbs,zhstfsrje,syhye,sdccmje,sdccmbs,sdwjfcfcsr,sdwjfcfrsr; 325 while csrCommon%FOUND Loop 326 327 -- 是否已接收审核则不统计 328 if not isAuditing(gAAA010,sBankOCode,gYear,gMonth,gDay) then 329 330 --删除DCJGZK（会计核算_采集跟踪库）中某家银行指定日期的采集记录 331 delete from DCJGZK where aaa010=gAAA010 and fyhdm=sBankOCode and FCJYEAR=To_number(gYear) and FCJTERM=To_number(gMonth) and FCJRI=To_number(gDay); 332 333 334 if not (szcsrbs=0 and fzcsrposbs=0 and fdcsrposbs=0 and fdccmposbs=0 and sdcsrbs=0 and stfsrbs=0 and sdccmbs=0 and szhlxsr=0 and sdchlxsr=0 and sdwjfcfcsr=0 and sdwjfcfrsr=0) then 335 336 337 --校验fc76表和fc74表，如有错误则将该银行数据退回，Continue继续处理下面数据 338 strTemp:='select datatype,yhbm,yhmc, '''||sDate||''' as afc015, jkshm,zje,xmhjje,lsh,err,'''||gAAA010||''' as czqh from (' 339 ||' select ''正常收入数据'' as dataType,a.yhbm,yhmc,a.jkshm,a.zje,a.xmhjje,'''' lsh,''总金额不等于项目合计金额'' err from' 340 ||' (select fa22.AFA101 yhbm,fa22.AFA102 yhmc,fc76.AFC001 jkshm,fc76.AFC157 zje,sum(fc76.AFC181) xmhjje' 341 ||' from fc76,fa22 where fc76.AFC153=fa22.AFA101 and fc76.AAA010='''||gAAA010||''' and fa22.AAA010='''||gAAA010||'''' 342 ||' and fc76.AFC015='''||sDate||''' and fc76.AFC153='''||sBankOCode||''' group by fa22.AFA101,fa22.AFA102,AFC001,AFC157) a' 343 ||' where a.ZJE<>a.XMHJJE ' 344 ||' union all ' 345 --bp01表《正常收入》中 总金额不等于项目合计金额的数据 346 ||' select ''正常收入数据'' as dataType,a.yhbm,yhmc,a.jkshm,a.zje,a.xmhjje,'''' lsh,''总金额不等于项目合计金额'' err from' 347 ||' (select fe20.afa051 yhbm,fe20.afa052 yhmc,fe20.afc001 jkshm,bp01.abp201 zje,fe20.afc157 xmhjje' 348 ||' from bp01,fe20 where bp01.afc501=fe20.afc501 and (select f.aaa010 from bp01 b,fpos02 f where b.abp205=f.aaa003 and b.abp205= bp01.abp205)='''||gAAA010||'''' 349 ||' and bp01.abp101='''||sDate||''' and bp01.abp203='''||sBankOCode||''' group by fe20.afa051,fe20.afa052,fe20.afc001,bp01.abp201,fe20.afc157) a' 350 ||' where a.ZJE<>a.XMHJJE ' 351 ||' union all ' 352 ||' select ''待查收入数据'' as dataType,fa22.AFA101 yhbm,fa22.AFA102 as yhmc,'''' as jkshm,0 as zje,0 as xmhjje,fc74.AFC401 lsh,''缺收款人全称项'' as err' 353 ||' from fc74,fa22 where fc74.AFA101=fa22.AFA101 and fc74.AAA010='''||gAAA010||''' and fa22.AAA010='''||gAAA010||''' and fc74.AFC015='''||sDate||''' and fc74.AFA101='''||sBankOCode||'''' 354 ||' and trim(fc74.AFC002) is null ' 355 ||' union all ' 356 ||' select ''待查收入数据'' as dataType,fa22.AFA101 yhbm,fa22.AFA102 as yhmc,'''' as jkshm,0 as zje,0 as xmhjje,fc74.AFC401 lsh,''缺收款人开户行项'' as err' 357 ||' from fc74,fa22' 358 ||' where fc74.AFA101=fa22.AFA101 and fc74.AAA010='''||gAAA010||''' and fa22.AAA010='''||gAAA010||''' and fc74.AFC015='''||sDate||''' and trim(fc74.AFC003) is null ' 359 ||' union all ' 360 ||' select ''待查收入数据'' as dataType,fa22.AFA101 yhbm,fa22.AFA102 as yhmc,'''' as jkshm,0 as zje,0 as xmhjje,fc74.AFC401 lsh,''缺收款人账号项'' as err' 361 ||' from fc74,fa22 ' 362 ||' where fc74.AFA101=fa22.AFA101 and fc74.AAA010='''||gAAA010||''' and fa22.AAA010='''||gAAA010||''' and fc74.AFC015='''||sDate||''' and trim(fc74.AFC004) is null ' 363 ||' union all ' 364 --缺票号项 bp02表中的票号均为空 365 ||' select ''待查收入数据'' as dataType,fe20.afa051 yhbm,fe20.afa052 as yhmc,'''' as jkshm,0 as zje,0 as xmhjje,fe20.afc150 lsh,''缺票号项'' as err ' 366 ||' from fe20 ' 367 ||' where fe20.afc015 ='''||sDate||''' ' 368 ||' union all ' 369 --收款金额不大于0 370 ||' select ''待查收入数据'' as dataType,fe20.afa051 yhbm,fe20.afa052 as yhmc,'''' as jkshm,0 as zje,0 as xmhjje,bp02.AFC401 lsh,''bp02收费金额不大于0'' err' 371 ||' from bp02,fe20 ' 372 ||' where bp02.abp203=fe20.afc153 and bp02.abp201<=0 and (select min(f.aaa010) from bp02 b,fpos02 f where b.abp205=f.aaa003 and b.abp205= bp02.abp205)='''||gAAA010||''' and bp02.abp101='''||sDate||''' and bp02.abp203='''||sBankOCode||''' ' 373 ||' union all ' 374 ||' select ''待查收入数据'' as dataType,fa22.AFA101 yhbm,fa22.AFA102 as yhmc,'''' as jkshm,0 as zje,0 as xmhjje,fc74.AFC401 lsh,''收费金额不大于0'' err' 375 ||' from fc74,fa22 ' 376 ||' where fc74.AFA101=fa22.AFA101 and fc74.AFC011<=0 and fc74.AAA010='''||gAAA010||''' and fa22.AAA010='''||gAAA010||''' and fc74.AFC015='''||sDate||''' and fc74.AFA101='''||sBankOCode||'''' 377 ||' ) order by dataType,yhbm,yhmc,jkshm,lsh '; 378 379 open csrCheck for strTemp; 380  fetch csrCheck into sdatatype,syhbm,syhmc,sdate,sjkshm,szje,sxmhjje,slsh,serr,sczqh; 381 if csrCheck%found then 382 strTmp:='delete from banktran where afc015='''||sdate||''' and yhbm ='''||syhbm||''' '; 383  execute immediate strTmp; 384 strTmp:='insert into banktran(datatype,yhbm,yhmc,afc015,jkshm,zje,xmhjje,lsh,err,aaa010) ' 385 ||strTemp; 386  execute immediate strTmp; 387 -- 退回银行 388  backbank(gAAA010,sBankOCode,sDate); 389 gResult:='1'; 390 else 391 392 --记跟踪采集数据 393 intFXH:=to_number(getnextnumber('FXH',20)); 394  insert into dcjgzk (FXH,FCJYEAR,FCJTERM,FCJRI,FIFCJ,FXQ,FZCSRJE,FZCSRBS,SZCSRPOSJE,SZCSRPOSBS,SDCSRPOSJE,SDCSRPOSBS,SDCCMPOSJE,SDCCMPOSBS, 395  FDCSRJE,FDCSRBS,FTFSRJE,FTFSRBS,FCZZZS,fdccmje,fdccmbs,fyhdm,aaa010,fzhlxsr,fdchlxsr,zhftfsrbs,zhftfsrje,dwjfcfcsr,dwjfcfrsr) values (intFXH,To_number(gYear),To_number(gMonth), 396 To_number(gDay),0,sWeek,szcsrje,szcsrbs,fzcsrposje,fzcsrposbs,fdcsrposje,fdcsrposbs,fdccmposje,fdccmposbs,sdcsrje,sdcsrbs,stfsrje,stfsrbs,syhye,sdccmje,sdccmbs,sBankOCode,gAAA010,szhlxsr,sdchlxsr,zhstfsrbs,zhstfsrje,sdwjfcfcsr,sdwjfcfrsr); 397 end if; 398  close csrCheck; 399 end if; 400 end if; 401  fetch csrCommon into sBankOCode,szcsrbs,szcsrje,fzcsrposje,fzcsrposbs,fdcsrposje,fdcsrposbs,fdccmposje,fdccmposbs,sdcsrbs,sdcsrje,szhlxsr,sdchlxsr,stfsrbs,stfsrje,zhstfsrbs,zhstfsrje,syhye,sdccmje,sdccmbs,sdwjfcfcsr,sdwjfcfrsr; 402 403  End Loop; 404  close csrCommon; 405  commit; 406 407 exception --异常处理 408  when others then 409 gResult:='统计银行数据出现错误，请稍后重试！'; 410  end; 411 end CountBankData_20150617;