Documentation for the automated product data synchronization between KDL and SAT systems

Product Data Synchronization

The product data synchronization ensures that all product records in KDL are regularly transferred to SAT, maintaining current product information for accurate sales, inventory management, and reporting workflows.

Overview

This scheduled integration point runs every five minutes, automatically synchronizing product data from KDL's InvMaster table to SAT. The synchronization includes product details, pricing information, units of measure, and warehouse configurations.

Technical Details

Code Location

Scheduled Command: sat-pull:products
Registration: app/Console/Kernel.php
Controller: SATIntegrationController@pullProducts
Primary Table: InvMaster
Related Tables: InvPrice
Model Method: InvMaster::scopeGetProducts

Execution Schedule

// Laravel Scheduler - every 5 minutes
$schedule->command('sat-pull:products')->everyFiveMinutes();

Data Selection Criteria

Only products meeting the following criteria are synchronized:

SAT_SYNC is 'N' or null
Product matches specific warehouse codes OR is in special product list
Active product records

Data Mapping

The following table shows how KDL product data is transformed for SAT:

KDL Field	Source Table	SAT Field	Transformation Notes
`StockCode`	InvMaster	`product_code`	Direct mapping
`Description`	InvMaster	`product_name`, `description`	Direct mapping
`ProductClass`	InvMaster	`category`	Direct mapping
`TaxCode`	InvMaster	`tax_code`	Direct mapping
`StockUom`	InvMaster	`uom_name`, `uom_code`	Direct mapping
`AlternateUom`	InvMaster	`alt_uom`	Direct mapping
`ConvFactAltUom`	InvMaster	`alt_uom_code`	Used for conversion factor
`Mass`	InvMaster	`weight`	Direct mapping
`TariffCode`	InvMaster	`hs_codes`	Direct mapping
`InvPrice.PriceCode`	InvPrice	`price_code`	Joined from InvPrice

Units of Measure (UOM) Handling

The system creates a comprehensive uom_list for each product:

// Primary UOM
$uom_list[] = [
    'uom_name' => $product->StockUom,
    'uom_code' => $product->StockUom,
    'conversion_factor' => 1
];

// Alternate UOM (if exists)
if ($product->AlternateUom) {
    $uom_list[] = [
        'uom_name' => $product->AlternateUom,
        'uom_code' => $product->AlternateUom,
        'conversion_factor' => $product->ConvFactAltUom ?: 1
    ];
}

Sample Data Structure

{
  "product_code": "PROD001",
  "product_name": "Premium Widget",
  "description": "High-quality premium widget for industrial use",
  "category": "WIDGETS",
  "tax_code": "VAT16",
  "uom_name": "PC",
  "uom_code": "PC",
  "alt_uom": "CTN",
  "alt_uom_code": "CTN",
  "weight": 2.5,
  "hs_codes": "8501.10.00",
  "price_code": "WHOLESALE",
  "uom_list": [
    {
      "uom_name": "PC",
      "uom_code": "PC", 
      "conversion_factor": 1
    },
    {
      "uom_name": "CTN",
      "uom_code": "CTN",
      "conversion_factor": 12
    }
  ]
}

Database Schema

Primary Tables

-- InvMaster: Main product table
SELECT 
    StockCode,
    Description,
    ProductClass,
    TaxCode,
    StockUom,
    AlternateUom,
    ConvFactAltUom,
    Mass,
    TariffCode,
    SAT_SYNC,
    WarehouseToUse
FROM InvMaster;

-- InvPrice: Product pricing information
SELECT 
    StockCode,
    PriceCode,
    Price
FROM InvPrice;

Configuration

Environment Variables

SAT_BASE_URL=https://kdl.solutechlabs.com/api
SAT_PASSWORD_AUTH=your_api_token
SAT_PRODUCTS_API=v1/sap/sap-products

Laravel Configuration

// config/sat.php
return [
    'products' => env('SAT_PRODUCTS_API'),
    'login' => env('SAT_LOGIN_URL'),
];

Advanced Features

Special Products List

Some products may need to sync regardless of warehouse rules:

// Define special products that always sync
$specialProducts = [
    'PROMO001',
    'SAMPLE002', 
    'DEMO003'
];

Batch Processing

Products are processed in batches to optimize performance:

// Process products in chunks
$products = InvMaster::getProducts()->chunk(100, function($chunk) {
    foreach ($chunk as $product) {
        // Process individual product
    }
});

Conversion Factor Validation

// Validate conversion factors
if ($product->ConvFactAltUom && $product->ConvFactAltUom <= 0) {
    Log::warning("Invalid conversion factor for product: {$product->StockCode}");
    $product->ConvFactAltUom = 1; // Default to 1:1 conversion
}

Monitoring & Logging

LogSatRequest Tracking

-- View recent product sync attempts
SELECT 
    created_at,
    success,
    error,
    doc_num,
    JSON_EXTRACT(payload, '$.product_code') as product_code
FROM LogSatRequest 
WHERE type = 'products' 
ORDER BY created_at DESC 
LIMIT 20;

Troubleshooting Guide

Issue: Products Not Syncing

Symptoms: Products remain unsynced despite meeting criteria

Diagnostic Steps:

Check if product meets warehouse criteria
Verify SAT_SYNC flag is not 'Y'
Review scheduler execution logs

Solutions:

-- Check warehouse eligibility
SELECT StockCode, WarehouseToUse, SAT_SYNC 
FROM InvMaster 
WHERE StockCode = 'PROD001';

-- Force product sync
UPDATE InvMaster 
SET SAT_SYNC = NULL 
WHERE StockCode = 'PROD001';

Issue: UOM Conversion Errors

Symptoms: Products fail with conversion factor errors

Solutions:

Validate conversion factors are positive numbers
Set default conversion factor for missing values
Review alternate UOM configurations

Product Data Synchronization

On this page