Getting Started With the Utility

Before setting up and running the Utility, you can analyze the data that is kept in sync between your PeopleSoft CS and PeopleSoft HCM databases. This information is needed for configuring the Utility.

Here are some points to consider regarding this analysis:

• Oracle integration design models (Owner/Subscriber, Subscriber Only) assume that shared data is replicated exactly between both databases.

  Note:

  Some data fields, such as audit date/time fields, may have slightly different values in each database due to the nature of Application Messaging.

• Is there any custom data filtering as part of your integration design? For tables where the data is not fully synchronized, a view is needed to achieve accurate comparison results. A view allows the Utility to compare a subset of fields between the databases, or supports the use of custom logic defined in the view SQL.

• The list of tables that are synchronized differ at every institution, and may include customizations. Your resulting list becomes the scope of tables to be compared between the databases.

• There are several delivered methods for performing data comparison and resolution. You can choose a method depending on your data and database environments. You may also develop and add other comparison or resolution methods.

• Related Language tables must be separately compared and resolved.

• Audit tables and fields should not be compared.

• The compare process reports data discrepancies by table, and it does not enforce the parent/child relationship. Please note the following guidance when using the Resolution process to resync data:

  • Add missing parent record rows before adding any children data rows.

  • Delete child data rows before deleting a parent data row.

Identify Synchronized Tables

Identify the list of tables where data is synchronized between your PeopleSoft CS and PeopleSoft HCM databases; be sure to include setup tables and all tables where manual data entry occurs in both databases.

The following list of synchronized tables is supported by delivered integrations. Not all tables may be populated in your CS database, or have data that your institution keeps in sync. These tables form part of the message structure definition and therefore are included in this list.

Delivered Base Tables

This list is also populated as setup data on the CS Permission Maps page.

