Docstoc

detail

Document Sample
detail Powered By Docstoc
					                                  Final




Detailed Design
RELEASE 1178


      TX Annual File and
      TX Monthly Change Reporting
         (Service Request 13228)

      CNA Annual File and
      CNA Monthly Change Reporting
         (Service Request 13229)




March 25, 1998



Information Systems & Computing
Office of the President
University of California
                                         Final



Introduction

Service Request 13228

Service Request 13228 asks that the Base Payroll/Personnel System (PPS) be modified to
support the University’s collective bargaining agreement with the University Professional
and Technical Employees (UPTE) regarding data on TX covered employees. The
requirements of the collective bargaining agreement with the UPTE ask that the University
provide UPTE, as an exclusive representative, the following items:

1. TX Annual file, from the Office of the President, containing information on each
   employee covered by UPTE at all locations.

2. A monthly change report, from each location, containing changes to the data provided in
   the TX Annual file, and of changes to the status of the covered employees listed in the
   TX Annual file.

   The monthly change report should contain such data as changes in title and pay rate,
   leave of absence and separation information. In addition, the report should contain data
   on employees newly employed at the University in the TX bargaining unit as well as
   employees who have transferred in and out of the bargaining unit, since the creation of
   the TX Annual file.

The detail modifications required to produce the above TX Annual file and TX Monthly
Change Report should be the same as the existing process for producing the CNA Annual
file and CNA Monthly Change Report for the California Nurses Association (CNA), except
that the Appointment Type (EDB 2020) should also be included in the TX Annual File and
TX Monthly Change Report.

Service Request 13229

Service Request 13229 asks that program PPP790 which writes the CNA Annual File and
Monthly Change Report be modified to obtain the translations for Separation Reason Codes
(EDB 0141) from the Code Translation Table, instead of the copymember CPWSXSEP.




                                                                                         1
                                           Final




Processing Overview
Service Request 13228

To facilitate the University’s ability to respond to the University Professional and Technical
Employees (UPTE) collective bargaining agreement regarding data on TX covered
employees, a process will be developed to supply such data:

•   TX Annual File

    Once each year, the TX Annual File will be created based on current data in the
    Employee Database (EDB), after Monthly Periodic Maintenance to begin May has been
    run. Each campus will transmit the TX Annual File to the Office of the President, where
    the TX Annual Files will be consolidated into a single file to be provided to UPTE.

    The previous year’s data on the DB2 TX Reporting Tables will be deleted; the new data
    sent on the TX Annual File will be stored on the TX Reporting Table at each location.

•   TX Monthly Change Reporting

    The TX Monthly Change Reporting Process will be executed after each Monthly
    Periodic Maintenance has run.

    Each month, the data on the DB2 TX Reporting Tables will be compared against the
    appropriate data on the current EDB; if a change is found, the data will be reported on
    the TX Monthly Change Report, and the appropriate data on the DB2 TX Reporting
    Tables will be updated with the changed data from the current EDB.

Service Request 13229

Program PPP790 will be modified to obtain the translation for the Separation Reason Code
(EDB 0141) from the Code Translation Table. Copymember CPWSXSEP containing the
list of translated Separation Reason Codes will be made obsolete.




                                                                                            2
                                           Final


Service Request 13228

Differences from Requirements
The following items are differences in the original requirements:

•   The requirements specify that the Percent Full-time (EDB 2012) should not be
    considered in determining the employee’s reported Percent Full-time in the unit
    whenever the Time Reporting Code (EDB 2017) on the Appointment record contains a
    value of ‘W’.

    It has been agreed that the Percent Full-time should not be considered in determining
    the employee’s reported percent full-time in the unit whenever the Appointment
    Without Salary Indicator (EDB 2028) contains a value of ‘Y’.

•   The requirement defines a distinct appointment as all appointments with the same
    Appointment Title Code (EDB 2006) and Appointment Department Code (EDB 2032).

    It has been agreed that the Appointment Department Code (EDB 2032) should not be a
    requirement for a definition of a distinct appointment.


Form

•   UPAY881

    The design of this form will be similar to existing form UPAY738 (CNA Annual File
    and Monthly Change Reporting).

    The following is a sample layout for the Run Specification record:

             1         2         3         4
    1---5----0----5----0----5----0----5----0
    PPP795-SPECANNUAL

    Columns 1 through 11 (Program ID)
    Columns 12 through 18 (Run Type)

    Valid Run Type:
     ANNUAL = TX Annual file and load initial TX data on database
     MONTHLY = TX Monthly Change report.



                                                                                       3
                                          Final



System File Changes
•   TX Annual File

    This is a new sequential file with variable length records, which will be transmitted,
    annually to UCOP from each location, and which will include information required by
    the UPTE for all TX covered employees. The first record on the file will be a control
    record, which will include the Campus Location Code, year the file was produced and
    the number of detail records contained in the TX Annual file. The file will be sorted
    by Employee Name, and within Employee Name by Employee ID. The Employee
    Name and Employee ID on the control record will contain all blanks so that it will be
    the first record in the file.

•   CNA Database

    The existing CNA database will contain two new DB2 TX Reporting Tables, similar to
    the existing DB2 CNA Personnel and CNA Appointment Tables.

    The data elements included in the DB2 TX Reporting Tables will be similar to those
    included in the TX Annual File, and will be made up of the following tables:

    1.TX Personnel Table (PPPTPR) (See TBTPR00C in section DDL Members)

    2.TX Appointment Table (PPPTAP) (see TBTAP00C in section DDL Members)

    Each year before the campus deletes the prior TX data from the CNA database, similar
    to the existing CNA Annual Process, it is suggested that a copy of all the old year TX
    data be produced and saved as tables in a partitioned data set using the IBM unload
    utility, DSNTIAUL.


DDL Members
•   TSTPR00C

    This member will contain the SQL statements necessary to allocate space for the
    PPPTPR Table.

    CREATE TABLESPACE PPPTPR
     IN      PPPCNA
    USING STOGROUP P70004D
       PRIQTY 220
       SECQTY 22


                                                                                        4
                                       Final

        BUFFERPOOL BP0
        FREEPAGE 5
        PCTFREE 5
        LOCKSIZE ANY
        CLOSE   NO ;

•   TSTAP00C

    This member will contain the SQL statements necessary to allocate space for the
    PPPTAP Table.

    CREATE TABLESPACE PPPTAP
     IN    PPPCNA
    USING STOGROUP P70004D
       PRIQTY 84
       SECQTY 8
       BUFFERPOOL BP0
       FREEPAGE 5
       PCTFREE 5
       LOCKSIZE ANY
       CLOSE   NO ;


•   TBTPR00C

    This member will contain the SQL statements necessary to create the TX Personnel
    Table (PPPTPR).

    CREATE TABLE PPPTPR
     (TPR_EMPLOYEE_ID             CHAR(9) NOT NULL,
      TPR_EMP_NAME                CHAR(26) NOT NULL WITH DEFAULT,
      TPR_NAMESUFFIX              CHAR(4) NOT NULL WITH DEFAULT,
      TPR_PRIOR_EMP_ID            CHAR(9) NOT NULL WITH DEFAULT,
      TPR_ADDRESS_LINE1           CHAR(30) NOT NULL WITH DEFAULT,
      TPR_ADDRESS_LINE2           CHAR(30) NOT NULL WITH DEFAULT,
      TPR_ADDRESS_CITY            CHAR(21) NOT NULL WITH DEFAULT,
      TPR_ADDRESS_STATE           CHAR(2) NOT NULL WITH DEFAULT,
      TPR_ADDRESS_ZIP             CHAR(9) NOT NULL WITH DEFAULT,
      TPR_HIRE_DATE               DATE NOT NULL,
      TPR_HOME_DEPT               CHAR(6) NOT NULL WITH DEFAULT,
      TPR_LOA_BEGIN_DATE          DATE NOT NULL,
      TPR_LOA_RETRN_DATE          DATE NOT NULL,
      TPR_LOA_TYPE_CODE           CHAR(2) NOT NULL WITH DEFAULT,
      TPR_SEPARATE_DATE           DATE NOT NULL,
      TPR_SEP_REASON              CHAR(2) NOT NULL WITH DEFAULT,


                                                                                   5
                                   Final

      PRIMARY KEY (TPR_EMPLOYEE_ID))
    IN PPPCNA.PPPTPR ;

•   TBTAP00C

    This member will contain the SQL statements necessary to create the TX
    Appointment Table (PPPTAP).

    CREATE TABLE PPPTAP
     (TAP_EMPLOYEE_ID     CHAR(9) NOT NULL,
      TAP_TITLE_CODE      CHAR(4) NOT NULL,
      TAP_PAY_RATE        DECIMAL(8,2) NOT NULL WITH DEFAULT,
      TAP_PRCNT_FULLTIME DECIMAL(3,2) NOT NULL WITH DEFAULT,
      TAP_APPT_TYPE        CHAR(1) NOT NULL WITH DEFAULT,
      PRIMARY KEY (TAP_EMPLOYEE_ID, TAP_TITLE_CODE),
      FOREIGN KEY (TAP_EMPLOYEE_ID) REFERENCES PPPTPR
          ON DELETE CASCADE)
    IN PPPCNA.PPPTAP ;




                                                                         6
                                     Final


•   IXTPR00C

    This member will contain the SQL statements necessary to create the PPPXTPR
    index to Table PPPTPR.

    CREATE UNIQUE INDEX PPPXTPR ON PPPTPR
      (TX_EMPLOYEE_ID )
       CLOSE NO
      BUFFERPOOL BP0
      CLUSTER
      FREEPAGE 0
      PCTFREE 10 ;

•   IXTAP00C

    This member will contain the SQL statements necessary to create the PPPXTAP
    index to Table PPPTAP.

    CREATE UNIQUE INDEX PPPXTAP ON PPPTAP
      (TX_EMPLOYEE_ID, TX_TITLE_CODE)
      FREEPAGE 0
      CLOSE NO
      CLUSTER
      BUFFERPOOL BP0
      PCTFREE 10 ;

