Friday, April 30, 2010

Dynamics GP Reporting Series: Sales Invoice (Receivables Management)

For Sales invoices that does not contains items, you might need to print invoice using Transactions>> Sales>> Transaction Entry, the good thing is, MS guys already created view for the Receivables Transactions, below the SQL command needed:

SQL Command:

dbo.RM10301.DOCTYPE, dbo.RM10301.DOCDATE,
dbo.RM00102.ADDRESS1, dbo.RM00102.ADDRESS2,
dbo.RM00102.ADDRESS3, dbo.RM00102.CITY,
dbo.RM10301.DOCDESCR, dbo.RM10301.CUSTNMBR,
dbo.RM10301.CUSTNAME, dbo.RM10301.SLPRSNID,
dbo.RM10301.SHIPMTHD, dbo.RM10301.PYMTRMID,
dbo.RM10301.DOCAMNT, dbo.RM10301.SLSAMNT,
dbo.RM10301.MISCAMNT, dbo.RM10301.TAXAMNT,
dbo.RM10301.FRTAMNT, dbo.RM10301.TRDISAMT,
dbo.RM10301.CASHAMNT, dbo.RM10301.CHEKAMNT,
dbo.RM10301.CRCRDAMT, dbo.RM10301.CURNCYID,
dbo.RM10301.DOCNUMBR, dbo.ReceivablesTransactions.[Originating Cash Amount],
dbo.ReceivablesTransactions.[Originating Check Amount],
dbo.ReceivablesTransactions.[Originating Credit Card Amount],
dbo.ReceivablesTransactions.[Originating Current Trx Amount],
dbo.ReceivablesTransactions.[Originating Discount Taken Amount],
dbo.ReceivablesTransactions.[Originating Freight Amount],
dbo.ReceivablesTransactions.[Originating Misc Amount],
dbo.ReceivablesTransactions.[Originating Sales Amount],
dbo.ReceivablesTransactions.[Originating Tax Amount],
dbo.ReceivablesTransactions.[Originating Trade Discount Amount],
dbo.ReceivablesTransactions.[Originating Write Off Amount],
dbo.ReceivablesTransactions.[Currency ID]
FROM         dbo.RM10301
LEFT OUTER JOIN dbo.ReceivablesTransactions
ON dbo.RM10301.DOCNUMBR = dbo.ReceivablesTransactions.[Document Number]
LEFT OUTER JOIN dbo.RM00102 ON dbo.RM10301.CUSTNMBR = dbo.RM00102.CUSTNMBR
AND dbo.RM10301.ADRSCODE = dbo.RM00102.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 original Sales Invoice:

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

1 comment:

Santosh Surti said...

This is very usefull query.

Do you have POP related sql query? I mean I need to have POP analysis which display PO, GRI,Invoice, Return etc.. in one single query. This will help to analyse PO data. This will also help me to identify what is my GRN (Accrual) balance.
Please share me if you have such sql query ready.
Regards and Thanks,

Related Posts:

Related Posts with Thumbnails