DAS LogoR⋆STARS Training Manual

Chapter #12

On-Line Inquiry In R⋆STARS


Table of Contents

ON-LINE INQUIRY

Factors That Impact Inquiries

Transaction Code Indicators

Financial Tables

Balance Types

Inquiry Types

Inquiry Year and Month

Summary and Detail Selection Options

Active or Inactive Records

Inquiry Drill Down

Drill Down Inquiry Summary

Summary of Financial Inquiries

Summary of Document Inquiries

Other Useful Inquiries


 

ON-LINE INQUIRY

The R⋆STARS On-line Inquiry features allow users to view information stored in the various financial tables.  Most inquiries fall into two categories:

◾ Financial Inquiries

◽ Focus on Appropriation, Budget, and General Ledger

◽ Often have drill down capabilities that allow users to view transaction details for amount summaries

◽ Can be understood by Non-Accountant personnel

◾ Document Inquiries

◽ Focus on information for a specific Document

◽ Frequently do not have drill down capabilities

◽ Help Accounts Receivable and Payable personnel find current account information on-line

You can find these two kinds of inquiries through the Reporting/Inquiry Menu, which can be accessed from the Main Menu.

 

Factors That Impact Inquiries

Transaction Code Indicators

Financial transactions are entered into R⋆STARS through Batches and stored in Financial Tables for later retrieval for inquiry and reporting.

For each transaction, the user indicates a Transaction Code.  The Transaction Code indicates the Financial Tables to which the transaction will post and the Balance Type the system will use to post to the table.  The 28A Transaction Code Decision Profile defines these indicators.

 S28A   UC: 01                   STATE OF OREGON              01/31/14 03:25 PM

 LINK TO:               TRANSACTION CODE DECISION PROFILE                  PROD

 

 TRAN CODE: 225 TITLE: VOUCHER PAYABLE ‑ ENCUMBERED

 GENERAL LEDGER   DR-1: 3501 CR-1: 1211 DR-2:      CR-2:

        POSTING   DR-3:      CR-3:      DR-4: 3011 CR-4: 2735

 TRANS     DOCD   DUDT I SVDT   CDOC I RDOC I  MOD I AGCY R  IDX    PCA   COBJ R

  EDIT     AOBJ   RVRS N  PDT R   CI N 1099 R WARR   INVC   VNUM R VNAM R VADD R

  INDS:   DMETH R APN# R FUND R  GLA N  AGL N GRNT   SUBG   PROJ   MULT   G38# N

 POST SEQ: 3 REG NO: 2 WW IND: 1 D/I: D WAR CANCL TC: 385 PYTC:     FUTMY: Y

 GEN- TC:      DT:    ACCR TC:     TR TC:     INTERFACE IND:

 PAY LIQ TC: 380 PAY RED TC: 390 CUM POST IND: Y BAL TC:       A/S DOC AMT: +

            A/S  BT  MATCH GLA DOC     A/S  BT  MATCH GLA DOC

         DF: +   03    M    8   2       +   04    M    8   2

 FILE    AP: -   18                     +   17

 POSTING AB: -   18                     +   17

 INDS:   CC:

         GP: -   18                     +   17

         PJ: -   18                     +   17

     AGY GL:                                             STATUS CODE: A

  EFF START DATE: 06011990   EFF END DATE:            LAST PROC DATE: 09022003

 Z06 RECORD SUCCESSFULLY RECALLED

 F1-HELP F3-DEL F5-NEXT F6-28B F9-INT F10-SAVE F11-SAVE/CLEAR ENT-INQ CLEAR-EXIT

 

Financial Tables

The financial tables include:

Accounting Event (AE)

Contains the full accounting classification for each financial transaction that posts to all of the other financial tables.

Agency Budget (AB)

Contains agency budget balances and revenue, expenditure, and encumbrance balances on a program, organization, fund, and/or object basis.

Appropriation (AP)

Contains appropriation balances and revenue, expenditure, and encumbrance balances.

Cash Control (CC)

Contains the balance of available cash by fund and provides the ability to control and monitor cash disbursements at a defined level of detail.

Document Financial Table (DF)

Contains the balance of each outstanding pre-encumbrance, encumbrance, accounts receivable, and due to/from account.

General Ledger Detail (GL)

