Skip to main content

Product Master, BOM & Sourcing Integration System

Executive Summary​

A comprehensive system to capture, structure, and intelligently process product master data, bills of materials (BOMs), and sourcing information to enable advanced S&OP analytics, constraint intelligence, and supply risk assessment.

1. Data Architecture & Database Schema​

Core Entity Relationships​

-- Product Master Table
CREATE TABLE products (
    product_id VARCHAR(50) PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    product_category VARCHAR(100),
    product_family VARCHAR(100),
    lifecycle_stage ENUM('development', 'introduction', 'growth', 'maturity', 'decline', 'discontinued'),
    abc_classification ENUM('A', 'B', 'C'),
    
    -- Financial attributes
    standard_cost DECIMAL(15,4),
    selling_price DECIMAL(15,4),
    currency_code VARCHAR(3),
    margin_percent DECIMAL(5,2),
    
    -- Planning attributes
    lead_time_days INTEGER,
    safety_stock_days INTEGER,
    minimum_order_quantity INTEGER,
    order_multiple INTEGER,
    make_vs_buy ENUM('make', 'buy', 'mixed'),
    
    -- Physical attributes
    unit_of_measure VARCHAR(10),
    weight_kg DECIMAL(10,4),
    volume_m3 DECIMAL(10,6),
    shelf_life_days INTEGER,
    
    -- Lifecycle management
    introduction_date DATE,
    planned_eol_date DATE,
    replacement_product_id VARCHAR(50),
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    data_source VARCHAR(50),
    data_quality_score DECIMAL(3,2)
);

-- Bill of Materials Header
CREATE TABLE bom_headers (
    bom_id VARCHAR(50) PRIMARY KEY,
    parent_product_id VARCHAR(50) NOT NULL,
    bom_version VARCHAR(10) NOT NULL,
    effective_date DATE NOT NULL,
    expiration_date DATE,
    bom_type ENUM('manufacturing', 'engineering', 'planning', 'costing'),
    status ENUM('active', 'inactive', 'pending'),
    
    -- Planning attributes
    yield_percentage DECIMAL(5,2) DEFAULT 100.00,
    setup_time_minutes DECIMAL(8,2),
    run_time_per_unit_seconds DECIMAL(8,2),
    
    -- Cost rollup
    material_cost DECIMAL(15,4),
    labor_cost DECIMAL(15,4),
    overhead_cost DECIMAL(15,4),
    total_cost DECIMAL(15,4),
    
    FOREIGN KEY (parent_product_id) REFERENCES products(product_id),
    INDEX idx_product_version (parent_product_id, bom_version),
    INDEX idx_effective_date (effective_date)
);

-- Bill of Materials Lines
CREATE TABLE bom_lines (
    bom_line_id VARCHAR(50) PRIMARY KEY,
    bom_id VARCHAR(50) NOT NULL,
    component_product_id VARCHAR(50) NOT NULL,
    sequence_number INTEGER,
    
    -- Quantity and usage
    quantity_per_unit DECIMAL(15,6) NOT NULL,
    unit_of_measure VARCHAR(10),
    scrap_percentage DECIMAL(5,2) DEFAULT 0.00,
    
    -- Sourcing information
    is_critical_component BOOLEAN DEFAULT FALSE,
    is_single_source BOOLEAN DEFAULT FALSE,
    substitution_allowed BOOLEAN DEFAULT FALSE,
    
    -- Planning parameters
    component_lead_time_days INTEGER,
    component_yield DECIMAL(5,2) DEFAULT 100.00,
    
    -- Cost information
    component_cost DECIMAL(15,4),
    extended_cost DECIMAL(15,4),
    
    FOREIGN KEY (bom_id) REFERENCES bom_headers(bom_id),
    FOREIGN KEY (component_product_id) REFERENCES products(product_id),
    INDEX idx_bom_component (bom_id, component_product_id)
);

