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:

 image

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, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

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:

 image

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, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

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:

 image

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, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

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:

 image

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, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

Dynamics GP Reporting Series: Sales Cash Receipt

As a part of the Receivables Management Module, we’ll need to print a cash receipt for the payments we receive from our customers, and due to many requests from the clients, I have included the distribution journal for the transaction, below the SQL command needed:

SQL Command:

SELECT    
dbo.SY00500.NUMOFTRX, dbo.SY00500.BCHCOMNT,
dbo.SY00500.BCHTOTAL, dbo.SY00500.CNTRLTOT,
dbo.SY00500.CNTRLTRX, dbo.SY00500.BACHFREQ,
dbo.SY00500.BACHDATE, dbo.SY00500.APRVLUSERID,
dbo.SY00500.APPRVLDT, dbo.SY00500.APPROVL,
dbo.SY00500.SERIES, dbo.RM10201.CUSTNMBR,
dbo.RM00101.CUSTNAME, dbo.RM10201.DOCNUMBR,
dbo.RM10201.DOCDATE, dbo.RM10201.TRXDSCRN,
dbo.RM10201.GLPOSTDT, dbo.RM10201.ORTRXAMT,
dbo.RM10201.WROFAMNT, dbo.RM10201.DISTKNAM,
dbo.RM10201.CURTRXAM, dbo.RM10201.CHEKNMBR,
dbo.GL00105.ACTNUMST, dbo.GL00100.ACTDESCR,
dbo.GL00100.ACTINDX, dbo.RM10101.CRDTAMNT,
dbo.RM10101.DEBITAMT, dbo.RM10101.ORDBTAMT,
dbo.RM10101.ORCRDAMT, dbo.RM10201.BACHNUMB,
dbo.RM10101.DISTTYPE, dbo.RM10201.CURNCYID
FROM        
dbo.GL00100
INNER JOIN dbo.RM10101 ON dbo.GL00100.ACTINDX = dbo.RM10101.DSTINDX
INNER JOIN dbo.GL00105 ON dbo.GL00100.ACTINDX = dbo.GL00105.ACTINDX
RIGHT OUTER JOIN dbo.RM10201
INNER JOIN dbo.SY00500 ON dbo.RM10201.BCHSOURC = dbo.SY00500.BCHSOURC
AND dbo.RM10201.BACHNUMB = dbo.SY00500.BACHNUMB
INNER JOIN dbo.RM00101 ON dbo.RM10201.CUSTNMBR = dbo.RM00101.CUSTNMBR
ON dbo.RM10101.DOCNUMBR = dbo.RM10201.DOCNUMBR

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:

 image

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, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

How to Handle Jordanian “5% Vendors Service Tax” in Microsoft Dynamics GP??

I got many requests from my customers on how to automatically manage the newly added governmental 5% Vendors Service Tax on all service vendors payments, with few hints from friends and researches, I been able to handle this tax using the built in Withholding Tax, below are the steps required to setup the tax:

1. Go to “Microsoft Dynamics GP menu >> Tools >> Setup >> Company >> Company” and click on “Options”:

image

Setup the tax committee as a vendor in your AP and fill it in the above form.

Go to “Cards >> Purchasing >> Vendor” and click on the added “Withholding” button, mark this vendor to be subject to the withholding deduction and define the deduction percent:

image

Now go to AP Transaction Entry “Transactions >> Purchasing >> Transaction Entry” and select your vendor, where a message will popup checking if this invoice will be subject to withholding:

image

So far, nothing will be affected financially but the document will be marked for deduction on the next payment.

To pay this vendor, go to “Transactions >> Purchasing >> Manual Payments” and select your vendor, then fill the amount you need to pay, and apply this payment the to invoice, where the system will automatically calculate the withholding amount as shown below:

image

Open the distribution and make sure that the Withholding Account “WH” was added to your invoice.

image

Post the payment and enjoy having the tax automatically calculated.

Regards,

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

Smart List Excel Export

Great article by Patrick from MS Developer Support, he explained a hidden DEX.ini switch that reduce the time needed to export data from Smart List to Excel, by his words:

I pressed the Export to Excel button and Smartlist exported the 10,000 rows by 7 columns in 1 minute, 55 seconds (115 seconds).

Since this switch is read on the fly, I didn’t have to restart Dynamics or even refresh the Smartlist.  I just pushed the Export to Excel button again.  How long was the export this time?  Would you believe 9 seconds?

This magical feature will be enhanced by just adding the following switch to your DEX.ini file:

SmartlistEnhancedExcelExport=TRUE

Take a look to the complete explanation about the switch in the Developing for Dynamics GP Blog.

Regards,

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