procedure load_products_list
(
  i_seller_id in number,
  i_seller_status in number,
  i_product_status in number,
  i_product_search_key in varchar2,
  i_between_start in number,
  i_between_end in number,
  o_result out sys_refcursor
)
is
  v_query varchar2(4000); 
begin

  v_query := 'select * from ( select t.id, t.seller, t.category, t.country,  t.shortname, t.description, t.price, t.quantity, t.quantity_real, t.model, t.brand, t.date_create, t.status, t.weight,  row_number() over (order by t.id) rn from  allstore.product t, allstore.seller s  where t.seller = s.id ';
  
  if i_seller_id is not null then 
    v_query :=  v_query || ' and t.seller = '||i_seller_id;
  end if;
  
  if i_seller_status is not null then
    v_query :=  v_query || ' and s.status = '||i_seller_status;
  end if;
  
  if i_product_status is not null then
    v_query :=  v_query || ' and t.status = '||i_product_status;
  end if;        
  
  if i_product_search_key is not null then
    v_query :=  v_query || ' and (upper(t.shortname) like upper(%'||i_product_status||'%) or upper(t.description) like upper(%'||i_product_status||'%))';
  end if;
  
  v_query :=  v_query || ') where rn between '||i_between_start||' and '||i_between_end||' order by rn;';
  
  open o_result for v_query;
	
exception
  when others then
    logs.write_log('E', sqlcode||' '||sqlerrm);
end load_products_list;

select * from (
        select
          t.id,
          t.seller,
          t.category,
          t.country,
          t.shortname,
          t.description,
          t.price,
          t.quantity,
          t.quantity_real,
          t.model,
          t.brand,
          t.date_create,
          t.status,
          t.weight,
          row_number() over
          (order by t.id) rn
        from 
          allstore.product t, 
          allstore.seller s 
        where 
          t.seller = 8
          and t.seller = s.id and    
          s.status = 1 and
          t.status = 1 and
          ((upper(t.shortname) like upper('%alo%')) or (upper(t.description) like upper('%alo%'))) 
      )
      where rn between 1 and 10
      order by rn