| | 227 | |
| | 228 | CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS |
| | 229 | SELECT circ.id, |
| | 230 | cn.owning_lib, |
| | 231 | bill.billing_type, |
| | 232 | SUM(bill.amount) AS billed |
| | 233 | FROM action.circulation circ |
| | 234 | JOIN money.billing bill ON (circ.id = bill.xact) |
| | 235 | JOIN asset.copy cp ON (circ.target_copy = cp.id) |
| | 236 | JOIN asset.call_number cn ON (cn.id = cp.call_number) |
| | 237 | WHERE circ.xact_finish IS NULL |
| | 238 | AND NOT bill.voided |
| | 239 | GROUP BY 1,2,3 |
| | 240 | ORDER BY 1,2,3; |
| | 241 | |
| | 242 | CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS |
| | 243 | SELECT owning_lib, |
| | 244 | ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, |
| | 245 | SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance |
| | 246 | FROM money.open_circ_balance_by_owning_lib x |
| | 247 | GROUP BY 1; |
| | 248 | |
| | 249 | |
| | 250 | |
| | 251 | |
| | 252 | |
| | 253 | CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS |
| | 254 | SELECT circ.id, |
| | 255 | circ.circ_lib, |
| | 256 | cn.owning_lib, |
| | 257 | bill.billing_type, |
| | 258 | SUM(bill.amount) AS billed |
| | 259 | FROM action.circulation circ |
| | 260 | JOIN money.billing bill ON (circ.id = bill.xact) |
| | 261 | JOIN asset.copy cp ON (circ.target_copy = cp.id) |
| | 262 | JOIN asset.call_number cn ON (cn.id = cp.call_number) |
| | 263 | WHERE circ.xact_finish IS NULL |
| | 264 | AND NOT bill.voided |
| | 265 | GROUP BY 1,2,3,4 |
| | 266 | ORDER BY 1,2,3,4; |
| | 267 | |
| | 268 | CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS |
| | 269 | SELECT circ_lib, |
| | 270 | owning_lib, |
| | 271 | ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, |
| | 272 | SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance |
| | 273 | FROM money.open_circ_balance_by_circ_and_owning_lib x |
| | 274 | GROUP BY 1,2; |
| | 275 | |
| | 276 | |
| | 277 | |
| | 278 | |
| | 279 | |
| | 280 | CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS |
| | 281 | SELECT circ.id, |
| | 282 | usr.home_ou, |
| | 283 | cn.owning_lib, |
| | 284 | bill.billing_type, |
| | 285 | SUM(bill.amount) AS billed |
| | 286 | FROM action.circulation circ |
| | 287 | JOIN money.billing bill ON (circ.id = bill.xact) |
| | 288 | JOIN asset.copy cp ON (circ.target_copy = cp.id) |
| | 289 | JOIN asset.call_number cn ON (cn.id = cp.call_number) |
| | 290 | JOIN actor.usr usr ON (circ.usr = usr.id) |
| | 291 | WHERE circ.xact_finish IS NULL |
| | 292 | AND NOT bill.voided |
| | 293 | GROUP BY 1,2,3,4 |
| | 294 | ORDER BY 1,2,3,4; |
| | 295 | |
| | 296 | CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS |
| | 297 | SELECT home_ou, |
| | 298 | owning_lib, |
| | 299 | ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, |
| | 300 | SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance |
| | 301 | FROM money.open_circ_balance_by_usr_home_and_owning_lib x |
| | 302 | GROUP BY 1,2; |
| | 303 | |
