UMRN Master

The UMRN (Unique Mandate Reference Number) Master is a mapping table that links NACH mandate references to loan IDs. It's used when payment files from Cashfree (GB and UGRO) have blank Link ID values - the system resolves the loan ID through the UMRN/sub-reference lookup.

Why It's Needed

Cashfree NACH payment files have two types of rows:

Row Type	Link ID	Order Note	How Loan ID is Resolved
Payment Link	GB00682_Apr26_Name	payment_link_198475554	Parsed from Link ID prefix
NACH Mandate	(blank)	charge for paymentId : 442195198 & subReferenceId : 5226394	UMRN Master lookup

For NACH mandate rows, the subReferenceId is extracted from the Order Note and looked up in the UMRN Master table to find the corresponding loan ID.

Upload

1. Go to File Upload > UMRN Master tab
2. Click Upload UMRN Master
3. Choose Load Mode:
   • Truncate & Load - replaces all existing data with the new file
   • Append to existing - adds new rows alongside existing data
4. Select the xlsx or csv file
5. Click Upload

Expected File Format

Column	Description	Example
LAN ID	Lender Account Number	TR10010
Loan No	Fineract loan external ID	LL0004
NBFC	Lender name	Liquiloans
Name	Borrower name	Sabanna Gowda BM
Phone 1	Contact number	9663222968
Sub Ref ID	UMRN / sub-reference ID	3285659

The Sub Ref ID column is the key field - it maps to the subReferenceId extracted from Cashfree NACH Order Notes.

Database Table

RAW.RAW_UMRN_MASTER

Column	Type	Description
lan_id	text	Lender account number
loan_id	text	Fineract loan external ID
nbfc	text	Lender name
name	text	Borrower name
phone	text	Contact number
sub_reference_id	text	UMRN / sub-reference (indexed)

Lookup Flow

During CSV processing (csv_processor.py):

1. Extract sub-reference - regex parses subReferenceId : {number} from the note column
2. UMRN lookup - queries RAW_UMRN_MASTER where sub_reference_id matches
3. Fill loan_id - if found, updates the blank loan_id with the UMRN master's loan_id
4. Flag if unresolved - rows still without a loan_id after lookup are flagged as bad records

Search

The UMRN Master tab includes a search box that filters by Loan ID, LAN ID, UMRN, or Name across all records.

API Endpoints

Method	Path	Description
POST	/api/umrn-master/upload	Upload file (form: file, load_mode)
GET	/api/umrn-master/stats	Row count + last upload timestamp
GET	/api/umrn-master	Paginated list with search