/*
************************************************
* Integrity Data, Inc.                         *
* Lincoln, IL  62656                           *
*                                              *
* Comprehensive Leave Manager                  *
*                                              *
* Sql script to set leave balances from        *
* transaction information                      *
************************************************
*/
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 isnull(SUM(APRPTOEarned),0.00) FROM APR_PTO20200 WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE),
              APRPTOAdjusted = 
                     (SELECT isnull(SUM(APRPTOAdjusted),0.00) FROM APR_PTO20200 WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE),
              APRPTOPending = 
                     (SELECT isnull(SUM(APRPTOPending),0.00) FROM APR_PTO20200 WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE),
              APRPTOTaken = 
                     (SELECT isnull(SUM(APRPTOTaken),0.00) FROM APR_PTO20200 WHERE EMPLOYID = @EMPLOYID AND APRPTOCode = @PTOCODE),
              APRPTORemaining = 
                     (SELECT isnull(SUM(APRPTOEarned),0.00) + isnull(SUM(APRPTOAdjusted),0.00) - isnull(SUM(APRPTOTaken),0.00) 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