Cumulative Compensation Decision Support System (CumCompDSS) – Table Definition

PDF iconView/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_CDThe Departmental Account element is part of the:
  - Master File Key (MFK)
CHAR5 
GL_FUNCTION_ELEM_CDThe Function element is part of the:
  - WhoKey
  - Master File Key (MFK)
CHAR2 
GL_COST_CTR_ELEM_CDThe Cost Center element is part of the:
  - Master File Key (MFK)
CHAR4 
PAY_PRD_BEGIN_DTThe Pay Period Begin Date.DATEmm/dd/yy 
PAY_PRD_END_DTThe Pay Period End Date.DATEmm/dd/yy 
PAY_CHECK_DTThe Pay Check Date.DATEmm/dd/yy 
PAY_AMTThe Amount of the transaction.NUMBER22 
GL_JRNL_IDThe Journal Identification (Journal ID) that is assigned to the specified general ledger journal entry.CHAR10The 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_DTThe Journal Effective Date that is assigned to the specified general ledger journal entry.DATEmm/dd/yy 
GL_JRNL_SEQ_NBRThe Journal Sequence Number that is assigned to the specified general ledger journal entry.NUMBER5 
ACCTG_PRDThe Accounting Period represents the fiscal accounting period.CHAR2A 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_YRThe Accounting Fiscal Year represents the University fiscal year.CHAR4Example, 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').                                                           
CHAR8Grant 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.               CHAR3This 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_INDAdjustment Indicator for the accounting period.             CHAR1A 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'.
UNIVIDThe individual’s University ID in the Human Resources system.CHAR11 

 

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_CDThe Departmental Account element is part of the:
  - Master File Key (MFK)
CHAR5 
GL_FUNCTION_ELEM_CDThe Function element is part of the:
  - WhoKey
  - Master File Key (MFK)
CHAR2 
GL_COST_CTR_ELEM_CDThe Cost Center element is part of the:
  - Master File Key (MFK)
CHAR4 
PAY_PRD_BEGIN_DTThe Pay Period Begin Date.DATEmm/dd/yy 
PAY_PRD_END_DTThe Pay Period End Date.DATEmm/dd/yy 
PAY_CHECK_DTThe Pay Check Date.DATEmm/dd/yy 
PAY_PAY_CUR_AMTThe Pay Amount by the University of Iowa fiscal accounting period.NUMBER22 
PAY_PAY_YTD_AMTThe Pay Amount Year-To-Date by the University of Iowa fiscal year.NUMBER22 
PAY_FRINGE_CUR_AMTThe Fringe Amount by the University of Iowa fiscal accounting period.NUMBER22 
PAY_FRINGE_YTD_AMTThe Fringe Amount Year-To-Date by the University of Iowa fiscal year.NUMBER22 
ACCTG_PRDThe Accounting Period represents the fiscal accounting period.CHAR2A 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_YRThe Accounting Fiscal Year represents the University fiscal year.CHAR4Example, 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').                                                           
CHAR8Grant 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.               CHAR3This 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_INDAdjustment Indicator for the accounting period.             CHAR1A 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'.
UNIVIDThe individual’s University ID in the Human Resources system.CHAR11 

 

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_CDThe Departmental Account element is part of the:
  - Master File Key (MFK)
CHAR5 
GL_FUNCTION_ELEM_CDThe Function element is part of the:
  - WhoKey
  - Master File Key (MFK)
CHAR2 
GL_COST_CTR_ELEM_CDThe Cost Center element is part of the:
  - Master File Key (MFK)
CHAR4 
PAY_PAY_YTD_AMTThe Pay Amount Year-To-Date by the University of Iowa fiscal year.NUMBER22 
PAY_FRINGE_YTD_AMTThe Fringe Amount Year-To-Date by the University of Iowa fiscal year.NUMBER22 
ACCTG_PRDThe Accounting Period represents the fiscal accounting period.CHAR2A 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_YRThe Accounting Fiscal Year represents the University fiscal year.CHAR4Example, 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').                                                           
CHAR8Grant 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.               CHAR3This 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_INDAdjustment Indicator for the accounting period.             CHAR1A 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'.
UNIVIDThe individual’s University ID in the Human Resources system.CHAR11