Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Monday, January 27, 2014

Microsoft Dynamics GP - MCC Award

 

1

 

With persistence comes achievements. I sincerely appreciate Microsoft’s award as an MCC and consider that as a push to keep on moving forward.

More is yet to come as we always learn from Microsoft that sharing is always the key. “The only thing to do with good advice is to pass it on. It is never of any use to oneself. ”

 

Best Regards,
Mahmoud M. AlSaadi

11

Requisition Management – Apply Account Budgets on Purchase Requests

 

It’s such a nice feature to have your Purchase Requests line items; which are submitted on the Business Portal, controlled by Dynamics GP Accounts Budgets. In a previous post, I have discussed the requisition life cycle ; Requisition Life Cycle. Now, you may be able to add further control on your Business Portal requisitions by activating the PO Commitment as illustrated in the chart below;

PO Com

Understanding PO Commitment:

When the end user submits a purchase request, each line item within the request will have its amount compared to the account budget in Dynamics GP (The actual line item account entered). In case the actual amount exceeds the budget amount and no tolerance is set up, an alert message pops up as shown below;

image

The user can still submit the requisition.

When the first approver checks the request, an alert box is shown next to the “over budget” line item as shown below;

image

If the approver has the authorization password, he can enter to authorize the request. Otherwise he can approve it. And the alert will remain

The request though, will never obtain  “Final Approval” and hence can not be migrated to PO in Dynamics GP unless the password is entered at a certain approval level.

Dynamics GP Setup

Go to Tools > Setup > Purchasing > Purchase Order enhancement. Check the “Activate Commitments”.

image

 

On the commitment Setup window, you have the following to consider as illustrated in the image below;

 image

Authorization Type:

  • Annual: Authorization will be based on the total budget and actual amount for the year
  • Period: Authorization will be based on the budget and actual amounts on a period bases.

  • YTD (Year to Date): Authorization will be based on the year-to-date budget and actual balances (the current date is derived from the user date)

Authorization Variance:

Just like a tolerance rate that can be considered when comparing the actual to the budget amount. Deviation from the budget can be accepted as shown below;

  • Amount: A positive variance allows commitments to be over the available budget by the entered dollar amount. A negative value requires commitments to be under the available budget by the entered dollar amount.
  • Percent: A positive variance allows commitments to be over the available budget by the entered percent. A negative value requires commitments to be under the available budget by the entered percent.

 

Business Portal Setup:

  • On the top-level Business Portal Website > Site Settings
  • Click on Business Portal Administration
  • Company Setting
  • Choose the Dynamics GP Company on which you want to activate the purchase order commitment
  • Choose option “Purchase request line amounts can be committed” to activate

Helping Note !  Prerequisite for Activation of Commitment on Business Portal “

Before you can start or stop using commitments in Requisition Management, you must be sure that the lines for all requisitions that have received final approval are either transferred to purchase orders in Microsoft Dynamics GP or voided.

 

Best Regards,
Mahmoud M. AlSaadi

Tuesday, January 21, 2014

HITB Per Period (Cumulative) – Inventory Balance on a Prior Date

 

A common question frequently asked by clients is “ What was my Inventory Balance of a specific item on a prior date of this year ! “ . In this post, inventory balance on a prior date report provides the following details;

1

 

In a previous post, Historical IV Trial Balance - Per Period , the report provides the inventory balances per period separately. I have received several requests to build up on this report to provide the; Inventory Balance on a prior date. To deploy this report, following the steps below;

1- Run the script provided on - Historical IV Trial Balance - Per Period. This will create a view V_HITB_Per_Period.

2- Run the script below, which builds up on the view above to retrieve the cumulative inventory balances

SELECT      X.Trx_Year AS 'Year',
                    X.Period ,
                    X.Item ,
                    X.QuantityBalance AS 'Quantity Balance',
                    X.CostBalance AS 'Cost Balance',
        (
            SELECT SUM(QuantityBalance)
            FROM   V_HITB_Per_Period AS Y
            WHERE  X.Item = Y.Item AND
                   DATEADD(MONTH, Y.Period - MONTH(0),       
                                DATEADD(YEAR,Y.Trx_Year - YEAR(0), 0 )
                            ) <=
                   DATEADD(MONTH,X.Period - MONTH(0),
                                DATEADD(YEAR,X.Trx_Year- YEAR(0), 0 )
                            )
        ) AS 'Running Quantity Balance',
        (
            SELECT SUM(CostBalance)
            FROM   V_HITB_Per_Period AS Y
            WHERE  X.Item = Y.Item AND
                   DATEADD(MONTH, Y.Period - MONTH(0),       
                                DATEADD(YEAR,Y.Trx_Year - YEAR(0), 0 )
                            ) <=
                   DATEADD(MONTH,X.Period - MONTH(0),
                                DATEADD(YEAR,X.Trx_Year- YEAR(0), 0 )
                            )
        ) AS 'Running Cost Balance'
        FROM  V_HITB_Per_Period  AS X
        ORDER BY Item,Trx_Year,Period

 

Helping Note!

Both reports users HITB table (SEE30303) primarily to retrieve inventory transactions. So If you have upgraded from prior GP versions without deploying the HITB Reset Tool, the report will not retrieve any data.

 

Best Regards,
Mahmoud M. AlSaadi

Historical IV Trial Balance - Per Period

Several Customers are encountered with the inquiry of having a detailed report for “Historical” Inventory Balances per Item per Period as shown below;

HITB Per Period>> For running total , create the view below and proceed with the next step on the
HITB Per Period (Cumulative) –  Inventory Balance on a Prior Date

The script below retrieves the data from Historical Inventory Trial Balance Tables (SEE30303), to provide both quantity and cost balances per item.

---Tables Included:

  • SY40100| Period Setup
  • SEE30303|Historical Inventory Trial Balance

CREATE VIEW V_HITB_Per_Period
AS
   SELECT  Z.Year AS Trx_Year,
           Z.Period,
           Z.Item,
           Z.[Quantity] AS QuantityBalance,
           Z.[Cost] AS CostBalance
           FROM    ( SELECT X.[Year],
                            X.[Period],
                            X.[Date],
                            X.[Item],
                            X.[Quantity],
                            X.[Cost]
                            FROM  
( SELECT E.[ITEMNMBR] AS Item,
         E.[Year1] AS Year,
         E.[PeriodID] AS Period ,
         ISNULL(F.[Quantity], 0) AS Quantity,
         ISNULL(F.[Cost], 0) AS Cost ,
         ( E.[Year1] * 365 ) + ( E.[PeriodID] * 30 ) 
         AS Date FROM 
        
(SELECT  Z.[ITEMNMBR] ,
                  D.[Year1],
                  D.[PeriodID]
          FROM     
         
( SELECT 
          DISTINCT
          Year1 ,
          PeriodID ,
          DATEADD(mm, 1,
          CONVERT(DATETIME, '01/'
          + CAST(PeriodID AS VARCHAR(2))
          + '/'
          + CAST(YEar1 AS VARCHAR(4)), 103)) AS Date
         
FROM  SY40100 AS X
          WHERE PeriodID <> 0
          ) AS D
       INNER JOIN (SELECT
                   Y.[ITEMNMBR],
                   MIN(Y.[DOCDATE]) AS Date
                   FROM
                   [SEE30303] AS Y
                   GROUP BY Y.[ITEMNMBR]
                   ) AS Z ON D.Date > Z.Date
                   ) AS E
                   LEFT OUTER JOIN
                  
(
                  
SELECT 
                   YEAR([DOCDATE]) AS Year ,
                   MONTH([DOCDATE]) AS Period ,
                   [ITEMNMBR] AS Item,
                   SUM([TRXQtyInBase]) AS Quantity,
                   SUM([EXTDCost]) AS Cost
                   FROM    SEE30303
                   GROUP BY YEAR([DOCDATE]),
                   MONTH([DOCDATE]),
                   [ITEMNMBR]
                   ) AS F ON E.ITEMNMBR = F.Item
                   AND E.PeriodID = F.Period
                   AND E.Year1 = F.Year
                   ) AS X
                   GROUP BY  X.[Year],
                             X.[Period],
                             X.[Date],
                             X.[Item],
                             X.[Quantity],
                             X.[Cost]
                   ) AS Z

Helping Note !
The script could be further enhanced in order to include further "drill down" details such as Inventory Account, Location and other inventorial parameters.

Best Regards,
Mahmoud M. AlSaadi

Friday, January 17, 2014

Analytics for Microsoft Dynamics GP by Mark Polino

 

Change has been proved to be an inevitable part of organizations lives, no organization can survive without facing it and managing strategies to respond in an efficient and effective way. In this essence, the importance of decision making emerges as a key function for managers.

In Dynamics GP, decision making has been absolutely enhanced with the new plug and play Analytics for Microsoft Dynamics GP by MVP Mark Polino. Data has been there in your system, but now well presented in a simple and meaningful way through elegant dashboards which overall provides the the capability to analyze, manipulate and correlate your business data efficiently and effectively.

Here’s the Sales, Starter Edition Dashboard:

Sales-Dashboard


The dashboard includes:

  • Top Customers with corresponding costs
  • Top Items with corresponding costs
  • Top Salespeople, Territories and Sites
  • Sales over time with forward looking projection
  • Sales mix over time by item class
  • A full range of data filters to control information

