Export & Import Special Loans

Navigation Path

Export & Import → Special Loan

URL

/export-import/special-loan-import-and-export

Description

• Manage special-purpose loans and advances via import/export
• Handle uniform loans, tool kits, equipment advances
• Process non-salary deduction loans (one-time or multi-installment)
• Bulk import special loan records
• Export existing special loans for modification
• Track loans linked to specific salary heads
• Support designation-based and unit-based loan management
• Maintain loan recovery schedules

1. Page Overview

What are Special Loans?

Special Loans are loans or advances given to employees for specific purposes beyond regular loan/advance categories. These are typically linked to particular salary heads and may have unique recovery patterns.

Common Special Loan Types:

• Uniform Loans: Advance for purchasing company uniforms
• Tool Kit Advance: For purchasing work-related tools/equipment
• Safety Equipment Loan: For safety gear and protective equipment
• ID Card Charges: Recovery for ID card issuance
• Training Material Advance: For training-related materials
• Mobile/Laptop Advance: For work devices
• Relocation Advance: For employee relocation expenses
• Medical Equipment: For specific medical devices
• License Fee Recovery: For professional license renewals

Key Characteristics:

• Linked to specific salary heads
• Can be one-time deduction or installment-based
• May be designation-specific (e.g., uniforms for security staff)
• Unit-specific applicability
• Recoverable from salary
• Non-interest bearing typically

Special Loans vs Regular Loans

Special Loans (This Page):

• Purpose-specific loans
• Often linked to job requirements
• May be mandatory (e.g., uniform)
• Typically smaller amounts
• Shorter recovery periods
• Linked to specific salary heads

Regular Loans (Loan/Advance Page):

• General purpose loans
• Personal financial assistance
• Voluntary by employee
• Larger amounts possible
• Longer EMI schedules
• Standard loan categories (Personal, Home, Vehicle, etc.)

2. Top Action Link

Export Example In Excel

Location: Top-center of the page (purple/blue text)

Function:

• Provides pre-formatted Excel template
• Shows required columns and data structure
• Includes built-in instructions and validation rules
• Contains sample special loan records
• Guides proper data format for import

Template Contents:

• Pre-formatted columns for all special loan fields
• Field-level instructions and descriptions
• Data validation rules embedded in Excel
• Sample loan records showing correct format
• Required vs optional field indicators
• Format specifications for dates, amounts, codes
• Notes on loan types and recovery methods

Best Practice: Always download this template before creating special loan import files

3. Filter Section

Unit Filter

Features:

• Search units by code or name
• Select multiple units simultaneously
• Clear all selections (X button)
• Upload icon for bulk selection
• Display Format: [Unit Code] | [Unit Name]

Examples from screenshot:

• UNT00007683 | TATA TELE SERVICES, NOIDA
• UNTPFS00008321 | BLS INTERNATIONAL SERVICES LTD.
• UNT004989 | GOLDEN SPARROW KESSEL MALL, KURUKSHETRA
• UNTPFS00008316 | M/S IBS SOFTWARE, SHANTINKETAN, WHITEFIELD, BANGALORE

Purpose:

• Filter special loans by location
• Export unit-specific loan records
• View loans for particular branches
• Manage uniform/equipment loans by unit

Designation Filter

Features:

• Search designations
• Select multiple designations simultaneously
• Clear all selections (X button)
• Upload icon for bulk selection
• Shows "NULL" if no designation assigned

Purpose:

• Filter loans by job role
• Essential for designation-specific loans (e.g., uniform loans for security staff)
• Export designation-wise loan data
• Manage role-based special advances

Common Use Cases:

• View all uniform loans for "Security Guard" designation
• Export tool kit advances for "Technician" roles
• Manage equipment loans for field staff

4. Action Buttons

Search and Reset

Search Button Behavior:

• Fetches special loan records based on filters
• Can search without filters to view all records
• Displays results in table format below
• Shows loan details, recovery status
• Enables export and further actions

Reset Button Behavior:

• Clears unit selection
• Clears designation selection
• Hides results table
• Returns page to initial state
• Useful for starting new search

5. Search Results Table

Table Display

After clicking "Search", a comprehensive table displays with special loan details.

Main Columns:

Table Features

• Bulk Selection: Checkbox in header to select all visible records
• Individual Selection: Checkbox for each loan record
• Export Button: Top-right corner to export selected/all loans
• Column Filters: Filter icon in each column header
• Column Sorting: Click header to sort ascending/descending
• Pagination: Navigate through multiple pages of records
• Horizontal Scroll: Scroll to view all columns
• Row Selection: Select specific loans for export

Table Data Examples (from screenshot)

Sample Records:

