The shipping function of the customer service is very slow.

The customer-generated miscellaneous warehouse picking function is very slow. The reasons for the preliminary test are as follows:

As the data volume grows rapidly from precision to each shard, the data volume in the beginning + first month reaches 500 million.

As a result, some validators are very slow.

Example 1:

DECLARE
V_ava_qty NUMBER;
BEGIN
V_ava_qty: = inv_item_inq.get_available_qty (p_organization_id => 83,
P_inventory_item_id => 1317,
P_revision => NULL,
P_subinventory_code => 'b01 ',
P_locator_id => 15,
P_lot_number => 'beha1mx-TP1B331A01 ',
P_cost_group_id =>-1,
P_revision_control => 'false ',
P_lot_control => 'true ',
P_serial_control => 'false ');
Dbms_output.put_line (v_ava_qty );
END;

During shipment, check the available quantity> = shipment.

Using this program to get the available amount is very slow. In the development environment, the program is not found to be so slow due to a small amount of data.

 

Change it:

Select sum (moq. transaction_quantity)
INTO p_onhand_qty
FROM mtl_onhand_quantities moq
WHERE moq. organization_id = p_inv_org_id
AND moq. subinventory_code = p_subinv
AND nvl (moq. locator_id,-1) = nvl (p_locator_id,-1)
AND moq. inventory_item_id = p_item_id
AND moq. lot_number = p_lot_number;

Select sum (mr. primary_reservation_quantity)
INTO p_res_qty
FROM mtl_reservations mr
WHERE mr. organization_id = p_inv_org_id
AND mr. subinventory_code = p_subinv
AND mr. supply_source_type_id = 13
AND nvl (mr. locator_id,-1) = nvl (p_locator_id,-1)
AND mr. inventory_item_id = p_item_id
AND mr. lot_number = p_lot_number;

RETURN nvl (p_onhand_qty, 0)-nvl (p_res_qty, 0 );