Created by Miki Yanagi, last modified on Mar 27, 2019

Search Tags: UMS05, UM, NSFA, Grants and Contracts 

JIRA Reference:   DM-263 - NSFA G&C for Database Closed

Report Purpose

This report is a data dump for grants and contracts by program and project for the University of Maine, College of Natural Sciences, Forestry, and Agriculture (NSFA). NSFA Office uses Access database and this data will be exported to the database.

Report Location

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

Report Name

  • UMS_NSFA_GRANT_CONT              (Fund 04, 20, & 24, Excluding Gifts)
  • UMS_NSFA_GRANT_CONT_DB        (For NSFA Database)

Report Type

Query

Prompts Required

For information on field values to enter, click the name of the prompt.

UMS_NSFA_GRANT_CONT:

UMS_NSFA_GRANT_CONT_DB:

Data Fields Generated

Fund CodeDept_Cr_2Dept Cr 3DeptidProject IdDESCRProj Fed Agcy DProj Gc Type DProj Grant NProj Mgr NameProject StatusProj Start DtProj End DtCB RevCB Tsf InCB Total Rev & TSFCB Fac SalCB Non Fac SalCB WagesCB StudentsCB BenefitsCB Student AidCB Supp & ServCB CreditsCB CapitalCB TsfCB Total DirectCB FA SponsorCB FA UMaineCB Offset OMCB Net FAPTD RevPTD Tsf InPTD Total Rev & TsfPTD Fac SalPTD Non Fac SalPTD WagesPTD StudentsPTD BenefitsPTD Student AidPTD Supp & ServPTD CreditsPTD CapitalPTD TsfPTD Total DirectPTD FA SponsorPTD FA UMainePTD Offset OMPTD Net FABalance Rev & TsfBalance DirectBalance FABalance OffsetBalance Direct CSBalance Net


Tables Used

Criteria Used

For UMS_NSFA_GRANT_CONT:

  • DEPTID in tree (UMSYS,,UMS_DEPT_ORG,2018-07-01,5NSFA) OR (5CLIMATE_CHG_INST, 5CTR_AGING, 5AQUACULT_RSRCH_INST, 5FOREST_BIOPRODUCTS, 5AQ_MAR_SCI_MEIF, 5BIOTECH_MEIF,5ENV_MEIF, 5ME_FISH_RESEARCH, 5SEA_GRANT, 5CENTER_SUSTAIN_FOR, 5M_SMITH_CTR_PUB_POL)
  • BUSINESS_UNIT= UMS05
  • FUND_CODE in list ('04', '20', '24')

  • PROJECT_ID > 0

  • UM_PROG_CAT1 <> 'GIFTS'

For UMS_NSFA_GRANT_CONT_DB:

  • DEPTID in tree (UMSYS,,UMS_DEPT_ORG,2017-07-01,5NSFA) OR (5CLIMATE_CHG_INST, 5CTR_AGING, 5AQUACULT_RSRCH_INST, 5FOREST_BIOPRODUCTS, 5AQ_MAR_SCI_MEIF, 5BIOTECH_MEIF,5ENV_MEIF, 5ME_FISH_RESEARCH, 5SEA_GRANT, 5CENTER_SUSTAIN_FOR, 5M_SMITH_CTR_PUB_POL)
  • BUSINESS_UNIT= UMS05
  • FUND_CODE in list ('04', '20', '24')

  • (PROGRAM_CODE < 0 and PROJECT_ID >0 ) OR (PROJECT_ID =5250325) OR (PROJECT_ID =5250329) 


Calculation Used

