SAP B1 Hana, solicitar 2 datos query manager

SAP Business One
1

Cordial saludo, tengo el siguiente código:

/* select from [dbo].[OPKL] T0 */


SELECT 
"F. Despacho", 
"Nº Pedido", 
"F. Documento", 
"F. Vence", 
"Diferencia", 
"Ciudad", 
"Nombre SN" AS "Cliente", 
SUM("Kilos Solicitados") AS "Kilogramos Solicitados", 
SUM("Kilos Despachados") AS "Kilogramos Despachados",
SUM("Kilos Pendientes") AS "Kilogramos Pendientes",

((SUM("Kilos Despachados")*100 ) / SUM("Kilos Solicitados") )  AS "% Cumplimiento"



FROM 

(
SELECT DISTINCT 
CAST(T10."PickDate" AS DATE) AS "F. Despacho", ---- ok
T0."DocNum" AS "Nº Pedido", ---- ok
-----CASE WHEN T0."NumAtCard" IS NULL THEN T8."NumAtCard" ELSE T0."NumAtCard" END AS "O.C. Cliente", 
-----T1."PickIdNo" AS "Nº Picking",
CAST (T0."DocDate" AS DATE) AS "F. Documento", ---- ok
CAST (T0."DocDueDate" AS DATE) AS "F. Vence", ---- ok


DAYS_BETWEEN (TO_DATE (CAST(T10."PickDate" AS DATE), 'YYYY-MM-DD'), 
TO_DATE(CAST(T0."DocDueDate" AS DATE), 'YYYY-MM-DD')) AS "Diferencia", ---- ok



T2."CityS" AS "Ciudad", ---- ok

-----T1."WhsCode" AS "Bodega", 
-----T0."CardCode" AS "Cod. SN", 

T0."CardName" AS "Nombre SN",  ----- ok
-----T1."ItemCode" AS "Producto", 
-----T1."Dscription" AS "Descripcion",
(T1."Quantity" * T1."NumPerMsr" * T5."IWeight1") AS "Kilos Solicitados", ---- ok

CASE WHEN T11."DelivrdQty" IS NULL THEN 
	(T1."DelivrdQty" * T1."NumPerMsr" * T5."IWeight1") 
ELSE ((T1."DelivrdQty" * T1."NumPerMsr" * T5."IWeight1") 
		+ (T11."DelivrdQty" * T1."NumPerMsr" * T5."IWeight1"))
END AS "Kilos Despachados", ---- ok

((T1."Quantity" * T1."NumPerMsr" * T5."IWeight1") - 
(CASE WHEN T11."DelivrdQty" IS NULL THEN 
	(T1."DelivrdQty" * T1."NumPerMsr" * T5."IWeight1") 
ELSE ((T1."DelivrdQty" * T1."NumPerMsr" * T5."IWeight1") 
		+ (T11."DelivrdQty" * T1."NumPerMsr" * T5."IWeight1"))
END)) AS "Kilos Pendientes", ----- ok

ROUND((((T1."DelivrdQty" * T1."NumPerMsr" * T5."IWeight1") 
/(T1."Quantity" * T1."NumPerMsr" * T5."IWeight1"))*100),0)||'%' AS "Cumplimiento" ---- ok



FROM ORDR T0  
INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry" 
INNER JOIN RDR12 T2 ON T0."DocEntry" = T2."DocEntry" 
INNER JOIN OSLP T3 ON T0."SlpCode" = T3."SlpCode" 
INNER JOIN OCRD T4 ON T0."CardCode" = T4."CardCode" 
INNER JOIN OITM T5 ON T1."ItemCode" = T5."ItemCode" 
INNER JOIN OITW T6 ON T1."ItemCode" = T6."ItemCode" AND T1."WhsCode" = T6."WhsCode"
INNER JOIN NNM1 T7 ON T0."Series" = T7."Series" AND T0."ObjType" = T7."ObjectCode"
LEFT JOIN OQUT T8 ON T1."BaseType" = T8."ObjType" AND T1."BaseEntry" = T8."DocEntry"
LEFT JOIN QUT1 T9 ON T8."DocEntry" = T9."DocEntry" AND T0."ObjType" = T9."TargetType"
					AND T0."DocEntry" = T9."TrgetEntry"
LEFT JOIN OPKL T10 ON T1."PickIdNo" = T10."AbsEntry"
LEFT JOIN(SELECT T1."BaseRef", 
			T1."BaseType", 
			T1."BaseEntry", 
			T1."BaseLine", 
			T1."ItemCode", 
			T1."DelivrdQty"
			FROM OINV T0  
			INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
			WHERE T0."CANCELED" = 'N'
			)T11 ON T0."Ref1" = T11."BaseRef"
			AND T0."ObjType" = T11."BaseType"
			AND T0."DocEntry" = T11."BaseEntry"
			AND T1."LineNum" = T11."BaseLine"
			AND T1."ItemCode" = T11."ItemCode"

WHERE T10."PickDate" >=   [%0]
AND T10."PickDate" <=  [%1]
AND T1."WhsCode" =  '06'
AND T1."ItemCode" LIKE '11%'
AND T0."CANCELED" = 'N'
AND T1."TargetType" != -1

GROUP BY T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T0."DocDueDate", 
T1."ItemCode", T1."Dscription", T1."Quantity", T1."UomCode", T1."InvQty", T1."Price", 
T2."CityS", T3."SlpName", T5."IWeight1", T1."WhsCode", T6."OnHand", T1."NumPerMsr", 
T2."StreetS", T8."NumAtCard", T7."SeriesName", T5."NumInSale", T5."FrgnName", T1."DelivrdQty",
T0."NumAtCard", T1."PickIdNo", T10."PickDate", T11."DelivrdQty"

----ORDER BY T0."DocNum"
)

