Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

This is the documentation for the UXPROD-2301 RM-Invoice ClusterThe 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.

Derived Tables

invoice_lines_adjustments - use to ?

invoice_lines_fund_distributions - use to ?

instance_identifiers - use to?

instance_statistical_codes - use to?

...

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 

-id
 -paidDateapproval 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
 -VendorID
 -folioInvoiceNo
 -vendorInvoiceNo


invoice_lines


 -id
 -total
 -subtotal -fundDistributions
 -poLineID
 -adjustments
 -adjustments_total

invoice_voucher_lines

invoice_vouchers

po_lines

 -id
 -acquisitionMethod
 -orderFormat
 -poLineNumber
 -instanceID
 -agreementID
 -purchaseOrderID-invoice_id

-invoice_line_number - use to group related invoice lines
 -total


po_lines


 -id


po_order_invoice_relns

po_purchase_orders

-id
 -acqUnitIds
 -order_type
 -approvalDate

...

finance_funds

-code

-description

Aggregation

...

-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

...

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)_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

...