cQuery1:="SELECT "
cQuery1+="produtos.Tributo_Id AS REGRA, "
cQuery1+="pedidos.TIPO_OPERACAO, "
cQuery1+="flag.N_CRT AS C_CRT, "
cQuery1+="danfe.C_UF AS UFEmitente, "
cQuery1+="danfe.E_UF AS UfDestinatario, "
cQuery1+="pedidositens.PEDIDOS_ID, "
cQuery1+="pedidositens.PRODUTOS_ID AS cProd, "
cQuery1+="pedidositens.ITEM AS nItem, "
cQuery1+="produtos.EAN13 AS cEAN, "
cQuery1+="pedidositens.DESPRO AS xProd, "
cQuery1+="regrastributarias.Origem_Mercadoria AS ICMS_ORIG, "
cQuery1+="regrastributarias.ICMS_CST_CSOSN AS CST, "
cQuery1+="produtos.NCM AS cNCM, "
cQuery1+="produtos.CEST AS CEST, "
cQuery1+="regrastributarias.nCFOP AS nCFOP, "
cQuery1+="produtos.TIPUNI AS uCom, "
cQuery1+="pedidositens.QTDPRO AS QCOM, "
cQuery1+="pedidositens.VLRUNI AS VUNCOM, "
cQuery1+="pedidositens.VLRTOT AS VPROD, "
cQuery1+="produtos.EAN13 AS CEANTRIB, "
cQuery1+="produtos.TIPUNI AS UTRIB, "
cQuery1+="pedidositens.QTDPRO AS QTRIB, "
cQuery1+="pedidositens.VLRUNI AS VUNTRIB, "
cQuery1+="1 AS INDTOT, "
cQuery1+="ROUND((pedidos.TOTDES * (pedidositens.QTDPRO * pedidositens.VLRUNI) / ( pedidos.TOTAL_GERAL ) ),4) AS vDESC, "
cQuery1+="regrastributarias.Origem_Mercadoria AS ICMS_ORIG, "
cQuery1+="regrastributarias.ICMS_CST_CSOSN AS CST, "
cQuery1+="pedidositens.VLRTOT AS ICMS_vBc, "
cQuery1+="regrastributarias.ICMS_Aliq AS ICMS_pICMS, "
cQuery1+="IF(regrastributarias.ICMS_Aliq = 0.0000, 0.00, ROUND(((pedidositens.VLRTOT * regrastributarias.ICMS_Aliq )/100),2) ) AS ICMS_vICMS, "
cQuery1+="regrastributarias.IPI_CST, "
cQuery1+="IF(regrastributarias.IPI_CST IS NULL, 0.00, pedidositens.VLRTOT) AS IPI_vBc, "
cQuery1+="regrastributarias.IPI_Aliq, "
cQuery1+="IF(regrastributarias.IPI_CST IS NULL, 0.00, ROUND(((pedidositens.VLRTOT * regrastributarias.IPI_Aliq )/100),2) ) AS vIPI, "
cQuery1+="regrastributarias.IPI_Enq, "
cQuery1+="regrastributarias.PIS_CST AS PIS_ST, "
cQuery1+="IF(regrastributarias.PIS_CST = 99, 0.00, pedidositens.VLRTOT) AS PIS_vBc, "
cQuery1+="regrastributarias.PIS_Aliq AS PIS_pPIS, "
cQuery1+="ROUND(((pedidositens.VLRTOT * regrastributarias.PIS_Aliq )/100),2) AS PIS_vPIS, "
cQuery1+="regrastributarias.COFINS_CST AS COF_ST, "
cQuery1+="IF(regrastributarias.COFINS_CST = 99, 0.00, pedidositens.VLRTOT) AS COF_vBc, "
cQuery1+="regrastributarias.COFINS_Aliq AS COF_pCOFINS, "
cQuery1+="ROUND(((pedidositens.VLRTOT * regrastributarias.COFINS_Aliq )/100),2) AS COF_vCOFINS, "
cQuery1+="(pedidositens.VLRTOT - (ROUND((pedidos.TOTDES * (pedidositens.QTDPRO * pedidositens.VLRUNI) / ( pedidos.TOTAL_GERAL ) ),4)) ) AS vBCUFDest, "
cQuery1+="AliquotaFCP(danfe.E_UF) AS pFCPUFDest, "
cQuery1+="AliquotaIcms(danfe.E_UF, danfe.E_UF) AS pICMSUFDest, "
cQuery1+="AliquotaIcms(danfe.C_UF, danfe.E_UF) AS pICMSInter, "
cQuery1+="0.00 AS pICMSOrigemPart, "
cQuery1+="100.00 AS pICMSDestinoPart, "
cQuery1+="ROUND(((pedidositens.VLRTOT - (ROUND((pedidos.TOTDES * (pedidositens.QTDPRO * pedidositens.VLRUNI) / ( pedidos.TOTAL_GERAL ) ),4)) ) * ((AliquotaIcms(danfe.E_UF, danfe.E_UF) - AliquotaIcms(danfe.C_UF, danfe.E_UF) ) / 100)), 2) AS DIFAL, "
cQuery1+="ROUND(((pedidositens.VLRTOT - (ROUND((pedidos.TOTDES * (pedidositens.QTDPRO * pedidositens.VLRUNI) / ( pedidos.TOTAL_GERAL ) ),4)) ) * (AliquotaFCP(danfe.E_UF) / 100)), 2) AS vFCPUFDest, "
cQuery1+="ROUND((((pedidositens.VLRTOT - (ROUND((pedidos.TOTDES * (pedidositens.QTDPRO * pedidositens.VLRUNI) / ( pedidos.TOTAL_GERAL ) ),4)) ) * ((AliquotaIcms(danfe.E_UF, danfe.E_UF) - AliquotaIcms(danfe.C_UF, danfe.E_UF) ) / 100)) * (100 / 100)), 2) AS vICMSUFDest, "
cQuery1+="ROUND((((pedidositens.VLRTOT - (ROUND((pedidos.TOTDES * (pedidositens.QTDPRO * pedidositens.VLRUNI) / ( pedidos.TOTAL_GERAL ) ),4)) ) * ((AliquotaIcms(danfe.E_UF, danfe.E_UF) - AliquotaIcms(danfe.C_UF, danfe.E_UF) ) / 100)) * (0 / 100)), 2) AS vICMSUFRemet, "
cQuery1+="CASE regrastributarias.Origem_Mercadoria "
cQuery1+=" WHEN '0' THEN ROUND((((pedidositens.QTDPRO * pedidositens.VLRUNI) * ncm.NACFED)/100),2) "
cQuery1+=" WHEN '3' THEN ROUND((((pedidositens.QTDPRO * pedidositens.VLRUNI) * ncm.NACFED)/100),2) "
cQuery1+=" WHEN '4' THEN ROUND((((pedidositens.QTDPRO * pedidositens.VLRUNI) * ncm.NACFED)/100),2) "
cQuery1+=" WHEN '5' THEN ROUND((((pedidositens.QTDPRO * pedidositens.VLRUNI) * ncm.NACFED)/100),2) "
cQuery1+="ELSE "
cQuery1+=" ROUND((((pedidositens.QTDPRO * pedidositens.VLRUNI) * ncm.IMPFED)/100),2) "
cQuery1+="END AS vTotFed, "
cQuery1+="ROUND((((pedidositens.QTDPRO * pedidositens.VLRUNI) * ncm.ESTADUAL)/100),2) AS vTotEst, "
cQuery1+="ncm.VERSAO, "
cQuery1+="pedidositens.ITEM AS nItemPed, "
cQuery1+="pedidositens.PEDIDOS_ID AS xPed "
cQuery1+="FROM pedidositens "
cQuery1+="JOIN flag ON flag.FLAG_Id = 1 "
cQuery1+="JOIN produtos ON produtos.PRODUTOS_Id = pedidositens.PRODUTOS_ID "
cQuery1+="JOIN pedidos ON pedidos.PEDIDOS_Id = pedidositens.PEDIDOS_ID "
cQuery1+="left JOIN danfe ON danfe.PEDIDOS_ID = pedidos.PEDIDOS_Id "
cQuery1+="LEFT JOIN regrastributarias ON regrastributarias.Tributo_id = produtos.Tributo_Id "
cQuery1+="AND regrastributarias.TipoOperacao_id = " + LTRIM(STR(::TIPO_OPERACAO)) + " "
cQuery1+="AND regrastributarias.Uf = '" + ::UFDestinatario + "' "
cQuery1+="LEFT JOIN ncm ON ncm.NUMNCM = produtos.NCM "
cQuery1+="WHERE pedidositens.PEDIDOS_ID = '"+ STRZERO(::nPedido_Id,11,0) + "' "
cQuery1+="GROUP BY pedidositens.ITEM "
funcoes do SGBD para ALIQUOTAS DE ICMS
cQuery:="CREATE FUNCTION IF NOT EXISTS `AliquotaIcms`( "
cQuery+="`cUFde` VARCHAR(2), "
cQuery+="`cUFate` VARCHAR(2) "
cQuery+=") "
cQuery+="RETURNS DECIMAL(10,2) "
cQuery+="LANGUAGE SQL "
cQuery+="NOT DETERMINISTIC "
cQuery+="CONTAINS SQL "
cQuery+="SQL SECURITY DEFINER "
cQuery+="COMMENT 'retorna a aliquota de icms para calculo do DIFAL/FCP' "
cQuery+="BEGIN "
cQuery+="DECLARE nAliquota DECIMAL(10,2) ; "
cQuery+="SET nAliquota = ( "
cQuery+="CASE "
cQuery+="WHEN cUFde = cUFate AND cUFde IN ( 'AL', 'AM', 'AP', 'BA', 'CE', "
cQuery+="'DF', 'ES', 'MA', 'MG', 'PB', 'PE', 'PI', 'PR', 'RN', 'RS', "
cQuery+="'SP', 'SE', 'TO' ) THEN 18 "
cQuery+="WHEN cUFde = cUFate AND cUFde IN ( 'GO', 'MT', 'MS', 'PA', 'RO', "
cQuery+="'RR', 'SC' ) THEN 17 "
cQuery+="WHEN cUFde = 'AC' AND cUFate = 'AC' THEN 12 "
cQuery+="WHEN cUFde = 'MG' AND cUFate IN ( 'PR', 'RJ', 'RS', 'SC', 'SP' ) THEN 12 "
cQuery+="WHEN cUFde = 'MG' THEN 7 "
cQuery+="WHEN cUFde = 'RJ' AND cUFate = 'RJ' THEN 20 "
cQuery+="WHEN cUFde = 'RJ' AND cUFate IN ( 'MG', 'PR', 'RS', 'SC', 'SP' ) THEN 12 "
cQuery+="WHEN cUFde = 'RJ' THEN 7 "
cQuery+="WHEN cUFde = 'RS' AND cUFate IN ( 'MG', 'PR', 'PR', 'MS', 'SC', 'SP' ) THEN 12 "
cQuery+="WHEN cUFde = 'SC' AND cUFate IN ( 'MG', 'PR', 'MS', 'PB', 'RS', 'SP' ) THEN 12 "
cQuery+="WHEN cUFde = 'SC' then 7 "
cQuery+="WHEN cUFde = 'SP' AND cUFate IN ( 'MG', 'PR', 'RJ', 'RS', 'SC' ) THEN 12 "
cQuery+="WHEN cUFde = 'SP' THEN 7 "
cQuery+="ELSE 12 "
cQuery+="END ) ; "
cQuery+="RETURN nAliquota ; "
cQuery+="END; "
Função do SGBD para Aliquota de FCP
cQuery := "CREATE FUNCTION IF NOT EXISTS AliquotaFCP( `cUFde` VARCHAR(2)) "
cQuery += "RETURNS DECIMAL(10,2) "
cQuery += "LANGUAGE SQL "
cQuery += "NOT DETERMINISTIC "
cQuery += "CONTAINS SQL "
cQuery += "SQL SECURITY DEFINER "
cQuery += "COMMENT '' "
cQuery += "BEGIN "
cQuery += " DECLARE nAliquota DECIMAL(10,2) ; "
cQuery += " SET nAliquota = ( "
cQuery += " CASE "
cQuery += " WHEN cUFde IN ( 'AC', 'AP', 'CE', 'PA', 'SC' ) THEN 0 "
cQuery += " WHEN cUFde IN ( 'AL', 'PI' ) THEN 1 "
cQuery += " WHEN cUFde = 'RJ' THEN 4 "
cQuery += " ELSE 2 "
cQuery += "END ) ; "
cQuery += "RETURN nAliquota ; "
cQuery += "END "
em um futuro proximo vou fazer que nem o Quintas , gerar todas as Querys em uma tabela no proprio SGBD facilitando com isso as modificacoes nelas