Contains a balance by the lowest level in the classification structure for all financial transactions that post to selected accounts, usually nominal accounts.

Grant Table (GP)

Contains the budgets, expenditures, and revenues of a grant.

Summary General Ledger (GS)

Contains summarized balances for each general ledger (asset, liability, fund balance, budgetary and nominal) account.

Project Table (PJ)

Contains the budgets, expenditures, and revenues of a project.

 

Balance Types

Each financial table, except the Accounting Event (AE), contains several balance types.  The program uses the balance types to accumulate all the financial data together, regardless of general ledger account.

Balance types are defined on the D05 Balance Type Profile, subdivided by TABLE ID (for example, DF).  Some Balance Types that appear on several tables include:

12

Cash Revenue

18

Encumbrances Outstanding

14

Accrued Revenue

20

Transfer in - Cash

15

Cash Expenditures

21

Transfer out - Cash

17

Accrued Expenditures

Accrued Revenue is revenue that has been earned but not yet received.  These include:

◾ Accounts receivable due from other agencies

◽ Accounts receivable for external grant and project billings

◽ Taxes receivable

When the accrued revenue is actually received, the revenue is subtracted from Accrued Revenue and added to Cash Revenue.  Not all revenue will be accrued prior to receipt.

Outstanding Encumbrances reflect the intention to purchase goods or services.  These include:

◾ Purchase Orders and Requisitions posted to R⋆STARS from ADPICS

◽ Personal Services Contracts

◽ Leases and Rental Agreements

When the agency processes a vendor’s invoice and creates a Voucher Payable in either ADPICS or R⋆STARS, the system subtracts the amount from Outstanding Encumbrances and adds it to Accrued Expenditures.

When the warrant is printed, the system subtracts the amount from Accrued Expenditures and adds it to Cash Expenditures.  Not all cash expenditures will have been accrued prior to payment.

 

Inquiry Types

On most Inquiry screens there is an Inquiry Type field.  This field defines the range of activity that the system will display.

In general terms, the first character of the value defines the year and the second character defines the activity; for example,

First Character:

Y

Prior Year

M

Current Year

Second Character:

A

Activity for a Month

Y

Activity for a Year

C

Cumulative

Specific Inquiry Types include:

MA

Monthly activity for the selected month and year

YA

Prior Year Activity – summarizes the prior year’s activity

MY

Month to Date – summarizes the current year up to the selected month

YY

Prior-Prior Year – summarizes activity 2 years prior to the selected year

MC

Month cumulative to Date – summarizes all of the years up to the current month (default)

YC

Prior year cumulative (prior year balance) – summarizes all years up to the end of the prior year

 

Inquiry Year and Month

The Inquiry Year refers to the fiscal year.  For example, July 1, 2013, to June 30, 2014 is Fiscal Year 14.  The Effective Date on the transaction determines the transaction’s fiscal year.

The Inquiry Month field refers to the fiscal month, not calendar month.  This means that month 03 is September, not March.

 

Summary and Detail Selection Options

Some inquiry screens provide two selection options for viewing data – summary and detail.

The detail (D) is the default value.  You may use the   F5  -NEXT to scroll through the records with the detail option.  For inquiries with drill down capabilities, you must select the detail option to drill down.

When the summary (S) option is selected, R⋆STARS summarizes and displays all of the records matching the entered elements.  The system adds all of the records together by balance type.  You may not use the   F5  -NEXT or drill down with the summary option.

Let’s use the 89 Summary General Ledger Account inquiry as an example.  You enter the agency, comptroller or Agency gl and state fund group, appr fund, or fund.  If the detail/summary is set to S, the system will display the summary for all of the activity for these elements.  If the detail/summary is set to D, the system will summarize the activity for a particular Transaction Code (displayed in the TC field).  With this option, you can scroll through the other T-Codes that have been used to record activity to this agency, general ledger, and fund combination.

 

Active or Inactive Records

To conserve computer storage space and processing time, the State has begun to implement the Purge subsystem of SFMS.  In the first step of this process, the system moves records from the Active to the Inactive file.  The State has begun running the purge for the Accounting Event Table (see information on the Financial Tables above).  It will run the Friday after the close of each Accounting Month.  Currently, the transactions for the prior two closed months and the current open month will remain in the Active file and all others will be in the Inactive file.

