procedure p_proc(p_param varchar2,
                outcur  out sys_refcursor) is
begin
  open outcur for
    select *
      from (select * 
              from first_query
             union all
            select * 
              from second_query)
        -- predicate block
     where 1=1;
end;

procedure p_getUserOnlineSales(p_emp_id varchar2,
                               p_office_id     varchar2,
                               p_date_from   date default trunc(sysdate),
                               p_date_to     date default sysdate + 1,
                               p_price_from  number default null,
                               p_price_to    number default null,
                               p_item_group  typ_item_groups default cast(null as
                                                                          typ_item_groups),
                               p_receipt_num varchar2 default null,
                                   
                               p_page_num  number default 0,
                               p_page_size number default 10,
                               outcur      out sys_refcursor) is
  item_group_cnt number;
  v_op_id number := f_getOPIdByCode(p_office_id);
  v_emp_id number := f_getEmpIdByCodeTimesheet(p_emp_id);
begin
  -- check filters empty
  item_group_cnt := p_item_group.count(); 
  --
  open outcur for
    select *
      from (select -- visible cols
                   si.item_full_name
                 , si.final_price
                 , si.full_price
                 , si.receipt_num
                 , si.receipt_date
                 , si.vendor_code
                 , case when det.br_summary is null and mr.motiv_rate_value is not null then mr.motiv_rate_value
                        when det.br_summary is not null then det.br_summary
                    end personal_bonus_amount
                 , case when det.br_summary is null and mr.motiv_rate_value is not null then 1
                        when det.br_summary is not null then det.cross_sale_kt
                    end personal_bonus_koeff
                 -- service cols
                 , case when det.br_summary is null and mr.motiv_rate_value is not null then 'approximate'
                        when det.br_summary is not null then 'definite'
                    end personal_bonus_type
                 , coalesce(det.sale_stream, mr.sale_stream, 'Не определено') item_group_name
                 , si.operation_type
                 , si.src
                 -- pagination
                 , row_number() over (order by si.receipt_date desc) rn 
              from (-- curr day
                    select b.cost final_price
                         , case when b.discount = 0 then null else b.price
                            end full_price
                         , b.doc_number receipt_num
                         , b.receipt_date receipt_date
                         , i.item_code vendor_code
                         , i.full_name item_full_name
                         , b.subsite code_op
                         , b.operator_id
                         , to_char(b.businessday, 'yyyymm') sale_period
                         , b.oper_type operation_type
                         , 'bill' src
                      from scheme.bills b
                      join scheme.items i on i.item_code = b.item
                     where b.businessday = trunc(p_date_to)
                       and b.subsite = p_office_id
                       and b.operator_id = p_emp_id
                    union all
                    -- prev days
                    select l.txn_amount final_price
                         , case when l.disc = 0 then null else l.price
                             end full_price
                         , t.receipt_num receipt_num
                         , t.ts receipt_date
                         , i.item_code vendor_code
                         , i.full_name item_full_name
                         , s.office_code code_op
                         , e.emp_code operator_id
                         , to_char(l.dt,'yyyymm') sale_period
                         , l.txn_type operation_type
                         , 'txn' src
                      from scheme.txn t
                      join scheme.txn_lines l on t.rtl_txn_id = l.rtl_txn_id
                      join scheme.items i on l.item_id = i.item_id
                      join scheme.offices s on t.subsite_id = s.subsite_id
                      join scheme.employees e on t.employee_id = e.employee_id
                     where t.ts between trunc(p_date_from) and trunc(p_date_to)
                       and t.subsite_id = v_op_id
                       and t.employee_id = v_emp_id
                 ) si
             /* fact */
             left join scheme.sales_details det on si.sale_period = det.period
                                               and si.code_op = det.op_code
                                               and ltrim(si.operator_id,'0') = ltrim(det.tab_num,'0')
                                               and si.receipt_num = det.rcpt_num
                                               and si.vendor_code = det.item_article
             /* prognosis */
             left join scheme.rates mr on si.sale_period = mr.motiv_rate_period
                                      and si.code_op = mr.code_op
                                      and si.vendor_code = mr.code_1c
            where 1 = 1
              and si.final_price between nvl(p_price_from, si.final_price) and nvl(p_price_to, si.final_price)
              /* if no filters */
              and (item_group_cnt = 0 or coalesce(det.sale_stream, mr.sale_stream, 'Не определено') in (select * from table(p_item_group)))
              and si.receipt_num = nvl(p_receipt_num, si.receipt_num)                                        
           )
     where rn between p_page_num * p_page_size + 1 and (p_page_num + 1) * p_page_size;
end;