Skip to Content

Taxsum returns zero (Query)

Hi guys!
I hope everything is fine with everyone!

I have a problem in the return of a query, where the taxsum returns me only the value "0" in three columns, which are: IPI, ICMS and ICMS-ST.

I crashed and I can't resolve this issue.
I ask for help from the experts.

Grateful and hug!


Follow the code:

--NF Ent

'NF Entrada' AS "Tipo",
f."BPLName" AS "Filial",
--t1."BPLId",
t1."DocNum" AS "NUM_SAP",
t1."Serial" AS "NF",
t1."CardCode",
t1."CardName",
t1."TaxDate",
t0."ItemCode",
t0."Dscription",
t12."NcmCode",
IFNULL(t0."Quantity",0)  AS "Qtd",
IFNULL(t0."Price",0) AS "Preco",
IFNULL(t0."LineTotal",0)  AS "T Product",
t0."CFOPCode",
t6."Descr" AS "Utz", 
t4."ItmsGrpNam",
ROUND((IFNULL(ICMS."TaxSum",0)),2) + ROUND((IFNULL(ICMS_D."TaxSum",0)),2)  AS "ICMS",
ROUND((IFNULL(IPI."TaxSum",0)),2)+ ROUND((IFNULL(IPI_D."TaxSum",0)),2) AS "IPI",
ROUND((IFNULL(PIS."TaxSum",0)),2)+ ROUND((IFNULL(PIS_D."TaxSum",0)),2) AS "PIS",
ROUND((IFNULL(COFINS."TaxSum",0)),2)+ ROUND((IFNULL(COFINS_D."TaxSum",0)),2) AS "COFINS",
ROUND(IFNULL(ST."TaxSum",0),2)+ ROUND(IFNULL(ST_D."TaxSum",0),2) AS "ICMS-ST",
ROUND(IFNULL(II."TaxSum",0),2)+ ROUND(IFNULL(II_D."TaxSum",0),2) AS "II",
t5."CityB" AS "City",
t5."StateB" AS "State",
t5."CountryB" AS "Country"

from OPCH t1 
inner join PCH1 t0 on t0."DocEntry" = t1."DocEntry"
inner join PCH12 t5 on t5."DocEntry" = t1."DocEntry"
inner join OITM t3 on t3."ItemCode" = t0."ItemCode"
inner join OBPL f on f."BPLId" = t1."BPLId"
left JOIN ONCM t12 on t12."AbsEntry" = t3."NCMCode"
inner join OITB t4 on t4."ItmsGrpCod" = t3."ItmsGrpCod"
left JOIN OUSG t6 on t6."ID" = t0."Usage"

-- Imp Exit
-- ICMS ITEM
left join PCH4 ICMS ON ICMS."DocEntry" = t1."DocEntry" and ICMS."LineNum" = t0."LineNum"
AND ICMS."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'ICMS')
AND ICMS."RelateType" = 1
-- ICMS DESPESA
left join PCH4 ICMS_D ON ICMS_D."DocEntry" = t1."DocEntry" and ICMS_D."LineNum" = t0."LineNum"
AND ICMS_D."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'ICMS')
AND ICMS_D."RelateType" = 13

-- IPI ITEM
left join PCH4 IPI ON IPI."DocEntry" = t1."DocEntry" and IPI."LineNum" = t0."LineNum"
AND IPI."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'IPI')
AND IPI."RelateType" = 1
-- IPI DESPESA
left join PCH4 IPI_D ON IPI_D."DocEntry" = t1."DocEntry" and IPI_D."LineNum" = t0."LineNum"
AND IPI_D."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'IPI')
AND IPI_D."RelateType" = 13

-- PIS ITEM
left join PCH4 PIS ON PIS."DocEntry" = t1."DocEntry" and PIS."LineNum" = t0."LineNum"
and PIS."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'PIS')
AND PIS."RelateType" = 1
-- PIS DESPESA
left join PCH4 PIS_D ON PIS_D."DocEntry" = t1."DocEntry" and PIS_D."LineNum" = t0."LineNum"
and PIS_D."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'PIS')
AND PIS_D."RelateType" = 13

-- COFINS ITEM
left join PCH4 COFINS ON COFINS."DocEntry" = t1."DocEntry" and COFINS."LineNum" = t0."LineNum"
and COFINS."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'COFINS')
AND COFINS."RelateType" = 1
-- COFINS DESPESA
left join PCH4 COFINS_D ON COFINS_D."DocEntry" = t1."DocEntry" and COFINS_D."LineNum" = t0."LineNum"
and COFINS_D."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'COFINS')
AND COFINS_D."RelateType" = 13

