times new romangreen

PROCEDURE Proc_list_articles(
pvc_user IN VARCHAR2,
pvc_shop_no IN VARCHAR2,
pvc_article_type IN VARCHAR2,
pvc_filter_type IN VARCHAR2,
pvc_filter_value IN VARCHAR2,
pd_valid_from_date IN DATE,
pd_valid_to_date IN DATE,
pn_rec_start IN NUMBER,
pn_rec_end IN NUMBER,
pn_count OUT NUMBER,
pcur_get_articles OUT ref_get_articles,
pvc_ret_status OUT VARCHAR2,
pobj_ret_msg OUT t_return_message_rec )
AS
sql_stmt VARCHAR2(4000);
sql_stmt1 VARCHAR2(4000);
sql_stmt2 VARCHAR2(4000);
sql_stmt3 VARCHAR2(4000);
sql_stmt_count VARCHAR2(4000);
lvc_topics VARCHAR2(500);
lvc_user_message VARCHAR2(2000);
ln_err_number NUMBER;
lvc_err_message VARCHAR2(2000);
cur_get_article_count ref_get_article_count;
BEGIN
Ehandler.init;
pvc_ret_status:= 'T';
sql_stmt_count := 'SELECT COUNT(1) FROM (';
BEGIN
sql_stmt1 := 'SELECT article_id,title,topic,keywords,published_date';
sql_stmt2 := sql_stmt2 || ' CURSOR( SELECT ia.article_id,aa.attachment_id,aa.description,ia.article_type,aa.last_updated_date';
sql_stmt2 := sql_stmt2 || ' FROM INTERFLORA_ARTICLES ia, ARTICLE_ATTACHMENTS aa';
sql_stmt2 := sql_stmt2||' WHERE ia.article_id=aa.article_id)cur_get_article_count';
sql_stmt3 :=sql_stmt3 ||' FROM interflora_articles';
sql_stmt3 :=sql_stmt3 ||' WHERE article_type =UPPER(TRIM('''||pvc_article_type||'''))';
IF TRIM(UPPER(pvc_filter_type)) = 'name' AND pvc_filter_value IS NOT NULL THEN
sql_stmt :=sql_stmt || ' AND UPPER(topic) LIKE ''%' || UPPER(TRIM(REPLACE(pvc_filter_value,'''', '''''')))||'%''';
ELSIF pd_valid_from_date IS NOT NULL AND pd_valid_to_date IS NOT NULL THEN
sql_stmt :=sql_stmt || ' AND (published_date BETWEEN ('''||pd_valid_from_date||''') AND ('''||pd_valid_to_date||''')) ';
END IF;
sql_stmt3 :=sql_stmt3 ||'order by published_date desc';
END;
sql_stmt_count :=sql_stmt_count|| sql_stmt1||sql_stmt3||')';
OPEN cur_get_article_count FOR sql_stmt_count;
LOOP
FETCH cur_get_article_count INTO pn_count;
EXIT WHEN cur_get_article_count%NOTFOUND;
END LOOP;

sql_stmt := sql_stmt1 || sql_stmt3;
sql_stmt := Func_Generatepgquery( sql_stmt, pn_rec_start, pn_rec_end );
sql_stmt := 'SELECT AA.*, ' || sql_stmt2 || ' FROM ( ' || sql_stmt || ' ) AA ';
--sql_stmt := Func_Generatepgquery(sql_stmt ,pn_rec_start,pn_rec_end);

INSERT INTO TEST_QUERY VALUES(sql_stmt);
COMMIT;
OPEN pcur_get_articles FOR sql_stmt;
IF pn_count > 0 THEN
pvc_ret_status := 'T';
ELSE
pvc_ret_status := 'F';
lvc_err_message := 'No Article Found!!!';
Ehandler.capture;
Pkg_Impwebformlib_Rose2.proc_set_stack_details ( lvc_err_message, ln_err_number, pobj_ret_msg );
RETURN;
END IF;
EXCEPTION
WHEN OTHERS THEN
pvc_ret_status := 'F';
lvc_err_message := SUBSTR(SQLERRM,1,1000);
ln_err_number := SQLCODE;
Ehandler.capture;
Pkg_Impwebformlib_Rose2.proc_set_stack_details ( lvc_err_message, ln_err_number, pobj_ret_msg );
END Proc_list_articles;


Please help me in the filter part
i want if the filter type and value is null then the filter type in topic and the value will be article type which is a blob


please please help me urgently