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