| Data element |
Definition |
COMMON_ID
Indexed - Yes
Format - VARCHAR2
Nullable - N | Created by Penn for data warehouse reporting, this is the unique identifier for both individuals and organizations. It is the concatenation of the EMPLID and EXT_ORG_ID fields. For individuals, it comes from EMPLID and for orgs, it comes from EXT_ORG_ID. |
BUSINESS_UNIT
Indexed - Yes
Format - VARCHAR2
Nullable - N | This is the business unit code of the department that entrered the gift -- most often UPENN. |
SESSION_NO
Indexed - Yes
Format - VARCHAR2
Nullable - N | This is the unique identifier of a batch of gifts or pledges -- used as part of unique key to identify a specific gift transaction. |
GIFT_NO
Indexed - Yes
Format - VARCHAR2
Nullable - N | This value is used to identify a specific gift or pledge. Although not unique, it is part of the key. Gift_no's starting with 'X','G', or '3' came from the legacy gifts system, while those starting with '4' are from Atlas. |
EMPLID
Indexed - Yes
Format - VARCHAR2
Nullable - N | Unique identifier for individual (non-org) records. |
EXT_ORG_ID
Indexed - Yes
Format - VARCHAR2
Nullable - N | Unique identifier for org (non-individual) records |
RECOGNITION_TYPE
Indexed - Yes
Format - VARCHAR2
Nullable - N | Flags gifts as either 'H' (Hard $ - the entity that gave the gift), 'S' (Soft $ - an entity given credit for the donation, without having given it explicitly) or 'C' (Challenge gift). |
DESIGNATION
Indexed - Yes
Format - VARCHAR2
Nullable - N | This is the identifier of the project, area, or fund receiving gifts. This is often the 6-digit fund segment from the GL, but there are may cases where it is not. |
INTV_CD
Indexed - Yes
Format - VARCHAR2
Nullable - N | Campaign initiative (campaign code) |
SA_ID_TYPE
Indexed - No
Format - VARCHAR2
Nullable - N | "Donor id type ("P" for individual, "O" for org) |
AV_DES_BU
Indexed - No
Format - VARCHAR2
Nullable - N | AV_DES_BU indicates the unit that owns the designation of the particular gift or pledge. It is linked to the SETID field in the various designation/account tables. |
SEQ_NO
Indexed - No
Format - NUMBER
Nullable - Y | Part of the unique identifier of a gift or pledge, this number increases with each the entry and adjustment of a gift or pledge. The first entry is always 1. Subsequent adjustments increase two, one increment for the reversal of the original and more for the new entry. |
RCG_DES_AMT
Indexed - No
Format - NUMBER
Nullable - Y | Amount of the gift or pledge as it appears in the RCG_DES table. GIFT_TYPE / AV_SESS_TYPE determines what type of transaction is shown in this field (ex. if AV_SESS_TYPE = 'PL' then the RCG_DES_AMT is a pledge). |
CHARITABLE_AMT
Indexed - No
Format - NUMBER
Nullable - Y | Amount of gift minus the value of services attached to gift. This is the tax deductible amount of the gift. |
COMMITMENTS
Indexed - No
Format - NUMBER
Nullable - Y | "A combination of pledges and outright gifts (no pledge payments). This field is commonly used in the reports office to display the "subscription"amount. |
RECEIPTS
Indexed - No
Format - NUMBER
Nullable - Y | A combination of pledge payments and outright gifts (no pledges). |
AV_PLDG_STATUS
Indexed - No
Format - VARCHAR2
Nullable - Y | Status of Pledge transaction: 'A' = Active, 'C' = Closed, 'W' = Written Off. |
CURRENCY_CD
Indexed - No
Format - VARCHAR2
Nullable - N | Country code for currency (most often USD) |
GIFT_DT
Indexed - Yes
Format - DATE
Nullable - Y | This is the date of transfer of a gift or pledge to the University. |
GIFT_DT_FISCAL_YEAR
Indexed - No
Format - NUMBER
Nullable - N | The fiscal year of the transaction, based upon the GIFT_DT field. For example, if the GIFT_DT = 06/30/2007 then the GIFT_DT_FISCAL_YEAR = 2007. |
DATA_ENTRY_DT
Indexed - No
Format - DATE
Nullable - Y | For historical gift reporting, this is the field to use to determine when a gift/pledge was entered into ATLAS (GL_POST_DT is the best field to use for gift transactions entry date from the R4 conversion -4/2007-onward). |
REFERENCE_DT
Indexed - No
Format - DATE
Nullable - Y | The original date of a matching gift soft credited to an individual. This was the date we have traditionally used to generate giving frequencies. So, if you have a reporting need to remove matching gifts that are coming in on one fiscal year, but refer t |
CNST_TYPE
Indexed - No
Format - NUMBER
Nullable - N | The constituent type (ex. '1' = Alumni) of the donor at the time the gift/pledge was made. |
ALUMNUS_TYPE
Indexed - No
Format - VARCHAR2
Nullable - N | G = Graduate, N = Non-Degree, U = Undergraduate. Refers to donor - sparsely populated. |
APPEAL_CODE
Indexed - No
Format - VARCHAR2
Nullable - N | Code used by fundraisers to indicate the particular appeal that caused the gift or pledge. |
AV_CASE_TYPE
Indexed - No
Format - VARCHAR2
Nullable - N | CASE (Council for the Advancement and Support of Education) type code (see WHSE_AV_CASE_TYPE_LKUP for values: ex. 'C' = 'Current Unrestricted') |
AV_CASE_PRPS
Indexed - No
Format - VARCHAR2
Nullable - N | CASE (Council for Advancement and Support of Education) purpose code (see WHSE_AV_CASE_PRPS_LKUP for values: ex. 'A' = 'Financial Aid') |
AV_SESS_TYPE
Indexed - No
Format - VARCHAR2
Nullable - N | Type of gift entry session (for ex. 'PL' = a pledge gift entry session). |
CAE_GIFT_TYPE
Indexed - No
Format - VARCHAR2
Nullable - N | Not currently used by Penn |
CAE_RE_FLAG
Indexed - No
Format - VARCHAR2
Nullable - N | Not currently used by Penn |
TENDER_TYPE
Indexed - No
Format - VARCHAR2
Nullable - Y | This inidicates the method of payment/tender of the gift or payment, eg. Cash or Credit Card. This is blank for pledges. |
TENDER_TYPE_ROLLUP
Indexed - No
Format - VARCHAR2
Nullable - Y | "Higher level version of tender type (for ex., instead of multiple varieties of GIKs, the rollup would include a single GIK "bucket"). This is blank for pledges. |
GIFT_TYPE
Indexed - No
Format - VARCHAR2
Nullable - N | This indicates the particular type of gift transaction, eg. Gift, pledge payment, or grant. It is blank for pledges. |
WHSE_GIFT_TYPE
Indexed - No
Format - VARCHAR2
Nullable - Y | "Code for type of gift transaction. This field includes the "pledge" gift type, and is therefore more versatile than the GIFT_TYPE field in reporting. |
FEED_FLAG
Indexed - No
Format - VARCHAR2
Nullable - Y | If FEED_FLAG = 'Y', then the gift transaction feeds the General Ledger (GL). For annual giving pledge reminders, set the FEED_FLAG <> 'Y' to remove multi-year pledges. |
CREDIT_CARD_TYPE
Indexed - No
Format - VARCHAR2
Nullable - Y | AE' = American Express, 'DI' = Discover, 'VI' = Visa/Mastercard. Populated when gift transaction is paid by credit card. |
GL_FEED_TECH_KEY
Indexed - No
Format - VARCHAR2
Nullable - Y | Future Use |
PLEDGE_GIFT_NO
Indexed - No
Format - VARCHAR2
Nullable - N | Gift number for pledge transactions. |
PLEDGE_EFFDT
Indexed - No
Format - DATE
Nullable - Y | Gift date of the pledge transaction itself or the pledge upon which a payment is made. |
PLEDGE_FY
Indexed - No
Format - NUMBER
Nullable - Y | Not populated |
HAS_BEEN_ADJ
Indexed - No
Format - VARCHAR2
Nullable - Y | Lets you know if the row you're looking at was previously adjusted in the database. If this flag = 'Y', then the gift/pledge transaction has been adjusted. |
ORIGINAL_EFFDT
Indexed - No
Format - DATE
Nullable - Y | Gift date of the original transaction when there's been an adjustment. |
ORIGINAL_FY
Indexed - No
Format - NUMBER
Nullable - Y | Fiscal year of the ORIGINAL_EFFDT. |
ORIGINAL_DATA_ENTRY_DT
Indexed - No
Format - DATE
Nullable - Y | "When ORIGINAL_DATA_ENTRY_DT = DATA_ENTRY_DT, the gift/pledge transaction can be considered a "new" gift (criteria for new gift report: GL_POST_DT = TODAY - 1 AND ORIGINAL_DATE_ENTRY_DT = DATA_ENTRY_DT) |
ORIGINAL_DATA_ENTRY_DT_FY
Indexed - No
Format - NUMBER
Nullable - Y | Calculated fiscal year based on ORIGINAL_DATA_ENTRY_DT. |
TT_AFFILIATED_GIVING
Indexed - No
Format - VARCHAR2
Nullable - Y | Tribute type = Affiliated Giving in PS_AV_TRIBUTE_DTL. Field = 'Y' if true. |
TT_GIFT_IN_PROGRESS
Indexed - No
Format - VARCHAR2
Nullable - Y | Tribute type = Gift in Progress in PS_AV_TRIBUTE_DTL. Field = 'Y' if true. |
TT_ULTIMATE_DESIGNATION
Indexed - No
Format - VARCHAR2
Nullable - Y | Taken from the tribute table, displays the designation to be created. Used for receipts. |
TT_LEAD_TRUST
Indexed - No
Format - VARCHAR2
Nullable - Y | Tribute type = Lead Trust in PS_AV_TRIBUTE_DTL. Field = 'Y' if true. |
TT_NONBIND_COMMIT
Indexed - No
Format - VARCHAR2
Nullable - Y | Tribute type = Non-Binding Commitment in PS_AV_TRIBUTE_DTL. Field = 'Y' if true. |
TT_SUPPRESS_RECEIPT
Indexed - No
Format - VARCHAR2
Nullable - Y | Tribute type = Suppress Receipt in PS_AV_TRIBUTE_DTL. Field = 'Y' if true. |
TT_DO_NOT_BILL
Indexed - No
Format - VARCHAR2
Nullable - Y | Tribute type = Do Not Bill in PS_AV_TRIBUTE_DTL. Field = 'Y' if true. |
TT_CONDITIONAL_PLEDGE
Indexed - No
Format - VARCHAR2
Nullable - Y | Tribute type = Conditional Pledge in PS_AV_TRIBUTE_DTL. Field = 'Y' if true. |
TT_BEQUEST_INTENTION
Indexed - No
Format - VARCHAR2
Nullable - Y | Tribute type = Bequest Intention in PS_AV_TRIBUTE_DTL. Field = 'Y' if true. |
ANONYMOUS_FLG
Indexed - No
Format - VARCHAR2
Nullable - N | When = 'Y', this individual gift/pledge transaction should be treated as originating from an anonymous donor. |
WHSE_DT_UPDATED
Indexed - No
Format - DATE
Nullable - N | Date that the database table row was updated in the data warehouse. Not to be confused with entry date (the date the gift was entered into ATLAS). |
UP_DEP_ID_TYPE
Indexed - No
Format - VARCHAR2
Nullable - Y | From the session table, this field indicates the method of deposit of funds to Penn's accounts. |
UP_BEN_DEPOSIT
Indexed - No
Format - VARCHAR2
Nullable - Y | From the session table, this is the deposity number of a particular deposit. Due to the implimentation of remote deposit capture at Penn, this field is not o great value. |
UP_DEPOSIT_DATE
Indexed - No
Format - DATE
Nullable - Y | From the session table, this field displays the date of fund deposit. |
UP_ORIGINAL_MODE
Indexed - No
Format - VARCHAR2
Nullable - Y | From the session table, this field indicates the origination of the gift or pledge session, such as lockbox, manual entry, or online giving. |
GL_POST_DT
Indexed - No
Format - DATE
Nullable - Y | Date the gift/pledge transaction is posted to the General Ledger. Used as selection criteria in daily gifts reports. |
COMPOSITE_KEY
Indexed - No
Format - VARCHAR2
Nullable - Y | Unique identifier for a gift/pledge transaction. Can be used as a group in reports to display distinct transactions. |
ROLLUP_ID
Indexed - No
Format - VARCHAR2
Nullable - Y | Used for linking gifts together, this field can be used to group separate gifts that the donor or Penn wants to consider as a single transaction |
ROLLUP_COMMON_ID
Indexed - No
Format - VARCHAR2
Nullable - Y | The donor id assigned to the grouped gifts under a rollup id. |
UP_DES_ID_TYPE
Indexed - No
Format - VARCHAR2
Nullable - N | Indicates Benefit Type from the PS_UP_ACCOUNT_BEN table. Values include U for unit, P for person, and O for org. |
BEN_UNIT
Indexed - No
Format - VARCHAR2
Nullable - N | The benefiting unit (unit which receives funds from a particular designation). |
DESIGNATION_PCT
Indexed - No
Format - NUMBER
Nullable - N | The percentage of a gift that is attributed to the benefiting unit. When the designation pct is less than 100, that means that gifts given to the particular designation will be split amongst multiple benefiting units. |
