1 alter procedure [LC0019999].[pro_zlzjzxzb]
2
3 @dw varchar(88),
4 @ksrq varchar(8),
5 @jsrq varchar(8)
6
7 as
8 begin
9 create table #T_01
10 (
11 F_01 varchar(8), /*单位编号*/
12 F_02 varchar(8), /*单据类型*/
13 F_03 varchar(60), /*账户编号*/
14 F_04 varchar(8), /*账户类型*/
15 F_05 varchar(8), /*账户组*/
16 F_06 varchar(60), /*对方账户*/
17 F_0601 varchar(8), /*对方单位*/
18 F_07 DECIMAL(18,4), /*增加额*/
19 F_08 DECIMAL(18,4), /*减少额*/
20 F_09 DECIMAL(18,4), /*调拨额*/
21
22 F_0101 varchar(80), /*单位名称*/
23 F_10 DECIMAL(18,4), /*上周账户余额*/
24 F_11 DECIMAL(18,4), /*内部户*/
25 F_12 DECIMAL(18,4), /*归集行*/
26 F_13 DECIMAL(18,4), /*其他账户*/
27 F_14 DECIMAL(18,4), /*合计*/
28 F_15 VARCHAR(4), /*货币编号*/
29 F_16 VARCHAR(8), /*货币名称*/
30 F_17 DECIMAL(18,4),/*汇率*/
31
32
33 F_20 varchar(8)
34 )
35 (SELECT LSBZDW.LSBZDW_DWBH hsdw into #hsdw FROM LSBZDW LSBZDW WHERE
36 ( LSBZDW.LSBZDW_DWBH IN (SELECT DISTINCT AO_DATA FROM GSPAURESULT WHERE BIZOBJID='GlobalId' AND BIZOPID='GlobalId' AND AO_ID='GSP_SCF_LSBZDW' AND
37 OWNERID=@dw) ) )
38
39 INSERT INTO #T_01
40 select
41 zjzh_dwbh F_01,'' F_02,zjzh_zhbh F_03,zjzh_lxbh F_04,zjzh_zhz F_05,'' F_06,'' F_0601,0 F_07,0 F_08,0 F_09,lsbzdw_dwmc F_0101,
42 0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'000' F_20
43 from zjzh
44 left join lsbzdw on zjzh_dwbh=lsbzdw_dwbh
45 left join zjzhxz ON zjzh_XZBH=ZJZHXZ_XZBH
46 where ZJZH_DWBH in (select hsdw from #hsdw )
47
48 INSERT INTO #T_01
49 select
50 zjzh_DWBH F_01,rq F_02,zjzh_zhbh F_03,zjzh_nwbz F_04,zjzh_zhz F_05,zjzh_lxbh F_06,'' F_0601,0 F_07,0 F_08,dqye F_09,lsbzdw_dwmc F_0101,
51 0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,bzbh F_15,'' F_16,0 F_17,'YE00' F_20
52 from(
53 select zjyhzhyeb_rq rq,zjyhzhyeb_zhnm,zjzh_zhbh,zjzh_mc,zjzh_nwbz,zjzh_XZBH,ZJZHXZ_XZMC,zjzh_DWBH,LSBZDW_DWMC,zjzh_lxbh,zjzh_zhz,
54 zjzhz_mc,zjyhzhyeb_bz bzbh,lswbzd_bzmc,zjyhzhyeb_dqye dqye
55 from zjzh
56 inner join zjyhzhyeb on zjzh_zhnm=zjyhzhyeb_zhnm
57 inner join LSBZDW ON zjzh_DWBH=LSBZDW_DWBH
58 inner join zjzhxz ON zjzh_XZBH=ZJZHXZ_XZBH
59 inner join zjzhz on zjzhz_zhzbh=zjzh_zhz
60 inner join lswbzd on lswbzd_bzbh=zjyhzhyeb_bz
61 where 1=1
62 and zjyhzhyeb_rq between dateadd(d,-1,@ksrq) and @jsrq
63 and ZJZH_DWBH in (select hsdw from #hsdw )
64 union all
65 select zjzhryeb_rq rq,zjzhryeb_zhnm,zjzh_zhbh,zjzh_mc,zjzh_nwbz,zjzh_XZBH,ZJZHXZ_XZMC,zjzh_DWBH,LSBZDW_DWMC,zjzh_lxbh,zjzh_zhz,
66 zjzhz_mc,zjzhryeb_bzbh bzbh,lswbzd_bzmc,abs(zjzhryeb_qcye) dqye
67 from zjzh
68 inner join zjzhryeb on zjzh_zhnm=zjzhryeb_zhnm and zjzh_nwbz='0'
69 inner join LSBZDW ON zjzh_DWBH=LSBZDW_DWBH
70 inner join zjzhxz ON zjzh_XZBH=ZJZHXZ_XZBH
71 inner join lswbzd on lswbzd_bzbh=zjzhryeb_bzbh
72 inner join zjzhz on zjzhz_zhzbh=zjzh_zhz
73 where 1=1
74 and zjzhryeb_rq between dateadd(d,-1,@ksrq) and @jsrq
75 and ZJZH_DWBH in (select hsdw from #hsdw )
76 ) yeb
77
78
79 INSERT INTO #T_01
80 SELECT
81 zh.zjzh_dwbh F1,zjjsdj_djlx F2,zh.ZJZH_ZHBH F3,zh.zjzh_lxbh F4,zh.zjzh_zhz F5,ZJJSDJ_DFZH F6,df.zjzh_dwbh F61,
82 isnull(SUM(CASE WHEN ZJJSDJ_DJLX IN ('01','06','07','11','15') THEN ZJJSDJ_JE END),0) F7,
83 isnull(SUM(CASE WHEN ZJJSDJ_DJLX IN ('02','05','09','12','15') THEN ZJJSDJ_JE END),0) F8,
84 isnull(SUM(CASE WHEN ZJJSDJ_DJLX IN ('15') THEN ZJJSDJ_JE END),0) F9,
85 LSBZDW_DWmc F_0101,0 F_10,0 F11,0 F_12,0 F_13,0 F_14,zjjsdj_yhbz F_15,'' F_16,1 F_17,
86 '0' F20
87 FROM ZJZH zh
88 LEFT JOIN ZJJSDJ ON ZJZH_ZHNM=ZJJSDJ_YHZH OR ZJZH_ZHNM=ZJJSDJ_NBZH
89 left join zjzh DF on df.zjzh_zhbh=zjjsdj.zjjsdj_dfzh
90 left join LSBZDW ON zh.zjzh_DWBH=LSBZDW_DWBH
91 left join zjzhxz ON zh.zjzh_XZBH=ZJZHXZ_XZBH
92 WHERE 1=1
93 and ZJJSDJ_DJZT='11'
94 AND zjjsdj_ywrq>=@ksrq
95 and zjjsdj_ywrq<=@jsrq
96 and zh.ZJZH_DWBH in (select hsdw from #hsdw )
97 GROUP BY zh.zjzh_dwbh,zjjsdj_djlx,zh.ZJZH_ZHBH,ZJJSDJ_DFZH,zh.zjzh_lxbh,zh.zjzh_zhz,df.zjzh_dwbh,LSBZDW_DWmc,zjjsdj_yhbz,zh.zjzh_XZBH,ZJZHXZ_XZMC
98
99 /*---汇率------------------------------------------------------*/
100 insert into #T_01
101 select
102 '' F_01,'' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,0 F_07,0 F_08,0 F_09,'' F_0101,
103 0 F_10,0 F_11,0 F_12,0 F_13,isnull(hl.lshl_hl,1) F_14,LSWBZD_BZbh F_15,LSWBZD_BZMC F_16,isnull(hl.lshl_qmhl,1) F_17,'HL00' F_20
104 from LSWBZD zd
105 left join LSHLLX2015 lx on zd.LSWBZD_BZbh= lx.lshllx_ybz
106 left join LSHL2015 hl on lx.LSHLLX_BH=hl.lshl_bh
107 WHERE LSHL_QJXH='08' or zd.LSWBZD_BZbh='01'
108
109 update #T_01 set F_14=HL.F_14,F_16=HL.F_16,F_17=HL.F_17
110 from #T_01 A,(select * from #T_01 B where B.F_20='HL00') HL where A.F_20 in ('0','YE00') and A.F_15=HL.F_15
111
112 --select top 1 * from #T_01 where F_20 in ('0','YE00') and F_15<>'01' and F_01='huaqi' and F_03='3602000109200173293'
113 update #T_01 set F_07=F_07*F_17,F_08=F_08*F_17,F_09=F_09*F_17 where F_20 in ('0','YE00')
114 --select top 1 * from #T_01 where F_20 in ('0','YE00') and F_15<>'01' and F_01='huaqi' and F_03='3602000109200173293'
115
116 /*---上周账户余额-----*/
117 insert into #T_01
118 select F_01,'','','','','','',0,0,sum(F_09),F_0101,sum(F_09) F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'YE01' F_20
119 from #T_01 where F_02=dateadd(d,-1,'20150810') and F_20='YE00' and F_01<>'jszx' group by F_01,F_0101
120 /*---内部户-----*/
121 insert into #T_01
122 select F_01,'','','','','','',0,0,sum(F_09),F_0101,0 F_10,sum(F_09) F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'YE02' F_20
123 from #T_01 where F_02='20150816' and F_04='0' and F_20='YE00' and F_01<>'jszx' group by F_01,F_0101
124 /*---归集行-----*/
125 insert into #T_01
126 select F_01,'','','','','','',0,0,sum(F_09),F_0101,0 F_10,0 F_11,sum(F_09) F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'YE03' F_20
127 from #T_01 where F_02='20150816' and F_05 in ('01','02','06','07') and F_20='YE00' and F_01<>'jszx' group by F_01,F_0101
128 /*---其他户-----*/
129 insert into #T_01
130 select F_01,'','','','','','',0,0,sum(F_09),F_0101,0 F_10,0 F_11,0 F_12,sum(F_09) F_13,0 F_14,'' F_15,'' F_16,0 F_17,'YE04' F_20
131 from #T_01 where F_02='20150816' and F_05='04' and F_20='YE00' and F_01<>'jszx' group by F_01,F_0101
132
133
134
135 /*---1. +调拨-----------------------------------*/
136 insert into #T_01
137 select F_01,'','','','','','',sum(F_07),sum(F_08),sum(F_09),F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'1' F_20
138 from #T_01 where F_20='0' and F_01<>'jszx' group by F_01,F_0101
139 union all
140 select F_01,'','','','','','',sum(F_07),sum(F_08),sum(F_09),F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'20' F_20
141 from #T_01 where F_20='0' and F_04='08' and F_05='03' and F_01<>'jszx' group by F_01,F_0101 /**/
142
143 /*---2.本周增+减---------------------*/
144 insert into #T_01
145 select F_01,'','','','','','',-abs(sum(F_07+F_08)),-abs(sum(F_07+F_08)),0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'2' F_20
146 from #T_01 where F_20='20' and F_01<>'jszx' group by F_01,F_0101
147
148 /*---3.本企业内部银行账户转账-----------------*/
149 insert into #T_01
150 select F_01,'','','','','','',sum(F_07),0,0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'31' F_20
151 from #T_01 where F_02 in ('01','11','15') and F_0601=F_01 and F_20='0' and F_01<>'jszx' group by F_01,F_0101
152 union all
153 select F_01,'','','','','','',0,sum(F_08),0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'31' F_20
154 from #T_01 where F_02 in ('02','12','15') and F_0601=F_01 and F_20='0' and F_01<>'jszx' group by F_01,F_0101
155 insert into #T_01
156 select F_01,'','','','','','',-abs(sum(F_07)),-abs(sum(F_08)),0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'3' F_20
157 from #T_01 where F_20='31' group by F_01,F_0101
158
159 /*---4.汇总 4---------------------------------*/
160 insert into #T_01
161 select F_01,'','','','','','',sum(F_07),sum(F_08),0,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'4' F_20
162 from #T_01 where F_20 in ('1','2','3') group by F_01,F_0101
163
164 /*---5.汇总 增加、减少额、上周余额、内部... 5---------------------------------*/
165 insert into #T_01
166 select F_01,'','','','','','',sum(F_07),sum(F_08),0,F_0101,sum(F_10),sum(F_11),SUM(F_12),sum(F_13),Sum(F_14),'' F_15,'' F_16,0 F_17,'5' F_20
167 from #T_01 where F_20 in ('4','YE01','YE02','YE03','YE04') group by F_01,F_0101
168
169
170 /*==========JSZX=====================*/
171
172 INSERT INTO #T_01
173 select F_01,F_02,F_03,F_04,F_05,F_06,F_0601,F_07,F_08,F_09,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZXYE' F_20
174 from #T_01 where F_01 ='jszx' and F_06='03' and F_20 in ('YE00')
175 UNION ALL
176 select F_01,F_02,F_03,F_04,F_05,F_06,F_0601,F_07,F_08,-ABS(F_09),F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZXYE' F_20
177 from #T_01 where F_01 <>'jszx' and F_06='08' and F_05='03' and F_20 in ('YE00')
178
179 /*---上周账户余额-----*/
180 INSERT INTO #T_01
181 select F_01,'','','','','','',0,0,0, F_0101,sum(F_09) F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZX01' F_20
182 from #T_01 where F_02=dateadd(d,-1,'20150810') and F_20='JSZXYE' group by F_01,F_0101
183
184
185 /*---内部户-----*/
186 INSERT INTO #T_01
187 select F_01,'','','','','','',0,0,0, F_0101,0 F_10,sum(F_09) F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZX02' F_20
188 from #T_01 where F_02='20150816' AND F_04='0' and F_20='JSZXYE' group by F_01,F_0101
189
190 /*---归集行-----没数据*/
191 INSERT INTO #T_01
192 select F_01,'','','','','','',0,0,0, F_0101,0 F_10,0 F_11,sum(F_09) F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZX03' F_20
193 from #T_01 where F_02='20150816' and F_05 in ('01','02','06','07') and F_20='JSZXYE' group by F_01,F_0101
194
195 /*---其他户-----*/
196 insert into #T_01
197 select F_01,'','','','','','',0,0,0, F_0101,0 F_10,0 F_11,0 F_12,sum(F_09) F_13,0 F_14,'' F_15,'' F_16,0 F_17,'JSZX04' F_20
198 from #T_01 where F_02='20150816' and F_05='04' and F_20='JSZXYE' group by F_01,F_0101
199
200 insert into #T_01
201 SELECT '','','','','','','',0,0,0,'' F_0101,sum(F_10),sum(F_11),sum(F_12),sum(F_13),0 F_14,'' F_15,'' F_16,0 F_17,'JSZX05' F_20
202 FROM #T_01 WHERE F_20 IN ('JSZX01','JSZX02','jszx03','jszx04') group by F_01,F_0101
203
204 /*---1.本周增减额 -----*/
205 insert into #T_01
206 select F_01,F_02,F_03,F_04,F_05,F_06,F_0601,F_07,F_08,F_09,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'6' F_20
207 from #T_01 where F_01 ='jszx' and F_04='03' and F_20 in ('0')
208 union all
209 select F_01,F_02,F_03,F_04,F_05,F_06,F_0601,-ABS(F_07),-ABS(F_08),F_09,F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'6' F_20
210 from #T_01 where F_01<>'jszx' and F_04='08' and F_05='03' and F_20 in ('0')
211
212 /*---2.汇总账户金额--------------------*/
213 insert into #T_01
214 select '','',F_03,'','','','',sum(F_07),sum(F_08),0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'7' F_20
215 from #T_01 where F_20='6' GROUP BY F_03
216
217 /*---3.本企业内部银行账户转账-------------*/
218 INSERT INTO #T_01
219 select '','','','','','','',sum(F_07),0,0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'30' F_20
220 from #T_01 where F_02 in ('01','11','15') and F_0601='JSZX' and F_20='6'
221 union all
222 select '','','','','','','',0,sum(F_08),0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'30' F_20
223 from #T_01 where F_02 in ('02','12','15') and F_0601='JSZX' and F_20='6'
224 INSERT INTO #T_01
225 select F_01,'','','','','','',-abs(sum(F_07)),-abs(sum(F_08)),0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'8' F_20
226 from #T_01 where F_20='30' group by F_01
227 delete #T_01 where F_20='30'
228
229 /*---4.JSZX 9 -----------------------------*/
230 insert into #T_01
231 select '','','','','','','',sum(F_07),sum(F_08),0,'' F_0101,0 F_10,0 F_11,0 F_12,0 F_13,0 F_14,'' F_15,'' F_16,0 F_17,'9' F_20
232 from #T_01 where F_20 IN ('7','8')
233
234 /*---5.JSZX 9 -----------------------------*/
235 insert into #T_01
236 select 'JSZX','','','','','','',sum(F_07),sum(F_08),0, '广东省旅游控股集团有限公司资金结算中心' F_0101,sum(F_10),sum(F_11),SUM(F_12),sum(F_13),Sum(F_14),'' F_15,'' F_16,0 F_17,'jszx00' F_20
237 from #T_01 where F_20 in ('9','jszx05')
238
239
240 /*--- -----------------------------*/
241 update #T_01 set F_02=zjzh_xzBH from #T_01 left join zjzh on F_01=zjzh_dwbh where F_20 in ('5','jszx00')
242 update #T_01 set F_03=ZJZHXZ_XZmc from #T_01 left join zjzhxz ON F_02=ZJZHXZ_XZBH where F_20 in ('5','jszx00')
243
244
245 insert into #T_01
246 select '▉小计' F_01,F_02+'01' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,sum(F_07),sum(F_08),sum(F_09),F_03,sum(F_10),sum(F_11),sum(F_12),sum(F_13),sum (F_14), '' F_15,'' F_16,0 F_17,'jszxxj' F_20
247 from #T_01 where F_20 in ('5','jszx00') group by F_02,F_03
248
249 insert into #T_01
250 select '' F_01,'9000' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,sum(F_07),sum(F_08),sum(F_09),'▉合计',sum(F_10),sum(F_11),sum(F_12),sum(F_13),sum (F_14), '' F_15,'' F_16,0 F_17,'jszxhj' F_20
251 from #T_01 where F_20 in ('5','jszxxj')
252
253 insert into #T_01
254 SELECT '' F_01,'9010' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,-abs(sum(zjjsdj_je)) F_07,0 F_08,0 F_09,'▉减：内部单位往来' F_0101,0 F_10,0 F_11,
255 0 F_12,0 F_13,0 F_14, '' F_15,'' F_16,0 F_17,'nbwl' F_20
256 FROM ZJZH zh
257 LEFT JOIN ZJJSDJ ON ZJZH_ZHNM=ZJJSDJ_YHZH OR ZJZH_ZHNM=ZJJSDJ_NBZH
258 WHERE 1=1
259 and ZJJSDJ_DJZT='11'
260 AND zjjsdj_ywrq>=@ksrq
261 and zjjsdj_ywrq<=@jsrq
262 and zh.ZJZH_DWBH in (select hsdw from #hsdw )
263 and zjjsdj_wldwid in (select hsdw from #hsdw )
264
265 insert into #T_01
266 SELECT
267 '' F_01,'9020' F_02,'' F_03,'' F_04,'' F_05,'' F_06,'' F_0601,sum(F_07) F_07,0 F_08,0 F_09,'▉合计' F_0101,0 F_10,0 F_11,
268 0 F_12,0 F_13,0 F_14, '' F_15,'' F_16,0 F_17,'nbwlhj' F_20
269 from #T_01 where F_20 in ('jszxhj','nbwl')
270
271 update #T_01 set F_14=F_10+F_07-F_08 where F_20 in ('jszx00','5','jszxxj','jszxhj','nbwl','nbwlhj')
272
273 select F_01,F_0101,F_10,F_07,F_08,F_11,F_12,F_13,F_14 from #T_01 where F_20 in ('jszx00','5','jszxxj','jszxhj','nbwl','nbwlhj') order by F_02
274
275
276
277 end
278
279
280 go
281 [pro_zlzjzxzb] '406fa89b-e1d2-4869-9380-2793b581aa0a','20150810','20150816'
