DARTS : UMS05 (UM) NSFA Farm Reports

Report Purpose

These reports provide GL data and HR journal data for the University of Maine, College of Natural Sciences, Forestry, and Agriculture (NSFA), Farm accounts only.

Report Location

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

Report Name

• UMS_NSFA_FARM_DETAIL
• UMS_NSFA_FARM_HR_DETAIL
• UMS_NSFA_FARM_GL_SUM

Report Type

Query

Prompts Required

For UMS_NSFA_FARM_DETAIL:

• JOURNAL_DATE From 
• JOURNAL_DATE To
• FUND_CODE
• DEPT_CR_3 (will be replaced)

For UMS_NSFA_FARM_HR_DETAIL:

• JOURNAL_DATE From 
• JOURNAL_DATE To
• FUND_CODE
• DEPT_CR_3 (will be replaced)

For UMS_NSFA_FARM_GL_SUM:

• Fiscal Year
• Accounting Period
• FUND_CODE
• DEPT_CR_3
• Program Code
• Project ID
• Account FROM 
• Account TO
• Deptid

Data Fields Generated

For UMS_NSFA_FARM_DETAIL:

For UMS_NSFA_FARM_HR_DETAIL:

For UMS_NSFA_FARM_GL_SUM:

Tables Used

For UMS_NSFA_FARM_DETAIL:

• A. UM_GLQ_REPRTING - Ledger Data
• B. UM_JRNL_APPR_VW - Journal, approval, cf descr
• C. UM_GLQ_ACCTA - GL reporting account attribute

For UMS_NSFA_FARM_HR_DETAIL:

• A. UM_GLQ_REPRTING - Ledger Data
• B. UM_HR_ACCTG_VW- HR Acctg Line with cf descr 
• C. UM_GLQ_ACCTA - GL reporting account attribute

For UMS_NSFA_FARM_GL_SUM:

• UM_GLQ_REPRTING - Ledger Data

Criteria Used

For UMS_NSFA_FARM_DETAIL:

• A.ACCOUNT (Less than 49999) OR (between 55000 and 89999) OR (54800, 54810)
  
• B.LEDGER_GROUP = 'ACTUALS'
• B. KK_AMOUNT_TYPE - Commitment Control Amount Type NOT in list ('2','3') -- 2= Encumbrance and 3= Pre-encumbrance
• A. ACCOUNTING_PERIOD between 1 and 12
• FARM_accounts * (Please see below)
  

For UMS_NSFA_FARM_HR_DETAIL:

• A. ACCOUNT between 50000 and 53999
• B. LEDGER_GROUP = 'ACTUALS'
• B. KK_AMOUNT_TYPE - Commitment Control Amount Type NOT in list ('2','3') -- 2= Encumbrance and 3= Pre-encumbrance
• A. ACCOUNTING_PERIOD between 1 and 12
• A.DEPTID in tree (UMSYS,,UMS_DEPT_ORG,2017-07-01,5NSFA)
• FARM_accounts * (Please see below)

For UMS_NSFA_FARM_GL_SUM:

• ACCOUNT between 10000 and 89999
• BUSINESS_UNIT= UMS05
• BASE_BUDGET <> 0 OR UM_CURR_BUDGET  <> 0 OR UM_ACTUALS_CURR <> 0 OR YTD_ACTUAL<>0 OR PTD_ACTUAL<>0 OR UM_TOT_ENCUMBRANCE <>0 OR Budget Balance <> 0
• FARM_accounts * (Please see below)

Special Expression

FARM_accounts:

CASE WHEN A.DEPTID = '5200104' and A.FUND_CODE = '00' and A.PROGRAM_CODE =' ' and A.PROJECT_ID =' ' THEN 'Y'
WHEN A.DEPTID in ('5200923','5200924', '5200926', '5200927', '5200928') and A.FUND_CODE = '23' and A.PROGRAM_CODE = '58814' and A.PROJECT_ID =' ' THEN 'Y'
WHEN A.DEPTID = '5200982' and A.FUND_CODE = '20' and A.PROGRAM_CODE = '52659' and A.PROJECT_ID =' ' THEN 'Y'
WHEN A.DEPTID = '5200982' and A.FUND_CODE = '12' and A.PROGRAM_CODE =' ' and A.PROJECT_ID in ('5500189', '5500192', '5500250', '5500279', '5500280', '5500281', '5501190', '5501192', '5501193') THEN 'Y'
WHEN A.DEPTID = '5200983' and A.FUND_CODE = '20' and A.PROGRAM_CODE ='52796' and A.PROJECT_ID =' ' THEN 'Y'
WHEN A.DEPTID = '5200984' and A.FUND_CODE = '20' and A.PROGRAM_CODE ='55187' and A.PROJECT_ID =' ' THEN 'Y'
WHEN A.DEPTID = '5201983' and A.FUND_CODE = '20' and A.PROGRAM_CODE in ('52985', '54656') and A.PROJECT_ID =' ' THEN 'Y'
WHEN A.DEPTID = '5205989' and A.FUND_CODE = '20' and A.PROGRAM_CODE ='50349' and A.PROJECT_ID =' ' THEN 'Y'
ELSE 'N' END