Report Description Detail

This is the documentation for the UXPROD-2301 RM-Invoice Cluster. The purpose of this library expenditures report cluster query is to show how much money was spent within a given date range. The report includes separate prorated charges such as shipping. The focus of this query will be to provide a summary of what you have spent per purchase order line (through a sum of one or more invoice lines). This query is most closely related to REP-82 - Invoice Report (with Order data).

Derived Tables

-none needed

Main Tables and Columns Included

invoices_invoices

-id

-approval date (as a stand in for datePaid)

-total - use initially to check totals being pulled from the invoice line; should equal summary of invoice lines reported out
-status

invoice_lines

-id
-total
-subtotal

-invoice_id

-invoice_line_number - use to group related invoice lines
-total

po_lines

-id

po_order_invoice_relns

-id

-invoice_id

-purchase_order_id - use to join to id in po_lines

Aggregation

using po_line_id as grouping element, join this with invoice_line and provide a summary total of what was spent by po_line_id

-start with invoice_invoices table and include all invoices paid within a date paid range with a status of paid (see status on invoice table)

Filters for Users to Select

-date range

Hardcoded Filters

Assumptions

Missing Data Elements

The datePaid data element is missing from this report because it is not scheduled for development until R1-2021.

--

--

--

--------------------- OLDER NOTES

Information Resources

See the Data Dictionary FOLIO Schema Parser for details on data elements

See the ACRL Diagram for tables and fields related to this report cluster

See the Acquisitions Interface Fields spreadsheet for data attribute definitions

---------- NOTES ---------------------

*Material Type (from Martina)
-Second material type set up is for DBS-related report expenditures
-RM bringing more data attributes into Order Lines
-You can count on more than one Order material type
-RM material types spreadsheet
-https://docs.google.com/spreadsheets/d/12aLp2hZ-4V9yDnMcwa_irtiivWd71JGoxoGB6kyr4i0/edit#gid=0

Costs Model

-invoice line on the invoice includes a payment, which has a PO link and the funds charged

-we need the detail of the invoice line to show the different charges associated with that invoice line

-invoices can have charges at the invoice level if you choose not to prorate them

-budget irrelevant to expenditures

-overall, eresources may need their own separate models because eresource and print resource data elements are separate; order format tells you whether the order was electronic or paper

-

Invoice Expenditures Model

-what did we spend on these items within this date range that includes separate prorated charges such shipping

-prorated charges are under adjustments in invoice line number

-pull ID from each table

-start with invoice_invoices table, show datePaid, which will be at this level

-start with invoice_invoices table and include all invoices paid within a date paid range with a status of paid (see status on invoice table)

-from this collections of invoices, select the invoice lines and show the detail

-include query here to show total on the invoice line and pull the element that shows the total for the invoice as a way of checking the data

-the invoice line has a link to the PO

-from invoice line table, pull total, subtotal, fund distributions, PO line ID, adjustments, and adjustments total

-use the PO Line ID to connect to the PO Lines table

-on PO Lines table, pull reporting codes, tags, acquisition method (shows firm vs approvals, etc.), description, details (subscription, may show whether subscription or firm order)

-connect from PO lines to Orders table (see po_lines:id description) and then to Order Lines table (see orders:id description)

-from Orders, pull acqUnitids, order type (firm or subscription)

-from Order Lines table, acquisition method, orderFormat, poLineNumber, instanceId, agreementId, purchaseOrderID

-when you connect to the fund from invoice line number, it will show a row for each fund charged, so this may need to be grouped

-

Questions for the RM SIG

-how do the details, eresource, and physical attributes work on the po lines table?

-(From Dennis) This is the number of the feature we are discussing. UXPROD-2362

-

Feedback

-please add material type from PO_Lines (NOT THERE - get from inventory?) - wait to find out where it will be located, PO Lines or Inventory - ask Dennis

-add encumbrances; try to show status on what may still need to be paid for, chase down invoices we need to pay (ADDED) - ask RM SIG for more details; are these just open POs?

-add expense type at both invoice line and PO line level (see Dennis)

