[OPEN-ILS-GENERAL] Acq report of invoices with totals

[Willis, Beth]

Here is the query for NOBLE's report of invoices with total spent
(excluding charges).

This report displays: internal invoice id, vendor invoice id, invoice date,
provider name, total amount, and count of items included.

The report filters on invoice date, receiver and provider id.

I hope this helps.
Beth

SELECT * FROM (SELECT	"466eadd40b3c10580e3ab4e8061161ce"."id" AS
"Internal Invoice ID",
	"466eadd40b3c10580e3ab4e8061161ce"."inv_ident" AS "Vendor Invoice ID",
	date("466eadd40b3c10580e3ab4e8061161ce"."recv_date") AS "Invoice Date",
	"3989545fd856bf72407b050354fe0c9c"."name" AS "Provider Name",
	SUM("fe7f56420663a8a9716e5e296407a41f"."amount") AS "Amount",
	COUNT("7bb4219e362cd965b10c45e2b97bb8bd"."id") AS "Item Count"
  FROM	acq.invoice AS "466eadd40b3c10580e3ab4e8061161ce"
	LEFT OUTER JOIN acq.invoice_entry AS
"6648b2507b1a9035e53597fb1e736aa8" ON
("466eadd40b3c10580e3ab4e8061161ce"."id" =
"6648b2507b1a9035e53597fb1e736aa8"."invoice")
	LEFT OUTER JOIN acq.lineitem AS "c1e9f36a7998d1b31d1db871b40b4f35" ON
("6648b2507b1a9035e53597fb1e736aa8"."lineitem" =
"c1e9f36a7998d1b31d1db871b40b4f35"."id")
	LEFT OUTER JOIN acq.lineitem_detail AS
"7bb4219e362cd965b10c45e2b97bb8bd" ON
("c1e9f36a7998d1b31d1db871b40b4f35"."id" =
"7bb4219e362cd965b10c45e2b97bb8bd"."lineitem")
	LEFT OUTER JOIN acq.fund_debit AS "fe7f56420663a8a9716e5e296407a41f"
ON ("7bb4219e362cd965b10c45e2b97bb8bd"."fund_debit" =
"fe7f56420663a8a9716e5e296407a41f"."id")
	INNER JOIN acq.provider AS "3989545fd856bf72407b050354fe0c9c" ON
("466eadd40b3c10580e3ab4e8061161ce"."provider" =
"3989545fd856bf72407b050354fe0c9c"."id")
  WHERE	date("466eadd40b3c10580e3ab4e8061161ce"."recv_date") BETWEEN
$_8130$2015-10-01$_8130$ AND $_8130$2015-10-31$_8130$
	AND "466eadd40b3c10580e3ab4e8061161ce"."receiver" IN ($_8130$59$_8130$)
	AND "3989545fd856bf72407b050354fe0c9c"."id" IN
($_8130$803$_8130$,$_8130$804$_8130$,$_8130$805$_8130$,$_8130$806$_8130$,$_8130$807$_8130$,$_8130$808$_8130$,$_8130$1209$_8130$,$_8130$809$_8130$,$_8130$810$_8130$,$_8130$813$_8130$,$_8130$814$_8130$,$_8130$774$_8130$,$_8130$778$_8130$,$_8130$818$_8130$,$_8130$820$_8130$,$_8130$1126$_8130$,$_8130$1174$_8130$,$_8130$1175$_8130$,$_8130$821$_8130$,$_8130$777$_8130$,$_8130$832$_8130$,$_8130$776$_8130$,$_8130$822$_8130$,$_8130$1024$_8130$,$_8130$826$_8130$,$_8130$823$_8130$,$_8130$833$_8130$,$_8130$1213$_8130$,$_8130$824$_8130$,$_8130$961$_8130$,$_8130$135$_8130$,$_8130$957$_8130$,$_8130$825$_8130$,$_8130$783$_8130$,$_8130$960$_8130$,$_8130$1031$_8130$,$_8130$784$_8130$,$_8130$955$_8130$,$_8130$1407$_8130$,$_8130$782$_8130$,$_8130$828$_8130$,$_8130$829$_8130$,$_8130$1146$_8130$,$_8130$956$_8130$,$_8130$1140$_8130$,$_8130$1144$_8130$,$_8130$1389$_8130$,$_8130$827$_8130$,$_8130$1258$_8130$,$_8130$1077$_8130$,$_8130$1138$_8130$,$_8130$830$_8130$,$_8130$1023$_8130$,$_8130$1266$_8130$,$_8130$831$_8130$,$_8130$834$_8130$,$_8130$835$_8130$,$_8130$1246$_8130$,$_8130$1264$_8130$)
  GROUP BY 1, 2, 3, 4
  ORDER BY "466eadd40b3c10580e3ab4e8061161ce"."id" ASC,
"466eadd40b3c10580e3ab4e8061161ce"."inv_ident" ASC,
date("466eadd40b3c10580e3ab4e8061161ce"."recv_date") ASC,
"3989545fd856bf72407b050354fe0c9c"."name" ASC,
SUM("fe7f56420663a8a9716e5e296407a41f"."amount") ASC,
COUNT("7bb4219e362cd965b10c45e2b97bb8bd"."id") ASC
) limited_to_1048575_hits LIMIT 1048575


