Importing Fixed Assets which are fully depreciated in Prior Years.

 

When importing Fixed Assets into Dynamics GP, several assets may be fully depreciated in “prior years”, which means (Depreciated to Date) will fall in prior years.

Based on your calculation, the asset is supposed to be fully depreciated at (let’s say) 10/02/2005.

The following error message pops up stating that (Depreciated to Date) should be the last day of the year, as shown below;

 

Solution:

All dates within prior years won’t be accept but (31/12). Therefore, regardless of the date at which the asset was fully depreciated, the depreciated date should be the last day of the year.

Helping Note!

When changing sensitive fields data, the depreciated to date value may be recalculated again and set to other than the last day of the year. This needs to be considered when considering mass entry tool such as Macro. Refer to support Article ID: 858354.

Best Regards,
Mahmoud M. AlSaadi

Inventory Transaction - User Who Posted !

 

Mostly in all Dynamics GP Modules, the information of “Who Posted This Transaction” is always at the tips of our fingers. Although, this piece of details is not available when digging in inventory tables.  Does it mean that I can not know who posted an inventory transactions ? The answer is simply, You can.

When the posting setup is configured to Post Through GL, the transaction automatically generates associated Journal Entries. In this essence, we can still get this information from GL Table, because the user who posted the IV transaction is the same who generated the GL journal entry.

The SQL Script below retrieves the following details for every single inventory transactions;

• Source Document
• IV Document Source
• IV Document Number
• Journal Entry
• Transaction Year
• Open/Closed Year
• Last User (Last user who edited the transaction)
• Last Edit Date
• User Who Posted (The user who actually posted the IV transaction )
• Posting Date

 

In order to retrieve the following details for a certain document number/s, include them in the “Where” condition in the following SQL Statement.

--- Tables Included:

• GL20000: Year to Date Transaction Open
• GL30000: Account Transaction History
• SEE30303: Historical Inventory Trial Balance

SELECT * FROM
(
    SELECT  DISTINCT
            SOURCDOC AS SourceDocument,
            ORGNTSRC AS IVDocumentSource,
            ORCTRNUM AS IVDocumentNumber,
            JRNENTRY AS JournalEntry,
            OPENYEAR AS TransactionYear,
           'Opean Year' AS 'Open/Closed Year',
            LASTUSER AS LastUser,
            LSTDTEDT AS LastEditDate, 
            USWHPSTD AS UserWhoPosted,
            ORPSTDDT AS PostingDate
    FROM GL20000
    UNION ALL
    SELECT  DISTINCT
            SOURCDOC,
            ORGNTSRC,
            ORCTRNUM,
            JRNENTRY,
            HSTYEAR,
            'History Year',
            LASTUSER,
            LSTDTEDT, 
            USWHPSTD,
            ORPSTDDT           
    FROM GL30000
)   AS A 
    WHERE A.JournalEntry IN
    (
     SELECT DISTINCT JRNENTRY
    FROM SEE30303
    WHERE DOCNUMBR = '#IVDocumentNumber'
    )

 

Helping Note !

The script considers the SEE30303 table. In case the HITB is not populated, you need to change the “WHERE Condition” in the script above to retrieve the IV document number from GL tables as follows;

WHERE A.IVDocumentNumber = '#IVDocumentNumber'

 

Best Regards,
Mahmoud M. AlSaadi

GP 2013 Document Attachments – FAQs

 

With GP 2013, document attachment has been widely considered as a desired feature. In this post, I am including common inquiries and answers about this feature.

Q1 - Where to find illustrative resources explaining document attachment feature on a step by step basis ?

On Microsoft Dynamics GP 2013 Guide: System User's Guide, chapter 19 | Document Attachments. The following content is thoroughly illustrated;

Q2 - Can I attach documents to payable transactions Journal entries ?

Until GP2013 SP2, document attachment doesn’t include neither payable transaction nor General Entry in GL. The following master records and transactions are supported by this feature.

Although, several suggestions have been provided on Microsoft Connect to add this feature for payable and General Entry.

Q3 - What are the document attachment tables ?

• CO40100 | Document Attachment Setup
• CO00101 | Document Attachment Master
• CO00102 | Document Attachment Reference
• CO00103 | Document Attachment Properties
• CO00104 | Document Attachment Status
• CO00105 | Document Attachment E-mail

Q4 – When I attach a document, the original file is deleted !

That’s simply a configuration check, on the Document Attachment setup screen (Microsoft Dynamics > Tools > Setup > Company > Document Attachment), there is an option to delete original attachment, if checked, the original will be deleted. As shown below;

Q5 - What is the “default location” field on the setup window for Document Attachment?

When you click on attachment icon either on a master record or transaction, the “Select an attachment”window will default to retrieve all the files in this path, from which you will attach a selected document.

Q6- When I delete an attachment, the file is still be shown on the “Deleted” tab, how can I completely remove an attachment ?

Dynamics GP manages the deletion of attachments by categorizing the deleted documents in a separate tab, which can still be shown on the document attachment window.

To completely remove an attachment, I would go for a database delete statement which is well considered.

Q7- Where are the attachment stored ?

Document attachment in Dynamics GP considers SQL Server solution for storing files and documents in the database.

Q8- What does the Document Attachment Utility do ?

Although it doesn’t provide any message or notification on completion, but if you follow the process on SQL Profiler, you will find that it does check all the attachments which fall (on or before) a date specified at the utility window, any of these with no link to a certain transaction or master record will be permanently deleted.

