r11i pjm reconciliation v9
TRANSCRIPT
Can We Actually Reconcile Project MFG to Inventory, WIP, Projects & G/L? What
Was I Thinking?
Catherine Gauchat TCI International & Douglas VolzDouglas Volz Consulting
Slide 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
Slide 3
Background
Military Customers
Commercial Customers
Inventory / WIP G/LProjects
Slide 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
Slide 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
Slide 6
Business Requirements
Ability to ReconcileAll at the same
time!
Inventory & WIP Value
G/L
Project Accounting G/L
Inventory & WIP Value
Project Accounting
Slide 7
Business Requirements
Identify Differences
Inventory & WIP compared to Projects
Inventory & WIP compared to G/L
Slide 8
Background Information for Project Manufacturing
Manufacturing has the following transactions:
Purchasing
Delivery
Purchasing
Receipt
Material
Transactions
WIP
Transactions
Time & Attendance
Borrow / Payback, etc.
Slide 9
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_SYSTEM_ITEMS_B• INVENTORY_ITEM_ID• ORGANIZATION_ID• SEGMENT1• DESCRIPTION
MTL_TRANSACTION_ACCOUNTS
•TRANSACTION_ID• ORGANIZATION_ID• INVENTORY_ITEM_ID• TRANSACTION_SOURCE_TYPE_ID• PRIMARY_QUANTITY• BASE_TRANSACTION_VALUE• REFERENCE_ACCOUNT• ACCOUNTING_LINE_TYPE
Inventory Transaction Tables
MTL_PARAMETERS• ORGANIZATION_ID• PRIMARY_COST_METHOD
Slide 10
WIP_TRANSACTIONS• WIP_ENTITY_ID •TRANSACTION_ID• ORGANIZATION_ID• TRANSACTION_TYPE• PROJECT_ID• PRIMARY_QUANTITY
MTL_SYSTEM_ITEMS_B• INVENTORY_ITEM_ID• ORGANIZATION_ID• SEGMENT1• DESCRIPTION
WIP_TRANSACTION_ACCOUNTS•TRANSACTION_ID• WIP_ENTITY_ID• ORGANIZATION_ID• PROJECT_ID• PRIMARY_QUANTITY• BASE_TRANSACTION_VALUE• REFERENCE_ACCOUNT• ACCOUNTING_LINE_TYPE
WIP Transaction Tables
MTL_PARAMETERS• ORGANIZATION_ID• PRIMARY_COST_METHOD
WIP_ENTITIES• WIP_ENTITY_ID• ENTITY_TYPE• ORGANIZATION_ID• WIP_ENTITY_NAME• PRIMARY_ITEM_ID
Slide 11
Project Manufacturing Integration
Cost Collector
PA_EXPENDITURE_ITEMS_ALL
INV and WIPDistribution Accounting
Tables
• PROJECT_ID• TRANSACTION_SOURCE• EXPENDITURE_TYPE• ORIG_TRANSACTION_REFERENCE• SYSTEM_LINKAGE_FUNCTION• PROJECT_BURDENED_COST
Project Balances
Slide 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
Slide 13
Background Information about PSI(Project Status Inquiry)
Certain material transactions are not PSI-related:
(Project Status Inquiry related)WIP
Completions
WIP Completions
Returns
Sales Order Issues
Sales Order Returns
WIP Scrap
WIP Scrap Returns
Slide 14
Background Information about PSI(Project Status Inquiry)
Transfers to / from the same project number and task are not PSI related:
Misc. Project Transfers
Subinventory Transfers
WIP component issues
WIP component returns
WIP negative component return
WIP negative component issue
Slide 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
Slide 16
Too Many Accounting Entries – Example 1 Accounting Debits and Credits for Material Issue to WIP
(Average Costing Example from Release 11i)
Matl Issue to WIPCost Group Xfer
WIP Matl Acct INV Matl Acct Cost Group
Xfer Acct
582.75 582.75 582.75
582.75
A two-line detailed transaction is written as a four-line entry
Slide 17
Too Many Accounting Entries – Example 2
Accounting Debits and Credits for Transfer to Project(Average Costing Example from Release 11i)
Transfer to Project(From Subinv PRJ)
To Project 123
INV Matl Acct
13.83 44.40
INVRes. Acct
INV ProdOVHD Acct
INV
OSP Acct
Project Matl Acct
13.83 0.00 0.00 44.40
ProjectRes. Acct
ProjectOVHD Acct
Project
OSP Acct
A four-line transaction is written as a six-line entry
Slide 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
Slide 19
Cost Collector Design Issues
The Cost Group Transfer ≠ Inventory Matl Accounting
This example is Material Component Issues to WIP
Slide 20
Cost Collector Design Issues The PA_EXPENDITURE_ITEMS_ALL Table has a
misleading foreign key reference Project transfers has two material transactions
“From”
(Minus Qty)
“To”
(Plus Qty)
INV Matl AcctMTL_MATERIAL_TRANSACTIONS
XXX
XXX
PA_ EXPENDITURE_
ITEMS_ALL
Projects References the “From Entry”
Material Accounting References the “To”
Entry
Slide 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
TRANSACTION SOURCE
ORIG_TRANSACTION_REFERENCE
PRJ_EXPENDITURE_TYPE
SYSTEM_LINKAGE_FUNCTION
(‘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’)
Slide 22
Cost Collector Design Issues
For Inventory Transactions the SYSTEM_LINKAGE_FUNCTION 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_id
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
Slide 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:
RESOURCE_ID
TRANSACTION_SOURCE
ORIG_TRANSACTION_REFERENCE
PRJ_EXPENDITURE_TYPE
SYSTEM_LINKAGE_FUNCTION
Slide 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
Slide 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 don’t directly address an overall reconciliation solution
So how do we solve these reconciliation
issues?
Slide 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)
Slide 27
XXX_RECONCILE_PRJ_MFG
Reconciliation Table Architecture
• 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
LEGENDInventory & WIP tables PA Expenditure Items All tableNew integration column
Slide 28
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
Reconciliation Table Architecture(XXX_RECONCILE_PRJ_MFG)
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)
LEGENDInventory & WIP tables PA Expenditure Items All tableNew integration column
Slide 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_ITEMS_ALL
You have all your reconciliation information in one place
Slide 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
Slide 31
Business Requirements – Report Reports
Reconcile to the G/L by full or partial account segments:
Slide 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
Slide 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)
Slide 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?
Slide 35
Custom Programs to Populate SLA Tables
CREATE ACCOUNTING
SLA Accounting Tables
XLA_AE_HEADERS
XLA_AE_LINES
XLA_DISTRIBUTION_LINKS
XLA_EVENTS
INV, WIP, ProjectsDistribution Accounting
Tables
XLA_AE_HEADERS
XLA_DISTRIBUTION_LINKS
XLA_EVENTS
CustomUPDATE Programs
To PopulateATTRIBUTE COLUMNS
Slide 36
Create Custom UPDATE Programs
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
Distribution AccountingTables
MTL_TRANSACTION_ACCOUNTS
WIP_TRANSACTION_ACCOUNTS
Project Cost Collection Table
PA_EXPENDITURE_ITEMS_ALL
LEGENDInventory & WIP tables PA Expenditure Items All tableIntegration columns
Slide 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
Slide 38
INV and WIP Integration Details for SLA
APPLICATION_ID – 707 (Cost Management)
ENTITY_CODE – MTL_ACCOUNTING_EVENTS
– WIP_ACCOUNTING_EVENTS
SOURCE_DISTRIBUTION_TYPE – 'MTL_TRANSACTION_ACCOUNTS – ‘WIP_TRANSACTION_ACCOUNTS
SOURCE_DISTRIBUTION_ID_NUM_1 – INV_SUB_LEDGER_ID – WIP_SUB_LEDGER_ID
Slide 39
Post Processing Program Details for SLA (Cont’d)
FROMinv.mtl_transaction_accounts mta,inv.mtl_material_transactions mmt,inv.mtl_transaction_types mtt,inv.mtl_system_items_b msi,apps.GL_CODE_COMBINATIONS_KFV gcc,inv.mtl_parameters mp, xla.xla_transaction_entities ent,xla.xla_events xe,xla.xla_distribution_links xdl,xla.xla_ae_headers ah,xla.xla_ae_lines al
Slide 40
Post Processing Program Details for SLA (Cont’d)
-- ===========================================-- Material Transaction, Org and Item Joins-- ===========================================where mta.transaction_id = mmt.transaction_idand mmt.transaction_type_id = mtt.transaction_type_idand mta.organization_id = msi.organization_idand mta.inventory_item_id = msi.inventory_item_idand mp.organization_id = msi.organization_id
Slide 41
Post Processing Program Details for SLA (Cont’d)-- ========================================================-- SLA table joins to get the exact account numbers - MTA-- ========================================================AND ent.entity_code = 'MTL_ACCOUNTING_EVENTS'AND ent.application_id = 707AND xe.application_id = ent.application_idAND xe.event_id = xdl.event_idAND ah.entity_id = ent.entity_idAND ah.ledger_id = ent.ledger_idAND ah.application_id = al.application_idAND ah.application_id = 707AND ah.event_id = xe.event_idAND ah.ae_header_id = al.ae_header_idAND al.application_id = ent.application_idAND al.ledger_id = ah.ledger_idAND al.AE_HEADER_ID = xdl.AE_HEADER_IDAND al.AE_LINE_NUM = xdl.AE_LINE_NUMAND xdl.application_id = ent.application_idAND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'AND xdl.source_distribution_id_num_1 = mta.inv_sub_ledger_idAND gcc.code_combination_id = al.code_combination_id
Slide 42
Post Processing Program Details for SLA (Cont’d)-- ========================================================-- SLA table joins to get the exact account numbers - WTA-- ========================================================AND ent.entity_code = ‘WIP_ACCOUNTING_EVENTS'AND ent.application_id = 707AND xe.application_id = ent.application_idAND xe.event_id = xdl.event_idAND ah.entity_id = ent.entity_idAND ah.ledger_id = ent.ledger_idAND ah.application_id = al.application_idAND ah.application_id = 707AND ah.event_id = xe.event_idAND ah.ae_header_id = al.ae_header_idAND al.application_id = ent.application_idAND al.ledger_id = ah.ledger_idAND al.AE_HEADER_ID = xdl.AE_HEADER_IDAND al.AE_LINE_NUM = xdl.AE_LINE_NUMAND xdl.application_id = ent.application_idAND xdl.source_distribution_type = ‘WIP_TRANSACTION_ACCOUNTS'AND xdl.source_distribution_id_num_1 = wta.wip_sub_ledger_idAND gcc.code_combination_id = al.code_combination_id
Slide 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
Slide 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
LEGENDInventory & WIP tables PA Expenditure Items All tableIntegration columns
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
Slide 45
Appendix
Professional Background for Douglas Volz
Slide 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 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
ExperienceSample 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)
Slide 47
Thanks for allowing us to discuss these topics with you!
For follow-up:[email protected]
[email protected]@volzconsulting.com