Bank Reconciliation Report Development and Format:
(1) In this blog we will do development of “Bank Reconciliation” or “Bank Reco.” report inD365 BC.
(2) For developing Bank Reco. Report: Use Dataitem : Bank Account Ledger Entry as shown. Note: The below marked field is not available in “Bank Account Ledger” entry table, for using the below format, develop these fields in “Bank Account Ledger” entry table.
(3) RDLC format as shown.
(4) After doing the above steps, publish the report.
Source Code:
| report 50011 “Bank Reconcillation Statement” { UsageCategory = Administration; ApplicationArea = All; RDLCLayout = ‘./BankRecoReport.rdl’; dataset { dataitem(Bank_Account_Ledger_Entry; “Bank Account Ledger Entry”) { DataItemTableView = SORTING(“Bank Account No.”, “Posting Date”); column(Posting_Date; “Posting Date”) { } column(Particulars; Description) { } column(Voucher_Type; “Document Type”) { } column(Transaction_Type; ‘Payment Mehod’) { } column(Instrument_No; ‘Cheque No.’) { } column(Instrument_Date; ‘Cheque Date’) { } column(Bank_Date; ‘Clearing Date’) { } column(Debit_Amount; “Debit Amount”) { } column(Credit_Amount; “Credit Amount”) { } column(CompanyInformation_Name; CompanyInformation.Name) { } column(CompanyInformation_Name_2; CompanyInformation.”Name 2″) { } column(CompanyInformation_Address; CompanyInformation.Address) { } column(CompanyInformation_Address_2; CompanyInformation.”Address 2″) { } column(CompanyInformation_City; CompanyInformation.City) { } column(CompanyInformation_Post_Code; CompanyInformation.”Post Code”) { } column(CompanyInformation_State; CompanyInformation.”State Code”) { } column(Bank_Name; GetBankAccount.Name) { } column(Start_Date; StartDate) { } column(End_Date; EndDate) { } column(BalAsPerBank_DR; BalAsPerBankDR) { } column(AmtyNotRefinBank_DR; AmtyNotRefinBankDR) { } column(BalAsPerBook_DR; BalAsPerBookDR) { } column(BalAsPerBank_CR; BalAsPerBankCR) { } column(AmtyNotRefinBank_CR; AmtyNotRefinBankCR) { } column(BalAsPerBook_CR; BalAsPerBookCR) { } trigger OnPreDataItem() begin CompanyInformation.get; SETFILTER(“Bank Account No.”, ‘%1’, BankAccountNo); SETFILTER(“Posting Date”, ‘%1..%2’, StartDate, EndDate); BalAsPerBook := 0; AmtyNotRefinBank := 0; BalAsPerBank := 0; BalAsPerBookDR := 0; AmtyNotRefinBankDR := 0; BalAsPerBankDR := 0; BalAsPerBookCR := 0; AmtyNotRefinBankCR := 0; BalAsPerBankCR := 0; BankAccountLedgerEntry1.RESET; BankAccountLedgerEntry1.SETCURRENTKEY(“Bank Account No.”, “Posting Date”); BankAccountLedgerEntry1.SETRANGE(“Bank Account No.”, BankAccountNo); BankAccountLedgerEntry1.SETFILTER(“Posting Date”, ‘..%1’, EndDate); IF BankAccountLedgerEntry1.FINDSET THEN BEGIN REPEAT BalAsPerBook += BankAccountLedgerEntry1.Amount; UNTIL BankAccountLedgerEntry1.NEXT = 0; END; BankAccountLedgerEntry2.RESET; BankAccountLedgerEntry2.SETCURRENTKEY(“Bank Account No.”, “Posting Date”); BankAccountLedgerEntry2.SETRANGE(“Bank Account No.”, BankAccountNo); BankAccountLedgerEntry2.SETFILTER(“Posting Date”, ‘..%1’, EndDate); IF BankAccountLedgerEntry2.FINDSET THEN BEGIN REPEAT AmtyNotRefinBank += BankAccountLedgerEntry2.Amount; UNTIL BankAccountLedgerEntry2.NEXT = 0; END; IF BalAsPerBook > 0 THEN BalAsPerBookDR := BalAsPerBook; IF BalAsPerBook < 0 THEN BalAsPerBookCR := BalAsPerBook; IF AmtyNotRefinBank > 0 THEN AmtyNotRefinBankDR := AmtyNotRefinBank; IF AmtyNotRefinBank < 0 THEN AmtyNotRefinBankCR := AmtyNotRefinBank; IF AmtyNotRefinBankDR <> 0 THEN BalAsPerBank := BalAsPerBook – AmtyNotRefinBankDR; IF AmtyNotRefinBankCR <> 0 THEN BalAsPerBank := BalAsPerBook + ABS(AmtyNotRefinBankCR); IF (AmtyNotRefinBankDR = 0) AND (AmtyNotRefinBankCR = 0) THEN BalAsPerBank := BalAsPerBook; IF BalAsPerBank > 0 THEN BalAsPerBankDR := BalAsPerBank; IF BalAsPerBank < 0 THEN BalAsPerBankCR := BalAsPerBank; end; trigger OnAfterGetRecord() begin GetBankAccount.GET(“Bank Account No.”); end; } } requestpage { layout { area(Content) { group(Options) { Caption = ‘Options’; field(BankAccountNo; BankAccountNo) { ApplicationArea = Basic, Suite; Caption = ‘BankAccount No.’; TableRelation = “Bank Account”; } field(StartDate; StartDate) { ApplicationArea = Basic, Suite; Caption = ‘Start Date’; } field(EndDate; EndDate) { ApplicationArea = Basic, Suite; Caption = ‘End Date’; } } } } actions { area(processing) { action(ActionName) { ApplicationArea = All; } } } } trigger OnPreReport() begin IF BankAccountNo = ” THEN ERROR(‘Bank Account No. must not be blank’); IF StartDate = 0D THEN ERROR(‘Start Date must not be blank’); IF EndDate = 0D THEN ERROR(‘End Date must not be blank’); end; trigger OnPostReport() begin end; var BankAccountNo: Text[250]; StartDate: Date; EndDate: Date; GetBankAccount: Record “Bank Account”; BalAsPerBook: Decimal; AmtyNotRefinBank: Decimal; BalAsPerBank: Decimal; BalAsPerBookDR: Decimal; AmtyNotRefinBankDR: Decimal; BalAsPerBankDR: Decimal; BalAsPerBookCR: Decimal; AmtyNotRefinBankCR: Decimal; BalAsPerBankCR: Decimal; BankAccountLedgerEntry1: Record “Bank Account Ledger Entry”; BankAccountLedgerEntry2: Record “Bank Account Ledger Entry”; CompanyInformation: Record “Company Information”; } |
