CREATE OR REPLACE type MDHE.T_EST as object 
(
  ESTABLISHMENT_ID          NUMBER,
  EST VARCHAR2 (100 BYTE),
  EST_BUSINESS_NAME VARCHAR2 (100 BYTE), 
  PHONE                   VARCHAR2 (20 BYTE),
  ADDRESS_ID                NUMBER,
  INSPECTOR_ID              INTEGER,
  ESTABLISHMENT_LICENSE_ID  NUMBER,
  LICENSE_TYPE_CODE       VARCHAR2 (30 BYTE)
)

CREATE OR REPLACE type MDHE.TB_EST as table of t_est;

CREATE OR REPLACE function MDHE.FN_LIC_MM (CODE IN varchar2, STATUS IN varchar2, CLOSED IN integer) return tb_est 
PARALLEL_ENABLE --RESULT_CACHE 
IS
Result tb_est;
CODE1 varchar2(2000);  
STATUS1 varchar2(2000);  
nCODE integer;
sCODE integer;
begin
nCode := 1; sCode := 1; CODE1:= CODE; STATUS1:= STATUS;  
if INSTR(CODE, '-') = 1 then nCode := 0; CODE1 := SUBSTR (CODE,2); end if; 
if INSTR(STATUS, '-') = 1 then sCode := 0; STATUS1 := SUBSTR (STATUS,2); end if;
SELECT t_est (es.Establishment_id,
                   es.name, es.est_business_name, es.phone,
                   es.address_id,
                   es.inspector_id,
                   aa.establishment_LICENSE_id, aa.LICENSE_TYPE_CODE) bulk collect into result
  from (
select e.Establishment_id, LICENSE_TYPE_CODE, max(establishment_license_id) establishment_license_id 
              FROM establishment  e
                   JOIN establishment_license el
                       ON e.establishment_id = el.establishment_id
             WHERE 
             (((e.est_closed IS NULL) and (CLOSED = 0)) or (NOT(e.est_closed IS NULL) and (CLOSED <> 0)))  
and (((nCode=1) and (INSTR (','||CODE1||',',','||LICENSE_TYPE_CODE||',') > 0))
or   ((nCode=0) and (INSTR (','||CODE1||',',','||LICENSE_TYPE_CODE||',') = 0)))
group by e.establishment_id, LICENSE_TYPE_CODE) aa
join establishment es 
on aa.establishment_id = es.establishment_id
JOIN establishment_license esl
on aa.establishment_license_id = esl.establishment_license_id
where  (((sCode=1) and (INSTR (','||STATUS1||',',','||esl.status_code||',') > 0))
  or    ((sCode=0) and (INSTR (','||STATUS1||',',','||esl.status_code||',') = 0)));
  return(Result);
end FN_LIC_MM;
/