How to create inventory for a product

[pinaki_22]

Hi All,

I am doing a load test on ofbiz and for the check out process in the script I have providing some prodcut id for check out. These products have only one inventory by default. So to continue the checkout process I heve set the flag Check Inventory=N in the store.

The checkout process continues and at the end of the test a lot of records are created in the INVENTORY_ITEM  table. Does this table get populated when there is no physical inventory in the data base
if yes then how can I increase that? What is the purpose of INVENTORY_ITEM_DETAIL table. Do records insert into this table when prodcut has inventory??

As the check out process continues the invocation of the following select query increases.
SELECT INVENTORY_ITEM_ID, INVENTORY_ITEM_TYPE_ID, PRODUCT_ID, PARTY_ID, OWNER_PARTY_ID, STATUS_ID, DATETIME_RECEIVED, DATETIME_MANUFACTURED, EXPIRE_DATE, FACILITY_ID, CONTAINER_ID, LOT_ID, UOM_ID, BIN_NUMBER, LOCATION_SEQ_ID, COMMENTS, QUANTITY_ON_HAND_TOTAL, AVAILABLE_TO_PROMISE_TOTAL, QUANTITY_ON_HAND, AVAILABLE_TO_PROMISE, SERIAL_NUMBER, SOFT_IDENTIFIER, ACTIVATION_NUMBER, ACTIVATION_VALID_THRU, UNIT_COST, CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP FROM INVENTORY_ITEM WHERE (PRODUCT_ID = ? AND FACILITY_ID = ?)

As it is a select query and as the record in this table (INVENTORY_ITEM) increases it is adding to the performance over head.

So Please tell me how I can increase the inventory of a product and utility of the tables INVENTORY_ITEM and INVENTORY_ITEM_DETAIL .

Thanks..

[James McGill-5]

On Tue, Nov 9, 2010 at 5:16 AM, pinaki_22 <[hidden email]> wrote:

>
>  What is the purpose of
> INVENTORY_ITEM_DETAIL table.
>

InventoryItemDetail is equivalent to a credit / debit system for
quantities.  Instead of tracking the quantity only in the InventoryItem, the
Detail records provide a running total of transactions that can be audited
against the InventoryItem.

If you want to manually change the ATP and QOH quantities, use the Variance
service.

In a production system, variances will probably be rare, since inventory
should be created as a result of shipment receipts or work efforts.

--
James McGill
Phoenix AZ

[BJ Freeman]

Setting Check Inventory=N(Product.requireInventory) means an order can
proceed regardless of inventory levels.
Unless you set SupplierProduct.canDropShip=Y Inventory is used.

=========================
BJ Freeman
Strategic Power Office with Supplier Automation  <http://www.businessesnetwork.com/automation/viewforum.php?f=52>
Specialtymarket.com  <http://www.specialtymarket.com/>
Systems Integrator-- Glad to Assist

Chat  Y! messenger: bjfr33man


pinaki_22 sent the following on 11/9/2010 4:16 AM:

>
> Hi All,
>
> I am doing a load test on ofbiz and for the check out process in the script
> I have providing some prodcut id for check out. These products have only one
> inventory by default. So to continue the checkout process I heve set the
> flag Check Inventory=N in the store.
>
> The checkout process continues and at the end of the test a lot of records
> are created in the INVENTORY_ITEM  table. Does this table get populated when
> there is no physical inventory in the data base
> if yes then how can I increase that? What is the purpose of
> INVENTORY_ITEM_DETAIL table. Do records insert into this table when prodcut
> has inventory??
>
> As the check out process continues the invocation of the following select
> query increases.
> SELECT INVENTORY_ITEM_ID, INVENTORY_ITEM_TYPE_ID, PRODUCT_ID, PARTY_ID,
> OWNER_PARTY_ID, STATUS_ID, DATETIME_RECEIVED, DATETIME_MANUFACTURED,
> EXPIRE_DATE, FACILITY_ID, CONTAINER_ID, LOT_ID, UOM_ID, BIN_NUMBER,
> LOCATION_SEQ_ID, COMMENTS, QUANTITY_ON_HAND_TOTAL,
> AVAILABLE_TO_PROMISE_TOTAL, QUANTITY_ON_HAND, AVAILABLE_TO_PROMISE,
> SERIAL_NUMBER, SOFT_IDENTIFIER, ACTIVATION_NUMBER, ACTIVATION_VALID_THRU,
> UNIT_COST, CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
> CREATED_STAMP, CREATED_TX_STAMP FROM INVENTORY_ITEM WHERE (PRODUCT_ID = ?
> AND FACILITY_ID = ?)
>
> As it is a select query and as the record in this table (INVENTORY_ITEM)
> increases it is adding to the performance over head.
>
> So Please tell me how I can increase the inventory of a product and utility
> of the tables INVENTORY_ITEM and INVENTORY_ITEM_DETAIL .
>
> Thanks..

