Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- *,
- COUNT(1) OVER() AS TOTAL_COUNT
- FROM
- (SELECT * FROM (
- SELECT
- rf.fun_matricula,
- rf.fun_cod,
- rf.fun_pescod,
- bfunc.pes_nome,
- bstatus.sta_titulo,
- ljs.lanjusstat_status,
- lpad(CAST(get_mes(cp.comp_data) AS VARCHAR), 2, '0') || '/' || get_ano(cp.comp_data) AS competencia
- FROM pto_lan_justificativa_status ljs
- INNER JOIN pto_lan_justificativa lj
- ON ljs.lanjusstat_lanjuscod = lj.lanjus_cod
- INNER JOIN rh_funcionario rf
- ON lj.lanjus_funcod = rf.fun_cod
- INNER JOIN pto_competencia cp
- ON
- (
- lj.lanjus_ate_dia IS NULL
- AND lj.lanjus_do_dia BETWEEN cp.comp_data_inicio AND cp.comp_data_fim
- )
- OR
- (
- lj.lanjus_ate_dia IS NOT NULL
- AND lj.lanjus_do_dia >= cp.comp_data_inicio
- AND lj.lanjus_do_dia <= cp.comp_data_fim
- )
- -- local de trabalho do lancamento
- INNER JOIN bas_pessoa bfunc
- ON rf.fun_pescod = bfunc.pes_cod
- INNER JOIN bas_status bstatus
- ON ljs.lanjusstat_status = bstatus.sta_numero
- AND bstatus.sta_tipo = 'STATUS_JUSTIFICATIVA'
- INNER JOIN pto_motivo_ausencia pma
- ON lj.lanjus_motauscod = pma.motaus_cod
- LEFT JOIN pto_motivo_ausencia_gusu mag
- ON mag.motausgusu_motauscod = lj.lanjus_motauscod
- AND mag.motausgusu_gusucod IN (
- SELECT
- gus.gusu_cod
- FROM bas_grupo_usuario gus
- INNER JOIN bas_usuario us
- ON us.usu_gusucod = gus.gusu_cod
- AND us.usu_cod = 1111
- )
- WHERE 1=1
- AND ljs.lanjusstat_ativo = 1
- AND (
- pma.motaus_ativo = 2
- OR (
- pma.motaus_ativo <> 2
- AND mag.motausgusu_cod IS NOT NULL
- )
- )
- AND (
- -- permissao local trabalho
- (
- SELECT
- count(1)
- FROM bas_local_trabalho lt_perm
- INNER JOIN edu_escola e_perm
- ON e_perm.escol_pescod = lt_perm.lctrab_pescod
- INNER JOIN edu_escola_usuario u_perm
- ON u_perm.escolusu_escolcod = e_perm.escol_cod
- AND u_perm.escolusu_usucod = 1111
- WHERE 1=1
- AND lt_perm.lctrab_cod = lj.lanjus_lctrabcod
- ) > 0
- )
- GROUP BY
- rf.fun_matricula,
- rf.fun_cod,
- rf.fun_pescod,
- bfunc.pes_nome,
- bstatus.sta_titulo,
- ljs.lanjusstat_status,
- cp.comp_data
- ORDER BY
- bfunc.pes_nome
- ) as view_filter WHERE 1=1
- AND remove_acento( CAST ( FUN_PESCOD AS varchar )) LIKE remove_acento('%13313%')) as view_pagging
- LIMIT 10 OFFSET 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement