1 / 37

Managerial Accounting Division

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

niveditha
Télécharger la présentation

Managerial Accounting Division

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 NameAbbreviation 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 > Less than LT or < Greater than or equal to GE or >= Less than or equal to LE or <=

  21. Where Clause • Non -Numeric Where Clause Conditions: Conditions:Example: SPANS WH C501 SPANS 87AA00*872299: 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,soma:dbn is fundtxx: (for aggregate totals and flash reports) • User,soma:dbn is hist: (for record details from Jan 1, 2000 forward) • User,soma:dbn is hist99: (for record details from October 31, 1998 to Dec 31, 1999) • User,soma:dbn is history: (for record details from April 30, 1998 to October 1998) • User,soma:dbn 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,C950,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: (don’t 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/somards/

More Related