Dynamics GP Reporting Series: POP Purchase Order

Moving to the Purchasing module, our first start will be by printing the Purchase Order and sending it to the vendors, below is the command needed:

SQL Command:

SELECT    
dbo.POP10100.PONUMBER, dbo.POP10100.VENDNAME,
dbo.POP10100.PYMTRMID, dbo.POP10100.DOCDATE,
dbo.POP10100.BUYERID, dbo.POP00101.DSCRIPTN,
dbo.POP10100.SHIPMTHD, dbo.POP10100.STATGRP,
dbo.POP10100.PURCHADDRESS1, dbo.POP10100.PURCHCITY,
dbo.POP10110.ITEMNMBR, dbo.POP10110.ITEMDESC,
dbo.POP10110.REQDATE, dbo.POP10110.UOFM,
dbo.POP10110.QTYORDER, dbo.POP10110.LineNumber,
dbo.POP10100.PRSTADCD, dbo.POP10100.PRMSHPDTE,
dbo.POP10110.CMPNYNAM, dbo.POP10110.ADDRESS1,
dbo.POP10110.CITY, dbo.POP10110.STATE,
dbo.POP10110.ZIPCODE, dbo.POP10100.PURCHSTATE,
dbo.POP10100.PURCHZIPCODE, dbo.POP10100.CURNCYID,
dbo.POP10100.CURRNIDX, dbo.POP10110.ORUNTCST,
dbo.POP10110.OREXTCST, dbo.POP10100.ORSUBTOT,
dbo.POP10100.ORTDISAM, dbo.POP10100.ORFRTAMT,
dbo.POP10100.OMISCAMT, dbo.POP10100.ORTAXAMT
FROM         dbo.POP10100
LEFT OUTER JOIN dbo.POP10110 ON dbo.POP10100.PONUMBER = dbo.POP10110.PONUMBER
LEFT OUTER JOIN dbo.POP00101 ON dbo.POP10100.BUYERID = dbo.POP00101.BUYERID
WHERE dbo.POP10110.PONUMBER = {?PONumber}
ORDER BY dbo.POP10110.LineNumber

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