SELECT UPPER(i.db_schema) ||'_'|| g.gang_id || '_' || gn.gang_name_id AS id , UPPER(i.db_schema) AS county , g.gang_id AS gang_id , gn.NAME AS gang_name , gn.gang_name_id as gang_name_id , g.level_code AS level_code , g.gang_type_code AS gang_type_code , gtc.description AS gang_type_desc , g.indx_sec_level_code AS indx_sec_level_code , g.indx_sec_level_id AS indx_sec_level_id , g.indx_sec_level_agency AS indx_sec_level_agency , g.active_subscription AS active_subscription , gn.name_type_code AS name_type_code , gn.name_type AS name_type , TO_CHAR (gn.date_of_info, 'MM/DD/YYYY HH:MI AM') AS date_of_info , ejs.ejs_comment AS ejs_comment , abs( EJS_SOLR_PK.EJS_GET_GANG_MEMBER_COUNT(g.gang_id)) as members_count , glc.gang_level_desc AS gang_level_desc , misc.description AS indx_sec_level_desc , ac.agency_desc AS indx_sec_level_agency_desc , entd.description AS name_type_desc , g.creator_id AS createdBy , g.creator_date AS creation_date , g.updator_date AS modification_date , g.updator_id AS modifiedBy , CONV_SOLR_CREATOR_DATE(g.creator_date, (SELECT ec.code FROM ejs_codes ec WHERE ec.code_type = 'DATABASE_TIMEZONE' AND ROWNUM = 1)) AS SOLR_CREATION_DATE FROM install i, gangs g INNER JOIN gang_names gn ON g.gang_id = gn.gang_id LEFT OUTER JOIN ejs_comments ejs ON g.rec_id = ejs.rec_id LEFT OUTER JOIN ejs_codes gtc ON gtc.code = g.gang_type_code AND gtc.code_type = g.gang_type_cd_type LEFT OUTER JOIN gang_level_codes glc ON g.level_code = glc.level_code LEFT OUTER JOIN master_index_security_codes misc ON g.indx_sec_level_code = misc.indx_sec_level_code LEFT OUTER JOIN agency_codes ac ON g.indx_sec_level_agency = ac.agency_code LEFT OUTER JOIN ejs_codes entd ON entd.code_type = NVL(gn.name_type,'GANG_NAME_TYPE_CODES') AND entd.code = gn.name_type_code