Managerial Accounting Division

1
Managerial Accounting Division
Training Program
Standard Operation and Maintenance Army Research
and Development Systems (SOMARDS) April 14, 2004
2
Overview

• SOMARDS is a comprehensive, computerized
  accounting system designed to serve as the
  standard system for Army logistic organizations.
  It is a complex combination of database files,
  applications, interfaces and accounting
  procedures and transactions.
• DFAS Sites in St. Louis and Rock Island are
  responsible for
• Monitoring fund availability
• Establishing and maintaining certain
  validation/edit tables
• Scheduling applications that update SOMARDS

3
Lesson Objectives

• Understand the terms used to describe the basic
  query structure
• Understand the hierarchical database structure of
  the Fund and History databases
• Understand the basic query structure
• Understand concatenated fields and their purpose

4
Hierarchical Database

• A hierarchical database is an organization of
  records structured in a parent/ descendant
  manner.
• A record may have several descendants but each
  record may have only one parent.
• Each record is made up of at least one field
  called component.
• Each component has its own name and number.
• These components form the hierarchical
  representation, known as schema.

5
Hierarchical Database

• SOMARDS has Five databases
• Database Name Abbreviation
• Fund FUND
• History HIST
• General Ledger GNLR
• Labor Master File LBRM
• Interfund Account Suspense File ISAC

6
Fund Database

• Fund database contains 596 database fields
• Fields are grouped into 21 records
• Records are arranged hierarchically in a
  parent-child structure
• When records are queried, a key database
  component from the parent record should be
  included in the Where Clause.

7
HIST Database

• The History database contains 223 database
  fields, of which six are key database fields.
• The HIST database is a compilation of the detail
  transactions that make up the summary balances in
  the FUND file.
• This database is used to research problems
  requiring detailed information.

8
Data Query Structure

• Data Query is a program that allows authorized
  users to search, retrieve, and manipulate data
  within the SOMARDS database files in order to
  create reports.
• Every basic query contains two major parts
  referred to as clauses. They are
• The Action Clause
• The Where Clause

9
Data Query Structure

• The two parts of the query must be separated by a
  space.
• All query language statements must be terminated
  by a colon.
• Example
• PR C501, C504,C507,C508 WH C501 EQ 87DA10

10
Action Clause

• Describes what kind of data is to be displayed
  and what format is desired for the data display.
• The elements of the Action Clause are
• Verbs
• Format Options
• Object list
• Ordered by (OB)

11
Action Clause

• The elements in an Action Clause must be in the
  following order
• VERB/FORMAT OPTIONS/OBJECT LIST,ORDERED BY
  ________(space)
• The Action Clause ends at the mandatory space
  between the last element in the Action Clause and
  the word WHERE which begins the Where Clause.

12
Verbs

• Mandatory part of an Action Clause. Most common
  verbs are
• PRINT (displays one value per line in a vertical
  sequence)
