Como fazer uma procedure com essa select

[wilke]

Selecionar tudo

select /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */
GC.CD_ORDEM,SG.NM_SUB_GRUPO_CONTABIL NM_GRUPO_CAIXA,
fn_venc_data(TO_DATE('16/01/2008','DD MM YYYY'),TO_DATE('17/01/2008','DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'2') as dt_vencimento,
SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_FORNECEDOR,
 MAX(decode(NM_GRUPO_CAIXA,'IMOBILIZADO','VL_FORNECEDOR',''))IMOBILIZADO
from TB_SUB_GRUPO_CONTABIL SG,
     TB_PLANO_CONTA PN,
     tb_pessoa ps,
     tb_mov_pagar mp,
     tb_pagar pg, 
     TB_GRUPO_CAIXA GC
where SG.CD_SUB_GRUPO_CONTABIL = '1.31' 
AND SG.NU_PLANO = '1'
AND PN.NU_PLANO = '1'  
AND SG.CD_SUB_GRUPO_CONTABIL = PN.CD_SUB_GRUPO_CONTABIL
AND PN.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL
AND PG.CD_GRUPO_CAIXA = GC.CD_GRUPO_CAIXA
AND (GC.FL_OPERACAO IN ('A','C') OR GC.CD_GRUPO_CAIXA = '15005')
and (((mp.dt_vencimento_boleta is null
and    mp.dt_prorrogacao is null 
and    mp.dt_vencimento  between fn_conv_data('05/01/2008') and fn_conv_data('17/01/2008')) 
or     mp.dt_prorrogacao between fn_conv_data('16/01/2008') and fn_conv_data('17/01/2008')
and    mp.dt_prorrogacao > decode(mp.dt_vencimento_boleta,null,fn_conv_data('01/01/1920'),mp.dt_vencimento_boleta))
or     mp.dt_vencimento_boleta between fn_conv_data('16/01/2008') and fn_conv_data('17/01/2008')
 and    mp.dt_vencimento_boleta > decode(mp.dt_prorrogacao,null,fn_conv_data('01/01/1920'),mp.dt_prorrogacao))
 AND    MP.DT_LIQUIDACAO IS NULL
 and ps.cd_pessoa = pg.cd_pessoa
 and pg.cd_pessoa <> '34629005'
 and pg.cd_pessoa <> '36392005'
 and pg.cd_pessoa <> '3441005' 
 and pg.CD_pessoa <> '34903005'
 and pg.cd_pagar = mp.cd_pagar
 and mp.cd_conta_contabil like '%' and MP.CD_CONTA_CONTABIL <> '25.10000'
 and ((mp.cd_banco||'' <> '299')or   mp.cd_banco||'' is null)
 and ( mp.ds_obs not like 'TRANSFERENCIA INTERNA%' or mp.ds_obs is null)
 AND ((MP.FL_POSICAO <> 'L'AND MP.FL_POSICAO <> 'C')OR MP.FL_POSICAO IS NULL)
 GROUP BY GC.CD_ORDEM,SG.NM_SUB_GRUPO_CONTABIL,
fn_venc_data(TO_DATE('16/01/2008','DD MM YYYY'),TO_DATE('17/01/2008','DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'2'),
decode(NM_GRUPO_CAIXA,'IMOBILIZADO','NM_GRUPO_CAIXA',NULL)
Order by DT_VENCIMENTO 
 :cry: desde já agradeço pela ajuda de Vocês

[Tineks]

E ai Wike, tudo beleza??

- a procedure que você deseja montar vai fazer somente o select, não vai ter regra de negocio dentro dela ?
- você quer que ela retorne todos os campos consultados ?
- o select pode retornar mais de uma linha?

[]'s

[wilke]

oi amigo tudo beleza.
essa procedure
que ela retorne todos os campos consultados
e a select pode retornar mais de uma linha.
desde já ti agradeço.

[Tineks]

No exemplo que eu montei eu criei uma procedure dentro de uma package, porem você pode criar a procedure sozinha, sem q esteja dentro da package, o resultado será o mesmo.

Selecionar tudo

CREATE OR REPLACE PACKAGE PCK_CRISTIANO
IS

   type myrefcur is ref CURSOR;
   PROCEDURE  prc_consulta (p_retorno OUT myrefcur);

END;

/
CREATE OR REPLACE PACKAGE BODY PCK_CRISTIANO
IS
   PROCEDURE prc_consulta (p_retorno OUT myrefcur)
   IS
      ret   myrefcur;
      v_CONSULTA VARCHAR2(10000);
   BEGIN
      v_CONSULTA := 'SELECT   /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */ '||
                    '         gc.cd_ordem,  '||
                    '         sg.nm_sub_grupo_contabil nm_grupo_caixa, '||
                    '         fn_venc_data (TO_DATE (''16/01/2008'', ''DD MM YYYY''), '||
                    '                       TO_DATE (''17/01/2008'', ''DD MM YYYY''), '||
                    '                       mp.dt_vencimento, '||
                    '                       mp.dt_vencimento_boleta, '||
                    '                       mp.dt_prorrogacao, '||
                    '                       ''2'' '||
                    '                      ) AS dt_vencimento, '||
                    '         SUM (  NVL (mp.vl_parcela, 0.00) '||
                    '              + NVL (mp.vl_encargo, 0.00) '||
                    '              - NVL (mp.vl_desconto, 0.00) '||
                    '             ) AS vl_fornecedor, '||
                    '         MAX (DECODE (nm_grupo_caixa, ''IMOBILIZADO'', ''VL_FORNECEDOR'', '''')) imobilizado '||
                    '    FROM tb_sub_grupo_contabil sg, '||
                    '         tb_plano_conta pn, '||
                    '         tb_pessoa ps, '||
                    '         tb_mov_pagar mp, '||
                    '         tb_pagar pg, '||
                    '         tb_grupo_caixa gc '||
                    '   WHERE sg.cd_sub_grupo_contabil = ''1.31'' '||
                    '     AND sg.nu_plano = ''1'' '||
                    '     AND pn.nu_plano = ''1'' '||
                    '     AND sg.cd_sub_grupo_contabil = pn.cd_sub_grupo_contabil '||
                    '     AND pn.cd_conta_contabil = mp.cd_conta_contabil '||
                    '     AND pg.cd_grupo_caixa = gc.cd_grupo_caixa '||
                    '     AND (gc.fl_operacao IN (''A'', ''C'') OR gc.cd_grupo_caixa = ''15005'') '||
                    '     AND (   (   (    mp.dt_vencimento_boleta IS NULL '||
                    '                  AND mp.dt_prorrogacao IS NULL '||
                    '                  AND mp.dt_vencimento BETWEEN fn_conv_data (''05/01/2008'') '||
                    '                                           AND fn_conv_data (''17/01/2008'') '||
                    '                 ) '||
                    '              OR     mp.dt_prorrogacao BETWEEN fn_conv_data (''16/01/2008'') '||
                    '                                           AND fn_conv_data (''17/01/2008'') '||
                    '                 AND mp.dt_prorrogacao > '||
                    '                        DECODE (mp.dt_vencimento_boleta, '||
                    '                                NULL, fn_conv_data (''01/01/1920''), '||
                    '                                mp.dt_vencimento_boleta '||
                    '                               ) '||
                    '             ) '||
                    '          OR     mp.dt_vencimento_boleta BETWEEN fn_conv_data (''16/01/2008'') '||
                    '                                             AND fn_conv_data (''17/01/2008'') '||
                    '             AND mp.dt_vencimento_boleta > '||
                    '                    DECODE (mp.dt_prorrogacao, '||
                    '                            NULL, fn_conv_data (''01/01/1920''), '||
                    '                            mp.dt_prorrogacao '||
                    '                           ) '||
                    '         ) '||
                    '     AND mp.dt_liquidacao IS NULL '||
                    '     AND ps.cd_pessoa = pg.cd_pessoa '||
                    '     AND pg.cd_pessoa <> ''34629005'' '||
                    '     AND pg.cd_pessoa <> ''36392005'' '||
                    '     AND pg.cd_pessoa <> ''3441005'' '||
                    '     AND pg.cd_pessoa <> ''34903005'' '||
                    '     AND pg.cd_pagar = mp.cd_pagar '||
                    '     AND mp.cd_conta_contabil LIKE ''%'' '||
                    '     AND mp.cd_conta_contabil <> ''25.10000'' '||
                    '     AND ((mp.cd_banco || '''' <> ''299'') OR mp.cd_banco || '''' IS NULL) '||
                    '     AND (mp.ds_obs NOT LIKE ''TRANSFERENCIA INTERNA%'' OR mp.ds_obs IS NULL) '||
                    '     AND (   (mp.fl_posicao <> ''L'' AND mp.fl_posicao <> ''C'') '||
                    '          OR mp.fl_posicao IS NULL '||
                    '         ) '||
                    'GROUP BY gc.cd_ordem, '||
                    '         sg.nm_sub_grupo_contabil, '||
                    '         fn_venc_data (TO_DATE (''16/01/2008'', ''DD MM YYYY''), '||
                    '                       TO_DATE (''17/01/2008'', ''DD MM YYYY''), '||
                    '                       mp.dt_vencimento, '||
                    '                       mp.dt_vencimento_boleta, '||
                    '                       mp.dt_prorrogacao, '||
                    '                       ''2'' '||
                    '                      ), '||
                    '         DECODE (nm_grupo_caixa, ''IMOBILIZADO'', ''NM_GRUPO_CAIXA'', NULL) '||
                    'ORDER BY dt_vencimento ';                    
      OPEN ret FOR v_CONSULTA;
      p_retorno := ret;
   END;

END;

acima tem o corpo da package+procedure, você pode ver que dentro da procedure está o seu select.

abaixo é o código que vai chamar essa procedure, dentro dele você pode notar que existe um loop, dessa forma ele vai ler todos os registros que sua consulta encontrou.

Selecionar tudo

/* Formatted on 2008/01/17 10:34 (Formatter Plus v4.8.7) */
DECLARE
   v_ref_cur          pck_cristiano.myrefcur;
   v_cd_ordem         NUMBER;
   v_nm_grupo_caixa   VARCHAR2 (1000);
   v_dt_vencimento    DATE;
   v_vl_fornecedor    NUMBER;
   v_imobilizado      VARCHAR2 (100);
BEGIN
   pck_cristiano.prc_consulta (v_ref_cur);

   LOOP
      FETCH v_ref_cur
       INTO v_cd_ordem, v_nm_grupo_caixa, v_dt_vencimento, v_vl_fornecedor, v_imobilizado;

      EXIT WHEN v_ref_cur%NOTFOUND;

      DBMS_OUTPUT.put_line ('cd_ordem : ' || v_cd_ordem);
      DBMS_OUTPUT.put_line ('v_nm_grupo_caixa : ' || v_nm_grupo_caixa);
      DBMS_OUTPUT.put_line ('v_vl_fornecedor : ' || v_vl_fornecedor);
      DBMS_OUTPUT.put_line ('v_imobilizado : ' || v_imobilizado);
   END LOOP;
END;

para poder observar o retorno eu utilizei o dbma_output, porem você pode utilizar da forma que quiser.

[]'s

[wilke]

amigo obrigado pela sua ajuda, eu vou estudar essa dica.

[wilke]

Amigo me perdoe ,mas eu não consigui entender a sua dica.
então eu estou tentando fazer uma conforme os meu conhecimento, que e tão pouco.

Selecionar tudo

 e eu fiz assim mas esta dando o erro pls-00428
aonde e que eu tenho que consertar?
CREATE or replace PROCEDURE PR_PESQUISA_IMOBILIZADO( DATA1  IN VARCHAR2,
                                                     DATA2  IN VARCHAR2,
                                                     VDATE1 IN VARCHAR2,
                                                     VDATE2 IN VARCHAR2)IS
                                                    
BEGIN                                     
SELECT   /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */
GC.CD_ORDEM,SG.NM_SUB_GRUPO_CONTABIL NM_GRUPO_CAIXA,
fn_venc_data(TO_DATE(VDATE1,'DD MM YYYY'),TO_DATE(VDATE2,'DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'2') as dt_vencimento,
SUM(NVL(MP.VL_PARCELA,0.00) + NVL(MP.VL_ENCARGO,0.00) - NVL(MP.VL_DESCONTO,0.00)) AS VL_FORNECEDOR
from TB_SUB_GRUPO_CONTABIL SG,
     TB_PLANO_CONTA PN,
     tb_pessoa ps,
     tb_mov_pagar mp,
     tb_pagar pg, 
     TB_GRUPO_CAIXA GC
where SG.CD_SUB_GRUPO_CONTABIL = '1.31' 
AND SG.NU_PLANO = '1'  
AND PN.NU_PLANO = '1'  
AND SG.CD_SUB_GRUPO_CONTABIL = PN.CD_SUB_GRUPO_CONTABIL
AND PN.CD_CONTA_CONTABIL = MP.CD_CONTA_CONTABIL
AND PG.CD_GRUPO_CAIXA = GC.CD_GRUPO_CAIXA
AND (GC.FL_OPERACAO IN ('A','C') OR GC.CD_GRUPO_CAIXA = '15005')
and (((mp.dt_vencimento_boleta is null
and    mp.dt_prorrogacao is null 
and    mp.dt_vencimento  between fn_conv_data(DATA1) and fn_conv_data(DATA2)) 
or     mp.dt_prorrogacao between fn_conv_data(VDATE1) and fn_conv_data(VDATE2)
and    mp.dt_prorrogacao > decode(mp.dt_vencimento_boleta,null,fn_conv_data('01/01/1920'),mp.dt_vencimento_boleta))
or     mp.dt_vencimento_boleta between fn_conv_data(VDATE1) and fn_conv_data(VDATE2)
and    mp.dt_vencimento_boleta > decode(mp.dt_prorrogacao,null,fn_conv_data('01/01/1920'),mp.dt_prorrogacao))
AND    MP.DT_LIQUIDACAO IS NULL
 and ps.cd_pessoa = pg.cd_pessoa
 and pg.cd_pessoa <> '34629005'
 and pg.cd_pessoa <> '36392005'
 and pg.cd_pessoa <> '3441005' 
 and pg.CD_pessoa <> '34903005'
 and pg.cd_pagar = mp.cd_pagar
 and mp.cd_conta_contabil like '%' and MP.CD_CONTA_CONTABIL <> '25.10000'
 and ((mp.cd_banco||'' <> '299')or   mp.cd_banco||'' is null)
 and ( mp.ds_obs not like 'TRANSFERENCIA INTERNA%' or mp.ds_obs is null)
 AND ((MP.FL_POSICAO <> 'L'AND MP.FL_POSICAO <> 'C')OR MP.FL_POSICAO IS NULL)
 GROUP BY GC.CD_ORDEM,SG.NM_SUB_GRUPO_CONTABIL,
fn_venc_data(TO_DATE(VDATE1,'DD MM YYYY'),TO_DATE(VDATE2,'DD MM YYYY'),mp.dt_vencimento,mp.dt_vencimento_boleta,mp.dt_prorrogacao,'2')
Order by DT_VENCIMENTO; 
   

end;  

[Tineks]

Olá Wilke, esse código que você montou precisa de algumas modificações, nesse caso você colocou seu select direto dentro da procedure, e isso não pode. Da uma olhada no código que eu mandei, nele você pode ver que o select está dentro de uma variavel..

Selecionar tudo

      v_CONSULTA VARCHAR2(10000); 
   BEGIN 
      v_CONSULTA := 'SELECT   /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */ '|| 
                    '         gc.cd_ordem,  '.......

e depois de colocar o select na variavel nós o executamos através do

Selecionar tudo

OPEN ret FOR v_CONSULTA;

Faz o seguinte, pega a package que eu montei e cria ela no seu banco, depois pega o código abaixo e tenta executar ela..

Selecionar tudo

DECLARE 
   v_ref_cur          pck_cristiano.myrefcur; 
   v_cd_ordem         NUMBER; 
   v_nm_grupo_caixa   VARCHAR2 (1000); 
   v_dt_vencimento    DATE; 
   v_vl_fornecedor    NUMBER; 
   v_imobilizado      VARCHAR2 (100); 
BEGIN 
   pck_cristiano.prc_consulta (v_ref_cur); 

   LOOP 
      FETCH v_ref_cur 
       INTO v_cd_ordem, v_nm_grupo_caixa, v_dt_vencimento, v_vl_fornecedor, v_imobilizado; 

      EXIT WHEN v_ref_cur%NOTFOUND; 

      DBMS_OUTPUT.put_line ('cd_ordem : ' || v_cd_ordem); 
      DBMS_OUTPUT.put_line ('v_nm_grupo_caixa : ' || v_nm_grupo_caixa); 
      DBMS_OUTPUT.put_line ('v_vl_fornecedor : ' || v_vl_fornecedor); 
      DBMS_OUTPUT.put_line ('v_imobilizado : ' || v_imobilizado); 
   END LOOP; 
END;

qualquer dúvida é só falar.

[]'s!!

[wilke]

amigo referente as datas ,eu posso criar as variaveis do lado v_consulta? ou qual seria a forma mas certa de manipular.desde eu ti agradeço.

[Tineks]

No caso das datas você pode concatena-las a consulta...
ficando assim.

Selecionar tudo

'                  AND mp.dt_vencimento BETWEEN fn_conv_data ('''||v_DATA_INICIAL||''') '|| 

[]'s

[wilke]

AMIGO EU VOU FAZER TESTE, EU TE AVISO

[wilke]

Amigo eu fiz assim, mas agora ele esta dando o error pls-00201, dizendo que a variavel myrefcur tem de ser declarada, eu coloquei ela como %type mas mesmo assim ela ta dando erro.

Selecionar tudo

CREATE OR REPLACE PROCEDURE pr_pesquisa_imobilizado(p_retorno OUT myrefcur) 
   IS 
      ret   myrefcur; 
      v_CONSULTA VARCHAR2(10000); 
   BEGIN 
      v_CONSULTA := 'SELECT   /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */ '|| 
                    '         gc.cd_ordem,  '|| 
                    '         sg.nm_sub_grupo_contabil nm_grupo_caixa, '|| 
                    '         fn_venc_data (TO_DATE (VDATE1, ''DD MM YYYY''), '|| 
                    '                       TO_DATE (VDATE2, ''DD MM YYYY''), '|| 
                    '                       mp.dt_vencimento, '|| 
                    '                       mp.dt_vencimento_boleta, '|| 
                    '                       mp.dt_prorrogacao, '|| 
                    '                       ''2'' '|| 
                    '                      ) AS dt_vencimento, '|| 
                    '         SUM (  NVL (mp.vl_parcela, 0.00) '|| 
                    '              + NVL (mp.vl_encargo, 0.00) '|| 
                    '              - NVL (mp.vl_desconto, 0.00) '|| 
                    '             ) AS vl_fornecedor, '|| 
                    '         MAX (DECODE (nm_grupo_caixa, ''IMOBILIZADO'', ''VL_FORNECEDOR'', '''')) imobilizado '|| 
                    '    FROM tb_sub_grupo_contabil sg, '|| 
                    '         tb_plano_conta pn, '|| 
                    '         tb_pessoa ps, '|| 
                    '         tb_mov_pagar mp, '|| 
                    '         tb_pagar pg, '|| 
                    '         tb_grupo_caixa gc '|| 
                    '   WHERE sg.cd_sub_grupo_contabil = ''1.31'' '|| 
                    '     AND sg.nu_plano = ''1'' '|| 
                    '     AND pn.nu_plano = ''1'' '|| 
                    '     AND sg.cd_sub_grupo_contabil = pn.cd_sub_grupo_contabil '|| 
                    '     AND pn.cd_conta_contabil = mp.cd_conta_contabil '|| 
                    '     AND pg.cd_grupo_caixa = gc.cd_grupo_caixa '|| 
                    '     AND (gc.fl_operacao IN (''A'', ''C'') OR gc.cd_grupo_caixa = ''15005'') '|| 
                    '     AND (   (   (    mp.dt_vencimento_boleta IS NULL '|| 
                    '                  AND mp.dt_prorrogacao IS NULL '|| 
                    '                  AND mp.dt_vencimento BETWEEN fn_conv_data (DATA1) '|| 
                    '                                           AND fn_conv_data (DATA2) '|| 
                    '                 ) '|| 
                    '              OR     mp.dt_prorrogacao BETWEEN fn_conv_data (VDATE1) '|| 
                    '                                           AND fn_conv_data (VDATE2) '|| 
                    '                 AND mp.dt_prorrogacao > '|| 
                    '                        DECODE (mp.dt_vencimento_boleta, '|| 
                    '                                NULL, fn_conv_data (''01/01/1920''), '|| 
                    '                                mp.dt_vencimento_boleta '|| 
                    '                               ) '|| 
                    '             ) '|| 
                    '          OR     mp.dt_vencimento_boleta BETWEEN fn_conv_data (VDATE1) '|| 
                    '                                             AND fn_conv_data (VDATE2) '|| 
                    '             AND mp.dt_vencimento_boleta > '|| 
                    '                    DECODE (mp.dt_prorrogacao, '|| 
                    '                            NULL, fn_conv_data (''01/01/1920''), '|| 
                    '                            mp.dt_prorrogacao '|| 
                    '                           ) '|| 
                    '         ) '|| 
                    '     AND mp.dt_liquidacao IS NULL '|| 
                    '     AND ps.cd_pessoa = pg.cd_pessoa '|| 
                    '     AND pg.cd_pessoa <> ''34629005'' '|| 
                    '     AND pg.cd_pessoa <> ''36392005'' '|| 
                    '     AND pg.cd_pessoa <> ''3441005'' '|| 
                    '     AND pg.cd_pessoa <> ''34903005'' '|| 
                    '     AND pg.cd_pagar = mp.cd_pagar '|| 
                    '     AND mp.cd_conta_contabil LIKE ''%'' '|| 
                    '     AND mp.cd_conta_contabil <> ''25.10000'' '|| 
                    '     AND ((mp.cd_banco || '''' <> ''299'') OR mp.cd_banco || '''' IS NULL) '|| 
                    '     AND (mp.ds_obs NOT LIKE ''TRANSFERENCIA INTERNA%'' OR mp.ds_obs IS NULL) '|| 
                    '     AND (   (mp.fl_posicao <> ''L'' AND mp.fl_posicao <> ''C'') '|| 
                    '          OR mp.fl_posicao IS NULL '|| 
                    '         ) '|| 
                    'GROUP BY gc.cd_ordem, '|| 
                    '         sg.nm_sub_grupo_contabil, '|| 
                    '         fn_venc_data (TO_DATE (VDATE1, ''DD MM YYYY''), '|| 
                    '                       TO_DATE (VDATE2, ''DD MM YYYY''), '|| 
                    '                       mp.dt_vencimento, '|| 
                    '                       mp.dt_vencimento_boleta, '|| 
                    '                       mp.dt_prorrogacao, '|| 
                    '                       ''2'' '|| 
                    '                      ), '|| 
                    '         DECODE (nm_grupo_caixa, ''IMOBILIZADO'', ''NM_GRUPO_CAIXA'', NULL) '|| 
                    'ORDER BY dt_vencimento ';                   
   'OPEN ret FOR v_CONSULTA'; 
   p_retorno := ret; 
  'END'; 
END;

[Tineks]

Esse erro é porque o myrefcur estava declararo na spec da package, eu coloquei ela na procedure, tente criar ela agora... segue o codigo dela abaixo.

Selecionar tudo

CREATE OR REPLACE PROCEDURE pr_pesquisa_imobilizado (p_retorno OUT myrefcur)
IS
   TYPE myrefcur IS REF CURSOR;

   ret          myrefcur;
   v_consulta   VARCHAR2 (10000);
BEGIN
   v_consulta :=
         'SELECT   /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */ '
      || '         gc.cd_ordem,  '
      || '         sg.nm_sub_grupo_contabil nm_grupo_caixa, '
      || '         fn_venc_data (TO_DATE (VDATE1, ''DD MM YYYY''), '
      || '                       TO_DATE (VDATE2, ''DD MM YYYY''), '
      || '                       mp.dt_vencimento, '
      || '                       mp.dt_vencimento_boleta, '
      || '                       mp.dt_prorrogacao, '
      || '                       ''2'' '
      || '                      ) AS dt_vencimento, '
      || '         SUM (  NVL (mp.vl_parcela, 0.00) '
      || '              + NVL (mp.vl_encargo, 0.00) '
      || '              - NVL (mp.vl_desconto, 0.00) '
      || '             ) AS vl_fornecedor, '
      || '         MAX (DECODE (nm_grupo_caixa, ''IMOBILIZADO'', ''VL_FORNECEDOR'', '''')) imobilizado '
      || '    FROM tb_sub_grupo_contabil sg, '
      || '         tb_plano_conta pn, '
      || '         tb_pessoa ps, '
      || '         tb_mov_pagar mp, '
      || '         tb_pagar pg, '
      || '         tb_grupo_caixa gc '
      || '   WHERE sg.cd_sub_grupo_contabil = ''1.31'' '
      || '     AND sg.nu_plano = ''1'' '
      || '     AND pn.nu_plano = ''1'' '
      || '     AND sg.cd_sub_grupo_contabil = pn.cd_sub_grupo_contabil '
      || '     AND pn.cd_conta_contabil = mp.cd_conta_contabil '
      || '     AND pg.cd_grupo_caixa = gc.cd_grupo_caixa '
      || '     AND (gc.fl_operacao IN (''A'', ''C'') OR gc.cd_grupo_caixa = ''15005'') '
      || '     AND (   (   (    mp.dt_vencimento_boleta IS NULL '
      || '                  AND mp.dt_prorrogacao IS NULL '
      || '                  AND mp.dt_vencimento BETWEEN fn_conv_data (DATA1) '
      || '                                           AND fn_conv_data (DATA2) '
      || '                 ) '
      || '              OR     mp.dt_prorrogacao BETWEEN fn_conv_data (VDATE1) '
      || '                                           AND fn_conv_data (VDATE2) '
      || '                 AND mp.dt_prorrogacao > '
      || '                        DECODE (mp.dt_vencimento_boleta, '
      || '                                NULL, fn_conv_data (''01/01/1920''), '
      || '                                mp.dt_vencimento_boleta '
      || '                               ) '
      || '             ) '
      || '          OR     mp.dt_vencimento_boleta BETWEEN fn_conv_data (VDATE1) '
      || '                                             AND fn_conv_data (VDATE2) '
      || '             AND mp.dt_vencimento_boleta > '
      || '                    DECODE (mp.dt_prorrogacao, '
      || '                            NULL, fn_conv_data (''01/01/1920''), '
      || '                            mp.dt_prorrogacao '
      || '                           ) '
      || '         ) '
      || '     AND mp.dt_liquidacao IS NULL '
      || '     AND ps.cd_pessoa = pg.cd_pessoa '
      || '     AND pg.cd_pessoa <> ''34629005'' '
      || '     AND pg.cd_pessoa <> ''36392005'' '
      || '     AND pg.cd_pessoa <> ''3441005'' '
      || '     AND pg.cd_pessoa <> ''34903005'' '
      || '     AND pg.cd_pagar = mp.cd_pagar '
      || '     AND mp.cd_conta_contabil LIKE ''%'' '
      || '     AND mp.cd_conta_contabil <> ''25.10000'' '
      || '     AND ((mp.cd_banco || '''' <> ''299'') OR mp.cd_banco || '''' IS NULL) '
      || '     AND (mp.ds_obs NOT LIKE ''TRANSFERENCIA INTERNA%'' OR mp.ds_obs IS NULL) '
      || '     AND (   (mp.fl_posicao <> ''L'' AND mp.fl_posicao <> ''C'') '
      || '          OR mp.fl_posicao IS NULL '
      || '         ) '
      || 'GROUP BY gc.cd_ordem, '
      || '         sg.nm_sub_grupo_contabil, '
      || '         fn_venc_data (TO_DATE (VDATE1, ''DD MM YYYY''), '
      || '                       TO_DATE (VDATE2, ''DD MM YYYY''), '
      || '                       mp.dt_vencimento, '
      || '                       mp.dt_vencimento_boleta, '
      || '                       mp.dt_prorrogacao, '
      || '                       ''2'' '
      || '                      ), '
      || '         DECODE (nm_grupo_caixa, ''IMOBILIZADO'', ''NM_GRUPO_CAIXA'', NULL) '
      || 'ORDER BY dt_vencimento ';

   OPEN ret FOR v_consulta;

   p_retorno := ret;
END;

[wilke]

Amigo ainda esta dando esse erro PLS-00201: identifier 'MYREFCUR' must be declared que sera que ainda pode ser , mas eu tenho certeza que agente vai conseguir vencer essa procedure.

[Tineks]

Olá Wilke, desculpa, mas não prestei atenção no código anterior, ele deu erro pois na spec já é utilizado o "myrefcur", porem eu disse pra você colocar o código no corpo da procedure, dessa forma esta errado mesmo..
Ao invez de você criar uma procedure crie uma package, como eu passei no começo desse topico pra você... pois na spec da package eu havia criado o type myrefcur, dessa forma não da erro.. abaixo eu montei essa package pra você, e já atualizei o select dela da forma que você precisa, segue abaixo o código dela..

Selecionar tudo

CREATE OR REPLACE PACKAGE PCK_CRISTIANO
IS

   type myrefcur is ref CURSOR;
   PROCEDURE  prc_consulta (p_retorno OUT myrefcur);

END;

/
CREATE OR REPLACE PACKAGE BODY PCK_CRISTIANO
IS
   PROCEDURE prc_consulta (p_retorno OUT myrefcur)
   IS
      ret   myrefcur;
      v_CONSULTA VARCHAR2(10000);
   BEGIN
    v_consulta := 
         'SELECT   /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */ ' 
      || '         gc.cd_ordem,  ' 
      || '         sg.nm_sub_grupo_contabil nm_grupo_caixa, ' 
      || '         fn_venc_data (TO_DATE (VDATE1, ''DD MM YYYY''), ' 
      || '                       TO_DATE (VDATE2, ''DD MM YYYY''), ' 
      || '                       mp.dt_vencimento, ' 
      || '                       mp.dt_vencimento_boleta, ' 
      || '                       mp.dt_prorrogacao, ' 
      || '                       ''2'' ' 
      || '                      ) AS dt_vencimento, ' 
      || '         SUM (  NVL (mp.vl_parcela, 0.00) ' 
      || '              + NVL (mp.vl_encargo, 0.00) ' 
      || '              - NVL (mp.vl_desconto, 0.00) ' 
      || '             ) AS vl_fornecedor, ' 
      || '         MAX (DECODE (nm_grupo_caixa, ''IMOBILIZADO'', ''VL_FORNECEDOR'', '''')) imobilizado ' 
      || '    FROM tb_sub_grupo_contabil sg, ' 
      || '         tb_plano_conta pn, ' 
      || '         tb_pessoa ps, ' 
      || '         tb_mov_pagar mp, ' 
      || '         tb_pagar pg, ' 
      || '         tb_grupo_caixa gc ' 
      || '   WHERE sg.cd_sub_grupo_contabil = ''1.31'' ' 
      || '     AND sg.nu_plano = ''1'' ' 
      || '     AND pn.nu_plano = ''1'' ' 
      || '     AND sg.cd_sub_grupo_contabil = pn.cd_sub_grupo_contabil ' 
      || '     AND pn.cd_conta_contabil = mp.cd_conta_contabil ' 
      || '     AND pg.cd_grupo_caixa = gc.cd_grupo_caixa ' 
      || '     AND (gc.fl_operacao IN (''A'', ''C'') OR gc.cd_grupo_caixa = ''15005'') ' 
      || '     AND (   (   (    mp.dt_vencimento_boleta IS NULL ' 
      || '                  AND mp.dt_prorrogacao IS NULL ' 
      || '                  AND mp.dt_vencimento BETWEEN fn_conv_data (DATA1) '  --* VERIFICAR
      || '                                           AND fn_conv_data (DATA2) '  --* VERIFICAR
      || '                 ) ' 
      || '              OR     mp.dt_prorrogacao BETWEEN fn_conv_data (VDATE1) '   --* VERIFICAR
      || '                                           AND fn_conv_data (VDATE2) '   --* VERIFICAR
      || '                 AND mp.dt_prorrogacao > ' 
      || '                        DECODE (mp.dt_vencimento_boleta, ' 
      || '                                NULL, fn_conv_data (''01/01/1920''), ' 
      || '                                mp.dt_vencimento_boleta ' 
      || '                               ) ' 
      || '             ) ' 
      || '          OR     mp.dt_vencimento_boleta BETWEEN fn_conv_data (VDATE1) '   --* VERIFICAR
      || '                                             AND fn_conv_data (VDATE2) '   --* VERIFICAR
      || '             AND mp.dt_vencimento_boleta > ' 
      || '                    DECODE (mp.dt_prorrogacao, ' 
      || '                            NULL, fn_conv_data (''01/01/1920''), ' 
      || '                            mp.dt_prorrogacao ' 
      || '                           ) ' 
      || '         ) ' 
      || '     AND mp.dt_liquidacao IS NULL ' 
      || '     AND ps.cd_pessoa = pg.cd_pessoa ' 
      || '     AND pg.cd_pessoa <> ''34629005'' ' 
      || '     AND pg.cd_pessoa <> ''36392005'' ' 
      || '     AND pg.cd_pessoa <> ''3441005'' ' 
      || '     AND pg.cd_pessoa <> ''34903005'' ' 
      || '     AND pg.cd_pagar = mp.cd_pagar ' 
      || '     AND mp.cd_conta_contabil LIKE ''%'' ' 
      || '     AND mp.cd_conta_contabil <> ''25.10000'' ' 
      || '     AND ((mp.cd_banco || '''' <> ''299'') OR mp.cd_banco || '''' IS NULL) ' 
      || '     AND (mp.ds_obs NOT LIKE ''TRANSFERENCIA INTERNA%'' OR mp.ds_obs IS NULL) ' 
      || '     AND (   (mp.fl_posicao <> ''L'' AND mp.fl_posicao <> ''C'') ' 
      || '          OR mp.fl_posicao IS NULL ' 
      || '         ) ' 
      || 'GROUP BY gc.cd_ordem, ' 
      || '         sg.nm_sub_grupo_contabil, ' 
      || '         fn_venc_data (TO_DATE (VDATE1, ''DD MM YYYY''), ' 
      || '                       TO_DATE (VDATE2, ''DD MM YYYY''), ' 
      || '                       mp.dt_vencimento, ' 
      || '                       mp.dt_vencimento_boleta, ' 
      || '                       mp.dt_prorrogacao, ' 
      || '                       ''2'' ' 
      || '                      ), ' 
      || '         DECODE (nm_grupo_caixa, ''IMOBILIZADO'', ''NM_GRUPO_CAIXA'', NULL) ' 
      || 'ORDER BY dt_vencimento '; 
                
      OPEN ret FOR v_CONSULTA;
      p_retorno := ret;
   END;

END;

OBS: no select acima eu coloquei em algumas linhas o texto "--* VERIFICAR", eu fiz isso pois notei que dentro do select existe alguns nomes como data2, vdate2.. se esses nomes forem de variaveis o select não vai funcionar, ai você precisaria mexer nesse ponto.

[]'s

[wilke]

Cristiano essa string, elas são obrigatoria, pois aonde não tem ,dar esse erro PLS-00103: Encountered the symbol "V_CONSULTA" when expecting one
of the following:
select

PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure form external

[wilke]

Cristiano eu Consegui Fazendo assim

Selecionar tudo

CREATE PROCEDURE PR_PESQUISA_IMOBILIZADO (
                                                      DATA1 IN VARCHAR2,
                                                      DATA2 IN VARCHAR2,
                                                      VDATE1 IN VARCHAR2,
                                                      VDATE2 IN VARCHAR2)IS
CURSOR CR_IMOBILIZADO IS
         SELECT   /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */
                 gc.cd_ordem,
                 sg.nm_sub_grupo_contabil nm_grupo_caixa,
                 fn_venc_data (TO_DATE (VDATE1, 'DD MM YYYY'),
                             TO_DATE (VDATE2, 'DD MM YYYY'),
                             mp.dt_vencimento,
                             mp.dt_vencimento_boleta,
                             mp.dt_prorrogacao,
                             '2'
                              ) AS dt_vencimento,
                   SUM (  NVL (mp.vl_parcela, 0.00)
                    + NVL (mp.vl_encargo, 0.00)
                       - NVL (mp.vl_desconto, 0.00)
                       ) AS vl_fornecedor,
               MAX (DECODE (nm_grupo_caixa, 'IMOBILIZADO', 'VL_FORNECEDOR','' )) imobilizado
              FROM tb_sub_grupo_contabil sg,
                   tb_plano_conta pn,
                   tb_pessoa ps,
                   tb_mov_pagar mp,
                   tb_pagar pg,
                   tb_grupo_caixa gc
             WHERE sg.cd_sub_grupo_contabil = '1.31'
               AND sg.nu_plano = '1'
               AND pn.nu_plano = '1'
               AND sg.cd_sub_grupo_contabil = pn.cd_sub_grupo_contabil
               AND pn.cd_conta_contabil = mp.cd_conta_contabil
               AND pg.cd_grupo_caixa = gc.cd_grupo_caixa
               AND (gc.fl_operacao IN ('A', 'C') OR gc.cd_grupo_caixa = '15005')
               AND (   (   (    mp.dt_vencimento_boleta IS NULL
                            AND mp.dt_prorrogacao IS NULL
                          AND mp.dt_vencimento BETWEEN fn_conv_data (DATA1)   --* VERIFICAR
                                                   AND fn_conv_data (DATA2)   --* VERIFICAR
                          )
                        OR     mp.dt_prorrogacao BETWEEN fn_conv_data (VDATE1)    --* VERIFICAR
                                                  AND fn_conv_data (VDATE2)    --* VERIFICAR
                          AND mp.dt_prorrogacao >
                                DECODE (mp.dt_vencimento_boleta,
                                      NULL, fn_conv_data ('01/01/1920'),
                                         mp.dt_vencimento_boleta
                                          )
                    )
                    OR     mp.dt_vencimento_boleta BETWEEN fn_conv_data (VDATE1)    --* VERIFICAR
                                                   AND fn_conv_data (VDATE2)    --* VERIFICAR
                      AND mp.dt_vencimento_boleta >
                             DECODE (mp.dt_prorrogacao,
                                   NULL, fn_conv_data ('01/01/1920'),
                                  mp.dt_prorrogacao
                                 )
               )
             AND mp.dt_liquidacao IS NULL
             AND ps.cd_pessoa = pg.cd_pessoa
             AND pg.cd_pessoa <> '34629005'
             AND pg.cd_pessoa <> '36392005'
             AND pg.cd_pessoa <> '3441005 '
             AND pg.cd_pessoa <> '34903005'
             AND pg.cd_pagar = mp.cd_pagar
             AND mp.cd_conta_contabil LIKE '%'
             AND mp.cd_conta_contabil <> '25.10000'
             AND ((mp.cd_banco || '' <> '299') OR mp.cd_banco || '' IS NULL)
             AND (mp.ds_obs NOT LIKE 'TRANSFERENCIA INTERNA%' OR mp.ds_obs IS NULL)
             AND (   (mp.fl_posicao <> 'L' AND mp.fl_posicao <> 'C')
                 OR mp.fl_posicao IS NULL
                )
        GROUP BY gc.cd_ordem,
               sg.nm_sub_grupo_contabil,
               fn_venc_data (TO_DATE (VDATE1, 'DD MM YYYY'),
                             TO_DATE (VDATE2, 'DD MM YYYY'),
                             mp.dt_vencimento,
                             mp.dt_vencimento_boleta,
                             mp.dt_prorrogacao,
                             '2'
                            ),
               DECODE (nm_grupo_caixa, 'IMOBILIZADO', 'NM_GRUPO_CAIXA', NULL)
      ORDER BY dt_vencimento;
ST_IMOBILIZADO CR_IMOBILIZADO%ROWTYPE;
 BEGIN
 OPEN CR_IMOBILIZADO;
 FETCH CR_IMOBILIZADO INTO ST_IMOBILIZADO;
 CLOSE CR_IMOBILIZADO;
END;

[Tineks]

E ai Wilke, beleza??

cara, dessa forma que você fez o programa compila numa boa, porem dessa forma o resultado do select está sendo atribuido a uma variavel que está dentro da sua procedure, você não vao conseguir ler esses dados a partir de outro ponto do seu programa..

[]'s

[wilke]

e se eu poder usar dessa forma, o que eu acrensentaria , sendo que eu preciso do campo vl_fornecedor e dt_vencimento ,como saida e as datas como entrada?.

[wilke]

Cristiano eu tenho feito assim , mas ainda da error você poderia verificar aonde eu tenho errado nessa procedure desde já eu te agradeço,

Selecionar tudo

CREATE  OR REPLACE PROCEDURE PR_PESQUISA_IMOBILIZADO (VL_FORNECEDOR OUT VARCHAR2,
                                                      DT_VENCIMENTO OUT VARCHAR2,
                                                      DATA1 IN VARCHAR2, 
                                                      DATA2 IN VARCHAR2, 
                                                      VDATE1 IN VARCHAR2, 
                                                      VDATE2 IN VARCHAR2)IS 
TYPE TPCR_IMOBILIZADO IS REF CURSOR;
CR_IMOBILIZADO TPCR_IMOBILIZADO;
V_CONSULTA VARCHAR2(10000);
V_AUX VARCHAR2(10000);
BEGIN 
    V_CONSULTA:= 'SELECT   /*+ INDEX( PR IX_PAGAR_PRODUTOR ) */'|| 
                 'gc.cd_ordem,'|| 
                 'sg.nm_sub_grupo_contabil nm_grupo_caixa,'|| 
                  fn_venc_data (TO_DATE (VDATE1, 'DD MM YYYY'), 
                               TO_DATE (VDATE2, 'DD MM YYYY'), 
                            ' mp.dt_vencimento,'|| 
                            ' mp.dt_vencimento_boleta,'|| 
                            ' mp.dt_prorrogacao,'|| 
                            '2'|| 
                            '  )AS dt_vencimento,'|| 
                   SUM(  NVL (mp.vl_parcela, 0.00)|| 
                    + NVL (mp.vl_encargo, 0.00)|| 
                       - NVL (mp.vl_desconto, 0.00)|| 
                       ') AS vl_fornecedor'|| 
                
              ' FROM'||
                    'tb_sub_grupo_contabil sg,'|| 
                    'tb_plano_conta pn,'|| 
                    'tb_pessoa ps,'|| 
                    'tb_mov_pagar mp,'|| 
                    'tb_pagar pg,'|| 
                    'tb_grupo_caixa gc'|| 
                ' WHERE'|| 
                 'sg.cd_sub_grupo_contabil = ''1.31'''|| 
                ' AND sg.nu_plano = ''1'''|| 
                ' AND pn.nu_plano = ''1'''|| 
                ' AND sg.cd_sub_grupo_contabil = pn.cd_sub_grupo_contabil'|| 
                ' AND pn.cd_conta_contabil = mp.cd_conta_contabil'|| 
                ' AND pg.cd_grupo_caixa = gc.cd_grupo_caixa'|| 
                ' AND (gc.fl_operacao IN (''A'', ''C'') OR gc.cd_grupo_caixa = ''15005'')'|| 
                ' AND (   (   (    mp.dt_vencimento_boleta IS NULL'|| 
                            AND mp.dt_prorrogacao IS NULL 
                          AND mp.dt_vencimento BETWEEN fn_conv_data (DATA1)   --* VERIFICAR 
                                                   AND fn_conv_data (DATA2)   --* VERIFICAR 
                          ) 
                        OR     mp.dt_prorrogacao BETWEEN fn_conv_data (VDATE1)    --* VERIFICAR 
                                                  AND fn_conv_data (VDATE2)    --* VERIFICAR 
                          AND mp.dt_prorrogacao > 
                                DECODE (mp.dt_vencimento_boleta, 
                                      NULL, fn_conv_data ('01/01/1920'), 
                                         mp.dt_vencimento_boleta 
                                          )
                    ) 
                    OR     mp.dt_vencimento_boleta BETWEEN fn_conv_data (VDATE1)    --* VERIFICAR 
                                                   AND fn_conv_data (VDATE2)    --* VERIFICAR 
                      AND mp.dt_vencimento_boleta > 
                             DECODE (mp.dt_prorrogacao, 
                                   NULL, fn_conv_data ('01/01/1920'), 
                                  mp.dt_prorrogacao 
                                 ) 
               ) 
        '     AND mp.dt_liquidacao IS NULL'|| 
        '     AND ps.cd_pessoa = pg.cd_pessoa' || 
        '     AND pg.cd_pessoa <> ''34629005'''|| 
        '     AND pg.cd_pessoa <> ''36392005'''|| 
        '     AND pg.cd_pessoa <> ''3441005'' '|| 
        '     AND pg.cd_pessoa <> ''34903005'''|| 
        '     AND pg.cd_pagar = mp.cd_pagar'|| 
        '     AND mp.cd_conta_contabil LIKE ''%'''|| 
        '     AND mp.cd_conta_contabil <> ''25.10000'''|| 
        '     AND ((mp.cd_banco || '' <> ''299'') OR mp.cd_banco || '' IS NULL)'|| 
        '     AND (mp.ds_obs NOT LIKE ''TRANSFERENCIA INTERNA%'' OR mp.ds_obs IS NULL)'|| 
        '     AND (   (mp.fl_posicao <> ''L'' AND mp.fl_posicao <> ''C'')'|| 
        '         OR mp.fl_posicao IS NULL'|| 
        '        )'|| 
        'GROUP BY gc.cd_ordem,'|| 
               'sg.nm_sub_grupo_contabil,'|| 
               fn_venc_data (TO_DATE (VDATE1, 'DD MM YYYY'), 
                             TO_DATE (VDATE2, 'DD MM YYYY'), 
                           ' mp.dt_vencimento,'|| 
                           ' mp.dt_vencimento_boleta,'|| 
                           ' mp.dt_prorrogacao,'|| 
                           '2' || 
                           ' ), '|| 
   DECODE (nm_grupo_caixa, 'IMOBILIZADO', 'NM_GRUPO_CAIXA', NULL)|| 
   ORDER BY dt_vencimento; 
   OPEN CR_IMOBILIZADO FOR V_CONSULTA; 
   LOOP 
   FETCH CR_IMOBILIZADO INTO V_AUX; 
   DBMS_OUTPUT.PUT_LINE(trim(V_AUX));
   END LOOP;
   CLOSE CR_IMOBILIZADO;
   END PR_PESQUISA_IMOBILIZADO;

[Tineks]

Selecionar tudo

' ), '|| 
   DECODE (nm_grupo_caixa, 'IMOBILIZADO', 'NM_GRUPO_CAIXA', NULL)|| 
   ORDER BY dt_vencimento; 

Até o ponto da primeira linha você utilizou aspas em todas as linhas, nas ultimas esta sem.

Selecionar tudo

                 'gc.cd_ordem,'|| 
                 'sg.nm_sub_grupo_contabil nm_grupo_caixa,'|| 
                  fn_venc_data (TO_DATE (VDATE1, 'DD MM YYYY'), 
                               TO_DATE (VDATE2, 'DD MM YYYY'), 
                            ' mp.dt_vencimento,'|| 
                            ' mp.dt_vencimento_boleta,'|| 
                            ' mp.dt_prorrogacao,'|| 
                            '2'|| 
                            '  )AS dt_vencimento,'|| 
                   SUM(  NVL (mp.vl_parcela, 0.00)|| 
                    + NVL (mp.vl_encargo, 0.00)|| 
                       - NVL (mp.vl_desconto, 0.00)|| 
                       ') AS vl_fornecedor'|| 

No seu select você informa varios campos, porem no trecho abaixo

Selecionar tudo

FETCH CR_IMOBILIZADO INTO V_AUX; 

você atribui uma unica variavel pra receber todos os campos, dessa forma não vai funcionar.

está faltando atribuir os valores da consulta as variaveis

Selecionar tudo

~
(VL_FORNECEDOR OUT VARCHAR2, 
DT_VENCIMENTO OUT VARCHAR2,

OBS, mesmo arrumando os ponto acima, dessa forma você vai ter problemas caso sua consulta retorne mais de um registro.. pois nos parametros você vai conseguir fazer o OUT somente de um registro..

[]s