Inventory Reconciliation – Adding new records

It is commonly known that Inventory Reconciliation can mostly handle incorrect balances as it goes through the transactions and recalculates the different balances of the item (quantity on hand, allocated quantity …etc). Although, it does definitely check the data integrity; like check links ,and add new records to the database. In this post, two common scenarios as related to Inventory Reconciliation are illustrated.

Before we start, you may refer to the overall visual explanation and illustration of the inventory reconciliation process on Inventory Reconciliation Flow

1- Added Purchase Receipt for lot number or serial number  | Or, Added Serial Number Reconcile | Or, Added Lot number Reconcile:

One of the primary checks conducted by the IV reconciliation is checking the lot number master and serial number master against inventory purchase receipt. Technically speaking, these are IV00300 and IV00200 against IV10200. The following chart illustrates generally the overall process

Inventory  Reconciliation Flow – Summarized Representation

Point indicates that lot number master and serial number master are both checked against the purchase receipt cost layers and accordingly the required modifications or corrections are deployed. In other words, the following rules are applied:

Scenario (1) Each purchase receipt for items with either with (lot number /serial number tracking options) should have a master lot or serial number record. If there is a missing record, a new record will be inserted in IV00300 or IV00200

See below reconciliation reports after running the IV Reconciliation Process:

Scenario (2) Visa versa, each open lot number in the IV00300 and serial number in IV00200 will check the associated purchase receipts in IV10200, if there is a purchase receipt missing, a new purchase receipt will be created

See below reconciliation reports after running the IV Reconciliation Process:

 

2- Added quantity sold detail record quantity

Point indicates that purchase receipt work and details are to be checked as well. In IV10200, each cost layer has a quantity received and quantity sold, the total number of quantity sold which is stored in IV10200 should match the total of all out transactions in IV10201. In case of mismatch, a RECON record will be created in IV10201 in order to tie the work and details.

See below reconciliation reports after running the IV Reconciliation Process:

In a previous post, an SQL Script was provided to check the data integrity between purchase receipt work and details before running the reconciliation, if you have no records returned, it means you are on the safe side.

In summary, it is important to take into consideration that Inventory Reconciliation is not only about recalculating the balances and correcting the summary tables, It could definitely insert new records when required for the sake of data integrity.

Best Regards,
Mahmoud M. AlSaadi

David Musgrave has left Microsoft – New Journey Begins with “Winthrop Development Consultants”

It has been sadly one of the most shocking news I’ve heard this year, David Musgrave is not a Microsoft employee anymore ! Well, this has been even more shocking to me than the announcement of dropping Microsoft Dynamics SMB ERP certifications. It’s been thirteen and half years of continuous achievements in the different aspects of Microsoft Dynamics GP that will never be forgotten.

Change has always been an inevitable part of our lives, it goes without saying that Intelligence is the ability to adapt to change and yet expect it and get well planned for it. The end of David’s journey with Microsoft is the beginning of another as he has already restarted his consulting business which he had before joining Microsoft; Winthrop Development Consultants. In this essence, the news is shocking but quite promising as there is a too much on the horizon to come.

Best Regards,
Mahmoud M. AlSaadi

Limited Users in Dynamics GP 2013 R2 – Security Insight

A very common issue that has been seen recently with among Dynamics GP client is the access of the limited user, you always encounter a simple question and proceed with several clicks and checks to reach an answer or “Yes it has access or No it doesn’t have an access” on this specific window.

It is worth saying that the answer is just there at the tips of your fingers. When opening the security task window, a new icon has been added to “denote windows available to limited user types”. See below:

 

 

 

 

 

 

 

 

 

 

Security Task Details as Related to Limited User – SQL View

The script below retrieves all the security task details as related to whether a limited user has an the access or not. This will definitely help you while building customized security roles for your limited users.

 

 

 

USE DYNAMICS
SELECT  RTRIM(PRODNAME) ProductName ,
        RTRIM(TYPESTR) Type ,
        RTRIM(Series_Name) AS Series ,
        RTRIM(DSPLNAME) DisplayName ,
        RESTECHNAME AS Resource_Technical_Name,
        CASE AvailLmtdUsrs
           WHEN 1 THEN 'Yes'
           ELSE 'No'
        END AS AvailableToLimitedUser
        --  SECRESTYPE SecurityResourceType,
        --     DICTID AS DictionaryID,
        --  SECURITYID SecyrityID ,
FROM    SY09400
ORDER BY 1,2,3,4

 

Security Resource Description – Excel Report

In order to have the maximum benefit of the script above, you can drop it into Excel and have a pivot table report, as shown below:

Here is the filter to consider:

Here is the Pivot Report:

You can download the Security Resource Excel report from Here

Best Regards,
Mahmoud M. AlSaadi

Unhandled script exception “Value of range” error when Running Receivables Reconciliation

When attempting to run the sales reconciliation; specifically for the “Current Customer Information”
an unhandled script exception [Value of Range]
Exception_Class_Script_Out_of_Range_Script_CMD_Selected.

 

As you proceed, when pressing the “Process”, you will get the error stated below:

Resolution
This is most likely caused by a missing statement cycle definition on the customer card, as shown below:

• Go to Cards > Sales > Customer
• On the customer card , press on the options button
• On the customer option window, check the “Statement Cycle”. If it is empty, choose one of the predefined options (monthly, weekly …etc)
  

For a mass update, you can consider the update of this option on the SQL level by running a simple update statement

To identify the customers with no statement cycle definition, run the script below:

SELECT  STMTCYCL AS StatementCycle ,
        CUSTNMBR
FROM    dbo.RM00101
WHERE   STMTCYCL = 0

To correct a bulk of customers, you can consider the following update statement:

UPDATE  dbo.RM00101
SET     STMTCYCL = StatementCycleValue
WHERE   CUSTCLAS IN ( )

 Statement Cycle values are:

1 NO STATEMENT
2 Weekly
3 Biweekly
4 SemiMonthly
5 Monthly
6 BiMonthly
7 Quarterly

If all the above doesn’t apply to resolving this error, you may need to check missing records in RM tables (RM00101,RM00102, RM00103, RM00104 and RM00106). Refer to Support Article ID: 851191 for further in-depth details

Best Regards,
Mahmoud M. AlSaadi

Analytical Accounting Excel-Based Macro Generator – Beta Testers Needed

Analytical Accounting journal entry has always been a complex area from a data entry perspective especially when it comes to mass JE’s with analytical details. In this essence, AA Macro-generator tool is designed to provide an added value and help the users in this specific area.

In this post, I am proposing a new approach for data entry in Dynamics GP by enhancing the use of both Dynamics GP Macros and MS Excel. This tool will be totally free and will be published with its full functionality on this blog.

Since it has been initially done for the community, having it tested by the great folks out there comes as an essential step before having it publicly shared.

 

 

 

 

 

 

 

 

If you are willing to be part of the beta test, please drop me an e-mail on MahmoodSaadi@Live.com and you will be notified of the details once enough testers are involved.

Best Regards,
Mahmoud M. AlSaadi