Cost Adjustment (1 out of 4) Override Documents

When inventory items are either purchased, manufactured or sold through the associated modules accordingly; Purchase Order Processing, Manufacturing or Sales Order Processing, these items will be exposed to specifically four different scenarios in which cost variance documents are calculated. Each of these scenarios have its own characteristics and considerations.

Understanding these scenarios comes as an essential part of understanding these modules behaviors and their effects on the inventory module from one side, and the inventory versus General Ledger on the other side.

In this post, I will be shedding a light on the first scenario, which is the override documents.

Setup and Configuration

On the setup level of Dynamics Great Plains, Quantity Shortage option can be mainly setup in one of the following;

• Inventory Control | Dynamics GP > Tools > Setup > Inventory > Inventory Control

Inventory Control – Override Setup

There are three basic options to check which will allow quantity shortage to be handled through override documents. Checking one of these options will allow the quantity on hand to drop to a “negative value” since the required quantity to be withdrawn is bigger than the available quantity

• Sales Order Processing |  Dynamics GP > Tools > Setup > Sales > Sales Order Processing > Sales Document Setup > Fulfillment Order/ Invoice

This option will allow the sales of quantities less than the quantity available, which results with quantity shortage and override documents

Sales Order Processing – Override Setup

Understanding Override Documents – Functional Point of View

Though this section, I will provide a practical example through which an adjustment override is allowed in Inventory to withdraw less than the quantity available resulting with negative quantity on hand.

Transaction Level – Quantity Shortage

• Document Type: Adjustment Out
• Item Number: Chair
• Current Cost: 5
• Quantity on Hand: 0

Adjustment Transaction

Account	Debit	Credit
Inventory	0	50
Inventory Offset	50	0

After the transaction is posted, the quantity on hand will be (-10).

Now, supposing that the after a while, an inventory replenishment occurs to relieve the override document. The replenishment is done through a receiving document (Shipment/Invoice) but with a higher unit cost (7).

The inventory replenishments will result not only with the receiving document, but with a cost adjustment document as well. The following journal entries will be posted;

Receiving Transaction

Account	Debit	Credit
Inventory	70	0
Inventory Offset	0	70

Cost Adjustment Document

Account	Debit	Credit
Inventory	0	20
Inventory Offset	20	0

Understanding Override Documents – Technical Point of View

Inventory tables responds to the override document in the best way to best fulfills data accuracy, by recording all the required fields. It’s worth saying that each of the inventory tables has its own behavior which are all working in consistency and harmony to provide several insights on the same piece of information

Section One | Quantity Shortage and Override Documents

IV10200

As for the purchase receipt layer work table, there is no available purchase layer from which to adjust out, therefore Dynamics GP throws an override purchase receipt layer with a zero quantity received and 10 quantity sold

IV10201

As for the purchase receipt layer details, a detail record for the adjustment out will be thrown and linked through the criteria

• (IV10200.ReceiptSequenceNo = IV10201.SourceReceiptSequence No)

SEE30303

Most importantly, the historical inventory trial balance table, records most of the essential details since it will be later the only inventory table containing cost adjustment document

Database View – Inventory Tables with Override Document

Section Two | Inventory Replenishment and Cost Adjustment
Now, to go with our scenario, a receiving transaction (Shipment/Invoice) for the same item is posted later on. Most importantly, with different cost. See below how tables are updated.

As for the Inventory Transaction Amount History (IV30300), no changes are performed on this table since the behavior of this table is to record historical transaction as is, and keep them in the same historical state regardless of any further changes

As for the Inventory Purchase Receipt Layer (IV10200), changes on the quantity received as long as the receipt sequence number (the link with IV10201)

As for the Inventory Purchase Receipt layer details, the original receipt sequence number is changed accordingly to be linked with the purchase receipt layer. On the other hand, the unit cost is replaced with the new cost while the old cost is recorded in ADJ cost field.

As for the HITB, a cost adjustment record is thrown with the cost adjustment journal that will be posted to General Ledger in order to adjust the previous layer unit cost. And keep Inventory versus GL tied together.

 

Database View – Inventory Tables with Inventory Replenishment

Cost Adjustment – Case 1 out of 4 (Override)

To be Continued …

Best Regards,
Mahmoud M. AlSaadi

User Security Role Enhancement–Intersect

