Jump to content
Sign in to follow this  
Fabio Silva Santos

Relatório De Provisões De Férias E 13 Salário

Recommended Posts

Bom dia alguém teria um relatório de provisões de férias e de 13 salário que traga os valores correspondente ao salário e seção da época, considerando os históricos de seção e salário e transferências entre seções do mesmo nível, seções de outro nível.

Consegui chegar nesta SQL, mas quando funcionário é transferido, e ao gerar o relatório com referencia a competências passadas o valor não bate, pois falta os funcionários transferidos.

SELECT C.NROCENCUSTOCONT QUEBRA$,

F.NOME AS CC,

A.CODCOLIGADA,

A.CHAPA,

A.ANO,

A.MES,

E.NOME,

E.dataadmissao,

E.dtvencferias,

A.nroavosvencferdec || ' / ' || A.nroavosproporcdec,

/*A.valprovfer,*/

I.vaLOR,

E.nrodiasferias,

CAST (CASE WHEN C.NROCENCUSTOCONT IS NULL THEN 'NAO SECAO ' || D.CODSECAO ||'NAO TEM CENTRO DE CUSTO AJUSTAR' ELSE C.NROCENCUSTOCONT END AS VARCHAR(10)) /* A.CODPCCUSTO CENTRO DE CUSTO DA SE«√O DA EPOCA)*/

AS CENTRO_CUSTO,

H.SALARIO

FROM rm.pfhstprov A

LEFT OUTER JOIN (

SELECT AA.CODCOLIGADA,

AA.CHAPA,

MAX (AA.DTMUDANCA) AS DTMUDANCA,

Z.ANO,

Z.MES

FROM RM.PFHSTSEC AA

LEFT OUTER JOIN (

SELECT Z.CODCOLIGADA,

Z.CHAPA,

to_date (CASE WHEN TO_CHAR(E.DATAADMISSAO,'YYYY')=Z.ANO AND TO_CHAR(E.DATAADMISSAO,'MM')=Z.MES

THEN CAST(TO_CHAR(E.DATAADMISSAO,'DD') AS VARCHAR(10))

ELSE '1' END

||'/'||

CAST (Z.MES AS VARCHAR(10))

||'/'||

CAST (Z.ANO AS VARCHAR(10))

, 'DD/MM/YYYY' )/* PARA ORACLE MUDAR PARA DATE SQL PARA DATETIME*/

AS DATA1, Z.MES, Z.ANO

FROM rm.pfhstprov Z

LEFT OUTER JOIN RM.PFUNC E ON Z.CODCOLIGADA=E.CODCOLIGADA AND E.CHAPA=Z.CHAPA

WHERE Z.MES=:MES AND Z.ANO=:ANO

) Z ON Z.CODCOLIGADA=AA.CODCOLIGADA AND Z.CHAPA=AA.CHAPA /* AND Z.DATA1>=AA.DTMUDANCA*/

GROUP BY AA.CODCOLIGADA,AA.CHAPA,Z.ANO, Z.MES) B ON A.CODCOLIGADA=B.CODCOLIGADA AND

A.CHAPA=B.CHAPA AND

A.MES=B.MES AND

A.ANO=B.ANO

LEFT OUTER JOIN RM.PFHSTSEC D ON B.CODCOLIGADA=D.CODCOLIGADA AND D.CHAPA=B.CHAPA AND D.DTMUDANCA=B.DTMUDANCA

LEFT OUTER JOIN RM.PSECAO C ON D.CODCOLIGADA=C.CODCOLIGADA AND D.CODSECAO=C.CODIGO

LEFT OUTER JOIN RM.PFUNC E ON A.CODCOLIGADA=E.CODCOLIGADA AND A.CHAPA=E.CHAPA

LEFT OUTER JOIN RM.PCCUSTO F ON C.CODCOLIGADA=F.CODCOLIGADA AND C.NROCENCUSTOCONT=F.CODCCUSTO

LEFT OUTER JOIN (

SELECT AA.CODCOLIGADA,

AA.CHAPA,

MAX (AA.DTMUDANCA) AS DTMUDANCA,

Z.ANO,

Z.MES

FROM RM.PFHSTSAL AA

LEFT OUTER JOIN (

SELECT Z.CODCOLIGADA,

Z.CHAPA,

to_date (CASE WHEN TO_CHAR(E.DATAADMISSAO,'YYYY')=Z.ANO AND TO_CHAR(E.DATAADMISSAO,'MM')=Z.MES

THEN CAST(TO_CHAR(E.DATAADMISSAO,'DD') AS VARCHAR(10))

ELSE '1' END

||'/'||

CAST (Z.MES AS VARCHAR(10))

||'/'||

CAST (Z.ANO AS VARCHAR(10))

, 'DD/MM/YYYY' )/* PARA ORACLE MUDAR PARA DATE SQL PARA DATETIME*/

AS DATA1, Z.MES, Z.ANO

FROM rm.pfhstPROV Z

LEFT OUTER JOIN RM.PFUNC E ON Z.CODCOLIGADA=E.CODCOLIGADA AND E.CHAPA=Z.CHAPA ) Z ON Z.CODCOLIGADA=AA.CODCOLIGADA AND Z.CHAPA=AA.CHAPA AND Z.DATA1>=AA.DTMUDANCA

GROUP BY AA.CODCOLIGADA,AA.CHAPA,Z.ANO, Z.MES) G ON A.CODCOLIGADA=G.CODCOLIGADA AND

A.CHAPA=G.CHAPA AND

A.MES=G.MES AND

A.ANO=G.ANO

LEFT OUTER JOIN RM.PFHSTSAL H ON G.CODCOLIGADA=H.CODCOLIGADA AND H.CHAPA=G.CHAPA AND H.DTMUDANCA=G.DTMUDANCA

LEFT OUTER JOIN RM.PFENCARGO I ON A.CODCOLIGADA=I.CODCOLIGADA AND A.CHAPA=I.CHAPA

WHERE C.CODIGO LIKE:CSECAO

AND A.CODCOLIGADA=:COLIGADA

AND A.ANO=:ANO

AND A.MES=:MES

AND I.ANOCOMP=:ANO

AND I.MESCOMP=:MES

AND I.CODENCARGO='05'

AND A.nroavosvencferdec =0

AND A.nroavosproporcdec =0

ORDER BY a.ano,a.mes,F.CODCCUSTO,E.NOME

Atenciosamente.

Fabio Silva

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.