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.