1. Uniform Loan for AC Operator

   • Loan Code: LOANT175083140162123...
   • Unit: DHARAMPAL SATYAPAL S...
   • Designation: AC OPERATOR
   • Loan Type: UNIFORM

2. Uniform Loan for Technician

   • Loan Code: LOANP175083140169619...
   • Unit: VERTIV ENERGY PRIVATE...
   • Designation: ASST. TECHNICIAN
   • Loan Type: UNIFORM

3. Uniform Loan for Building Manager

   • Loan Code: LOANM175083140171173...
   • Unit: JONES LANG LASALLE PR...
   • Designation: BUILDING MANAGER
   • Loan Type: UNIFORM

4. Uniform Loan for Care Taker

   • Loan Code: LOANC175083140172954...
   • Unit: AMRITA INTERNATIONAL...
   • Designation: BUS MAIDS
   • Loan Type: UNIFORM

Export Button

Location: Top-right corner of the page

Function:

• Export selected loan records to Excel
• Or export all filtered results
• Download format-ready for modification and re-import

Export Contains:

• All loan details from the table
• Complete recovery schedule
• Employee information
• Current balance and status
• All columns including hidden ones

No Data Message

If no records match the criteria:

"No special loan records found matching the selected criteria."

Or if no search performed yet:

"Please select filters and click Search to view special loans."

6. Import Sections

Section 1: Import New Special Loans

Label: "Select (Special Loans Excel) File:"

Purpose: Import brand new special loan records that don't exist in the system

Upload Area:

• Drag-and-drop zone with dashed border
• Upload icon (cloud with arrow)
• Text: "Upload or drag and drop (.xlsx, .xls)"
• Accepts Excel files only

Import Button:

• Color: Purple/Blue
• Label: "Import"
• Action: Processes and creates new special loan records

Use Cases:

• Importing uniform loans for new batch of employees
• Creating tool kit advances for new joiners
• Bulk creation of designation-specific loans
• Setting up seasonal equipment advances
• Initial loan setup for new unit

Section 2: Import Modified Special Loans

Label: "Select (Special Loans Excel) Exported Sheet File:"

Purpose: Import modifications to existing special loan records that were previously exported

Upload Area:

• Drag-and-drop zone with dashed border
• Upload icon (cloud with arrow)
• Text: "Upload or drag and drop (.xlsx, .xls)"
• Accepts Excel files only

Import Button:

• Color: Purple/Blue
• Label: "Import"
• Action: Updates existing special loan records

Use Cases:

• Modifying recovery schedules
• Adjusting installment amounts
• Updating loan status
• Correcting loan amounts
• Changing recovery modes
• Stopping or resuming recoveries

7. Workflow: Export Special Loans

Step-by-Step Process

1. Apply Filters (Optional)

   • Select specific units if needed
   • Select designations for targeted export
   • Or leave blank to search all

2. Search Records

   • Click "Search" button
   • System displays table with special loan records
   • Review the loan list

3. Select Loans to Export

   • Use checkboxes to select specific loans
   • Or use header checkbox to select all
   • Or export all without selection (depending on system)

4. Export Data

   • Click "Export" button at top-right
   • System generates Excel file
   • File downloads automatically

5. Review Exported File

   • Open downloaded Excel file
   • Verify all loan records and details
   • Check recovery schedules
   • Review balance amounts

Output: Excel file containing complete special loan data ready for modification

8. Workflow: Import New Special Loans

Step-by-Step Process

1. Download Template

   • Click "Export Example In Excel" link
   • Download the sample template
   • Review structure and required fields

2. Prepare Special Loan Data

   • Open downloaded template
   • Fill employee details:
     • Employee code (must exist in system)
     • Employee name
     • Unit
     • Designation
   • Enter loan information:
     • Unique loan code (system may auto-generate)
     • Loan type (Uniform, Tool Kit, etc.)
     • Loan amount
     • Salary head to link with
   • Configure recovery:
     • Recovery mode (One-time / Installment)
     • If installment: number of installments
     • Start date for recovery
   • Add remarks if needed

3. Validate Data

   • Ensure all required fields are filled
   • Verify employee codes exist
   • Check loan amounts are valid
   • Confirm recovery mode and installments match
   • Validate date formats
   • Verify salary heads exist

4. Upload File

   • Go to "Select (Special Loans Excel) File" section
   • Click upload area or drag-and-drop file
   • File name appears after selection

5. Import Loans

   • Click "Import" button
   • System validates data
   • Creates new special loan records
   • Links to salary heads
   • Shows success/error messages

6. Verify Import

   • Search for newly imported loans
   • Check all loan details are correct
   • Verify recovery schedules
   • Confirm salary head linkage
   • Test with sample salary calculation if needed

