Query_Exemplo

SELECT
     p.cdg_ordem,
     t.cdg_funcional,
     p.nome,
     t.exercicio,
     DECODE(NVL(t.ref_pagamento,0),0,'''',SUBSTR(t.ref_pagamento,5,2)
                                          || '' / ''
                                          || SUBSTR(t.ref_pagamento,1,4)
           ) AS ref_pag_1,
     t.dias_usufruidos_1,
     t.dias_n_usufruidos_1,
     DECODE(NVL(t.ref_pagamento2,0),0,'''',SUBSTR(t.ref_pagamento2,5,2)
                                           || '' / ''
                                           || SUBSTR(t.ref_pagamento2,1,4)
           ) AS ref_pag_2,
     t.dias_usufruidos_2,
     t.dias_n_usufruidos_2,
     t.dias_total_usufruidos,
     t.dias_pagamento,
     t.dias_total_n_usufruidos,
     t.cdg_sitfunc,
     t.cdg_gru_cargo,
     c.cdg_cargo,
     c.nme_cargo
  FROM
     usistema.tbcadfuncional fun,
     usistema.tbcadpessoal p,
     usistema.tbdcargo dc,
     usistema.tbcargo c,
     TABLE ( usistema.ferias_usuf_pend_servidores(:cdg_orgao,:ref_anomes,:cdg_sitfunc,:cdg_gru_cargo,:cdg_ordem,:exercicio_inicio,:exercicio_fim) ) t
 WHERE
        ( fun.cdg_orgao     =:cdg_orgao )
    AND ( fun.ref_anomes    =:ref_anomes )
    AND ( fun.id_aux        =:id_aux )
    AND ( fun.status        NOT IN (5 ) )
    AND ( fun.cdg_ordem     = t.cdg_ordem )
    AND ( fun.cdg_funcional = t.cdg_funcional )
    AND ( p.cdg_ordem       = fun.cdg_ordem )
    AND ( dc.cdg_orgao      =:cdg_orgao )
    AND ( dc.cdg_ordem      = fun.cdg_ordem )
    AND ( dc.cdg_funcional  = fun.cdg_funcional )
    AND (    dc.dta_ini_cargo  = (SELECT MAX(dc2.dta_ini_cargo)
                                    FROM usistema.tbdcargo dc2
                                   WHERE ( dc2.cdg_orgao      = :cdg_orgao )
                                     AND ( dc2.cdg_ordem      = dc.cdg_ordem )
                                     AND ( dc2.cdg_funcional  = dc.cdg_funcional )
                                     AND ( TO_NUMBER ( TO_CHAR( dc2.dta_ini_cargo,''YYYYMM'')) <= :REF_ANOMES)
                                 )
         AND ((TO_NUMBER(TO_CHAR(DC2.DTA_FIM_CARGO,''YYYYMM'')) >= :REF_ANOMES) OR (DC2.DTA_FIM_CARGO IS NULL) )
        ))
    AND ((DC.ID_CARGO    = 1))
    AND (C.CDG_ORGAO     = :CDG_ORGAO)
    AND (C.CDG_CARGO     = DC.CDG_CARGO)
    AND (C.DTA_INI_CARGO = (SELECT MAX(C2.DTA_INI_CARGO) FROM USISTEMA.TBCARGO C2
                             WHERE (C2.CDG_ORGAO = C.CDG_ORGAO)
                               AND (C2.CDG_CARGO = C.CDG_CARGO)
                               AND (TO_NUMBER(TO_CHAR(C2.DTA_INI_CARGO,'' yyyymm '' ) ) <=:ref_anomes)
                            )
        )
    AND ( t.dias_total_n_usufruidos > 0 )
ORDER BY
    c.nme_cargo,
    p.nome,
    t.exercicio