Sample Request: Vacation and CTO Balances As Of Jan 01, 2005

Note: Leave data is only available for campuses participating in the SCO's Leave Accounting System.

The sample report below identifies balances for vacation and compensating time off (CTO) as of January 1, 2005. In order to get balances for that point in time, the December 2004 leave period is used as the selection criteria from the Leave Balance (LB) file. That file contains detailed leave benefit activity for your employees in the last 5 years. However, the LB does not contain employee name and CBID, which is needed for this report. So, a match is used between the LB file and the Leave Accounting Summary (LX) file, which contains employees with leave activity within the past 12 months. Note that if the report is modified to use an earlier pay period, employee name and CBID will not display due to the timeframe differences of the files. Only a portion of the actual report is shown below. The complete report request is available in the CIRS common library as FOC5009.

current ben begin total ending total avail

social employee name cbid id bal used bal accrued Jan 1

------ ------------- ------- --- ------- ------ ------- ------- ------

123-45-6789 penguin, am m80 va 321.000 .000 321.000 16.000 337.00

234-56-7890 monkey, my r09 va 176.653 32.000 144.653 11.333 155.99 345-67-8901 ardvark, pa m80 va 44.676 27.000 17.676 6.667 23.34

ct .100 .000 7.600 7.500 15.10

456-78-9012 walrus, ja c99 va 56.000 .000 56.000 16.000 72.00

Part 1

To begin the match, all the standard defines are executed, then the required data is gathered from the LB file (old) and the required data is gathered from the LX file (new). The match option OLD is used to retain all the data gathered from the LB file and to include any field matching in the LX file. The matched data is held to your PERMRPT file.

EX lb

ex lx

match file lb

print lb:lp lb:lt lb:begbal lb:totused lb:endbal lb:totcr

by lb:ssa as 'ssa'

if lb:lp eq 200412

IF LB:LT EQ 'VA' OR 'CT'

run

file lx

print lx:cbid lx:wname

by lx:ssa as 'ssa'

after match hold as permrpt old

end

Part II

Using the data held in PERMRPT, defined fields are created to calculate the leave benefit balance as of Jan 1 using the ending balance for December 2004 plus the credit earned for December 2004, and to fill-in the blank values for name and CBID resulting from the match.

DEFINE FILE PERMRPT

JAN_BAL/P8.2 = LB:ENDBAL + LB:TOTCR;

NAME/A39 = IF (SSA NE LAST SSA ) AND (LX:WNAME EQ ' ') THEN ' ' ELSE

IF (LX:WNAME EQ ' ' ) AND (SSA EQ LAST SSA) AND

(LAST LX:WWNAME NE ' ') THEN LAST LX:WNAME ELSE

IF (LX:WNAME EQ ' ' ) AND (SSA EQ LAST SSA) AND

(LAST LX:WWNAME EQ ' ') THEN LAST NAME ELSE LX:WNAME;

CBID/A3 = IF (SSA NE LAST SSA ) AND (LX:cbid EQ ' ') THEN ' ' ELSE

IF (LX:cbid EQ ' ' ) AND (SSA EQ LAST SSA) AND

(LAST LX:cbid NE ' ') THEN LAST LX:cbid ELSE

IF (LX:cbid EQ ' ' ) AND (SSA EQ LAST SSA) AND

(LAST LX:cbid EQ ' ') THEN LAST cbid ELSE LX:cbid;

END

Part III

The final report is generated from the matched data held in PERMRPT. It is written to produce a printed report, but it can be modified to produce a hold file by un-commenting the ON TABLE HOLD... line before the END command. Please note that all formatting will be lost if a hold file in a lotus format is created.

TABLE FILE PERMRPT

HEADING CENTER

"CALIFORNIA STATE UNIVERSITY"

"LEAVE BALANCES FOR VACATION AND CTO AS OF JAN 1, 2005"

"REPORT GENERATED ON: &DATE"

" "

PRINT LB:LP AS 'LEAVE,PERIOD'

LB:LT AS 'BENIFIT,ID'

LB:BEGBAL AS 'BEGIN,BAL'

LB:TOTUSED AS 'TOTAL,USED'

LB:ENDBAL AS 'ENDING,BAL'

LB:TOTCR AS 'total,accrued'

JAN_BAL AS 'AVAIL,JAN 1'

BY SSA AS 'SOCIAL'

BY NAME AS 'EMPLOYEE NAME'

BY CBID AS 'CURRENT,CBID'

BY LB:LP NOPRINT

ON SSA SKIP-LINE

-* ON TABLE HOLD AS PERMRLG FORMAT LOTUS

END

If you have any questions about the report, refer to your FOCUS documentation or call the CIRS Hotline.