I have to develop a report to display all vendor prepayment transactions (with a ‘X’ GL account) within date parameters(which I successfully developed). Now, I have to add opening balance of the vendor transactions until from date and match the total with GL balance. I calculated the opening balance from amountMST field in vendtrans Table but this value is very high and is not matching with GL opening balance. Really appreciate your help in this regard.
This is the query I developed.
select sum(AmountMST) from vendTrans
join vendTable where vendTable.AccountNum == vendTrans.AccountNum
&& vendTrans.TransDate < fromdate
join vendInvoiceJour where vendInvoiceJour.InvoiceId == vendTrans.Invoice
&& vendInvoiceJour.OrderAccount == vendtrans.AccountNum
exists join subLedger where subLedger.Voucher == vendTrans.Voucher
join header where header.RecId == subLedger.GeneralJournalEntry
join trans where trans.GeneralJournalEntry == header.RecId
join dimComb where dimComb.RecId == trans.LedgerDimension
join acc where acc.RecId == dimComb.MainAccount
&& acc.MainAccountId == ‘X’;