3 CREATE OR REPLACE VIEW acq.lineitem_summary AS
8 FROM acq.lineitem_detail lid
13 FROM acq.lineitem_detail lid
14 WHERE recv_time IS NOT NULL AND lineitem = li.id
18 FROM acq.lineitem_detail lid
19 WHERE cancel_reason IS NOT NULL AND lineitem = li.id
23 FROM acq.lineitem_detail lid
24 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
25 WHERE NOT debit.encumbrance AND lineitem = li.id
28 SELECT COUNT(DISTINCT(lid.id))
29 FROM acq.lineitem_detail lid
30 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
31 WHERE lineitem = li.id
34 SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
35 FROM acq.lineitem_detail lid
36 WHERE lid.cancel_reason IS NULL AND lineitem = li.id
37 ) AS estimated_amount,
39 SELECT SUM(debit.amount)::NUMERIC(8,2)
40 FROM acq.lineitem_detail lid
41 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
42 WHERE debit.encumbrance AND lineitem = li.id
43 ) AS encumbrance_amount,
45 SELECT SUM(debit.amount)::NUMERIC(8,2)
46 FROM acq.lineitem_detail lid
47 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
48 WHERE NOT debit.encumbrance AND lineitem = li.id
51 FROM acq.lineitem AS li;