Sample Request: Gross Pay and Employer Contributions

The sample report below is based on a request per Deborah Brothwell at San Luis Obispo to generate a printed report of total gross pay and employer contributions issued by your campus for the prior business month.  The report is sorted by issue date, clearance type and clearance number.  It is generated using the Payment History (PH) - Prior Business Month file and requires DEFINES and the MATCH command.  Each portion of the report request is described below. The complete report request is available in the CIRS Common Library as FOC6007.

 

THE CALIFORNIA STATE UNIVERSITY

GROSS PAY AND EMPLOYER CONTRIBUTIONS

BY ISSUE DATE AND CLEARANCE NUMBER

REPORT DATE:  08/03/2006

 

ISSUE        CLR  CLR                            EMPLOYER          

DATE         TYP  NO           GROSS PAY          CONTRIB          TOTAL

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

2006/07/05   1    10000       $4,655.14           $152.09       $4,807.23

             5    30323        -$624.63          -$174.63        -$799.26

                  30324        -$739.20          -$330.98      -$1,070.18

                  30325      -$1,426.53          -$357.14      -$1,783.67

             6    10002     -$11,391.30        -$1,816.00     -$13,207.30

             7    10003      $11,391.30         $1,816.00      $13,207.30

 

*TOTAL FOR 2006/07/05         $1,864.78          -$710.66       $1,154.12

 

To use this report, you must first copy the request into your library and modify the 2 screening statements 'IF PH:CAMPX EQ X' by replacing the X value with your alpha campus code.  If desired, the report can be easily modified for other purposes.  For example:

 

  • To report on the data for the year to date, select the PH current year file at the time of execution.

  • To get data for an individual employee, add a screening statement for the specific social (i.e., IF PH:SSA EQ 999-99-9999).  

  • To get data for all your employees, include a sort statement by social (e.g., BY PH:SSA) after the sort by campus (BY PH:CAMPX).

Part 1:  

The first portion of the report request executes the standard defines for the PH file and then creates multiple defines to create new fields identifying all the various state share amounts.   

 

EX PH                                                                   

DEFINE FILE PH ADD                                                      

GROSS/P12.2 = PH:GROSSPAY;                                              

HEALTH/P12.2 = IF PH:DEDTYPE EQ 'HB' THEN PH:HBSTATE ELSE 00;           

HBCST/P6.2  = IF PH:DEDTYPE EQ 'HB' or 'fh' THEN PH:HBADCST ELSE 00;    

DENTAL/P12.2 = IF PH:DEDTYPE EQ 'HD' THEN PH:HBSTATE ELSE 00;           

DENTCST/P6.2 = IF PH:DEDTYPE EQ 'HD' or 'fd' THEN PH:HBADCST ELSE 00;   

VISION/P12.2 = IF PH:DEDTYPE EQ 'HV' THEN PH:HBSTATE ELSE 00;           

FLEXHEALTH/P12.2 = IF PH:DEDTYPE EQ 'FH' THEN PH:HBSTATE ELSE 00;       

FLEXDENTAL/P12.2 = IF PH:DEDTYPE EQ 'FD' THEN PH:HBSTATE ELSE 00;       

FLEXCASH/P12.2 = IF PH:DEDTYPE EQ 'FC' THEN (PH:DEDAMT * (-1)) ELSE 00;

LIFEINS/P12.2 = IF PH:DEDTYPE EQ 'BL' THEN PH:BLSTATE ELSE 00;          

SSARREARS/P12.2 = IF PH:DEDTYPE EQ 'SS' THEN PH:SSASTWH ELSE 00;        

MEDARREARS/P12.2 = IF PH:DEDTYPE EQ 'MD' THEN PH:MEDSTWH ELSE 00;       

RETARREARS/P12.2 = IF PH:DEDTYPE EQ 'RP' OR 'RT' THEN PH:RARETSTSHR     

END                                                                     

Part 2:  

The next part of the request begins the 'old' portion of the match and gathers data from the payment segment.  A match is needed to get all payment records that do not have deductions.  The gross pay and employer contributions for social security, medicare and retirement reside in this segment of the file.  Note that you must change the value for PH:CAMPX to your single alpha campus code.    

 

MATCH FILE PH        

IF PH:CAMPX EQ X     

SUM GROSS            

    PH:SSTAXWHS      

    PH:MDTAXWHS      

    PH:RETSTSHR      

BY PH:CAMPX          

BY PH:ISSUEDTE       

BY PH:CLEARTYP       

BY PH:CLRNR          

RUN                  

Part 3:  

The next part of the request is the 'new' portion of the match that gathers data from the deduction segment and matches the data to the old portion. The sort fields used in this part of the request must repeat those used in the first part of the match.  The results of the match are held to PERMRPT. Note that you must change the value for PH:CAMPX to your single alpha campus code.

 

FILE PH                                 

IF PH:CAMPX EQ X                        

SUM HEALTH                              

    DENTAL                              

    VISION                              

    LIFEINS                             

    FLEXHEALTH                          

    FLEXDENTAL                          

    FLEXCASH                            

    HBCST                               

    DENTCST                             

    SSARREARS                           

    MEDARREARS                          

    RETARREARS                          

BY PH:CAMPX                             

BY PH:ISSUEDTE                          

BY PH:CLEARTYP                          

BY PH:CLRNR                             

AFTER MATCH HOLD AS PERMRPT OLD-OR-NEW  

END                                     

Part 4:  

This final portion of the report request generates the printed output. All the individual deduction costs are rolled up into a single field defined as EMPCOST. The value of that field is then added to gross pay to calculate a field defined as TOTAL. To view the detail of the employer contribution amounts, delete the reference to the defined field EMPCOST from the verb statement and sum the individual fields (i.e., SUM  GROSS  HEALTH  DENTAL  VISION etc.).

 

DEFINE FILE PERMRPT                                                   

EMPCOST/P16.2M = HEALTH + HBCST + DENTAL + DENTCST + VISION +         

                FLEXHEALTH + FLEXDENTAL + FLEXCASH + LIFEINS +        

                PH:SSTAXWHS + PH:MDTAXWHS + PH:RETSTSHR + SSARREARS +

                MEDARREARS + RETARREARS;                              

TOTAL/P16.2M = GROSS + EMPCOST;                                       

END                                                                   

TABLE FILE PERMSML                                                    

SUM GROSS/P16.2M as 'gross'

    EMPCOST      aS 'EMPLOYER,CONTRIB'

    TOTAL        as 'total'        

BY PH:ISSUEDTE   AS 'ISSUE,DATE'                                      

BY PH:CLEARTYP   AS 'CLR,TYP'                                         

BY PH:CLRNR      AS 'CLR,NO'                                          

ON PH:ISSUEDTE SUB-TOTAL MULTILINES AS '*TOTAL FOR:'                  

HEADING CENTER                                                        

"THE CALIFORNIA STATE UNIVERSITY"                                     

"GROSS PAY AND EMPLOYER CONTRIBUTIONS"                                

"BY ISSUE DATE AND CLEARANCE NUMBER"                                  

"REPORT DATE &DATE"                                                   

" "                                                                   

END                                                                   

 

For additional information on creating defined fields and matching data, please refer to your FOCUS documentation or the Advanced Reporting Agenda and Modules.