Docs
AI Docs
MFG Lite
Database Tables for Mfg Lite v0 Sprint

Database Tables for Mfg Lite v0 Sprint

This document defines the database tables, Prisma schemas, and SQL Server DDL scripts required for Mfg Lite v0 (Phase 1 Core Sales).

To maintain the greenfield, simplified scope defined in Mfg-Lite-Roadmap-and-V0-Sprint.md, all Phase 2 features (Custom Fields, Audit Logging, E-Invoicing Logs, Export Invoices, and General Ledger Accounts) are excluded.

Table Grouping by Sprint

Sprint 1: App Shell and Core Masters

Core tables supporting authentication, tenant context, organization structure, and regional taxation:

  1. Company: Main tenant entity profile.
  2. CompanyBranch: Branches and GST registration configs.
  3. Currency: Currency master data (System defined).
  4. GstState: Indian states list (System defined).
  5. GstRate: Standard GST rates registry (System defined).
  6. GstType: Standard GST tax types (System defined).
  7. Transporter: Shipping agencies/transporters.
  8. ModeMst: Line level shipping mode configuration per transporter.

Sprint 2: Party and Product Setup

Entities for product catalog, pricing matrices, and business partners: 9. PartyGroup: Corporate analytical group classification for parties. 10. Party: Unified customer and vendor master. 11. PartyDeliveryAddress: Shipping locations and GSTIN overrides per party. 12. Uom: Unit of measure definitions. 13. Brand: Product brand classifications. 14. Product: Product SKU definitions and base prices. 15. PriceMaster: Pricing rules matching by Party, Brand, or combinations. 16. CeilingFloorPrice: Currency boundary validation rules. 17. OrderStatus: Sales order transaction status codes (System defined).

Sprint 3: Sales Order Flow

Tables for quotations, proforma, and direct sales orders: 18. SalesOrderHeader: Order metadata, totals, and temporary party fields. 19. SalesOrderDetails: Order lines with items and temporary item names.

Sprint 4: Invoice Flow

Tables for tax invoices and logistical dispatch tracking: 20. SalesInvoiceHeader: Commercial invoice header with embedded logistics and dispatch details. 21. SalesInvoiceDetails: Invoice line-item product details.

1. Company (Company)

  • Purpose: Stores legal entity profiles for the multi-tenant deployment.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT, Primary Key), companyname (NVARCHAR(50)), panno (NCHAR(10)), companyemail (NVARCHAR(50))
      • Optional: SignatoryType (NVARCHAR(50)), Signatoryname (NVARCHAR(100)), AuditTrail (NVARCHAR(1)), shifthrs (DECIMAL(4,2))
      • System: clientid (NVARCHAR(5), Tenant Scoping Key)
    • Removed Columns: coid, stregn, ctregn, ecno, tinni, lbtnno, Cinno
  • Prisma Model: 
    model Company {
  id            Int             @id @default(autoincrement())
  clientid      String          @db.NVarChar(5)
  companyname   String          @db.NVarChar(50)
  panno         String          @db.NChar(10)
  companyemail  String          @db.NVarChar(50)
  SignatoryType String?         @db.NVarChar(50)
  Signatoryname String?         @db.NVarChar(100)
  AuditTrail    String?         @db.NVarChar(1)
  shifthrs      Decimal?        @db.Decimal(4, 2)

  branches      CompanyBranch[]

  @@unique([clientid])
}
  • SQL: 
    CREATE TABLE Company (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    companyname NVARCHAR(50) NOT NULL,
    panno NCHAR(10) NOT NULL,
    companyemail NVARCHAR(50) NOT NULL,
    SignatoryType NVARCHAR(50) NULL,
    Signatoryname NVARCHAR(100) NULL,
    AuditTrail NVARCHAR(1) NULL,
    shifthrs DECIMAL(4,2) NULL,
    CONSTRAINT PK_Company PRIMARY KEY (id),
    CONSTRAINT UQ_Company_ClientId UNIQUE (clientid)
);

2. Company Branch (CompanyBranch)

  • Purpose: Configures branch business units and local GST tax registration details.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), companyId (INT, FK to Company), gstin (NVARCHAR(20)), homeStateCode (NCHAR(2)), unitCode (NCHAR(2)), unitName (NVARCHAR(20)), isActive (NCHAR(1)), addline1 (NVARCHAR(100)), authorizationToken (NVARCHAR(500)), authExpiryDate (DATETIME), sessionId (NVARCHAR(500)), einvoiceUserName (NVARCHAR(100)), einvoicePassword (NVARCHAR(100))
      • Optional: taxperson (NVARCHAR(100)), turnover (DECIMAL(18, 2)), pincode (NCHAR(6)), isRegdOffice (NVARCHAR(1)), notes (NVARCHAR(250))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: coid, addline2
  • Prisma Model: 
    model CompanyBranch {
  id                 Int       @id @default(autoincrement())
  clientid           String    @db.NVarChar(5)
  companyId          Int
  gstin              String    @db.NVarChar(20)
  homeStateCode      String    @db.NChar(2)
  unitCode           String    @db.NChar(2)
  unitName           String    @db.NVarChar(20)
  isActive           String    @db.NChar(1)
  addline1           String    @db.NVarChar(100)
  authorizationToken String    @db.NVarChar(500)
  authExpiryDate     DateTime  @db.DateTime
  sessionId          String    @db.NVarChar(500)
  einvoiceUserName   String    @db.NVarChar(100)
  einvoicePassword   String    @db.NVarChar(100)
  taxperson          String?   @db.NVarChar(100)
  turnover           Decimal?  @db.Decimal(18, 2)
  pincode            String?   @db.NChar(6)
  isRegdOffice       String?   @db.NVarChar(1)
  notes              String?   @db.NVarChar(250)

  company            Company   @relation(fields: [companyId], references: [id], onDelete: Cascade)
  salesOrders        SalesOrderHeader[]
  salesInvoices      SalesInvoiceHeader[]

  @@unique([clientid, unitCode])
}
  • SQL: 
    CREATE TABLE CompanyBranch (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    companyId INT NOT NULL,
    gstin NVARCHAR(20) NOT NULL,
    homeStateCode NCHAR(2) NOT NULL,
    unitCode NCHAR(2) NOT NULL,
    unitName NVARCHAR(20) NOT NULL,
    isActive NCHAR(1) NOT NULL,
    addline1 NVARCHAR(100) NOT NULL,
    authorizationToken NVARCHAR(500) NOT NULL,
    authExpiryDate DATETIME NOT NULL,
    sessionId NVARCHAR(500) NOT NULL,
    einvoiceUserName NVARCHAR(100) NOT NULL,
    einvoicePassword NVARCHAR(100) NOT NULL,
    taxperson NVARCHAR(100) NULL,
    turnover DECIMAL(18, 2) NULL,
    pincode NCHAR(6) NULL,
    isRegdOffice NVARCHAR(1) NULL,
    notes NVARCHAR(250) NULL,
    CONSTRAINT PK_CompanyBranch PRIMARY KEY (id),
    CONSTRAINT UQ_CompanyBranch_ClientId_UnitCode UNIQUE (clientid, unitCode),
    CONSTRAINT FK_CompanyBranch_Company FOREIGN KEY (companyId) REFERENCES Company(id) ON DELETE CASCADE
);

3. Currency Master (Currency)

  • Purpose: Stores standard currency definitions (System defined).
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), code (NCHAR(3)), currencyName (NVARCHAR(50)), mainCurrency (NVARCHAR(50)), subCurrency (NVARCHAR(50)), symbol (NVARCHAR(10))
      • Optional: None
      • System: clientid (NVARCHAR(5))
    • Removed Columns: owner
  • Prisma Model: 
    model Currency {
  id           Int                  @id @default(autoincrement())
  clientid     String               @db.NVarChar(5)
  code         String               @db.NChar(3)
  currencyName String               @db.NVarChar(50)
  mainCurrency String               @db.NVarChar(50)
  subCurrency  String               @db.NVarChar(50)
  symbol       String               @db.NVarChar(10)

  prices       CeilingFloorPrice[]
  priceRules   PriceMaster[]
  salesOrders  SalesOrderHeader[]

  @@unique([clientid, code])
}
  • SQL: 
    CREATE TABLE Currency (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    code NCHAR(3) NOT NULL,
    currencyName NVARCHAR(50) NOT NULL,
    mainCurrency NVARCHAR(50) NOT NULL,
    subCurrency NVARCHAR(50) NOT NULL,
    symbol NVARCHAR(10) NOT NULL,
    CONSTRAINT PK_Currency PRIMARY KEY (id),
    CONSTRAINT UQ_Currency_ClientId_Code UNIQUE (clientid, code)
);

4. GST State Master (GstState)

  • Purpose: Official list of Indian GST states with code assignments (System defined).
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), stateCode (NCHAR(2)), stateName (NVARCHAR(30)), isUt (NCHAR(1)), isActive (NVARCHAR(1))
      • Optional: shortCode (NCHAR(2))
      • System: clientid (NVARCHAR(5))
  • Prisma Model: 
    model GstState {
  id        Int      @id @default(autoincrement())
  clientid  String   @db.NVarChar(5)
  stateCode String   @db.NChar(2)
  stateName String   @db.NVarChar(30)
  shortCode String?  @db.NChar(2)
  isUt      String   @db.NChar(1)
  isActive  String   @db.NVarChar(1)

  @@unique([clientid, stateCode])
}
  • SQL: 
    CREATE TABLE GstState (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    stateCode NCHAR(2) NOT NULL,
    stateName NVARCHAR(30) NOT NULL,
    shortCode NCHAR(2) NULL,
    isUt NCHAR(1) NOT NULL,
    isActive NVARCHAR(1) NOT NULL DEFAULT 'Y',
    CONSTRAINT PK_GstState PRIMARY KEY (id),
    CONSTRAINT UQ_GstState_ClientId_Code UNIQUE (clientid, stateCode)
);

5. GST Rates Master (GstRate)

  • Purpose: GST percentage classifications applied to items (System defined).
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), gstCode (NCHAR(2)), gstName (NVARCHAR(30)), rate (NUMERIC(5,2)), isActive (NCHAR(1))
      • Optional: shortName (NVARCHAR(30))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: mainAc, subAc, owner
  • Prisma Model: 
    model GstRate {
  id        Int      @id @default(autoincrement())
  clientid  String   @db.NVarChar(5)
  gstCode   String   @db.NChar(2)
  gstName   String   @db.NVarChar(30)
  rate      Decimal  @db.Decimal(5, 2)
  shortName String?  @db.NVarChar(30)
  isActive  String   @db.NChar(1)

  @@unique([clientid, gstCode])
}
  • SQL: 
    CREATE TABLE GstRate (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    gstCode NCHAR(2) NOT NULL,
    gstName NVARCHAR(30) NOT NULL,
    rate NUMERIC(5,2) NOT NULL,
    shortName NVARCHAR(30) NULL,
    isActive NCHAR(1) NOT NULL DEFAULT 'Y',
    CONSTRAINT PK_GstRate PRIMARY KEY (id),
    CONSTRAINT UQ_GstRate_ClientId_Code UNIQUE (clientid, gstCode)
);

6. GST Type Master (GstType)

  • Purpose: Tax types classification (System defined).
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), type (NCHAR(2)), description (NVARCHAR(30)), ind (NCHAR(1)), isActive (NCHAR(1))
      • Optional: None
      • System: clientid (NVARCHAR(5))
  • Prisma Model: 
    model GstType {
  id          Int      @id @default(autoincrement())
  clientid    String   @db.NVarChar(5)
  type        String   @db.NChar(2)
  description String   @db.NVarChar(30)
  ind         String   @db.NChar(1)
  isActive    String   @db.NChar(1)

  @@unique([clientid, type])
}
  • SQL: 
    CREATE TABLE GstType (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    type NCHAR(2) NOT NULL,
    description NVARCHAR(30) NOT NULL,
    ind NCHAR(1) NOT NULL,
    isActive NCHAR(1) NOT NULL DEFAULT 'Y',
    CONSTRAINT PK_GstType PRIMARY KEY (id),
    CONSTRAINT UQ_GstType_ClientId_Type UNIQUE (clientid, type)
);

7. Transporter Master (Transporter)

  • Purpose: Shipping agency / transporter registry.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), transporterCode (NCHAR(3)), transporterName (NVARCHAR(50)), owner (NVARCHAR(50)), modeCode (NCHAR(2)), gstin (NVARCHAR(15))
      • Optional: None
      • System: clientid (NVARCHAR(5))
    • Removed Columns: tel1, notes
  • Prisma Model: 
    model Transporter {
  id              Int       @id @default(autoincrement())
  clientid        String    @db.NVarChar(5)
  transporterCode String    @db.NChar(3)
  transporterName String    @db.NVarChar(50)
  owner           String    @db.NVarChar(50)
  modeCode        String    @db.NChar(2)
  gstin           String    @db.NVarChar(15)

  modes           ModeMst[]
  salesInvoices   SalesInvoiceHeader[]

  @@unique([clientid, transporterCode])
}
  • SQL: 
    CREATE TABLE Transporter (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    transporterCode NCHAR(3) NOT NULL,
    transporterName NVARCHAR(50) NOT NULL,
    owner NVARCHAR(50) NOT NULL,
    modeCode NCHAR(2) NOT NULL,
    gstin NVARCHAR(15) NOT NULL,
    CONSTRAINT PK_Transporter PRIMARY KEY (id),
    CONSTRAINT UQ_Transporter_ClientId_Code UNIQUE (clientid, transporterCode)
);

8. Mode Master (ModeMst)

  • Purpose: Represents transporter-specific shipping modes (line-level child table of Transporter).
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), transporterCode (NCHAR(3), FK to Transporter), code (NCHAR(2)), description (NVARCHAR(50)), ewayMode (NVARCHAR(1))
      • Optional: None
      • System: clientid (NVARCHAR(5))
  • Prisma Model: 
    model ModeMst {
  id              Int         @id @default(autoincrement())
  clientid        String      @db.NVarChar(5)
  transporterCode String      @db.NChar(3)
  code            String      @db.NChar(2)
  description     String      @db.NVarChar(50)
  ewayMode        String      @db.NVarChar(1)

  transporter     Transporter @relation(fields: [clientid, transporterCode], references: [clientid, transporterCode], onDelete: Cascade)

  @@unique([clientid, transporterCode, code])
}
  • SQL: 
    CREATE TABLE ModeMst (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    transporterCode NCHAR(3) NOT NULL,
    code NCHAR(2) NOT NULL,
    description NVARCHAR(50) NOT NULL,
    ewayMode NVARCHAR(1) NOT NULL,
    CONSTRAINT PK_ModeMst PRIMARY KEY (id),
    CONSTRAINT UQ_ModeMst UNIQUE (clientid, transporterCode, code),
    CONSTRAINT FK_ModeMst_Transporter FOREIGN KEY (clientid, transporterCode) REFERENCES Transporter(clientid, transporterCode) ON DELETE CASCADE
);

