USP_DATAFORMTEMPLATE_EDIT_INVOICE

The save procedure used by the edit dataform template "Invoice 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.
@NUMBER	nvarchar(60)	IN	Invoice number
@VENDORID	uniqueidentifier	IN	Vendor
@AMOUNT	money	IN	Invoice amount
@DATE	datetime	IN	Invoice date
@PURCHASEORDERID	nvarchar(20)	IN	PO number
@DATEDUE	datetime	IN	Due date
@POSTSTATUSCODE	tinyint	IN	Post status
@POSTDATE	datetime	IN	Post date
@DISCOUNTPERCENT	numeric(20, 4)	IN	Discount percent
@DISCOUNTAMOUNT	money	IN	Discount amount
@DISCOUNTEXPIRATIONDATE	datetime	IN	Discount expiration date
@PAYMENTMETHODCODE	tinyint	IN	Payment method
@BANKACCOUNTID	uniqueidentifier	IN	Bank account
@REMITADDRESSID	uniqueidentifier	IN	Remit to address
@SEPARATEPAYMENT	bit	IN	Create a separate disbursement for this invoice
@FREQUENCYCODE	smallint	IN	Frequency
@SCHEDULESTARTDATE	date	IN	Starting on
@NUMBEROFINSTALLMENTS	int	IN	No. installments
@INVOICESCHEDULES	xml	IN	Invoice schedules
@HASSCHEDULE	bit	IN	Has Schedule
@DESCRIPTION	nvarchar(100)	IN	Invoice description
@LINEITEMS	xml	IN	Line items

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_INVOICE (
    @ID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier = null
    ,@NUMBER nvarchar(60)
    ,@VENDORID uniqueidentifier
    ,@AMOUNT money
    ,@DATE datetime
    ,@PURCHASEORDERID nvarchar(20)
    ,@DATEDUE datetime
    ,@POSTSTATUSCODE tinyint
    ,@POSTDATE datetime
    ,@DISCOUNTPERCENT numeric(20,4)
    ,@DISCOUNTAMOUNT money
    ,@DISCOUNTEXPIRATIONDATE datetime
    ,@PAYMENTMETHODCODE tinyint
    ,@BANKACCOUNTID uniqueidentifier
    ,@REMITADDRESSID uniqueidentifier
    ,@SEPARATEPAYMENT bit
  ,@FREQUENCYCODE smallint
  ,@SCHEDULESTARTDATE date 
  ,@NUMBEROFINSTALLMENTS integer
  ,@INVOICESCHEDULES xml
  ,@HASSCHEDULE bit
    ,@DESCRIPTION nvarchar(100)
    ,@LINEITEMS xml
)
as

    set nocount on;

    declare @CURRENTDATE datetime;

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

        set @CURRENTDATE = getdate();

    if @POSTSTATUSCODE = 3 and @POSTDATE is not null
      set @POSTDATE = null;

    if @AMOUNT <= 0 
      raiserror('ERR_NOT_ALLOW_ZEROAMOUNT', 13, 1);   

        exec dbo.USP_FINANCIALTRANSACTION_EDIT @ID, @CHANGEAGENTID, @VENDORID, @NUMBER, @AMOUNT, @DATE, @POSTDATE, @POSTSTATUSCODE, @DESCRIPTION;
    exec dbo.USP_FINANCIALTRANSACTIONSCHEDULE_UPDATE @ID, @CHANGEAGENTID, @AMOUNT, @DATEDUE;

        if @LINEITEMS is null
            raiserror('ERR_FINANCIALTRANSACTION_LINEITEM_TOTALAMOUNT', 13,1);

    exec dbo.USP_FINANCIALTRANSACTIOLINEITEM_EDIT @ID, @CHANGEAGENTID, @LINEITEMS;

        -- Recalc from DISCOUNTTOTAL to get DISCOUNTAMOUNT or DISCOUNTPERCENT if one or the other is missing
        -- We don't do both because we have to trust what is given due to mathematical value constraints.
        if @DISCOUNTAMOUNT = 0 and @DISCOUNTPERCENT > 0
            set @DISCOUNTAMOUNT = @AMOUNT / @DISCOUNTPERCENT;
        else if @DISCOUNTPERCENT = 0 and @DISCOUNTAMOUNT > 0 and @AMOUNT > 0
            set @DISCOUNTPERCENT = (@DISCOUNTAMOUNT / @AMOUNT) * 100;

    exec dbo.USP_INVOICE_VALIDATE @ID,@DATEDUE,@REMITADDRESSID,@DISCOUNTPERCENT,@DISCOUNTAMOUNT,@DISCOUNTEXPIRATIONDATE,@PAYMENTMETHODCODE,@SEPARATEPAYMENT,@BANKACCOUNTID 

        update dbo.INVOICE set
            DATEDUE = @DATEDUE,
            PURCHASEORDERID = @PURCHASEORDERID,
            REMITADDRESSID = @REMITADDRESSID,
            DISCOUNTAMOUNT = @DISCOUNTAMOUNT,
            DISCOUNTEXPIRATIONDATE = @DISCOUNTEXPIRATIONDATE,
            DISCOUNTPERCENT = @DISCOUNTPERCENT,
            SEPARATEPAYMENT = @SEPARATEPAYMENT,
            BANKACCOUNTID = @BANKACCOUNTID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID;

    if exists (select ID from dbo.FINANCIALTRANSACTION1099DISTRIBUTION where FINANCIALTRANSACTIONID = @ID)
      exec dbo.USP_UPDATE1099DISTRIBUTION @ID, @AMOUNT;

    if @HASSCHEDULE = 1
      exec dbo.USP_DATAFORMTEMPLATE_EDIT_UNPAIDINVOICESCHEDULE @ID,@CHANGEAGENTID,@FREQUENCYCODE,@SCHEDULESTARTDATE,@NUMBEROFINSTALLMENTS,@INVOICESCHEDULES;

    if exists (
      select FTS.FINANCIALTRANSACTIONID
        from dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
      where 
        FTS.FINANCIALTRANSACTIONID = @ID and FTS.DELETED = 0
      group by
        FTS.FINANCIALTRANSACTIONID
      having
        sum(AMOUNT) <> @AMOUNT )
      raiserror('FT_SCHEDULE_AMOUNT_MUST_MATCH_INVOICEAMOUNT', 16, 1);

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

return 0;