Before Yearend Closing Check – Retained Earnings Exception Report

 

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 the
Select Account Currencies window, the Multicurrency Retained Earnings Exception
Report also will be printed. This report displays all retained earnings accounts for
which multicurrency transactions were closed, even though the currency for the
multicurrency amounts wasn’t assigned to the retained earnings account. If you
didn’t intend to close these accounts to the retained earnings account, you can use
this report to determine the amounts you’ll need for correcting these transactions.

Hope that this helps.


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Dynamics GP Data Level Security

 

I am getting requests about this subject almost from all my customers, therefore we at Dynamics Innovations have decided to develop an add-on for Dynamics GP to fulfill this request a month ago and my development team is currently in the process of developing the final touches on the utility.

The add-on is basically works on creating new Dynamic lookup that replaces traditional Dynamics GP lookups, the same lookup is used for all kind of data inside Dynamics GP and the rows are filter dynamically at runtime based on user privileges which to be setup by creating a specific view for each user/role per each business entity and the system will display only data from the linked view:

image

By achieving this, the system administrator will be able to customize columns names and columns to be displayed by using SQL views or direct SQL queries and rows to be displayed, and the lookup will automatically filter rows using “like/contains” operator based on the user selection and based on the clicked column:

image

The data load were enhanced to load data into .Net data grid that pulls rows from database directly into the user interface, 250,000 records were pulled within less than 7 seconds:

image

In addition to the lookup functionality we have developed a routine that validates user inputs inside GP texts and will make sure that the user selected data is a part of the user query:

image 

The development team is currently working to enhance the navigation buttons as it might violates our security setup.

We been able to automate all the above processes and hanged with a couple of issues, the first is reports and the other is the smart list, and been able to handle smart list security by linking users view with smart list views using smart list builder, so each user will have his smart list configured, not an easy task but doable.

The last part is our challenge for the time being, we cannot figure out a solution for filtering report writer reports using available algorithms, we tried to pass restrictions fields to the reports using VBA but this wasn’t a sustainable solution, we still didn’t give up but we might not be able have this done.


Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Dynamics GP Workflow Backup

 

It has been a very tough month, me and all my team which now are 12 consultants in Dynamics Innovations are overloaded with tasks and projects assignments, I am travelling to Jeddah – KSA the upcoming couple of hours and wanted to post some articles before it leave my remaining rooms of my mind!

I been in a situation the last couple of days were one of my customers had a failure in their portals server that holds the Workflow and the Business Portal, after performing the reinstallation and making sure that all systems are up and running, I realized that the workflow I have created for this clients is a tree with 7 levels in depth and 900 lines of conditions that checks the cost center for each purchase order line and redirect the purchase order for the concerned department manager for first line approval! In addition, imagine the documents that will be lost during the maintenance procedure and the documents that will be stuck between the workflow and Dynamics GP!

I been lucky having a planned backup performed on daily basis, where I been able to restore the backup and proceed with the production environment smoothly.

Worth to mention that when I used the backup set I had for the Workflow database it failed to launch the applications! I had to restore the entire web application from the SharePoint Central Administration portal to get this done.

I wanted to share the script I used to schedule the daily backup, it is basically a batch file that will be called by Windows Scheduler daily and will command the STSADM to perform a backup to specific folder, that will be automatically created based on the backup date, replace “<servername>” with your actual server name and “<port>” with your actual port:

@ECHO OFF
@SET STSADM=”c:\program files\common files\microsoft shared\web server extensions\12\bin\stsadm.exe”
for /F “tokens=1-4 delims=/- ” %%A in (‘date/T’) do set DATE=%%B%%C%%D
for /F “tokens=1-4 delims=:., ” %%a in (‘time/T’) do set TIME=%%a%%b%%c

echo Workflow Backup Operation Started….

%STSADM% -o backup -url http://<servername>:<port>/sites/DynamicsGPworkflow -filename “C:\WSS Backups\Daily Backups\Workflow\Workflow_%DATE%_%TIME%.dat”