Further details can be found on Dynamic Accounting on the following link: Analytics for Microsoft Dynamics GP

Simplicity has always been the secret.

Best Regards,
Mahmoud M. AlSaadi

Tuesday, January 14, 2014

Bank Reconciliation - Check book versus Cash Account Balance

 

One of the common cases encountered quite often with GP users is an unbalanced checkbook versus General Ledger. As shown below in, the check book current balance is not tied to General Ledger Balance. In this post, one of the common root causes for this case is proposed and a solution criteria is explained.

11

Common Mistakes of Bank Transaction Entry

Although it is a common sense, but most GP Users fall in the trap of using Bank Transaction Entry to record an increase or decrease adjustment for the sake of a another check book which is apparently a bank transfer !

The user enters Bank Transaction Entry (either to increase or decrease adjustment on a certain check book). A common mistake is represented with choosing a “Cash Account” on the other distribution line.

The result is correct GL balance but “incorrect” check book current balance as shown below.

1

Apparently, the user meant to enter a Bank Transfer to transfer an amount of money between two checkbooks (on the Bank Module Level) and their corresponding Cash Accounts (on the General Ledger Level). See illustration below;

1

Solution Criteria

1- Go to Smart List > Financial > Account Transaction. Filter on the specific “Cash Account” of the unbalanced check book.

2-Go to Smart List > Financial > Bank Transactions. Filter on the specific unbalanced “Check Book”.

3- As you match both of the reports above, you will find journal entries on the account transactions that don’t have  corresponding CM Transactions in the Bank Transactions report.

4- These are “Bank Transactions” that should have been recorded as “Bank Transfers”. Go to Transactions > Financial > Bank Transaction > Void Transaction. To void the incorrect CM Transactions.

5- Re-enter correct Bank Transfers to affect both check book and cash account balances.

The result is a check book tied to general ledger with no variance at all.

Recommendation

Unlike vendors and customers, check book setup is recommended to have a single cash account for every single check book. In other words, one to one relationship between CM and GL.

This will enhance the level of control over check book balances, and easy reconciliation with GL and above all your bank statement.

Best Regards,
Mahmoud M. AlSaadi

Friday, January 10, 2014

Inventory– Maintain/Remove Transaction History

 

In a previous post Inventory Distribution History, illustration was provided regarding Inventory distribution history. It is important now to understand the effects of maintaining or removing Inventory transaction history.

Inventory History Setup:

From Item Card > Options > Maintain History.

  • Calendar Year
  • Fiscal Year
  • Transaction
  • Distribution

Untitled

Maintaining transaction history is associated with the IV30300 Table. Inventory Transaction Amount History.

Helping Note !

If you un check the “Transaction” checkbox under Maintain History, IV transactions will not be recorded in IV30300. The following inquiries/reports will be lost;

  • Inquiry: Item Stock Inquiry (reads from IV30300 and IV00102)
  • Report: Historical Stock Status (reads from IV30300)

Remove Transaction History

Microsoft Dynamics GP > Tools > Utilities > Inventory > Remove Transaction History

Untitled

Even if the inventory is setup to maintain transaction history, you can still remove transactions history from the “remove transaction history utilities”. Which will primarily remove all records in IV30300 for a certain item number, document number, date or site.

Best Regards,
Mahmoud M. AlSaadi

Thursday, January 2, 2014

Customer Combiner Utility

 

The customer combiner utility is used to combine an existing customer to another existing customer, the source customer is removed from the system while summary information are recalculated for the destination customer, and here’s a detailed illustration of what happens.

1- Customer Card

The two customer apparently have different details in customer card, which can be categorized as below;

  • Informational Fields (name, short name, address, country, city, payment terms …etc.)
  • Accounts (AR, Sales, Cost of Sales …etc.)
  • Options (Credit Limit, Maximum Write off, Minimum Payment, Rate type, statement cycle ..etc. )

All the details of the source customer are removed from the system, while the destination customer card is remained as is.

For instance, credit limit of the destination is not updated to be the sum of both, and the same as well for all definition fields.

RM00101

Note:

If the two customers are assigned to different currencies, you will not be able to combine them unless both of them are assigned to the same currency.

2- Customer Summary

The summary information (RM00103) are updated for the destination customer to be the sum of both such as (Customer Balance, Aging per amount, Total sales LTD, YTD… etc.)

RM00103

 

3- Customer Transactions

All the transactions’ details are kept the same with no update on any of the fields. Only the Customer ID field of the source customer is updated with the id of the destination customer.

In this essence, the destination customer will have the balance of both (as shown above) and the unapplied amount (current transaction amount) of both as well.

RM00101

RM00101

 

Best Regards,
Mahmoud M. AlSaadi