Dynamics SQL question RE Statement Aging

[ckelsoe]

I have created a custom statement in Access. I am using RM00101 for Customer data, RM00103 for aging and balance information, RM20101 for open transactions and RM30101 for transaction history. Based on those tables, I am accurately (except for NSF cases) creating statements. In addition, I am calculating accurate Balance Brought Forward based on a calculation. However, I can not figure out how to recreate the aging data found in RM00103. I have found tables RM20201 and RM30201 which seems to have some of the necessary data.

Has anyone figured out how to create aging as of a specified date outside of Dynamics? If so, some tips would be appreciated.

 

[hooggie]

The aging in Great Plains on RM is not real time it is snap shot data to fix the ledger in a position between statements.

To recreate the aging information on the sales ledger you would need to calculate it on the same date as the aging was last calculated in great plains.

To ignore the RM00103 data you could perform your own summary calculations based the due date field in the RM Open and RM History files, you should also refer to the RM work which you are not currently doing as there may be posted applied payments which would effect the statement position.

It is worth asking the qeustion why you are not using the statements in Great Plains, is it because you don't know how to use the report writer adequeatly well as it is a powerfull tool and in 6 years of consulting there are only about 5 cases where I have not been able to produce the report exactly to meet someones requirements.

 

[ckelsoe]

Thanks for the reply. The company had two large GP consulting firms say that there was no way to create the statement like the company wanted in GP. I was able to create the statement like they wanted in the Access report writer. I have figured out how to accurately calculate the balance brought forward at any given date. However, the aging is still an issue. There is additional restrictions to normal GP usage due to a third party application that feeds data to the GP system. That may be why others have failed to create the statement correctly. I need the ability to calculate the aging as of the statement date, not when the system was last aged. That is why I am looking to ignore the RM00103 data and calculate it on the open, history, and now work tables. I need to do this in a stored proc as there are some plans to let the customer pull their own statements via the web at some point in the future.

Thanks for your reply and suggestions.

 

[hooggie]

I might be over simplifying the issue but if you are in Access why don't you create a query like this (either in Access or in a SQL view (In SQL you have to use case statements iif is not a valid function))

Current: iif(DOCDATE > (StatementDate - 30), CURTRXAM, 0)

30 - 60 Days: iif(DOCDATE > (StatementDate - 60 and DOCDATE <= StatementDate - 30), CURTRXAM, 0)

etc...

This would assume that you had a union query with all the transactions in and that StatementDate was a user input field.