9. Workflow: Modify and Re-import Special Loans

Step-by-Step Process

1. Export Existing Loans

   • Filter by unit/designation if needed
   • Click Search
   • Select loans to modify
   • Click Export button
   • Download Excel file

2. Modify Loan Data

   • Open exported Excel file
   • DO NOT change Loan Code (identifier for matching)
   • Modify as needed:
     • Adjust loan amounts
     • Change installment numbers
     • Update recovery mode
     • Modify installment amounts
     • Change status (if needed)
     • Update remarks
     • Adjust balance amounts
   • Common modifications:
     • Stop recovery (change status)
     • Reduce installment amount
     • Extend recovery period
     • Write off balance

3. Save Modified File

   • Save Excel with changes
   • Keep original structure
   • Don't add/remove columns

4. Upload Modified File

   • Go to "Select (Special Loans Excel) Exported Sheet File" section
   • Upload the modified Excel file
   • File name appears

5. Import Updates

   • Click "Import" button in second section
   • System matches loans by Loan Code
   • Updates existing records
   • Shows success/error messages

6. Verify Updates

   • Search for updated loans
   • Verify changes applied correctly
   • Check recovery schedules updated
   • Confirm balance recalculated
   • Test in salary processing if major changes

Important: Always use exported file for modifications to ensure proper loan matching

10. Special Loan Data Structure

Employee Information

Loan Details

Recovery Configuration

Note: If Recovery Mode = "One-time", the full amount is deducted in one salary cycle

Status Tracking

14. Validation Rules

File Validation

✓ File Format

• Must be Excel (.xlsx or .xls)
• Not corrupted or password-protected
• File size within limits
• Proper encoding

✓ Template Structure

• All required columns present
• Headers match template
• No extra required columns
• Data starts from correct row

Data Validation

✓ Employee Details

• Employee code must exist in system
• Employee must be active
• Unit must match employee's current unit
• Designation should match employee record

✓ Loan Details

• Loan code must be unique (for new import)
• Loan amount must be greater than 0
• Loan amount should be reasonable (not negative, not excessively large)
• Loan type should be from predefined list
• Loan date cannot be future date

✓ Recovery Configuration

• Recovery mode must be "One-time" or "Installment"
• If Installment mode, number of installments required
• Number of installments must be greater than 0
• Start date must be valid month (format: MM/YYYY or DD/MM/YYYY)
• Start date cannot be before loan date
• Installment amount should match (Loan Amount / No. of Installments)

✓ Salary Head

• Head name must exist in salary head master
• Head must be of type "Deduction"
• Head must be active

✓ Dates and Numbers

• Dates in correct format
• Amounts are numeric, non-negative
• No of installments is positive integer
• Start date is valid calendar date

Business Logic Validation

• Cannot create duplicate loan codes
• Employee should not have excessive special loans (system may have limits)
• Total monthly deductions should not exceed employee's net salary
• Recovery start date should be in future or current month
• If modifying loan, balance amount should be logical (not exceed original loan)

15. Success and Error Messages

Success Messages

New Loan Import:

✅ "Special loans imported successfully. X loan(s) created for Y employee(s)."

Loan Update:

✅ "Special loan records updated successfully. X loan(s) modified."

Export:

✅ "Special loan data exported successfully. Downloading file..."

Search Results:

ℹ️ "Found X special loan record(s) matching your criteria."

Error Messages

No File Selected:

❌ "Please select a file to import."

Invalid File Format:

❌ "Invalid file format. Please upload Excel file (.xlsx or .xls)."

Template Mismatch:

❌ "File structure doesn't match template. Please download and use the correct template."

Employee Not Found:

❌ "Employee code 'EMP123' not found in system. Please verify employee code in row X."

Duplicate Loan Code:

❌ "Loan code 'LOAN001' already exists. Use unique loan codes or use exported file to update."

Missing Required Fields:

❌ "Required fields missing in row X: Employee Code, Loan Amount, Recovery Mode."

Invalid Recovery Mode:

❌ "Invalid recovery mode in row X. Allowed values: 'One-time' or 'Installment'."

Missing Installments:

❌ "Recovery mode is 'Installment' but number of installments is missing in row X."

Invalid Salary Head:

❌ "Salary head 'Uniform Ded' not found in row X. Please use valid deduction head."

Invalid Date Format:

❌ "Invalid date format in row X. Expected format: DD/MM/YYYY."

Zero or Negative Amount:

❌ "Loan amount must be greater than zero in row X. Found: -500."

Partial Import:

⚠️ "Partial import completed. X loan(s) created, Y loan(s) failed. Check error details below."

