I’ll provide a complete solution for your data integrity issues:
1. Custom Field Mapping - Standardization Strategy:
First, create a mapping table for compliance field transformations:
CREATE TABLE compliance_mapping (
legacy_value VARCHAR2(100),
target_value VARCHAR2(50),
field_name VARCHAR2(50)
);
Populate with your variations:
- ‘RoHS Compliant’ → ‘Compliant’
- ‘RoHS-OK’ → ‘Compliant’
- ‘Non-compliant’ → ‘Non-Compliant’
- etc.
2. BOM Hierarchy Validation - Rebuilding Relationships:
Create a PX process extension to fix orphaned items. Here’s the pseudocode approach:
// Pseudocode - Hierarchy Fix Process:
1. Query all SBOM items where parent_id references non-existent SBOM_ID
2. Cross-reference with migration_log table to find legacy parent IDs
3. Look up new SBOM_ID for each legacy parent in id_mapping table
4. Update orphaned items with correct parent SBOM_ID
5. Validate hierarchy depth doesn't exceed max_levels (typically 10)
// Commit in batches of 50 to avoid lock timeouts
Key validation before updates:
- Verify parent SBOM exists in target system
- Check that update doesn’t create circular references
- Ensure parent-child relationship maintains proper BOM structure levels
3. PX Scripting for Data Fix - Complete Implementation:
Script 1: Compliance Field Standardization
Implement in PX using IAgileSession:
// Pseudocode - Field Value Standardization:
1. Get all SBOM items with custom compliance fields
2. For each field (RoHS_Status, REACH_Compliance, etc.):
a. Read current value
b. Look up standardized value in compliance_mapping
c. If mapping exists, update field with target_value
d. Log transformation for audit
3. Handle NULL values: set default based on legacy system rules
4. Validate against list field constraints before commit
// Process in batches, transaction per 25 items
Script 2: Hierarchy Reconstruction
This is more complex - you need to process in correct order:
// Top-level pseudo-logic
List<IItem> orphanedItems = findOrphanedSBOMs();
for (IItem item : orphanedItems) {
String legacyParentId = getLegacyParent(item);
IItem newParent = findMigratedParent(legacyParentId);
item.setValue(SBOM_PARENT_FIELD, newParent);
}
4. Execution Plan:
Phase 1: Pre-Fix Validation
- Run integrity check queries to document current state
- Export affected SBOM records to CSV for rollback capability
- Verify all legacy parent IDs have corresponding migrated SBOMs
Phase 2: Field Value Fixes (Run First)
- Execute compliance field standardization PX
- Validate 100% of custom fields have valid list values
- Generate compliance report to verify data accuracy
Phase 3: Hierarchy Fixes (Run Second)
- Execute hierarchy reconstruction PX
- Validate no orphaned items remain
- Run BOM explosion report on sample items to verify structure
Phase 4: Post-Fix Validation
- Re-run all integrity checks
- Compare record counts: should have zero orphans
- Test compliance report generation end-to-end
5. Handling Edge Cases:
Missing Parents: If legacy parent wasn’t migrated (obsolete item), either:
- Create placeholder parent SBOM with minimal data
- Reassign to a ‘Legacy_Unmapped’ parent for later cleanup
- Document for manual review
Circular References: Your PX script MUST check for these:
- Before assigning parent, verify item isn’t already an ancestor
- Use recursive query to check full hierarchy path
- Reject and log any circular relationship attempts
Data Type Mismatches: For fields beyond simple lists:
- Date fields: parse legacy format and convert
- Numeric fields: handle decimal precision differences
- Multi-select lists: split comma-separated legacy values
6. Monitoring and Rollback:
Create monitoring queries:
-- Check orphaned items
SELECT COUNT(*) FROM sbom_items
WHERE parent_id NOT IN (SELECT sbom_id FROM sbom_master);
-- Check NULL compliance fields
SELECT COUNT(*) FROM sbom_custom_fields
WHERE field_name IN ('RoHS_Status','REACH_Compliance')
AND field_value IS NULL;
Run these before and after each fix phase.
If you need to rollback, restore from your pre-fix CSV exports using Data Loader in update mode.
Expected Outcome:
After completing all phases, you should have:
- Zero orphaned SBOM components
- 100% populated compliance custom fields with valid list values
- Accurate compliance reports that match legacy system baseline
- Full audit trail of all data transformations
The 15% failure rate you’re seeing should drop to 0% after hierarchy fixes. The NULL custom fields should be completely resolved by the field standardization script.
This draft is based on general Oracle Agile PLM knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.