# Exemplos de queries para retorno de regras da Reforma Tributária

# Exemplo 1

**Retorna regras “aprovadas” buscando novidades desde “YYYYMMDDHHmmss” (exemplo: 20170203112300 para retorno de regras desde 03/02/2017 às 11:23:00)**

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">SELECT \*</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">FROM TBL\_CENARIO\_MATERIAL\_REFORMA CM (NOLOCK)</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">INNER JOIN TBL\_CENARIO C (NOLOCK) ON CM.CENARIO\_CODI = C.CENARIO\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_MATERIAL M (NOLOCK) ON CM.MAT\_CODI = M.MAT\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_IBS I1 (NOLOCK) ON CM.IMP\_IBS = IMP\_CODI AND I.IMP\_STATUS = 'A</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IBS IBS (NOLOCK) ON I1.IMP\_CODI = IBS.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_IBSMUN I2 (NOLOCK) ON CM.IMP\_IBSMUN = I2.IMP\_CODI AND I2.IMP\_STATUS ='A'</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IBSMUN IBSMUN (NOLOCK) ON I2.IMP\_CODI = IBSMUN.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_CBS I3 (NOLOCK) ON CM.IMP\_CBS = I3.IMP\_CODI AND I3.IMP\_STATUS = 'A'</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_CBS CBS (NOLOCK) ON I3.IMP\_CODI = CBS.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">WHERE CM.CMA\_ATIV = 1 AND (CM.IMP\_IBS IS NOT NULL OR CM.IMP\_IBSMUN IS NOT NULL OR CM.IMP\_CBS IS NOT NULL) AND CM.CMA\_PONTEIRO= ' YYYYMMDDHHmmss'</span>

# Exemplo 2

**Query 2: Consulta pontual de produto retornando as regras mais recentes aprovadas.**

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">SELECT \*</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">FROM TBL\_CENARIO\_MATERIAL\_REFORMA CM (NOLOCK)</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">INNER JOIN TBL\_CENARIO C (NOLOCK) ON CM.CENARIO\_CODI = C.CENARIO\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_MATERIAL M (NOLOCK) ON CM.MAT\_CODI = M.MAT\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_IBS I1 (NOLOCK) ON CM.IMP\_IBS = IMP\_CODI AND I.IMP\_STATUS = 'A</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IBS IBS (NOLOCK) ON I1.IMP\_CODI = IBS.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_IBSMUN I2 (NOLOCK) ON CM.IMP\_IBSMUN = I2.IMP\_CODI AND I2.IMP\_STATUS ='A'</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IBSMUN IBSMUN (NOLOCK) ON I2.IMP\_CODI = IBSMUN.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_CBS I3 (NOLOCK) ON CM.IMP\_CBS = I3.IMP\_CODI AND I3.IMP\_STATUS = 'A'</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_CBS CBS (NOLOCK) ON I3.IMP\_CODI = CBS.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">WHERE CM.CMA\_ATIV = 1 AND (CM.IMP\_IBS IS NOT NULL OR CM.IMP\_IBSMUN IS NOT NULL OR CM.IMP\_CBS IS NOT NULL) AND M.COD\_PROD = 'CODIGOPRODUTO'</span>

# Exemplo 3

**Consulta pontual de produto retornando as regras mais recentes pendentes do produto.**

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">SELECT \*</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">FROM TBL\_CENARIO\_MATERIAL\_REFORMA CM (NOLOCK)</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">INNER JOIN TBL\_CENARIO C (NOLOCK) ON CM.CENARIO\_CODI = C.CENARIO\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_MATERIAL M (NOLOCK) ON CM.MAT\_CODI = M.MAT\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_IBS I1 (NOLOCK) ON CM.CMA\_CODI = I1.CMA\_CODI AND CODI AND I1.IMP\_STATUS = 'P' AND I1.IMP\_ATIV = 1</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IBS IBS (NOLOCK) ON I1.IMP\_CODI = IBS.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_IBSMUN I2 (NOLOCK) ON CM.CMA\_CODI = I2.CMA\_CODI AND I2.IMP\_STATUS = 'P' AND I2.IMP\_ATIV = 1</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IBSMUN IBSMUN (NOLOCK) ON I2.IMP\_CODI = IBSMUN.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_IMP\_CBS I3 (NOLOCK) ON CM.CMA\_CODI = I3.CMA\_CODI AND I3.IMP\_STATUS = 'P' AND I3.IMP\_ATIV = 1</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">LEFT JOIN TBL\_CBS CBS (NOLOCK) ON I3.IMP\_CODI = CBS.IMP\_CODI</span>

<span lang="EN-US" style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">WHERE CM.CMA\_ATIV = 1 AND M.COD\_PROD = 'CODIGOPRODUTO'</span>

<span lang="EN-US" style="mso-ansi-language: EN-US;"> </span>

<span style="background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial;">Obs.: Os campos imp\_ibs I1, imp\_ibsmun I2 e imp\_cbs I3 da tabela tbl\_cenario\_material\_reforma, guardam o código do tributo mais recente que você aprovou ou rejeitou, então eles são utilizados para retorno da query de aprovados ou rejeitados.</span>