Help Joining OITW with the Union of INV1 and RIN1

[GAP_C]

Please your help, I'm almost there, I need to join the OITW table with stock information with the union of the tables INV1 and RIN1, which i have used to calculate the maximum sales per sku. My objective is to calculate if Inventory onHand + Requested is grater than the max sales multiply by 3, as 3 months is the lead time.

TABLE 1

SELECT
TBL."SKU",
TBL."DESCRIPCION",
MAX(TBL."TOTAL") AS MAX_VENTA
FROM
(
SELECT
MES,
SKU,
DESCRIPCION,
SUM(TOTAL) AS TOTAL
FROM
(
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
T3."Quantity" AS TOTAL
FROM INV1 T3
WHERE T3."ItemCode" IS NOT NULL
UNION ALL
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
-T3."Quantity" AS TOTAL
FROM RIN1 T3
WHERE T3."ItemCode" IS NOT NULL
) TABLA1
GROUP BY MES, SKU, DESCRIPCION
ORDER BY MES, SKU
) TBL
GROUP BY SKU, DESCRIPCION
ORDER BY SKU

TABLE 2

SELECT
T0."ItemCode" AS SKU,
T0."OnHand",
T0."IsCommited",
T0."OnOrder",
T0."Counted"
FROM OITW T0

[Johan_H · Accepted solution]

Hi,

I think it is reasonably simple. You are almost there, like you said:

SELECT
TBL."SKU",
TBL."DESCRIPCION",
MAX(TBL."TOTAL") AS MAX_VENTA
/* T0. fields here as needed */
FROM
(
SELECT
MES,
SKU,
DESCRIPCION,
SUM(TOTAL) AS TOTAL
FROM
(
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
T3."Quantity" AS TOTAL,
T3."WhsCode"
FROM INV1 T3
WHERE T3."ItemCode" IS NOT NULL
UNION ALL
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
-T3."Quantity" AS TOTAL,
T3."WhsCode"
FROM RIN1 T3
WHERE T3."ItemCode" IS NOT NULL
) TABLA1
GROUP BY MES, SKU, DESCRIPCION
ORDER BY MES, SKU
) TBL
INNER JOIN OITW T0 ON TBL."SKU" = T0."ItemCode" AND TBL."WhsCode" = T0."WhsCode"
GROUP BY SKU, DESCRIPCION /* remember to also group the necessary T0 fields */
ORDER BY SKU

Regards,

Johan

[GAP_C · Accepted solution]

Dear Johan_H, thanks a lot, it worked perfectly.

[GAP_C]

---

@GAP_C wrote:

Please your help, I'm almost there, I need to join the OITW table with stock information with the union of the tables INV1 and RIN1, which i have used to calculate the maximum sales per sku. My objective is to calculate if Inventory onHand + Requested is grater than the max sales multiply by 3, as 3 months is the lead time.

TABLE 1

SELECT
TBL."SKU",
TBL."DESCRIPCION",
MAX(TBL."TOTAL") AS MAX_VENTA
FROM
(
SELECT
MES,
SKU,
DESCRIPCION,
SUM(TOTAL) AS TOTAL
FROM
(
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
T3."Quantity" AS TOTAL
FROM INV1 T3
WHERE T3."ItemCode" IS NOT NULL
UNION ALL
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
-T3."Quantity" AS TOTAL
FROM RIN1 T3
WHERE T3."ItemCode" IS NOT NULL
) TABLA1
GROUP BY MES, SKU, DESCRIPCION
ORDER BY MES, SKU
) TBL
GROUP BY SKU, DESCRIPCION
ORDER BY SKU

TABLE 2

SELECT
T0."ItemCode" AS SKU,
T0."OnHand",
T0."IsCommited",
T0."OnOrder",
T0."Counted"
FROM OITW T0

---

This is the final script I used.