9. Party Group Master (PartyGroup)

  • Purpose: Corporate grouping for customer/vendor analytical reporting.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), groupCode (NCHAR(2)), groupName (NVARCHAR(50))
      • Optional: None
      • System: clientid (NVARCHAR(5))
    • Removed Columns: notes
  • Prisma Model: 
    model PartyGroup {
  id         Int      @id @default(autoincrement())
  clientid   String   @db.NVarChar(5)
  groupCode  String   @db.NChar(2)
  groupName  String   @db.NVarChar(50)

  parties    Party[]

  @@unique([clientid, groupCode])
}
  • SQL: 
    CREATE TABLE PartyGroup (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    groupCode NCHAR(2) NOT NULL,
    groupName NVARCHAR(50) NOT NULL,
    CONSTRAINT PK_PartyGroup PRIMARY KEY (id),
    CONSTRAINT UQ_PartyGroup_ClientId_Code UNIQUE (clientid, groupCode)
);

10. Party Master (Party)

  • Purpose: Registry of customers and vendors (partyType separates them).
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), partyId (NCHAR(6)), partyName (NVARCHAR(75)), partyType (NCHAR(1), ‘C’=Customer/‘V’=Vendor), controlAc (NCHAR(6)), panno (NCHAR(10)), tdsCode (NVARCHAR(10)), isActive (NCHAR(1)), groupCode (NCHAR(2)), creditDays (SMALLINT), creditLimit (DECIMAL(18,2)), stdItcType (NVARCHAR(15)), isEinvoice (NCHAR(1))
      • Optional: region (NVARCHAR(50)), allowInvoice (NCHAR(1)), isTcs (NCHAR(1)), smNo (NVARCHAR(20)), termCode (NVARCHAR(10)), categoryCode (NVARCHAR(10)), discountPercent (NUMERIC(5,2)), segmentCode (NVARCHAR(10)), createdOn (DATETIME), msme (NCHAR(1)), multipleOrders (NCHAR(1))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: notes, shortName, hundicreditdays, isapplicablemeandate, hundicredittype, hudicreditperiod, ind, acestype, invoicetrntype, billtrntype
  • Prisma Model: 
    model Party {
  id              Int                    @id @default(autoincrement())
  clientid        String                 @db.NVarChar(5)
  partyId         String                 @db.NChar(6)
  partyName       String                 @db.NVarChar(75)
  partyType       String                 @db.NChar(1) // 'C' (Customer), 'V' (Vendor)
  controlAc       String                 @db.NChar(6)
  panno           String                 @db.NChar(10)
  tdsCode         String                 @db.NVarChar(10)
  isActive        String                 @db.NChar(1)
  groupCode       String                 @db.NChar(2)
  creditDays      Int                    @db.SmallInt
  creditLimit     Decimal                @db.Decimal(18, 2)
  stdItcType      String                 @db.NVarChar(15)
  isEinvoice      String                 @db.NChar(1)

  region          String?                @db.NVarChar(50)
  allowInvoice    String?                @db.NChar(1)
  isTcs           String?                @db.NChar(1)
  smNo            String?                @db.NVarChar(20)
  termCode        String?                @db.NVarChar(10)
  categoryCode    String?                @db.NVarChar(10)
  discountPercent Decimal?               @db.Decimal(5, 2)
  segmentCode     String?                @db.NVarChar(10)
  createdOn       DateTime?              @db.DateTime
  msme            String?                @db.NChar(1)
  multipleOrders  String?                @db.NChar(1)

  group           PartyGroup             @relation(fields: [clientid, groupCode], references: [clientid, groupCode])
  branches        PartyDeliveryAddress[]
  orders          SalesOrderHeader[]
  priceRules      PriceMaster[]

  @@unique([clientid, partyId])
}
  • SQL: 
    CREATE TABLE Party (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    partyId NCHAR(6) NOT NULL,
    partyName NVARCHAR(75) NOT NULL,
    partyType NCHAR(1) NOT NULL, -- 'C', 'V'
    controlAc NCHAR(6) NOT NULL,
    panno NCHAR(10) NOT NULL,
    tdsCode NVARCHAR(10) NOT NULL,
    isActive NCHAR(1) NOT NULL,
    groupCode NCHAR(2) NOT NULL,
    creditDays SMALLINT NOT NULL,
    creditLimit DECIMAL(18, 2) NOT NULL,
    stdItcType NVARCHAR(15) NOT NULL,
    isEinvoice NCHAR(1) NOT NULL,
    region NVARCHAR(50) NULL,
    allowInvoice NCHAR(1) NULL,
    isTcs NCHAR(1) NULL,
    smNo NVARCHAR(20) NULL,
    termCode NVARCHAR(10) NULL,
    categoryCode NVARCHAR(10) NULL,
    discountPercent NUMERIC(5,2) NULL,
    segmentCode NVARCHAR(10) NULL,
    createdOn DATETIME NULL,
    msme NCHAR(1) NULL,
    multipleOrders NCHAR(1) NULL,
    CONSTRAINT PK_Party PRIMARY KEY (id),
    CONSTRAINT UQ_Party_ClientId_PartyId UNIQUE (clientid, partyId),
    CONSTRAINT FK_Party_Group FOREIGN KEY (clientid, groupCode) REFERENCES PartyGroup(clientid, groupCode)
);

11. Party Delivery Address (PartyDeliveryAddress)

  • Purpose: Shipping addresses and local state GSTIN configs per customer/vendor.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), partyId (NCHAR(6)), branchId (NCHAR(3)), branchName (NVARCHAR(50)), addline1 (NVARCHAR(100)), city (NVARCHAR(50)), pincode (NVARCHAR(10)), gstStateCode (NVARCHAR(2)), gstregino (NVARCHAR(20)), gstRegiType (NCHAR(1)), customerType (NVARCHAR(20))
      • Optional: addline2 (NVARCHAR(100)), stcode (NVARCHAR(10))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: ecc, stregn, cstregn, excode, lbtno, planno, range, rpreqd, applylbt, statecode, distance, plantcode
  • Prisma Model: 
    model PartyDeliveryAddress {
  id           Int      @id @default(autoincrement())
  clientid     String   @db.NVarChar(5)
  partyId      String   @db.NChar(6)
  branchId     String   @db.NChar(3)
  branchName   String   @db.NVarChar(50)
  addline1     String   @db.NVarChar(100)
  city         String   @db.NVarChar(50)
  pincode      String   @db.NVarChar(10)
  gstStateCode String   @db.NVarChar(2)
  gstregino    String   @db.NVarChar(20)
  gstRegiType  String   @db.NChar(1)
  customerType String   @db.NVarChar(20)

  addline2     String?  @db.NVarChar(100)
  stcode       String?  @db.NVarChar(10)

  party        Party    @relation(fields: [clientid, partyId], references: [clientid, partyId])
  salesOrders  SalesOrderHeader[]
  salesInvoices SalesInvoiceHeader[]

  @@unique([clientid, partyId, branchId])
}
  • SQL: 
    CREATE TABLE PartyDeliveryAddress (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    partyId NCHAR(6) NOT NULL,
    branchId NCHAR(3) NOT NULL,
    branchName NVARCHAR(50) NOT NULL,
    addline1 NVARCHAR(100) NOT NULL,
    city NVARCHAR(50) NOT NULL,
    pincode NVARCHAR(10) NOT NULL,
    gstStateCode NVARCHAR(2) NOT NULL,
    gstregino NVARCHAR(20) NOT NULL,
    gstRegiType NCHAR(1) NOT NULL,
    customerType NVARCHAR(20) NOT NULL,
    addline2 NVARCHAR(100) NULL,
    stcode NVARCHAR(10) NULL,
    CONSTRAINT PK_PartyDeliveryAddress PRIMARY KEY (id),
    CONSTRAINT UQ_PartyDeliveryAddress UNIQUE (clientid, partyId, branchId),
    CONSTRAINT FK_PartyDeliveryAddress_Party FOREIGN KEY (clientid, partyId) REFERENCES Party(clientid, partyId)
);