-- Supplier Master
CREATE TABLE suppliers (
    supplier_id VARCHAR(50) PRIMARY KEY,
    supplier_name VARCHAR(255) NOT NULL,
    supplier_type ENUM('manufacturer', 'distributor', 'service_provider'),
    
    -- Geographic information
    primary_country VARCHAR(3),
    primary_region VARCHAR(50),
    manufacturing_countries JSON, -- Array of countries where they manufacture
    
    -- Business attributes
    annual_revenue_usd DECIMAL(15,0),
    employee_count INTEGER,
    certifications JSON, -- Array of certifications (ISO, FDA, etc.)
    
    -- Risk attributes
    financial_stability_rating VARCHAR(10),
    quality_rating VARCHAR(10),
    delivery_performance_score DECIMAL(3,2),
    
    -- ESG factors
    sustainability_score DECIMAL(3,2),
    carbon_footprint_score DECIMAL(3,2),
    social_responsibility_rating VARCHAR(10),
    
    -- Contact and operational
    primary_contact_email VARCHAR(255),
    payment_terms VARCHAR(50),
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Product-Supplier Relationships
CREATE TABLE product_suppliers (
    product_supplier_id VARCHAR(50) PRIMARY KEY,
    product_id VARCHAR(50) NOT NULL,
    supplier_id VARCHAR(50) NOT NULL,
    
    -- Sourcing attributes
    supplier_priority INTEGER, -- 1 = primary, 2 = secondary, etc.
    capacity_percentage DECIMAL(5,2), -- What % of demand this supplier can handle
    minimum_order_quantity INTEGER,
    lead_time_days INTEGER,
    
    -- Pricing information
    unit_price DECIMAL(15,4),
    currency_code VARCHAR(3),
    price_breaks JSON, -- Volume-based pricing tiers
    effective_date DATE,
    expiration_date DATE,
    
    -- Quality and performance
    quality_rating DECIMAL(3,2),
    delivery_performance DECIMAL(3,2),
    cost_competitiveness DECIMAL(3,2),
    
    -- Risk assessment
    supply_risk_score DECIMAL(3,2),
    single_source_risk BOOLEAN DEFAULT FALSE,
    geographic_risk_score DECIMAL(3,2),
    
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
    UNIQUE KEY unique_product_supplier (product_id, supplier_id)
);

-- Supply Chain Network
CREATE TABLE supply_chain_nodes (
    node_id VARCHAR(50) PRIMARY KEY,
    node_type ENUM('supplier', 'manufacturing_plant', 'distribution_center', 'warehouse', 'customer'),
    node_name VARCHAR(255) NOT NULL,
    
    -- Geographic information
    country VARCHAR(3),
    region VARCHAR(50),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    
    -- Capacity information
    capacity_units VARCHAR(20),
    max_capacity DECIMAL(15,2),
    current_utilization DECIMAL(5,2),
    
    -- Cost information
    fixed_cost_per_month DECIMAL(15,2),
    variable_cost_per_unit DECIMAL(10,4),
    
    -- Operational attributes
    operating_calendar JSON, -- Working days, shifts, etc.
    lead_time_days INTEGER,
    
    -- Risk factors
    natural_disaster_risk DECIMAL(3,2),
    political_stability_risk DECIMAL(3,2),
    infrastructure_quality DECIMAL(3,2)
);

-- Product Review Tracking
CREATE TABLE product_reviews (
    review_id VARCHAR(50) PRIMARY KEY,
    review_date DATE NOT NULL,
    review_type ENUM('new_product_introduction', 'end_of_life', 'promotion', 'phase_out', 'regular_review'),
    
    -- Product information
    product_id VARCHAR(50) NOT NULL,
    current_lifecycle_stage VARCHAR(20),
    proposed_lifecycle_stage VARCHAR(20),
    
    -- Financial impact
    revenue_impact DECIMAL(15,2),
    margin_impact DECIMAL(15,2),
    investment_required DECIMAL(15,2),
    
    -- Supply chain impact
    new_suppliers_required INTEGER,
    capacity_impact DECIMAL(10,2),
    inventory_impact DECIMAL(15,2),
    
    -- Demand impact
    demand_forecast_change_percent DECIMAL(5,2),
    cannibalization_risk DECIMAL(3,2),
    market_expansion_potential DECIMAL(3,2),
    
    -- Decision and approvals
    recommendation TEXT,
    decision_status ENUM('approved', 'rejected', 'pending', 'deferred'),
    approved_by VARCHAR(100),
    implementation_date DATE,
    
    -- Participants and stakeholders
    participants JSON, -- Array of participant roles/names
    
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_review_date (review_date),
    INDEX idx_product_review (product_id, review_date)
);

2. Easy Data Input Mechanisms​

CSV/Excel Import Templates​

// Product Master Import Template
const PRODUCT_MASTER_TEMPLATE = {
  required_fields: [
    'product_id', 'product_name', 'product_category', 'lifecycle_stage',
    'standard_cost', 'selling_price', 'lead_time_days'
  ],
  optional_fields: [
    'product_family', 'abc_classification', 'safety_stock_days',
    'minimum_order_quantity', 'make_vs_buy', 'weight_kg', 'shelf_life_days'
  ],
  validation_rules: {
    'product_id': 'unique, max_length: 50',
    'lifecycle_stage': 'enum: development|introduction|growth|maturity|decline|discontinued',
    'standard_cost': 'numeric, min: 0',
    'lead_time_days': 'integer, min: 0'
  }
};

// BOM Import Template
const BOM_TEMPLATE = {
  required_fields: [
    'parent_product_id', 'component_product_id', 'quantity_per_unit'
  ],
  optional_fields: [
    'bom_version', 'sequence_number', 'scrap_percentage',
    'is_critical_component', 'substitution_allowed'
  ],
  hierarchical_structure: true, // Supports multi-level BOMs
  validation_rules: {
    'parent_product_id': 'must_exist_in_products_table',
    'component_product_id': 'must_exist_in_products_table',
    'quantity_per_unit': 'numeric, min: 0'
  }
};

// Supplier and Sourcing Template
const SOURCING_TEMPLATE = {
  supplier_sheet: {
    required_fields: ['supplier_id', 'supplier_name', 'primary_country'],
    optional_fields: ['supplier_type', 'annual_revenue_usd', 'certifications']
  },
  product_supplier_sheet: {
    required_fields: ['product_id', 'supplier_id', 'supplier_priority', 'unit_price'],
    optional_fields: ['lead_time_days', 'minimum_order_quantity', 'quality_rating']
  }
};

Web-Based Data Entry Interface​

// Product Master Entry Component
const ProductMasterEntry = () => {
  const [products, setProducts] = useState([]);
  const [currentProduct, setCurrentProduct] = useState({});
  const [validationErrors, setValidationErrors] = useState({});

  const productForm = {
    basic_info: {
      title: "Basic Product Information",
      fields: [
        { name: 'product_id', label: 'Product ID', type: 'text', required: true },
        { name: 'product_name', label: 'Product Name', type: 'text', required: true },
        { name: 'product_category', label: 'Category', type: 'select', options: 'categories' },
        { name: 'lifecycle_stage', label: 'Lifecycle Stage', type: 'select', options: 'lifecycle_stages' }
      ]
    },
    financial: {
      title: "Financial Information",
      fields: [
        { name: 'standard_cost', label: 'Standard Cost', type: 'currency', required: true },
        { name: 'selling_price', label: 'Selling Price', type: 'currency', required: true },
        { name: 'margin_percent', label: 'Target Margin %', type: 'percentage' }
      ]
    },
    planning: {
      title: "Planning Parameters",
      fields: [
        { name: 'lead_time_days', label: 'Lead Time (Days)', type: 'integer', required: true },
        { name: 'safety_stock_days', label: 'Safety Stock (Days)', type: 'integer' },
        { name: 'make_vs_buy', label: 'Make vs Buy', type: 'select', options: ['make', 'buy', 'mixed'] }
      ]
    }
  };

  return (
    <div className="product-entry-form">
      <WizardForm
        sections={productForm}
        currentData={currentProduct}
        onDataChange={setCurrentProduct}
        onValidationChange={setValidationErrors}
        onSave={handleProductSave}
        onBulkImport={handleBulkImport}
      />
    </div>
  );
};

// BOM Entry Component with Visual Tree Structure
const BOMEntry = ({ productId }) => {
  const [bomTree, setBomTree] = useState({});
  const [draggedItem, setDraggedItem] = useState(null);

  return (
    <div className="bom-entry-interface">
      <div className="bom-header">
        <ProductSelector 
          selectedProduct={productId}
          onProductChange={handleProductChange}
        />
        <BOMVersionControl
          currentVersion={bomTree.version}
          onVersionChange={handleVersionChange}
        />
      </div>

      <div className="bom-structure">
        <TreeView
          data={bomTree}
          onNodeAdd={handleAddComponent}
          onNodeEdit={handleEditComponent}
          onNodeDelete={handleDeleteComponent}
          dragAndDrop={true}
          onDragEnd={handleDragEnd}
        />
      </div>

      <div className="component-library">
        <SearchableProductList
          onProductSelect={handleComponentAdd}
          filterByType="component"
        />
      </div>
    </div>
  );
};

Integration APIs for ERP Systems​

// ERP Integration Service
class ERPIntegrationService {
  constructor() {
    this.supportedERPs = {
      'SAP': new SAPConnector(),
      'Oracle': new OracleConnector(),
      'Microsoft Dynamics': new DynamicsConnector(),
      'NetSuite': new NetSuiteConnector()
    };
  }

  async syncProductMaster(erpSystem, connectionConfig) {
    const connector = this.supportedERPs[erpSystem];
    
    try {
      // Extract product master data from ERP
      const erpProducts = await connector.extractProducts(connectionConfig);
      
      // Transform to ChainAlign schema
      const transformedProducts = this.transformProductData(erpProducts, erpSystem);
      
      // Validate data quality
      const validationResults = await this.validateProducts(transformedProducts);
      
      // Load into ChainAlign database
      const loadResults = await this.loadProducts(transformedProducts);
      
      return {
        extracted: erpProducts.length,
        transformed: transformedProducts.length,
        validation_errors: validationResults.errors,
        loaded: loadResults.success_count,
        failed: loadResults.failure_count
      };
      
    } catch (error) {
      throw new Error(`ERP sync failed: ${error.message}`);
    }
  }

  transformProductData(erpProducts, erpSystem) {
    return erpProducts.map(product => {
      // Standard transformation logic
      const transformed = {
        product_id: product.material_number || product.item_code,
        product_name: product.description,
        product_category: this.mapCategory(product.product_hierarchy),
        lifecycle_stage: this.mapLifecycleStage(product.status),
        standard_cost: product.standard_price,
        selling_price: product.sales_price,
        lead_time_days: product.procurement_lead_time,
        data_source: erpSystem,
        data_quality_score: this.calculateDataQuality(product)
      };
      
      // ERP-specific mappings
      if (erpSystem === 'SAP') {
        transformed.abc_classification = product.abc_indicator;
        transformed.make_vs_buy = product.procurement_type === 'F' ? 'make' : 'buy';
      }
      
      return transformed;
    });
  }
}

// SAP-specific connector example
class SAPConnector {
  async extractProducts(config) {
    // Connect to SAP via RFC or OData
    const sapConnection = new SAPConnection(config);
    
    const query = `
      SELECT 
        MATNR as material_number,
        MAKTX as description,
        PRDHA as product_hierarchy,
        MMSTA as status,
        STPRS as standard_price,
        PEINH as price_unit,
        PLIFZ as procurement_lead_time,
        BESKZ as procurement_type,
        ABCIN as abc_indicator
      FROM MARA 
      INNER JOIN MAKT ON MARA.MATNR = MAKT.MATNR
      INNER JOIN MBEW ON MARA.MATNR = MBEW.MATNR
      WHERE MAKT.SPRAS = 'EN'
      AND MARA.LVORM = ''  -- Not marked for deletion
    `;
    
    return await sapConnection.executeQuery(query);
  }

  async extractBOMs(config) {
    const sapConnection = new SAPConnection(config);
    
    const query = `
      SELECT 
        STLNR as bom_number,
        MATNR as parent_material,
        STLAL as alternative,
        IDNRK as component,
        MENGE as quantity,
        MEINS as unit_of_measure,
        AUSCH as scrap_percentage,
        POTX1 as line_text
      FROM STPO 
      INNER JOIN STKO ON STPO.STLNR = STKO.STLNR
      WHERE STKO.STLTY = 'M'  -- Material BOM
      AND STKO.LOSVN <= CURRENT_DATE 
      AND STKO.LOSBS >= CURRENT_DATE
    `;
    
    return await sapConnection.executeQuery(query);
  }
}

3. AI Preprocessing and Intelligence​

Product Intelligence Engine​

class ProductIntelligenceEngine {
  constructor() {
    this.nlpProcessor = new NLPProcessor();
    this.categoryClassifier = new ProductCategoryClassifier();
    this.riskAssessor = new SupplyRiskAssessor();
    this.costPredictor = new CostPredictionModel();
  }

  async processNewProduct(productData) {
    const intelligence = {
      // Category and classification intelligence
      suggested_category: await this.classifyProduct(productData),
      abc_classification: await this.predictABCClassification(productData),
      lifecycle_prediction: await this.predictLifecycleStage(productData),
      
      // Cost and pricing intelligence
      cost_benchmarks: await this.getBenchmarkCosts(productData),
      margin_optimization: await this.optimizeMargins(productData),
      price_elasticity: await this.analyzePriceElasticity(productData),
      
      // Supply chain intelligence
      complexity_score: await this.assessComplexity(productData),
      supply_risk_assessment: await this.assessSupplyRisk(productData),
      sourcing_recommendations: await this.recommendSourcingStrategy(productData),
      
      // Market intelligence
      competitive_analysis: await this.analyzeCompetitors(productData),
      demand_patterns: await this.analyzeDemandPatterns(productData),
      seasonality_indicators: await this.detectSeasonality(productData)
    };

    return intelligence;
  }

  async classifyProduct(productData) {
    // Use product name, description, and attributes for classification
    const features = this.extractProductFeatures(productData);
    
    // Multi-level classification: Industry -> Category -> Subcategory
    const classification = await this.categoryClassifier.predict({
      name: productData.product_name,
      description: productData.description,
      features: features,
      existing_categories: await this.getExistingCategories()
    });
    
    return {
      suggested_category: classification.primary_category,
      confidence_score: classification.confidence,
      alternative_categories: classification.alternatives,
      reasoning: classification.reasoning_chain
    };
  }

  async assessSupplyRisk(productData) {
    // Analyze BOM complexity and supplier diversity
    const bomData = await this.getBOMData(productData.product_id);
    const supplierData = await this.getSupplierData(productData.product_id);
    
    const riskFactors = {
      // Component-level risks
      critical_components: this.identifyCriticalComponents(bomData),
      single_source_components: this.identifySingleSourceComponents(bomData, supplierData),
      long_lead_time_components: this.identifyLongLeadTimeComponents(bomData),
      
      // Supplier-level risks
      supplier_concentration: this.calculateSupplierConcentration(supplierData),
      geographic_concentration: this.calculateGeographicRisk(supplierData),
      supplier_financial_health: this.assessSupplierFinancialHealth(supplierData),
      
      // External risks
      geopolitical_risks: await this.assessGeopoliticalRisks(supplierData),
      environmental_risks: await this.assessEnvironmentalRisks(supplierData),
      market_volatility_risks: await this.assessMarketVolatilityRisks(bomData)
    };

    // Calculate overall supply risk score
    const overallRisk = this.calculateOverallSupplyRisk(riskFactors);
    
    return {
      overall_risk_score: overallRisk.score,
      risk_level: overallRisk.level, // low, medium, high, critical
      primary_risk_factors: overallRisk.primary_factors,
      mitigation_recommendations: overallRisk.mitigation_strategies,
      risk_monitoring_plan: overallRisk.monitoring_plan
    };
  }

  async optimizeMargins(productData) {
    // Analyze cost structure and pricing opportunities
    const costStructure = await this.analyzeCostStructure(productData);
    const marketPricing = await this.getMarketPricingData(productData);
    const competitorPricing = await this.getCompetitorPricing(productData);
    
    const optimization = {
      // Cost reduction opportunities
      material_cost_reduction: await this.identifyMaterialCostReduction(costStructure),
      process_optimization: await this.identifyProcessOptimization(costStructure),
      supplier_negotiation_targets: await this.identifySupplierNegotiationOpportunities(costStructure),
      
      // Pricing optimization
      optimal_price_range: this.calculateOptimalPricing(marketPricing, competitorPricing),
      price_elasticity_analysis: await this.analyzePriceElasticity(productData),
      value_based_pricing_opportunity: await this.assessValueBasedPricing(productData),
      
      // Margin improvement strategies
      bundling_opportunities: await this.identifyBundlingOpportunities(productData),
      premium_positioning: await this.assessPremiumPositioning(productData),
      volume_discount_optimization: await this.optimizeVolumeDiscounts(productData)
    };

    return optimization;
  }
}

BOM Intelligence and Analysis​

class BOMIntelligenceEngine {
  async analyzeBOM(bomData) {
    const analysis = {
      // Structural analysis
      complexity_metrics: await this.calculateComplexityMetrics(bomData),
      cost_rollup: await this.performCostRollup(bomData),
      lead_time_analysis: await this.analyzeCriticalPath(bomData),
      
      // Risk analysis
      supply_risk_assessment: await this.assessBOMSupplyRisk(bomData),
      single_point_failures: await this.identifySinglePointFailures(bomData),
      obsolescence_risk: await this.assessObsolescenceRisk(bomData),
      
      // Optimization opportunities
      standardization_opportunities: await this.identifyStandardizationOpportunities(bomData),
      cost_reduction_opportunities: await this.identifyCostReductionOpportunities(bomData),
      design_for_manufacturability: await this.assessManufacturability(bomData)
    };

    return analysis;
  }

  calculateComplexityMetrics(bomData) {
    return {
      total_components: bomData.components.length,
      levels_deep: this.calculateBOMDepth(bomData),
      unique_suppliers: this.countUniqueSuppliers(bomData),
      critical_components: this.identifyCriticalComponents(bomData).length,
      complexity_index: this.calculateComplexityIndex(bomData),
      assembly_complexity: this.assessAssemblyComplexity(bomData)
    };
  }

  async identifyStandardizationOpportunities(bomData) {
    // Find similar components across BOMs that could be standardized
    const allBOMs = await this.getAllBOMs();
    const componentUsage = this.analyzeComponentUsage(allBOMs);
    
    const opportunities = [];
    
    // Look for components with similar specifications
    const similarComponents = this.findSimilarComponents(componentUsage);
    
    for (const group of similarComponents) {
      if (group.length > 1 && group.some(c => c.usage_count > 5)) {
        opportunities.push({
          opportunity_type: 'component_standardization',
          components: group,
          estimated_savings: this.calculateStandardizationSavings(group),
          implementation_complexity: this.assessImplementationComplexity(group),
          recommendation: this.generateStandardizationRecommendation(group)
        });
      }
    }
    
    return opportunities;
  }
}

4. Product Review Process Integration​

Product Review Workflow Engine​

class ProductReviewEngine {
  async initiateProductReview(reviewData) {
    const review = {
      review_id: this.generateReviewId(),
      review_type: reviewData.type,
      product_id: reviewData.product_id,
      
      // AI-generated impact analysis
      impact_analysis: await this.generateImpactAnalysis(reviewData),
      
      // Stakeholder analysis
      required_participants: await this.identifyStakeholders(reviewData),
      
      // Recommendations
      ai_recommendations: await this.generateRecommendations(reviewData),
      
      // Supporting data
      market_intelligence: await this.gatherMarketIntelligence(reviewData.product_id),
      financial_projections: await this.generateFinancialProjections(reviewData),
      supply_chain_impact: await this.assessSupplyChainImpact(reviewData)
    };

    return review;
  }

  async generateImpactAnalysis(reviewData) {
    const product = await this.getProductData(reviewData.product_id);
    const historicalData = await this.getHistoricalPerformance(reviewData.product_id);
    
    return {
      // Financial impact
      revenue_impact: {
        current_annual_revenue: historicalData.annual_revenue,
        projected_revenue_change: await this.projectRevenueChange(reviewData, historicalData),
        margin_impact: await this.assessMarginImpact(reviewData, product),
        investment_required: await this.calculateInvestmentRequired(reviewData)
      },
      
      // Market impact  
      market_impact: {
        market_share_effect: await this.assessMarketShareImpact(reviewData),
        competitive_response: await this.predictCompetitiveResponse(reviewData),
        customer_reaction: await this.assessCustomerImpact(reviewData)
      },
      
      // Supply chain impact
      supply_impact: {
        capacity_requirements: await this.assessCapacityImpact(reviewData),
        supplier_impact: await this.assessSupplierImpact(reviewData),
        inventory_impact: await this.assessInventoryImpact(reviewData),
        lead_time_changes: await this.assessLeadTimeImpact(reviewData)
      },
      
      // Risk assessment
      risks: {
        execution_risks: await this.identifyExecutionRisks(reviewData),
        market_risks: await this.identifyMarketRisks(reviewData),
        operational_risks: await this.identifyOperationalRisks(reviewData)
      }
    };
  }

  async generateRecommendations(reviewData) {
    const analysis = await this.generateImpactAnalysis(reviewData);
    
    // AI-driven recommendation engine
    const recommendations = {
      primary_recommendation: await this.generatePrimaryRecommendation(analysis),
      alternative_options: await this.generateAlternativeOptions(analysis),
      implementation_timeline: await this.generateImplementationPlan(reviewData, analysis),
      success_metrics: await this.defineSuccessMetrics(reviewData, analysis),
      monitoring_plan: await this.createMonitoringPlan(reviewData, analysis)
    };

    return recommendations;
  }
}

This comprehensive system addresses your critical gap by providing:

  1. Structured data foundation with proper database schema for products, BOMs, and sourcing
  2. Easy data input mechanisms via templates, web interfaces, and ERP integrations
  3. AI preprocessing that adds intelligence about supply risk, cost optimization, and market positioning
  4. Product review workflow that leverages all this data for strategic decision-making

The system integrates directly with your existing ChainAlign architecture, feeding structured product data into your constraint intelligence engine and financial modeling capabilities. The AI preprocessing ensures that raw product data becomes actionable business intelligence that drives better S&OP decisions.

5. Functional Requirements​

5.1. Data Governance and Quality​

  • FR-5.1.1: The system SHALL implement a mechanism to calculate and update the data_quality_score for product master data, BOMs, and supplier data.
  • FR-5.1.2: The system SHALL define and enforce data governance rules for product master data, including ownership, data cleansing processes, and validation checks.

5.2. Product Review Workflow Enhancements​

  • FR-5.2.1: The system SHALL integrate with the Scenario Modeling Service to enable "what-if" scenario analysis directly from the Product Review Dashboard (e.g., "what if we discontinue Product X").
  • FR-5.2.2: The system SHALL implement a formal approval workflow for product review decisions, including digital signatures and tracking of implementation status.
  • FR-5.2.3: The system SHALL provide comparative analysis capabilities within the Product Review Dashboard, including year-over-year performance and peer benchmarking.
  • FR-5.2.4: The system SHALL store AI-generated insights and recommendations from the Product Review Dashboard in a structured format (e.g., dedicated table or JSONB field in product_reviews table) for traceability and historical context.

5.3. Version Control and Auditability​

  • FR-5.3.1: The system SHALL implement a formal version control process for product master data and BOMs, tracking changes, who made them, when, and why.