The following lines contain the word 'select', 'insert', 'update' or 'delete':
* Added an extra FOR UPDATE clause in the below cursor to avoid the
* deadlock scenario.
*/
cursor supply_cursor is
select ms.quantity,
ms.unit_of_measure,
ms.item_id,
ms.from_organization_id,
ms.to_organization_id,
ms.receipt_date,
ms.supply_type_code,
ms.supply_source_id,
ms.rowid row_id
from mtl_supply ms
where ms.change_flag = 'Y'
FOR UPDATE ;
select inventory_organization_id
into fsp_org_id
from financials_system_parameters;
delete from mtl_supply
where rowid = supply_rec.row_id;
select postprocessing_lead_time
into lead_time
from mtl_system_items
where inventory_item_id = supply_rec.item_id
and organization_id =
nvl(supply_rec.to_organization_id,
fsp_org_id);
update mtl_supply
set to_org_primary_quantity = primary_qty,
to_org_primary_uom = primary_uom,
change_flag = null,
expected_delivery_date =
decode(supply_rec.item_id, null, null,
(supply_rec.receipt_date)
+ nvl(lead_time, 0 ))
where rowid = supply_rec.row_id;