Records that meet the following criteria will remain in the Active file:

◾ Warrant writing indicator is not blank and warrant has not been written

◽ Fiscal Month is not closed

◽ Any transaction from the same batch is still on the 530 View Batch Header screen

◽ Any transaction from the same batch is still on the Posting Driver table

◽ The Current Document is still on the Document Financial Table

◽ The Reference Document is still on the Document Financial Table

Several inquiry screens have a field to indicate whether you want the inquiry to include records from the Active (A) or Inactive (I) file.  At this time, only the inquiries that access the Accounting Event Table will be impacted.  Those inquiries that include transactions from the Accounting Event Table Inactive files will take longer to complete.

 

Inquiry Drill Down

Drill-Down Inquiry is an R⋆STARS feature that allows a user to start at a summary inquiry and drill down to the individual transaction detail level by simply pressing a few keys.

This feature is best explained with a brief demonstration.

Link to the 62 Appropriation Financial Inquiry Screen.  If you were a manager and wanted to know the details that made up a specific Balance Type, you would do the following:

 S062   UC: 10                   STATE OF OREGON              01/31/14 03:22 PM

 LINK TO:              APPROPRIATION FINANCIAL INQUIRY                     PROD

 AGY: 101 APPN YEAR: 15 APPN #: 84017   GF APPROPRIATION

 APPR FUND: 8000  FUND:         COMP SRCE/GRP:      OBJ/COMP OBJ:

 PRG CD: 0502  LVL: 02 OREGON COMMISSION  ORG CD:      LVL:

  INQ TYPE: MC  INQ YEAR: 14  INQ MONTH: 07 DETAIL/SUMMARY: D

 CASH BASIS BUD:             246,105.63         ADJ BUD:           1,339,094.00

 ACCR BASIS BUD:             245,919.90  REM APPN ALLOT:             112,158.48

  ENC BASIS BUD:             238,183.48   ALLOT TO BUD % :  90.50

  EXP TO BUD % :  81.60                   UNSCHED TO BUD % :   7.60

 BT    TITLE                   AMOUNT     BT    TITLE                   AMOUNT

 01 ORIG BUDGT           1,513,682.00

 02 REVISIONS-              63,729.00-

 08 UNSCHED AP             110,859.00

 15 CASH EXPEN           1,092,988.37

 17 ACCRD EXPE                 185.73

 18 ENCUM OUTS               7,736.42

 23 ALLOTMENT-           1,213,069.00

 30 UNITS ACCU              22,471.07

 

 

 

 

 F1-HELP   F2-DOC INQ   F5-NEXT   F9-INTERRUPT   ENTER-INQUIRE   CLEAR-EXIT

Step 01: Place the cursor in Balance Type 15, Cash Expenditures, and press   F2  -DOC INQ.  The 11 Document Inquiry Screen will display.

The DETAIL/SUMMARY option must be set to D.

 S011   UC: 10                   STATE OF OREGON              01/31/14 03:23 PM

 LINK TO:      ACTIVE       DOCUMENT SUMMARY INQUIRY                       PROD

 

 AGENCY: 101   FISCAL YR: 14  MO: 07  DAY: 31  BT: 15 CASH EXPEN

                  BT AMT:           1,092,988.37

      AY AFND PGMC APPN#

 KEY: 15 8000 0502 84017

 EFFECTIVE DATE DOC AGY   DOC NUMBER     REFERENCE DOC      BALANCE/AMOUNT

 20130131          101     PA101011                               90458.05

 20130131          101     PM121201                                 170.57

 20110128          101     BT005815       VP052390                 1389.75

 20130128          101     ZE10128R       VP052971                   86.26

 20130128          101     ZE10128R       VP052972                  116.30

 20130128          101     ZE10128R       VP052973                  711.42

 20130128          101     ZE10128R       VP052974                   85.20

 20130128          101     ZE10128R       VP052975                  137.38

 20130128          101     ZE10128R       VP052976                  659.23

 20130128          101     ZE10128R       VP052977                  253.26

 20130128          101     ZE10128R       VP052978                   13.84

 20130128          101     ZE10128R       VP052979                   36.64

  

 Z06 RECORD SUCCESSFULLY RECALLED

 

 F1-HELP F2-TRANS INQ F3-PRIOR INQ F7-PRIOR F8-NEXT F9-INTERRUPT ENTER-INQUIRE

