Support #97
closedBulk GRV Import Testing
Description
Bulk GRV → CGRN Import Wizard --- Enhancements & Implementation Description¶
1. Project Overview¶
The GRV to CGRN Import Wizard is a guided Frappe-based import
workflow that converts an ERP-exported Goods Receipt Voucher (GRV)
Excel file into a Draft Customer Goods Receipt Note (CGRN).
The wizard is designed to reduce manual data entry and provide
controlled validation before a CGRN is created. It handles:
- Bill of Entry selection and validation
- GRV Excel upload
- Mapping of GRV headers and line-item fields to CGRN fields
- Mapping preview with sample values
- User-editable mapped values
- Re-validation after manual corrections
- Line-item review
- Internal-lot / Lot Reference matching
- Existing-lot reuse and new-lot creation
- Duplicate and consistency checks
- Quantity and balance-quantity validation
- Optional logistics fields
- Import summary and CGRN creation
- Navigation to the created Draft CGRN
- Post-creation Lot Balance refresh through Fetch LOT Balance Qty
- Restarting the workflow through Start New Import
- Responsive handling of wide result tables through the final
horizontal-scroll enhancement
The intended business flow is:
ERP GRV Excel
|
v
+---------------------------+
| GRV → CGRN Import Wizard |
+---------------------------+
|
v
1. Upload GRV File
|
v
2. Mapping Preview
|
v
3. Review & Validate
|
v
4. Confirm & Create
|
v
Draft CGRN
|
v
Review / Fetch Lot Balance
|
v
Submit CGRN
|
v
Inventory Updated
The wizard creates the Draft CGRN; inventory is updated only after
the CGRN is subsequently submitted.
2. Core Problem Addressed¶
The original process requires information from a GRV Excel export to be
transferred into the CGRN process while maintaining relationships with:
- Bill of Entry
- Part Number
- Internal Lot / Lot Reference
- Invoice Number
- Source EXIM transaction information
- Source quantity
- CGRN inward quantity
- Remaining / balance quantity
- Existing CGRN and Lot relationships
Manual handling creates risks such as:
- Incorrect field mapping
- Incorrect Bill of Entry selection
- Duplicate lot references
- Incorrect quantities
- Negative or invalid quantities
- Reusing the wrong lot
- Creating new lots unnecessarily
- Creating CGRNs after source quantity has already been consumed
- Inconsistent cumulative inward quantities
- Missing mandatory information
- Duplicate submission caused by repeated clicks
- Difficulty reviewing wide result tables
The wizard therefore uses a validate-before-create approach.
3. Wizard Architecture¶
The implementation can be understood as four logical layers:
┌─────────────────────────────────────────────┐
│ USER INTERFACE │
│ │
│ Upload → Mapping → Review → Confirm/Results │
└──────────────────────┬──────────────────────┘
│
v
┌─────────────────────────────────────────────┐
│ IMPORT / MAPPING LOGIC │
│ │
│ Excel reading → Header mapping → Line data │
│ → Value normalization → User corrections │
└──────────────────────┬──────────────────────┘
│
v
┌─────────────────────────────────────────────┐
│ VALIDATION / BUSINESS LOGIC │
│ │
│ BOE → duplicates → lots → quantities → │
│ balances → mandatory data → final checks │
└──────────────────────┬──────────────────────┘
│
v
┌─────────────────────────────────────────────┐
│ FRAPPE / ERP DATA │
│ │
│ Bill of Entry / Lot / CGRN / linked data │
└─────────────────────────────────────────────┘
This separation allows the user to correct mapping information before
the creation step rather than discovering errors only after a CGRN has
been created.
4. Step 1 --- Upload GRV File¶
Purpose¶
Step 1 establishes the source document and the business context for the
import.
The user provides:
- Bill of Entry
- Mapping Profile
- GRV
.xlsxfile
The Preview Mapping action becomes available only when the required
input is available.
Flow¶
Select Bill of Entry
|
v
Select Mapping Profile
|
v
Choose .xlsx
|
v
Validate Excel
|
+---- Invalid / Empty ----> Error
|
v
Preview Mapping
|
v
Step 2
Upload validations¶
The wizard handles scenarios including:
- Valid Excel upload
- Empty Excel
- Missing Bill of Entry selection
- Bill of Entry mismatch
- Multiple Excel selection
- Missing mandatory Excel information
- Invalid source data
The test coverage specifically verifies that a mismatched Bill of Entry
produces an error and prevents normal progression, while an empty file
is also rejected.
The interface also restricts the import to a single Excel document.
5. Step 2 --- Mapping Preview¶
Step 2 is the main transformation and review stage.
The uploaded GRV structure is mapped into the CGRN structure before any
document is created.
GRV Excel
|
+--> Header fields
|
+--> Line-item fields
|
+--> Sample values
|
v
Mapped CGRN representation
The user can inspect:
- GRV label / source column
- Target CGRN field
- Sample value
- Imported line information
The purpose is to make the transformation visible instead of treating
the Excel import as a black box.
Editable mapping¶
The mapped fields are user-editable.
This provides a controlled correction mechanism:
Excel Data
|
v
Automatic Mapping
|
v
User Reviews
|
+---- Correct ----> Continue
|
+---- Incorrect --> Edit Field
|
v
Re-validate
|
v
Continue
The Re-validate functionality allows the user to validate the
updated values again after editing.
6. Header and Line-Item Mapping¶
The mapping layer validates both document-level and line-level
information.
Important relationships include:
GRV Header
|
+--> CGRN Header
GRV Line
|
+--> Part Code
+--> Quantity
+--> Invoice
+--> Internal Lot
+--> Source EXIM information
|
v
CGRN Item / Lot relationship
The implementation specifically verifies that the GRV header fields and
line-item fields are mapped correctly and that the mapped sample values
correspond to the source Excel.
The Lot Reference relationship is also checked against the Internal Lot
information shown during review.
7. Step 3 --- Review & Validate¶
Step 3 converts the mapping preview into a business-level validation
screen.
Each GRV row is presented for review together with its associated lot
information.
The user can verify:
- Part Number
- Quantity
- Invoice Number
- Lot Reference
- Lot Match
- Source-related information
- Optional logistics information
The workflow only allows the creation stage after the validation
conditions are satisfied.
8. Optional Fields¶
The wizard supports the following optional fields:
- Vehicle No
- One Time Lock No
- Insurance Policy
These fields are intentionally optional and do not prevent the import
when they are not applicable.
Insurance information is also supported as part of the mapped/imported
data where available.
9. Lot Matching Logic¶
Lot handling is a central part of the wizard.
The logic distinguishes between:
Existing Part / Existing Lot¶
When the same relevant business combination already exists, the existing
lot can be reused rather than unnecessarily creating another lot.
Conceptually:
Incoming GRV Row
|
v
Find matching BOE / Invoice / Part relationship
|
+---- Existing Lot Found ----> Reuse Existing Lot
|
+---- No Existing Lot -------> Create New Lot
This preserves the relationship between subsequent CGRNs and the
relevant Lot Number.
The tested behavior includes reuse of an existing lot when the same
applicable part information already exists, while a new part can result
in a new lot.
10. Repeated Part Numbers¶
Repeated part numbers do not automatically mean that the same lot must
be reused.
The import logic supports cases where the same part code occurs more
than once but the source rows represent separate lot references.
The resulting behavior is:
Same Part Code
|
+---- Row 1 --> Lot A
|
+---- Row 2 --> Lot B
|
+---- Row 3 --> Lot C
This avoids incorrectly collapsing distinct source rows into one lot.
11. Duplicate Detection¶
The wizard includes duplicate checks at both source and business-data
levels.
Examples covered by testing include:
- Same Part Number + same Internal Lot appearing multiple times
- Repeated Internal Lot numbers
- Duplicate Excel fields
- Repeated invoice information
When a duplicate condition requires correction, the user can return to
the earlier step, modify the mapping/source values, and re-run the
validation.
The objective is to stop invalid data before the CGRN creation
operation.
12. Quantity and Balance Validation¶
Quantity handling is one of the most important business controls.
The wizard maintains the relationship:
Item Quantity in Source
=
CGRN Item Inward Quantity
+
Item Qty in Source Less CGRN Inward Qty
This relationship is used to ensure that the amount received through
CGRNs does not exceed the source quantity.
Quantity validation model¶
Source Quantity
|
v
Already Inward / Consumed Quantity
|
v
Remaining Balance
|
v
Requested CGRN Quantity
|
+---- <= Balance ----> Valid
|
+---- > Balance -----> Error
|
+---- <= 0 ----------> Error
The validation is intended to prevent:
- Zero quantity
- Negative quantity
- Quantity greater than available balance
- Further creation after the source quantity has been completely
consumed - Cumulative inward quantity exceeding the source quantity
13. Cumulative Inward Quantity¶
The wizard is designed around cumulative quantity rather than checking
each new import in isolation.
The relevant relationship is:
Source Item Quantity
|
v
Existing CGRN inward quantities
|
v
Current requested inward quantity
|
v
Total cumulative inward quantity
|
v
Must remain <= Source Item Quantity
This is important when multiple CGRNs are created against the same
source item or lot.
The Lot Number / CGRN relationship is therefore part of the quantity
calculation and validation model.
14. Fully Consumed Source Quantity¶
The wizard also handles the case where the source quantity has already
been fully consumed.
Expected business behavior:
Source Quantity = 100
Existing Inward = 100
Remaining = 0
New Import
|
v
Remaining Balance = 0
|
v
No Balance Available
|
v
CGRN creation blocked
The expected user-facing outcome is a No balance quantity available
condition rather than allowing another CGRN to be created against a
fully consumed source quantity.
15. Lot Balance Validation¶
The system also checks the requested CGRN quantity against the available
Lot Number balance.
Requested CGRN Qty
|
v
Lot Number Balance Qty
|
+---- Requested <= Balance --> Valid
|
+---- Requested > Balance ---> Error
This provides a second level of quantity protection after source-level
validation.
16. Data Normalization¶
Source Excel data may contain formatting differences such as leading or
trailing spaces.
The wizard normalizes values before validation where required.
Example:
Excel:
" PART-001 "
|
v
Trim / Normalize
|
v
"PART-001"
This prevents formatting noise from causing false mismatches and ensures
that the stored/matched part information uses the normalized value.
17. Validation Before CGRN Creation¶
The overall design follows a gate-based validation model.
┌─────────────────┐
│ Excel Uploaded │
└────────┬────────┘
v
┌─────────────────┐
│ BOE Validation │
└────────┬────────┘
v
┌─────────────────┐
│ Mapping Check │
└────────┬────────┘
v
┌─────────────────┐
│ Duplicate Check │
└────────┬────────┘
v
┌─────────────────┐
│ Lot Validation │
└────────┬────────┘
v
┌─────────────────┐
│ Qty / Balance │
└────────┬────────┘
v
┌─────────────────┐
│ Final Review │
└────────┬────────┘
v
┌─────────────────┐
│ Create CGRN │
└─────────────────┘
The key design principle is:
Do not create the CGRN until the imported data has passed the
required validation stages.
18. Step 4 --- Confirm & Create¶
Step 4 presents the final import summary.
The user reviews the summarized import information and selects Create
CGRN.
Step 3 Validation Complete
|
v
Import Summary
|
v
Create CGRN
|
v
Draft CGRN Created
The system creates the CGRN in Draft state.
Inventory is not considered updated merely because the wizard created
the Draft CGRN.
19. Duplicate-Click Protection¶
The Create action is protected against accidental repeated submission.
Example:
User clicks Create CGRN
|
v
Creation process starts
|
+---- Second click
|
v
Must not create
another CGRN
Testing explicitly covers double-click behavior and verifies that only
one CGRN is created.
This is important because the creation action is a state-changing
operation.
20. Rollback / Navigation Safety¶
The workflow also supports returning to earlier stages when validation
requires correction.
Conceptually:
Step 3 Validation
|
+---- Failure / Correction Required
|
v
Step 2 / Previous Stage
|
v
Edit / Correct
|
v
Re-validate
|
v
Continue
The intended behavior is that invalid intermediate data does not result
in an unintended CGRN being created.
21. CGRN and Lot Relationship¶
After creation, the resulting CGRN maintains the relationship with the
relevant Lot Number and source item.
The data relationship can be visualized as:
Bill of Entry
|
+------------------+
| |
v v
Source Item Lot Number
| |
| +------> Existing CGRN(s)
|
+------> Current CGRN
This relationship is important for:
- Inward quantity calculations
- Remaining quantity calculations
- Existing lot reuse
- Cumulative quantity control
- Lot balance refresh
22. Post-Creation Workflow¶
Once the Draft CGRN is created, the user can:
- Open the CGRN
- Review the imported information
- Review linked lot numbers
- Refresh lot balances
- Submit the CGRN
The documented operational sequence is:
Draft CGRN
|
v
Review Details
|
v
Fetch LOT Balance Qty
|
v
Remaining Lot Qty Updated
|
v
Submit
|
v
Inventory Updated
The Fetch LOT Balance Qty action is therefore an important
post-creation step before submission.
23. Start New Import¶
After a successful CGRN creation, the wizard provides a Start New
Import path.
CGRN Successfully Created
|
+---- Open CGRN
|
+---- Start New Import
|
v
Step 1
This allows another GRV import to begin without requiring the user to
manually navigate through unrelated screens.
24. UI / Usability Enhancements¶
24.1 Mapping Visibility¶
The mapping and review screens were designed so that users can inspect
the imported values rather than relying only on automatic processing.
The testing specifically checks that fields required for mapping
verification are visible and that long values such as Part Code remain
accessible.
24.2 Editable Mapping¶
The mapping preview allows user corrections before creation.
24.3 Re-validation¶
After editing mapped values, the user can explicitly re-run validation.
24.4 Clear Workflow Navigation¶
The wizard provides a controlled sequence:
Upload
↓
Mapping
↓
Review
↓
Confirm
↓
Result
24.5 Final Results Table Horizontal Scrolling¶
A final UI enhancement was made to the Step 4 Results table.
Problem¶
When result columns become wider than the available browser/window
width, the table can exceed the visible area.
Previously:
| Col 1 | Col 2 | Col 3 | Col 4 | Col 5 | Col 6 | ...
↑
content becomes inaccessible
The page could require resizing/reloading to access content outside the
visible width.
Enhancement¶
The existing results table was made horizontally scrollable when its
content exceeds the available width.
┌───────────────────────────────────────────────┐
│ Results Table │
│ │
│ | C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | │
│ │
│ ←──────────── horizontal scroll ───────────→ │
└───────────────────────────────────────────────┘
Expected behavior¶
Table fits:
┌─────────────────────────────┐
│ All columns visible │
└─────────────────────────────┘
Table overflows:
┌─────────────────────────────┐
│ Visible columns │
│ │
│ ←──────── scroll ─────────→ │
└─────────────────────────────┘
The enhancement is intentionally limited to horizontal accessibility. It
does not redesign the table, change its data, alter its columns, modify
sorting/filtering, change Retry behavior, change CGRN navigation, or
change backend business logic.
This keeps the existing functionality intact while making the Results
screen usable across different browser/window widths.
25. Error Handling Model¶
The wizard uses validation errors as workflow gates rather than allowing
invalid data to proceed silently.
Validation
|
+---- PASS ----> Continue
|
+---- FAIL ----> Show Error
|
v
Correct Data
|
v
Re-validate
Important error scenarios include:
- Bill of Entry mismatch
- Missing Bill of Entry
- Empty Excel
- Duplicate data
- Invalid lot relationship
- Invalid quantity
- Quantity exceeding lot balance
- No remaining source balance
- Missing mandatory information
- Invalid downstream CGRN data
The result is a controlled failure model instead of partial document
creation.
26. Validation Coverage¶
The implemented workflow has been tested against a broad set of
scenarios, including:
Area Validation
Upload Valid Excel import
Upload Empty Excel
Upload Multiple Excel selection
BOE Missing BOE selection
BOE BOE mismatch
Mapping Header mapping
Mapping Line-item mapping
Mapping Sample-value comparison
Mapping Editable fields
Mapping Re-validation
Lot Lot Reference validation
Lot Existing lot reuse
Lot New lot creation
Lot Repeated lot detection
Duplicate Repeated Part + Internal Lot
Duplicate Duplicate Excel fields
Invoice Repeated invoice validation
Quantity Source vs inward quantity
Quantity Remaining balance
Quantity Lot balance
Quantity Fully consumed source
Quantity Zero / negative quantity
Quantity Cumulative inward quantity
Data Quality Leading/trailing spaces
Creation Draft CGRN creation
Creation Double-click protection
Navigation Start New Import
Navigation Back / correction flow
Post-Creation Lot balance refresh
UI Wide Results table accessibility
The recorded test suite contains 40 populated test cases covering the
wizard's primary workflow and business validations.
27. Dependencies / Technology Areas¶
The implementation operates within the Frappe/ERP application
environment and depends on the application's existing data model and
business logic.
The major technical areas are:
Frontend¶
- Frappe page/wizard UI
- JavaScript-driven step navigation
- Dynamic form/table rendering
- Client-side validation and interaction handling
- Responsive table/container behavior
Backend / ERP¶
- Frappe server-side APIs / document operations
- Bill of Entry data
- Lot Number data
- CGRN data
- Existing linked-document relationships
- Quantity and balance information
Input¶
- ERP-exported
.xlsxGRV file - Selected Bill of Entry
- Selected Mapping Profile
Data Processing¶
- Excel parsing
- Header/field mapping
- Row-level processing
- Value normalization
- Duplicate detection
- Lot matching
- Quantity calculation
- Validation before document creation
The wizard is therefore not simply an Excel uploader; it is an
integration layer between the ERP GRV export and the Frappe CGRN
workflow.
28. End-to-End Technical Flow¶
┌─────────────────────┐
│ ERP GRV Excel File │
└──────────┬──────────┘
│
v
┌─────────────────────┐
│ Step 1 │
│ Upload GRV │
│ + BOE + Profile │
└──────────┬──────────┘
│
v
┌─────────────────────┐
│ Step 2 │
│ Mapping Preview │
│ │
│ Header + Line Data │
│ + Sample Values │
└──────────┬──────────┘
│
User can edit
│
v
┌─────────────────────┐
│ Re-validation │
└──────────┬──────────┘
│
v
┌─────────────────────┐
│ Step 3 │
│ Review & Validate │
│ │
│ Lots + Qty + Invoice│
│ + Optional Fields │
└──────────┬──────────┘
│
v
┌─────────────────────┐
│ Business Validation │
│ │
│ BOE │
│ Duplicate │
│ Lot │
│ Quantity │
│ Balance │
│ Cumulative Inward │
└──────────┬──────────┘
│
PASS │ FAIL
│ └──────────────> Error
v
┌─────────────────────┐
│ Step 4 │
│ Confirm & Create │
└──────────┬──────────┘
│
v
┌─────────────────────┐
│ Draft CGRN Created │
└──────────┬──────────┘
│
┌──────────┴──────────┐
│ │
v v
Open CGRN Start New Import
│
v
Fetch LOT Balance Qty
│
v
Submit
│
v
Inventory Updated
29. Design Principles Used¶
Validate Before Create¶
Business validation occurs before the CGRN creation operation.
Preserve Source Relationships¶
The imported data is linked to the relevant Bill of Entry, Part,
Invoice, Lot and quantity information.
Reuse Existing Business Data¶
Existing applicable Lot Numbers are reused instead of unnecessarily
generating new lots.
Prevent Quantity Over-Consumption¶
The source quantity, inward quantity and remaining balance are treated
as related values.
Allow Controlled User Correction¶
Mapping values can be reviewed and edited before final creation.
Protect State-Changing Operations¶
The Create CGRN action is protected against accidental repeated
execution.
Fail Safely¶
Validation failures should stop progression rather than produce
partially valid CGRNs.
Keep UI Enhancements Targeted¶
The final Results-table enhancement adds horizontal accessibility
without changing the existing business logic or result behavior.
30. Final Outcome¶
The enhanced Bulk GRV → CGRN Import Wizard provides a controlled
end-to-end process for converting ERP GRV Excel data into Draft CGRNs.
The completed workflow combines:
Excel Import
+
Dynamic Mapping
+
User Review
+
Re-validation
+
Lot Matching
+
Quantity / Balance Controls
+
Duplicate Protection
+
Draft CGRN Creation
+
Lot Balance Refresh
+
Responsive Results UI
The final system therefore provides both business validation and
operational usability improvements while preserving the existing
CGRN workflow.
The latest UI enhancement specifically addresses wide result tables by
allowing horizontal scrolling when required, without altering the
underlying result data, columns, actions, or backend logic.
31. Concise Project Summary¶
Built and enhanced a Frappe-based Bulk GRV to CGRN Import Wizard that
imports ERP GRV Excel data, maps headers and line items into CGRN
fields, validates Bill of Entry and source data, matches/reuses or
creates Lot Numbers, controls duplicate and quantity conditions,
supports user correction and re-validation, creates a Draft CGRN, and
guides the user through post-creation Lot Balance refresh and
submission. The wizard was further enhanced for usability and responsive
behavior, including controlled navigation, duplicate-click protection,
and horizontal scrolling for the Step 4 Results table when the table
exceeds the available screen width.
Files