DARTS : Query Manager Checklist

Finance Query

1.Saving a query:

a. Query name should start with "UMS_DTS_"

b. Enter JIRA ticket number and date on Description.

c. Put reporting category of report under Folder.

d. Put Date and DARTS email on Query Definitions.

e. Save the query as "Private" and Tamara is the only one who can change it to "Public".

2. Expressions:

a. If you are entering an aggregate value, such as SUM, AVG, MIN, MAX, or COUNT, select the Aggregate Expression check box.

b. For the comment on Expression, only one set of /*+ */ is allowed.

c. For Number and Signed Number, expression types, the Length field defines the total length of the number (integer portion + decimals portion). For example, if Length = 10 and Decimals = 3, then this means that the integer portion = 7 (Length - Decimals = Integer).

d. Make sure a completed set of parentheses exists.

e. Divisor cannot be 0. Use case statement to separate calculation for 0.

f. Use ANY for long list of items

3. Prompts:

a. The list of prompt table is documented on Finance Prompt Tables.

b. ACCOUNTING_PERIOD

• • • Change Edit Type from "No Table Edit" to "Prompt Table"
    • Use "UM_FY_PERIODS" for Prompt Table
    • Check Optional checkbox so all Accounting Periods for Fiscal Year can be returned when left blank

c. BUSINESS_UNIT

• • • Change Edit Type from "No Table Edit" to "Prompt Table"
    • Use "BUS_UNIT_TBL_FS" for Prompt Table
    • Check Optional checkbox so all Business Unit will be returned when left blank

d. FISCAL_YEAR

• • • We don't use prompt table for Fiscal Year, so Edit Type should be "No Table Edit" 
    • Fill in Default Value to '2018' or most recent Fiscal Year.
    • When there are multiple FYs, use "OR" condition under fields.
       e. DATES
  • • Use both "not less than" and "not greater than" when defining two dates.
    
    
    

4. Fields:

a. Change Column Title (Heading Text) to ALL_CAPS with no_spaces (use "_")

b. Reorder/ Sort as on Discoverer Reports

5. Criteria:

a. Manual join: Use "Expression" and pick field and adding (+) at the end without space. All base table is on the same side of the standard join.

6. Other tips:

a. DEPT_CR: We need to replace DEPT_CR (Campus Responsibility Codes) since they are legacy codes from the previous FAST System and are not being built for new accounts. The replacement is to use "In Tree" can be found at Replacing DEPT_CRx with "in tree" logic.

b. Accounts Payable Table: Flag it on a query if the table was used. Not many people have access to the table, so we need to notify the users.

c. Long Case Statements in Discoverer: Try to join the table instead of pasting the long case statements in Expressions. ex.) Use "UM_GLQ_ACCTA" table for ACCT_CATEG (account category).

7. Errors:

a. Message=ORA-00932: inconsistent datatypes: expected - got CLOB (50,380)

Solutions

a1. check data type. If the type is text, change to character. (ex. TO_CHAR(A.longdescr)

a2. remove distinct may solve the error.

a3. check null or join criteria

a4. Limit the number of characters to pull by using TRIM (ex. SUBSTR(TRIM(A.longdescr), 500, 1))