Step 02: Place the cursor in the effective date of the third document and press   F2  -TRANS INQ.  The 60 Transaction Summary Inquiry will display.

Use   F7  -PRIOR and   F8  -NEXT will scroll back and forth through the list of transactions.

If you are looking for a transaction in a particular month or day of a month, you can enter the year, month, and day in the FISCAL YR, MO and DAY fields.  Press  Enter  to perform the inquiry.  The system will display these transactions at the beginning of the list.

 S060   UC: 10                   STATE OF OREGON              01/31/14 03:25 PM

 LINK TO:      ACTIVE    TRANSACTION SUMMARY INQUIRY                      PROD

 

     AGENCY: 101   FISCAL YEAR: 14    EFF DATE: 20130128  SFX: 002

   REC TYPE: A         DOC AGY: 101     DOC NO: BT005815  REF DOC: VP052390

 

       AY AFND PGMC APPN#

 KEYS: 15 8000 0502 84017

 SFX   EFF DATE   VENDOR NO/MC    VENDOR NAME          TC            AMOUNT  R

 002   20130128  1411881896 001  US BANK CORPORATE PA  415            74.55

 003   20130128  1411881896 001  US BANK CORPORATE PA  415            14.96

 004   20130128  1411881896 001  US BANK CORPORATE PA  415             6.17

 005   20130128  1411881896 001  US BANK CORPORATE PA  415             4.14

 006   20130128  1411881896 001  US BANK CORPORATE PA  415            42.51

 007   20130128  1411881896 001  US BANK CORPORATE PA  415            11.93

 008   20130128  1411881896 001  US BANK CORPORATE PA  415           191.69

 016   20130128  1411881896 001  US BANK CORPORATE PA  415             0.09

 017   20130128  1411881896 001  US BANK CORPORATE PA  415             0.08

 018   20130128  1411881896 001  US BANK CORPORATE PA  415             0.97

 019   20130128  1411881896 001  US BANK CORPORATE PA  415             0.54

 025   20130128  1411881896 001  US BANK CORPORATE PA  415             9.15

 

F1-HELP  F2-AE DETAIL  F3-PRIOR INQ  F7-PRIOR  F8-NEXT  F9-INTER  ENTER-INQUIRE

Step 03: Place the cursor in the suffix of the first transaction and press   F2  -AE DETAIL.  The 84 Accounting Event Record will display.  This screen gives all the information entered on the original transaction.

Press   F7  -PRIOR and   F8  -NEXT to scroll through any additional screens.

The system displays all transactions for a document in numeric order by the suffix (SFX) number.  You can enter a SFX number and press  ↵Enter . The system will display that transaction first.

 S084   UC: 10                   STATE OF OREGON              01/31/14 03:26 PM

 LINK TO:                ACCOUNTING EVENT RECORD INQUIRY                   PROD

 ACTIVE

 BATCH: AGENCY 101 DATE 012814 TYPE 4 NO 600 SEQ NO 00006 REC TYPE: A STATUS:

 

 GL: DR1 3500 CR1 0070 DR2      CR2      DR3      CR3      DR4      CR4

 DOC AGY: 101  DOC DATE:         EFF DATE: 012814  DUE DATE: 012814

  SERV DATE:        CUR DOC/SFX/CLASS: BT005815 002     MOD:

                    REF DOC/SFX/CLASS: VP052390 001     AGENCY: 101

   TRANS CODE: 415 INDEX: 20000  PCA: 47027 AY: 15     COMP/AGY OBJ: 4104 4104

       AMOUNT:          74.55   RVS:   DISCOUNT:            .00   FO:   PDT:

           CI:   PROP #:            1099:     INV-NO: 01/25/2014     DT: 012514

 VEND/MC: 1411881896 001 DESC: ALLOCATE EXPENDITURES

 NAME: US BANK CORPORATE PAYMENT SYSTEMS                CONT NO:

 CITY: ST LOUIS             ST: MO ZIP: 63179 0428          RTI: RHBBFG

 PMT-NO:           DT:        AP NO: 84017 FUND: 0014    AGY GL:

  GRANT NO/PH: 412694 27    SUB GRANTEE:                 PROJ NO/PH:

 MPCD:                AGY CD-1:      2:      3: 04000     CASH FUND:

 G38-TRANSFER:                  PMT TYPE:   INT TM:  0.000 BANK:

 DISC-DT:        TM:    PEN-DT:        TM:    PEN AMT:            .00

 LAST PROC - DT: 01282011 TIME: 0936 ORIG SEQ NO: 00004   APPROVAL DT: 012814

 Z06 RECORD SUCCESSFULLY RECALLED

 

 F1-HELP   F2-85   F3-PRIOR INQ   F4-PRIOR   F5-NEXT  F7-86  F9-INT ENTER-INQ

