Skip to content

NetSuite Integration

The NetSuite integration connects the Stratpoint Timesheet Application with NetSuite ERP through database-level data storage and reference management, enabling project and customer data synchronization for financial and operational reporting.

Integration Overview

NetSuite Integration Architecture

graph TB
    A[NetSuite ERP System] --> B[Data Export/Import]
    B --> C[Database Storage Layer]
    C --> D[NetSuite Reference Data]

    D --> E[Customer IDs]
    D --> F[Project IDs]
    D --> G[Branch Information]
    D --> H[Financial References]

    I[Timesheet Application] --> C
    I --> J[Project Management]
    I --> K[User Management]
    I --> L[Financial Reporting]

    J --> D
    K --> D
    L --> D

Key Features

  • Customer Reference Management: Store and manage NetSuite customer IDs for project association
  • Branch Integration: Track NetSuite branch assignments for users and projects
  • Project Synchronization: Maintain NetSuite project references for financial reporting
  • Data Consistency: Ensure consistent NetSuite references across timesheet operations
  • Reporting Integration: Support NetSuite-based financial and operational reporting

Database Integration

The NetSuite integration operates through database-level storage rather than real-time API calls:

NetSuite Data Tables

1. NetSuite Branches

Table: netsuite_branches

Stores NetSuite branch information for user and project assignments.

Key Fields: - id: Primary key - name: Branch name - netsuite_id: NetSuite branch identifier - description: Branch description - is_active: Active status

2. Project NetSuite References

Table: projects

Contains NetSuite-related fields for project management: - netsuiteCustomer_id: Links project to NetSuite customer - netsuiteMainId: Main NetSuite project identifier - netsuiteBranch_id: Associated NetSuite branch

3. User NetSuite Assignments

Table: users / resources

Contains NetSuite branch assignments for users: - netsuiteBranch_id: User's assigned NetSuite branch

Database Schema Examples

-- NetSuite Branches Table
CREATE TABLE netsuite_branches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    netsuite_id VARCHAR(100) UNIQUE,
    description TEXT,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Projects with NetSuite References
ALTER TABLE projects ADD COLUMN netsuiteCustomer_id INT;
ALTER TABLE projects ADD COLUMN netsuiteMainId VARCHAR(100);
ALTER TABLE projects ADD COLUMN netsuiteBranch_id INT;

-- Users with NetSuite Branch Assignment
ALTER TABLE resources ADD COLUMN netsuiteBranch_id INT;

Data Management Operations

NetSuite Customer Management

// Example PHP code for NetSuite customer assignment
class ProjectNetSuiteManager {

    public function assignNetSuiteCustomer($projectId, $netsuiteCustomerId) {
        return DB::table('projects')
            ->where('id', $projectId)
            ->update(['netsuiteCustomer_id' => $netsuiteCustomerId]);
    }

    public function getProjectsWithNetSuiteData($filters = []) {
        $query = DB::table('projects as p')
            ->leftJoin('netsuite_branches as nb', 'p.netsuiteBranch_id', '=', 'nb.id')
            ->select([
                'p.id',
                'p.name as project_name',
                'p.netsuiteCustomer_id',
                'p.netsuiteMainId',
                'nb.name as netsuite_branch_name',
                'nb.netsuite_id as netsuite_branch_id'
            ]);

        if (isset($filters['netsuite_branch_id'])) {
            $query->where('p.netsuiteBranch_id', $filters['netsuite_branch_id']);
        }

        if (isset($filters['has_netsuite_customer'])) {
            $query->whereNotNull('p.netsuiteCustomer_id');
        }

        return $query->get();
    }
}

User Branch Assignment

// NetSuite branch management for users
class UserNetSuiteBranchManager {

    public function assignUserToBranch($userId, $branchId) {
        return DB::table('resources')
            ->where('user_id', $userId)
            ->update(['netsuiteBranch_id' => $branchId]);
    }

    public function getUsersByBranch($branchId) {
        return DB::table('users as u')
            ->join('resources as r', 'u.id', '=', 'r.user_id')
            ->join('netsuite_branches as nb', 'r.netsuiteBranch_id', '=', 'nb.id')
            ->where('r.netsuiteBranch_id', $branchId)
            ->select([
                'u.id',
                'u.name',
                'u.email',
                'nb.name as branch_name',
                'nb.netsuite_id'
            ])
            ->get();
    }
}

