USP_DATAFORMTEMPLATE_ADD_INVOICE

The save procedure used by the add dataform template "Invoice Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@VENDORID	uniqueidentifier	IN	Vendor
@NUMBER	nvarchar(60)	IN	Invoice number
@DATEDUE	datetime	IN	Due date
@PURCHASEORDERID	nvarchar(20)	IN	PO number
@AMOUNT	money	IN	Invoice amount
@DATE	datetime	IN	Invoice date
@REMITADDRESSID	uniqueidentifier	IN	Remit to address
@DISCOUNTPERCENT	numeric(20, 4)	IN	Discount percent
@DISCOUNTAMOUNT	money	IN	Discount amount
@DISCOUNTEXPIRATIONDATE	datetime	IN	Discount expiration date
@SEPARATEPAYMENT	bit	IN	Create a separate disbursement for this invoice
@PAYMENTMETHODCODE	tinyint	IN	Payment method
@POSTDATE	datetime	IN	Post date
@POSTSTATUSCODE	tinyint	IN	Post status
@BANKACCOUNTID	uniqueidentifier	IN	Bank account
@FINANCIALTRANSACTION1099DISTRIBUTIONS	xml	IN	1099 distribution
@DESCRIPTION	nvarchar(100)	IN	Invoice description
@LINEITEMS	xml	IN	Line items

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_INVOICE
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
    @VENDORID uniqueidentifier = null,
    @NUMBER nvarchar(60) = '',
  @DATEDUE datetime = null,
  @PURCHASEORDERID nvarchar(20) = '',
  @AMOUNT money = 0,
    @DATE datetime = null,
  @REMITADDRESSID uniqueidentifier = null,
  @DISCOUNTPERCENT numeric(20,4) = 0,
  @DISCOUNTAMOUNT money = 0,
  @DISCOUNTEXPIRATIONDATE datetime = null,
  @SEPARATEPAYMENT bit = 0,
  @PAYMENTMETHODCODE tinyint = 1,
  @POSTDATE datetime = null,
  @POSTSTATUSCODE tinyint = 1,
  @BANKACCOUNTID uniqueidentifier = null,
  @FINANCIALTRANSACTION1099DISTRIBUTIONS xml = null,
    @DESCRIPTION  nvarchar(100) = ''
    ,@LINEITEMS xml = null
)
as

set nocount on;

if @ID is null
    set @ID = newid();

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

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

begin try
  -- Record ID 101 = Invoice

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

  exec dbo.USP_FINANCIALTRANSACTION_ADD @ID, @CHANGEAGENTID, @VENDORID, @NUMBER, 101, @AMOUNT, @DATE, @POSTDATE, @POSTSTATUSCODE, @DESCRIPTION, @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;

  -- Validate that the vendor allows invoices to be created

  if not exists(
    select V.ID
    from dbo.VENDOR V
    where V.ID = @VENDORID and V.ALLOWTOCREATEINVOICES = 1)
  begin
    raiserror('ERR_VENDOR_NOT_ALLOW_INVOICES', 13, 1);
  end

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

    -- handle inserting the data

    insert into dbo.INVOICE (
    ID, 
    DATEDUE, 
    PURCHASEORDERID, 
    REMITADDRESSID, 
    DISCOUNTEXPIRATIONDATE, 
    DISCOUNTPERCENT,
    DISCOUNTAMOUNT,
    SEPARATEPAYMENT, 
    --PAYMENTMETHODCODE,

    BANKACCOUNTID,
    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
  values (
    @ID, 
    @DATEDUE, 
    @PURCHASEORDERID, 
    @REMITADDRESSID, 
    @DISCOUNTEXPIRATIONDATE, 
    @DISCOUNTPERCENT,
    @DISCOUNTAMOUNT,
    @SEPARATEPAYMENT, 
    --@PAYMENTMETHODCODE,

    @BANKACCOUNTID,
    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

  -- We cannot eliminate date due from the invoice itself, since it is in the document (from the vendor) and we need to record it

  -- regardless of schedule

  exec USP_FINANCIALTRANSACTIONSCHEDULE_ADD null, @CHANGEAGENTID, @ID, @DATEDUE, @AMOUNT

  if not @FINANCIALTRANSACTION1099DISTRIBUTIONS is null
    exec dbo.USP_FINANCIALTRANSACTION_1099DISTRIBUTION_UPDATEFROMXML @ID, @VENDORID, @FINANCIALTRANSACTION1099DISTRIBUTIONS, @CHANGEAGENTID, @CURRENTDATE;
  else if exists(select * from dbo.VENDOR1099DISTRIBUTION where VENDOR1099SETTINGID = @VENDORID)
    insert into dbo.FINANCIALTRANSACTION1099DISTRIBUTION (
      ID,
      FINANCIALTRANSACTIONID,
      BOXNUMBER1099ID,
      STATEID,
      [PERCENT],
      AMOUNT,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select 
      newid(), 
      @ID, 
      BOXNUMBER1099ID, 
      STATEID, 
      [PERCENT],
      ((@AMOUNT * [PERCENT]) / 100),
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from dbo.VENDOR1099DISTRIBUTION 
    where VENDOR1099SETTINGID = @VENDORID;
end try

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

return 0;