Documentation for the automated stock transfer and returns synchronization between KDL and SAT systems

Stock Transfer & Returns Synchronization

The stock transfer and returns synchronization ensures that all stock movements, product returns, and associated damages recorded in KDL are regularly transferred to SAT, supporting accurate inventory management, loss tracking, and reconciliation workflows.

Overview

This scheduled integration point synchronizes both stock transfers and product returns from KDL to SAT. The process handles stock movements between warehouses, product returns from customers, and damage reports, ensuring comprehensive inventory tracking across both systems.

Technical Details

Code Location

Controller Methods:
- SATIntegrationController@pullStock (production)
- SATIntegrationController@testPullStock (testing)
Registration: app/Console/Kernel.php (scheduler configuration)
Primary Tables:
- Transfers: TransferHeader, TransferLines
- Returns: ReturnHeader, ReturnLines, ReturnDamages

Execution Schedule

The stock synchronization is executed on a scheduled basis. The exact timing should be confirmed in the Laravel scheduler configuration.

// Example scheduler configuration
$schedule->command('sat-pull:stock')->everyThirtyMinutes();

Data Types

1. Stock Transfers (stock_in)

Stock movements from warehouses with status 'Printed' and type 'TMI':

// Stock transfer structure (from pullStock method)
$stockTransfer = [
    'doc_num' => $transferID,                    // TransferHeader.TransferID
    'vehicle_code' => $vehicle,                  // TransferHeader.Vehicle
    'warehouse_code' => $transferFrom,           // TransferHeader.TransferFrom
    'store_code' => $transferTo,                 // TransferHeader.TransferTo
    'region' => $region,                         // TransferHeader.Region
    'Date' => $createdOn,                        // TransferHeader.CreatedOn
    'Remarks' => $reference,                     // TransferHeader.Reference
    'warehouse_desc' => $transferFromDesc,       // TransferHeader.TransferFromDesc
    'store_desc' => $transferToDesc,             // TransferHeader.TransferToDesc
    'user_code' => null,                         // Set to null
    'item_list' => [...],                        // Transfer line items
    'remarks' => 'Inventory Transfers'           // Fixed value
];

2. Product Returns (stock_out)

Returns from ReturnHeader joined with TransferHeader for vehicle/region info:

// Return structure (from pullStock method)
$productReturn = [
    'doc_num' => $returnID,                      // ReturnHeader.ReturnID
    'vehicle_code' => $vehicle,                  // TransferHeader.Vehicle (joined)
    'warehouse_code' => $returnFrom,             // ReturnHeader.ReturnFrom
    'store_code' => $returnTo,                   // ReturnHeader.ReturnTo
    'region' => $region,                         // TransferHeader.Region (joined)
    'Date' => $createdOn,                        // ReturnHeader.CreatedOn
    'Remarks' => $reference,                     // TransferHeader.Reference (joined)
    'warehouse_desc' => $returnFromDesc,         // ReturnHeader.ReturnFromDesc
    'store_desc' => $returnToDesc,               // ReturnHeader.ReturnToDesc
    'user_code' => null,                         // Set to null
    'item_list' => [...],                        // Merged return lines + damages
    'remarks' => 'Inventory Transfers'           // Fixed value
];

Line Item Structures

Stock Transfer Line Items

// From TransferLines table
$transferLineItem = [
    'transfer_id' => $transferID,                // TransferLines.TransferID
    'item_code' => $stockCode,                   // TransferLines.StockCode
    'uom_code' => $orderUOM,                     // TransferLines.OrderUOM
    'item_quantity' => $quantity,                // TotalQty or Packets (if > 0)
    'remarks' => $description,                   // TransferLines.Description
    'item_price' => $fixedPrice,                 // TransferLines.FixedPrice
    'item_batch' => null,                        // Set to null
    'doc_num' => $docNum,                        // Parent document number
    'warehouse_code' => $warehouseCode,          // Parent warehouse code
    'store_code' => $storeCode                   // Parent store code
];

Return Line Items (Merged from ReturnLines + ReturnDamages)

// From ReturnLines
$returnLineItem = [
    'transfer_id' => $returnID,                  // ReturnLines.ReturnID
    'item_code' => $stockCode,                   // ReturnLines.StockCode
    'uom_code' => $orderUOM,                     // ReturnLines.OrderUOM
    'item_quantity' => $returnPackets,           // ReturnLines.ReturnPackets
    'remarks' => $reason,                        // ReturnLines.Reason
    'item_price' => $fixedPrice,                 // ReturnLines.FixedPrice
    'item_batch' => null,                        // Set to null
    'doc_num' => $docNum,                        // Parent document number
    'warehouse_code' => $warehouseCode,          // Parent warehouse code
    'store_code' => $storeCode                   // Parent store code
];