GROUP BY 
"F. Despacho", 
"Nº Pedido", 
"F. Documento", 
"F. Vence", 
"Diferencia", 
"Ciudad", 
"Nombre SN"


ORDER BY "F. Despacho"

Dentro de los datos estoy solicitando un rango de fecha y tambien quiero solicitar el numero del almacen, actualmente funciona dejando el almacen por defecto como se ve a continuación:

AND T1."WhsCode" =  '06'

Quiero que este dato de almacen sea ingresado, sin embargo cuando solicito el dato como se ve en el codigo a continuación, la consulta deja de funcionar

AND T1."WhsCode" =  [%2]

Como podria escribir esto para que sea posible solicitar tambien el dato del almacén?

2

prueba colocando comillas simples a tu parametros: ‘[%0]’
y si no funciona asi, puedes usar variables, y cargara los parametros a las variables al inicio.

Declare FI  timestamp
SET @FI :=  (/*SELECT I0.DocDate FROM SBO_FERTEC.dbo.ORCT I0 WHERE I0."DocDate"= */'[%0]')
3

Como estas, hice la prueba con ‘[%0]’ pero no funciono, tan pronto pregunto el dato del almacén se muestra error.

img%200

Ese código que colocas como segunda opción también funciona para SAP Business one Hana?

4

Hola @camilog
El problema se presenta cuando se utilizan parámetros en subconsultas. Prueba de la siguiente manera, a mí me ha funcionado.

/* SELECT * FROM OINV a WHERE a."DocDate" = '[%0]' AND a."DocDate" = '[%1]' */

SELECT T0.*
FROM "OINV"  T0
WHERE T0."CreateDate" BETWEEN '[%0]' AND '[%1]'

Nota: el Declare solo es valido para sqlServer

Andres Ramirez Jaramillo :colombia:

1 me gusta
5

@camilog Prueba con esta manera, funciona para hana.

/* Select FROM OINV a WHERE a."DocDate" = [%0] and a."DocDate" = [%1] */

Select COUNT(*) FROM "OINV"  WHERE "DocDate" >  [%0] and "DocDate" <  [%1];

Espero te sriva.

2 Me gusta
6

Con la fecha no tengo problema, el problema lo tengo es al solicitar el almacen.

Serian 3 datos los que se solicitan: fecha inicial, fecha final y numero de bodega.

7

Con la fecha no tengo problema, el problema lo tengo es al solicitar el almacen.
Serian 3 datos los que se solicitan: fecha inicial, fecha final y numero de bodega.
Conoces una opcion para tres datos?

8

Hola @andresramirez

Para solicitar los 3 datos use como guia tu codigo y finalmente lo conseguí muchas gracias.

/* Select FROM OINV a INNER JOIN INV1 T1 ON a."DocEntry" = T1."DocEntry" WHERE a."DocDate" = [%0] and a."DocDate" = [%1] and T1."WhsCode" = [%2] */
2 Me gusta