LP#1270289 Split canceled into delayed vs canceled
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.schema.acq-cancel-display.sql
1
2 BEGIN;
3
4 DROP VIEW acq.lineitem_summary;
5
6 CREATE VIEW acq.lineitem_summary AS
7     SELECT 
8         li.id AS lineitem, 
9         (
10             SELECT COUNT(lid.id) 
11             FROM acq.lineitem_detail lid
12             WHERE lineitem = li.id
13         ) AS item_count,
14         (
15             SELECT COUNT(lid.id) 
16             FROM acq.lineitem_detail lid
17             WHERE recv_time IS NOT NULL AND lineitem = li.id
18         ) AS recv_count,
19         (
20             SELECT COUNT(lid.id) 
21             FROM acq.lineitem_detail lid
22                 JOIN acq.cancel_reason acqcr ON (acqcr.id = lid.cancel_reason)
23             WHERE acqcr.keep_debits IS FALSE AND lineitem = li.id
24         ) AS cancel_count,
25         (
26             SELECT COUNT(lid.id) 
27             FROM acq.lineitem_detail lid
28                 JOIN acq.cancel_reason acqcr ON (acqcr.id = lid.cancel_reason)
29             WHERE acqcr.keep_debits IS TRUE AND lineitem = li.id
30         ) AS delay_count,
31         (
32             SELECT COUNT(lid.id) 
33             FROM acq.lineitem_detail lid
34                 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
35             WHERE NOT debit.encumbrance AND lineitem = li.id
36         ) AS invoice_count,
37         (
38             SELECT COUNT(DISTINCT(lid.id)) 
39             FROM acq.lineitem_detail lid
40                 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
41             WHERE lineitem = li.id
42         ) AS claim_count,
43         (
44             SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
45             FROM acq.lineitem_detail lid
46             WHERE lid.cancel_reason IS NULL AND lineitem = li.id
47         ) AS estimated_amount,
48         (
49             SELECT SUM(debit.amount)::NUMERIC(8,2)
50             FROM acq.lineitem_detail lid
51                 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
52             WHERE debit.encumbrance AND lineitem = li.id
53         ) AS encumbrance_amount,
54         (
55             SELECT SUM(debit.amount)::NUMERIC(8,2)
56             FROM acq.lineitem_detail lid
57                 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
58             WHERE NOT debit.encumbrance AND lineitem = li.id
59         ) AS paid_amount
60
61         FROM acq.lineitem AS li;
62
63 COMMIT;