Buenas tardes, estoy presentando un problema al ejecutar un Procedure adjunto el código por si alguien me pudiera ayudar. El error presentado es “SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near “.”: line 35 col 6 (at pos 1189)”.
DROP PROCEDURE SBO_SP_TransactionNotification;
-- B1 DEPENDS: BEFORE:PT:PROCESS_START
CREATE PROCEDURE SBO_SP_TransactionNotification
(
in object_type nvarchar(20), -- SBO Object Type
in transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
in num_of_cols_in_key int,
in list_of_key_cols_tab_del nvarchar(255),
in list_of_cols_val_tab_del nvarchar(255)
)
LANGUAGE SQLSCRIPT
AS
-- Return values
error int; -- Result (0 for no error)
error_message nvarchar (200); -- Error string to be displayed
begin
error := 0;
error_message := N'Ok';
--------------------------------------------------------------------------------------------------------------------------------
-- ADD YOUR CODE HERE
-----------------------------------------------------------------------------------
-- PEDIDOS
-----------------------------------------------------------------------------------
If object_type = '17' Then
DocEntry := list_of_cols_val_tab_del;
If transaction_type = 'A' or transaction_type = 'U' then
Declare NuevoPrecio Decimal:=0.00;
Declare Articulo NVARCHAR(50);
select Y."Nuevo Precio" Into NuevoPrecio,
Y."Código Artículo" Into Articulo
from (
select CASE WHEN X."Caso" = 1 and X."Familia" IN('TENEDORES','CUCHARAS') and X."Biodegradable" = 'N' and X."Validación" = 'SI' THEN ROUND(510 / 1.12,2)
WHEN X."Caso" = 1 and X."Familia" IN('TENEDORES','CUCHARAS') and X."Biodegradable" = 'Y' and X."Validación" = 'SI' THEN ROUND(540 / 1.12,2)
WHEN X."Caso" = 2 and X."Familia" IN('TENEDORES','CUCHARAS') and X."Biodegradable" = 'N' and X."Validación" = 'SI' THEN ROUND(480 / 1.12,2)
WHEN X."Caso" = 2 and X."Familia" IN('TENEDORES','CUCHARAS') and X."Biodegradable" = 'Y' and X."Validación" = 'SI' THEN ROUND(520 / 1.12,2)
WHEN X."Caso" = 3 and X."Familia" IN('TENEDORES','CUCHARAS') and X."Biodegradable" = 'N' and X."Validación" = 'SI' THEN ROUND(470 / 1.12,2)
WHEN X."Caso" = 3 and X."Familia" IN('TENEDORES','CUCHARAS') and X."Biodegradable" = 'Y' and X."Validación" = 'SI' THEN ROUND(510 / 1.12,2)
WHEN X."Caso" = 4 and X."Familia" IN('TENEDORES','CUCHARAS') and X."Biodegradable" = 'N' and X."Validación" = 'SI' THEN ROUND(460 / 1.12,2)
WHEN X."Caso" = 4 and X."Familia" IN('TENEDORES','CUCHARAS') and X."Biodegradable" = 'Y' and X."Validación" = 'SI' THEN ROUND(500 / 1.12,2)
WHEN X."Caso" = 1 and X."Familia" = 'CUCHARITA' and X."Biodegradable" = 'N' and X."Validación" = 'SI' THEN ROUND(175 / 1.12,2)
WHEN X."Caso" = 1 and X."Familia" = 'CUCHARITA' and X."Biodegradable" = 'Y' and X."Validación" = 'SI' THEN ROUND(185 / 1.12,2)
WHEN X."Caso" = 2 and X."Familia" = 'CUCHARITA' and X."Biodegradable" = 'N' and X."Validación" = 'SI' THEN ROUND(170 / 1.12,2)
WHEN X."Caso" = 2 and X."Familia" = 'CUCHARITA' and X."Biodegradable" = 'Y' and X."Validación" = 'SI' THEN ROUND(180 / 1.12,2)
WHEN X."Caso" = 3 and X."Familia" = 'CUCHARITA' and X."Biodegradable" = 'N' and X."Validación" = 'SI' THEN ROUND(165 / 1.12,2)
WHEN X."Caso" = 3 and X."Familia" = 'CUCHARITA' and X."Biodegradable" = 'Y' and X."Validación" = 'SI' THEN ROUND(175 / 1.12,2)
WHEN X."Caso" = 4 and X."Familia" = 'CUCHARITA' and X."Biodegradable" = 'N' and X."Validación" = 'SI' THEN ROUND(160 / 1.12,2)
WHEN X."Caso" = 4 and X."Familia" = 'CUCHARITA' and X."Biodegradable" = 'Y' and X."Validación" = 'SI' THEN ROUND(170 / 1.12,2)
ELSE X."Precio"
END as "Nuevo Precio",
X."Código Artículo"
from (
select Z."Validación",
Z."Total Fardos",
Z."Código Artículo",
Z."Descripción",
Z."Cantidad",
Z."Precio",
Z."Familia",
Z."Biodegradable",
CASE WHEN Z."Total Fardos" BETWEEN 1 AND 10 THEN 1
WHEN Z."Total Fardos" BETWEEN 11 AND 25 THEN 2
WHEN Z."Total Fardos" BETWEEN 26 AND 50 THEN 3
WHEN Z."Total Fardos" > 51 THEN 4
END as "Caso",
Z."DocNum"
from (
select CASE WHEN (select count(N."ItemCode")
from ORDR M INNER JOIN
RDR1 N ON
M."DocEntry" = N."DocEntry" LEFT JOIN
OITM O ON
N."ItemCode" = O."ItemCode" LEFT JOIN
OITB P ON
O."ItmsGrpCod" = P."ItmsGrpCod"
where O."QryGroup10" = 'Y' and
M."DocNum" = A."DocNum"
) > 1 and
count(B."Quantity") between 1 and 100 then 'SI'
ELSE 'NO'
END as "Validación",
(select sum(N."Quantity")
from ORDR M INNER JOIN
RDR1 N ON
M."DocEntry" = N."DocEntry" LEFT JOIN
OITM O ON
N."ItemCode" = O."ItemCode" LEFT JOIN
OITB P ON
O."ItmsGrpCod" = P."ItmsGrpCod"
where O."QryGroup10" = 'Y' and
M."DocNum" = A."DocNum"
) as "Total Fardos",
B."ItemCode" as "Código Artículo",
B."Dscription" as "Descripción",
B."Quantity" as "Cantidad",
ROUND(B."Price",2) as "Precio",
D."ItmsGrpNam" as "Familia",
C."QryGroup11" as "Biodegradable",
A."DocNum"
from ORDR A INNER JOIN
RDR1 B ON
A."DocEntry" = B."DocEntry" LEFT JOIN
OITM C ON
B."ItemCode" = C."ItemCode" LEFT JOIN
OITB D ON
C."ItmsGrpCod" = D."ItmsGrpCod"
where C."QryGroup10" = 'Y' and
A."DocNum" = DocEntry and
A."CardCode" NOT IN('C00124','C00335','C00125')
group by A."DocNum",
B."ItemCode",
B."Dscription",
B."Quantity",
B."Price",
D."ItmsGrpNam",
C."QryGroup11"
)Z
)X
where X."Código Artículo" = X."Código Artículo"
ORDER BY X."DocNum" DESC
)Y;
Update INV1
Set "Price" = NuevoPrecio
Where "DocEntry" = DocEntry and
"ItemCode" = Articulo;
Update INV1
Set "LineTotal" = "Quantity"*NuevoPrecio
Where "DocEntry" = DocEntry and
"ItemCode" = Articulo;
Update INV1
Set "VatSum" = "LineTotal"*0.12
Where "DocEntry" = DocEntry and
"ItemCode" = Articulo;
End If;
End If;
--------------------------------------------------------------------------------------------------------------------------------
-- Select the return values
select :error, :error_message FROM dummy;
end;
