CURRENCYEXCHANGERATE

Stores information pertaining to currency exchange rates.

Primary Key

Primary Key Field Type
ID uniqueidentifier

Fields

Field Field Type Null Notes Description
RATE decimal(20, 8) Default = 0 The rate to use when performing the conversion.
ASOFDATE datetimeoffset The date and time for which the exchange rate is valid.
TYPECODE tinyint Default = 0 The type of the exchange rate.
TYPE nvarchar(9) (Computed) yes CASE [TYPECODE] WHEN 0 THEN N'Corporate' WHEN 1 THEN N'Daily' WHEN 2 THEN N'Spot' END Provides a translation for the 'TYPECODE' field.
DATEADDED datetime Default = getdate() Indicates the date this record was added.
DATECHANGED datetime Default = getdate() Indicates the date this record was last changed.
TS timestamp Timestamp.
TSLONG bigint (Computed) yes CONVERT(bigint, TS) Numeric representation of the timestamp.
ISLATESTRATEFORASOFDATE bit Default = 0 Indicates that this rate has not been superseded by another rate with the same as of date.
NEXTRATEASOFDATE datetimeoffset yes The date and time at which the exchange rate is superseded by another rate for the same currencies and type code class.
EXPIRATIONDATE datetimeoffset yes The last day with time offset that this exchange rate is valid.
ASOFDATESDTZ datetime yes The date and time for which the exchange rate is valid, expressed in the system default time zone.
NEXTRATEASOFDATESDTZ datetime yes The date and time at which the exchange rate is superseded by another rate for the same currencies and type code class, expressed in the system default time zone.
NEXTRATEASOFDATEORMAX datetimeoffset (Computed) yes coalesce(CURRENCYEXCHANGERATE.NEXTRATEASOFDATE, convert(datetimeoffset(3), '9999-12-31T23:59:59.999', 126))
NEXTRATEASOFDATEORMAXSDTZ datetime (Computed) yes coalesce(convert(datetime, dateadd(ms, -2, CURRENCYEXCHANGERATE.NEXTRATEASOFDATE)), convert(datetime, '9999-12-31T23:59:59.997', 126))

Foreign Keys

Foreign Key Field Type Null Notes Description
FROMCURRENCYID uniqueidentifier CURRENCY.ID The currency from which the conversion is taking place.
TOCURRENCYID uniqueidentifier CURRENCY.ID The currency to which the conversion is taking place.
SOURCECODEID uniqueidentifier yes CURRENCYEXCHANGERATESOURCECODE.ID The source for the exchange rate.
ADDEDBYID uniqueidentifier CHANGEAGENT.ID FK to CHANGEAGENT.
CHANGEDBYID uniqueidentifier CHANGEAGENT.ID FK to CHANGEAGENT.
TIMEZONEENTRYID uniqueidentifier yes TIMEZONEENTRY.ID FK to TIMEZONEENTRY

Indexes

