[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1 FROM dual WHERE exists (SELECT NULL FROM mrp_parameters);
select to_char(line_number), demand_class_code
from oe_order_lines_all
where line_id = p_dsrc_line;
SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
FROM mtl_reservations
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND supply_source_type_id = 13 -- Inventory
AND ((subinventory_code = p_subinv) OR
(subinventory_code IS NULL AND p_subinv IS NULL))
AND ((locator_id = p_locator) OR
(locator_id IS NULL AND p_locator IS NULL))
AND ((lot_number = p_lotnumber) OR
(lot_number IS NULL AND p_lotnumber IS NULL))
AND ((revision = p_revision) OR
(revision IS NULL AND p_revision IS NULL))
AND ((lpn_id = p_lpn_id) OR (lpn_id IS NULL))
AND demand_source_type_id = p_dsrc_type
AND ((demand_source_header_id = p_header_id) OR
(demand_source_header_id IS NULL AND (p_header_id = 0 or p_header_id IS NULL)))
AND ((demand_source_name = p_dsrc_name) OR
(demand_source_name IS NULL AND p_dsrc_name IS NULL))
AND ((p_dsrc_type NOT IN (2,8,12))
OR (p_dsrc_type IN (2,8,12)
AND ((demand_source_line_id = p_dsrc_line) OR
(demand_source_line_id IS NULL
AND p_dsrc_line IS NULL))
AND ((demand_source_delivery = p_dsrc_delivery) OR
(demand_source_delivery IS NULL
AND p_dsrc_delivery IS NULL))))
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE; */
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
FROM mtl_reservations
WHERE demand_source_header_id = :demand_source_header_id
AND demand_source_line_id = :demand_source_line_id
AND demand_source_type_id = :demand_source_type_id
AND supply_source_type_id = :supply_source_type_id
AND (:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery)
AND (:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id)
AND (:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULL
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING p_header_id
, p_dsrc_line
, p_dsrc_type
, l_supply_src_type_id
, p_dsrc_delivery
, l_miss_num
, p_dsrc_delivery
, p_dsrc_delivery
, p_organization_id
, l_miss_num
, p_organization_id
, p_organization_id
, p_inventory_item_id
, l_miss_num
, p_inventory_item_id
, p_inventory_item_id
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
FROM mtl_reservations
WHERE organization_id = :organization_id
AND inventory_item_id = :inventory_item_id
AND supply_source_type_id = :supply_source_type_id
AND (:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id = demand_source_header_id)
AND (:demand_source_line_id = :l_miss_num
OR :demand_source_line_id IS NULL
AND demand_source_line_id IS NULL
OR :demand_source_line_id = demand_source_line_id)
AND (:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id = demand_source_type_id)
AND (:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING p_organization_id
, p_inventory_item_id
, l_supply_src_type_id
, p_header_id
, l_miss_num
, p_header_id
, p_header_id
, p_dsrc_line
, l_miss_num
, p_dsrc_line
, p_dsrc_line
, p_dsrc_type
, l_miss_num
, p_dsrc_type
, p_dsrc_type
, p_dsrc_delivery
, l_miss_num
, p_dsrc_delivery
, p_dsrc_delivery
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
FROM mtl_reservations
WHERE (:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id)
AND (:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULL
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id)
AND supply_source_type_id = :supply_source_type_id
AND (:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id = demand_source_header_id)
AND (:demand_source_line_id = :l_miss_num
OR :demand_source_line_id IS NULL
AND demand_source_line_id IS NULL
OR :demand_source_line_id = demand_source_line_id)
AND (:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id = demand_source_type_id)
AND (:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING p_organization_id
, l_miss_num
, p_organization_id
, p_organization_id
, p_inventory_item_id
, l_miss_num
, p_inventory_item_id
, p_inventory_item_id
, l_supply_src_type_id
, p_header_id
, l_miss_num
, p_header_id
, p_header_id
, p_dsrc_line
, l_miss_num
, p_dsrc_line
, p_dsrc_line
, p_dsrc_type
, l_miss_num
, p_dsrc_type
, p_dsrc_type
, p_dsrc_delivery
, l_miss_num
, p_dsrc_delivery
, p_dsrc_delivery
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
FROM mtl_reservations
WHERE demand_source_header_id = :demand_source_header_id
AND demand_source_type_id = :demand_source_type_id
AND supply_source_type_id = :supply_source_type_id
AND (:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id)
AND (:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULl
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code = subinventory_code
OR subinventory_code IS NULL)
AND (:locator_id = :l_miss_num
OR :locator_id = locator_id
OR locator_id IS NULL)
AND (:lot_number = :l_miss_char
OR :lot_number = lot_number
OR lot_number IS NULL)
AND (:revision = :l_miss_char
OR :revision = revision
OR revision IS NULL)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY lpn_id, revision, lot_number, subinventory_code, locator_id FOR UPDATE '
USING p_header_id
, p_dsrc_type
, l_supply_src_type_id
, p_organization_id
, l_miss_num
, p_organization_id
, p_organization_id
, p_inventory_item_id
, l_miss_num
, p_inventory_item_id
, p_inventory_item_id
, p_subinv
, l_miss_char
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
FROM mtl_reservations
WHERE organization_id = :organization_id
AND inventory_item_id = :inventory_item_id
AND supply_source_type_id = :supply_source_type_id
AND (:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL OR :demand_source_header_id = 0
AND demand_source_header_id IS NULL
OR :demand_source_header_id = demand_source_header_id)
AND (:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id = demand_source_type_id)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code = subinventory_code
OR subinventory_code IS NULL)
AND (:locator_id = :l_miss_num
OR :locator_id = locator_id
OR locator_id IS NULL)
AND (:lot_number = :l_miss_char
OR :lot_number = lot_number
OR lot_number IS NULL)
AND (:revision = :l_miss_char
OR :revision = revision
OR revision IS NULL)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY lpn_id, revision, lot_number, subinventory_code, locator_id FOR UPDATE '
USING p_organization_id
, p_inventory_item_id
, l_supply_src_type_id
, p_header_id
, l_miss_num
, p_header_id
, p_header_id
, p_header_id --Added for bug 4376838
, p_dsrc_type
, l_miss_num
, p_dsrc_type
, p_dsrc_type
, p_subinv
, l_miss_char
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
FROM mtl_reservations
WHERE supply_source_type_id = :supply_source_type_id
AND (:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL OR :demand_source_header_id = 0
AND demand_source_header_id IS NULL
OR :demand_source_header_id = demand_source_header_id)
AND (:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id = demand_source_type_id)
AND (:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id)
AND (:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULl
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING l_supply_src_type_id
, p_header_id
, l_miss_num
, p_header_id
, p_header_id
, p_header_id --Added for Bug 4376838
, p_dsrc_type
, l_miss_num
, p_dsrc_type
, p_dsrc_type
, p_organization_id
, l_miss_num
, p_organization_id
, p_organization_id
, p_inventory_item_id
, l_miss_num
, p_inventory_item_id
, p_inventory_item_id
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
SELECT 1 FROM dual WHERE exists (SELECT NULL FROM mrp_parameters);
select to_char(line_number), demand_class_code
from oe_order_lines_all
where line_id = p_dsrc_line;
select tracking_quantity_ind
from mtl_system_items
where organization_id = p_organization_id and inventory_item_id = p_inventory_item_id;
SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
FROM mtl_reservations
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND supply_source_type_id = 13 -- Inventory
AND ((subinventory_code = p_subinv) OR
(subinventory_code IS NULL AND p_subinv IS NULL))
AND ((locator_id = p_locator) OR
(locator_id IS NULL AND p_locator IS NULL))
AND ((lot_number = p_lotnumber) OR
(lot_number IS NULL AND p_lotnumber IS NULL))
AND ((revision = p_revision) OR
(revision IS NULL AND p_revision IS NULL))
AND ((lpn_id = p_lpn_id) OR (lpn_id IS NULL))
AND demand_source_type_id = p_dsrc_type
AND ((demand_source_header_id = p_header_id) OR
(demand_source_header_id IS NULL AND (p_header_id = 0 or p_header_id IS NULL)))
AND ((demand_source_name = p_dsrc_name) OR
(demand_source_name IS NULL AND p_dsrc_name IS NULL))
AND ((p_dsrc_type NOT IN (2,8,12))
OR (p_dsrc_type IN (2,8,12)
AND ((demand_source_line_id = p_dsrc_line) OR
(demand_source_line_id IS NULL
AND p_dsrc_line IS NULL))
AND ((demand_source_delivery = p_dsrc_delivery) OR
(demand_source_delivery IS NULL
AND p_dsrc_delivery IS NULL))))
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE; */
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
, NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
FROM mtl_reservations
WHERE demand_source_header_id = :demand_source_header_id
AND demand_source_line_id = :demand_source_line_id
AND demand_source_type_id = :demand_source_type_id
AND supply_source_type_id = :supply_source_type_id
AND (:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery)
AND (:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id)
AND (:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULL
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING p_header_id
, p_dsrc_line
, p_dsrc_type
, l_supply_src_type_id
, p_dsrc_delivery
, l_miss_num
, p_dsrc_delivery
, p_dsrc_delivery
, p_organization_id
, l_miss_num
, p_organization_id
, p_organization_id
, p_inventory_item_id
, l_miss_num
, p_inventory_item_id
, p_inventory_item_id
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
, NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
FROM mtl_reservations
WHERE organization_id = :organization_id
AND inventory_item_id = :inventory_item_id
AND supply_source_type_id = :supply_source_type_id
AND (:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id = demand_source_header_id)
AND (:demand_source_line_id = :l_miss_num
OR :demand_source_line_id IS NULL
AND demand_source_line_id IS NULL
OR :demand_source_line_id = demand_source_line_id)
AND (:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id = demand_source_type_id)
AND (:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING p_organization_id
, p_inventory_item_id
, l_supply_src_type_id
, p_header_id
, l_miss_num
, p_header_id
, p_header_id
, p_dsrc_line
, l_miss_num
, p_dsrc_line
, p_dsrc_line
, p_dsrc_type
, l_miss_num
, p_dsrc_type
, p_dsrc_type
, p_dsrc_delivery
, l_miss_num
, p_dsrc_delivery
, p_dsrc_delivery
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
, NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
FROM mtl_reservations
WHERE (:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id)
AND (:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULL
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id)
AND supply_source_type_id = :supply_source_type_id
AND (:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id = demand_source_header_id)
AND (:demand_source_line_id = :l_miss_num
OR :demand_source_line_id IS NULL
AND demand_source_line_id IS NULL
OR :demand_source_line_id = demand_source_line_id)
AND (:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id = demand_source_type_id)
AND (:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING p_organization_id
, l_miss_num
, p_organization_id
, p_organization_id
, p_inventory_item_id
, l_miss_num
, p_inventory_item_id
, p_inventory_item_id
, l_supply_src_type_id
, p_header_id
, l_miss_num
, p_header_id
, p_header_id
, p_dsrc_line
, l_miss_num
, p_dsrc_line
, p_dsrc_line
, p_dsrc_type
, l_miss_num
, p_dsrc_type
, p_dsrc_type
, p_dsrc_delivery
, l_miss_num
, p_dsrc_delivery
, p_dsrc_delivery
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
, NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
FROM mtl_reservations
WHERE demand_source_header_id = :demand_source_header_id
AND demand_source_type_id = :demand_source_type_id
AND supply_source_type_id = :supply_source_type_id
AND (:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id)
AND (:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULl
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING p_header_id
, p_dsrc_type
, l_supply_src_type_id
, p_organization_id
, l_miss_num
, p_organization_id
, p_organization_id
, p_inventory_item_id
, l_miss_num
, p_inventory_item_id
, p_inventory_item_id
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
, NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
FROM mtl_reservations
WHERE organization_id = :organization_id
AND inventory_item_id = :inventory_item_id
AND supply_source_type_id = :supply_source_type_id
AND (:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id = demand_source_header_id)
AND (:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id = demand_source_type_id)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING p_organization_id
, p_inventory_item_id
, l_supply_src_type_id
, p_header_id
, l_miss_num
, p_header_id
, p_header_id
, p_dsrc_type
, l_miss_num
, p_dsrc_type
, p_dsrc_type
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
OPEN l_rsv_cur2 FOR ' SELECT
reservation_id
, primary_reservation_quantity - NVL(detailed_quantity,0)
, NVL(secondary_reservation_quantity,0) - NVL(secondary_detailed_quantity,0) -- INVCONV
FROM mtl_reservations
WHERE supply_source_type_id = :supply_source_type_id
AND (:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id = demand_source_header_id)
AND (:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id = demand_source_type_id)
AND (:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id)
AND (:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULl
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id)
AND (:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code)
AND (:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id)
AND (:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number)
AND (:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision)
AND (:lpn_id = :l_miss_num
OR ((lpn_id IS NULL) OR (:lpn_id = lpn_id)))
AND (:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name)
AND (primary_reservation_quantity - NVL(detailed_quantity,0)) > 0
ORDER BY NVL(lpn_id, 0) DESC, reservation_id FOR UPDATE '
USING l_supply_src_type_id
, p_header_id
, l_miss_num
, p_header_id
, p_header_id
, p_dsrc_type
, l_miss_num
, p_dsrc_type
, p_dsrc_type
, p_organization_id
, l_miss_num
, p_organization_id
, p_organization_id
, p_inventory_item_id
, l_miss_num
, p_inventory_item_id
, p_inventory_item_id
, p_subinv
, l_miss_char
, p_subinv
, p_subinv
, p_locator
, l_miss_num
, p_locator
, p_locator
, p_lotnumber
, l_miss_char
, p_lotnumber
, p_lotnumber
, p_revision
, l_miss_char
, p_revision
, p_revision
, p_lpn_id
, l_miss_num
, p_lpn_id
, p_dsrc_name
, l_miss_char
, p_dsrc_name
, p_dsrc_name ;
SELECT msn.inventory_item_id, msn.serial_number bulk collect INTO
l_serial_number_table FROM
mtl_serial_numbers msn, mtl_unit_transactions mut WHERE
msn.reservation_id = l_reservation_id AND msn.current_organization_id =
p_organization_id AND msn.inventory_item_id = p_inventory_item_id
AND mut.transaction_id = p_transaction_id AND msn.serial_number =
mut.serial_number AND msn.inventory_item_id = mut.inventory_item_id
AND msn.current_organization_id = mut.organization_id;
SELECT A.ORGANIZATION_ID,
A.INVENTORY_ITEM_ID,
NVL(A.TRANSACTION_SOURCE_ID, 0) TRANSACTION_SOURCE_ID,
A.TRANSACTION_SOURCE_TYPE_ID,
A.TRX_SOURCE_DELIVERY_ID,
A.TRX_SOURCE_LINE_ID,
A.REVISION,
DECODE(C.LOT_CONTROL_CODE, 2, B.LOT_NUMBER, A.LOT_NUMBER) LOT_NUMBER,
A.SUBINVENTORY_CODE,
A.LOCATOR_ID,
DECODE (C.LOT_CONTROL_CODE, 2,
ABS(NVL(B.PRIMARY_QUANTITY,0)),
A.PRIMARY_QUANTITY *(-1)) PRIMARY_QUANTITY,
A.TRANSACTION_SOURCE_NAME,
FND_DATE.DATE_TO_CANONICAL(A.TRANSACTION_DATE) TRANSACTION_DATE
FROM MTL_SYSTEM_ITEMS C,
MTL_TRANSACTION_LOTS_TEMP B,
MTL_MATERIAL_TRANSACTIONS_TEMP A
WHERE A.TRANSACTION_HEADER_ID = p_transaction_header_id
AND A.ORGANIZATION_ID = C.ORGANIZATION_ID
AND A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
AND B.TRANSACTION_TEMP_ID (+) = A.TRANSACTION_TEMP_ID
AND A.PRIMARY_QUANTITY < 0
ORDER BY A.TRANSACTION_SOURCE_TYPE_ID,
A.TRANSACTION_SOURCE_ID,
A.TRANSACTION_SOURCE_NAME,
A.TRX_SOURCE_LINE_ID,
A.TRX_SOURCE_DELIVERY_ID,
A.INVENTORY_ITEM_ID,
A.ORGANIZATION_ID;