RM-Order Cluster Prototype

Report Description Detail

The RM-Order Cluster covers approximately XX reports (REP-XXX issues) that provide Resource Management data on library expenditures. 

-(later) create a "what have I not paid for this year?" report based on all open orders from the po query and po_line ids that do not exist yet from the invoice query

The purpose of this report is to provide details about each purchase order with filtering by date and/or order type.

-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, material type, poLineNumber, instanceId, agreementId, purchaseOrderID

-leave funds out for now

Data Fields

MAIN TABLES AND COLUMNS INCLUDED

--

PO_LINES
 -purchase order line id
 -purchase_order_number from po_lines_id
 -tags (data array)
 -description
 -acquisition method
 -subscription from and subscription to (json extract text - see "interesting times" data row)
 -material type (derived table)
 -instanceid
 -agreementid

---
 
PO_PURCHASE_ORDERS

 -order_type
 -approval_date

-renewal_date
 -workflow-status (filter)
 -acquisition_unit_ids (data array)
-updated date and created date (data array from po_purchase_order) 
-subtotal
 -invoice_id
 -invoice_line_number
 -po_line_id

(from invoices?)

--

---

Report Criteria

Filters

--

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 line number table, join it to the invoice table (which will have the date added soon)

-start with invoice 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