[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select internal_order_enabled_flag
into l_internal_order_enabled_flag
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_organization_id;
select purchasing_enabled_flag
into l_purchasable_flag
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_organization_id;
select nvl(msi.unit_of_issue,msi.primary_unit_of_measure)
into l_unit_of_issue
from mtl_system_items msi
where msi.inventory_item_id = p_item_id
and msi.organization_id = p_organization_id;
select shippable_item_flag, so_transactions_flag, stock_enabled_flag, mtl_transactions_enabled_flag
into l_shippable_flag, l_oe_transactable_flag, l_stockable_flag, l_mtl_transactable_flag
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_organization_id;
select 1 into l_is_valid_shipping_network
from mtl_interorg_parameters
where from_organization_id = p_from_organization_id
and to_organization_id = p_to_organization_id;
SELECT 1 into l_is_item_assigned
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id
and msi.organization_id = p_source_organization_id;
SELECT ALL_SOURCES_V.SOURCE_ORGANIZATION_ID,
ALL_SOURCES_V.SOURCE_TYPE,
ALL_SOURCES_V.VENDOR_ID,
ALL_SOURCES_V.VENDOR_SITE_ID
FROM
(
SELECT SOURCE_ORGANIZATION_ID, SOURCE_TYPE, VENDOR_ID, VENDOR_SITE_ID,
RANK, ALLOCATION_PERCENT, SOURCING_LEVEL
FROM MRP_ITEM_SOURCING_LEVELS_V
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = p_dest_organization_id
AND ASSIGNMENT_SET_ID = l_set_id
AND (SOURCE_TYPE = 1 OR
(SOURCE_TYPE = 3 AND
'Y' = l_is_purchasable_flag AND
VENDOR_ID = p_vendor_id AND
NVL(VENDOR_SITE_ID, p_vendor_site_id) = p_vendor_site_id))
AND SYSDATE BETWEEN EFFECTIVE_DATE AND NVL(DISABLE_DATE, SYSDATE+1)
) ALL_SOURCES_V
ORDER BY ALL_SOURCES_V.SOURCING_LEVEL ASC,
ALL_SOURCES_V.ALLOCATION_PERCENT DESC,
NVL(ALL_SOURCES_V.RANK, 9999) ASC;
SELECT msub.secondary_inventory_name,
(nvl(mos.total_qoh,0) - sum(nvl(mrs.primary_reservation_quantity,0))) avail_quantity
FROM mtl_secondary_inventories msub,
mtl_onhand_sub_v mos,
mtl_reservations mrs,
mtl_system_items msi
WHERE msub.organization_id = l_source_organization_id -- bug 5470125, do not bind p_source_organization_id
AND 'Y' = (CASE WHEN Nvl(fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER'),2) = 2
AND msub.asset_inventory = 2
THEN 'N'
ELSE 'Y'
END)
and msi.organization_id = l_source_organization_id
and msi.inventory_item_id = p_item_id
and (trunc(sysdate) < nvl(msub.disable_date, trunc(sysdate + 1)))
and msub.quantity_tracked = 1
and msub.secondary_inventory_name = mos.subinventory_code (+)
and mos.inventory_item_id = mrs.inventory_item_id (+)
and mos.organization_id = mrs.organization_id (+)
and mos.subinventory_code = mrs.subinventory_code (+)
and mos.inventory_item_id (+) = p_item_id
and mos.organization_id (+) = l_source_organization_id
and (msi.restrict_subinventories_code = 2
or (msi.restrict_subinventories_code = 1
and exists (select null
from mtl_item_sub_inventories mis
where mis.organization_id = msi.organization_id
and mis.inventory_item_id = msi.inventory_item_id
and mis.secondary_inventory = msub.secondary_inventory_name)
)
)
and msub.reservable_type=1 -- bug 2986842 need to restrict to reservable subinventory
-- otherwise po creation will fail
GROUP BY msub.secondary_inventory_name, mos.total_qoh
ORDER BY avail_quantity DESC, msub.secondary_inventory_name ASC;