Inactivating a Customer with Open Transactions - Work Around Solution (SQL Tool)

It's a common case in Dynamics GP that you want to inactivate a customer, although, you always receive the warning message that "You cannot inactivate a customer record with un-posted or posted transactions."

Here is the scenario:

A customer with open transactions has a zero balance, all invoices are paid. Although, the system doesn't allow inactivating a customer. 

Customer Maintenance Window

Here is the customer aging report: 

Customer Aging Report

Here is the warning message:

Inactive versus on Hold | Customer Card Definition

There is a huge difference between having a customer "Inactive" or "On Hold", here is the definition:

• Inactivating a customer doesn't allow entering any type of transactions, the customer record is blocked until reactivation is applied.
• Having a customer on hold means preventing any further sales transactions even if the customer has an unpaid balance. Meanwhile, payments can be processed for this customer.

Cause

The system doesn't allow inactivating a customer unless the following conditions are met:

• The customer has a zero balance
• All invoices are paid 
• Paid Transaction Removal are processed in order to archive all paid transactions and move them to history.

Resolution

The resolution is primarily to have all transactions paid, and move them to history by running the "Paid Transaction Removal" routine. Although, this is not applicable in several times especially when a huge amount of transactions are processed on a daily bases. 

Work Around Solution - SQL Script

The following work around solution can be applied on Dynamics GP in order to inactivate a customer regardless of all of the conditions above. Here are the proposed resolution:

1. Putting the customer on-hold
2. The script below will force the customer to be set to inactive regardless of any of the conditions
3. When the hold is unchecked, the customer will be set back to active

Important Note !  
                               Do not run the script below on production environment without prior testing

----The following table is an Audit table in order to keep track of the changes applied on the customer card as related specifically to the "Hold" process

CREATE TABLE [dbo].[DI_MasterFilesAuditLog](

       [Event] [nvarchar](max) NULL,

       [Entity_Type] [nvarchar](max) NULL,

       [EntityID] [nvarchar](max) NULL,

       [Entity_Description] [nvarchar](max) NULL,

       [Creation/Modification_Date] [date] NULL,

       [Created/Modifiedby] [nvarchar](max) NULL,

       [Application_Name] [nvarchar](max) NULL,

       [HostName] [nvarchar](max) NULL,

       [StatusBefore] [nvarchar](max) NULL,

       [StatusAfter] [nvarchar](max) NULL,

       [Dex_row_TS] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

----The following script creates a trigger on the RM00101 in order to process the inactive/active status of the customer according to the on-hold status

CREATE TRIGGER [dbo].[DI_RM_Update_MasterFilesChecker] ON [dbo].[RM00101]

    AFTER UPDATE

AS

    IF UPDATE(HOLD)

        BEGIN

            DECLARE @CustomerNumber NVARCHAR(MAX) ,

                @CustomerName NVARCHAR(MAX) ,

                @ModifiedDate DATE ,

                @Modifiedby NVARCHAR(MAX) ,

                @Active_Before NVARCHAR(MAX) ,

                @Active_After NVARCHAR(MAX),

                           @Hold_Before   NVARCHAR(MAX),

                           @Hold_After    NVARCHAR(MAX)

            SET @CustomerNumber = ( SELECT  CUSTNMBR

                                    FROM    INSERTED

                                  )

                                         

            SET @CustomerName = ( SELECT    CUSTNAME

                                  FROM      inserted

                                )

            SET @ModifiedDate = ( SELECT    MODIFDT

                                  FROM      inserted

                                )

          

            SET @Active_Before = ( SELECT    INACTIVE

                                  FROM      deleted

                                )

            SET @Active_After= ( SELECT INACTIVE

                                 FROM   inserted

                               )

          

            SET @Hold_Before= ( SELECT    HOLD

                                  FROM      deleted

                                )

            SET @Hold_After= ( SELECT HOLD

                                 FROM   inserted

                               )

                   

                    UPDATE RM00101 SET INACTIVE = @Hold_After

                                                    

            INSERT  [DI_MasterFilesAuditLog]

                    SELECT  'Update' ,

                            'Customer' ,

                            @CustomerNumber ,

                            @CustomerName ,

                            @ModifiedDate ,

                            SUSER_SNAME() ,

                            CASE APP_NAME()

                              WHEN '' THEN 'Microsoft Dynamics GP'

                              ELSE APP_NAME()

                            END ,

                            HOST_NAME() ,

                            CASE @Hold_Before

                              WHEN 0 THEN 'Active'

                              WHEN 1 THEN 'Inactive'

                              ELSE ''

                            END ,

                            CASE @Hold_After

                              WHEN 0 THEN 'Active'

                              WHEN 1 THEN 'Inactive'

                              ELSE ''

                            END ,

                            GETDATE()

                    FROM    INSERTED

        END

GO

 



Best Regards,
Mahmoud M. AlSaadi