Name	Description
ACCOM_DIAGNOSIS	EE Accommodation Diagnosis
ACCOM_JOB_TASK	EE Accommodation Job Tasks
ACCOM_OPTION	EE Accommodation Options
ACCOM_OP_LNG	EE Accommodation Options
ACCOM_REQUEST	EE Accommodation Requests
ADDRESSES	Address Type
ADDRESS_TYP_LNG	Address Type Table (rel. lang)
ADDRESS_TYP_TBL	Address Type Table
BEN_PROG_PARTIC	EE Benefit Programs
BUS_UNIT_HR_LNG	Rellang HR Business Unit
BUS_UNIT_TBL_HR	HR Business Unit Table
CAN_JOBCODE_CU	Canadian WCB Jobcode to CU
CITIZENSHIP	EE/Dependent Citizenship
CITIZEN_PSSPRT	EE/Dependent Passport Data
CITIZEN_STS_LNG	Citizen Status Language Table
CITIZEN_STS_TBL	Citizen Status Table
CIT_PSSPRT_LANG	EE/Dependent Passport Data
COMPANY_TBL	Company Codes
COMPANY_TBL_ITA	Company Table - ITA
COMPENSATION	EE Compensation Data
COMPNY_TBL_LANG	Related Lang-Company Tbl
COMP_PHONE_TBL	Company Phone Table
COUNTRYTBL_LANG	Country Related Language
COUNTRY_TBL	Countries
CURRCD_TBL_LANG	Currency Related Language
CURRENCY_CD_TBL	Currency Codes
DEPT_TBL	Departments
DEPT_TBL_LANG	Related Lang-Department Tbl
DISABILITY	Disability
DISABILITY_BRA	Disability Brazil
DISABILITY_CHE	Disability Table - CHE
DISABILITY_ESP	Disability Table - ESP
DISABILITY_FRA	Disability
DISABILITY_GER	GER Disabled EE info
DISABILITY_NLD	Disability Netherlands
DISABILITY_NZL	NZL Employee Disability Data
DIVERSITY	Diversity Data
DIVERS_ETHNIC	Ethnic Diversity
DIVERS_RELIGION	Religion Diversity - Asia Pac
EMAIL_ADDRESSES	Email Addresses
EMERGENCY_CNTCT	EE Emergency Contacts
EMERGENCY_PHONE	EE Emergency Cntct-Phone Nbr
EO_ADDRFMT_LANG	Address fields
EO_ADDR_FMT_TBL	Address fields
ETHNIC_GRP_LANG	Ethnic Group -Lang
ETHNIC_GRP_TBL	Ethnic Group Table
HOLIDAY_DATE	Holiday Dates
HOLIDAY_DTE_LNG	Holiday Dates RelLang
HOLIDAY_LANG	Holiday Table ID RelLang
HOLIDAY_TBL	Holiday Table ID
HP_EMPLT_TEACH
HR_EE_SNR_DATES	Job Labor Seniority Dates
JOB	EE Job History
JOBCD_COMP_RATE	Job Code Comp Rate Table
JOBCD_TRN_PROG	Job Codes
JOBCODE_LANG	Related Lang-Job Code Tbl
JOBCODE_TBL	Job Codes
JOB_AUS	Job Extension for AU
JOB_EARNS_DIST	EE Job Earnings Distribution
JOB_IND	Job table for India
JOB_JR	Job Junior
JOB_MIL	Military Related Job Fields
JOB_USF	Job Extension for USF
LOCATION_LANG	Related Lang-Location Tbl
LOCATION_TBL	Company Site Locations
MAJOR_TBL	College Major Subject Codes
MAJOR_TBL_LANG	Related Lang-Major Tbl
NAMES	Person Names
NAME_PREFIX_LNG	Related Lang - Name Prefix Tbl
NAME_PREFIX_TBL	Name Prefixes
NAME_SUFFIX_LNG	Related Lang - Name Suffix Tbl
NAME_SUFFIX_TBL	Name Suffix Table
NAME_TYPE_VW	Employee Name Types
NATIONALITY_GER	GER - Nationality record
NID_TYPE_LANG	Related Lng Tbl - NID_TYPE_TBL
NID_TYPE_TBL	National ID Type table
NM_ROYPREF_LNG	Related Lang for Roy Pref
NM_ROYPREF_TBL	Name Royal Pref Table
NM_ROYSUFF_LNG	Related Lang for Royal Suff
NM_ROYSUFF_TBL	Name Royal Suff Table
OPR_DEF_TBL_HR	Operator Defaults Table - HR
PERSON	PERSON record
PERSONAL_PHONE	Personal Data - Phone Numbers
PERSON_BRA	PERSON BRAZIL
PERSON_FRA	Person Subrecord - FRA
PERSON_SA	Higher Education Person Extension Record
PERS_DATA_BRA	Personal Data Effdt
PERS_DATA_CAN	Extension of PERSON for CAN
PERS_DATA_CHE	Extension of PERSON for CHE
PERS_DATA_DEU	Effdt Ext of PERSON for DEU
PERS_DATA_EFFDT	Effective Dated Personal Data
PERS_DATA_ESP	Extension of PERSON for ESP
PERS_DATA_FPS	Extension of PERSON for FPS
PERS_DATA_FRA	Extension of PERSON for FRA
PERS_DATA_IND	Personal Data - India
PERS_DATA_ITA	Extension of PERSON for ITA
PERS_DATA_JPN	Extension of PERSON for JPN
PERS_DATA_MEX	Personal Data Effdt Mexico
PERS_DATA_USA	Extension of PERSON for USA
PERS_DATA_USF	Ext of PERS_DATA_EFFDT for USF
PERS_HUKOU_CHN	PERSON CHINA
PERS_NID	PERS_NID Record
PERS_REGIST_BEL	Empl Registration Number - BEL
PERS_SMOKER	PERSON Smoker History
PERS_WRKLIF_CHN	PERSON WORK LIFE INFO CHINA
PERS_WRKLV_CHN	Working & Living Permit
PER_ORG_ASGN	Person Org Assignments
PER_ORG_ASG_BEL	PER_ORG_ASG - Exts for Belgium
PER_ORG_ASG_BRA	PER_ORG_ASG Ext for Brazil
PER_ORG_ASG_FA	PER_ORG_ASG_FA
PER_ORG_ASG_HP	Per Org Asgn for E&G
PER_ORG_ASG_JPN	PER_ORG_ASGN Ext for JPN
PER_ORG_ASG_LNG	Rel Lng for PER_ORG_ASGN
PER_ORG_ASG_MIL	Person Assignment - Military
PER_ORG_ASG_NLD	PER_ORG- Exts for Netherlands
PER_ORG_INST	Person Org Instance
PER_POI_SCRTY	Per Poi Security
PER_POI_SCR_DT	Per Poi Security
PER_POI_TRANS	Dflt Transaction Tbl for POIs
PER_POI_TRN_LNG	Lng Tbl for PER_POI_TRANS
PER_POI_TYPE	Person POI Types
PLACE_ORIG_CHE	Employee Place of Origin
POI_TYPE_LNG	POI Type Language
POI_TYPE_TBL	POI Type Table
REG_REGION_LANG	Regulatory Region
REG_REGION_TBL	Regulatory Region
SETID_LANG	TableSet IDs
SETID_TBL	TableSet IDs
SET_CNTRL_GROUP	TableSet Record Groups
SET_CNTRL_REC	TableSet Record Detail
SET_CNTRL_TBL	TableSet Controls
STATE_TBL	State Codes/Names w/in Country
STATE_TBL_LANG	State Names Related Language
SUPPORT_DOC_TBL	Visa Supporting Documents
SUPPRT_DOC_LANG	Related Lang-Visa Support Docs
TITLE_TBL	Title Table
TITLE_TBL_LNG	Rellang Title Table
US_SOC_TBL	US Standard Occupational Codes
US_SOC_TBL_LNG	US Standard Occupational Codes
VISA_PERMIT_SUP	Visa Supporting Docs Needed
VISA_PERMIT_TBL	Visa Requirements by Country
VISA_PERMT_LANG	Related Lang-Visa Requirements
VISA_PMT_DATA	EE/Dependent Visa Data
VISA_PMT_DA_LNG	EE/Dependent Visa Data
VISA_PMT_SUPPRT	EE/Depndnt Visa Support Docs

