From f466ba180c9f77719df8f5cd58392b939a0943b4 Mon Sep 17 00:00:00 2001
From: Chris Sharp <csharp@georgialibraries.org>
Date: Mon, 15 Jul 2019 09:10:15 -0400
Subject: [PATCH] Add linkages to invoice entries.

---
 Open-ILS/examples/fm_IDL.xml                       | 11 ++--
 Open-ILS/src/sql/Pg/example.reporter-extension.sql | 60 ++++++++++++----------
 2 files changed, 40 insertions(+), 31 deletions(-)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 36a4863a22..5050530185 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -13149,8 +13149,11 @@ SELECT  usr,
 			<field reporter:label="Fund Code" name="fund_code" reporter:datatype="text"/>
 			<field reporter:label="Fund Debit ID" name="fund_debit_id" reporter:datatype="link"/>
 			<field reporter:label="Invoice Direct Charge ID" name="invoice_item_id" reporter:datatype="link"/>
-			<field reporter:label="Internal Invoice ID" name="inv_id" reporter:datatype="link"/>
-			<field reporter:label="Vendor Invoice ID" name="invoice_ident" reporter:datatype="text"/>
+			<field reporter:label="Invoice Item Internal Invoice ID" name="invoice_item_invoice_id" reporter:datatype="link"/>
+			<field reporter:label="Invoice Item Vendor Invoice ID" name="invoice_ident" reporter:datatype="text"/>
+			<field reporter:label="Invoice Entry ID" name="invoice_entry_id" reporter:datatype="link"/>
+			<field reporter:label="Invoice Entry Internal Invoice ID" name="invoice_entry_invoice_id" reporter:datatype="link"/>
+			<field reporter:label="Invoice Entry Vendor Invoice ID" name="invoice_entry_invoice_ident" reporter:datatype="text"/>
 			<field reporter:label="PO Direct Charge ID" name="po_item_id" reporter:datatype="link"/>
 			<field reporter:label="PO Direct Charge Purchase Order ID" name="po_item_po_id" reporter:datatype="link"/>
 			<field reporter:label="Line Item Detail ID" name="lineitem_detail_id" reporter:datatype="link"/>
@@ -13165,7 +13168,9 @@ SELECT  usr,
 			<link field="fund_id" reltype="has_a" key="id" map="" class="acqf"/>
 			<link field="fund_debit_id" reltype="has_many" key="id" map="" class="acqfdeb"/>
 			<link field="invoice_item_id" reltype="has_many" key="id" map="" class="acqii"/>
-			<link field="inv_id" reltype="has_a" key="id" map="" class="acqinv"/>
+			<link field="invoice_item_invoice_id" reltype="has_a" key="id" map="" class="acqinv"/>
+			<link field="invoice_entry_id" reltype="has_many" key="id" map="" class="acqie"/>
+			<link field="invoice_entry_invoice_id" reltype="has_a" key="id" map="" class="acqinv"/>
 			<link field="po_item_id" reltype="has_many" key="id" map="" class="acqpoi"/>
 			<link field="po_item_po_id" reltype="has_a" key="id" map="" class="acqpo"/>
 			<link field="lineitem_detail_id" reltype="has_many" key="id" map="" class="acqlid"/>
diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
index b1c514c600..4a186a01a8 100644
--- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql
+++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql
@@ -316,33 +316,37 @@ 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);
+DROP VIEW IF EXISTS reporter.acq_fund_debit_view;
+CREATE 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,
+    acqii_invoice.id AS invoice_item_invoice_id,
+    acqii_invoice.inv_ident AS invoice_item_invoice_ident,
+    aie.id AS invoice_entry_id,
+    aie_invoice.id AS invoice_entry_invoice_id,
+    aie_invoice.inv_ident AS invoice_entry_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,
+    fd.debit_type AS fund_debit_type,
+    fd.create_time
+   FROM acq.fund fund
+     LEFT JOIN acq.fund_debit fd ON fd.fund = fund.id
+     LEFT JOIN acq.invoice_item acqii ON acqii.fund_debit = fd.id
+     LEFT JOIN acq.invoice acqii_invoice ON acqii.invoice = acqii_invoice.id
+     LEFT JOIN acq.po_item acqpoi ON acqpoi.fund_debit = fd.id
+     LEFT JOIN acq.purchase_order po_item_po ON acqpoi.purchase_order = po_item_po.id
+     LEFT JOIN acq.lineitem_detail lid ON lid.fund_debit = fd.id
+     LEFT JOIN acq.lineitem jub ON lid.lineitem = jub.id
+     LEFT JOIN acq.purchase_order po ON jub.purchase_order = po.id
+     LEFT JOIN acq.invoice_entry aie ON (aie.lineitem = jub.id)
+     LEFT JOIN acq.invoice aie_invoice ON (aie.invoice = aie_invoice.id);
 
 COMMIT;
-
-
-- 
2.11.0