USP_DATAFORMTEMPLATE_EDIT_UNPAIDINVOICESCHEDULE

The save procedure used by the edit dataform template "Unpaid Invoice Schedule Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@FREQUENCYCODE	smallint	IN	Frequency
@STARTDATE	date	IN	Starting on
@NUMBEROFINSTALLMENTS	int	IN	Number of installments
@INVOICESCHEDULES	xml	IN	Invoice schedules

Definition

Copy




CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_UNPAIDINVOICESCHEDULE (
    @ID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier = null
  ,@FREQUENCYCODE smallint
  ,@STARTDATE date 
  ,@NUMBEROFINSTALLMENTS integer
  ,@INVOICESCHEDULES xml
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

begin try
  if exists (
    select 
      *    
    from 
      dbo.INVOICESCHEDULEINFORMATION as ISI
    where 
      ISI.ID = @ID
  )
    update dbo.INVOICESCHEDULEINFORMATION       
        set FREQUENCYCODE = @FREQUENCYCODE
        ,STARTDATE = @STARTDATE
        ,NUMBEROFINSTALLMENTS = @NUMBEROFINSTALLMENTS
        -- boilerplate
        ,CHANGEDBYID = @CHANGEAGENTID
        ,DATECHANGED = @CURRENTDATE
      where ID=@ID;
  else
      insert into dbo.INVOICESCHEDULEINFORMATION
          (
        ID
        ,FREQUENCYCODE
        ,STARTDATE
        ,NUMBEROFINSTALLMENTS
        -- boilerplate
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
      )
      values
          (
        @ID
        ,@FREQUENCYCODE
        ,@STARTDATE
        ,@NUMBEROFINSTALLMENTS
        -- boilerplate
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE
      )
  ;    
  exec dbo.USP_INVOICE_SCHEDULES_UPDATEFROMXML @ID, @INVOICESCHEDULES, @CHANGEAGENTID;

    if exists(
        select 
          *
        from                         
              dbo.FINANCIALTRANSACTION as FT 
            left outer join dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
                on FT.ID = FTS.FINANCIALTRANSACTIONID
    where FT.ID = @ID and FTS.DELETED = 0
        group by FT.ID, FT.TRANSACTIONAMOUNT
        having              
              COALESCE(SUM(FTS.AMOUNT),FT.TRANSACTIONAMOUNT)<>FT.TRANSACTIONAMOUNT              
    )   
    raiserror('FT_SCHEDULE_AMOUNT_MUST_MATCH_INVOICEAMOUNT' , 16, 1);     

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0