Get All Transactions

Overview

Query and retrieve all transactions across the entire CBS system from the unified CBSTransaction table. This endpoint provides powerful filtering, pagination, and automatic account lookups for client and branch information.

Key Features

• ✅ Universal Coverage: All transaction types in one query (deposits, loans, teller, cheques, till, vault)
• ✅ Dynamic Filtering: Filter by date range, transaction type, status, amount, account, client, etc.
• ✅ Automatic Joins: System automatically looks up account, client, and branch details
• ✅ Pagination Support: Efficient handling of large result sets
• ✅ Export Mode: Retrieve all matching records for reporting

---

Endpoint

POST /api/bpm/cmd/RetrieveAllTransactionsQuery

---

Request Parameters

Core Parameters

Parameter	Type	Required	Default	Description
pageNumber	integer	No	1	Page number for pagination
pageSize	integer	No	50	Number of records per page (max 500)
isExport	boolean	No	false	If true, returns all matching records (no pagination)

Filter Parameters

Parameter	Type	Required	Description
id	integer	No	Filter by specific transaction ID
startDate	string (ISO 8601)	No	Filter transactions from this date (inclusive)
endDate	string (ISO 8601)	No	Filter transactions until this date (inclusive)
status	array of integers	No	Filter by transaction states (see Transaction States below)
accountReference	string	No	Filter by account number
transactionType	string	No	Filter by transaction type (DEPOSIT, WITHDRAWAL, etc.)
minAmount	decimal	No	Filter transactions >= this amount
maxAmount	decimal	No	Filter transactions <= this amount
branchId	integer	No	Filter by branch ID
clientId	integer	No	Filter by client ID
transactionReference	string	No	Search by transaction reference/key

Transaction States (status values)

Value	State	Description
0	PENDING	Transaction awaiting approval
1	SETTLED	Transaction completed successfully
2	CANCELLED	Transaction cancelled before settlement
3	REVERSED	Transaction reversed after settlement

Note: CANCELLED and REVERSED transactions are automatically excluded from results unless explicitly requested.

---

Request Examples

Example 1: Get Recent Transactions (Paginated)

{
  "pageNumber": 1,
  "pageSize": 20,
  "startDate": "2026-01-01T00:00:00Z",
  "endDate": "2026-01-31T23:59:59Z"
}

Example 2: Search High-Value Pending Transactions

{
  "status": [0],
  "minAmount": 100000,
  "startDate": "2026-01-01T00:00:00Z"
}

Example 3: Find Specific Account Transactions

{
  "accountReference": "1001234567",
  "pageNumber": 1,
  "pageSize": 50
}

Example 4: Branch Daily Report (Export Mode)

{
  "branchId": 5,
  "startDate": "2026-01-05T00:00:00Z",
  "endDate": "2026-01-05T23:59:59Z",
  "isExport": true
}

Example 5: Multi-Filter Search

{
  "startDate": "2026-01-01T00:00:00Z",
  "endDate": "2026-01-31T23:59:59Z",
  "status": [0, 1],
  "branchId": 10,
  "minAmount": 50000,
  "maxAmount": 500000,
  "pageNumber": 1,
  "pageSize": 100
}

---

Response Format

Success Response

{
  "success": true,
  "message": "Transactions retrieved successfully",
  "code": "SUCCESS",
  "data": {
    "items": [
      {
        "id": 12345,
        "transactionKey": "TRX-2026-001234",
        "transactionType": "DEPOSIT",
        "transactionDate": "2026-01-05T10:30:00Z",
        "amount": 50000.00,
        "currency": "NGN",
        "accountReference": "1001234567",
        "accountType": "SAVINGS",
        "cbsTransactionHoldState": 1,
        "stateDescription": "SETTLED",
        "notes": "Cash deposit via teller",
        
        // Account Information (automatically looked up)
        "depositAccountId": 789,
        "depositAccountEncodedKey": "8a8587c07d...",
        
        // Client Information (from account lookup)
        "clientId": 456,
        "clientName": "John Doe",
        "clientEncodedKey": "8a8587c07c...",
        "clientCode": "CLI-001234",
        
        // Branch Information (from account lookup)
        "branchId": 10,
        "branchName": "Victoria Island Branch",
        "branchEncodedKey": "8a8587c07b...",
        
        // Transaction Details
        "depositTransaction": {
          "id": 567,
          "transactionType": "DEPOSIT",
          "amount": 50000.00,
          "balanceBefore": 100000.00,
          "balanceAfter": 150000.00,
          "channel": "TELLER",
          "tellerName": "Jane Smith"
        },
        
        "loanTransaction": null,
        
        // Audit Fields
        "createdBy": "teller@branch.com",
        "dateCreated": "2026-01-05T10:30:00Z",
        "lastModifiedDate": "2026-01-05T10:30:05Z"
      }
    ],
    "pageNumber": 1,
    "pageSize": 20,
    "totalCount": 1543,
    "totalPages": 78,
    "hasNextPage": true,
    "hasPreviousPage": false
  }
}