Step 04:  F3  -PRIOR INQ on each of these screens will take you back one screen.    F9  -INT will take you back to the screen where you began the drill down – in this case, the 62 Appropriation Financial Inquiry.

 

Drill Down Inquiry Summary

Image #12-01 - Drill Down Inquiry Graphic

 

Summary of Financial Inquiries

Following is a list of the Financial Inquiry Screens and a brief description of each screen.  You can access these inquiries through the Financial Inquiry Menu.

Title

Description

57 Cash Control Summary

Displays information in the Cash Control Table.  Summarizes the information by Agency, or Agency and Fund, or Appropriated Fund.

58 Agency/Fund Summary

Displays information in the Agency Fund Table.  The Transaction Code File Posting Indicators for the AP Table (28A Profile) control the postings to this table.

61 Agency Budget Financial

Displays information in the Agency Budget Table by Agency.

62 Appropriation Financial

Displays information in the Appropriation Table.  It displays information by Agency and Appropriation.

63 Cash Control

Reads the Cash Control Table.

65 General Ledger Record

Reads the General Ledger Table and displays information by Agency and General Ledger Account.

66 Grant Record

Reads the Grant Financial Table and displays information by Agency and Grant Number.

69 Agency/Fund Record

Reads the Agency Fund Financial Table.  The Transaction Code File Posting Indicators for the AP Table (28A Profile) control the postings to this table.

80 Project Financial

Reads the Project Financial Table and displays information by Agency and Project Number.

89 Summary General Ledger

Reads the Summary General Ledger Table and displays information by Agency and General ledger and either State Fund Group or Appropriated Fund.

 

Summary of Document Inquiries

Following is a list of the Document Inquiry Screens.  You can access these inquiries through the Document Inquiry Menu.

Title

Description

56 Project Transaction

Reads the Accounting Event Table and displays supporting detail financial information for the Agency, Project, and Balance Type.

59 Vendor Document

Reads the Document Financial Table and lists all documents related to a particular vendor in chronological order (oldest first).  Displays information for specific document types for a certain fiscal year that have a balance greater than zero.

64 Document Record

Reads the Document Record Table and displays information by Agency and Document Number.

67 Grant Transaction

Reads the Accounting Event Table and displays supporting detail financial information for the Agency, Grant, and Balance Type.

68 Contract Record

Reads the Contract Table and displays information by Agency and Contract number.

84 Accounting Event

Reads the Accounting Event Table and displays the detail accounting classification elements for each financial transaction.

85 Vendor Transaction

Reads the Accounting Event Table and provides information by Vendor Number and Document Type.  Information may be restricted by Mail Code (MC), Agency, Invoice Number, Appropriated Fund.

86 Document Transaction

Reads the Accounting Event Table and provides information by Agency and Document Number.

See the section on Financial Tables earlier in this chapter for information on the data stored in each table.

In the following chapters, we will look at each of the inquiries in more detail.

 

Other Useful Inquiries

Title

Description

14 Linked Cash/GF Appn Financial Inquiry

Combines the general fund appropriation balance types (left side of the screen) and the federal funds cash control balance types (right side of the screen) to determine the available linking balance (only valid for agencies using linking).

105 Transaction Detail by Project and AOBJ/AGL Inquiry

Summarizes transactions by project phase, agency object, and agency general ledger.  Displays the transactions within each category by batch date (most recent first) and current document number and suffix (alpha/numeric order).

106 Transaction Detail by AOBJ/AGL and Project Inquiry

Summarizes transactions by agency object, agency general ledger, and project and phase.  Displays the transactions within each category by PCA, T-Code, service date, batch date, and current document number and suffix.


DAS Logo