Cumulative Compensation Decision Support System (CumCompDSS) Table Definition

View/Print PDF Version

Tables Available

CUMCOMP_CUM_COMP_DTL

This table has transactions for the compensation of individuals at the person, journal and Master File Key (MFK) level by pay period. It includes transactions that are entered into the HR Peoplesoft system and adjustments made through the web-based PayCV application.

For more information about the MFK, see the University Accounting Code Manual.

Field Name

Field Description

Data Type

Data Length

----------------------Comments----------------------
PAY_PERSON_ID_NBR

The Person Identification Number in the Human Resources system.

VARCHAR2

11

 

PAY_SOC_SEC_NBR

The individual’s Social Security Number.

CHAR

9

 

PAY_FULL_NAME

The individual’s full name in the Human Resources system.

VARCHAR2

40

Format: Last name and first name with no commas.

PAY_JOB_CD

The Job Code in the Human Resources system.

CHAR

4

 

PAY_GROUP

The Pay Group in the Human Resources system.

CHAR

3

 

PAY_EARNINGS_CD

The Earnings Code in the Human Resources system.

CHAR

3

Only associated with salary amounts.

PAY_DEDUCT_CD

The Deduction Code in the Human Resources system.

CHAR

6

Only associated with fringe amounts.

GL_CORP_ELEM_CD

The Corporation Code element of the University Master File Key (MFK).

CHAR

2

 

GL_FUND_ELEM_CD

The Fund element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

3

 

GL_ORG_ELEM_CD

The Organizational Unit element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

2

 

GL_DEPT_ELEM_CD

The Department element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

4

The system treats Department and Sub-Department as one element for validation purposes.

GL_SUB_DEPT_ELEM_CD

The Sub-Department element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

5

The system treats Department and Sub-Department as one element for validation purposes.
The Sub-Department is a sub-element of the Department.

GL_GRANT_PGM_ELEM_CD

The Grant/Program element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

8

The system treats the Grant/Program ID, Grant/Program Number and Grant/Program Sub-Component as one element for validation purposes.

GL_INST_ACCT_ELEM_CD

The Institutional Account element is part of the:
  - Master File Key (MFK)

CHAR

4

For Healthcare Org Units, the system treats Institutional Account and Organizational Account as one element for validation purposes.

GL_ORG_ACCT_ELEM_CD

The Organizational Account element is part of the:
  - Master File Key (MFK)

CHAR

3

For Healthcare Org Units, the system treats Institutional Account and Organizational Account as one element for validation purposes.

GL_DEPT_ACCT_ELEM_CD The Departmental Account element is part of the:
  - Master File Key (MFK)
CHAR 5  
GL_FUNCTION_ELEM_CD The Function element is part of the:
  - WhoKey
  - Master File Key (MFK)
CHAR 2  
GL_COST_CTR_ELEM_CD The Cost Center element is part of the:
  - Master File Key (MFK)
CHAR 4  
PAY_PRD_BEGIN_DT The Pay Period Begin Date. DATE mm/dd/yy  
PAY_PRD_END_DT The Pay Period End Date. DATE mm/dd/yy  
PAY_CHECK_DT The Pay Check Date. DATE mm/dd/yy  
PAY_AMT The Amount of the transaction. NUMBER 22  
GL_JRNL_ID The Journal Identification (Journal ID) that is assigned to the specified general ledger journal entry. CHAR 10 The first digit indicates the type of journal (1, 2, 3, 4, 5 – Actuals; 6 – Balance Forward; 7 - Budget; 9– Encumbrance). The Journal ID, Journal Effective Date, Journal Sequence Number and Journal Line Number uniquely identify a journal transaction.
GL_JRNL_EFFECT_DT The Journal Effective Date that is assigned to the specified general ledger journal entry. DATE mm/dd/yy  
GL_JRNL_SEQ_NBR The Journal Sequence Number that is assigned to the specified general ledger journal entry. NUMBER 5  
ACCTG_PRD The Accounting Period represents the fiscal accounting period. CHAR 2 A two character field and is related to the GL_PS_ACCTG_PRD field. The periods are populated as:
01-12: Base periods for all units. July as '01', August as '02' etc. up through June as '12'
13-15: June Preliminary as '13', June Final as '14', June Audit as '15' for year-end closing (15 is not used for CumComp purposes, only GLDSS)
ACCTG_FISC_YR The Accounting Fiscal Year represents the University fiscal year. CHAR 4 Example, fiscal year 2001 represents July 2000 through June 2001.
BUDGET_REF     The Budget Reference (BRF) element is part of the:
  - WhoKey
  - Master File Key (MFK)
