Adding Fixed Asset from Purchase Order Processing (Multiple Assets per Shipment)

In a previous post, adding fixed asset from the Payable Management has been thoroughly illustrated. Although, this is not efficient in case you want to add multiple assets. Suppose you want to define a hundred computers, if you go through the AP process then you will post one hundred invoices ! Alternatively, you can consider another approach by adding several line items on the Receiving Transaction Entry window.

Again, it is a common mistake that the “Purchase” process is directly applied from the Fixed Asset module. The truth is, the invoice (Receiving or AP invoice) initiate the process and specifically the distribution accounts.

Receiving Transaction Entry

The first step is to create a receiving transaction entry with several line items. Go to Transactions > Purchasing > Receiving Transaction Entry window.

• Choose the vendor ID
• Enter the vendor document number
• Enter the document date
• On the line items grid, enter as many line items as required which will be created later on as assets on the fixed asset module.

For each line item, you have to consider the following options:

• The item filled in this line is a non-inventory item, which means that it should not have be defined on the item card maintenance window
• In order for the item to be considered as a FA, you should check the “Capital Item” option. To do so:
  • Place the mouse cursor on the line item
  • Click the arrow highlighted in the screen show below to reach the Receiving Item Detail window
    
    
  • Now on the receiving item detail window:

  

 

Helping Note !
On the example above, we have included a quantity received of (5) for the first line item. When clicking the “Capital Item” checkbox, the system will consider the 5 pieces as one asset. If you want to split the 5 pieces into five assets, click on the arrow next to the “Capital Item” to reach the FA PO Additional Information.

The Fixed Asset clearing account is an offset account which is used between the Purchasing and Fixed Asset module. It should be setup first  on Microsoft Dynamics GP> Tools > Setup > Fixed Asset > Purchasing Posting Account.

The final step is to check the document distribution and make sure that the “Fixed Asset Clearing” account number is included under the “PURCH” distribution line. Then, you can proceed with posting the receiving transaction once all the line items are configured accordingly.

 

Fixed Asset – General Information Window

As you move on to the Fixed Asset window, the “Purchase” button will be activated indicating that there are pending purchases to be defined as assets. In my example above, the first line item was checked to split quantity received into multiple assets.

Go to Cards > Fixed Asset > General

 

You will have to add the assets one by one and they will inherit any FA details that were defined initially on the receiving transactions along with the acquisition cost.

Then, you will have to move on with the usual cycle of FA GL Posting, then General Ledger posting which have been thoroughly illustrated on the previous post Adding Fixed Assets from Payable Management

Best Regards,
Mahmoud M. AlSaadi

Purchase Order Receiving and Invoices Details – SQL Script

In this post, an SQL Script is provided to list down all the purchase orders along with the associated details related to the POP transactions (Shipment, Shipment invoices …etc). The graph below shows the overall fields retrieved.

/*----------------------------------------------------------------------------
Creation Date: 27th of September, 2014
Created by: Mahmoud M. AlSaadi

The main purpose of the script is to provide a detailed report for the
Purchase order along with the associated receiving and invoices details

The script has been tested on a very limited sample data.
Please feel free to contact me for any further enhancement

Revision History:
Revision No.         RevisionDate    Description
1                    27/09/2014           Original Version
----------------------------------------------------------------------------*/

Tables Included:

• POP10100 | Purchase Order Work
• POP10110 | Purchase Order Line Work
• POP30310 | Receipt Line History
• POP30300 | Receipt History Header