12. UOM Master (Uom)

  • Purpose: Base product transaction unit of measure definitions.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), uomCode (NCHAR(2)), uomName (NVARCHAR(5))
      • Optional: None
      • System: clientid (NVARCHAR(5))
  • Prisma Model: 
    model Uom {
  id       Int       @id @default(autoincrement())
  clientid String    @db.NVarChar(5)
  uomCode  String    @db.NChar(2)
  uomName  String    @db.NVarChar(5)

  items    Product[]

  @@unique([clientid, uomCode])
}
  • SQL: 
    CREATE TABLE Uom (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    uomCode NCHAR(2) NOT NULL,
    uomName NVARCHAR(5) NOT NULL,
    CONSTRAINT PK_Uom PRIMARY KEY (id),
    CONSTRAINT UQ_Uom_ClientId_Code UNIQUE (clientid, uomCode)
);

13. Brand Master (Brand)

  • Purpose: Product brands inventory mapping tags.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), brandId (NCHAR(3)), brandName (NVARCHAR(50))
      • Optional: None
      • System: clientid (NVARCHAR(5))
  • Prisma Model: 
    model Brand {
  id        Int                  @id @default(autoincrement())
  clientid  String               @db.NVarChar(5)
  brandId   String               @db.NChar(3)
  brandName String               @db.NVarChar(50)

  items      SalesOrderDetails[]
  priceRules PriceMaster[]

  @@unique([clientid, brandId])
}
  • SQL: 
    CREATE TABLE Brand (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    brandId NCHAR(3) NOT NULL,
    brandName NVARCHAR(50) NOT NULL,
    CONSTRAINT PK_Brand PRIMARY KEY (id),
    CONSTRAINT UQ_Brand_ClientId_Code UNIQUE (clientid, brandId)
);

14. Product Master (Product)

  • Purpose: Catalog of active item SKUs and standard base values.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), itemId (NCHAR(6)), itemName (NVARCHAR(150)), uomId (NCHAR(2), FK to Uom), categoryId (NVARCHAR(10)), classId (NVARCHAR(10)), isActive (NCHAR(1)), itemGroupId (NVARCHAR(10)), stdPkg (DECIMAL(10,2)), segmentCode (NVARCHAR(10)), hsnCode (NVARCHAR(8)), gstCode (NCHAR(2))
      • Optional: uom1Id (NCHAR(2)), nFactor (DECIMAL(10,4)), rol (DECIMAL(10,2)), eoq (DECIMAL(10,2)), releaseStatus (NCHAR(1)), leadTime (INT), stdWeight (DECIMAL(10,4))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: shortName, exchapterid, divisionid, notes, lbtcode, excode, rackinfo, parentcoitemid, hobranchsyncitemid, showinrg231, Maxol, floorprice, ceilingprice, createdon, iscritcal, safetystock, idutyperc, warranty
  • Prisma Model: 
    model Product {
  id            Int                  @id @default(autoincrement())
  clientid      String               @db.NVarChar(5)
  itemId        String               @db.NChar(6)
  itemName      String               @db.NVarChar(150)
  uomId         String               @db.NChar(2)
  categoryId    String               @db.NVarChar(10)
  classId       String               @db.NVarChar(10)
  isActive      String               @db.NChar(1)
  itemGroupId   String               @db.NVarChar(10)
  stdPkg        Decimal              @db.Decimal(10, 2)
  segmentCode   String               @db.NVarChar(10)
  hsnCode       String               @db.NVarChar(8)
  gstCode       String               @db.NChar(2)

  uom1Id        String?              @db.NChar(2)
  nFactor       Decimal?             @db.Decimal(10, 4)
  rol           Decimal?             @db.Decimal(10, 2)
  eoq           Decimal?             @db.Decimal(10, 2)
  releaseStatus String?              @db.NChar(1)
  leadTime      Int?
  stdWeight     Decimal?             @db.Decimal(10, 4)

  uom           Uom                  @relation(fields: [clientid, uomId], references: [clientid, uomCode])
  orderlines    SalesOrderDetails[]
  priceRules    PriceMaster[]
  ceilPrices    CeilingFloorPrice[]

  @@unique([clientid, itemId])
}
  • SQL: 
    CREATE TABLE Product (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    itemId NCHAR(6) NOT NULL,
    itemName NVARCHAR(150) NOT NULL,
    uomId NCHAR(2) NOT NULL,
    categoryId NVARCHAR(10) NOT NULL,
    classId NVARCHAR(10) NOT NULL,
    isActive NCHAR(1) NOT NULL,
    itemGroupId NVARCHAR(10) NOT NULL,
    stdPkg DECIMAL(10, 2) NOT NULL,
    segmentCode NVARCHAR(10) NOT NULL,
    hsnCode NVARCHAR(8) NOT NULL,
    gstCode NCHAR(2) NOT NULL,
    uom1Id NCHAR(2) NULL,
    nFactor DECIMAL(10, 4) NULL,
    rol DECIMAL(10, 2) NULL,
    eoq DECIMAL(10, 2) NULL,
    releaseStatus NCHAR(1) NULL,
    leadTime INT NULL,
    stdWeight DECIMAL(10, 4) NULL,
    CONSTRAINT PK_Product PRIMARY KEY (id),
    CONSTRAINT UQ_Product_ClientId_ItemId UNIQUE (clientid, itemId),
    CONSTRAINT FK_Product_Uom FOREIGN KEY (clientid, uomId) REFERENCES Uom(clientid, uomCode)
);

15. Price Master (PriceMaster)

  • Purpose: Pricing matrix handling Party, Brand, Currency, or combined lookups (replaces legacy pricemst).
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), partyId (NCHAR(6), FK to Party), itemId (NCHAR(6), FK to Product), price (NUMERIC(12,4)), cost (NUMERIC(12,4)), currency (NVARCHAR(3), FK to Currency), rate (NUMERIC(12,4)), updatedOn (DATETIME), brandId (NCHAR(3), FK to Brand), priceMstNo (NVARCHAR(20))
      • Optional: mrp (NUMERIC(12,4)), factor (NUMERIC(10,4)), discountPerc (NUMERIC(5,2))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: sysdate, notes, xferprice, creditdays, stdcost, cstperc
  • Prisma Model: 
    model PriceMaster {
  id           Int      @id @default(autoincrement())
  clientid     String   @db.NVarChar(5)
  partyId      String   @db.NChar(6)
  brandId      String   @db.NChar(3)
  itemId       String   @db.NChar(6)
  currency     String   @db.NVarChar(3)
  price        Decimal  @db.Decimal(12, 4)
  cost         Decimal  @db.Decimal(12, 4)
  rate         Decimal  @db.Decimal(12, 4)
  updatedOn    DateTime @db.DateTime
  priceMstNo   String   @db.NVarChar(20)

  mrp          Decimal? @db.Decimal(12, 4)
  factor       Decimal? @db.Decimal(10, 4)
  discountPerc Decimal? @db.Decimal(5, 2)

  party        Party    @relation(fields: [clientid, partyId], references: [clientid, partyId])
  brand        Brand    @relation(fields: [clientid, brandId], references: [clientid, brandId])
  product      Product  @relation(fields: [clientid, itemId], references: [clientid, itemId])
  curMst       Currency @relation(fields: [clientid, currency], references: [clientid, code])

  @@unique([clientid, partyId, brandId, itemId, currency])
}
  • SQL: 
    CREATE TABLE PriceMaster (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    partyId NCHAR(6) NOT NULL,
    brandId NCHAR(3) NOT NULL,
    itemId NCHAR(6) NOT NULL,
    currency NVARCHAR(3) NOT NULL,
    price NUMERIC(12,4) NOT NULL,
    cost NUMERIC(12,4) NOT NULL,
    rate NUMERIC(12,4) NOT NULL,
    updatedOn DATETIME NOT NULL,
    priceMstNo NVARCHAR(20) NOT NULL,
    mrp NUMERIC(12,4) NULL,
    factor NUMERIC(10,4) NULL,
    discountPerc NUMERIC(5,2) NULL,
    CONSTRAINT PK_PriceMaster PRIMARY KEY (id),
    CONSTRAINT UQ_PriceMaster UNIQUE (clientid, partyId, brandId, itemId, currency),
    CONSTRAINT FK_PriceMaster_Party FOREIGN KEY (clientid, partyId) REFERENCES Party(clientid, partyId),
    CONSTRAINT FK_PriceMaster_Brand FOREIGN KEY (clientid, brandId) REFERENCES Brand(clientid, brandId),
    CONSTRAINT FK_PriceMaster_Product FOREIGN KEY (clientid, itemId) REFERENCES Product(clientid, itemId),
    CONSTRAINT FK_PriceMaster_Currency FOREIGN KEY (clientid, currency) REFERENCES Currency(clientid, code)
);
CREATE INDEX IX_PriceMaster_Lookup ON PriceMaster (clientid, partyId, brandId, itemId, currency);

16. Ceiling & Floor Price Master (CeilingFloorPrice)

  • Purpose: Manages price boundaries (Ceiling/Floor indicators) per currency for items.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), ind (NVARCHAR(1), Indicator: ‘C’=Ceiling / ‘F’=Floor), itemId (NCHAR(6), FK to Product), currency (NVARCHAR(3), FK to Currency), rate (NUMERIC(18,4))
      • Optional: None
      • System: clientid (NVARCHAR(5))
  • Prisma Model: 
    model CeilingFloorPrice {
  id        Int      @id @default(autoincrement())
  clientid  String   @db.NVarChar(5)
  ind       String   @db.NVarChar(1) // 'C' (Ceiling), 'F' (Floor)
  itemId    String   @db.NChar(6)
  currency  String   @db.NVarChar(3)
  rate      Decimal  @db.Decimal(18, 4)

  product   Product  @relation(fields: [clientid, itemId], references: [clientid, itemId])
  curMst    Currency @relation(fields: [clientid, currency], references: [clientid, code])

  @@unique([clientid, itemId, currency, ind])
}
  • SQL: 
    CREATE TABLE CeilingFloorPrice (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    ind NVARCHAR(1) NOT NULL, -- 'C' (Ceiling), 'F' (Floor)
    itemId NCHAR(6) NOT NULL,
    currency NVARCHAR(3) NOT NULL,
    rate NUMERIC(18,4) NOT NULL,
    CONSTRAINT PK_CeilingFloorPrice PRIMARY KEY (id),
    CONSTRAINT UQ_CeilingFloorPrice UNIQUE (clientid, itemId, currency, ind),
    CONSTRAINT FK_CeilingFloorPrice_Product FOREIGN KEY (clientid, itemId) REFERENCES Product(clientid, itemId),
    CONSTRAINT FK_CeilingFloorPrice_Currency FOREIGN KEY (clientid, currency) REFERENCES Currency(clientid, code)
);

17. Order Status Master (OrderStatus)

  • Purpose: Sales order lifecycle tracking state codes (System defined).
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), code (NCHAR(2)), description (NVARCHAR(50))
      • Optional: take4mrp (NCHAR(1)), take4poreport (NCHAR(1))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: shortName
  • Prisma Model: 
    model OrderStatus {
  id            Int                 @id @default(autoincrement())
  clientid      String              @db.NVarChar(5)
  code          String              @db.NChar(2)
  description   String              @db.NVarChar(50)
  take4mrp      String?             @db.NChar(1)
  take4poreport String?             @db.NChar(1)

  items         SalesOrderDetails[]

  @@unique([clientid, code])
}
  • SQL: 
    CREATE TABLE OrderStatus (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    code NCHAR(2) NOT NULL,
    description NVARCHAR(50) NOT NULL,
    take4mrp NCHAR(1) NULL,
    take4poreport NCHAR(1) NULL,
    CONSTRAINT PK_OrderStatus PRIMARY KEY (id),
    CONSTRAINT UQ_OrderStatus_ClientId_Code UNIQUE (clientid, code)
);

18. Orders Header (SalesOrderHeader)

  • Purpose: Header details for quotations, proforma, and direct sales orders.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), orderNo (NCHAR(6)), dated (DATETIME), partyId (NCHAR(6), FK to Party), value (NUMERIC(12,2)), branchId (NCHAR(3), FK to PartyDeliveryAddress), orderType (NCHAR(1), ‘Q’/‘P’/‘D’), yearOrderNo (NVARCHAR(20)), currency (NCHAR(3), FK to Currency), freightReqd (NCHAR(1)), freightId (NVARCHAR(10)), focAmount (NUMERIC(12,2)), unitCode (NCHAR(2), FK to CompanyBranch)
      • Optional: pono (NVARCHAR(30)), podate (DATETIME), notes (NVARCHAR(1000)), smNo (NVARCHAR(20)), subNo (NVARCHAR(20)), oCreditDays (SMALLINT), oTermCode (NVARCHAR(10)), supplyState (NCHAR(2)), shiptoPincode (NVARCHAR(10)), discount (NUMERIC(12,2)), oShiptoParty (NVARCHAR(75)), oShiptoState (NCHAR(2)), oShiptoLoc (NVARCHAR(50)), endCustomerId (NCHAR(6)), endCustomerBranchId (NCHAR(3))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: stcode2, isConfirmed, poFilePath, partyNameForQuotation, partyAddressForQuotation, shiptoCustomerName, shiptoAddr, shiptoGstin
  • Prisma Model: 
    model SalesOrderHeader {
  id                       Int                 @id @default(autoincrement())
  clientid                 String              @db.NVarChar(5)
  orderNo                  String              @db.NChar(6)
  dated                    DateTime            @db.DateTime
  partyId                  String              @db.NChar(6)
  value                    Decimal             @db.Decimal(12, 2)
  branchId                 String              @db.NChar(3)
  orderType                String              @db.NChar(1) // 'Q' (Quotation), 'P' (Proforma), 'D' (Direct)
  yearOrderNo              String              @db.NVarChar(20)
  currency                 String              @db.NChar(3)
  freightReqd              String              @db.NChar(1)
  freightId                String              @db.NVarChar(10)
  focAmount                Decimal             @db.Decimal(12, 2)
  unitCode                 String              @db.NChar(2)

  pono                     String?             @db.NVarChar(30)
  podate                   DateTime?           @db.DateTime
  notes                    String?             @db.NVarChar(1000)
  smNo                     String?             @db.NVarChar(20)
  subNo                    String?             @db.NVarChar(20)
  oCreditDays              Int?                @db.SmallInt
  oTermCode                String?             @db.NVarChar(10)
  supplyState              String?             @db.NChar(2)
  shiptoPincode            String?             @db.NVarChar(10)
  discount                 Decimal?            @db.Decimal(12, 2)
  oShiptoParty             String?             @db.NVarChar(75)
  oShiptoState             String?             @db.NChar(2)
  oShiptoLoc               String?             @db.NVarChar(50)
  endCustomerId            String?             @db.NChar(6)
  endCustomerBranchId      String?             @db.NChar(3)

  party                    Party               @relation(fields: [clientid, partyId], references: [clientid, partyId])
  curMst                   Currency            @relation(fields: [clientid, currency], references: [clientid, code])
  companyBranch            CompanyBranch       @relation(fields: [clientid, unitCode], references: [clientid, unitCode])
  deliveryAddress          PartyDeliveryAddress @relation(fields: [clientid, partyId, branchId], references: [clientid, partyId, branchId])
  items                    SalesOrderDetails[]

  @@unique([clientid, orderNo])
}
  • SQL: 
    CREATE TABLE SalesOrderHeader (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    orderNo NCHAR(6) NOT NULL,
    dated DATETIME NOT NULL,
    partyId NCHAR(6) NOT NULL,
    value NUMERIC(12,2) NOT NULL,
    branchId NCHAR(3) NOT NULL,
    orderType NCHAR(1) NOT NULL, -- 'Q' (Quotation), 'P' (Proforma), 'D' (Direct)
    yearOrderNo NVARCHAR(20) NOT NULL,
    currency NCHAR(3) NOT NULL,
    freightReqd NCHAR(1) NOT NULL,
    freightId NVARCHAR(10) NOT NULL,
    focAmount NUMERIC(12,2) NOT NULL,
    unitCode NCHAR(2) NOT NULL,
    pono NVARCHAR(30) NULL,
    podate DATETIME NULL,
    notes NVARCHAR(1000) NULL,
    smNo NVARCHAR(20) NULL,
    subNo NVARCHAR(20) NULL,
    oCreditDays SMALLINT NULL,
    oTermCode NVARCHAR(10) NULL,
    supplyState NCHAR(2) NULL,
    shiptoPincode NVARCHAR(10) NULL,
    discount NUMERIC(12,2) NULL,
    oShiptoParty NVARCHAR(75) NULL,
    oShiptoState NCHAR(2) NULL,
    oShiptoLoc NVARCHAR(50) NULL,
    endCustomerId NCHAR(6) NULL,
    endCustomerBranchId NCHAR(3) NULL,
    CONSTRAINT PK_SalesOrderHeader PRIMARY KEY (id),
    CONSTRAINT UQ_SalesOrderHeader_ClientId_OrderNo UNIQUE (clientid, orderNo),
    CONSTRAINT FK_SalesOrderHeader_Party FOREIGN KEY (clientid, partyId) REFERENCES Party(clientid, partyId),
    CONSTRAINT FK_SalesOrderHeader_Currency FOREIGN KEY (clientid, currency) REFERENCES Currency(clientid, code),
    CONSTRAINT FK_SalesOrderHeader_Branch FOREIGN KEY (clientid, unitCode) REFERENCES CompanyBranch(clientid, unitCode),
    CONSTRAINT FK_SalesOrderHeader_DeliveryAddress FOREIGN KEY (clientid, partyId, branchId) REFERENCES PartyDeliveryAddress(clientid, partyId, branchId)
);
CREATE INDEX IX_SalesOrderHeader ON SalesOrderHeader (clientid, orderType);

19. Orders Details (SalesOrderDetails)

  • Purpose: Item lines matching sales orders.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), orderNo (NCHAR(6)), qty (NUMERIC(9,3)), rate (DECIMAL(12,4)), value (NUMERIC(12,2)), itemId (NCHAR(6), FK to Product), orderStatus (NCHAR(2), FK to OrderStatus), yearOrderNo (NVARCHAR(20)), brandId (NCHAR(3), FK to Brand), mrp (NUMERIC(12,4)), gstCode (NCHAR(2))
      • Optional: reqdBy (DATETIME), isNonStd (NCHAR(1)), plannedDate (DATETIME), prNo (NVARCHAR(20)), nonStdNote (NVARCHAR(250)), effectiveDate (DATETIME), notes (NVARCHAR(200)), leadTime (INT), bookingNo (NVARCHAR(20)), baseRate (DECIMAL(12,4))
      • System: clientid (NVARCHAR(5))
    • Removed Columns: userqty, factor, itemNameForQuotation
  • Prisma Model: 
    model SalesOrderDetails {
  id                   Int              @id @default(autoincrement())
  clientid             String           @db.NVarChar(5)
  orderNo              String           @db.NChar(6)
  itemId               String           @db.NChar(6)
  qty                  Decimal          @db.Decimal(9, 3)
  rate                 Decimal          @db.Decimal(12, 4)
  value                Decimal          @db.Decimal(12, 2)
  orderStatus          String           @db.NChar(2)
  yearOrderNo          String           @db.NVarChar(20)
  brandId              String           @db.NChar(3)
  mrp                  Decimal          @db.Decimal(12, 4)
  gstCode              String           @db.NChar(2)

  reqdBy               DateTime?        @db.DateTime
  isNonStd             String?          @db.NChar(1)
  plannedDate          DateTime?        @db.DateTime
  prNo                 String?          @db.NVarChar(20)
  nonStdNote           String?          @db.NVarChar(250)
  effectiveDate        DateTime?        @db.DateTime
  notes                String?          @db.NVarChar(200)
  leadTime             Int?
  bookingNo            String?          @db.NVarChar(20)
  baseRate             Decimal?         @db.Decimal(12, 4)

  order                SalesOrderHeader @relation(fields: [clientid, orderNo], references: [clientid, orderNo], onDelete: Cascade)
  status               OrderStatus      @relation(fields: [clientid, orderStatus], references: [clientid, code])
  product              Product          @relation(fields: [clientid, itemId], references: [clientid, itemId])
  brand                Brand            @relation(fields: [clientid, brandId], references: [clientid, brandId])

  @@index([clientid, orderNo])
}
  • SQL: 
    CREATE TABLE SalesOrderDetails (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    orderNo NCHAR(6) NOT NULL,
    itemId NCHAR(6) NOT NULL,
    qty NUMERIC(9,3) NOT NULL,
    rate DECIMAL(12,4) NOT NULL,
    value NUMERIC(12,2) NOT NULL,
    orderStatus NCHAR(2) NOT NULL,
    yearOrderNo NVARCHAR(20) NOT NULL,
    brandId NCHAR(3) NOT NULL,
    mrp NUMERIC(12,4) NOT NULL,
    gstCode NCHAR(2) NOT NULL,
    reqdBy DATETIME NULL,
    isNonStd NCHAR(1) NULL,
    plannedDate DATETIME NULL,
    prNo NVARCHAR(20) NULL,
    nonStdNote NVARCHAR(250) NULL,
    effectiveDate DATETIME NULL,
    notes NVARCHAR(200) NULL,
    leadTime INT NULL,
    bookingNo NVARCHAR(20) NULL,
    baseRate DECIMAL(12,4) NULL,
    CONSTRAINT PK_SalesOrderDetails PRIMARY KEY (id),
    CONSTRAINT FK_SalesOrderDetails_Header FOREIGN KEY (clientid, orderNo) REFERENCES SalesOrderHeader(clientid, orderNo) ON DELETE CASCADE,
    CONSTRAINT FK_SalesOrderDetails_Status FOREIGN KEY (clientid, orderStatus) REFERENCES OrderStatus(clientid, code),
    CONSTRAINT FK_SalesOrderDetails_Product FOREIGN KEY (clientid, itemId) REFERENCES Product(clientid, itemId),
    CONSTRAINT FK_SalesOrderDetails_Brand FOREIGN KEY (clientid, brandId) REFERENCES Brand(clientid, brandId)
);

