Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Wednesday, April 29, 2015

Payment Document Management (Sales) Series – Accounting, Legal and System Insights


It is taken for granted that an international system; any system, should provide the company with broad functionalities to automate their daily transactions as derived from the international standards. For that, I find the payment document checks automation is a complex and yet ambiguous area to be covered.
>>Throughout the article, PDC abbreviation will be used to refer to the “post-dated-checks”

I have been previously working on such a project related to PDC (post dated checks) specifically for sales. The reason was simply a “Fraud incident” of thousands of dollars for a big retail company that processes 100+ post dated checks on a daily bases. The concerns were primarily represented in few questions; what are the accounting standards as relating to PDC, the legal references and the reflection of the previous on the system automation. 

It was quite essential to start a basic research about the post dated checks topic in general, in order to conclude how the international standard regulate this process. What was quite difficult is to separate among the legal, accounting and system perspectives of this complicated topic.

In this essence, I am writing down my conclusion about post dated checks including my own perspective of this complex topic, supported with references either from international standards and commercial codes.

Accounting Perspective

The critical question is whether to consider the PDC as cash or account receivable, this will have further implications on both the financial reporting and bookkeeping. Furthermore, and most importantly (when considering PDC for sales), the customer credit limit. If all these implications are not properly managed, it may lead to potential fraud incidents.

Customer Credit limit and PDC

The generally accepted accounting principles (GAAP) considers the post dated check as a “promise to pay”. This means, the PDC should be classified as account receivables rather than cash until the due date stated on the check. In other words, there should not be any journal entry recorded to clear the account receivables and debit the cash.

Post Dated Checks - Consideration

Lets translate the above concept into simple accounting entries that should be recorded accordingly starting from issuing the check until it having it finally settled in the bank account.
>> Remember, this article is considering the (sales – post dated checks)

When the check is issued, there should be two dates which are practically the check date and due date, at this time, the following accounting entry should be recorded:

Account                                 Debit                   Credit
Checks (Clearing Account)          X                         --
Accounts Receivable                  --                         X

When the check is to be settled on its due date, the cash balance should increase with the same amount:

Account                                 Debit                   Credit
Cash Account                            X                          --
Checks (Clearing Account)         --                         X

The entries illustrated above are straightforward and clearly reflect the accounting principle which stats that the post dated check is not “cash”, while it is a promise to pay and should be recorded as account receivable.

Meanwhile, lets get one more factor into our consideration, which is the customer credit limit. In definition, a customer credit limit is “the amount of credit that a financial institution extends to a client. Credit limit also refers to the maximum amount a credit card company will allow someone to borrow on a single card. Credit limits are usually determined based on information contained in the application of the person seeking credit, or that person's credit rating.according to Investopedia, Credit limit definition

Regardless of how you set a specific credit limit for a specific client; which usually considers various methodologies to reach a fair, reasonable and yet appropriate limit between the business and each of their clients, the credit limit is the ultimate balance that a client is allowed to reach, any dollar amount above the credit limit could be considered as a risk that a business is taking.

In this essence, and in the light of the principles and straightforward standards mentioned above, how a post dated check should relate to customer’s credit limit ?

Apparently, when the post dated check is issued, the amount paid by the client is still categorized under the accounts receivable, not cash category. Until the due date, at which the check is finally settled right ? Lets take a practical and yet simple example to make things crystal clear

PDC Case Study:

Suppose you are a central distributer of confectionary goods, selling primarily retail stores who in turn sell your products to the consumers. A specific retail client credit limit is $10,000 and has an open balance of $6000 (as of 1st of January, 2015)

Case Study - Time Line

At the 15th of Jan, the client has paid by check (post dated) an amount of $6000, to be collected after one month from the issuance (due date is 15th of February). At the 1st of February, the client placed an order for $5000. Should this order be fulfilled and shipped to the customer ? or rejected since it exceeds the credit limit ?

The customer credit limit is 10,000. The open balance is $6000 and the PDC amount is $6000. According to the consideration mentioned above, this order should be rejected as it exceeds the credit limit with $ 1000. The client can place an order for only $ 4000 until the PDC amount is settled which will clear both their balance and their credit limit.

Practically, the check amount represents AR, which means that the customer balance is “$ 6000”, they can order up to $ 4000 as long as they order prior to the due date of the check. (Special cases of cashing the post dated check will be explained further under the legal section of this series).

The question remains, should the customer balance be 0 after the post dated check ? or it should remain as $6000 until the cash amount is settled. If it the customer balance is correct to be  zero , what is the allowed invoices for the client at this moment ? Should it be only  $ 4000 or it should be $ 6000 .

>> To be continued …

 

Best Regards,
Mahmoud M. AlSaadi

Monday, April 27, 2015

Management Reporter Data Mart integration with Dynamics GP 2015 | Company Integration

Although this is an oldie piece of information, but it is definitely a “Goldie”. With the release of Management Reporter CU 11 and Dynamics GP 2015, a new feature was introduced to control the integration of newly added companies.

Go to Microsoft Dynamics GP > Tools > Setup > Company > Company.
Click on “Options” to open the “Company Setup Options” window

Company Options

As shown above, there is a separate section for management reporter integration with Dynamics GP when deploying the data mart provider. If these options are unchecked, then the financial data of this company will not be integrated into Management Reporter data mart.

Technical Perspective:

Within the data mart database (by default Management Reporter DM db) , there is a table named “Organization” retrieving the companies to be integrated from Dynamics db according to the configuration above.

Organization Table

Within Dynamics database, the company master table stores all the companies within Dynamics DB. There are two fields corresponding to the configuration within the company setup options, which are:

  • Enable GL Reporting
  • Enable AA Reporting

SY01500

In this essence, you can no longer import companies manually from Management Reporter configuration console, when a new company is added to Dynamics GP, the configuration mentioned above will determine whether this company will be integrated or not.

Best Regards,
Mahmoud M. AlSaadi

Thursday, April 23, 2015

Sales Order Integration–Sales Person and Territory are not populated to Line Items


In one of the old environment GP 10 service pack 5, which is integrated with order-taking system, eConnect showed an abnormal case when importing sales orders into Dynamics GP. It is represented with migrating the sales person assigned to each customer for the SOP header table only without populating the sales person ID into the line item details.

Here is a sample of an imported invoice in which all the details are correct with no issues at all:

Sales Invoice

Sales Transaction Entry window

Clicking the blue arrow next to the customer, will open up the sales customer detail entry in which the sales person ID is populated correctly according to the setup configured on the customer card level

Sales Customer Detail

Sales Customer Detail Entry – Sales Person and Territory

On the other hand, clicking on one of the line item and checking the sales item detail entry shows that no “sales person” no “sales territory” is populated.

Sales Line Item Details

Sales Item Detail Entry = Sales Person and Territory

Concern:

And the question is, what are the consequences for such issue, what if the sales person ID is not populated to the detail level and being properly recorded on the header level ?

The sales amount of the sales person will be recorded correctly, since the total of the sales invoice will be added up to the sales of this sales person, and practically commissions are being calculated properly.

>> The problem is, you will not be able to report how much from a specific SKU this sales person has sold, only total numbers. For instance, the sales line items smart list will report nothing for the sales person, as you cannot filter neither by the territory nor the sales person id.

Issue Identification:

The issue is identified on the SQL level as shown below:


SELECT  *

FROM    ( SELECT    SOPNUMBE ,

                    SOPTYPE ,

                    SLPRSNID ,

                    SALSTERR

          FROM      SOP10100

        ) AS A

        LEFT OUTER JOIN ( SELECT    SOPNUMBE ,

                                    SOPTYPE ,

                                    SLPRSNID ,

                                    SALSTERR

                          FROM      dbo.SOP10200

                        ) AS B ON A.SOPNUMBE = B.SOPNUMBE

                                  AND A.SOPTYPE = B.SOPTYPE

WHERE   ( B.SLPRSNID = ''

          OR B.SALSTERR = ''

        )

        AND ( A.SLPRSNID <> ''

              AND A.SALSTERR <> ''

            )


Test - Sales Person ID mismatching

Matching sales person and territory details – SOP header versus details (SOP10100 and SOP10200)

eConnect is not passing neither the sales person ID nor the territory, leaving the default configuration to take effect once a sales order is imported. The result is having both fields populated in the header only leaving empty sales order details.

Resolution:

Method One

The easy method to get this done is practically to adjust the eConnect and pass both parameters for <taSopHdrIvcInsert> and <taSopLineIvcInsert>

eConnect schema - SOP Header and Detail

Method Two:

Another method is to get this corrected in the work tables of Dynamics GP before being posted, through SQL jobs that tracks down such issues and correct it accordingly.

This can be done practically by proposing two different scenarios, the first one is to build an SQL solution which will track down the issue, correct every single record individually and keep a log for every single correction. The other scenario is to mass update the whole records once at a time.

Now lets start with the first scenario (Individual correction, with Log)

The first step is to create the views which will retrieve the corrupted records (Sales line item details missing sales person and territory data, while header has them populated correctly)

View Definition


CREATE   VIEW [dbo].[V_SalesTransactions_SalesPersonMatching]

AS

    SELECT  *

    FROM    ( SELECT    DOCDATE AS HD_DOCDATE ,

                        SOPTYPE AS HD_SOPTYPE ,

                                         CUSTNMBR,

                        SLPRSNID AS HD_SLPRSNID ,

                        SALSTERR AS HD_SALSTERR ,

                        SOPNUMBE AS HD_SOPNUMBE

              FROM      SOP10100

              WHERE     SOPNUMBE IN ( SELECT    DISTINCT

                                                SOPNUMBE

                                      FROM      SOP10200

                                      WHERE     SLPRSNID = ''

                                                AND SALSTERR = '' )

                        AND dbo.SOP10100.SLPRSNID <> ''

            ) AS A

            LEFT OUTER JOIN ( SELECT    SALSTERR AS DT_SALSTERR ,

                                        SLPRSNID AS DT_SLPRSNID ,

                                        SOPNUMBE AS DT_SOPNUMBE ,

                                        SOPTYPE AS DT_SOPTYPE ,

                                        ITEMNMBR,

                                                                     DEX_ROW_ID

                              FROM      SOP10200

                              WHERE     SLPRSNID = ''

                                        AND SALSTERR = ''

                            ) AS B ON A.HD_SOPNUMBE = B.DT_SOPNUMBE

                                      AND A.HD_SOPTYPE = B.DT_SOPTYP


The next step is to create a log table to keep track of every single update, the old and new values of the sales person id and sales territory fields.

 

Log Table Definition


CREATE TABLE [dbo].[TB_UpdatedSalesPerson_Log]

    (

      [SOPTYPE] [smallint] NULL ,

      [SOPNUMBE] [char](21) NULL ,

      [CUSTNMBR] [char](21) NULL ,

      [ITEMNMBR] [char](31) NULL ,

      [OLD_SLPRSNID] [char](15) NULL ,

      [OLD_SALSTERR] [char](15) NULL ,

      [Updated_SLPRSNID] [char](15) NULL ,

      [Updated_SALSTERR] [char](15) NULL ,

      [Dex_Row_ID] [int] NULL ,

      [TimeStamp] [datetime] NULL

    )

 


 

The next step is to build the SQL job which will check the imported data on frequently and keep track of any errors which occurs in the log table

 

SQL Job Definition
      Note:
      Change the database included under “@database_name = N'TWO'”


USE [msdb]

GO

 

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT  @ReturnCode = 0

 

IF NOT EXISTS ( SELECT  name

                FROM    msdb.dbo.syscategories

                WHERE   name = N'[Uncategorized (Local)]'

                        AND category_class = 1 )

    BEGIN

        EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',

            @type = N'LOCAL', @name = N'[Uncategorized (Local)]'

        IF ( @@ERROR <> 0

             OR @ReturnCode <> 0

           )

            GOTO QuitWithRollback

 

    END

 

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'Jb_Process_SalesPersonMismatching',

    @enabled = 1, @notify_level_eventlog = 0, @notify_level_email = 0,

    @notify_level_netsend = 0, @notify_level_page = 0, @delete_level = 0,

    @description = N'No description available.',

    @category_name = N'[Uncategorized (Local)]', @owner_login_name = N'sa',

    @job_id = @jobId OUTPUT

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

 

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,

    @step_name = N'Step One', @step_id = 1, @cmdexec_success_code = 0,

    @on_success_action = 1, @on_success_step_id = 0, @on_fail_action = 2,

    @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0,

    @os_run_priority = 0, @subsystem = N'TSQL',

    @command = N'

IF EXISTS ( SELECT  *

            FROM    V_SalesTransactions_SalesPersonMatching )

    BEGIN

       

        DECLARE @SOPTYPE AS NVARCHAR(MAX) ,

            @SOPNUMBE AS NVARCHAR(MAX) ,

            @CUSTNMBR AS NVARCHAR(MAX) ,

            @ITEMNMBR AS NVARCHAR(MAX) ,

            @Old_SLPRSNID AS NVARCHAR(MAX) ,

            @Old_SALSTERR AS NVARCHAR(MAX) ,

            @Updated_SLPRSNID AS NVARCHAR(MAX) ,

            @Updated_SALSTERR AS NVARCHAR(MAX) ,

            @Dex_ROW_Id AS INT

             

        SET @DEX_ROW_ID = ( SELECT TOP 1

                                    DEX_ROW_ID

                            FROM    V_SalesTransactions_SalesPersonMatching

                          )

        SET @SOPTYPE = ( SELECT DT_SOPTYPE

                         FROM   V_SalesTransactions_SalesPersonMatching

                         WHERE  DEX_ROW_ID = @DEX_ROW_ID

                       )

        SET @SOPNUMBE = ( SELECT    DT_SOPNUMBE

                          FROM      V_SalesTransactions_SalesPersonMatching

                          WHERE     DEX_ROW_ID = @DEX_ROW_ID

                        )

        SET @CUSTNMBR = ( SELECT    CUSTNMBR

                          FROM      V_SalesTransactions_SalesPersonMatching

                          WHERE     DEX_ROW_ID = @DEX_ROW_ID

                        )

        SET @ITEMNMBR = ( SELECT    ITEMNMBR

                          FROM      V_SalesTransactions_SalesPersonMatching

                          WHERE     DEX_ROW_ID = @DEX_ROW_ID

                        )

        SET @Old_SLPRSNID = ( SELECT    DT_SLPRSNID

                              FROM      V_SalesTransactions_SalesPersonMatching

                              WHERE     DEX_ROW_ID = @DEX_ROW_ID

                            )

        SET @Old_SALSTERR = ( SELECT    DT_SALSTERR

                              FROM      V_SalesTransactions_SalesPersonMatching

                              WHERE     DEX_ROW_ID = @DEX_ROW_ID

                            )

        SET @Updated_SLPRSNID = ( SELECT    HD_SLPRSNID

                                  FROM      V_SalesTransactions_SalesPersonMatching

                                  WHERE     DEX_ROW_ID = @DEX_ROW_ID

                                )

        SET @Updated_SALSTERR = ( SELECT    HD_SALSTERR

                                  FROM      V_SalesTransactions_SalesPersonMatching

                                  WHERE     DEX_ROW_ID = @DEX_ROW_ID

                                )

 

 

        UPDATE  SOP10200

        SET     SALSTERR = @Updated_SALSTERR ,

                SLPRSNID = @uPDATED_SLPRSNID

        WHERE   Dex_Row_ID = @Dex_Row_ID

 

 

        INSERT  INTO TB_UpdatedSalesPerson_Log

        VALUES  ( @SOPTYPE, @SOPNUMBE, @CUSTNMBR, @ITEMNMBR, @Old_SLPRSNID,

                  @Old_SALSTERR, @Updated_SLPRSNID, @Updated_SALSTERR,

                  @DEX_ROW_ID, GETDATE() )

    END', @database_name = N'TWO', @flags = 0

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId,

    @name = N'Default', @enabled = 1, @freq_type = 4, @freq_interval = 1,

    @freq_subday_type = 2, @freq_subday_interval = 10,

    @freq_relative_interval = 0, @freq_recurrence_factor = 0,

    @active_start_date = 20150422, @active_end_date = 99991231,

    @active_start_time = 0, @active_end_time = 235959,

    @schedule_uid = N'e5712bb7-9053-42c1-84ae-fe65f0f50315'

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId,

    @server_name = N'(local)'

