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

 

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

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

 

 

 

 

 

 

 

 

 

 

 

Best Regards,
Mahmoud M. AlSaadi

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

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.

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:

 

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

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

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.

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:

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.

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:

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.

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.

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

 

The data set view is shown below:

 

--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

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

 

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.

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:

 

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 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:

 

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.

Best Regards,
Mahmoud M. AlSaadi

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.

 

 

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)

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.

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.

 

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