•   PPPVZTPR

    This DDL member will contain the SQL statements to create a view of all the
    columns of the TX Personnel Table (PPPTPR):

    CREATE VIEW PPPVZTPR_TPR
     (   TX_EMP_NAME         ,
          TX_NAMESUFFIX        ,
          TX_PRIOR_EMP_ID     ,
          TX_ADDRESS_LINE1      ,
          TX_ADDRESS_LINE2      ,
          TX_ADDRESS_CITY        ,
          TX_ADDRESS_STATE     ,
          TX_ADDRESS_ZIP      ,
          TX_HIRE_DATE        ,
          TX_HOME_DEPT       ,
          TX_LOA_BEGIN_DATE ,
          TX_LOA_RETRN_DATE ,


                                                                              7
                                          Final

             TX_LOA_TYPE_CODE   ,
             TX_SEPARATE_DATE   ,
             TX_SEPARATE_REASON )
      AS
      SELECT
        PPPRTPR.TX_EMPLOYEE_ID ,
           TX_EMP_NAME         ,
           TX_NAMESUFFIX        ,
           TX_PRIOR_EMP_ID     ,
           TX_ADDRESS_LINE1      ,
           TX_ADDRESS_LINE2      ,
           TX_ADDRESS_CITY        ,
           TX_ADDRESS_STATE     ,
           TX_ADDRESS_ZIP      ,
           CHAR(TX_HIRE_DATE,ISO)   ,
           TX_HOME_DEPT        ,
           CHAR(TX_LOA_BEGIN_DATE,ISO) ,
           CHAR(TX_LOA_RETURN_DATE,ISO) ,
           TX_LOA_TYPE_CODE    ,
           CHAR(TX_SEPARATE_DATE,ISO)    ,
           TX_SEPARATE_REASON
       FROM PAYADM.PPPTPR ;

•   PPPVZTAP

    This DDL member will contain the SQL statements necessary to create a view of all
    the columns of the TX Appointment Table (PPPTAP):

    CREATE VIEW PPPVZTAP_TAP
     AS
     SELECT
       PPPTAP.TAP_EMPLOYEE_ID ,
           TX_TITLE_CODE     ,
           TX_PAY_RATE       ,
           TX_PRCNT_FULLTIME ,
           TX_APPT_TYPE
     FROM PAYADM.PPPTAP;

•   PPPV795A

    This DDL will contain the SQL statements necessary to create a view of the columns
    required by program PPP795 from the EDB Personnel (PPPPER), Appointment
    (PPPAPP), Payroll Computations (PPPPCM), and Payroll (PPPPAY) Tables. The
    SELECT statement will create the view using a join of the three tables since this gives
    exactly the data we wish to use.


                                                                                         8
                                   Final


This DDL member will contain the SQL statements necessary to create a view of all
the columns of the TX Appointment Table (PPPTAP):

CREATE VIEW PPPV795A_CNAEDB
( EMPLOYEE_ID    ,
  EMP_NAME            ,
  NAMESUFFIX          ,
  EMP_ORGAN_DISCLO    ,
  PRIOR_EMP_ID        ,
  ADDRESS_LINE1       ,
  ADDRESS_LINE2       ,
  ADDRESS_CITY        ,
  ADDRESS_STATE       ,
  ADDRESS_ZIP         ,
  HIRE_DATE           ,
  HOME_DEPT           ,
  LOA_BEGIN_DATE      ,
  LOA_RETURN_DATE     ,
  LOA_TYPE_CODE       ,
  SEPARATE_DATE       ,
  SEPARATE_REASON     ,
  RATE_CODE           ,
  APPT_BEGIN_DATE     ,
  APPT_END_DATE       ,
  PAY_RATE            ,
  PERCENT_FULLTIME    ,
  APPT_TYPE           ,
  APPT_NUM )
AS
SELECT
  PPPPER.EMPLOYEE_ID ,
  EMP_NAME            ,
  NAMESUFFIX          ,
  EMP_ORGAN_DISCLO    ,
  PRIOR_EMP_ID        ,
  ADDRESS_LINE1       ,
  ADDRESS_LINE2       ,
  ADDRESS_CITY        ,
  ADDRESS_STATE       ,
  ADDRESS_ZIP         ,
  CHAR(HIRE_DATE,ISO) ,
  HOME_DEPT           ,
  CHAR(LOA_BEGIN_DATE,ISO)                  ,
  CHAR(LOA_RETURN_DATE,ISO)                 ,


                                                                                9
                                        Final

      LOA_TYPE_CODE         ,
      CHAR(SEPARATE_DATE,ISO)          ,
      SEPARATE_REASON       ,
      RATE_CODE                   ,
     CHAR(APPT_BEGIN_DATE,ISO)         ,
     CHAR(APPT_END_DATE,ISO)           ,
     PAY_RATE               ,
     PERCENT_FULLTIME       ,
     APPT_TYPE              ,
     APPT_NUM
    FROM PPPPER, PPPAPP, PPPPAY, PPPPCM, PPPSCR
    WHERE PPPPER.EMPLOYEE_ID = PPPPAY.EMPLOYEE_ID AND
          PPPPER.EMPLOYEE_ID = PPPAPP.EMPLOYEE_ID AND
          PPPPER.EMPLOYEE_ID = PPPPCM.EMPLOYEE_ID AND
          PPPAPP.APPT_REP_CODE = ‘C’ AND
          PPPAPP.TITLE_UNIT_CODE – ‘TX’ AND
          PPPAPP.APPT_WOS_IND NOT = ‘Y’ AND
          PPPAPP.APPT_BEGIN_DATE <= SCR_CURRENT_DATE AND
          PPPAPP.APPT_END_DATE      >= SCR_CURRENT_DATE AND
          PPPPCM.MIN_RCD_FLAG NOT = ‘1’ ;

