Data Migration Enhancement for Asset (AX06US01)

Asset Module Data Migration Business Rules

1. Asset Data Types and Upload Categories

The Asset Module data migration supports the following data types for upload:

1.1 Primary Asset Data Types

• Assets (Physical asset inventory and specifications)
• Facilities (Infrastructure facilities and locations)
• Systems/Networks (Operational systems and network configurations)
• Asset Classifications (Asset categorization and types)
• System Types Management (System type definitions and parameters)

1.2 Supported Data Categories

Data Type	Description	Primary Use Case
Assets	Individual asset records with technical and financial data	Asset inventory management, maintenance planning
Facilities	Infrastructure facilities with operational details	Facility management, capacity planning
Systems	Operational systems with performance metrics	System monitoring, operational management
Networks	Network infrastructure and connectivity	Network management, operational oversight
Asset Classifications	Standardized asset types and categories	Asset categorization, reporting standards
System Types	System type definitions with parameters	System configuration, standardization

2. Asset Upload Process Rules

2.1 Data Selection Process

• Users must select "Assets" from the available data type options
• The system displays asset-specific upload interface with relevant templates
• Asset Classification Template must be uploaded before individual asset records for proper categorization

2.2 File Upload Requirements

• File Format: CSV format only
• Maximum File Size: 50,000 rows per file upload
• File Naming: System auto-fills migration name with filename if not specified by user
• Upload Methods: Drag-and-drop or file browser selection

2.3 Template Download Requirements

Each asset data type provides downloadable CSV templates containing:

• Predefined column headers based on selected asset data type
• Sample data in first few rows for format guidance
• Field descriptions and validation rules

3. Asset Data Field Specifications

3.1 Assets Data Fields

Field Name	Data Type	Required	Validation Rules	Description
Asset Name/Description	String	Yes	Max 255 chars, No special chars except hyphen/underscore	Primary asset identifier
Facility ID	String	Yes	Must exist in Facilities table	Associated facility reference
System/Network ID	String	No	Must exist in Systems/Networks table	Associated system/network reference
Asset Class	String	Yes	Must exist in Asset Classifications	Primary asset category
Asset Type	String	Yes	Must exist in Asset Classifications	Specific asset type
Location	String	No	Max 500 chars	Physical location within facility
Installation Year	Integer	No	Range: 1900-current year+5	Year asset was installed
Serial Number	String	No	Unique per manufacturer	Manufacturer serial number
Useful Life	Integer	No	Range: 1-100 years	Expected useful life in years
Manufacturer	String	No	Max 255 chars	Asset manufacturer name
Status	String	Yes	Active, Inactive, Retired, Under Maintenance	Current operational status
Installation Cost	Decimal	No	Positive number, 2 decimal places	Original installation cost
Replacement Cost	Decimal	No	Positive number, 2 decimal places	Current replacement cost
Depreciation Method	String	No	Straight Line, Declining Balance, Units of Production	Depreciation calculation method
Current Value	Decimal	No	Positive number, 2 decimal places	Current asset value

3.2 Facilities Data Fields

Field Name	Data Type	Required	Validation Rules	Description
Facility Name	String	Yes	Max 255 chars	Facility name
Facility Type	String	Yes	Treatment Plant, Pumping Station, Storage, Distribution	Facility category
Operating Status	String	Yes	Active, Inactive, Under Construction, Decommissioned	Current status
Operational Hours	String	No	24/7, Business Hours, Custom	Operating schedule
Address/Site Label	String	No	Max 500 chars	Physical address
Latitude	Decimal	No	Range: -90 to 90, 6 decimal places	GPS coordinate
Longitude	Decimal	No	Range: -180 to 180, 6 decimal places	GPS coordinate
Service Zone/Division	String	No	Max 255 chars	Service area covered
Facility Manager	String	No	Max 255 chars	Primary contact person
Design Capacity (MLD)	Decimal	No	Positive number	Million liters per day capacity
Commissioned Date	Date	No	YYYY-MM-DD format	Date facility became operational
Design Useful Life	Integer	No	Range: 1-100 years	Expected facility lifespan
Annual O&M Budget	Decimal	No	Positive number	Annual operations & maintenance budget

3.3 Systems Data Fields