// From ReturnDamages (merged with above, quantities summed by item_code)
$damageLineItem = [
    'requisition_id' => $returnID,               // ReturnDamages.ReturnID
    'item_code' => $stockCode,                   // ReturnDamages.StockCode
    'uom_code' => $uom,                          // ReturnDamages.Uom
    'item_quantity' => $damagePkt,               // ReturnDamages.DamagePkt
    'remarks' => $damageReason,                  // ReturnDamages.DamageReason
    'item_price' => $fixedPrice,                 // ReturnDamages.FixedPrice
    'item_batch' => null,                        // Set to null
    'doc_num' => $docNum,                        // Parent document number
    'warehouse_code' => $warehouseCode,          // Parent warehouse code
    'store_code' => $storeCode                   // Parent store code
];

Sample Data Structures

Stock Transfer Example

{
  "doc_num": "12345",
  "vehicle_code": "KCA123A",
  "warehouse_code": "WH001",
  "store_code": "ST001",
  "region": "Nairobi",
  "Date": "2024-01-15 09:00:00",
  "Remarks": "Routine transfer",
  "warehouse_desc": "Main Warehouse",
  "store_desc": "Branch Store",
  "user_code": null,
  "remarks": "Inventory Transfers",
  "item_list": [
    {
      "transfer_id": "12345",
      "item_code": "PROD001",
      "uom_code": "PCS",
      "item_quantity": 50,
      "remarks": "Product description",
      "item_price": 150.00,
      "item_batch": null,
      "doc_num": "12345",
      "warehouse_code": "WH001",
      "store_code": "ST001"
    }
  ]
}

Return Example

{
  "doc_num": "RTN-67890",
  "vehicle_code": "KCA456B",
  "warehouse_code": "ST001",
  "store_code": "WH001",
  "region": "Nairobi",
  "Date": "2024-01-15 14:30:00",
  "Remarks": "Customer return",
  "warehouse_desc": "Branch Store",
  "store_desc": "Main Warehouse",
  "user_code": null,
  "remarks": "Inventory Transfers",
  "item_list": [
    {
      "transfer_id": "RTN-67890",
      "item_code": "PROD001",
      "uom_code": "PCS",
      "item_quantity": 15,
      "remarks": "Defective item",
      "item_price": 150.00,
      "item_batch": null,
      "doc_num": "RTN-67890",
      "warehouse_code": "ST001",
      "store_code": "WH001"
    }
  ]
}

Database Schema

Transfer Tables

-- TransferHeader: Transfer document headers
SELECT 
    TransferID,
    TransferType,
    Vehicle,
    BranchName,
    TransferTo,
    Region,
    CreatedOn,
    Reference,
    SAT_SYNC
FROM TransferHeader;

-- TransferLines: Transfer line items
SELECT 
    TransferID,
    StockCode,
    OrderUOM,
    Qty,
    FixedPrice
FROM TransferLines;

Processing Logic

Transfer Processing

// Select unsynced transfers
$transfers = TransferHeader::where('SAT_SYNC', '!=', 'Y')
    ->orWhereNull('SAT_SYNC')
    ->with('transferLines')
    ->get();

foreach ($transfers as $transfer) {
    $transferData = [
        'type' => $transfer->TransferType,
        'doc_num' => $transfer->TransferID,
        'item_list' => []
    ];
    
    foreach ($transfer->transferLines as $line) {
        $transferData['item_list'][] = [
            'transfer_id' => $line->TransferID,
            'item_code' => $line->StockCode,
            'item_quantity' => $line->Qty,
            'item_batch' => null
        ];
    }
}

Configuration

Environment Variables

SAT_BASE_URL=https://kdl.solutechlabs.com/api
SAT_PASSWORD_AUTH=your_api_token
SAT_STOCK_ENDPOINT=/api/stock-movements

Laravel Configuration

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

Monitoring & Logging

LogSatRequest Tracking

-- Recent stock sync attempts
SELECT 
    created_at,
    success,
    error,
    doc_num,
    type
FROM LogSatRequest 
WHERE type = 'sat.stocks'
ORDER BY created_at DESC 
LIMIT 25;

-- Stock sync success rates by type
SELECT 
    type,
    COUNT(CASE WHEN success = true THEN 1 END) as successful,
    COUNT(CASE WHEN success = false THEN 1 END) as failed,
    ROUND(COUNT(CASE WHEN success = true THEN 1 END) * 100.0 / COUNT(*), 2) as success_rate
FROM LogSatRequest 
WHERE type = 'sat.stocks'
AND created_at >= NOW() - INTERVAL 7 DAY
GROUP BY type;

Troubleshooting

Issue: Stock Movements Not Syncing

Symptoms: SAT_SYNC flag not updated to 'Y'

Diagnostic Steps:

Check stock records and make sure the column 'Status','=','Printed'
Test SAT API connectivity or the KDL Server uptime

Stock Transfer & Returns Synchronization

On this page