Dynamics GP Reporting Series: POP Receiving Transaction Entry

After creating the purchase order, we’ll need to receive the goods to the inventory, we’ll generate the receiving document along with its journal, the receiving view below displays only the posted receiving transactions, below is the needed command:

SQL Command:

SELECT   
dbo.POP30300.POPRCTNM, dbo.POP30300.POPTYPE,
dbo.POP30300.VNDDOCNM, dbo.POP30300.receiptdate,
dbo.POP30300.VENDNAME, dbo.POP30300.VENDORID,
dbo.POP30300.VOIDSTTS, dbo.POP30300.CURNCYID,
dbo.POP30300.ORSUBTOT, dbo.POP30300.ORTDISAM,
dbo.POP30300.ORFRTAMT, dbo.POP30300.ORMISCAMT,
dbo.POP30300.ORTAXAMT, dbo.POP30310.ITEMNMBR,
dbo.POP30310.ITEMDESC, dbo.POP30310.UOFM,
dbo.POP30310.ORUNTCST, dbo.POP30310.OREXTCST,
dbo.POP30310.CURRNIDX, dbo.PM00200.ADDRESS1,
dbo.PM00200.CITY, dbo.PM00200.STATE,
dbo.PM00200.COUNTRY, dbo.PM00200.ZIPCODE,
dbo.PM00200.ADDRESS2, dbo.POP30310.PONUMBER,
dbo.POP10500.QTYINVCD, dbo.POP30300.VCHRNMBR,
(SELECT     Top 1 reqdate
FROM         POP10110
WHERE     ponumber = dbo.POP30310.ponumber) AS ReqDate
FROM         dbo.POP30300
INNER JOIN dbo.POP30310 ON dbo.POP30300.POPRCTNM = dbo.POP30310.POPRCTNM
INNER JOIN dbo.PM00200 ON dbo.POP30300.VENDORID = dbo.PM00200.VENDORID
INNER JOIN dbo.POP10500 ON dbo.POP30310.PONUMBER = dbo.POP10500.PONUMBER
AND dbo.POP30310.RCPTLNNM = dbo.POP10500.RCPTLNNM
AND dbo.POP30310.POPRCTNM = dbo.POP10500.POPRCTNM
AND dbo.POP30310.ITEMNMBR = dbo.POP10500.ITEMNMBR
WHERE     dbo.POP30300.POPRCTNM ={?POPRCTNM}

Command for the Receiving Journal:

SELECT    
dbo.POP30390.POPRCTNM, dbo.GL00105.ACTNUMST,
dbo.POP30390.CRDTAMNT, dbo.POP30390.ORCRDAMT,
dbo.POP30390.DEBITAMT, dbo.POP30390.ORDBTAMT,
dbo.POP30390.CURNCYID, dbo.GL00100.ACTDESCR
FROM         dbo.POP30390
INNER JOIN dbo.GL00105 ON dbo.POP30390.ACTINDX = dbo.GL00105.ACTINDX
INNER JOIN dbo.GL00100 ON dbo.POP30390.ACTINDX = dbo.GL00100.ACTINDX

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