Field Name	Data Type	Required	Validation Rules	Description
System Name	String	Yes	Max 255 chars	System name
Facility ID	String	Yes	Must exist in Facilities table	Associated facility
System Type	String	Yes	Must exist in System Types table	System category
Status	String	Yes	Active, Inactive, Under Construction, Maintenance	Operational status
Commissioned/Planned Date	Date	No	YYYY-MM-DD format	Date operational or planned
Backup Status	String	No	Available, Not Available, Partial	Backup system availability
Operational Hours	String	No	24/7, Business Hours, Custom	Operating schedule
Address/Site Label	String	No	Max 500 chars	Physical location
Latitude	Decimal	No	Range: -90 to 90, 6 decimal places	GPS coordinate
Longitude	Decimal	No	Range: -180 to 180, 6 decimal places	GPS coordinate
Description/Purpose	String	No	Max 1000 chars	System description
Design Capacity	Decimal	No	Positive number	System capacity value
Design Capacity Unit	String	No	MLD, m³/h, kW, etc.	Capacity measurement unit
Current Throughput	Decimal	No	Positive number	Current performance value
Current Throughput Unit	String	No	Must match capacity unit type	Performance measurement unit

3.4 Networks Data Fields

Field Name	Data Type	Required	Validation Rules	Description
Network Name	String	Yes	Max 255 chars	Network identifier
Facility ID	String	Yes	Must exist in Facilities table	Associated facility
Network Type	String	Yes	Distribution, Transmission, Collection, Communication	Network category
Operational Status	String	Yes	Active, Inactive, Under Construction, Maintenance	Current status
Network Operator	String	No	Max 255 chars	Operating entity
Backup Status	String	No	Available, Not Available, Partial	Backup availability
Design Capacity	Decimal	No	Positive number	Network capacity
Unit	String	No	m³/h, MLD, Mbps, etc.	Capacity unit
Description/Notes	String	No	Max 1000 chars	Additional information

3.5 Asset Classifications Data Fields

Field Name	Data Type	Required	Validation Rules	Description
Asset Class	String	Yes	Max 100 chars, Unique	Primary asset category
Asset Type	String	Yes	Max 100 chars	Specific asset type within class
Dimension	String	No	Max 100 chars	Measurement dimension for asset type
Unit	String	No	Max 50 chars	Unit of measurement for dimension

3.6 System Types Management Data Fields

Field Name	Data Type	Required	Validation Rules	Description
System Type Name	String	Yes	Max 100 chars, Unique	System type identifier
Description	String	No	Max 500 chars	System type description
Parameter Name	String	Yes	Max 100 chars	Performance parameter name
Unit of Measurement	String	Yes	Max 50 chars	Parameter measurement unit
Parameter Description	String	No	Max 500 chars	Parameter description

4. Data Analysis and Mapping Rules

4.1 AI-Powered Column Mapping

• Automatic Analysis: AI analyzes uploaded files and suggests column mappings
• Confidence Levels: Each mapping assigned High, Medium, or Low confidence
• Manual Override: Users can manually adjust AI suggestions through dropdown selection

4.2 Confidence Level Criteria for Assets

Confidence Level	Criteria	Example
High  ✅	- Exact name match<br>- Exact data type match<br>- Strong pattern similarity	"Asset_ID" → "Asset ID"
Medium  ⚠️	- Partial name match<br>- Compatible data types<br>- Minor variations	"Asset_Name" → "Asset Name/Description"
Low  ❌	- No name similarity<br>- Incompatible data types<br>- Unrecognized content	"Notes" → "Installation Cost"

4.3 Mandatory Field Mapping

• Required fields must be mapped before migration can proceed
• System enforces proper field type mapping (e.g., dates to date fields, numbers to numeric fields)
• Sample data displayed for each mapping to verify accuracy

5. Primary Key Management for Assets

5.1 Required Primary Keys by Dataset

Dataset Type	Primary Key Required	Auto-Generation Rules
Assets	Asset ID	ASSET + sequential number
Facilities	Facility ID	FAC + sequential number
Systems	System ID	SYS + sequential number
Networks	Network ID	NET + sequential number
Asset Classifications	Classification ID	CLASS + sequential number
System Types	System Type ID	SYSTYPE + sequential number

