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) = 4and 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 DataORCTRNUM [Document Number],CRDTAMNT [Credit Amount],DEBITAMT [debit Amount],ORCRDAMT [Orginating Credit Amount],ORDBTAMT [Orginating Debit Amount]FROMGL20000 GL LEFT OUTER JOIN ReceivablesTransactions AR ONGL.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,
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!
Hi Gilbertnm,
Yes your understanding is very much correct on my question /query 1.
And with reg to the second script is exactly what I am trying to bring the detailed break-up of the first query. Conceptually it is pulling only the net balance instead of actual outstanding.
I will keep posted if I am getting the result.
Chemp