Inventory Balance Between Two Dates

 

Commonly asked to determine the inventory balance and cost for a certain item on a certain location between two dates. In this post, I provide a stored procedure which retrieves the following report;

The balances are grouped by the document type. In addition, Master Quantity data are provided along with the summary to provide better insight for analysts.

• Quantity on Hand: Calculated from the Purchase Receipt Layer
• Quantity Allocated: retrieved “as is” from Item Quantity Master
• Quantity Damaged: retrieved “as is” from Item Quantity Master
• Quantity on Order: retrieved “as is” from Item Quantity Master

--- Tables Included:

• SEE30303: Historical Inventory Trial Balance
• IV10200: Purchase Receipt Layers
• IV00101: Item Master
• IV00102: Item Quantity Master

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[IV_ITM_LOCTN_Summary]  -- Item Summary Between Two Datese per Location
    (
      @ITEMNMBR CHAR(31) ,
      @TRXLOCTN CHAR(11) ,
      @FromDate DATETIME ,
      @ToDate DATETIME
    )
AS
SET nocount ON
SELECT  IVSUM.DocumentType,
        IVSUM.ITEMNMBR,
        IVSUM.ItemType,
        IVSUM.Item_Inactive,
        IVSUM.LOCNCODE,
        IVSUM.Quantity,
        IVSUM.Value,
        IVBLNC.QuantityOnHand AS 'Quantity On Hand',
        IVQMSTR.QTYONORD AS 'Quantity On ORDER',
        IVQMSTR.QTYDMGED AS 'Quantity Damaged',
        IVQMSTR.ATYALLOC AS 'Allocated Quantity'
        FROM
(
SELECT  DocumentType,
        ITEMNMBR,
        ItemType,
        LOCNCODE,
        Item_Inactive,
        SUM(TRXQTYInBase) AS Quantity,
        SUM(Extended_Cost) AS Value
        FROM
        (

         Select    CASE A.DOCTYPE
                        WHEN 1 THEN 'Adjustment'
                        WHEN 2 THEN 'Variance'
                        WHEN 3 THEN 'Transfer'
                        WHEN 4 THEN 'Receiving'
                        WHEN 5 THEN 'Sales Return'
                        WHEN 6 THEN 'Sales'
                        WHEN 7 THEN 'BOM-Assembly'
                        ELSE        'Cost ADJ'
                    END AS DocumentType,
                    A.ITEMNMBR,
                    CASE B.ITEMTYPE
                        WHEN 1 THEN 'Sales Inventory'
                        WHEN 2 THEN 'Discontinued'
                        WHEN 3 THEN 'Kit'
                        WHEN 4 THEN 'Miscellaneous Charges'
                        WHEN 5 THEN 'Services'
                        WHEN 6 THEN 'Flat Fee'
                        ELSE        'N.A'
                    END AS ItemType,
                    CASE B.INACTIVE
                        WHEN 0 THEN 'No'
                        WHEN 1 THEN 'Yes'
                        ELSE        'N.A'
                    END AS Item_Inactive,
                    A.DOCDATE,
                    A.LOCNCODE,
                    A.TRXQTYInBase,
                    A.UNITCOST,
                    A.TRXQTYInBase * A.UNITCOST

                                        AS  Extended_Cost
                    FROM dbo.SEE30303 AS A
                    LEFT OUTER JOIN dbo.IV00101 AS B
                    ON A.ITEMNMBR = B.ITEMNMBR
                    WHERE    A.ITEMNMBR =  @ITEMNMBR AND
                            A.LOCNCODE = @TRXLOCTN AND 
          (A.DOCDATE BETWEEN @FromDate AND @ToDate)       
        ) AS IVTRX
        GROUP BY DocumentType,
                 ITEMNMBR,
                 ItemType,
                 Item_Inactive,
                 LOCNCODE
                
) AS IVSUM
LEFT OUTER JOIN
(
    SELECT  ITEMNMBR,TRXLOCTN,
            SUM(QTYRECVD-QTYSOLD) AS QuantityOnHand
    FROM IV10200
   GROUP BY ITEMNMBR , TRXLOCTN
) AS IVBLNC
ON    IVSUM.ITEMNMBR = IVBLNC.ITEMNMBR AND
      IVSUM.LOCNCODE = IVBLNC.TRXLOCTN
LEFT OUTER JOIN dbo.IV00102 AS IVQMSTR
ON    IVQMSTR.ITEMNMBR = IVBLNC.ITEMNMBR AND
      IVQMSTR.LOCNCODE = IVBLNC.TRXLOCTN

SET nocount OFF   
GO

 

To run the procedure, run the following code including the required parameters;

EXECUTE [dbo].[IV_ITM_LOCTN_Summary]
               '#ItemNumber'
              ,'#Location Code'
              ,'#2013-01-01'
              ,'#2013-12-31'
GO

 

Best Regards,
Mahmoud M. AlSaadi

Inventory Reconciliation Flow

 

When Dynamics GP administrators encounter  an incorrect item balance or allocation, posting corruption and many other issues related to inventory, the first thing that comes in mind is to “Inventory Reconciliation”. Therefore, it is essential to understand the Inventory Reconciliation Process flow in order to determine how things will get corrected.

The following graphs shows the steps performed in Inventory Reconciliation for each item. Arrows directions are used to represent the “Data Flow”

 

Summary:

Below as an overall summarized representation for the several processes being involved in the process.

 

Best Regards,
Mahmoud M. AlSaadi

Adding Fixed Asset from Payable Management

 

A step by step illustration on how to add a fixed asset from Payable Management, which is integrated with Fixed Asset Module. This post provide further details for Microsoft Support Article ID: 2761966. The whole process is shown below in the graph as it is applied on the system.

It is a common mistake that the “Purchase” is directly applied on the system from Fixed Asset. The truth is, the AP invoice and specifically the distribution initiate the process. A prerequisite is to check the setup as provided in the support Article mentioned at the beginning of the post above.

AP Invoice

The first step to start with is the AP Invoice, in which all the usual invoice details are to be filled out. The acquisition cost value will be retrieved from the “Purchases” field in this window.

On the Payable Transaction Entry Distribution window, when the PURCH distribution type is debited for the “Purchase Posting Account”, the “Purchase” button will appear in the Fixed Asset General Information Window to allow adding the asset as entered on the AP Invoice as soon as the AP invoice is posted.

Helping Note !

The trigger account mentioned above should have already been defined in the Purchase Posting Accounts on the following path (Microsoft Dynamics GP > Tools > Setup > Fixed Assets > Purchasing Posting Accounts)

 

Fixed Asset – General Information Window

Now on the Asset General Information (Cards > Fixed Assets > Asset General Information) enter an asset ID, and then click on “Purchase Button” to select the document number originating from AP.

All associated details will be inherited into the newly added asset, such as acquisition cost and Asset Description.

 

Fixed Asset GL Posting

The next step now is to generate the records of this specific asset, and to be transferred into General Ledger. Go to Tools > Routines > Fixed Asset > GL Posting

Fill out the specific Asset ID created in step (2), and then click “Process”. The details of the generated Journal Entry will be shown before posting the Batch.

Important Note ! 
(For old Dynamics GP versions – Prior to GP2013)

The Fixed Asset GL posting window has been redesigned with the introduce of Dynamics GP 2013 as it is showing above. Prior to GP 2013, the screen included only the following fields:

• Beginning Period
• Ending Period
• Transaction Date
• Comment

Here is the old Fixed Asset GL Posting window:

Suppose the asset is added in 22/04/2015, then you will have to include the beginning period as (2015004) and the ending period as (2015004), which is practically the year (2014)and the month (004). Then include the GL posting data in the transaction date and click continue.

Unfortunately, you will not be able to see the transactions or a validation report before that. After that, a batch will be immediately created in the General Ledger module, Journal entry window.

General Ledger Posting

On the General Ledger (Transactions > Financial > General), a batch will be created after the previous step is processed. Posting this batch will do the following;

• Clear the Purchase Posting Account.
• Posting the acquisition cost into the associated Asset Cost account

 

 

Below is the posting Journal Reports generated after both (AP Invoice) and (General Ledger Posting) as a reference; 

• AP Invoice Posting will generate the following Journal Entry

• General Ledger Posting will generate the following Journal Entry

 

Best Regards,
Mahmoud M. AlSaadi

Requisition Management – How to Manage Delegations

When it comes to requisition approval, there is always a necessity to manage delegations among different users in the approval hierarchy. In this post, I will try to provide a comprehensive illustration on how to manage this requirement.

1- Extended Approver:

On the user properties web part, a user can be granted an “Extended Approver”, this will allow the user to be delegated to approve pending requests assigned to anyone under them in the Approval Hierarchy.

 

2- Change Approver:

Another possible way to apply the delegation is to grant the users (C,D,E and F) the privilege to change their approver. This option enables users to submit their requests to a higher level in the Approval Hierarchy.

3- Replace Level:

Another possible way is to replace the level with another user/role in the approval hierarchy to be delegated. When replacing the user/role, a warning message will pop up stating that all current “work” documents will be assigned to the new user/role.

 

Best Regards,
Mahmoud M. AlSaadi

Delete Item Site Assignment

Throughout the implementation phase, clients are mostly busy with delivering precise and accurate opening balances, without paying considerable attention to other essential details such as: Item-Site Assignment master file. They usually request to assign the item to all sites (regardless of which sites the item is physically assigned to). Later on, deleting this assignment could be one of the riskiest things you could imagine.

An inventory transaction is recorded primarily in the following four tables, which are;

• IV30300 | Inventory Transaction Amount History
• IV10200 | Inventory Purchase Receipt Work
• IV10201 | Inventory Purchase Receipt Details
• SEE30303 | Historical Inventory Trial Balance

On the Item-Site assignment window (Cards > Inventory > Quantities/Site), deleting the Item-Site assignment delete all associated records in both tables (IV10200, IV10201), leaving only (IV30300, SEE30303). Which means, you will lose the following reports:

• Smart List > Inventory Transactions
• Purchase Receipt Inquiry
• Purchase Receipt Report

Below, is a view on the tables on how the records associated with the Item-Site “delete” will be deleted from the Purchase Receipt Layers Work and Details.

Before delete, there are records associated with the item-site assignment:

After deleting the assignment, the records will be completely deleted from IV10200 and IV10201 as shown below.

 

Best Regards,
Mahmoud M. AlSaadi

Business Portal Requisitions – Approval Hierarchy SQL View

 

In Requisition Management, Approval Hierarchy is managed on the Top-Level Business Portal web site by considering the following groups (Requisition Creator, Approver and Purchaser) in order to draw the route through which a requisition will flow and get approvals before being migrated to Purchase Order in Dynamics GP. As follows, I provide an SQL View for the Approval Hierarchy for each Dynamics GP Company.

SQL View for Requisition Management – Approval Hierarchy

The following script retrieves the Approval Hierarchy as setup under company settings. Each dynamics GP has one active approval hierarchy.

--- Tables Included:

• DYNAMICS..ReqMgmtApprovalTreeNode
• DYNAMICS..ReqMgmtApprovalTreeDefaultNode
• DYNAMICS..ReqMgmtApprovalTree
• DYNAMICS..MbfUser
• DYNAMICS..SY01500

 

SELECT  Y.GPCompanyID,
        Y.GPCompanyName,
        X.ApprovalHierarchy,
        X.UserName,
        X.ApproverName
        FROM
            (SELECT E.Text AS ApprovalHierarchy,
                    ISNULL(D.Name,A.Text) AS UserName,
                    ISNULL(C.Name,'') AS ApproverName,
                    A.ResidentID,
                    B.ResidentID AS Parent_ID,
                    ISNULL(A.ParentID,'') AS ParentID ,
                    A.PersistentTreeID,
                    A.GroupRootID,
                    E.ID
                    FROM ReqMgmtApprovalTreeNode AS A
                    LEFT OUTER JOIN
                    ReqMgmtApprovalTreeDefaultNode AS B
                    ON A.ParentID = B.PersistentTreeNodeID
                    LEFT OUTER JOIN MbfUser AS C
                    ON B.ResidentID = C.ID
                    LEFT OUTER JOIN MbfUser AS D
                    ON A.ResidentID = D.ID
                    LEFT OUTER JOIN
                    ReqMgmtApprovalTree AS E
                    ON A.PersistentTreeID = E.ID
                    ) AS X LEFT OUTER JOIN
            (SELECT B.CompanyID AS GPCompanyID,
                    C.CMPNYNAM AS GPCompanyName,
                    A.Text AS ApprovalHierarchy
                    FROM ReqMgmtApprovalTree AS A
                    LEFT OUTER JOIN
                    ReqMgmtSetupCompany AS B
                    ON A.ID = B.ApprovalTreeID
                    LEFT OUTER JOIN SY01500 AS C
                    ON B.CompanyID = C.CMPANYID
             ) AS Y
            ON X.ApprovalHierarchy = Y.ApprovalHierarchy
            ORDER BY Y.GPCompanyID, X.ApproverName

 

Best Regards,
Mahmoud M. AlSaadi