Sunday, January 29, 2012

one of my clients is a constructions firm whose not interested to activate the project accounting and wanted to distribute their project management over analytical accounting dimensions.

Therefore we have implemented Dynamics GP and created dimension for projects to distribute all expenses accounts of analytical accounting codes.

One of their requirements been to have a vendor statement per project which is reasonable for such a line of business, therefore I have created the below view that extracts the information including from AP, GL and AA and wanted to share the idea to avoid reinventing the weel for people who works with the same situation, below is the script I used:

One of my clients requested to reconcile their GL Account Summary to GL Account Details periodically using SQL which is not currently available in Dynamics GP, I suggested to schedule a macro run but they wanted this to be done at the SQL level, therefore I have written the SQL command below to empty GL10110 table and repopulate the records to the tables using figures in GL20000.

Disclaimer: This script works when your fiscal periods are matched with calendar periods only as it uses month of the transaction date as the transaction period, on the other hand, I tested this on limited data, if you find an issue or have any idea for script enhancement, please let feel free to comment.

INSERT INTO [GL10110]([ACTINDX],[YEAR1],[PERIODID],[PERDBLNC],[ACTNUMBR_1],[ACTNUMBR_2],[ACTNUMBR_3],[ACTNUMBR_4],[ACTNUMBR_5],[ACTNUMBR_6],[ACTNUMBR_7],[ACTNUMBR_8],[ACTNUMBR_9],[ACTNUMBR_10],[ACCATNUM],[CRDTAMNT],[DEBITAMT])

I been in a situation where I had to create a report with sub-report in crystal and the report worked perfectly on my machine.

while implementing the report on the client machines I noticed that the sub-report is always empty on all machines and even on the server.

I tried everything with no luck, I even recreated the report with no luck as well, after deep investigations in the issue I have noticed that both Main Report and Sub-Report are using commands and both has the same identical name “command”, changing the command name in the sub-report to “command_1” resolved my issue!!!

Hope that this does not happen to you and helps reducing research time!

For those who are still thinking about when to purchase Dynamics ERP’s, I wanted to remind you that the give me 5 promotion which will allow you to purchase 5 users for 3,000$ is around to expire, and you have less than 22 days to take the decision!

Below what quoted from Microsoft Dynamics Website:

Get 5 users for one low priceTo renew or not renew, that is the question. Your annual maintenance agreement is right around the corner. You know that the money you spend year after year provides little value and keeps you tied to software that hinders your business growth.

Grow Your Business with Microsoft Dynamics ERPMicrosoft Dynamics ERP gives you greater control over and visibility into your entire business. With greater control, you can feel extra confident about the everyday decisions you and your employees make. Decisions that will help improve profitability, turn margins into cash flow, and ultimately drive your business growth.

It has been long time since I been an active online community member as before, as the day has only 24 hours and is really not enough for 45 customers with many GP issues, offline contribution, online community activities, my masters , family, and fun time!!!

However, I am doing my best to recover and follow up with all these! As a start, I wanted to share one thing to be included in your checks before closing your financial year, if you have several currencies linked to your profit and loss accounts, you will need to make sure that all these currencies are linked to the retained earnings account, so the multicurrency management module can work this with no issues.

If you forgot to do so, the system will generate the accounts along with the currencies not linked to the Retained Earnings in a report called “Retained Earnings Exception Report” and the report will be printed automatically.

Nothing to be done from your side at this point as the system will affect the retained earnings with the amounts, but you need to validate your profit/loss balance and make sure that the RE account affected correctly.

Quoting from the multicurrency management printable manual:

If you haven’t assigned all currency IDs to your retained earnings account using theSelect Account Currencies window, the Multicurrency Retained Earnings ExceptionReport also will be printed. This report displays all retained earnings accounts forwhich multicurrency transactions were closed, even though the currency for themulticurrency amounts wasn’t assigned to the retained earnings account. If youdidn’t intend to close these accounts to the retained earnings account, you can usethis report to determine the amounts you’ll need for correcting these transactions.