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.