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
