USP_DATALIST_CONSTITUENT_TRANSACTIONMANAGER

Returns a list of Transaction Manager records.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@INCLUDESOFTCREDIT	bit	IN	Include Soft Credits
Definition

Copy


            CREATE proc [dbo].[USP_DATALIST_CONSTITUENT_TRANSACTIONMANAGER]
            (
                @CONSTITUENTID uniqueidentifier,
                @INCLUDESOFTCREDIT bit
            )
            AS
                    SELECT
                      R.[ID],
                      R.[AMOUNT],
                      R.[DATE],
                      R.[GIVENANONYMOUSLY],
                      CASE R.[TRANSACTIONTYPECODE] WHEN 1 THEN [dbo].[UFN_PLEDGE_GETBALANCE](R.[ID]) ELSE NULL END AS 'BALANCE',
                           [dbo].[UFN_REVENUE_DESIGNATIONLIST](R.[ID]) AS 'DESIGNATIONS',
                      RPM.[PAYMENTMETHOD],
                      ISNULL(GAGD.[AMOUNT], 0) AS 'GIFTAIDAMOUNT',
                      R.[TRANSACTIONTYPE],
                      R.[TRANSACTIONTYPECODE],
                      RR.[RECEIPTDATE],
                      RR.[RECEIPTNUMBER],
                      (
                          select 
                              dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) 
                          from 
                              dbo.CAMPAIGN 
                          inner join 
                              dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                          inner join
                              dbo.REVENUESPLIT on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                          where
                              REVENUESPLIT.REVENUEID = R.ID
                       ) as 'CAMPAIGNS',
                       RS.FREQUENCY,
                       RS.NUMBEROFINSTALLMENTS,
                       PST.NAME as 'PLEDGESUBTYPE',
                       0 as 'RECOGNITIONCREDIT'
                  FROM
                       [dbo].[REVENUE] R
                       INNER JOIN [dbo].[REVENUEPAYMENTMETHOD] RPM
                          ON RPM.REVENUEID = R.ID
                       LEFT JOIN [dbo].[GIFTAIDGLDISTRIBUTION] GAGD
                          ON GAGD.REVENUEID = R.ID
                       LEFT JOIN [dbo].[REVENUERECEIPT] RR
                          ON RR.[REVENUEID] = R.[ID]
                       LEFT JOIN [dbo].[REVENUESCHEDULE] RS
                          ON RS.[ID] = R.[ID]
                      LEFT JOIN [dbo].[PLEDGESUBTYPE] PST
                          ON PST.[ID] = RS.[PLEDGESUBTYPEID]
                  WHERE
                      R.[CONSTITUENTID] = @CONSTITUENTID

                  union all

                  SELECT
                      R.[ID],
                      R.[AMOUNT],
                      R.[DATE],
                      R.[GIVENANONYMOUSLY],
                      CASE R.[TRANSACTIONTYPECODE] WHEN 1 THEN [dbo].[UFN_PLEDGE_GETBALANCE](R.[ID]) ELSE NULL END AS 'BALANCE',
                           [dbo].[UFN_REVENUE_DESIGNATIONLIST](R.[ID]) AS 'DESIGNATIONS',
                      RPM.[PAYMENTMETHOD],
                      ISNULL(GAGD.[AMOUNT], 0) AS 'GIFTAIDAMOUNT',
                      R.[TRANSACTIONTYPE],
                      R.[TRANSACTIONTYPECODE],
                      RR.[RECEIPTDATE],
                      RR.[RECEIPTNUMBER],
                      (
                          select 
                              dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) 
                          from 
                              dbo.CAMPAIGN 
                          inner join 
                              dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                          inner join
                              dbo.REVENUESPLIT on REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
                          where
                              REVENUESPLIT.REVENUEID = R.ID
                       ) as 'CAMPAIGNS',
                       RS.FREQUENCY,
                       RS.NUMBEROFINSTALLMENTS,
                       PST.NAME as 'PLEDGESUBTYPE',
                       1 as 'RECOGNITIONCREDIT'
                  FROM
                  dbo.REVENUERECOGNITION as RRec
                  INNER JOIN dbo.REVENUESPLIT RSplt 
                      ON RRec.REVENUESPLITID = RSplt.ID
                  INNER JOIN dbo.REVENUE R 
                      ON RSplt.REVENUEID = R.ID
                  INNER JOIN [dbo].[REVENUEPAYMENTMETHOD] RPM
                      ON RPM.REVENUEID = R.ID
                  LEFT JOIN [dbo].[REVENUERECEIPT] RR
                      ON RR.[REVENUEID] = R.[ID]
      LEFT JOIN [dbo].[GIFTAIDGLDISTRIBUTION] GAGD
                      ON GAGD.REVENUEID = R.ID
                  LEFT JOIN [dbo].[REVENUESCHEDULE] RS
                      ON RS.[ID] = R.[ID]
                  LEFT JOIN [dbo].[PLEDGESUBTYPE] PST
                      ON PST.[ID] = RS.[PLEDGESUBTYPEID]
                  WHERE
                      @INCLUDESOFTCREDIT = 1
                      and
                      RRec.CONSTITUENTID = @CONSTITUENTID    
                      and
                      R.CONSTITUENTID <> RRec.CONSTITUENTID