����vlog����

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.

• Call Numbers
• General Encumbrances
• Receiving
• Requisitions
• Stranded Deletes

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.

• FY18 through Current FY Transaction Detail by Cycle (pull date for multiple or individual fiscal years)
• FY17 Transaction Detail by Cycle
• FY16 Transaction Detail by Cycle
• FY15 Transaction Detail by Cycle
• FY14 Transaction Detail by Cycle
• FY13 Transaction Detail by Cycle
• FY12 Transaction Detail by Cycle

ICR by FY by UNIT by Grant w/ Grant Number

Do you want to know where the ICR went that your unit's grants generated? How about knowing where the ICR you see in your unit came from? This is the query for you!"

Shows the amount of ICR generated by grant each fiscal year. This query will show all ICR generated by grants "owned" at the department level or higher, and/or it will show the amount of ICR earned at any level of the org structure, depending on the user-defined variables entered. 

Revenue by FY by UNIT by Grant  w/ Grant Number

This query answers questions like, "where did the revenue from my unit's grants go?," or, "did we receive revenue from grants on which we collaborated?"

Shows the amount of revenue generated by grant each fiscal year and assigns it to a single SBS category. This query will show all revenue generated by grants "owned" at the department level or higher, and/or it will show the amount of revenue earned at any level of the org structure, depending on the user-defined variables entered.

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.  This used to be sent regularly to PPA/HR Coordinators from UAF HR.  Here are the instructions on how to fix via EPAF to ensure information in Banner is correct.

Travel Projected Spend  (FAR2TPS)

• Source: The FAR2TPS table is populated from a Concur report updated daily at 3:30 AM in the Production database.
• RPTP Update Schedule: FAR2TPS is refreshed in the RPTP on-premises database between 4 AM and 6 AM, ensuring the latest projections from Concur in both PROD and RPTP.
• Data Origin:
  • Requests: Initially, trip data comes from any submitted Request.
  • Expense Reports: Once a Request is linked to a submitted Expense Report, the data reflects the Expense Report.
  • Approval: After final approval of the Expense Report, the data is removed from FAR2TPS and posted as a year-to-date expense in Banner with a Concur JV (starting with CJ).
  • Cancellations/Closures: Cancelled or closed Requests will remove the trip data from FAR2TPS.
  • New Expense Reports: If a new Expense Report is submitted for a previously associated Request, the new data will be available in FAR2TPS.

 

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 . 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

• Payroll by UA ID PCN

   Series #6 - Query Building & Practical Applications

• ORGN Rollup

   Series #7 - Table Connections with Data Transformation

• Miscellaneous Receipt Detail

   Series #8 - Troubleshooting Old Queries

• PO Status by a RANGE of ORGNs

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

• Transaction Detail Combined with Labor Transitional
• Transaction Detail Combined with Labor

   Series #13 thru #14

• Grant Current & Pending

   Series #15

• Active Assignments by TKL Range with Leave

   Series #16

• FY18 thru Current FY Transaction Detail by Cycle (TSM)
• FY18 thru Current FY Transaction Detail by Cycle (SQL)

   Series #19

• PCN Budget & Salary

   Series #21

• Grant Inception to Date by PI

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 �������ܱ�.”

---

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.