Monday, May 24, 2010

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

6 comments:

All Cars Removal said...

Get the top cash for old cars in Caboolture and get paid instantly. We offer the free vehicle removals through Caboolture, Qld. Contact now.
scrap car removal brisbane

Janice Berry said...

All Car Removal Brisbane is a fully professional car removal service based in Brisbane. We buy old cars, car scraps, junks, etc. from Brisbane, Sunshine Coast & Gold Coast area, and pay you instant cash up to $9999. Contact us today itself to get a quote.
cash for cars brisbane

Crystal Coleman said...

This is a very useful post, thank you! I agree that innovations are the most important thing for most businesses because with today’s fast-changing world we need to stay on top of the changes if we want to survive.
mazda wreckers brisbane
brisbane wreckers
car wreckers brisbane
wreckers brisbane
auto recyclers brisbane
brisbane holden wreckers
mazda wreckers sunshine coast
4x4 wreckers brisbane
scrap car brisbane
cash for cars brisbane
cash for car gold coast
Best place to scrap a car
cash for junk cars brisbane
toyota wreckers Brisbane
brisbane cash for car
cash for scrap cars brisbane
cash for cars gold coast
nissan wrecker brisbane
Brisbane spare auto part
brisbane spare part auto

Hazel Donovan said...

Excellent post. I will certainly come back to read more of the articles about food. We love to try new receipts.
car removal brisbane
scrap car removal caboolture
car removal brisbane
cash for cars caboolture
caboolture scrap car
caboolture cash for car
cash for car caboolture
car removal caboolture
cash for unwanted cars ipswich
cash for car logan
scrap cars redcliffe
all car removal brisbane
cars removal brisbane
quick cash for cars brisbane
scrap cars brisbane
scrap car removal brisbane
brisbane cash for cars
cash for scrap cars brisbane
cash for scrap cars ipswich
scrap cars for cash brisbane
sell car for cash brisbane
best scrap car removal brisbane
cash for cars brisbane

Hazel Donovan said...

While marketing can be tricky, it is the content that makes the site important. If you provide informative content that serves the needs of your visitors, word will spread.
car removal brisbane
scrap car removal caboolture
car removal brisbane
cash for cars caboolture
caboolture scrap car
caboolture cash for car
cash for car caboolture
car removal caboolture
cash for unwanted cars ipswich
cash for car logan
scrap cars redcliffe
all car removal brisbane
cars removal brisbane
quick cash for cars brisbane
scrap cars brisbane
scrap car removal brisbane
brisbane cash for cars
cash for scrap cars brisbane
cash for scrap cars ipswich
scrap cars for cash brisbane
sell car for cash brisbane
best scrap car removal brisbane
cash for cars brisbane

Julia Roach said...

Some really useful content here. I've been looking for something like this to help with a research piece I've been working on.
cash for cars caboolture
caboolture cash for cars
caboolture scrap car
scrap car removal caboolture
cash for cars brisbane
junk your car for money
junk car buyers
cash for cars Redcliffe
cash for cars ipswich
Cash for cars logan
Cash for car
scrap cars brisbane
car removal brisbane
scrap car removal
used cars caboolture
cash for cars brisbane

Related Posts:

Related Posts with Thumbnails