Delete item receipt microsoft dynamics pos 2009
However, the problem I have is when joining the POP30310 via the POPRCTNM.
POP30300 joins nicely with PM30200 via the VNDDOCNM and DOCNUMBR – this has a direct 1-1 relationship. INNER JOIN POP30310 P5 ON P5.POPRCTNM = P4.POPRCTNM INNER JOIN POP30300 P4 ON P4.VENDORID = P1.VENDORID AND P4.VNDDOCNM = P3.DOCNUMBR Get the correct PO Number for each Payment. INNER JOIN PM30200 P3 ON P3.DOCNUMBR = P2.APTODCNM AND P3.VENDORID = P1.VENDORID INNER JOIN PM30300 P2 ON P2.APFRDCNM = P1.DOCNUMBR AND P2.VENDORID = P1.VENDORID Following your script above, I came up with the following: So then, I stumbled across this article of yours, which seems to be very similar to what I want – except for the fact that I’m looking at Payments rather than invoices. However, what I found was that the PORDNMBR isn’t reliable for extracting the PO Number as it doesn’t always contain the data we want – sometimes it has “*Multiple”, which indicates that the invoice has more than one payment or credit applied to it. P1.DOCTYPE IN (5, 6) - Only calculate the sum of entries with doctype (5, 6 = Payment) LEFT OUTER JOIN PM30200 P3 ON P3.DOCNUMBR = P2.APTODCNM AND P3.VENDORID = P1.VENDORID LEFT OUTER JOIN PM30300 P2 ON P2.APFRDCNM = P1.DOCNUMBR AND P2.VENDORID = P1.VENDORID SUM(ISNULL(P2.APFRMAPLYAMT, P1.DOCAMNT)) AS TOTAL_PAYMENT
To give you some context, my aim is to get extract all Payments per VENDORID, then group them by the PO Number and get the sum of the amount. Hi Victoria, thanks for this, it was very helpful! However, I have come across an issue with the relationships between tables and I couldn’t quite get it right so I’m hoping you are able to shed some light on it! Any input would be much appreciated!
Delete item receipt microsoft dynamics pos 2009 update#
GRANT SELECT ON view_Payables_POP_Invoices TO DYNGRPįor more Dynamics GP SQL scripts take a look at the GP Reports page on this blog.ĭisclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone. Leave this section off if you do not want to grant permissions **/ ON a.JRNENTRY = b.JRNENTRY) G -GL entriesĪND POP.POPTYPE in (2,3) - invoices only /** the following will grant permissions to this view to DYNGRP,
SELECT DISTINCT SOURCDOC, a.JRNENTRY, ORDOCNUM, ORMSTRID SELECT DISTINCT SOURCDOC, JRNENTRY, ORDOCNUM, ORMSTRID (SELECT DISTINCT SOURCDOC, JRNENTRY, ORDOCNUM, ORMSTRID Updated to include GL journal entry number ~~~~~ CREATE VIEW view_Payables_POP_Invoices
This Dynamics GP SQL view originated from a request on the Dynamics GP customer forum, but is also something that I can see being useful in a variety of situations. It returns all posted Payables invoices that came from the Purchase Order Processing module with details of the items that were received on each invoice as well as the GL journal entry number for each POP receipt and invoice.