FieldCalculation
CB RevSUM(CASE WHEN Account >= '40000' AND Account <= '49999' THEN CURRENT_BUDGET ELSE 0 END)
CB Tsf InSUM(CASE WHEN Account >= '80000' AND Account <= '89000' THEN CURRENT_BUDGET ELSE 0 END)
CB Total Rev & TSFCB Rev-CB Tsf In
CB Fac SalSUM(CASE WHEN Account >= '50000' AND Account <= '50999' THEN CURRENT_BUDGET ELSE 0 END)
CB Non Fac SalSUM(CASE WHEN Account >= '51000' AND Account <= '51999' THEN CURRENT_BUDGET ELSE 0 END)
CB WagesSUM(CASE WHEN Account >= '52000' AND Account <= '52999' THEN CURRENT_BUDGET ELSE 0 END)
CB StudentsSUM(CASE WHEN Account >= '53000' AND Account <= '53899' THEN CURRENT_BUDGET ELSE 0 END)
CB BenefitsSUM(CASE WHEN Account >= '53900' AND Account <= '54999' THEN CURRENT_BUDGET ELSE 0 END)
CB Student AidSUM(CASE WHEN Account >= '55100' AND Account <= '55999' THEN CURRENT_BUDGET ELSE 0 END)
CB Supp & ServSUM(CASE WHEN Account >= '60000' AND Account <= '65599' THEN CURRENT_BUDGET ELSE 0 END)
CB CreditsSUM(CASE WHEN Account >= '65800' AND Account <= '65899' THEN CURRENT_BUDGET ELSE 0 END)
CB CapitalSUM(CASE WHEN Account >= '67000' AND Account <= '67999' THEN CURRENT_BUDGET ELSE 0 END)
CB TsfSUM(CASE WHEN Account >= '70000' AND Account <= '79999' THEN CURRENT_BUDGET ELSE 0 END)
CB Total DirectCB Benefits-CB Capital-CB Credits-CB Fac Sal-CB Non Fac Sal-CB Student Aid-"CB Supp & Serv"-CB Tsf-CB Students-CB Wages
CB FA SponsorSUM(CASE WHEN Account >= '65700' AND Account <= '65719' AND Fund Code = '20' THEN CURRENT_BUDGET ELSE 0 END)
CB FA UMaineCB FA UMaine New Method-CB FA UMaine Old Method
CB Offset OMSUM(CASE WHEN Account = '65710' AND Fund Code = '20' THEN CURRENT_BUDGET ELSE 0 END)
CB Net FACB FA UMaine-CB FA Sponsor-CB Offset OM
PTD RevSUM(CASE WHEN Account >= '400000' AND Account <= '49999' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD Tsf In SUM(CASE WHEN Account >= '80000' AND Account <= '89999' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD Total Rev & TsfPTD Rev-"PTD Tsf In "
PTD Fac SalSUM(CASE WHEN Account >= '50000' AND Account <= '50999' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD Non Fac SalSUM(CASE WHEN Account >= '51000' AND Account <= '51999' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD WagesSUM(CASE WHEN Account >= '52000' AND Account <= '52999' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD StudentsSUM(CASE WHEN Account >= '53000' AND Account <= '53899' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD BenefitsSUM(CASE WHEN Account >= '53900' AND Account <= '54999' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD Student AidSUM(CASE WHEN Account >= '55100' AND Account <= '55999' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD Supp & ServSUM(CASE WHEN Account >= '60000' AND Account <= '65599' THEN ACTUALS_PTD-ENCUMBRANCE ELSE 0 END)
PTD CreditsSUM(CASE WHEN Account >= '65800' AND Account <= '65899' THEN ACTUALS_PTD-ENCUMBRANCE ELSE 0 END)
PTD CapitalSUM(CASE WHEN Account >= '67000' AND Account <= '67999' THEN ACTUALS_PTD-ENCUMBRANCE ELSE 0 END)
PTD TsfSUM(CASE WHEN Account >= '70000' AND Account <= '79999' THEN ( ACTUALS_PTD-ENCUMBRANCE ) ELSE 0 END)
PTD Total DirectPTD Benefits-PTD Capital-PTD Credits-PTD Fac Sal-PTD Non Fac Sal-PTD Student Aid-PTD Students-"PTD Supp & Serv"-PTD Tsf-PTD Wages
PTD FA SponsorSUM(CASE WHEN Account >= '65700' AND Account <= '65719' AND Fund Code = '20' THEN ACTUALS_PTD ELSE 0 END)
PTD FA UMainePTD FA UMaine New Method-PTD FA UMaine Old Method
PTD Offset OMSUM(CASE WHEN Account = '65710' AND Fund Code = '20' THEN ACTUALS_PTD ELSE 0 END)
PTD Net FAPTD FA UMaine-PTD FA Sponsor-PTD Offset OM
Balance Rev & TsfCB Total Rev & TSF-"PTD Total Rev & Tsf"
Balance DirectCB Total Direct-PTD Total Direct
Balance FACB Net FA-PTD Net FA
Balance OffsetCB FA Offset-PTD FA Offset
Balance Direct CSCB Direct CS-PTD Direct CS
Balance NetBalance Direct-"Balance Rev & Tsf"-Balance FA-Balance Offset-Balance Direct CS
CB FA UMaine Old MethodSUM(CASE WHEN Account = '65710' AND Project Id <> '5405084' THEN CURRENT_BUDGET ELSE 0 END)
CB FA UMaine New MethodSUM(CASE WHEN Account >= '65711' AND Account <= '65715' AND Project Id <> '5405084' THEN CURRENT_BUDGET ELSE 0 END)
PTD FA UMaine Old MethodSUM(CASE WHEN Account = '65710' AND Project Id <> '5405084' THEN ACTUALS_PTD ELSE 0 END)
PTD FA UMaine New MethodSUM(CASE WHEN ( Account >= '65711' AND Account <= '65715' ) THEN ACTUALS_PTD ELSE 0 END)
CB Direct CSSUM(CASE WHEN Account >= '65720' AND Account <= '65730' THEN CURRENT_BUDGET ELSE 0 END)
PTD Direct CSSUM(CASE WHEN Account >= '65720' AND Account <= '65730' THEN ACTUALS_PTD ELSE 0 END)
CB FA OffsetSUM(CASE WHEN Account = '65719' THEN CURRENT_BUDGET ELSE 0 END)
PTD FA OffsetSUM(CASE WHEN Account = '65719' THEN ACTUALS_PTD ELSE 0 END)

New to PeopleSoft Query?

Click on Getting Started and begin with the Query Basics video

Need help running a report?

Click on Running Reports with Query Viewer & Query Manager or How to Run an SQR for simple how-to guides for running reports. If you've followed the path to an SQR location and cannot see the report, your PeopleSoft settings may need adjustment. Please contact the Change Management /Systems Security Analyst at 581-5420.

HELP IS AT HAND!

Round, red help button. Click to email DARTS.Question, comment, critique? We are here to help and we strive to make our site a comprehensive, user friendly experience. Contact us at DARTS@maine.edu.