• LIST (displays output in a horizontal form)
• DESCRIBE (displays the components or file
  definition (schema) vertically.

13
Format Options

• Format option allows designation of how the
  output will appear.
• Format option immediately follows the Verb and
  must be placed between slashes.
• Format option is optional.
• Most common Number/Name, Stub/Stub Suppress,
  Indent/Block, Single Space/Double Space,
  Tree/Record

14
Object List

• Specifies which data components are to be
  produced.
• Object list is a mandatory element when the verb
  is either PRINT or LIST
• Example
• PR C501, C504, C507, C508 WH C501 EQ 87DA10

15
Object List Summary Functions

• Mathematical functions can be included within the
  Object List to provide summation for one
  component.
• Most common summary function is SUM
• Example PR SUM C367 WH C2 EQ 6N98202000006N206423
  
• Other summary functions are COUNT, AVG, MIN,
  MAX, SIGMA

16
Object List Calculations Functions

• Arithmetic calculations can be performed on
  components within a single record.
• Calculations include additions (), subtraction
  (-), multiplication (), and division (/).
• The desired calculations are placed in
  parentheses and included in the Object list

17
Object List Calculations Functions

• For example, the following command would provide
  dollar values for obligations, disbursements, and
  unliquidated obligations for a specific MRRN.
• PR C359,C367,(C359-C367) WH C301 EQ AA9W20002ZAN

18
Ordered by

• The Ordered BY (OB) element will sort the query
  answer in a specified manner.
• The Ordered by element must be preceded by a
  comma at the end of the Object list, followed by
  a space, then by the Where Clause.
• Example command will provide
• LI C501, C626,C634, C645,OB C501 WH C301 EQ
  AA9W2000ZAN

19
Where Clause

• The Where Clause is the qualification part of a
  command that allows the user to select only those
  data records that meet some desired condition.
• The Where Clause always begins with the word
  WHERE of the abbreviation WH.
• The Where Clause is always separated from the
  Action Clause with a space.
• A key component should be used in the query,
  otherwise the computer will search every record
  in the database.

20
Where Clause

• Numeric Where Clause Conditions
• Conditions Abbreviations
• Equal EQ or
• Not equal NE
• Greater than GT or gt
• Less than LT or lt
• Greater than or equal to GE or gt
• Less than or equal to LE or lt

21
Where Clause

• Non -Numeric Where Clause Conditions
• Conditions Example
• SPANS WH C501 SPANS 87AA00872299
• EXISTS WH C501 EXISTS
• FAILS WH C501 FAILS
• CONTAINS/CONT WH C501 CONT 8A
• NOT WH NOT C501 EQ 87DA10

22
Concatenated Fields

• Combine individual components (key and non-key)
  into one field in order to create a key
  component.
• Used in the Where Clause of a command to improve
  the query response time.
• Field positions if useful when using the CONTAINS
  in POS condition in WH clause.
• FUND C2, C601, C726, C901, C1326, C1526
• HIST C926

23
SOMARDS Access
Enter Selection QH (CECOM) and hit enter twice
24
SOMARDS Access
Enter User ID and Password then hit enter
25
Query Access
Move your cursor to ABBTSP01 P4 Domain TSO line
26
Query Access
Type AS2K for Enter Proc Name and press enter
27
Query Access
When you see three asterisks, press enter again
28
Query Access
Wait until you see the word READY and type
SOMA2 and enter
29
Query Access
When you see SYSTEM 2000 INTERACTIVE INTERFACE
READY, type the string for the database you want
to query.
30
Query Access

• Type the string for the database you want to
  query It will be one of the following
• User,somadbn is fundtxx (for aggregate totals
  and flash reports)
• User,somadbn is hist (for record details from
  Jan 1, 2000 forward)
• User,somadbn is hist99 (for record details from
  October 31, 1998 to Dec 31, 1999)
• User,somadbn is history (for record details
  from April 30, 1998 to October 1998)
• User,somadbn is histold (for records older than
  history)

31
Query Access
Make sure to type a at the end of each string
32
SOMARDS Strings in Fund Database

• TO FIND THE MRRN FOR A PARTICULAR JOAN
• Li C301 wh C501 eq JOAN
• TO FIND JOAN BY MRRN
• Li C501 wh C301 cont MRRN
• TO QUERY CONTRACT NULO
• LI c501,c1004,c1003,C1001,C1005,c1015,c1022,
• (C1015-C1022),c2, c1040, ob C1040 WH C1022 GT
  c1015 and c10 gt 1990 and c2 cont 22 in 1

33
SOMARDS Strings in Fund Database

• TO QUERY MIPR NULO
• LI C501,C927,C926,C928,C937,C946,(C937-C946),C2,C9
  50,OB C950 WH C946 GT   C937 AND C10 GT 1990
  AND C2 CONT 22 IN 1
• TO LIST ALL COMMITMENTS/OBLG/DISB BY JOAN
• Li C501, C301, C338, C355 (C338-C355), C359,
  (C355-C359), C367, (C359-C367), ob c501 wh c501
  eq JOAN

34
SOMARDS Strings in Fund Database

• TO SEE FLASH REPORT AT PROGRAM LEVEL
• Li C301, C338, C355,c359,c367 wh c301 cont MRRN
• TO SEE FLASH REPORT SHOWING ONLY RESR-OBLG-CFI
  AND RESR-OBLG-CRM
• Li C301, C359, C361, ob C301 wh C301 cont A40 in
  1

35
SOMARDS Strings in HIST Database

• TO SHOW ALL INDIVIDUAL COMMITMENT TRANSACTIONS
• LI C501, C626, C676, C677, C680, C685, ob C501 wh
  C626 cont COMT-REF-NO
• TO ORDER BY CONTRACT NUMBER UNDER THE CRN
• Li C927, C934, C933, C932, C929, C928, C930,
  C931, C939, C976, C977, C978, C980, C997, C999,
  C988, C989, C992, ob C929, C980, C997 wh C926
  cont JONO and C933 cont COMT-REF-NO

36
Query Access

• The string will provide you with raw data. There
  are no formal reports in SOMARDS. You can either
  print screen for each screen full of data or
  copy/paste into a Wordpad document for later use
• When you are finished with your queries,
• Type EXIT (dont forget the ) enter
• Type LOGOFF (no colon)
• Press F3
• Enter X and enter
• You can now close your window.

37
SOMARDS Website
https//dfas4dod.dfas.mil/centers/dfasin/systems/s
omards/