Created by Jakub Ukrop, last modified by Gavin Zhang on Feb 13, 2023

Data model

For every report, we’ll store a record in the DB

  • Report metadata

    • creation timestamp

    • type of reconciliation (cash-in, cash-out …)

    • vendor (Paynamics, DigiPay ,…)

    • version - a sequence unique per type+vendor+date

    • ID of user who created the reconciliation

    • number of transactions

      • on our side

      • on their side

      • how many were missing on their side [A]

      • how many were missing on our side [B]

      • how many were on both sides but the data (e.g. amount) did not match [C]

      • how many were on both sides and the data matched [D]

  • Raw input itself - link to the archived excel file

  • The report (output) itself - link to the archived excel file

// Example
{
  "id": "5b396d59-b14f-4151-b805-6e454845308b",
  "createdAt": "2022-11-23T10:54:12.266Z",
  "reportDate": "2022-11-24",
  "userId": "00u29qvc20EraUzNX697",
  "type": "CASH_IN",
  "vendor": "PAYNAMICS",
  "version": 1,
  "internalRecordsCount": 0,
  "vendorRecordsCount": 20,
  "internalMissingRecordsCount": 20,
  "vendorMissingRecordsCount": 0,
  "consistentRecordsCount": 0,
  "inconsistentRecordsCount": 0,
  "originalVendorReportFileName": "Temp.xls"
}

Cash-in (Paynamics)

Internal attribute

Paynamics attribute

Note

paynamicsResponseId

RESPONSE_ID

The is the key to match transactions

amount

AMOUNT

vedorFee

TRX_FEE

currency

CURRENCY

Procedure

  1. Match transactions by “Paynamics response ID”

  2. Find those that are

    • Missing in internal data, present only in Paynamics data [A]

    • Missing in Paynamics data, present only in internal data [B]

    • Present in both sets but some of the attributes (see table above) are different [C]

    • Correctly matched

Output report structure

Output record is an excel file (XLSX)

  • the file name is cashin_paynamics_reconciliation_2022_11_30_v1.xlsx using the correct date

  • for any subsequent files created for that date, increase the version

There are two tabs

  1. “Non-matched transactions” (cases A, B, C)

  2. “Matched transactions” (matched ones)

Both tabs have the same structure

Attribute [implementation note]

A

B

C

request ID [from REQUEST_ID vendor column]

x

x

response ID [from RESPONSE_ID vendor column]

x

x

x

processing channel [from PROCESSING_CHANEL column]

x

x

transaction ID

x

x

transaction type

x

x

customer Id

x

x

issues

x

x

x

internal amount

x

x

paynamics amount

x

x

internal vedor fee

x

x

paynamics vedor fee

x

x

internal currency

x

x

paynamics currency

x

x

Cash-out (Paynamics)

Internal attribute

Paynamics attribute

Note

paynamicsResponseId

dreqResponseID

The is the key to match transactions

amount

dreqAmount

vedorFee

merchant_fee

currency

dreqCurrency

  • The reconciliation procedure is the same as for Cash-in

  • File name: cashout_paynamics_reconciliation_2022_11_30_v1.xlsx

Tab structure:

Attribute [implementation note]

A

B

C

request ID [from dreqRequestID vendor column]

x

x

response ID [from dreqResponseID vendor column]

x

x

x

response code [from dresResponseCode vendor column]

message [from dresResponseMessage vendor column]

x

x

advice [from dresResponseAdvise vendor column]

x

x

transaction ID

x

x

transaction type

x

x

customer Id

x

x

issues

x

x

x

internal amount

x

x

paynamics amount

x

x

internal vedor fee

x

x

paynamics vedor fee

x

x

internal currency

x

x

paynamics currency

x

x

Disbursement wallet (Paynamics)

TODO: Analyse when in scope

Bill payments (Paynamics)

TODO: Analyse when example vendor report is available

Internal attribute

Paynamics attribute

Note

paynamicsResponseId

The is the key to match transactions

amount

vedorFee

currency

Bill payments (ECPay)

TODO Gavin Zhang (Unlicensed)

Cards (Euronet)

TODO: Analyse when in scope

Internal reconciliation

TODO: Analyse when in scope