Skip to main content

Reconciling Settlements

Overview​

This documentation details the development of the Settlement Reconciliation feature for CampX, focusing solely on reconciling settlements between CampX and BillDesk. The primary objective is to enhance payment transparency.

Feature Description​

Settlement Reconciliation Module​

  • Purpose: To accurately match the payment transactions recorded in CampX with the settlement data received from BillDesk.
  • Key Functions:
    • Data Retrieval: Securely import settlement data from BillDesk using their Settlement Info API v1.2.
    • Data Matching: Match each CampX transaction with corresponding settlement records based on payment voucher numbers and UTR (Unique Transaction Reference) numbers.
    • Discrepancy Handling: Identify and flag any discrepancies for further investigation.
  • Output: A comprehensive reconciliation report detailing matched transactions and highlighting any discrepancies.

Process Flow​

  • Retrieve Settlement Data: Daily automated retrieval of settlement data from BillDesk using the API.
  • Match Transactions to Settlements: Use unique identifiers (voucher and UTR numbers) to align CampX transactions with BillDesk settlement data.
  • Identify Discrepancies: Systematically flag any mismatches for manual review and resolution.
  • Generate Reconciliation Report: Produce a detailed report for internal use, ensuring transparency in settlements.

Development Steps​

1. Get Tenants​

  • Objective: Retrieve and list tenants using BillDesk with their merchant IDs establishing a secure connection with billdesk API Download Billdesk Settlements API Docs
  • Implementation:
    • Query CampX database to filter tenants using BillDesk.
    • Extract merchant_id for each relevant tenant.
  • Output: List of tenant IDs and corresponding BillDesk merchant IDs.
  • Test Case: Ensure accurate retrieval of BillDesk users and their merchant IDs.

2. Get Settlements​

  • Objective: Fetch settlement data for each merchant from BillDesk and store it with the status "created".
  • Steps:
    • Schedule a cron job at 12 AM daily.
    • For each merchant, fetch settlements of the last 24 hours from BillDesk.
    • Store settlements in MongoDB with a status field set to "created".
  • Implementation:
    • Utilize BillDesk API for data retrieval.
    • Insert data into MongoDB, ensuring the status field in each record is set to "created".
  • Output: Settlement records in MongoDB marked as "created".
  • Test Case: Verify cron job execution, data retrieval accuracy, and correct status marking in the database.

3. Get Settlement Details​

  • Objective: Retrieve detailed transaction records for each settlement and update the settlement status to "details_fetched".
  • Steps:
    • Schedule a cron job at 12:30 AM daily.
    • Retrieve detailed transaction data for each settlement from BillDesk.
    • Update the corresponding settlement record in MongoDB to "details_fetched".
  • Implementation:
    • Call BillDesk API for detailed transaction data.
    • Update status in MongoDB records to "details_fetched".
  • Output: Updated settlement records in MongoDB with status "details_fetched".
  • Test Case: Ensure accurate fetching of details, updating of records, and status change in the database.

Schema Changes​

Settlements Schema​

src/payments/domain/schemas/settlements.schema.ts
import { Prop, Schema, SchemaFactory } from "@nestjs/mongoose";
import { HydratedDocument, Schema as MongooseSchema } from "mongoose";
import { getSetDefaultFn, parseDecimal } from "src/utils/util-functions";

export type BillDeskSettlementType = {
  objectId: string;
  pv_number: string;
  mercid: string;
  payout_mercid: string;
  pv_file_date: string;
  currency: string;
  amount_details: {
    settlement: string;
    refund: string;
    chargeback: string;
    refund_reversal: string;
    chargeback_reversal: string;
    adjustment: string;
  };
  charges: string;
  taxes: string;
  other_adjustments: string;
  payout_amount: string;
  status: string;
  settlement_date: string;
  utr: string;
  utr_date: string;
};

@Schema({ _id: false })
export class BillDeskSettlement {
  @Prop({ required: true })
  merchantId: string;

  @Prop({ required: true })
  pvNumber: string;

  @Prop({ required: true })
  date: string;

  @Prop({
    required: true,
    type: MongooseSchema.Types.Decimal128,
    get: parseDecimal,
    set: getSetDefaultFn(0),
  })
  payoutAmount: number;

  @Prop({ required: true, type: MongooseSchema.Types.Mixed })
  settlementResponse: BillDeskSettlementType;
}