5.2 Primary Key Handling Scenarios

Scenario 1: ONB Has Predefined Prefix & Starting Number

• Action: Auto-fill missing primary keys using predefined settings
• Format: [Prefix][Starting Number + Increment]
• Example: ASSET1001, ASSET1002, ASSET1003...
• User Options: Confirm auto-filled keys or re-upload with custom keys

Scenario 2: ONB Missing Prefix & Starting Number

• Action: Prompt user for prefix and starting number
• Requirements:
  • Prefix: 2-10 alphanumeric characters
  • Starting Number: Positive integer
• Preview: Display generated keys before finalization
• Validation: Ensure no duplicate keys generated

6. Asset Data Validation Rules

6.1 Validation Categories

• Valid Records: Records passing all validation checks
• Warnings: Records with minor issues that don't prevent migration
• Errors: Records with critical issues requiring resolution

6.2 Asset-Specific Validation Rules

Financial Data Validation

• Installation Cost: Must be positive decimal with max 2 decimal places
• Replacement Cost: Must be ≥ Installation Cost (warning if lower)
• Current Value: Must be ≤ Replacement Cost (warning if higher)
• Depreciation: Calculate and validate against Current Value

Date Validation

• Installation Year: Must be between 1900 and current year + 5
• Commissioned Date: Must be valid date in YYYY-MM-DD format
• Future Dates: Warning for dates more than 1 year in future

Reference Data Validation

• Facility ID Reference: Must exist in uploaded or existing Facilities data
• System/Network ID Reference: Must exist in uploaded or existing Systems/Networks data
• Asset Class/Type: Must exist in Asset Classifications data

Technical Validation

• Useful Life: Must be realistic (1-100 years) for asset type
• Serial Numbers: Check for duplicates within same manufacturer
• Status Combinations: Validate status against operational dates

6.3 Cross-Reference Validation

• Asset-Facility Mapping: Verify asset belongs to correct facility
• System-Facility Mapping: Ensure systems are assigned to correct facilities
• Capacity Validation: Warn if total asset capacity exceeds facility capacity

7. Error Handling and Correction

7.1 Asset-Specific Error Types

Error Type	Message Format	Suggested Fix
Missing Asset ID	"Asset ID missing in row [X]. Required for asset identification."	"Provide unique Asset ID or enable auto-generation."
Invalid Facility ID Reference	"Facility ID '[ID]' not found in row [X]."	"Upload Facilities data first or correct facility ID."
Invalid System/Network ID Reference	"System/Network ID '[ID]' not found in row [X]."	"Upload Systems/Networks data first or correct ID."
Invalid Financial Data	"Installation Cost '[Value]' invalid in row [X]. Must be positive number."	"Enter valid positive number with max 2 decimal places."
Date Format Error	"Installation Year '[Value]' invalid in row [X]. Expected format: YYYY."	"Use 4-digit year format (e.g., 2024)."
Duplicate Asset	"Asset ID '[ID]' already exists in row [X]."	"Use unique Asset ID or update existing record."
Invalid Status	"Asset Status '[Status]' invalid in row [X]."	"Use: Active, Inactive, Retired, or Under Maintenance."
Missing Classification	"Asset Class '[Class]' not found in row [X]."	"Upload Asset Classifications data first or correct class name."
Invalid System Type	"System Type '[Type]' not found in row [X]."	"Upload System Types data first or correct system type."

8.2 Auto-Correction Capabilities

• Format Standardization: Auto-format dates, numbers, and text fields
• ID Reference Resolution: Suggest similar facility/system/network IDs for typos
• Default Values: Apply default values for optional fields where appropriate

8. Migration Process Monitoring

8.1 Progress Tracking

• Real-time Progress: Display processed vs. total records
• Success Rate: Track successful migrations and errors
• Processing Time: Monitor migration performance
• Batch Processing: Handle large asset inventories in manageable batches

8.2 Asset Migration Logs

• Timestamp: All activities logged with precise timestamps
• Asset Details: Log asset ID, type, and facility for each operation
• Status Changes: Track asset status changes during migration
• Error Details: Specific error messages with row numbers and asset identifiers

9. Migration Reporting