•   PPPV795B

    This DDL will contain the SQL statements necessary to create a view of the columns
    required by program PPP795 from the TX Personnel (PPPTPR),and TX Appointment
    (PPPTAP) Tables. The SELECT statement will create the view using a join of the two
    tables since this gives exactly the data we wish to use.

    CREATE VIEW PPPV795B_CNAMO
    (     TX_EMPLOYEE_ID ,
          TX_EMP_NAME                          ,
          TX_NAMESUFFIX                       ,
          TX_PRIOR_EMP_ID                     ,
          TX_ADDRESS_LINE1                    ,
          TX_ADDRESS_LINE2                    ,
          TX_ADDRESS_CITY                    ,
          TX_ADDRESS_STATE                  ,
          TX_ADDRESS_ZIP     ,
          TX_HIRE_DATE       ,
          TX_HOME_DEPT       ,
          TX_LOA_BEGIN_DATE                 ,
          TX_LOA_RETURN_DATE                ,
          TX_LOA_TYPE_CODE                 ,
          TX_SEPARATE_DATE                 ,
          TX_SEPARATE_REASON              ,


                                                                                    10
                                        Final

             TX_TITLE_CODE               ,
             TX_PAY_RATE                 ,
             TX_PRCNT_FULLTIME           ,
             TX_APPT_TYPE )
    AS
    SELECT
          PPPTPR.TX_EMPLOYEE_ID         ,
          TX_EMP_NAME           ,
          TX_NAMESUFFIX         ,
          TX_PRIOR_EMP_ID       ,
          TX_ADDRESS_LINE1        ,
          TX_ADDRESS_LINE2        ,
          TX_ADDRESS_CITY         ,
          TX_ADDRESS_STATE         ,
          TX_ADDRESS_ZIP          ,
          CHAR(TX_HIRE_DATE,ISO) ,
          TX_HOME_DEPT        ,
          CHAR(TX_LOA_BEGIN_DATE,ISO)     ,
          CHAR(TX_LOA_RETURN_DATE,ISO) ,
          TX_LOA_TYPE_CODE            ,
          CHAR(TX_SEPARATE_DATE,ISO)      ,
          TX_SEPARATE_REASON,
          TX_TITLE_CODE              ,
          TX_PAY_RATE                ,
          TX_PRCNT_FULLTIME          ,
          TX_APPT_TYPE
    FROM PPPTPR, PPPTAP
    WHERE PPPTPR.TX_EMPLOYEE_ID = PPPTAP.TX_EMPLOYEE_ID ;

•   PPPV795C

    This DDL will contain the SQL statements necessary to create a view of the columns
    required by program PPP795 from the EDB Personnel (PPPPER), and EBD Payroll
    (PPPPAY) Tables. The SELECT statement will create the view using a join of the
    two tables since this gives exactly the data we wish to use.

    CREATE VIEW PPPV795C_EDBPER
    (     EMPLOYEE_ID      ,
          EMP_NAME            ,
          NAMESUFFIX         ,
          PRIOR_EMP_ID       ,
          ADDRESS_LINE1        ,
          ADDRESS_LINE2        ,
          ADDRESS_CITY       ,
          ADDRESS_STATE       ,


                                                                                   11
                                     Final

            ADDRESS_ZIP        ,
            CHAR(HIRE_DATE,ISO) ,
            HOME_DEPT        ,
            CHAR(LOA_BEGIN_DATE,ISO)           ,
            CHAR(LOA_RETURN_DATE,ISO)           ,
            LOA_TYPE_CODE       ,
            CHAR(SEPARATE_DATE,ISO)                 ,
            SEPARATE_REASON )
    AS
    SELECT
       PPPPER.EMPLOYEE_ID         ,
           EMP_NAME            ,
           NAMESUFFIX         ,
           PRIOR_EMP_ID       ,
           ADDRESS_LINE1        ,
           ADDRESS_LINE2        ,
           ADDRESS_CITY       ,
           ADDRESS_STATE       ,
           ADDRESS_ZIP        ,
           CHAR(HIRE_DATE,ISO) ,
           HOME_DEPT        ,
           CHAR(LOA_BEGIN_DATE,ISO)   ,
           CHAR(LOA_RETURN_DATE,ISO)   ,
           LOA_TYPE_CODE       ,
           CHAR(SEPARATE_DATE,ISO)       ,
           SEPARATE_REASON
    FROM PPPPER, PPPPAY
    WHERE PPPPER.EMPLOYEE_ID = PPPPAY.EMPLOYEE_ID ;


