-2147217900 [ma-3.1.10][10.5.5-MariaDB]You have an error in your SQL syntax; check the manual
that corresponds to your MariaDB server version for the right syntax to
use near 'IPISSBAS = IF( IPISSALI > 0, @VALPRO + IPIIVAL, 0 ), ;
IPISSVAL = IF( IPISSA...' at line 136
Full SQL:
CREATE PROCEDURE ze_CalculaPedido( nIdPedido INT )
THIS: BEGIN
IF COALESCE( nIdPedido, 0 ) = 0 THEN
LEAVE THIS;
END IF;
/* soma tudo pra rateio */
UPDATE JPPEDIDO
JOIN
(
SELECT IPPEDIDO, SUM( IPQTDE * IPPREPED ) AS SOMA
FROM JPITPED
WHERE IPPEDIDO = nIdPedido
GROUP BY IPPEDIDO ) AS TEMP
ON TEMP.IPPEDIDO = JPPEDIDO.IDPEDIDO
SET JPPEDIDO.PDVALPRO = TEMP.SOMA
WHERE IDPEDIDO = nIdPedido;
/* rateio adicionais */
UPDATE JPITPED
JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
SET
IPVALADI = ( @VALADI := ROUND( PDVALADI / IF( PDVALPRO = 0, 1, PDVALPRO ) * ( IPPREPED * IPQTDE ), 2 ) ),
IPPRENOT = ( @PRENOT := ROUND( IF( IPQTDE = 0, 0, ( ( IPPREPED + @VALADI ) * IPQTDE ) )
* ( ( 100 - JPPEDIDO.PDPERDES ) / 100 ) * ( ( 100 + JPPEDIDO.PDPERADI ) / 100 )
/ IF( IPQTDE = 0, 1, IPQTDE ) ), 5 ),
IPVALPRO = @PRENOT * IPQTDE
WHERE IPPEDIDO = nIdPedido;
SET @VALADI := NULL;
SET @PRENOT := NULL;
/* rateio e totais */
UPDATE JPPEDIDO
JOIN
(
SELECT SUM( IPVALPRO ) AS SOMA
FROM JPITPED
WHERE IPPEDIDO = nIdPedido
GROUP BY IPPEDIDO) AS TEMP SET PDVALPRO = TEMP.SOMA
WHERE IDPEDIDO = nIdPedido;
/* recalcula apos ajustes */
UPDATE JPITPED
JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
SET
IPVALFRE = ROUND( PDVALFRE * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALSEG = ROUND( PDVALSEG * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALOUT = ROUND( PDVALOUT * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALEXT = ROUND( PDVALEXT * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALDES = ROUND( PDVALDES * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 ),
IPVALADI = ROUND( PDVALADI * IPVALPRO / IF( PDVALPRO = 0, 1, PDVALPRO ), 2 )
WHERE JPITPED.IPPEDIDO = nIdPedido;
/* resoma item */
UPDATE JPITPED SET IPVALNOT = IPVALPRO
WHERE IPPEDIDO = nIdPedido;
/* resoma pedido */
UPDATE JPPEDIDO
JOIN
(
SELECT SUM( IPVALNOT ) AS SOMA
FROM JPITPED
WHERE IPPEDIDO = nIdPedido
GROUP BY IPPEDIDO ) AS TEMP
SET JPPEDIDO.PDVALNOT = TEMP.SOMA
WHERE IDPEDIDO = nIdPedido;
/* grava tributacao */
UPDATE JPITPED
LEFT JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
LEFT JOIN JPCADASTRO ON IDCADASTRO = PDCADASTRO
LEFT JOIN JPITEM ON IDPRODUTO = IPPRODUTO
LEFT JOIN JPTABUF ON IDUF = CDUFENT
LEFT JOIN JPTRANSACAO ON IDTRANSACAO = PDTRANSACAO
LEFT JOIN JPIMPOSTO
ON IMTRANSACAO = PDTRANSACAO
AND IMTRIUF = UFTRIUF
AND IMTRICAD = CDTRICAD
AND IMTRIPRO = IETRIPRO
LEFT JOIN JPTABIBPT ON IBCODIGO = JPITEM.IENCM
SET
IPCFOP = COALESCE( IMCFOP, '' ),
IPIIALI = COALESCE( IMIIALI, 0 ),
IPISSALI = COALESCE( IMISSALI, 0 ),
IPIPICST = COALESCE( IMIPICST, '' ),
IPIPIALI = COALESCE( IMIPIALI, 0 ),
IPIPIICM = COALESCE( IMIPIICM, '' ),
IPIPIENQ = COALESCE( IMIPIENQ, '' ),
IPICMCST = COALESCE( IMICMCST, '' ),
IPICMALI = COALESCE( IMICMALI, 0 ),
IPICMRED = COALESCE( IMICMRED, 0 ),
IPFCPALI = COALESCE( IMFCPALI, 0 ),
IPSUBALI = COALESCE( IMSUBALI, 0 ),
IPSUBIVA = COALESCE( IMSUBIVA, 0 ),
IPSUBRED = COALESCE( IMSUBRED, 0 ),
IPDIFCAL = COALESCE( IMDIFCAL, '' ),
IPDIFALII = COALESCE( IMDIFALII, 0 ),
IPDIFALIU = COALESCE( IMDIFALIU, 0 ),
IPDIFALIF = COALESCE( IMDIFALIF, 0 ),
IPPISCST = COALESCE( IMPISCST, 0 ),
IPPISALI = COALESCE( IMPISALI, 0 ),
IPPISENQ = COALESCE( IMPISENQ, '' ),
IPCOFCST = COALESCE( IMCOFCST, '' ),
IPCOFALI = COALESCE( IMCOFALI, 0 ),
IPCOFENQ = COALESCE( IMCOFENQ, '' ),
IPICSALI = COALESCE( IMICSALI, 0 ),
IPLEIS = COALESCE( IMLEIS, '' ),
IPTRIBUT = COALESCE( IDIMPOSTO, 0 )
WHERE IDPEDIDO = nIdPedido AND IPTRIBUT <> 999999;
UPDATE JPITPED
LEFT JOIN JPPEDIDO ON IDPEDIDO = IPPEDIDO
LEFT JOIN JPTRANSACAO ON IDTRANSACAO = PDTRANSACAO
LEFT JOIN JPITEM ON IDPRODUTO = IPPRODUTO
LEFT JOIN JPTABIBPT ON IBCODIGO = IENCM
SET
IPICMRED = IF( NOT SUBSTR( IPICMCST, 2 ) IN ( '20', '70', '90', '202', '203', '900' ), 0, IPICMRED ),
IPSUBALI = IF( NOT SUBSTR( IPICMCST, 2 ) IN ( '10', '20', '30', '70', '90', '900' ), 0, IPSUBALI ),
IPSUBIVA = IF( NOT SUBSTR( IPICMCST, 2 ) IN ( '10', '20', '30', '70', '90', '900' ), 0, IPSUBIVA ),
IPSUBRED = IF( NOT SUBSTR( IPICMCST, 2 ) IN ( '10', '20', '30', '70', '90', '900' ), 0, IPSUBRED ),
IPICMALI = IF( NOT SUBSTR( IPICMCST, 2 ) IN ( '00', '10', '20', '70', '90', '201', '202', '203', '900' ), 0, IPICMALI ),
IPICMRED = IF( NOT SUBSTR( IPICMCST, 2 ) IN ( '00', '10', '20', '70', '90', '201', '202', '203', '900' ), 0, IPICMRED ),
IPIMPALI = IF( TRREACAO LIKE '%CONSUMIDOR%',
IF( LEFT( IPICMCST, 1 ) IN ( '0', '3', '4', '5' ),
COALESCE( IBNACALI, 0 ), COALESCE( IBIMPALI, 0 ) ), 0 )
WHERE IPPEDIDO = nIdPedido;
UPDATE JPITPED
SET
IPVALPRO = ( @VALPRO := ROUND( IPPRENOT * IPQTDE, 2 ) ),
IPIIBAS = IF( IPIIALI > 0, @VALPRO, 0 ),
IPIIVAL = IF( IPIIALI > 0, FLOOR( @VALPRO * IPIIALI ) / 100, 0 ),
IPISSBAS = IF( IPISSALI > 0, @VALPRO + IPIIVAL, 0 ),
IPISSVAL = IF( IPISSALI > 0, FLOOR( ( @VALPRO + IPIIVAL ) * IPISSALI ) / 100, 0 )
WHERE IPPEDIDO = nIdPedido;
SET @IPVALPRO := NULL;
END;
;
Called from ADOCLASS:EXECUTECMD(262)
Called from UPDATESQL(1537)
Called from UPDATE2020(29)
Called from ZE_UPDATE(120)
Called from SISTEMA(66)
Called from (b)MAIN(77)
Chega a ser engraçado...
Tanto comando, e o erro é numa vÃrgula kkkkk
Ou melhor... num ponto e vÃrgula que veio do fonte PRG...