Monitoring user security privileges in Dynamics GP might be a bit of headache sometimes. Therefore, several GP experts have provided SQL views to collect the security roles and tasks for a certain user in order to simplify this process. Although, several inquiries might still require enormous time especially when it comes to give a certain GP user bits and pieces of other user privileges. 

In this post, I am proposing a new methodology in enhancing this process by taking the SQL view a bit forward to further simplify such requests.

Practical Example:

Suppose you may have a problem with a certain user (User A) who’s not being able to access a certain window (transaction, report, inquiry or posting privileges). On the other hand, (User B) is not encountering the problem.

The methodology I am supposing is to intersect the security privileges of both users (A and B) in order to eliminate the security tasks range in which the required access resides. See the chart below for graphical illustration

In this essence, the idea embedded within the chart above must be broken down into pieces in order to understand the proposed enhancement:

• Check the security privileges (Roles and Tasks) for both users (A and B). The script to check the user security is provided at the end of the post
•  Build the logic between the two scripts using one of the following (in or not in)

 

The script below retrieves the security role and associated tasks for a selected GP User. It represents the basic block of the method above.

Tables Included:

• SY10500 | Security Assignment User Role
• SY01500 | Company Master
• SY10600 | Security Assignment Role Task
• SY09000 | Security Tasks MASTER

 

SELECT  RTRIM(SR.CMPNYNAM) + '__' + RTRIM(SR.USERID) + '__'
         + SR.SECURITYROLEID AS 'Company User Role' ,
SR.CMPNYNAM AS 'Company Name' ,
USERID AS 'GP User ID' ,
ST.SECURITYROLEID AS 'Security Role ID' ,
ST.SECURITYTASKID AS 'Security Task ID' ,
ST.SECURITYTASKNAME AS 'Security Task Name' ,
ST.SECURITYTASKDESC AS 'Security Task Description'
FROM    ( SELECT    [CMPNYNAM] ,
                    [USERID] ,
                    [SECURITYROLEID]
                    FROM      [SY10500]  AS A
                    INNER JOIN [SY01500] AS B ON A.[CMPANYID] = B.[CMPANYID]

         ) AS SR
        LEFT OUTER JOIN ( SELECT    C.[SECURITYROLEID] ,
                                    C.[SECURITYTASKID] ,
                                    D.[SECURITYTASKNAME] ,
                                    D.[SECURITYTASKDESC]
                          FROM      [SY10600] AS C
                          INNER JOIN [SY09000] AS D ON C.[SECURITYTASKID] = D.[SECURITYTASKID]
                         ) AS ST 
                           ON ST.SECURITYROLEID = SR.SECURITYROLEID

Best Regards,
Mahmoud M. AlSaadi

Edit List Reports Destination

 

Batch or Transaction Edit List reports are considered of a great value when it comes to the “Quality Checks” required to be applied before posting. And the question is, what are the “Batch Edit List Reports” and most importantly, is it possible to change these reports destination? Which is the main target of this post.

Batch Edit List reports can be found on any batch window in Dynamics GP when clicking the “Print Button” just before posting.

Batch or Transaction Edit List reports are shown below;

These reports keep the end-users away from posting issues such as (Closed Fiscal Period, assignment issues, Security privileges …etc.), which sometimes lead to batch problems that are widely known such as ( A batch is held in the posting, busy, marked to post, Locked … etc.)

Batch Edit List Reports Destination Setup

The usual path to change the destination preferences for Edit List reports is to go through the following;

• Check the report name, after clicking the “Print Button”
• Choose the associated “Series” and “origin” from the “Posting Setup window”
• Under the Report Name, choose the default destination to be applied each time the report is printed

The choices above are stored in the “SY02200 | Posting Journal Destination” table, including the following details:

Edit List Reports Destination Problem

I will provide a practical case through which we go on a step-by-step basis to illustrate and explain what’s wrong!

I will take the “Transaction > Financial > General” as an example, when I click the “Print Button”, the following screen pops up;

On the posting setup window, Under “Financial Series”, “General Entry”, there is no (General Entry Edit List) under reports.

Troubleshooting

In SY02200 | Posting Journal Destination, only the following edit list reports are available to be managed and change the associated destination permanently:

• Inventory Transaction Edit List
• Inventory Transfer Edit List
• Edit PO

