Oracle Financials Tips and Hints I Wish I Knew That Six Months Ago.

1
Oracle Financials Tips and Hints I Wish I Knew
That Six Months Ago.

• Steve Bradley
• OASIS Consulting Group
• ATL-OAUG August 17 2001

2
Agenda

• ADI
• Publishing Standard Reports
• Publishing Report Sets
• Budget/History load templates
• General
• Flexible Default Company based on Responsibility
• Tricks with Folders

3
Agenda

• General Ledger
• Consolidations without using Oracles
  Consolidation Process
• Validation Priority in Accounting Flexfields
• Disabling Code combinations that violate new
  Cross Validation Rules
• Adjusting periods why you should never use them
• Statistical Accounts Problems and Solutions

4
Agenda

• Payables/Purchasing
• Vendor Cleanup
• Fix Duplicate Invoices in Vendor Merge
• Unmerge Vendors

5
ADI

• Publishing Standard Reports
• Can publish any standard report from any module
• Publish reports to Excel or HTML
• Customize ADI Themes to properly parse columns
• Create a theme with proper column widths
• Or create a theme with one column with 232 char

6
ADI

• Publishing Report Sets
• Version 7
• ADI 7 supports 10.7 11.x and 11i
• Runs on Windows 95 98 2000 and NT

7
ADI

• Report Sets (continued)
• Run report set in the application
• Publish the set from ADI Request Center
• Assign a theme to each report in the set
• ADI will remember the theme assignments for the
  next use

8
ADI

• Budget/History Load Templates
• Great for repetitive processes
• Create a separate copy paste sheet in the ADI
  worksheet
• Create the template to be use for history or
  budget loads including account and amount info
• Map ADI template cells to account and amount
  cells on the copy paste sheet

9
ADI

• Copy templates coming in from departments into
  the copy paste sheet
• No reformatting is needed to get into ADI
  template

10
General Ledger

• Consolidations without Consolidating
• Oracles GL consolidation was designed for
  dissimilar COA or calendars
• Provides mapping for COA and calendars
• Consolidation not needed if functional currency
  is the only violation of the 3-Cs between Sets
  of Books
• FSGs can cross Sets of Books
• Use FSGs if COA and calendar are shared

11
Consolidations with FSGs

• Shared Chart of Accounts
• Set of Books using the same AFF definition
• Not AFFs that are a duplication of each other

12
Consolidations with FSGs

• Crossing SOBs with FSG reports
• FSGs default to the current SOB of not specified
• Use the Account Assignment form to assign one or
  more SOBs to rows or columns
• Use multiple SOB assignments in a column for
  Consolidated
• Use single SOB assignments in multiple columns
  for Consolidating

13
Consolidations with FSGs

• Advantages
• No need to define or map consolidations
• No need to run consolidation process
• Consolidations are snapshots
• FSG consolidations are real time
• Do not need to re-run consolidation for
  adjustments made after consolidation

14
General Ledger

• Validation Priority in Accounting Flexfields
• Transactions get into Oracle Apps either manually
  or via automated processes
• Oracle reacts differently for each
• It is important to understand the differences in
  how they are treated

15
AFF Validation Priority

• Manual Entry
• Validate Segments
• Exists
• Enabled
• Validate Combination
• Exists
• Combination Enabled
• Violate Cross Validation

16
AFF Validation Priority

• ADI Entry
• Validate Segments
• Use LOV Validates real time
• Manual Entry Validate during/after upload
• Validate Combinations
• Validated during or after upload depending on
  upload Pre-validation options

17
AFF Validation Priority

• ADI Pre-Validation Options
• Full All Journal data including category
  source currency date cross validation and
  segment
• Partial Journal validation including segment
  security. No account validation.
• None No validation except segment security
  during upload. Validation during JE Import

18
AFF Validation Priority

• What is the fastest ADI upload
• According to ADI documentation
• Set system profile option Validate at Server
  Yes
• Select No Pre-validation during upload
• Note This will only be successful if you know
  the data is good such as with recurring types of
  entries that have been uploaded before. If you
  have bad values then you will spend more time
  correcting and re-running the uploads.

19
AFF Validation Priority

• Automated Entry of AFF Transactions
• Interface transactions to GL from subsidiary
  ledgers
• Payables distribution built by matching to PO

20
AFF Validation Priority

• Validation of Automated Transactions
• Combination exists in sub system. GL performs
  additional validation upon import
• Segment values typically not validated
• Code combinations validation (without segment
  validation) is faster
