Sales Register Report: Great Plains Dynamics GP Reporting Samples
If you are controller or responsible for tying numbers from General Ledger back to Sales Journal, then you may like to have one place, where you see your Monthly or Quarterly Sales Ledger distribution in General Ledger.
Contributed article
Key takeaways
- 01The report summarizes Accounts Receivable account Debits for specific transaction types.
- 02The report should exclude payments from the Accounts Receivable Debit summary.
- 03SQL stored procedures are used to build the Sales Journal Register report.
- 04The GL20000 table includes Customer ID, Customer Name, and Sales Invoice or Return number.
- 05The article provides guidance for those not familiar with Dynamics GP or Crystal Reports.
Also,
we heard observations from corporate accounting people, as well as from Dynamics GP functional consultants that this report is typically difficult exercise if tried to be designed in FRx or Dynamics GP Report Writer (assuming that you are not taking Great Plains Dexterity customization route). In this small publication we would like to give you hints on how to design this report in Crystal Reports:
1. Reporting Logic. This report should summarize all Accounts Receivable account Debits (with Returns, counted as negative debits), but only for transactions with SJ and RMJ types: meaning only Sales Order Processing Invoices, Returns and Receivables Management module Invoices, Debit memos, Credit memos. This AR Debit summary should not include any payments. Now from the Credit side you should look at all Sales related accounts: Sales, Freight, Miscellaneous Charges, Sales Tax Payable, VAT payable
2. Under the hood. We assume that you are on Microsoft SQL Server 2005 or 2000 DB platform and you will be building SQL query as SQL stored procedure and will base Sales Journal Register report on the SQL stored procedure, where you will supply Date From and Date To parameters fields. Ideal life looks very attractive, but in real life you see various alterations to SOP and RM documents distributions. This is why we recommend you to build SQL Select statement to get a list of all the accounts, participating in Sales Invoices and Returns distribution. You can get it from GL20000 table, again making restrictions on Journal type: SJ and RMJ. Accounts numbers and names should be queried to GL00100 table – Account Master
3. Sales Journal Transactions Audit. Include Select statement details in hidden body section of the report with the option Drill Down OK. If you review the structure of GL20000 table, you will realize that it includes Customer ID in ORMSTRID field, Customer Name in ODMSTRNM and Sales Invoice or Return number in ORDOCNUM field
4. Let us know. The paragraphs above should give you very good start. If you feel, that you are not really Dynamics GP technical consultant or Crystal Reports Developer, feel free to call or email us
Common questions
What transaction types should be included in the Accounts Receivable Debit summary?
Include only Sales Order Processing Invoices, Returns, Receivables Management module Invoices, Debit memos, and Credit memos.
What should be excluded from the Accounts Receivable Debit summary?
Payments should be excluded from the Accounts Receivable Debit summary.
What database platform is assumed for building the report?
The report assumes the use of Microsoft SQL Server 2005 or 2000.