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.