Trial Balance Data Extraction

An example search showing the extraction of the trial balance is:

var transactionSearchObj = search.create({
   type: "transaction",
   settings:[{"name":"consolidationtype","value":"NONE"}],
   filters:
   [
      ["posting","is","T"], 
      "AND", 
      ["account.internalid","noneof","@NONE@"], 
      "AND", 
      ["subsidiary","anyof","8"], 
      "AND", 
      ["accountingperiod.enddate","onorbefore","31/07/2024"]
   ],
   columns:
   [
      search.createColumn({
         name: "formulatext",
         summary: "GROUP",
         formula: "case    when {accounttype} IN ('Income', 'Cost of Goods Sold', 'Expense', 'Other Expense', 'Other Income')      then case        when {accountingperiod.startdate} < to_date('01/01/2024', 'dd/mm/yyyy')          then 'Retained Earnings'        else          case            when {accountingperiod.startdate} >= to_date('01/01/2024', 'dd/mm/yyyy')              then '3013	Legal Entity Restructuring'       else              {account}          end      end    else      {account}  end",
         label: "account"
      }),
      search.createColumn({
         name: "formulacurrency",
         summary: "SUM",
         formula: "case when {accountingperiod.enddate} <= to_date('31/07/2024', 'dd/mm/yyyy')      then NVL({debitamount},0) - NVL({creditamount},0)     else 0 end",
         label: "amount"
      }),
      search.createColumn({
         name: "formulacurrency",
         summary: "SUM",
         formula: "case when {accountingperiod.enddate} <= to_date('31/07/2024', 'dd/mm/yyyy')      then NVL({debitamount},0)     else 0 end",
         label: "debits"
      }),
      search.createColumn({
         name: "formulacurrency",
         summary: "SUM",
         formula: "case when {accountingperiod.enddate} <= to_date('31/07/2024', 'dd/mm/yyyy')      then NVL({creditamount},0)     else 0 end",
         label: "credits"
      })
   ]
});

Criteria

This search extracts all transactions from the ledger up until the cut over date:

• Posting: The search should include only posting transactions, ensuring only finalised ledger entries are retrieved.

• Account Internal ID: All transactions should relate to valid accounts (i.e., exclude transactions where the account is @NONE@). This avoid

• Subsidiary: Filter transactions to those associated with the specific subsidiary being migrated (e.g., Subsidiary ID = 8).

• Accounting Period: The search should include all transactions posted on or before the cut-over date (e.g., 31 July 2024).

Results

The results of the search will include the following columns:

1. Account: Transactions are grouped by account. However, for transactions that occurred before the current financial year (e.g., before 1 January 2024), they are grouped under "Retained Earnings". This allows for the proper treatment of prior-year balances.

2. Amount: The net balance of transactions (debits minus credits) up to the cut-over date. This balance will be used when loading data into the target system.

The “Balance” column will be used to load the balances into the target system, the “Debit” and “Credit” columns are included for reference only.