[Heidi Dehaes - Olagos]

There are 4 tables,
- inventory_item
- inventory_item_detail
- PhysicalInventory
- Inventory_item_variance

I understand the use of the tables "inventory_item" and
"inventory_item_detail" .

In the physicalinventory table i can insert a line at for example 31
december 2010 when i will count my physical inventory with the reason.

The i can insert in the table "inventory_item_variance" the link to the
physicalinventory (physicalinventoryid) with the "inventoryitemid"  and i
want to insert here aivalabletopromiseVARIANCE value and the
quantityonhandVARIANCE value.
However this is not possible in the entity engine in frameworktools.

How do i have to do it then? How does the thing works then?

regards,
Heidi
2010/11/9 James McGill <[hidden email]>

> On Tue, Nov 9, 2010 at 5:16 AM, pinaki_22 <[hidden email]>
> wrote:
>
> >
> >  What is the purpose of
> > INVENTORY_ITEM_DETAIL table.
> >
>
> InventoryItemDetail is equivalent to a credit / debit system for
> quantities.  Instead of tracking the quantity only in the InventoryItem,
> the
> Detail records provide a running total of transactions that can be audited
> against the InventoryItem.
>
> If you want to manually change the ATP and QOH quantities, use the Variance
> service.
>
> In a production system, variances will probably be rare, since inventory
> should be created as a result of shipment receipts or work efforts.
>
> --
> James McGill
> Phoenix AZ
>

[pinaki_22]

But the question still remain unanswered is how do I increase the inventory for a product? Is it possible to do from catalog/store?if yes, then how?

another thing I did observe is that when I place an order for a product A(suppose) with a particular user name more than I record got populated in the INVENTORY_ITEM and INVENTORY_ITEM_DETAIL table.
If I do a checkout again for that same product with the same user name and password more then one records are inserted but is not same for the earlier case.

As this continues for a long time during the check out process lots of record get populated and add to the performance over head.

Is there any way to check the insertion of the records into the table? or an event that can be commented in the code? I want to stop the query

(SELECT INVENTORY_ITEM_ID, INVENTORY_ITEM_TYPE_ID, PRODUCT_ID, PARTY_ID, OWNER_PARTY_ID, STATUS_ID, DATETIME_RECEIVED, DATETIME_MANUFACTURED, EXPIRE_DATE, FACILITY_ID, CONTAINER_ID, LOT_ID, UOM_ID, BIN_NUMBER, LOCATION_SEQ_ID, COMMENTS, QUANTITY_ON_HAND_TOTAL, AVAILABLE_TO_PROMISE_TOTAL, QUANTITY_ON_HAND, AVAILABLE_TO_PROMISE, SERIAL_NUMBER, SOFT_IDENTIFIER, ACTIVATION_NUMBER, ACTIVATION_VALID_THRU, UNIT_COST, CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP FROM INVENTORY_ITEM WHERE (PRODUCT_ID = ? AND FACILITY_ID = ?)

to fire. This query occurs when checkout of a product is done many times, which should not happen.