create or replace function fsget_idcodelengths(fp_plant_id number, fp_packagetype number, fp_primary_YNB varchar2 default 'Y') return varchar2 is
-- function returns a comma separated string with all allowed ID code lenghts for the passed plant and package type
-- parameter fp_primary_YNB defines if only lengths of primary ID's (Y) or only secondary ID's (N) or both (B) should be returned
  Result varchar2(100);
begin
  if (fp_plant_id is null) or (fp_packagetype is null) then
     return('');
  else
  begin
    result := '';
    for vCursor in(select distinct(i.length) len
                     from uniqueidconfiguration i,
                          businessunit b
                    where i.packagetype = fp_packagetype
                      and b.businessunit_id = i.businessunit_id
                      and b.plant_id = fp_plant_id
                      and (i.primaryid = fp_primary_YNB or fp_primary_YNB = 'B')
                    order by i.length
          ) loop
      result := result || vCursor.len || ',';
    end loop;
  result := substr(result,1,length(result)-1);
  result := substr(result,1,40);
  return(Result);
  end;
  end if;
end fsget_idcodelengths;
/