9.1 Asset Migration Report Metrics

• Total Asset Records: Count of all processed asset records
• Success Rate: Percentage of successfully migrated assets
• Error Rate: Percentage of failed asset migrations
• Processing Time: Total time for asset data migration
• Asset Distribution: Breakdown by facility, asset type, and status

9.2 Asset Error Report Format

Asset ID,Asset Name,Facility ID,System/Network ID,Asset Type,Row Number,Error Description
ASSET001,Water Pump,FAC001,SYS001,Pump,15,Installation Cost must be positive number
,Backup Generator,FAC002,,Generator,23,Asset ID is required and cannot be empty
ASSET003,Control Valve,FAC999,,Valve,31,Facility ID 'FAC999' not found
ASSET004,Flow Meter,FAC001,NET999,Meter,42,System/Network ID 'NET999' not found

10. Security and Access Control

10.1 Asset Data Security Rules

• Multi-factor Authentication: Required for asset migration access
• Role-based Permissions: Asset managers, facility managers, and system administrators
• Audit Trail: Complete log of all asset data modifications
• Data Encryption: Encrypt sensitive asset financial data

10.2 Asset Data Integrity

• Referential Integrity: Maintain relationships between assets, facilities, and systems
• Version Control: Track asset data changes over time
• Backup Requirements: Automatic backup before migration execution
• Rollback Capability: Ability to reverse migration if critical errors detected

11. Post-Migration Validation

11.1 Asset Data Verification

• Count Verification: Confirm all valid records were migrated
• Relationship Validation: Verify asset-facility-system/network relationships
• Financial Data Integrity: Validate cost calculations and depreciation
• Status Consistency: Ensure asset statuses align with operational dates
• ID Reference Integrity: Verify all facility and system/network ID references are valid

11.2 Migration Success Criteria

• Data Completeness: All required fields populated correctly
• Reference Integrity: All cross-references resolved successfully
• Validation Compliance: All validation rules satisfied
• Performance Standards: Migration completed within acceptable timeframe

12. Enhanced Data Mapping & Update Rules

12.1 Data Upload Enhancement

• Users can now choose between:
  • Upload New Data – traditional full-data migration
  • Update Existing Data – partial field-level updates on existing records
• Unique Identifier Requirement:
  • Updates require a valid system identifier (e.g., Asset ID, Facility ID, System ID)
  • If not provided, the row is skipped and flagged in logs
• Field Selection Rules:
  • Users can select specific fields to update (from a system-curated list of updatable fields)
  • Only selected fields are included in the downloadable CSV template
  • System enforces update validity (e.g., correct data type, allowed values)
• CSV Template Controls:
  • Data update templates are mandatory to download before upload
  • Upload is disabled until the downloaded template is used (template check enforced)
  • File format: CSV only

---

12.2 System Data Mapping (Conditional Screen)

• If mandatory system-related fields are not mapped during field mapping, users are shown a System Data Mapping screen, similar to plan mapping for consumers

Trigger Conditions:

• System detects unmapped critical system fields, such as:
  • Assets: Asset Class, Asset Type, System/Network
  • Systems: System Type, Facility
  • Networks: Network Type, Facility
  • Facilities: Facility Type

Key Features:

• Tab-based UI: Each unmapped required field is shown as a tab
• Record List: Shows only records missing the corresponding system field
• Search & Filter:
  • Search across all columns
  • Multiselect filters per field
• Selection Options:
  • Users can select rows individually, in bulk, or "Select All"
• Bulk Assignment:
  • Assign system data (e.g., a System Type or Asset Class) to all selected rows
• Inline Tagging:
  • Once assigned, values are visibly tagged to the respective rows
• Progress Tracker:
  • Shows % of system field mappings completed
• User can only proceed to the next step after all required mappings are completed

---

12.3 Migration History & Rollback Enhancements

• History Logging:
  • Every upload or update (new or partial) is logged with metadata:
    • Timestamp, record count, user, method (new/upload)
    • Modified fields (for update)
• Rollback Capability:
  • Admins can rollback a data update/upload within 3 days
  • Rollback will:
    • Restore old values for updated records
    • Delete new records in case of upload
    • Remove all associated transaction logs tied to affected records