Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Wednesday, December 31, 2014

GP 2015 – Fixed Assets Year End Closing Report

Another nice feature comes with Dynamics GP 2015 as related to the Fixed Assets module, it is a tiny yet good addition when it comes to the year-end closing procedure for the fixed assets module. When performing the Year-End closing routine for the FA module, an optional report is printed to provide a detailed status for all the assets affected by this procedure.

Closing the Fixed Assets Year:

It is a must to read the Year-End closing procedures for the Fixed Assets Management module knowledge base article 865653 before performing this routine.
Go to Microsoft Dynamics GP > Tools > Routine > Fixed Assets > Year End

FA Year End

 

Select the book and get it inserted into the “Selected Books” section. Once done, click “Continue” to perform the year-end routine.

Starting FA Year -End closing

Click “Continue”, and the optional FA report will print out as shown below:

FA Year End Closing Report Destination

 

FA Year End Closing Report

 

 

 

 

 

 

 

 

 

 

Best Regards,
Mahmoud M. AlSaadi

Monday, December 29, 2014

GP 2015 – Warning of Vendors that have Purchase Orders

A nice feature is introduced with Dynamics GP 2015, the main purpose of it is to ensure the correct flow of the transactions as related to vendors. To activate this feature,

  • Go to Microsoft Dynamics GP > Tools > Setup > Purchasing > Payables.
  • On the Payables Management Setup window, there is a check option to “Warn if Vendor has existing purchase order”.
  • Have the check box near the “Warning” checked as shown below

Payable Management Setup

Now, on the payable transaction window (Transaction > Purchasing > Transaction Entry ), the system will check whether there is an existing purchase order for the selected vendor and pop up a warning message indicating the possible options.

Payable Transaction

In order for the warning to pop up, there are logical conditions which are checked for the selected vendor before hand, the flow is described below:

Warning Logical Flow

 

In summary:

  • The warning doesn’t include payables Finance Charges, Miscellaneous Charges, Return or Credit Memo types.
  • The warning occurs for vendors with purchase order which status is new, released, change order or received.

Best Regards,
Mahmoud M. AlSaadi

Monday, December 22, 2014

GP 2015 – Understanding Receivable Batch Approval

The new workflow engine has been enhanced to include provide further more capabilities. Now, you can manage the batch posting processing by creating a workflow which is tailored to your business needs. Previous articles explained the vendor card approval and payable batch approval

To setup a receivable batch approval workflow, go to Microsoft Dynamics GP > Tools > Setup > Company > Workflow > Workflow Maintenance.

The sales series has only one predefined workflow which is “Receivable Batch Approval”. To create a workflow, you may follow the steps provided on GP 2013 R2- New Requisitions Workflow

Receivable Batch Workflow

Changes to the Old Receivable Batch Approval Approach

The old-fashioned batch approval on the posting setup window; which is represented with an approval password, is still there. Although, you can not keep both active at the same time, you will have to either activate the new receivable batch workflow or keep the old receivable batch-password approval approach.

Posting Setup

Once the new receivable batch approval is activated, activating the old password approval will be rejected by the system, the following warning message will indicate so:

Password Approval Warning Message

The New Receivable Batch Approval

The batch entry window has new additions represented with the common new workflow bar showing the workflow status, as well as the workflow section including workflow buttons such as: approve, reject, recall, submit …etc. Further more, the workflow history shows the steps and approval history for a specific batch.

The old approved checkbox and its associated details (user id and approval date) disappears as you configure and activate the new receivable batch approval workflow. Once the new workflow is activate, these fields are gone.

RM - Batch Entry Window

Batch Edit through the Approval Process

Throughout the batch approval, the batch can be edited, deleted and posted (when finally approved).
You can add transactions to the batch which will require a “recall”, it means that the the batch will go through the approval process all over again in order to acquire the required approval. When picking an existing batch with pending approval, the system will give warning as shown below:

Batch Recall

Throughout the approval process, the batch can be deleted with no issues. As for posting, the “POST” button will show only when the batch has completed all the required approval.

Batch Approval History

When the batch is posted, it can be reused again (unless it is a recurring batch). Approval history are stored and can be viewed at any time after posting the batch from the payable inquiry zoom window.

Workflow - View History

Best Regards,
Mahmoud M. AlSaadi

Pure “Bank Transactions and Transfers” with General Ledger Journal Details – SQL Script

In Dynamics GP, the bank is affected by other modules such as Receivables and Payables. Pure bank transactions include increase adjustment, decrease adjustment, check, withdrawal and Bank Transfer. In this post, an SQL script is provided to drill link the “pure” bank transactions with their associated general ledger journals.

To simplify the overall target, here is a chart illustrating the basic Dynamics GP modules, it shows how any other module related to the “Bank Reconciliation” is excluded, only transactions entered on the Bank Reconciliation module is retrieved.

Dynamics GP Modules

Pure Bank Transactions with their associated Journals –
>> Related Article: ERP Modules Data Flow

 

The data set view is shown below:

Data Set view

 

--Tables Included:

  • CM00100  | CM Check Book Master
  • CM20200  | CM Transactions
  • CM20300  | CM Receipts
  • GL20000  | Year to Date Transactions Open
  • GL30000  | Account Transaction History

 

SELECT  X.CHEKBKID 'Checkbook ID',
       
CMRECNUM 'CM ReconNumber' ,
       
CMTrxNum 'CM Transaction Number',
           TRXDATE AS 'CM Transactin Date',
           CASE X.VOIDED
              WHEN 1 THEN 'Yes'
              WHEN 0 THEN 'No'
           END AS 'Voided',
           CMTRXTPE 'CM Transaction Type',
           paidtorcvdfrom 'Paid To/Received From',
           DSCRIPTN 'Description',
           JRNENTRY 'Journal Entry',
           DEBITAMT 'Debit Amount',
           CRDTAMNT 'Credit Amount'
FROM
          ( SELECT A.CHEKBKID ,
               
B.ACTINDX ,
               
A.CMRECNUM ,
                            A.sRecNum ,
                            A.CMTrxNum ,
                            A.TRXDATE ,
                            CASE A.CMTrxType
                                   WHEN 1 THEN 'Deposit'
                                   WHEN 3 THEN 'Check'
                                   WHEN 4 THEN 'Withdrawal'
                                   WHEN 5 THEN 'Increase Adjustment'
                                   WHEN 6 THEN 'Decrease Adjustment'
                                   WHEN 7 THEN 'Transfer'
                                   ELSE ''
                            END AS CMTRXTPE ,
                            A.paidtorcvdfrom ,
                            CASE
                                    WHEN A.DSCRIPTN = ' '
                               
AND A.CMTrxType <> 7
                                     THEN 'Bank Transaction Entry'
                                    WHEN A.DSCRIPTN = ' '
                                    AND A.CMTrxType = 7
                     THEN 'Bank Transfer Entry'
                                     ELSE A.DSCRIPTN
                            END AS DSCRIPTN ,
                            A.AUDITTRAIL ,
                            A.SRCDOCNUM ,
                            A.VOIDED
                            FROM   CM20200 AS A
                LEFT OUTER JOIN dbo.CM00100 AS B 
                ON A.CHEKBKID = B.CHEKBKID
                UNION ALL
                SELECT    A.CHEKBKID ,
                B.ACTINDX ,
                A.CMRECNUM ,
                A.sRecNum ,
                A.RCPTNMBR ,
                A.RECEIPTDATE ,
                CASE A.RcpType
                                    WHEN 1 THEN 'ReceiptCheck'
                                    WHEN 2 THEN 'ReceiptCash'
                                    WHEN 3 THEN 'ReceiptCreditCard'
                                    ELSE ''
                END AS ReceiptType ,
                A.RcvdFrom ,
                CASE A.DSCRIPTN
                                    WHEN ' ' THEN 'Bank Transaction Entry'
                                    ELSE A.DSCRIPTN
                END AS DSCRIPTN ,
                A.AUDITTRAIL ,
                A.SRCDOCNUM ,
                A.VOIDED
                FROM   dbo.CM20300 AS A
                LEFT OUTER JOIN dbo.CM00100 AS B
                ON A.CHEKBKID = B.CHEKBKID
                ) AS X
               
