I’ll provide you with a comprehensive solution addressing all three critical areas:
1. Cost Element Mapping Configuration
The “cost element mapping not found” error is your primary issue. Here’s how to fix it:
Step 1: Audit Current Cost Element Usage
-- Find items with unmapped cost elements
SELECT DISTINCT c.item_number, c.cost_element_id, ce.element_name
FROM item_costs c
LEFT JOIN cost_elements ce ON c.cost_element_id = ce.id
WHERE ce.id IS NULL OR ce.active = 'N';
Step 2: Create ERP-to-Agile Cost Element Mapping
Your ERP structure needs to map to Agile’s cost elements:
| ERP Cost Type |
Agile Cost Element |
Element ID |
Notes |
| Material |
Material |
1000 |
Direct mapping |
| Labor |
Labor |
1001 |
Direct mapping |
| Burden |
Overhead |
1002 |
Overhead includes burden |
| Freight |
Material |
1000 |
Add to material cost |
Step 3: Update Migrated Cost Records
-- Update cost element mapping for migrated data
UPDATE item_costs
SET cost_element_id = CASE
WHEN legacy_cost_type = 'Material' THEN 1000
WHEN legacy_cost_type = 'Labor' THEN 1001
WHEN legacy_cost_type = 'Burden' THEN 1002
WHEN legacy_cost_type = 'Freight' THEN 1000
ELSE cost_element_id
END
WHERE import_batch = 'BOM_MIGRATION_2025';
Step 4: Verify Cost Basis Consistency
Agile supports multiple cost bases (Standard, Current, Simulated). Your BOM import must use consistent cost basis:
- Check Admin > Cost Management > Cost Basis Settings
- Verify all migrated items use the same cost basis for rollup
- Default is usually ‘Standard Cost’ - confirm your import used this
2. BOM Import Validation and Correction
Several BOM-related issues can break cost rollup:
Issue A: Zero Quantities
The “zero quantity detected” error indicates BOM quantity problems:
-- Find problematic BOM quantities
SELECT parent_item, component_item, quantity
FROM bom_lines
WHERE quantity IS NULL
OR quantity <= 0
OR quantity > 999999 -- Unrealistic quantities
ORDER BY parent_item;
Fix with:
UPDATE bom_lines
SET quantity = 1
WHERE quantity IS NULL OR quantity <= 0;
Issue B: Include in Cost Rollup Flag
Verify BOM lines are marked for rollup inclusion:
SELECT COUNT(*) as excluded_count
FROM bom_lines
WHERE include_in_cost_rollup = 'N'
AND import_date >= '2025-09-01';
Update if needed:
UPDATE bom_lines
SET include_in_cost_rollup = 'Y'
WHERE import_batch = 'BOM_MIGRATION_2025'
AND item_type = 'Part'; -- Don't include reference docs
Issue C: BOM Structure Validation
Verify no circular references or orphaned components:
-- Check for circular BOM references
WITH RECURSIVE bom_hierarchy AS (
SELECT parent_item, component_item, 1 as level
FROM bom_lines
UNION ALL
SELECT h.parent_item, b.component_item, h.level + 1
FROM bom_hierarchy h
JOIN bom_lines b ON h.component_item = b.parent_item
WHERE h.level < 20
)
SELECT parent_item, component_item
FROM bom_hierarchy
WHERE parent_item = component_item; -- Circular reference
3. Cost Rollup Job Configuration and Logs
Understanding Rollup Job Behavior:
The cost rollup job processes BOMs bottom-up (components first, then assemblies). Failures cascade:
Step 1: Enable Detailed Logging
// In cost rollup job configuration
CostRollupJob job = new CostRollupJob();
job.setLogLevel("DEBUG");
job.setLogDetail(true);
job.setFailureAction("CONTINUE"); // Don't stop on first error
Step 2: Analyze Job Logs
Look for these patterns in agile_cost_rollup.log:
WARNING: Cost element mapping not found
→ Missing or invalid cost_element_id in item_costs table
ERROR: Cannot calculate rollup - missing cost basis
→ Item has no cost record for the specified cost basis
INFO: Skipping assembly - zero quantity
→ BOM line has null or zero quantity
ERROR: Circular reference detected
→ Item references itself in BOM structure
Step 3: Fix Specific Rollup Failures
For your 2,500 affected assemblies:
-- Identify assemblies with rollup failures
SELECT a.item_number, a.description,
COUNT(b.component_item) as component_count,
SUM(CASE WHEN c.cost_amount IS NULL THEN 1 ELSE 0 END) as missing_costs
FROM items a
JOIN bom_lines b ON a.item_number = b.parent_item
LEFT JOIN item_costs c ON b.component_item = c.item_number
WHERE a.item_type = 'Assembly'
AND a.rollup_cost = 0
GROUP BY a.item_number, a.description
ORDER BY missing_costs DESC;
Step 4: Manual Rollup Trigger
After fixing data issues, trigger rollup for specific items:
// Programmatic rollup trigger
public void recalculateCosts(List<String> itemNumbers) {
ICostManager costMgr = agile.getCostManager();
for (String itemNum : itemNumbers) {
IItem item = (IItem) agile.getObject(IItem.OBJECT_TYPE, itemNum);
costMgr.calculateRollupCost(item, "Standard");
}
}
Complete Remediation Process:
Phase 1: Data Cleanup (Do First)
- Run cost element validation query
- Update all migrated costs with proper element mapping
- Fix BOM quantities (nulls and zeros)
- Set include_in_cost_rollup flag to ‘Y’
- Validate BOM structure (no circular refs)
Phase 2: Configuration Verification
- Confirm cost basis setting (Standard vs Current)
- Verify cost element definitions are active
- Check rollup job configuration
- Enable detailed logging
Phase 3: Incremental Rollup
- Start with leaf items (no BOM children)
- Run rollup job for 100 items at a time
- Review logs after each batch
- Fix any new issues discovered
- Proceed to next level assemblies
Phase 4: Validation
-- Validate rollup results
SELECT a.item_number,
a.rollup_cost as agile_cost,
e.standard_cost as erp_cost,
ABS(a.rollup_cost - e.standard_cost) as variance,
CASE WHEN ABS(a.rollup_cost - e.standard_cost) > 0.01
THEN 'REVIEW' ELSE 'OK' END as status
FROM items a
JOIN erp_cost_comparison e ON a.item_number = e.part_number
WHERE a.item_type = 'Assembly'
ORDER BY variance DESC;
Root Cause Analysis for Your Specific Issues:
Based on your error messages:
-
“Cost element mapping not found” = Your BOM import didn’t populate cost_element_id properly. The fix is the SQL update I provided above.
-
“Cannot calculate rollup - missing cost basis” = Some components have no cost record at all. Query for items in BOMs that have no entry in item_costs table:
SELECT DISTINCT b.component_item
FROM bom_lines b
LEFT JOIN item_costs c ON b.component_item = c.item_number
WHERE c.item_number IS NULL;
- “Zero quantity detected” = BOM quantity data quality issue. Use the quantity fix SQL above.
Expected Outcome:
After implementing these fixes:
- All 2,500 assemblies should have valid rollup costs
- Cost rollup job should complete without errors
- Variance between Agile and ERP costs should be < 1%
- Pricing and margin analysis can resume with accurate data
Run a final validation comparing 20-30 assemblies between Agile rollup cost and your ERP standard cost to confirm accuracy before declaring the migration complete.
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.