PeopleSoft Campus Solutions 9.0 Application Fundamentals PeopleBook

Preparing for Data Conversion

This chapter provides an overview of data conversion and discusses how to:

Perform data conversion.
Convert Recruiting and Admissions and Campus Community data.
Convert Financial Aid data.
Convert Student Records data.
Convert Student Financials data.
Convert Contributor Relations data.

Understanding Data Conversion

Conversion of data from an existing system is one of the most challenging and critical tasks that you accomplish while installing the Campus Solutions system. The conversion task takes preparation and planning, a programming effort, and robust testing.

Because of many factors—amount of data to convert, business practices, institutional schedule—much of the decisions as to when, how much, and even the method of converting historical data is ultimately up to you. The more you convert the better the new system becomes, but the more challenging the conversion task is. This section contains some best practice recommendations for converting data into Campus Solutions; however, the information presented here is not exhaustive.

Consider these tasks:

Explore Campus Solutions thoroughly before you convert any data.

Mapping the data to existing fields in PeopleSoft is a step that requires you to know the system in depth. Take advantage of documentation and training available.
Completely scope the project before starting an implementation and conversion.

An institution should develop a well-defined scope or implementation plan—including information such as the full extent of the conversion, how much data to convert, and the timing of each phase of data conversion—before starting the project. This plan should have the approval or buy-in of the institution's management team, the project team, and the partners or PeopleSoft consultants with whom you are working.
Test, test, test.

It is critical that you test the processes thoroughly in an environment where you can catch errors before you convert the data.
Plan a conversion schedule.

Because Campus Solutions is a fully integrated system with multiple applications, it is important to plan which applications are converted in a specific order. You may convert the data any way you want, but this chapter suggests how to maximize the system, and hopefully, minimize any challenges you may encounter during conversion.

There are as many ways to plan a conversion as there are universities. So it is a unique process based upon the legacy system, your needs, and how you do business. It is suggested that you:

Enter go-live dates (or a go-live term) for fall term, not mid-year.

If you do choose a spring term as the go-live term, you may run into some serious problems. For example, in the case of PeopleSoft Financial Aid, the financial aid cycle starts months before the fall term. The Financial Aid office awards financial aid starting in the March and April time frame for the fall semester. So, timing the implementation drastically impacts the amount of data that needs to be converted as well as the complexity of the conversion process. Work closely with partners, consultants, and account managers to determine the best go-live dates for the institution.
Convert names, addresses, and personal information first and as early as possible.

From there you can choose the order of the conversion of each application. It is suggested that you convert PeopleSoft Recruiting and Admissions and Financial Aid, then PeopleSoft Student Records and PeopleSoft Student Financials. If you are using PeopleSoft Human Resources applications in addition to Campus Solutions applications, it is critical that you coordinate the name, address, and personal information conversion process with the Human Resources conversion team, thereby reducing the amount of cleanup required and the duplicity and conflict of data when the data is converted.
Enter an effective date that is adequate for your own history.

This issue can particularly arise in Student Records.

Performing Data Conversion

This section provides overviews of the conversion process and referential integrity and discusses how to:

Map data for conversion.
Prepare data for conversion.
Verify converted data.
Use data dictionaries.
Use data load programs.
Keep systems in sync.
Estimate disk usage space.

See Also

PeopleTools PeopleBook: Data Management

Understanding the Conversion Process

Here is a review of the scope of the conversion process, so that you can gain an understanding of the phases involved:

Decide how much data to convert.

You may choose to convert a different amount of data for each application. For example, you may want to convert 10 years' worth of student records data, two years of admissions data or two years of financial aid data.
Map data to PeopleSoft data fields.

To effectively plan a conversion, dig into the old system and identify that one obscure field on that one obscure table and figure out how it maps into the new PeopleSoft system. In some cases, it is difficult, and at times impossible, to map all the codes and tables from the legacy system directly to PeopleSoft tables. In addition, think about the impact that these legacy fields or codes have on reports generated by the PeopleSoft system.
Export the data, using a Structured Query Report (SQR) or another method to interim tables.

