SQL Query To Determine Quantity To Order

I recently had cause to knock together a fairly simple SQL script to determine the quantity to order based on Sales transactions in the system taking into account the Order Point Quantity and Quantity To Order for the Item/Site combination in the Inventory module:

SELECT
   SOP10200.LOCNCODE AS 'Site'
   ,SOP10200.ITEMNMBR AS 'Item Number'
   ,SUM(SOP10200.QUANTITY) AS 'Quantity Required'
   ,IV00102.ORDRUPTOLVL - SUM(SOP10200.QTYTORDR) AS 'Quantity To Order'
FROM
   SOP10200 (NOLOCK)
LEFT OUTER JOIN
   IV00102 (NOLOCK)
      ON IV00102.ITEMNMBR = SOP10200.ITEMNMBR AND IV00102.LOCNCODE = SOP10200.LOCNCODE
WHERE
   SOP10200.QTYTORDR <= ORDRPNTQTY AND SOP10200.QTYTORDR <> 0
GROUP BY
   SOP10200.ITEMNMBR, SOP10200.LOCNCODE, IV00102.ORDRUPTOLVL

MDGP 2013 R2 Feature of the Day: Assign An Item To Multiple Sites

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The thirtieth Feature of the Day covered is Assign An Item To Multiple Sites. This feature will allow the user to select an item and assign it to one, multiple or all sites quickly and easily. The user can also select key item/site default settings for Item Print Option, Primary Vendor and Landed Cost Group during the assignment process.

When I started looking at this feature, it took me a few minutes to actually find it. To assign items to multiple sites open Item Quantities Maintenance (Inventory >> Cards >> Quantities/Sites) and click the Go To’s button on the ribbon and select Site Item Assignments:

Item Quantities Maintenance

Continue reading

MDGP 2013 R2 Feature of the Day: Dashboards

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The twentieth Feature of the Day covered is Dashboards. Four new reporting dashboards have been added to the Excel Reports in Dynamcis GP 2013.

The new dashboards are for the Financial, Sales, Purchasing and Inventory series and are deployed with the other Excel Reports. These refreshable reports can deployed to a shared network drive, SharePoint or SharePoint Online.

The Financial dashboard:

Financial dashboard

Continue reading

MDGP 2013 R2 Feature of the Day: Suggested Item Enhancements – Sales Script and Additional Information

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The nineteenth Feature of the Day covered is Suggested Item Enhancements – Sales Script and Additional Information. The Suggested item enhancements allows the user to add a sales script to each suggested item. This script will give the salesperson more information about the item.

As you can see in the screenshot the Item Maintenance – Suggest Items window (Inventory >> Cards >> Item Maintenance | Suggest Items button) has been updated with a Sales Script field for each item:

Item Maintenance - Suggest Items

Continue reading

MDGP 2013 R2 Feature of the Day: Suggested Item Enhancements – Analysis and Assignment

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The eighteenth Feature of the Day covered is Suggested Item Enhancements – Analysis and Assignment. Suggested Items were introduced in Dynamics GP 2013 and it is really good to see this feature extended further.

The Suggestion item enhancements allows the user to analyze an item and recommends companion items that should be sold with it. The assignment feature uses the suggestions from the analysis and will provide the ability to update the Suggested Sales Items window at the time of the analysis.

One window has been amended and one created for this feature. The amended window is the Item Maintenance – Suggest Items window (Inventory >> Cards >> Item Maintenance | Suggest Items button):

Item Maintenance - Suggest Items

Continue reading

Microsoft Dynamics GP 2013 R2 Coming Soon