echo Business Portal Backup Operation Started….

%STSADM% -o backup -url http://<servername>:<port> -filename “C:\WSS Backups\Daily Backups\Business Portal\BP_%DATE%_%TIME%.dat”

echo Farm Backup Operation Started….

MD “C:\WSS Backups\Daily Backups\Farm\FARM_%DATE%_%TIME%”
%STSADM% -o backup -directory “C:\WSS Backups\Daily Backups\Farm\FARM_%DATE%_%TIME%” -backupmethod full

:End

Paste this into NOTEPAD and save it with “.CMD” extension to be an executable package and schedule the run periodically.

Enjoy!

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Dynamics GP Workflow Installation – The selected website is not a SharePoint site

 

I been in a situation yesterday with one of my clients, they had a problem in their portals server and been getting the above message during the uninstallation of the Workflow of Dynamics GP.

The exact message was: “The selected website is not a SharePoint site”, but if you open the SharePoint Central Administration you finds the web application that holds the Workflow and it is indeed your Workflow web application.

While digging deeply into this, I noticed that the web applications for Workflows are being identified by unique identifier instead of using the web application name, so the mentioned case might became due to an operation that you have deleted the SharePoint web application from SharePoint Central Administration which workflow installed, and re-added the web application with the same name and same configuration, even with the same data as I have restored the web application from a SharePoint backup I had earlier.

The only way to resolve this is by forcing the workflows to be uninstalled without validating the SharePoint, to do this you will need to download Windows Installer Cleanup Utility which could be downloaded from here, and uninstall the workflow application by clicking on uninstall, this will remove the information of the workflow from your system but will not delete workflow files, normally workflow files will be replaced after doing the reinstallation, but the only thing that will have to be removed manually is the registered workflows and features that was deployed over SharePoint, this will need to be done by running following commands in CMD:

cd C:\Program Files\Common files\Microsoft shared\Web server extensions\12\Bin\         
stsadm -o deactivatefeature -name DynamicsApproval -url <DynamicsGPWorkflow URL> –force
stsadm -o deactivatefeature -name DynamicsWorkflowForms -url <DynamicsGPWorkflow URL> -force
stsadm -o deactivatefeature -name DynamicsWorkflow -url <DynamicsGPWorkflow URL> -force
stsadm -o uninstallfeature -name DynamicsApproval -force
stsadm -o uninstallfeature -name DynamicsWorkflowForms -force
stsadm -o uninstallfeature -name DynamicsWorkflow -force

Replace the <DynamicsGPWorkflow URL> placeholder with the path of the DynamicsGPWorkflow site collection. For example, replace the placeholder with the following path:

http:// Server_name /Sites/DynamicsGPWorkflow

Finally you’ll be able to reinstall the workflow with no issues.

Enjoy!

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

PO Receiving Hangs During Posting

 

Sometimes I feel like an idiot while standing behind such a strange issue! I been in a situation with one of my clients where they have a rare case that occur once a week or once every two weeks.

When they post the Receiving Batch, the posting screen is shown and never finalize posting, GP remain responding and the user can work, process monitor has nothing pending and everything looks like the transaction was successfully posted.

When returning back to the Receiving screen you will find that the transaction remain un-posted, posting it again will return tons of errors that duplicate document numbers are exist.

Investigating this issue further shown that transaction has the below impact:

1. GL Batch was created!

2. Payables invoice was created.

3. Receiving Posted Transactions Table Header (POP30300) is affected while Receiving Posted Transactions Line Items Table (POP30330) was not.

4. Inventory Cost Layers Table (IV10200) was partially affected.

5. Inventory Transactions History Table (IV30300) was partially affected.

Client used to perform manual operation like creating an inventory adjustment with differences to fix this miss, but this actually will cover the issue but will not correct the transaction.

Current installation with existing customizations done internally does not allow for application reinstallation specially since the client is using terminal services environment, therefore we been unable to identify the actual reason behind this issue which most probably due to a corrupted dictionaries that cause such an error.

