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
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;
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;
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;
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”.
On the commitment Setup window, you have the following to consider as illustrated in the image below;
Authorization Type:
Period: Authorization will be based on the budget and actual amounts on a period bases.
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;
Business Portal Setup:
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
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;
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
Several Customers are encountered with the inquiry of having a detailed report for “Historical” Inventory Balances per Item per Period as shown below;
>> 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:
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
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:
The dashboard includes:
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
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.
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.
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;
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
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.
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;
Remove Transaction History
Microsoft Dynamics GP > Tools > Utilities > Inventory > Remove Transaction History
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
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;
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.
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.)
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.
Best Regards,
Mahmoud M. AlSaadi