20. Sales Invoice Header (SalesInvoiceHeader)

  • Purpose: Domestic tax invoice details, consolidating secondary dispatch override addresses and transporter logistics.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), invoiceNo (NCHAR(6)), dated (DATETIME), partyId (NCHAR(6), FK to Party), value (NUMERIC(12,2)), stax2Amt (NUMERIC(12,2)), discount (NUMERIC(12,2)), pkgFwd (NUMERIC(12,2)), freight (NUMERIC(12,2)), freightAmt (NUMERIC(12,2)), rdg (NCHAR(1)), yearInvoiceNo (NVARCHAR(20)), subType (NVARCHAR(10)), unitCode (NCHAR(2), FK to CompanyBranch), supplyType (NVARCHAR(10)), branchId (NCHAR(3), FK to PartyDeliveryAddress), mode (NCHAR(2)), fcAmount (NUMERIC(12,2)), transporter (NCHAR(3), FK to Transporter), notes (NVARCHAR(1000)), vehicleDetails (NVARCHAR(20)), mainAc (NCHAR(6)), subAc (NCHAR(6)), trnType (NVARCHAR(10)), ein_uom (NVARCHAR(10)), isEinvoice (NCHAR(1)), ein_supplytype (NVARCHAR(10)), carrierCharges (NUMERIC(12,2)), isSpecialHeader (NCHAR(1)), isCancelled (NCHAR(1)), isAutoEwaybill (NCHAR(1))
      • Optional: subNo (NVARCHAR(20)), exciseAmt (NUMERIC(12,2)), stax1Amt (NUMERIC(12,2)), cess2 (NUMERIC(12,2)), discountRate (NUMERIC(5,2)), dueDate (DATETIME), exchRate (NUMERIC(10,4)), ewaybillNo (NVARCHAR(14)), specialNotes (NVARCHAR(250)), lrno (NVARCHAR(20)), lrdate (DATETIME), cases (INT), weight (NUMERIC(10,3)), portbeNo (NVARCHAR(20)), portbeDate (DATETIME)
      • System: clientid (NVARCHAR(5))
    • Removed Columns: currency, supplyto, freightTerms, deliveryDate, shiptoCustomerName, shiptoAddr, shiptoPincode, shiptoStateCode, shiptoGstin, shiptoLoc, dispName, dispAddr1, dispLoc, dispPincode, dispStateCode
  • Prisma Model: 
    model SalesInvoiceHeader {
  id               Int                   @id @default(autoincrement())
  clientid         String                @db.NVarChar(5)
  invoiceNo        String                @db.NChar(6)
  dated            DateTime              @db.DateTime
  partyId          String                @db.NChar(6)
  value            Decimal               @db.Decimal(12, 2)
  stax2Amt         Decimal               @db.Decimal(12, 2)
  discount         Decimal               @db.Decimal(12, 2)
  pkgFwd           Decimal               @db.Decimal(12, 2)
  freight          Decimal               @db.Decimal(12, 2)
  freightAmt       Decimal               @db.Decimal(12, 2)
  rdg              String                @db.NChar(1)
  yearInvoiceNo    String                @db.NVarChar(20)
  subType          String                @db.NVarChar(10)
  unitCode         String                @db.NChar(2)
  supplyType       String                @db.NVarChar(10)
  branchId         String                @db.NChar(3)
  mode             String                @db.NChar(2)
  fcAmount         Decimal               @db.Decimal(12, 2)
  transporter      String                @db.NChar(3)
  notes            String                @db.NVarChar(1000)
  vehicleDetails   String                @db.NVarChar(20)
  mainAc           String                @db.NChar(6)
  subAc            String                @db.NChar(6)
  trnType          String                @db.NVarChar(10)
  ein_uom          String                @db.NVarChar(10)
  isEinvoice       String                @db.NChar(1)
  ein_supplytype   String                @db.NVarChar(10)
  carrierCharges   Decimal               @db.Decimal(12, 2)
  isSpecialHeader  String                @db.NChar(1)
  isCancelled      String                @db.NChar(1)
  isAutoEwaybill   String                @db.NChar(1)

  subNo            String?               @db.NVarChar(20)
  exciseAmt        Decimal?              @db.Decimal(12, 2)
  stax1Amt         Decimal?              @db.Decimal(12, 2)
  cess2            Decimal?              @db.Decimal(12, 2)
  discountRate     Decimal?              @db.Decimal(5, 2)
  dueDate          DateTime?             @db.DateTime
  exchRate         Decimal?              @db.Decimal(10, 4)
  ewaybillNo       String?               @db.NVarChar(14)
  specialNotes     String?               @db.NVarChar(250)
  lrno             String?               @db.NVarChar(20)
  lrdate           DateTime?             @db.DateTime
  cases            Int?
  weight           Decimal?              @db.Decimal(10, 3)
  portbeNo         String?               @db.NVarChar(20)
  portbeDate       DateTime?             @db.DateTime

  party            Party                 @relation(fields: [clientid, partyId], references: [clientid, partyId])
  companyBranch    CompanyBranch         @relation(fields: [clientid, unitCode], references: [clientid, unitCode])
  deliveryAddress  PartyDeliveryAddress  @relation(fields: [clientid, partyId, branchId], references: [clientid, partyId, branchId])
  transporterMst   Transporter           @relation(fields: [clientid, transporter], references: [clientid, transporterCode])
  items            SalesInvoiceDetails[]

  @@unique([clientid, invoiceNo])
}
  • SQL: 
    CREATE TABLE SalesInvoiceHeader (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    invoiceNo NCHAR(6) NOT NULL,
    dated DATETIME NOT NULL,
    partyId NCHAR(6) NOT NULL,
    value NUMERIC(12,2) NOT NULL,
    stax2Amt NUMERIC(12,2) NOT NULL,
    discount NUMERIC(12,2) NOT NULL,
    pkgFwd NUMERIC(12,2) NOT NULL,
    freight NUMERIC(12,2) NOT NULL,
    freightAmt NUMERIC(12,2) NOT NULL,
    rdg NCHAR(1) NOT NULL,
    yearInvoiceNo NVARCHAR(20) NOT NULL,
    subType NVARCHAR(10) NOT NULL,
    unitCode NCHAR(2) NOT NULL,
    supplyType NVARCHAR(10) NOT NULL,
    branchId NCHAR(3) NOT NULL,
    mode NCHAR(2) NOT NULL,
    fcAmount NUMERIC(12,2) NOT NULL,
    transporter NCHAR(3) NOT NULL,
    notes NVARCHAR(1000) NOT NULL,
    vehicleDetails NVARCHAR(20) NOT NULL,
    mainAc NCHAR(6) NOT NULL,
    subAc NCHAR(6) NOT NULL,
    trnType NVARCHAR(10) NOT NULL,
    ein_uom NVARCHAR(10) NOT NULL,
    isEinvoice NCHAR(1) NOT NULL,
    ein_supplytype NVARCHAR(10) NOT NULL,
    carrierCharges NUMERIC(12,2) NOT NULL,
    isSpecialHeader NCHAR(1) NOT NULL,
    isCancelled NCHAR(1) NOT NULL,
    isAutoEwaybill NCHAR(1) NOT NULL,
    subNo NVARCHAR(20) NULL,
    exciseAmt NUMERIC(12,2) NULL,
    stax1Amt NUMERIC(12,2) NULL,
    cess2 NUMERIC(12,2) NULL,
    discountRate NUMERIC(5,2) NULL,
    dueDate DATETIME NULL,
    exchRate NUMERIC(10,4) NULL,
    ewaybillNo NVARCHAR(14) NULL,
    specialNotes NVARCHAR(250) NULL,
    lrno NVARCHAR(20) NULL,
    lrdate DATETIME NULL,
    cases INT NULL,
    weight NUMERIC(10,3) NULL,
    portbeNo NVARCHAR(20) NULL,
    portbeDate DATETIME NULL,
    CONSTRAINT PK_SalesInvoiceHeader PRIMARY KEY (id),
    CONSTRAINT UQ_SalesInvoiceHeader UNIQUE (clientid, invoiceNo),
    CONSTRAINT FK_SalesInvoiceHeader_Party FOREIGN KEY (clientid, partyId) REFERENCES Party(clientid, partyId),
    CONSTRAINT FK_SalesInvoiceHeader_Branch FOREIGN KEY (clientid, unitCode) REFERENCES CompanyBranch(clientid, unitCode),
    CONSTRAINT FK_SalesInvoiceHeader_DeliveryAddress FOREIGN KEY (clientid, partyId, branchId) REFERENCES PartyDeliveryAddress(clientid, partyId, branchId),
    CONSTRAINT FK_SalesInvoiceHeader_Transporter FOREIGN KEY (clientid, transporter) REFERENCES Transporter(clientid, transporterCode)
);
CREATE INDEX IX_SalesInvoiceHeader ON SalesInvoiceHeader (clientid, partyId);