Include Members
•   PPPVZTPR

    This INCLUDE member will declare the COBOL working storage, and SQL columns
    for table view PPPVZTPR_TPR.

•   PPPVZTAP

    This INCLUDE member will declare the COBOL working storage, and SQL columns
    for table view PPPVZTAP_TAP.




                                                                             12
                                          Final


•   PPPV795A

    This INCLUDE member will declare the COBOL working storage, and SQL columns
    for table view PPPV795A_CNAEDB.

•   PPPV795B

    This INCLUDE member will declare the COBOL working storage, and SQL columns
    for table view PPPV795B_CNAMO.

•   PPPV795C

    This INCLUDE member will declare the COBOL working storage, and SQL columns
    for table view PPPV795C_EDBPER.


Copy Members
•   CPFDXTXA

    This copymember will be the file description of the TX Annual File.

•   CPWSXTXA

This copymember will contain the record layouts for the control record and detail records
which make up the TX Annual File.


Bind Members
•   PPP789

    This copymember will contain the bind statements for plan PPP789.

    BIND                            -
     PLAN(PPP789)                   -
     MEMBER(PPP789)                 -
     ACTION(REPLACE)                -
     RETAIN                         -
     VALIDATE(BIND)                 -
     ISOLATION(CS)                  -
     FLAG(I)                        -
     ACQUIRE(USE)                   -
     RELEASE(COMMIT)-


                                                                                      13
                                          Final

     EXPLAIN(YES)

•   PPP795

    This copymember will contain the bind statements for plan PPP795.

    BIND                           -
     PLAN(PPP795)                  -
     MEMBER(PPP795,                -
          PPDOSUT2,                -
          PPMSSG2,                 -
          PPAPTDST,                -
          PPCTTUTL)                -
     ACTION(REPLACE)               -
     RETAIN                        -
     VALIDATE(BIND)                -
     ISOLATION(CS)                 -
     FLAG(I)                       -
     ACQUIRE(USE)                  -
     RELEASE(COMMIT)-
     EXPLAIN(YES)


Programs

PPP789

Initialization:

The program will attempt to verify that the CNA database is present. If the CNA database is
missing, error message ‘PPP789: SQL ERROR READING CNA DATA BASE OR CNA
DATABASE MISSING’ will be issued with a severity level of ‘9’,and the program will
terminate.

If the TX Personnel Table is present, but contains no TX Personnel records, error message
‘PPP789: NO TX EMPLOYEES FOUND ON CNA DATA BASE - STOP RUN’ will be
issued with a severity level of ‘9’, and the program will terminate.

Inputs:

TX Personnel Table

TX Appointment Table



                                                                                        14
                                           Final

Process:

If there are TX Personnel records on the TX Personnel Table, the program will be delete all
TX Personnel records with non-blank Employee IDs. With the deletion of the TX Personnel
records, all TX Appointment records associated with the same Employee ID will be deleted
as well.

Outputs:

TX Personnel Table

TX Appointment Table

Report PPP7891 (Control Totals report). See Attachment A for sample report.

Report PPP7892 (Error Messages report).


PPP795

Initialization:

The program will read the specification record.

Edit Specification Record

•   If the Specification card is missing, error message ‘OPER - PPP795 SPEC CARD IS
    MISSING’ will be issued with a severity level of ‘8’.

•   If the Specification Program Run ID is not valid, error message ‘OPER - PPP795
    INVALID SPEC CARD PROGRAM ID – REJECTED’ will be issued with a severity
    level of ‘8’.

•   If the Run Type is either ‘MONTHLY’ or ‘ANNUAL’, error message ‘OPER -
    PPP795 SPEC CARD RUN TYPE INVALID – REJECTED’ will be issued with a
    severity level of ‘8’

•   The program will attempt to verify that the CNA database is present. If the CNA
    database is missing, error message ‘PPP795: SQL ERROR READING CNA DATA
    BASE OR CNA DATABASE MISSING’ will be issued with a severity level of ‘9’.

•   If the TX Personnel Table is present, but contains no TX Personnel records, error
    message ‘PPP795: NO TX EMPLOYEES FOUND ON CNA DATA BASE - STOP
    RUN’ will be issued with a severity level of ‘9’, and the program will terminate.




                                                                                        15
                                            Final

•   If the Run Type on the Specification record specifies ‘MONTHLY’ and the TX
    Personnel Table is empty, error message ‘OPER - PPP795 SPEC CARD FOR
    MONTHLY BUT NO TX RECORDS IN CNA’ will be issued with a severity level of
    8’.

