fix quotations, RT#21103
[freeside.git] / bin / explain-ar-total.sql
1 EXPLAIN SELECT    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
2                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
3                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill   LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date >  ( EXTRACT( EPOCH FROM  now() ) - 2592000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
4                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
5                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
6                                                               + ( SELECT COALESCE(SUM(refund
7                                                               - COALESCE( 
8                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
9                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
10                                                                           ,0
11                                                                         )
12                                                               - COALESCE(
13                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
14                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
15                                                                           ,0
16                                                                         )
17                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
18                                                               - ( SELECT COALESCE(SUM(amount
19                                                                   - COALESCE(
20                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
21                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
22                                                                               ,0
23                                                                             )
24                                                                   - COALESCE(
25                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
26                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
27                                                                               ,0
28                                                                             )
29                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
30                                                               - ( SELECT COALESCE(SUM(paid
31                                                                   - COALESCE( 
32                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
33                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
34                                                                               ,0
35                                                                             )
36                                                                   - COALESCE(
37                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
38                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
39                                                                               ,0
40                                                                             )
41                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
42                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )   )
43                                                               + ( SELECT COALESCE(SUM(refund
44                                                               - COALESCE( 
45                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
46                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
47                                                                           ,0
48                                                                         )
49                                                               - COALESCE(
50                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
51                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
52                                                                           ,0
53                                                                         )
54                                                             ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date >  ( EXTRACT( EPOCH FROM  now() ) - 2592000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
55                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
56                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
57                                                               + ( SELECT COALESCE(SUM(refund
58                                                               - COALESCE( 
59                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
60                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
61                                                                           ,0
62                                                                         )
63                                                               - COALESCE(
64                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
65                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
66                                                                           ,0
67                                                                         )
68                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
69                                                               - ( SELECT COALESCE(SUM(amount
70                                                                   - COALESCE(
71                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
72                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
73                                                                               ,0
74                                                                             )
75                                                                   - COALESCE(
76                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
77                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
78                                                                               ,0
79                                                                             )
80                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
81                                                               - ( SELECT COALESCE(SUM(paid
82                                                                   - COALESCE( 
83                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
84                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
85                                                                               ,0
86                                                                             )
87                                                                   - COALESCE(
88                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
89                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
90                                                                               ,0
91                                                                             )
92                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
93                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
94                                                               - ( SELECT COALESCE(SUM(amount
95                                                                   - COALESCE(
96                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
97                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
98                                                                               ,0
99                                                                             )
100                                                                   - COALESCE(
101                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
102                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
103                                                                               ,0
104                                                                             )
105                                                             ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date >  ( EXTRACT( EPOCH FROM  now() ) - 2592000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
106                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
107                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
108                                                               + ( SELECT COALESCE(SUM(refund
109                                                               - COALESCE( 
110                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
111                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
112                                                                           ,0
113                                                                         )
114                                                               - COALESCE(
115                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
116                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
117                                                                           ,0
118                                                                         )
119                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
120                                                               - ( SELECT COALESCE(SUM(amount
121                                                                   - COALESCE(
122                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
123                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
124                                                                               ,0
125                                                                             )
126                                                                   - COALESCE(
127                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
128                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
129                                                                               ,0
130                                                                             )
131                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
132                                                               - ( SELECT COALESCE(SUM(paid
133                                                                   - COALESCE( 
134                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
135                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
136                                                                               ,0
137                                                                             )
138                                                                   - COALESCE(
139                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
140                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
141                                                                               ,0
142                                                                             )
143                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
144                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
145                                                               - ( SELECT COALESCE(SUM(paid
146                                                                   - COALESCE( 
147                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
148                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
149                                                                               ,0
150                                                                             )
151                                                                   - COALESCE(
152                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
153                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
154                                                                               ,0
155                                                                             )
156                                                             ),    0) FROM cust_pay    LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date >  ( EXTRACT( EPOCH FROM  now() ) - 2592000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
157                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
158                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
159                                                               + ( SELECT COALESCE(SUM(refund
160                                                               - COALESCE( 
161                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
162                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
163                                                                           ,0
164                                                                         )
165                                                               - COALESCE(
166                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
167                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
168                                                                           ,0
169                                                                         )
170                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
171                                                               - ( SELECT COALESCE(SUM(amount
172                                                                   - COALESCE(
173                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
174                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
175                                                                               ,0
176                                                                             )
177                                                                   - COALESCE(
178                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
179                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
180                                                                               ,0
181                                                                             )
182                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
183                                                               - ( SELECT COALESCE(SUM(paid
184                                                                   - COALESCE( 
185                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
186                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
187                                                                               ,0
188                                                                             )
189                                                                   - COALESCE(
190                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
191                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
192                                                                               ,0
193                                                                             )
194                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
195                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )    )
196                                                              AS balance_0_30,   ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
197                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
198                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill   LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM  now() ) - 2592000 ) AND cust_bill._date >  ( EXTRACT( EPOCH FROM  now() ) - 5184000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
199                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
200                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
201                                                               + ( SELECT COALESCE(SUM(refund
202                                                               - COALESCE( 
203                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
204                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
205                                                                           ,0
206                                                                         )
207                                                               - COALESCE(
208                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
209                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
210                                                                           ,0
211                                                                         )
212                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
213                                                               - ( SELECT COALESCE(SUM(amount
214                                                                   - COALESCE(
215                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
216                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
217                                                                               ,0
218                                                                             )
219                                                                   - COALESCE(
220                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
221                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
222                                                                               ,0
223                                                                             )
224                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
225                                                               - ( SELECT COALESCE(SUM(paid
226                                                                   - COALESCE( 
227                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
228                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
229                                                                               ,0
230                                                                             )
231                                                                   - COALESCE(
232                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
233                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
234                                                                               ,0
235                                                                             )
236                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
237                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )   )
238                                                               + ( SELECT COALESCE(SUM(refund
239                                                               - COALESCE( 
240                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
241                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
242                                                                           ,0
243                                                                         )
244                                                               - COALESCE(
245                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
246                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
247                                                                           ,0
248                                                                         )
249                                                             ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM  now() ) - 2592000 ) AND cust_refund._date >  ( EXTRACT( EPOCH FROM  now() ) - 5184000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
250                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
251                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
252                                                               + ( SELECT COALESCE(SUM(refund
253                                                               - COALESCE( 
254                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
255                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
256                                                                           ,0
257                                                                         )
258                                                               - COALESCE(
259                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
260                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
261                                                                           ,0
262                                                                         )
263                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
264                                                               - ( SELECT COALESCE(SUM(amount
265                                                                   - COALESCE(
266                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
267                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
268                                                                               ,0
269                                                                             )
270                                                                   - COALESCE(
271                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
272                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
273                                                                               ,0
274                                                                             )
275                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
276                                                               - ( SELECT COALESCE(SUM(paid
277                                                                   - COALESCE( 
278                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
279                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
280                                                                               ,0
281                                                                             )
282                                                                   - COALESCE(
283                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
284                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
285                                                                               ,0
286                                                                             )
287                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
288                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
289                                                               - ( SELECT COALESCE(SUM(amount
290                                                                   - COALESCE(
291                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
292                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
293                                                                               ,0
294                                                                             )
295                                                                   - COALESCE(
296                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
297                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
298                                                                               ,0
299                                                                             )
300                                                             ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM  now() ) - 2592000 ) AND cust_credit._date >  ( EXTRACT( EPOCH FROM  now() ) - 5184000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
301                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
302                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
303                                                               + ( SELECT COALESCE(SUM(refund
304                                                               - COALESCE( 
305                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
306                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
307                                                                           ,0
308                                                                         )
309                                                               - COALESCE(
310                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
311                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
312                                                                           ,0
313                                                                         )
314                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
315                                                               - ( SELECT COALESCE(SUM(amount
316                                                                   - COALESCE(
317                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
318                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
319                                                                               ,0
320                                                                             )
321                                                                   - COALESCE(
322                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
323                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
324                                                                               ,0
325                                                                             )
326                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
327                                                               - ( SELECT COALESCE(SUM(paid
328                                                                   - COALESCE( 
329                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
330                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
331                                                                               ,0
332                                                                             )
333                                                                   - COALESCE(
334                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
335                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
336                                                                               ,0
337                                                                             )
338                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
339                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
340                                                               - ( SELECT COALESCE(SUM(paid
341                                                                   - COALESCE( 
342                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
343                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
344                                                                               ,0
345                                                                             )
346                                                                   - COALESCE(
347                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
348                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
349                                                                               ,0
350                                                                             )
351                                                             ),    0) FROM cust_pay    LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM  now() ) - 2592000 ) AND cust_pay._date >  ( EXTRACT( EPOCH FROM  now() ) - 5184000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
352                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
353                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
354                                                               + ( SELECT COALESCE(SUM(refund
355                                                               - COALESCE( 
356                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
357                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
358                                                                           ,0
359                                                                         )
360                                                               - COALESCE(
361                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
362                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
363                                                                           ,0
364                                                                         )
365                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
366                                                               - ( SELECT COALESCE(SUM(amount
367                                                                   - COALESCE(
368                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
369                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
370                                                                               ,0
371                                                                             )
372                                                                   - COALESCE(
373                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
374                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
375                                                                               ,0
376                                                                             )
377                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
378                                                               - ( SELECT COALESCE(SUM(paid
379                                                                   - COALESCE( 
380                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
381                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
382                                                                               ,0
383                                                                             )
384                                                                   - COALESCE(
385                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
386                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
387                                                                               ,0
388                                                                             )
389                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
390                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )    )
391                                                              AS balance_30_60,   ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
392                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
393                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill   LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM  now() ) - 5184000 ) AND cust_bill._date >  ( EXTRACT( EPOCH FROM  now() ) - 7776000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
394                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
395                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
396                                                               + ( SELECT COALESCE(SUM(refund
397                                                               - COALESCE( 
398                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
399                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
400                                                                           ,0
401                                                                         )
402                                                               - COALESCE(
403                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
404                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
405                                                                           ,0
406                                                                         )
407                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
408                                                               - ( SELECT COALESCE(SUM(amount
409                                                                   - COALESCE(
410                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
411                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
412                                                                               ,0
413                                                                             )
414                                                                   - COALESCE(
415                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
416                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
417                                                                               ,0
418                                                                             )
419                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
420                                                               - ( SELECT COALESCE(SUM(paid
421                                                                   - COALESCE( 
422                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
423                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
424                                                                               ,0
425                                                                             )
426                                                                   - COALESCE(
427                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
428                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
429                                                                               ,0
430                                                                             )
431                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
432                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )   )
433                                                               + ( SELECT COALESCE(SUM(refund
434                                                               - COALESCE( 
435                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
436                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
437                                                                           ,0
438                                                                         )
439                                                               - COALESCE(
440                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
441                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
442                                                                           ,0
443                                                                         )
444                                                             ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM  now() ) - 5184000 ) AND cust_refund._date >  ( EXTRACT( EPOCH FROM  now() ) - 7776000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
445                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
446                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
447                                                               + ( SELECT COALESCE(SUM(refund
448                                                               - COALESCE( 
449                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
450                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
451                                                                           ,0
452                                                                         )
453                                                               - COALESCE(
454                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
455                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
456                                                                           ,0
457                                                                         )
458                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
459                                                               - ( SELECT COALESCE(SUM(amount
460                                                                   - COALESCE(
461                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
462                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
463                                                                               ,0
464                                                                             )
465                                                                   - COALESCE(
466                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
467                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
468                                                                               ,0
469                                                                             )
470                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
471                                                               - ( SELECT COALESCE(SUM(paid
472                                                                   - COALESCE( 
473                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
474                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
475                                                                               ,0
476                                                                             )
477                                                                   - COALESCE(
478                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
479                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
480                                                                               ,0
481                                                                             )
482                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
483                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
484                                                               - ( SELECT COALESCE(SUM(amount
485                                                                   - COALESCE(
486                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
487                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
488                                                                               ,0
489                                                                             )
490                                                                   - COALESCE(
491                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
492                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
493                                                                               ,0
494                                                                             )
495                                                             ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM  now() ) - 5184000 ) AND cust_credit._date >  ( EXTRACT( EPOCH FROM  now() ) - 7776000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
496                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
497                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
498                                                               + ( SELECT COALESCE(SUM(refund
499                                                               - COALESCE( 
500                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
501                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
502                                                                           ,0
503                                                                         )
504                                                               - COALESCE(
505                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
506                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
507                                                                           ,0
508                                                                         )
509                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
510                                                               - ( SELECT COALESCE(SUM(amount
511                                                                   - COALESCE(
512                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
513                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
514                                                                               ,0
515                                                                             )
516                                                                   - COALESCE(
517                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
518                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
519                                                                               ,0
520                                                                             )
521                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
522                                                               - ( SELECT COALESCE(SUM(paid
523                                                                   - COALESCE( 
524                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
525                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
526                                                                               ,0
527                                                                             )
528                                                                   - COALESCE(
529                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
530                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
531                                                                               ,0
532                                                                             )
533                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
534                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
535                                                               - ( SELECT COALESCE(SUM(paid
536                                                                   - COALESCE( 
537                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
538                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
539                                                                               ,0
540                                                                             )
541                                                                   - COALESCE(
542                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
543                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
544                                                                               ,0
545                                                                             )
546                                                             ),    0) FROM cust_pay    LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM  now() ) - 5184000 ) AND cust_pay._date >  ( EXTRACT( EPOCH FROM  now() ) - 7776000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
547                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
548                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
549                                                               + ( SELECT COALESCE(SUM(refund
550                                                               - COALESCE( 
551                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
552                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
553                                                                           ,0
554                                                                         )
555                                                               - COALESCE(
556                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
557                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
558                                                                           ,0
559                                                                         )
560                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
561                                                               - ( SELECT COALESCE(SUM(amount
562                                                                   - COALESCE(
563                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
564                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
565                                                                               ,0
566                                                                             )
567                                                                   - COALESCE(
568                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
569                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
570                                                                               ,0
571                                                                             )
572                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
573                                                               - ( SELECT COALESCE(SUM(paid
574                                                                   - COALESCE( 
575                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
576                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
577                                                                               ,0
578                                                                             )
579                                                                   - COALESCE(
580                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
581                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
582                                                                               ,0
583                                                                             )
584                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
585                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )    )
586                                                              AS balance_60_90,   ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
587                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
588                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill   LEFT JOIN cust_main USING ( custnum ) WHERE cust_bill._date <= ( EXTRACT( EPOCH FROM  now() ) - 7776000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
589                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
590                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
591                                                               + ( SELECT COALESCE(SUM(refund
592                                                               - COALESCE( 
593                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
594                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
595                                                                           ,0
596                                                                         )
597                                                               - COALESCE(
598                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
599                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
600                                                                           ,0
601                                                                         )
602                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
603                                                               - ( SELECT COALESCE(SUM(amount
604                                                                   - COALESCE(
605                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
606                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
607                                                                               ,0
608                                                                             )
609                                                                   - COALESCE(
610                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
611                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
612                                                                               ,0
613                                                                             )
614                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
615                                                               - ( SELECT COALESCE(SUM(paid
616                                                                   - COALESCE( 
617                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
618                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
619                                                                               ,0
620                                                                             )
621                                                                   - COALESCE(
622                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
623                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
624                                                                               ,0
625                                                                             )
626                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
627                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )   )
628                                                               + ( SELECT COALESCE(SUM(refund
629                                                               - COALESCE( 
630                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
631                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
632                                                                           ,0
633                                                                         )
634                                                               - COALESCE(
635                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
636                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
637                                                                           ,0
638                                                                         )
639                                                             ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE cust_refund._date <= ( EXTRACT( EPOCH FROM  now() ) - 7776000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
640                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
641                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
642                                                               + ( SELECT COALESCE(SUM(refund
643                                                               - COALESCE( 
644                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
645                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
646                                                                           ,0
647                                                                         )
648                                                               - COALESCE(
649                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
650                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
651                                                                           ,0
652                                                                         )
653                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
654                                                               - ( SELECT COALESCE(SUM(amount
655                                                                   - COALESCE(
656                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
657                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
658                                                                               ,0
659                                                                             )
660                                                                   - COALESCE(
661                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
662                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
663                                                                               ,0
664                                                                             )
665                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
666                                                               - ( SELECT COALESCE(SUM(paid
667                                                                   - COALESCE( 
668                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
669                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
670                                                                               ,0
671                                                                             )
672                                                                   - COALESCE(
673                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
674                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
675                                                                               ,0
676                                                                             )
677                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
678                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
679                                                               - ( SELECT COALESCE(SUM(amount
680                                                                   - COALESCE(
681                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
682                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
683                                                                               ,0
684                                                                             )
685                                                                   - COALESCE(
686                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
687                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
688                                                                               ,0
689                                                                             )
690                                                             ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE cust_credit._date <= ( EXTRACT( EPOCH FROM  now() ) - 7776000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
691                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
692                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
693                                                               + ( SELECT COALESCE(SUM(refund
694                                                               - COALESCE( 
695                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
696                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
697                                                                           ,0
698                                                                         )
699                                                               - COALESCE(
700                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
701                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
702                                                                           ,0
703                                                                         )
704                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
705                                                               - ( SELECT COALESCE(SUM(amount
706                                                                   - COALESCE(
707                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
708                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
709                                                                               ,0
710                                                                             )
711                                                                   - COALESCE(
712                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
713                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
714                                                                               ,0
715                                                                             )
716                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
717                                                               - ( SELECT COALESCE(SUM(paid
718                                                                   - COALESCE( 
719                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
720                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
721                                                                               ,0
722                                                                             )
723                                                                   - COALESCE(
724                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
725                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
726                                                                               ,0
727                                                                             )
728                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
729                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
730                                                               - ( SELECT COALESCE(SUM(paid
731                                                                   - COALESCE( 
732                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
733                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
734                                                                               ,0
735                                                                             )
736                                                                   - COALESCE(
737                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
738                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
739                                                                               ,0
740                                                                             )
741                                                             ),    0) FROM cust_pay    LEFT JOIN cust_main USING ( custnum ) WHERE cust_pay._date <= ( EXTRACT( EPOCH FROM  now() ) - 7776000 ) AND    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
742                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
743                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
744                                                               + ( SELECT COALESCE(SUM(refund
745                                                               - COALESCE( 
746                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
747                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
748                                                                           ,0
749                                                                         )
750                                                               - COALESCE(
751                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
752                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
753                                                                           ,0
754                                                                         )
755                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
756                                                               - ( SELECT COALESCE(SUM(amount
757                                                                   - COALESCE(
758                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
759                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
760                                                                               ,0
761                                                                             )
762                                                                   - COALESCE(
763                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
764                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
765                                                                               ,0
766                                                                             )
767                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
768                                                               - ( SELECT COALESCE(SUM(paid
769                                                                   - COALESCE( 
770                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
771                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
772                                                                               ,0
773                                                                             )
774                                                                   - COALESCE(
775                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
776                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
777                                                                               ,0
778                                                                             )
779                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
780                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )    )
781                                                              AS balance_90_0,   ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
782                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
783                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill   LEFT JOIN cust_main USING ( custnum ) WHERE    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
784                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
785                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
786                                                               + ( SELECT COALESCE(SUM(refund
787                                                               - COALESCE( 
788                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
789                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
790                                                                           ,0
791                                                                         )
792                                                               - COALESCE(
793                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
794                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
795                                                                           ,0
796                                                                         )
797                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
798                                                               - ( SELECT COALESCE(SUM(amount
799                                                                   - COALESCE(
800                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
801                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
802                                                                               ,0
803                                                                             )
804                                                                   - COALESCE(
805                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
806                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
807                                                                               ,0
808                                                                             )
809                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
810                                                               - ( SELECT COALESCE(SUM(paid
811                                                                   - COALESCE( 
812                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
813                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
814                                                                               ,0
815                                                                             )
816                                                                   - COALESCE(
817                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
818                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
819                                                                               ,0
820                                                                             )
821                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
822                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )   )
823                                                               + ( SELECT COALESCE(SUM(refund
824                                                               - COALESCE( 
825                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
826                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
827                                                                           ,0
828                                                                         )
829                                                               - COALESCE(
830                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
831                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
832                                                                           ,0
833                                                                         )
834                                                             ), 0) FROM cust_refund LEFT JOIN cust_main USING ( custnum ) WHERE    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
835                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
836                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
837                                                               + ( SELECT COALESCE(SUM(refund
838                                                               - COALESCE( 
839                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
840                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
841                                                                           ,0
842                                                                         )
843                                                               - COALESCE(
844                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
845                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
846                                                                           ,0
847                                                                         )
848                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
849                                                               - ( SELECT COALESCE(SUM(amount
850                                                                   - COALESCE(
851                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
852                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
853                                                                               ,0
854                                                                             )
855                                                                   - COALESCE(
856                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
857                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
858                                                                               ,0
859                                                                             )
860                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
861                                                               - ( SELECT COALESCE(SUM(paid
862                                                                   - COALESCE( 
863                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
864                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
865                                                                               ,0
866                                                                             )
867                                                                   - COALESCE(
868                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
869                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
870                                                                               ,0
871                                                                             )
872                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
873                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
874                                                               - ( SELECT COALESCE(SUM(amount
875                                                                   - COALESCE(
876                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
877                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
878                                                                               ,0
879                                                                             )
880                                                                   - COALESCE(
881                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
882                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
883                                                                               ,0
884                                                                             )
885                                                             ), 0) FROM cust_credit LEFT JOIN cust_main USING ( custnum ) WHERE    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
886                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
887                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
888                                                               + ( SELECT COALESCE(SUM(refund
889                                                               - COALESCE( 
890                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
891                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
892                                                                           ,0
893                                                                         )
894                                                               - COALESCE(
895                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
896                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
897                                                                           ,0
898                                                                         )
899                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
900                                                               - ( SELECT COALESCE(SUM(amount
901                                                                   - COALESCE(
902                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
903                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
904                                                                               ,0
905                                                                             )
906                                                                   - COALESCE(
907                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
908                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
909                                                                               ,0
910                                                                             )
911                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
912                                                               - ( SELECT COALESCE(SUM(paid
913                                                                   - COALESCE( 
914                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
915                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
916                                                                               ,0
917                                                                             )
918                                                                   - COALESCE(
919                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
920                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
921                                                                               ,0
922                                                                             )
923                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
924                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL ) )
925                                                               - ( SELECT COALESCE(SUM(paid
926                                                                   - COALESCE( 
927                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
928                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
929                                                                               ,0
930                                                                             )
931                                                                   - COALESCE(
932                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
933                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
934                                                                               ,0
935                                                                             )
936                                                             ),    0) FROM cust_pay    LEFT JOIN cust_main USING ( custnum ) WHERE    ( SELECT COALESCE(SUM(charged - ( SELECT COALESCE(SUM(amount),0) FROM cust_bill_pay
937                                                                  WHERE cust_bill.invnum = cust_bill_pay.invnum   ) - ( SELECT COALESCE(SUM(amount),0) FROM cust_credit_bill
938                                                                  WHERE cust_bill.invnum = cust_credit_bill.invnum   )),         0) FROM cust_bill    WHERE cust_main.custnum = cust_bill.custnum   )
939                                                               + ( SELECT COALESCE(SUM(refund
940                                                               - COALESCE( 
941                                                                           ( SELECT SUM(amount) FROM cust_credit_refund
942                                                                               WHERE cust_refund.refundnum = cust_credit_refund.refundnum )
943                                                                           ,0
944                                                                         )
945                                                               - COALESCE(
946                                                                           ( SELECT SUM(amount) FROM cust_pay_refund
947                                                                               WHERE cust_refund.refundnum = cust_pay_refund.refundnum )
948                                                                           ,0
949                                                                         )
950                                                             ), 0) FROM cust_refund  WHERE cust_main.custnum = cust_refund.custnum )
951                                                               - ( SELECT COALESCE(SUM(amount
952                                                                   - COALESCE(
953                                                                               ( SELECT SUM(amount) FROM cust_credit_refund
954                                                                                   WHERE cust_credit.crednum = cust_credit_refund.crednum )
955                                                                               ,0
956                                                                             )
957                                                                   - COALESCE(
958                                                                               ( SELECT SUM(amount) FROM cust_credit_bill
959                                                                                   WHERE cust_credit.crednum = cust_credit_bill.crednum )
960                                                                               ,0
961                                                                             )
962                                                             ), 0) FROM cust_credit  WHERE cust_main.custnum = cust_credit.custnum )
963                                                               - ( SELECT COALESCE(SUM(paid
964                                                                   - COALESCE( 
965                                                                               ( SELECT SUM(amount) FROM cust_bill_pay
966                                                                                   WHERE cust_pay.paynum = cust_bill_pay.paynum )
967                                                                               ,0
968                                                                             )
969                                                                   - COALESCE(
970                                                                               ( SELECT SUM(amount) FROM cust_pay_refund
971                                                                                   WHERE cust_pay.paynum = cust_pay_refund.paynum )
972                                                                               ,0
973                                                                             )
974                                                             ),    0) FROM cust_pay     WHERE cust_main.custnum = cust_pay.custnum    )
975                                                              > 0 AND ( agentnum = 1 OR agentnum = 2 OR agentnum = 3 OR agentnum = 4 OR agentnum IS NULL )    )
976                                                              AS balance_0_0