Penn Computing

University of Pennsylvania
Penn Computing << go backback

WHSE_TRANSACTION_DETAIL Table
   Tables and Data Elements   General Ledger Home   Data Warehouse Home

WHSE_TRANSACTION_DETAIL Table - Data Element Index

Select a data element to view its definition and its indexed, format, and null values.

  • COMMON_ID
  • BUSINESS_UNIT
  • SESSION_NO
  • GIFT_NO
  • SEQ_NO
  • ADJUSTMENT_FLG
  • EMPLID
  • EXT_ORG_ID
  • AV_SESS_TYPE
  • ADJ_REASON_CD
  • OPRID
  • GL_POST_DT
  • GL_POST_DT_FISCAL_YEAR
  • RECOGNITION_TYPE
  • DESIGNATION
  • AV_DES_NAME
  • INTV_CD
  • SA_ID_TYPE
  • AV_DES_BU
  • RCG_DES_AMT
  • CHARITABLE_AMT
  • BENEFIT_AMT
  • PLEDGE_AMT
  • COMMITMENTS
  • RECEIPTS
  • AV_PLDG_STATUS
  • CURRENCY_CD
  • GIFT_DT
  • GIFT_DT_FISCAL_YEAR
  • DATA_ENTRY_DT
  • ORIGINAL_GL_POST_DT
  • ORIGINAL_GL_FISCAL_YEAR
  • ORIGINAL_EFFDT
  • ORIGINAL_FY
  • ORIGINAL_DATA_ENTRY_DT
  • ORIGINAL_DATA_ENTRY_DT_FY
  • REFERENCE_DT
  • HAS_BEEN_ADJ
  • UP_DEPOSIT_DATE
  • CNST_TYPE
  • ALUMNUS_TYPE
  • APPEAL_CODE
  • AV_CASE_TYPE
  • AV_CASE_PRPS
  • CAE_GIFT_TYPE
  • CAE_RE_FLAG
  • TENDER_TYPE
  • TENDER_TYPE_ROLLUP
  • GIFT_TYPE
  • WHSE_GIFT_TYPE
  • FEED_FLAG
  • CREDIT_CARD_TYPE
  • GL_FEED_TECH_KEY
  • PLEDGE_GIFT_NO
  • PLEDGE_PMT_SORT
  • PLEDGE_EFFDT
  • PLEDGE_FY
  • TT_AFFILIATED_GIVING
  • TT_ULTIMATE_DESIGNATION
  • TT_LEAD_TRUST
  • TT_NONBIND_COMMIT
  • TT_SUPPRESS_RECEIPT
  • TT_DO_NOT_BILL
  • TT_CONDITIONAL_PLEDGE
  • TT_BEQUEST_INTENTION
  • TT_VERBAL
  • ANONYMOUS_FLG
  • WHSE_DT_UPDATED
  • UP_DEP_ID_TYPE
  • UP_BEN_DEPOSIT
  • UP_ORIGINAL_MODE
  • COMPOSITE_KEY
  • ROLLUP_ID
  • ROLLUP_COMMON_ID
  • DESCRLONG
  • POP_TYPE
  • WHSE_MODE


  • 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.

    CNAC_CODES Table
       Tables and Data Elements   General Ledger Home   Data Warehouse Home

    Questions about this page? Email us at da-staff@isc.upenn.edu

    Information Systems and Computing
    University of Pennsylvania
    Information Systems and Computing, University of Pennsylvania