create or replace
package BIP_DynamicQuery as

 deptwhereclause varchar2(1000) := ' ';
 empwhereclause varchar2(1000) := ' ';
 p_deptno varchar2(400);
 p_year varchar2(400);
 p_job varchar2(400);

 function BeforeReport return boolean;

end BIP_DynamicQuery;


create or replace
package body BIP_DynamicQuery as

function BeforeReport return boolean
is
begin
  if p_deptno is not null then
     deptwhereclause := 'WHERE DEPTNO IN ('||p_deptno||')';
  end if;
  if p_year is not null then
     empwhereclause := 'AND TO_CHAR(HIREDATE,''YY'') in ('||p_year||')';
  end if;
  if p_job is not null then
     if instr(p_job,',')>0 then
         empwhereclause := empwhereclause || ' AND JOB in ('||p_job||')';
     else 
         empwhereclause := empwhereclause || ' AND JOB = '''||p_job||'''';
     end if;
  end if;     
  return true;
end;

end BIP_DynamicQuery;
