How to Import Beginning Balances for Comprehensive Leave Manager

Written By: Julie Jensen of Integrity Data

from April 26, 2013

So, you own Integrity Data’s Comprehensive Leave Manager and you need to know how to import beginning balance transactions.  The best way to import beginning balance transactions is to create an Excel spreadsheet with the beginning balance adjustments.  Here is an example of the fields you will need to populate:

Once you have finished the spreadsheet and saved the file as a comma separated file (.csv), you will be able to import the transactions into the Leave Transactions table using Table Import.  Keep in mind; you will first need to assign the leave codes to an employee within Microsoft Dynamics® GP before importing the beginning balance transactions.

Now that you are ready to import, you can complete the following steps:

1. Log into Microsoft Dynamics GP as the system administrator and select Microsoft Dynamics GP > Tools > Integrate > Table Import.

2. Enter a Definition ID and Description

3. Select the appropriate Source File Format

4. Navigate to the Source File

5. Destination Table select the Ellipsis button; the Choose A Table window will open; and then choose the following:

a. Product: Comprehensive Leave Manage
b. Series: 3PrdP Party
c. Table List: Leave Employee Transactions

6. Select the appropriate field names from the source

7. Click the Import button and click Yes to save the Import Definition

8. Review the Import Status information and then click Close

Congratulations! You have now imported the beginning balance information to the Employees Leave Transactions records.  The last step needed is to reconcile the transactions to the employee summary.  You can simply run the SQL script below against your company database and you’ll be set.

You can simply run the SQL script below against your company database and you’ll be set.

DECLARE @EMPLOYID AS varchar(15)

DECLARE @PTOCODE AS varchar(15)

 

– This section ensures the Remaining Balance is corrrect on each transaction

DECLARE @SEQNUMBR AS integer

DECLARE CLM_Trx CURSOR FOR

      SELECT EMPLOYID, APRPTOCode, SEQNUMBR

      FROM APR_PTO20200

 

OPEN CLM_Trx

FETCH NEXT FROM CLM_Trx INTO @EMPLOYID, @PTOCODE, @SEQNUMBR

 

WHILE @@FETCH_STATUS = 0

BEGIN

      UPDATE APR_PTO20200

      SET APRPTORemaining =

      (SELECT SUM(APRPTOEarned) + SUM(APRPTOAdjusted) - SUM(APRPTOTaken)

FROM APR_PTO20200 WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE AND SEQNUMBR <= @SEQNUMBR)

WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE AND SEQNUMBR = @SEQNUMBR

     

      FETCH NEXT FROM CLM_Trx INTO @EMPLOYID, @PTOCODE, @SEQNUMBR

END

 

CLOSE CLM_Trx

DEALLOCATE CLM_Trx

– This section updates CLM Master Data from the transactional data

DECLARE CLM_Master CURSOR FOR

      SELECT EMPLOYID, APRPTOCode

      FROM APR_PTO00120

 

OPEN CLM_Master

FETCH NEXT FROM CLM_Master INTO @EMPLOYID, @PTOCODE

 

WHILE @@FETCH_STATUS = 0

BEGIN

      UPDATE APR_PTO00120

            SET

            APRPTOEarned =

                  (SELECT SUM(APRPTOEarned) FROM APR_PTO20200

WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE),

            APRPTOAdjusted =

                  (SELECT SUM(APRPTOAdjusted) FROM APR_PTO20200

WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE),

            APRPTOPending =

                  (SELECT SUM(APRPTOPending) FROM APR_PTO20200

WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE),

            APRPTOTaken =

                  (SELECT SUM(APRPTOTaken) FROM APR_PTO20200

WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE),

            APRPTORemaining =

(SELECT SUM(APRPTOEarned) + SUM(APRPTOAdjusted) - SUM(APRPTOTaken) FROM APR_PTO20200

WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE)

            WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE

      FETCH NEXT FROM CLM_Master INTO @EMPLOYID, @PTOCODE

END

 

CLOSE CLM_Master

DEALLOCATE CLM_Master