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;
