Mountain Silversmiths — Oracle EBS Set of Books Migration & Multi-Org Data Remediation

Executive Summary

Mountain Silversmiths — a Brighton, Colorado manufacturer of Western and Native American-inspired silver jewelry and accessories — engaged William Delaney Consulting in January 2005 for a technical Oracle EBS data remediation engagement focused on Set of Books (SOB) migration. The engagement's artifacts — UPDATE_SOB_ID.prc (PL/SQL stored procedure), SOB Table Update Script.sql, Org Update Script.sql, Update GL Interface.sql, and AP Invoice Update Script Output.txt — tell a clear technical story: Set of Books IDs had changed in Mountain Silversmiths' Oracle environment (due to a database migration, Multi-Org restructuring, or EBS upgrade), and existing data records throughout Oracle's AP, GL, and organization tables contained the old SOB IDs that needed to be updated to reflect the new structure.

This engagement represents a category of Oracle technical work that is rarely discussed in implementation case studies but is extremely common in mature Oracle environments: surgical data remediation using PL/SQL scripts to correct Oracle data integrity issues that arise from environment migrations, upgrades, or organizational restructuring. The ADI (Applications Desktop Integrator) documents in the folder indicate that journal entry upload capability was also configured or reconfigured as part of the engagement — likely requiring ADI setup updates to reflect the new Set of Books structure.

Engagement Context

Mountain Silversmiths is a mid-size manufacturer with a distinctive product line: Western-themed and Native American-inspired silver jewelry, belt buckles, and accessories sold through wholesale channels to gift shops, western wear stores, and Native American arts retailers. Their Oracle EBS environment supported the standard manufacturing financial cycle — purchasing materials (silver, turquoise, and other materials), tracking inventory and work-in-process, and managing receivables from wholesale customers.

Set of Books ID changes occur in Oracle 11i environments under several scenarios: when an Oracle database is migrated from one server to another and the DBA-level database restore doesn't perfectly carry SOB reference integrity, when a Multi-Org restructuring changes the SOB hierarchy, or when an Oracle clone (production-to-test environment copy) needs SOB ID adjustments to prevent test environment transactions from interfering with production accounting. In Mountain Silversmiths' case, the precise trigger is not documented in the file artifacts, but the remediation scope — AP invoices, GL interface records, and organization tables all requiring SOB ID updates — indicates that the SOB ID change was systemic, affecting data across multiple Oracle modules.

Direct Oracle table updates using PL/SQL stored procedures are a high-risk but sometimes necessary remediation approach for data integrity issues in Oracle EBS. Oracle's standard functional interfaces do not provide mechanisms for bulk SOB ID updates — the correct SOB ID is established at transaction creation and is not exposed as a user-editable field in the Oracle functional layer. When SOB IDs need to be corrected in existing data, the remediation must occur at the table level using PL/SQL, with careful attention to Oracle's data model constraints and the impact on subledger-to-GL reconciliation.

Technical Remediation Scope

UPDATE_SOB_ID Stored Procedure

The core remediation artifact was a PL/SQL stored procedure (UPDATE_SOB_ID.prc) that systematically updated the SET_OF_BOOKS_ID column across Oracle's financial tables. The procedure design required: identification of all Oracle tables containing SET_OF_BOOKS_ID that held records with the old SOB ID value, validation that the records being updated were in an appropriate status for modification (posted, not-posted, or open, depending on the table), execution of UPDATE statements with appropriate WHERE clause constraints, and commit/rollback logic that enabled the update to be rolled back if validation failed at any step. The output file (Update Script Output.txt, AP Invoice Update Script Output.txt) confirms that update execution was logged — a necessary audit trail for direct table updates of this sensitivity.

AP Invoice Table Remediation

AP Invoice update scripts targeted Oracle's AP_INVOICES_ALL table and related AP tables (AP_INVOICE_LINES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL) where the SET_OF_BOOKS_ID required correction. AP invoice records are among the most sensitive Oracle table updates: incorrect SOB IDs on posted invoices create subledger-to-GL reconciliation breaks that are difficult to diagnose. The AP Invoice Update Script Output confirms the remediation was executed and logged.

GL Interface and Organization Updates

The GL Interface update script (Update GL Interface.sql) addressed the GL_INTERFACE table — the staging table for journal entries awaiting import into Oracle GL. Unimported GL interface records with incorrect SOB IDs would fail the GL Import program with a "Set of Books not found" error. Correcting the SOB ID in GL_INTERFACE before running the import resolved this class of import failures. The Org Update Script addressed HR_ALL_ORGANIZATION_UNITS and related organization hierarchy tables where SOB associations needed correction.

ADI Reconfiguration

The ADI configuration documents reflect the need to update Oracle Applications Desktop Integrator settings to reference the correct (post-migration) Set of Books — ADI stores the SOB ID in its journal entry template configuration, and ADI templates built against the old SOB ID would fail upload after the SOB ID change. Updating ADI configuration restored the journal entry upload capability that the SOB ID change had broken.

Key Deliverables

Consultant Insights

Related Engagements