TOAD

TOAD is an extremely useful tool that can access information from Banner tables to customize reports and eliminate repetitive steps often used when compiling data in Excel.

Visit the OIT Software webpage to download the TOAD installation package plus access official SQL training opportunities, the various licenses offered through OIT and additional resources offered to help UA make use of this powerful tool. Access to the UA Ad-Hoc Databrowser Team is also available through the OIT website.

Locked Out?

Entering an incorrect username/password combination three times in a row will automatically lock your account.

Reset your password:

  1. Log in to UA ELMO with your UA username (e.g. slphillips) and password
  2. Follow the instructions for "Password Reset"
    1. Select your Banner username (e.g. "fnxxx," "snxxx," "anxxx" or "inxxx") from the "Username" drop-down menu
    2. Enter and confirm your new password
    3. Click "Set Password"
  3. Log in to TOAD using your new password

Contact our office if resetting your password does not resolve the issue. Users from other campuses can locate their account Security Coordinator from the "View Security Coordinators" drop-down list in the Getting Started section on the OIT Security Coordinators webpage


Queries

Access the growing library of TOAD queries designed to help fiscal staff build reports based on relevant data. You must save the queries to your computer and then open them from TOAD.

Queries written by OFA are done so in the current release for TOAD. We recommend end users download and utilize the current release from the OIT software download site but maintain an older version (3.3) on your PC to protect against any unintended compatibility issues.

Note: All links download a TSM file.

Incomplete Documents (Incomplete Document Management Information & Instructions)

An incomplete document is any started Banner document not yet approved in the system. Many situations can cause incomplete documents including workflow processes (such as sensitive items), NSF flags, Banner client crashes and user errors.

It is essential to monitor incomplete documents and delete or complete them as appropriate on a weekly or bi-weekly basis.

Budget Control Detail

Returns the budget, reservations, encumbrances, YTD and balance information for a chosen Budget Control Org or for an entire Unit (level 3). You can review this information in the FGIBAVL Banner screen. However, Org detail is included in the query to easily pinpoint budget deficits.

Org Rollup Structure

Pull a list of all orgs that roll-up to a given Level (level 3 - VC and level 8 - entry level) within the current hierarchy structure.

ProCard Transaction Detail

Returns a list of all items within a document (ex. PNP00001) to identify the document reference number or sequence number associated with a specific line item. This is very useful when JVing ProCard charges that map in to Banner with a PNPxxxxx document number, which is the QP number equivalent used for the JPMorgan website.

Although this was created for ProCard document numbers, the query will return results for any document number including JVs, Finance, (Fxxxxxx) and Budget Revisions.

Prog/Fund/Acct Code Mismatches

The query used to populate the bi-monthly report posted to the Program Code Compliance page. Run it for your unit to capture recently corrected mismatch issues to stay on top of these errors before they show up on the quarterly reviews. 

SubLedger Detail for Dept Reporting

Returns the budget, reservations, encumbrances, YTD and balance information for a given cycle and FY range. You can limit this report by Org Level and Fund or Fund Type.

Transaction Detail for Dept Reporting

Returns every direct expenditure transaction that occurred during the specified cycle and FY for the designated org. You can pull information by orgn Level2 through Level5 or the entry level (Level8). Results include Transaction Date, Document, Description and you can easily verify them in Banner FGIBDST.

Active Jobs by TKL

Mimics the QMenu Active Jobs report. It will prompt you to enter the TKL upon query execution.

Labor Distribution by TKL

Displays current and future labor distributions for each employee within a user-specified home TKL.

Labor Distribution by TKL - Detail

Displays more in-depth information to include hourly rate, FTE, etc. (pulls the job TKL data instead of the home TKL)

Labor Distribution & DLevel Mismatch Listing

Checks the Home Dept dlevel listed in PEAEMPL against the dlevel associated with the labor distribution org. This mismatch will affect the way eDir displays employee locations as well as certain reports that base employee figures off the Home Dept. 

 

Training

Visit the OFA training page and check out the different levels of training courses offered by OFA to see which one fits your skill level. This includes recorded sessions (YouTube) of the TOAD weekly training and discussion designed to familiarize financial managers with the use of TOAD. The sessions emphasize the use of TOAD to explore data, create and modify queries and customize outcomes to fit the needs of the analysis.​

These training queries were built and/or modified during the weekly TOAD training discussion sessions and are provided here for reference.