Response Fields

Transaction Core Fields

Field	Type	Description
id	integer	Unique transaction ID
transactionKey	string	Human-readable transaction reference
transactionType	string	Type of transaction (DEPOSIT, WITHDRAWAL, LOAN_DISBURSEMENT, etc.)
transactionDate	datetime	When transaction occurred
amount	decimal	Transaction amount
currency	string	Currency code (NGN, USD, etc.)
accountReference	string	Account number involved
accountType	string	Type of account (SAVINGS, CURRENT, LOAN, etc.)
cbsTransactionHoldState	integer	Current state (0=PENDING, 1=SETTLED, 2=CANCELLED, 3=REVERSED)
stateDescription	string	Human-readable state
notes	string	Transaction notes/description

Account Lookup Fields

Field	Type	Description
depositAccountId	integer	Deposit account ID (if applicable)
depositAccountEncodedKey	string	Deposit account encoded key
loanAccountId	integer	Loan account ID (if applicable)
loanAccountEncodedKey	string	Loan account encoded key

Client Lookup Fields

Field	Type	Description
clientId	integer	Client ID
clientName	string	Full client name (individual: "FirstName LastName", group: "GroupName")
clientEncodedKey	string	Client encoded key
clientCode	string	Client code/number

Branch Lookup Fields

Field	Type	Description
branchId	integer	Branch ID
branchName	string	Branch name
branchEncodedKey	string	Branch encoded key

Transaction Detail Objects

DepositTransaction (for deposit-related transactions):

{
  "id": 567,
  "transactionType": "DEPOSIT",
  "amount": 50000.00,
  "balanceBefore": 100000.00,
  "balanceAfter": 150000.00,
  "channel": "TELLER",
  "tellerName": "Jane Smith",
  "tillId": 123,
  "terminalId": "TERM-001"
}

LoanTransaction (for loan-related transactions):

{
  "id": 890,
  "transactionType": "LOAN_REPAYMENT",
  "principalAmount": 10000.00,
  "interestAmount": 2000.00,
  "penaltyAmount": 500.00,
  "totalAmount": 12500.00,
  "outstandingPrincipal": 90000.00,
  "channel": "DIRECT_DEBIT"
}

Pagination Fields

Field	Type	Description
pageNumber	integer	Current page number
pageSize	integer	Number of items per page
totalCount	integer	Total number of matching transactions
totalPages	integer	Total number of pages
hasNextPage	boolean	Whether next page exists
hasPreviousPage	boolean	Whether previous page exists

---

Usage Examples

Example 1: Branch End-of-Day Report

// Get all settled transactions for today
const endOfDayReport = await fetch('/api/bpm/cmd/RetrieveAllTransactionsQuery', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    branchId: userBranchId,
    status: [1], // SETTLED only
    startDate: new Date().toISOString().split('T')[0] + 'T00:00:00Z',
    endDate: new Date().toISOString().split('T')[0] + 'T23:59:59Z',
    isExport: true // Get all records
  })
});

const data = await endOfDayReport.json();
console.log(`Total transactions today: ${data.data.totalCount}`);
console.log(`Total amount: ${data.data.items.reduce((sum, t) => sum + t.amount, 0)}`);

Example 2: Search Pending Approvals

// Find all pending transactions awaiting approval
const pendingApprovals = await fetch('/api/bpm/cmd/RetrieveAllTransactionsQuery', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    status: [0], // PENDING
    minAmount: 100000, // High-value only
    pageNumber: 1,
    pageSize: 50
  })
});

const data = await pendingApprovals.json();
data.data.items.forEach(txn => {
  console.log(`${txn.transactionKey}: ${txn.amount} - ${txn.clientName}`);
});

Example 3: Customer Transaction History

// Get transaction history for specific customer account
const customerHistory = await fetch('/api/bpm/cmd/RetrieveAllTransactionsQuery', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    accountReference: '1001234567',
    startDate: '2026-01-01T00:00:00Z',
    pageNumber: 1,
    pageSize: 100
  })
});