SELECT  A.[PONUMBER] ,
        CASE A.[POSTATUS]
           WHEN 1 THEN 'NEW'
           WHEN 2 THEN 'RELEASED'
           WHEN 3 THEN 'CHANGE ORDER'
           WHEN 4 THEN 'RECEIVED'
           WHEN 5 THEN 'CLOSED'
           WHEN 6 THEN 'CANCELED'
         END AS POSTATUS ,
        CASE A.[POTYPE]
           WHEN 1 THEN 'STANDARD'
           WHEN 2 THEN 'DROP-SHIP'
           WHEN 3 THEN 'BLANKET'
           WHEN 4 THEN 'BLANKET DROP-SHIP '
        END AS POTYPE ,
        A.[DOCDATE] AS Date ,
        A.[PRMDATE] AS PromiseDate ,
        A.[REQDATE] AS RequestedDate ,
        A.[REMSUBTO] AS RemainingSubTotal ,
        A.[SUBTOTAL] AS SubTotal ,
        A.OREMSUBT AS OriginatingRemainingSubTotal ,
        A.ORSUBTOT AS OriginatingSubTotal ,
        A.[VENDORID] AS VendorID ,
        A.[VENDNAME] AS VendorName ,
        A.[DUEDATE] AS DueDate ,
        A.[CURNCYID] ,
        A.[BUYERID] ,
        CASE B.[POLNESTA]
           WHEN 1 THEN 'NEW'
           WHEN 2 THEN 'RELEASED'
           WHEN 3 THEN 'CHANGE ORDER'
           WHEN 4 THEN 'RECEIVED'
           WHEN 5 THEN 'CLOSED'
           WHEN 6 THEN 'CANCELED'
        END AS POLineStatus ,
        B.[ITEMNMBR] AS ItemNumber ,
        B.[ITEMDESC] AS ItemDescrption ,
        B.[VENDORID] AS LineVendorID ,
        B.[VNDITNUM] AS VendorItemNumber ,
        B.[LOCNCODE] ,
        B.[UOFM] ,
        B.[QTYORDER] AS QuantityOrdered ,
        B.[QTYCANCE] AS QuantityCancelled ,
        B.[UNITCOST] ,
        B.ORUNTCST AS OriginatingUnitCost ,
        B.[EXTDCOST] AS ExtendedCost ,
        B.OREXTCST AS OriginatingExtendedCost ,
        B.XCHGRATE AS ExchangeRate ,
        ISNULL(C.POPRCTNM, ' ') AS POPRCTNM ,
        ISNULL(C.QTYSHPPD, 0) AS QuantityShipped ,
        ISNULL(C.QTYINVCD, 0) AS QuantityInvoiced ,
        ISNULL(C.QTYREJ, 0) AS QuantityRejected ,
        ISNULL(C.QTYMATCH, 0) AS QuantityMatch ,
        ISNULL(C.QTYRESERVED, 0) AS QuantityReserved ,
        CASE C.POPTYPE
           WHEN 1 THEN 'SHIPMENT'
           WHEN 2 THEN 'INVOICE'
           WHEN 3 THEN 'SHIPMENT/INVOICE'
           ELSE ' '
        END AS DocumentType ,
        ISNULL(C.UOFM, ' ') AS UOFM,
        ISNULL(C.DATERECD ,'') AS DateReceived,
        ISNULL(C.PCHRPTCT, 0) AS PURCHASERECEIPTCOST ,
        ISNULL(D.ORUNTCST, 0) AS ORGRECUNITCOST ,
        ISNULL(D.OREXTCST, 0) AS ORGRECCOST
        FROM    dbo.POP10100 AS A
        INNER JOIN dbo.POP10110 AS B ON A.[PONUMBER] = B.[PONUMBER]
        LEFT OUTER JOIN dbo.POP10500 AS C ON B.[PONUMBER] = C.[PONUMBER]
                                         AND B.[ORD] = C.[POLNENUM]
        LEFT OUTER JOIN dbo.POP30310 AS D ON C.[PONUMBER] = D.[PONUMBER]
                                         AND C.[RCPTLNNM] = D.[RCPTLNNM]
                                         AND C.POPRCTNM = D.POPRCTNM
        LEFT OUTER JOIN dbo.POP30300 AS E ON D.[POPRCTNM] = E.[POPRCTNM]

 

Download Link |  Purchase Order Details Script

Best Regards,
Mahmoud M. AlSaadi

Dynamics GP Web Client – Manage User Sessions

When a user opens the web client, they receive a message indicating whether there are any open sessions, this leaves the choice for user to manage their own sessions which usually results with a mess. The below message is received when a user first login to the Dynamics GP Web Client

“You have an existing Dynamics GP Session. If you want to connect to an existing session, select the session in the list and click the connect to existing session button. If you want to create a new session, click the create new session button.”

In this essence, the user is left to choose and they usually decide to create a new session, at least what I have seen quite often. The result is not quite promising as shown below:

In order to get things settled, you will have to often make proper communications with the customer and provide awareness sessions that they are not supposed to leave open sessions.

1. The user shall sign out rather than closing the browser
2. System admin should check the web management consol to get rid of idle open sessions
3. Communications with the users.

Proposed Alternative Solution

In the desktop version of Dynamics GP, the user is not allowed to have more than one open session to a specific company, which is managed by the Activity reference. When a user renter Dynamics GP, if there is an open session, they will have to delete it first in order to be able to login.