A logical work around assumption is represented with inserting a new record on the posting journal destination table with the required Edit List Report, which results with “new report record” in the posting setup window, but still the destination options are the same.

Justification and Clarification

On SQL Profiler, it was clear that a stored procedure (zDP_SY02300SS_1) is used with the three reports above to read the Posting Journal Destination table and retrieve the configured options for destination.

On the other hand, the same procedure is not called with several Edit List reports such as;

• Financial General Entry
• Receiving Transaction Entry
• Payable Invoice
• Other reports…

As a matter of fact, the “Ask option” is disabled since there is neither corresponding stored procedure nor records in the Database to manage the destination options. Rather, a pre-set of hardcoded options might be stored in the Dexterity Layer to set default printing options for the Edit List Report.

Therefore, there is, unfortunately, the destination of these reports is not editable nor manageable from the Posting Setup Window

 

Best Regards,

Mahmoud M. AlSaadi

Item Quantities Inquiry - Negative Quantity Sold Justification

One of the cases I have been working on recently on the Microsoft Dynamics Community is the Justification of having negative "Quantity Sold" filed on the Item Inquiry. 

After thorough and continuous testing, I have figured it out, and the answer is simply "Sales Returns" !

I will start my post with the end-result in my mind, here is what the inquiry will show:

Inquiry > Inventory > Item

The fact is that the window above retrieves the data from (Item Quantity Master Table | IV00102 ) in base unit of measurement. And the question remains on the Data Source from which Item Quantity Master retrieves data, and how does it calculate and record these numbers.

Date Flow and Data Source: 

Data Flow - Item Inquiry Data Sources

• Item Inquiry retrieves data primarily from Item Quantity Master (IV00102)
• Quantity fields in Item Quantity Master are updated after every transaction (IV, SOP and IVC). In addition, Inventory Reconciliation re-calculates correctly these quantities.

Calculation Criteria: 

• IV00102..Quantity on Hand = IV10200.. Quantity Received - IV10200.. Quantity Sold (excluding returns documents)
• IV00102..Quantity Sold = SOP30300..Quantity Sold  - SOP30300..Quantity Returned

When the Quantity Returned is greater than the Quantity Sold, Item Inquiry will show negative quantity value.

Note:

Adjustment and Transfer documents don't update the "Quantity Sold" field in Item Quantity Master (IV00102).

Finally, I will  list down the transactions performed on the test item (123123123) shown in the screen shot above, and show how the "Quantity Sold" field is calculated, 

Tables View 

• IV00102..Quantity on Hand = IV10200.. Quantity Received (100) - IV10200.. Quantity Sold (22) = 78
• IV00102..Quantity Sold = SOP30300..Quantity Sold (12)  - SOP30300..Quantity Returned (20) =  -8

Best Regards, 

Mahmoud M. AlSaadi

Business Portal MBFWebBehavior recurring Error Message

When end-users accessing Business Portal from their client workstations, an error message pops up on the screen requesting to download the required “.dll files” in order for MBFWebBehaviors Product to work correctly.

• Program Name: MBFWebBehavior.cab
• Verified Publisher:  Microsoft Corporation
• File Origin: Downloaded from Internet

MBFWebBehavior.cab Error Message

Technically speaking, two dll files are missing since the user doesn't have “Administrator” privileges to access the folders in which those dll files are to be downloaded.

The result is, the message keeps popping up on the end-user screen forever, these dll files are shown below;

• MbfWebBehaviors.dll
• Msvcr71.dll

There is a support article from Microsoft explaining thoroughly on how to resolve the issue, No.905429. 

To me, I found it more easily to log off from the windows and sign in with the “SPAdmin” account, then access the business portal in order for the error message to pop up again. Click (Yes) in order for the dll files to be downloaded correctly, and the end-user can get back to accessing Business Portal without the headache of the recurring “MBFWebBehavior” error message.

Important Note ! 
When opening the internet explorer, right click on the program and (run as administrator).

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP 2013 SP2 - SmartList Designer

Dynamics GP 2013 service pack (2) comes with a new feature; SmartList Designer. Immediately, several questions come in mind, Can I edit an existing "out of the box" Dynamics GP SmartList, and the answer is still no. 