-- ST ITEM
left join PCH4 ST ON ST."DocEntry" = t1."DocEntry" and ST."LineNum" = t0."LineNum"
and ST."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'ST')
AND ST."RelateType" = 1
-- ST DESPESA
left join PCH4 ST_D ON ST_D."DocEntry" = t1."DocEntry" and ST_D."LineNum" = t0."LineNum"
and ST_D."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'ST')
AND ST_D."RelateType" = 13

-- II ITEM
left join PCH4 II ON II."DocEntry" = t1."DocEntry" and II."LineNum" = t0."LineNum"
and II."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'II')
AND II."RelateType" = 1
-- II ITEM
left join PCH4 II_D ON II_D."DocEntry" = t1."DocEntry" and II_D."LineNum" = t0."LineNum"
and II_D."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'II')
AND II_D."RelateType" = 13

where 
t1."CANCELED" = 'N' and
T1."Model" IS NOT NULL and T1."Model" <> 0
and T0."DocEntry" NOT IN ( SELECT IFNULL(DNF1."BaseEntry",0) FROM ORPC DNF INNER JOIN RPC1 DNF1 ON DNF."DocEntry" = DNF1."DocEntry"
WHERE DNF1."BaseType" = 18 and DNF."SeqCode" = 1)
--and t1."DocEntry" = '1379'                                           
--- con
and T1."TaxDate" >=[%0]
and  T1."TaxDate" <=[%1]

UNION ALL

SELECT

'DEV NF Entrada' AS "Tipo",
f."BPLName" AS "Filial",
--t1."BPLId",
t1."DocNum" AS "NUM_SAP",
t1."Serial" AS "NF",
t1."CardCode",
t1."CardName",
t1."TaxDate",
t0."ItemCode",
t0."Dscription",
t12."NcmCode",
IFNULL(t0."Quantity",0)  AS "Qtd",
IFNULL(t0."Price",0) AS "Preco",
IFNULL(t0."LineTotal",0) * -1 AS "T Product",
t0."CFOPCode",
t6."Descr" AS "Utilizacao", 
t4."ItmsGrpNam",
(ROUND((IFNULL(ICMS."TaxSum",0)),2)+ROUND((IFNULL(ICMS_DD."TaxSum",0)),2)) * -1 AS "ICMS",
(ROUND((IFNULL(IPI."TaxSum",0)),2)+ROUND((IFNULL(IPI_DD."TaxSum",0)),2)) * -1 AS "IPI",
(ROUND((IFNULL(PIS."TaxSum",0)),2)+ROUND((IFNULL(PIS_DD."TaxSum",0)),2)) * -1 AS "PIS",
(ROUND((IFNULL(COFINS."TaxSum",0)),2)+ROUND((IFNULL(COFINS_DD."TaxSum",0)),2)) * -1 AS "COFINS",
(ROUND(IFNULL(ST."TaxSum",0),2)+ROUND(IFNULL(ST_DD."TaxSum",0),2)) * -1 AS "ICMS-ST",
(ROUND(IFNULL(II."TaxSum",0),2)+ROUND(IFNULL(II_DD."TaxSum",0),2)) * -1 AS "II",
t5."CityB" AS "Cidade",
t5."StateB" AS "Estado",
t5."CountryB" AS "País"

from ORPC t1 
inner join RPC1 t0 on t0."DocEntry" = t1."DocEntry"
inner join RPC12 t5 on t5."DocEntry" = t1."DocEntry"
inner join OITM t3 on t3."ItemCode" = t0."ItemCode"
inner join OBPL f on f."BPLId" = t1."BPLId"
left JOIN ONCM t12 on t12."AbsEntry" = t3."NCMCode"
inner join OITB t4 on t4."ItmsGrpCod" = t3."ItmsGrpCod"
left JOIN OUSG t6 on t6."ID" = t0."Usage"

-- imp Ext
-- ICMS ITEM
left join RPC4 ICMS ON ICMS."DocEntry" = t1."DocEntry" and ICMS."LineNum" = t0."LineNum"
AND ICMS."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'ICMS')
AND ICMS."RelateType" = 1
-- ICMS DESPESA
left join RPC4 ICMS_DD ON ICMS_DD."DocEntry" = t1."DocEntry" and ICMS_DD."LineNum" = t0."LineNum"
AND ICMS_DD."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'ICMS')
AND ICMS_DD."RelateType" = 13

-- IPI ITEM
left join RPC4 IPI ON IPI."DocEntry" = t1."DocEntry" and IPI."LineNum" = t0."LineNum"
AND IPI."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'IPI')
AND IPI."RelateType" = 1
-- IPI DEPESA
left join RPC4 IPI_DD ON IPI_DD."DocEntry" = t1."DocEntry" and IPI_DD."LineNum" = t0."LineNum"
AND IPI_DD."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'IPI')
AND IPI_DD."RelateType" = 13

