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 |
Number used to identify a specific gift or pledge (not unique, part of key). |
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 one mor |
ADJUSTMENT_FLG
Indexed - No
Format - VARCHAR2
Nullable - Y |
Yes/No flag indicating the status of a gift or pledge. Yes means it has been adjusted and is no longer the valid row for the particular transaction. |
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 |
AV_SESS_TYPE
Indexed - No
Format - VARCHAR2
Nullable - N |
Type of gift entry session (for ex. 'PL' = a pledge gift entry session). |
ADJ_REASON_CD
Indexed - No
Format - VARCHAR2
Nullable - Y |
This field indicates the reason the gift was adjusted. |
OPRID
Indexed - No
Format - VARCHAR2
Nullable - Y |
This is the user id of the gift entry operator who keyed the gift session for this particular transaction. |
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. |
GL_POST_DT_FISCAL_YEAR
Indexed - No
Format - NUMBER
Nullable - Y |
Calculated fiscal year of the GL post date. |
RECOGNITION_TYPE
Indexed - No
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. |
AV_DES_NAME
Indexed - No
Format - VARCHAR2
Nullable - Y |
This is the short name of the gift or pledge's designation. |
INTV_CD
Indexed - No
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. |
RCG_DES_AMT
Indexed - No
Format - NUMBER
Nullable - N |
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 - N |
Amount of gift minus the value of services attached to gift. This is the tax deductible amount of the gift. |
BENEFIT_AMT
Indexed - No
Format - NUMBER
Nullable - Y |
This is the portion of a gift that is not tax deductible (only relevant for
hard $ recognition_type = 'H'). Calculated as the difference between
the RCG_DES_AMT and CHARITABLE_AMT, it presents the amount the donor benefitted
from the gift. |
PLEDGE_AMT
Indexed - No
Format - NUMBER
Nullable - Y |
The value of a pledge |
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 - Yes
Format - NUMBER
Nullable - Y |
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. Conversely,
f the GIFT_DT = 07/01/2007 then the GIFT_DT_FISCAL_YEAR = 2008. |
DATA_ENTRY_DT
Indexed - No
Format - DATE
Nullable - Y |
For historical gift reporting, this is the field to used to determine when
a gift/pledge was entered into ATLAS (GL_POST_DT is the best field to use for
gift transaction entry date from the R4 conversion -4/2007-onward. Note that
the entry date field for the negative side of gift adjustements recorded in
Atlas carries the date of the prior positve entry.) |
ORIGINAL_GL_POST_DT
Indexed - No
Format - DATE
Nullable - Y |
For adjusted gifts, this field indicates the first time a gift or pledge posted into Atlas. |
ORIGINAL_GL_FISCAL_YEAR
Indexed - No
Format - NUMBER
Nullable - Y |
Fiscal year of the ORIGINAL_GL_POST_DT. |
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. |
REFERENCE_DT
Indexed - Yes
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 |
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. |
UP_DEPOSIT_DATE
Indexed - No
Format - DATE
Nullable - Y |
From the session table, this field displays the date of fund deposit. |
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') |
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 - Yes
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_PMT_SORT
Indexed - No
Format - VARCHAR2
Nullable - Y |
For pledge payments, this is the PLEDGE_GIFT_NO, and for pledges it is the GIFT_NO. This field allows pledges and there payments to be sorted on a common value. |
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 |
TT_AFFILIATED_GIVING
Indexed - No
Format - VARCHAR2
Nullable - Y |
Tribute type = Affiliated Giving 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. |
TT_VERBAL
Indexed - No
Format - VARCHAR2
Nullable - Y |
Yes/No - Indicates a verbal commitment |
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. Typically this value is "remote deposit capture." |
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_ORIGINAL_MODE
Indexed - No
Format - VARCHAR2
Nullable - Y |
See WHSE_MODE below:
From the session table, this field indicates the origination
of the gift or pledge session, such as lockbox, manual entry, or online
giving. Possible
values are: C Conversion
E Ruffalo Cody pledges
F Ruffalo Cody recurring CC [CC = Credit Card]
L Wachovia Lockbox Cash
M Manual [entered directly on Atlas]
O On-line Gifts
P On-line Pledges
Q On-line Pledge Payments
R Recurring CC Gifts (On-line)
V Recurring CC gifts [being phased out – eventually all recurring CC
will be R]
W Wachovia Lockbox Credit Card
|
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. |
DESCRLONG
Indexed - No
Format - LONG
Nullable - Y |
Free text field containing notes about a particular gift or pledge |
POP_TYPE
Indexed - No
Format - VARCHAR2
Nullable - Y |
This inicates the primary source for the row -- PS_AV_GIFT_DTL, PS_AV_ADJ_GIFT_DTL, PS_AV_PLEDGE_DTL, or PS_AV_ADJ_PLDG_DTL |
WHSE_MODE
Indexed - No
Format - VARCHAR2
Nullable - N |
The original mode of entry for a gift or pledge. (The UP_ORIGINAL_MODE
is tied to the session creating or adjusting a gift. It is possible for the
mode to change over time. For example, an online gift may be adjusted
and have its mode changed.) See UP_ORIGINAL_MODE, above, for values. |