The web client should behave similarly. Although it’s technically different, but from a process perspective the same rule should be applied regardless of the way a user logs in the company.

I have created a request on MS Connect to get this functionality in future releases. If you are convinced that this will be of an added value, share your vote on MS Connect – ID 983432 | Prevent multiple open sessions per user on Dynamics GP Web Client.

Best Regards,
Mahmoud M. AlSaadi

Production Traceability – Lot number tracking “Auto Generate”

The importance of production traceability is well distinguished for those who work in the manufacturing domain, it is considered as an obligation in several countries that you have to be able to track your manufactured items in order to meet specific international standards. In specific standards, there are labeling requirements for the products depending on their category (food, medicine …etc)

In Dynamics GP, such standards are met through well designed functionality. I am shedding a light in this post on the Auto Generate feature in GP 2013 which enables you to code your lots through a systematic method.

For instance, a lot mask could include several sub-codes as part of the label as a whole. Such as:

• Production date (Year and Month)
• Prefix indicating the product category
• Serial Number

You will have to follow the following steps in order to implement the criteria mentioned above.

1. Go to Item Card Maintenance window (Cards > Inventory > Items)
2. You will have to initially make sure that the item is set to be tracked by lot number
   
   
    
   
3. To do so, you will have to pick the ‘lot number’ option as shown above in the screenshot
4. Lot number mask: in order to define the criteria by which the lot number will be rules, click on the arrow beside the tracking option to reach the ‘Item Serial/ lot number definition’. You may insert multiple segments for the lot definition, usually ending with an incremental serial number from
   
   
   
   
5. Now once all you settings are saved. The lot number will be generated according to the criteria considered above
6. Suppose you will proceed with receiving a shipment for this specific item, the system will prompt a screen to enter the lot details. There is a new button to automatically generate lot number according tot he mast you already configured in previous steps

At the end, keep in mind that the business process and procedures required to manage and control lot number tracking are to be considered initially before activating the lot number tracking on the system level. I have seen several businesses failing to manage their lots physically, which leads to dreadful results in keeping up with the lot entry at every window in the distribution modules.

 

Best Regards,
Mahmoud M. AlSaadi

Unit Accounts – Statistical data really matters

The ERP has never been only a financial system, its reporting capabilities should not be limited to a statement of financial position, profit and loss, or cash flow. Statistics and numbers provide comprehensive readings for managers and directors as an essential input in the decision making process.

In one of the projects I have been working on recently, several point of sales; integrated with a backbone Dynamics GP system, manage the daily sales transactions. A huge number of sales transactions are migrated on real time bases to Dynamics GP

 

One of the branches above is a coffee shop, with high level standards regarding customer satisfactions, the service and joy of time provided is their main asset in which they invest. They look for a system to provide important readings for this specific purpose. One of the inquiries I have received is indicated below:

“I don’t want a balance sheet or income statement, that is important but traditional. My sales might be increasing as the income statement might show. Although, I might be loosing loyal customers !”

For me, when it comes to statistics in Dynamics GP, something comes to my mind immediately, which is “Unit Accounts”. Unit accounts are completely similar to the common posting accounts, they are both used for transaction entry, you can keep budget for them as well. Although, Unit accounts are not used to track financial information, they track statistical data which can be compared later on to financial data. The following notes are to be considered regarding unit accounts:

• They can be used for sales and purchasing series (AP, AR)
• They can be used for General Ledger Journals
• When you enter a unit account along other posting accounts on the distribution tab, the total of financial amounts (Dr and Cr) is not affected. Since unit accounts track quantities not financial data

 

Now back to the case study, how this can be enhanced to cover such a requirement related to the number of daily customers versus total sales ?
Practically speaking, the daily sales of the POS is migrated to Dynamics GP as AR invoices with cash receipts (inventory is not tracked in this example). Within the distribution (Cash and Sales), another line is added for a unit account called “Number of customers”

 

I will show below one of the daily sales transactions along with the distribution:

 

Note !
The number above unit the unit account indicates that there were 190 customers visiting the coffee shop at the 19th of December. This number doesn’t affect the financial numbers recorded for both (Cash and Sales)

After all, it was important to provide a report linking the daily total sales with the number of customers visiting the coffee shop, to be used as an important indicator of customer satisfaction, here is the result:

Apparently, the increasing trend of sales amount is not an indicator of ultimate profit, the business is losing customers in the first half of the month. After corrective actions were applied the daily sales amount went even higher with an obvious increase of customers as well.

Statistics really matters !

Best Regards,
Mahmoud M. AlSaadi

User Activity Report - Idle and Login Details SQL Script

It’s a common issue to have users logged into Dynamics GP without closing the system for several hours, and sometimes for days. The user activity inquiry doesn’t provide much in-depth details regarding the Idle time and login date and timing. In this post, an SQL script is provided to cover this concern as illustrated below:

 

 

 

 

Tables Included:

• DYNAMICS..ACTIVITY
• DYNAMICS..SY01400  | USER Master
• DYNAMICS..SY01500  | Company Master
• Tempdb..DEX_SESSION
• master..sysprocesses
• master..sysdatabases

/*---------------------------------------------------------------------
Creation Date: 6th of September, 2014
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to provide a user activity report,
along with the Idle and login details
The script has been tested on a very limited sample data.
Please feel free to contact me for any further enhancement

Revision History:

Revision No.         RevisionDate  Description
1                                 06/09/2014           Original Version 
2                                 15/09/2014           Modified Version 
---------------------------------------------------------------------*/

        SELECT  RTRIM(A.USERID) AS UserID ,
        RTRIM(B.USERNAME) AS UserName ,
        RTRIM(C.INTERID) AS CompanyDatabase ,
        RTRIM(C.CMPNYNAM) AS CompanyName ,
        LOGINDAT AS LoginDate ,
        CONVERT(VARCHAR(1000), DATEPART(HH, LOGINTIM)) + ':'
        + CONVERT(VARCHAR(1000), DATEPART(MI, LOGINTIM)) AS LoginTime ,
        ISNULL(CONVERT(VARCHAR(1000),E.last_batch),'') AS SQL_LastBatch ,
        CASE WHEN D.session_id IS NULL
        THEN 'Corrupted Missing DEX_SESSION'
        ELSE CONVERT(VARCHAR(1000),session_id)
        END DEX_SESSION ,
        CASE WHEN CONVERT(VARCHAR(1000),E.SPID) IS NULL
        THEN 'Corrupted SQL_SESSION'
        ELSE CONVERT(VARCHAR(1000),SPID)
        END SQL_SESSION ,
        CASE WHEN DATEDIFF(mi, E.last_batch, GETDATE()) > 1
        THEN DATEDIFF(hh, E.last_batch, GETDATE())
        ELSE 0
        END AS 'IdleTime - InHours' ,
        CASE WHEN DATEDIFF(MI, LOGINDAT + LOGINTIM, GETDATE()) > 1
        THEN DATEDIFF(HH, LOGINDAT + LOGINTIM, GETDATE())
        ELSE 0
        END AS 'Logged in for – InHours'
        FROM    DYNAMICS..ACTIVITY A
        LEFT JOIN DYNAMICS..SY01400 B ON A.USERID = B.USERID
        LEFT JOIN DYNAMICS..SY01500 C ON A.CMPNYNAM = C.CMPNYNAM
        LEFT JOIN tempdb..DEX_SESSION D ON A.SQLSESID = D.session_id
        LEFT JOIN master..sysprocesses E ON D.sqlsvr_spid = E.spid
        AND ecid = 0       
        LEFT JOIN master..sysdatabases F ON E.dbid = F.dbid

 

Best Regards,
Mahmoud M. AlSaadi

Changes to Void Historical Payables Transactions throughout GP versions

A minor yet limiting addition introduced in Dynamics GP 2013 for the void process of historical payable transactions made a lot of noise among Dynamics GP clients, because it simply broke the habit of voiding sequential payables transactions (such as checks) which are not necessarily issued to the same vendor. Here is a summary for the different phases of the void historical payable transactions.

GP 2010

Vendor ID was not a requirement in order to void a historical payable transactions. You can simply void several transactions of several vendors by clicking the associated button below.

GP 2013 and 2013 SP2

The new feature was released, I would personally not prefer to call it a feature since it is nothing but a simple “validation” on the void process. Here if you don’t pick up the vendor, you will not be able to void a historical payable transaction. You can not void a list of payable transactions for different vendors.

GP 2013 R2

In the latest release of Dynamics GP R2, the vendor remains but not as a required field anymore, as it was back in GP 2010, you can simply void multiple transactions for one or different vendors.

 

Best Regards,
Mahmoud M. AlSaadi