Focal Point
[SOLVED]Stock Out day

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/4907032086

January 11, 2016, 04:36 PM
Petter
[SOLVED]Stock Out day
I have one table that shows the material needs by item and date, and another table that shows on-hand inventory by item. I need to find out when we are running out of stock. I want to clarify that I only have access to Web Focus reporting tool. Our company does not use Web Focus maintain. Here is an simplified example of the tables.

TABLE: MATNEEDS This table shows the quantity needed for each part at a giving date.
========================================================================================
ITEM DATE NEED UNIT
A01 01/11/15 10 EA
A01 01/12/15 5 EA
A01 01/15/15 8 EA
A01 01/16/15 3 EA

BBB 01/11/15 8 EA
BBB 01/12/15 3 EA
BBB 01/13/15 3 EA
BBB 01/14/15 2 EA


Table: OHHAND This table show the quantity of each item we have on our warehouse
=================================================================================
ITEM INVENTORY UNIT
A01 21 EA
BBB 12 EA

So, I want to join both tables and then find out when we run out of stock.

ITEM DATE NEED INVENTORY UNIT INVENTORY - NEED
A01 01/11/15 10 21 EA 21 - 10 = 11
A01 01/12/15 5 21 EA 11 - 5 = 6 ==> Notice that 11 comes from the previous calculation 21-10 = 11
A01 01/15/15 8 21 EA 6 - 8 = -2 ==> At this point, I know I will run out of stock on 1/15/13
A01 01/16/15 3 21 EA -2 -3 = -5

BBB 01/11/15 8 12 EA 12 -8 = 4
BBB 01/12/15 3 12 EA 4 - 3 = 1 ==> Notice that 4 comes from the previous calculation 12-8 = 4
BBB 01/13/15 3 12 EA 1 - 3 = -2 ==> At this point, I know I will run out of stock on 1/13/13
BBB 01/14/15 2 12 EA -2 - 12 = -14

Can someone show me how to accomplish the calculation described in the INVENTORY-NEED column above? Or any other way to get the same info.

Thank you

Petter

This message has been edited. Last edited by: <Emily McAllister>,


WebFocus 7703
Windows, All Outputs
January 11, 2016, 07:19 PM
Ricardo Augusto
Hi Petter,

Maintain is used to create forms to insert or update records. BTW, we can do that using FOCUS too.

I will try to resume my suggestion to solve your report:

1-

JOIN CLEAR *
JOIN ITEM IN MATNEEDS TO ITEM IN OHHAND AS J1
END

TABLE FILE MATNEEDS
SUM NEED
INVENTORY
COMPUTE STOCK/D8 = INVENTORY - NEED;
BY ITEM
BY DATE
END

2- Add a conditional styling to highlight STOCK column when it is LT 0

3- Read the manual about JOIN, TABLE, DEFINE and COMPUTE. The verbs too, of course.

I hope it helps. Good lucky!


WebFOCUS 8.1.05 / APP Studio
January 12, 2016, 07:44 AM
MartinY
Hi Petter,
here a sample :
TABLE FILE GGSALES
SUM BUDUNITS
BY REGION
BY PRODUCT
SUM UNITS
BY REGION
BY PRODUCT
BY DATE
ON TABLE HOLD AS TMP1 FORMAT FOCUS
END

DEFINE FILE TMP1
REMAIN /I8 = IF LAST REGION  EQ REGION  THEN (IF LAST PRODUCT EQ PRODUCT THEN LAST REMAIN - UNITS ELSE BUDUNITS - UNITS) ELSE BUDUNITS - UNITS;
END
TABLE FILE TMP1
SUM BUDUNITS
    UNITS
    REMAIN
BY REGION
BY PRODUCT
BY DATE
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     DEFMACRO=OUTOFSTOCK,
     MACTYPE=RULE,
     WHEN=REMAIN LE 0,
$
TYPE=DATA,
     COLUMN=REGION,
     COLOR=RED,
     MACRO=OUTOFSTOCK,
$
TYPE=DATA,
     COLUMN=PRODUCT,
     COLOR=RED,
     MACRO=OUTOFSTOCK,
$
TYPE=DATA,
     COLUMN=DATE,
     COLOR=RED,
     MACRO=OUTOFSTOCK,
$
TYPE=DATA,
     COLUMN=BUDUNITS,
     COLOR=RED,
     MACRO=OUTOFSTOCK,
$
TYPE=DATA,
     COLUMN=UNITS,
     COLOR=RED,
     MACRO=OUTOFSTOCK,
$
TYPE=DATA,
     COLUMN=REMAIN,
     COLOR=RED,
     MACRO=OUTOFSTOCK,
$
END



WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
January 12, 2016, 10:02 AM
Petter
Thank you for the suggestion but it does not work.
The STOCK field resulting from the COMPUTE command shows INVENTORY-NEED for each line. It does not take into account that some of the inventory was consumed on the previous day.

DATE ITEM NEED INVENTORY STOCK
01/11/15 A01 10 21 11
01/12/15 A01 5 21 16
01/15/15 A01 8 21 13
01/16/15 A01 3 21 18

It should be as follow:
DATE ITEM NEED INVENTORY STOCK
01/11/15 A01 10 21 11
01/12/15 A01 5 21 6
01/15/15 A01 8 21 -2
01/16/15 A01 3 21 -5

On 1/12/15, the stock is 6 EA. It is the result of subtracting he previous day remaining stock (11) and the current day need (5). Same process for the next day and so on.


WebFocus 7703
Windows, All Outputs
January 12, 2016, 10:09 AM
Ricardo Augusto
Use LAST at your COMPUTE like MartinY suggested.


WebFOCUS 8.1.05 / APP Studio
January 12, 2016, 10:30 AM
RSquared
Petter,

Try something like this

quote:
JOIN CLEAR *
JOIN ITEM IN MATNEEDS TO ITEM IN OHHAND AS J1 END
TABLE FILE MATNEEDS
PRINT NEED INVENTORY
BY ITEM BY DATE
ON TABLE HOLD AS HOLDITEM
END
DEFINE FILE HOLDITEM
INV_ON_HAND/D20=IF ITEM EQ LAST ITEM THEN
INVENTORY - LAST NEED ELSE INVENTORY;
END
TABLE FILE HOLDITEM
PRINT NEED
INV_ON_HAND
COMPUTE INV_LEFT/D20=INV_ON_HAND - NEED;
BY ITEM BY DATE




WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
January 12, 2016, 10:44 AM
Ricardo Augusto
Good One


WebFOCUS 8.1.05 / APP Studio
January 12, 2016, 11:20 AM
Petter
Thank Martin, Ricardo and Guru!!

I used your suggestions and finished my report


WebFocus 7703
Windows, All Outputs
January 12, 2016, 11:25 AM
Ricardo Augusto
Nice to read that.
Please update subject to [SOLVED].

Have a nice work-day.


WebFOCUS 8.1.05 / APP Studio