Although, smartList Designer gives the work around solution for modifying current smartlists by being able to create new smartlist based on existing ones. In this essence, the answer to your customers could be “yes” somehow.

One the usual SmartList window, a new button is added through which you go to the SmartList Designer Window as shown below;

SmartList "New" Button

The SmartList Designer Window provides “Database View” considering the tables’ physical names. Objects to choose from includes:

• Tables
• Views
• SmartList Objects

SmartList Designer

As shown above, you can create a new report by considering the relationships between the data sources you choose, as well as any filtration criteria if any. Once completed, the “Execute Query” is clicked to show the T-SQL Query created upon your to retrieve the data requested.

It’s worth saying that the “script” generated below with the T-SQL query pane is not editable, as you may think initially about including further codes within the script. It’s not even saved in the Database as a static script after completion. Only the rules that you configure in the relationship, filter and selected fields’ panes are stored.

You can still consider “calculated fields” in the SmartList Designer through the Athematic, text, Date and time, Math and aggregate functions, in order to add further capabilities to your smartlist report.

Many questions still roll up in my mind, aren’t there quite duplicated features among the SmartList Builder and SmartList Designer, Why not combine and extend the capabilities of the two in one greatest SmartList Tool !   Further versions might hold the answer.

But now, it is worth saying that the SmartList Designer has achieved a considerable progress, as well as the better performance in retrieving data.

Best Regards, 

Mahmoud M. AlSaadi

Inventory Distribution History

Inventory Distribution shows the amounts affecting posting for each transaction originating from Inventory Module. In this post, I intend to illustrate upon several issues regarding the Inventory Distribution setup, utilities and reports.

Inventory Distribution Setup

Within Item Maintenance, there is an option under “Maintain History” to either keep distribution history or not, which is not quite an easy option to go through without thinking of the negative ramifications.

In case the “Maintain Distribution History” is not checked, distribution breakdown registers and posting journals may not be re-printed. Although, there is good news in here at the “System Level” which are:

Within the Audit Trail Codes Setup window, there is an option for reprinting the posting journals, which will enable the distribution history to be kept regardless of how this option is selected in Item Maintenance Options Window.

 

Item Maintenance Options

 

Inventory Distribution Utilities

Dynamics GP provides a utility for removing General Ledger distribution history records. This will be found on:
Dynamics GP > Tools > Utilities > Inventory > Remove Distribution

 

Remove Distribution History

 

Inventory Distribution Reporting

Depending on the configurations above, you will either be able to print the distribution history report or not. In case your configuration allows inventory distribution, you could print the report at the following path:

Inventory > Reports > History > Distribution History

 

Inventory Distribution History Report

The following script provides a detailed view for the posting account distribution history originating from Inventory Transactions. This view will not retrieve data in one of the following scenarios:

        - Distribution history is not maintained
        - Audit Trail Code setup window is not configured to re-print the posting journal

 

--Tables Included:

--IV30500 | Inventory Distribution History
--GL00100 | Account Master
--GL00105 | Account Index Master

SELECT  C.[DOCNUMBR] 'Document Number' ,
        C.[ITEMNMBR] 'Item Number' ,
           C.[ACTINDX] 'Account Index' ,
        D.[ACTNUMST] 'Account Number' ,
        C.[ACTDESCR] 'Account Description' ,
        C.[POSTEDDT] 'Posting Date' ,
        C.[DEBITAMT] 'Debit Amount' ,
        C.[CRDTAMNT] 'Credit Amount'
FROM    ( SELECT    RTRIM(A.[DOCNUMBR]) AS [DOCNUMBR] ,
                    RTRIM(A.[ITEMNMBR]) AS [ITEMNMBR] ,
                    A.[ACTINDX] ,
                    RTRIM(B.[ACTDESCR]) AS [ACTDESCR] ,
                    A.[POSTEDDT] ,
                    A.[DEBITAMT] ,
                    A.[CRDTAMNT]
                    FROM      [IV30500] AS A
                    LEFT OUTER JOIN [GL00100] AS B ON A.[ACTINDX] = B.[ACTINDX]
        ) AS C
        LEFT OUTER JOIN [GL00105] AS D ON D.ACTINDX = C.ACTINDX
WHERE   C.DOCNUMBR = '#DocumentNumber'

 

Best Regards,
Mahmoud M. AlSaadi