the good thing is I gave them the script below which will remove the effect of posting the transaction from the inventory and allow to repost the transaction which will be posted correctly on the second try! For sure they will still have to manually void the AP invoice and will need to manually delete the GL Journal.

 

CREATE PROCEDURE FIXPOHANG (

@POPRCTNM VARCHAR(500)

)

AS

DECLARE @BACHNUMB VARCHAR(500)

SELECT @BACHNUMB = BACHNUMB FROM POP30300

DELETE FROM POP30300

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30310

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30330

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30700

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30390

WHERE POPRCTNM = @POPRCTNM

DELETE FROM IV30300

WHERE DOCNUMBR = @POPRCTNM

DELETE FROM IV10200

WHERE RCPTNMBR = @POPRCTNM

DELETE FROM SEE30303

WHERE DOCNUMBR = @POPRCTNM

DELETE FROM DYNAMICS..SY00800

WHERE BACHNUMB= @BACHNUMB

DELETE FROM DYNAMICS..SY00801

UPDATE SY00500 SET MKDTOPST=’0′, BCHSTTUS=’0′, USERID=”

WHERE BACHNUMB = @BACHNUMB

 

Hope that this helps people have such an issue.

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Inventory Reset Utility– HITB Did not match Staging Report and actual GL Journals!

 

While doing the inventory reset for one of my clients, I shocked when finalizing the last step when my Historical Inventory Trial Balance did not match the General Ledger upon finalizing the journals posting which cannot be correct, we have initiated the reset process from the beginning to match GL to Inventory but been able to achieve this.

I did the reset for many clients so far without having any issues, however the client has huge number of daily transactions, around 2.5 records in IV30300 per year and has some issues in uncompleted transactions and posting interruption, but this does not represent the fact that the reset utility much handle such an issue.

While investigating the issue further I have found that many transactions in SEE30303 does not have corresponding GL Journals and found that those transactions are actually duplicated, I used the script below to clear all transactions from SEE30303 that does not have corresponding Journal and managed to tie both numbers:

DELETE FROM SEE30303 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL20000)

It worked! Hope that will help the community.

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Vendor Notifications on EFT Bank Transfer

 

I got a request from one of my clients to notify vendors by mail once they release his payment to the EFT bank, where I had to create a trigger on CM20202 to monitor payments and send the mail to the vendor, below the script I used:

Create TRIGGER [dbo].[SendVendorMails]
   ON  [dbo].[CM20202]
   FOR INSERT
AS
BEGIN
 
DECLARE @MAILPROFILE VARCHAR(8000)
DECLARE @ToMAIL  VARCHAR(8000)
DECLARE @MESSAGE     VARCHAR(8000)
DECLARE @HEADER         VARCHAR(8000)

SET @HEADER = ‘Payment Transfer’
SET @MESSAGE = ‘Dear Esteemed Vendor,’ + char(10) + char(10)

+ ‘Kindly be advised that we have processed payment with the amount of (‘ + CONVERT(VARCHAR(500), (SELECT [Checkbook_Amount] FROM INSERTED)) + ‘) to your account. ‘ + char(10) + char(10)
+ ‘Your kind confirmation of subject payment to the following email is highly appreciated (payables@XXXX.com)’ + char(10)
+ char(10)
+ char(10)
+ ‘Regards,’ + char(10) + char(10)
+ ‘Accounts Payable Unit’ + char(10)

SELECT @ToMAIL = COMMENT1 FROM PM00200 WHERE VENDORID = (SELECT [CustomerVendor_ID] FROM INSERTED)
SET @MAILPROFILE        = ‘Administrator’
EXEC msdb.dbo.sp_send_dbmail
@Profile_Name = @MAILPROFILE,
@recipients = @ToMAIL,
@body = @MESSAGE,
@subject = @HEADER;

END

Note: the vendor e-mail address was saved in COMMENT1 field in the vendor card, you can modify the query to pull vendor mail from different field.

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
www.mohdaoud.com