#55 Valuation Effective Date

[Ayaz Ahmed]

Hi, Victor Perez

When I try to generate "Valuation Effective Date Report" It is taking very long time approximately 15 to 30 minutes.

Ayaz Ahmed

[ADAXA]

This may be related, perhaps you can try something similar...

Overview: Inventory Valuation Performance
Description: This routine is used regularly for inventory valuation, but is consuming a lot of time to calculate.
UPDATE T_InventoryValue SET QtyOnHand=(SELECT T_InventoryValue.QtyOnHand - COALESCE(SUM(t.MovementQty), 0) FROM M_Transaction t INNER JOIN M_Locator l ON (t.M_Locator_ID=l.M_Locator_ID) WHERE t.M_Product_ID=T_InventoryValue.M_Product_ID AND t.MovementDate > T_InventoryValue.DateValue AND l.M_Warehouse_ID=T_InventoryValue.M_Warehouse_ID) WHERE T_InventoryValue.M_AttributeSetInstance_ID=0;
Day Time Count Av. Duration 996.63

Closure details:
Created this index to bring query down to around 6-8 seconds.
CREATE INDEX m_transaction_inc100720007
ON m_transaction
USING btree
(m_locator_id, m_product_id, movementdate)
TABLESPACE indexspace;

regards

[Ayaz Ahmed]

Hi, adaxa

CREATE INDEX testindex ON t_inventoryvalue USING btree (m_warehouse_id, m_product_id, ad_pinstance_id, m_attributesetinstance_id, m_pricelist_version_id, datevalue, c_currency_id, qtyonhand, cost, costamt, m_costelement_id, m_costtype_id, m_product_category_id, costamtll, cumulatedamt)
I have created above index but still it is taking time. can you check and give me comments if this index is not correct.

Ayaz Ahmed