PROCEDURE LISTAR_DOCS_FILTRADO(
  P_DESTINATARIO                  IN VARCHAR2,
  P_COD_AREA                      IN VARCHAR2,
  P_DATA_INICIO                   IN DATE,
  P_DATA_FIM                      IN DATE,
  P_ROWS                          IN INTEGER,
  P_PEDACO_ASSUNTO                IN VARCHAR2,
  P_CURSOR                        OUT Sys_Refcursor) IS

  V_SQL VARCHAR(32000);
  BEGIN

    V_SQL := 'select * from ( ' ;
    V_SQL := V_SQL || 'select cp.dcc_dcr_id_cpe,
             cred.dcr_rge_ruc,
             c.dce_id_comunic,
             c.dce_dtc_id,
             c.dce_titulo,
             t.dtc_descricao,
             aa.dta_cod_area,
             aa.dta_nom_area,
             a.das_cod_assunto,
             a.das_nom_assunto,
             NVL(cp.dcc_corpo_comunic, c.dce_corpo_comunic) as "DCE_CORPO_COMUNIC",
             NVL(cp.dcc_id_origem, c.dce_id_origem) as "DCE_ID_ORIGEM",
             cp.dcc_data_leitura,
             cp.dcc_data_envio,
             cp.dcc_data_remocao,
             c.dce_autor,
             c.dce_origem_param,
             cie.dce_dce_id_estado_ciente as EstadoCiente,
             (select count(1) from dtec_anexo_comunic ac
             where ac.dac_dce_id_comunic=c.dce_id_comunic and
             (ac.dac_dcr_idcpe=cp.dcc_dcr_id_cpe or ac.dac_dcr_idcpe is null)) as "Anexos"
        from dtec_comunicacao c
        join dtec_comunicacao_cpe cp
          on c.dce_id_comunic = cp.dcc_dce_id_comunic
        join dtec_tipo_comunic t
          on c.dce_dtc_id = t.dtc_id
        join dtec_assunto a
          on a.das_cod_assunto = c.dce_das_cod_assunto
        join dtec_area aa
          on aa.dta_cod_area = c.dce_dta_cod_area
        join dtec_credenciamento cred
          on cred.dcr_idcpe = cp.dcc_dcr_id_cpe
        left join dtec_ciente_eletronico cie
          on cie.dce_dce_id_comunic = cp.dcc_dce_id_comunic
         and cp.dcc_dcr_id_cpe = cie.dce_dcr_id_cpe

       where cp.dcc_data_remocao is null ';

       IF P_DESTINATARIO IS NOT NULL THEN
          V_SQL := V_SQL || ' and cp.dcc_dcr_id_cpe=' || P_DESTINATARIO || ' ';
       END IF;

       IF P_DATA_INICIO IS NOT NULL THEN
          V_SQL := V_SQL || ' and trunc(cp.dcc_data_envio) >= :P_DATA_INICIO ';
       ELSE
          V_SQL := V_SQL || ' and :P_DATA_INICIO is null ';
       END IF;

       IF P_DATA_FIM IS NOT NULL THEN
          V_SQL := V_SQL || ' and trunc(cp.dcc_data_envio) <= :P_DATA_FIM ';
       ELSE
          V_SQL := V_SQL || ' and :P_DATA_FIM is null ';
       END IF;

       IF P_COD_AREA IS NOT NULL THEN
          V_SQL := V_SQL || ' and aa.dta_cod_area  in (' || P_COD_AREA || ') ';
       END IF;

       IF P_PEDACO_ASSUNTO IS NOT NULL THEN
          V_SQL := V_SQL || ' and LOWER(c.dce_titulo) like LOWER(''' || P_PEDACO_ASSUNTO || ''')';
       END IF;

       V_SQL := V_SQL || ' order by cp.dcc_data_envio desc ' ;

       V_SQL := V_SQL || ' ) where ROWNUM <= ' ||  P_ROWS ;

       DBMS_OUTPUT.put_line(V_SQL);

       OPEN P_CURSOR FOR V_SQL using P_DATA_INICIO, P_DATA_FIM;

END LISTAR_DOCS_FILTRADO;