Sample Request: Difference In Base Pay For Reassignments

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: May 8, 2006