Can We Actually Reconcile Project MFG to Inventory, WIP, Projects

1
Can We Actually Reconcile Project MFG to
Inventory, WIP, Projects G/L? What Was I
Thinking?

• Catherine Gauchat
  TCI International
• Douglas Volz
• Douglas Volz Consulting

2
Background

• Through Project Partners, TCI requested help
  reconciling PJM
• One Ledger (set of books), one operating unit,
  one inventory organization
• About 50 to 80 projects per month
• 50,000 to 80,000 inventory and WIP transactions
  each month
• Project MFG, configurator, FIN modules (G/L, PO,
  A/P, A/R, F/A, Project Costing, Project Billing)
• Manufactures antenna systems for commercial and
  defense clients

3
Background
G/L
Projects
Inventory / WIP
Military Customers
Commercial Customers
4
Agenda

• Business Requirements
• Reconcile Inventory WIP Projects G/L all
  together
• Lay of the Land Built-In PJM Issues
• PJM Inventory/WIP Accounting Design Issues
• Cost Collector Design Issues
• Lack of Reconciliation Reporting
• Reconciliation Opportunities Release 11i 12
• Reconciliation Association Table
• Reconciliation Reporting

5
Agenda (Continued)

• Using Subledger Accounting for PJM Reconciliation
• Use Subledger Accounting Rules to Populate
  attribute columns
• Coupled with Custom Reconciliation Reporting
• Pros and Cons with SLA vs. Custom Reconciliation
  Table
• Presentation Summary
• Business Requirements
• Built-in PJM Reconciliation Issues
• Solution Summary

6
Business Requirements

• Ability to Reconcile

All at the same time!
Inventory WIP Value
G/L
Project Accounting
G/L
Inventory WIP Value
Project Accounting
7
Business Requirements

• Identify Differences
• Inventory WIP compared to Projects
• Inventory WIP compared to G/L

8
Background Information for Project Manufacturing

• Manufacturing has the following transactions

9
Inventory Transaction Tables

• MTL_SYSTEM_ITEMS_B
• INVENTORY_ITEM_ID
• ORGANIZATION_ID
• SEGMENT1
• DESCRIPTION

• MTL_PARAMETERS
• ORGANIZATION_ID
• PRIMARY_COST_METHOD

• MTL_MATERIAL_TRANSACTIONS
• TRANSACTION_ID
• TRANSFER_TRANSACTION_ID
• ORGANIZATION_ID
• INVENTORY_ITEM_ID
• TRANSACTION_SOURCE_TYPE_ID
• TRANSACTION_TYPE_ID
• PROJECT_ID
• TO_PROJECT_ID
• SOURCE_PROJECT_ID
• SUBINVENTORY_CODE
• PRIMARY_UOM
• PRIMARY_QUANTITY

• MTL_TRANSACTION_ACCOUNTS
• TRANSACTION_ID
• ORGANIZATION_ID
• INVENTORY_ITEM_ID
• TRANSACTION_SOURCE_TYPE_ID
• PRIMARY_QUANTITY
• BASE_TRANSACTION_VALUE
• REFERENCE_ACCOUNT
• ACCOUNTING_LINE_TYPE

10
WIP Transaction Tables

• MTL_SYSTEM_ITEMS_B
• INVENTORY_ITEM_ID
• ORGANIZATION_ID
• SEGMENT1
• DESCRIPTION

• MTL_PARAMETERS
• ORGANIZATION_ID
• PRIMARY_COST_METHOD

• WIP_TRANSACTIONS
• WIP_ENTITY_ID
• TRANSACTION_ID
• ORGANIZATION_ID
• TRANSACTION_TYPE
• PROJECT_ID
• PRIMARY_QUANTITY

• WIP_ENTITIES
• WIP_ENTITY_ID
• ENTITY_TYPE
• ORGANIZATION_ID
• WIP_ENTITY_NAME
• PRIMARY_ITEM_ID

• WIP_TRANSACTION_ACCOUNTS
• TRANSACTION_ID
• WIP_ENTITY_ID
• ORGANIZATION_ID
• PROJECT_ID
• PRIMARY_QUANTITY
• BASE_TRANSACTION_VALUE
• REFERENCE_ACCOUNT
• ACCOUNTING_LINE_TYPE

11
Project Manufacturing Integration
Cost Collector
INV and WIPDistribution AccountingTables
PA_EXPENDITURE_ITEMS_ALL

• PROJECT_ID
• TRANSACTION_SOURCE
• EXPENDITURE_TYPE
• ORIG_TRANSACTION_REFERENCE
• SYSTEM_LINKAGE_FUNCTION
• PROJECT_BURDENED_COST

