Support #97
Updated by Rajesh Gotur about 2 months ago
# 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:
``` text
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:
``` text
┌─────────────────────────────────────────────┐
│ 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:
1. Bill of Entry
2. Mapping Profile
3. GRV `.xlsx` file
The **Preview Mapping** action becomes available only when the required
input is available.
### Flow
``` text
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.
``` text
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:
``` text
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:
``` text
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:
``` text
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:
``` text
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:
``` text
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
``` text
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:
``` text
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:
``` text
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.
``` text
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:
``` text
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.
``` text
┌─────────────────┐
│ 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**.
``` text
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:
``` text
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:
``` text
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:
``` text
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:
1. Open the CGRN
2. Review the imported information
3. Review linked lot numbers
4. Refresh lot balances
5. Submit the CGRN
The documented operational sequence is:
``` text
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.
``` text
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:
``` text
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:
``` text
| 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.
``` text
┌───────────────────────────────────────────────┐
│ Results Table │
│ │
│ | C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | │
│ │
│ ←──────────── horizontal scroll ───────────→ │
└───────────────────────────────────────────────┘
```
### Expected behavior
``` text
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.
``` text
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 `.xlsx` GRV 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
``` text
┌─────────────────────┐
│ 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:
``` text
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.**