USP_DATAFORMTEMPLATE_VIEW_VENDOR1099

The load procedure used by the view dataform template "Vendor 1099 Setting 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.
@CHECK1099BOX9	bit	INOUT	Mark 1099 box 9 for direct sales of consumer products
@CHECK10992TIN	bit	INOUT	Mark the 1099 2nd TIN notification box
@NAMEFIRSTLINE	nvarchar(40)	INOUT	Name
@NAMESECONDLINE	nvarchar(40)	INOUT	Name - Line 2
@FORMATTEDADDRESS	nvarchar(300)	INOUT	Address
@DISTRIBUTION1099	nvarchar(4000)	INOUT	1099 box number distribution

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_VENDOR1099
(
  @ID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@CHECK1099BOX9 bit = null output
  ,@CHECK10992TIN bit = null output
  ,@NAMEFIRSTLINE nvarchar(40) = null output
  ,@NAMESECONDLINE nvarchar(40) = null output
  ,@FORMATTEDADDRESS nvarchar(300) = null output
  ,@DISTRIBUTION1099 nvarchar(4000) = null output
)
as
  set nocount on;
  declare @NAME1 as nvarchar(300)
  declare @NAME2 as nvarchar(300)
  -- be sure to set this, in case the select returns no rows

  set @DATALOADED = 0;

  -- populate the output parameters, which correspond to fields on the form.  Note that

  -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

  -- will display a "no data loaded" message.

  select @DATALOADED = 1
    ,@CHECK1099BOX9 = VS.CHECK1099BOX9
    ,@CHECK10992TIN = VS.CHECK10992TIN
    ,@NAME1 = coalesce(A1.NAME, C.NAME)
    ,@NAME2 = coalesce(A2.NAME, '')
    ,@FORMATTEDADDRESS = dbo.UFN_BUILDFULLADDRESS( null, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID)
    ,@DISTRIBUTION1099 = substring((SELECT 
                                      ';' + (B.BOXNUMBER + ' - ' + B.DESCRIPTION) + ', ' + coalesce(S.ABBREVIATION + ', ', '') + cast(VD.[PERCENT] as nvarchar) + '%'
                                    from dbo.VENDOR1099DISTRIBUTION VD
                                      left join [STATE] S on S.ID = VD.STATEID
                                      left outer join dbo.FINANCIALTRANSACTION1099BOXNUMBER as B on B.ID = VD.BOXNUMBER1099ID
                                    where VD.VENDOR1099SETTINGID = VS.ID
                                    for XML PATH( '' )
                                   ), 2, 4000 )
  from dbo.VENDOR1099SETTING VS
    inner join dbo.VENDOR V on VS.ID = V.ID
    inner join dbo.CONSTITUENT C on V.ID = C.ID
    left outer join dbo.ALIAS A1 on A1.ID = VS.NAMEFIRSTLINEALIASTYPECODEID
    left outer join dbo.ALIAS A2 on A2.ID = VS.NAMESECONDLINEALIASTYPECODEID
    left outer join dbo.ADDRESS as A on (A.ID = VS.ADDRESSID and VS.ADDRESSID is not null) or (VS.ADDRESSID is null and A.ISPRIMARY = 1 and A.CONSTITUENTID = C.ID)
  where VS.ID = @ID

  if LEN(@NAME1) > 40 AND LEN(@NAME2) = 0
      begin
          declare @Temp as nvarchar(40)
          declare @LastIndex as int
          set @Temp = @NAME1
          if CHARINDEX(' ', @NAME1) > 0 AND CHARINDEX(' ', @NAME1) < 40
              set @LastIndex = 42 - CHARINDEX(' ',REVERSE(@Temp),0)
          else
              set @LastIndex = 41
          set @NAMEFIRSTLINE = RTRIM(SUBSTRING(@NAME1, 0, @LastIndex))
          set @NAMESECONDLINE = LTRIM(SUBSTRING(@NAME1, @LastIndex, 300))
      end
  else
      begin
          set @NAMEFIRSTLINE = @NAME1
          set @NAMESECONDLINE = @NAME2
      end

  if @DATALOADED = 0
  begin
      set @DATALOADED = 1
      set @CHECK1099BOX9 = 0
      set @CHECK10992TIN = 0
  end

  return 0;