Project Balances
12
Background Information for Project Manufacturing

• These transactions are integrated to Projects
  through
• Project Id ? Project Id, To Project Id, Source
  Project Id
• Transaction Sources ? WIP, Inventory, Time
  Attendance
• Expenditure Types ? similar to Cost Elements
• System Linkage Function ? similar to Cost
  Elements
• ORIG_TRANSACTION_REFERENCE ? Transaction ID for
  Inventory WIP

13
Background Information about PSI(Project Status
Inquiry)

• Certain material transactions are not
  PSI-related (Project Status Inquiry related)

WIP Completions Returns
WIP Scrap
14
Background Information about PSI(Project Status
Inquiry)

• Transfers to / from the same project number and
  task are not PSI related

15
PJM Inventory/WIP Accounting Design Issues

• Too Many Accounting Entries!
• PJM significantly increases the number of
  detailed accounting entries
• Transfers by Cost Group on top of normal entries
• Accounting by Cost Element
• A Cost Group Transfer is required every time
  goods move from one Cost Group to another

16
Too Many Accounting Entries Example 1

• Accounting Debits and Credits for Material Issue
  to WIP(Average Costing Example from Release 11i)

Cost GroupXfer Acct
WIP Matl Acct
INV Matl Acct
Matl Issue to WIP Cost Group Xfer
582.75 582.75

582.75 582.75
A two-line detailed transaction is written as a
four-line entry
17
Too Many Accounting Entries Example 2

• Accounting Debits and Credits for Transfer to
  Project(Average Costing Example from Release
  11i)

INV Matl Acct
INV Res. Acct
INV Prod OVHD Acct
INV OSP Acct
Transfer to Project (From Subinv PRJ) To
Project 123
13.83
44.40
Project Matl Acct
Project Res. Acct
Project OVHD Acct
Project OSP Acct
13.83 0.00 0.00
44.40
A four-line transaction is written as a six-line
entry
18
Too Many Accounting Entries Technical Look

• Sample Data from MTL_MATERIAL_TRANSACTIONS and
  MTL_TRANSACTION_ACCOUNTS
• Average Costing Example from Release 11i

Cost Group Xfer
Real Entries
19
Cost Collector Design Issues

• The Cost Group Transfer ? Inventory Matl
  Accounting
• This example is Material Component Issues to WIP

20
Cost Collector Design Issues

• The PA_EXPENDITURE_ITEMS_ALL Table has a
  misleading foreign key reference
• Project transfers has two material transactions

MTL_MATERIAL_TRANSACTIONS
INV Matl Acct
From (Minus Qty)
To (Plus Qty)
XXX
XXX
Material Accounting References the To Entry
PA_ EXPENDITURE_ITEMS_ALL
Projects References the From Entry
21
Cost Collector Design Issues

• Difficult to Join PA_EXPENDITURE_ITEMS_ ALL with
• MTL_TRANSACTION_ACCOUNTS and
• WIP_TRANSACTION_ACCOUNTS
• Key Reference Columns
  Column Values

(Inventory, Work in Process) (TRANSACTION_ID
from MTL_TRANSACTION_ACCOUNTS
WIP_TRANSACTION_ACCOUNTS) (similar to cost
elements) (Inventory and WIP values are
INV, WIP, BTC)
22
Cost Collector Design Issues

• For Inventory Transactions the SYSTEM_LINKAGE_FUNC
  TION value of INV is not unique
• For miscellaneous project transfers from
  Inventory to a Project, the value INV could be
  for cost element id 1 (material) or 2 (material
  overhead) for the same expenditure type
• Sample code
• and (decode(peia.SYSTEM_LINKAGE_FUNCTION,
  'INV', 1,'BTC', 5, 1) xrpm.cost_element_i
  d
• or
• decode(peia.SYSTEM_LINKAGE_FUNCTION,
  'INV', 2,'BTC', 5, 1)
• xrpm.cost_element_id)
• XRPM is the table alias for the reconciliation
  table XXX_RECONCILE_PRJ_MFG
• peia is the table PA_EXPENDITURE_ITEMS_ALL

23
Cost Collector Design Issues

• For WIP Transactions the SYSTEM_LINKAGE_FUNCTION
  value of WIP is not unique
• For the same project task, PA_EXPENDITURE_ITEMS_
  ALL rows can have the same

24
Cost Collector Design Issues

• WIP Material Returns Not Picked Up by Cost
  Collector
• Going from WIP/jobs related to a project returned
  into a non-project subinventory