SELECT
TBL2."SKU",
TBL2."DESCRIPCION",
TBL2."MAX_VENTA",
T0."OnHand",
T0."IsCommited",
T0."OnOrder"
FROM
(
SELECT
TBL."SKU",
TBL."DESCRIPCION",
MAX(TBL."TOTAL") AS MAX_VENTA
FROM
(
SELECT
MES,
SKU,
DESCRIPCION,
SUM(TOTAL) AS TOTAL
FROM
(
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
T3."Quantity" AS TOTAL
FROM INV1 T3
WHERE T3."ItemCode" IS NOT NULL
UNION ALL
SELECT
MONTH(T3."DocDate") AS MES,
T3."ItemCode" AS SKU,
T3."Dscription" AS DESCRIPCION,
-T3."Quantity" AS TOTAL
FROM RIN1 T3
WHERE T3."ItemCode" IS NOT NULL
) TABLA1
GROUP BY MES, SKU, DESCRIPCION
ORDER BY MES, SKU
) TBL
GROUP BY SKU, DESCRIPCION 
ORDER BY SKU
) TBL2
INNER JOIN OITM T0 ON TBL2."SKU" = T0."ItemCode"
WHERE T0."InvntItem"='Y'
ORDER BY "SKU"

[nidhisingh14]

Hi GAP_C 

Please check below mentioned similar posts:

https://community.sap.com/t5/enterprise-resource-planning-q-a/total-quantity-of-inv1-rin1/qaq-p/9240...

Hope it helps.

Kind Regards
Nidhi Singh
SAP Business One Support

[BattleshipCobra]

Hello!

I don't exact follow your requirements.  Assuming since you're using OITW you just want current information so something like this would work.  It just uses the last 30 days worth of data for max quantity and then multiplies by three:

SELECT

T0."ItemCode" AS "SKU"

,MAX(T0."ItemName") AS "DESCRIPCION"

,YEAR(CURRENT_DATE) || '-' || LPAD(MONTH(CURRENT_DATE),2,0) AS "MES"

,IFNULL(SUM(X."Quantity"),0) AS "MAX_VENTA"

,IFNULL(SUM(X."Quantity"),0) * 3 AS "VENTA_X3"

,MAX(Y."OnHand") AS "ONHAND"

,MAX(Y."IsCommited") AS "COMMITTED"

,MAX(Y."OnOrder") AS "ORDERED"

,MAX(Y."Available") AS "AVAILABLE"

,CASE

WHEN ( IFNULL(SUM(X."Quantity"),0) * 3 ) > MAX(Y."Available") THEN 'WARNING'

ELSE '' END AS "STOCK_CHECK"

,CASE

WHEN ( IFNULL(SUM(X."Quantity"),0) * 3 ) > MAX(Y."Available") THEN (IFNULL(SUM(X."Quantity"),0) * 3) - MAX(Y."Available")

ELSE NULL END AS "TO_BUY"

FROM

OITM T0

LEFT JOIN (

-- INVOICES

SELECT

D0."ItemCode"

,D0."DocDate"

,D0."Quantity"

FROM INV1 D0

UNION ALL

-- CREDITS

SELECT

D0."ItemCode"

,D0."DocDate"

,D0."Quantity" * -1 AS "Quantity"

FROM RIN1 D0

) X ON T0."ItemCode" = X."ItemCode" 

AND 

-- FILTER DATE RANGE FROM DOCS

X."DocDate" BETWEEN ADD_DAYS(CURRENT_DATE,-30) AND CURRENT_DATE

INNER JOIN (

-- WAREHOUSE

SELECT

D0."ItemCode"

,SUM(D0."OnHand") AS "OnHand"

,SUM(D0."IsCommited") AS "IsCommited"

,SUM(D0."OnOrder") AS "OnOrder"

,SUM(D0."OnHand" - D0."IsCommited" + D0."OnOrder") AS "Available"

FROM OITW D0

GROUP BY D0."ItemCode"

) Y ON T0."ItemCode" = Y."ItemCode"

GROUP BY T0."ItemCode"

ORDER BY T0."ItemCode"

If nothing else you can see some different techniques for joining derived tables and things.

I think it's better to use OITM (Item Master) as the base and then join the tables off of it so you can see all of your items.  You can always filter out items but this will give you a complete list.

There are a ton of ways to do this, hopefully this gets you close!

Mike

PS. I had this all nicely formatted but the forum removed it... sorry about the lack of indenting.