const data = await customerHistory.json();
console.log(`Account ${accountNumber} has ${data.data.totalCount} transactions`);

Example 4: Fraud Detection Query

// Find suspicious large cash deposits
const suspiciousTransactions = await fetch('/api/bpm/cmd/RetrieveAllTransactionsQuery', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    transactionType: 'DEPOSIT',
    minAmount: 500000, // Above threshold
    startDate: new Date(Date.now() - 7*24*60*60*1000).toISOString(), // Last 7 days
    pageNumber: 1,
    pageSize: 100
  })
});

---

Performance Considerations

Optimization Tips

✅ Always use date filters for better performance:

{
  "startDate": "2026-01-01T00:00:00Z",
  "endDate": "2026-01-31T23:59:59Z"
}

✅ Use pagination for large result sets:

{
  "pageSize": 50,
  "pageNumber": 1
}

✅ Filter by branch to reduce result set:

{
  "branchId": 10
}

❌ Avoid querying without any filters - may timeout:

{} // Don't do this!

❌ Avoid very large page sizes:

{
  "pageSize": 10000 // Too large, use isExport instead
}

Indexing

The following fields are indexed for optimal query performance:

• DateCreated
• CBSTransactionHoldState
• AccountReference
• BranchId
• ClientId
• TransactionType

---

Error Responses

Invalid Date Range

{
  "success": false,
  "message": "End date must be after start date",
  "code": "INVALID_DATE_RANGE"
}

Page Size Too Large

{
  "success": false,
  "message": "Page size cannot exceed 500. Use isExport=true for larger datasets.",
  "code": "PAGE_SIZE_EXCEEDED"
}

No Results Found

{
  "success": true,
  "message": "No transactions found matching criteria",
  "code": "SUCCESS",
  "data": {
    "items": [],
    "totalCount": 0,
    "pageNumber": 1,
    "pageSize": 50,
    "totalPages": 0
  }
}

---

Security & Permissions

Required Permissions

• Role: ViewTransactions or higher
• Branch Access: Users can only view transactions from branches they have access to

Automatic Filtering

The system automatically applies security filters:

• Branch-level access control
• User role validation
• Sensitive field masking (where applicable)

---

Related Endpoints

• Get Transaction Details - Get full details for a specific transaction
• Approve Transaction - Approve a pending transaction
• Reject Transaction - Reject a pending transaction
• Reverse Transaction - Reverse a settled transaction
• Cancel Transaction - Cancel a pending transaction

---

Implementation Notes

How Account Lookups Work

The system performs intelligent account lookups:

1. Query CBSTransaction table with filters
2. Extract unique account references from results
3. Batch query DepositAccount and LoanAccount tables
4. Create in-memory dictionaries for O(1) lookups
5. Join client and branch data from accounts
6. Map enriched data to response

This approach avoids N+1 query problems and ensures optimal performance.

Transaction Type Coverage

The query returns transactions from:

• ✅ Deposit operations (deposits, withdrawals, transfers)
• ✅ Loan operations (disbursements, repayments, writeoffs)
• ✅ Teller operations (OTC transactions)
• ✅ Till operations (cash management)
• ✅ Vault operations (vault funding, transfers)
• ✅ Cheque operations (deposits, withdrawals, clearing)
• ✅ Internal transfers (account-to-account)
• ✅ Fee/charge postings
• ✅ Interest postings

---

Testing

Sample Test Cases

// Test 1: Basic pagination
test('should paginate results correctly', async () => {
  const response = await getTransactions({ pageNumber: 1, pageSize: 10 });
  expect(response.data.items.length).toBeLessThanOrEqual(10);
  expect(response.data.pageNumber).toBe(1);
});

// Test 2: Date filtering
test('should filter by date range', async () => {
  const response = await getTransactions({
    startDate: '2026-01-01T00:00:00Z',
    endDate: '2026-01-31T23:59:59Z'
  });
  response.data.items.forEach(txn => {
    expect(new Date(txn.transactionDate)).toBeAfter('2026-01-01');
    expect(new Date(txn.transactionDate)).toBeBefore('2026-02-01');
  });
});

// Test 3: Status filtering
test('should filter by transaction state', async () => {
  const response = await getTransactions({ status: [1] }); // SETTLED only
  response.data.items.forEach(txn => {
    expect(txn.cbsTransactionHoldState).toBe(1);
  });
});