From a022bc7610838c0fa5c2c60f6fc071c1616daacd Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Tue, 18 Sep 2018 11:25:02 -0400 Subject: [PATCH] Acquisitions fund debit reporter view There is currently not an efficient (or even possible) way to fully report which PO entries/direct charges and which Invoice entries/direct charges are drawn from a particular fund. This view provides those linkages. Signed-off-by: Chris Sharp --- Open-ILS/src/sql/Pg/example.reporter-extension.sql | 27 ++++++++++++++++++++++ 1 file changed, 27 insertions(+) diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql index 8cee578cb5..b1c514c600 100644 --- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql +++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql @@ -316,6 +316,33 @@ CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS FROM money.open_circ_balance_by_usr_home_and_owning_lib x GROUP BY 1,2; +CREATE OR REPLACE VIEW reporter.acq_fund_debit_view AS +SELECT fund.id AS fund_id, + fund.code AS fund_code, + fd.id AS fund_debit_id, + acqii.id AS invoice_item_id, + invoice.id AS inv_id, + invoice.inv_ident AS invoice_ident, + acqpoi.id AS po_item_id, + po_item_po.id AS po_item_po_id, + lid.id AS lineitem_detail_id, + jub.id AS lineitem_id, + po.id AS po_id, + fd.origin_amount AS fund_debit_origin_amount, + fd.amount AS fund_debit_amount, + fd.encumbrance AS encumbrance, + fd.debit_type AS fund_debit_type, + fd.create_time AS create_time +FROM acq.fund fund + LEFT OUTER JOIN acq.fund_debit fd ON (fd.fund = fund.id) + LEFT OUTER JOIN acq.invoice_item acqii ON (acqii.fund_debit = fd.id) + LEFT OUTER JOIN acq.invoice invoice ON (acqii.invoice = invoice.id) + LEFT OUTER JOIN acq.po_item acqpoi ON (acqpoi.fund_debit = fd.id) + LEFT OUTER JOIN acq.purchase_order po_item_po ON (acqpoi.purchase_order = po_item_po.id) + LEFT OUTER JOIN acq.lineitem_detail lid ON (lid.fund_debit = fd.id) + LEFT OUTER JOIN acq.lineitem jub ON (lid.lineitem = jub.id) + LEFT OUTER JOIN acq.purchase_order po ON (jub.purchase_order = po.id); + COMMIT; -- 2.11.0