Quisiera saber en que version de SAP BO ejecutaron esta query, si se tuvieron que definir las funciones, estoy trabajando con SAP 9.3, queria veria ver si el resultado me servia pero me sale error de sintaxis por los ||
Continuando la discusión desde Query de saldos por mes :
SELECT
EJERCICIO,
PERIODO,
COD_ALMACEN,
ALMACEN,
COD_GRUPO,
GRUPO,
COD_ARTICULO,
ARTICULO,
PRECIO,
SW_OBSOLETO,
CANTIDAD,
SUM(CANTIDAD) OVER ( PARTITION BY COD_ALMACEN,COD_ARTICULO ORDER BY EJERCICIO,PERIODO,COD_ALMACEN,COD_ARTICULO) AS STOCK
FROM
(
SELECT
year(TC."RateDate") AS EJERCICIO,
month(TC."RateDate") AS PERIODO,
ST."WhsCode" AS COD_ALMACEN,
ALM."WhsName" AS ALMACEN,
ART."ItmsGrpCod" AS COD_GRUPO,
UPPER(GR."ItmsGrpNam") AS GRUPO,
ART."ItemCode" AS COD_ARTICULO,
ART."ItemName" AS ARTICULO,
ART."LastPurPrc" AS PRECIO,
(
SELECT
CASE WHEN MESES_SIN_CONS >= 6 THEN 1 ELSE 0 END
FROM
(
SELECT
MONTHS_BETWEEN (MV."DocDate",LAST_DAY(TO_DATE(year(TC."RateDate")||'-'||month(TC."RateDate")||'-'||'1'))) AS MESES_SIN_CONS,
row_number()over(order by MV."DocDate" desc) rn
FROM OINM MV
WHERE MV."DocDate" <= LAST_DAY(TO_DATE(year(TC."RateDate")||'-'||month(TC."RateDate")||'-'||'1'))
AND MV."ItemCode" = ART."ItemCode" AND MV."Warehouse" = MV."Warehouse"
) WHERE rn=1
) AS SW_OBSOLETO,
case when exists (
select * from OINM MV
where MV."ItemCode" = ART."ItemCode" and YEAR(MV."DocDate") = year(TC."RateDate") AND month(MV."DocDate") = month(TC."RateDate")
AND MV."Warehouse" = ST."WhsCode"
) then
(
select IFNULL(SUM("InQty")-SUM("OutQty"),0) from OINM MV
where MV."ItemCode" = ART."ItemCode" and YEAR(MV."DocDate") = year(TC."RateDate") AND month(MV."DocDate") = month(TC."RateDate")
AND MV."Warehouse" = ST."WhsCode"
)
else 0
end AS CANTIDAD
FROM ORTT TC
INNER JOIN OITM ART ON 2021 = 2021
INNER JOIN OITB AS GR on GR."ItmsGrpCod" = ART."ItmsGrpCod"
INNER JOIN OITW ST ON ST."ItemCode" = ART."ItemCode"
INNER JOIN OWHS ALM ON ALM."WhsCode" = ST."WhsCode"
WHERE TC."RateDate" <= CURRENT_DATE AND ART."ItmsGrpCod" NOT IN ('111','112')
AND EXISTS (
select * from OINM MV
where YEAR(MV."DocDate") = year(TC."RateDate") AND month(MV."DocDate") = month(TC."RateDate")
AND MV."Warehouse" = ST."WhsCode"
)
GROUP BY
year(TC."RateDate"),
month(TC."RateDate"),
ST."WhsCode",
ALM."WhsName",
ART."ItmsGrpCod",
UPPER(GR."ItmsGrpNam"),
ART."ItemCode",
ART."ItemName",
ART."LastPurPrc"
ORDER BY 1,2
)
Esta desarrollado para motor HANA