Reporting Integration

NetSuite-based Reports

// Generate reports with NetSuite data
class NetSuiteReportGenerator {

    public function generateProjectRevenueByBranch($startDate, $endDate) {
        return DB::table('project_logs as pl')
            ->join('projects as p', 'pl.project_id', '=', 'p.id')
            ->join('netsuite_branches as nb', 'p.netsuiteBranch_id', '=', 'nb.id')
            ->whereBetween('pl.date', [$startDate, $endDate])
            ->groupBy('nb.id', 'nb.name')
            ->select([
                'nb.id as branch_id',
                'nb.name as branch_name',
                'nb.netsuite_id',
                DB::raw('SUM(pl.revenue) as total_revenue'),
                DB::raw('COUNT(DISTINCT p.id) as project_count'),
                DB::raw('COUNT(pl.id) as total_entries')
            ])
            ->get();
    }

    public function getCustomerProjectSummary($customerId) {
        return DB::table('projects as p')
            ->leftJoin('netsuite_branches as nb', 'p.netsuiteBranch_id', '=', 'nb.id')
            ->where('p.netsuiteCustomer_id', $customerId)
            ->select([
                'p.id',
                'p.name',
                'p.status',
                'p.netsuiteMainId',
                'nb.name as branch_name',
                'nb.netsuite_id as branch_netsuite_id'
            ])
            ->get();
    }
}

Data Synchronization

Import/Export Processes

// Example data synchronization workflow
class NetSuiteDataSync {

    // Import NetSuite branch data
    async importBranchData(branchData) {
        const branches = branchData.map(branch => ({
            name: branch.name,
            netsuite_id: branch.id,
            description: branch.description,
            is_active: branch.active
        }));

        return await this.batchInsertBranches(branches);
    }

    // Export project data for NetSuite
    async exportProjectData(dateRange) {
        const projects = await this.getProjectsWithNetSuiteRefs(dateRange);

        return projects.map(project => ({
            timesheet_project_id: project.id,
            netsuite_customer_id: project.netsuiteCustomer_id,
            netsuite_main_id: project.netsuiteMainId,
            branch_id: project.netsuite_branch_id,
            revenue_data: project.revenue_summary,
            time_entries: project.time_entries
        }));
    }

    // Validate NetSuite references
    validateNetSuiteReferences(data) {
        const validationResults = {
            valid: true,
            errors: [],
            warnings: []
        };

        // Check customer ID format
        if (data.netsuiteCustomer_id && !this.isValidCustomerId(data.netsuiteCustomer_id)) {
            validationResults.errors.push('Invalid NetSuite Customer ID format');
            validationResults.valid = false;
        }

        // Check branch assignment
        if (data.netsuiteBranch_id && !this.branchExists(data.netsuiteBranch_id)) {
            validationResults.errors.push('NetSuite branch not found');
            validationResults.valid = false;
        }

        return validationResults;
    }
}

Usage Guidelines

Best Practices

  • Data Integrity: Always validate NetSuite references before storing
  • Batch Operations: Use batch operations for bulk data synchronization
  • Reference Consistency: Maintain consistent NetSuite ID formats across all tables
  • Backup Strategy: Regular backup of NetSuite reference data before major updates

Error Handling

// Error handling for NetSuite operations
class NetSuiteErrorHandler {

    public function handleDataSyncError($operation, $error, $data) {
        $errorLog = [
            'timestamp' => now(),
            'operation' => $operation,
            'error_type' => get_class($error),
            'message' => $error->getMessage(),
            'data_context' => $data
        ];

        Log::error('NetSuite Integration Error', $errorLog);

        switch ($operation) {
            case 'branch_assignment':
                return $this->rollbackBranchAssignment($data);
            case 'customer_link':
                return $this->validateCustomerLink($data);
            case 'data_export':
                return $this->retryDataExport($data);
            default:
                return $this->escalateError($errorLog);
        }
    }
}

Permissions and Access Control

The NetSuite integration includes specific permissions for data access:

  • Permission ID 126: View NetSuite user data
  • Permission ID 127: Receive NetSuite expense import emails
  • Access Control: Restrict NetSuite data modification to authorized users only

This NetSuite integration provides essential ERP data synchronization capabilities through database-level storage, enabling consistent financial reporting and operational alignment between the Stratpoint Timesheet Application and NetSuite ERP system.