Proforma Invoice (PI) — Workflow & App Guide

App: ToolJet — Proforma Invoice Generator Schema: saar_biotech Related tables: tbl_order_details, tbl_client, tbl_product, tbl_client_address, tbl_proforma_drafts Data model: see tbl_proforma_drafts in Data Dictionary

1. What This App Does

The Proforma Invoice app lets dispatch/admin staff generate a client-facing Proforma Invoice (PI) PDF for any order. It is opened by clicking an order row in the main orders table.

The app: - Pre-fills all client details, addresses, and line items from the database - Lets the user override names, addresses, GSTIN, DL numbers, transport info, and line-item details (batch, dates, boxes) - Auto-calculates GST (CGST/SGST for Himachal Pradesh clients, IGST for others) using a master HSN rates table - Shows Plate Charges as a transparent line item (displayed but excluded from the payable total) - Saves a draft so the user can come back without losing their edits - Generates and downloads a formatted PDF

2. Queries at a Glance

Query	Type	Purpose
`fetch_pi_details`	SQL	Main data loader — fetches order, client, product, address, and existing draft
`pi_initialize_draft_items`	RunJS	Parses the SQL result, loads the saved draft (if any), or builds fresh line items
`fetch_hsn_code_rates`	SQL	Loads the master HSN code to GST rate lookup table
`pi_save_changes`	RunJS	Triggers the database save and then launches the PDF print dialog
`pi_save_changes_database`	SQL	UPSERT into `tbl_proforma_drafts` — saves all form fields and line items
`pi_upsert_address`	SQL	Saves a new/edited address to `tbl_client_address`
`split_pi_row`	RunJS	Splits one line-item row into two in the editable table
`download_pi_pdf`	RunJS	Saves draft first, then renders the HTML into a print-dialog PDF

3. Data Load and Hydration Flow

When the user opens an order, the following sequence runs automatically:

User clicks order row
        |
        v
fetch_pi_details (SQL)
        |
        +-- Fetches from: tbl_order_details + tbl_client + tbl_product
        |   + tbl_client_address (aggregated as ALL_ADDRESSES JSON)
        |   + tbl_proforma_drafts (LEFT JOIN -- returns NULL if no draft exists)
        |
        v
pi_initialize_draft_items (RunJS)
        |
        +-- Does PI_DATA_JSON exist in the result?
        |       |
        |       +-- YES --> Load saved draft:
        |       |           - Set variable: active_draft_data  (all form field values)
        |       |           - Set variable: draft_pi_items     (saved line-item rows)
        |       |           - STOP (draft fully restored, DB values ignored)
        |       |
        |       +-- NO  --> Build fresh items:
        |                   - Map each SQL row into a draft_pi_items object
        |                   - Set BATCH_NO, MFG_DATE, EXP_DATE to empty strings
        |                   - Set No_of_Boxes = 1 (default)
        |
        v
form_pi_settings reads active_draft_data to pre-fill all inputs
html1 renders the live PDF preview using draft_pi_items + form inputs

4. The `fetch_pi_details` SQL

SELECT
    o."ORDER_NO_C", o."MARG_ORDER_NO_C", o."BRAND_C", o."PACKING_DIMENSIONS_C",
    o."CARTON_PACKAGING_C", o."QUANTITY_I", o."RATE_I", o."MRP_I",
    o."HSN_CODE_I", o."BATCH_NO_C", o."PLATE_CHARGES_B",
    o."INVENTORY_CHARGES_B", o."CYLINDER_CHARGES_B", o."ENQ_PHONE_C",
    c."CLIENT_NAME_C", c."GST_NO_C", c."DRUG_LICENSE_NO_C", c."CLIENT_ID_I",
    p."PRODUCT_NAME_C",
    (SELECT jsonb_agg(addr) FROM (
        SELECT "ADDESS_ID_C" AS value,
               "ADDRESS_TYPE_C" || ' - ' || "C_ADDRESS_DISPLAY" AS label,
               "ADDRESS_TYPE_C" AS type,
               REPLACE("C_ADDRESS_DISPLAY", CHR(10), '<br/>') AS raw_address,
               "STATE_C" AS state
        FROM saar_biotech.tbl_client_address
        WHERE "CLIENT_ID_I" = o."CLIENT_ID_I"
    ) addr) AS "ALL_ADDRESSES",
    pd."PI_DATA_JSON",   -- NULL if no draft saved yet
    pd."UPDATED_TS"
FROM saar_biotech.tbl_order_details o
LEFT JOIN saar_biotech.tbl_client c ON o."CLIENT_ID_I" = c."CLIENT_ID_I"
LEFT JOIN saar_biotech.tbl_product p ON p."PRODUCT_ID_I" = o."COMPOSITION_C"
LEFT JOIN saar_biotech.tbl_proforma_drafts pd ON pd."ORDER_NO_C" = o."ORDER_NO_C"
WHERE o."ORDER_NO_C" = '{{components.table1.selectedRow.ORDER_NO_C}}'

