Transcript Document 7827674

You CAN Change Your
Accounting Flexfield
Melanie Bock
Consultant
[email protected]
July 2004 NorCal OAUG
1
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
2
Agenda
•
•
•
•
Tables with chart of accounts data
Rules for changes
Types of changes
Case studies
• Main features in core Financial and
Manufacturing modules
3
Tables with Chart of Accts
•
•
•
•
•
Values and descriptions
Account combinations
Ranges of values
Concatenated values
Miscellaneous
4
Column Naming
•
•
•
•
•
•
•
Segment
Segment value
Range low/high, value low/high
Bal seg, company
Account, acct
Cost center
Concatenated
5
Sample Accounting Flex
3 segments with value sets
Company Department
10 US
Account
100 Manufacturing 1010 Cash
20 Canada 200 Operations
1020 Trade AR
Dash is separator
10-000-1010
6
Values and Descriptions
fnd_flex_values
flex_value_set_id
1002001 (dept)
value flex_value_id
100
2001
1002001 (dept)
200
1002002 (account) 1010
1002002 (account) 1020
2002
2003
2004
fnd_flex_values_tl
flex_value_id description
language
2001
Manufacturing US
2002
2003
2004
Operations
Cash
Trade AR
US
US
US
7
Values - Dependent
fnd_flex_values
parent_flex_
value_low
flex_value_set_id (acct for
value flex_value_id
subacct)
1002002 (account)
1010 2003
1002002 (account)
1020 2004
1002003 (subacct) 1010
1002003 (subacct) 1010
1002003 (subacct) 1020
001
002
001
2005
2006
2007
1002003 (subacct) 1020
002
2008
8
Combinations
gl_code_combinations
code_combination segment1
_id
(company)
10001
10
segment2 segment3
(dept)
(account)
000
1010
10002
10
000
1020
10003
10
100
7010
10004
10
200
7010
Table also has chart_of_accounts_id
9
Values
Expense report approvals
ap_web_signing_limits
employee_id cost_center signing_limit
12058
100
5000
17485
200
10000
10
Values
FA category accounts
fa_category_books
asset_cost_account_ccid asset_cost_account
12503
1710
24860
1720
Same table has several other accounts
11
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
12
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
13
Ranges of Values
PO approval groups
po_control_rules
segment segment segment segment segment segment
1_low
1_high
2_low
2_high
3_low
3_high
00
99
100
199
7000
8999
00
99
200
299
7000
8999
14
Ranges of Values – some null
FSG row and column set accounts
rg_report_axis_contents
segment segment segment segment segment segment
1_low
1_high
2_low
2_high
3_low
3_high
1000
1999
2000
2999
15
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
16
Concatenated Values
Item templates
mtl_item_templ_attributes
attribute_name attribute_value report_user_value
(ccid)
MTL_SYSTEM_ 10123
ITEMS.SALES_
ACCOUNT
10-200-4010
Same table has 3 other accounts
17
Concatenated Values
Cross validation rules
fnd_flex_validation_rule_lines
include_exclude concatenated_
_indicator
segments_low
concatenated_
segments_high
I
--
--
E
-100-1000
-999-3999
18
Other Concatenated Tables
• Shorthand aliases
• FSG report segment overrides
(reports setup and run history)
• Expense report templates
• Mass allocations (type)
• AP credit cards
19
Miscellaneous
AR Auto Accounting setup
ra_account_default_segments
segment
segment_num constant
SEGMENT3 3
4000
20
Other Miscellaneous
•
•
•
•
•
•
Summary accounts
Consolidation value set mapping
Optimizer
PA AutoAccounting rule assignments
Account Generator
Descriptive flexfields
21
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
22
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
23
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
24
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
25
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
26
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
27
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
28
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
29
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 reimplementing
30