USP_MKTRECORDSOURCEFIELDMAPPINGS_GETFIELDMAPPINGS

CREATE procedure dbo.[USP_MKTRECORDSOURCEFIELDMAPPINGS_GETFIELDMAPPINGS]
(
  @RECORDSOURCEID uniqueidentifier = null
)
as
  set nocount on;

  select
    [FM].[ID] as [RECORDSOURCEID],
    [CQV].[CONSOLIDATEDQUERYVIEWCATALOGID],
    [FM].[FULLNAMEFIELD],
    [FM].[FIRSTNAMEFIELD],
    [FM].[MIDDLENAMEFIELD],
    [FM].[LASTNAMEFIELD],
    [FM].[ORGNAMEFIELD],
    [FM].[TITLEFIELD],
    [FM].[SUFFIXFIELD],
    [FM].[COUNTRYFIELD],
    [FM].[ADDRESSLINE1FIELD],
    [FM].[ADDRESSLINE2FIELD],
    [FM].[CITYFIELD],
    [FM].[STATEFIELD],
    [FM].[POSTCODEFIELD],
    [FM].[CARTFIELD],
    [FM].[DPCFIELD],
    [FM].[LOTFIELD],
    [FM].[PHONENUMBERFIELD],
    [FM].[EMAILADDRESSFIELD],
    [FM].[FIRSTGIFTDATEFIELD],
    [FM].[ADDRESSLINE3FIELD],
    [FM].[ADDRESSLINE4FIELD],
    [FM].[ADDRESSLINE5FIELD],
    [FM].[HOUSEHOLDIDFIELD],
    [FM].[HEADOFHOUSEHOLDFIELD],
    [FM].[ISHOUSEHOLDFIELD],
    [FM].[DATELASTCHANGEDFIELD],
    [FM].[ADDRESSEEFIELD],
    [FM].[SALUTATIONFIELD],
    case when (dbo.[UFN_MKTRECORDSOURCE_VALIDFORBBEC](@RECORDSOURCEID) = 1) then 'CONTACTADDRESSEE' else '' end as [CONTACTADDRESSEEFIELD],
    [FM].[LOOKUPIDFIELD],

    [GFM].[QUERYVIEWCATALOGID] [REVENUEQUERYVIEWCATALOGID],
    [GFM].[APPEALSYSTEMIDFIELD] [REVENUEAPPEALSYSTEMIDFIELD],
    [GFM].[APPEALIDFIELD] [REVENUEAPPEALIDFIELD],
    [GFM].[APPEALDESCRIPTIONFIELD] [REVENUEAPPEALDESCRIPTIONFIELD],
    [GFM].[AMOUNTFIELD] [REVENUEAMOUNTFIELD],
    [GFM].[DONORIDFIELD] [REVENUEDONORIDFIELD],
    [GFM].[SOURCECODEFIELD] [REVENUESOURCECODEFIELD],
    [GFM].[MAILINGIDFIELD] [REVENUEMAILINGIDFIELD],
    [GFM].[DATEFIELD] [REVENUEDATEFIELD],
    [GFM].[FINDERNUMBERFIELD] [REVENUEFINDERNUMBERFIELD],
    [GFM].[DATEADDEDFIELD] [REVENUEDATEADDEDFIELD],
    [GFM].[DONOTACKNOWLEDGEFIELD] [REVENUEDONOTACKNOWLEDGEFIELD],
    [GFM].[DONOTRECEIPTFIELD] [REVENUEDONOTRECEIPTFIELD],
    [GFM].[DATECHANGEDFIELD] [REVENUEDATECHANGEDFIELD],
    [GFM].[LOOKUPIDFIELD] [REVENUELOOKUPIDFIELD],
    [GFM].[BASECURRENCYIDFIELD] [REVENUEBASECURRENCYIDFIELD],

    [MFM].[QUERYVIEWCATALOGID] [MEMBERSHIPQUERYVIEWCATALOGID],
    [MFM].[MEMBERSHIPSYSTEMIDFIELD],
    [MFM].[MEMBERIDFIELD] as [MEMBERSHIPMEMBERIDFIELD],
    [MFM].[MEMBERFIELD] as [MEMBERSHIPMEMBERFIELD],
    [MFM].[GIVENBYIDFIELD] as [MEMBERSHIPGIVENBYIDFIELD],
    [MFM].[GIVENBYFIELD] as [MEMBERSHIPGIVENBYFIELD],
    [MFM].[EXPIRATIONDATEFIELD] as [MEMBERSHIPEXPIRATIONDATEFIELD],
    [MFM].[ISGIFTFIELD] as [MEMBERSHIPISGIFTFIELD],
    [MFM].[JOINDATEFIELD] as [MEMBERSHIPJOINDATEFIELD],
    [MFM].[LASTRENEWEDONDATEFIELD] as [MEMBERSHIPLASTRENEWEDONDATEFIELD],
    [MFM].[LEVELFIELD] as [MEMBERSHIPLEVELFIELD],
    [MFM].[PROGRAMFIELD] as [MEMBERSHIPPROGRAMFIELD],
    [MFM].[SENDRENEWALFIELD] as [MEMBERSHIPSENDRENEWALFIELD],
    [MFM].[STATUSFIELD] as [MEMBERSHIPSTATUSFIELD],
    [MFM].[TERMFIELD] as [MEMBERSHIPTERMFIELD],
    [MFM].[TYPEFIELD] as [MEMBERSHIPTYPEFIELD],
    'ISMEMBER' as [MEMBERSHIPISMEMBERFIELD],
    [MFM].[LOOKUPIDFIELD] as [MEMBERSHIPLOOKUPIDFIELD],

    [SFM].[QUERYVIEWCATALOGID] [SPONSORSHIPQUERYVIEWCATALOGID],
    [SFM].[SPONSORSHIPSYSTEMIDFIELD],
    [SFM].[SPONSORIDFIELD] as [SPONSORSHIPIDFIELD],
    [SFM].[ACTIONFIELD] as [SPONSORSHIPACTIONFIELD],
    [SFM].[REASONFIELD] as [SPONSORSHIPREASONFIELD],
    [SFM].[GIFTSPONSORSHIPFIELD] as [SPONSORSHIPGIFTSPONSORSHIPFIELD],
    [SFM].[SPONSORSHIPOPPORTUNITYIDCHILDFIELD] as [SPONSORSHIPOPPORTUNITYIDCHILDFIELD],
    [SFM].[SPONSORSHIPOPPORTUNITYIDPROJECTFIELD] as [SPONSORSHIPOPPORTUNITYIDPROGRAMFIELD],
    [SFM].[SPONSORSHIPCHILDPROGRAMIDFIELD] as [SPONSORSHIPCHILDPROJECTIDFIELD],
    [SFM].[SPONSORSHIPPROJECTPROGRAMIDFIELD] as [SPONSORSHIPROJECTPROGRAMIDFIELD],
    [SFM].[SPONSORSHIPLOCATIONIDFIELD] as [SPONSORSHIPLOCATIONIDFIELD]

  from dbo.[MKTRECORDSOURCEFIELDMAPPINGS] [FM]
  left outer join dbo.[MKTGIFTRECORDSOURCE] [GFM] on [GFM].[ID] = [FM].[ID]
  left outer join dbo.[MKTMEMBERSHIPRECORDSOURCE] as [MFM] on [MFM].[ID] = [FM].[ID]
  left outer join dbo.[MKTSPONSORSHIPRECORDSOURCE] as [SFM] on [SFM].[ID] = [FM].[ID]
  left outer join dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC] [CQV] on [CQV].[ID] = [FM].[ID]
  where (@RECORDSOURCEID is null or [FM].[ID] = @RECORDSOURCEID)
  and dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([FM].[ID]) = 1;

  return 0;