• Transactions that Are Not PSI Related are Picked
  Up by the Cost Collector
• Inventory material transactions transferred
  to/from same project
• For inventory transfers into a project, the Cost
  Collector picks up the wrong Expenditure Type
• Really hard to find these discrepancies

25
Lack of Reconciliation Tools

• New Tools Exist in R12 but
• Only summary inquiry screens Cost Activity
  Workbench
• No easy to use reconciliation reports
• The new tools help with Project Manufacturing
  inquiries and drill-down but dont directly
  address an overall reconciliation solution

So how do we solve these reconciliation issues?
26
Custom Opportunities In Release 11i and 12

• So What Can Be Done for Release 11i?
• Create custom reconciliation tables
• Create custom reconciliation reports
• Simple yet brute force design (copy lots and
  lots of rows)

27
Reconciliation Table Architecture
LEGEND Inventory WIP tables PA Expenditure
Items All table New integration column
XXX_RECONCILE_PRJ_MFG

• TRANSACTION_ID
• TRANSACTION_SOURCE
• EXPENDITURE_ITEM_ID
• PROJECT_ID
• PSI_RELATED_FLAG
• ORIG_TRANSACTION_REFERENCE
• BASE_TRANSACTION_VALUE
• PROJECT_BURDENED_COST

PA_EXPENDITURE_ITEMS_ALL
MTL_TRANSACTION_ACCOUNTS WIP_TRANSACTION_ACCOUNTS
28
Reconciliation Table Architecture(XXX_RECONCILE_P
RJ_MFG)
LEGEND Inventory WIP tables PA Expenditure
Items All table New integration column
INSERT_REASON
VARCHAR2(50) INSERT_SCRIPT
VARCHAR2(30) UPDATED_FLAG
VARCHAR2(1)
INVENTORY_ITEM_ID NUMBER
ORGANIZATION_ID
NUMBER TRANSACTION_DATE
DATE TRANSACTION_ID
NUMBER TRANSFER_TRANSACTION_ID
NUMBER TRANSACTION_ACTION_ID
NUMBER TRANSACTION_SOURCE_TYPE_ID NUMBER
TRANSACTION_SOURCE_ID NUMBER
TRANSACTION_SOURCE
VARCHAR2(30) TRANSACTION_TYPE_ID
NUMBER TRANSACTION_TYPE_NAME
VARCHAR2(80) SUBINVENTORY_CODE
VARCHAR2(10) TRANSFER_SUBINVENTORY
VARCHAR2(10) LOCATOR_ID
NUMBER TRANSFER_LOCATOR_ID
NUMBER WIP_ENTITY_NAME
VARCHAR2(80) PROJECT_ID
NUMBER
ORIG_PROJECT_ID
NUMBER SOURCE_PROJECT_ID
NUMBER TO_PROJECT_ID
NUMBER
ACTUAL_COST
NUMBER NEW_COST
NUMBER PRIOR_COST
NUMBER TRANSACTION_COST
NUMBER GL_BATCH_ID
NUMBER RESOURCE_ID
NUMBER UOM

VARCHAR2(3) BASE_TRANSACTION_VALUE
NUMBER REFERENCE_ACCOUNT
NUMBER ACCOUNTING_LINE_TYPE
NUMBER COST_ELEMENT_ID
NUMBER EXPENDITURE_ITEM_ID
NUMBER EXPENDITURE_ITEM_DATE
DATE ORIG_TRANSACTION_REFERENCE VARCHAR2(30)
WIP_RESOURCE_ID
NUMBER UNIT_OF_MEASURE
VARCHAR2(30) QUANTITY
NUMBER PROJECT_BURDENED_COST
NUMBER INV_EXPENDITURE_TYPE
VARCHAR(30) PRJ_EXPENDITURE_TYPE
VARCHAR(30) PSI_RELATED_FLAG
VARCHAR(1)
MMT_PRIMARY_QUANTITY NUMBER
MTA_PRIMARY_QUANTITY NUMBER
PM_COST_COLLECTED VARCHAR(1)
29
Run List INSERT UPDATE Scripts

• Insert all rows from MTA and WTA into the recon.
  table for all rows where the accounting line type
  is Inventory and WIP (1 and 7) or where the
  row is related to a project
• In effect, make a copy of the material and wip
  accounting tables for inventory and wip valuation
  entries
• Also copy the non-inventory and non-WIP valuation
  entries that reference a project so that we can
  reconcile the PSI inquiry to the G/L
• Update the reconciliation table with the
  corresponding information from PA_EXPENDITURE_ITEM
  S_ALL
