USP_DATAFORMTEMPLATE_ADD_INVOICE_GL

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

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_GL
(
  @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 @FINANCIALTRANSACTION1099DISTRIBUTIONS is not 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;