Script to mark a years worth of bank transactions as cleared for bank reconciliation in Dynamics 365 finance and operations

This post I would  like to share my experience with people who is looking for reconcile years of bank transactions at single click, Of-course we can do it manually but it would be lot of time consuming for the users to select each and every transaction in case if you need to reconcile thousands of transactions.

The business need for our client is , they where using AMC banking ISV solution for all banking related activities hence they have not reconciled single transaction from couple of years. but now they would like to give up on AMC banking and go with standard module cash and bank management. 

In the process to use features like advance bank reconciliation in dynamics 365 finance and operation, either of two options should be satisfied.

1. All the fiscal periods should be open for un-reconcile transactions.

2. Or all the transactions should be reconciled. 

Option 1 would be impossible because we can't go and open all closed periods, hence we have too rely on option 2 and here I came up with script where with just one click it will reconcile all un-reconcile transaction over period. 

Below is the class which helps to solve the purpose.

public static class JP_SupportScripts
{
    public static void autoReconcilation(TransDate _accountStatementDate, BankAccountStatementNum _accountStatementNum, CompanyBankAccountId _accountId)
    {
        BankAccountStatement bankAccountStatement;
        BankAccountTrans     bankAccountTrans, bankAccountTransUpdate;
        BankAccountTable     bankAccountTable;
        int                  noOfRec;

        ttsbegin;

        select firstonly bankAccountTable
            exists join bankAccountTrans
            where bankAccountTrans.AccountId == bankAccountTable.AccountID
            && bankAccountTable.AccountID == _accountId
            && bankAccountTrans.Reconciled == NoYes::No
            && bankAccountTrans.AccountStatementDate == Global::dateNull()
            && bankAccountTrans.AccountStatement == "";

        if (bankAccountTable.RecId)
        {
            if(Box::yesNo(strFmt("Do you want to reconcile all the transaction for the bank %1", bankAccountTable.AccountID), DialogButton::Yes) == DialogButton::Yes)
            {
                bankAccountStatement.AccountId            = _accountId;
                bankAccountStatement.AccountStatementDate = _accountStatementDate;
                bankAccountStatement.AccountStatementNum  = _accountStatementNum;
                bankAccountStatement.CurrencyCode         = bankAccountTable.CurrencyCode;
                bankAccountStatement.EndingBalance        = bankAccountTable.balanceCur();
                bankAccountStatement.insert();

                while select forupdate bankAccountTransUpdate
                    where bankAccountTransUpdate.Reconciled == NoYes::No
                    && bankAccountTransUpdate.AccountId == bankAccountTable.AccountId
                    && bankAccountTransUpdate.AccountStatementDate == Global::dateNull()
                    && bankAccountTransUpdate.AccountStatement == ""
                {
                    bankAccountTransUpdate.Included = NoYes::Yes;
                    bankAccountTransUpdate.AccountStatement = bankAccountStatement.AccountStatementNum;
                    bankAccountTransUpdate.AccountStatementDate = bankAccountStatement.AccountStatementDate;
                    bankAccountTransUpdate.update();
                    noOfRec++;
                }
                info (strFmt("Bank statemant '%1' created with %2 transactions", _accountStatementNum, noOfRec));
            }
           
        }
        else
            info(strFmt('No transactions to reconcile for bank %1', _accountId));

        ttscommit;

    }

}

This script will create account statement with ending balance for the provided bank account and reconciles all the transactions.

Hope this helps , I will come-up with another interesting blog post soon, till then happy daxing :)