USP_DATALIST_EDUCATIONALINSTITUTIONAFFILIATED

create procedure dbo.USP_DATALIST_EDUCATIONALINSTITUTIONAFFILIATED
as
    set nocount on;

    select
                        E.ID,
                        E.FICECODE,
                        E.NAME,
                        E.CITY,
                        dbo.UFN_STATE_GETABBREVIATION(E.STATEID),
                        dbo.UFN_COUNTRY_GETDESCRIPTION(E.COUNTRYID),
            ADDRESS.ADDRESSBLOCK, 
            ADDRESS.POSTCODE,
            PHONE.NUMBER,
            B.ID as BBISID
                    from
                        dbo.EDUCATIONALINSTITUTION as E
              inner join dbo.BBNCCODETABLEIDMAP B on E.ID = B.TABLEENTRYID and B.CODETABLECATALOGID = '0E885882-A716-49B8-8F2F-A6BAE5D220D8'
              left outer join dbo.ADDRESS on E.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
              left outer join dbo.PHONE on E.ID = PHONE.CONSTITUENTID and PHONE.ISPRIMARY = 1
                    where E.ISAFFILIATED = 1
                    order by
                        E.NAME;