Skip to content

Proforma Drafts Schema — tbl_proforma_drafts

Schema: saar_biotech Used by: Proforma Invoice Workflow Purpose: Stores the work-in-progress draft for any Proforma Invoice. One row per order. Allows users to save their edits and return later without losing any data.

Table: tbl_proforma_drafts

Column Type Use
ORDER_NO_C TEXT (Primary Key) Internal order number — links to tbl_order_details
SALE_ORDER_NO_C TEXT The Marg/ERP order reference number
PI_DATA_JSON JSONB The entire saved draft state (form fields, transport info, line items)
CREATED_BY VARCHAR(100) User ID who first created the draft
UPDATED_BY VARCHAR(100) User ID who last saved the draft
UPDATED_TS TIMESTAMP When the draft was last saved

One row per order. Saving always upserts — never creates duplicates.

PI_DATA_JSON — What Is Stored Inside

This single JSONB column holds the complete PI draft in three sections:

selections — Form Field Values

Everything the user typed into the PI settings form.

Field What It Stores
billing_client_name Name shown in the Billing section
delivery_client_name Name shown in the Delivery section
billing_address_type Selected billing address ID
delivery_address_type Selected delivery address ID
billing_gst Billing GSTIN — also drives CGST vs IGST routing
billing_dl Billing Drug License number
delivery_gst Delivery GSTIN
delivery_dl Delivery Drug License number
enq_phone Phone number printed on the invoice

Example: 

{
  "billing_client_name":  "ABC Pharma Ltd",
  "billing_address_type": "addr-uuid-1",
  "billing_gst":          "02XXXXX1234A1Z5",
  "billing_dl":           "MH/DL/12345",
  "delivery_client_name": "ABC Pharma Ltd",
  "delivery_address_type":"addr-uuid-2",
  "delivery_gst":         "02XXXXX1234A1Z5",
  "delivery_dl":          "MH/DL/12345",
  "enq_phone":            "9876543210"
}

dispatch_info — Transport & PO Details

Field What It Stores
marg_order_no Marg/ERP sale order number
po_no Client's Purchase Order number
po_date PO date
transport_name Transport company name
transport_mode Mode of transport (BY ROAD, BY AIR, etc.)
gr_no GR/LR number from the transporter

Example: 

{
  "marg_order_no":  "SO-2024-001",
  "po_no":          "PO-999",
  "po_date":        "01/09/2026",
  "transport_name": "DTDC Logistics",
  "transport_mode": "BY ROAD",
  "gr_no":          "GR-456"
}

order_items — Line Item Snapshot

A JSON array. Each object is one product row — a merged snapshot of the database values plus any edits the user made in the ToolJet table before saving.

Field What It Stores
BRAND_C Brand name
PRODUCT_NAME_C Product name
HSN_CODE_I HSN code for this product
PACKING_DIMENSIONS_C Bottle/tube size
CARTON_PACKAGING_C Units per carton
RATE_I Price per unit
MRP_I MRP
QUANTITY_I Original order quantity
PACK_SIZE Packing size (copy of CARTON_PACKAGING_C)
No_of_Boxes Number of boxes — editable by user
BATCH_NO Batch number — filled by user
MFG_DATE Manufacturing date — filled by user
EXP_DATE Expiry date — filled by user
PRODUCT_SUFFIX Optional suffix label shown on the invoice

Example: 

[
  {
    "BRAND_C":        "MYCOVITA FORTE",
    "PRODUCT_NAME_C": "Multivitamin Capsules",
    "HSN_CODE_I":     "30049099",
    "No_of_Boxes":    5,
    "BATCH_NO":       "B-2026-001",
    "MFG_DATE":       "01/2026",
    "EXP_DATE":       "01/2028",
    "RATE_I":         "150",
    "MRP_I":          "200",
    "PRODUCT_SUFFIX": ""
  }
]

Key Behaviours

  • If PI_DATA_JSON is NULL (no draft saved yet), the app builds fresh line items directly from tbl_order_details
  • billing_gst inside selections drives live GST routing in the invoice — changing it to a non-HP GSTIN instantly switches from CGST/SGST to IGST
  • order_items captures live table edits by merging the saved variable with Table_DraftItems.dataUpdates at save time

See also: Proforma Invoice Workflow for the full app and query logic.