KB01-007
This Excel spreadsheet provides an example file to import balances. Depending on the leave setups you may or may not want to enter time in the Earned, Pending, Adjusted fields. We will discuss this further below. Once the import is comlete you will be able to view the imported transaction in the Employee Leave Transaction window.
Click Here to Download the Excel Template
Import Considerations:
Yearly Accrual Maximum
When an Accrual Schedule is setup with a Maximum Hours Based On “Per Year” either Calendar or Accrual year, the system will add all the Earned and Pending for the year to determine if the employee should be allowed to accrue.
For example, if an employee currently has a balance of 60 hours consisting of 40 hours from last year and 20 hours accrued this year. If my maximum is 48 hours per year depending on how I import the balances will impact my accrual amount this year. If the 60 hours is imported as Adjusted then the system will calculate my maximum accrual this year for 0 hours, instead of 20 and the employee could potentially over accrue their leave time. If the 60 hours is imported as Earned the system will not accrue any time for this year because it will view the 60 hours as over the 48 hour yearly maximum. For the system to accruately calculate the maximum, we would import 40 hours in the adjusted field and 20 hours in the earned field. Then the system would see that we have accrued 20 hours towards our yearly maximum this year.
Waiting Period/Pending Time:
If the leave policy requires a waiting period then hours may need imported into the Pending status. If an employee is currently in a waiting period then any accrued time will be in a Pending status. Once the employee fullfills the waiting period requirements the system will move the pending time to Avaialble during payroll based upon the leave setups.
Import Files
- Leave Transaction Import.xlsx – this file is formatted for the import. There is some sample data in the spreadsheet and instructions below on how to import
- CLMReconcileLeaveBalances.txt – this file will calculate the remaining balances for the transactions entered and update the summary table.
Steps for Table Import
- Setup and assign leave codes to the employees within Dynamics GP.
- Enter the employee data in the attached spreadsheet.
- Remove all the headings from the example file after you have entered the import data.
The Excel file should look similar to this before saving to a CSV.
- Save Microsoft Excel spreadsheet as a comma separated files (.csv) or a tab delimited file (.txt).
- Log into Microsoft Dynamics GP as the system administrator
- Open the Table Import Definition window
Microsoft Dynamics GP > Tools > Integrate > Table Import - Enter a Definition ID and Description
- Select the appropriate Source File Format
- Navigate to the Source File
- Destination Table select the Ellipsis button; the Choose A Table window will open; and then choose the following:
- Product: Comprehensive Leave Manager
- Series: 3PrdP Party
- Table List: Leave Employee Transactions
9. Select the appropriate field names from the source
Note: Depending on if you entered time in the Earned, Taken, Adjusted or Pending fields will depend on which fields are selected.
The screenshot below is an example, your setups may require slightly differnt selections.
10. Click the Import button and click Yes to save the Import Definition
11. Review the Import Status information and then click Close
12. Run the attached CLMReconcileLeaveBalances.txt script to update the summary information based upon the transaction information.
Questions / Resources
If you have questions or comments please email Support@integrity-data.com.
If you would like to search for information and resources for Integrity Data products check out the following sites:
Knowledge Base: https://www.integrity-data.com/hrp/knowledge-base/
Videos: https://www.integrity-data.com/hrp/videos/
Last Review: 4/1/2016 – Revision: 1.1
Applies To:
Microsoft Dynamics GP 2015 R2
Microsoft Dynamics GP 2013 R2