Account Receivable Balance As of a particular date

I am trying run this query to extract the exact GL Balance as of a particular date but it is giving the net change

Example

Customer A

04/01/2018 Opening Balance      $100

04/10/2018 Invoice                      $  50

04/25/2018 Payment                   $  25

04/30/2018 Closing Balance         $ 125 

Customer B

04/01/2018 Opening Balance      $150

04/10/2018 Invoice                      $  150

04/25/2018 Payment                   $  125

04/30/2018 Closing Balance         $  25 

But if I run the query AR GL I get $ 50. The query for checking GL Balance is as under:

select SUM(DEBITAMT)-SUM(crdtamnt) from GL20000 where ACTINDX in
(select ACTINDX from GL00105 where ACTNUMST = '1100-000')
and YEAR(trxdate) = '2018' and MONTH(trxdate) = 4
and ORDOCNUM <> '' 

and similarly the AR Detailed query provides me $50

SELECT
--Open Items
GL.JRNENTRY,
----Key
(select cmpnynam from dynamics..sy01500 where interid = (select db_name())) [Company ID],
----Business Partner
AR.[Document Abbreviation][Type],
AR.[Document Type][Document Type],
AR.[Customer Number][Customer ID],
GL.ORMSTRNM [Customer Name],
AR.[Tax Registration Number] [Tax Registration Number],
----Document Details
AR.[Document Date][Document Date],
AR.[Document Number][Document Number],
AR.[Document Description][Transaction Description],
AR.[Current Trx Amount][Current Trx Amount],
----Transaction Currency
AR.[Currency ID][Currency ID],
AR.[Sales Amount][Sales Amount],
AR.[Discount Taken Amount][Discount Amount],
AR.[Document Amount][Document Amount],
----Company Currency
(select FUNLCURR from MC40000) [Company Currency],
AR.[Document Amount][Document Amount],
----Payment Terms
AR.[Due Date][Due Date],
AR.[Payment Terms ID][Payment Terms ID],
----Bank Details
----Data for Import of Goods
----Legally Required Invoice Data
----Additional Assignment
--Tax Data
--Withholding Tax Data
ORCTRNUM [Document Number],
CRDTAMNT [Credit Amount],
DEBITAMT [debit Amount],
ORCRDAMT [Orginating Credit Amount],
ORDBTAMT [Orginating Debit Amount]
FROM
GL20000 GL LEFT OUTER JOIN
ReceivablesTransactions AR ON
GL.ORDOCNUM = AR.[Document Number]
where GL.ACTINDX in
(select ACTINDX from GL00105 where ACTNUMST = '1100-000')
and YEAR(trxdate) = '2018'
and MONTH(trxdate) = '4'
and ORDOCNUM <> ''

Help will be appreciated!

Thanks,

Parents
No Data
Reply
  • Hi Chemp,

    Thanks for providing the detailed information.  I understand that you are attempting to build a SQL script to reconcile GL activity data back the subledger in which the transaction originated, is this correct?

    If yes, my first question would be, are you able to reconcile the GL to the AR subledger using the "canned" GL trail balance and Historical Aged AR Trial Balance reports?.  If yes, then we know that the balances are accurate and in sync.  If not, then I would first ensure these reports reconcile before attempting to build your own GL activity query.

    In re: to building the SQL script, I believe that you are on the right track but without having access to the data is it hard to tell.  I see that you are attempting to reconcile the cash account, is this account for general operating expenses?  What other (non-AR) cash transaction could be included in your SQL query results?  the GL20000 tables collects transaction data from the entire system, so being able to control the scope of the results when reconciling is very important. 

    Thanks so much!

Children
Related
Recommended