4 DROP VIEW acq.lineitem_summary;
6 CREATE VIEW acq.lineitem_summary AS
11 FROM acq.lineitem_detail lid
12 WHERE lineitem = li.id
16 FROM acq.lineitem_detail lid
17 WHERE recv_time IS NOT NULL AND lineitem = li.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
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
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
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
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,
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,
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
61 FROM acq.lineitem AS li;