A customer needed a new Smartlist showing their inventory quantities on hand analysed by Lot Number and site in order to complete monthly stock takes. This is the view I came up with, hope its of use to someone else. The total quantity on hand should reconcile to the out of the box Item Quantities Smartlist in GP.

___________________________________________________________________________________________

create view [dbo].[IS_InvOnHand_LotNo]
as

/********************************************************************
view: IS_InvOnHand_LotNo
Created on Nov 21, 2017 by Ian Stewart
Inventory quantities on hand analysed by Lot Number and Site. Showing the Quantity on hand,
the total quantity in, the total quantity out – analysed by Sales and Returns.

Tables used:
A – IV00300 – Item Lot Number Master
B – SOP10201 – Sales Serial/Lot work and history
C – IV00101 – Item Master

The results of this view should reconcile to the total On Hand quantity from the Item Quantities
Smartlist delivered out of the box with GP.

********************************************************************/

SELECT
A.ITEMNMBR AS ITEM,
C.ITEMDESC AS DESCRIPTION,
A.LOCNCODE AS SITE,
A.LOTNUMBR,
A.EXPNDATE AS EXPIRY_DATE,
(A.QTYRECVD-(CASE WHEN A.QTYTYPE = 1 THEN A.QTYSOLD ELSE (A.QTYRECVD) END)) AS QTY_ON_HAND,
A.QTYRECVD AS QTY_IN,
CASE WHEN A.QTYTYPE = 1 THEN A.QTYSOLD ELSE (A.QTYRECVD) END AS QTY_OUT,
A.UNITCOST,
A.QTYTYPE AS QTY_TYPE,
A.QTYRECVD AS QTY_RECEIVED,
A.QTYSOLD AS QTY_SOLD,
CASE WHEN A.QTYTYPE = 2 THEN A.QTYRECVD ELSE (0) END AS QTY_RETURNED,
C.ITMSHNAM AS SHORTNAME,
C.ITMGEDSC AS GENERIC_DESC,
C.ITMCLSCD AS CLASS_ID

FROM IV00300  A
left JOIN SOP10201 B on B.ITEMNMBR = A.ITEMNMBR and B.SERLTNUM = A.LOTNUMBR
left JOIN IV00101 C ON C.ITEMNMBR = A.ITEMNMBR

group by A.ITEMNMBR, A.QTYRECVD,
A.QTYSOLD, A.LOTNUMBR, A.LOCNCODE,
A.QTYTYPE, A.UNITCOST, C.ITEMDESC,
C.ITMSHNAM, C.ITMCLSCD, C.ITMGEDSC,
A.EXPNDATE

 

GO

 

 

 

 

 

Advertisement

On 1 January 2018, VAT will come on stream in the Middle East. There have been a lot of quastions asked about this and Dynamics GP readiness in the community forums lately.

The short answer is that Dynamics GP has everything you need to record and account for VAT for the Middle East already built in (and not just in the latest version). The core Dynamics GP tax engine suffices to set up and transact with VAT for the Middle East. The VAT Daybooks module extends this functionality to allow for additional reporting and most importantly auditing of your VAT returns.

You do need to complete some set up procedures (VAT Details and schedules, assigning to Customers, Vendors, Items, and configuring VAT Daybook reports to suit etc.) following an analysis of your requirements, but once completed, VAT recording and reporting should be seamless.

One issue is the out of the box VAT Return reports. There are examples pre-configured for European countries. These may or may not suit your local Middle East / UAE requirements, but they can be edited or you can utilise SSRS. However, most users don’t use these system generated returns, instead using the VAT Daybook reports or a smartlist as the basis of their paper based or online returns.

 

Quick one, but its a hot topic with users. Dynamics GP 2015…Workflow 2 will include out of the box approvals for General Ledger batches [general journals, clearing journals and budget transactions]. Payables and Receivables transactions and Vendor account approval are all included.

By the way, the new Organisational Account User Verification with Dynamics GP 2015 can be used with Workflow. So the same user credentials you use to access the web client, office 365 and other cloud applications can be used with workflow tasks and approvals assignments.

Came across this one yesterday. Absolutely useless error message! Went around the houses trying to resolve – re-adding users, changing their windows security, reinstall, everything I could think of. What I found by pure chance is that if you add a new font format type to your install of MR – call it anything [I called mine ‘test’] – the error goes away and you can successfully print reports.
[Click format on the MR menu bar]. I found it because we had two separate installs with the same issue. One also needed a new font added, which I did, and then I went back to the resolve the original error, and it was solved. Only thing I did was add a font format – tried it on the other install, and bingo!

Go figure that one out!

Came across this issue on a client site recently. Difficult to track down, and we went around the houses for a few weeks! The upshot is that the Due Days on tasks do not update correctly if the task is progressed through SharePoint. If the task is updated through the Dynamics GP 2013 interface, all is well.

Bug diagnosis:
When a user approves a Purchase Order in Dynamics GP, the system assigns a due date for the approving tasks based on the time limit set in the approval workflow setup on the Workflow configuration site [The correct behaviour], but if a user approves a Purchase Order through the Workflow tasks site, the system assigns the same due date as the date the Purchase Order was submitted.

So, what does this mean for Dynamics GP 2013 users who are running [for example] a Purchase Order Approvals workflow?

The first step is creating the Purchase Order in Dynamics GP and submitting it for approval. All good, a SharePoint task gets created with the correct due days and is routed to the first approval level. An email goes out to the approver notifying them that there is a Purchase Order awaiting their approval. They have a choice…skip to the Workflow site direct from the email and approve the Purchase Order…or log into Dynamics GP and approve the Purchase Order there. If they skip out to the workflow site and approve the Purchase Order, it will error out and reset the due date to be the same as the original submission date. The task then becomes stranded and the document cannot be progressed. If they log into Dynamics GP and approve the Purchase Order there, there are no issues.

This is a bug, and don’t hold your breath for it to be fixed. With the upcoming new workflow system in Dynamics GP 2013 R2 – this bug becomes irrelevant as the whole system is being redesigned and SharePoint is being dropped from the equation. Hence…don’t hold your breath. If you are currently running workflow – then you have a problem. You need to approve Purchase Order’s through the Dynamics GP interface.

Rider!…This may be related to instances where there are multiple levels to the approval matrix, but to be honest we haven’t had the time to fully define the issue and recreate it in a clean test environment. I’d welcome all and any comments.

In Microsoft Dynamics GP 2013 Service Pack 2 – there is a ‘bug’ in the Manufacturing module Labor code maintenance window. The Radio Buttons for selecting if overheads are amounts or percentages are missing on the standard window. This is caused by the Tab Sequence on this window not being set correctly.

To correct this issue temporarily while Microsoft issues a patch – open the window in Modifier, [tools >> Customise >> Modify current window] and select Layout >> Set Tab Sequence. Now the fun begins.

Hit the tab key to start tabbing through the fields. Tab until the Fixed Overhead field gets highlighted. Now is the time to interrupt the tab sequence and set it straight. Double click the Fixed Overhead field [highlighted in Red above] until the Radio Group is selected [Tip: Double click the top right hand corner of the red box above]. Then hit tab. Double click the first Radio option, tab, and double click the second radio button. Then tab through to the Variable Overhead field and repeat. You will need to do this for the 4 Radio Groups on this window.

Once complete, click Layout >> Set Tab Sequence again. save and close the modified window and return to Dynamics GP.  Then go to GP 2013 Security – and grant the user access to this new modified window. It might take a few attempts to get it right. If you get tied up in knots, delete the modified window from Modifier and reinsert the original to start from scratch.

Also take a look at David’s post on setting tab sequences.

Smartlist Designer for Dynamics GP, a nice addition, has a wee bit of a problem!

There seems to be a limit on the length of the query that can be built when you link one or more tables. So, if you select two tables, link them, mark all fields to be included in the output and then look at the SQL Query at the bottom of the window…the SQL query truncates at a certain number of characters.

By removing sufficient fields from the selection, you will eventually get a complete query. I haven’t bothered to count the max number of characters allowed because I just couldn’t be bothered, and I don’t know if this is a bug or ‘by design’!! But if your new smartlist won’t run correctly – maybe its truncated.

The Scenario: GP 2013. 2 Terminal Servers that all users use to access GP. They use the Transactions >> Purchasing >> Print Purchasing Documents window to print their Purchase Orders en masse.

The Problem: User opens this window, selects a range of PO’s…and clicks print. The print dialogue window opens as per normal and they select Screen / Printer / File…it doesn’t matter which. They click OK to print and….nothing happens!!

The Weird Bit: One domain User on one Terminal server…can print!! No one else can. It doesn’t matter what GP user ID they use to log into GP…It seems to be connected to the Domain User account. Go figure. Also – all users can print the alignment form from his window, or can go to the PO Entry window and print an individual PO there…but when it comes to printing a range of PO’s, nothing happens.

The Solution! [discovered by Babu] On the ‘good’ Terminal Server that users Date and Time format was set to US – this is the one that worked. On the other Terminal Server it was set to UK. All other domain users…were set to UK on both Terminal Servers. Changing any one of these to US settings – and they could print the PO’s.

The Competition: Guess how long it took us to figure this one out!

Update…..David Musgrave has a post which describes a similar issue and the reasons why!!