declare
v_d1 varchar2(12) := TO_char(:FROM_DATE,'dd-mm-yyyy');
v_d2 varchar2(12) := TO_char(:TO_DATE,'DD-MM-YYYY');
blk_id block := find_block('come_leav');
emp_where varchar2(2000);
def_where varchar2(2000);
begin
--def_where := 'emp_no = '||''''||upper(:txt_find)||'''';
emp_where := 'emp_no between '||:txt_find||' and '||:txt_find2;
if :from_date is not null and :txt_find is not null then
def_where := emp_where||' and '||'trunc(to_date(work_date,''dd-mm-yyyy'')) between '||''''||v_d1||''''||' and '||''''||v_d2||'''';
elsif :from_date is not null and :txt_find is null then
def_where := 'trunc(to_date(work_date,''dd-mm-yyyy'')) between '||''''||v_d1||''''||' and '||''''||v_d2||'''';
elsif :from_date is null and :txt_find is not null then
def_where := emp_where;
elsif :from_date is null and :txt_find is null then
def_where := '1 = 1';
end if;
set_block_property(blk_id,default_where,def_where);
go_block('come_leav');
execute_query;
end;
v_d1 varchar2(12) := TO_char(:FROM_DATE,'dd-mm-yyyy');
v_d2 varchar2(12) := TO_char(:TO_DATE,'DD-MM-YYYY');
blk_id block := find_block('come_leav');
emp_where varchar2(2000);
def_where varchar2(2000);
begin
--def_where := 'emp_no = '||''''||upper(:txt_find)||'''';
emp_where := 'emp_no between '||:txt_find||' and '||:txt_find2;
if :from_date is not null and :txt_find is not null then
def_where := emp_where||' and '||'trunc(to_date(work_date,''dd-mm-yyyy'')) between '||''''||v_d1||''''||' and '||''''||v_d2||'''';
elsif :from_date is not null and :txt_find is null then
def_where := 'trunc(to_date(work_date,''dd-mm-yyyy'')) between '||''''||v_d1||''''||' and '||''''||v_d2||'''';
elsif :from_date is null and :txt_find is not null then
def_where := emp_where;
elsif :from_date is null and :txt_find is null then
def_where := '1 = 1';
end if;
set_block_property(blk_id,default_where,def_where);
go_block('come_leav');
execute_query;
end;
No comments:
Post a Comment