Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- procedure LISTAR_DOCS_FILTRADO(
- P_DESTINATARIO in varchar2,
- P_DATA_INICIO in date,
- P_DATA_FIM in date,
- P_COD_AREA in varchar2,
- P_ROWS in integer,
- P_PEDACO_ASSUNTO in varchar2,
- P_CURSOR out Sys_Refcursor) is
- begin
- open P_CURSOR for
- select * from (
- select
- pdoc.ped_cod_documento,
- pdoc.ped_dat_publicacao,
- pdoc.ped_pea_cod_area,
- pdoc.ped_pda_cod_assunto,
- pdoc.ped_peu_cod_usuario,
- pxml.pdx_arq_publicacao,
- pdoc.ped_num_doc_origem,
- pdoc.ped_comando_sucesso,
- pdoc.ped_comando_erro,
- pdoc.ped_des_titulo,
- pcom.id_destinatario,
- pedi.pde_ped_cod_documento,
- (select pep.edc_dat_publicacao from ped_edicao_pesef pep
- where pep.edc_num_edicao=pedi.pde_edc_num_edicao) as "DATA_EDICAO",
- pedi.pde_edc_num_edicao
- from
- ped_documento pdoc
- join ped_documento_xml pxml
- on pdoc.ped_cod_documento=pxml.pdx_ped_cod_documento
- join pesef_doc_comunicacao pcom
- on pdoc.ped_cod_documento=pcom.id_documento
- left join ped_docs_excluidos_adiados pexcl
- on pdoc.ped_cod_documento=pexcl.dea_ped_cod_documento
- left join ped_documento_edicao pedi
- on pdoc.ped_cod_documento=pedi.pde_ped_cod_documento
- where pexcl.dea_ped_cod_documento is null
- and (pcom.id_destinatario=P_DESTINATARIO or P_DESTINATARIO is null)
- and (to_char(pdoc.ped_dat_publicacao,'yyyymmdd') >= to_char(P_DATA_INICIO,'yyyymmdd') or P_DATA_INICIO is null)
- and (to_char(pdoc.ped_dat_publicacao,'yyyymmdd') <= to_char(P_DATA_FIM,'yyyymmdd') or P_DATA_FIM is null)
- and (pdoc.ped_pea_cod_area in (P_COD_AREA) or P_COD_AREA is null)
- and (LOWER(pdoc.ped_des_titulo) like LOWER(P_PEDACO_ASSUNTO) or P_PEDACO_ASSUNTO is null)
- order by pdoc.ped_cod_documento desc
- ) where ROWNUM <= P_ROWS;
- end LISTAR_DOCS_FILTRADO;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement