Cash Collection per Customer per SalesPerson

We usually think of commission from sales perspective, which means that the higher a sales person sells, the higher their commission is. Although, this is not quite accurate when you work with big distribution companies, you start to realize that "cash collection" for the sales is more important than the sales itself. 

The concept is pretty much straight forward, when evaluating sales force and salesperson, it is not only important to give commissions for sales persons who can up-sell and increase sales, but rather the ones who can sell and collect high as well. 

When trying to understand this requirements from Dynamics GP perspective, it is clear that the system doesn't keep track of the sales person per cash receipt, but rather it is stored on the customer card level. For that purpose, the report below retrieves all the cash receipts (check and cash) per Customer per Sales Person

CREATE VIEW [dbo].[GPEssentials_Vw_SalespersonCollection]

AS

    SELECT  A.DOCNUMBR AS DocumentNumber ,

            BACHNUMB AS BatchNumber ,

            A.CUSTNMBR AS CustomerNumber ,

            C.CUSTNAME CustomerName ,

            C.CUSTCLAS AS CustomerClass ,

            D.CLASDSCR AS CustomerClassDescription ,

            C.SLPRSNID AS CustomerSalesPersonID ,

            E.SalesPersonName ,

            DOCDATE AS DocumentDate ,

            Trx_Month AS TransactionMonth ,

            A.CHEKBKID AS CheckBookID ,

            A.CURNCYID AS CurrencyID ,

            CURTRXAM AS CurrentTransactionAmount ,

            ORTRXAMT AS OriginalTransactionAmount ,

            CASE CSHRCTYP

              WHEN 1 THEN 'Cash'

              WHEN 0 THEN 'Check'

              ELSE ''

            END AS CashReceiptType

    FROM    ( SELECT    'Unposted' AS Trx_Status ,

                        BACHNUMB ,

                        DOCNUMBR ,

                        CUSTNMBR ,

                        DOCDATE ,

                        MONTH(DOCDATE) AS Trx_Month ,

                        CHEKBKID ,

                        CURNCYID ,

                        CURTRXAM ,

                        ORTRXAMT ,

                        CSHRCTYP

              FROM      RM10201

                        DOCNUMBR NOT LIKE 'CSIR%'

              UNION ALL

              SELECT    'Posted' AS Trx_Status ,

                        BACHNUMB ,

                        DOCNUMBR ,

                        CUSTNMBR ,

                        DOCDATE ,

                        MONTH(DOCDATE) AS Trx_Month ,

                        MSCSCHID ,

                        CURNCYID ,

                        CURTRXAM ,

                        ORTRXAMT ,

                        CSHRCTYP

              FROM      RM20101

              WHERE     RMDTYPAL = 9

                        AND DOCNUMBR NOT LIKE 'CSIR%'

            ) AS A

            LEFT OUTER JOIN RM00101 AS C ON C.CUSTNMBR = A.CUSTNMBR

            LEFT OUTER JOIN RM00201 AS D ON D.CLASSID = C.CUSTCLAS

            LEFT OUTER JOIN ( SELECT    RTRIM(LTRIM(SLPRSNID)) AS SalesPersonID ,

                                        CONCAT(RTRIM(LTRIM(SLPRSNFN)), ' ',

                                               RTRIM(LTRIM(SPRSNSMN)), ' ',

                                               RTRIM(LTRIM(SPRSNSLN))) AS SalesPersonName

                              FROM      RM00301

                            ) AS E ON E.SalesPersonID = D.SLPRSNID;

GO

Best Regards, 

Mahmoud M. AlSaadi

Sales Transactions Entry - Trade Discount and Markdown

When thinking of sales invoice discount, there is always a distinction between what's called a discount per item (Markdown) and the discount per invoice (Trade Discount). The difference is primarily represented with the allocation of item discount per item which is in turn deducted from the extended item price.

In Dynamics GP, there is a predefined set of accounts for both; discount per item and discount per invoice. The discount per item is configured on the item card level, while the discount per invoice is configured on the customer card level as illustrated below:

Item Card Accounts - Markdown

Customer Card Accounts - Trade Discount

The markdown amount is the difference between the unit cost of the item in Invoicing and the reduced price. For example, if the unit cost of an item is $100 and you mark it down 10%, $10 will be posted to the Markdown account. This account will be posted to if you've chosen to use posting accounts for the item rather than the customer in the Invoicing or Sales Order Processing modules.

On the other hand, the trade discount amount filled out on the sales transaction entry under the "Trade Discount" is deducted from the overall sales document amount, without being deducted from each item prices. So item prices are not affected but rather the whole invoice.

Sales Transaction Entry

Trade Discount into Markdown 

In standard Dynamics GP, there is no link between the trade discount and the markdown, which means trade discount can not be distributed into line items. Although, the system keeps track and does distribute the trade discount (which is filled out on the invoice level) into line items without any effect on the application level. Here is a quick technical view on the Sales Transaction Entry tables:

 Here is a screen shot for an invoice which doesn't have Trade Discount : 

Sales Header versus Details

 Here is a screen shot for an invoice which have Trade Discount : 

In this essence, this article is meant to provide an important piece of information, which seems to be the bases for creating an additional important feature in Dynamics GP, which distribute the invoice discount and reflect it on an average base on the line items level.

Best Regards, 

Mahmoud M. AlSaadi