Use the interim tables to review and clean up the data before moving it to the PeopleSoft system. Corruption of data often is the result of data entry errors. You do not want to move corrupt data from the old system to the new PeopleSoft system.
Import the data using an SQR.

Understanding Referential Integrity

An important topic to consider and test for as part of the conversion effort is referential integrity. You can convert a lot of data into the correct tables, but the overall system may not have the necessary connections. If you use the normal posting processes in the various applications to convert most of the legacy data, you should not have referential integrity problems because those processes contain many of the necessary referential integrity rules.

An example in Student Financials is third-party contract information. If you link a third party or a transaction to a contract, the contract needs to exist. However, if the conversion neglects to convert contracts, then a referential integrity problem can occur.

Mapping Data for Conversion

When you map data, you are identifying legacy system data and redefining it in PeopleSoft record definitions. This step can be performed while setting the conversion timeline; however, your data mapping efforts will be more focused if the conversion time line has been set.

Issues to consider when mapping data include defining the amount of historical data to be converted, code validation, workflow, reporting, and other processes. The amount of historical data converted may vary across PeopleSoft applications. The complexity of how the legacy system stores history may contribute to less history mapped into the PeopleSoft system. With code validation, valid codes for the institution may have changed over time, and this too must be considered in the conversion effort.

To map data for conversion:

Define each legacy data element.
Determine if the legacy data element is pertinent to the implementation scope.
Map the legacy data element to the Campus Solutions data element.
If the legacy data element cannot be mapped directly within your Campus Solutions product line, determine if the legacy data should be tracked any more or if it could be tracked in another PeopleSoft product line, such as PeopleSoft Human Resources Management.
Reformat the legacy data to fit, for example, the Campus Solutions field size, format or data type.

If certain legacy data cannot be reformatted, indicate those as gaps in the gap analysis.

Spreadsheets are helpful tools when mapping data. At a minimum, these spreadsheets should include the legacy system data source (if data comes from more than one source), the legacy system data element and data type, the specific Campus Solutions or Contributor Relations record, and the specific field and data type in that record. Optional description fields might include related prompt tables, conversion translation values, and legacy system data position.

Another option is to build a mini-application using PeopleTools to map data. You can build Structured Query Language (SQL) views to link various PeopleTools tables together, such as PSRECFIELD and PSRECDEFN, which creates a robust method of identifying field attributes. You can create special reports to identify key fields, required fields, or legacy system data not yet mapped.

Preparing Data for Conversion

After you have identified the legacy data to be converted, you must prepare it for conversion. For example, when coming from the legacy system, data may be packed in EBCDIC format. These fields should be reformatted into a conversion-ready unpacked flat file in ASCII standard format. Usually, a COBOL program is written and run on the database server to create a file placed on the legacy system.

Conversion-ready data does not have to reside in a single file. The program may be designed to create multiple files during the formatting process, with each file only containing data pertinent to a single or related set of Campus Solutions or Contributor Relations destination tables. Smaller file sizes are more manageable and result in faster conversion processing. Also, the data preparation program itself may be broken into multiple programs based on the destination table.

One of the largest time-consuming tasks of the conversion project can be data cleanup. One way to minimize this task is to clean up the data before importing it into the PeopleSoft system. This can be done by using the data preparation and extract program. This program contains more logic to ensure data validity and therefore takes a longer time to program and test, but the program reduces processing time during the data cleanup.

Another option to reduce the data cleanup effort for the conversion team is to create reports for review by appropriate functional experts, technical experts, employees, and managers. These reports can be used for data verification and correction before the data is entered in the database. Correct invalid legacy data before preparing for conversion.

Not all data conversion must occur through background programs. You may determine that online application data entry is necessary for prompt tables and other required PeopleSoft tables for which there was no legacy data.

Verifying Converted Data

After data has been converted to the PeopleSoft system, you must verify the data and check its integrity. Two methods are online verification and batch verification. Data verification must be performed to clean up invalid data that may not have been keyed, required, or validated in the preparation or conversion processes. This process can also identify and clean up duplicates in the legacy system before you finish converting and mapping the data to the new PeopleSoft tables.

You can write batch programs or SQL scripts to check for parent-child relationships and other validation checking. Writing the batch programs requires more time commitment but can quickly check more records.

Online data verification is less exact than the background process, because records are randomly accessed and require a longer amount of time. However, online data entry can be used to enter nonrequired data or supplemental data that was not available from the legacy system.

Using Data Dictionaries

PeopleSoft does not issue a data dictionary as part of its documentation. PeopleTools provides the ability for online documentation of page and field relationships. The development team documents its material as much as is feasible online in PeopleTools. You can write an SQR that provides all the data dictionary information that you need.

Using Data Load Programs

After data preparation, design conversion programs that extract the appropriate file layout, to transfer the legacy data to Campus Solutions tables.

Input Format

Often the input format for the conversion program is the flat file created by the data extract program on the legacy system and transferred—by using File Transfer Protocol—to the database server. Consider loading the data into temporary tables within the PeopleSoft system instead of directly into the "live" tables. By performing this task, you can use the tools already available to you to both clean up and properly convert data. The temporary tables would then be the input for the conversion program.

Conversion Program and Program Language

The conversion program transfers the legacy data from the input format to Campus Solutions tables, including reformatting, validating, and prompt table translating not encompassed in the data preparation programs. The conversion program must include defaults for Campus Solutions record keys and required fields whenever legacy data has no data map.

The two most commonly used languages for conversions are COBOL and SQR. Another option is a specific database management system (DBMS) import utility, if one exists—such as the Oracle SQL Loader. Determinants for making this decision include your skill set. For example, if you already know COBOL, there is no associated learning curve downtime, or increased resources if you use COBOL. Regardless of your skill set, consider if it would be advantageous to learn a language that is likely be used in the future for Campus Solutions modifications, conversion file size, level of translations and edits, and DBMS capability.

COBOL	Has a more robust debugger, can process a larger number of records faster, and is more widely recognized in the industry.
SQR	Is more commonly used in reporting and minor modifications. In most cases, SQR code can be written in less time than COBOL code.
DBMS Import Utility	Requires the least conversion effort but is limited to no application-specific edits or translations.

Table Loading Sequence

Conversion programming may include a single program that incorporates all pertinent tables and their sequence or multiple programs that incorporate single tables or a few related tables. Multiple programs can run simultaneously—increasing efficiency and enabling the allocation of programming responsibility.

Errors and Exception Handling

Regardless of the number of conversion programs, there must be adequate and standard error and exception handling. Reports of database action failures, validation logic failures, and pertinent details help ensure a quality and thorough conversion.

Totals Controls

In addition to errors and exceptions, totals controls also help in quality control. Totals controls include the number of records processed on both the source and destination sides, and dollar tallies whenever possible are pertinent for Financial Aid and Student Financials applications.

Required Fields

You can find out what fields are required for any table or page by searching PeopleSoft Application Designer in PeopleTools. You can also write an SQR to list all required data elements from tables that you want to use in the conversion. It is important that you also read and understand the PeopleCode. There are PeopleCode restrictions within records that can mean that in certain circumstances a field is required or a default value is required. Without understanding the PeopleCode, incorrect data could be entered.

Warning! If a field is not required, do not assume that you do not need to convert data into that field. It is recommended that when in doubt, you review the PeopleCode to determine whether you need data or default values for a particular field.

Note. Refer to individual discussions regarding each application in this chapter for specific data conversion and data loading tips for applications within Campus Solutions.

Keeping Systems in Sync

Depending on the implementation plan, there are three scenarios for moving data processing from a legacy system to a PeopleSoft system. The first scenario is to cut over everything at once. When all students are fully implemented, you perform a cutover to the PeopleSoft system. Until that point, the legacy system is fully operational. No system synchronization is required.

The second scenario is the mutually exclusive implementation approach. For example, when a student is converted to an application in Campus Solutions, such as Financial Aid, the student is officially taken off of the legacy system's processing in that application. All of the student's other application processing—such as Student Records—is performed on the legacy system. There are two production systems. This approach increases the complexity of joining two data sources for interfaces and reports.

The third scenario is the parallel system approach. An alternative to having two production systems is to test each Campus Solutions application as necessary, but completely delete the application database and rerun a full conversion with the most up-to-date data from the legacy system until full cutover is achieved after full testing. This approach implies that the legacy system is in full use until cutover, but no new programs must be written—for example, the conversion program is reused and all reports continue to be produced from the legacy system. A variance of this approach is to write new data refresh programs that periodically update the various applications with student information entered in the legacy system. This variance provides for more up-to-date information for more accurate reporting and testing, but also implies the additional effort to program the refresh routine. Care must be taken in the refresh routine design to ensure that data in the PeopleSoft system is not duplicated or overlaid by the legacy data.

Estimating Disk Usage Space

For Campus Solutions, PeopleSoft has done some preliminary research on the sizing of a production system. PeopleSoft realizes that the theoretical amount of disk space required by an application is different than the actual amount of disk space required by a database. The difference is due to comparing bytes with database blocks. For instance, if a particular table's average row length is 25 bytes and there are 1,000 rows in the table, theoretically the required amount of space is 25,000. However, if the database block size is 4K (4096), the number of database blocks required to hold the data is seven blocks or 28,673. Therefore, the actual amount of disk space required is greater than the theoretical. With this in mind and realizing that each installation may require a different block size, PeopleSoft states the size in theoretical terms.

For 1,000 student prospects, the amount of space needed for tables and indexes would be 40 megabytes (MB).

For enrollment, PeopleSoft needs to work with a specific, albeit theoretical, scenario. PeopleSoft's scenario was to enroll 1,000 undergraduate students over three terms into four classes per term. In the first term, there are no prerequisites to enroll in any of classes; the second term has one prerequisite for each of the classes; the third term has two prerequisites for each of the classes. During enrollment, the students have no time conflicts and there is no lack of facilities (rooms or seats). The amount of disk space excludes description information stored in long text fields. For 1,000 student enrollments, the amount of space needed for tables and indexes would be 20 MB.

Converting Recruiting and Admissions and Campus Community Data

This section provides an overview of conversion of recruiting and admissions and campus community data and discusses how to populate tables for recruiting and admissions and campus community data.

Understanding Conversion of Recruiting and Admissions and Campus Community Data

You should convert recruiting and admissions data first along with relevant Campus Community data such as personal data—names, addresses, or phone numbers.

It is up to you how much of this data you convert—for instance, how many addresses you convert for one individual—and how much history you take into consideration. Of course, if the student records team is converting 10 years of student records, make sure that there are 10 years of personal data elements in the system.

You should convert up to two years of recruiting and admissions data for your institution. Again, this may vary depending upon your practices and your needs. Regardless of how much history you do convert, it is important that you keep the prospect stacks of data and the Recruiting and Admissions stacks of data consistent. You do not want to end up with a year of recruiting and admissions data without the prospect data for the same year. This also ensures that the data required for institutional research reporting—for instance, cohort reporting and statistical reporting—remains consistent.

Populating Tables for Recruiting and Admissions and Campus Community Data

Aside from address and other information, there are some tables that are precursors to all application conversions in Campus Solutions. These tables represent the minimum to be converted.

Academic Organization Tables

Populate these tables in this order for academic organizations:

INSTITUTION_TBL
CAMPUS_TBL
ACAD_CAR_TBL
ACAD_ORG_TBL
SUBJECT_TBL
ACAD_PROG_TBL
ACAD_PLAN_TBL
ACAD_SUBPLAN_TBL

Level and Load and Term and Session Tables

Populate these tables in this order for level and load and term and sessions:

LEVEL_LD_RULE_TBL
ACAD_LEVEL_TBL
ACAD_LOAD_TBL
TERM_TBL
TERM_VAL_TBL
SESSION_TBL
ACAD_CAL_TBL
ACAD_CALTRM_TBL
ACAD_CALSES_TBL

In addition to these tables, you must convert these tables as early in the process as possible in the sequence. Use the name and address load routines delivered with the system, to load data:

PERSON.
PERS_DATA_EFFDT.
PER_POI_TYPE.
PERSONAL_DATA.
DISABILITY: required for PERS_DATA_EFFDT and PERSONAL_DATA to function properly.
DIVERSITY: required for PERS_DATA_EFFDT and PERSONAL_DATA to function properly.
NAMES.
ADDRESSES: to display and convert any address information, including those on PERSONAL_DATA.
ADM_PRSPCT_CAR.
STDNT_CAREER.
ADM_APPL_DATA.
ADM_APPL_PROG.
GENL_MATERIALS: to track materials already received for applications.
EXT_ORG_TBL.

Converting Financial Aid Data

This section provides an overview of conversion of financial aid data and discusses how to populate tables for financial aid.

Understanding Conversion of Financial Aid Data

This section lists prerequisites and discusses conversion of financial aid data.

Prerequisites

Before you can convert financial aid data, personal data for each financial aid record must already be converted. In addition, financial aid data should be converted prior to student financials data, because posting of financial aid in Student Financials is dependent upon the disbursements in Financial Aid.

You must define item types before you begin conversion of financial aid data. You must decide whether financial aid or student financials has ownership of the PeopleSoft Item Types table.

Conversion of Financial Aid Data

You should convert up to two years of financial aid data for schools, and as much aggregate data as possible, because it has lifetime loan limit information. Ultimately, it is up to you how much data you convert. You may decide to keep the legacy system up until fall of the conversion year to close out Pell Payment and FISAP reporting.

Note. Please refer to the Department of Education's requirements for maintaining financial aid records to assist you in deciding how much legacy data to convert to the PeopleSoft system.

See Also

Converting Student Financials Data

Populating Tables for Financial Aid

At a minimum for Financial Aid, convert the aggregate data for these tables:

STDNT_AGGR_LIFE
STDNT_AGGR_ALL
STDNT_AGGR_SCHL

You may want to convert additional data depending on what you have in the current system:

Automatic packaging.
ISIR data.
Student budget information.
Years of award activity.
Loan history.
Work-study history.
Checklists for documents already received.

Converting Student Records Data

You enter data in Student Records tables by academic program. How much history data you convert is up to you.

This section lists prerequisites and discusses how to:

Populate tables for student records.
Convert enrollment data.

Prerequisites

Some of the tables, as noted in the Converting Recruiting and Admissions and Campus Community Data section, must be loaded before you load the student records data.

Populating Tables for Student Records

At a minimum for Student Records, set up these tables in the sequence they are listed.

For the course catalog and schedule of classes, there is PeopleCode behind the COURSE_ID field to increment a new course ID by 1. To mimic what this PeopleCode does, the conversion program needs to do the +1 logic. You can manually set the next available course ID in the installation table for Campus Solutions. You should update the last course ID assigned in the Installation table, so that you do not have collisions between the course IDs assigned by the conversion and course IDs manually created later.

In addition to the conversion program you write to create the catalog, you should set the Status field on the Catalog Data page to Active so that the course is active as soon as you run the conversion program to create the record.

Course Catalog Tables

Populate these tables for the course catalog:

CRSE_CATALOG
CRSE_COMPONENT
CRSE_OFFER

Schedule of Classes Tables

Populate these tables for the schedule of classes:

CLASS_TBL
CLASS_ASSOC
CLASS_COMPONENT

Student Careers and Plans

Populate these tables for student careers and plans:

STDNT_CAREER
STDNT_CAR_SEQ
ACAD_PROG
ACAD_PLAN

Student Degrees

Populate these tables for student degrees:

DEGREE_TBL: populate this table manually.
ACAD_DEGREE.
ACAD_DEGR_HON.
ACAD_DEGR_PLAN.
ACAD_DEGR_SPLN.

Grades

Populate these tables manually for grades:

GRADING_SCHEME_TBL
GRADE_BASIS_TBL
GRADE_TBL

Student Term Data

Populate these tables for student term data:

STDNT_CAR_TERM
ENRL_REQ_HEADER
ENRL_REQ_DETAIL

See Also

Selecting General Installation Options

Setting Up the Course Catalog

Converting Enrollment Data

There are three options to select for converting student enrollment data. Note that when you convert enrollment data you must create enrollment request input transactions and engage the enrollment COBOL engine to actually post the enrollments. This is the only way that the statistics on the STDNT_CAR_TERM table are automatically calculated.

Options for converting enrollment data are:

Convert the data as normal student enrollment data.

This is the recommended option. The course catalog and schedule of classes must be converted first, and you must have data for these as far back as you want to have enrollment data. The conversion program must create class association groups for the offerings when you convert the schedule of classes. In addition, a student career term must be present for every past term that you intend to convert. For each class section that you convert historically, you need an entry in the CLASS_TBL. This does not mean that you must convert all of the class sections, just the sections that are graded. Even for graded sections the amount of data needed is minimal. For example, you may choose not to convert meeting times and instructors for classes. You may want to have the conversion programs roll all enrollments for a course in a term into one section. For example, if English 101 had 51 sections in fall 1996, you could create a Section 1 for that term and convert all 51 sections into that Section 1.
Convert enrollment data as internal transfer credit or other credit.

This option is necessary if you have only your course catalog but not your class schedule for as far back as you want to convert data. This method can make transcript printing a challenge, because past credits prior to conversion appear as transfer credit. Having split transcripts may also be an option, if issuing two transcripts for a student is acceptable—one from the old system for prior work and one from the new Campus Solutions system for all work after you bring the system up. In this case, you would still want to convert the enrollment data by summary transfer credit, so that the prior system's term academic statistics roll into Campus Solutions.
Use the Historical Course Enrollment page (Manage Student Records, Manage Academic Records, Use, Historical Course Enrollment) and convert statistics in summary by using the transfer credit engine.

If you cannot go through the effort to convert prior enrollment history, or the data is not available to you electronically to reconstruct prior course catalog and schedule of classes data, use the Historical Course Enrollment page. Create an SQR or other program to directly load enrollment data onto the Historical Course Enrollment page, and manually clean up this data. If you select this option, convert summary statistics by using the transfer credit COBOL process.

See Also

Creating Historical Enrollment Records

Converting Student Financials Data

This section lists prerequisites and discusses how to populate tables for student financials.

Prerequisites

Student Financials receives data from many of the other Campus Solutions applications. For this reason, you must set up parts of other applications before you can convert the student financials information.

You must convert personal data tables and external organizations data before you convert student financials data.

Populating Tables for Student Financials

This list presents information about populating the tables related to Student Financials:

General Ledger (GL).

Make sure that the GL information is set up on the Item Type table. You can convert the data and run a GL interface to set the GL fields, or you can convert the fields as having already been sent to the GL.
Financial Aid.

Another important consideration in the sequencing of the student financials conversion is processing financial aid. If you are thinking about going live with financial aid and its disbursements at mid-year, consider the effect on the coordination of disbursing financial aid and the balance of the student's account for the year. For example, the disbursed fields in Financial Aid should be in sync with the amounts in Student Financials, if automated disbursement is to take place for that particular term. In addition, anticipated aid is used in numerous processes in Student Financials. Therefore, an important consideration is the conversion of anticipated aid for current processing cycles, as conversion or lack of conversion affects student financials processes. You should ensure that conversion is for fall term, rather than for mid-year, for these reasons.
Payment Plans.