Key point: tbl_proforma_drafts is a LEFT JOIN. If no draft exists, PI_DATA_JSON is NULL and the initializer builds fresh items from tbl_order_details.

5. The HTML Renderer

The html1 component runs a JavaScript function that produces the live PDF preview.

5.1 Three-Tier Value Resolution

Every field resolves in priority order: 1. Live form input — what the user is currently typing in form_pi_settings 2. Saved draft — the last value stored in PI_DATA_JSON.selections 3. Database fallback — the original value from tbl_order_details or tbl_client

5.2 GST Routing

Determined from the Billing GSTIN in the form (not hardcoded from DB):

const isHimachalClient =
    clientGst.startsWith('02')      // State code 02 = Himachal Pradesh
    || billingState.includes('HIMACHAL')
    || billingState === 'HP';

Client State	Tax Applied
Himachal Pradesh	CGST + SGST (rate split equally)
Any other state	IGST (full rate)

Changing the Billing GSTIN in the form instantly recalculates all GST values in the preview.

5.3 Plate Charges

PLATE_CHARGES_B is read from the database row (not from the form)
If > 0, a PLATE CHARGES row is added to the invoice table
GST is looked up using the fixed HSN code 84425010
Plate charges are included in totalAssValue and flow into the Grand Total
The QTY cell shows - — they are not counted in the physical goods quantity total

Business rule: Plate charges are shown to the client for transparency, but are considered already included in the Billing Amount for payment tracking. They are never manually entered — always read from the database.

5.4 Per-Row HSN Resolution

row.HSN_CODE_I || row.HSN_CODE || row.HSN_CODE_C || row.hsn_code || globalHsnCode

A per-product HSN code always overrides the global HSN selector in the form.

6. Form Settings Fields (`form_pi_settings`)

Field	Pre-filled from	Also used for
`billing_client_name`	`CLIENT_NAME_C`	Printed name on invoice
`delivery_client_name`	`CLIENT_NAME_C`	Delivery section name
`billing_address`	Address dropdown	Selects which address block prints
`delivery_address`	Address dropdown	Selects delivery address block
`billing_gst`	`GST_NO_C`	Drives CGST/IGST routing
`billing_dl`	`DRUG_LICENSE_NO_C`	DL No. under billing section
`delivery_gst`	`GST_NO_C`	Delivery section GSTIN
`delivery_dl`	`DRUG_LICENSE_NO_C`	Delivery section DL No.
`enq_phone`	`ENQ_PHONE_C`	Phone number on invoice
`po_no`, `po_date`	Empty	PO reference from client
`transport_name`	Empty	Transport company name
`transport_mode`	`BY ROAD`	Dropdown
`gr_no`	Empty	GR/LR number
`hsn_code`	`HSN_CODE_I`	Global fallback HSN code

7. Save Flow (`pi_save_changes_database`)

The save query performs an UPSERT into tbl_proforma_drafts. One row per order — saving always overwrites the previous draft.

The order_items saved are built by merging the base variable with any live table cell edits:

variables.draft_pi_items.map((row, index) => ({
    ...row,
    ...(components.Table_DraftItems?.dataUpdates?.[index] || {})
}))

The three sections saved are:

selections — all form_pi_settings field values (names, addresses, GSTIN, DL, phone)
dispatch_info — transport name/mode, GR No., PO No., PO Date
order_items — complete snapshot of all line items including user edits (batch, dates, boxes, suffix)

For the full table schema, see Data Dictionary → tbl_proforma_drafts.

8. PDF Generation (`download_pi_pdf`)

User clicks Download PDF
        |
        v
await pi_save_changes_database.run()    <- Save always happens first
        |
        v
Read html1.rawHTML                      <- Grab the rendered invoice HTML
        |
        v
Create a hidden iframe                  <- Invisible to user
Inject HTML + print CSS                 <- A4 Portrait, 8mm margins,
                                           background colors forced on
        |
        v
Set document.title to                   <- Tricks the browser Save dialog
"PI {MargOrderNo} {ClientName}"            into using the right filename
        |
        v
iframe.contentWindow.print()            <- Opens native PDF dialog
        |
        v
Restore title, remove iframe            <- Cleanup after 1 second

Filename format: PI SO-2024-001 ABC Pharma (spaces, no underscores, forbidden chars stripped)

9. Known Behaviours and Rules

Behaviour	Reason
Save always overwrites the previous draft	`ON CONFLICT DO UPDATE` — one draft per order by design
Plate charges QTY shows `-`, not added to physical total	Plate charges are not physical goods
Editing Billing GSTIN to a non-HP code instantly re-routes all GST to IGST	`isHimachalClient` is computed live from the form, not from the DB value
Per-row HSN code always wins over the form selector	Product-level HSN is more specific than the order-level fallback
Download always saves first	Ensures the PDF always reflects the latest edits

For the approval workflow that runs before PI generation, see Pre-Sales Order Approval. For the tbl_proforma_drafts data model, see Data Dictionary.