DARTS : UMS_DTS_SPAN_FISCAL_YR_PROMPTS - Template query to span fiscal years in finance

Report Purpose

This is a public query in Finance that allows the user to aggregate data across months or fiscal years as need. It is useful for managing grant and contract funds where the term of the agreement spans University fiscal years.

The attached screenshots show the criteria page of the query and illustrate the build process and the SQL that is created.

Report Location

MyCampus > MaineStreet > Enterprise Applications > Financials > Main Menu > Reporting Tools > Query > Query Manager or Query Viewer

Report Name

UMS_DTS_SPAN_FISCAL_YR_PROMPTS

Report Type

Query

Prompts Required

Data Fields Generated

Tables Used

UM_GLQ_REPRTING - Ledger Data

Criteria

The setup for this criteria is as follows in the PS Query tool.

SQL

Note that:

• “not less than”  is the same as “>=”
• “not greater than”  is the same as “<=”

And grouping criteria precisely is very critical to proper operation. See color coded parenthesis below.

FROM PS_UM_GLQ_REPRTING A

  WHERE ( A.DEPTID = '8750110'

     AND ( ( A.FISCAL_YEAR = :1

     AND A.ACCOUNTING_PERIOD >= :2 )

     OR A.FISCAL_YEAR > :1 )

     AND ( ( A.FISCAL_YEAR = :3

     AND A.ACCOUNTING_PERIOD <= :4 )

     OR A.FISCAL_YEAR < :3 )

     AND A.ACCOUNTING_PERIOD <> 0

     AND A.UM_ACTUALS_CURR <> 0 )