Skip to main content

SYSPRO Data Models

Overview​

This document details the SYSPRO database entities used by the MiniMrpOrderCreation dashboard, including standard SYSPRO tables, custom tables, and views created specifically for MRP calculations.

Standard SYSPRO Tables​

InvMaster (Inventory Master)​

Primary stock master table:

  • StockCode: Unique stock identifier
  • Description: Stock description
  • Supplier: Default supplier code
  • PartCategory: Part classification (B=Buy)
  • StockOnHold: Hold status indicator
  • StockUom: Stock unit of measure
  • AlternateUom: Alternative UOM
  • OtherUom: Other UOM options

InvWarehouse (Warehouse Stock)​

Warehouse-specific stock data:

  • StockCode: Stock identifier (FK to InvMaster)
  • Warehouse: Warehouse code
  • QtyOnHand: Current stock quantity
  • QtyAllocated: Allocated to sales orders
  • QtyAllocatedWip: Allocated to WIP jobs
  • QtyOnOrder: On purchase order
  • QtyInTransit: In transit quantity
  • SafetyStockQty: Safety stock level
  • MinimumQty: Minimum stock level
  • ReOrderQty: Reorder quantity

ApSupplier (Supplier Master)​

Supplier information:

  • Supplier: Unique supplier code
  • SupplierName: Supplier name
  • Currency: Trading currency
  • OnHold: Hold status
  • PurchOrdAllowed: PO allowed flag
  • CurrentBalance: Current balance

PorSupStkInfo (Supplier Stock Info)​

Supplier-specific stock information:

  • StockCode: Stock identifier
  • Supplier: Supplier code
  • LastPricePaid: Last purchase price
  • LastPrcUom: Last price UOM
  • DefaultPrcUom: Default price UOM

Custom Tables and Views​

CG_InventoryOrdering_View​

Custom view for MRP calculations combining multiple tables to calculate reorder requirements based on safety stock and minimum quantities.

Key calculated fields:

  • QtyToOrder: Calculated order quantity
  • Demand: Total demand (allocated + WIP)
  • DemandType: 'S' for safety stock, 'M' for minimum

CG_v_SalesOrderJobDemand​

View showing sales order and job allocations:

  • JobSalesOrder: Job or sales order number
  • StockCode: Stock code
  • Warehouse: Warehouse
  • QtyRequired: Required quantity
  • QtyIssued: Issued quantity
  • QtyAllocated: Allocated quantity
  • DemandType: 'S' for sales, 'J' for jobs

Data Relationships​

  • InvMaster 1:N InvWarehouse (via StockCode)
  • InvMaster N:1 ApSupplier (via Supplier)
  • InvMaster 1:N PorSupStkInfo (via StockCode)
  • ApSupplier 1:N PorSupStkInfo (via Supplier)

Best Practices​

  • Always check StockOnHold before processing
  • Validate supplier OnHold status
  • Consider currency conversions for multi-currency
  • Handle null values in optional fields