-add elements necessary to report on Groups (still under development, will work toward establishing a hierarchy, working on shared allocations, might add a group type)

-add invoice number (ADDED vendorInvoiceNo and folioInvoiceNo?)

-see Virgina Martin about RM reports

Questions about Tables in the RM Costs Cluster

-Sharon to update the RM Costs Cluster prototype to include LDP attribute column

-Sharon to find out about Invoice_Line_Number - is this a deprecated table?

-

Non-Prorated Invoice Model

-compared with prorated, this will just create another invoice line for the additional related charges, such as shipping

-some institutions (e.g., German) must pay a VAT (value added tax), which is not part of the invoice but it is being recorded on the invoice (how do we separate this out?)

-

*Table not found

orders is now po_purchase_orders
Table name: mod-orders-storage/order_lines is no po_lines
invoice_line_number temporarily disabled in LDP
Sharon to update this prototype for LDP naming conventions

Folio Attribute (Module/Path:Object)	Folio Data Element Description	Parameters/Query
*Table Name: mod-finance-storage/invoice_line_number
*Invoice Line Number ID (mod-invoice-storage/invoice_line_number:id)	UUID of the invoice line number
Total (mod-invoice-storage/invoice_line_number:total)	invoice line number total with adjustments	This total includes expenditures with adjustments.
Subtotal (mod-invoice-storage/invoice_line_number:subtotal)	invoice line number subtotal without adjustments	This total includes expenditures without adjustments.
Fund Distributions (mod-invoice-storage/invoice_line_number:fundDistributions)	shows the distribution of expenditures by fund	When you connect to Fund Distributions from the invoice line id table, it will show a row for each fund charged, so the resulting rows will need to be grouped by fund. This will vary depending on whether you want to report by Fund (total) or by PO Line Number (item).
PO Line ID (mod-invoice-storage/invoice_line:poLineID)	ID of the purchase order line	Provides a join between the mod-finance-storage/invoice_line_number table and mod-orders-storage/po_lines table.
Adjustments (mod-invoice-storage/invoice_line_number:adjustments)	invoice line number adjustments	Often prorated charges (e.g., service charges, shipping, taxes, etc.) and some non-prorated charges are under adjustments on the invoice line number table
Adjustments Total (mod-invoice-storage/invoice_line_number:adjustments_total)	total of invoice line number adjustments
Table Name: mod-invoice-storage/invoice
Invoice ID (mod-invoice-storage/invoices:id)	UUID of the invoice.	Provides a join between the mod-orders-storage/invoices table and the mod-finance-storage/invoice_line_number table.
Invoice Paid Date (mod-invoice-storage/invoices:paidDate)	Date the invoice was paid.	placeholder for the invoice paid date data attribute; this will be used to set the time period being reported. Often, we show fiscal year as the time period (e.g., 7/1/XX through 6/30/XX)
Invoice Status (mod-invoice-storage/invoices:status)	Status of the invoice (e.g., paid, approved,etc.)	This report needs to show the status as paid.
Vendor ID (mod-invoice-storage/invoices:VendorId)	UUID of the vendor	This identifies the vendor. Provides a join to organizations vendors for natural vendor name.
FOLIO Invoice Number (mod-invoice-storage/invoices:folioInvoiceNo)	Invoice number in folio system; internally assigned; used and assigned by the system only
Vendor Invoice Number (mod-invoice-storage/invoices:vendorInvoiceNo)	This is the number from the vendor's invoice, which is different from the folioInvoiceNo
Table Name: mod-organizations-storage/organizations
Organization ID mod-organizations-storage/organizations:id	The unique UUID for this organization	Provides a join from VendorID to Organization ID to get Organization Name and Organization Code
Organization Name mod-organizations-storage/organizations:name	The name for this organization
Organization Code mod-organizations-storage/organizations:code	The code for this organization
*Table Name: mod-orders-storage/po_purchase_orders
Order ID (mod-orders-storage/orders:id)	UUID for a specific order	Provides join between the mod-orders-storage/orders table and the mod-orders-storage/order_lines table.
Acquisition Unit ID (mod-orders-storage/orders:acqUnitIds	The ID of acquisition unit.	Optional. Use this attribute only if you need to show the acquisition unit involved.
Order Type (mod-orders-storage/orders:order_type)	Provides the category for the order type ( One-Time or Ongoing)	Will be needed to get "One-time" (firm order) or" Ongoing" (continuation, subscription) expenditures. Show a sum of all expenditures for each order type. Filter by order type.
Approval Date (mod-orders-storage/orders:approvalDate)	Date the order was approved.	Optional. Used for reporting based on the date the order was made.
*Table name: mod-orders-storage/order_lines
Order Lines ID (mod-orders-storage/order_lines:id)	The ID of the order line.
Acquisition Method (mod-orders-storage/order_lines:acquisitionMethod)	The acquisition method of the order, e.g. firm order, approval, subscriptions, etc.
Order Format (mod-orders-storage/order_lines:orderFormat)	The format of the material being ordered, e.g., physical, electronic, or mixed.
PO Line Number (mod-orders-storage/order_lines:poLineNumber)	The line number on the purchase order.
Instance ID (mod-orders-storage/order_lines:instanceID)	The ID for the instance (bibliographic record ID).
Agreement ID (mod-orders-storage/order_lines:agreementID)	The ID of the agreement pertaining to this order line.
PO ID (mod-orders-storage/order_lines:purchaseOrderID)	The ID of the purchase order for this purchase order line.
Table name: mod-orders-storage/po_lines
PO lines ID: (mod-orders-storage/po_lines:id)	The UUID of a PO Line	Provides a join between the mod-order-storage/po_lines table and the mod-orders-storage/orders table.
PO lines Order Format name: (mod-orders-storage/po-lines/order_format:name)	Provides the format of the item purchased	Will provide in the report the format name of the order. (Electronic Resource, Physical Resource, P/E Mix,Other).
Reporting Codes: (mod-orders-storage/po_lines:reportingCodes*)	a list of reporting codes associated with this purchase order line
Tags: (mod-orders-storage/po_lines:tags)	arbitrary tags associated with this purchase order line	tags are in the data array
Acquisition Method: (mod-orders-storage/po_lines:acquisitionMethod)	NO DESCRIPTION
Description: (mod-orders-storage/po_lines:description)	The title of the resource being ordered.	May not be needed.
Details (Subscription): (mod-orders-storage/po_lines:details)	details record
Electronic Resource ID (mod-orders-storage/po_lines:eresource*)	UUID of the electronic resource (details) record.	Indicates that the item being ordered is an electronic resource. Material type of the item can be found in the data array. Join to the derived table for physical and/or electronic resource.
Physical ID (mod-orders-storage/po_lines:physical*)	UUID of the physical (details) record.	Indicates that the item being ordered is a physical resource. Material type of the item can be found in the data array. Join to the derived table for physical and/or electronic resource.
Requester (mod-orders-storage/po_lines:requester)	The person who requested the order.	Contains personal data. This field may be omitted in cases when GDPR-compliance is required.
Selector (mod-orders-storage/po_lines:selector)	The person who is authorized to submit the order.	Contains personal data. This field may be omitted in cases when GDPR-compliance is required.
*Material Type (mod-orders-storage/po_lines:material_type)		not sure this will be developed
Table name: mod-finance-storage/finance-storage/finance_transactions
Encumbrance (mod-finance-storage/finance-storage/transactions: paymentEncumbranceId*)	UUID of the encumbrance associated with this payment/credit taking place.
Encumbrance (mod-finance-storage/finance-storage/transactions: encumbrance*)	Encumbrance sub-object - holds encumbrance-specific information not applicable to other transaction types
Table name: mod-inventory-storage/instance-storage/instances
Instances Statistical Code ID (mod-inventory-storage/instances: statistical_code_id)	UUID of a statistical code	Connects with mod-inventory-storage/instances: statistical_code_id. May allow to filter by type/format if needed. Repeatable field Will be pulled from JSON.
Instance ID mod-inventory-storage/instance-storage/instances/id	The unique ID of the instance record; a UUID	Connects with instance of this record. Used to make joins to bibliographic metadata, such as subject.

Browser not supported