• You have all your reconciliation information in
  one place

30
Business Requirements Report Reports

• Summary by

• Full account
• Organization code
• Project
• Expenditure Type

• Transaction Name
• Amount to Subinventory
• Amount to WIP
• Amount directly to Projects

31
Business Requirements Report Reports

• Reconcile to the G/L by full or partial account
  segments

32
Business Requirements Report Layout

• Reconcile Project Manufacturing to Project
  Accounting by project number, as the values for
  both are held in the same table

This example was designed for monthly totals
33
Business Requirements Report Differences

• Report Differences transactions exist in both
  but do not agree(Cost Group Transfer Issue)
• PSI-Related transactions exist in MTA or WTA but
  not in PEIA
• Transactions related to a project but not PSI
  Related and still in PEIA (still in the Project
  Status Inquiry)

34
How to Implement in Release 12?

• Use SLA to populate attribute columns or
  supporting references
• But only 5 supporting references are allowed
• Will need custom sources to help populate
  attribute columns
• Timing issues as SLA has to run for both
  Inventory/WIP and Projects, before you can figure
  out what is missing
• So how would we do this in Release 12?

35
Custom Programs to Populate SLA Tables
INV, WIP, ProjectsDistribution
AccountingTables
SLA Accounting Tables
XLA_EVENTS
XLA_EVENTS
CREATE ACCOUNTING
CustomUPDATE Programs To Populate ATTRIBUTE
COLUMNS
XLA_AE_HEADERS
XLA_AE_HEADERS
XLA_AE_LINES
XLA_DISTRIBUTION_LINKS
XLA_DISTRIBUTION_LINKS
36
Create Custom UPDATE Programs
LEGEND Inventory WIP tables PA Expenditure
Items All table Integration columns
POPULATE ATTRIBUTE COLUMNS FOR
Distribution AccountingTables MTL_TRANSACTION_A
CCOUNTS WIP_TRANSACTION_ACCOUNTS

• TRANSACTION_ID
• TRANSACTION_SOURCE
• EXPENDITURE_ITEM_ID
• PROJECT_ID
• PSI_RELATED_FLAG
• ORIG_TRANSACTION_REFERENCE
• BASE_TRANSACTION_VALUE
• PROJECT_BURDENED_COST

Project Cost Collection Table PA_EXPENDITURE_IT
EMS_ALL
37
Why is the Release 12 Solution Different?

• Attribute columns available in SLA tables
• SLA can change the account numbers (accounting
  flexfield)
• Update the MTA and WTA SLA entries for
  information in PA_EXPENDITURE_ITEMS_ALL (PEIA)
• For both entries in inventory (onhand) belonging
  to a project and entries issued from inventory,
  not onhand, belonging to a project

38
INV and WIP Integration Details for SLA
APPLICATION_ID 707 (Cost
Management) ENTITY_CODE
MTL_ACCOUNTING_EVENTS
WIP_ACCOUNTING_EVENTS SOURCE_DISTRIBUTION_T
YPE 'MTL_TRANSACTION_ACCOUNTS
WIP_TRANSACTION_ACCOUNTS SOURCE_DISTRIBUTION_
ID_NUM_1 INV_SUB_LEDGER_ID
WIP_SUB_LEDGER_ID
39
Post Processing Program Details for SLA (Contd)
FROM inv.mtl_transaction_accounts
mta, inv.mtl_material_transactions
mmt, inv.mtl_transaction_types
mtt, inv.mtl_system_items_b msi, apps.GL_CODE_CO
MBINATIONS_KFV gcc, inv.mtl_parameters mp,
xla.xla_transaction_entities ent, xla.xla_event
s xe, xla.xla_distribution_links
xdl, xla.xla_ae_headers ah, xla.xla_ae_lines al
40
Post Processing Program Details for SLA (Contd)
-- --
Material Transaction, Org and Item Joins --
where
mta.transaction_id mmt.transaction_id and
mmt.transaction_type_id mtt.transaction_type_id
and mta.organization_id
msi.organization_id and mta.inventory_item_id
msi.inventory_item_id and mp.organization_id
msi.organization_id
41
Post Processing Program Details for SLA (Contd)
--
-- SLA table joins to get the exact
account numbers - MTA --
AND
ent.entity_code
'MTL_ACCOUNTING_EVENTS' AND ent.application_id
707 AND xe.application_id
ent.application_id AND xe.event_id
xdl.event_id AND ah.entity_id
ent.entity_id AND ah.ledger_id
ent.ledger_id AND
ah.application_id
al.application_id AND ah.application_id
707 AND ah.event_id
xe.event_id AND ah.ae_header_id
al.ae_header_id AND al.application_id
ent.application_id AND al.ledger_id
ah.ledger_id AND al.AE_HEADER_ID
xdl.AE_HEADER_ID AND
al.AE_LINE_NUM
xdl.AE_LINE_NUM AND xdl.application_id
ent.application_id AND xdl.source_distributio
n_type 'MTL_TRANSACTION_ACCOUNTS' AND
xdl.source_distribution_id_num_1
mta.inv_sub_ledger_id AND gcc.code_combination_id
al.code_combination_id
42
Post Processing Program Details for SLA (Contd)
--
-- SLA table joins to get the exact
account numbers - WTA --
AND
ent.entity_code
WIP_ACCOUNTING_EVENTS' AND ent.application_id
707 AND xe.application_id
ent.application_id AND xe.event_id
xdl.event_id AND ah.entity_id
ent.entity_id AND ah.ledger_id
ent.ledger_id AND
ah.application_id
al.application_id AND ah.application_id
707 AND ah.event_id
xe.event_id AND ah.ae_header_id
al.ae_header_id AND al.application_id
ent.application_id AND al.ledger_id
ah.ledger_id AND al.AE_HEADER_ID
xdl.AE_HEADER_ID AND
al.AE_LINE_NUM
xdl.AE_LINE_NUM AND xdl.application_id
ent.application_id AND xdl.source_distributio
n_type WIP_TRANSACTION_ACCOUNTS' AND
xdl.source_distribution_id_num_1
wta.wip_sub_ledger_id AND gcc.code_combination_id
al.code_combination_id
43
Summary

