USP_PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAIL_SELECT

            create procedure dbo.USP_PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAIL_SELECT(
                @ID uniqueidentifier
            )
            as
            begin
                select 
                    PISRBO.JOBID,
                    PISRBO.DUNSNUM,
                    case 
                        when PISRBOD.PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPID is null then 0 
                        else 1 
                    end HASDETAILRECORD,
                    dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(PISRBO.ADDRESS, '', PISRBO.CITY, PISRBO.STATE, PISRBO.ZIP) ADDRESS,
                    PISRBO.ADDRESS as ADDRESSBLOCK,
                    PISRBO.CITY,
                    PISRBO.STATE,
                    PISRBO.ZIP,
                    '' as ZIP4,
                    coalesce(nullif(dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDNAMEFULL('', PISRBO.FIRSTNAME, PISRBO.MIDDLENAME, PISRBO.LASTNAME, PISRBO.SUFFIX), ''), PISRBO.FULLNAME) DISPLAYNAME,
                    PISRBO.FIRSTNAME,
                    PISRBO.MIDDLENAME,
                    PISRBO.LASTNAME,
                    PISRBO.SUFFIX,
                    PISRBO.FULLNAME,
                    PISRBO.COMPANY,
                    PISRBO.PHONE,
                    PISRBOD.PARENT,
                    PISRBOD.SECNAME,
                    PISRBOD.DESCRIPTION,
                    dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(PISRBOD.MAILINGADDRESS, '', PISRBOD.MAILINGCITY, PISRBOD.MAILINGSTATE, PISRBOD.MAILINGZIP) MAILINGADDRESS,
                    PISRBOD.MAILINGADDRESS as MAILINGADDRESSBLOCK,
                    PISRBOD.MAILINGCITY,
                    PISRBOD.MAILINGSTATE,
                    PISRBOD.MAILINGZIP,
                    PISRBOD.MAILINGZIP4,
                    PISRBOD.PRIMARYSICCODE,
                    PISRBOD.YEARSTARTED,
                    PISRBOD.NUMEMPLOYEES,
                    PISRBOD.HELDCODE,
                    PISRBOD.SALES,
                    PISRBOD.RATIO,
                    PISRBOD.VALUATION
                from 
                    dbo.PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIP PISRBO
                left join dbo.PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPDETAIL PISRBOD
                    on PISRBO.ID = PISRBOD.PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIPID
                where
                    PISRBO.ID = @ID

                return 0;
            end