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

Invoice Data Synchronization

The invoice data synchronization ensures that all sales invoices generated in KDL are regularly transferred to SAT, maintaining up-to-date sales and financial records for accurate reporting, reconciliation, and downstream business processes.

Overview

This scheduled integration point automatically synchronizes invoice data from KDL's SalesImport table to SAT. Each invoice includes header information and associated line items grouped by OrderRef (invoice number).

Technical Details

Code Location

Controller Method: SATIntegrationController@pullInvoices
Registration: app/Console/Kernel.php (scheduler configuration)
Primary Table: SalesImport
Grouping Field: OrderRef (Invoice Number)

Execution Schedule

The invoice 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:invoices')->hourly();

Data Structure

Invoice Header

Each invoice is represented by a single header record with associated line items:

// Invoice structure (from PushInvoicesToSAT.php)
$mapped = [
    'doc_num' => trim($item->doc_num),                    // Sales order number (SalesHeader.SalesOrder)
    'date' => Carbon::parse($item->date)                  // Invoice creation date (SalesHeader.CreatedOn)
        ->startOfDay()->format('Y-m-d H:i:s'),
    'warehouse_code' => $item->warehouse_code,            // Dispatch warehouse (SalesHeader.DispatchFrom)
    'vehicle_code' => $item->vehicle_code,                // Vehicle code (SalesHeader.Vehicle)
    'customer_code' => $item->customer_code,              // Customer code (SalesHeader.Customer)
    'user_code' => $item->user_code,                      // User who created (SalesHeader.CreatedBy)
    'item_list' => $item->invoiceItems                    // Line items from SalesImport
];

Line Items

Each invoice contains multiple line items with product and quantity details:

// Line item structure (from GetInvoicesToSyncService.php)
$lineItem = [
    'OrderRef' => $orderRef,          // Reference to sales order
    'SalesOrder' => $salesOrder,      // Sales order ID
    'uom_code' => $orderUOM,          // Unit of measure (SalesImport.OrderUOM)
    'item_code' => $stockCode,        // Product code (SalesImport.StockCode)
    'item_quantity' => $totalQty,     // Quantity (SalesImport.TotalQuantity)
    'quantity' => $totalQty,          // Duplicate quantity field
    'item_price' => $fixedPrice,      // Unit price (SalesImport.FixedPrice)
    'SAT_SYNC' => $syncStatus         // Sync status flag
];

Data Mapping

KDL Field	Source Table	SAT Field	Description
`SalesOrder`	SalesHeader	`doc_num`	Sales order number
`CreatedOn`	SalesHeader	`date`	Invoice creation date (formatted to start of day)
`DispatchFrom`	SalesHeader	`warehouse_code`	Dispatch warehouse code
`Vehicle`	SalesHeader	`vehicle_code`	Vehicle code
`Customer`	SalesHeader	`customer_code`	Customer code
`CreatedBy`	SalesHeader	`user_code`	User who created the invoice
`OrderRef`	SalesImport	`OrderRef`	Reference to sales order
`SalesOrder`	SalesImport	`SalesOrder`	Sales order ID
`OrderUOM`	SalesImport	`uom_code`	Unit of measure
`StockCode`	SalesImport	`item_code`	Product code
`TotalQuantity`	SalesImport	`item_quantity`, `quantity`	Quantity (duplicated fields)
`FixedPrice`	SalesImport	`item_price`	Unit price
`SAT_SYNC`	SalesImport	`SAT_SYNC`	Sync status flag

Sample Data Structure

{
  "doc_num": "12345",
  "date": "2024-01-15 00:00:00",
  "warehouse_code": "WH001",
  "vehicle_code": "KCA123A",
  "customer_code": "CUST001",
  "user_code": "USER123",
  "item_list": [
    {
      "OrderRef": "1234567890",
      "SalesOrder": 12345,
      "uom_code": "PCS",
      "item_code": "PROD001",
      "item_quantity": 10,
      "quantity": 10,
      "item_price": 150.00,
      "SAT_SYNC": null
    },
    {
      "OrderRef": "1234567891",
      "SalesOrder": 12345,
      "uom_code": "CTN",
      "item_code": "PROD002",
      "item_quantity": 5,
      "quantity": 5,
      "item_price": 75.00,
      "SAT_SYNC": null
    }
  ]
}

Configuration

Environment Variables

SAT_BASE_URL=https://kdl.solutechlabs.com/api
SAT_PASSWORD_AUTH=your_api_token
SAT_INVOICES_API=v1/sap/erp-invoices

Laravel Configuration

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

Monitoring & Logging

LogSatRequest Tracking

-- Recent invoice sync attempts
SELECT 
    created_at,
    success,
    error,
    doc_num,
    JSON_EXTRACT(payload, '$.item_list') as item_count
FROM LogSatRequest 
WHERE type = 'invoices' 
ORDER BY created_at DESC 
LIMIT 20;

-- Invoice sync success rate by day
SELECT 
    DATE(created_at) as sync_date,
    COUNT(CASE WHEN success = true THEN 1 END) as successful,
    COUNT(CASE WHEN success = false THEN 1 END) as failed,
    COUNT(*) as total
FROM LogSatRequest 
WHERE type = 'invoices'
AND created_at >= NOW() - INTERVAL 7 DAY
GROUP BY DATE(created_at)
ORDER BY sync_date DESC;

Troubleshooting

Issue: Invoices Not Syncing

Symptoms: Invoices remain in SalesImport without SAT_SYNC = 'Y' Diagnostic Steps:

Check invoice status by checking EXPORT_FLAG
Verify line items exist and are valid
Review scheduler execution logs
Test SAT API connectivity

Issue: Duplicate Invoices in SATa

Symptoms: Same invoice appears multiple times in SAT Diagnostic Steps:

Check SAT de-duplication logic
Verify SAT_SYNC flag updates on KDL Dashboard

Invoice Data Synchronization

On this page