The expense distribution record details the expenses (benefits and employee earnings) incurred during that pay cycle and associates them with the funding. This data originates from the monthly-produced Expense Distribution File (EDW), formerly known as the Expense Distribution Workfile (EDW). The Full-Accounting-Unit (FAU) has been split into its separate components (Chart, Account, Sub-Account, Object, Sub-Object and Project). The DaFIS-related information such as the Organization (Org), Higher Education Indicator, Sub Fund Group Type, OP Fund, and Cost-sharing code have been added to the record as it is loaded from Office of the President. The Transfer of Expense (ETHTOE) table contains much of the same information, only in a more summarized format. A more detailed write-up covers the differences between these two tables.
Similar to the ETHTOE and PAR tables, the EDWEXP table will be split by fiscal year:
the EDWEXP is for the current fiscal year while EDWEXP98 will be for the 1997-98 fiscal
year.
| Field Name | Business Name | Data Type | Description |
|---|---|---|---|
| EDW_BEN_01_RETR_MTCH | RETIREMENT MATCH BENEFIT AMOUNT | NUMBER(7,2) | The total amount of matched retirement benefits associated with the earnings distribution. Know as "match contribution" on reports. |
| EDW_BEN_02_FILL02 | NOT CURRENTLY USED | NUMBER(7,2) | NOT USED |
| EDW_BEN_03_GSTUI | GRADUATE STUDENT INSURANCE BENEFITS AMOUNT | NUMBER(7,2) | The total amount of the graduate student health insurance benefits associated with the earnings distribution. |
| EDW_BEN_04_OASDI | O A STATE DISABILITY INSURANCE GROSS | NUMBER(7,2) | The gross earnings paid which are subject to employee and employer Old Age and Survivors Disability Insurance (OASDI) contribution. |
| EDW_BEN_05_HEALTH | HEALTH BENEFITS AMOUNT | NUMBER(7,2) | The total amount of the health insurance benefits associated with the earnings distribution. |
| EDW_BEN_06_ANN_HEALTH | ANN HEALTH BENEFITS AMOUNT | NUMBER(7,2) | The total amount of the annuitant health insurance benefits associated with the earnings distribution. |
| EDW_BEN_07_LI | LIFE INSURANCE BENEFITS AMOUNT | NUMBER(7,2) | The total amount of Life Lnsurance benefits associated with the earnings distribution. |
| EDW_BEN_08_NDI | NDI BENEFITS AMOUNT | NUMBER(7,2) | The total amount of disability benefits associated with the earnings distribution. |
| EDW_BEN_09_WC | WORKERS COMPENSATION BENEFIT AMOUNT | NUMBER(7,2) | The total amount of Workers Compensation benefits associated with the earnings distribution. |
| EDW_BEN_10_UI | UNEMPLOYMENT INSURANCE BENEFITS AMOUNT | NUMBER(7,2) | The total amount of unemployment insurance benefits associated with the earnings distribution. |
| EDW_BEN_11_ACCRD_LEAVE | ACCRUED LEAVE BENEFITS AMOUNT | NUMBER(7,2) | The total amount of accrued leave benefits associated with the earnings distribution. |
| EDW_BEN_12_FILL12 | INCENTIVE AWARDS PROGRAM | NUMBER(7,2) | The total amount of Incentive Awards Program (IAP) benefits associated with the earnings distribution. |
| EDW_BEN_13_DENTAL | DENTAL BENEFITS AMOUNT | NUMBER(7,2) | The total amount of the dental insurance benefits associated with the earnings distribution. |
| EDW_BEN_14_MEDICR | MEDICARE BENEFITS AMOUNT | NUMBER(7,2) | The total amount of the medicare insurance benefits associated with the earnings distribution. |
| EDW_BEN_15_VISION | VISION BENEFITS AMOUNT | NUMBER(7,2) | The total amount of the vision insurance benefits associated with the earnings distribution. |
| EDW_BEN_16_GSPFR | GSPFR BENEFITS AMOUNT | NUMBER(7,2) | The total amount of Graduate Student Fee Reduction benefits associated with the earnings distribution. |
| EDW_BEN_17_LEGAL | LEGAL PLAN BENEFITS AMOUNT | NUMBER(7,2) | The total amount of the Legal plan benefits associated with the earnings distribution. |
| EDW_BEN_18_EMP_SUP | EMPLOYEE SUP PROGRAM BENEFIT AMOUNT | NUMBER(7,2) | The total amount of employee support program (ESP) benefits associated with the earnings distribution. No longer used at UCD. |
| EDW_BEN_19_CORE_MEDICAL | CORE MEDICAL BENEFITS AMOUNT | NUMBER(7,2) | The total amount of core medical benefits associated with the earnings distribution. |
| EDW_BEN_20_CORE_LIFE | CORE LIFE INSURANCE BENEFITS AMOUNT | NUMBER(7,2) | The total amount of core life insurance benefits associated with the earnings distribution. |
| EDW_BEN_21_GSH | GSH BENEFITS AMOUNT | NUMBER(7,2) | The total amount of Graduate Student Health benefits associated with the earnings distribution. |
| EDW_CYCLE_TYPE | CYCLE TYPE | VARCHAR2(2) | The code indicating pay cycle (values are B1=first biweekly of the month, B2=second biweekly of the month, MA=monthly arrears, MO=monthly regular). Not currently implemented. Must be derived from the PARERN table using EMPLOYEE_ID and PAR_CNTL_NUM + PAR_DIST_NUM. |
| EDW_DOS | DESCRIPTION OF SERVICE CODE | VARCHAR2(3) | The code indicating the type of service or type of pay associated with the earnings distribution. (translation in CTLDOS table) |
| EDW_EARN_APPT_TYPE_CODE | APPOINTMENT TYPE CODE | VARCHAR2(1) | The code indicating the type of appointment on which the employee was paid. (values are 1=contract, 2=regular/career, 3=casual, 4=casual/restricted, 5=academic, 6=per diem, or 7=regular/career partial-year) |
| EDW_EARN_COVERAGE_IND | EARNINGS COVERAGE INDICATOR | VARCHAR2(1) | The code indicating whether the predominant appointment of an individual is eligible to be represented, for collective bargaining purposes at the time the employee was paid. Use EDW_EMP_COV_IND instead - relationship between these two fields is still under research. |
| EDW_EARN_DIST_UNIT_CODE | EARNINGS DISTRIBUTION UNIT CODE | VARCHAR2(1) | NOT USED. (The code indicating the distribution unit status of the predominant distribution of the predominant appointment of an individual for collective bargaining purposes at the time the employee was paid. The relationship to EDW_EMP_DIST_UNIT_CODE is still under research. (values are BLANK=no predominant distribution unit code, ?=indeterminant, or *=to be determined) |
| EDW_EARN_EMP_REL_CODE | EARNINGS EMPLOYEE RELATIONS UNIT CODE | VARCHAR2(1) | The code indicating the designation of the employee for purposes of collective bargaining at the time the employee was paid. Use EDW_EMP_REL_CODE instead - relationship between these two fields is still under research. |
| EDW_EMP_COV_IND | EMPLOYEE COVERAGE INDICATOR | VARCHAR2(1) | The code indicating whether the predominant appointment of an individual is eligible to be represented, for collective bargaining purposes at the time the employee was paid. Use instead of EDW_EARN_COVERAGE_IND - the difference between these two fields is still under research. (values are C=covered, U=uncovered, S=supervisor-uncovered) |
| EDW_EMP_DIST_UNIT_CODE | EMPLOYEE DISTRIBUTION UNIT CODE | VARCHAR2(1) | NOT USED. (The code indicating the distribution unit status of the predominant distribution of the predominant appointment of an individual for collective bargaining purposes at the time the employee was paid. The relationship to EDW_EARN_DIST_UNIT_CODE is still under research. (values are BLANK=no predominant distribution unit code, ?=indeterminant, or *=to be determined) |
| EDW_EMP_REL_CODE | EMPLOYEE RELATIONS CODE | VARCHAR2(1) | The code indicating the supervisory/confidential designation of an individual for purposes of collective bargaining. Use instead of EDW_EARN_EMP_REL_CODE - relationship between these two fields is still under research. (values are A=manager-not confidential, B=manager-confidential, C=supervisor-not confidential, D=supervisor-confidential, E=all others-not confidential, F=all others-confidential, G-not covered by HEERA-out of state, H-student academic- covered by HEERA, I=student academic-not covered by HEERA) |
| EDW_EMP_SPCL_HNDLG_CODE | EMPLOYEE SPECIAL HANDLING CODE | VARCHAR2(1) | The code indicating the Title Special Handling Code of the predominant appointment of an individual for collective bargaining purposes at the time the employee was paid. Use instead of EDW_ERN_SPCL_HNDLG_CODE - relationship between these two fields is still under research. (values are BLANK=no title special handling code,?=indeterminate, *=to be determined) |
| EDW_EMP_UNIT_CODE | EMPLOYEE UNIT CODE | VARCHAR2(2) | The code indicating the title unit code (bargaining unit) of the predominant appointment of an individual for collective bargaining purposes. (translation in CTLBUT table) |
| EDW_EMPLOYEE_ID | EMPLOYEE ID NUMBER | VARCHAR2(9) | The unique employee identification number. (key) |
| EDW_ERN_SPCL_HNDLG_CODE | EARNINGS SPECIAL HANDLING CODE | VARCHAR2(1) | The code indicating the Title Special Handling Code of the predominant appointment of an individual for collective bargaining purposes at the time the employee was paid. Use EDW_EMP_SPCL_HNDLG_CODE instead - relationship between these two fields is still under research. |
| EDW_ERNG_PP_END_DATE | EARNINGS PAY PERIOD END DATE | DATE | The ending date of the pay period when this transaction is processed. |
| EDW_ET_MONTH | ET FISCAL YEAR-MONTH | VARCHAR2(4) | Fiscal Year and Month during which this expense transaction was processed. Field has been split into: FISCAL_YEAR and FISCAL_PERIOD. (key) |
| EDW_EXP_TRNF_RC | EXPENSE TRANSFER RC | VARCHAR2(1) | Under research |
| EDW_FAU | FULL ACCOUNTING UNIT | VARCHAR2(30) | The identifier assigned by the department for the purpose of identifying a specific expenditure. For payroll purposes, this is the identifier of an account against which salary or wage expenses are charged. The full accounting unit (FAU) is composed of the chart of accounts number, account number, sub account number, object number, sub object number and project number. This field has been split into its individual components during the update/load process) |
| EDW_FAU_CHART | CHART OF ACCOUNTS NUMBER | VARCHAR2(1) | The chart of accounts identifier. (created from FAU field during update/load process) (translation in COACRT table) |
| EDW_FAU_ACCT | ACCOUNT NUMBER | VARCHAR2(7) | The organization-chosen identifier used to classify financial resources for accounting and reporting purposes. (created from FAU field during update/load process) (translation in COAACR table) |
| EDW_FAU_SUBACCT | SUB ACCOUNT NUMBER | VARCHAR2(5) | The code that allows an account to be broken down in more detail (Cost Center) in order to better track detail transactions. The sub account takes on the attributes of the account it reports to, including account manager, fund group and function code. Assigned by account managers. (created from FAU field during update/load process) (translation in COASAC table) |
| EDW_FAU_OBJECT | OBJECT NUMBER | VARCHAR2(4) | The identifer used to classify accounting transactions by type. Examples are academic salaries, in-state travel, Reg Fee income. As of December 1997, this also represents the FAU Object Consolidation. (created from FAU field during update/load process) (translation in COAOBJ table) |
| EDW_FAU_SUBOBJ | SUB OBJECT NUMBER | VARCHAR2(3) | The code that provides for an object code to be broken down into greater detail. Assigned by account manager. (created from FAU field during update/load process) (translation in COASOB table) |
| EDW_FAU_PROJECT | PROJECT NUMBER | VARCHAR2(10) | The code used to track and accumulate transactions across multiple charts, accounts and fund groups. (created from FAU field during update/load process) (translation in COAPRJ table) |
| EDW_FAU_COST_SHARING_IND | COST SHARING INDICATOR | VARCHAR2(1) | The code indicating the cost sharing requirements of an account. (added from DaFIS Account table during update/load process) |
| EDW_FAU_FFT_CODE | FEDERAL-FLOW-THROUGH CODE | VARCHAR2(1) | The code indicating that this account is designated as a Federal-Flow-Through account. (added from DaFIS Account table during update/load process) |
| EDW_FAU_HIGH_ED_FUNC_CD | HIGHER EDUCATION FUNCTION CODE | VARCHAR2(4) | Function codes which are assigned to each account which match expenditures to the functionality of the account. Higher education examples are instruction, academic support, art and museums, etc. These names are the most detailed level of functional classification and are utilized in determining AICPA function codes, and federal function codes for indirect cost purposes. (added from DaFIS Account table during update/load process) |
| EDW_FAU_HOME_DEPT_CD | HOME DEPARTMENT NUMBER | VARCHAR2(6) | The department who funded the expenditures. Currently used to identify campus departments for Payroll and report distribution. (added from DaFIS Account table during update/load process) (translation on CTLHME table) |
| EDW_FAU_OP_FUND | UCD/OP FUND NUMBER | VARCHAR2(6) | UCD pre-DAFIS fund number for reporting to Office of the President. (added from DaFIS Account table during update/load process) |
| EDW_FAU_ORG_CD | ORGANIZATION CODE | VARCHAR2(4) | The DaFIS identifier of the organization funding the expenses. (added from DaFIS Account table during update/load process) |
| EDW_FAU_SUB_FUND_GRP_TYP_CD | SUB FUND GROUP TYPE CODE | VARCHAR2(1) | Used to group like sub funds. For example, Federal Contracts would be a sub fund group type with a number of associated sub fund groups. (added from DaFIS Account table during update/load process) |
| EDW_NAME | EMPLOYEE NAME | VARCHAR2(26) | The name of the employee. Format is "last,first m". |
| EDW_ORIG_GROSS_ERN | ORIGINAL GROSS EARNINGS | NUMBER(7,2) | The total amount of the earnings distribution. |
| EDW_PAR_CNTL_NUM | PAR CONTROL NUMBER | NUMBER(3) | Under research |
| EDW_PAR_DIST_NUM | PAR DISTRIBUTION NUMBER | NUMBER(2) | The number uniquely identifying a payroll distribution associated with an appointment. |
| EDW_PAY_CYCLE | PAY CYCLE CODE | VARCHAR2(1) | The code indicating the type of pay cycle for this distribution (values are B=biweekly, S=semi-monthly, M=monthly, X=special payroll). |
| EDW_RATE_TYPE | RATE TYPE CODE | VARCHAR2(1) | The code indicating whether the associated distribution pay rate is an hourly pay rate (H) or a pay period salary pay rate (%). |
| EDW_RATE_SALARY | SALARY RATE | NUMBER(9,4) | The full time hourly rate, pay period amount, or by-agreement amount associated with the distribution. |
| EDW_RETR_PLN | RETIREMENT PLAN CODE | VARCHAR2(1) | The code indicating the retirement plan to which the individual belongs. |
| EDW_RETRO_IND | RETROACTIVE DISTRIBUTION INDICATOR | VARCHAR2(1) | The code indicating that this is a retroactive earnings distribution. (values are R=retroactive, BLANK=not retroactive) |
| EDW_SPC_RETIR | SPECIAL RETIREMENT | NUMBER(1) | Under research |
| EDW_TIME | AMOUNT OF TIME IN HOURS OR AS A PERCENT | NUMBER(5,2) | The number of hours of time associated with the distribution (if the RATE_TYPE = 'H' - ie. 30.50 hours) or the percentage of full time associated with the earnings (if the RATE_TYPE = '%' - ie. 75.00 percent time ). |
| EDW_TIME_RPT_CODE | TIME REPORTING CODE | VARCHAR2(1) | The time reporting code associated with this earnings distribution. (values are P=positive, E=exception) |
| EDW_TITLE | JOB TITLE CODE | VARCHAR2(4) | The code indicating the position classification associated with the earnings distribution. (translation in CTLTCI table) |
| EDW_TITLE_UNIT_CODE | TITLE UNIT CODE | VARCHAR2(2) | The code indicating the title unit code (bargaining unit) of the job title for the individual for collective bargaining purposes. (translation in CTLBUT table) |
| EDW_TOE_SEQ_NUM | TOE SEQUENCE NUMBER | NUMBER(4) | NOT USED |
| EDW_TRANS_ADJ_CODE | TRANSACTION ADJUSTMENT CODE | VARCHAR2(1) | The code, if present, indicating type of adjustment. (values are E=expense transfer, C=cancellation, O=overpayment, H=hand drawn, R=r hand drawn, BLANK=not an adjustment transaction) |
| EDW_TRANS_END_DATE | TRANSACTION END DATE | DATE | The ending date of the original pay cycle associated with the earnings distribution. |
| EDW_TRANS_OBJ_CODE | TRANSACTION OBJECT CODE | VARCHAR2(4) | The identifer used to classify accounting transactions by type. Examples are academic salaries, in-state travel, Reg Fee income. |
| FISCAL_PERIOD | FISCAL PERIOD-MONTH | VARCHAR2(2) | The fiscal Period or Month during which this expense transaction was processed. |
| FISCAL_YEAR | FISCAL YEAR | VARCHAR2(4) | The four-digit Fiscal Year during which this expense transaction was processed. |
| Data Dictionary | PPS Main Menu |
Send Comments to: PPS Data
Warehouse Support
Last Updated: February 24, 1999