--SQL Ajustada, adicionada a coluna CODSECAO - AMOVFUN.6140<br />
<br />
DECLARE @COL SMALLINT, @CHAPA VARCHAR(10), @NOMEFUNCAO VARCHAR(100), @DTINI DATETIME, @DTFIM DATETIME, @VALORCHEIO FLOAT, @SALBASE FLOAT,@SALARIO FLOAT, @MES SMALLINT, @ANO SMALLINT<br />
SET @COL     = :FRM_COLIGADA<br />
SET @CHAPA   = :FRM_CHAPA<br />
<br />
SET @NOMEFUNCAO = (SELECT B.NOME FROM PFUNC A (NOLOCK), PFUNCAO B (NOLOCK) WHERE B.CODCOLIGADA=A.CODCOLIGADA AND B.CODIGO=A.CODFUNCAO AND A.CODCOLIGADA=@COL AND A.CHAPA=@CHAPA)<br />
IF @NOMEFUNCAO LIKE '%COLETOR%' SET @VALORCHEIO=25.00<br />
ELSE <br />
IF @NOMEFUNCAO LIKE '%MOTORISTA%' SET @VALORCHEIO=30.00<br />
<br />
SET @DTINI = :FRM_INIPER<br />
SET @DTFIM = :FRM_FIMPER<br />
<br />
SELECT @ANO=ANOCOMP, @MES=MESCOMP FROM APERIODO A (NOLOCK) WHERE CODCOLIGADA=@COL AND INICIOMENSAL=@DTINI AND FIMMENSAL=@DTFIM<br />
<br />
SET @SALBASE=(SELECT SALARIO FROM PFUNC WHERE CODCOLIGADA=@COL AND CHAPA=@CHAPA)<br />
<br />
/*Fazer leitura do envelope na base P_RMBackOffice para somar os adicionais*/<br />
SET @SALARIO=(SELECT ISNULL(SUM(ADICIONAIS),0.00) /*+ ISNULL(MIN(SALBASE),0.00)*/<br />
FROM (<br />
SELECT B.SALARIO SALBASE, CASE WHEN A.CODEVENTO LIKE '7%' THEN A.VALOR *(-1) ELSE A.VALOR END AS ADICIONAIS<br />
FROM P_RMBackOffice..PFFINANC A (NOLOCK), PFUNC B (NOLOCK)<br />
WHERE B.CODCOLIGADA=A.CODCOLIGADA AND B.CHAPA=A.CHAPA<br />
AND   A.CODCOLIGADA=@COL AND A.CHAPA=@CHAPA AND A.ANOCOMP=@ANO AND A.MESCOMP=@MES AND A.CODEVENTO IN(SELECT CODCLIENTE FROM GCONSIST (NOLOCK) WHERE APLICACAO='P' AND CODTABELA='INCSAL')<br />
) INCSAL<br />
)<br />
<br />
SET @SALARIO = ISNULL(@SALARIO,0) + @SALBASE<br />
<br />
<br />
SELECT SUM(BONIFICACAO) BONIFICACAO, CODSECAO<br />
FROM (<br />
SELECT DISTINCT A.CHAPA, B.NOME, C.NOME FUNCAO, B.CODSECAO, @SALARIO SALARIO, A.DATA, 0 EXECUTADO, 120 A_EXECUTAR, @VALORCHEIO*DBO.FN_FLAGBATIDAS(A.CODCOLIGADA,A.CHAPA,A.DATA,@DTFIM) BONIFICACAO<br />
<br />
FROM AAFHTFUN A (NOLOCK) LEFT JOIN AABONFUN E (NOLOCK) ON(E.CODCOLIGADA=A.CODCOLIGADA AND E.CHAPA=A.CHAPA AND E.DATA=A.DATA AND E.CODABONO='08')<br />
, PFUNC B (NOLOCK), PFUNCAO C (NOLOCK), PFRATEIOFIXO D (NOLOCK)<br />
, PFCOMPL F (NOLOCK)<br />
WHERE F.CODCOLIGADA=A.CODCOLIGADA AND F.CHAPA=A.CHAPA AND F.DEPTO IN('126','127')<br />
AND   D.CODCOLIGADA=A.CODCOLIGADA AND D.CHAPA=A.CHAPA AND D.CODCCUSTO IN('01.CNT.0002.03','001.CNT.0003.03') AND D.VALOR=(SELECT MAX(Z.VALOR) FROM PFRATEIOFIXO Z (NOLOCK) WHERE Z.CODCOLIGADA=A.CODCOLIGADA AND Z.CHAPA=A.CHAPA)<br />
AND   C.CODCOLIGADA=A.CODCOLIGADA AND C.CODIGO=B.CODFUNCAO AND (C.NOME LIKE '%COLETOR%' OR C.NOME LIKE '%MOTORISTA%')<br />
AND   B.CODCOLIGADA=A.CODCOLIGADA AND B.CHAPA=A.CHAPA<br />
AND   A.CODCOLIGADA=@COL AND A.DATA&gt;=@DTINI AND A.DATA&lt;=@DTFIM AND A.BASE&gt;=20 AND A.EXTRAAUTORIZADO=0 AND  ((A.HTRAB&gt;0.00 AND (A.ABONO=0.00 OR E.CODABONO IN('08') )) OR (A.HTRAB=0.00 AND E.CODABONO IN('08')))<br />
AND   A.CODCOLIGADA=@COL AND A.CHAPA=@CHAPA<br />
AND   (A.CODCOLIGADA=16 OR (A.CODCOLIGADA=7 AND B.CODSECAO='00.CNT.0003.01'))<br />
UNION ALL<br />
SELECT DISTINCT A.CHAPA, B.NOME, C.NOME FUNCAO, B.CODSECAO, @SALARIO SALARIO, A.DATA, A.EXTRAAUTORIZADO EXECUTADO, 120-A.EXTRAAUTORIZADO A_EXECUTAR, (((@SALARIO*1.25)*1.5)/(B.JORNADAMENSAL/60.00)) * ((120-A.EXTRAAUTORIZADO)/60.00) BONIFICACAO<br />
FROM AAFHTFUN A (NOLOCK) LEFT JOIN AABONFUN E (NOLOCK) ON(E.CODCOLIGADA=A.CODCOLIGADA AND E.CHAPA=A.CHAPA AND E.DATA=A.DATA AND E.CODABONO='08')<br />
, PFUNC B (NOLOCK), PFUNCAO C (NOLOCK), PFRATEIOFIXO D (NOLOCK)<br />
, PFCOMPL F (NOLOCK)<br />
WHERE F.CODCOLIGADA=A.CODCOLIGADA AND F.CHAPA=A.CHAPA AND F.DEPTO IN('126','127')<br />
AND   D.CODCOLIGADA=A.CODCOLIGADA AND D.CHAPA=A.CHAPA AND D.CODCCUSTO IN('01.CNT.0002.03','001.CNT.0003.03') AND D.VALOR=(SELECT MAX(Z.VALOR) FROM PFRATEIOFIXO Z (NOLOCK) WHERE Z.CODCOLIGADA=A.CODCOLIGADA AND Z.CHAPA=A.CHAPA)<br />
AND   C.CODCOLIGADA=A.CODCOLIGADA AND C.CODIGO=B.CODFUNCAO AND (C.NOME LIKE '%COLETOR%' OR C.NOME LIKE '%MOTORISTA%')<br />
AND   B.CODCOLIGADA=A.CODCOLIGADA AND B.CHAPA=A.CHAPA<br />
AND   A.DATA&gt;=@DTINI AND A.DATA&lt;=@DTFIM AND A.BASE&gt;=20 AND A.EXTRAAUTORIZADO&gt;0 AND A.EXTRAAUTORIZADO&lt;60 AND ((A.HTRAB&gt;0.00 AND (A.ABONO=0.00 OR E.CODABONO IN('08') )) OR (A.HTRAB=0.00 AND E.CODABONO IN('08')))<br />
AND   A.CODCOLIGADA=@COL AND A.CHAPA=@CHAPA<br />
AND   (A.CODCOLIGADA=16 OR (A.CODCOLIGADA=7 AND B.CODSECAO='00.CNT.0003.01'))<br />
UNION ALL<br />
SELECT DISTINCT A.CHAPA, B.NOME, C.NOME FUNCAO, B.CODSECAO, @SALARIO SALARIO, A.DATA, A.EXTRAEXECUTADO EXECUTADO, 120-A.EXTRAAUTORIZADO A_EXECUTAR, (((@SALARIO*1.20)*1.5)/(B.JORNADAMENSAL/60.00)) * ((120-A.EXTRAAUTORIZADO)/60.00) BONIFICACAO<br />
FROM AAFHTFUN A (NOLOCK) LEFT JOIN AABONFUN E (NOLOCK) ON(E.CODCOLIGADA=A.CODCOLIGADA AND E.CHAPA=A.CHAPA AND E.DATA=A.DATA AND E.CODABONO='08')<br />
, PFUNC B (NOLOCK), PFUNCAO C (NOLOCK), PFRATEIOFIXO D (NOLOCK)<br />
, PFCOMPL F (NOLOCK)<br />
WHERE F.CODCOLIGADA=A.CODCOLIGADA AND F.CHAPA=A.CHAPA AND F.DEPTO IN('126','127')<br />
AND   D.CODCOLIGADA=A.CODCOLIGADA AND D.CHAPA=A.CHAPA AND D.CODCCUSTO IN('01.CNT.0002.03','001.CNT.0003.03') AND D.VALOR=(SELECT MAX(Z.VALOR) FROM PFRATEIOFIXO Z (NOLOCK) WHERE Z.CODCOLIGADA=A.CODCOLIGADA AND Z.CHAPA=A.CHAPA)<br />
AND   C.CODCOLIGADA=A.CODCOLIGADA AND C.CODIGO=B.CODFUNCAO AND (C.NOME LIKE '%COLETOR%' OR C.NOME LIKE '%MOTORISTA%')<br />
AND   B.CODCOLIGADA=A.CODCOLIGADA AND B.CHAPA=A.CHAPA<br />
AND A.DATA&gt;=@DTINI AND A.DATA&lt;=@DTFIM AND A.BASE&gt;=20 AND A.EXTRAAUTORIZADO&gt;=60 AND A.EXTRAAUTORIZADO&lt;120 AND ((A.HTRAB&gt;0.00 AND (A.ABONO=0.00 OR E.CODABONO IN('08') )) OR (A.HTRAB=0.00 AND E.CODABONO IN('08')))<br />
AND   A.CODCOLIGADA=@COL AND A.CHAPA=@CHAPA<br />
AND   (A.CODCOLIGADA=16 OR (A.CODCOLIGADA=7 AND B.CODSECAO='00.CNT.0003.01'))<br />
) BONIFICACAO<br />
GROUP BY CODSECAO