USP_DATAFORMTEMPLATE_EDIT_FAF_REVENUE_RECOGNITION

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_FAF_REVENUE_RECOGNITION  (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier 
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime,
          @IsPaidPendingGift bit = 0,
          @RevenueSplitID uniqueidentifier = null;

    set @CURRENTDATE = getdate()

  begin try

   select @IsPaidPendingGift = 1 from dbo.FINANCIALTRANSACTION FT 
   join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
   join dbo.REVENUESPLIT_EXT REVSXT on FTLI.ID = REVSXT.ID 
   where FTLI.ID = @ID and FT.TYPECODE = 0 and REVSXT.APPLICATIONCODE = 17

    if @CONSTITUENTID is null
      delete from dbo.REVENUERECOGNITION where REVENUESPLITID=@ID
    else
      merge into dbo.REVENUERECOGNITION as Target
        using 
        (
            select FINANCIALTRANSACTIONLINEITEM.ID, FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT as AMOUNT, isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID,  FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT as ORGANIZATIONAMOUNT, FINANCIALTRANSACTION.ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID
            from dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
            where FINANCIALTRANSACTIONLINEITEM.ID=@ID
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                and FINANCIALTRANSACTION.DELETEDON is null
        ) as source on (target.REVENUESPLITID = source.ID)
        when matched then
            update set
                CONSTITUENTID = @CONSTITUENTID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
        when not matched then
             insert (ID, REVENUESPLITID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID)
             values (newid(), @ID, @CONSTITUENTID, source.AMOUNT, @CURRENTDATE, NULL, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, source.BASECURRENCYID, source.ORGANIZATIONAMOUNT, source.ORGANIZATIONEXCHANGERATEID);

    if @IsPaidPendingGift = 1
    begin
      if @CONSTITUENTID is null
      begin
        SELECT @RevenueSplitID=FTLI.ID
        from dbo.FINANCIALTRANSACTIONLINEITEM RS
        join INSTALLMENTSPLITPAYMENT ISP on RS.ID = ISP.PAYMENTID
        join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on ISP.PLEDGEID = FTLI.FINANCIALTRANSACTIONID
        WHERE RS.ID = @ID
            and RS.DELETEDON is null
            and RS.TYPECODE <> 1
        if not @RevenueSplitID is null
          delete from dbo.REVENUERECOGNITION where REVENUESPLITID=@RevenueSplitID
      end
      else
        merge into dbo.REVENUERECOGNITION as Target
          using
          (
            SELECT FTLI.ID, FTLI.TRANSACTIONAMOUNT, isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID) as BASECURRENCYID, FTLI.ORGAMOUNT, R.ORGEXCHANGERATEID ORGANIZATIONEXCHANGERATEID
            from dbo.FINANCIALTRANSACTIONLINEITEM RS
            join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
            join dbo.REVENUE_EXT on R.ID = REVENUE_EXT.ID
            join dbo.PDACCOUNTSYSTEM on R.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
            join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
            join INSTALLMENTSPLITPAYMENT ISP on RS.ID = ISP.PAYMENTID
            join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on ISP.PLEDGEID = FTLI.FINANCIALTRANSACTIONID
            WHERE RS.ID = @ID     
                and RS.DELETEDON is null
                and RS.TYPECODE <> 1
                and R.DELETEDON is null
          ) as source on (target.REVENUESPLITID = source.ID)
          when matched then
            update set
                  CONSTITUENTID = @CONSTITUENTID,
                  CHANGEDBYID = @CHANGEAGENTID,
                  DATECHANGED = @CURRENTDATE
          when not matched then
            insert (ID, REVENUESPLITID, CONSTITUENTID, AMOUNT, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID)
            values (newid(), source.ID, @CONSTITUENTID, source.TRANSACTIONAMOUNT, @CURRENTDATE, NULL, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, source.BASECURRENCYID, source.ORGAMOUNT, source.ORGANIZATIONEXCHANGERATEID);
    end

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;