USP_DATAFORMTEMPLATE_EDIT_REVENUE_INSTALLMENTS_3

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUE_INSTALLMENTS_3 (
  @ID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@AMOUNT money
  ,@RESCHEDULEBALANCE money
  ,@FREQUENCYCODE tinyint
  ,@NUMBEROFINSTALLMENTS int
  ,@STARTDATE datetime
  ,@INSTALLMENTS xml
  ,@ADJPAYMENT_DATE datetime
  ,@ADJPAYMENT_POSTDATE datetime
  ,@ADJPAYMENT_REASONCODEID uniqueidentifier
  ,@ADJPAYMENT_DETAILS nvarchar(255)
  ,@INSTALLMENTAMOUNT money  
  )
as
begin
  set nocount on;

  declare @CURRENTDATE datetime;
  declare @SUM money;
  declare @INSTALLMENTCOUNT int;
  declare @INSTALLMENTSPLITS xml;    
  begin try
    if @CHANGEAGENTID is null
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    set @CURRENTDATE = GetDate();

    select @SUM = sum(AMOUNT)
      ,@INSTALLMENTCOUNT = count(AMOUNT)
    from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS);

    if @INSTALLMENTCOUNT = 0
      raiserror (
          'INSTALLMENTCOUNT'
          ,13
          ,1
          );

    if @NUMBEROFINSTALLMENTS > 150
      raiserror (
          'BBERR_NUMINSTALLMENTS'
          ,13
          ,1
          );

    if @SUM <> @AMOUNT
      raiserror (
          'INSTALLMENTSUM'
          ,13
          ,1
          );

    if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
      raiserror (
          'Installment dates are out of sequence.'
          ,13
          ,1
          );


    declare @BASECURRENCYID uniqueidentifier;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @ISMEMBERSHIPPLEDGE bit;
    declare @REVENUEDATE datetime;

    select @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
      ,@ORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID
      ,@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
      ,@BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
      ,@ISMEMBERSHIPPLEDGE = case when FINANCIALTRANSACTION.TYPECODE = 15 then 1 else 0 end
      ,@REVENUEDATE = cast(FINANCIALTRANSACTION.date as datetime)
    from dbo.FINANCIALTRANSACTION
    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 FINANCIALTRANSACTION.ID = @ID
      and FINANCIALTRANSACTION.DELETEDON is null

    if @FREQUENCYCODE = 4 
      exec dbo.USP_REVENUEINSTALLMENT_VALIDATEINSTALLMENTS @INSTALLMENTS, @REVENUEDATE, @AMOUNT, @ISMEMBERSHIPPLEDGE;

    update dbo.REVENUESCHEDULE
    set FREQUENCYCODE = @FREQUENCYCODE
      ,NUMBEROFINSTALLMENTS = @INSTALLMENTCOUNT
      ,STARTDATE = @STARTDATE
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
    where ID = @ID;



    exec dbo.USP_PLEDGE_UPDATEINSTALLMENT3 @ID
      ,@INSTALLMENTS
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@ADJPAYMENT_DATE
      ,@ADJPAYMENT_POSTDATE
      ,@ADJPAYMENT_REASONCODEID
      ,@ADJPAYMENT_DETAILS
      ,@BASECURRENCYID
      ,@ORGANIZATIONEXCHANGERATEID
      ,@TRANSACTIONCURRENCYID
      ,@BASEEXCHANGERATEID

    exec dbo.USP_PLEDGE_VALIDATE @ID;

    merge into dbo.PLEDGEINSTALLMENTOPTION as Target
    using (select ID  from dbo.PLEDGEINSTALLMENTOPTION where ID  = @ID)  as Source
    on (Target.ID = Source.ID)
    when matched then
      update set Target.INSTALLMENTAMOUNT = @INSTALLMENTAMOUNT,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    when not matched by Target then
        insert 
            (ID
            ,INSTALLMENTAMOUNT
            ,SPLITSCHEDULEOPTIONCODE
            ,ADDEDBYID
            ,CHANGEDBYID
            ,DATEADDED
            ,DATECHANGED)
        values (@ID
            ,case @FREQUENCYCODE when 4 then null else @INSTALLMENTAMOUNT end
            ,0 --@INSTALLMENTSPLITSCHEDULEOPTIONCODE
            ,@CHANGEAGENTID
            ,@CHANGEAGENTID
            ,@CURRENTDATE
            ,@CURRENTDATE);      


  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;

    return 1;
  end catch

  return 0;
end