1 / 47

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

This article explores the challenges of reconciling Project MFG to Inventory, WIP, Projects, and the General Ledger. It discusses the business requirements, built-in PJM reconciliation issues, and offers a solution summary.

clusk
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Projects G/L 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. Purchasing Delivery Purchasing Receipt Time & Attendance Borrow / Payback, etc. Material Transactions WIP Transactions 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 INV and WIPDistribution AccountingTables Cost Collector 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. WIP Completions Sales Order Issues Sales Order Returns WIP Scrap Returns Background Information about PSI(Project Status Inquiry) • Certain material transactions are not PSI-related: (Project Status Inquiry related) WIP Completions Returns WIP Scrap

  14. Misc. Project Transfers Subinventory Transfers WIP component issues WIP component returns WIP negative component return WIP negative component issue 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. TRANSACTION SOURCE ORIG_ TRANSACTION_REFERENCE PRJ_EXPENDITURE_TYPE SYSTEM_LINKAGE_FUNCTION 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_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

  23. RESOURCE_ID TRANSACTION_SOURCE ORIG_TRANSACTION_REFERENCE PRJ_EXPENDITURE_TYPE SYSTEM_LINKAGE_FUNCTION 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 don’t 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 MTL_TRANSACTION_ACCOUNTS WIP_TRANSACTION_ACCOUNTS PA_EXPENDITURE_ITEMS_ALL

  28. Reconciliation Table Architecture(XXX_RECONCILE_PRJ_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_ITEMS_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_ACCOUNTS 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_ITEMS_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_TYPE – '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 (Cont’d) 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_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

  40. Post Processing Program Details for SLA (Cont’d) -- =========================================== -- 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 (Cont’d) -- ======================================================== -- 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_distribution_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 (Cont’d) -- ======================================================== -- 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_distribution_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 andCost 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@TCI.spx.com randerson@projectp.com doug@volzconsulting.com

More Related