Third-party processing and payment plans are also an important conversion issue for student financials. To take advantage of Student Financials processing for payment plans and third-party processing, ensure that the conversion of this data occurs and that separate accounts are created for the various contracts that you have established for student sponsorship or extended payment options.
Posting.

The major focus of the conversion effort for student financials is getting all of the old account and transaction information into the new system. The primary vehicle for converting the information is posting. It is possible to directly update the processing tables with data from the legacy system. However, it is much safer and cleaner to use the posting process to convert the legacy data. This is not to say that you do not need to update the information after posting. Using the posting process gives you cleaner data and provides a base upon which you can edit the information.

Create groups and use the group posting process to get the information on the system. Update other information as needed. The group posting process enables you to break down and track student populations and time categories into meaningful groups that you can edit and correct. Possible group scenarios might be academic—that is, for example, convert all medical school students, dental students, veterinary students. Another possible categorization is using time-based groups. You may want to break the student population into groups by term.

However you break down the groups, plan the data mapping carefully between the group posting tables and the current legacy data. Run several trials of posting groups and test the system to see if it processes correctly from adjusting tuition, adjusting financial aid disbursements, and producing a bill.
Tuition Charges.

If you are planning to use the tuition calculation process to convert tuition charges for prior terms, you have two options. You can either convert and post all the information from prior terms, or you can skip the tuition charges and use the tuition calculation process for prior terms as the method for converting the data. The former option does not require the conversion of academic data from prior terms; the latter does require that correct academic data be converted prior to the student financial data.

Converting Contributor Relations Data

This section lists prerequisites and discusses how to:

Populate tables for Contributor Relations.
Create accumulated records for gift and pledge inquiry.

Prerequisites

Contributor Relations uses data from some of the other Campus Solutions applications. For this reason, you must set up Student Financials item types and related general ledger information as well as Human Resources departments before you can convert some of the contributor relations information.

Populating Tables for Contributor Relations

At a minimum for Contributor Relations, set up these tables.

Organization Structure Tables

Populate these tables for organization structure:

INSTITUTION_TBL
CAMPUS_TBL
DEPARTMENT_TBL
AV_BUS_UNIT_TBL

Campus Community and Constituent Tables

Populate these tables for Campus Community and constituent data:

PERSONAL_DATA.
PERS_DATA_EFFDT.
PERS_NAME_TYPE.
DISABILITY: required for PERS_DATA_EFFDT and PERSONAL_DATA to function properly.
DIVERSITY: required for PERS_DATA_EFFDT and PERSONAL_DATA to function properly.
NAMES.
ADDRESS_TYPE.
ADDRESSES: to display or convert any address information, including that on PERSONAL_DATA.
EXT_ORG_TBL.
AV_CNST_TYP_TBL.
AV_LEGACY_DEG: to convert alumni degree records if you do not want to create all previously existing academic structures in the student records tables.

Otherwise, all of the Student Careers and Plans and Student Degrees tables listed in the Converting Student Records Data section are required for tracking academic data.

Note. The EXT_ORG_ID field should be left blank for PeopleSoft data. This field is used to store data for external organizations. When dealing with an organization, you would leave EMPLID blank and load the EXT_ORG_ID for the organization.

Gift Tables

Populate these tables for gifts:

AV_SESSION_TBL: required.
AV_BTCH_TOT: required.
AV_GIFT_DTL: required.
AV_DESIGNATION: required.
AV_RECOGNITION: required.
AV_RCG_DES: required.
AV_MTCH_GIFT: optional.
AV_TRIBUTE_DTL: optional (In Honor Of or In Memory Of).
AV_TRIBUTE_NTFY: optional.

Pledge Tables

Populate these tables for pledge data:

AV_SESSION_TBL: required.
AV_BTCH_TOT: required.
AV_PLEDGE_DTL: required.
AV_DESIGNATION: required.
AV_RECOGNITION: required.
AV_RCG_DES: required.
AV_PLDG_SCHD: required for active pledges but optional for complete pledges.
AV_TRIBUTE_DTL: optional (In Honor Of or In Memory Of).
AV_TRIBUTE_NTFY: optional.
AV_SESSION_TBL.