IF ( @@ERROR <> 0

     OR @ReturnCode <> 0

   )

    GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF ( @@TRANCOUNT > 0 )

    ROLLBACK TRANSACTION

EndSave:

 

GO


 

As you process sales orders, the log details will be recorded for every single update statement as illustrated below: 

 

Log table

 

The other scenario which comes with a better performance, but yet miss the structured log of the first scenario is to update all mismatched records according to the view mentioned above


Mass update mismatch records


IF EXISTS ( SELECT  *

            FROM    V_SalesTransactions_SalesPersonMatching )

 UPDATE  SOP10200

 SET    

 SLPRSNID =

( SELECT HD_SLPRSNID

    FROM   V_SalesTransactions_SalesPersonMatching

    WHERE 

       V_SalesTransactions_SalesPersonMatching.DT_SOPNUMBE = dbo.SOP10200.SOPNUMBE

AND V_SalesTransactions_SalesPersonMatching.DT_SOPTYPE = dbo.SOP10200.SOPTYPE

AND V_SalesTransactions_SalesPersonMatching.DEX_ROW_ID = dbo.SOP10200.DEX_ROW_ID

) ,

 SALSTERR =

( SELECT HD_SALSTERR

    FROM   V_SalesTransactions_SalesPersonMatching

    WHERE 

       V_SalesTransactions_SalesPersonMatching.DT_SOPNUMBE = dbo.SOP10200.SOPNUMBE

AND V_SalesTransactions_SalesPersonMatching.DT_SOPTYPE = dbo.SOP10200.SOPTYPE

AND V_SalesTransactions_SalesPersonMatching.DEX_ROW_ID = dbo.SOP10200.DEX_ROW_ID

)

 WHERE   DEX_ROW_ID IN ( SELECT DISTINCT

                                DEX_ROW_ID

                         FROM   

                         V_SalesTransactions_SalesPersonMatching )


 

Summary

Method which builds a database solution to correct imported records has two option; an option for correcting individual records (scenario one) or to mass correct records (scenario two)

 

For scenario one:

  • The individual records in SOP10200 will be revised and logically tested to check whether an update is required or not.
  • An update on SOP10200 is run to match the sales person ID and territory ID with the ones in the SOP10100
  • Log is kept accordingly for all the changes.

Script required for Scenario one:

  • View definition , V_SalesTransactions_SalesPersonMatching
  • Table log definition, TB_UpdatedSalesPerson_Log
  • SQL Job, Jb_Process_SalesPersonMismatching

 

For scenario two:

  • The individual records in SOP10200 will be revised and logically tested to check whether an update is required or not
  • Mass update is performed on all the lines at once
  • No log is kept

Scripts required for Scenario Two:

  • View definition, V_SalesTransactions_SalesPersonMatching
  • Mass update, Mass Update

 

 

All scripts can be downloaded from here >> Download Link

 

Best Regards,
Mahmoud M. AlSaadi