Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Payment history report 1
- Thread starter villica
- Start date
-
1
- #2
VictoriaYudin
IS-IT--Management
This may accomplish what you're looking for:
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
- Thread starter
- #3
- Thread starter
- #4
VictoriaYudin
IS-IT--Management
I just updated the blog post to include the payment date. 
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
- Thread starter
- #6
Hi Victoria,
I copied and pasted the query but I am getting some strange results
for example a document date of feb 02 2009 and the payment date is showing as 1900... something
anothe scenario is a document data of feb 02 2009 and payment date of jan 05 2009(before the document date). Any ideas please
villica
I copied and pasted the query but I am getting some strange results
for example a document date of feb 02 2009 and the payment date is showing as 1900... something
anothe scenario is a document data of feb 02 2009 and payment date of jan 05 2009(before the document date). Any ideas please
villica
VictoriaYudin
IS-IT--Management
Villica,
A payment date of 1/1/1900 would mean there is no payment yet. These records should show blanks for payment voucher number, payment doc number and payment type columns and the applied amount should be 0. If that's not the case, there may be something else going on in the data that you need to investigate. The reason that there are lines with no payments is that this view 'starts' with ALL posted payables transactions. You can either change the view to only include historical payables transactions, or filter the unpaid transactions out on your report.
A scenario with the payment date prior to the invoice date is absolutely plausible in real life. This could be a prepayment or a credit memo that is applied to a future invoice. There is no rule in GP as to what has to come first, invoice or payment/credit. Maybe you were thinking of the payment date as the 'apply date'? If so, that's not what the view is showing. It is showing the Document Date of the payment transaction (which could be different from the GL Posting Date or from the Apply Date or from the Apply GL Date).
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
A payment date of 1/1/1900 would mean there is no payment yet. These records should show blanks for payment voucher number, payment doc number and payment type columns and the applied amount should be 0. If that's not the case, there may be something else going on in the data that you need to investigate. The reason that there are lines with no payments is that this view 'starts' with ALL posted payables transactions. You can either change the view to only include historical payables transactions, or filter the unpaid transactions out on your report.
A scenario with the payment date prior to the invoice date is absolutely plausible in real life. This could be a prepayment or a credit memo that is applied to a future invoice. There is no rule in GP as to what has to come first, invoice or payment/credit. Maybe you were thinking of the payment date as the 'apply date'? If so, that's not what the view is showing. It is showing the Document Date of the payment transaction (which could be different from the GL Posting Date or from the Apply Date or from the Apply GL Date).
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
- Thread starter
- #8
Hi Victoria
thank you for your reply.. It makes sense what you are saying. I guess what I am looking for is the date of the invoice and date when the invoice was paid off.
vendor, vendor ID, invoice number, invoice date, payment number and payment date.
villica
thank you for your reply.. It makes sense what you are saying. I guess what I am looking for is the date of the invoice and date when the invoice was paid off.
vendor, vendor ID, invoice number, invoice date, payment number and payment date.
villica
VictoriaYudin
IS-IT--Management
Villica,
It sounds like you're looking for what GP calls the Apply Date. I updated the view on my blog to include this field.
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
It sounds like you're looking for what GP calls the Apply Date. I updated the view on my blog to include this field.
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
- Thread starter
- #10
VictoriaYudin
IS-IT--Management
Villica,
Which posting date? The document (invoice), the payment or the apply?
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
Which posting date? The document (invoice), the payment or the apply?
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
- Thread starter
- #12
Sorry Victoria, I was not aware there was more than one posting date.
The information I want to see is how long is taking us to pay our invoices. We want to see the difference between the batch date and the payment date. Does this make any sense. this is the information I am getting and is making it difficult because I don't know the difference between all the days that GP stores in the database so I have to apologize about that.
villica
The information I want to see is how long is taking us to pay our invoices. We want to see the difference between the batch date and the payment date. Does this make any sense. this is the information I am getting and is making it difficult because I don't know the difference between all the days that GP stores in the database so I have to apologize about that.
villica
VictoriaYudin
IS-IT--Management
No problem, this happens frequently when writing requirements for reports. I understand what you're asking (in theory), however, there is really no such field as a 'batch date' in GP. Sometimes, people refer to the GL Posting Date for the batch as the batch date, but I do not think that's what you want here. Let me give you a scenario an as example:
1. I receive an invoice on 3/2/09 (date 1).
2. The invoice is dated 2/23/09 (date 2).
3. I physically enter and save this invoice into GP on 3/5/09 (date 3a) with a document date of 2/23/09 (date 3b) and a GL posting date of 2/28/09 (date 3c).
4. I pull up the saved invoice on 3/6/09 (date 4), make a change, then re-save it.
5. I physically click the Post button on the batch on 3/7/09 (date 5).
To report on these:
Date 1 - unless you enter this somewhere in GP, there is no way to report on it, no field stores or asks for this date.
Date 2 - this will only be stored if you also use this as the document date (date 3b). Many businesses will not put the real invoice date in for various reasons, depending on the cut off dates for the end of the month and other factors.
Date 3a - there is a modified date for each transaction, but this will only save the last modified date, so the modified date can be used here if step 4 is not done or if it's ok to use the last modified date.
Date 3b - document date.
Date 3c - GL posting date.
Date 4 - this will be the modified date, but if there are multiple changes on different dates, only the last one will be saved.
Date 5 - this is the posted date. (post-ED as opposed to the GL post-ING date in 3c)
And we have not even started talking about the payment dates! Once you can determine what dates your users are really looking for, you should be able to add them to the framework setup in the view on my blog to get your report.
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
1. I receive an invoice on 3/2/09 (date 1).
2. The invoice is dated 2/23/09 (date 2).
3. I physically enter and save this invoice into GP on 3/5/09 (date 3a) with a document date of 2/23/09 (date 3b) and a GL posting date of 2/28/09 (date 3c).
4. I pull up the saved invoice on 3/6/09 (date 4), make a change, then re-save it.
5. I physically click the Post button on the batch on 3/7/09 (date 5).
To report on these:
Date 1 - unless you enter this somewhere in GP, there is no way to report on it, no field stores or asks for this date.
Date 2 - this will only be stored if you also use this as the document date (date 3b). Many businesses will not put the real invoice date in for various reasons, depending on the cut off dates for the end of the month and other factors.
Date 3a - there is a modified date for each transaction, but this will only save the last modified date, so the modified date can be used here if step 4 is not done or if it's ok to use the last modified date.
Date 3b - document date.
Date 3c - GL posting date.
Date 4 - this will be the modified date, but if there are multiple changes on different dates, only the last one will be saved.
Date 5 - this is the posted date. (post-ED as opposed to the GL post-ING date in 3c)
And we have not even started talking about the payment dates! Once you can determine what dates your users are really looking for, you should be able to add them to the framework setup in the view on my blog to get your report.
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
- Thread starter
- #14
VictoriaYudin
IS-IT--Management
The field name you're using is typically found in the GL tables - it is the ORPSTDDT. But it is the equivalent of the posted date in the PM tables.
In PM30200 and PM20000 this is the POSTEDDT. I won't be updating the view on my blog with this field, as it's not something that most people would ask for, but it should be relatively straightforward to do on your own.
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
In PM30200 and PM20000 this is the POSTEDDT. I won't be updating the view on my blog with this field, as it's not something that most people would ask for, but it should be relatively straightforward to do on your own.
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
- Thread starter
- #16
VictoriaYudin
IS-IT--Management
Villica,
Excellent, thanks for the follow-up! Just one note: GL30000 will only have historical (closed) year transactions...you might need to also link to GL20000 for any open years.
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
Excellent, thanks for the follow-up! Just one note: GL30000 will only have historical (closed) year transactions...you might need to also link to GL20000 for any open years.
Victoria Yudin
Dynamics GP MVP 2005 - 2009
Flexible Solutions - home of GP Reports
blog:
- Status
Similar threads
- Locked
- Question
- Locked
- Question