These queries may or may not return results, but serve as examples of how to build and modify TOAD queries. For full context, you should view these queries along with the corresponding recorded weekly session (YouTube). You should run these queries using the most recent version of TOAD.

Note: All links download a TSM file unless otherwise indicated.

   Series #5 - Solution for Labor Distribution on Effort Certifications

   Series #6 - Query Building & Practical Applications

   Series #7 - Table Connections with Data Transformation

   Series #8 - Troubleshooting Old Queries

   Series #9 thru #12 - Combining Labor and Finance Data

   Series #13 thru #14

   Series #15

   Series #16

   Series #19

   Series #21

 

Reference Guides

Access reference guides for various TOAD topics to keep you on track when developing or editing queries. These are meant to provide TOAD users with easy-to-follow instructions that assist with the completion of various functions within the environment.

Note: The downloadable PDFs include screenshots and other helpful visual cues.

Instructions meant for new computer installations or for use in the Computer Labs.

Downloadable PDF: Toad Setup


Add a Connection
  1. In the Navigation Manager section (defaulted to window at top-left), click the "Create a Connection" icon
  2. Select "Oracle" from the drop-down menu
  3. Fill out the following information
    1. Database name: rptp.alaska.edu
    2. User: Banner username (i.e. fnxxx)
    3. Password: first-time users will default to lowercase s + SSN (e.g. s12345678) If incorrect password is entered 3x in a row, the account will be locked. Call OFA for a password reset.
  4. Click "Connect"
Customize View
  1. From the top menu, click "Tools>Options"
  2. From the left menu in the Options window, click "Explorer>Objects"
  3. Uncheck all items except Synonyms (2) and Tables (1)
  4. In the same Options window, click "Database>General"  and change the number in the top box to 20000
  5. Now click "Database>Oracle" and change that number to 20000
  6. Click "Apply" then "OK"

You are now ready to start running queries!

BlueWhale is the TOAD shared drive that contains canned reports for student, human resources and finance purposes.

Downloadable PDF: Map to the BlueWhale Drive


Add a network shared drive in Windows 10:

  1. Open the File Explorer icon from the taskbar
  2. Select "This PC" from the side navigation
  3. Select "Computer" in the top tab menu and click "Map network drive"
    1. Select an available letter in the "Drive" field
      ("T:" for TOAD is a good choice if it is available)
    2. Type the shared drive path in the "Folder" field:
      \\sw-bluewhale.apps.ad.alaska.edu\ToadQueries
    3. Leave the "Reconnect at logon" box checked
    4. Check the "Connect using different credentials" box
  4. Select "Finish"
  5. Enter your UA username & password
  6. Check the "Remember my credentials" box and click "OK"

Your mapped ToadQueries drive should now appear automatically upon login.

Insert a custom field into your query:

  1. In an existing query, click the '∑' button located in the top toolbar of the TOAD window (you can also click "Query Builder>Calculated Fields" from the menu)
  2. Enter a field name in the "New field name" box and click the 'plus-sign' to add it to the list
  3. Enter the code for the calculated field in the “Field definition”
    1. Click the 'down-arrow' to see all of the table fields and operators
    2. Double-click to insert into the code
  4. Select a table from the "Attach to table" drop-down list (any table will work, but if you remove that table from your query, you will also remove that calculated field)
  5. Select "OK"

You can now see your calculated field as a column within the query diagram!

Downloadable PDF: Calculated Fields 

Syntax and examples of a more flexible version of the DECODE statement.

Downloadable PDF: CASE Statement 


A CASE statement is an extended version of the DECODE statement that can accept multiple expressions and return different sets of results.

Syntax:

CASE <selector> (optional)
WHEN <condition> THEN <statement>
WHEN <condition> THEN <statement>
ELSE <statement> (optional)
END “When <selector> equals <condition> then <result> (else <default>)”

Example:

CASE FGBTRNH.FGBTRNH_DR_CR_IND
WHEN ‘C’
THEN -FGBTRNH.FGBTRNH_TRANS_AMT
WHEN ‘D’
THEN FGBTRNH.FGBTRNH_TRANS_AMT,
ELSE ‘0’ END

The above statement reads “When debit/credit indicator equals credit, then return the negative of Trans Amt. When debit/credit indicator equals debit then just return the (positive) Trans Amt. If debit/credit indicator equals anything else, return 0 as the default.”


