The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT msi.inventory_item_id,
msi.organization_id ,
msi.primary_uom_code,
msi.secondary_uom_code ,
msi.inventory_item_flag ,
msi.shippable_item_flag ,
msi.tracking_quantity_ind ,
msi.reservable_type ,
muomt1.uom_class,
muomt2.uom_class
INTO p_inventory_item_rec.inventory_item_id,
p_inventory_item_rec.org_id ,
p_inventory_item_rec.primary_uom_code,
p_inventory_item_rec.secondary_uom_code ,
p_inventory_item_rec.inventory_item_flag ,
p_inventory_item_rec.shippable_item_flag ,
p_inventory_item_rec.tracking_quantity_ind ,
p_inventory_item_rec.reservable_type ,
p_inventory_item_rec.primary_uom_class,
p_inventory_item_rec.secondary_uom_class
FROM mtl_system_items msi,
MTL_UNITS_OF_MEASURE muomt1,
MTL_UNITS_OF_MEASURE muomt2
WHERE msi.primary_uom_code = muomt1.uom_code
AND msi.secondary_uom_code = muomt2.uom_code(+)
AND msi.inventory_item_id = p_line_rec.inventory_item_id
AND msi.organization_id = p_line_rec.ship_from_org_id;
SELECT wms_enabled_flag
INTO p_inventory_item_rec.wms_enabled_flag
FROM mtl_parameters
WHERE organization_id= p_line_rec.ship_from_org_id;
Select fulfillment_base
Into l_fulfillment_base
From oe_order_lines_all
Where line_id = p_line_id ;
SELECT 1
INTO l_validate_combinition
FROM mtl_system_items_b msi,
org_organization_definitions org
WHERE msi.inventory_item_id= p_line_rec.Inventory_Item_Id
AND org.organization_id=msi.organization_id
AND sysdate<=nvl(org.disable_date,sysdate)
AND org.organization_id=p_line_rec.ship_from_org_id
AND rownum=1 ;
SELECT fulfillment_base
INTO l_fulfillment_base
FROM oe_order_lines
WHERE line_id = p_line_rec.reference_line_id;
SELECT fulfillment_base
INTO l_fulfillment_base
FROM oe_order_lines
WHERE line_id = p_line_rec.split_from_line_id;
SELECT UOM_CLASS
INTO l_inventory_item_rec.ordered_uom_class
FROM MTL_UNITS_OF_MEASURE
WHERE uom_code = p_line_rec.order_quantity_uom ;
SELECT 'Y'
INTO l_pick_status
FROM WSH_DELIVERY_LINE_STATUS_V
WHERE SOURCE_CODE = 'OE'
AND SOURCE_LINE_ID = p_line_rec.line_id
AND PICK_STATUS IN('C','Y','S');
Suppose there is a update(like change in UOM) on line 1.1 which causes FB on line 1.1 to change to S .... this change be allowed
,since referenced RMA against the line has null FB, (which means non wms warehouse on line, so whenever in future, warehouse on line 2.1 is modified to a wms warehouse ,
it will take value of FB from line 1.1 and copy it to 2.1).
*/
BEGIN
l_ret_exists:= NULL;
SELECT DISTINCT fulfillment_base
INTO l_ful_base
FROM oe_order_lines_all
WHERE line_category_code= 'RETURN'
AND return_context IN ('INVOICE' ,'ORDER')
AND fulfillment_base IS NOT NULL
AND reference_line_id = p_line_rec.line_id ;