for only sponsored project accounts (funds 500/510) or cost sharing accounts (grant/program starts with a '3').                                                           
CHAR 8 Grant Accounting establishes the values from '01' to '99', based on the budget period(s) specific for each grant/cost share account.
All other funding source accounts will have a blank/no value for this field.
GL_PS_ACCTG_PRD    The PeopleSoft Accounting Period represents the base or adjustment accounting period.                CHAR 3 This is a maximum three character field and is related to the ACCTG_PRD field. The periods are populated as:
01-12: Base periods for all units. July as '01', August as '02' etc. up through June as '12'
13-15: June Preliminary as '13', June Final as '14', June Audit as '15' for year-end closing (15 is not used for CumComp purposes, only GLDSS)
901-911: Adjustment periods for Healthcare units accrual reporting. '901' is the July adjustment period that goes with
July period '01', etc. (Not used for CumComp purposes, only GLDSS)
912: Adjustment period for June reporting that goes with periods 13, 14 & 15 (Period 15 is not used for CumComp purposes, only GLDSS).
ADJUSTMENT_IND Adjustment Indicator for the accounting period.              CHAR 1 A one character field that indicates if the accounting period is an adjustment period (Y) or not (N) and is related to the GL_PS_ACCTG_PRD and ACCTG_PRD fields.
The field is populated with a 'Y' when GL_PS_ACCTG_PRD has a value of '901'-'911' OR the ACCTG_PRD has a value of '15'.
The field is populated with an 'N' when ACCTG_PRD has a value of '01'-'14'.
UNIVID The individual’s University ID in the Human Resources system. CHAR 11  

 

 

 

 

 

CUMCOMP_CUM_COMP_SUMRY

This table has compensation data for individuals at the person and Master File Key (MFK) level by Accounting Period and Fiscal Year. It includes summarization for data that is entered into the HR Peoplesoft system and adjustments made through the web-based Pay CV application.

For more information about the MFK, see the University Accounting Code Manual.

Field Name

Field Description

Data Type

Data Length

----------------------Comments----------------------

PAY_PERSON_ID_NBR

The Person Identification Number in the Human Resources system.

VARCHAR2

11

 

PAY_SOC_SEC_NBR

The individual’s Social Security Number.

CHAR

9

 

PAY_FULL_NAME

The individual’s full name in the Human Resources system.

VARCHAR2

40

Format: Last name and first name with no commas.

PAY_JOB_CD

The Job Code in the Human Resources system.

CHAR

4

 

GL_CORP_ELEM_CD

The Corporation Code element of the University Master File Key (MFK).

CHAR

2

 

GL_FUND_ELEM_CD

The Fund element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

3

 

GL_ORG_ELEM_CD

The Organizational Unit element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

2

 

GL_DEPT_ELEM_CD

The Department element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

4

The system treats Department and Sub-Department as one element for validation purposes.

GL_SUB_DEPT_ELEM_CD

The Sub-Department element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

5

The system treats Department and Sub-Department as one element for validation purposes.
The Sub-Department is a sub-element of the Department.

GL_GRANT_PGM_ELEM_CD

The Grant/Program element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

8

The system treats the Grant/Program ID, Grant/Program Number and Grant/Program Sub-Component as one element for validation purposes.

GL_INST_ACCT_ELEM_CD

The Institutional Account element is part of the:
  - Master File Key (MFK)

CHAR

4

For Healthcare Org Units, the system treats Institutional Account and Organizational Account as one element for validation purposes.

GL_ORG_ACCT_ELEM_CD

The Organizational Account element is part of the:
  - Master File Key (MFK)

CHAR

3

For Healthcare Org Units, the system treats Institutional Account and Organizational Account as one element for validation purposes.

GL_DEPT_ACCT_ELEM_CD The Departmental Account element is part of the:
  - Master File Key (MFK)
