Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Saturday, May 20, 2017

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

No comments:

Post a Comment