1

You CAN Change Your Accounting Flexfield

Melanie BockConsultant

melaniejbock@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

Company Department Account

10 US 100 Manufacturing 1010 Cash

20 Canada 200 Operations 1020 Trade AR

Dash is separator

10-000-1010

3 segments with value sets

7

Values and Descriptionsfnd_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

flex_value_id description language

2001 Manufacturing US

2002 Operations US

2003 Cash US

2004 Trade AR US

fnd_flex_values_tl

8

Values - Dependentfnd_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

Combinationsgl_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 approvalsap_web_signing_limits

employee_id cost_center signing_limit

12058 100 5000

17485 200 10000

11

Values

FA category accountsfa_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 groupspo_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 accountsrg_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 templatesmtl_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 rulesfnd_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 setupra_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_id’s

• Manually change low volume data and ranges that are not all-inclusive

• Test thoroughly

• It CAN be done without re-implementing