Skip to Content
0
Jul 06, 2020 at 07:26 PM

Taxsum returns zero (Query)

71 Views Last edit Jul 04, 2020 at 12:59 AM 3 rev

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]

Attachments

print.png (21.4 kB)