Populate all key fields, plus all other fields you can populate. The SESS_STATUS = O for trial runs but should ultimately be set to P for posted. The user ID is important here as it dictates security. (They can all have the same user ID, such as PS.) The ACKN_FLG field should be set to Y.
AV_GIFT_DTL.

Populate all key fields, plus all other fields you can populate. The FISCAL_YEAR (CCYY) should be populated. The SESSION_NO should start with one and increment by one for every group of 100 records, if the organization chooses to abide by the recommendation of 100 records per session. The SEQ_NO should be 1 for historical data, provided these are all just gifts and not adjustments. The sequence is one for the original gift and increments (by two within the AV_GIFT_DTL table—for example 1, 3, and 5), with each adjustment made to the original gift. The SEQ_NO in the AV_ADJ_GIFT_DTL table increments with even numbers by two to indicate the offsetting record to the related AV_GIFT_DTL record. The ACKN_FLG field should be set to Y.

Note. If you don't want historical gifts to get an acknowledgement, then set the AV_SESSION_TBL. ACKN_FLG field to Y.
AV_DESIGNATION.

Populate all key fields, plus all other fields you can populate. This is not a setup table, but an allocation of the gift detail record to one or more designations.

Note. Amounts can default to the gift amount if there is only one designation or recognition per gift. If there is more than one designation or recognition per gift, then the amount fields need to be populated with actual amounts or percentages.
AV_RECOGNITION.

Populate all key fields, plus all other fields that you can populate. RCG_PCT_AMT and RCG_APPLIED should be mapped to gift amount (if there is only one recognition per gift). The RECOGNITION_TYPE = H for hard credit. For soft credit, there are different types (such as vehicle credit) that are defined by the institution. No soft credit Values are: delivered. RECOGNITION_TYPE is a setup value, with H being a delivered, required value.
AV_RCG_DES.

Populate all key fields, plus all other fields that you can populate. RCG_DES_AMT and CHARITABLE_AMT should be mapped to gift amount (if there is only one recognition or designation per gift). For Soft Credit CHARITABLE_AMT = 0.

Note. For converted active pledges to have pledge payments made against them, the AV_SESSION_TBL.SESS_STATUS field for the pledge session must be set to P.

Creating Accumulated Records for Gift and Pledge Inquiry

In Contributor Relations, the system runs Giving Profile and Commitment Summary online reports based on summary accumulator records rather than detailed transaction records. For the proper information to appear on these reports, you must initialize the summary accumulator records for all constituents and all gifts and pledges in the system. Contributor Relations delivers a Constituent Accumulator Initialization Application Engine process (AV_ACC) to perform this task. To access the process, select Set Up SACR, Product Related, Contributor Relations, Install Contributor Relations, Initialize CR, Accumulator Initialization. When initialized, incremental accumulations take place for affected constituents during the Constituent Accumulator process. You can run this process either by itself or along with the GL Interface job.

The Constituent Accumulator Initialization process is a resource-intensive process that requires an extended background processing window. The process deletes and recalculates all data from the accumulator records (PS_AV_CNST_ACC, PS_AV_CNST_ACC_DES, and PS_AV_CNST_ACC_FY). If all transactions contained within in-process sessions are first completed (their session status changes to Posted), do not select the Search for unposted Pledge Payments and Search for unposted Matching Gifts options. This improves the performance of the initialization process.

Note. If you do not complete the initialization process, the system does not complete the information displayed online on the Giving Profile and Commitment Summary pages.

If additional groups of constituents are required to allow the process to complete within the particular background processing window, the records in the PS_AV_ACCUM_INIT table can be modified to include more, smaller groups. Remember, however, that additional occurrences of the temporary tables used by the AV_ACC Application Engine process are required if you run more than the delivered five processes at any one time.

See Also

Setting User Defaults

PeopleTools PeopleBook: PeopleSoft Application Engine