-- PIS ITEM
left join RPC4 PIS ON PIS."DocEntry" = t1."DocEntry" and PIS."LineNum" = t0."LineNum"
and PIS."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'PIS')
AND PIS."RelateType" = 1
-- PIS ITEM
left join RPC4 PIS_DD ON PIS_DD."DocEntry" = t1."DocEntry" and PIS_DD."LineNum" = t0."LineNum"
and PIS_DD."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'PIS')
AND PIS_DD."RelateType" = 13

-- COFINS ITEM
left join RPC4 COFINS ON COFINS."DocEntry" = t1."DocEntry" and COFINS."LineNum" = t0."LineNum"
and COFINS."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'COFINS')
AND COFINS."RelateType" = 1
-- COFINS DESPESA
left join RPC4 COFINS_DD ON COFINS_DD."DocEntry" = t1."DocEntry" and COFINS_DD."LineNum" = t0."LineNum"
and COFINS_DD."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'COFINS')
AND COFINS_DD."RelateType" = 13

-- ST ITEM
left join RPC4 ST ON ST."DocEntry" = t1."DocEntry" and ST."LineNum" = t0."LineNum"
and ST."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'ST')
AND ST."RelateType" = 1
-- ST DESPESA
left join RPC4 ST_DD ON ST_DD."DocEntry" = t1."DocEntry" and ST_DD."LineNum" = t0."LineNum"
and ST_DD."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'ST')
AND ST_DD."RelateType" = 13

-- II ITEM
left join RPC4 II ON II."DocEntry" = t1."DocEntry" and II."LineNum" = t0."LineNum"
and II."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'II')
AND II."RelateType" = 1
-- II DESPESA
left join RPC4 II_DD ON II."DocEntry" = t1."DocEntry" and II_DD."LineNum" = t0."LineNum"
and II_DD."staType" in (select a."AbsId" from OSTT a inner join ONFT b on a."NfTaxId" = b."AbsId" and "Code" like 'II')
AND II_DD."RelateType" = 13

where 
t1."CANCELED" = 'N' and
T1."Model" IS NOT NULL and T1."Model" <> 0

--- cond
and T1."TaxDate" >=[%0]
and  T1."TaxDate" <=[%1]

print.png (21.4 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Jul 07, 2020 at 05:42 AM

    Hi,

    You have used LEFT JOIN on the table, and ISNULL and ROUND on the fields. You say that only three of the results return 0.

    Have you checked these three cases? Should there be a number larger than 0.01 ?

    Regards,

    Johan

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Junior,

      Ok, this is difficult for me to troubleshoot, as I have no data in table PCH4, so the query returns 0 for me anyway.

      Please try a simplified version of your query, to find the problem, as it is probably the same for each of the three columns. Something like this (please note, I have MS SQL, so the syntax is a little different):

      SELECT
      t1.CardCode,
      t1.CardName,
      t1.TaxDate,
      t0.ItemCode,
      t0.Dscription,
      ROUND((ISNULL(ICMS.TaxSum,0)),2) + ROUND((ISNULL(ICMS_D.TaxSum,0)),2)  AS [ICMS]
      FROM OPCH t1 
      inner join PCH1 t0 on t0.[DocEntry] = t1.[DocEntry]
      left join PCH4 ICMS ON ICMS.DocEntry = t1.DocEntry
                         and ICMS.LineNum = t0.LineNum
                         AND ICMS.staType in (select a.AbsId 
                                              from OSTT a 
                                                   inner join ONFT b on a.NfTaxId = b.AbsId 
                                                                    and Code like 'ICMS')
                         AND ICMS.RelateType = 1
      left join PCH4 ICMS_D ON ICMS_D.DocEntry = t1.DocEntry 
                           and ICMS_D.LineNum = t0.LineNum
                           AND ICMS_D.staType in (select a.AbsId 
                                                  from OSTT a 
                                                       inner join ONFT b on a.NfTaxId = b.AbsId 
                                                                        and Code like 'ICMS')
                           AND ICMS_D.RelateType = 13
      WHERE T1.TaxDate >= '2020/06/01'
      ORDER BY ICMS DESC

      Best is probably to start with the FROM clause. One of the first things I would try is specifying the table to which the Code field belongs. The next thing would be to comment out the third and fourth restraints ('AND ICMS.staType in ...etc.' and 'AND ICMS.RelateType = 1').

      Regards,

      Johan

  • Posted on Jul 09, 2020 at 06:33 PM
    
    
    

    Johan, how are you?

    Your code gave me the unlock I was having.
    At OSTT I have similar values and with that in the expression 'LIKE' he did not bring the information of "ST" and "IPI" precisely because of the replications of the names. So instead of "ST" I put "ICMS-ST" and with that he brought the necessary information.

    I really appreciate your help.

    A big hug!


    ostt.png (8.9 kB)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.