Index Name Fields Unique Primary Clustered
IX_CURRENCYEXCHANGERATE_ASOFDATE_NEXTRATEASOFDATE_FROMCURRENCYID_TOCURRENCYID_ISLATESTRATEFORASOFDATE_TYPECODE ASOFDATE, NEXTRATEASOFDATE, FROMCURRENCYID, TOCURRENCYID, ISLATESTRATEFORASOFDATE, TYPECODE
IX_CURRENCYEXCHANGERATE_ASOFDATESDTZ_NEXTRATEASOFDATESDTZ_FROMCURRENCYID_TOCURRENCYID_ISLATESTRATEFORASOFDATE_TYPECODE ASOFDATESDTZ, NEXTRATEASOFDATESDTZ, FROMCURRENCYID, TOCURRENCYID, ISLATESTRATEFORASOFDATE, TYPECODE
IX_CURRENCYEXCHANGERATE_DATEADDED DATEADDED yes
IX_CURRENCYEXCHANGERATE_DATECHANGED DATECHANGED
IX_CURRENCYEXCHANGERATE_FROMCURRENCYID_TOCURRENCYID_ISLATESTRATEFORASOFDATE_TYPECODE_ASOFDATE_EXPIRATIONDATE FROMCURRENCYID, TOCURRENCYID, ISLATESTRATEFORASOFDATE, TYPECODE, ASOFDATE, EXPIRATIONDATE
IX_CURRENCYEXCHANGERATE_FROMCURRENCYID_TOCURRENCYID_ISLATESTRATEFORASOFDATE_TYPECODE_ASOFDATE_NEXTRATEASOFDATEORMAX FROMCURRENCYID, TOCURRENCYID, ISLATESTRATEFORASOFDATE, TYPECODE, ASOFDATE, NEXTRATEASOFDATEORMAX
IX_CURRENCYEXCHANGERATE_FROMCURRENCYID_TOCURRENCYID_ISLATESTRATEFORASOFDATE_TYPECODE_ASOFDATESDTZ_EXPIRATIONDATE FROMCURRENCYID, TOCURRENCYID, ISLATESTRATEFORASOFDATE, TYPECODE, ASOFDATESDTZ, EXPIRATIONDATE
IX_CURRENCYEXCHANGERATE_FROMCURRENCYID_TOCURRENCYID_ISLATESTRATEFORASOFDATE_TYPECODE_ASOFDATESDTZ_NEXTRATEASOFDATEORMAXSDTZ FROMCURRENCYID, TOCURRENCYID, ISLATESTRATEFORASOFDATE, TYPECODE, ASOFDATESDTZ, NEXTRATEASOFDATEORMAXSDTZ
IX_CURRENCYEXCHANGERATE_TOCURRENCYID TOCURRENCYID
PK_CURRENCYEXCHANGERATE ID yes yes

Triggers

Trigger Name Description
TR_CURRENCYEXCHANGERATE_INSERTUPDATEDELETE_DATEFIELDS
TR_CURRENCYEXCHANGERATE_AUDIT_UPDATE
TR_CURRENCYEXCHANGERATE_AUDIT_DELETE

Referenced by

Referenced by Field
ADJUSTMENT BASEEXCHANGERATEID
ADJUSTMENT ORGANIZATIONEXCHANGERATEID
APPEAL ORGANIZATIONEXCHANGERATEID
APPEALBENEFIT ORGANIZATIONEXCHANGERATEID
APPEALBENEFITDETAIL ORGANIZATIONEXCHANGERATEID
AUCTIONITEM BASEEXCHANGERATEID
AUCTIONITEM BASETOORGANIZATIONEXCHANGERATEID
AUCTIONITEM ORIGINTOORGANIZATIONEXCHANGERATEID
AUCTIONPURCHASEADJUSTMENT ORGANIZATIONEXCHANGERATEID
AUCTIONPURCHASEADJUSTMENT BASEEXCHANGERATEID
BANKACCOUNTADJUSTMENT EXCHANGERATEID
BANKACCOUNTDEPOSIT TRANSACTIONEXCHANGERATEID
BATCHREVENUEUNAPPLIEDMATCHINGGIFTSPLIT ORGANIZATIONEXCHANGERATEID
BATCHREVENUEUNAPPLIEDMATCHINGGIFTSPLIT BASEEXCHANGERATEID
BENEFITADJUSTMENT BASEEXCHANGERATEID
BENEFITADJUSTMENT ORGANIZATIONEXCHANGERATEID
CAMPAIGNGOAL CURRENCYEXCHANGERATEID
CAMPAIGNHIERARCHYGOAL CURRENCYEXCHANGERATEID
CAMPAIGNPRIORITY CURRENCYEXCHANGERATEID
CAMPAIGNSUBPRIORITY CURRENCYEXCHANGERATEID
COMMITMENTREVALUATION BASEEXCHANGERATEID
COMMITMENTREVALUATION ORGANIZATIONEXCHANGERATEID
CONSTITUENTRECOGNITION ORGANIZATIONEXCHANGERATEID
CONSTITUENTRECOGNITIONBENEFIT ORGANIZATIONEXCHANGERATEID
CONSTITUENTRECOGNITIONREVENUE ORGANIZATIONEXCHANGERATEID
DESIGNATIONGOAL ORGANIZATIONEXCHANGERATEID
DESIGNATIONLEVEL ORGANIZATIONEXCHANGERATEID
DESIGNATIONLEVELDECAPITALIZATION ORGANIZATIONEXCHANGERATEID
DESIGNATIONLEVELDISBURSEMENT ORGANIZATIONEXCHANGERATEID
DESIGNATIONLEVELFINANCIALINFO ORGANIZATIONEXCHANGERATEID
DESIGNATIONLEVELGIFTFEESTRUCTURE ORGANIZATIONEXCHANGERATEID
DESIGNATIONLEVELGOAL ORGANIZATIONEXCHANGERATEID
DESIGNATIONLEVELRECIPIENT ORGANIZATIONEXCHANGERATEID
DONORCHALLENGE ORGANIZATIONEXCHANGERATEID
DONORCHALLENGEENCUMBERED ORGANIZATIONEXCHANGERATEID
DONORCHALLENGESPLIT ORGANIZATIONEXCHANGERATEID
EVENTEXPENSE ORGANIZATIONEXCHANGERATEID
EVENTPRICE ORGANIZATIONEXCHANGERATEID
EVENTPRICEBENEFIT ORGANIZATIONEXCHANGERATEID
EVENTPRICEBENEFIT BASEEXCHANGERATEID
EVENTREGISTRANTPAYMENT APPLICATIONEXCHANGERATEID
FINANCIALTRANSACTION BASEEXCHANGERATEID
FINANCIALTRANSACTION ORGEXCHANGERATEID
FUNDINGPLAN ORGANIZATIONEXCHANGERATEID
FUNDINGREQUEST BASEEXCHANGERATEID
FUNDINGREQUEST ORGANIZATIONEXCHANGERATEID
GIFTFEEADJUSTMENT BASEEXCHANGERATEID
GIFTFEEADJUSTMENT ORGANIZATIONEXCHANGERATEID
GIFTFEESTRUCTURE ORGANIZATIONEXCHANGERATEID
GIFTINKINDAPPRAISAL ORGANIZATIONEXCHANGERATEID
GIFTINKINDAPPRAISAL BASEEXCHANGERATEID
GIFTINKINDPAYMENTMETHODDETAIL ORGANIZATIONEXCHANGERATEID
GIFTINKINDPAYMENTMETHODDETAIL BASEEXCHANGERATEID
GIFTINKINDSALEADJUSTMENT ORGANIZATIONEXCHANGERATEID
GIFTINKINDSALEADJUSTMENT BASEEXCHANGERATEID
GRANTS ORGANIZATIONEXCHANGERATEID
GRANTS BASEEXCHANGERATEID
GROUPGOAL ORGANIZATIONEXCHANGERATEID
INSTALLMENT ORGANIZATIONEXCHANGERATEID
INSTALLMENT BASEEXCHANGERATEID
INSTALLMENTSPLIT ORGANIZATIONEXCHANGERATEID
INSTALLMENTSPLIT BASEEXCHANGERATEID
INSTALLMENTSPLITPAYMENT APPLICATIONEXCHANGERATEID
INSTALLMENTSPLITWRITEOFF ORGANIZATIONEXCHANGERATEID
INSTALLMENTSPLITWRITEOFF BASEEXCHANGERATEID
JOB ORGANIZATIONEXCHANGERATEID
JOBOCCURRENCE ORGANIZATIONEXCHANGERATEID
JOURNALENTRY_EXT PRECALCORGANIZATIONEXCHANGERATEID
JOURNALENTRY_EXT PRECALCBASEEXCHANGERATEID
MATCHINGGIFTCONDITION ORGANIZATIONEXCHANGERATEID
MEMBERSHIPADDON BASEEXCHANGERATEID
MEMBERSHIPADDON ORGANIZATIONEXCHANGERATEID
MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT ORGANIZATIONEXCHANGERATEID
MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT BASEEXCHANGERATEID
MEMBERSHIPLEVELBENEFIT ORGANIZATIONEXCHANGERATEID
MEMBERSHIPLEVELTERM ORGANIZATIONEXCHANGERATEID
MEMBERSHIPPROGRAM ORGANIZATIONEXCHANGERATEID
MEMBERSHIPPROGRAMADDON BASEEXCHANGERATEID
MEMBERSHIPPROGRAMADDON ORGANIZATIONEXCHANGERATEID
MEMBERSHIPPROMO ORGANIZATIONEXCHANGERATEID
MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE CURRENCYEXCHANGERATEID
MKTASKLADDERITEM ORGANIZATIONEXCHANGERATEID
MKTCREATIVE CURRENCYEXCHANGERATEID
MKTDOCUMENT CURRENCYEXCHANGERATEID
MKTEXPENSE CURRENCYEXCHANGERATEID
MKTLIST CURRENCYEXCHANGERATEID
MKTMARKETINGPLANBRIEF CURRENCYEXCHANGERATEID
MKTMARKETINGPLANITEM CURRENCYEXCHANGERATEID
MKTMARKETINGPLANITEMEXPENSE CURRENCYEXCHANGERATEID
MKTMATERIAL CURRENCYEXCHANGERATEID
MKTMEMBERSHIPMAILINGTEMPLATERULE CURRENCYEXCHANGERATEID
MKTPACKAGE CURRENCYEXCHANGERATEID
MKTSEGMENTATIONBUDGET ORGANIZATIONCURRENCYEXCHANGERATEID
MKTSEGMENTATIONPACKAGE ORGANIZATIONCURRENCYEXCHANGERATEID
MKTSEGMENTATIONSEGMENT CURRENCYEXCHANGERATEID
MKTSEGMENTATIONSEGMENTLIST ORGANIZATIONCURRENCYEXCHANGERATEID
MKTSEGMENTATIONTESTSEGMENT ORGANIZATIONCURRENCYEXCHANGERATEID
MKTSEGMENTLIST CURRENCYEXCHANGERATEID
MKTSEGMENTLISTHISTORICAL CURRENCYEXCHANGERATEID
MKTSPONSORSHIPMAILINGTEMPLATERULE CURRENCYEXCHANGERATEID
NAMINGOPPORTUNITY ORGANIZATIONEXCHANGERATEID
NAMINGOPPORTUNITYRECOGNITION ORGANIZATIONEXCHANGERATEID
OPPORTUNITY BASEEXCHANGERATEID
OPPORTUNITY ORGANIZATIONEXCHANGERATEID
OPPORTUNITYAMOUNTBRACKET ORGANIZATIONEXCHANGERATEID
OPPORTUNITYDESIGNATION ORGANIZATIONEXCHANGERATEID
OPPORTUNITYDESIGNATION BASEEXCHANGERATEID
PAYMENTORIGINALAMOUNT ORGANIZATIONEXCHANGERATEID
PAYMENTORIGINALAMOUNT BASEEXCHANGERATEID
PLANNEDGIFT ORGANIZATIONEXCHANGERATEID
PLANNEDGIFT BASEEXCHANGERATEID
PLANNEDGIFTADDITION ORGANIZATIONEXCHANGERATEID
PLANNEDGIFTADDITION BASEEXCHANGERATEID
PLANNEDGIFTADDITIONASSET ORGANIZATIONEXCHANGERATEID
PLANNEDGIFTADDITIONASSET BASEEXCHANGERATEID
PLANNEDGIFTADDITIONDESIGNATION ORGANIZATIONEXCHANGERATEID
PLANNEDGIFTADDITIONDESIGNATION BASEEXCHANGERATEID
PLANNEDGIFTASSET ORGANIZATIONEXCHANGERATEID
PLANNEDGIFTASSET BASEEXCHANGERATEID
PLANNEDGIFTDESIGNATION ORGANIZATIONEXCHANGERATEID
PLANNEDGIFTDESIGNATION BASEEXCHANGERATEID
PLANNEDGIFTPAYOUTADJUSTMENT BASEEXCHANGERATEID
PLANNEDGIFTPAYOUTADJUSTMENT ORGANIZATIONEXCHANGERATEID
PLEDGEORIGINALAMOUNT ORGANIZATIONEXCHANGERATEID
PLEDGEORIGINALAMOUNT BASEEXCHANGERATEID
PROPERTYDETAILADJUSTMENT ORGANIZATIONEXCHANGERATEID
PROPERTYDETAILADJUSTMENT BASEEXCHANGERATEID
RECOGNITIONCREDIT ORGANIZATIONEXCHANGERATEID
RECOGNITIONLEVELAMOUNT ORGANIZATIONEXCHANGERATEID
RECOGNITIONLEVELBENEFIT ORGANIZATIONEXCHANGERATEID
RECOGNITIONPROGRAM ORGANIZATIONEXCHANGERATEID
RECURRINGGIFTACTIVITY APPLICATIONEXCHANGERATEID
RECURRINGGIFTAMENDMENT BASEEXCHANGERATEID
RECURRINGGIFTAMENDMENT PREVIOUSBASEEXCHANGERATEID
RECURRINGGIFTAMENDMENT ORGANIZATIONEXCHANGERATEID
RECURRINGGIFTAMENDMENT PREVIOUSORGANIZATIONEXCHANGERATEID
RECURRINGGIFTDEVELOPMENTFUNCTIONHISTORY ORGANIZATIONEXCHANGERATEID
RECURRINGGIFTDEVELOPMENTFUNCTIONHISTORY PREVIOUSORGANIZATIONEXCHANGERATEID
RECURRINGGIFTDEVELOPMENTFUNCTIONHISTORY BASEEXCHANGERATEID
RECURRINGGIFTDEVELOPMENTFUNCTIONHISTORY PREVIOUSBASEEXCHANGERATEID
RECURRINGGIFTINSTALLMENT ORGANIZATIONEXCHANGERATEID
RECURRINGGIFTINSTALLMENT BASEEXCHANGERATEID
RECURRINGGIFTINSTALLMENTPAYMENT APPLICATIONEXCHANGERATEID
RECURRINGGIFTINSTALLMENTWRITEOFF ORGANIZATIONEXCHANGERATEID
RECURRINGGIFTINSTALLMENTWRITEOFF BASEEXCHANGERATEID
REGISTRANTBENEFIT BASEEXCHANGERATEID
REGISTRANTBENEFIT ORGANIZATIONEXCHANGERATEID
REGISTRANTDESIGNATION ORGANIZATIONEXCHANGERATEID
REGISTRANTREGISTRATION ORGANIZATIONEXCHANGERATEID
REVENUERECOGNITION ORGANIZATIONEXCHANGERATEID
REVENUESOLICITOR ORGANIZATIONEXCHANGERATEID
REVENUESPLITBUSINESSUNIT ORGANIZATIONEXCHANGERATEID
REVENUESPLITGIFTAID BASEEXCHANGERATEID
REVENUESPLITGIFTAID ORGANIZATIONEXCHANGERATEID
REVENUESPLITGIFTAIDAMOUNTS ORGANIZATIONEXCHANGERATEID
REVENUESPLITGIFTAIDAMOUNTS BASEEXCHANGERATEID
REVENUESPLITGIFTAIDAMOUNTS REVENUEORGANIZATIONEXCHANGERATEID
REVENUESPLITGIFTAIDAMOUNTS REVENUEBASEEXCHANGERATEID
REVENUESPLITGIFTAIDREFUND ORIGINALORGANIZATIONEXCHANGERATEID
REVENUESPLITGIFTAIDREFUND ORIGINALBASEEXCHANGERATEID
REVENUESPLITGIFTFEE ORGANIZATIONEXCHANGERATEID
REVENUESPLITGIFTFEE BASEEXCHANGERATEID
REVENUETRIBUTE ORGANIZATIONEXCHANGERATEID
REVENUETRIBUTETAXCLAIMAMOUNT ORGANIZATIONEXCHANGERATEID
REVENUEVAT ORGANIZATIONEXCHANGERATEID
REVENUEVAT BASEEXCHANGERATEID
SALESORDERITEMMEMBERSHIPITEMPROMOTION ORGANIZATIONEXCHANGERATEID
SPONSORSHIPOPPORTUNITYPROJECT ORGANIZATIONEXCHANGERATEID
STOCKDETAIL ORGANIZATIONEXCHANGERATEID
STOCKDETAIL BASEEXCHANGERATEID
STOCKSALEADJUSTMENT ORGANIZATIONEXCHANGERATEID
STOCKSALEADJUSTMENT BASEEXCHANGERATEID
TEAMFUNDRAISER ORGANIZATIONEXCHANGERATEID
TEAMFUNDRAISINGTEAM ORGANIZATIONEXCHANGERATEID
UNREALIZEDGAINLOSSADJUSTMENT BASEEXCHANGERATEID
UNREALIZEDGAINLOSSADJUSTMENT ORGANIZATIONEXCHANGERATEID
WRITEOFFADJUSTMENT ORGANIZATIONEXCHANGERATEID
WRITEOFFADJUSTMENT BASEEXCHANGERATEID