SYSPRO Data Models

Overview

This document provides comprehensive documentation of all SYSPRO entity models used in the AR Payment Reversal dashboard, including table relationships, field mappings, data type conversions, custom table extensions, and validation rules. Understanding these data models is crucial for maintaining data integrity and ensuring proper integration with SYSPRO's Accounts Receivable module.

Key Concepts

SYSPRO Core Tables: Standard ERP tables for AR operations
Custom Extension Tables: CG_ prefixed tables for dashboard-specific data
Composite Keys: Multi-field primary keys common in SYSPRO
Referential Integrity: Foreign key relationships between tables
Data Type Mappings: SYSPRO to .NET type conversions
Field Constraints: Business rules enforced at the database level

Implementation Details

Entity Framework Configuration

DbContext Setup

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/PluginSysproDataContext.cs
public class PluginSysproDataContext : DbContext
{
    public PluginSysproDataContext() : base("name=SysproEntities")
    {
        // Disable features that can cause issues with SYSPRO tables
        this.Configuration.LazyLoadingEnabled = false;
        this.Configuration.ProxyCreationEnabled = false;
        this.Configuration.AutoDetectChangesEnabled = false;
        
        // SYSPRO tables often have long-running queries
        this.Database.CommandTimeout = 60;
    }
    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Configure composite keys for SYSPRO tables
        modelBuilder.Entity<ArInvoicePay>()
            .HasKey(e => new { 
                e.Customer, 
                e.Invoice, 
                e.TrnYear, 
                e.TrnMonth, 
                e.Journal 
            });
        
        modelBuilder.Entity<ArCshJnlPay>()
            .HasKey(e => new { 
                e.TrnYear, 
                e.TrnMonth, 
                e.Journal, 
                e.EntryNumber,
                e.SubEntry
            });
        
        // Configure decimal precision for currency fields
        modelBuilder.Entity<ArInvoice>()
            .Property(e => e.CurrencyValue)
            .HasPrecision(18, 2);
        
        base.OnModelCreating(modelBuilder);
    }
}

Core SYSPRO Tables

ArCustomer - Customer Master

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/ArCustomer.cs
[Table("ArCustomer")]
public class ArCustomer
{
    [Key]
    [Column(TypeName = "varchar")]
    [StringLength(15)]
    public string Customer { get; set; }
    
    [Required]
    [StringLength(50)]
    public string Name { get; set; }
    