On Wed, Nov 18, 2015 at 12:57 PM, Sharon Douglas <douglass at exchange.larl.org
> wrote:

> Hi Beth,
>
> I’ve not been able to show the TOTAL AMOUNT SPENT PER INVOICE and would
> like to see your query for doing that.
>
> Can you share it?
>
>
>
> Sharon Douglas  -  Automation Coordinator - Lake Agassiz Regional Library
>
> 118 5th St S  - Moorhead MN 56561  -  218-233-3757 ext 138  -
> www.larl.org
>
> The mission of LARL is to enrich lives and strengthen communities
>
> LARL values intellectual freedom, equal access to information, respect,
>
> tolerance, fun and welcoming atmosphere, excellent customer service,
>
> and a current community driven collection.
>
>
>
> *From:* Open-ils-general [mailto:
> open-ils-general-bounces at list.georgialibraries.org] *On Behalf Of *Willis,
> Beth
> *Sent:* November 18, 2015 11:16 AM
> *To:* Evergreen Discussion Group
> *Subject:* Re: [OPEN-ILS-GENERAL] Acq report of invoices with totals
>
>
>
> Hi Janet,
>
>
>
> We have a report that shows the total amount spent per invoice, along with
> the total number of items purchased.  But, this report does not include
> additional charges.  We have to run a separate report for those.  I was not
> able to create one report that would include both.  If someone else has, I
> would love to see the query.
>
>
>
> If you would like more information about our report templates, just let me
> know.
>
>
>
> Thanks.
>
> beth
>
>
>
> On Mon, Nov 2, 2015 at 5:27 PM, Janet Brown <Janetb at burlingtonwa.gov>
> wrote:
>
>
>
> We have been trying to come up with a report of invoices which shows the
> total amount spent on each invoice along with any added charges, i.e.
> taxes, shipping or processing fees that have been added to the invoice. We
> have created some reports but they return lists of all the individual items
> on the invoices rather than the total spent for each invoice. And they
> don’t include the added charges.
>
>
>
> Part of our issue may be that we can only use the reporting tool in
> Evergreen which might not be capable of doing what I would like since I
> can’t seem to create a report that gives the results we want.
>
>
>
> We would definitely appreciate help if anyone has created a report such as
> this that works.
>
>
>
> Thank you!
>
>
>
> Janet Brown
>
> SysAdmin/Circ Supervisor
>
>
>
> Burlington Public Library
>
> 820 E Washington Ave
>
> Burlington WA  98233
>
> 360-755-0760
>
> janetb at burlingtonwa.gov
>
>
>
>
>
>
>
> --
>
> Beth Willis
> Digital & Catalog Librarian
> NOBLE, Inc.
> 26 Cherry Hill Drive
> Danvers, MA 01923
>



-- 
Beth Willis
Digital & Catalog Librarian
NOBLE, Inc.
26 Cherry Hill Drive
Danvers, MA 01923
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20151119/c99da6e2/attachment.html>