Monday, May 24, 2010

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:

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

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.

Mohammad R. Daoud - CTO
+962 - 79 - 999 65 85

No comments:

Related Posts:

Related Posts with Thumbnails