1 / 22

AgVantage Software’s Most Commonly Used Queries Lab - Part 1

AgVantage Software’s Most Commonly Used Queries Lab - Part 1. Presented by: Mike Smark AgVantage National Conference 2010.

nida
Download Presentation

AgVantage Software’s Most Commonly Used Queries Lab - Part 1

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. AgVantage Software’s Most Commonly Used Queries Lab - Part 1 Presented by: Mike Smark AgVantage National Conference 2010

  2. AGENDAQuick basic overview of Query processFiles/Libraries and StructureWork through several common queriesYou take it from there !!

  3. Why Query? Queries allow you to access information that you cannot currently access/view using standard AgVantage reports. It is a supplemental, useful tool for pulling data into files and reports based on the information you want to see.

  4. QueryOverview Quick Utility Efficient Report/Files Yes! Impress your manager!

  5. TerminologyLibraries, Files, Records and Fields I-Series – Stores information much like a file cabinet Libraries – AgVantage stores your information in a company library named CO*DATA where * is your company letter, number or a symbol. iSeries COUDATA COBDATA COSDATA

  6. TerminologyLibraries, Files, Records and Fields • Files – A group of records with related information on one topic similar to a file folder. • Master Files – U4CSTMR Customer Master • Transaction Files – U4TRND A/R Transaction COUDATA

  7. TerminologyLibraries, Files, Records and Fields Records – A smaller grouping of information on one topic within a file. In the customer file, each record contains information about one customer. Similar to a piece of paper Fields – One piece of information within a record. Similar to the words on a piece of paper Customer name, address, etc. Doe Jane U4CSTMR

  8. Example • Library Co*data – Data library which contains all AgVantage files • Files U4CSTMR – Customer Master • Records Each individual customer’s information • Fields CSLKNM – Customer Lookup Name

  9. Query Libraries Recommendations: Create your own library for queries. CRTLIB command Use QRY at the end of the name. Ex. “COOP”QRY or SMARKQRY (own server) Most already have “base” Query Library ASP customers (Use company name/abbrev, then your name in description) Common AgVantage Query Libraries GRNQRYV65 (Version 7 not there yet) SYSQRYV6 (Version 7 not there yet)

  10. File Structures USYSTM AgVantage System Menu Select one of the following: 1. General Ledger GLS or U1 2. Accounts Payable U2 3. Payroll U3 4. Accounts Receivable U4 5. Grain Accounting U5 6. Agronomy U6 7. Energy Management U7 8. Inventory U8 9. Patronage U9 10. Mailing Manager UA 11. Counter Invoicing 12. Investments UI 13. Interfaces 14. Feed UF Selection or command F3=Exit F4=Prompt F9=Retrieve F12=Cancel

  11. Accounts Receivable U4CSTMR Customer Master U4ITMMR Item Master U4ITMBL Item Master Balance U4DEPT Department U4FLC Fine Line Code U4TRNH A/R Header Information U4TRND A/R Detail Information U4CAGE A/R Aging Information

  12. Grain U5CROPS Crop Information U5PCTRL Purchase Control/Contracts U5PLOAD Purchase Load Header File U5PLOADJN Purchase Load Header File Joined with Archive U5PSBLD Sub-Load Information U5SCTRL Sales Control/Contracts U5SETTLH Purchase Settlements Header U5SETTLD Purchase Settlements Detail U5SETTLDJN Purchase Settlements Detail Joined with Archive U5SARLD Sales Settlements SPECIAL NOTE: Library GRTRANx contains crop position and general ledger daily posting information

  13. Inventory U8APINV A/P File U8BLEND Blends U8INTRN Summary Transaction U8INVNT Detail Transaction U8ORDTL Purchase Order Detail U8ORHDR Purchase Order Header AccountsPayable U2VMSTR Vendor Account Master U2HSTTR Invoice History Detail U2IVHST Invoice History General Ledger GLSACCT Account Master GLSMAST Account Master Balances GLSJRNL Journal Detail

  14. Accessing Query RUNQRY Quick listing of a file WRKQRY Writes and saves queries

  15. RUNQRY (F4)Used to display the data in the fileUsed to run a specific query Type choices, press Enter. Query . . . . . . . . . . . . . Name, *NONE Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB Query file: File . . . . . . . . . . . . . Name, *SAME Library . . . . . . . . . . *LIBL Name, *RUNOPT, *LIBL, *CURLIB Member . . . . . . . . . . . . *FIRST Name, *RUNOPT, *FIRST, *LAST + for more values Report output type . . . . . . . *RUNOPT *RUNOPT, *DISPLAY... Output form . . . . . . . . . . *RUNOPT *RUNOPT, *DETAIL, *SUMMARY Record selection . . . . . . . . *NO *NO, *YES

  16. WRKQRYUsed to create, change, delete, display & run queries Work with Queries Type choices, press Enter. Option . . . . . . 2 1=Create, 2=Change, 3=Copy,4=Delete 5=Display, 6=Print definition 8=Run in batch, 9=Run Query . . . . . . . Name, F4 for list Library . . . . . SMARKQRY Name, *LIBL, F4 for list

  17. Work With Queries Work with Queries Library . . . . . . . . SMARKQRY Name, *LIBL, F4 for list Subset . . . . . . . . Name, generic* Position to . . . . . . Starting characters Type options (and Query), press Enter. 1=Create 2=Change 3=Copy 4=Delete 5=Display 6=Print 8=Run in batch 9=Run Opt Query Text Changed AGEBDGTSUM Budget Trans by Customer in Summary 10/19/05 AGECSTMDTL Detail U4TRND for a Selected Customer 10/25/07 AGECSTRSUM U4TRND Activity for Period CSTMR/Summary 12/13/06 AGEJRNLDTL Detail U4TRND by Journal by Customer 08/06/07 AGEJRNLSUM AR Records by GL Journal to Balance to Gen L 11/02/06 AGEPRPYSUM Prepay Trans by Customer in Summary 10/19/05 AGING U4AGIN/U4TRND IN SUMMARY BY CUST AGECSTSUM 10/19/05 APGLDETAIL 08/29/06 CUSTLIST1 Class - 11 x 8.5 - Customer Phone Listing 10/19/05 More... F3=Exit F4=Prompt F5=Refresh F11=Display names only F12=Cancel F19=Next group

  18. Let’s go through some Queries !

  19. THANKS FOR ATTENDING ! • PLEASE FILL OUT YOUR EVALUATION FORM !

More Related