CHAR 5  
GL_FUNCTION_ELEM_CD The Function element is part of the:
  - WhoKey
  - Master File Key (MFK)
CHAR 2  
GL_COST_CTR_ELEM_CD The Cost Center element is part of the:
  - Master File Key (MFK)
CHAR 4  
PAY_PRD_BEGIN_DT The Pay Period Begin Date. DATE mm/dd/yy  
PAY_PRD_END_DT The Pay Period End Date. DATE mm/dd/yy  
PAY_CHECK_DT The Pay Check Date. DATE mm/dd/yy  
PAY_PAY_CUR_AMT The Pay Amount by the University of Iowa fiscal accounting period. NUMBER 22  
PAY_PAY_YTD_AMT The Pay Amount Year-To-Date by the University of Iowa fiscal year. NUMBER 22  
PAY_FRINGE_CUR_AMT The Fringe Amount by the University of Iowa fiscal accounting period. NUMBER 22  
PAY_FRINGE_YTD_AMT The Fringe Amount Year-To-Date by the University of Iowa fiscal year. NUMBER 22  
ACCTG_PRD The Accounting Period represents the fiscal accounting period. CHAR 2 A two character field and is related to the GL_PS_ACCTG_PRD field. The periods are populated as:
01-12: Base periods for all units. July as '01', August as '02' etc. up through June as '12'
13-15: June Preliminary as '13', June Final as '14', June Audit as '15' for year-end closing (15 is not used for CumComp purposes, only GLDSS)
ACCTG_FISC_YR The Accounting Fiscal Year represents the University fiscal year. CHAR 4 Example, fiscal year 2001 represents July 2000 through June 2001.
BUDGET_REF     The Budget Reference (BRF) element is part of the:
  - WhoKey
  - Master File Key (MFK)
for only sponsored project accounts (funds 500/510) or cost sharing accounts (grant/program starts with a '3').                                                           
CHAR 8 Grant Accounting establishes the values from '01' to '99', based on the budget period(s) specific for each grant/cost share account.
All other funding source accounts will have a blank/no value for this field.
GL_PS_ACCTG_PRD    The PeopleSoft Accounting Period represents the base or adjustment accounting period.                CHAR 3 This is a maximum three character field and is related to the ACCTG_PRD field. The periods are populated as:
01-12: Base periods for all units. July as '01', August as '02' etc. up through June as '12'
13-15: June Preliminary as '13', June Final as '14', June Audit as '15' for year-end closing (15 is not used for CumComp purposes, only GLDSS)
901-911: Adjustment periods for Healthcare units accrual reporting. '901' is the July adjustment period that goes with
July period '01', etc. (Not used for CumComp purposes, only GLDSS)
912: Adjustment period for June reporting that goes with periods 13, 14 & 15 (Period 15 is not used for CumComp purposes, only GLDSS).
ADJUSTMENT_IND Adjustment Indicator for the accounting period.              CHAR 1 A one character field that indicates if the accounting period is an adjustment period (Y) or not (N) and is related to the GL_PS_ACCTG_PRD and ACCTG_PRD fields.
The field is populated with a 'Y' when GL_PS_ACCTG_PRD has a value of '901'-'911' OR the ACCTG_PRD has a value of '15'.
The field is populated with an 'N' when ACCTG_PRD has a value of '01'-'14'.
UNIVID The individual’s University ID in the Human Resources system. CHAR 11  

 

CUMCOMP_VW_CUM_COMP_SUMRY_YTD

This view has YTD compensation data for individuals at the person and Master File Key (MFK) level by Accounting Period and Fiscal Year. It includes summarization for data that is entered into the HR Peoplesoft system and adjustments made through the web-based Pay CV application.

For more information about the MFK, see the University Accounting Code Manual.

Field Name

Field Description

Data Type

Data Length

----------------------Comments----------------------

PAY_PERSON_ID_NBR

The Person Identification Number in the Human Resources system.

VARCHAR2

11

 

PAY_SOC_SEC_NBR

The individual’s Social Security Number.

CHAR

9

 

PAY_FULL_NAME

The individual’s full name in the Human Resources system.

VARCHAR2

40

Format: Last name and first name with no commas.

PAY_JOB_CD

The Job Code in the Human Resources system.

CHAR

4

 

GL_CORP_ELEM_CD