• Overall PJM reconciliation tools do not exist in
  current releases
• You could enlarge this example to include any
  project subledger or expenditure source, such as
• Payables
• Purchasing
• Time and Attendance
• Other outside systems
• You can save time just like TCI did
• two people over three weeks to now less than 3
  days
• Use the information presented here to help you
  create your own solution

44
Summary

• In Release 11i creating a custom table is your
  only choice
• In Release 12 use similar custom programs to
  populate attribute columns (ATTRIBUTE1 15) with
  the same information as found in the
  reconciliation table

• POPULATE ATTRIBUTE COLUMNS FOR
• TRANSACTION_ID
• TRANSACTION_SOURCE
• EXPENDITURE_ITEM_ID
• PROJECT_ID
• PSI_RELATED_FLAG
• ORIG_TRANSACTION_REFERENCE
• BASE_TRANSACTION_VALUE
• PROJECT_BURDENED_COST

LEGEND Inventory WIP tables PA Expenditure
Items All table Integration columns
45
Appendix

• Professional Background for Douglas Volz

46
Douglas Volz
Professional Background

Doug Volz is a Senior Architect and Advisor for
Oracle Application projects, with a particular
interest in Project and Cost Management. He has
30 years accumulated experience, including 5
years in Oracle Development (co-designing Oracle
Cost Management) and 12 years in industry in Cost
and Accounting Management positions. His
Manufacturing and Cost systems experience covers
project management, software design/development,
delivery and consulting services, for both Oracle
Corporation, and multiple international
consulting firms. Prior to his systems career,
Mr. Volz also held numerous management accounting
positions for telecommunications, defense, and
electronics companies. In his consulting
roles, Doug has served over 100 clients. Many of
these were multi-org, multi-currency with global
footprints. Countries include US, Mexico, UK,
Netherlands, Belgium, Taiwan, P.R.O.C., Norway,
Japan, Italy and Germany. Doug leads the Cost
Sub-Committee, for the OAUG Discrete
Manufacturing Special Interest Group. He also
advises and participates on the Oracle Customer
Advisory Board for Fusion Costing.
Core Expertise
Experience

• Sample of clients served
• Beckman Coulter (US)
• Matsushita (UK, Mexico)
• NTL (now Virgin Media)
• Logitech (US, Taiwan, P.R.C.)
• Matsushita (UK, Mexico)
• NTL (now Virgin Media)
• TCI International (US)
• Onninen AS (Norway)

• Multi-organization, Multi-currency ERP
  Implementations
• Project Management and Senior Project Advisor
• Core manufacturing processes
• Cost Management
• Inventory
• Bills of Material
• WIP
• Systems Integration and Data Conversions

47

• Thanks for allowing us to discuss these topics
  with you!
• For follow-up
• Catherine.Gauchat_at_TCI.spx.com
• randerson_at_projectp.com
• doug_at_volzconsulting.com