Base table for financial reporting, has one row for each transaction split.
Please note:
- Atlas policy does not allow revenue entry on households, groups and committees. If money is entered incorrectly however, it will still appear in this table.
- In Atlas there can be multiple campaigns per split. The CAMPAIGNLIST field includes all campaigns at the revenue level and not at the split level. To report on campaigns, use the fact_revenuecampaign table.
- All the constituent data is for the revenue constituent, which is usually the same as the hard credit recognition recipient but this is not a requirement in CRM.
Conversion Notes: This table is a mix of DR_Gift and some aspects of PHS_householdgiving. Like DR_Gift it is optimized to be the base table for all revenue reporting.
The table contains one row for each application split, eg one row for each gift split just like DR_Gift did.
| Column | Comments |
|---|---|
| FINANCIALTRANSACTIONLINEITEMFACTID | |
| FINANCIALTRANSACTIONLINEITEMSYSTEMID | |
| FINANCIALTRANSACTIONFACTID | |
| FINANCIALTRANSACTIONSYSTEMID | |
| CONSTITUENTDIMID | This is the dim ID for the revenue donor, which is not the same as the hard credit donor. |
| HOUSEHOLDCONSTITUENTDIMID | This is the household dim id for the revenue donor. |
| ISCOMMITMENT | Yes/No |
| ISCASH | Yes/No |
| GIFTTYPEDIMID | |
| GIFTTYPE | |
| REVENUEAPPLICATION | |
| VENDOR | From Financial Transaction Attribute of same name |
| REVENUECHANNELDESCRIPTION | |
| EVENTSYSTEMID | |
| EVENTDIMID | |
| EVENTID | |
| EVENTNAME | |
| EVENTREGISTRATIONDESCRIPTION | |
| REVENUEPAYMENTMETHOD | |
| PAYMENTSUBTYPE | Data from the revenue attribute Payment Subtype - note this attribute MUST be included in the DW for this field to be populated. |
| FINANCIALTRANSACTIONLINEITEMTRANSACTIONAMOUNT | |
| FINANCIALTRANSACTIONAMOUNT | |
| FINANCIALTRANSACTIONRECEIPTAMOUNT | Note that Atlas calculates receipt amounts at the revenue level, NOT at the revenue split level |
| TOTALBENEFITAMOUNT | |
| LINEITEMBALANCE | Balance on this split |
| TRANSACTIONBALANCE | Balance on the overall pledge |
| FINANCIALTRANSACTIONDATE | |
| FINANCIALTRANSACTIONDATEDIMID | |
| FINANCIALTRANSACTIONDATEFISCALYEAR | |
| FINANCIALTRANSACTIONDATEFISCALMONTH | |
| FINANCIALTRANSACTIONDATEFISCALMONTHNAME | |
| FINANCIALTRANSACTIONDATECALENDARYEAR | |
| FINANCIALTRANSACTIONDATECALENDARMONTH | |
| FINANCIALTRANSACTIONDATECALENDARMONTHNAME | |
| ADDEDDATE | |
| ADDEDDATEDIMID | |
| CHANGEDDATE | |
| CHANGEDDATEDIMID | |
| REVENUEPOSTSTATUS | |
| FINANCIALTRANSACTIONPOSTDATE | |
| FINANCIALTRANSACTIONPOSTDATEDIMID | |
| SORTNAME | For the revenue constituent, not the hard credit recognition donor |
| KEYNAME | For the revenue constituent, not the hard credit recognition donor |
| FULLNAME | For the revenue constituent, not the hard credit recognition donor |
| CONSTITUENTSYSTEMID | For the revenue constituent, not the hard credit recognition donor |
| CONSTITUENTLOOKUPID | For the revenue constituent, not the hard credit recognition donor |
| ISDECEASED | |
| GIVESANONYMOUSLY | |
| DONORCLASS | |
| FIRSTGIFT | |
| ISONLINEGIFT | |
| SPLITRATIO | |
| REVENUESPLITBUSINESSUNITDIMID | |
| REVENUEBUSINESSUNIT | |
| REVENUESPLITBUSINESSSUBUNIT | |
| CAMPAIGNLIST | List of campaigns associated with the transaction at the revenue level (not the split level) |
| APPEALDIMID | |
| APPEALSYSTEMID | |
| APPEALDESCRIPTION | |
| APPEALPURPOSE | From Appeal Attribute |
| APPEALPROGRAM | From Appeal Attribute |
| APPEALSITE | |
| APPEALBUSINESSUNITDIMID | |
| APPEALBUSINESSUNIT | |
| APPEALBUSINESSSUBUNIT | |
| APPEALNAME | |
| APPEALLIST | |
| APPEALCATEGORY | |
| MARKETINGSEGMENTATIONDIMID | |
| MARKETINGEFFORTSOURCECODE | |
| MARKETINGEFFORTDESCRIPTION | |
| DESIGNATIONDIMID | |
| DESIGNATIONSYSTEMID | |
| DESIGNATIONLOOKUPID | |
| DESIGNATIONNAME | |
| DESIGNATIONDEPARTMENT | From Designation Attribute |
| DESIGNATIONLIST | List of designations associated with all the applications of the revenue |
| DESIGNATIONSITE | |
| PURPOSESCATEGORY | |
| PURPOSESITE | |
| ISHONOROFTRIBUTE | If Tribute Type not like %memory% then 'Yes'. Not this is based on the words in the Tribute Type rather than the Tribute Text. In most cases, the Tribute Text includes the Tribute Type, unless it was manually changed |
| ISMEMORYOFTRIBUTE | If Tribute Type like %memory% then 'Yes'. Not this is based on the words in the Tribute Type rather than the Tribute Text. In most cases, the Tribute Text includes the Tribute Type, unless it was manually changed |
| TRIBUTELIST | Tribute List is the contents of TRIBUTE.TRIBUTETEXT from the OLTP database. By default, the formula used for populating it starts with the Tribute Type - but that can be overridden when the Tribute is being created/edited. Only almost all of tributes are going to start with the Tribute Type. |
| ISANONYMOUS | |
| ISACKNOWLEDGED | |
| ACKNOWLEDGEDDATEDIMID | |
| ISSUE | |
| ISSUERNUMBEROFUNITS | |
| ISSUERSYMBOL | |
| STOCKSALEDATE | |
| DONOTACKNOWLEDGE | |
| DONOTRECEIPT | |
| ISREALIZED | |
| REMAINDERVALUE | |
| REMAINDERVALUEDATE | |
| NPVVALUE | |
| NPVVALUEDATE | |
| DEBITACCOUNT | |
| CREDITACCOUNT | |
| FEDTOFINANCE | Pulls from Fed to Finance smart field |
| BATCHNUMBER | |
| FINANCIALTRANSACTIONLOOKUPID | |
| SITEDIMID | |
| SITENAME | |
| SITEDESCRIPTION | |
| PURPOSELEVELDIMID | |
| PURPOSELEVELDESCRIPTION | |
| PURPOSELEVELNAME | |
| PURPOSELEVELLIST | |
| PURPOSELEVELPUBLICNAME | |
| PURPOSELEVELPUBLICNAMELIST | |
| PURPOSELEVELLOOKUPID | |
| PURPOSELEVELCATEGORY | |
| PURPOSELEVELTYPE | |
| OTHERPAYMENTMETHOD | |
| GIFTTYPETRANSLATION | |
| HOUSEHOLDFORMALADDRESSEE | |
| REVENUETRANSACTIONTYPE | |
| REFERENCE | |
| RECEIPTSTATUS |