LEFT OUTER JOIN 
               
( SELECT  A.JRNENTRY ,
                          A.DEBITAMT ,
                          A.CRDTAMNT ,
                          A.ACTINDX ,
                          B.CHEKBKID ,
                          A.REFRENCE ,
                          A.SOURCDOC ,
                          A.ORGNTSRC ,
                          A.ORMSTRNM ,
                          A.ORMSTRID ,
                          A.ORDOCNUM ,
                          A.ORTRXSRC ,
                          A.VOIDED
                          FROM  
                         
( SELECT JRNENTRY ,
                                   DEBITAMT ,
                                   CRDTAMNT ,
                                   ACTINDX ,
                                   REFRENCE ,
                                   SOURCDOC ,
                                   ORGNTSRC ,
                                   ORMSTRNM ,
                                   ORMSTRID ,
                                   ORDOCNUM ,
                                   ORTRXSRC ,
                                   VOIDED
                                   FROM   dbo.GL20000
                                   UNION ALL
                                   SELECT JRNENTRY ,
                                   DEBITAMT ,
                                   CRDTAMNT ,
                                   ACTINDX ,
                                   REFRENCE ,
                                   SOURCDOC ,
                                   ORGNTSRC ,
                                   ORMSTRNM ,
                                   ORMSTRID ,
                                   ORDOCNUM ,
                                   ORTRXSRC ,
                                   VOIDED
                                   FROM   dbo.GL30000
                                   ) AS A
                                   LEFT OUTER JOIN dbo.CM00100 AS B 
                                   ON A.ACTINDX = B.ACTINDX
                                   ) AS Y 
                                  
ON ( 
                                   X.ACTINDX = Y.ACTINDX
                                   AND X.DSCRIPTN = Y.REFRENCE
                                   AND X.CHEKBKID = Y.ORMSTRID
                                   AND X.AUDITTRAIL = Y.ORGNTSRC
                                   AND X.AUDITTRAIL = Y.ORTRXSRC
                                   AND X.CMTrxNum = Y.ORDOCNUM
                                   AND X.CMTRXTPE IN 
                                  
( 'Increase Adjustment',
                                                    'Decrease Adjustment',
                                                    'Check', 'Withdrawal',
                                                    'ReceiptCheck','ReceiptCash',
                                                    'ReceiptCreditCard' 
                                   )
                                  
)
                                  
OR    
                                  
(  
                                       X.ACTINDX = Y.ACTINDX
                                                 AND X.DSCRIPTN = Y.REFRENCE
                                                 AND X.AUDITTRAIL = Y.ORTRXSRC
                                                 AND X.CMTrxNum = Y.ORDOCNUM
                                                 AND X.CMTRXTPE = 'Transfer'
                                                 )
WHERE  
ISNULL(X.AUDITTRAIL, 0) LIKE 'CMT%' OR
ISNULL(X.AUDITTRAIL, 0) LIKE 'CMX%'

Download Link >>>> Click here to download the SQL script.

Previous related article Reconcile Checkbooks to General Ledger – SQL Script

Best Regards,
Mahmoud M. AlSaadi

Sunday, December 14, 2014

GP 2015 – Understanding Payable Batch Approval

The new workflow engine has been enhanced to include provide further more capabilities. Now, you can manage the batch posting processing by creating a workflow which is tailored to your business needs.