The <selector> can be a bind variable that prompts the user for the value used in the When statements.

Example of bind variable as <selector>:

CASE :cycle
WHEN ‘01’ THEN FGBOPAL.FGBOPAL_01_YTD_ACTV
WHEN ‘02’ THEN FGBOPAL.FGBOPAL_02_YTD_ACTV
WHEN ‘03’ THEN FGBOPAL.FGBOPAL_03_YTD_ACTV
etc...

The above statement reads “When the user enters 01 as the cycle, then return cycle 1 YTD figure. When the user enters 02 as the cycle, then return cycle 2 YTD figure...”


To use more complex boolean conditions, leave out the optional selector.

For example:

CASE
WHEN FTVORGN_LEVELS.LEVEL6 LIKE ‘D%’
THEN FTVORGN_LEVELS.LEVEL6
WHEN FTVORGN_LEVELS.LEVEL7 LIKE ‘D%’
THEN FTVORGN_LEVELS.LEVEL7 ELSE ‘ERROR’
END

The above statement reads “When level6 starts with a D then return level6. When level7 starts with a D then return level7. If either of these conditions do not match, return the word ‘ERROR’”.


Keep in mind that once the condition is met, the CASE statement will stop. Order the when/then statements in a way that make sense for what you are trying to accomplish. When using variable boolean expressions in the When statements, order them from most restrictive to least restrictive to accurately group resulting values.

This Excel macro converts a column of data to a single cell with commas and parentheses to easily insert into a Toad query.

Syntax and examples of the SQL "If...then" statement

Downloadable PDF: DECODE Statement 


A DECODE statement is the SQL version of an IF statement in programs like Excel.

Syntax:

DECODE (expression, search, result)
“IF expression equals search then result (else default)”

Example:

DECODE(FGBTRNH.FGBTRNH_DR_CR_IND, ‘C’, -FGBTRNH.FGBTRNH_TRANS_AMT, FGBTRNH.FGBTRNH_TRANS_AMT)

The above statement reads “If debit/credit indicator equals credit, then return the negative of Trans Amt. If it equals anything else (debit) then return the Trans Amt as the default value.”


There can be as many Search/Result items as needed but there can only be one expression that will be searched.

DECODE(expression, search1, result1, search2, result2, …, default)

Example:

DECODE(FGBTRNH.FGBTRNH_DR_CR_IND, ‘C’, ‘Credit’, ‘D’, ‘Debit’, ‘ERROR’)

A SW document walking users through the process of finding table and field names in Toad.

Downloadable PDF: Locating Banner Tables in Toad


Data entered in Banner are stored in tables. Each table consists of a field for each piece of data (e.g. requisition number, user id, transaction date, etc.). The field name is comprised of two parts, the TABLE NAME and the FIELD NAME. These parts are separated by an (underscore) character. For example in FPBREQH_CODE, the table name is FPBREQH and the field name is CODE.

Using Toad for Data Analysts to build a query requires knowing the names of table fields. The quickest way to find a table field name specific to a Banner form field is to use "Help (Item Properties)" while the cursor is located in the field of interest on the Banner form of interest. (Figure 1 of the PDF)

For example, if you want to know on what table the Requisition Number is stored:

  1. With the cursor in the Requisition field, click the "Help" drop-down menu from the tool bar
  2. Click "Help (Item Properties)"

When "Help (Item Property)" is selected, a screen appears that provides the field name along with some field attributes. In this example, a help function was performed from the Requisition document number field on FPIREQN. The actual field name is FPBREQH_CODE. (Figure 2 of the PDF)


Important: Not ALL fields appearing on a Banner form are found on a table. For example, Figure 3 of the PDF shows the table field name for Nation is DISPLAY_SHIP_NATION. It is easily recognized this does not fit the typical table field name configuration. Exercise care to confirm the correct name before executing a query.

Since many tables may contain the information required to complete a form, this method is a quick way to determine how to craft your query in Toad for Data Analysts.

In Toad for Data Analysts, the Viewer Table (Figure 4 of the PDF), accessed through Database Explorer, holds the table Field Name values as well. Comparing the Banner location to the table Name in Toad will ensure the query you build will return the expected data.

This blog article teaches you how to turn your SQL code into a visual diagram to make editing an easier process.

 

Our staff are happy to answer questions and help navigate TOAD.