• May check to see if combination is still enabled
• Enabled combination does not mean enabled
  segments! 11i is a little different here.

21
AFF Validation Priority

• Typical Example PO to AP to GL
• Building of Payables distribution from PO has no
  validation!
• Create and approve PO
• Disable old department segment and combinations
• Match Invoice to PO
• Automatically Create Invoice Distribution line
• Approve and pay invoice
• Interface to GL. Passes import validation
• Combination fails in Posting

22
General Ledger

• Disabling Code Combinations for new CVRs
• Catch 22
• Disabling a segment value will not let you
  manually enter that value on a form
• Because of this you cannot test the cross
  validation rule to see if the combination is also
  disabled
• Disabling a segment value does not prohibit using
  an existing combination with that value (prior to
  11i)
• Interfaces will not test for disabled segments

23
Disabling Code Combinations

• Mass Disabling of Combinations
• Create CVR that prohibits use of segment value
  disabled with any other segment
• Disable all other CVRs. (Make note of the ones
  you disable if there are others already disabled)
• From System Administration run Cross Validation
  Rules Violation report with disable off
• Review and run again with disable on
• Enable the CVRs you disabled above

24
Disabling Code Combinations

• Caution
• This process will disable all combinations
  violating active CVRs. That is why you disabled
  all other CVRs.
• Be sure you want to do this. Remember the
  PO/AP/GL issue mentioned earlier.

25
General Ledger

• Adjusting Periods Never use them
• Carryover from old days for audit entries
• Did not want to affect management reports
• Cause problems in Oracle GL
• ADI cannot post to them
• Reversing entries try to use them
• They cause havoc for rolling FSG reports

26
Adjusting Periods

• Adjusting Periods and ADI
• ADI uses accounting date not period
• Adjusting period dates can overlap other periods
• ADI cannot determine correct period so it will
  select the non-adjusting period

27
Adjusting Periods

• Adjusting Periods and Reversals
• Year end reversing periods will try to reverse in
  the adjusting periods
• People operate out of habit and will probably
  forget to change the reversing period to the 1st
  period of the next year

28
Adjusting Periods

• Adjusting Periods and FSGs
• Rolling period reports (prior 6 months) must be
  custom coded for each report crossing an
  adjusting period
• How do you handle month to month comparatives at
  year end

29
Adjusting Periods

• Adjusting Period Solution
• Create a separate department division company
  etc. for adjusting entries
• Entries can be made for any period or quarter
• Exclude this segment value from management
  reports
• Include it for audited reports

30
General Ledger

• Statistical Accounts Problems
• Any Oracle Statistical account can be use in a
  non-statistical journal entry
• No security measures can prevent this
• Can cause an out of balance FSG Trial Balance

31
Statistical Accounts

• Solutions
• Develop good naming conventions for STAT and
  natural accounts
• Develop an FSG report to list all STAT accounts
  with functional currency (USD) balances

32
Statistical Accounts

• STAT Account Naming Convention
• Place them near the end of the COA structure
• Placing them at the beginning makes them too easy
  to select in error
• Suggest starting them with S
• Alpha values does not slow down STAT entry since
  they are usually predefined recurring entries -
  ADI

33
Statistical Accounts

• STAT Account FSG Error Listing
• Lists all activity with STAT accounts having
  non-STAT currency balance
• Column Set has two columns
• PTD
• YTD (in case you missed them last month)

34
Statistical Accounts

• STAT FSG Row Set
• Row Zone
• Currency USD (or your functional currency)
• Display Options Window
• Display Zero No
• Flexfield Assignments Zone
• Flexfield Range enter your statistical range
• Display E (expand to display all values found)

35
Payables/Purchasing

• Vendor Cleanup
• Should have occurred during implementation
• Needed periodically if you do not have a good
  vendor naming convention
• Implementation may have created Dead Vendors

36
Vendor Cleanup

• Dead Vendors
• Unused Vendors - no invoices and no POs
• Vendors with only activity being Canceled
  Invoices
• Vendors with in name

37
Legal Disclaimer

• Oracle does not provide Application support for
  non-interface tables that have been updated
  directly using SQL
• Use these SQL scripts at your own risk
• You can give me credit for these scripts but I
  will accept no blame for their use
• Do not call me with problems or support
• If you do not understand them - do not use them

38
Vendor Cleanup

• SQL listings in Paper on CD
• Duplicate Vendors Listing
• Fix Duplicate Invoices preventing a vendor merge
• Delete Unused Vendor Sites
• Unmerge Vendors

39
Vendor Merge Assistance

• Oracles Vendor Merge listing is useless
• You need to find the following duplicates
• IBM and International Business Machines
• AICPA and American Society of CPAs
• Ace Hardware and Axe Hardware
• BellSouth Mobility and Cingular Wireless
• This is best done by looking for similar street
  addresses within zip codes

40
Duplicate Invoices in Vendor Merge

• There may be times when merging 2 vendors creates
  a Duplicate Invoice situation
• This frequently happens when dates are used as
  invoice numbers
• Oracles Vendor Merge will not touch these
  duplicate invoices
• SQL script appends -Merge to invoice number of
  duplicate

41
Unmerge Vendors

• With all of the cleanup there is bound to be an
  error when the wrong vendor is merged
• Oracle stores all vendor merge activity in a
  table
• SQL script will permit the unmerging by entering
  origin and destination vendor/site data

42
General

• Flexible Default Company based on Responsibility
• Oracle provides the ability to define a new User
  Profile Option that can be used to assign default
  values based upon the Responsibility used to
  access the application.
• Oracle AFF segment definition provides to ability
  to assign a Default value based upon a User
  Profile.

43
Flexible Default Company based on Responsibility

• With these two features you can have a default
  Company or other segment based upon the users
  responsibility.
• This can compliment your flexfield security. Not
  only restricting the user but also providing a
  custom default value.

44
Responsibility Default Configuration

• Define Default Company User Profile Option
• Define segment value default in AFF definition
• Set Site Level Default Company
• Set Responsibility Level Default Companies for
  each Responsibility needing non-Site Default

45
Responsibility Default Configuration

• Define Default Company User Profile Option
• In Application Developer Navigate to Profiles
• Enter
• Name (no spaces) DEFAULT_COMPANY
• Application Oracle General Ledger
• Profile Name (displayed name) Default Company
• Description Assign Default Company by
  Responsibility

46
Responsibility Default Configuration

• SQL Validation for Profile Option
• Optional creates company list of values for
  profiles form
• SQLSELECT a.description DEFAULT_COMPANY
  a.flex_value into visible_option_value
  profile_option_valueFROM fnd_flex_values a
  fnd_flex_value_sets bWHERE a.flex_values_set_id
  b.flex_value_set_id AND b.flex_value_set_name
  company_value_set_nameCOLUMNDEFAULT_COMPANY
  (30)

47
Responsibility Default Configuration

• Company Default Profile Definition (cont)
• User Access
• Visible and Updateable
• Sys Admin Access
• Site Visible and Updateable
• Resp. Visible and Updateable

48
Responsibility Default Configuration

• Define segment value default in AFF definition
• In GL Setup navigate to Flexfields Key
  Segments
• Query your AFF and open your company segment
• Validation
• Default Type Profile
• Default Value DEFAULT_COMPANY

49
Responsibility Default Configuration

• Set Site Level Default Company
• In Sys Admin responsibility navigate to Profile
  System
• Find Screen
• Site Yes
• Profile Default Company
• Set Site level to desired value using list of
  values

50
General

• Tricks with Folders
• Gives you the ability to modify the display and
  operation of any screen displaying the Folder
  icon
• Hide columns
• Change the title of columns
• Change width of columns
• Change display order of columns
• Change the sort order of data displayed
  (available in 1st 3 columns)

51
Folders

• Make more data displayable on the 1st screen
• Place important data on the 1st screen
• Sort the data in a meaningful manner
• Have the form perform specific query (display
  only unapproved invoices)
• You can make a custom folder the default
• You can make a folder available for use by other
  users

52
Folders

• Modify the Invoice Entry form to sort by vendor
  and inv. Date
• Type (no sort)
• Supplier (ascending sort)
• Invoice Date (descending sort)
• Invoice Number
• Site
• Sup (Supplier Number)
• Invoice Amount
• Description

53
Folders

• Invoice Distributions
• Display more data on the 1st form
• Number (Line Number)
• Type
• Amount
• Tax
• GL Date
• Account
• Description

54
Folders

• Payment Batches
• Batch Name (no sort)
• Payment Date (sort descending)
• Status
• Bank Account
• Document
• Pay Group
• Payment Method
• Temp (Template)

55
Folders

• Payment Screen
• Type
• Bank Account
• Document Name
• Ck (Document Number)
• Payment Date
• Ck Amount (Payment Amount)
• Supplier
• Supplier Site

56
Questions
Steve Bradley OASIS Consulting Group sbradley_at_spry
net.com Office 404-272-4111