This following sample request identifies addresses for faculty paid by your campus in the prior business month. A match is needed between the Payment History (PH) - prior business month file which contains the targeted employees, and the Active Current Status (AC) file which has address information for active employees. To identify the address for employees who have separated since the payment was issued, an additional match is needed with the Separated Positions (SP) file. Refer to the notes in the report request below for additional information. The request is in the CIRS common library as FOC5002.
Sample Output (comma delimited file):
"222-22-2222","monkey, mm ","400 40th ave ","sacramento, ca ","99999"
"888-88-8888","bear, jr ","123 main st ","san francsico, ca","99999"
Report Request:
-****************************************************************
-* all required defines must be executed before the match command.
-*****************************************************************
EX PH
EX AC
EX SP
-*****************************************************************
-* the lines below identify r03 ee's paid in the prior pay period.
-* employee name is summed so only one record is selected.
-******************************************************************
match file ph
sum ph:wname
by ph:ssa as 'ssa'
if ph:cbid eq r03
IF PH:CAMPX EQ _
if ph:payperiod eq 2005/01
run
-***********************************************************
-* the lines below gather address for active positions
-* and matches the data to the ssn's selected from ph file.
-* the match option 'old' is used to keep all the records from
_* the ph file and only bring in matching aC records.
-* the resulting matched data is held to permsml.
-***********************************************************
file ac
print ac:street ac:ctyst ac:zip
by ac:ssa as 'ssa'
after match hold as permsml old
end
-***********************************************************
-* the line below gathers address for separated positions
-* and matches it to the data held in permsml.
-* the resulting matched data is held to permlrg
-***********************************************************
match file permsml
print ph:wname ac:street ac:ctyst ac:zip
by ssa
run
-*
file sp
print sp:street sp:ctyst sp:zip
by sp:ssa as 'ssa'
after match hold as permlrg old
end
-***********************************************************
-*the defines below merge the address fields from ac and sp.
-************************************************************
define file permrlg
street/a28 = if ac:street eq ' ' then sp:street else ac:street;
ctyst/a25 = if ac:ctyst eq ' ' then sp:ctyst else ac:ctyst;
zip/a5 = if ac:zip eq ' ' then sp:zip else ac:zip;
end
-**********************************************************
-* the lines below generate the final file for downloading.
-**********************************************************
table file permlrg
print ssa ph:wname street ctyst zip
on table hold as permrpt format lotus
end