To setup a payable batch approval workflow, go to Microsoft Dynamics GP > Tools > Setup > Company > Workflow > Workflow Maintenance.

The Purchasing series has two new additions which are the “Payable Batch Approval” and the “Vendor Approval”. To create a workflow, you may follow the steps provided on GP 2013 R2- New Requisitions Workflow

Paybale Batch Workflow

 

Changes to the Old Payable Batch Approval Approach

The old-fashioned batch approval on the posting setup window; which is represented with an approval password, is still there. Although, you can not keep both active at the same time, you will have to either activate the new payable batch workflow or keep the old payable batch password approval approach.

Posting Setup - Purchasing Series

Once the new payable batch approval is activated, activating the old password approval will be rejected by the system, the following warning message will indicate so:

Password Approval Warning Message

 

The new Payable Batch Approval

The batch entry window has new additions represented with the common new workflow bar showing the workflow status, as well as the workflow section including workflow buttons such as: approve, reject, recall, submit …etc. Further more, the workflow history shows the steps and approval history for a specific batch.

The old approved checkbox and its associated details (user id and approval date) disappears as you configure and activate the new payable batch approval workflow. Once the new workflow is activate, these fields are gone.

Batch Entry Window

Batch Edit through the Approval Process 

Throughout the batch approval, the batch can be edited, deleted and posted (when finally approved).
You can add transactions to the batch which will require a “recall”, it means that the the batch will go through the approval process all over again in order to acquire the required approval. When picking an existing batch with pending approval, the system will give warning as shown below:

Batch Recall - Transactions Additions

 

Throughout the approval process, the batch can be deleted with no issues. As for posting, the “POST” button will show only when the batch has completed all the required approval.

Post Bach

Batch Approval History

When the batch is posted, it can be reused again (unless it is a recurring batch). Approval history are stored and can be viewed at any time after posting the batch from the payable inquiry zoom window.

Payabel Inquiry

Best Regards,
Mahmoud M. AlSaadi

Wednesday, December 10, 2014

GP 2015 – Understanding Vendor Approval Workflow

In Dynamics GP 2015, a new functionality is provided to manage the vendor approval process and control the setup and editing of new or existing vendors. The process is perfectly designed in terms of the cards, transactions entry and posting. Throughout this article, I am providing an overall explanation for the cycle, starting from the workflow definition, vendor card setup, payable transaction entry from both a business and technical perspective.

Vendor Approval – Workflow Maintenance

- Go to Microsoft Dynamics GP > Tools > Setup > Company > Workflow > Workflow Maintenance.
- Under the purchasing Series > Click on the predefined workflow “Vendor Approval”.
- Click on “New Workflow”.
- To define steps, configure the workflow properties, check GP 2013 R2 – New Requisitions Workflow

Once the workflow is configured, the steps are defined. Never forget to activate it by clicking the “Active” button on the workflow maintenance window. From now on, any new vendor will abide by the rules defined in your workflow.

Vendor Maintenance Window

The vendor card has new enhancements represented with adding new sections to manage the workflow, this is mainly represented with both: workflow sections and workflow status.

The workflow sections includes the buttons, for instance submitting the vendor for approval after the vendor has been configured and saved. You can view the history of the vendor at any time of the process. Additionally, the workflow actions in order to approve, or reject, recall or resubmit for approvals.

 

Vendor Card - Workflow additions 

Here is another screen shot for the vendor maintenance window when showing the workflow actions, when an “Approval” or “Task” is required (from the web client)

Workflow Actions

Here is the workflow history window showing the detailed steps that this workflow has gone through, it shows when it was originated, edited, completed and by whom.

Workflow History

Vendor Transactions

As long as the vendor is either not submitted, or through the approval process. Transactions can be “entered” and saved but not posted for this specific vendor. When posting a transaction for a vendor that is still in the approval process, the batch will be held in the batch recovery, and the posting report will stat that an error has occurred preventing the transaction from posting.

