Dynamics GP Reporting Series: PM Transaction Entry

When we need to perform any adjustment transactions on our vendors, we’ll need to print a document to be attached to the original document sent by the vendor, below is the needed command:

SQL Command:

SELECT    
dbo.PM10000.DOCNUMBR, dbo.PM10000.DOCTYPE,
dbo.PM10000.DOCAMNT, dbo.PM10000.DOCDATE,
dbo.PM10000.PYMTRMID, dbo.PM10000.SHIPMTHD,
dbo.PM00300.ADDRESS1, dbo.PM00300.ADDRESS2,
dbo.PM00300.ADDRESS3, dbo.PM00300.CITY,
dbo.PM10000.PORDNMBR, dbo.PM10000.VCHRNMBR,
dbo.PM10000.TRXDSCRN, dbo.PM10000.PRCHAMNT,
dbo.PM10000.TRDISAMT, dbo.PM10000.TAXAMNT,
dbo.PM10000.FRTAMNT, dbo.PM10000.MSCCHAMT,
dbo.PM10000.CASHAMNT, dbo.PM10000.CHEKAMNT,
dbo.PM10000.CRCRDAMT, dbo.PM00200.VENDNAME,
dbo.PM10000.DISTKNAM, dbo.PM10000.CURTRXAM,
dbo.PM10000.CURNCYID, dbo.PM10000.VCHNUMWK,
dbo.PM10000.VENDORID, dbo.MC020103.ORCTRXAM,
dbo.MC020103.ORFRTAMT, dbo.MC020103.ORTAXAMT,
dbo.MC020103.ORCASAMT, dbo.MC020103.ORCHKAMT,
dbo.MC020103.ORCCDAMT, dbo.MC020103.ORDISTKN,
dbo.MC020103.ORWROFAM, dbo.MC020103.OMISCAMT,
dbo.MC020103.OPURAMT, dbo.MC020103.ORTDISAM
FROM         dbo.PM00200
INNER JOIN dbo.PM10000 ON dbo.PM00200.VENDORID = dbo.PM10000.VENDORID
LEFT OUTER JOIN dbo.MC020103 ON dbo.PM10000.DOCTYPE = dbo.MC020103.DOCTYPE
AND dbo.PM10000.VCHNUMWK = dbo.MC020103.VCHRNMBR
LEFT OUTER JOIN dbo.PM00300 ON dbo.PM10000.VENDORID = dbo.PM00300.VENDORID
AND dbo.PM10000.VADDCDPR = dbo.PM00300.ADRSCODE
WHERE dbo.PM10000.VCHNUMWK = {?VCHNUMWK}

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