USP_DATAFORMTEMPLATE_VIEW_INVOICE_INFORMATION

The load procedure used by the view dataform template "Invoice Information View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@DESCRIPTION	nvarchar(100)	INOUT	Description
@BANKACCOUNT	nvarchar(100)	INOUT	Bank account
@REMITTOADDRESS	nvarchar(150)	INOUT	Remit to address
@SEPARATEPAYMENT	bit	INOUT	Create a separate disbursement for this invoice

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_INVOICE_INFORMATION
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
  @DESCRIPTION nvarchar(100) = null output,
    @BANKACCOUNT nvarchar(100) = null output,
    @REMITTOADDRESS nvarchar(150) = null output,
    @SEPARATEPAYMENT bit = null output
)
as
    set nocount on;

    declare @CONSTITUENTID as uniqueidentifier;
  declare @PASSVAL nvarchar(37);

  create table #temp (
    VALUE uniqueidentifier
    ,LABEL nvarchar(400) collate DATABASE_DEFAULT
  );

    set @DATALOADED = 0;

  select 
    @CONSTITUENTID = CONSTITUENTID
  from 
    dbo.FINANCIALTRANSACTION 
  where
    ID = @ID;

  set @PASSVAL = convert(nvarchar(36), @CONSTITUENTID) + '1';

  insert into #temp
    exec dbo.USP_SIMPLEDATALIST_CONSTITUENTLETTERADDRESS @PASSVAL;

    select @DATALOADED = 1,
       @DESCRIPTION = FT.DESCRIPTION,
           @BANKACCOUNT = BANKACCOUNT.ACCOUNTNAME,
           @REMITTOADDRESS = #temp.LABEL,
           @SEPARATEPAYMENT = SEPARATEPAYMENT
    from dbo.INVOICE
    left outer join dbo.FINANCIALTRANSACTION as FT on INVOICE.ID = FT.ID
    left outer join dbo.BANKACCOUNT on INVOICE.BANKACCOUNTID = BANKACCOUNT.ID
    left outer join #temp on #temp.VALUE = INVOICE.REMITADDRESSID
    where INVOICE.ID = @ID;

  drop table #temp;

    return 0;