Here’s a comprehensive solution addressing all three focus areas:
BOM Structure Validation:
D365’s BOMEntity enforces strict referential integrity during import. Every component item referenced in a BOM line must exist in the InventTable with valid configuration before the BOM can be created. This validation occurs at the staging level, preventing incomplete BOM structures from entering the system.
Validation checks include:
- Component ItemId exists in InventTable
- Component item has valid dimension groups (Storage, Tracking, Product)
- Component item has active status for the transaction type (Production, Purchase)
- BOM line quantities are within valid ranges
- Effective dates are logically consistent
- Component item type is compatible with BOM line type (Item, BOM, Formula)
Item Master Completeness:
Before importing BOMs, ensure item master completeness:
- Gap Analysis:
-- Identify missing component items
SELECT DISTINCT b.ComponentItemId
FROM LegacyBOM b
LEFT JOIN InventTable i ON b.ComponentItemId = i.ItemId
WHERE i.ItemId IS NULL
ORDER BY b.ComponentItemId
2. **Item Classification:**
Categorize missing items by type:
- Purchased items: Require vendor setup, lead times, pricing
- Manufactured items: Require their own BOMs, routing data
- Subassemblies: May be in design phase, need engineering input
- Generic/phantom items: May need special BOM types
3. **Placeholder Item Strategy:**
Create minimal viable items to unblock BOM import:
Minimum required setup:
ItemId: Use legacy number or generate new
ItemName: "[PLACEHOLDER] " + description
ItemType: Item (not BOM or Service)
ItemModelGroup: Standard (matches production items)
ItemGroup: Create “PLACEHOLDER” group for tracking
StorageDimensionGroup: Site-Warehouse (minimal)
TrackingDimensionGroup: None (unless required by parent BOM)
ProductDimensionGroup: None
4. **Placeholder Identification:**
Add custom field to InventTable:
- IsPlaceholder: Boolean flag
- PlaceholderReason: Text (In Design, Awaiting Vendor, Under Review)
- PlaceholderCreatedDate: Date
- ExpectedCompletionDate: Date
5. **Transaction Blocking Strategy:**
Configure item status to prevent misuse:
- Block for purchase orders (until vendor setup complete)
- Block for sales orders (placeholder items shouldn't be sellable)
- Allow for production (needed for MRP calculation)
- Allow for BOM calculations (needed for costing)
**Legacy MRP Data Mapping:**
Transform legacy BOM data to meet D365 requirements:
1. **Data Enrichment Process:**
Phase 1 - Item Master Completion:
- Import all finished goods first
- Import all existing component items
- Create placeholder items for missing components
- Validate dimension group consistency
Phase 2 - BOM Structure Import:
- Import single-level BOMs (no subassemblies)
- Import multi-level BOMs (with subassemblies)
- Validate BOM explosion logic
- Test MRP calculation with sample BOMs
2. **Mapping Transformation:**
```sql
-- BOM transformation with item validation
CREATE VIEW vw_BOM_Import AS
SELECT
b.ParentItemId AS ItemId,
b.ComponentItemId,
b.Quantity,
b.UnitId,
CASE
WHEN i.ItemId IS NULL THEN 1 -- Flag for placeholder creation
ELSE 0
END AS NeedsPlaceholder,
b.EffectiveDate,
b.ExpirationDate
FROM LegacyBOM b
LEFT JOIN InventTable i ON b.ComponentItemId = i.ItemId
- Legacy Data Quality Issues:
Common problems requiring cleanup:
- Obsolete components still in BOM (remove or mark inactive)
- Incorrect quantities or units of measure (validate against standards)
- Missing effectivity dates (apply defaults based on business rules)
- Circular references in multi-level BOMs (detect and resolve)
- Phantom items not properly flagged (convert to appropriate BOM type)
Complete Implementation Approach:
-
Pre-Migration Preparation:
- Run gap analysis to identify all missing items
- Classify missing items by urgency and type
- Create placeholder items in D365 with proper flags
- Set up custom reports to track placeholder completion status
- Define workflow for completing placeholder items
-
Phased Import Strategy:
Phase 1 - Complete BOMs (85% of data):
- Import BOMs where all components exist
- Validate BOM explosion and costing
- Run sample MRP calculations
Phase 2 - BOMs with Placeholders (15% of data):
- Import BOMs referencing placeholder items
- Flag these BOMs for review in production planning
- Create task list for completing placeholder items
Phase 3 - Placeholder Resolution:
- Complete item setup as information becomes available
- Update BOM data if quantities/specs change
- Remove placeholder flags once validated
-
MRP Configuration:
- Configure MRP parameters to handle placeholder items
- Set up planning groups to exclude incomplete items if needed
- Create custom filters for production schedulers
- Test MRP runs with mixed complete/placeholder BOMs
-
Data Governance:
- Create dashboard showing placeholder item status
- Set up alerts for BOMs using placeholder items
- Define SLA for completing placeholder items (e.g., 30 days)
- Implement approval workflow for new item creation
- Regular review meetings to clear placeholder backlog
-
Validation and Testing:
- Verify BOM explosion for sample finished goods
- Compare MRP results to legacy system
- Test costing calculation with placeholder items
- Validate that blocked items don’t create purchase requisitions
- Test production order creation with mixed BOMs
-
Post-Migration Monitoring:
-- Report: BOMs with placeholder components
SELECT
b.ItemId AS FinishedGood,
b.ComponentItemId,
i.ItemName,
i.PlaceholderReason,
i.ExpectedCompletionDate,
COUNT(*) OVER (PARTITION BY b.ItemId) AS PlaceholderCount
FROM BOM b
JOIN InventTable i ON b.ComponentItemId = i.ItemId
WHERE i.IsPlaceholder = 1
ORDER BY i.ExpectedCompletionDate
The key is balancing speed-to-go-live with data quality. Placeholder items enable BOM import and basic MRP functionality while maintaining referential integrity. The custom flagging system ensures visibility and drives completion of incomplete items post-migration, preventing placeholder items from becoming permanent technical debt.