Dynamics GP 2018 R2 - Transaction Level Posting allows to Post "Through" General Ledger

This has always been a mystery to some clients, and a problem to multiple other clients, the fact that Dynamics GP prevents transaction level posting from posting "Through" general ledger is no longer true. 

With t the release of Dynamics GP 2018 R2, the system now allows end users to post transactions without a batch, although, it will post through GL rather than the need to post these transactions again from the general ledger level.

In order to allow transaction level posting with "Post Through" option, open the posting setup window from Microsoft Dynamics GP > Tools > Setup > Posting > Posting

A new option is added under "Allow Transaction Posting", which is: "Post Through General Ledger Files"

Here is the screen shot from the Posting Setup window:

Posting Setup Window - Post Through General Ledger Files

Below is a list of windows which have an option to "Allow Post Through with Transaction Level Posting" 

Sales Transaction Module:

• Sales Deposits
• Invoice Entry
• Sales Transaction Entry
• Receivables Sales Entry
• Receivables Cash Receipts
• Receivables Apply Doc
• Voided Transaction Maintenance

 Purchase Order Processing 

• Receiving Transaction Entry
• Purchasing Invoice Entry
• Returns Transaction Entry
• Payable Transaction Entry
• Payment Entry
• Apply To
• Purchasing Prepayments

Inventory Management

• Transaction Entry
• Transfer Entry
• Assembly Entry

Payroll

• Manual Checks
  

Project Accounting 

• Time sheet Entry
• Equipment Log Entry
• Misc. Log Entry
• Employee Expense Entry
• Billing Entry
• Revenue Recognition Entry
• Inventory Transfer Entry
• PA Returns Transaction Entry

Best Regards, 

Mahmoud M. AlSaadi

Smartlist Export Failure - Excel Cannot open file because the File Fomrat or File Extension is not valid

I have received recently a quite "weird" support case, in which the smart list is failing to export data into Excel addressing that Excel can not open the file. Here is the exact error message:

"Excel Cannot open file because the  file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file"

Smart list export error

After several troubleshooting attempts suspecting that Excel is the source root cause, it rather appeared that "data" which is being exported is the root-cause of this error. It was found out that the data contains special characters.  Here is the screen shot of the "corrupted" data record which was failing the whole process:

An arrow Symbol (Special Character)

Best Regards, 

Mahmoud M. AlSaadi

Workflow Delegation Cleanup - Delegations are being removed automatically

In workflow 2.0, Delegations of the workflow tasks are managed by the users per workflow type to ensure that proper actions are taken when a user is out of the office. A previous associated topic has shed a light on an issue with the delegation and a work-around solution was provided on Workflow Delegations under User Preferences, Access Denied for Limited Users. In this post, I am shedding a light on another issue in which the delegations submitted by the users are removed automatically by the system by midnight, everyday !

Workflow Delegation Overview

Delegations are managed by the user through the "Workflow Delegations" on the user preferences window, the user can create automatic delegations for any or all of the workflow types, so that specific users are alternatively selected by the system when a new task is received.

Workflow Delegations

The delegations are recorded in WF40500 (Workflow User Delegation Master) and WF40510 (Workflow User Delegation Lines) as shown below:

Workflow Delegation Tables

Workflow Delegation Company Cleanup

There is a job which purpose is "to remove out of office delegation records that are tied to invalid AD users.  It will also turn off Delegation for records that have expired."

Scan for Invalid Active Directory Users and Expired Delegations (all companies)

The job is scheduled to run on a daily bases once at 12:00 am, it primarily has one step which is running a stored procedure wfOutOfOfficeDelegationCleanupCompany in Dynamics DB. In Turn, this stored procedure checks for the available companies within Dynamics and clear all delegations regardless of the fact that they are active or inactive.

>> Therefore, any delegation that is submitted by any user will be deleted automatically at the end of every day, whether it is expired or not. 

Here is the original stored procedure which runs through the workflow delegation tables, and remove all delegations for all the users [wfOutOfOfficeDelegationCleanup]

 

CREATE PROCEDURE [dbo].[wfOutOfOfficeDelegationCleanup]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @DomainUserName CHAR(255);

DECLARE @UsersListGuid CHAR(37) ,

    @ActualUsersListGuid CHAR(37);

DECLARE Delegation_Users CURSOR LOCAL FAST_FORWARD

FOR

    SELECT  DomainUserName ,

            UsersListGuid

    FROM    WF40500;

OPEN Delegation_Users;

FETCH NEXT FROM Delegation_Users INTO @DomainUserName, @UsersListGuid;

WHILE @@FETCH_STATUS = 0

    BEGIN

        SELECT  @ActualUsersListGuid =

                                  RTRIM(UPPER(ISNULL(( SELECT

                    DYN15.dbo.GetObjectGuidByUser(@DomainUserName,

                    1, 0)

                                                        ), '')));

        IF RTRIM(UPPER(@UsersListGuid)) <> RTRIM(@ActualUsersListGuid)

            BEGIN

                DELETE  FROM WF40510

                WHERE   UsersListGuid = @UsersListGuid;

                DELETE  FROM WF40500

                WHERE   UsersListGuid = @UsersListGuid;

            END;

        FETCH NEXT FROM Delegation_Users INTO @DomainUserName,

            @UsersListGuid;

    END;

CLOSE Delegation_Users;

DEALLOCATE Delegation_Users;

DECLARE Delegation_Lines CURSOR LOCAL FAST_FORWARD

FOR

    SELECT  DomainUserName

    FROM    WF40510;

OPEN Delegation_Lines;

