Sem 3‎ > ‎DBMS (SQL) LAB‎ > ‎

Oracle: 2 Understanding item_master table

posted Sep 3, 2011, 12:42 PM by Neil Mathew   [ updated Sep 3, 2011, 12:53 PM ]

ITEM_MASTER


ITNO NAME QOH CLA UOM ROL ROQ RATE
1090 Hammer 234 A pcs 12 34 400.9
1089 Saw 456 B pcs 17 23 800.89
1088 Lawn Mover 123 C pcs 21 21 5000.88
1087 Dish Washer 234 A pcs 76 45 950.87
1067 Baking Oven 145 A pcs 87 34 6000.67
1063 Spark Plug 150 C watt 34 67 750.63
1609 Alternator 168 B watt 50 56 750.09
1890 Battery 189 A volt 30 40 300.9
1378 Piston 234 B pcs 45 50 250.78

Qoh = Quantity on Hand
UoM = Unit of Measurement (pcs = pieces)
RoL = Reorder Level
RoQ = Reorder Quantity

 

Reorder Level

The Reorder Level represents the minimum stock level for the vehicle (equivalent to the MIN in a Min/Max-based system).  Whenever the vehicle drops at least 1 unit below the Reorder Level, auto-replenishment will automatically place an order with the central warehouse for shipment to the vehicle warehouse. (If the Reorder Level is set at 1, the technician will run out of that part before an order is placed. Therefore, it is recommended that you always set the Reorder Level to 2 or higher.)


A minimum amount of an item which a company holds in stock, such that, 
when stock falls to this amount, the item must be reordered


Example:

Warehouse

Part

Reorder Level

Reorder Quantity

FTSV092

12345678

2

3

Whenever the on-hand quantity of part 12345678 in vehicle FTSV092 drops below 2, a replenishment order will be created for parts to be shipped from the department’s central warehouse to this vehicle warehouse.


Reorder Quantity

The Reorder Quantity represents “how many more than the minimum are normally allowed” for this vehicle warehouse.

  • If you want to keep exactly a certain number on the vehicle, then the Reorder Quantity will be 0.
       For example, if you want to always keep 2 units of part XYZ on the vehicle, the reorder level is 2 and the reorder quantity is 0.


  • If you want to allow for some fluctuation in the on-hand quantity, then Reorder Quantity will be the difference between the minimum and maximum quantity to keep stocked.
       For example, if you want to always keep between 6 and 10 units of part XYZ on the vehicle, the reorder level is 6 and the reorder quantity is 4.


MAX stock level = Reorder Level + Reorder Quantity



Example:

Warehouse

Part

Reorder Level

Reorder Quantity

FTSV092

12345678

2

3


The maximum number to keep on hand [Reorder Level + Reorder Quantity] is 5. 


For part 12345678, the vehicle is to keep on-hand a quantity of between 2 and 5 at all times.  When placing an auto-replenishment order, the system will request the number needed to return the vehicle to a total of 5 for part 12345678.

If the vehicle reaches an on-hand quantity of 1, auto-replenishment will place an order for 4 more of this part.