AP Posting Report

 

Vendor Master Table - Technical Perspective  | PM00200

The vendor master table has a new column to reflect the workflow status for each vendor, this column is Workflow_Status and it has integer values from 1 to 7. Here is the column definition:

1 'Not Submitted'
2 _ _ _ 
3 'SUBMITTED  - No Action Needed'
4 ''SUBMITTED - Pending Approval'
5 'Recalled'
6 'Approved'
7 'Rejected'
8 _ _ _
9 'Workflow not Activated'
10 _ _ _

Best Regards,
Mahmoud M. AlSaadi

Sunday, December 7, 2014

Revisiting the Average Costing in Dynamics GP

I have received an interesting inquiry from a Dynamics GP customer having concerns about the degree to which Average Costing Calculation is accurate with a special case related to the currency decimals and unit of measurement, which has been thoroughly illustrated in a previous post. In this post, I am presenting a complicated case including both average costing calculation and cost layers split case.

First of all, lets summarize the calculation criteria that the system rely on to calculate the average cost of a specific cost layer, without digging quite deeply into the common “inventory ripple effect” approach which is comprehensively illustrated by MVP Mariano Gomez on Why is my inventory-related transaction posting so slow? The following scenario will shed a light on the standard approach that is considered to calculate the “average cost”

Lets suppose that I am posting 5 inventory receipts on a sequential order in different dates (January, February, March …etc). The image below illustrates how purchase receipts are posted in terms of order and dates.

Cost Layers - Standard Scenario

 

Cost Layers Details - Standard

As shown above, all the receipts have the same unit cost, which results in the same unit cost and adjusted unit cost (both are 20). Now, if I enter another receipts proceeding to all the dates above (for instance, 1/6/2017), the average cost will be calculated as follows:

Calculation

Calculation_2>> Further details regarding the calculation above can be found on Article ID: 923960 Enhancements made to the calculation of average cost in Microsoft Dynamics GP

Now, what If the same scenario above happens to be with a “Cost Layer Split Scenario”. The Cost layer split scenario is the case at which the system has a variance resulting from dividing the unit cost by the equivalent unit of measurement factor to calculate the unit cost of the smallest unit of measurement level such as (50/6= 8.333333 ) >>> See Previous post : Unit cost and Currency decimals setup. In this essence, the system will create two cost layers, both of them will be included in the average cost calculation.

Suppose the I am posting 5 receipts as well and then entering a back dated transaction. See below:

Cost Layers - Backdated

The receipts are posted in the order illustrated under the “Transaction Order” column. Here is receipt 6 details:

* Item Number: Item X
* UOM:  CASE = 6 Piece
* Quantity Received: 1 Case
* Unit Cost (for the Case): 50
* Base UOM unit cost: 50/6 = 8.333333

Therefore, this cost layer will be divided among two cost layer, one of them will handle the rounding variance as shown below:
Cost Layer One | 5 Pieces , Unit cost = 8.33 , Extended Cost = (8.33 * 5 = 41.65)
Cost Layer Two | 1 Piece ,  Unit cost = 8.35 , Extended Cost = (8.35 * 1 = 8.35)
Grand Total = 41.65 + 8.35 =  50

Now, it should be noted that the other layer ( one piece for 8.35) is embedded in the calculation of the average cost for the back dated transactions, this is illustrated below:

Splitted Cost Layer

What if the system didn’t split the cost layers to handle the rounding and just throw the variance at a specific suspense account ? This means that the cost layer will be something like (6 pieces with a unit cost of 8.33, extended cost = 49.98, variance = 0.02)

Here is the comparison

Cmparision for the splitted cost layers

 

Attached is the excel file illustrating the formulas used to calculating the running average cost per cost layers considering the various scenarios above. >> Excel File (Average Cost Calculation) | Download Link

Best Regards,
Mahmoud M. AlSaadi