0
I have the following appointment:
SELECT distinct CAPA.COD_HOLDING,
CAPA.COD_MATRIZ,
CAPA.COD_FILIAL,
CAPA.ID_NF_ENTRADA,
CAPA.NUM_NF,
CAPA.DT_ENTRADA,
CAPA.DT_EMISSAO,
CAPA.COD_CLIFOR,
CAPA.COD_UF,
CAPA.CGC_CPF,
CAPA.COD_CFOP_LEGAL,
CAPA.CHAVE_NF_E,
ITEM.ID_ITEM,
ITEM.COD_CLASSIF_FISCAL,
ITEM.COD_IVA,
ITEM.DESCRICAO_NOTA,
ITEM.COD_PRODUTO,
(ITEM.VLR_BRUTO + ITEM.VLR_FRETE + ITEM.VLR_DESPESAS) AS VLR_BRUTO,
IMP.COD_IMPOSTO,
IMP.COD_TP_LANC_IMP,
DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.BASE_CALCULO, 0) AS BASE_CALCULO,
IMP.ALIQUOTA,
DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.VLR_IMPOSTO, 0) AS VLR_IMPOSTO,
OBS.COD_OBSERVACAO,
DECODE(IMP.COD_TP_LANC_IMP, '2', IMP.BASE_CALCULO, 0) AS ISENTAS,
DECODE(IMP.COD_TP_LANC_IMP, '3', IMP.BASE_CALCULO, 0) AS OUTRAS
FROM LF_NF_ENTRADA CAPA,
LF_NF_ENTRADA_ITEM ITEM,
LF_NF_ENTRADA_IMPOSTO IMP,
LF_NF_ENTRADA_OBSERVACOES OBS
WHERE CAPA.COD_HOLDING = ITEM.COD_HOLDING
AND CAPA.COD_MATRIZ = ITEM.COD_MATRIZ
AND CAPA.COD_FILIAL = ITEM.COD_FILIAL
AND CAPA.ID_NF_ENTRADA = ITEM.ID_NF_ENTRADA
AND ITEM.COD_HOLDING = IMP.COD_HOLDING
AND ITEM.COD_MATRIZ = IMP.COD_MATRIZ
AND ITEM.COD_FILIAL = IMP.COD_FILIAL
AND ITEM.ID_NF_ENTRADA = IMP.ID_NF_ENTRADA
AND ITEM.ID_ITEM = IMP.ID_ITEM
AND CAPA.COD_HOLDING = OBS.COD_HOLDING
AND CAPA.COD_MATRIZ = OBS.COD_MATRIZ
AND CAPA.COD_FILIAL = OBS.COD_FILIAL
AND CAPA.ID_NF_ENTRADA = OBS.ID_NF_ENTRADA
AND CAPA.COD_HOLDING = 'HOL00'
AND CAPA.COD_MATRIZ = 'I003'
AND CAPA.COD_FILIAL = '0003'
AND CAPA.DT_ENTRADA >= TO_DATE('01/05/2016', 'DD/MM/YYYY')
AND CAPA.DT_ENTRADA <= TO_DATE('31/05/2016', 'DD/MM/YYYY')
AND IMP.COD_IMPOSTO IN ('01')
AND CAPA.COD_STATUS = 01
AND CAPA.COD_CFOP_LEGAL = '2551'
GROUP BY CAPA.COD_HOLDING,
CAPA.COD_MATRIZ,
CAPA.COD_FILIAL,
CAPA.ID_NF_ENTRADA,
CAPA.NUM_NF,
CAPA.DT_ENTRADA,
CAPA.DT_EMISSAO,
CAPA.COD_CLIFOR,
CAPA.COD_UF,
CAPA.CGC_CPF,
CAPA.COD_CFOP_LEGAL,
CAPA.CHAVE_NF_E,
ITEM.ID_ITEM,
ITEM.COD_CLASSIF_FISCAL,
ITEM.COD_IVA,
ITEM.DESCRICAO_NOTA,
ITEM.COD_PRODUTO,
(ITEM.VLR_BRUTO + ITEM.VLR_FRETE + ITEM.VLR_DESPESAS),
IMP.COD_IMPOSTO,
IMP.COD_TP_LANC_IMP,
DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.BASE_CALCULO, 0),
IMP.ALIQUOTA,
DECODE(IMP.COD_TP_LANC_IMP, '1', IMP.VLR_IMPOSTO, 0),
OBS.COD_OBSERVACAO,
DECODE(IMP.COD_TP_LANC_IMP, '2', IMP.BASE_CALCULO, 0),
DECODE(IMP.COD_TP_LANC_IMP, '3', IMP.BASE_CALCULO, 0)
ORDER BY CAPA.COD_HOLDING,
CAPA.COD_MATRIZ,
CAPA.COD_FILIAL,
CAPA.NUM_NF,
CAPA.DT_ENTRADA,
IMP.COD_IMPOSTO,
ITEM.ID_ITEM;
In it I return tax records with the respective tax and value, but I have a scenario where an item has 2 lines of taxes, and these 2 lines have some fields that I do the grouping different, so it is repeating values, as below:
Note that when the highlighted row is repeated, the "VLR_BRUTO" column is printed again with the same value, how could I display this zeroed column? Is there any way to do this in a query ?
Every time the value is repeated the BASE_CALCULO is reset, or the COD_TIPO_LANC_IMP is greater than 1?
– Reginaldo Rigo
Reginaldo, the line is being duplicated because for this item there are two lines of the tax in question. One of them has the COD_TP_LANC_IMP field equal to 1 and the other with the value 2, so the record is being repeated.
– Gercy Campos
If you only selected items whose BASE_CALCULO is greater than zero would be right or you need it to come to duplicate lines including?
– Reginaldo Rigo
See date conversion and session format https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams122.htm#REFRN10119 https://docs.oracle.com/cd/B28359_01/server.111/b286/sql_elements004.htm#SQLRF00210
– Motta