1 -- 0732.schema.acq-lineitem-summary.sql
4 SELECT evergreen.upgrade_deps_block_check('0732', :eg_version);
6 CREATE OR REPLACE 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 WHERE cancel_reason IS NOT NULL AND lineitem = li.id
26 FROM acq.lineitem_detail lid
27 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
28 WHERE NOT debit.encumbrance AND lineitem = li.id
31 SELECT COUNT(DISTINCT(lid.id))
32 FROM acq.lineitem_detail lid
33 JOIN acq.claim claim ON (claim.lineitem_detail = lid.id)
34 WHERE lineitem = li.id
37 SELECT (COUNT(lid.id) * li.estimated_unit_price)::NUMERIC(8,2)
38 FROM acq.lineitem_detail lid
39 WHERE lid.cancel_reason IS NULL AND lineitem = li.id
40 ) AS estimated_amount,
42 SELECT SUM(debit.amount)::NUMERIC(8,2)
43 FROM acq.lineitem_detail lid
44 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
45 WHERE debit.encumbrance AND lineitem = li.id
46 ) AS encumbrance_amount,
48 SELECT SUM(debit.amount)::NUMERIC(8,2)
49 FROM acq.lineitem_detail lid
50 JOIN acq.fund_debit debit ON (lid.fund_debit = debit.id)
51 WHERE NOT debit.encumbrance AND lineitem = li.id
54 FROM acq.lineitem AS li;