CREATE OR REPLACE TYPE crtr_array IS VARRAY(200) OF VARCHAR2(2000)

 PROCEDURE myproc (p_varray_s IN strg_array,
                                            p_behv_fea_c IN varchar2,
                                            p_txlt_crtr IN crtr_array,
                                             p_is_avl_i OUT VARCHAR2,
                                            p_avail_rc OUT SYS_REFCURSOR,
                                            p_res_rc OUT VARCHAR2 )
  IS
TYPE av_set_rectype IS RECORD
    (id VARCHAR2(50),
     av_flag VARCHAR2(1));
TYPE av_set_tabtype IS TABLE OF av_set_rectype
INDEX BY BINARY_INTEGER;
av_set_rec av_set_tabtype;
v_query VARCHAR2(1000);
l_select_string VARCHAR2(4000);
v_view varchar2(50);
v_query_begin varchar2(100);
v_query_end varchar2(50);
v_rslt_set SYS_REFCURSOR;
l_count number;

BEGIN
   IF v_query IS NOT NULL
   THEN
      v_query := ' ( ' || v_query || ') and';
   END IF;

   IF p_varray_s.COUNT = 0
   THEN
      l_select_string :=
            ' select a.loan_n N, ''Y'' AVL_I from '
         || v_view
         || ' a  where'
         || v_query
         || ' 1 = 1  ';

      OPEN p_avail_rc FOR l_select_string;

      OPEN v_rslt_set FOR l_select_string;
   ELSE
      v_query_begin :=
         'select id N , decode(code, '''',''N'',''Y'') AVL_I ' || ' from (';
      v_query_end := ' ) ';
      l_select_string :=
            v_query_begin
         || ' select a.loan_n code, t.column_value id from '
         || v_view
         || ' a,  '
         || ' TABLE(:p_varray_s) t '
         || ' where a.loan_n (+)= t.column_value and '
         || v_query
         || ' 1 = 1  '
         || v_query_end;

      OPEN p_avail_rc FOR l_select_string USING p_varray_s;

      OPEN v_rslt_set FOR l_select_string USING p_varray_s;
   END IF;

   l_count := 1;
   p_is_avl_i := 'Y';

   LOOP
      FETCH v_rslt_set INTO   av_set_rec (l_count);

      EXIT WHEN v_rslt_set%NOTFOUND;

      IF av_set_rec (l_count).av_flag = 'N'
      THEN
         p_is_avl_i := 'N';

         EXIT;
      END IF;
   END LOOP;

   CLOSE v_rslt_set;

   p_res_rc := ' Success. ';
END IF;

EXCEPTION
  -------------

END myproc;