Procediment per generar etiquetes per línies i quantitats d’un document de SAP Business One versió per HANA, per utilitzar amb el Crystal Reports:
CREATE PROCEDURE INTRX_DocumentLabels ( in docEntry int, in objectId int ) LANGUAGE SQLSCRIPT AS BEGIN CREATE LOCAL TEMPORARY COLUMN TABLE #DOC ( DocEntry int, LineNum int, ItemCode nVarChar(20), Quantity int, Cod_Name nVarChar(60), CodBar nVarChar(30), PVP double, Cod_Grupo nVarChar(20) ); INSERT INTO #DOC select T0.DocNum as DocEntry, T1.LineNum, T1.ItemCode as Articulo, T1.Quantity as Quantity, T1.OCRCode3 as Cod_Name, T1.ItemCode as CodBar,T1.PriceBefDI as PVP, T1.WHSCODE as Cod_Grupo from ENCABEZADO T0 join DETALLADO T1 on T1.DocNum=T0.DocNum join RETENCIONES T2 on T2.DocNum=T1.DocNum and T2.LineNum=T1.LineNum where T0.DocNum=:docEntry; CREATE LOCAL TEMPORARY COLUMN TABLE #RESULTATS ( DocEntry int, LineNum int, ItemCode nVarChar(20), Cod_Name nVarChar(60), CodBar nVarChar(30), PVP double, Cod_Grupo nVarChar(20) ); BEGIN DECLARE quantitat int; DECLARE idx int; DECLARE CURSOR registres FOR SELECT * FROM #DOC; FOR registre AS registres DO quantitat:=registre.Quantity; FOR idx IN 1 .. quantitat DO INSERT INTO #RESULTATS (DocEntry,LineNum,ItemCode,Cod_Name,CodBar,PVP,Cod_Grupo) VALUES (registre.DocEntry,registre.LineNum,registre.ItemCode,registre.Cod_Name,registre.CodBar,registre.PVP,registre.Cod_Grupo); END FOR; END FOR; END; select * from #RESULTATS; DROP TABLE #RESULTATS; DROP TABLE #DOC; END;
La idea es que a partir de un DocEntry de un albarán, el procedimiento sea capaz de devolver una SELECT con tantos registros como combinaciones de artículos/cantidad existan en ese albarán, es decir, un albarán de este tipo:
LINIA ARTICULO CANTIDAD
—– ————– —————-
1 ART01 2
2 ART02 1
debe devolver algo así:
DocEntry LineNum ItemCode Codebar
———– ———– ———– ————
1 1 ART01 8454320126032
1 1 ART01 8454320126032
1 2 ART02 8454430126043
para que puedan imprimir tres etiquetas.
Lógicamente hay que ampliar los campos para que devuelva la información que se necesite.
CREATE PROCEDURE INTRX_DocumentLabels
(
in docEntry int,
in objectId int
)
LANGUAGE SQLSCRIPT
AS
BEGIN
CREATE LOCAL TEMPORARY COLUMN TABLE #DOC
(
DocEntry int,
LineNum int,
ItemCode nVarChar(20),
Codebar nVarChar(20),
Quantity int
);
INSERT INTO #DOC
SELECT T0.DocNum as DocEntry, T1.LineNum, T1.ItemCode, T1.Quantity as Quantity, T1.Codebars as Codebar
FROM ODLN T0
JOIN DLN1 T1 on T1.DocNum=T0.DocNum
WHERE T0.DocNum=:docEntry;
CREATE LOCAL TEMPORARY COLUMN TABLE #RESULTATS
(
DocEntry int,
LineNum int,
ItemCode nVarChar(20),
Codebar nVarChar(20)
);
BEGIN
DECLARE quantitat int;
DECLARE idx int;
DECLARE CURSOR registres FOR SELECT * FROM #DOC;
FOR registre AS registres DO
quantitat:=registre.Quantity;
FOR idx IN 1 .. quantitat DO
INSERT INTO #RESULTATS (DocEntry,LineNum,ItemCode,Codebar) VALUES (registre.DocEntry,registre.LineNum,registre.ItemCode,registre.Codebar);
END FOR;
END FOR;
END;
SELECT * FROM #RESULTATS;
DROP TABLE #RESULTATS;
DROP TABLE #DOC;
END;