16. Common Use Cases

Case 1: Uniform Loan for New Security Guards

Scenario: 50 new security guards joined, each needs uniform costing ₹2,000

Steps:

1. Download template
2. Fill 50 rows with:
   • Employee codes of guards
   • Loan Type: UNIFORM
   • Loan Amount: 2000
   • Head Name: Uniform Deduction
   • Recovery Mode: Installment
   • No of Installments: 10
   • Start Date: Next month
3. Import via "New Special Loans" section
4. Verify all 50 loans created
5. Recovery will auto-start next month at ₹200/month per guard

Result: Uniform loans set up, automatic recovery for 10 months

Case 2: Tool Kit Advance for Technicians

Scenario: 20 technicians need tool kits, cost varies ₹3,000-₹5,000 per person

Steps:

1. Prepare list with employee codes and individual amounts
2. Download template
3. Fill data:
   • Loan Type: TOOL KIT
   • Loan Amount: (individual amounts)
   • Head Name: Tool Kit Recovery
   • Recovery Mode: Installment
   • No of Installments: 15
   • Start Date: Next month
4. Import file
5. Verify loans created with different amounts
6. Recovery starts at different EMI amounts per person

Result: Customized tool kit advances with individual recovery schedules

Case 3: Bulk ID Card Charges

Scenario: Charge all 500 new joiners ₹100 for ID card

Steps:

1. Export employee list for new joiners
2. Download special loan template
3. Fill data:
   • Loan Type: ID CARD
   • Loan Amount: 100
   • Head Name: ID Card Charges
   • Recovery Mode: One-time
   • Start Date: Current month
4. Import 500 records
5. Full amount deducted in current month's salary

Result: One-time ₹100 deduction for all 500 employees

Case 4: Adjusting Loan Installments

Scenario: Employee's financial situation changed, need to reduce EMI

Steps:

1. Search for employee's special loan
2. Export the loan record
3. Modify:
   • Increase number of installments (e.g., 10 to 15)
   • Installment amount will reduce automatically (₹300 to ₹200)
4. Import via "Exported Sheet" section
5. Verify updated schedule

Result: Extended recovery period with lower monthly burden

Case 5: Stopping Recovery

Scenario: Employee resigned/terminated, stop all special loan recoveries

Steps:

1. Search loans by employee
2. Export employee's special loans
3. Change Status: "Stopped"
4. Add Remarks: "Employee resigned - recovery stopped"
5. Re-import file
6. Recoveries will not happen in next salary

Result: Loan recovery halted (balance may need write-off or recovery via F&F)

17. Best Practices

Before Import

1. Download Fresh Template

   • Always use latest template from system
   • Don't reuse old templates
   • Review template notes and instructions

2. Verify Employee Data

   • Confirm employee codes are correct
   • Check employees are active
   • Verify unit and designation match

3. Plan Recovery Schedule

   • Consider employee salary
   • Ensure EMI amount is not too high (typically under 10% of net salary)
   • Plan start date appropriately
   • Avoid multiple large deductions in same month

4. Validate Amounts

   • Check loan amounts are reasonable
   • Verify installment calculations
   • Ensure consistency across similar loan types

5. Test with Small Batch

   • Import 2-3 test records first
   • Verify import works correctly
   • Check salary processing integration
   • Then proceed with bulk import

During Import

1. Use Correct Section

   • New loans → "Special Loans Excel" section
   • Updates → "Exported Sheet" section
   • Don't mix up sections

2. Maintain Data Consistency

   • Use standard loan type names
   • Follow consistent naming conventions
   • Keep date formats uniform
   • Use standard salary head names

3. Complete All Required Fields

   • Don't leave required fields blank
   • Provide defaults where applicable
   • Add meaningful remarks

4. Logical Recovery Configuration

   • Match recovery mode with loan type
   • Set reasonable installment numbers
   • Choose appropriate start dates
   • Consider salary processing schedule

After Import

1. Immediate Verification

   • Search for imported loans
   • Check all loan details
   • Verify recovery schedules
   • Confirm salary head linkage

2. Test Salary Processing

   • Run test salary for sample employees
   • Verify deductions appear correctly
   • Check installment amounts match
   • Confirm net salary calculation

3. Communication

   • Inform employees about loan deductions
   • Provide recovery schedule details
   • Explain salary slip deduction heads
   • Share contact for queries

4. Documentation

   • Maintain import file records
   • Document loan purposes
   • Keep approval records
   • Archive for audit trail

5. Regular Monitoring

   • Review active special loans periodically
   • Check for completed loans
   • Identify stuck/failed recoveries
   • Clean up old records

End of Documentation