Best Regards,
Mahmoud M. AlSaadi

Requisition Attachments

 

When using requisition management as a solution to manage Approval Hierarchy, clients commonly ask for attachment. Unfortunately, this very simple feature is not yet provided as an out of the box functionality in Business portal, Requisition Management. What is the simple, efficient and effective work around solution for this !

Simply, create a separate SharePoint list that is not at all connected to the requisition management site. The list has got to include the following details;

Requisition Number: which is the auto number generated when a new requisition is created, unique and could be the primary key.

Purchase Order Number: once a requisition is finally approved and migrated into Dynamics GP, a purchase order number is generated in GP. This piece of detail can be recorded in the list above for “mapping purposes”

Attachment: simply attach the files; image, PDF, excel or whatever document you want to keep for a specific requisition.

In a previous post, Business Portal Requisition, the requisition life cycle is shown. The steps are;

1. Create Requisition
2. Submit for Approvals
3. Approvals
4. Final Approvals
5. Transfer to PO

Applying this solution will add up an addition step to the five steps above in order to “manually” attach documents to requisition.

For the mean time, although this is a manual work-around, but it won’t take more than five minutes to be created and deployed, to meet the need of requisition attachments.

Best Regards,
Mahmoud M. AlSaadi

GP Cards Configuration – Automated Paperless Solution (2 of 2)

 

In the previous post, GP Cards Configuration – Automated Paperless Solution (1 of 2) the proposed solution has been illustrated without digging into considerable details. In this post, practical cases studies on both; Item Card and Vendor Card are illustrated.

Item Card Configuration | InfoPath Form

The following template represents how “Item Card Form” can be automated with InfoPath designer. Rules are validations are built in the form to ensure that only correct data is entered.

The InfoPath form is divided into four basic sections, each of them is dedicated to receive data from a certain node of the approval chain.

1. Item card details: this section should be filled out by any employee in the company who deals with new items. It could be the warehouse keeper, purchasing clerk, production supervisor… etc.
2. First Level Approval: this section is dedicated to retrieve the first level approval.
3. Second Level Approval: in case there is more than one approval to configure a new item on the system, this section is dedicated to retrieve the second approval.
4. GP Item Card Configuration: once all approvals are corrected, GP administrator can proceed with the configuration of the item on the system, and fill out the fields within this section accordingly.

On the form completion, a notification mail should be forwarded to all associated parties within the company including the “new item code” and associated details.

Below, is the illustration of the various rules that can be applied on the form as part of the validation layer.

 

Vendor Configuration | InfoPath Form

In addition to the form above, another template is provided below that represents the automation of the vendor card of Dynamics GP.

 

Following with the same criteria, customer, fixed asset and GL Account template can be created on InfoPath along with the validation layer accordingly.

Once the InfoPath for is designed and created. It won’t take more than a few minutes to publish the form into SharePoint document library.

In SharePoint Designer, workflow steps can be outlined to ensure that the old-fashioned paper base system is replaced with an effective automated solution.

Best Regards,
Mahmoud M. AlSaadi

GP Cards Configuration – Automated Paperless Solution (1 of 2)

 

When implementing an Enterprise Resources Planning System, clients are mostly keen to replace their current paper work with automated solutions which are  fully integrated with the backbone ERP system of the business, in order to  effectively and efficiently manage the Approval Chain.

In Dynamics GP, configuring a new customer, vendor, item, fixed asset or GL account always requires sensitive data to be filled out and approved before being configured on the system, such data will highly affect the quality of the transactions and reports generated.

In this series of two posts, I will try to explain the value of using certain MS products together to provide a very effective automated solution for managing Approvals specifically for GP Cards (customer, vendor, FA, item and GL account ).

Below is a proposal for using three common MS products together to provide the maximum added value to the business with Dynamics GP, these products are:

 

1- Form Management :

The very essential value of InfoPath is to replace the paper forms with elegant e-forms, with a validation layer within to ensure that the data filled out is correct.

2- Document Repository:

elegant e-forms require an online repository for archiving the documents. SharePoint library can be the best mate for InfoPath.

InfoPath forms can be published into SharePoint Library, as shown below;

What’s valuable about Publishing an InfoPath form into SharePoint Library is that “meta data” within the form can be published into a grid view in the document library, which gives powerful filtration and search capabilities.

3- Workflow Management:

Finally and most importantly, the approval cycle can be managed with SharePoint Designer, through which a customized and up to the need workflow can be designed in order to automate the approval collections of the old-fashioned paper work.

When deployed on the top of the solution, the SharePoint designer provides the end-users who are filling out the forms and the approvers an added value through mail notifications on every single step in the process.

All in all, the proposed solution can be outlined as drafted below, to eliminate paper work, ensure correct data to be filled out, approvals to be well managed, forms to be archived and most importantly the “Sensitive Cards” in Dynamics GP to be well configured.

 

Helping Note !

The automated solution proposed above can be further enhanced to automatically migrate the data that has been fully approved to Dynamics GP. Since data of InfoPath is stored in an XML format. eConnect can be considered including the following eConnect Schema:

• Item: IVItemMasterType
• Customer: RMCustomerMasterType
• Vendor: PMVendorMasterType
• Fixed Asset: FACreateAssetIDType
• GL Account: GLAccountType

In the next post of this series, I will apply the concept above on a practical case study through which Item and Vendor cards are designed and well configured in InfoPath.

When integrated, Simple MS products can do such a great job !

Best Regards,
Mahmoud M. AlSaadi