Dynamics Community Forum Thread Details

Received and Invoiced POS

Hi All

Please I want to run an sql query to see all POs that have been received and invoiced and possibly payed for if available

Can anyone help

Tanks

Hello Michael,

The tables we would want to look at are the POP10100 (Purchase Order Work (header)) and POP10110 (Purchase Order Line Work (line detail)). There is a POSTATUS in the POP10100 and a POLNESTA column in the POP10110. Here are the values for those two collumns as they are the same:

1 – New

2 – Released

3 – Change Order

4 – Received

5 – Closed

6 – Cancelled

So what we could do is run a script like the one below to show you all POs that have been received and invoiced. If a PO has been received and Invoiced it will be be in Closed status:

select * from POP10100 where POSTATUS > 4

If you want the line information from the POs you would run a similar script against the POP10110:

select * from POP10110 where POLNESTA > 4

To be able to create one script that will also show the paid document you would have to link the above two tables with the invoice number from the POP10500 to the PM30200 for the paid history. If the invoice is in the PM30200, its in history and has been paid.

There are many ways to approach this, but hopefully this gives you some ideas of the tables you might use.

If you need more detailed steps on how to create the script, I would recommend submitting a support case so an engineer can take a closer look at your request.

Best regards,

Dan Moore

Partner Online Technical Community

-----------------------------------------------------------------------------------------

We hope you get value from our new forums platform! Tell us what you think:

social.microsoft.com/.../threads

------------------------------------------------------------------------------------------

This posting is provided "AS IS" with no warranties, and confers no rights

Quick Links

Leaderboard