Compliance Verification and Validation

Author(s)

• Abhishak Kumar Roy
• Reshmi Karan

Last Updated Date

2024-12-24

---

SRS References

---

Version History

Version	Date	Changes	Author
1.0	2024-12-06	Initial draft with workflow and schema	Abhishak Kumar Roy
2.0	2024-12-24	Change schema and apis	Reshmi Karan
3.0	2025-01-02	UI design and Workflow	Ribhu Gautam

---

Feature Overview

Objective:
To verify the compliance documents (wage register, EPF ECR, and ESIC) submitted monthly by vendors or the IR department on behalf of vendors to ensure adherence to government norms and CESC policies. The feature ensures vendors pay their employees as per legal requirements.

Scope:
This feature includes:

• Document upload and validation.
• Automated compliance verification against government and organizational norms.
• Notifications for non-compliance and corrective actions.
• Secure storage and audit-ready records.

Dependencies:

• Integration with vendor and employee master data.
• Document storage for uploaded files.
• Notification system for compliance alerts.

---

Requirements

1. Vendors or IR departments must upload wage register (Excel), EPF ECR (PDF), and ESIC records monthly.
2. Validate uploaded documents against:
   • Minimum wage requirements.
   • EPF and ESIC contributions as per government norms.
3. Notify stakeholders of compliance results and required corrective actions.
4. Generate monthly compliance reports with detailed analysis for vendors and employees.
5. Enable IR Managers to reset and reprocess non-compliant submissions.
6. Ensure secure storage and retrieval for compliance audits.
7. Validate data against:
   • Minimum Wage: Basic Salary = Minimum Wage × Days Worked.
   • EPF Contributions: EPF Amount = 12% of Basic Salary.

---

Design Specifications

• UI/UX Design:
  The interface includes:

  • Vendor dashboard for uploading documents and viewing compliance status.
  • Admin tools for IR Managers to reset submissions and track compliance.
  • Notifications for compliance results.

• Data Models:
  The following C# classes represent the database schema:

public record MonthlyComplianceMaster
{
    public Guid ComplianceId { get; set; }
    public Guid VendorId { get; set; }
    public Guid DivisionId { get; set; }
    public string? VendorName { get; set; }
    public string? DivisionName { get; set; }
    public int Month { get; set; }
    public int Year { get; set; }
    public Guid UploadId { get; set; }
    public ComplianceStatus ComplianceStatus { get; set; } // Active, Failed, Pending
    public DateTime LogDts { get; set; }
    public DateTime InsertedOn { get; set; }
    public MonthlyComplianceMaster()
    {

    }

public record MonthlyComplianceUploadRequest
{
    public required Guid VendorId { get; set; }
    public required Guid DivisionId { get; set; }
    public required string VendorName { get; set; }
    public required string DivisionName { get; set; }
    public required int Month { get; set; }
    public required int Year { get; set; }
}

public class MonthlyComplianceUploadResponse
{
    public Guid UploadId { get; set; }
    public string Status { get; set; } // Success, Failed
    public string Message { get; set; }
}

public class ComplianceSummaryResponse
{
    public Guid VendorId { get; set; }
    public string VendorName { get; set; }
    public string Month { get; set; }
    public string Year { get; set; }
    public string ComplianceStatus { get; set; } // Compliant, Non-Compliant
    public DateTime LastUpdated { get; set; }
    [JsonIgnore]
    public int TotalNumber { get; set; }
}

public record ComplianceDetailResponse
{
    public Guid TransactionId { get; set; }
    public Guid VendorId { get; set; }
    public string? VendorName { get; set; }
    public Guid DivisionId { get; set; }
    public string? DivisionName { get; set; }
    public ComplianceStatus ComplianceStatus { get; set; }
    public int Month { get; set; }
    public int Year { get; set; }
    public List<MonthlyEmployeeComplianceAnalyzedInfo> EmployeeComplianceDetails { get; set; } = [];
}

public record MonthlyEmployeeComplianceAnalyzedInfo
{
    public Guid TransactionId { get; set; }
    public Guid VendorId { get; set; }
    public Guid DivisionId { get; set; }
    public int Month { get; set; }
    public int Year { get; set; }
    public decimal NumberOfDaysWorked { get; set; }
    public Guid EmployeeId { get; set; }
    public string? EmployeeName { get; set; }
    public decimal MinimumWage { get; set; }
    public decimal BasicWageAmount { get; set; }
    public bool MinWageValidationStatus { get; set; } = false;
    public bool EpfValidationStatus { get; set; }
    public decimal EpfAmount { get; set; }
    public bool EsicValidationStatus { get; set; } = false;
    public bool FinalValidationStatus { get; set; } = false;
    public string? Message { get; set; }
}


