Dynamics GP Reporting Series: PM Manual Payment

Finally we’ll need to print the vendor payment, we’ll use the Manual Payment form to enter the transaction and print out the payment using the report, below is the needed command:

SQL Command:

SELECT    
dbo.PM10400.PMNTNMBR, dbo.PM10400.DOCNUMBR,
dbo.PM10400.DOCDATE, dbo.PM10400.VENDORID,
dbo.PM00200.VENDNAME, dbo.PM10400.PYENTTYP,
dbo.PM10400.CARDNAME, dbo.PM10400.CHEKBKID,
dbo.PM10100.CRDTAMNT, dbo.PM10100.DEBITAMT,
dbo.PM10100.DSTINDX, dbo.PM10100.ORCRDAMT,
dbo.PM10100.ORDBTAMT, dbo.PM10400.DOCAMNT,
dbo.CM00100.DSCRIPTN, dbo.PM10400.CURNCYID,
dbo.PM10100.DISTTYPE, dbo.GL00100.ACTDESCR,
dbo.GL00105.ACTNUMST, dbo.PM00200.VNDCHKNM,
dbo.PM10400.TRXDSCRN
FROM         dbo.PM10400
INNER JOIN dbo.PM10100 ON dbo.PM10400.VCHRNMBR = dbo.PM10100.VCHRNMBR
INNER JOIN dbo.PM00200 ON dbo.PM10400.VENDORID = dbo.PM00200.VENDORID
INNER JOIN dbo.GL00100
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
ON dbo.PM10100.DSTINDX = dbo.GL00100.ACTINDX
LEFT OUTER JOIN dbo.CM00100 ON dbo.PM10400.CHEKBKID = dbo.CM00100.CHEKBKID
WHERE     (dbo.PM10400.CNTRLTYP = 1) and  (dbo.PM10100.CNTRLTYP = 1)

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the below:

Crystal Report Design:

 

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com