const BillDeskSettlementSchema = SchemaFactory.createForClass(BillDeskSettlement);

export const SettlementCName = "settlements";
export type SettlementDocument = HydratedDocument<Settlement>;

@Schema({ timestamps: true, collection: SettlementCName })
export class Settlement {
  @Prop({ required: true })
  id: string;

  @Prop({ required: true })
  paymentClientType: string;

  @Prop({ required: true })
  date: string;

  @Prop({
    required: true,
    type: MongooseSchema.Types.Decimal128,
    get: parseDecimal,
    set: getSetDefaultFn(0),
  })
  amount: number;

  @Prop({ type: BillDeskSettlementSchema })
  billDeskSettlement: BillDeskSettlement;

  @Prop()
  createdAt: string;

  @Prop()
  updatedAt: string;
}
export const SettlementSchema = SchemaFactory.createForClass(Settlement);
SettlementSchema.index({ id: 1 }, { unique: true });

Settlements's Transaction Schema​

src/payments/domain/schemas/settlement-transaction.schema.ts
import { Prop, Schema, SchemaFactory } from "@nestjs/mongoose";
import { HydratedDocument, Schema as MongooseSchema } from "mongoose";
import { getSetDefaultFn, parseDecimal } from "src/utils/util-functions";

export type BillDeskSettlementTransaction = {
  transaction_type: string;
  bankid: string;
  bank_ref_no: string;
  billdesk_id: string;
  merc_ref_id: string;
  date: string;
  settlement_date: string;
  amount: string;
  charges: string;
  taxes: string;
  net_amount: string;
  reference_id: string;
};

export type BillDeskSettlementTransactions = {
  objectId: string;
  pv_number: string;
  mercid: string;
  payout_mercid: string;
  pv_file_date: string;
  currency: string;
  request_batchid: string;
  page_total: string;
  page_number: string;
  page_record_count: string;
  records: BillDeskSettlementTransaction[];
};

export const SettlementTransactionCName = "settlement_transactions";
export type SettlementTransactionDocument = HydratedDocument<SettlementTransaction>;

@Schema({ timestamps: true, collection: SettlementTransactionCName })
export class SettlementTransaction {
  @Prop({ required: true })
  id: string;

  @Prop({ required: true })
  paymentClientType: string;

  @Prop({ required: true })
  settlementDate: string;

  @Prop({ required: true })
  transactionDate: string;

  @Prop({
    required: true,
    type: MongooseSchema.Types.Decimal128,
    get: parseDecimal,
    set: getSetDefaultFn(0),
  })
  amount: number;

  @Prop({
    required: true,
    type: MongooseSchema.Types.Decimal128,
    get: parseDecimal,
    set: getSetDefaultFn(0),
  })
  charges: number;

  @Prop({
    required: true,
    type: MongooseSchema.Types.Decimal128,
    get: parseDecimal,
    set: getSetDefaultFn(0),
  })
  taxes: number;

  @Prop({ type: MongooseSchema.Types.Mixed })
  billDeskSettlementTransaction: BillDeskSettlementTransaction;

  @Prop()
  createdAt: string;

  @Prop()
  updatedAt: string;
}

export const SettlementTransactionSchema = SchemaFactory.createForClass(SettlementTransaction);
SettlementTransactionSchema.index({ id: 1 }, { unique: true });

Online Transaction Schema Changes​

src/payments/domain/schemas/online-transaction.schema.ts
...
export class OnlineTransaction {
  ...

  @Prop()
  settlementId: string;

  ...
}

Functions with Individual Test Cases​

  • getBillDeskSettlements
    • Test Cases:
      • Ensure accurate retrieval of settlements from BillDesk.
      • Update settlement if there is already a settlement with the same pvNumber and merchantId.
      • Verify correct insertion of settlements in MongoDB.
      • Confirm correct marking of settlements in MongoDB.
  • getBillDeskSettlementDetails
    • Test Cases:
      • Ensure accurate retrieval of settlement details from BillDesk.
      • Verify correct insertion of settlement transaction and updating settlement id in online-transaction in MongoDB.

Dependent Module Changes​

Conclusion​

This documentation presents a structured approach for developing the Settlement Reconciliation feature in CampX. It emphasizes secure, efficient, and accurate matching of transaction data with BillDesk settlement records, enhancing payment transparency in the system.