Sir,
Below is the sql build dynamically based on input.
I need your suggesion if we can do better with such kinda sql, by forcing optimizer to rebuild a plan each time for this sql.
SELECT * 
FROM ( 
   SELECT 
      ROWNUM RN,
      Q.* 
      FROM ( 
      SELECT 1,
         C.CSID,
         E.PNME ,
         L.DSC ,
         E.IN_TMS ,
         E.IN_TMS ,
         R.VLAST_NME ,
         R.VFIRST_NME ,
         R.VP_CDE ,
         R.CST_NBR ,
         R.CLAST_NME ,
         R.PCFIRST_NME ,
         R.DRUG_NME ,
         R.TR_NBR ,
         R.RC_NBR ,
         C.CS_I_ID 
      FROM PF F, TR R, OTM N, CSIT C, WP E, CDM L 
      WHERE E.SID = C.CSID 
      AND E.STYPE= 'C' 
      AND R.OID = F.OID 
      AND F.C_ID = N.SID 
      AND F.CIS_NBR = N.SUB_SID 
      AND R.OID = C.PRID
      AND E.LID = '07' 
      AND E.PNME NOT IN ('TRN') 
      AND L.CTXT_ID = E.PNME 
      AND L.QTXT = 'B347' 
      AND (((((E.IN1_TMS BETWEEN TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am')  AND TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') 
      AND NULL IS NULL ) 
      OR (E.IN2_TMS BETWEEN TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') AND TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') 
      AND NULL IS NOT NULL )) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VLAST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VFIRST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VFIRST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VP_CDE)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VP_CDE)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.CLAST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CFIRST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CST_NBR)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.CST_NBR)) IS NULL)))) 
      OR C.CS_I_ID = NULL 
      OR C.CSID = 786987652
      OR R.TR_NBR = NULL 
      OR R.RC_NBR = NULL ) 
      AND F.C_ID = N.SID 
      AND F.CIS_NBR = N.SUB_SID 
      AND N.LAST_TMS = GET_MX_TMS(N.SID, N.S_SID
      ) 
      AND ((N.DEST_ID) = E.PNME 
      OR (N.DEST_ID) LIKE '%NIK') 
      AND F.C_ID = GET_MX_F(C.CS_I_ID) 
      ORDER BY E.IN_TMS, L.DSC DESC) Q
      WHERE ROWNUM <= 100 ) 
      WHERE RN >= 10 

AND (((((E.IN1_TMS BETWEEN TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am')  AND TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') 
      AND NULL IS NULL ) 
      OR (E.IN2_TMS BETWEEN TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') AND TO_DATE(NULL, 'mm/dd/yyyy hh:mi:ss am') 
      AND NULL IS NOT NULL )) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VLAST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VFIRST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VFIRST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VP_CDE)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VP_CDE)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.CLAST_NME)) IS NULL)) 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CFIRST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.CST_NBR)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.CST_NBR)) IS NULL)))) 
      OR C.CS_I_ID = NULL 
      OR C.CSID = 786987652
      OR R.TR_NBR = NULL 
      OR R.RC_NBR = NULL ) 

...
      AND ((UPPER(NULL )) IS NULL 
      OR ((UPPER(R.VLAST_NME)) LIKE (UPPER(NULL ))||'%') 
      OR ((UPPER(NULL )) IS NULL 
      AND (UPPER(R.VLAST_NME)) IS NULL))... 

create object o_rrowtype as (c1 ...,c2...,...,cn ...)
/
create object c_rrowtype as table of o_rrowtype
/

from   e
     , c
     , cast(tf(:p1,:p2,...,:pn) as c_rrowtype) r

function tf(p1...,p2...,pn...) return c_rrowtype is
   c_rrowtype_v c_rrowtype;
   sqlv varchar2(32000);
begin
--
-- dynamically construct a sqlstatement that does whole object assignement
-- then execute it, returning results into your collection variable
--
   if p1 is not null then <add it to where clause> end if;
   ...
   if pn is not null then ... end if;
   execute immediate sql_v into c_rrowtype_v;
   return (c_rrowtype_v);
end;
/

select cast(multiset(select * from(
                                    select *
                                    from t
                                    where ...
                                  )
                    ) as c_rrowtype
            )
into c_rrowtype_v
from dual
;