The next version of Microsoft Dynamics GP, 2013 R2, is going to be released soon. I can hardly wait, but, unfortunately, I have to :(

The Dynamics GP Support and Services Blog have posted an article today outlining their intended blog post schedule where they are going to run through the upgrade process and all of the new features which are due in the coming version.

  • April 29 – Lifecycle
  • May 1 – Upgrade Planning
  • May 6 – Known Upgrade Issues
  • May 8 – Version Checks & Upgrades
  • May 13 – Database Upgrade
  • May 15 – Modified Dictionary Upgrade
  • May 20 – Upgrade Troubleshooting
  • May 22 – GP is upgraded – what’s next?
  • May 27 – Upgrading Web Client
  • May 29 – Tables Changes in R2 – Review the SDK
  • June 3 – Identity Management
  • June 4 – Azure Backups
  • June 5 – New Workflow
  • June 10 – Requisition Management
  • June 11 – OLE Document Attach
  • June 12 – Human Resource Time Entry
  • June 17 – Project Accounting Time Entry
  • June 18 – General Ledger
  • June 19 – Payables Management
  • June 24 – Receivables Management
  • June 25 – Bank Reconciliation
  • June 26 – Analytical Accounting
  • July 1 – Fixed Assets
  • July 2 – Sales Order Processing
  • July 3 – Inventory Control
  • July 8 – Purchase Order Processing/Encumbrance Management
  • July 9 – Email or Print any report in Word
  • July 10 – SmartList Designer 2.0
  • July 15 – Office 365
  • July 16 –Take Company Offline
  • July 17 – Ribbons in Desktop

MDGP 2013 Feature of the Day: Document Attach

Microsoft are posting the Microsoft Dynamics GP 2013 Feature of the Day series on the Inside Microsoft Dynamics GP Blog.

The sixty eighth feature they’ve announced is Document Attach.

You can now easily and efficiently attach documents to a Purchasing or Sales transaction or Purchasing, Sales or Inventory master records. This feature creates the foundation for Document Attachment and the necessary additions to address Document Attachment for the Procure to Pay & Quote to Cash process and Inventory card.

The feature supports the scan process “Scan to be attached later”. “Scan to be attached later” – Is the process when documents are scanned using either a desktop or bulk multi-functional scanner to a storage location. At a later time the image is attached to a transaction or record in the ERP system.

Document attachment will store the initial scans in a file location and then upon attaching to the Microsoft Dynamics GP 2103 record, the image will store the attachment in a new SQL table specific to Document Attach. Attachments are stored by company.

Easy access is achieved from the transaction or record.

The default location for the documents to be attached can be specified in a new Document Attachment Setup window;

Document Attachment Setup

When entering a Purchase Order, for example, a new button is available in the scrolling window;

Purchase Order Entry

Which opens the Document Attachment Management window where the user can specify the files to be attached;

Document Attachment Management

This new feature is targeted at the following roles:

  • Warehouse Manager
  • Accounting Manager
  • Purchasing Agent
  • Shipping and Receiving
  • Accounts Payable Coordinator
  • IT Manager

MDGP 2013 Feature of the Day: Return Quantity Types

Microsoft are posting the Microsoft Dynamics GP 2013 Feature of the Day series on the Inside Microsoft Dynamics GP Blog.

The sixty seventh feature they’ve announced is Return Quantity Types.

Prior to Microsoft Dynamics GP 2013, RMA returned items would default to On Hand quantity type. Now you can return quantities to other inventory quantity types to more accurately account for the return. For example, if a return is because of a damaged product, you’ll be able to return the item to the Damaged quantity type instead of the always returning to On Hand. You can set up a default quantity type for returns, which then can be changed at RMA Receiving.

The inventory quantity types can be handled separately in terms of RMA processing for scrap, RTV or Work orders. You can also change the quantity type from the Process Returns window.
The quantity type is visible from the RTV or the Work Order as well.

RMA Type Maintenance

Continue reading

MDGP 2013 Feature of the Day: Honour Discontinued Item Status In Field Service

Microsoft are posting the Microsoft Dynamics GP 2013 Feature of the Day series on the Inside Microsoft Dynamics GP Blog.

The sixty sixth feature they’ve announced is Honour Discontinued Item Status In Field Service.

In Microsoft Dynamics GP 2013, when a discontinued part is entered on a service call or depot work order, the system searches the requested warehouse and the restocking warehouses to determine quantity available. When the quantity available is less than the quantity entered a window will appear showing you the quantities available so that you are able to make a decision such as changing the quantity entered or substituting the item.

Discontinued Items

This new feature is targeted at the following roles:

  • Customer Service Rep
  • Customer Service Manager
  • Order Processor
  • Purchasing Agent
  • Purchasing Manager
  • Accounts Payable Coordinator
  • Shipping and Receiving
  • Warehouse/Inventory Manager
  • Accounts Receivables Administrator

MDGP 2013 Feature of the Day: Inactivate Item/Site

Microsoft are posting the Microsoft Dynamics GP 2013 Feature of the Day series on the Inside Microsoft Dynamics GP Blog.

The sixty fourth feature they’ve announced is Inactivate Item/Site.

Inactivate a Site – This feature allows a customer to keep the site in the system for historical transaction analysis. Marking the item/site inactive will stop an end user from posting transactions for the site;

Site Maintenance

Continue reading