How to determine if invoice from SOP10100 is paid via db queries

(0) Share

Report

Posted on by Community Member

Hi. I know virtually nothing about gp dynamics but we have an application that feeds to it. I have been tasked with trying to query if an invoice has been paid in full, or even partial for that matter. All I have to go off of is the invoice number, the SOPNUMBE from SOP10100. In my googling, it looks like the PM tables determine paid or not by which one it is on. But is there a way to link the SOPNUMBE to the PM tables? I'm not yet finding a way but am still searching. Or am I way off track here?

Thanks, Adam

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (2)

KirkLivermont 5,985 on at

Like (0)

Report

Adam,

How (and when) is the order being paid? There are a couple of different ways you can go about doing this.

Regards,

Kirk

Was this reply helpful? Yes No
Verified answer

L Vail 65,271 on at

Like (0)

Report

The SOP10110 is the document header file for UNposted SOP transactions. The table contains columns stating the original trx amount and the current trxamt. If the current transaction amount is zero, the invoice is fully paid. As soon as you post a SOP invoice, it creates a receivable in the RM20101 table. This is a very simple statement, so I can imagine somebody has that query alredy done for you. The first place I would look at Victoria Yudin's website. She has dozens of SQL views you can download.
Kind regards,
Leslie

Was this reply helpful? Yes No
Suggested answer

Victoria Yudin 22,769 on at

Like (1)

Report
Adam,

As Leslie mentioned, SOP10100 holds unposted sales invoices. PM stands for 'payables management', so I think whatever you Googled for that put you on the wrong track a bit.

It is possible that sales invoices have payments entered against them while they are still unposted. In that case, there are 4 fields in the SOP10100 table that you can look at to help you see the details:

DOCAMNT - this is the total amount of the invoice

PYMTRCVD - this is the payment received on the invoice

DEPRECVD - this is the deposit received on the invoice (not sure if this would show up on an invoice transaction, it might if the invoice was transferred from an order that had a deposit)

ACCTAMNT - this is the amount still due on the invoice - you could compare this to the DOCAMNT and if the ACCTAMNT is less, then you know there has been a payment applied.

Please note that all the fields above are in the functional currency. If you need to see originating currency, the fields that appear in the table right after each field I listed above would be the corresponding originating currency field.

Hope that helps.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello everyone. Thanks for your quick replies. So the SOP10100 is for 'unposted' transactions but can still track payments received. Once the invoice is paid in full, it moves it to the RM20101? And the DOCNUMBR there is what the SOPNUMBE was, so that would be what I would use to link?

Was this reply helpful? Yes No
Verified answer

Victoria Yudin 22,769 on at

Like (0)

Report
Adam,

It's a bit more complicated than this and there may be other tables involved. It would help to know what you are trying to accomplish to better answer your questions.

The short answers to your questions are:

Invoices are only in the SOP10100 table until they are posted.

Only payments entered against the invoice BEFORE it is posted will show up in the SOP10100 table.

Once posted invoices will move from the SOP10100 table to the SOP30200 table and will also be created in the RM20101 table.

When invoices are paid they may stay in the RM20101 table or be moved to the RM30101 table. (Depending on what users do.)

To link from SOP10100 to RM20101:

SOP10101.SOPNUMBE= RM20101.DOCNUMB

SOP10101.SOPTYPE = 3

RM20101.RMDTYPAL = 1

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Victoria,

Thankyou for response and sorry for my delay. I think that should answer my question. We have an application that feeds into dynamicsgp. Unfortunately I am not sure what the users do on the dynamics side once it hits the SOP10100 table. We have a client that wants a report of knowing when the invoices are paid, which is where I came in. Someone else wrote a report that uses the SOPNUMBE to get the invoice number but that's as much as I knew. I was able to track down both an unpaid and paid example from our system, in dynamics using the tables that were suggested in this thread, the SOP10100 and RM20101. So for now, I will use those tables to generate new reports and if something changes I will post another forum thread.

Thanks again

Was this reply helpful? Yes No
Deepesh.Singh 5 on at

Like (0)

Report

Can i get a query for this

Was this reply helpful? Yes No