Buen dia a todos, les escribo pidiendo su apoyo a lo siguiente estoy trabajando en un reporte en Crystal Reports que funciona muy bien al visualizarlo desde sap bussines one como muestra en las capturas de pantalla
Este el codigo utilizado obtener el reporte , trabajo con sap b1 9.3 y Hana.
SELECT DISTINCT T0."ItemCode" CODIGO, T0."ItemName" DESCRIPCION,T0."OnHand" STOCK,T0."IsCommited" COMPROMETIDO, T0."OnOrder" PEDIDO,
(CASE T1."Currency"
WHEN 'US$' THEN ((T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18)*(SELECT "Rate" FROM ORTT WHERE "RateDate" =TO_DATE(CURRENT_DATE)))
ELSE (T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18) END) "Precio en S/.",
(CASE T1."Currency"
WHEN 'S/' THEN ((T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18)/(SELECT "Rate" FROM ORTT WHERE "RateDate" =TO_DATE(CURRENT_DATE)))
ELSE (T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18) END) "Precio en US$ ",(SELECT "Rate" FROM ORTT WHERE "RateDate" =TO_DATE(CURRENT_DATE)) TC
FROM OITM T0 INNER JOIN ITM1 T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OIBQ T2 ON T0."ItemCode" = T2."ItemCode"
WHERE
UPPER(T0."ItemName") NOT LIKE '%VIRTUAL%'
and T1."PriceList" = 1
and T0."frozenFor"<>'Y'
ORDER BY T0."ItemName"
Al parece es con la columna de dolares , no entiendo porque no se visualiza desde el aplicativo.
Hola si disculpa la demora, el problema era en el query , en la parte de la subconsulta donde jalo el tipo de cambio (SELECT “Rate” FROM ORTT WHERE “RateDate” =TO_DATE(CURRENT_DATE) AND “Currency” =‘US$’) , faltaba definir que la moneda sea la dolar.
Aqui dejo mi Query corregido:
SELECT DISTINCT T0."ItemCode" CODIGO, T0."ItemName" DESCRIPCION,T0."OnHand" STOCK,T0."IsCommited" COMPROMETIDO, T0."OnOrder" PEDIDO,
T1."Currency" MON,(T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18) "Precio",
(CASE T1."Currency"
WHEN 'US$' THEN (T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18)*(SELECT "Rate" FROM ORTT WHERE "RateDate" =TO_DATE(CURRENT_DATE) AND "Currency" ='US$')
ELSE ((T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18)) END) "Precio S/",
(CASE T1."Currency"
WHEN 'US$' THEN (T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18)
ELSE ((T1."Price"*(100-T0."U_SYP_DSCTO")*0.01*1.10*1.18))/(SELECT "Rate" FROM ORTT WHERE "RateDate" =TO_DATE(CURRENT_DATE) AND "Currency" ='US$') END) "Precio US$",
(SELECT "Rate" FROM ORTT WHERE "RateDate" =TO_DATE(CURRENT_DATE) AND "Currency" ='US$') TC
FROM OITM T0 INNER JOIN ITM1 T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN OIBQ T2 ON T0."ItemCode" = T2."ItemCode"
WHERE
UPPER(T0."ItemName") NOT LIKE '%VIRTUAL%'
and T1."PriceList" = 1
and T0."frozenFor"<>'Y'
ORDER BY T0."ItemName"