The Corporation Code element of the University Master File Key (MFK).

CHAR

2

 

GL_FUND_ELEM_CD

The Fund element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

3

 

GL_ORG_ELEM_CD

The Organizational Unit element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

2

 

GL_DEPT_ELEM_CD

The Department element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

4

The system treats Department and Sub-Department as one element for validation purposes.

GL_SUB_DEPT_ELEM_CD

The Sub-Department element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

5

The system treats Department and Sub-Department as one element for validation purposes.
The Sub-Department is a sub-element of the Department.

GL_GRANT_PGM_ELEM_CD

The Grant/Program element is part of the:
  - WhoKey
  - Master File Key (MFK)

CHAR

8

The system treats the Grant/Program ID, Grant/Program Number and Grant/Program Sub-Component as one element for validation purposes.

GL_INST_ACCT_ELEM_CD

The Institutional Account element is part of the:
  - Master File Key (MFK)

CHAR

4

For Healthcare Org Units, the system treats Institutional Account and Organizational Account as one element for validation purposes.

GL_ORG_ACCT_ELEM_CD

The Organizational Account element is part of the:
  - Master File Key (MFK)

CHAR

3

For Healthcare Org Units, the system treats Institutional Account and Organizational Account as one element for validation purposes.

GL_DEPT_ACCT_ELEM_CD The Departmental Account element is part of the:
  - Master File Key (MFK)
CHAR 5  
GL_FUNCTION_ELEM_CD The Function element is part of the:
  - WhoKey
  - Master File Key (MFK)
CHAR 2  
GL_COST_CTR_ELEM_CD The Cost Center element is part of the:
  - Master File Key (MFK)
CHAR 4  
PAY_PAY_YTD_AMT The Pay Amount Year-To-Date by the University of Iowa fiscal year. NUMBER 22  
PAY_FRINGE_YTD_AMT The Fringe Amount Year-To-Date by the University of Iowa fiscal year. NUMBER 22  
ACCTG_PRD The Accounting Period represents the fiscal accounting period. CHAR 2 A two character field and is related to the GL_PS_ACCTG_PRD field. The periods are populated as:
01-12: Base periods for all units. July as '01', August as '02' etc. up through June as '12'
13-15: June Preliminary as '13', June Final as '14', June Audit as '15' for year-end closing (15 is not used for CumComp purposes, only GLDSS)
ACCTG_FISC_YR The Accounting Fiscal Year represents the University fiscal year. CHAR 4 Example, fiscal year 2001 represents July 2000 through June 2001.
BUDGET_REF     The Budget Reference (BRF) element is part of the:
  - WhoKey
  - Master File Key (MFK)
for only sponsored project accounts (funds 500/510) or cost sharing accounts (grant/program starts with a '3').                                                           
CHAR 8 Grant Accounting establishes the values from '01' to '99', based on the budget period(s) specific for each grant/cost share account.
All other funding source accounts will have a blank/no value for this field.
GL_PS_ACCTG_PRD    The PeopleSoft Accounting Period represents the base or adjustment accounting period.                CHAR 3 This is a maximum three character field and is related to the ACCTG_PRD field. The periods are populated as:
01-12: Base periods for all units. July as '01', August as '02' etc. up through June as '12'
13-15: June Preliminary as '13', June Final as '14', June Audit as '15' for year-end closing (15 is not used for CumComp purposes, only GLDSS)
901-911: Adjustment periods for Healthcare units accrual reporting. '901' is the July adjustment period that goes with
July period '01', etc. (Not used for CumComp purposes, only GLDSS)
912: Adjustment period for June reporting that goes with periods 13, 14 & 15 (Period 15 is not used for CumComp purposes, only GLDSS).
ADJUSTMENT_IND Adjustment Indicator for the accounting period.              CHAR 1 A one character field that indicates if the accounting period is an adjustment period (Y) or not (N) and is related to the GL_PS_ACCTG_PRD and ACCTG_PRD fields.
The field is populated with a 'Y' when GL_PS_ACCTG_PRD has a value of '901'-'911' OR the ACCTG_PRD has a value of '15'.
The field is populated with an 'N' when ACCTG_PRD has a value of '01'-'14'.
UNIVID The individual’s University ID in the Human Resources system. CHAR 11