21. Sales Invoice Details (SalesInvoiceDetails)

  • Purpose: Product item lines matched to sales invoices.
  • Column Classification:
    • Required Columns:
      • Mandatory: id (INT PK), invoiceNo (NCHAR(6)), itemId (NCHAR(6), FK to Product), qty (NUMERIC(9,3)), rate (DECIMAL(12,4)), value (NUMERIC(12,2)), orders1Id (INT), yearInvoiceNo (NVARCHAR(20)), itemState (NVARCHAR(10)), stage (NVARCHAR(20)), gstCode (NCHAR(2)), packingSlip (NVARCHAR(20)), noOfPackets (INT)
      • Optional: None
      • System: clientid (NVARCHAR(5))
    • Removed Columns: nonstdnote
  • Prisma Model: 
    model SalesInvoiceDetails {
  id            Int                @id @default(autoincrement())
  clientid      String             @db.NVarChar(5)
  invoiceNo     String             @db.NChar(6)
  itemId        String             @db.NChar(6)
  qty           Decimal            @db.Decimal(9, 3)
  rate          Decimal            @db.Decimal(12, 4)
  value         Decimal            @db.Decimal(12, 2)
  orders1Id     Int
  yearInvoiceNo String             @db.NVarChar(20)
  itemState     String             @db.NVarChar(10)
  stage         String             @db.NVarChar(20)
  gstCode       String             @db.NChar(2)
  packingSlip   String             @db.NVarChar(20)
  noOfPackets   Int

  header        SalesInvoiceHeader @relation(fields: [clientid, invoiceNo], references: [clientid, invoiceNo], onDelete: Cascade)
  product       Product            @relation(fields: [clientid, itemId], references: [clientid, itemId])

  @@index([clientid, invoiceNo])
}
  • SQL: 
    CREATE TABLE SalesInvoiceDetails (
    id INT IDENTITY(1,1) NOT NULL,
    clientid NVARCHAR(5) NOT NULL,
    invoiceNo NCHAR(6) NOT NULL,
    itemId NCHAR(6) NOT NULL,
    qty NUMERIC(9,3) NOT NULL,
    rate DECIMAL(12,4) NOT NULL,
    value NUMERIC(12,2) NOT NULL,
    orders1Id INT NOT NULL,
    yearInvoiceNo NVARCHAR(20) NOT NULL,
    itemState NVARCHAR(10) NOT NULL,
    stage NVARCHAR(20) NOT NULL,
    gstCode NCHAR(2) NOT NULL,
    packingSlip NVARCHAR(20) NOT NULL,
    noOfPackets INT NOT NULL,
    CONSTRAINT PK_SalesInvoiceDetails PRIMARY KEY (id),
    CONSTRAINT FK_SalesInvoiceDetails FOREIGN KEY (clientid, invoiceNo) REFERENCES SalesInvoiceHeader(clientid, invoiceNo) ON DELETE CASCADE,
    CONSTRAINT FK_SalesInvoiceDetails_Product FOREIGN KEY (clientid, itemId) REFERENCES Product(clientid, itemId)
);

Discussion / Doubt Points (Pending Confirmation)

Table-Specific Doubts

18. SalesOrderHeader

  • oTermCode: If oTermCode is used, do we need to add a PaymentTerms master table or will we use a hardcoded combo for it?
  • SupplyState: Can we take SupplyState directly from Party instead of storing it separately?
  • isConfirmed: Needs to be discussed.

19. SalesOrderDetails

  • UserQty: We can remove it since we are storing that value in the qty column directly.
  • Factor: If non-INR, the currency exchange factor is taken from PriceMaster. Do we still need to store it locally?

20. InvoiceHeader

  • Currency: We are capturing currency in the sales order. Do we still need to repeat it in the invoice?
  • SupplyState: Can we take SupplyState directly from Party?

21. InvoiceDetails

  • Stage: Will the stage column use a hardcoded combo box?

Common / General Doubts

  • Doc No vs. Year Doc No: Instead of having both docno and yeardocno (e.g. orderno and yearorderno), can we store only yearorderno / yeardocno in the tables?
  • Party Branch: Instead of having two separate columns for party branch (e.g. partyId and branchId), can we store them in the same column?