•   If the Run Type on the Specification record specifies ’ANNUAL’ and the TX Personnel
    Table contains TX records, error message ‘OPER - PPP795 SPEC CARD FOR
    ANNUAL BUT TX RECORDS IN CNA’ will be issued with a severity level of ‘8’.

If an error is found from any of the above edits, the program will terminate.

Inputs:

•   Specification record

•   Employee Database (EDB):

    Employee Personnel Table (PER)

    Employee Appointment Table (APP)

    Employee Payroll Computations Table (PCM)

    Employee Payroll Table (PAY)

•   CNA Database (CNA):

    TX Personnel Table (TPR)

    TX Appointment Table (TAP)

Process:

The Run Type value on the Specification record will determine whether the program will
produce the TX Annual File and load initial TX data on the TX Personnel and TX
Appointment Tables, or will it update the current TX data on the TX Personnel and TX
Appointment Tables using the changed data from the EDB.

•   TX Annual Process:

    1.The TX data needed for the TX Annual File will be obtained from the following EDB
       Tables:

       Employee Personnel Table




                                                                                    16
                                     Final

   Employee ID
   Employee Name (EDB 0105)
   Name Suffix (EDB 0106)
   Employee Organization Disclosure (EDB 0214)
   Home Department (EDB 0114)
   Employment Status (EDB 0144)
   Leave of Absence Begin Date (EDB 0137)
   Leave of Absence End Date (EDB 0138)
   Hire Date (EDB 0113)
   Separation Date (EDB (0140)
   Separation Reason (EDB 0141)

   Employee Payroll Table

   Prior Employee ID (EDB 0147)

   Employee Appointment Table

   Appointment Rate Code (EDB 2015)
   Appointment Begin Date (EDB 2002)
   Appointment End Date (EDB 2003)
   Appointment Title Code (EDB 2006)
   Appointment Pay Rate (EDB 2014)
   Appointment Type (EDB 2020)

   The employee’s Appointment records will be selected based on the following
   conditions:

       If the Appointment Representation Code (EDB 2031) is equal to ‘C’
       (AND)
       Appointment Title Unit Code (EDB 2029) is equal to ‘TX’
       (AND)
       Appointment Begin Date (EDB 2002) is equal to or less than the SCR Current
       Date
       (AND)
       Appointment End Date (EDB 2003) is equal to or greater than the SCR Current
       Date
       (AND)
       Appointment Without Salary Indicator (EDB 2028) is not equal to ‘Y’.
       (AND)
       The Minimum Record Flag (EDB 0145) is not equal to ‘1’ on the Payroll
       Computation Table (PPPPCM).

2.Selected records for the SORT Process




                                                                               17
                                      Final

   Calculate Annual Pay Rate/Percent Full-time:


   If there is more than one appointment for a given Appointment Title Code,

       The highest Appointment Pay Rate for a given Appointment Title Code will be
       used in the calculation below.

       The Appointment Percent of Full-time (EDB 2012) will be added together. If the
       Total Percent Full-time exceeds 100 percent, 100 percent will be moved to the
       Total Percent Full-time. The Total Percent Full-time will be moved to the sort
       record (SRT-PRCNT-FULLTIME).

   For each unique Employee ID/Title Code combination, a sort record will be created
   and released to the SORT Process. The Sort record will contain all pertinent data to
   be included in the TX Annual File. All EDB data will be moved directly to the sort
   record except for the following modifications:

       If the Appointment Rate Code (EDB 2015) is equal to ‘H’
            If the Appointment Title Code (EDB 2006) is equal to ‘9803’, ‘9804’, or
            ‘9805’, the Appointment Pay Rate will be multiplied by 2920 (Rounded)
            (ELSE)
                  the Appointment Pay Rate will be multiplied by 2088 (Rounded)
       ELSE
            the Appointment Pay Rate will taken from the Appointment record.

       The calculated Appointment Pay Rate will be moved to the sort record (SRT-
       PAY-RATE).

       If the Employment Organization Disclosure value is equal to ‘Y’ or blank, the
       address information will be disclosed on TX Monthly Change report and TX
       Personnel Table, Otherwise the address information will not be disclosed on the
       TX Monthly Change report and the TX Personnel Table.

3.Sort records returned form SORT Process

   3a. Insert TX Reporting Tables:

       For each unique Employee ID, a TX Personnel record will be written to the TX
       Personnel Table.

       For each unique Employee ID/Appointment Title Code combination, a TX
       Appointment record will be written to the TX Appointment Table.

   3b. Write TX Annual record


                                                                                      18
                                          Final


           For each unique Employee ID, the data in the TX Personnel record will be
           written to the fixed portion of the TX Annual record.

           For each unique Employee ID/Appointment Title Code combination, the data in
           the TX Appointment record will be written to the Appointment table entry of the
           TX Annual record.

    4.The number of employees written to the TX Personnel Table will be reported on the
       PPP7951 Report, and on the TX Annual Control record.

•   TX Monthly Change Reporting Process:

    1.The TX data needed for the TX Monthly Change Reporting Process will be obtained
       from the following EDB Tables:

       Employee Personnel Table (PPPPER)

       Employee ID
       Employee Name
       Name Suffix
       Employee Organization Disclosure
       Home Department
       Employment Status
       Leave of Absence Begin Date
       Leave of Absence End Date
       Leave of Absence Type Code
       Hire Date
       Separation date
       Separation Reason

       Employee Payroll Table (PPPPCM)

       Prior Employee ID

       Employee Appointment Table (PPPAPP)

       Appointment Rate Code
       Appointment Begin Date
       Appointment End Date
       Appointment Title Code
       Appointment Pay Rate
       Appointment Type




                                                                                       19
                                     Final

   The employee’s Appointment records will be selected based on the following
   conditions:

       If the Appointment Representation Code (EDB 2031) is equal to ‘C’
       (AND)
       Title Unit Code is equal to ‘TX’
       (AND)
       Appointment Begin Date (EDB 2002) is equal to or less than the SCR Current
       Date
       (AND)
       Appointment End Date (EDB 2003) is equal to or greater than the SCR Current
       Date
       (AND)
       Appointment Without Salary Indicator (EDB 2028) is not equal to ‘Y’.
       (AND)
       The Minimum Record Flag is not equal to ‘1’ on the Payroll Computation
       Table.

2.The TX data used for comparing against the data on the current EDB will be obtained
   from the following TX Reporting Tables (TX Personnel and TX Appointment):

   TX Personnel Table:

   Employee ID
   Employee Name
   Name Suffix
   Prior Employee ID
   Address Line 1
   Address Line 2
   Address City
   Address State
   Address Zip
   Hire Date
   Home Department
   Leave of Absence Begin date
   Leave of Absence End Date
   Leave of Absence Type Code
   Separation Date
   Separation Reason Code

   TX Appointment Table:

   Appointment Title Code
   Appointment Pay Rate
   Appointment Percent Full-time


                                                                                  20
                                       Final

   Appointment Type

3.Update TX Reporting Tables:

   When Employee ID from EDB is equal to Employee ID on TX
   Personnel/Appointment Tables:

   If Employment Status from EDB is equal to ‘S’ or ‘I’, the employee will be deleted
   from the TX Personnel/Appointment Tables.

   (ELSE)

         If employee has all selected appointments that are associated with Shift
         Deferential, the employee will be deleted from the TX
         Personnel/Appointment Tables.

         (ELSE)

                Compare TX Data on TX Reporting Tables against the appropriate
                data on the current EDB. Note any differences between the EDB data
                and TX data.

   When Employee ID from EDB is greater than Employee ID on TX
   Personnel/Appointment Tables:

       The employee will be deleted from the TX Personnel/Appointment Table

   When Employee ID from EDB is less than the Employee ID on TX
   Personnel/Appointment Tables:

       If Employment Status from EDB is not equal to ‘S’ or ‘I’, the employee will be
       added to the TX Personnel/Appointment Tables.

4.Verify Prior Employee ID

  Verify that the Employee ID which appears to new by checking to see if the Prior
  Employee ID (EDB 0147) from the EDB corresponds to an Employee ID on the TX
  Personnel Table. If so, it will be reported as a change to the TX Personnel Table
  rather than a new TX employee.

5.Calculate the TX Annual Pay Rate and Percent Full-time as in the TX Annual Process

6.Include the address information following the same criteria as in the TX Annual
    Process.




                                                                                    21
                                            Final

    7. A sort record will be written containing all report information associated with changes
       to the TX Personnel/Appointment Table.

     The sort records will be sorted in Employee Name order. The changes to the TX data on
     the Personnel/Appointment Tables will be reported on the PPP7952 Report.


Outputs:

•    Tables in CNA Database (CNA):

     TX Personnel Table (TPR)

     TX Appointment Table (TAP)

•    TX Annual File

•    TX Annual Control Report (PPP7951) (See Attachment B)

•    TX Monthly Change Report (PPP7952) (See Attachment C)

•    Standard Error Messages Report (PPP7953)


Control Table Updates
•    System Messages Table:

     The following messages will be added to the table with a severity level of 3:

     79-515 OPER: PPP795 SPEC CARD FOR MONTHLY BUT NO TX RECORDS IN
     CNA DB
     79-518 PPP795: NO TX EMPLOYEES SELECTED FROM EDB
     79-519 PPP795: NO TX EMPLOYEES SELECTED FROM CNA DATABASE
     79-530 EMPL'S APPOINTMENT HAS ONLY DIFFERENTIAL PAY

     The following messages will be added to the table with a severity level of 8:

     78-901 OPER: PPP789 CNA DATA BASE MISSING OR IN SERIOUS ERROR
     78-902 OPER: PPP789 NO TX EMPLOYEES FOUND ON CNA DATA BASE -
             STOP
     79-510 OPER: PPP795 SPEC CARD MISSING
     79-512 OPER: PPP795 BAD SPEC CARD PROGRAM ID - REJECTED
     79-513 OPER: PPP795 SPEC CARD RUN TYPE INVALID - REJECTED
     79-516 OPER: PPP795 SPEC CARD FOR ANNUAL BUT TX TABLES NOT


                                                                                           22
                                       Final

          EMPTY
79-517   OPER: PPP795 ERROR IN RETRIEVING SCR FROM EDB
79-520   PPP795: ERROR IN OPENING OF TX TABLE FOR SEQUENTIAL READ
79-521   OPER: PPP795 CNA DATA BASE MISSING OR IN SERIOUS ERROR
79-524   PPP795: ERROR IN OPEN OF EDB FOR SEQUENTIAL READ
79-525   DOS TABLE EXCEEDED MAXIMUM TABLE LIMIT
79-527   EMPTY DOS CONTROL TABLE
79-528   SQL ERROR ENCOUNTERED BY PPAPTDST
79-529   NO APPOINTMENT RETURNED FROM PPAPTDST

The following messages will be added to the table with a severity level of 9:

79-531 CTT LOOK UP ERROR - LEAVE REASON DESCRIPTION
79-532 CTT LOOK UP ERROR - SEPARATION REASON DESCRIPTION




                                                                                23
                                           Final


Service Request 13229

Program Changes
•   PPP790:

    Program PPP790 will be modified to obtain the translations for the Separation Reason
    Codes (EDB 0141) from the Code Translation Table. Copymember CPWSXSEP
    containing the translated Separation Reason Codes will be made obsolete.


Control Table Updates
•   System Messages Table:

    The following message will be added to the table with a severity level of 9:

    79-032 CTT LOOK UP ERROR - SEPARATION REASON DESCRIPTION


Obsolete Objects
•   Copymember:

    CPWSXSEP will be obsoleted.




                                                                                     24
                                  Final




Attachments
Attachment A   PPP7891 Report (Control Report)
Attachment B   PPP7951 Report (Annual Report)
Attachment C   PPP7952 Report (Monthly Change Report)




                                                        25
                               Final



PPP7891/PPP789/030198              UNIVERSITY OF CALIFORNIA-SYSTEMWIDE   PAGE NO.     0001
RETN: SEE RPTS DISP SCHEDULE/DIST.      UPTE PROCESSING                  RUN DATE 02/01/98
DATA BASE UPDATE DATE 01/01/97 UPTE TX EXCLUSIVELY REPRESENTED EMPLYS

                                           CONTROL COUNTS

                     CNA DATA BASE – TOTAL TX EMPLOYEES DELETED    18




                                                                         ATTACHMENT A




                                                                                             26
                            Final


PPP7951/PPP795/030198           UNIVERSITY OF CALIFORNIA-SYSTEMWIDE    PAGE NO.    0001
RETN: SEE RPTS DISP SCHEDULE/DIST.        UPTE PROCESSING              RUN DATE 02/01/98
DATA BASE UPDATE DATE 01/01/97 UPTE TX EXCLUSIVELY REPRESENTED EMPLS

                                    ANNUAL TOTALS REPORT

                                       CONTROL COUNTS

                   TX ANNUAL FILE TOTAL EMPLOYEES:      18




                                                                       ATTACHMENT B




                                                                                           27
                                      Final


PPP7952/PPP795/030198           UNIVERSITY OF CALIFORNIA-SYSTEMWIDE      PAGE NO.       0001
RETN: SEE RPTS DISP SCHEDULE/DIST.        UPTE PROCESSING                RUN DATE   02/01/98
DATA BASE UPDATE DATE 01/01/97 UPTE TX EXCLUSIVELY REPRESENTED EMPLS

                                                 MONTHLY CHANGE REPORT

             PRIOR DATA                       DATA DESCRIPTION                CURRENT DATA

BARR,CLARK           000050050                ACTION: OTHER CHANGE
    8263 UPHOLSTERER                          TITLE CODE/NAME                 8263 UPHOLSTERER
      52,617.60                               PAY RATE                         52,617.60
    1.00                                      PERCENT FULL TIME                1.00
    2                                         APPOINTMENT TYPE                1.00
    8312 GLASSBLOWER, LABORATORY, SR          TITLE CODE/NAME                 8312 GLASSBLOWER, LABORATORY, SR
    40,089.60                                 PAY RATE                         42,804.00
    1.00                                      PERCENT FULL TIME                1.00
    2                                         APPOINTMENT TYPE                2

NINO,ELM N                955971235           ACTION: NEW HIRE
                                              EMPLOYEE ID                     955971235
                                              EMPLOYEE NAME                   NINO,ELMO N
                                              HIRE DATE                       01/01/1997
                                              TITLE CODE/NAME                 9805 FIRE FIGHTER
                                              PAY RATE                          146,000.00
                                              PERCENT FULL TIME                1.00
                                              APPOINTMENT TYPE                2
                                              HOME DEPARTMENT                 HOSPITAL EDUCATION
                                              HOME DEPARTMENT ADDRESS         1 WILCOX WAY

                                                                              SAN FRANCISCO          CA 95813




                                                                                      ATTACHMENT C



                                                                                                                 28

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:10/7/2011
language:English
pages:29