Determine Views Needed for Record Maps

The Utility generally performs data comparison using base tables. In some cases, using views instead of base tables, provides the ability to filter the data compared between the two databases. This is useful in cases where:

• The Last Updated Date/Time field values may differ and should be excluded from the compare;

• Record definitions are different between the databases;

• Record definitions include long varchar or image fields (as these are not supported by all comparison methods);

• Data rows may not be identical – by design – between the databases, such as for filtered populations.

The following views are delivered and can be used for setting Record Maps. These views have certain fields excluded to increase accurate data comparison – such as drop out hits on LASTUPDDTTM values.

• Some of these views are existing HCR interface views, and are used elsewhere in Campus Solutions.

• New views created for this purpose are named HCI_%.

The following list of base tables and views are referenced in delivered message definitions used with CS-to-HCM integrations.

Base Table Name	View Name
ACCOM_OPTION	HCI_ACCOMOPT_VW
ACCOM_OP_LNG	HCI_ACCOMOPL_VW
ACCOM_REQUEST	HCI_ACCOMREQ_VW
ADDRESSES	HCR_PER_ADDR_I
CITIZEN_PSSPRT	HCI_CIT_PASS_VW
CIT_PSSPRT_LANG	HCI_CITPSPTL_VW
CURRENCY_CD_TBL	HCI_CRNCY_CD_VW
CURRCD_TBL_LANG	HCI_CRNCY_CD_VL
DEPT_TBL	HCI_DEPT_VW
DEPT_TBL_LANG	HCI_DEPT_VWLANG
DISABILITY	HCI_DISABIL_VW
DISABILITY_FRA	HCI_DISAB_FR_VW
DISABILITY_NZL	HCI_DISAB_NZ_VW
JOB	HCI_JOB_VW HCI_JOB_V2
JOB_IND	HCI_JOB_IND_VW
JOBCODE_TBL	HCI_JOBCODE_VW HCI_JOBCODE_V2
JOBCODE_LANG	HCI_JOBCODE_VWL HCI_JOBCODE_VL2
JOB_JR	HCI_JOB_JR_VW
JOB_MIL	HCI_JOB_MIL_VW
LOCATION_TBL	HCI_LOCATION_VW
NAMES	HCR_PER_NAME_I
PERSON	HCR_PERSON_I
PERS_DATA_BRA	HCI_PDATA_BRAVW
PERS_DATA_EFFDT	HCR_PER_PDE_I
PERS_NID	HCR_PER_NID_I
PER_ORG_ASGN	HCI_PERORGASGNV
PER_ORG_ASG_LNG	HCI_PERORGASGNL
PER_ORG_INST	HCI_PERORGINSVW
PER_POI_SCRTY	HCI_PERPOISECVW
PER_POI_TYPE	HCI_PERPOITYPVW
POI_TYPE_LNG	HCI_POITYPLN_VW
POI_TYPE_TBL	HCI_POI_TYP_VW
SETID_LANG	HCI_SETID_LN_VW
SETID_TBL	HCI_SETID_VW
US_SOC_TBL	HCI_US_SOC_VW
US_SOC_TBL_LNG	HCI_US_SOCLN_VW
VISA_PERMIT_TBL	HCI_VISAPRMT_VW
VISA_PERMT_LANG	HCI_VISAPRMTLVW

Note:

The HCI_JOBCODE_VW and HCI_JOBCODE_VWL views pick up the currently effective row while the HCI_JOB_V2, HCI_JOBCODE_V2, and HCI_JOBCODE_VL2 views pick up all rows.