 public record ComplianceFilter : SortingPaginatedData
 {
     public required Guid DivisionId { get; set; }
     public List<Guid>? VendorId { get; set; }
     public required int Month { get; set; }
     public required int Year { get; set; }
     public int PageNumber { get; set; }
     public int RowsPerPage { get; set; }
     public bool IsExcel { get; init; }

     public override IEnumerable<string> AllowedSortByFields => new[]
    {
         "VendorName",
         "ComplianceStatus",
         "DivisionName",
         "Month",
         "Year"
     };
 }
  public abstract record SortingPaginatedData
  {
    public abstract IEnumerable<string> AllowedSortByFields { get; }
    public string? SortBy { get; set; }
    [AllowedValues("asc", "desc")]
    public string SortingOrder { get; set; } = "asc";
    public bool IsValidSortBy()
    {
        return SortBy == null || AllowedSortByFields.Contains(SortBy, StringComparer.OrdinalIgnoreCase);
    }
  }
  public class ServerPaginatedData<T>
  {
    public List<T>? Data { get; set; }
    public long Totalnumber { get; set; }
  }

 public record ComplianceResetRequest
 {
     public Guid VendorId { get; set; }
     public Guid DivisionId { get; set; }
     public int Month { get; set; }
     public int Year { get; set; }
 }

public class ComplianceResetResponse
{
    public string Status { get; set; } // Success, Failed
    public string Message { get; set; }
}
public enum ComplianceStatus
{
    None = 0,
    Compliant = 1,
    NonCompliant = 2,
    Reset = 3
}
public record FileWithType
{
    public required IFormFile File { get; set; }
    public FileType FileType { get; set; }
}
public enum FileType
{
    Unknown,
    Aadhar,
    GST,
    PAN,
    ECR,
    Wage
}

---

• API Interfaces:
  Define the following API endpoints for screens and workflows:

  Endpoint	Method	Parameters	Response	Response Status Codes
  /api/compliance/upload	POST	MonthlyComplianceUploadRequest as a string , List<FileWithType>	MonthlyComplianceUploadResponse	201, 400, 500
  /api/compliance/status/{id}	GET	id (Upload ID)	ComplianceSummaryResponse	200, 404, 500
  /api/compliance/get	GET	complianceId (Transaction ID)	ComplianceDetailResponse	200, 404, 500
  /api/compliance/reset-status	PUT	ComplianceResetRequest	ComplianceResetResponse	200, 400, 500
  /api/compliance/get/all	POST	ComplianceFilter	ServerPaginatedData<ComplianceSummaryResponse>	200, 404, 500

---

This structure provides detailed data models, ensures API responses are paginated for large datasets, and includes filters for efficient querying. Each API supports its respective screen or workflow.

• Third-Party Integrations:

  • Blob storage for file uploads.
  • Notification services for email alerts.

• Workflow:
  

• Database:
  

---

Development Tasks & Estimates

No	Task Name	Estimate (Hours)	Dependencies	Notes
1	UI/UX design for file upload	12 hours	None	-
2	API development for file handling	20 hours	Database schema	-
3	Scheduler for file processing	15 hours	File validation logic	-
4	Employee-level analysis logic	18 hours	Minimum wage data	-
5	Notification system integration	10 hours	Notification service setup	-
6	Testing and QA	18 hours	All completed features	-
7	Total	93 hours	-	-

---

Testing & Quality Assurance

• Unit Tests:

  • Validate file format and size checks.
  • Test database constraints for compliance records.

• Integration Tests:

  • Verify interaction between UI, backend, and file storage.

• Acceptance Criteria:

  • Notifications sent promptly for non-compliance issues.
  • Employee-level analysis reports are accurate and complete.

• Testing Tools:

  • Postman for API testing.

---

Deployment Considerations

• Configuration Changes:

  CREATE TABLE tblMonthlyComplainceMaster (
    ComplianceId UUID PRIMARY KEY,
    VendorId UUID NOT NULL,
    DivisionId UUID NOT NULL,
    VendorName text NOT NULL,
    DivisionName text NOT NULL,
    Month int NOT NULL,
    Year int NOT NULL,
    UploadId UUID NOT NULL,
    ComplianceStatus TEXT,
    InsertedOn TIMESTAMPTZ NOT NULL,
    LogDts TIMESTAMPTZ,
    CONSTRAINT unique_vendor_division_month_year UNIQUE (VendorId, DivisionId, Month, Year)
  );
  
  CREATE TABLE tblMonthlyComplianceUploadDetails (
    UploadId UUID PRIMARY KEY,
    VendorId UUID NOT NULL,
    DivisionId UUID NOT NULL,
    Month int NOT NULL,
    Year int NOT NULL,
    SubmissionDate TIMESTAMPTZ NOT NULL,
    ProcessedOn TIMESTAMPTZ,
    WageRegisterFile Jsonb, -- FilePath
    EpfEcrFile Jsonb,       -- FilePath
    EsicFile Jsonb,         -- FilePath
    ProcessStatus TEXT,
    ComplianceStatus TEXT,
    ErrorDetails TEXT,
    InsertedBy TEXT NOT NULL,
    InsertedOn TIMESTAMPTZ NOT NULL
  );
  CREATE INDEX idx_monthly_compliance_vendor_division_date
  ON tblMonthlyComplianceUploadDetails (VendorId, DivisionId, Month, Year, InsertedOn DESC);
  
  CREATE TABLE tblMonthComplainceAnalyzedReport (
    TransactionId UUID,
    VendorId UUID NOT NULL,
    DivisionId UUID NOT NULL,
    EmployeeId UUID,
    EmployeeName text,
    Month int NOT NULL,
    Year int NOT NULL,
    MinimumWage NUMERIC NOT NULL,
    NumberOfDaysWorked DECIMAL(10, 2) NOT NULL,
    BasicWageAmount NUMERIC NOT NULL,
    MinWageValidationStatus BOOLEAN,
    EpfValidationStatus BOOLEAN,
    EpfAmount NUMERIC NOT NULL,
    EsicValidationStatus BOOLEAN,
    FinalValidationStatus BOOLEAN,
    Message TEXT,
    PRIMARY KEY (TransactionId, EmployeeId),
   CONSTRAINT fk_monthly_compliance_master_transaction
    FOREIGN KEY (TransactionId) REFERENCES tblMonthlyComplainceMaster (ComplianceId)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
  );

• Rollout Plan:

  • Gradual rollout to test users followed by full deployment.

---

Risks & Mitigations

Risk	Impact	Likelihood	Mitigation Strategy
High upload volume delays	High	Medium	Optimize scheduler and processing.
Incorrect file formats	Medium	Medium	Validate file formats during upload.
Data inconsistencies	High	Low	Enforce strict database constraints.

---

Review & Approval

• Reviewer:
  Abhishak Kumar Roy

• Approval Date:
  2024-12-31

---

Notes