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