USP_DATAFORMTEMPLATE_VIEW_WPBUSINESSOWNERSHIP2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_WPBUSINESSOWNERSHIP2 (
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CDATE datetime = null output,
    @CVALUE money = null output,
    @CNOTES nvarchar(1024) = null output,
    @FULLNAME nvarchar(100) = null output,
    @TITLE nvarchar(100) = null output,
    @LINE1 nvarchar(150) = null output,
    @CITY nvarchar(30) = null output,
    @STATE nvarchar(100) = null output,
    @ZIP nvarchar(10) = null output,
    @BIO nvarchar(1500) = null output,
    @HELD nvarchar(100) = null output,
    @COMPANY nvarchar(100) = null output,
    @PHONE nvarchar(20) = null output,
    @LINE_OF_BUSINESS_DESCRIPTION nvarchar(50) = null output,
    @SALES_VOLUME money = null output,
    @YEAR_STARTED dbo.UDT_YEAR = null output,
    @EMPLOYEES_TOTAL int = null output,
    @SIC_CODE nvarchar(4) = null output,
    @DUNS nvarchar(10) = null output,
    @PERCENTAGE decimal(20,4) = null output,
    @VALUATION money = null output,
    @SPOUSEIND tinyint = null output,
    @OWNERSHIPVALUE money = null output,
    @SICNAME nvarchar(100) = null output,
    @MAILINGLINE1 nvarchar(150) = null output,
    @MAILINGCITY nvarchar(30) = null output,
    @MAILINGSTATE nvarchar(100) = null output,
    @MAILINGZIP nvarchar(10) = null output,
    @HOMELINE1 nvarchar(150) = null output,
    @HOMECITY nvarchar(30) = null output,
    @HOMESTATE nvarchar(100) = null output,
    @HOMEZIP nvarchar(10) = null output,
    @YEAROFBIRTH smallint = null output,
    @GENDER nvarchar(10) = null output,
    @ADDRESS nvarchar(300) = null output,
    @MAILINGADDRESS nvarchar(300) = null output,
    @HOMEADDRESS nvarchar(300) = null output,
    @SOURCE nvarchar(100) = null output,
    @RATIO decimal(28, 4) = null output,
    @OWNERSHIP xml = null output,
  @SIC_CODE1 nvarchar(4) = null output,
  @SIC_CODE2 nvarchar(4) = null output,
  @SIC_CODE3 nvarchar(4) = null output,
  @SIC_CODE4 nvarchar(4) = null output,
  @SIC_CODE5 nvarchar(4) = null output,
  @SIC_CODE6 nvarchar(4) = null output,
  @RATIO1 decimal(28, 4) = null output,
  @RATIO2 decimal(28, 4) = null output,
  @RATIO3 decimal(28, 4) = null output,
  @RATIO4 decimal(28, 4) = null output,
  @RATIO5 decimal(28, 4) = null output,
  @RATIO6 decimal(28, 4) = null output,
    @MATCHCODE nvarchar(100) = null output
) as begin

    set nocount on;

    set @DATALOADED = 0;

    select 
        @DATALOADED = 1,
        @CDATE = BO.CDATE,
        @CVALUE = BO.CVALUE,
        @CNOTES = BO.CNOTES,
        @FULLNAME = BO.FULLNAME,
        @TITLE = BO.TITLE,
        @LINE1 = BO.LINE1,
        @CITY = BO.CITY,
        @STATE = BO.STATE,
        @ZIP = BO.ZIP,
        @BIO = BO.BIO,
        @HELD = BO.HELD,
        @COMPANY = BO.COMPANY,
        @PHONE = BO.PHONE,
        @LINE_OF_BUSINESS_DESCRIPTION = BO.LINE_OF_BUSINESS_DESCRIPTION,
        @SALES_VOLUME = BO.SALES_VOLUME,
        @YEAR_STARTED = BO.YEAR_STARTED,
        @EMPLOYEES_TOTAL = BO.EMPLOYEES_TOTAL,
        @SIC_CODE = BO.SIC_CODE,
        @DUNS = coalesce(RBO.DUNS, ''),
        @PERCENTAGE = BO.PERCENTAGE,
        @VALUATION = BO.VALUATION,
        @SPOUSEIND = BO.SPOUSEIND,
        @SICNAME = BO.SICNAME,
        @MAILINGLINE1 = BO.MAILINGLINE1,
        @MAILINGCITY = BO.MAILINGCITY,
        @MAILINGSTATE = BO.MAILINGSTATE,
        @MAILINGZIP = BO.MAILINGZIP,
        @HOMELINE1 = BO.HOMELINE1,
        @HOMECITY = BO.HOMECITY,
        @HOMESTATE = BO.HOMESTATE,
        @HOMEZIP = BO.HOMEZIP,
        @YEAROFBIRTH = BO.YEAROFBIRTH,
        @GENDER = BO.GENDER,
        @ADDRESS = dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(@LINE1, '', @CITY, @STATE, @ZIP),
        @MAILINGADDRESS = dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(@MAILINGLINE1, '', @MAILINGCITY, @MAILINGSTATE, @MAILINGZIP),
        @HOMEADDRESS = dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(@HOMELINE1, '', @HOMECITY, @HOMESTATE, @HOMEZIP),
        @SOURCE = BO.SOURCE,
        @RATIO = BO.RATIO,
        @OWNERSHIP = dbo.UFN_BUSINESSOWNERSHIP_GETOWNERSHIP_TOITEMLISTXML(@ID),
        @OWNERSHIPVALUE = OWNERSHIPVALUE,
    @SIC_CODE1 = BO.SIC_CODE1,
    @SIC_CODE2 = BO.SIC_CODE2,
    @SIC_CODE3 = BO.SIC_CODE3,
    @SIC_CODE4 = BO.SIC_CODE4,
    @SIC_CODE5 = BO.SIC_CODE5,
    @SIC_CODE6 = BO.SIC_CODE6,
    @RATIO1 = BO.RATIO1,
    @RATIO2 = BO.RATIO2,
    @RATIO3 = BO.RATIO3,
    @RATIO4 = BO.RATIO4,
    @RATIO5 = BO.RATIO5,
    @RATIO6 = BO.RATIO6,
        @MATCHCODE = MC + ' - ' + dbo.UFN_WPMATCHCODE_DESCRIPTION(MC)
    from
        dbo.WPBUSINESSOWNERSHIP BO
        left join dbo.WPRELATIONSHIP_BO RBO
            on BO.WPRELATIONSHIP_BO_ID = RBO.ID
    where
        BO.ID=@ID;

    return 0

end