Ocorrência
Como é montada a query para considerar os quadro F e G referencia a receita bruta
Ambiente
Microsiga Protheus – Livros Fiscais – A partir da versão 11.80
Causa
Como é feita a montagem dos valores dos quadro F e G referente a receita bruta
Solução
Query dos Registros F e G (valor bruto )
SELECT MONTH(SF2.F2_EMISSAO) AS EMISSAO,
SUM(SD2.D2_VALBRUT) AS NVALBRUTO,
SUM(SD2.D2_VALICM) AS NVALICM,
SUM(SD2.D2_BASEISS) AS NBASEISS,
SUM(SD2.D2_ICMSRET) AS NICMSRET,
SUM(SFT.FT_VALCONT) AS NVALCONT,
SUM(SFT.FT_VALPIS) AS NVALPIS,
SUM(SFT.FT_VALCOF) AS NVALCOF
FROM SF2990 AS SF2 LEFT OUTER JOIN
SD2990 AS SD2 ON SD2.D2_FILIAL = '01'
AND SF2.F2_DOC = SD2.D2_DOC
AND SF2.F2_SERIE = SD2.D2_SERIE
AND SF2.F2_CLIENTE = SD2.D2_CLIENTE
AND SF2.F2_LOJA = SD2.D2_LOJA
AND SD2.D_E_L_E_T_ = ' '
LEFT OUTER JOIN SFT990 AS SFT ON SFT.FT_FILIAL = '01'
AND SD2.D2_DOC = SFT.FT_NFISCAL
AND SD2.D2_SERIE = SFT.FT_SERIE
AND SD2.D2_CLIENTE = SFT.FT_CLIEFOR AND
SD2.D2_LOJA = SFT.FT_LOJA
AND SD2.D2_ITEM = SFT.FT_ITEM
AND SFT.D_E_L_E_T_ = ' '
LEFT OUTER JOIN SB1990 AS SB1 ON SB1.B1_FILIAL = ' '
AND SD2.D2_COD = SB1.B1_COD
AND SB1.D_E_L_E_T_ = ' '
LEFT OUTER JOIN SF4990 AS SF4 ON SF4.F4_FILIAL = '01'
AND SD2.D2_TES = SF4.F4_CODIGO
AND SF4.D_E_L_E_T_ = ' '
WHERE (SF2.F2_FILIAL = '01')
AND (SF2.F2_EMISSAO >= '20150101') AND (SF2.F2_EMISSAO <= '20151231')
AND (SF2.F2_TIPO IN ('N', 'C', 'P'))
AND (SF2.F2_VALFAT > 0)
AND (SF2.D_E_L_E_T_ = ' ')
GROUP BY MONTH(SF2.F2_EMISSAO)
ORDER BY MONTH(SF2.F2_EMISSAO)
--Devolução (DEVE-SE SUBTRAIR O VALOR BRUTO DAS DEVOLUÇÕES.)
SELECT MONTH(SF1.F1_EMISSAO) AS EMISSAO,
SUM(SD1.D1_TOTAL) AS NVALBRUTO,
SUM(SD1.D1_VALICM) AS NVALICM,
SUM(SD1.D1_BASEISS) AS NBASEISS,
SUM(SD1.D1_ICMSRET) AS NICMSRET,
SUM(SFT.FT_VALCONT) AS NVALCONT,
SUM(SFT.FT_VALPIS) AS NVALPIS,
SUM(SFT.FT_VALCOF) AS NVALCOF
FROM SF1990 AS SF1
LEFT OUTER JOIN SD1990 AS SD1 ON SD1.D1_FILIAL = '01'
AND SF1.F1_DOC = SD1.D1_DOC
AND SF1.F1_SERIE = SD1.D1_SERIE
AND SF1.F1_FORNECE = SD1.D1_FORNECE
AND SF1.F1_LOJA = SD1.D1_LOJA
AND SD1.D_E_L_E_T_ = ' '
LEFT OUTER JOIN SFT990 AS SFT ON SFT.FT_FILIAL = '01'
AND SD1.D1_DOC = SFT.FT_NFISCAL
AND SD1.D1_SERIE = SFT.FT_SERIE
AND SD1.D1_FORNECE = SFT.FT_CLIEFOR
AND SD1.D1_LOJA = SFT.FT_LOJA
AND SD1.D1_ITEM = SFT.FT_ITEM
AND SFT.D_E_L_E_T_ = ' '
LEFT OUTER JOIN SB1990 AS SB1 ON SB1.B1_FILIAL = ' '
AND SD1.D1_COD = SB1.B1_COD
AND SB1.D_E_L_E_T_ = ' '
LEFT OUTER JOIN SF4990 AS SF4 ON SF4.F4_FILIAL = '01'
AND SD1.D1_TES = SF4.F4_CODIGO
AND SF4.D_E_L_E_T_ = ' '
WHERE (SF1.F1_FILIAL = '01')
AND (SF1.F1_EMISSAO >= '20159901')
AND (SF1.F1_EMISSAO <= '20151231')
AND (SF1.F1_TIPO IN ('D'))
AND (SF1.D_E_L_E_T_ = ' ')
GROUP BY MONTH(SF1.F1_EMISSAO)
ORDER BY MONTH(SF1.F1_EMISSAO)
0 Comentários