You CAN Change Your Accounting Flexfield

1
You CAN Change Your Accounting Flexfield

• Melanie Bock
• Consultant
• melaniejbock_at_cs.com
• July 2004 NorCal OAUG

2
Introduction

• You can change key setups such as flexfields,
  calendar, and org structure without
  re-implementing
• Re-implementing requires data conversion
• Objective of this presentation accounting flex
  change

3
Agenda

• Tables with chart of accounts data
• Rules for changes
• Types of changes
• Case studies
• Main features in core Financial and Manufacturing
  modules

4
Tables with Chart of Accts

• Values and descriptions
• Account combinations
• Ranges of values
• Concatenated values
• Miscellaneous

5
Column Naming

• Segment
• Segment value
• Range low/high, value low/high
• Bal seg, company
• Account, acct
• Cost center
• Concatenated

6
Sample Accounting Flex
3 segments with value sets
Company Department Account
10 US 100 Manufacturing 1010 Cash
20 Canada 200 Operations 1020 Trade AR

• Dash is separator
• 10-000-1010

7
Values and Descriptions

• fnd_flex_values

flex_value_set_id value flex_value_id
1002001 (dept) 100 2001
1002001 (dept) 200 2002
1002002 (account) 1010 2003
1002002 (account) 1020 2004
fnd_flex_values_tl
flex_value_id description language
2001 Manufacturing US
2002 Operations US
2003 Cash US
2004 Trade AR US
8
Values - Dependent

• fnd_flex_values

flex_value_set_id parent_flex_value_low (acct for subacct) value flex_value_id
1002002 (account) 1010 2003
1002002 (account) 1020 2004
1002003 (subacct) 1010 001 2005
1002003 (subacct) 1010 002 2006
1002003 (subacct) 1020 001 2007
1002003 (subacct) 1020 002 2008
9
Combinations

• gl_code_combinations

code_combination_id segment1 (company) segment2 (dept) segment3 (account)
10001 10 000 1010
10002 10 000 1020
10003 10 100 7010
10004 10 200 7010
Table also has chart_of_accounts_id
10
Values

• Expense report approvals
• ap_web_signing_limits

employee_id cost_center signing_limit
12058 100 5000
17485 200 10000
11
Values

• FA category accounts
• fa_category_books

asset_cost_account_ccid asset_cost_account
12503 1710
24860 1720
Same table has several other accounts
12
Other Tables with Values

• Stat units of measure
• Journal balancing segment values
• Recurring journals
• Mass allocations
• Intercompany accounts, elimination sets, and
  other GIS setup
• Revaluation gain/loss (11.5.9)
• Translation balancing segment values

13
Other Tables with Values

• FSG column set overrides
• AP credit cards
• Tax reporting entities (balancing segment values)
• AR AutoAccounting constants
• FA book controls (ie gain/loss)
• PA lookup sets for AutoAccounting

14
Ranges of Values

• PO approval groups
• po_control_rules

segment1_low segment1_high segment2_low segment2_high segment3_low segment3_high
00 99 100 199 7000 8999
00 99 200 299 7000 8999
15
Ranges of Values some null

• FSG row and column set accounts
• rg_report_axis_contents

segment1_low segment1_high segment2_low segment2_high segment3_low segment3_high
1000 1999
2000 2999
16
Other Range Tables

• Child ranges for parents
• Security rules
• Budget organizations
• Consolidation mapping
• Revaluation accounts
• FSG content sets
• Expense Distribution Detail (thru R11)
• FA mass transaction history

17
Concatenated Values

• Item templates
• mtl_item_templ_attributes

attribute_name attribute_value (ccid) report_user_value
MTL_SYSTEM_ITEMS.SALES_ACCOUNT 10123 10-200-4010
Same table has 3 other accounts
18
Concatenated Values

• Cross validation rules
• fnd_flex_validation_rule_lines

include_exclude_indicator concatenated_segments_low concatenated_segments_high
I -- --
E -100-1000 -999-3999
19
Other Concatenated Tables

• Shorthand aliases
• FSG report segment overrides (reports setup and
  run history)
• Expense report templates
• Mass allocations (type)
• AP credit cards

20
Miscellaneous

• AR Auto Accounting setup
• ra_account_default_segments

segment segment_num constant
SEGMENT3 3 4000
21
Other Miscellaneous

• Summary accounts
• Consolidation value set mapping
• Optimizer
• PA AutoAccounting rule assignments
• Account Generator
• Descriptive flexfields

22
Other Considerations

• Custom tables, interfaces, legacy systems,
  reports, programs, parameters, profile options
• Report wrapping or truncation
• ADI
• Multiple charts of accounts
• General setup changes
• Data cleanup
• Audit issues (archive data or reports)
• Future segments

23
Rules for Changes

• One-to-one relationship for combinations (do not
  collapse)
• Change all sets of books with same structure
• Conduct separate project, not as part of upgrade
• Test thoroughly
• Never admit to Oracle you did this

24
Overall Approach

• Process interfaces, close, run reports
• Load or update values
• Unfreeze, change, and refreeze accounting flex
• Update tables programmatically
• Make manual changes
• Query data in screens
• Re-run reports and compare

25
Change Segments

• Add new segment at end and default in history
  with all zeros
• Variations
• Drop a segment (futures!)
• Combine 2 segments
• Split 1 segment into 2

26
Add Segment, Default 000

• Setup new value set with value 000
• Add segment to acctg flex and freeze
• Combinations
• segment4 000
• Range tables, if require values
• segment4_low 000, segment4_high 999
• Concatenated tables
• Append -000 and -999, or -
• Miscellaneous tables, such as add segment4 for AR
  AutoAcctg

27
Change Values

• Re-number values of a segment, such as company or
  account
• Variations
• Change first, middle, or last segments
• Re-number multiple segments
• Lengthen or shorten a segment
• Rule or mapping table
• Often ranges require manual updates if changing
  values

28
Re-number Companies

• Setup new value set with values
• Change value set on acctg flex and freeze
• Combinations
• Update segment1
• Range tables, if have values
• segment1_low and segment1_high
• Concatenated tables, if have values
• Change beginning of field
• Miscellaneous tables

29
Miscellaneous Changes

• Change segment from number to character
• Change segment name
• Change display order of segments
• Change account types on natural accounts
• Change balancing segment or cost center segment

30
Final Advice

• Ensure user-driven with management buy-in and
  sufficient user resources
• Analyze all data for impact
• Do not update code_combination_ids
• Manually change low volume data and ranges that
  are not all-inclusive
• Test thoroughly
• It CAN be done without re-implementing