I would like to generate a PIS and COFINS tax forecast report, which is calculated based on a CFOP code of the notes issued in a given period, but would like to separate this information in "blocks" / p>
I expect the result this way:
+--------------------------------------------------------------+
| CRÉDITO |
+---------+----------------+--------------+---------+----------+
| CFOP | Descrição | Valor Base | PIS | COFINS |
+---------+----------------+--------------+---------+----------+
+--------------------------------------------------------------+
| DÉBITO - Cumulativo |
+---------+----------------+--------------+---------+----------+
| CFOP | Descrição | Valor Base | PIS | COFINS |
+---------+----------------+--------------+---------+----------+
+--------------------------------------------------------------+
| DÉBITO - Não-Cumulativo |
+---------+----------------+--------------+---------+----------+
| CFOP | Descrição | Valor Base | PIS | COFINS |
+---------+----------------+--------------+---------+----------+
I believe that for this I will need to use SubReports in Crystal Reports, but I can not think of how to organize the groups in the report and subreports. Remember that all data will be based on the same period that will be passed as a parameter.
The SQL used is basically the same in all reports, changing only the percentage value for tax basis depending on CFOP.
Here is the example SQL that returns the issued NFs (which will generate the debit "blocks"):
DECLARE @PIS decimal(5,4) = 0.0165;
DECLARE @COFINS decimal(5,4) = 0.076;
DECLARE @PIS_I decimal(5,4) = 0.0065;
DECLARE @COFINS_I decimal(5,4) = 0.03;
SELECT
DataEmissao
,CFOP
,Base
,Natureza
,'Categoria' = UPPER(CASE
WHEN Base = 'A' THEN 'Imune'
WHEN Base = 'B' THEN 'Deferido'
WHEN BASE = 'C' THEN 'Tributado'
ELSE '' END)
,[Valor Base]
,'PIS' = CASE
WHEN Base = 'A' THEN [Valor Base]*@PIS_I
ELSE [Valor Base]*@PIS END
,'COFINS' = CASE
WHEN Base = 'A' THEN [Valor Base]*@COFINS_I
ELSE [Valor Base]*@COFINS END
FROM
(SELECT
NotasFiscais.DataEmissao
,'CFOP' = SUBSTRING(REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.',''),1,4)
,'Base' = CASE
WHEN SUBSTRING(REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.',''),5,1) = 'P' THEN SUBSTRING(REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.',''),6,1)
ELSE SUBSTRING(REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.',''),5,1)
END
,'Natureza' = UPPER((SELECT NatOperacao FROM MT_MAZER.dbo.CFOP CFOP_Desc
WHERE REPLACE(REPLACE(CFOP_Desc.CFOP,' ',''),'.','') = REPLACE(REPLACE(CFOP.CFOP,' ',''),'.','') AND
LEN(CFOP_Desc.NatOperacao) = MAX(LEN(CFOP.NatOperacao))))
,'Valor Base' = SUM(NotasFiscais.ValorTotalNota)
FROM
DB.dbo.NotasFiscais
INNER JOIN DB.dbo.CFOP ON NotasFiscais.cfop1 = CFOP.CFOP
WHERE
CFOP.Fatura = 'S' AND
CFOP.Devolucao = 'N' AND
NotasFiscais.CodEmpresa = 1 AND
NotasFiscais.Situacao = 'N' AND
NotasFiscais.TipoNota = 'S'
GROUP BY
NotasFiscais.DataEmissao
,REPLACE(REPLACE(NotasFiscais.cfop1,' ',''),'.','')
,CFOP.CFOP
,NotasFiscais.CodEmpresa) Impostos