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