USP_DATAFORMTEMPLATE_VIEW_SOLICITORSBYPAYMENTREVENUESPLITID

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SOLICITORSBYPAYMENTREVENUESPLITID
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TYPE nvarchar(20) = null output,
    @AMOUNT money = null output,
    @SOLICITORS XML= null output,
  @BASECURRENCYID uniqueidentifier = null output
)
as
    set nocount on;

  declare @SOURCEREVENUEID uniqueidentifier;
  select @SOURCEREVENUEID = PLEDGEID from dbo.INSTALLMENTSPLITPAYMENT where PAYMENTID = @ID;
  if @SOURCEREVENUEID is null
    select @SOURCEREVENUEID = SOURCEREVENUEID from dbo.RECURRINGGIFTACTIVITY where PAYMENTREVENUEID = @ID;

  -- revenue split temp table

  declare @REVENUESPLITTABLE table
  (
    REVENUESPLITID uniqueidentifier
  )

  declare @REVENUESOLICITORSTABLE table
  (    
    [ID] uniqueidentifier,
    [CONSTITUENTID] uniqueidentifier,
    [AMOUNT] money,
    [SEQUENCE] int,
    [BASECURRENCYID] uniqueidentifier,
    [ORGANIZATIONAMOUNT] money,
    [ORGANIZATIONEXCHANGERATEID] uniqueidentifier
  )

  --If @SOURCEREVENUEID is null, means this SP is called for the new commitment payment and @ID is commitment's APPLICATIONID.

  --So Pull Solicitors from commitments

  if @SOURCEREVENUEID is null
  begin  

    insert into @REVENUESPLITTABLE(REVENUESPLITID)
    select ID
    from dbo.REVENUESPLIT
    where REVENUEID = @ID

  end  
  else
  begin

    insert into @REVENUESPLITTABLE(REVENUESPLITID)
    select ID
    from dbo.REVENUESPLIT
    where REVENUEID = @SOURCEREVENUEID
    and DESIGNATIONID = (select DESIGNATIONID from dbo.REVENUESPLIT where ID = @ID)

  end  

  ;with REVENUESOLICITOR_CTE As
    (                      
        select
            REVENUESOLICITOR.ID,
            REVENUESOLICITOR.CONSTITUENTID,
            SUM(REVENUESOLICITOR.AMOUNT) OVER (PARTITION BY REVENUESOLICITOR.CONSTITUENTID ) As AMOUNT,
            REVENUESOLICITOR.SEQUENCE,
            REVENUESOLICITOR.BASECURRENCYID,
            SUM(REVENUESOLICITOR.ORGANIZATIONAMOUNT) OVER (PARTITION BY REVENUESOLICITOR.CONSTITUENTID ) As ORGANIZATIONAMOUNT,
            REVENUESOLICITOR.ORGANIZATIONEXCHANGERATEID,
            ROW_NUMBER() OVER (PARTITION BY REVENUESOLICITOR.CONSTITUENTID ORDER BY REVENUESOLICITOR.ID) As RS_ROWNUMBER
        from @REVENUESPLITTABLE RS
        inner join REVENUESOLICITOR on REVENUESOLICITOR.REVENUESPLITID = RS.REVENUESPLITID
  )

   insert into @REVENUESOLICITORSTABLE
    (
      [ID],
      [CONSTITUENTID],
      [AMOUNT],
      [SEQUENCE],
      [BASECURRENCYID],
      [ORGANIZATIONAMOUNT],
      [ORGANIZATIONEXCHANGERATEID]
    )
    select
      REVENUESOLICITOR_CTE.ID,
      REVENUESOLICITOR_CTE.CONSTITUENTID,
      REVENUESOLICITOR_CTE.AMOUNT,
      REVENUESOLICITOR_CTE.SEQUENCE,
      REVENUESOLICITOR_CTE.BASECURRENCYID,
      REVENUESOLICITOR_CTE.ORGANIZATIONAMOUNT,
      REVENUESOLICITOR_CTE.ORGANIZATIONEXCHANGERATEID
   from REVENUESOLICITOR_CTE
   where RS_ROWNUMBER = 1;

  select @DATALOADED = 1,
    @TYPE = REVENUE.TRANSACTIONTYPE,
    @AMOUNT = sum(REVENUESPLIT.AMOUNT),
    @SOLICITORS = (
               select ID,
                      CONSTITUENTID,
                      AMOUNT,
                      SEQUENCE,
                      BASECURRENCYID,
                      ORGANIZATIONAMOUNT,
                      ORGANIZATIONEXCHANGERATEID
               from @REVENUESOLICITORSTABLE for xml raw('ITEM'),type,elements,root('SOLICITORS'),BINARY BASE64),
    @BASECURRENCYID = REVENUESPLIT.BASECURRENCYID
    from dbo.REVENUESPLIT
    inner join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
    inner join @REVENUESPLITTABLE REVENUESPLITTABLE on REVENUESPLITTABLE.REVENUESPLITID = REVENUESPLIT.ID
    group by REVENUE.TRANSACTIONTYPE, REVENUESPLIT.BASECURRENCYID 

    return 0;