    [StringLength(50)]
    public string ShortName { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(3)]
    public string Currency { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal CreditLimit { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal Balance { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string CreditStatus { get; set; } // H=Hold, O=Open, C=Closed
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string CustomerOnHold { get; set; } // Y/N
    
    [StringLength(10)]
    public string CustomerClass { get; set; }
    
    [StringLength(10)]
    public string Branch { get; set; }
    
    [StringLength(10)]
    public string Salesperson { get; set; }
    
    [StringLength(10)]
    public string Area { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime? DateLastSale { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime? DateLastPayment { get; set; }
    
    // Navigation properties
    public virtual ICollection<ArInvoice> Invoices { get; set; }
    public virtual ICollection<ArPayHistory> PaymentHistory { get; set; }
}

ArInvoice - Invoice Header

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/ArInvoice.cs
[Table("ArInvoice")]
public class ArInvoice
{
    [Key, Column(Order = 0)]
    [StringLength(15)]
    public string Customer { get; set; }
    
    [Key, Column(Order = 1)]
    [StringLength(20)]
    public string Invoice { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime InvoiceDate { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime? DueDate { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string DocumentType { get; set; } // I=Invoice, C=Credit, D=Debit
    
    [StringLength(20)]
    public string SalesOrder { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal CurrencyValue { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal InvoiceBal1 { get; set; } // Current balance
    
    [Column(TypeName = "decimal")]
    public decimal DiscountValue { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal TaxValue { get; set; }
    
    [StringLength(10)]
    public string Branch { get; set; }
    
    [StringLength(10)]
    public string Salesperson { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string InvoiceComplete { get; set; } // Y/N
    
    // Calculated properties
    [NotMapped]
    public decimal OutstandingAmount => InvoiceBal1;
    
    [NotMapped]
    public bool IsFullyPaid => InvoiceBal1 == 0;
    
    [NotMapped]
    public bool IsPartiallyPaid => InvoiceBal1 > 0 && InvoiceBal1 < CurrencyValue;
}

ArInvoicePay - Invoice Payment Applications

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/ArInvoicePay.cs
[Table("ArInvoicePay")]
public class ArInvoicePay
{
    [Key, Column(Order = 0)]
    [StringLength(15)]
    public string Customer { get; set; }
    
    [Key, Column(Order = 1)]
    [StringLength(20)]
    public string Invoice { get; set; }
    
    [Key, Column(Order = 2)]
    public decimal TrnYear { get; set; }
    
    [Key, Column(Order = 3)]
    public decimal TrnMonth { get; set; }
    
    [Key, Column(Order = 4)]
    public decimal Journal { get; set; }
    
    [StringLength(20)]
    public string Reference { get; set; } // Check number
    
    [Column(TypeName = "decimal")]
    public decimal TrnValue { get; set; } // Payment amount (negative for payments)
    
    [Column(TypeName = "decimal")]
    public decimal DiscValue { get; set; } // Discount taken
    
    [Column(TypeName = "datetime")]
    public DateTime PostDate { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string PostStatus { get; set; } // P=Posted, U=Unposted
    
    // Foreign key relationships
    public virtual ArInvoice Invoice { get; set; }
    public virtual ArCustomer Customer { get; set; }
}

ArCshJnlCtl - Cash Journal Control

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/ArCshJnlCtl.cs
[Table("ArCshJnlCtl")]
public class ArCshJnlCtl
{
    [Key, Column(Order = 0)]
    public decimal TrnYear { get; set; }
    
    [Key, Column(Order = 1)]
    public decimal TrnMonth { get; set; }
    
    [Key, Column(Order = 2)]
    public decimal Journal { get; set; }
    
    [StringLength(10)]
    public string CashAccBank { get; set; } // Bank account
    
    [Column(TypeName = "decimal")]
    public decimal ControlTotal { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal PostedTotal { get; set; }
    
    public int EntryCount { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime JournalDate { get; set; }
    
    [StringLength(30)]
    public string JournalDesc { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string JournalStatus { get; set; } // O=Open, P=Posted, C=Cancelled
    
    [StringLength(10)]
    public string Operator { get; set; }
    
    // Navigation properties
    public virtual ICollection<ArCshJnlDet> Details { get; set; }
    public virtual ApBank Bank { get; set; }
}

ArCshJnlDet - Cash Journal Details

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/ArCshJnlDet.cs
[Table("ArCshJnlDet")]
public class ArCshJnlDet
{
    [Key, Column(Order = 0)]
    public decimal TrnYear { get; set; }
    
    [Key, Column(Order = 1)]
    public decimal TrnMonth { get; set; }
    
    [Key, Column(Order = 2)]
    public decimal Journal { get; set; }
    
    [Key, Column(Order = 3)]
    public decimal EntryNumber { get; set; }
    
    [StringLength(15)]
    public string Customer { get; set; }
    
    [StringLength(50)]
    public string CustomerName { get; set; }
    
    [StringLength(20)]
    public string Reference { get; set; } // Check number
    
    [Column(TypeName = "decimal")]
    public decimal ChequeValue { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal DiscountValue { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime PaymentDate { get; set; }
    
    [StringLength(30)]
    public string Narration { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string PaymentType { get; set; } // C=Check, E=Electronic, O=Other
    
    // Navigation properties
    public virtual ArCshJnlCtl JournalControl { get; set; }
    public virtual ICollection<ArCshJnlPay> PaymentApplications { get; set; }
}

ArCshJnlPay - Cash Journal Payment Applications

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/ArCshJnlPay.cs
[Table("ArCshJnlPay")]
public class ArCshJnlPay
{
    [Key, Column(Order = 0)]
    public decimal TrnMonth { get; set; }
    
    [Key, Column(Order = 1)]
    public decimal TrnYear { get; set; }
    
    [Key, Column(Order = 2)]
    public decimal Journal { get; set; }
    
    [Key, Column(Order = 3)]
    public decimal EntryNumber { get; set; }
    
    [Key, Column(Order = 4)]
    public decimal SubEntry { get; set; }
    
    [StringLength(20)]
    public string PayInvoice { get; set; } // Invoice being paid
    
    [Column(TypeName = "decimal")]
    public decimal PaymentValue { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal DiscountValue { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string DocumentType { get; set; }
    
    // Navigation properties
    public virtual ArCshJnlDet JournalDetail { get; set; }
    public virtual ArInvoice Invoice { get; set; }
}

ArPayHistory - Payment History

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/ArPayHistory.cs
[Table("ArPayHistory")]
public class ArPayHistory
{
    [Key, Column(Order = 0)]
    [StringLength(15)]
    public string Customer { get; set; }
    
    [Key, Column(Order = 1)]
    public decimal PaymYear { get; set; }
    
    [Key, Column(Order = 2)]
    public decimal PaymMonth { get; set; }
    
    [Key, Column(Order = 3)]
    public decimal CashJournal { get; set; }
    
    [Key, Column(Order = 4)]
    public decimal EntryNumber { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime PayDate { get; set; }
    
    [StringLength(20)]
    public string Reference { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal PaymentValue { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal DiscountValue { get; set; }
    
    [StringLength(10)]
    public string Bank { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string PaymentType { get; set; }
    
    // Navigation properties
    public virtual ArCustomer Customer { get; set; }
}

ApBank - Bank Master

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/ApBank.cs
[Table("ApBank")]
public class ApBank
{
    [Key]
    [StringLength(10)]
    public string Bank { get; set; }
    
    [StringLength(50)]
    public string Description { get; set; }
    
    [StringLength(15)]
    public string GlCode { get; set; }
    
    [StringLength(20)]
    public string AccountNumber { get; set; }
    
    [StringLength(10)]
    public string BranchCode { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(3)]
    public string Currency { get; set; }
    
    [Column(TypeName = "char")]
    [StringLength(1)]
    public string BankOnHold { get; set; } // Y/N
    
    [Column(TypeName = "decimal")]
    public decimal CurrentBalance { get; set; }
    
    // Navigation properties
    public virtual ICollection<ArCshJnlCtl> CashJournals { get; set; }
}

Custom Extension Tables

CG_ArReversePaymentQueueHeader

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/CG_ArReversePaymentQueueHeader.cs
[Table("CG_ArReversePaymentQueueHeader")]
public class CG_ArReversePaymentQueueHeader
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    [Required]
    [StringLength(20)]
    public string Customer { get; set; }
    
    [Required]
    [StringLength(20)]
    public string CheckNumber { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal? CheckValue { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime? PaymentDate { get; set; }
    
    [StringLength(10)]
    public string Bank { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal? TrnYear { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal? TrnMonth { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal? Journal { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime CreatedDate { get; set; }
    
    [StringLength(20)]
    public string CreatedBy { get; set; }
    
    [StringLength(50)]
    public string QueueStatus { get; set; } // Pending, Processing, Completed, Failed
}

SQL Creation Script:

-- MepApps.Dash.Ar.Maint.PaymentReversal/Resources/SQL/CREATE TABLE CG_ArReversePaymentQueueHeader.sql
CREATE TABLE [dbo].[CG_ArReversePaymentQueueHeader](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Customer] [varchar](20) NULL,
    [CheckNumber] [varchar](20) NULL,
    [CheckValue] [decimal](18, 2) NULL,
    [PaymentDate] [datetime] NULL,
    [Bank] [varchar](10) NULL,
    [TrnYear] [decimal](4, 0) NULL,
    [TrnMonth] [decimal](2, 0) NULL,
    [Journal] [decimal](10, 0) NULL,
    [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(),
    [CreatedBy] [varchar](20) NULL,
    [QueueStatus] [varchar](50) NULL DEFAULT 'Pending',
 CONSTRAINT [PK_CG_ArReversePaymentQueueHeader] PRIMARY KEY CLUSTERED ([Id] ASC)
)

-- Add indexes for performance
CREATE NONCLUSTERED INDEX [IX_CG_ArReversePaymentQueue_Customer] 
ON [dbo].[CG_ArReversePaymentQueueHeader] ([Customer])

CREATE NONCLUSTERED INDEX [IX_CG_ArReversePaymentQueue_Status] 
ON [dbo].[CG_ArReversePaymentQueueHeader] ([QueueStatus])

CG_ArReversePaymentPostCompletionHistory

// MepApps.Dash.Ar.Maint.PaymentReversal/Db/CG_ArReversePaymentPostCompletionHistory.cs
[Table("CG_ArReversePaymentPostCompletionHistory")]
public class CG_ArReversePaymentPostCompletionHistory
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    [Column(TypeName = "datetime")]
    public DateTime PostDate { get; set; }
    
    [StringLength(20)]
    public string PostedBy { get; set; }
    
    public bool PostSucceeded { get; set; }
    
    public int ItemsProcessed { get; set; }
    
    public int ItemsInvalid { get; set; }
    
    public int JournalCount { get; set; }
    
    public int PaymentCount { get; set; }
    
    [Column(TypeName = "decimal")]
    public decimal PaymentTotal { get; set; }
    
    [StringLength(20)]
    public string BusinessObject { get; set; }
    
    [Column(TypeName = "nvarchar(max)")]
    public string InputXml { get; set; }
    
    [Column(TypeName = "nvarchar(max)")]
    public string ParamXml { get; set; }
    
    [Column(TypeName = "nvarchar(max)")]
    public string OutputXml { get; set; }
    
    [StringLength(500)]
    public string ErrorMessage { get; set; }
}

Table Relationships

Entity Relationship Diagram

ArCustomer (1) ──────────< (∞) ArInvoice
    │                              │
    │                              │
    └──< (∞) ArInvoicePay >────────┘
             │
             │
    ArCshJnlCtl (1) ────< (∞) ArCshJnlDet
         │                        │
         │                        │
    ApBank (1)           ArCshJnlPay (∞)
         
    CG_ArReversePaymentQueueHeader (standalone)
    CG_ArReversePaymentPostCompletionHistory (standalone)

Data Type Conversions

SYSPRO to .NET Type Mappings

public static class SysproTypeMapper
{
    public static Type GetClrType(string sysproType)
    {
        return sysproType.ToUpper() switch
        {
            "VARCHAR" => typeof(string),
            "CHAR" => typeof(string),
            "NVARCHAR" => typeof(string),
            "DECIMAL" => typeof(decimal),
            "NUMERIC" => typeof(decimal),
            "INT" => typeof(int),
            "SMALLINT" => typeof(short),
            "BIGINT" => typeof(long),
            "BIT" => typeof(bool),
            "DATETIME" => typeof(DateTime),
            "DATE" => typeof(DateTime),
            "TIME" => typeof(TimeSpan),
            "FLOAT" => typeof(double),
            "REAL" => typeof(float),
            _ => typeof(object)
        };
    }
    
    public static object ConvertSysproValue(object value, Type targetType)
    {
        if (value == null || value == DBNull.Value)
            return null;
        
        // Handle SYSPRO specific conversions
        if (targetType == typeof(bool))
        {
            // SYSPRO uses 'Y'/'N' for booleans
            if (value is string strValue)
                return strValue.Equals("Y", StringComparison.OrdinalIgnoreCase);
        }
        
        if (targetType == typeof(DateTime))
        {
            // SYSPRO sometimes uses decimal for dates (YYYYMMDD)
            if (value is decimal decDate)
            {
                var dateStr = decDate.ToString("00000000");
                return DateTime.ParseExact(dateStr, "yyyyMMdd", null);
            }
        }
        
        return Convert.ChangeType(value, targetType);
    }
}

Field Validation Rules

Business Rule Validation

public class SysproFieldValidator
{
    public ValidationResult ValidateCustomerCode(string customer)
    {
        var errors = new List<string>();
        
        // SYSPRO customer codes are max 15 chars
        if (customer?.Length > 15)
            errors.Add("Customer code exceeds 15 characters");
        
        // Must not contain special characters
        if (customer?.Any(c => !char.IsLetterOrDigit(c)) ?? false)
            errors.Add("Customer code contains invalid characters");
        
        // Cannot be blank
        if (string.IsNullOrWhiteSpace(customer))
            errors.Add("Customer code is required");
        
        return new ValidationResult
        {
            IsValid = !errors.Any(),
            Errors = errors
        };
    }
    
    public ValidationResult ValidateInvoiceNumber(string invoice)
    {
        var errors = new List<string>();
        
        // SYSPRO invoice numbers are max 20 chars
        if (invoice?.Length > 20)
            errors.Add("Invoice number exceeds 20 characters");
        
        // Cannot be blank
        if (string.IsNullOrWhiteSpace(invoice))
            errors.Add("Invoice number is required");
        
        return new ValidationResult
        {
            IsValid = !errors.Any(),
            Errors = errors
        };
    }
    
    public ValidationResult ValidateCurrencyAmount(decimal amount)
    {
        var errors = new List<string>();
        
        // SYSPRO uses 18,2 precision for currency
        if (Math.Abs(amount) > 9999999999999999.99m)
            errors.Add("Amount exceeds maximum value");
        
        // Check for appropriate decimal places
        if (decimal.Round(amount, 2) != amount)
            errors.Add("Amount has more than 2 decimal places");
        
        return new ValidationResult
        {
            IsValid = !errors.Any(),
            Errors = errors
        };
    }
}

Query Optimization

Index Usage

public class OptimizedQueries
{
    // Use composite index on ArInvoicePay
    public IQueryable<ArInvoicePay> GetPaymentsByCustomerAndPeriod(
        PluginSysproDataContext context,
        string customer,
        decimal year,
        decimal month)
    {
        return context.ArInvoicePays
            .Where(p => p.Customer == customer 
                     && p.TrnYear == year 
                     && p.TrnMonth == month)
            .OrderBy(p => p.Journal)
            .ThenBy(p => p.Invoice);
    }
    
    // Use covering index for performance
    public IQueryable<dynamic> GetPaymentSummary(
        PluginSysproDataContext context,
        string customer)
    {
        return context.ArInvoicePays
            .Where(p => p.Customer == customer)
            .GroupBy(p => new { p.TrnYear, p.TrnMonth })
            .Select(g => new 
            {
                Year = g.Key.TrnYear,
                Month = g.Key.TrnMonth,
                TotalPayments = g.Sum(p => p.TrnValue),
                PaymentCount = g.Count()
            });
    }
}

Best Practices

Always use composite keys for SYSPRO table joins
Respect field length constraints to prevent truncation
Handle nullable fields appropriately
Use appropriate indexes for query performance
Validate data types before persistence
Maintain referential integrity across related tables
Document custom fields and their purpose

Common Pitfalls

Ignoring composite keys causing incorrect joins
Type mismatches between SYSPRO and .NET
Missing null checks for optional fields
Incorrect precision for decimal fields
Character encoding issues with varchar fields

SYSPRO Integration Overview - Architecture overview
SYSPRO Posting Patterns - Transaction posting
SYSPRO Business Objects - API usage
Data Services - Data access patterns

Summary

The SYSPRO data models form the foundation of the AR Payment Reversal dashboard's data layer. Understanding these models, their relationships, and constraints is essential for maintaining data integrity and ensuring proper integration with SYSPRO's business logic. The combination of standard SYSPRO tables and custom extensions provides a flexible yet robust data architecture for payment reversal operations.

Overview​

Key Concepts​

Implementation Details​

Entity Framework Configuration​

DbContext Setup​

Core SYSPRO Tables​

ArCustomer - Customer Master​

ArInvoice - Invoice Header​

ArInvoicePay - Invoice Payment Applications​

ArCshJnlCtl - Cash Journal Control​

ArCshJnlDet - Cash Journal Details​

ArCshJnlPay - Cash Journal Payment Applications​

ArPayHistory - Payment History​

ApBank - Bank Master​

Custom Extension Tables​

CG_ArReversePaymentQueueHeader​

CG_ArReversePaymentPostCompletionHistory​

Table Relationships​

Entity Relationship Diagram​

Data Type Conversions​

SYSPRO to .NET Type Mappings​

Field Validation Rules​

Business Rule Validation​

Query Optimization​

Index Usage​

Best Practices​

Common Pitfalls​

Related Documentation​

Summary​