The sample data file below lists employees who received a reassignment (A60) in the prior fiscal year and identifies the new base pay along with the difference in base pay rate from the prior employment history transaction. The report request used to generate the file consists of 3 parts. The first part sorts data in the Transaction Data (TR) file and places the data in a hold file. The second part uses defines to calculate the differences in base pay using the LAST command. The last part generates the final output by identifying the specific transaction code and desired timeframe. The entire report is available in the CIRS common library as FOC6005.
PERMSML (Lotus Format)
|
"123-45-6789", "BEE, SUE ", 07012004, "A60", 8,000.00, $2,333.00 "234-56-7890", "YAK, BOB ", 08202004, "A60", 7,696.00, -$1,154.00 |
Part I
By default, transactions in the TR file are sorted in descending date order (high to low). In order to retrieve the preceding value for base pay, transactions must first be sorted in ascending date order (low to high) using the command BY HIGHEST TR:LISTCNT. The field TR:LISTCNT identifies the correct posting order of the transactions. The command HIGHEST reverses the sort order. Note that this portion of the report does not contain any screening statements or a record limit. If you restrict the range of dates, you may not receive a 'before' transaction with which to compare. The data is then held to PERMLRG for further processing.
Commands
|
EX TR TABLE FILE TR PRINT TR:EFFDATE TR:TRANCODE TR:BASEPAY BY TR:SSA BY TR:WNAME BY TR:PSNSEQ BY HIGHEST TR:LISTCNT ON TABLE HOLD AS PERMLRG END |
Output (PERMLRG)
|
SSA NAME PSN LIST EFF TRAN BASEPAY --- ---- -- ---- --- ---- ------- 123-45-6789 BEE, SUE 02 04 07/31/2002 A50 $5,667.00 02 03 03/01/2003 120 $5,667.00 02 02 07/01/2004 A60 $8,000.00 02 01 07/01/2005 SCR $8,360.00 234-56-7890 YAK, BOB 01 03 07/31/2003 A50 $8,850.00 01 02 08/20/2004 A60 $7,696.00 01 01 07/01/2005 GEN $7,965.00 |
Part II
After the data is sorted in the correct order, the LAST command is used in a define (LASTBASE) to retrieve the base pay from the prior record only if the record is for the same SSN and within the same position sequence. After that value is determined, another field is defined (DIFF) to determine the difference between base pay on the current transaction and the field LASTBASE. The data is then held to PERMRPT for further processing.
Commands
|
DEFINE FILE PERMLRG ADD LASTBASE/P12.2M = IF (TR:SSA EQ LAST TR:SSA) AND (TR:PSNSEQ EQ LAST TR:PSNSEQ) THEN LAST TR:BASEPAY ELSE 0; DIFF/P12.2M = IF LASTBASE GT 0 THEN (TR:BASEPAY - LASTBASE) ELSE 0;
END TABLE FILE PERMLRG PRINT TR:LISTCNT TR:EFFDATE TR:TRANCODE TR:BASEPAY LASTBASE DIFF BY TR:WNAME BY TR:SSA BY TR:PSNSEQ ON TABLE HOLD AS PERMRPT END |
Output (PERMRPT)
|
SSA NAME PSN LIST EFF TRAN BASEPAY LASTBASE DIFF --- ---- -- ---- --- ---- ------- -------- ---- 123-45-6789 BEE, SUE 02 04 07/31/2002 A50 $5,667.00 $.00 $.00 02 03 03/01/2003 120 $5,667.00 $5,667.00 $.00 02 02 07/01/2004 A60 $8,000.00 $5,667.00 $2,333.00 02 01 07/01/2005 SCR $8,360.00 $8,000.00 $360.00 234-56-7890 YAK, BOB 01 03 07/31/2003 A50 $8,850.00 $.00 $.00 01 02 08/20/2004 A60 $7,696.00 $8,850.00 -$1,154.00 01 01 07/01/2005 GEN $7,965.00 $7,696.00 $269.00 |
Part III
The final file is generated from the data held in PERMRPT. Screening statements are used to select the specific transaction (A60) and effective date range. The data is held to PERMSML in a lotus format for downloading. If additional fields are required for the final report, they may be added to the PRINT statement in Parts I, II and III.
Commands
|
TABLE FILE PERMRPT PRINT TR:SSA TR:WNAME TR:EFFDATE TR:TRANCODE TR:BASEPAY DIFF IF TR:TRANCODE EQ A60 IF TR:EFFDATE FROM 07/01/2004 TO 06/30/2005 ON TABLE HOLD AS PERMSML FORMAT LOTUS END |
Output (PERMSML)
|
"123-45-6789", "BEE, SUE ", 07012004, "A60", 8,000.00, $2,333.00 "234-56-7890", "YAK, BOB ", 08202004, "A60", 7,696.00, -$1,154.00
|
Last Updated: March 1, 2024