USP_REVENUEBATCH_DEFAULTMARKETINGINFORMATION_FROMREVENUESTREAMS

CREATE procedure dbo.USP_REVENUEBATCH_DEFAULTMARKETINGINFORMATION_FROMREVENUESTREAMS
(
  @REVENUESTREAMS xml,
    @REVENUEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
    set nocount on

    declare @SOURCECODE nvarchar(50) = '';
    declare @MAILINGID uniqueidentifier = null;
    declare @APPEALID uniqueidentifier = null;
    declare @TRANSACTIONTYPECODE tinyint;
    declare @APPLICATIONID uniqueidentifier = null;


    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate()

    DECLARE @RS table
    (
       APPLICATIONID uniqueidentifier,
     APPLIED money,
     TYPECODE tinyint
    )

    insert into @RS
    select  
     APPLICATIONID,  
     APPLIED,  
     TYPECODE
    from dbo.UFN_REVENUEBATCH_GETAPPLICATIONS_FROMITEMLISTXML(@REVENUESTREAMS)  
    where APPLIED > 0;  

    --One and only one application is applied, otherwise exit

    if @@ROWCOUNT = 1
    begin
      select @APPLICATIONID = R.APPLICATIONID
      , @TRANSACTIONTYPECODE = R.TYPECODE
      from @RS R

    -- TYPECODE from revuenuestream is REVENUE.TRANSACTIONTYPECODE of the original revenue, 1 for pledge and 2 for recurring gift

      if @TRANSACTIONTYPECODE in (1, 2)  --pledge and recurring gift

      begin
        --locate from "source" revenue

        select
                @SOURCECODE = REVENUE.SOURCECODE,
                @MAILINGID = REVENUE.MAILINGID,
                @APPEALID = REVENUE.APPEALID
            from 
                dbo.REVENUE 
            where REVENUE.ID = @APPLICATIONID          
      end
      --If appeal found default if one is not present

      if (@SOURCECODE is not null and @SOURCECODE <> '') or @MAILINGID is not null or @APPEALID is not null
          update dbo.REVENUE
          set 
              SOURCECODE = coalesce(nullif(SOURCECODE, ''), @SOURCECODE),
              MAILINGID = coalesce(MAILINGID, @MAILINGID),
              APPEALID = coalesce(APPEALID, @APPEALID),
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CHANGEDATE
          from dbo.REVENUE
              where 
                  ID = @REVENUEID 
                  and (
                      SOURCECODE is null 
                      or SOURCECODE = ''
                      or MAILINGID is null
                      or APPEALID is null
                  )

  end