FETCH NEXT FROM Delegation_Lines INTO @DomainUserName;

WHILE @@FETCH_STATUS = 0

    BEGIN

        IF DYN15.dbo.IsValidUserByUser(@DomainUserName, 1, 0) = 0

            BEGIN

                DELETE  FROM WF40510

                WHERE   DomainUserName = @DomainUserName;

            END;

        FETCH NEXT FROM Delegation_Lines INTO @DomainUserName;

    END;

CLOSE Delegation_Lines;

DEALLOCATE Delegation_Lines;

DELETE  FROM WF40500

WHERE   RTRIM(UPPER(UsersListGuid)) NOT IN (

        SELECT  RTRIM(UPPER(UsersListGuid))

        FROM    WF40510 );

DECLARE @UserDelegationDates TABLE

    (

        ENDDATE DATETIME ,

        UsersListGuid CHAR(37)

    );

INSERT  INTO @UserDelegationDates

        SELECT  MAX(ENDDATE) AS ENDDATE ,

                UsersListGuid

        FROM    WF40510

        GROUP BY UsersListGuid;

UPDATE  WF40500

SET     WF_Auto_Delegate_Tasks = 0

WHERE   WF_Auto_Delegate_Tasks = 1

        AND UsersListGuid IN

              ( SELECT   UsersListGuid

                     FROM     @UserDelegationDates

            WHERE    ENDDATE < SYSDATETIME() );

END;  

    GO

There are two cursors in the code above, to go through all the records in both WF40500 and WF40510 tables, and remove all records. Therefore, the delegations are removed on a daily bases regardless of its expiration date.

Workflow Delegation Cleanup - Solution 

You could either disable the SQL job, or run the query below on each of the company databases in order to modify the stored procedure responsible for the delegation removal, and ensure that only expired delegations are removed. Here is the modified version of the same stored procedure.

/*-----------------------------------------------------------------------------

Modified by: Mahmoud M. AlSaadi

The stored procedure is originally packed with Dynamics GP. The script below 
is a modification in order to ensure that only expired delegations are removed.

The script has been tested on a very limited sample data.

Revision History:

Revision No.            Revision Date    Description

1                       10/03/2016      Modified Version

------------------------------------------------------------------------------- */

ALTER PROCEDURE [dbo].[wfOutOfOfficeDelegationCleanup]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @Today AS DATETIME;

SET @Today = CONVERT(DATE, GETDATE());

SET NOCOUNT ON;

DECLARE @DomainUserName CHAR(255);

DECLARE @UsersListGuid CHAR(37) ,

    @ActualUsersListGuid CHAR(37);

DECLARE Delegation_Users CURSOR LOCAL FAST_FORWARD

FOR

    SELECT  A.DomainUserName ,

            A.UsersListGuid

    FROM    WF40500 AS A

            LEFT OUTER JOIN WF40510 AS B ON A.UsersListGuid = B.UsersListGuid

    WHERE   B.ENDDATE < @Today;

                    

OPEN Delegation_Users;

FETCH NEXT FROM Delegation_Users INTO @DomainUserName, @UsersListGuid;

WHILE @@FETCH_STATUS = 0

    BEGIN

        SELECT  @ActualUsersListGuid =

              RTRIM(UPPER(ISNULL(( SELECT

        DYNAMICS.dbo.GetObjectGuidByUser(@DomainUserName,

        1, 0)

        ), '')));

        IF RTRIM(UPPER(@UsersListGuid)) <> RTRIM(@ActualUsersListGuid)

            BEGIN

                DELETE  FROM WF40510

                WHERE   UsersListGuid = @UsersListGuid;

                DELETE  FROM WF40500

                WHERE   UsersListGuid = @UsersListGuid;

            END;

        FETCH NEXT FROM Delegation_Users INTO @DomainUserName,

            @UsersListGuid;

    END;

CLOSE Delegation_Users;

DEALLOCATE Delegation_Users;

DECLARE Delegation_Lines CURSOR LOCAL FAST_FORWARD

FOR

    SELECT  DomainUserName

    FROM    WF40510

    WHERE   ENDDATE < @Today;

OPEN Delegation_Lines;

FETCH NEXT FROM Delegation_Lines INTO @DomainUserName;

WHILE @@FETCH_STATUS = 0

    BEGIN

        IF DYNAMICS.dbo.IsValidUserByUser(@DomainUserName, 1, 0) = 0

            BEGIN

                DELETE  FROM WF40510

                WHERE   DomainUserName = @DomainUserName;

            END;

        FETCH NEXT FROM Delegation_Lines INTO @DomainUserName;

    END;

CLOSE Delegation_Lines;

DEALLOCATE Delegation_Lines;

DELETE  FROM WF40500

WHERE   RTRIM(UPPER(UsersListGuid)) NOT IN (

        SELECT  RTRIM(UPPER(UsersListGuid))

        FROM    WF40510 );

DECLARE @UserDelegationDates TABLE

    (

        ENDDATE DATETIME ,

        UsersListGuid CHAR(37)

    );

INSERT  INTO @UserDelegationDates

        SELECT  MAX(ENDDATE) AS ENDDATE ,

                UsersListGuid

        FROM    WF40510

        GROUP BY UsersListGuid;

UPDATE  WF40500

SET     WF_Auto_Delegate_Tasks = 0

WHERE   WF_Auto_Delegate_Tasks = 1

        AND UsersListGuid IN ( SELECT   UsersListGuid

                                FROM     @UserDelegationDates

                                WHERE    ENDDATE < SYSDATETIME() );

END;  

GO

Best Regards, 

Mahmoud M. AlSaadi